From: Roy Lyseng
Date: November 18 2011 10:38am
Subject: bzr push into mysql-trunk branch (roy.lyseng:3489 to 3490) Bug#13339643
List-Archive: http://lists.mysql.com/commits/142053
X-Bug: 13339643
Message-Id: <20111118103802.72FD6203@tyr67.norway.sun.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

 3490 Roy Lyseng	2011-11-18
      Bug#13339643: Assertion in JOIN::flatten_subqueries on second execution
      
      Bug#11764757 (former 57623) introduced a DBUG_ASSERT, assuming that a
      join_nest containing a nested join never had the prep_on_expr field set
      when flattening subqueries. Thus, the permanent transformation of this
      field could not be done here, but was postponed to simplify_joins().
      
      However, this bug shows that in the case of a viewed table, the
      join_nest contains a nested join, and prep_on_expr is already set.
      Thus, the assertion is proven wrong, and it should be removed.
      
      mysql-test/include/subquery_sj.inc
        Added test case for bug#13339643
      
      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#13339643
      
      sql/sql_select.cc
        Removed DBUG_ASSERT that failed when view was specified.

    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_select.cc
 3489 Roy Lyseng	2011-11-14
      Bug#13383857: Another crash in memcpy from join_cache::write_record_data
      
      This is a similar case as bug#13106350, except that the derived table
      here has join buffering enabled. When materializing the table in this
      case, setting up buffers for copying of current rowid was omitted.
      
      The fix moves materialization check from join_matching_records()
      to sub_select_cache() and adds setup of buffers for copying of
      current rowid.
      
      mysql-test/t/derived.test
        Added test case for bug#13383857.
      
      mysql-test/r/derived.result
        Added test case results for bug#13383857.
      
      sql/join_cache.cc
        materialization of derived tables is moved into sub_select_cache().
      
      sql/sql_select.cc
        In sub_select_cache(), materialize derived table when needed
        and set up buffers for copying of current rowid.

    modified:
      mysql-test/r/derived.result
      mysql-test/t/derived.test
      sql/sql_join_cache.cc
      sql/sql_select.cc
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc	2011-11-09 09:04:15 +0000
+++ b/mysql-test/include/subquery_sj.inc	2011-11-18 10:33:08 +0000
@@ -4442,4 +4442,48 @@ DROP TABLE t1, t2, t3;
 
 --echo # End of test for bug#13334882.
 
+--echo #
+--echo # Bug#13339643: Assertion on JOIN::flatten_subqueries on second execution
+--echo #
+
+CREATE TABLE t1 (
+  col_int_nokey INT,
+  col_varchar_nokey VARCHAR(1)
+);
+
+INSERT INTO t1 VALUES
+ (1,'o'),
+ (2,'t');
+
+CREATE TABLE t2 LIKE t1;
+
+INSERT INTO t2 VALUES
+ (1,'o'),
+ (4,'f');
+
+CREATE VIEW v_t2 AS SELECT * FROM t2;
+
+CREATE TABLE t3 LIKE t1;
+
+INSERT INTO t3 VALUES
+ (1,'o'),
+ (4,'f');
+
+let $query=
+SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+     INNER JOIN v_t2 AS alias2
+     ON alias2.col_int_nokey = alias1.col_int_nokey AND
+          'o' IN (SELECT col_varchar_nokey
+                  FROM t3);
+eval explain $query;
+eval $query;
+
+eval PREPARE stmt FROM "$query";
+EXECUTE stmt;
+
+DROP VIEW v_t2;
+DROP TABLE t1, t2, t3;
+--echo # End of test for bug#13339643.
+
 --echo # End of 5.6 tests

=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result	2011-11-09 09:04:15 +0000
+++ b/mysql-test/r/subquery_sj_all.result	2011-11-18 10:33:08 +0000
@@ -7112,5 +7112,54 @@ AND grandparent1.col_int_key <> 3
 g1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13334882.
