MDEV-24910 Crash with SELECT that uses table value constructor as a subselect

This bug caused crashes of the server when processing queries with table
value constructors (TVC) that contained subqueries and were used itself as
subselects. For such TVCs the following transformation is applied at the
prepare stage:
  VALUES (v1), ... (vn) => SELECT * FROM (VALUES (v1), ... (vn)) tvc_x.
This transformation allows to reduce the problem of evaluation of TVCs used
as subselects to the problem of evaluation of regular subselects.
The transformation is implemented in the wrap_tvc(). The code the function
to mimic the behaviour of the parser when processing the result of the
transformation. However this imitation was not free of some flaws. First
the function called the method exclude() that completely destroyed the
select tree structures below the transformed TVC. Second the function
used the procedure mysql_new_select to create st_select_lex nodes for
both wrapping select of the transformation and TVC. This also led to
constructing of invalid select tree structures.
The patch actually re-engineers the code of wrap_tvc().

Approved by Oleksandr Byelkin <sanja@mariadb.com>

3 files changed, 167 insertions, 4 deletions

diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result
index e112aca78af..603f21a772d 100644
--- a/mysql-test/main/table_value_constr.result
+++ b/mysql-test/main/table_value_constr.result
@@ -748,7 +748,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 3	MATERIALIZED	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	
 2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where 1
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1)) `tvc_0`) where 1
 explain extended select * from t1
 where a in (select * from (values (1)) as tvc_0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
@@ -983,7 +983,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 3	MATERIALIZED	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	
 2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1
+Note	1003	/* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` semi join ((values (1),(2)) `tvc_0`) where 1
 explain extended select * from t1 
 where a = any (select * from (values (1),(2)) as tvc_0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
@@ -2775,4 +2775,110 @@ 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;
+#
+# MDEV-24910: TVC containing subquery used as a subselect
+#
+create table t1 (a int) engine=myisam;
+insert into t1 values (3), (7), (1);
+create table t2 (b int) engine=myisam;
+insert into t2 values (1), (2);
+select (values ((select 2))) from t2;
+(values ((select 2)))
+2
+2
+explain select (values ((select 2))) from t2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
+4	SUBQUERY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+Warnings:
+Note	1249	Select 3 was reduced during optimization
+prepare stmt from "select (values ((select 2))) from t2";
+execute stmt;
+(values ((select 2)))
+2
+2
+execute stmt;
+(values ((select 2)))
+2
+2
+deallocate prepare stmt;
+select (values ((select * from t1 where a > 10))) from t2;
+(values ((select * from t1 where a > 10)))
+NULL
+NULL
+explain select (values ((select * from t1 where a > 10))) from t2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	
+4	SUBQUERY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	3	Using where
+prepare stmt from "select (values ((select * from t1 where a > 10))) from t2";
+execute stmt;
+(values ((select * from t1 where a > 10)))
+NULL
+NULL
+execute stmt;
+(values ((select * from t1 where a > 10)))
+NULL
+NULL
+deallocate prepare stmt;
+create table t3 (a int);
+insert into t3 values
+(3), (7), (7), (1), (3), (9), (7), (9), (8), (7), (8);
+create view v1 as select count(a) as c from t3 group by a;
+select
+(values ((select * from t3 where a in (select * from v1))));
+(values ((select * from t3 where a in (select * from v1))))
+1
+explain select
+(values ((select * from t3 where a in (select * from v1))));
+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
+6	SUBQUERY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	11	
+3	SUBQUERY	<subquery4>	eq_ref	distinct_key	distinct_key	8	func	1	Using where
+4	MATERIALIZED	<derived5>	ALL	NULL	NULL	NULL	NULL	11	
+5	DERIVED	t3	ALL	NULL	NULL	NULL	NULL	11	Using temporary; Using filesort
+prepare stmt from "select
+(values ((select * from t3 where a in (select * from v1))))";
+execute stmt;
+(values ((select * from t3 where a in (select * from v1))))
+1
+execute stmt;
+(values ((select * from t3 where a in (select * from v1))))
+1
+deallocate prepare stmt;
+select
+(values ((select * from t3
+where a > 10 and a in (select * from v1))));
+(values ((select * from t3
+where a > 10 and a in (select * from v1))))
+NULL
+explain select
+(values ((select * from t3
+where a > 10 and a in (select * from v1))));
+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
+6	SUBQUERY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
+2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	11	Using where
+3	SUBQUERY	<subquery4>	eq_ref	distinct_key	distinct_key	8	func	1	Using where
+4	MATERIALIZED	<derived5>	ALL	NULL	NULL	NULL	NULL	11	
+5	DERIVED	t3	ALL	NULL	NULL	NULL	NULL	11	Using temporary; Using filesort
+prepare stmt from "select
+(values ((select * from t3
+where a > 10 and a in (select * from v1))))";
+execute stmt;
+(values ((select * from t3
+where a > 10 and a in (select * from v1))))
+NULL
+execute stmt;
+(values ((select * from t3
+where a > 10 and a in (select * from v1))))
+NULL
+deallocate prepare stmt;
+drop view v1;
+drop table t1,t2,t3;
 End of 10.3 tests
diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test
index 0a78fd9b386..2246a19d306 100644
--- a/mysql-test/main/table_value_constr.test
+++ b/mysql-test/main/table_value_constr.test
@@ -1459,4 +1459,61 @@ eval explain $q3;
 
 drop table t1;
 
+--echo #
+--echo # MDEV-24910: TVC containing subquery used as a subselect
+--echo #
+
+create table t1 (a int) engine=myisam;
+insert into t1 values (3), (7), (1);
+create table t2 (b int) engine=myisam;
+insert into t2 values (1), (2);
+
+let $q1=
+select (values ((select 2))) from t2;
+eval $q1;
+eval explain $q1;
+eval prepare stmt from "$q1";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+let $q2=
+select (values ((select * from t1 where a > 10))) from t2;
+eval $q2;
+eval explain $q2;
+eval prepare stmt from "$q2";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+create table t3 (a int);
+insert into t3 values
+ (3), (7), (7), (1), (3), (9), (7), (9), (8), (7), (8);
+
+create view v1 as select count(a) as c from t3 group by a;
+
+let $q3=
+select
+(values ((select * from t3 where a in (select * from v1))));
+eval $q3;
+eval explain $q3;
+eval prepare stmt from "$q3";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+let $q4=
+select
+(values ((select * from t3
+          where a > 10 and a in (select * from v1))));
+eval $q4;
+eval explain $q4;
+eval prepare stmt from "$q4";
+execute stmt;
+execute stmt;
+deallocate prepare stmt;
+
+drop view v1;
+drop table t1,t2,t3;
+
 --echo End of 10.3 tests
diff --git a/mysql-test/suite/compat/oracle/r/table_value_constr.result b/mysql-test/suite/compat/oracle/r/table_value_constr.result
index d4f8e28fe07..539329554d5 100644
--- a/mysql-test/suite/compat/oracle/r/table_value_constr.result
+++ b/mysql-test/suite/compat/oracle/r/table_value_constr.result
@@ -746,7 +746,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 3	MATERIALIZED	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	
 2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1)) "tvc_0") where 1
+Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1)) "tvc_0") where 1
 explain extended select * from t1
 where a in (select * from (values (1)) as tvc_0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
@@ -981,7 +981,7 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 3	MATERIALIZED	<derived2>	ALL	NULL	NULL	NULL	NULL	2	100.00	
 2	DERIVED	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
-Note	1003	select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1),(2)) "tvc_0") where 1
+Note	1003	/* select#1 */ select "test"."t1"."a" AS "a","test"."t1"."b" AS "b" from "test"."t1" semi join ((values (1),(2)) "tvc_0") where 1
 explain extended select * from t1 
 where a = any (select * from (values (1),(2)) as tvc_0);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra