From: Roy Lyseng
Date: March 26 2012 10:51am
Subject: bzr push into mysql-trunk branch (roy.lyseng:3891 to 3892) Bug#13845930
List-Archive: http://lists.mysql.com/commits/143313
X-Bug: 13845930
Message-Id: <20120326105117.8B28E211@tyr67.norway.sun.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

 3892 Roy Lyseng	2012-03-26
      Bug#13845930: Segfault in st_join_table::and_with_condition
      
      This problem occurs during second execution of a query involving
      outer join and subquery to semi-join transformation. The outer join
      is correctly converted to an inner join, but in make_join_select(),
      the JOIN_TAB for the outer-joined table (alias3) has an on_expr_ref
      that is non-NULL, even though the first_inner pointer is NULL.
      
      The problem originates from convert_subquery_to_semijoin() where
      a "wrap nest" is created that will contain the table alias3 and
      the semi-joined tables sq1_alias1 and sq1_alias2. When creating
      the wrap nest, the join condition is transferred from the inner table
      (alias3) to the wrap nest. In this process, resetting the
      prep_join_cond field of the inner table was forgotten.
      
      mysql-test/include/subquery_sj.inc
        Added test case for bug#13845930.
      
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_bka.result
      mysql-test/r/subquery_sj_all_bka_nixbnl.result
      mysql-test/r/subquery_sj_all_bkaunique.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_bka.result
      mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
      mysql-test/r/subquery_sj_dupsweed_bkaunique.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_bka.result
      mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
      mysql-test/r/subquery_sj_firstmatch_bkaunique.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_bka.result
      mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
      mysql-test/r/subquery_sj_loosescan_bkaunique.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_bka.result
      mysql-test/r/subquery_sj_mat_bka_nixbnl.result
      mysql-test/r/subquery_sj_mat_bkaunique.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_bka.result
      mysql-test/r/subquery_sj_none_bka_nixbnl.result
      mysql-test/r/subquery_sj_none_bkaunique.result
        Added test case results for bug#13845930.
      
      sql/sql_optimizer.cc
        In convert_subquery_to_semijoin(), when creating a wrap nest,
        reset the prep_join_cond field for the table whose join condition
        is copied to the wrap nest.

    modified:
      mysql-test/include/subquery_sj.inc
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_bka.result
      mysql-test/r/subquery_sj_all_bka_nixbnl.result
      mysql-test/r/subquery_sj_all_bkaunique.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_bka.result
      mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
      mysql-test/r/subquery_sj_dupsweed_bkaunique.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_bka.result
      mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
      mysql-test/r/subquery_sj_firstmatch_bkaunique.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_bka.result
      mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
      mysql-test/r/subquery_sj_loosescan_bkaunique.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_bka.result
      mysql-test/r/subquery_sj_mat_bka_nixbnl.result
      mysql-test/r/subquery_sj_mat_bkaunique.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_bka.result
      mysql-test/r/subquery_sj_none_bka_nixbnl.result
      mysql-test/r/subquery_sj_none_bkaunique.result
      sql/sql_optimizer.cc
 3891 Roy Lyseng	2012-03-22
      WL#5561 - Enable semi-join transformation with outer join queries
      
      Third worklog commit, after Guilhem's review.
      
      sql/sql_optimizer.cc
        Deleted some unused code, forgotten in previous commit.
      
      sql/sql_planner.cc
        Updated check_interleaving_with_nj() according to review comments.
      
      sql/sql_select.cc
        Typos fixed.
      
      sql/table.h
        Adjusted some comments.

    modified:
      sql/sql_optimizer.cc
      sql/sql_planner.cc
      sql/sql_select.cc
      sql/table.h
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc	2012-03-21 11:22:13 +0000
+++ b/mysql-test/include/subquery_sj.inc	2012-03-26 10:45:48 +0000
@@ -5435,4 +5435,43 @@ eval $query;
 DROP TABLE t1,t2;
 SET @@optimizer_search_depth=@old_depth;
 