+#
+# Bug#13339643: Assertion on JOIN::flatten_subqueries on second execution
+#
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,'o'),
+(2,'t');
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 VALUES
+(1,'o'),
+(4,'f');
+CREATE VIEW v_t2 AS SELECT * FROM t2;
+CREATE TABLE t3 LIKE t1;
+INSERT INTO t3 VALUES
+(1,'o'),
+(4,'f');
+explain SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+col_varchar_nokey
+o
+PREPARE stmt FROM "SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3)";
+EXECUTE stmt;
+col_varchar_nokey
+o
+DROP VIEW v_t2;
+DROP TABLE t1, t2, t3;
+# End of test for bug#13339643.
 # 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	2011-11-09 09:04:15 +0000
+++ b/mysql-test/r/subquery_sj_all_bka.result	2011-11-18 10:33:08 +0000
@@ -7113,6 +7113,55 @@ AND grandparent1.col_int_key <> 3
 g1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13334882.
+#
+# Bug#13339643: Assertion on JOIN::flatten_subqueries on second execution
+#
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,'o'),
+(2,'t');
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 VALUES
+(1,'o'),
+(4,'f');
+CREATE VIEW v_t2 AS SELECT * FROM t2;
+CREATE TABLE t3 LIKE t1;
+INSERT INTO t3 VALUES
+(1,'o'),
+(4,'f');
+explain SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+col_varchar_nokey
+o
+PREPARE stmt FROM "SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3)";
+EXECUTE stmt;
+col_varchar_nokey
+o
+DROP VIEW v_t2;
+DROP TABLE t1, t2, t3;
+# End of test for bug#13339643.
 # 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	2011-11-09 09:04:15 +0000
+++ b/mysql-test/r/subquery_sj_all_bka_nixbnl.result	2011-11-18 10:33:08 +0000
@@ -7113,6 +7113,55 @@ AND grandparent1.col_int_key <> 3
 g1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13334882.
+#
+# Bug#13339643: Assertion on JOIN::flatten_subqueries on second execution
+#
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,'o'),
+(2,'t');
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 VALUES
+(1,'o'),
+(4,'f');
+CREATE VIEW v_t2 AS SELECT * FROM t2;
+CREATE TABLE t3 LIKE t1;
+INSERT INTO t3 VALUES
+(1,'o'),
+(4,'f');
+explain SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+col_varchar_nokey
+o
+PREPARE stmt FROM "SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3)";
+EXECUTE stmt;
+col_varchar_nokey
+o
+DROP VIEW v_t2;
+DROP TABLE t1, t2, t3;
+# End of test for bug#13339643.
 # 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	2011-11-09 09:04:15 +0000
+++ b/mysql-test/r/subquery_sj_all_bkaunique.result	2011-11-18 10:33:08 +0000
@@ -7114,6 +7114,55 @@ AND grandparent1.col_int_key <> 3
 g1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13334882.
+#
+# Bug#13339643: Assertion on JOIN::flatten_subqueries on second execution
+#
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,'o'),
+(2,'t');
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 VALUES
+(1,'o'),
+(4,'f');
+CREATE VIEW v_t2 AS SELECT * FROM t2;
+CREATE TABLE t3 LIKE t1;
+INSERT INTO t3 VALUES
+(1,'o'),
+(4,'f');
+explain SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+col_varchar_nokey
+o
+PREPARE stmt FROM "SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3)";
+EXECUTE stmt;
+col_varchar_nokey
+o
+DROP VIEW v_t2;
+DROP TABLE t1, t2, t3;
+# End of test for bug#13339643.
 # 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	2011-11-09 09:04:15 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result	2011-11-18 10:33:08 +0000
@@ -7112,5 +7112,54 @@ AND grandparent1.col_int_key <> 3
 g1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13334882.
