MDEV-24936 EXPLAIN for query based on table value constructor lacks info

           on used subqueries

If a query was based on a table value constructor that contained subqueries
then EXPLAIN for such query did not contain any lines explaining the
execution plans of the subqueries.
This happened because
- no optimize() method was called for any subquery used by the table value
  constructor when EXPLAIN command for the query was processed;
- EXPLAIN node created for the table value constructor itself did not
  assume that some child nodes could be attached to it.

Approved by Oleksandr Byelkin <sanja@mariadb.com>

2 files changed, 58 insertions, 0 deletions

diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result
index d7b32865db5..e112aca78af 100644
--- a/mysql-test/main/table_value_constr.result
+++ b/mysql-test/main/table_value_constr.result
@@ -2742,4 +2742,37 @@ NULL	UNION RESULT	<union4,5>	ALL	NULL	NULL	NULL	NULL	NULL	NULL
 Warnings:
 Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <nop>(<in_optimizer>(`test`.`t1`.`a`,<min>(/* select#4 */ select `tvc_0`.`7` from (values (7)) `tvc_0` union /* select#5 */ select `tvc_0`.`8` from (values (8)) `tvc_0`) <= <cache>(`test`.`t1`.`a`)))
 drop table t1;
+#
+# MDEV-24934:EXPLAIN for queries based on TVC using subqueries
+#
+create table t1 (a int);
+insert into t1 values (3), (7), (1);
+values (8), ((select * from t1 where a between 2 and 4));
+8
+8
+3
+explain values (8), ((select * from t1 where a between 2 and 4));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+values  ((select * from t1 where a between 2 and 4)),
+((select * from t1 where a > 10));
+(select * from t1 where a between 2 and 4)
+3
+NULL
+explain values  ((select * from t1 where a between 2 and 4)),
+((select * from t1 where a > 10));
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+values (10,11), ((select * from t1 where a = 7) + 1, 21);
+10	11
+10	11
+8	21
+explain values (10,11), ((select * from t1 where a = 7) + 1, 21);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+2	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+drop table t1;
 End of 10.3 tests
diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test
index bac85ff75cc..0a78fd9b386 100644
--- a/mysql-test/main/table_value_constr.test
+++ b/mysql-test/main/table_value_constr.test
@@ -1434,4 +1434,29 @@ eval explain extended $q;
 
 drop table t1;
 
+--echo #
+--echo # MDEV-24934:EXPLAIN for queries based on TVC using subqueries
+--echo #
+
+create table t1 (a int);
+insert into t1 values (3), (7), (1);
+
+let $q1=
+values (8), ((select * from t1 where a between 2 and 4));
+eval $q1;
+eval explain $q1;
+
+let $q2=
+values  ((select * from t1 where a between 2 and 4)),
+         ((select * from t1 where a > 10));
+eval $q2;
+eval explain $q2;
+
+let $q3=
+values (10,11), ((select * from t1 where a = 7) + 1, 21);
+eval $q3;
+eval explain $q3;
+
+drop table t1;
+
 --echo End of 10.3 tests