MDEV-5368: Server crashes in Item_in_subselect::optimize on ...

- convert_subq_to_sj() must connect child select's tables into 
  parent select's TABLE_LIST::next_local chain.  
- The problem was that it took child's leaf_tables.head() which
  is different. This could cause certain tables (in this bug's case,
  child select's non-merged semi-join) not to be present in 
  TABLE_LIST::next_local chain.  Which would cause non-merged semi-join
  not to be initialized in setup_tables(), which would lead to 
  NULL pointer dereference.

3 files changed, 56 insertions, 0 deletions

diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index 66b94af5d44..c47881d5434 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -2023,6 +2023,24 @@ INSERT INTO t1 VALUES (1,3,5),(2,4,6);
 SELECT * FROM t1 WHERE 8 IN (SELECT MIN(pk) FROM t1) AND (pk = a OR pk = b);
 pk	a	b
 DROP TABLE t1;
+# 
+#  MDEV-5368: Server crashes in Item_in_subselect::optimize on 2nd 
+#    execution of PS with IN subqueries, materialization+semijoin
+# 
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(3);
+CREATE TABLE t2 (b INT) ENGINE=MyISAM;
+CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2;
+INSERT INTO t2 VALUES (8),(9);
+PREPARE stmt FROM "
+SELECT * FROM t1 WHERE 1 IN ( SELECT b FROM v2 WHERE 2 IN ( SELECT MAX(a) FROM t1 ) )
+";
+EXECUTE stmt;
+a
+EXECUTE stmt;
+a
+DROP TABLE t1, t2;
+DROP VIEW v2;
 # End of 5.3 tests
 set @subselect_mat_test_optimizer_switch_value=null;
 set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off';
diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
index 61ebe3cf5c3..f267f32c887 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -2063,4 +2063,22 @@ INSERT INTO t1 VALUES (1,3,5),(2,4,6);
 SELECT * FROM t1 WHERE 8 IN (SELECT MIN(pk) FROM t1) AND (pk = a OR pk = b);
 pk	a	b
 DROP TABLE t1;
+# 
+#  MDEV-5368: Server crashes in Item_in_subselect::optimize on 2nd 
+#    execution of PS with IN subqueries, materialization+semijoin
+# 
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(3);
+CREATE TABLE t2 (b INT) ENGINE=MyISAM;
+CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2;
+INSERT INTO t2 VALUES (8),(9);
+PREPARE stmt FROM "
+SELECT * FROM t1 WHERE 1 IN ( SELECT b FROM v2 WHERE 2 IN ( SELECT MAX(a) FROM t1 ) )
+";
+EXECUTE stmt;
+a
+EXECUTE stmt;
+a
+DROP TABLE t1, t2;
+DROP VIEW v2;
 # End of 5.3 tests
diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test
index 840e5436a56..bf5360dd4a7 100644
--- a/mysql-test/t/subselect_sj_mat.test
+++ b/mysql-test/t/subselect_sj_mat.test
@@ -1706,4 +1706,24 @@ INSERT INTO t1 VALUES (1,3,5),(2,4,6);
 SELECT * FROM t1 WHERE 8 IN (SELECT MIN(pk) FROM t1) AND (pk = a OR pk = b);
 DROP TABLE t1;
 
+--echo # 
+--echo #  MDEV-5368: Server crashes in Item_in_subselect::optimize on 2nd 
+--echo #    execution of PS with IN subqueries, materialization+semijoin
+--echo # 
+CREATE TABLE t1 (a INT) ENGINE=MyISAM;
+INSERT INTO t1 VALUES (1),(3);
+
+CREATE TABLE t2 (b INT) ENGINE=MyISAM;
+CREATE ALGORITHM=MERGE VIEW v2 AS SELECT * FROM t2;
+INSERT INTO t2 VALUES (8),(9);
+
+PREPARE stmt FROM "
+SELECT * FROM t1 WHERE 1 IN ( SELECT b FROM v2 WHERE 2 IN ( SELECT MAX(a) FROM t1 ) )
+";
+
+EXECUTE stmt;
+EXECUTE stmt;
+DROP TABLE t1, t2;
+DROP VIEW v2;
+
 --echo # End of 5.3 tests