+#
+# Bug#13339643: Assertion on JOIN::flatten_subqueries on second execution
+#
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,'o'),
+(2,'t');
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 VALUES
+(1,'o'),
+(4,'f');
+CREATE VIEW v_t2 AS SELECT * FROM t2;
+CREATE TABLE t3 LIKE t1;
+INSERT INTO t3 VALUES
+(1,'o'),
+(4,'f');
+explain SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+col_varchar_nokey
+o
+PREPARE stmt FROM "SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3)";
+EXECUTE stmt;
+col_varchar_nokey
+o
+DROP VIEW v_t2;
+DROP TABLE t1, t2, t3;
+# End of test for bug#13339643.
 # 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	2011-11-09 09:04:15 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka.result	2011-11-18 10:33:08 +0000
@@ -7113,6 +7113,55 @@ AND grandparent1.col_int_key <> 3
 g1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13334882.
+#
+# Bug#13339643: Assertion on JOIN::flatten_subqueries on second execution
+#
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,'o'),
+(2,'t');
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 VALUES
+(1,'o'),
+(4,'f');
+CREATE VIEW v_t2 AS SELECT * FROM t2;
+CREATE TABLE t3 LIKE t1;
+INSERT INTO t3 VALUES
+(1,'o'),
+(4,'f');
+explain SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+col_varchar_nokey
+o
+PREPARE stmt FROM "SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3)";
+EXECUTE stmt;
+col_varchar_nokey
+o
+DROP VIEW v_t2;
+DROP TABLE t1, t2, t3;
+# End of test for bug#13339643.
 # 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	2011-11-09 09:04:15 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result	2011-11-18 10:33:08 +0000
@@ -7113,6 +7113,55 @@ AND grandparent1.col_int_key <> 3
 g1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13334882.
+#
+# Bug#13339643: Assertion on JOIN::flatten_subqueries on second execution
+#
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,'o'),
+(2,'t');
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 VALUES
+(1,'o'),
+(4,'f');
+CREATE VIEW v_t2 AS SELECT * FROM t2;
+CREATE TABLE t3 LIKE t1;
+INSERT INTO t3 VALUES
+(1,'o'),
+(4,'f');
+explain SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+col_varchar_nokey
+o
+PREPARE stmt FROM "SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3)";
+EXECUTE stmt;
+col_varchar_nokey
+o
+DROP VIEW v_t2;
+DROP TABLE t1, t2, t3;
+# End of test for bug#13339643.
 # 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	2011-11-09 09:04:15 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bkaunique.result	2011-11-18 10:33:08 +0000
@@ -7114,6 +7114,55 @@ AND grandparent1.col_int_key <> 3
 g1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13334882.
+#
+# Bug#13339643: Assertion on JOIN::flatten_subqueries on second execution
+#
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,'o'),
+(2,'t');
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 VALUES
+(1,'o'),
+(4,'f');
+CREATE VIEW v_t2 AS SELECT * FROM t2;
+CREATE TABLE t3 LIKE t1;
+INSERT INTO t3 VALUES
+(1,'o'),
+(4,'f');
+explain SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+col_varchar_nokey
+o
+PREPARE stmt FROM "SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3)";
+EXECUTE stmt;
+col_varchar_nokey
+o
+DROP VIEW v_t2;
+DROP TABLE t1, t2, t3;
+# End of test for bug#13339643.
 # 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	2011-11-09 09:04:15 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result	2011-11-18 10:33:08 +0000
@@ -7113,6 +7113,55 @@ AND grandparent1.col_int_key <> 3
 g1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13334882.
+#
+# Bug#13339643: Assertion on JOIN::flatten_subqueries on second execution
+#
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,'o'),
+(2,'t');
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 VALUES
+(1,'o'),
+(4,'f');
+CREATE VIEW v_t2 AS SELECT * FROM t2;
+CREATE TABLE t3 LIKE t1;
+INSERT INTO t3 VALUES
+(1,'o'),
+(4,'f');
+explain SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+col_varchar_nokey
+o
+PREPARE stmt FROM "SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3)";
+EXECUTE stmt;
+col_varchar_nokey
+o
+DROP VIEW v_t2;
+DROP TABLE t1, t2, t3;
+# End of test for bug#13339643.
 # 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	2011-11-09 09:04:15 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka.result	2011-11-18 10:33:08 +0000
