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>

6 files changed, 231 insertions, 31 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
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 9766a28757e..70d795c145d 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -2464,9 +2464,32 @@ void st_select_lex_node::add_slave(st_select_lex_node *slave_arg)
   {
     slave= slave_arg;
     slave_arg->master= this;
+    slave->prev= &master->slave;
+    slave->next= 0;
   }
 }
 
+/*
+  @brief
+    Substitute this node in select tree for a newly creates node
+
+  @param  subst the node to substitute for
+
+  @details
+    The function substitute this node in the select tree for a newly
+    created node subst. This node is just removed from the tree but all
+    its link fields and the attached sub-tree remain untouched.
+*/
+
+void st_select_lex_node::substitute_in_tree(st_select_lex_node *subst)
+{
+  if ((subst->next= next))
+    next->prev= &subst->next;
+  subst->prev= prev;
+  (*prev)= subst;
+  subst->master= master;
+}
+
 
 /*
   include on level down (but do not link)
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 77b4e15aaf0..979e212c1f6 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -711,6 +711,7 @@ public:
   void include_global(st_select_lex_node **plink);
   void exclude();
   void exclude_from_tree();
+  void substitute_in_tree(st_select_lex_node *subst);
 
   void set_slave(st_select_lex_node *slave_arg) { slave= slave_arg; }
   void move_node(st_select_lex_node *where_to_move)
diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc
index 0a5f6687e17..0a771b592e4 100644
--- a/sql/sql_tvc.cc
+++ b/sql/sql_tvc.cc
@@ -654,44 +654,61 @@ st_select_lex *wrap_tvc(THD *thd, st_select_lex *tvc_sl,
 
   Query_arena backup;
   Query_arena *arena= thd->activate_stmt_arena_if_needed(&backup);
+
+  Item *item;
+  SELECT_LEX *wrapper_sl;
+  SELECT_LEX_UNIT *derived_unit;
+
   /*
-    Create SELECT_LEX of the select used in the result of transformation
+    Create SELECT_LEX wrapper_sl of the select used in the result
+    of the transformation
   */
-  lex->current_select= tvc_sl;
-  if (mysql_new_select(lex, 0, NULL))
+  if (!(wrapper_sl= new (thd->mem_root) SELECT_LEX()))
     goto err;
-  mysql_init_select(lex);
-  /* Create item list as '*' for the subquery SQ */
-  Item *item;
-  SELECT_LEX *wrapper_sl;
-  wrapper_sl= lex->current_select;
+  wrapper_sl->select_number= ++thd->lex->stmt_lex->current_select_number;
+  wrapper_sl->parent_lex= lex; /* Used in init_query. */
+  wrapper_sl->init_query();
+  wrapper_sl->init_select();
+
+  wrapper_sl->nest_level= tvc_sl->nest_level;
+  wrapper_sl->parsing_place= tvc_sl->parsing_place;
   wrapper_sl->linkage= tvc_sl->linkage;
-  wrapper_sl->parsing_place= SELECT_LIST;
+
+  lex->current_select= wrapper_sl;
   item= new (thd->mem_root) Item_field(thd, &wrapper_sl->context,
                                        NULL, NULL, &star_clex_str);
   if (item == NULL || add_item_to_list(thd, item))
     goto err;
   (wrapper_sl->with_wild)++;
-  
-  /* Exclude SELECT with TVC */
-  tvc_sl->exclude();
+
+  /* Include the newly created select into the global list of selects */
+  wrapper_sl->include_global((st_select_lex_node**)&lex->all_selects_list);
+
+  /* Substitute select node used of TVC for the newly created select */
+  tvc_sl->substitute_in_tree(wrapper_sl);
+
   /*
-    Create derived table DT that will wrap TVC in the result of transformation
+    Create a unit for the substituted select used for TVC and attach it
+    to the the wrapper select wrapper_sl as the only unit. The created
+    unit is the unit for the derived table tvc_x of the transformation.
   */
-  SELECT_LEX *tvc_select; // select for tvc
-  SELECT_LEX_UNIT *derived_unit; // unit for tvc_select
-  if (mysql_new_select(lex, 1, tvc_sl))
+  if (!(derived_unit= new (thd->mem_root) SELECT_LEX_UNIT()))
     goto err;
-  tvc_select= lex->current_select;
-  derived_unit= tvc_select->master_unit();
-  tvc_select->linkage= DERIVED_TABLE_TYPE;
+  derived_unit->init_query();
+  derived_unit->thd= thd;
+  derived_unit->include_down(wrapper_sl);
 
-  lex->current_select= wrapper_sl;
+  /*
+    Attach the select used of TVC as the only slave to the unit for
+    the derived table tvc_x of the transformation
+  */
+  derived_unit->add_slave(tvc_sl);
+  tvc_sl->linkage= DERIVED_TABLE_TYPE;
 
   /*
-    Create the name of the wrapping derived table and
-    add it to the FROM list of the wrapper
-   */
+    Generate the name of the derived table created for TVC and
+    add it to the FROM list of the wrapping select
+  */
   Table_ident *ti;
   LEX_CSTRING alias;
   TABLE_LIST *derived_tab;
@@ -710,10 +727,6 @@ st_select_lex *wrap_tvc(THD *thd, st_select_lex *tvc_sl,
   wrapper_sl->table_list.first->derived_type= DTYPE_TABLE | DTYPE_MATERIALIZE;
   lex->derived_tables|= DERIVED_SUBQUERY;
 
-  wrapper_sl->where= 0;
-  wrapper_sl->set_braces(false);
-  derived_unit->set_with_clause(0);
-
   if (arena)
     thd->restore_active_arena(arena, &backup);
   thd->lex->result= save_result;