4 files changed, 83 insertions, 5 deletions
diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result
index 00c87e83657..f0f317a52da 100644
--- a/mysql-test/r/subselect_mat.result
+++ b/mysql-test/r/subselect_mat.result
@@ -1656,7 +1656,6 @@ a
 0
 0
 DROP TABLE t2,t3,t4,t5;
-set optimizer_switch=@subselect_sj_mat_tmp;
 #
 # BUG#860300: Second crash with get_fanout_with_deps() with semijoin + materialization
 #
@@ -1703,6 +1702,31 @@ f3
 7
 DROP TABLE t1, t2, t3, t4;
 set optimizer_switch=@tmp_860535;
+#
+# BUG#860553: Crash in create_ref_for_key with semijoin + materialization 
+#
+CREATE TABLE t1 (f1 int) ;
+CREATE TABLE t2 (f5 varchar(52) NOT NULL) ;
+CREATE TABLE t3 (f1 varchar(3), f4 varchar(52) , KEY (f4), PRIMARY KEY (f1));
+CREATE TABLE t4 (f3 int, KEY (f3));
+INSERT INTO t4 VALUES (17),(20);
+CREATE TABLE t5 (f2 int);
+INSERT INTO t5 VALUES (0),(0);
+SELECT *
+FROM t1
+JOIN t2
+ON ( t2.f5 ) IN (
+SELECT t3.f4
+FROM t3
+WHERE ( 1 ) IN (
+SELECT t4.f3
+FROM t4 , t5
+)
+);
+f1	f5
+DROP TABLE t1, t2, t3, t4, t5;
+# This must be at the end:
+set optimizer_switch=@subselect_sj_mat_tmp;
 set @subselect_mat_test_optimizer_switch_value=null;
 set @@optimizer_switch='materialization=on,in_to_exists=off,semijoin=off';
 set optimizer_switch='mrr=on,mrr_sort_keys=on,index_condition_pushdown=on';
diff --git a/mysql-test/r/subselect_sj2_mat.result b/mysql-test/r/subselect_sj2_mat.result
index b5e15946037..da5796296e7 100644
--- a/mysql-test/r/subselect_sj2_mat.result
+++ b/mysql-test/r/subselect_sj2_mat.result
@@ -593,7 +593,7 @@ select * from t1 left join t2 on (t2.a= t1.a and t2.a in (select pk from t3));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	3	
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	3	Using where
-2	DEPENDENT SUBQUERY	t3	unique_subquery	PRIMARY	PRIMARY	4	func	1	Using index
+2	SUBQUERY	t3	index	PRIMARY	PRIMARY	4	NULL	10	Using index
 drop table t0, t1, t2, t3;
 create table t1 (a int);
 insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
diff --git a/mysql-test/r/subselect_sj_mat.result b/mysql-test/r/subselect_sj_mat.result
index 2c22d6a4b5d..a6c7ced3e00 100644
--- a/mysql-test/r/subselect_sj_mat.result
+++ b/mysql-test/r/subselect_sj_mat.result
@@ -1696,7 +1696,6 @@ a
 0
 0
 DROP TABLE t2,t3,t4,t5;
-set optimizer_switch=@subselect_sj_mat_tmp;
 #
 # BUG#860300: Second crash with get_fanout_with_deps() with semijoin + materialization
 #
@@ -1743,3 +1742,28 @@ f3
 7
 DROP TABLE t1, t2, t3, t4;
 set optimizer_switch=@tmp_860535;
+#
+# BUG#860553: Crash in create_ref_for_key with semijoin + materialization 
+#
+CREATE TABLE t1 (f1 int) ;
+CREATE TABLE t2 (f5 varchar(52) NOT NULL) ;
+CREATE TABLE t3 (f1 varchar(3), f4 varchar(52) , KEY (f4), PRIMARY KEY (f1));
+CREATE TABLE t4 (f3 int, KEY (f3));
+INSERT INTO t4 VALUES (17),(20);
+CREATE TABLE t5 (f2 int);
+INSERT INTO t5 VALUES (0),(0);
+SELECT *
+FROM t1
+JOIN t2
+ON ( t2.f5 ) IN (
+SELECT t3.f4
+FROM t3
+WHERE ( 1 ) IN (
+SELECT t4.f3
+FROM t4 , t5
+)
+);
+f1	f5
+DROP TABLE t1, t2, t3, t4, t5;
+# This must be at the end:
+set optimizer_switch=@subselect_sj_mat_tmp;
diff --git a/mysql-test/t/subselect_sj_mat.test b/mysql-test/t/subselect_sj_mat.test
index 2ba075a047d..ef4a40172e2 100644
--- a/mysql-test/t/subselect_sj_mat.test
+++ b/mysql-test/t/subselect_sj_mat.test
@@ -1349,8 +1349,6 @@ WHERE t2.a = ALL (
 
 DROP TABLE t2,t3,t4,t5;
 
-set optimizer_switch=@subselect_sj_mat_tmp;
-
 --echo #
 --echo # BUG#860300: Second crash with get_fanout_with_deps() with semijoin + materialization
 --echo #
@@ -1399,3 +1397,35 @@ SELECT * FROM t4;
 DROP TABLE t1, t2, t3, t4;
 set optimizer_switch=@tmp_860535;
 
+--echo #
+--echo # BUG#860553: Crash in create_ref_for_key with semijoin + materialization 
+--echo #
+CREATE TABLE t1 (f1 int) ;
+CREATE TABLE t2 (f5 varchar(52) NOT NULL) ;
+
+CREATE TABLE t3 (f1 varchar(3), f4 varchar(52) , KEY (f4), PRIMARY KEY (f1));
+
+CREATE TABLE t4 (f3 int, KEY (f3));
+INSERT INTO t4 VALUES (17),(20);
+
+CREATE TABLE t5 (f2 int);
+INSERT INTO t5 VALUES (0),(0);
+
+SELECT *
+FROM t1
+JOIN t2
+ON ( t2.f5 ) IN (
+        SELECT t3.f4
+        FROM t3
+        WHERE ( 1 ) IN (
+                SELECT t4.f3
+                FROM t4 , t5
+        )
+);
+
+DROP TABLE t1, t2, t3, t4, t5;
+
+
+--echo # This must be at the end:
+set optimizer_switch=@subselect_sj_mat_tmp;
+