@@ -7114,6 +7114,55 @@ AND grandparent1.col_int_key <> 3
 g1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13334882.
+#
+# Bug#13339643: Assertion on JOIN::flatten_subqueries on second execution
+#
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,'o'),
+(2,'t');
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 VALUES
+(1,'o'),
+(4,'f');
+CREATE VIEW v_t2 AS SELECT * FROM t2;
+CREATE TABLE t3 LIKE t1;
+INSERT INTO t3 VALUES
+(1,'o'),
+(4,'f');
+explain SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+col_varchar_nokey
+o
+PREPARE stmt FROM "SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3)";
+EXECUTE stmt;
+col_varchar_nokey
+o
+DROP VIEW v_t2;
+DROP TABLE t1, t2, t3;
+# End of test for bug#13339643.
 # 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	2011-11-09 09:04:15 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result	2011-11-18 10:33:08 +0000
@@ -7114,6 +7114,55 @@ AND grandparent1.col_int_key <> 3
 g1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13334882.
+#
+# Bug#13339643: Assertion on JOIN::flatten_subqueries on second execution
+#
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,'o'),
+(2,'t');
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 VALUES
+(1,'o'),
+(4,'f');
+CREATE VIEW v_t2 AS SELECT * FROM t2;
+CREATE TABLE t3 LIKE t1;
+INSERT INTO t3 VALUES
+(1,'o'),
+(4,'f');
+explain SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+col_varchar_nokey
+o
+PREPARE stmt FROM "SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3)";
+EXECUTE stmt;
+col_varchar_nokey
+o
+DROP VIEW v_t2;
+DROP TABLE t1, t2, t3;
+# End of test for bug#13339643.
 # 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	2011-11-09 09:04:15 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bkaunique.result	2011-11-18 10:33:08 +0000
@@ -7115,6 +7115,55 @@ AND grandparent1.col_int_key <> 3
 g1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13334882.
+#
+# Bug#13339643: Assertion on JOIN::flatten_subqueries on second execution
+#
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,'o'),
+(2,'t');
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 VALUES
+(1,'o'),
+(4,'f');
+CREATE VIEW v_t2 AS SELECT * FROM t2;
+CREATE TABLE t3 LIKE t1;
+INSERT INTO t3 VALUES
+(1,'o'),
+(4,'f');
+explain SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+col_varchar_nokey
+o
+PREPARE stmt FROM "SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3)";
+EXECUTE stmt;
+col_varchar_nokey
+o
+DROP VIEW v_t2;
+DROP TABLE t1, t2, t3;
+# End of test for bug#13339643.
 # 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	2011-11-09 09:04:15 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result	2011-11-18 10:33:08 +0000
@@ -7113,5 +7113,54 @@ AND grandparent1.col_int_key <> 3
 g1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13334882.
+#
+# Bug#13339643: Assertion on JOIN::flatten_subqueries on second execution
+#
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,'o'),
+(2,'t');
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 VALUES
+(1,'o'),
+(4,'f');
+CREATE VIEW v_t2 AS SELECT * FROM t2;
+CREATE TABLE t3 LIKE t1;
+INSERT INTO t3 VALUES
+(1,'o'),
+(4,'f');
+explain SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+col_varchar_nokey
+o
+PREPARE stmt FROM "SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3)";
+EXECUTE stmt;
+col_varchar_nokey
+o
+DROP VIEW v_t2;
+DROP TABLE t1, t2, t3;
+# End of test for bug#13339643.
 # 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	2011-11-09 09:04:15 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka.result	2011-11-18 10:33:08 +0000
@@ -7114,6 +7114,55 @@ AND grandparent1.col_int_key <> 3
 g1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13334882.