+--echo #
+--echo # Bug#13845930: Segfault in st_join_table::and_with_condition
+--echo #
+
+CREATE TABLE t1 (
+  col_int INTEGER
+);
+
+CREATE TABLE t2 (
+  col_varchar_1 VARCHAR(1),
+  col_varchar_2 VARCHAR(1)
+);
+
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+     JOIN
+      (t2 AS alias2
+       LEFT JOIN t2 AS alias3
+       ON (8, 92) IN
+          (SELECT sq1_alias1.col_int,
+                  sq1_alias2.col_int
+           FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+          )
+      )
+     ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+
+EXECUTE stmt;
+EXECUTE stmt;
+
+DEALLOCATE prepare stmt;
+
+DROP TABLE t1, t2;
+
+--echo # End of test for bug#13845930.
+
 --echo # End of 5.6 tests

=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result	2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_all.result	2012-03-26 10:45:48 +0000
@@ -8542,5 +8542,37 @@ col_datetime_key
 2008-09-27 00:34:58
 DROP TABLE t1,t2;
 SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+     JOIN
+      (t2 AS alias2
+       LEFT JOIN t2 AS alias3
+       ON (8, 92) IN
+          (SELECT sq1_alias1.col_int,
+                  sq1_alias2.col_int
+           FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+          )
+      )
+     ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
 # End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_all_bka.result'
--- a/mysql-test/r/subquery_sj_all_bka.result	2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_all_bka.result	2012-03-26 10:45:48 +0000
@@ -8543,6 +8543,38 @@ col_datetime_key
 2008-09-27 00:34:58
 DROP TABLE t1,t2;
 SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+     JOIN
+      (t2 AS alias2
+       LEFT JOIN t2 AS alias3
+       ON (8, 92) IN
+          (SELECT sq1_alias1.col_int,
+                  sq1_alias2.col_int
+           FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+          )
+      )
+     ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_all_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_all_bka_nixbnl.result	2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_all_bka_nixbnl.result	2012-03-26 10:45:48 +0000
@@ -8543,6 +8543,38 @@ col_datetime_key
 2008-09-27 00:34:58
 DROP TABLE t1,t2;
 SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+     JOIN
+      (t2 AS alias2
+       LEFT JOIN t2 AS alias3
+       ON (8, 92) IN
+          (SELECT sq1_alias1.col_int,
+                  sq1_alias2.col_int
+           FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+          )
+      )
+     ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_all_bkaunique.result'
--- a/mysql-test/r/subquery_sj_all_bkaunique.result	2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_all_bkaunique.result	2012-03-26 10:45:48 +0000
@@ -8544,6 +8544,38 @@ col_datetime_key
 2008-09-27 00:34:58
 DROP TABLE t1,t2;
 SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+     JOIN
+      (t2 AS alias2
+       LEFT JOIN t2 AS alias3
+       ON (8, 92) IN
+          (SELECT sq1_alias1.col_int,
+                  sq1_alias2.col_int
+           FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+          )
+      )
+     ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result	2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result	2012-03-26 10:45:48 +0000
@@ -8541,5 +8541,37 @@ col_datetime_key
 2008-09-27 00:34:58
 DROP TABLE t1,t2;
 SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+     JOIN
+      (t2 AS alias2
+       LEFT JOIN t2 AS alias3
+       ON (8, 92) IN
+          (SELECT sq1_alias1.col_int,
+                  sq1_alias2.col_int
+           FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+          )
+      )
+     ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
 # End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka.result	2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka.result	2012-03-26 10:45:48 +0000
@@ -8542,6 +8542,38 @@ col_datetime_key
 2008-09-27 00:34:58
 DROP TABLE t1,t2;
 SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+     JOIN
