MDEV-7846: Server crashes in Item_subselect::fix_fields or fails with Thread stack overrun

Substitute into transformed subselects original left expression and than register its change in case it was substituted.

10 files changed, 185 insertions, 10 deletions

diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 0ab2d487e99..cf52ba2e2be 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -7053,3 +7053,31 @@ WHERE h.host in (SELECT host FROM mysql.user)
 ) AS sq
 FROM mysql.host h GROUP BY h.host;
 sq
+#
+# MDEV-7846:Server crashes in Item_subselect::fix
+#_fields or fails with Thread stack overrun
+#
+CREATE TABLE t1 (column1 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (3),(9);
+CREATE TABLE t2 (column2 INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1),(4);
+CREATE TABLE t3 (column3 INT) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (6),(8);
+CREATE TABLE t4 (column4 INT) ENGINE=MyISAM;
+INSERT INTO t4 VALUES (2),(5);
+PREPARE stmt FROM "
+SELECT ( 
+  SELECT MAX( table1.column1 ) AS field1 
+  FROM t1 AS table1
+  WHERE table3.column3 IN ( SELECT table2.column2 AS field2 FROM t2 AS table2 ) 
+) AS sq
+FROM t3 AS table3, t4 AS table4 GROUP BY sq
+";
+EXECUTE stmt;
+sq
+NULL
+EXECUTE stmt;
+sq
+NULL
+deallocate prepare stmt;
+drop table t1,t2,t3,t4;
diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result
index 5780351e811..73a69e98c5b 100644
--- a/mysql-test/r/subselect_no_mat.result
+++ b/mysql-test/r/subselect_no_mat.result
@@ -7050,6 +7050,34 @@ WHERE h.host in (SELECT host FROM mysql.user)
 ) AS sq
 FROM mysql.host h GROUP BY h.host;
 sq
+#
+# MDEV-7846:Server crashes in Item_subselect::fix
+#_fields or fails with Thread stack overrun
+#
+CREATE TABLE t1 (column1 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (3),(9);
+CREATE TABLE t2 (column2 INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1),(4);
+CREATE TABLE t3 (column3 INT) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (6),(8);
+CREATE TABLE t4 (column4 INT) ENGINE=MyISAM;
+INSERT INTO t4 VALUES (2),(5);
+PREPARE stmt FROM "
+SELECT ( 
+  SELECT MAX( table1.column1 ) AS field1 
+  FROM t1 AS table1
+  WHERE table3.column3 IN ( SELECT table2.column2 AS field2 FROM t2 AS table2 ) 
+) AS sq
+FROM t3 AS table3, t4 AS table4 GROUP BY sq
+";
+EXECUTE stmt;
+sq
+NULL
+EXECUTE stmt;
+sq
+NULL
+deallocate prepare stmt;
+drop table t1,t2,t3,t4;
 set optimizer_switch=default;
 select @@optimizer_switch like '%materialization=on%';
 @@optimizer_switch like '%materialization=on%'
diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result
index 4bea029341b..1512e39c52d 100644
--- a/mysql-test/r/subselect_no_opts.result
+++ b/mysql-test/r/subselect_no_opts.result
@@ -7048,4 +7048,32 @@ WHERE h.host in (SELECT host FROM mysql.user)
 ) AS sq
 FROM mysql.host h GROUP BY h.host;
 sq
+#
+# MDEV-7846:Server crashes in Item_subselect::fix
+#_fields or fails with Thread stack overrun
+#
+CREATE TABLE t1 (column1 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (3),(9);
+CREATE TABLE t2 (column2 INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1),(4);
+CREATE TABLE t3 (column3 INT) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (6),(8);
+CREATE TABLE t4 (column4 INT) ENGINE=MyISAM;
+INSERT INTO t4 VALUES (2),(5);
+PREPARE stmt FROM "
+SELECT ( 
+  SELECT MAX( table1.column1 ) AS field1 
+  FROM t1 AS table1
+  WHERE table3.column3 IN ( SELECT table2.column2 AS field2 FROM t2 AS table2 ) 
+) AS sq
+FROM t3 AS table3, t4 AS table4 GROUP BY sq
+";
+EXECUTE stmt;
+sq
+NULL
+EXECUTE stmt;
+sq
+NULL
+deallocate prepare stmt;
+drop table t1,t2,t3,t4;
 set @optimizer_switch_for_subselect_test=null;
diff --git a/mysql-test/r/subselect_no_scache.result b/mysql-test/r/subselect_no_scache.result
index fdb3b129d46..26cea1f9f70 100644
--- a/mysql-test/r/subselect_no_scache.result
+++ b/mysql-test/r/subselect_no_scache.result
@@ -7059,6 +7059,34 @@ WHERE h.host in (SELECT host FROM mysql.user)
 ) AS sq
 FROM mysql.host h GROUP BY h.host;
 sq