+#
+# Bug#13339643: Assertion on JOIN::flatten_subqueries on second execution
+#
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,'o'),
+(2,'t');
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 VALUES
+(1,'o'),
+(4,'f');
+CREATE VIEW v_t2 AS SELECT * FROM t2;
+CREATE TABLE t3 LIKE t1;
+INSERT INTO t3 VALUES
+(1,'o'),
+(4,'f');
+explain SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+col_varchar_nokey
+o
+PREPARE stmt FROM "SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3)";
+EXECUTE stmt;
+col_varchar_nokey
+o
+DROP VIEW v_t2;
+DROP TABLE t1, t2, t3;
+# End of test for bug#13339643.
 # 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	2011-11-09 09:04:15 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result	2011-11-18 10:33:08 +0000
@@ -7114,6 +7114,55 @@ AND grandparent1.col_int_key <> 3
 g1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13334882.
+#
+# Bug#13339643: Assertion on JOIN::flatten_subqueries on second execution
+#
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,'o'),
+(2,'t');
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 VALUES
+(1,'o'),
+(4,'f');
+CREATE VIEW v_t2 AS SELECT * FROM t2;
+CREATE TABLE t3 LIKE t1;
+INSERT INTO t3 VALUES
+(1,'o'),
+(4,'f');
+explain SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+col_varchar_nokey
+o
+PREPARE stmt FROM "SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3)";
+EXECUTE stmt;
+col_varchar_nokey
+o
+DROP VIEW v_t2;
+DROP TABLE t1, t2, t3;
+# End of test for bug#13339643.
 # 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	2011-11-09 09:04:15 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bkaunique.result	2011-11-18 10:33:08 +0000
@@ -7115,6 +7115,55 @@ AND grandparent1.col_int_key <> 3
 g1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13334882.
+#
+# Bug#13339643: Assertion on JOIN::flatten_subqueries on second execution
+#
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,'o'),
+(2,'t');
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 VALUES
+(1,'o'),
+(4,'f');
+CREATE VIEW v_t2 AS SELECT * FROM t2;
+CREATE TABLE t3 LIKE t1;
+INSERT INTO t3 VALUES
+(1,'o'),
+(4,'f');
+explain SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+col_varchar_nokey
+o
+PREPARE stmt FROM "SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3)";
+EXECUTE stmt;
+col_varchar_nokey
+o
+DROP VIEW v_t2;
+DROP TABLE t1, t2, t3;
+# End of test for bug#13339643.
 # 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	2011-11-09 09:04:15 +0000
+++ b/mysql-test/r/subquery_sj_mat.result	2011-11-18 10:33:08 +0000
@@ -7112,5 +7112,54 @@ AND grandparent1.col_int_key <> 3
 g1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13334882.
+#
+# Bug#13339643: Assertion on JOIN::flatten_subqueries on second execution
+#
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,'o'),
+(2,'t');
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 VALUES
+(1,'o'),
+(4,'f');
+CREATE VIEW v_t2 AS SELECT * FROM t2;
+CREATE TABLE t3 LIKE t1;
+INSERT INTO t3 VALUES
+(1,'o'),
+(4,'f');
+explain SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+col_varchar_nokey
+o
+PREPARE stmt FROM "SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3)";
+EXECUTE stmt;
+col_varchar_nokey
+o
+DROP VIEW v_t2;
+DROP TABLE t1, t2, t3;
+# End of test for bug#13339643.
 # 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	2011-11-09 09:04:15 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka.result	2011-11-18 10:33:08 +0000
@@ -7113,6 +7113,55 @@ AND grandparent1.col_int_key <> 3
 g1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13334882.
+#
+# Bug#13339643: Assertion on JOIN::flatten_subqueries on second execution
+#
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,'o'),
+(2,'t');
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 VALUES
+(1,'o'),
+(4,'f');
+CREATE VIEW v_t2 AS SELECT * FROM t2;
+CREATE TABLE t3 LIKE t1;
+INSERT INTO t3 VALUES
+(1,'o'),
+(4,'f');
+explain SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+col_varchar_nokey
+o
+PREPARE stmt FROM "SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3)";
+EXECUTE stmt;
+col_varchar_nokey
+o
+DROP VIEW v_t2;
+DROP TABLE t1, t2, t3;
+# End of test for bug#13339643.
 # 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	2011-11-09 09:04:15 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka_nixbnl.result	2011-11-18 10:33:08 +0000
