summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorSergei Petrunia <psergey@askmonty.org>2015-06-20 05:01:16 +0300
committerSergei Petrunia <psergey@askmonty.org>2015-06-20 05:01:16 +0300
commitcbb8b2d033359fb6e4ebfa00e059b47e60442b8e (patch)
tree0b4a5d997833e857824710f900ebaf830919e31d
parentebe2bd74fe7e30306feefae7acf201d18cbec267 (diff)
downloadmariadb-git-cbb8b2d033359fb6e4ebfa00e059b47e60442b8e.tar.gz
More testcases.
-rw-r--r--mysql-test/r/analyze_stmt_orderby.result106
-rw-r--r--mysql-test/t/analyze_stmt_orderby.test31
2 files changed, 135 insertions, 2 deletions
diff --git a/mysql-test/r/analyze_stmt_orderby.result b/mysql-test/r/analyze_stmt_orderby.result
index 440a55060d4..0f5c7c8437b 100644
--- a/mysql-test/r/analyze_stmt_orderby.result
+++ b/mysql-test/r/analyze_stmt_orderby.result
@@ -381,7 +381,7 @@ id select_type table type possible_keys key key_len ref rows r_rows filtered r_f
1 SIMPLE t3 ALL NULL NULL NULL NULL 1 0.00 100.00 100.00 Using filesort
drop table t3;
#
-#
+# A test for duplicate_removal()
#
create table t3 (a int, b int);
insert into t3 select a, 123 from t0;
@@ -426,5 +426,109 @@ ANALYZE
}
}
}
+#
+# A query with two filesort calls:
+# - first is needed to do group-by-group grouping to calculate COUNT(DISTINCT)
+# - the second is need to produce ORDER BY.
+# (see MDEV-7836 for description of the query plan)
+create table t5 (a int , b int) ;
+create table t6 like t5 ;
+create table t7 like t5 ;
+insert into t5 values (0, 100), (1, 2), (1, 3), (2, 2), (2, 7),
+(2, -1), (3, 10);
+insert into t6 values (0, 0), (1, 1), (2, 1), (3, 1), (4, 1);
+insert into t7 values (3, 3), (2, 2), (1, 1);
+# TODO: This ANALYZE output doesn't make it clear what is used for what.
+analyze format=json
+select count(distinct t5.b) as sum from t5, t6
+where t5.a=t6.a and t6.b > 0 and t5.a <= 5
+group by t5.a order by sum limit 1;
+ANALYZE
+{
+ "query_block": {
+ "select_id": 1,
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "filesort": {
+ "r_loops": 1,
+ "r_limit": 1,
+ "r_used_priority_queue": true,
+ "r_output_rows": 2,
+ "filesort": {
+ "r_loops": 1,
+ "r_used_priority_queue": false,
+ "r_output_rows": 6,
+ "r_buffer_size": "REPLACED",
+ "temporary_table": {
+ "temporary_table": {
+ "table": {
+ "table_name": "t6",
+ "access_type": "ALL",
+ "r_loops": 1,
+ "rows": 5,
+ "r_rows": 5,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 100,
+ "r_filtered": 80,
+ "attached_condition": "((t6.b > 0) and (t6.a <= 5))"
+ },
+ "block-nl-join": {
+ "table": {
+ "table_name": "t5",
+ "access_type": "ALL",
+ "r_loops": 1,
+ "rows": 7,
+ "r_rows": 7,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 100,
+ "r_filtered": 100
+ },
+ "buffer_type": "flat",
+ "buffer_size": "128Kb",
+ "join_type": "BNL",
+ "attached_condition": "(t5.a = t6.a)",
+ "r_filtered": 21.429
+ }
+ }
+ }
+ }
+ }
+ }
+}
+explain format=json
+select count(distinct t5.b) as sum from t5, t6
+where t5.a=t6.a and t6.b > 0 and t5.a <= 5
+group by t5.a order by sum limit 1;
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "filesort": {
+ "temporary_table": {
+ "function": "buffer",
+ "table": {
+ "table_name": "t6",
+ "access_type": "ALL",
+ "rows": 5,
+ "filtered": 100,
+ "attached_condition": "((t6.b > 0) and (t6.a <= 5))"
+ },
+ "block-nl-join": {
+ "table": {
+ "table_name": "t5",
+ "access_type": "ALL",
+ "rows": 7,
+ "filtered": 100
+ },
+ "buffer_type": "flat",
+ "buffer_size": "128Kb",
+ "join_type": "BNL",
+ "attached_condition": "(t5.a = t6.a)"
+ }
+ }
+ }
+ }
+}
+drop table t5,t6,t7;
drop table t3;
drop table t0,t1;
diff --git a/mysql-test/t/analyze_stmt_orderby.test b/mysql-test/t/analyze_stmt_orderby.test
index ab89163defa..4e280bebd37 100644
--- a/mysql-test/t/analyze_stmt_orderby.test
+++ b/mysql-test/t/analyze_stmt_orderby.test
@@ -110,7 +110,7 @@ delete from t3 order by a;
drop table t3;
--echo #
---echo #
+--echo # A test for duplicate_removal()
--echo #
create table t3 (a int, b int);
insert into t3 select a, 123 from t0;
@@ -119,5 +119,34 @@ insert into t3 select a, 123 from t0;
analyze format=json
select distinct max(t3.b) Q from t0, t3 where t0.a=t3.a group by t0.a order by null;
+
+--echo #
+--echo # A query with two filesort calls:
+--echo # - first is needed to do group-by-group grouping to calculate COUNT(DISTINCT)
+--echo # - the second is need to produce ORDER BY.
+--echo # (see MDEV-7836 for description of the query plan)
+
+
+create table t5 (a int , b int) ;
+create table t6 like t5 ;
+create table t7 like t5 ;
+insert into t5 values (0, 100), (1, 2), (1, 3), (2, 2), (2, 7),
+ (2, -1), (3, 10);
+insert into t6 values (0, 0), (1, 1), (2, 1), (3, 1), (4, 1);
+insert into t7 values (3, 3), (2, 2), (1, 1);
+
+--echo # TODO: This ANALYZE output doesn't make it clear what is used for what.
+--replace_regex /"r_total_time_ms": [0-9]*[.]?[0-9]*/"r_total_time_ms": "REPLACED"/ /"r_buffer_size": "[^"]+"/"r_buffer_size": "REPLACED"/
+analyze format=json
+select count(distinct t5.b) as sum from t5, t6
+ where t5.a=t6.a and t6.b > 0 and t5.a <= 5
+ group by t5.a order by sum limit 1;
+
+explain format=json
+select count(distinct t5.b) as sum from t5, t6
+ where t5.a=t6.a and t6.b > 0 and t5.a <= 5
+ group by t5.a order by sum limit 1;
+drop table t5,t6,t7;
+
drop table t3;
drop table t0,t1;