+#
+# MDEV-7846:Server crashes in Item_subselect::fix
+#_fields or fails with Thread stack overrun
+#
+CREATE TABLE t1 (column1 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (3),(9);
+CREATE TABLE t2 (column2 INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1),(4);
+CREATE TABLE t3 (column3 INT) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (6),(8);
+CREATE TABLE t4 (column4 INT) ENGINE=MyISAM;
+INSERT INTO t4 VALUES (2),(5);
+PREPARE stmt FROM "
+SELECT ( 
+  SELECT MAX( table1.column1 ) AS field1 
+  FROM t1 AS table1
+  WHERE table3.column3 IN ( SELECT table2.column2 AS field2 FROM t2 AS table2 ) 
+) AS sq
+FROM t3 AS table3, t4 AS table4 GROUP BY sq
+";
+EXECUTE stmt;
+sq
+NULL
+EXECUTE stmt;
+sq
+NULL
+deallocate prepare stmt;
+drop table t1,t2,t3,t4;
 set optimizer_switch=default;
 select @@optimizer_switch like '%subquery_cache=on%';
 @@optimizer_switch like '%subquery_cache=on%'
diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result
index cb6d35d3606..4c6f03780dd 100644
--- a/mysql-test/r/subselect_no_semijoin.result
+++ b/mysql-test/r/subselect_no_semijoin.result
@@ -7048,5 +7048,33 @@ WHERE h.host in (SELECT host FROM mysql.user)
 ) AS sq
 FROM mysql.host h GROUP BY h.host;
 sq
+#
+# MDEV-7846:Server crashes in Item_subselect::fix
+#_fields or fails with Thread stack overrun
+#
+CREATE TABLE t1 (column1 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (3),(9);
+CREATE TABLE t2 (column2 INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1),(4);
+CREATE TABLE t3 (column3 INT) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (6),(8);
+CREATE TABLE t4 (column4 INT) ENGINE=MyISAM;
+INSERT INTO t4 VALUES (2),(5);
+PREPARE stmt FROM "
+SELECT ( 
+  SELECT MAX( table1.column1 ) AS field1 
+  FROM t1 AS table1
+  WHERE table3.column3 IN ( SELECT table2.column2 AS field2 FROM t2 AS table2 ) 
+) AS sq
+FROM t3 AS table3, t4 AS table4 GROUP BY sq
+";
+EXECUTE stmt;
+sq
+NULL
+EXECUTE stmt;
+sq
+NULL
+deallocate prepare stmt;
+drop table t1,t2,t3,t4;
 set @optimizer_switch_for_subselect_test=null;
 set @join_cache_level_for_subselect_test=NULL;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 3eb056d879b..00ab48b2c63 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -5930,3 +5930,37 @@ SELECT
     WHERE h.host in (SELECT host FROM mysql.user)
   ) AS sq
 FROM mysql.host h GROUP BY h.host;
