BUG#860553: Crash in create_ref_for_key with semijoin + materialization

- The problem was that JOIN::save/restore_query_plan() did not save/restore parts of 
  the query plan that are located inside SJ_MATERIALIZATION_INFO structures. This could
  cause parts of one plan to be used with another, which led get_best_combination() to
  constructing non-sensical join plans (and crash).
  Fixed by saving/restoring SJM parts of the query plans.

- check_and_do_in_subquery_rewrites() will not set SUBS_MATERIALIZATION flag when it 
  records that the subquery predicate is to be converted into semi-join. 
  If convert_join_subqueries_to_semijoins() later decides not to convert to semi-join,
  let it set SUBS_MATERIALIZATION flag, if appropriate.

1 files changed, 25 insertions, 1 deletions

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;