3482 Roy Lyseng 2011-11-09
Bug#13334882: assertion table->sort.record_pointers == __null
When assigning ref access methods for a join operation, references
to expressions in an outer query context are also considered.
However, such expressions are not available when materializing a
semi-join nest, so they have to be masked away. This happened only
half-way: OUTER_REF_TABLE_BIT was masked away in
JOIN::set_access_methods() when calling create_ref_for_key(), but it was
not masked away when analyzing ref expressions in best_access_path().
The results was that the ref access object was incomplete when
accessing the storage layer, and an assert was raised.
mysql-test/include/subquery_sj.inc
Added test case for bug#13334882
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#13334882
sql/sql_select.cc
When initializing excluded_tables in Optimize_table_order, add
OUTER_REF_TABLE_BIT as an excluded table.
Also in make_join_select(): make sure that outer tables are not
available when pushing condition parts into a materialized semi-join
nests.
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
3481 Roy Lyseng 2011-11-08
Bug#13335319: Segmentation fault when analyzing FirstMatch semi-join strategy
There was a segmentation fault inside
semijoin_firstmatch_loosescan_access_paths() because it was called
with first_firstmatch_table = MAX_TABLES. The reason for this was that
first_firstmatch_table was copied from the previous position, then
the test (outer_corr_tables & pos->first_firstmatch_rtbl) caused
first_firstmatch_table to be set to MAX_TABLES, but we nevertheless
called the access path calculation function.
The fix is to always copy first_firstmatch_table from the previous
position (the old behaviour was using some heuristics that FirstMatch
would not be considered if FirstMatch was really selected for the
previous position, but it would be considered if a different strategy
was select), and to never call the access path calculation function
if the semi-join tables were dependent on outer tables in
remaining_tables.
mysql-test/include/subquery_sj.inc
Added test case for bug#13335319
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#13335319
sql/sql_select.cc
Assigns pos->first_firstmatch_table unconditionally from
strategy decision from previous position.
Adds the tables of a semi-join nest to pos->firstmatch_need_tables
only once per position.
Assures that semijoin_firstmatch_loosescan_access_paths() is not
called when semi-join nest is dependent on outer table that is not
in the join prefix.
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
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc 2011-11-08 12:06:01 +0000
+++ b/mysql-test/include/subquery_sj.inc 2011-11-09 09:04:15 +0000
@@ -4371,4 +4371,75 @@ DROP TABLE ot1, ot2, it1, it2;
--echo # End of test for bug#13335319.
+--echo #
+--echo # Bug#13334882: Assertion keypart_map failed in MyIsam function
+--echo #
+
+CREATE TABLE t1 (
+ pk int NOT NULL,
+ col_int_nokey INT NOT NULL,
+ col_int_key INT NOT NULL,
+ PRIMARY KEY (pk),
+ KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+
+INSERT INTO t1 VALUES
+(1,4,0),
+(2,6,8),
+(3,3,1),
+(7,2,6),
+(8,9,1),
+(9,3,6),
+(10,8,2),
+(11,1,4),
+(12,8,8),
+(13,8,4),
+(14,5,4);
+
+CREATE TABLE t2 (
+ pk int NOT NULL,
+ col_int_nokey int NOT NULL,
+ col_int_key int NOT NULL,
+ PRIMARY KEY (pk),
+ KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+
+INSERT INTO t2 VALUES
+(10,8,7);
+
+CREATE TABLE t3
+SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+ (SELECT parent1.col_int_key AS p1,
+ parent1.col_int_key AS p2
+ FROM t1 AS parent1
+ LEFT JOIN t2 AS parent2
+ ON parent1.col_int_nokey = parent2.col_int_key
+ )
+ AND grandparent1.col_int_key <> 3
+;
+
+let $query=
+SELECT * FROM t3
+WHERE g1 NOT IN
+ (SELECT grandparent1.col_int_nokey AS g1
+ FROM t1 AS grandparent1
+ WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+ (SELECT parent1.col_int_key AS p1,
+ parent1.col_int_key AS p2
+ FROM t1 AS parent1
+ LEFT JOIN t2 AS parent2
+ ON parent1.col_int_nokey = parent2.col_int_key
+ )
+ AND grandparent1.col_int_key <> 3
+);
+
+eval explain $query;
+eval $query;
+
+DROP TABLE t1, t2, t3;
+
+--echo # End of test for bug#13334882.
+
--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-08 12:06:01 +0000
+++ b/mysql-test/r/subquery_sj_all.result 2011-11-09 09:04:15 +0000
@@ -7035,5 +7035,82 @@ a a
3 1
DROP TABLE ot1, ot2, it1, it2;
# End of test for bug#13335319.
+#
+# Bug#13334882: Assertion keypart_map failed in MyIsam function
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey INT NOT NULL,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(1,4,0),
+(2,6,8),
+(3,3,1),
+(7,2,6),
+(8,9,1),
+(9,3,6),
+(10,8,2),
+(11,1,4),
+(12,8,8),
+(13,8,4),
+(14,5,4);
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(10,8,7);
+CREATE TABLE t3
+SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+;
+explain SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY parent1 ref col_int_key col_int_key 4 func 2 Using index condition; Start temporary
+2 DEPENDENT SUBQUERY parent2 index col_int_key col_int_key 4 NULL 1 Using where; Using index; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY grandparent1 ref col_int_key col_int_key 4 func 2 Using index condition; Using where; End temporary
+SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+g1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13334882.
# 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-08 12:06:01 +0000
+++ b/mysql-test/r/subquery_sj_all_bka.result 2011-11-09 09:04:15 +0000
@@ -7036,6 +7036,83 @@ a a
3 1
DROP TABLE ot1, ot2, it1, it2;
# End of test for bug#13335319.
+#
+# Bug#13334882: Assertion keypart_map failed in MyIsam function
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey INT NOT NULL,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(1,4,0),
+(2,6,8),
+(3,3,1),
+(7,2,6),
+(8,9,1),
+(9,3,6),
+(10,8,2),
+(11,1,4),
+(12,8,8),
+(13,8,4),
+(14,5,4);
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(10,8,7);
+CREATE TABLE t3
+SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+;
+explain SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY parent1 ref col_int_key col_int_key 4 func 2 Using index condition; Start temporary
+2 DEPENDENT SUBQUERY parent2 index col_int_key col_int_key 4 NULL 1 Using where; Using index; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY grandparent1 ref col_int_key col_int_key 4 func 2 Using index condition; Using where; End temporary; Using join buffer (Batched Key Access)
+SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+g1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13334882.
# 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-08 12:06:01 +0000
+++ b/mysql-test/r/subquery_sj_all_bka_nixbnl.result 2011-11-09 09:04:15 +0000
@@ -7036,6 +7036,83 @@ a a
3 1
DROP TABLE ot1, ot2, it1, it2;
# End of test for bug#13335319.
+#
+# Bug#13334882: Assertion keypart_map failed in MyIsam function
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey INT NOT NULL,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(1,4,0),
+(2,6,8),
+(3,3,1),
+(7,2,6),
+(8,9,1),
+(9,3,6),
+(10,8,2),
+(11,1,4),
+(12,8,8),
+(13,8,4),
+(14,5,4);
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(10,8,7);
+CREATE TABLE t3
+SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+;
+explain SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY grandparent1 ref col_int_key col_int_key 4 func 2 Using index condition; Using where
+2 DEPENDENT SUBQUERY parent1 ref col_int_key col_int_key 4 func 2 Using index condition
+2 DEPENDENT SUBQUERY parent2 ref col_int_key col_int_key 4 test.parent1.col_int_nokey 2 Using index; FirstMatch(grandparent1)
+SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+g1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13334882.
# 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-08 12:06:01 +0000
+++ b/mysql-test/r/subquery_sj_all_bkaunique.result 2011-11-09 09:04:15 +0000
@@ -7037,6 +7037,83 @@ a a
3 1
DROP TABLE ot1, ot2, it1, it2;
# End of test for bug#13335319.
+#
+# Bug#13334882: Assertion keypart_map failed in MyIsam function
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey INT NOT NULL,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(1,4,0),
+(2,6,8),
+(3,3,1),
+(7,2,6),
+(8,9,1),
+(9,3,6),
+(10,8,2),
+(11,1,4),
+(12,8,8),
+(13,8,4),
+(14,5,4);
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(10,8,7);
+CREATE TABLE t3
+SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+;
+explain SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY parent1 ref col_int_key col_int_key 4 func 2 Using index condition; Start temporary
+2 DEPENDENT SUBQUERY parent2 index col_int_key col_int_key 4 NULL 1 Using where; Using index; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY grandparent1 ref col_int_key col_int_key 4 func 2 Using index condition; Using where; End temporary; Using join buffer (Batched Key Access (unique))
+SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+g1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13334882.
# 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-08 12:06:01 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result 2011-11-09 09:04:15 +0000
@@ -7035,5 +7035,82 @@ a a
3 1
DROP TABLE ot1, ot2, it1, it2;
# End of test for bug#13335319.
+#
+# Bug#13334882: Assertion keypart_map failed in MyIsam function
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey INT NOT NULL,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(1,4,0),
+(2,6,8),
+(3,3,1),
+(7,2,6),
+(8,9,1),
+(9,3,6),
+(10,8,2),
+(11,1,4),
+(12,8,8),
+(13,8,4),
+(14,5,4);
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(10,8,7);
+CREATE TABLE t3
+SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+;
+explain SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY parent1 ref col_int_key col_int_key 4 func 2 Using where; Start temporary
+2 DEPENDENT SUBQUERY parent2 index col_int_key col_int_key 4 NULL 1 Using where; Using index; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY grandparent1 ref col_int_key col_int_key 4 func 2 Using where; End temporary
+SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+g1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13334882.
# 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-08 12:06:01 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka.result 2011-11-09 09:04:15 +0000
@@ -7036,6 +7036,83 @@ a a
3 1
DROP TABLE ot1, ot2, it1, it2;
# End of test for bug#13335319.
+#
+# Bug#13334882: Assertion keypart_map failed in MyIsam function
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey INT NOT NULL,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(1,4,0),
+(2,6,8),
+(3,3,1),
+(7,2,6),
+(8,9,1),
+(9,3,6),
+(10,8,2),
+(11,1,4),
+(12,8,8),
+(13,8,4),
+(14,5,4);
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(10,8,7);
+CREATE TABLE t3
+SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+;
+explain SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY parent1 ref col_int_key col_int_key 4 func 2 Using where; Start temporary
+2 DEPENDENT SUBQUERY parent2 index col_int_key col_int_key 4 NULL 1 Using where; Using index; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY grandparent1 ref col_int_key col_int_key 4 func 2 Using where; End temporary
+SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+g1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13334882.
# 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-08 12:06:01 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result 2011-11-09 09:04:15 +0000
@@ -7036,6 +7036,83 @@ a a
3 1
DROP TABLE ot1, ot2, it1, it2;
# End of test for bug#13335319.
+#
+# Bug#13334882: Assertion keypart_map failed in MyIsam function
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey INT NOT NULL,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(1,4,0),
+(2,6,8),
+(3,3,1),
+(7,2,6),
+(8,9,1),
+(9,3,6),
+(10,8,2),
+(11,1,4),
+(12,8,8),
+(13,8,4),
+(14,5,4);
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(10,8,7);
+CREATE TABLE t3
+SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+;
+explain SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY grandparent1 ref col_int_key col_int_key 4 func 2 Using where; Start temporary
+2 DEPENDENT SUBQUERY parent1 ref col_int_key col_int_key 4 func 2 Using where
+2 DEPENDENT SUBQUERY parent2 ref col_int_key col_int_key 4 test.parent1.col_int_nokey 2 Using index; End temporary
+SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+g1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13334882.
# 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-08 12:06:01 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bkaunique.result 2011-11-09 09:04:15 +0000
@@ -7037,6 +7037,83 @@ a a
3 1
DROP TABLE ot1, ot2, it1, it2;
# End of test for bug#13335319.
+#
+# Bug#13334882: Assertion keypart_map failed in MyIsam function
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey INT NOT NULL,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(1,4,0),
+(2,6,8),
+(3,3,1),
+(7,2,6),
+(8,9,1),
+(9,3,6),
+(10,8,2),
+(11,1,4),
+(12,8,8),
+(13,8,4),
+(14,5,4);
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(10,8,7);
+CREATE TABLE t3
+SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+;
+explain SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY parent1 ref col_int_key col_int_key 4 func 2 Using where; Start temporary
+2 DEPENDENT SUBQUERY parent2 index col_int_key col_int_key 4 NULL 1 Using where; Using index; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY grandparent1 ref col_int_key col_int_key 4 func 2 Using where; End temporary
+SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+g1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13334882.
# 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-08 12:06:01 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result 2011-11-09 09:04:15 +0000
@@ -7036,6 +7036,83 @@ a a
3 1
DROP TABLE ot1, ot2, it1, it2;
# End of test for bug#13335319.
+#
+# Bug#13334882: Assertion keypart_map failed in MyIsam function
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey INT NOT NULL,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(1,4,0),
+(2,6,8),
+(3,3,1),
+(7,2,6),
+(8,9,1),
+(9,3,6),
+(10,8,2),
+(11,1,4),
+(12,8,8),
+(13,8,4),
+(14,5,4);
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(10,8,7);
+CREATE TABLE t3
+SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+;
+explain SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY parent1 ref col_int_key col_int_key 4 func 2 Using where; Start temporary
+2 DEPENDENT SUBQUERY parent2 index col_int_key col_int_key 4 NULL 1 Using where; Using index; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY grandparent1 ref col_int_key col_int_key 4 func 2 Using where; End temporary
+SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+g1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13334882.
# 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-08 12:06:01 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka.result 2011-11-09 09:04:15 +0000
@@ -7037,6 +7037,83 @@ a a
3 1
DROP TABLE ot1, ot2, it1, it2;
# End of test for bug#13335319.
+#
+# Bug#13334882: Assertion keypart_map failed in MyIsam function
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey INT NOT NULL,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(1,4,0),
+(2,6,8),
+(3,3,1),
+(7,2,6),
+(8,9,1),
+(9,3,6),
+(10,8,2),
+(11,1,4),
+(12,8,8),
+(13,8,4),
+(14,5,4);
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(10,8,7);
+CREATE TABLE t3
+SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+;
+explain SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY parent1 ref col_int_key col_int_key 4 func 2 Using where; Start temporary
+2 DEPENDENT SUBQUERY parent2 index col_int_key col_int_key 4 NULL 1 Using where; Using index; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY grandparent1 ref col_int_key col_int_key 4 func 2 Using where; End temporary
+SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+g1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13334882.
# 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-08 12:06:01 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result 2011-11-09 09:04:15 +0000
@@ -7037,6 +7037,83 @@ a a
3 1
DROP TABLE ot1, ot2, it1, it2;
# End of test for bug#13335319.
+#
+# Bug#13334882: Assertion keypart_map failed in MyIsam function
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey INT NOT NULL,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(1,4,0),
+(2,6,8),
+(3,3,1),
+(7,2,6),
+(8,9,1),
+(9,3,6),
+(10,8,2),
+(11,1,4),
+(12,8,8),
+(13,8,4),
+(14,5,4);
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(10,8,7);
+CREATE TABLE t3
+SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+;
+explain SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY grandparent1 ref col_int_key col_int_key 4 func 2 Using where
+2 DEPENDENT SUBQUERY parent1 ref col_int_key col_int_key 4 func 2 Using where
+2 DEPENDENT SUBQUERY parent2 ref col_int_key col_int_key 4 test.parent1.col_int_nokey 2 Using index; FirstMatch(grandparent1)
+SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+g1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13334882.
# 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-08 12:06:01 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bkaunique.result 2011-11-09 09:04:15 +0000
@@ -7038,6 +7038,83 @@ a a
3 1
DROP TABLE ot1, ot2, it1, it2;
# End of test for bug#13335319.
+#
+# Bug#13334882: Assertion keypart_map failed in MyIsam function
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey INT NOT NULL,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(1,4,0),
+(2,6,8),
+(3,3,1),
+(7,2,6),
+(8,9,1),
+(9,3,6),
+(10,8,2),
+(11,1,4),
+(12,8,8),
+(13,8,4),
+(14,5,4);
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(10,8,7);
+CREATE TABLE t3
+SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+;
+explain SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY parent1 ref col_int_key col_int_key 4 func 2 Using where; Start temporary
+2 DEPENDENT SUBQUERY parent2 index col_int_key col_int_key 4 NULL 1 Using where; Using index; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY grandparent1 ref col_int_key col_int_key 4 func 2 Using where; End temporary
+SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+g1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13334882.
# 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-08 12:06:01 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result 2011-11-09 09:04:15 +0000
@@ -7036,5 +7036,82 @@ a a
3 1
DROP TABLE ot1, ot2, it1, it2;
# End of test for bug#13335319.
+#
+# Bug#13334882: Assertion keypart_map failed in MyIsam function
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey INT NOT NULL,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(1,4,0),
+(2,6,8),
+(3,3,1),
+(7,2,6),
+(8,9,1),
+(9,3,6),
+(10,8,2),
+(11,1,4),
+(12,8,8),
+(13,8,4),
+(14,5,4);
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(10,8,7);
+CREATE TABLE t3
+SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+;
+explain SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY parent1 ref col_int_key col_int_key 4 func 2 Using where; Start temporary
+2 DEPENDENT SUBQUERY parent2 index col_int_key col_int_key 4 NULL 1 Using where; Using index; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY grandparent1 ref col_int_key col_int_key 4 func 2 Using where; End temporary
+SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+g1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13334882.
# 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-08 12:06:01 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka.result 2011-11-09 09:04:15 +0000
@@ -7037,6 +7037,83 @@ a a
3 1
DROP TABLE ot1, ot2, it1, it2;
# End of test for bug#13335319.
+#
+# Bug#13334882: Assertion keypart_map failed in MyIsam function
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey INT NOT NULL,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(1,4,0),
+(2,6,8),
+(3,3,1),
+(7,2,6),
+(8,9,1),
+(9,3,6),
+(10,8,2),
+(11,1,4),
+(12,8,8),
+(13,8,4),
+(14,5,4);
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(10,8,7);
+CREATE TABLE t3
+SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+;
+explain SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY parent1 ref col_int_key col_int_key 4 func 2 Using where; Start temporary
+2 DEPENDENT SUBQUERY parent2 index col_int_key col_int_key 4 NULL 1 Using where; Using index; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY grandparent1 ref col_int_key col_int_key 4 func 2 Using where; End temporary
+SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+g1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13334882.
# 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-08 12:06:01 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result 2011-11-09 09:04:15 +0000
@@ -7037,6 +7037,83 @@ a a
3 1
DROP TABLE ot1, ot2, it1, it2;
# End of test for bug#13335319.
+#
+# Bug#13334882: Assertion keypart_map failed in MyIsam function
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey INT NOT NULL,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(1,4,0),
+(2,6,8),
+(3,3,1),
+(7,2,6),
+(8,9,1),
+(9,3,6),
+(10,8,2),
+(11,1,4),
+(12,8,8),
+(13,8,4),
+(14,5,4);
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(10,8,7);
+CREATE TABLE t3
+SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+;
+explain SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY grandparent1 ref col_int_key col_int_key 4 func 2 Using where; Start temporary
+2 DEPENDENT SUBQUERY parent1 ref col_int_key col_int_key 4 func 2 Using where
+2 DEPENDENT SUBQUERY parent2 ref col_int_key col_int_key 4 test.parent1.col_int_nokey 2 Using index; End temporary
+SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+g1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13334882.
# 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-08 12:06:01 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bkaunique.result 2011-11-09 09:04:15 +0000
@@ -7038,6 +7038,83 @@ a a
3 1
DROP TABLE ot1, ot2, it1, it2;
# End of test for bug#13335319.
+#
+# Bug#13334882: Assertion keypart_map failed in MyIsam function
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey INT NOT NULL,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(1,4,0),
+(2,6,8),
+(3,3,1),
+(7,2,6),
+(8,9,1),
+(9,3,6),
+(10,8,2),
+(11,1,4),
+(12,8,8),
+(13,8,4),
+(14,5,4);
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(10,8,7);
+CREATE TABLE t3
+SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+;
+explain SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY parent1 ref col_int_key col_int_key 4 func 2 Using where; Start temporary
+2 DEPENDENT SUBQUERY parent2 index col_int_key col_int_key 4 NULL 1 Using where; Using index; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY grandparent1 ref col_int_key col_int_key 4 func 2 Using where; End temporary
+SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+g1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13334882.
# 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-08 12:06:01 +0000
+++ b/mysql-test/r/subquery_sj_mat.result 2011-11-09 09:04:15 +0000
@@ -7035,5 +7035,82 @@ a a
3 1
DROP TABLE ot1, ot2, it1, it2;
# End of test for bug#13335319.
+#
+# Bug#13334882: Assertion keypart_map failed in MyIsam function
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey INT NOT NULL,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(1,4,0),
+(2,6,8),
+(3,3,1),
+(7,2,6),
+(8,9,1),
+(9,3,6),
+(10,8,2),
+(11,1,4),
+(12,8,8),
+(13,8,4),
+(14,5,4);
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(10,8,7);
+CREATE TABLE t3
+SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+;
+explain SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY parent1 ref col_int_key col_int_key 4 func 2 Using where; Start temporary
+2 DEPENDENT SUBQUERY parent2 index col_int_key col_int_key 4 NULL 1 Using where; Using index; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY grandparent1 ref col_int_key col_int_key 4 func 2 Using where; End temporary
+SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+g1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13334882.
# 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-08 12:06:01 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka.result 2011-11-09 09:04:15 +0000
@@ -7036,6 +7036,83 @@ a a
3 1
DROP TABLE ot1, ot2, it1, it2;
# End of test for bug#13335319.
+#
+# Bug#13334882: Assertion keypart_map failed in MyIsam function
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey INT NOT NULL,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(1,4,0),
+(2,6,8),
+(3,3,1),
+(7,2,6),
+(8,9,1),
+(9,3,6),
+(10,8,2),
+(11,1,4),
+(12,8,8),
+(13,8,4),
+(14,5,4);
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(10,8,7);
+CREATE TABLE t3
+SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+;
+explain SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY parent1 ref col_int_key col_int_key 4 func 2 Using where; Start temporary
+2 DEPENDENT SUBQUERY parent2 index col_int_key col_int_key 4 NULL 1 Using where; Using index; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY grandparent1 ref col_int_key col_int_key 4 func 2 Using where; End temporary
+SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+g1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13334882.
# 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-08 12:06:01 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka_nixbnl.result 2011-11-09 09:04:15 +0000
@@ -7036,6 +7036,83 @@ a a
3 1
DROP TABLE ot1, ot2, it1, it2;
# End of test for bug#13335319.
+#
+# Bug#13334882: Assertion keypart_map failed in MyIsam function
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey INT NOT NULL,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(1,4,0),
+(2,6,8),
+(3,3,1),
+(7,2,6),
+(8,9,1),
+(9,3,6),
+(10,8,2),
+(11,1,4),
+(12,8,8),
+(13,8,4),
+(14,5,4);
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(10,8,7);
+CREATE TABLE t3
+SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+;
+explain SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY grandparent1 ref col_int_key col_int_key 4 func 2 Using where; Start temporary
+2 DEPENDENT SUBQUERY parent1 ref col_int_key col_int_key 4 func 2 Using where
+2 DEPENDENT SUBQUERY parent2 ref col_int_key col_int_key 4 test.parent1.col_int_nokey 2 Using index; End temporary
+SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+g1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13334882.
# 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-08 12:06:01 +0000
+++ b/mysql-test/r/subquery_sj_mat_bkaunique.result 2011-11-09 09:04:15 +0000
@@ -7037,6 +7037,83 @@ a a
3 1
DROP TABLE ot1, ot2, it1, it2;
# End of test for bug#13335319.
+#
+# Bug#13334882: Assertion keypart_map failed in MyIsam function
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey INT NOT NULL,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(1,4,0),
+(2,6,8),
+(3,3,1),
+(7,2,6),
+(8,9,1),
+(9,3,6),
+(10,8,2),
+(11,1,4),
+(12,8,8),
+(13,8,4),
+(14,5,4);
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(10,8,7);
+CREATE TABLE t3
+SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+;
+explain SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY parent1 ref col_int_key col_int_key 4 func 2 Using where; Start temporary
+2 DEPENDENT SUBQUERY parent2 index col_int_key col_int_key 4 NULL 1 Using where; Using index; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY grandparent1 ref col_int_key col_int_key 4 func 2 Using where; End temporary
+SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+g1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13334882.
# 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-08 12:06:01 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result 2011-11-09 09:04:15 +0000
@@ -7112,5 +7112,82 @@ a a
3 1
DROP TABLE ot1, ot2, it1, it2;
# End of test for bug#13335319.
+#
+# Bug#13334882: Assertion keypart_map failed in MyIsam function
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey INT NOT NULL,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(1,4,0),
+(2,6,8),
+(3,3,1),
+(7,2,6),
+(8,9,1),
+(9,3,6),
+(10,8,2),
+(11,1,4),
+(12,8,8),
+(13,8,4),
+(14,5,4);
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(10,8,7);
+CREATE TABLE t3
+SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+;
+explain SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY grandparent1 ALL col_int_key NULL NULL NULL 11 Using where
+3 SUBQUERY parent1 ALL NULL NULL NULL NULL 11
+3 SUBQUERY parent2 index col_int_key col_int_key 4 NULL 1 Using where; Using index; Using join buffer (Block Nested Loop)
+SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+g1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13334882.
# 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-08 12:06:01 +0000
+++ b/mysql-test/r/subquery_sj_none.result 2011-11-09 09:04:15 +0000
@@ -7047,5 +7047,82 @@ a a
3 1
DROP TABLE ot1, ot2, it1, it2;
# End of test for bug#13335319.
+#
+# Bug#13334882: Assertion keypart_map failed in MyIsam function
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey INT NOT NULL,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(1,4,0),
+(2,6,8),
+(3,3,1),
+(7,2,6),
+(8,9,1),
+(9,3,6),
+(10,8,2),
+(11,1,4),
+(12,8,8),
+(13,8,4),
+(14,5,4);
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(10,8,7);
+CREATE TABLE t3
+SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+;
+explain SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY grandparent1 ALL col_int_key NULL NULL NULL 11 Using where
+3 DEPENDENT SUBQUERY parent1 ref col_int_key col_int_key 4 func 2 Using where
+3 DEPENDENT SUBQUERY parent2 index col_int_key col_int_key 4 NULL 1 Using where; Using index; Using join buffer (Block Nested Loop)
+SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+g1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13334882.
# 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-08 12:06:01 +0000
+++ b/mysql-test/r/subquery_sj_none_bka.result 2011-11-09 09:04:15 +0000
@@ -7048,6 +7048,83 @@ a a
3 1
DROP TABLE ot1, ot2, it1, it2;
# End of test for bug#13335319.
+#
+# Bug#13334882: Assertion keypart_map failed in MyIsam function
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey INT NOT NULL,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(1,4,0),
+(2,6,8),
+(3,3,1),
+(7,2,6),
+(8,9,1),
+(9,3,6),
+(10,8,2),
+(11,1,4),
+(12,8,8),
+(13,8,4),
+(14,5,4);
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(10,8,7);
+CREATE TABLE t3
+SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+;
+explain SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY grandparent1 ALL col_int_key NULL NULL NULL 11 Using where
+3 DEPENDENT SUBQUERY parent1 ref col_int_key col_int_key 4 func 2 Using where
+3 DEPENDENT SUBQUERY parent2 index col_int_key col_int_key 4 NULL 1 Using where; Using index; Using join buffer (Block Nested Loop)
+SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+g1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13334882.
# 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-08 12:06:01 +0000
+++ b/mysql-test/r/subquery_sj_none_bka_nixbnl.result 2011-11-09 09:04:15 +0000
@@ -7048,6 +7048,83 @@ a a
3 1
DROP TABLE ot1, ot2, it1, it2;
# End of test for bug#13335319.
+#
+# Bug#13334882: Assertion keypart_map failed in MyIsam function
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey INT NOT NULL,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(1,4,0),
+(2,6,8),
+(3,3,1),
+(7,2,6),
+(8,9,1),
+(9,3,6),
+(10,8,2),
+(11,1,4),
+(12,8,8),
+(13,8,4),
+(14,5,4);
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(10,8,7);
+CREATE TABLE t3
+SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+;
+explain SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY grandparent1 ALL col_int_key NULL NULL NULL 11 Using where
+3 DEPENDENT SUBQUERY parent1 ref col_int_key col_int_key 4 func 2 Using where
+3 DEPENDENT SUBQUERY parent2 ref col_int_key col_int_key 4 test.parent1.col_int_nokey 2 Using index
+SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+g1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13334882.
# 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-08 12:06:01 +0000
+++ b/mysql-test/r/subquery_sj_none_bkaunique.result 2011-11-09 09:04:15 +0000
@@ -7049,6 +7049,83 @@ a a
3 1
DROP TABLE ot1, ot2, it1, it2;
# End of test for bug#13335319.
+#
+# Bug#13334882: Assertion keypart_map failed in MyIsam function
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_nokey INT NOT NULL,
+col_int_key INT NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(1,4,0),
+(2,6,8),
+(3,3,1),
+(7,2,6),
+(8,9,1),
+(9,3,6),
+(10,8,2),
+(11,1,4),
+(12,8,8),
+(13,8,4),
+(14,5,4);
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES
+(10,8,7);
+CREATE TABLE t3
+SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+;
+explain SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
+2 DEPENDENT SUBQUERY grandparent1 ALL col_int_key NULL NULL NULL 11 Using where
+3 DEPENDENT SUBQUERY parent1 ref col_int_key col_int_key 4 func 2 Using where
+3 DEPENDENT SUBQUERY parent2 index col_int_key col_int_key 4 NULL 1 Using where; Using index; Using join buffer (Block Nested Loop)
+SELECT * FROM t3
+WHERE g1 NOT IN
+(SELECT grandparent1.col_int_nokey AS g1
+FROM t1 AS grandparent1
+WHERE (grandparent1.col_int_nokey, grandparent1.col_int_key) IN
+(SELECT parent1.col_int_key AS p1,
+parent1.col_int_key AS p2
+FROM t1 AS parent1
+LEFT JOIN t2 AS parent2
+ON parent1.col_int_nokey = parent2.col_int_key
+)
+AND grandparent1.col_int_key <> 3
+);
+g1
+DROP TABLE t1, t2, t3;
+# End of test for bug#13334882.
# 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-08 12:06:01 +0000
+++ b/sql/sql_select.cc 2011-11-09 09:04:15 +0000
@@ -287,7 +287,8 @@ public:
thd(thd), join(join),
cur_embedding_map(0), cur_sj_inner_tables(0), emb_sjm_nest(sjm_nest),
excluded_tables(sjm_nest ?
- join->all_table_map & ~sjm_nest->sj_inner_tables :
+ (join->all_table_map & ~sjm_nest->sj_inner_tables) |
+ OUTER_REF_TABLE_BIT :
0)
{}
~Optimize_table_order()
@@ -10953,7 +10954,7 @@ static bool make_join_select(JOIN *join,
!(used_tables & tab->emb_sj_nest->sj_inner_tables))
{
save_used_tables= used_tables;
- used_tables= join->const_table_map | OUTER_REF_TABLE_BIT;
+ used_tables= join->const_table_map;
}
used_tables|= current_map;
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (roy.lyseng:3481 to 3482) Bug#13334882 | Roy Lyseng | 11 Nov |