+
+
+--echo #
+--echo # MDEV-7846:Server crashes in Item_subselect::fix
+--echo #_fields or fails with Thread stack overrun
+--echo #
+CREATE TABLE t1 (column1 INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (3),(9);
+
+CREATE TABLE t2 (column2 INT) ENGINE=MyISAM;
+
+INSERT INTO t2 VALUES (1),(4);
+
+CREATE TABLE t3 (column3 INT) ENGINE=MyISAM;
+INSERT INTO t3 VALUES (6),(8);
+
+CREATE TABLE t4 (column4 INT) ENGINE=MyISAM;
+INSERT INTO t4 VALUES (2),(5);
+
+
+PREPARE stmt FROM "
+SELECT ( 
+  SELECT MAX( table1.column1 ) AS field1 
+  FROM t1 AS table1
+  WHERE table3.column3 IN ( SELECT table2.column2 AS field2 FROM t2 AS table2 ) 
+) AS sq
+FROM t3 AS table3, t4 AS table4 GROUP BY sq
+";
+
+EXECUTE stmt;
+EXECUTE stmt;
+
+deallocate prepare stmt;
+drop table t1,t2,t3,t4;
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 51a38f0341c..29fff8e460f 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -1455,12 +1455,12 @@ bool Item_in_optimizer::fix_left(THD *thd, Item **ref)
        next execution we need to copy args[1]->left_expr again.
     */
     ref0= &(((Item_in_subselect *)args[1])->left_expr);
-    args[0]= ref0[0];
   }
-  if ((!args[0]->fixed && args[0]->fix_fields(thd, ref0)) ||
-      (!cache && !(cache= Item_cache::get_cache(ref0[0]))))
+  if ((!(*ref0)->fixed && (*ref0)->fix_fields(thd, ref0)) ||
+      (!cache && !(cache= Item_cache::get_cache(*ref0))))
     DBUG_RETURN(1);
-  args[0]= ref0[0];
+  if (args[0] != (*ref0))
+    current_thd->change_item_tree(args, (*ref0));
   DBUG_PRINT("info", ("actual fix fields"));
 
   cache->setup(args[0]);
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 4c11808c3f7..e04f5ff7518 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -1360,7 +1360,7 @@ Item_in_subselect::Item_in_subselect(Item * left_exp,
   upper_item(0)
 {
   DBUG_ENTER("Item_in_subselect::Item_in_subselect");
-  left_expr= left_exp;
+  left_expr_orig= left_expr= left_exp;
   func= &eq_creator;
   init(select_lex, new select_exists_subselect(this));
   max_columns= UINT_MAX;
@@ -1384,7 +1384,7 @@ Item_allany_subselect::Item_allany_subselect(Item * left_exp,
   :Item_in_subselect(), func_creator(fc), all(all_arg)
 {
   DBUG_ENTER("Item_allany_subselect::Item_allany_subselect");
-  left_expr= left_exp;
+  left_expr_orig= left_expr= left_exp;
   func= func_creator(all_arg);
   init(select_lex, new select_exists_subselect(this));
   max_columns= 1;
@@ -2584,15 +2584,13 @@ Item_in_subselect::select_in_like_transformer(JOIN *join)
   arena= thd->activate_stmt_arena_if_needed(&backup);
   if (!optimizer)
   {
-    result= (!(optimizer= new Item_in_optimizer(left_expr, this)));
+    result= (!(optimizer= new Item_in_optimizer(left_expr_orig, this)));
     if (result)
       goto out;
   }
 
   thd->lex->current_select= current->return_after_parsing();
   result= optimizer->fix_left(thd, optimizer->arguments());
-  /* fix_fields can change reference to left_expr, we need reassign it */
-  left_expr= optimizer->arguments()[0];
   thd->lex->current_select= current;
 
   if (changed)
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index 592e7711a10..930bd665e3a 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -449,6 +449,7 @@ protected:
                                     Item **having_item);
 public:
   Item *left_expr;
+  Item *left_expr_orig;
   /* Priority of this predicate in the convert-to-semi-join-nest process. */
   int sj_convert_priority;
   /*
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index 5b1a7f2f22a..1363be002b9 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -1593,7 +1593,9 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred)
   {
     nested_join->sj_outer_expr_list.push_back(subq_pred->left_expr);
     Item_func_eq *item_eq=
-      new Item_func_eq(subq_pred->left_expr, subq_lex->ref_pointer_array[0]);
+      new Item_func_eq(subq_pred->left_expr_orig, subq_lex->ref_pointer_array[0]);
+    if (subq_pred->left_expr_orig != subq_pred->left_expr)
+      thd->change_item_tree(item_eq->arguments(), subq_pred->left_expr);
     item_eq->in_equality_no= 0;
     sj_nest->sj_on_expr= and_items(sj_nest->sj_on_expr, item_eq);
   }