[Commits] f264498: MDEV-17017 Explain for query using derived table specified with a table

19 Aug 2018

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;

    

[Commits] f264498: MDEV-17017 Explain for query using derived table specified with a table

IgorBabaev