+      (t2 AS alias2
+       LEFT JOIN t2 AS alias3
+       ON (8, 92) IN
+          (SELECT sq1_alias1.col_int,
+                  sq1_alias2.col_int
+           FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+          )
+      )
+     ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result	2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result	2012-03-26 10:45:48 +0000
@@ -8542,6 +8542,38 @@ col_datetime_key
 2008-09-27 00:34:58
 DROP TABLE t1,t2;
 SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+     JOIN
+      (t2 AS alias2
+       LEFT JOIN t2 AS alias3
+       ON (8, 92) IN
+          (SELECT sq1_alias1.col_int,
+                  sq1_alias2.col_int
+           FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+          )
+      )
+     ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed_bkaunique.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bkaunique.result	2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bkaunique.result	2012-03-26 10:45:48 +0000
@@ -8543,6 +8543,38 @@ col_datetime_key
 2008-09-27 00:34:58
 DROP TABLE t1,t2;
 SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+     JOIN
+      (t2 AS alias2
+       LEFT JOIN t2 AS alias3
+       ON (8, 92) IN
+          (SELECT sq1_alias1.col_int,
+                  sq1_alias2.col_int
+           FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+          )
+      )
+     ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result	2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result	2012-03-26 10:45:48 +0000
@@ -8542,6 +8542,38 @@ col_datetime_key
 2008-09-27 00:34:58
 DROP TABLE t1,t2;
 SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+     JOIN
+      (t2 AS alias2
+       LEFT JOIN t2 AS alias3
+       ON (8, 92) IN
+          (SELECT sq1_alias1.col_int,
+                  sq1_alias2.col_int
+           FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+          )
+      )
+     ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
 # End of 5.6 tests
 #
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for

=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka.result	2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka.result	2012-03-26 10:45:48 +0000
@@ -8543,6 +8543,38 @@ col_datetime_key
 2008-09-27 00:34:58
 DROP TABLE t1,t2;
 SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+     JOIN
+      (t2 AS alias2
+       LEFT JOIN t2 AS alias3
+       ON (8, 92) IN
+          (SELECT sq1_alias1.col_int,
+                  sq1_alias2.col_int
+           FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+          )
+      )
+     ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
 # End of 5.6 tests
 #
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for

=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result	2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result	2012-03-26 10:45:48 +0000
@@ -8543,6 +8543,38 @@ col_datetime_key
 2008-09-27 00:34:58
 DROP TABLE t1,t2;
 SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+     JOIN
+      (t2 AS alias2
+       LEFT JOIN t2 AS alias3
+       ON (8, 92) IN
+          (SELECT sq1_alias1.col_int,
+                  sq1_alias2.col_int
+           FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+          )
+      )
+     ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
 # End of 5.6 tests
 #
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for

=== modified file 'mysql-test/r/subquery_sj_firstmatch_bkaunique.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bkaunique.result	2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bkaunique.result	2012-03-26 10:45:48 +0000
@@ -8544,6 +8544,38 @@ col_datetime_key
 2008-09-27 00:34:58
 DROP TABLE t1,t2;
 SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+     JOIN
+      (t2 AS alias2
+       LEFT JOIN t2 AS alias3
+       ON (8, 92) IN
+          (SELECT sq1_alias1.col_int,
+                  sq1_alias2.col_int
+           FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+          )
+      )
+     ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
 # End of 5.6 tests
 #
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for

=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result	2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result	2012-03-26 10:45:48 +0000
@@ -8542,5 +8542,37 @@ col_datetime_key
 2008-09-27 00:34:58
 DROP TABLE t1,t2;
 SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+     JOIN
+      (t2 AS alias2
+       LEFT JOIN t2 AS alias3
+       ON (8, 92) IN
+          (SELECT sq1_alias1.col_int,
+                  sq1_alias2.col_int
+           FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+          )
+      )
+     ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
 # End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_loosescan_bka.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka.result	2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka.result	2012-03-26 10:45:48 +0000
