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 62ec0955e88..ce93f70df63 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -2058,6 +2058,24 @@ CA	ML	CA	ML
 CA	ML	RO	ML
 DROP TABLE t1,t2;
 set join_cache_level=@tmp_mdev5056;
+# 
+#  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.5 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 38ab07882de..2ccb108eb38 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -2098,4 +2098,22 @@ CA	ML	CA	ML
 CA	ML	RO	ML
 DROP TABLE t1,t2;
 set join_cache_level=@tmp_mdev5056;
+# 
+#  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.5 tests
diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test
index 58831c4ffb3..4194c0a9e00 100644
--- a/mysql-test/t/subselect_sj_mat.test
+++ b/mysql-test/t/subselect_sj_mat.test
@@ -1750,5 +1750,25 @@ WHERE ( alias2.c2, alias1.c1 ) IN ( SELECT c4, c3 FROM t2 ) AND alias1.c1 IN ( S
 DROP TABLE t1,t2;
 set join_cache_level=@tmp_mdev5056;
 
+--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.5 tests