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, 29 insertions, 0 deletions

diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test
index eb5ea59f829..eb30f00fa91 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;