@@ -8543,6 +8543,38 @@ col_datetime_key
 2008-09-27 00:34:58
 DROP TABLE t1,t2;
 SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+     JOIN
+      (t2 AS alias2
+       LEFT JOIN t2 AS alias3
+       ON (8, 92) IN
+          (SELECT sq1_alias1.col_int,
+                  sq1_alias2.col_int
+           FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+          )
+      )
+     ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result	2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result	2012-03-26 10:45:48 +0000
@@ -8543,6 +8543,38 @@ col_datetime_key
 2008-09-27 00:34:58
 DROP TABLE t1,t2;
 SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+     JOIN
+      (t2 AS alias2
+       LEFT JOIN t2 AS alias3
+       ON (8, 92) IN
+          (SELECT sq1_alias1.col_int,
+                  sq1_alias2.col_int
+           FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+          )
+      )
+     ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_loosescan_bkaunique.result'
--- a/mysql-test/r/subquery_sj_loosescan_bkaunique.result	2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bkaunique.result	2012-03-26 10:45:48 +0000
@@ -8544,6 +8544,38 @@ col_datetime_key
 2008-09-27 00:34:58
 DROP TABLE t1,t2;
 SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+     JOIN
+      (t2 AS alias2
+       LEFT JOIN t2 AS alias3
+       ON (8, 92) IN
+          (SELECT sq1_alias1.col_int,
+                  sq1_alias2.col_int
+           FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+          )
+      )
+     ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result	2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_mat.result	2012-03-26 10:45:48 +0000
@@ -8542,5 +8542,37 @@ col_datetime_key
 2008-09-27 00:34:58
 DROP TABLE t1,t2;
 SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+     JOIN
+      (t2 AS alias2
+       LEFT JOIN t2 AS alias3
+       ON (8, 92) IN
+          (SELECT sq1_alias1.col_int,
+                  sq1_alias2.col_int
+           FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+          )
+      )
+     ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
 # End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_bka.result'
--- a/mysql-test/r/subquery_sj_mat_bka.result	2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka.result	2012-03-26 10:45:48 +0000
@@ -8543,6 +8543,38 @@ col_datetime_key
 2008-09-27 00:34:58
 DROP TABLE t1,t2;
 SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+     JOIN
+      (t2 AS alias2
+       LEFT JOIN t2 AS alias3
+       ON (8, 92) IN
+          (SELECT sq1_alias1.col_int,
+                  sq1_alias2.col_int
+           FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+          )
+      )
+     ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_mat_bka_nixbnl.result	2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka_nixbnl.result	2012-03-26 10:45:48 +0000
@@ -8543,6 +8543,38 @@ col_datetime_key
 2008-09-27 00:34:58
 DROP TABLE t1,t2;
 SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+     JOIN
+      (t2 AS alias2
+       LEFT JOIN t2 AS alias3
+       ON (8, 92) IN
+          (SELECT sq1_alias1.col_int,
+                  sq1_alias2.col_int
+           FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+          )
+      )
+     ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_bkaunique.result'
--- a/mysql-test/r/subquery_sj_mat_bkaunique.result	2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_mat_bkaunique.result	2012-03-26 10:45:48 +0000
@@ -8544,6 +8544,38 @@ col_datetime_key
 2008-09-27 00:34:58
 DROP TABLE t1,t2;
 SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+     JOIN
+      (t2 AS alias2
+       LEFT JOIN t2 AS alias3
+       ON (8, 92) IN
+          (SELECT sq1_alias1.col_int,
+                  sq1_alias2.col_int
+           FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+          )
+      )
+     ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
--- a/mysql-test/r/subquery_sj_mat_nosj.result	2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result	2012-03-26 10:45:48 +0000
@@ -8618,5 +8618,37 @@ col_datetime_key
 2008-09-27 00:34:58
 DROP TABLE t1,t2;
 SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+     JOIN
