[Commits] f264498: MDEV-17017 Explain for query using derived table specified with a table
revision-id: f26449872153d4b80ad0d99ee2f79a4eca701fd7 (mariadb-10.3.7-138-gf264498) parent(s): 34c7222c088ded8f1192142db935c000b2ba6b8d author: Igor Babaev committer: Igor Babaev timestamp: 2018-08-18 22:40:58 -0700 message: MDEV-17017 Explain for query using derived table specified with a table value constructor shows wrong number of rows If the specification of a derived table contained a table value constructor then the cardinality of the constructor was not passed to the derived table and as a result the optimizer incorrectly estimated the number of rows in the derived table. This could lead to choosing inefficient execution plans. --- mysql-test/main/opt_tvc.result | 30 ++++++++-------- mysql-test/main/opt_tvc.test | 2 +- mysql-test/main/range.result | 6 ++-- mysql-test/main/range.test | 4 +++ mysql-test/main/range_mrr_icp.result | 6 ++-- mysql-test/main/table_value_constr.result | 57 +++++++++++++++++++++++++++++++ mysql-test/main/table_value_constr.test | 29 ++++++++++++++++ sql/sql_tvc.h | 2 ++ sql/sql_union.cc | 2 ++ 9 files changed, 115 insertions(+), 23 deletions(-) diff --git a/mysql-test/main/opt_tvc.result b/mysql-test/main/opt_tvc.result index 0ecae5b..fdbd932 100644 --- a/mysql-test/main/opt_tvc.result +++ b/mysql-test/main/opt_tvc.result @@ -486,34 +486,32 @@ a b deallocate prepare stmt; # use inside out access from tvc rows set @@in_predicate_conversion_threshold= default; -select * from t3 where a in (1,4,10); +select * from t3 where a in (1,4); a b 1 abc 1 todd 1 sm 4 yq -10 abc -explain extended select * from t3 where a in (1,4,10); +explain extended select * from t3 where a in (1,4); id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t3 range idx idx 5 NULL 5 100.00 Using index condition +1 SIMPLE t3 range idx idx 5 NULL 4 100.00 Using index condition Warnings: -Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where `test`.`t3`.`a` in (1,4,10) +Note 1003 select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` where `test`.`t3`.`a` in (1,4) set @@in_predicate_conversion_threshold= 2; -select * from t3 where a in (1,4,10); +select * from t3 where a in (1,4); a b 1 abc 1 todd 1 sm 4 yq -10 abc -explain extended select * from t3 where a in (1,4,10); +explain extended select * from t3 where a in (1,4); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 1 PRIMARY t3 ref idx idx 5 tvc_0.1 3 100.00 2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: -Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` semi join ((values (1),(4),(10)) `tvc_0`) where `test`.`t3`.`a` = `tvc_0`.`1` +Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`b` AS `b` from `test`.`t3` semi join ((values (1),(4)) `tvc_0`) where `test`.`t3`.`a` = `tvc_0`.`1` # use vectors in IN predeicate set @@in_predicate_conversion_threshold= 4; select * from t1 where (a,b) in ((1,2),(3,4)); @@ -540,9 +538,9 @@ explain extended select * from t2 where (a,b) in ((1,2),(8,9)) and (a,c) in ((1,3),(8,0),(5,1)); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 100.00 +1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 3 100.00 1 PRIMARY t2 ALL NULL NULL NULL NULL 6 100.00 Using where; Using join buffer (flat, BNL join) -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` semi join ((values (1,3),(8,0),(5,1)) `tvc_0`) where `test`.`t2`.`a` = `tvc_0`.`1` and `test`.`t2`.`c` = `tvc_0`.`3` and (`tvc_0`.`1`,`test`.`t2`.`b`) in (<cache>((1,2)),<cache>((8,9))) @@ -570,7 +568,7 @@ explain extended select * from t1 where (a,b) not in ((1,2),(8,9), (5,1)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`1` and `test`.`t1`.`b` = `<subquery2>`.`2`)))) @@ -578,7 +576,7 @@ explain extended select * from t1 where (a,b) not in (select * from (values (1,2),(8,9), (5,1)) as tvc_0); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`1` and `test`.`t1`.`b` = `<subquery2>`.`2`)))) @@ -592,7 +590,7 @@ explain extended select * from t1 where b < 7 and (a,b) not in ((1,2),(8,9), (5,1)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 6 100.00 Using where -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`b` < 7 and !<expr_cache><`test`.`t1`.`a`,`test`.`t1`.`b`>(<in_optimizer>((`test`.`t1`.`a`,`test`.`t1`.`b`),(`test`.`t1`.`a`,`test`.`t1`.`b`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t1`.`a` in <temporary table> on distinct_key where `test`.`t1`.`a` = `<subquery2>`.`1` and `test`.`t1`.`b` = `<subquery2>`.`2`)))) @@ -608,7 +606,7 @@ explain extended select * from t2 where (a,c) not in ((1,2),(8,9), (5,1)); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 6 100.00 Using where -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 100.00 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 3 100.00 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t2` where !<expr_cache><`test`.`t2`.`a`,`test`.`t2`.`c`>(<in_optimizer>((`test`.`t2`.`a`,`test`.`t2`.`c`),(`test`.`t2`.`a`,`test`.`t2`.`c`) in ( <materialize> (/* select#2 */ select `tvc_0`.`1`,`tvc_0`.`2` from (values (1,2),(8,9),(5,1)) `tvc_0` ), <primary_index_lookup>(`test`.`t2`.`a` in <temporary table> on distinct_key where `test`.`t2`.`a` = `<subquery2>`.`1` and `test`.`t2`.`c` = `<subquery2>`.`2`)))) @@ -632,7 +630,7 @@ i EXPLAIN EXTENDED SELECT * FROM t1 WHERE i IN (NULL, NULL, NULL, NULL, NULL); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 -1 PRIMARY <derived3> ALL NULL NULL NULL NULL 2 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) +1 PRIMARY <derived3> ALL NULL NULL NULL NULL 5 100.00 Using where; FirstMatch(t1); Using join buffer (flat, BNL join) 3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`i` AS `i` from `test`.`t1` semi join ((values (NULL),(NULL),(NULL),(NULL),(NULL)) `tvc_0`) where `test`.`t1`.`i` = `tvc_0`.`NULL` diff --git a/mysql-test/main/opt_tvc.test b/mysql-test/main/opt_tvc.test index d5c9a5c..2d06a0a 100644 --- a/mysql-test/main/opt_tvc.test +++ b/mysql-test/main/opt_tvc.test @@ -255,7 +255,7 @@ deallocate prepare stmt; --echo # use inside out access from tvc rows -let $query= select * from t3 where a in (1,4,10); +let $query= select * from t3 where a in (1,4); set @@in_predicate_conversion_threshold= default; eval $query; eval explain extended $query; diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result index e2996b9..cbf9d5b 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -1052,6 +1052,7 @@ create table t1 (a int); insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t2 (a int, key(a)); insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C; +set in_predicate_conversion_threshold= 2000; set @a="select * from t2 force index (a) where a NOT IN(0"; select count(*) from (select @a:=concat(@a, ',', a) from t2 ) Z; count(*) @@ -1062,15 +1063,14 @@ set @b= concat("explain ", @a); prepare stmt1 from @b; execute stmt1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index NULL a 5 NULL 1003 Using where; Using index -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 -3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +1 SIMPLE t2 index a a 5 NULL 1003 Using where; Using index prepare stmt1 from @a; execute stmt1; a 11 13 15 +set in_predicate_conversion_threshold= default; drop table t1, t2; CREATE TABLE t1 ( id int NOT NULL DEFAULT '0', diff --git a/mysql-test/main/range.test b/mysql-test/main/range.test index 36e0e32..43b5b18 100644 --- a/mysql-test/main/range.test +++ b/mysql-test/main/range.test @@ -870,6 +870,8 @@ insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t2 (a int, key(a)); insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C; +set in_predicate_conversion_threshold= 2000; + set @a="select * from t2 force index (a) where a NOT IN(0"; select count(*) from (select @a:=concat(@a, ',', a) from t2 ) Z; set @a=concat(@a, ')'); @@ -884,6 +886,8 @@ execute stmt1; prepare stmt1 from @a; execute stmt1; +set in_predicate_conversion_threshold= default; + drop table t1, t2; # diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result index 629d183..483957f 100644 --- a/mysql-test/main/range_mrr_icp.result +++ b/mysql-test/main/range_mrr_icp.result @@ -1054,6 +1054,7 @@ create table t1 (a int); insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); create table t2 (a int, key(a)); insert into t2 select 2*(A.a + 10*(B.a + 10*C.a)) from t1 A, t1 B, t1 C; +set in_predicate_conversion_threshold= 2000; set @a="select * from t2 force index (a) where a NOT IN(0"; select count(*) from (select @a:=concat(@a, ',', a) from t2 ) Z; count(*) @@ -1064,15 +1065,14 @@ set @b= concat("explain ", @a); prepare stmt1 from @b; execute stmt1; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 index NULL a 5 NULL 1003 Using where; Using index -2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 2 -3 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +1 SIMPLE t2 index a a 5 NULL 1003 Using where; Using index prepare stmt1 from @a; execute stmt1; a 11 13 15 +set in_predicate_conversion_threshold= default; drop table t1, t2; CREATE TABLE t1 ( id int NOT NULL DEFAULT '0', diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index 9e0a096..b0b0fa8 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -2097,3 +2097,60 @@ v # with t as (values (),()) select 1 from t; ERROR HY000: Row with no elements is not allowed in table value constructor in this context +# +# MDEV-17017: TVC in derived table +# +create table t1 (a int); +insert into t1 values (9), (3), (2); +select * from (values (7), (5), (8), (1), (3), (8), (1)) t; +7 +7 +5 +8 +1 +3 +8 +1 +explain select * from (values (7), (5), (8), (1), (3), (8), (1)) t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 7 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +select * from (values (1,11), (7,77), (3,31), (4,42)) t; +1 11 +1 11 +7 77 +3 31 +4 42 +explain select * from (values (1,11), (7,77), (3,31), (4,42)) t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 4 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +select * from (values (7), (5), (8), (1) union values (3), (8), (1)) t; +7 +7 +5 +8 +1 +3 +explain select * from (values (7), (5), (8), (1) union values (3), (8), (1)) t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 7 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +select * from (values (7), (5), (8), (1) union select * from t1) t; +7 +7 +5 +8 +1 +9 +3 +2 +explain select * from (values (7), (5), (8), (1) union select * from t1) t; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 7 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION t1 ALL NULL NULL NULL NULL 3 +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL +drop table t1; diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test index eb5ea59..eb30f00 100644 --- a/mysql-test/main/table_value_constr.test +++ b/mysql-test/main/table_value_constr.test @@ -1075,3 +1075,32 @@ DELIMITER ;| --error ER_EMPTY_ROW_IN_TVC with t as (values (),()) select 1 from t; + +--echo # +--echo # MDEV-17017: TVC in derived table +--echo # + +create table t1 (a int); +insert into t1 values (9), (3), (2); + +let $q1= +select * from (values (7), (5), (8), (1), (3), (8), (1)) t; +eval $q1; +eval explain $q1; + +let $q2= +select * from (values (1,11), (7,77), (3,31), (4,42)) t; +eval $q2; +eval explain $q2; + +let $q3= +select * from (values (7), (5), (8), (1) union values (3), (8), (1)) t; +eval $q3; +eval explain $q3; + +let $q4= +select * from (values (7), (5), (8), (1) union select * from t1) t; +eval $q4; +eval explain $q4; + +drop table t1; diff --git a/sql/sql_tvc.h b/sql/sql_tvc.h index 420311c..128cc88 100644 --- a/sql/sql_tvc.h +++ b/sql/sql_tvc.h @@ -50,6 +50,8 @@ class table_value_constr : public Sql_alloc have_query_plan(QEP_NOT_PRESENT_YET), explain(0), select_options(select_options_arg) { }; + + ha_rows get_records() { return lists_of_values.elements; } bool prepare(THD *thd_arg, SELECT_LEX *sl, select_result *tmp_result, diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 1203532..c8bf9bd 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -1321,6 +1321,8 @@ bool st_select_lex_unit::optimize() thd->lex->current_select= lex_select_save; DBUG_RETURN(TRUE); } + if (derived) + sl->increase_derived_records(sl->tvc->get_records()); continue; } thd->lex->current_select= sl;
participants (1)
-
IgorBabaev