Merge fix for BUG#611704

4 files changed, 98 insertions, 1 deletions

diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result
index edcdf08e3bb..a9ea5fc0e0b 100644
--- a/mysql-test/r/subselect_sj.result
+++ b/mysql-test/r/subselect_sj.result
@@ -1562,4 +1562,34 @@ f1
 1
 1
 DROP TABLE t1,t2,t3;
+#
+# BUG#611704: Crash in replace_where_subcondition with nested subquery and semijoin=on
+# 
+CREATE TABLE t1 ( f1 int) ;
+CREATE TABLE t2 ( f1 int) ;
+CREATE TABLE t3 ( f1 int) ;
+SELECT * FROM (
+SELECT t3.*
+FROM t2 STRAIGHT_JOIN t3
+ON t3.f1
+AND (t3.f1 ) IN (
+SELECT t1.f1
+FROM t1
+)
+) AS alias1;
+f1
+DROP TABLE t1,t2,t3;
+# BUG#611704: another testcase:
+CREATE TABLE t1 ( f1 int(11), f3 varchar(1), f4 varchar(1)) ;
+CREATE TABLE t2 ( f2 int(11), KEY (f2));
+CREATE TABLE t3 ( f4 varchar(1)) ;
+PREPARE st1 FROM '
+SELECT *
+FROM t1
+STRAIGHT_JOIN ( t2 STRAIGHT_JOIN t3 ON t2.f2 )
+ON (t1.f3) IN ( SELECT f4 FROM t1 )
+';
+EXECUTE st1;
+f1	f3	f4	f2	f4
+DROP TABLE t1,t2,t3;
 set optimizer_switch=@subselect_sj_tmp;
diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result
index fb9c330496a..6f4f730b07f 100644
--- a/mysql-test/r/subselect_sj_jcl6.result
+++ b/mysql-test/r/subselect_sj_jcl6.result
@@ -1571,6 +1571,36 @@ f1
 1
 1
 DROP TABLE t1,t2,t3;
+#
+# BUG#611704: Crash in replace_where_subcondition with nested subquery and semijoin=on
+# 
+CREATE TABLE t1 ( f1 int) ;
+CREATE TABLE t2 ( f1 int) ;
+CREATE TABLE t3 ( f1 int) ;
+SELECT * FROM (
+SELECT t3.*
+FROM t2 STRAIGHT_JOIN t3
+ON t3.f1
+AND (t3.f1 ) IN (
+SELECT t1.f1
+FROM t1
+)
+) AS alias1;
+f1
+DROP TABLE t1,t2,t3;
+# BUG#611704: another testcase:
+CREATE TABLE t1 ( f1 int(11), f3 varchar(1), f4 varchar(1)) ;
+CREATE TABLE t2 ( f2 int(11), KEY (f2));
+CREATE TABLE t3 ( f4 varchar(1)) ;
+PREPARE st1 FROM '
+SELECT *
+FROM t1
+STRAIGHT_JOIN ( t2 STRAIGHT_JOIN t3 ON t2.f2 )
+ON (t1.f3) IN ( SELECT f4 FROM t1 )
+';
+EXECUTE st1;
+f1	f3	f4	f2	f4
+DROP TABLE t1,t2,t3;
 set optimizer_switch=@subselect_sj_tmp;
 #
 # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off
diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test
index c389a4dda97..e454d3e49b9 100644
--- a/mysql-test/t/subselect_sj.test
+++ b/mysql-test/t/subselect_sj.test
@@ -1424,5 +1424,40 @@ WHERE t1.f1 IN (
 
 DROP TABLE t1,t2,t3;
 
+
+--echo #
+--echo # BUG#611704: Crash in replace_where_subcondition with nested subquery and semijoin=on
+--echo # 
+
+CREATE TABLE t1 ( f1 int) ;
+CREATE TABLE t2 ( f1 int) ;
+CREATE TABLE t3 ( f1 int) ;
+
+SELECT * FROM (
+        SELECT t3.*
+        FROM t2 STRAIGHT_JOIN t3
+        ON t3.f1
+        AND (t3.f1 ) IN (
+                SELECT t1.f1
+                FROM t1
+        )
+) AS alias1;
+DROP TABLE t1,t2,t3;
+
+--echo # BUG#611704: another testcase:
+CREATE TABLE t1 ( f1 int(11), f3 varchar(1), f4 varchar(1)) ;
+CREATE TABLE t2 ( f2 int(11), KEY (f2));
+CREATE TABLE t3 ( f4 varchar(1)) ;
+
+PREPARE st1 FROM '
+SELECT *
+FROM t1
+STRAIGHT_JOIN ( t2 STRAIGHT_JOIN t3 ON t2.f2 )
+ON (t1.f3) IN ( SELECT f4 FROM t1 )
+';
+EXECUTE st1;
+DROP TABLE t1,t2,t3;
+
+
 # The following command must be the last one the file 
 set optimizer_switch=@subselect_sj_tmp;
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index a1a984fbd2c..31a4c3db8e3 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -3413,7 +3413,9 @@ bool SELECT_LEX::merge_subquery(TABLE_LIST *derived, SELECT_LEX *subq_select,
          in_subq++)
     {
       join->sj_subselects.append(join->thd->mem_root, *in_subq);
-      (*in_subq)->emb_on_expr_nest= derived;
+      DBUG_ASSERT((*in_subq)->emb_on_expr_nest != NULL);
+      if ((*in_subq)->emb_on_expr_nest == NO_JOIN_NEST)
+        (*in_subq)->emb_on_expr_nest= derived;
     }
   }
   /*