revision-id: 88a80e92dc444ce30718f3e08d3ab66fb02bcea4 (mariadb-10.3.10-284-g88a80e92dc4) parent(s): 3032cd8e91f1e1ead8b6f941e75cd29e473e7eaa author: Varun Gupta committer: Varun Gupta timestamp: 2019-04-24 13:31:24 +0530 message: MDEV-9959: A serious MariaDB server performance bug Step #2: If any field in the select list of the derived tables is present in the group by list also , then we are again guaranteed that ref access to the derived table would always produce one row per key. --- mysql-test/main/cte_nonrecursive.result | 6 +- mysql-test/main/derived.result | 43 +++++++++ mysql-test/main/derived.test | 26 +++++ mysql-test/main/derived_cond_pushdown.result | 106 ++++++++++----------- mysql-test/main/derived_opt.result | 2 +- mysql-test/main/derived_split_innodb.result | 4 +- mysql-test/main/derived_view.result | 34 +++---- mysql-test/main/join_cache.result | 6 +- mysql-test/main/selectivity.result | 4 +- mysql-test/main/selectivity_innodb.result | 4 +- mysql-test/main/subselect_extra.result | 2 +- mysql-test/main/subselect_extra_no_semijoin.result | 2 +- sql/sql_lex.cc | 62 ++++++++++++ sql/sql_lex.h | 1 + sql/table.cc | 3 + 15 files changed, 220 insertions(+), 85 deletions(-) diff --git a/mysql-test/main/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result index d80d34ecc7f..b04b0335289 100644 --- a/mysql-test/main/cte_nonrecursive.result +++ b/mysql-test/main/cte_nonrecursive.result @@ -85,14 +85,14 @@ with t as (select a, count(*) from t1 where b >= 'c' group by a) select * from t2,t where t2.c=t.a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t2.c 2 +1 PRIMARY <derived2> ref key0 key0 5 test.t2.c 1 2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort explain select * from t2, (select a, count(*) from t1 where b >= 'c' group by a) as t where t2.c=t.a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t2.c 2 +1 PRIMARY <derived2> ref key0 key0 5 test.t2.c 1 2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort # specivication of t contains having with t as (select a, count(*) from t1 where b >= 'c' @@ -597,7 +597,7 @@ explain select * from v2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where -1 PRIMARY <derived3> ref key0 key0 5 test.t2.c 2 +1 PRIMARY <derived3> ref key0 key0 5 test.t2.c 1 3 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort # with clause in the specification of a view that whose definition # table alias for a with table diff --git a/mysql-test/main/derived.result b/mysql-test/main/derived.result index 857246d68b4..e19b80678be 100644 --- a/mysql-test/main/derived.result +++ b/mysql-test/main/derived.result @@ -1249,3 +1249,46 @@ a a 4 4 6 6 drop table t1,t2,t3; +create table t1(a int, b int); +insert into t1 values (1,1),(2,2),(3,3); +create table t2(a int, b int,c int); +insert into t2(a,b,c) values (7,2,2),(8,1,1),(1,2,5); +create table t3(a int, b int); +insert into t3(a,b) values (7,1),(8,2), (1,3); +# expects rows=1 for derived table as group by fields are a prefix of the keyparts involved in ref access +explain select * from t1 , ((select a,b,c from t2 group by a))q where t1.a=q.a; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 1 +2 DERIVED t2 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort +select * from t1 , ((select a,b,c from t2 group by a))q where t1.a=q.a; +a b a b c +1 1 1 2 5 +# expects rows=1 for derived table as group by fields are a prefix of the keyparts involved in ref access, this case +# involves use of multiple equalities to check the prefix condition for rows=1 +explain select * from t1 , ((select a,b from t2 where a=c group by c))q where t1.a=q.a; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 1 +2 DERIVED t2 ALL NULL NULL NULL NULL 3 Using where; Using temporary; Using filesort +select * from t1 , ((select a,b from t2 where a=c group by c))q where t1.a=q.a; +a b a b +# rows should not be 1 for the derived table as group by is not a prefix of the keyparts for ref access +explain select * from t1 , ((select a,b from t2 where b=c group by c))q where t1.a=q.a; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 2 +2 DERIVED t2 ALL NULL NULL NULL NULL 3 Using where; Using temporary; Using filesort +select * from t1 , ((select a,b from t2 where b=c group by c))q where t1.a=q.a; +a b a b +# rows should not be 1 for the derived table as group by is not a prefix of the keyparts for ref access +explain select * from t1 , ((select a,b,c from t2 group by a))q where t1.a=q.b and t1.b=q.c; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY <derived2> ref key0 key0 10 test.t1.a,test.t1.b 2 +2 DERIVED t2 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort +select * from t1 , ((select a,b,c from t2 group by a))q where t1.a=q.b and t1.b=q.c; +a b a b c +1 1 8 1 1 +2 2 7 2 2 +drop table t1,t2,t3; diff --git a/mysql-test/main/derived.test b/mysql-test/main/derived.test index 990f955450a..494500a1c8a 100644 --- a/mysql-test/main/derived.test +++ b/mysql-test/main/derived.test @@ -1062,3 +1062,29 @@ analyze select * from t1 , ( (select t2.a from t2 order by c) union all (select select * from t1 , ( (select t2.a from t2 order by c) union all (select t2.a from t2 order by c) except(select t3.a from t3 order by b))q where t1.a=q.a; drop table t1,t2,t3; + +create table t1(a int, b int); +insert into t1 values (1,1),(2,2),(3,3); +create table t2(a int, b int,c int); +insert into t2(a,b,c) values (7,2,2),(8,1,1),(1,2,5); +create table t3(a int, b int); +insert into t3(a,b) values (7,1),(8,2), (1,3); + +--echo # expects rows=1 for derived table as group by fields are a prefix of the keyparts involved in ref access +explain select * from t1 , ((select a,b,c from t2 group by a))q where t1.a=q.a; +select * from t1 , ((select a,b,c from t2 group by a))q where t1.a=q.a; + +--echo # expects rows=1 for derived table as group by fields are a prefix of the keyparts involved in ref access, this case +--echo # involves use of multiple equalities to check the prefix condition for rows=1 +explain select * from t1 , ((select a,b from t2 where a=c group by c))q where t1.a=q.a; +select * from t1 , ((select a,b from t2 where a=c group by c))q where t1.a=q.a; + +--echo # rows should not be 1 for the derived table as group by is not a prefix of the keyparts for ref access +explain select * from t1 , ((select a,b from t2 where b=c group by c))q where t1.a=q.a; +select * from t1 , ((select a,b from t2 where b=c group by c))q where t1.a=q.a; + +--echo # rows should not be 1 for the derived table as group by is not a prefix of the keyparts for ref access +explain select * from t1 , ((select a,b,c from t2 group by a))q where t1.a=q.b and t1.b=q.c; +select * from t1 , ((select a,b,c from t2 group by a))q where t1.a=q.b and t1.b=q.c; +drop table t1,t2,t3; + diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index 8086c4480f6..2f46e7eeeed 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -1551,7 +1551,7 @@ a b max_c avg_c a b c d explain select * from v1,t2 where (v1.a=v1.b) and (v1.a=t2.a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where -1 PRIMARY <derived2> ref key0 key0 10 test.t2.a,test.t2.a 2 +1 PRIMARY <derived2> ref key0 key0 10 test.t2.a,test.t2.a 1 2 DERIVED t1 ALL NULL NULL NULL NULL 20 Using where; Using temporary; Using filesort explain format=json select * from v1,t2 where (v1.a=v1.b) and (v1.a=t2.a); EXPLAIN @@ -1573,7 +1573,7 @@ EXPLAIN "key_length": "10", "used_key_parts": ["a", "b"], "ref": ["test.t2.a", "test.t2.a"], - "rows": 2, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -1839,7 +1839,7 @@ explain select * from v_decimal as v,t2_decimal as t where (v.a=v.b) and (v.b=t.b) and ((t.b>1) or (v.a=1)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t ALL NULL NULL NULL NULL 9 Using where -1 PRIMARY <derived2> ref key0 key0 6 test.t.b,test.t.b 2 +1 PRIMARY <derived2> ref key0 key0 6 test.t.b,test.t.b 1 2 DERIVED t1_decimal ALL NULL NULL NULL NULL 9 Using where; Using temporary; Using filesort explain format=json select * from v_decimal as v,t2_decimal as t where (v.a=v.b) and (v.b=t.b) and ((t.b>1) or (v.a=1)); @@ -1862,7 +1862,7 @@ EXPLAIN "key_length": "6", "used_key_parts": ["a", "b"], "ref": ["test.t.b", "test.t.b"], - "rows": 2, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -2157,7 +2157,7 @@ explain select * from v_double as v,t2_double as t where (v.b=v.c) and (v.c=t.c) and ((t.c>10) or (v.a=1)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t ALL NULL NULL NULL NULL 9 Using where -1 PRIMARY <derived2> ref key0 key0 18 test.t.c,test.t.c 2 Using where +1 PRIMARY <derived2> ref key0 key0 18 test.t.c,test.t.c 1 Using where 2 DERIVED t1_double ALL NULL NULL NULL NULL 9 Using where; Using temporary; Using filesort explain format=json select * from v_double as v,t2_double as t where (v.b=v.c) and (v.c=t.c) and ((t.c>10) or (v.a=1)); @@ -2180,7 +2180,7 @@ EXPLAIN "key_length": "18", "used_key_parts": ["b", "c"], "ref": ["test.t.c", "test.t.c"], - "rows": 2, + "rows": 1, "filtered": 100, "attached_condition": "t.c > 10 or v.a = 1", "materialized": { @@ -2380,7 +2380,7 @@ where t1.a>5 group by a,b having max_c < 707) v1, t2 where (v1.a=t2.a) and (v1.max_c>300) and (v1.a=v1.b); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where -1 PRIMARY <derived2> ref key0 key0 10 test.t2.a,test.t2.a 2 Using where +1 PRIMARY <derived2> ref key0 key0 10 test.t2.a,test.t2.a 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 20 Using where; Using temporary; Using filesort explain format=json select * from (select a, b, max(c) as max_c, avg(c) as avg_c from t1 @@ -2405,7 +2405,7 @@ EXPLAIN "key_length": "10", "used_key_parts": ["a", "b"], "ref": ["test.t2.a", "test.t2.a"], - "rows": 2, + "rows": 1, "filtered": 100, "attached_condition": "v1.max_c > 300", "materialized": { @@ -2506,7 +2506,7 @@ a b max_c avg_c a b c d explain select * from v1,t2 where (v1.a=t2.a) and (v1.b=t2.b); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where -1 PRIMARY <derived2> ref key0 key0 10 test.t2.a,test.t2.b 2 +1 PRIMARY <derived2> ref key0 key0 10 test.t2.a,test.t2.b 1 2 DERIVED t1 ALL NULL NULL NULL NULL 20 Using temporary; Using filesort explain format=json select * from v1,t2 where (v1.a=t2.a) and (v1.b=t2.b); EXPLAIN @@ -2528,7 +2528,7 @@ EXPLAIN "key_length": "10", "used_key_parts": ["a", "b"], "ref": ["test.t2.a", "test.t2.b"], - "rows": 2, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -3204,7 +3204,7 @@ explain select * from v1,v2,t2 where (v1.a=t2.a) and (v1.a=v1.b) and (v1.a=v2.a) and (v2.max_c<300); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where -1 PRIMARY <derived2> ref key1 key1 10 test.t2.a,test.t2.a 2 +1 PRIMARY <derived2> ref key1 key1 10 test.t2.a,test.t2.a 1 1 PRIMARY <derived3> ref key0 key0 5 test.t2.a 2 Using where 3 DERIVED t1 ALL NULL NULL NULL NULL 20 Using where; Using temporary; Using filesort 2 DERIVED t1 ALL NULL NULL NULL NULL 20 Using where; Using temporary; Using filesort @@ -3229,7 +3229,7 @@ EXPLAIN "key_length": "10", "used_key_parts": ["a", "b"], "ref": ["test.t2.a", "test.t2.a"], - "rows": 2, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -7207,7 +7207,7 @@ SELECT d FROM v4 WHERE s > a id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1) -3 DEPENDENT SUBQUERY <derived5> index_subquery key0 key0 5 func 2 Using where +3 DEPENDENT SUBQUERY <derived5> index_subquery key0 key0 5 func 1 Using where 5 DERIVED t4 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort explain format=json SELECT * FROM t1 WHERE a IN ( SELECT b FROM v2 WHERE b < a OR b IN ( @@ -7245,7 +7245,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["d"], "ref": ["func"], - "rows": 2, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -8514,7 +8514,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["b"], "ref": ["test.t1.a"], - "rows": 2, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -8564,7 +8564,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["b"], "ref": ["test.t1.a"], - "rows": 2, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -8848,9 +8848,21 @@ EXPLAIN "query_block": { "select_id": 1, "table": { - "table_name": "<derived2>", + "table_name": "t1", "access_type": "ALL", - "rows": 3, + "rows": 4, + "filtered": 100, + "attached_condition": "t1.id2 is not null" + }, + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "5", + "used_key_parts": ["id2"], + "ref": ["test.t1.id2"], + "rows": 1, "filtered": 100, "attached_condition": "vc.ct > 0", "materialized": { @@ -8870,18 +8882,6 @@ EXPLAIN } } } - }, - "block-nl-join": { - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 4, - "filtered": 100 - }, - "buffer_type": "flat", - "buffer_size": "256Kb", - "join_type": "BNL", - "attached_condition": "t1.id2 = vc.id2" } } } @@ -14986,7 +14986,7 @@ on t1.a=t.a where t1.b < 3; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 range idx_b idx_b 5 NULL 4 100.00 Using index condition; Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 2 100.00 +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 1 100.00 2 LATERAL DERIVED t2 ref idx_a idx_a 5 test.t1.a 2 100.00 Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`t`.`s` AS `s`,`t`.`m` AS `m` from `test`.`t1` join (/* select#2 */ select `test`.`t2`.`a` AS `a`,sum(`test`.`t2`.`b`) AS `s`,min(`test`.`t2`.`c`) AS `m` from `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`a` group by `test`.`t2`.`a`) `t` where `t`.`a` = `test`.`t1`.`a` and `test`.`t1`.`b` < 3 @@ -15019,7 +15019,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t1.a"], - "rows": 2, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -15096,7 +15096,7 @@ on t1.a=t.a where t1.b <= 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL idx_b NULL NULL NULL 12 75.00 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 9 100.00 +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 1 100.00 2 DERIVED t2 ALL idx_a NULL NULL NULL 90 100.00 Using temporary; Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`t`.`s` AS `s`,`t`.`m` AS `m` from `test`.`t1` join (/* select#2 */ select `test`.`t2`.`a` AS `a`,sum(`test`.`t2`.`b`) AS `s`,min(`test`.`t2`.`b`) AS `m` from `test`.`t2` group by `test`.`t2`.`a`) `t` where `t`.`a` = `test`.`t1`.`a` and `test`.`t1`.`b` <= 5 @@ -15125,7 +15125,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t1.a"], - "rows": 9, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -15200,7 +15200,7 @@ from t1 left join on t1.a=t.a; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 -1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 2 100.00 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 1 100.00 Using where 2 LATERAL DERIVED t2 ref idx_a idx_a 5 test.t1.a 2 100.00 Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`t`.`max` AS `max`,`t`.`min` AS `min` from `test`.`t1` left join (/* select#2 */ select `test`.`t2`.`a` AS `a`,max(`test`.`t2`.`b`) AS `max`,min(`test`.`t2`.`b`) AS `min` from `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`a` group by `test`.`t2`.`a`) `t` on(`t`.`a` = `test`.`t1`.`a` and `test`.`t1`.`a` is not null) where 1 @@ -15227,7 +15227,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t1.a"], - "rows": 2, + "rows": 1, "filtered": 100, "attached_condition": "trigcond(trigcond(t1.a is not null))", "materialized": { @@ -15289,7 +15289,7 @@ on t3.a=t.a and t3.c=t.c where t3.b > 15; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 range idx_b idx_b 5 NULL 3 100.00 Using index condition; Using where -1 PRIMARY <derived2> ref key0 key0 133 test.t3.a,test.t3.c 2 100.00 +1 PRIMARY <derived2> ref key0 key0 133 test.t3.a,test.t3.c 1 100.00 2 LATERAL DERIVED t4 ref idx idx 133 test.t3.a,test.t3.c 1 100.00 Warnings: Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`c` AS `c`,`t`.`max` AS `max`,`t`.`min` AS `min` from `test`.`t3` join (/* select#2 */ select `test`.`t4`.`a` AS `a`,`test`.`t4`.`c` AS `c`,max(`test`.`t4`.`b`) AS `max`,min(`test`.`t4`.`b`) AS `min` from `test`.`t4` where `test`.`t4`.`a` = `test`.`t3`.`a` and `test`.`t4`.`c` = `test`.`t3`.`c` group by `test`.`t4`.`a`,`test`.`t4`.`c`) `t` where `t`.`a` = `test`.`t3`.`a` and `t`.`c` = `test`.`t3`.`c` and `test`.`t3`.`b` > 15 @@ -15322,7 +15322,7 @@ EXPLAIN "key_length": "133", "used_key_parts": ["a", "c"], "ref": ["test.t3.a", "test.t3.c"], - "rows": 2, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -15367,7 +15367,7 @@ on t3.a=t.a and t3.c=t.c where t3.b <= 15; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL idx_b NULL NULL NULL 12 75.00 Using where -1 PRIMARY <derived2> ref key0 key0 133 test.t3.a,test.t3.c 4 100.00 +1 PRIMARY <derived2> ref key0 key0 133 test.t3.a,test.t3.c 1 100.00 2 DERIVED t4 ALL idx NULL NULL NULL 40 100.00 Using temporary; Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`c` AS `c`,`t`.`max` AS `max`,`t`.`min` AS `min` from `test`.`t3` join (/* select#2 */ select `test`.`t4`.`a` AS `a`,`test`.`t4`.`c` AS `c`,max(`test`.`t4`.`b`) AS `max`,min(`test`.`t4`.`b`) AS `min` from `test`.`t4` group by `test`.`t4`.`a`,`test`.`t4`.`c`) `t` where `t`.`a` = `test`.`t3`.`a` and `t`.`c` = `test`.`t3`.`c` and `test`.`t3`.`b` <= 15 @@ -15396,7 +15396,7 @@ EXPLAIN "key_length": "133", "used_key_parts": ["a", "c"], "ref": ["test.t3.a", "test.t3.c"], - "rows": 4, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -15441,7 +15441,7 @@ on t3.a=t.a and t3.c=t.c where t3.b > 15; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 range idx_b idx_b 5 NULL 3 100.00 Using index condition; Using where -1 PRIMARY <derived2> ref key0 key0 133 test.t3.a,test.t3.c 2 100.00 +1 PRIMARY <derived2> ref key0 key0 133 test.t3.a,test.t3.c 1 100.00 2 LATERAL DERIVED t4 ref idx idx 133 test.t3.a,test.t3.c 1 100.00 Warnings: Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`c` AS `c`,`t`.`max` AS `max`,`t`.`min` AS `min` from `test`.`t3` join (/* select#2 */ select `test`.`t4`.`a` AS `a`,`test`.`t4`.`c` AS `c`,max(`test`.`t4`.`b`) AS `max`,min(`test`.`t4`.`b`) AS `min` from `test`.`t4` where `test`.`t4`.`a` = `test`.`t3`.`a` and `test`.`t4`.`c` = `test`.`t3`.`c` group by `test`.`t4`.`c`,`test`.`t4`.`a`) `t` where `t`.`a` = `test`.`t3`.`a` and `t`.`c` = `test`.`t3`.`c` and `test`.`t3`.`b` > 15 @@ -15474,7 +15474,7 @@ EXPLAIN "key_length": "133", "used_key_parts": ["a", "c"], "ref": ["test.t3.a", "test.t3.c"], - "rows": 2, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -15519,7 +15519,7 @@ on t3.a=t.a and t3.c=t.c where t3.b <= 15; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL idx_b NULL NULL NULL 12 75.00 Using where -1 PRIMARY <derived2> ref key0 key0 133 test.t3.a,test.t3.c 4 100.00 +1 PRIMARY <derived2> ref key0 key0 133 test.t3.a,test.t3.c 1 100.00 2 DERIVED t4 ALL idx NULL NULL NULL 40 100.00 Using temporary; Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`c` AS `c`,`t`.`max` AS `max`,`t`.`min` AS `min` from `test`.`t3` join (/* select#2 */ select `test`.`t4`.`a` AS `a`,`test`.`t4`.`c` AS `c`,max(`test`.`t4`.`b`) AS `max`,min(`test`.`t4`.`b`) AS `min` from `test`.`t4` group by `test`.`t4`.`c`,`test`.`t4`.`a`) `t` where `t`.`a` = `test`.`t3`.`a` and `t`.`c` = `test`.`t3`.`c` and `test`.`t3`.`b` <= 15 @@ -15548,7 +15548,7 @@ EXPLAIN "key_length": "133", "used_key_parts": ["a", "c"], "ref": ["test.t3.a", "test.t3.c"], - "rows": 4, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -15603,7 +15603,7 @@ where t2.b between 80 and 85 and t2.c in ('y','z') and t2.a=t3.a and t3.c=t.c; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 range idx idx 133 NULL 2 100.00 Using index condition; Using where 1 PRIMARY t3 ref idx_a idx_a 5 test.t2.a 2 100.00 Using where -1 PRIMARY <derived2> ref key0 key0 128 test.t3.c 2 100.00 +1 PRIMARY <derived2> ref key0 key0 128 test.t3.c 1 100.00 2 LATERAL DERIVED t4 ref idx_c idx_c 128 test.t3.c 3 100.00 Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`t`.`c` AS `t_c`,`t`.`max` AS `max`,`t`.`min` AS `min` from `test`.`t2` join `test`.`t3` join (/* select#2 */ select `test`.`t4`.`c` AS `c`,max(`test`.`t4`.`b`) AS `max`,min(`test`.`t4`.`b`) AS `min` from `test`.`t4` where `test`.`t4`.`c` = `test`.`t3`.`c` group by `test`.`t4`.`c`) `t` where `test`.`t3`.`a` = `test`.`t2`.`a` and `t`.`c` = `test`.`t3`.`c` and `test`.`t2`.`b` between 80 and 85 and `test`.`t2`.`c` in ('y','z') @@ -15646,7 +15646,7 @@ EXPLAIN "key_length": "128", "used_key_parts": ["c"], "ref": ["test.t3.c"], - "rows": 2, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -15750,7 +15750,7 @@ where t2.b < 40 and t2.a=t3.a and t3.c=t.c; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 90 100.00 Using where 1 PRIMARY t3 ref idx_a idx_a 5 test.t2.a 2 100.00 Using where -1 PRIMARY <derived2> ref key0 key0 128 test.t3.c 10 100.00 +1 PRIMARY <derived2> ref key0 key0 128 test.t3.c 1 100.00 2 DERIVED t4 ALL idx_c NULL NULL NULL 160 100.00 Using temporary; Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`t`.`c` AS `t_c`,`t`.`max` AS `max`,`t`.`min` AS `min` from `test`.`t2` join `test`.`t3` join (/* select#2 */ select `test`.`t4`.`c` AS `c`,max(`test`.`t4`.`b`) AS `max`,min(`test`.`t4`.`b`) AS `min` from `test`.`t4` group by `test`.`t4`.`c`) `t` where `test`.`t3`.`a` = `test`.`t2`.`a` and `t`.`c` = `test`.`t3`.`c` and `test`.`t2`.`b` < 40 @@ -15788,7 +15788,7 @@ EXPLAIN "key_length": "128", "used_key_parts": ["c"], "ref": ["test.t3.c"], - "rows": 10, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -16344,7 +16344,7 @@ a c explain extended SELECT * FROM t4 WHERE c IN ( SELECT c FROM v1 ) and a < 2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t4 range a a 5 NULL 1 100.00 Using index condition; Using where -1 PRIMARY <derived3> ref key0 key0 128 test.t4.c 2 100.00 FirstMatch(t4) +1 PRIMARY <derived3> ref key0 key0 128 test.t4.c 1 100.00 FirstMatch(t4) 3 LATERAL DERIVED t3 ref c c 128 test.t4.c 2 100.00 3 LATERAL DERIVED <subquery4> eq_ref distinct_key distinct_key 4 func 1 100.00 4 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 100.00 @@ -16376,7 +16376,7 @@ EXPLAIN "key_length": "128", "used_key_parts": ["c"], "ref": ["test.t4.c"], - "rows": 2, + "rows": 1, "filtered": 100, "first_match": "t4", "materialized": { @@ -16498,7 +16498,7 @@ pk1 f pk2 cnt EXPLAIN EXTENDED SELECT * FROM t1 INNER JOIN v2 ON pk1 = pk2 WHERE f <> 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 2 100.00 Using where -1 PRIMARY <derived2> ref key0 key0 4 test.t1.pk1 2 100.00 +1 PRIMARY <derived2> ref key0 key0 4 test.t1.pk1 1 100.00 2 LATERAL DERIVED t2 eq_ref PRIMARY PRIMARY 4 test.t1.pk1 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`f` AS `f`,`v2`.`pk2` AS `pk2`,`v2`.`cnt` AS `cnt` from `test`.`t1` join `test`.`v2` where `v2`.`pk2` = `test`.`t1`.`pk1` and `test`.`t1`.`f` <> 5 @@ -16523,7 +16523,7 @@ EXPLAIN "key_length": "4", "used_key_parts": ["pk2"], "ref": ["test.t1.pk1"], - "rows": 2, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -16737,7 +16737,7 @@ left join on u.id=auditlastlogin.userid; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY u ALL NULL NULL NULL NULL 2 -1 PRIMARY <derived2> ref key0 key0 5 test.u.id 2 +1 PRIMARY <derived2> ref key0 key0 5 test.u.id 1 2 DERIVED au ALL NULL NULL NULL NULL 4 Using temporary; Using filesort select * from t1 as u left join diff --git a/mysql-test/main/derived_opt.result b/mysql-test/main/derived_opt.result index 48ac7e62653..40e034e5c61 100644 --- a/mysql-test/main/derived_opt.result +++ b/mysql-test/main/derived_opt.result @@ -535,7 +535,7 @@ ON t2.id=t.id WHERE t2.id < 3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 3 Using index condition -1 PRIMARY <derived2> ref key0 key0 5 test.t2.id 2 +1 PRIMARY <derived2> ref key0 key0 5 test.t2.id 1 2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using temporary; Using filesort set join_cache_level=default; set optimizer_switch= @save_optimizer_switch; diff --git a/mysql-test/main/derived_split_innodb.result b/mysql-test/main/derived_split_innodb.result index b9ed016429b..6625bd7df0d 100644 --- a/mysql-test/main/derived_split_innodb.result +++ b/mysql-test/main/derived_split_innodb.result @@ -16,7 +16,7 @@ EXPLAIN SELECT t1.n1 FROM t1, (SELECT n1, n2 FROM t1 WHERE c1 = 'a' GROUP BY n1) WHERE t.n1 = t1.n1 AND t.n2 = t1.n2 AND c1 = 'a' GROUP BY n1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index c1,n1_c1_n2 n1_c1_n2 9 NULL 2 Using where; Using index -1 PRIMARY <derived2> ref key0 key0 8 test.t1.n1,test.t1.n2 2 +1 PRIMARY <derived2> ref key0 key0 8 test.t1.n1,test.t1.n2 1 2 LATERAL DERIVED t1 ref c1,n1_c1_n2 n1_c1_n2 4 test.t1.n1 1 Using where; Using index SELECT t1.n1 FROM t1, (SELECT n1, n2 FROM t1 WHERE c1 = 'a' GROUP BY n1) as t WHERE t.n1 = t1.n1 AND t.n2 = t1.n2 AND c1 = 'a' GROUP BY n1; @@ -95,7 +95,7 @@ ON t2.id=t.id WHERE t2.id < 3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t2.id 2 +1 PRIMARY <derived2> ref key0 key0 5 test.t2.id 1 2 LATERAL DERIVED t1 eq_ref PRIMARY PRIMARY 4 test.t2.id 1 set join_cache_level=default; DROP TABLE t1,t2; diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result index 30831e75341..242f8514bf4 100644 --- a/mysql-test/main/derived_view.result +++ b/mysql-test/main/derived_view.result @@ -213,7 +213,7 @@ explain extended select * from t1 join (select * from t2 group by f2) tt on f1=f2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 11 100.00 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t1.f1 2 100.00 +1 PRIMARY <derived2> ref key0 key0 5 test.t1.f1 1 100.00 2 DERIVED t2 ALL NULL NULL NULL NULL 11 100.00 Using temporary; Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11`,`tt`.`f2` AS `f2`,`tt`.`f22` AS `f22` from `test`.`t1` join (/* select#2 */ select `test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f22` AS `f22` from `test`.`t2` group by `test`.`t2`.`f2`) `tt` where `tt`.`f2` = `test`.`t1`.`f1` @@ -227,7 +227,7 @@ flush status; explain select * from t1 join (select * from t2 group by f2) tt on f1=f2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 11 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t1.f1 2 +1 PRIMARY <derived2> ref key0 key0 5 test.t1.f1 1 2 DERIVED t2 ALL NULL NULL NULL NULL 11 Using temporary; Using filesort show status like 'Handler_read%'; Variable_name Value @@ -287,7 +287,7 @@ explain showing created indexes explain extended select * from t1 join v2 on f1=f2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 11 100.00 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t1.f1 2 100.00 +1 PRIMARY <derived2> ref key0 key0 5 test.t1.f1 1 100.00 2 DERIVED t2 ALL NULL NULL NULL NULL 11 100.00 Using temporary; Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11`,`v2`.`f2` AS `f2`,`v2`.`f22` AS `f22` from `test`.`t1` join `test`.`v2` where `v2`.`f2` = `test`.`t1`.`f1` @@ -338,7 +338,7 @@ flush status; explain select * from t1 join v2 on f1=f2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 11 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t1.f1 2 +1 PRIMARY <derived2> ref key0 key0 5 test.t1.f1 1 2 DERIVED t2 ALL NULL NULL NULL NULL 11 Using temporary; Using filesort show status like 'Handler_read%'; Variable_name Value @@ -371,7 +371,7 @@ Handler_read_rnd_next 36 explain extended select * from v1 join v4 on f1=f2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 11 100.00 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t2.f2 2 100.00 +1 PRIMARY <derived2> ref key0 key0 5 test.t2.f2 1 100.00 2 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 Using where; Using temporary; Using filesort Warnings: Note 1003 /* select#1 */ select `v1`.`f1` AS `f1`,`v1`.`f11` AS `f11`,`test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f22` AS `f22` from `test`.`v1` join `test`.`t2` where `v1`.`f1` = `test`.`t2`.`f2` and `test`.`t2`.`f2` in (2,3) @@ -395,7 +395,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["f1"], "ref": ["test.t2.f2"], - "rows": 2, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -529,7 +529,7 @@ join on x.f1 = z.f1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived3> ALL NULL NULL NULL NULL 11 100.00 Using where -1 PRIMARY <derived5> ref key0 key0 5 tt.f1 2 100.00 +1 PRIMARY <derived5> ref key0 key0 5 tt.f1 1 100.00 5 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 Using where; Using temporary; Using filesort 3 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 Using where; Using temporary; Using filesort Warnings: @@ -575,7 +575,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["f1"], "ref": ["tt.f1"], - "rows": 2, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -652,7 +652,7 @@ join on x.f1 = z.f1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 11 100.00 Using where -1 PRIMARY <derived4> ref key0 key0 5 x.f1 2 100.00 +1 PRIMARY <derived4> ref key0 key0 5 x.f1 1 100.00 4 DERIVED <derived5> ALL NULL NULL NULL NULL 11 100.00 Using where; Using temporary; Using filesort 5 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 Using where; Using temporary; Using filesort 2 DERIVED <derived3> ALL NULL NULL NULL NULL 11 100.00 Using where; Using temporary; Using filesort @@ -719,7 +719,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["f1"], "ref": ["x.f1"], - "rows": 2, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -863,7 +863,7 @@ join of above two explain extended select * from v6 join v7 on f2=f1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 11 100.00 Using where -1 PRIMARY <derived5> ref key0 key0 5 test.t2.f2 2 100.00 +1 PRIMARY <derived5> ref key0 key0 5 test.t2.f2 1 100.00 5 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 Using where; Using temporary; Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f22` AS `f22`,`v1`.`f1` AS `f1`,`v1`.`f11` AS `f11` from `test`.`t2` join `test`.`v1` where `v1`.`f1` = `test`.`t2`.`f2` and `test`.`t2`.`f2` < 7 and `test`.`t2`.`f2` in (2,3) @@ -887,7 +887,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["f1"], "ref": ["test.t2.f2"], - "rows": 2, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -917,7 +917,7 @@ test two keys explain select * from t1 join (select * from t2 group by f2) tt on t1.f1=tt.f2 join t1 xx on tt.f22=xx.f1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 11 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t1.f1 2 +1 PRIMARY <derived2> ref key0 key0 5 test.t1.f1 1 1 PRIMARY xx ALL NULL NULL NULL NULL 11 Using where; Using join buffer (flat, BNL join) 2 DERIVED t2 ALL NULL NULL NULL NULL 11 Using temporary; Using filesort select * from t1 join (select * from t2 group by f2) tt on t1.f1=tt.f2 join t1 xx on tt.f22=xx.f1; @@ -1106,7 +1106,7 @@ SELECT * FROM t1, t2, v1 WHERE t2.a=t1.a AND t2.a=v1.a AND t2.a=v1.b; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ref a a 4 const 1 Using index -1 PRIMARY <derived2> ref key0 key0 8 const,const 1 +1 PRIMARY <derived2> ref key1 key1 8 func,func 1 2 DERIVED t3 ALL NULL NULL NULL NULL 12 Using temporary; Using filesort SELECT * FROM t1, t2, v1 WHERE t2.a=t1.a AND t2.a=v1.a AND t2.a=v1.b; a a a b @@ -1139,7 +1139,7 @@ SELECT * FROM t3 WHERE t3.a IN (SELECT v1.a FROM v1, t2 WHERE t2.a = v1.b); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 4 100.00 Using where -2 DEPENDENT SUBQUERY <derived3> ref key1 key1 5 func 2 100.00 +2 DEPENDENT SUBQUERY <derived3> ref key1 key1 5 func 1 100.00 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 3 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using temporary; Using filesort Warnings: @@ -1505,7 +1505,7 @@ EXPLAIN SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where -1 PRIMARY <derived3> ref key0 key0 10 test.t1.a,test.t1.b 2 FirstMatch(t1) +1 PRIMARY <derived3> ref key0 key0 10 test.t1.a,test.t1.b 1 FirstMatch(t1) 3 DERIVED t2 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort SELECT * FROM v2; a b @@ -1932,7 +1932,7 @@ EXPLAIN SELECT v1.a FROM v1,v2 WHERE v2.b = v1.b ORDER BY 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 Using where; Using filesort -1 PRIMARY <derived3> ref key0 key0 4 v1.b 2 +1 PRIMARY <derived3> ref key0 key0 4 v1.b 1 3 DERIVED t2 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort 2 DERIVED t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort DROP VIEW v1,v2; diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result index fde6e0fec6b..23396d22876 100644 --- a/mysql-test/main/join_cache.result +++ b/mysql-test/main/join_cache.result @@ -5197,7 +5197,7 @@ SELECT * FROM (SELECT DISTINCT * FROM t1) t WHERE t.a IN (SELECT t2.a FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where; Start temporary -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 2 End temporary +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 End temporary 2 DERIVED t1 ALL NULL NULL NULL NULL 3 Using temporary SELECT * FROM (SELECT DISTINCT * FROM t1) t WHERE t.a IN (SELECT t2.a FROM t2); @@ -5208,8 +5208,8 @@ EXPLAIN SELECT * FROM (SELECT DISTINCT * FROM t1) t WHERE t.a IN (SELECT t2.a FROM t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 -1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where; Start temporary +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 End temporary 2 DERIVED t1 ALL NULL NULL NULL NULL 3 Using temporary SELECT * FROM (SELECT DISTINCT * FROM t1) t WHERE t.a IN (SELECT t2.a FROM t2); diff --git a/mysql-test/main/selectivity.result b/mysql-test/main/selectivity.result index 00907235ecc..69f4be54414 100644 --- a/mysql-test/main/selectivity.result +++ b/mysql-test/main/selectivity.result @@ -140,7 +140,7 @@ and total_revenue = (select max(total_revenue) from revenue0) order by s_suppkey; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY supplier ALL PRIMARY NULL NULL NULL 10 100.00 Using filesort -1 PRIMARY <derived3> ref key0 key0 5 dbt3_s001.supplier.s_suppkey 10 100.00 Using where +1 PRIMARY <derived3> ref key0 key0 5 dbt3_s001.supplier.s_suppkey 1 100.00 Using where 3 DERIVED lineitem range i_l_shipdate,i_l_suppkey i_l_shipdate 4 NULL 268 100.00 Using where; Using temporary; Using filesort 2 SUBQUERY <derived4> ALL NULL NULL NULL NULL 268 100.00 4 DERIVED lineitem range i_l_shipdate i_l_shipdate 4 NULL 268 100.00 Using where; Using temporary; Using filesort @@ -161,7 +161,7 @@ and total_revenue = (select max(total_revenue) from revenue0) order by s_suppkey; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY supplier ALL PRIMARY NULL NULL NULL 10 100.00 Using filesort -1 PRIMARY <derived3> ref key0 key0 5 dbt3_s001.supplier.s_suppkey 10 100.00 Using where +1 PRIMARY <derived3> ref key0 key0 5 dbt3_s001.supplier.s_suppkey 1 100.00 Using where 3 DERIVED lineitem range i_l_shipdate,i_l_suppkey i_l_shipdate 4 NULL 268 100.00 Using where; Using temporary; Using filesort 2 SUBQUERY <derived4> ALL NULL NULL NULL NULL 268 100.00 4 DERIVED lineitem range i_l_shipdate i_l_shipdate 4 NULL 268 100.00 Using where; Using temporary; Using filesort diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result index 93917065722..be5348a2a15 100644 --- a/mysql-test/main/selectivity_innodb.result +++ b/mysql-test/main/selectivity_innodb.result @@ -143,7 +143,7 @@ and total_revenue = (select max(total_revenue) from revenue0) order by s_suppkey; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY supplier index PRIMARY PRIMARY 4 NULL 10 100.00 -1 PRIMARY <derived3> ref key0 key0 5 dbt3_s001.supplier.s_suppkey 10 100.00 Using where +1 PRIMARY <derived3> ref key0 key0 5 dbt3_s001.supplier.s_suppkey 1 100.00 Using where 3 DERIVED lineitem range i_l_shipdate,i_l_suppkey i_l_shipdate 4 NULL 229 100.00 Using where; Using temporary; Using filesort 2 SUBQUERY <derived4> ALL NULL NULL NULL NULL 229 100.00 4 DERIVED lineitem range i_l_shipdate i_l_shipdate 4 NULL 229 100.00 Using where; Using temporary; Using filesort @@ -164,7 +164,7 @@ and total_revenue = (select max(total_revenue) from revenue0) order by s_suppkey; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY supplier index PRIMARY PRIMARY 4 NULL 10 100.00 -1 PRIMARY <derived3> ref key0 key0 5 dbt3_s001.supplier.s_suppkey 10 100.00 Using where +1 PRIMARY <derived3> ref key0 key0 5 dbt3_s001.supplier.s_suppkey 1 100.00 Using where 3 DERIVED lineitem range i_l_shipdate,i_l_suppkey i_l_shipdate 4 NULL 229 100.00 Using where; Using temporary; Using filesort 2 SUBQUERY <derived4> ALL NULL NULL NULL NULL 228 100.00 4 DERIVED lineitem range i_l_shipdate i_l_shipdate 4 NULL 229 100.00 Using where; Using temporary; Using filesort diff --git a/mysql-test/main/subselect_extra.result b/mysql-test/main/subselect_extra.result index dbcf00268c2..6bcea113759 100644 --- a/mysql-test/main/subselect_extra.result +++ b/mysql-test/main/subselect_extra.result @@ -389,7 +389,7 @@ EXPLAIN SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where -1 PRIMARY <derived3> ref key0 key0 10 test.t1.a,test.t1.b 2 FirstMatch(t1) +1 PRIMARY <derived3> ref key0 key0 10 test.t1.a,test.t1.b 1 FirstMatch(t1) 3 DERIVED t2 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort SELECT * FROM v2; a b diff --git a/mysql-test/main/subselect_extra_no_semijoin.result b/mysql-test/main/subselect_extra_no_semijoin.result index 49a1431eb9b..7e257cb9291 100644 --- a/mysql-test/main/subselect_extra_no_semijoin.result +++ b/mysql-test/main/subselect_extra_no_semijoin.result @@ -391,7 +391,7 @@ EXPLAIN SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY <derived3> index_subquery key0 key0 10 func,func 2 Using where +2 DEPENDENT SUBQUERY <derived3> index_subquery key0 key0 10 func,func 1 Using where 3 DERIVED t2 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort SELECT * FROM v2; a b diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index c52005e7683..aa645ac00ca 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -7617,6 +7617,68 @@ Item *st_select_lex::build_cond_for_grouping_fields(THD *thd, Item *cond, } +/** + Check if any any item in the group by list is also present in the select_list + @retval true: All elements common between select and group by list +*/ + +void st_select_lex::is_group_by_prefix(KEY *keyinfo) +{ + uint key_parts= keyinfo->usable_key_parts; + KEY_PART_INFO *key_part_info= keyinfo->key_part; + bool found= FALSE; + + if (key_parts < group_list.elements) + return; + + uint matched_fields=0, i, j; + Item *item; + + for (i= 0; i < key_parts; key_part_info++, i++) + { + uint fld_idx= key_part_info->fieldnr - 1; + item= join->fields_list.elem(fld_idx); + for (ORDER *order= group_list.first; order; order= order->next) + { + Item *ord_item= order->item[0]->real_item(); + Item_equal *item_equal= ord_item->get_item_equal(); + + if (item_equal) + { + Item_equal_fields_iterator it(*item_equal); + Item *equal_item; + while ((equal_item= it++)) + { + if (equal_item->eq(item, 0)) + { + matched_fields++; + found= TRUE; + break; + } + } + } + else + { + if (item->eq(ord_item, 0)) + { + matched_fields++; + found= TRUE; + } + } + if (found) + break; + } + + if (matched_fields == group_list.elements) + { + for (j=matched_fields - 1; j < key_parts; j++) + keyinfo->rec_per_key[j]= 1; + return; + } + found= FALSE; + } +} + int set_statement_var_if_exists(THD *thd, const char *var_name, size_t var_name_length, ulonglong value) { diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 4eaec7d062b..6f5d289f3be 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -1387,6 +1387,7 @@ class st_select_lex: public st_select_lex_node bool cond_pushdown_is_allowed() const { return !olap && !explicit_limit && !tvc; } + void is_group_by_prefix(KEY *keyinfo); private: bool m_non_agg_field_used; diff --git a/sql/table.cc b/sql/table.cc index c4494c9ae4b..a4d050748ba 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -7287,6 +7287,9 @@ bool TABLE::add_tmp_key(uint key, uint key_parts, derived->check_distinct_in_union()) keyinfo->rec_per_key[key_parts - 1]= 1; } + + if (!first->is_part_of_union() && first->group_list.elements) + first->is_group_by_prefix(keyinfo); } set_if_bigger(s->max_key_length, keyinfo->key_length);