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.

6 files changed, 174 insertions, 0 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;