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

19 Aug 2018

revision-id: dbc7c3562d523e194734c206c0f0e1cb78164cf0 (mariadb-10.3.7-138-gdbc7c35)
parent(s): 34c7222c088ded8f1192142db935c000b2ba6b8d
author: Igor Babaev
committer: Igor Babaev
timestamp: 2018-08-18 22:57:20 -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 optimizer incorrectly estimated the number of rows in the derived
table. This happened because the optimizer did not take into account the
number of rows in the constructor. The wrong estimate 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] dbc7c35: MDEV-17017 Explain for query using derived table specified with a table

IgorBabaev