+      (t2 AS alias2
+       LEFT JOIN t2 AS alias3
+       ON (8, 92) IN
+          (SELECT sq1_alias1.col_int,
+                  sq1_alias2.col_int
+           FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+          )
+      )
+     ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
 # End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result	2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_none.result	2012-03-26 10:45:48 +0000
@@ -8553,5 +8553,37 @@ col_datetime_key
 2008-09-27 00:34:58
 DROP TABLE t1,t2;
 SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+     JOIN
+      (t2 AS alias2
+       LEFT JOIN t2 AS alias3
+       ON (8, 92) IN
+          (SELECT sq1_alias1.col_int,
+                  sq1_alias2.col_int
+           FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+          )
+      )
+     ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
 # End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none_bka.result'
--- a/mysql-test/r/subquery_sj_none_bka.result	2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_none_bka.result	2012-03-26 10:45:48 +0000
@@ -8554,6 +8554,38 @@ col_datetime_key
 2008-09-27 00:34:58
 DROP TABLE t1,t2;
 SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+     JOIN
+      (t2 AS alias2
+       LEFT JOIN t2 AS alias3
+       ON (8, 92) IN
+          (SELECT sq1_alias1.col_int,
+                  sq1_alias2.col_int
+           FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+          )
+      )
+     ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_none_bka_nixbnl.result	2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_none_bka_nixbnl.result	2012-03-26 10:45:48 +0000
@@ -8554,6 +8554,38 @@ col_datetime_key
 2008-09-27 00:34:58
 DROP TABLE t1,t2;
 SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+     JOIN
+      (t2 AS alias2
+       LEFT JOIN t2 AS alias3
+       ON (8, 92) IN
+          (SELECT sq1_alias1.col_int,
+                  sq1_alias2.col_int
+           FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+          )
+      )
+     ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none_bkaunique.result'
--- a/mysql-test/r/subquery_sj_none_bkaunique.result	2012-03-21 11:22:13 +0000
+++ b/mysql-test/r/subquery_sj_none_bkaunique.result	2012-03-26 10:45:48 +0000
@@ -8555,6 +8555,38 @@ col_datetime_key
 2008-09-27 00:34:58
 DROP TABLE t1,t2;
 SET @@optimizer_search_depth=@old_depth;
+#
+# Bug#13845930: Segfault in st_join_table::and_with_condition
+#
+CREATE TABLE t1 (
+col_int INTEGER
+);
+CREATE TABLE t2 (
+col_varchar_1 VARCHAR(1),
+col_varchar_2 VARCHAR(1)
+);
+INSERT INTO t2 VALUES ('x','x'), ('c','c');
+PREPARE stmt FROM '
+SELECT alias2.col_varchar_2 AS field1
+FROM t2 AS alias1
+     JOIN
+      (t2 AS alias2
+       LEFT JOIN t2 AS alias3
+       ON (8, 92) IN
+          (SELECT sq1_alias1.col_int,
+                  sq1_alias2.col_int
+           FROM t1 AS sq1_alias1 JOIN t1 AS sq1_alias2
+          )
+      )
+     ON alias3.col_varchar_1 = alias2.col_varchar_2
+';
+EXECUTE stmt;
+field1
+EXECUTE stmt;
+field1
+DEALLOCATE prepare stmt;
+DROP TABLE t1, t2;
+# End of test for bug#13845930.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'sql/sql_optimizer.cc'
--- a/sql/sql_optimizer.cc	2012-03-22 11:53:41 +0000
+++ b/sql/sql_optimizer.cc	2012-03-26 10:45:48 +0000
@@ -6238,6 +6238,7 @@ static bool convert_subquery_to_semijoin
 
       wrap_nest->set_join_cond(outer_tbl->join_cond());
       outer_tbl->set_join_cond(NULL);
+      outer_tbl->prep_join_cond= NULL;
 
       List_iterator<TABLE_LIST> li(*wrap_nest->join_list);
       TABLE_LIST *tbl;

No bundle (reason: useless for push emails).