@@ -7113,6 +7113,55 @@ AND grandparent1.col_int_key <> 3
 g1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13334882.
+#
+# Bug#13339643: Assertion on JOIN::flatten_subqueries on second execution
+#
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,'o'),
+(2,'t');
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 VALUES
+(1,'o'),
+(4,'f');
+CREATE VIEW v_t2 AS SELECT * FROM t2;
+CREATE TABLE t3 LIKE t1;
+INSERT INTO t3 VALUES
+(1,'o'),
+(4,'f');
+explain SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+col_varchar_nokey
+o
+PREPARE stmt FROM "SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3)";
+EXECUTE stmt;
+col_varchar_nokey
+o
+DROP VIEW v_t2;
+DROP TABLE t1, t2, t3;
+# End of test for bug#13339643.
 # 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	2011-11-09 09:04:15 +0000
+++ b/mysql-test/r/subquery_sj_mat_bkaunique.result	2011-11-18 10:33:08 +0000
@@ -7114,6 +7114,55 @@ AND grandparent1.col_int_key <> 3
 g1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13334882.
+#
+# Bug#13339643: Assertion on JOIN::flatten_subqueries on second execution
+#
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,'o'),
+(2,'t');
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 VALUES
+(1,'o'),
+(4,'f');
+CREATE VIEW v_t2 AS SELECT * FROM t2;
+CREATE TABLE t3 LIKE t1;
+INSERT INTO t3 VALUES
+(1,'o'),
+(4,'f');
+explain SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+col_varchar_nokey
+o
+PREPARE stmt FROM "SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3)";
+EXECUTE stmt;
+col_varchar_nokey
+o
+DROP VIEW v_t2;
+DROP TABLE t1, t2, t3;
+# End of test for bug#13339643.
 # 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	2011-11-09 09:04:15 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result	2011-11-18 10:33:08 +0000
@@ -7189,5 +7189,54 @@ AND grandparent1.col_int_key <> 3
 g1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13334882.
+#
+# Bug#13339643: Assertion on JOIN::flatten_subqueries on second execution
+#
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,'o'),
+(2,'t');
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 VALUES
+(1,'o'),
+(4,'f');
+CREATE VIEW v_t2 AS SELECT * FROM t2;
+CREATE TABLE t3 LIKE t1;
+INSERT INTO t3 VALUES
+(1,'o'),
+(4,'f');
+explain SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	
+SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+col_varchar_nokey
+o
+PREPARE stmt FROM "SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3)";
+EXECUTE stmt;
+col_varchar_nokey
+o
+DROP VIEW v_t2;
+DROP TABLE t1, t2, t3;
+# End of test for bug#13339643.
 # 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	2011-11-09 09:04:15 +0000
+++ b/mysql-test/r/subquery_sj_none.result	2011-11-18 10:33:08 +0000
@@ -7124,5 +7124,54 @@ AND grandparent1.col_int_key <> 3
 g1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13334882.
+#
+# Bug#13339643: Assertion on JOIN::flatten_subqueries on second execution
+#
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,'o'),
+(2,'t');
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 VALUES
+(1,'o'),
+(4,'f');
+CREATE VIEW v_t2 AS SELECT * FROM t2;
+CREATE TABLE t3 LIKE t1;
+INSERT INTO t3 VALUES
+(1,'o'),
+(4,'f');
+explain SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+col_varchar_nokey
+o
+PREPARE stmt FROM "SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3)";
+EXECUTE stmt;
+col_varchar_nokey
+o
+DROP VIEW v_t2;
+DROP TABLE t1, t2, t3;
+# End of test for bug#13339643.
 # 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	2011-11-09 09:04:15 +0000
