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).
| Thread |
|---|
| • bzr push into mysql-trunk branch (roy.lyseng:3489 to 3490) Bug#13339643 | Roy Lyseng | 21 Nov |
