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.

1 files changed, 57 insertions, 0 deletions

diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result
index 9e0a0968932..b0b0fa81664 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;