+++ b/mysql-test/r/subquery_sj_none_bka.result	2011-11-18 10:33:08 +0000
@@ -7125,6 +7125,55 @@ AND grandparent1.col_int_key <> 3
 g1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13334882.
+#
+# Bug#13339643: Assertion on JOIN::flatten_subqueries on second execution
+#
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,'o'),
+(2,'t');
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 VALUES
+(1,'o'),
+(4,'f');
+CREATE VIEW v_t2 AS SELECT * FROM t2;
+CREATE TABLE t3 LIKE t1;
+INSERT INTO t3 VALUES
+(1,'o'),
+(4,'f');
+explain SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+col_varchar_nokey
+o
+PREPARE stmt FROM "SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3)";
+EXECUTE stmt;
+col_varchar_nokey
+o
+DROP VIEW v_t2;
+DROP TABLE t1, t2, t3;
+# End of test for bug#13339643.
 # 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	2011-11-09 09:04:15 +0000
+++ b/mysql-test/r/subquery_sj_none_bka_nixbnl.result	2011-11-18 10:33:08 +0000
@@ -7125,6 +7125,55 @@ AND grandparent1.col_int_key <> 3
 g1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13334882.
+#
+# Bug#13339643: Assertion on JOIN::flatten_subqueries on second execution
+#
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,'o'),
+(2,'t');
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 VALUES
+(1,'o'),
+(4,'f');
+CREATE VIEW v_t2 AS SELECT * FROM t2;
+CREATE TABLE t3 LIKE t1;
+INSERT INTO t3 VALUES
+(1,'o'),
+(4,'f');
+explain SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+col_varchar_nokey
+o
+PREPARE stmt FROM "SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3)";
+EXECUTE stmt;
+col_varchar_nokey
+o
+DROP VIEW v_t2;
+DROP TABLE t1, t2, t3;
+# End of test for bug#13339643.
 # 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	2011-11-09 09:04:15 +0000
+++ b/mysql-test/r/subquery_sj_none_bkaunique.result	2011-11-18 10:33:08 +0000
@@ -7126,6 +7126,55 @@ AND grandparent1.col_int_key <> 3
 g1
 DROP TABLE t1, t2, t3;
 # End of test for bug#13334882.
+#
+# Bug#13339643: Assertion on JOIN::flatten_subqueries on second execution
+#
+CREATE TABLE t1 (
+col_int_nokey INT,
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+(1,'o'),
+(2,'t');
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 VALUES
+(1,'o'),
+(4,'f');
+CREATE VIEW v_t2 AS SELECT * FROM t2;
+CREATE TABLE t3 LIKE t1;
+INSERT INTO t3 VALUES
+(1,'o'),
+(4,'f');
+explain SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	alias1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3);
+col_varchar_nokey
+o
+PREPARE stmt FROM "SELECT alias1.col_varchar_nokey
+FROM t1 AS alias1
+INNER JOIN v_t2 AS alias2
+ON alias2.col_int_nokey = alias1.col_int_nokey AND
+'o' IN (SELECT col_varchar_nokey
+FROM t3)";
+EXECUTE stmt;
+col_varchar_nokey
+o
+DROP VIEW v_t2;
+DROP TABLE t1, t2, t3;
+# End of test for bug#13339643.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-11-14 10:48:09 +0000
+++ b/sql/sql_select.cc	2011-11-18 10:33:08 +0000
@@ -4587,12 +4587,9 @@ skip_conversion:
           Some precaution is needed when dealing with PS/SP:
           fix_prepare_info_in_table_list() sets prep_on_expr, but only for
           tables, not for join nest objects. This is instead populated in
-          simplify_joins(), which is called after this function. Hence, we need
-          to check that *tree is non-NULL before calling replace_subcondition.
+          simplify_joins(), which is called after this function. The case
+          where *tree is NULL is handled by this procedure.
         */
-        DBUG_ASSERT((*subq)->embedding_join_nest->nested_join ?
-                    *tree == NULL :
-                    *tree != NULL);
       }
       else
         tree= &select_lex->prep_where;

No bundle (reason: useless for push emails).