3475 Roy Lyseng 2011-11-07
Bug#13340270: assertion table->sort.record_pointers == __null
bug#12694872 fixed a memory leak in Unique::get(), but did not expect
Unique::get() to be called twice before the end of the query, where
buffers are freed.
The solution to this problem is to free the filesort buffers if the
Unique object is reused.
mysql-test/include/subquery_sj.inc
Added test case for bug#13340270
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#13340270
sql/opt_range.cc
In QUICK_INDEX_MERGE_SELECT::read_keys_and_merge(), delete existing
filesort buffers when reusing a Unique buffer.
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/opt_range.cc
3474 Guilhem Bichot 2011-11-02
fix for Bug#13335170 - ASSERT IN PLAN_CHANGE_WATCHDOG::~PLAN_CHANGE_WATCHDOG() ON SELECT DISTINCT
the "no_changes" parameter of test_if_skip_sort_order() was not honoured.
@ sql/sql_select.cc
The bug is in test_if_skip_sort_order(): tab->select->quick
is not NULL upon entrance, then in this block:
{
uint best_key_parts= 0;
...
if (best_key >= 0)
{
...
select->quick= 0;
select->test_quick_select(join->thd, ...
the assignment sets tab->select->quick to NULL. Even if "no_changes" is true.
Thus, we have a plan change which is not allowed, and Plan_change_watchdog barks...
The fix is to always restore to "save_quick" is "no_changes" is true.
Misc other changes:
- return before setting local variables
- improving one comment
modified:
mysql-test/r/distinct.result
mysql-test/t/distinct.test
sql/sql_select.cc
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc 2011-10-24 14:01:11 +0000
+++ b/mysql-test/include/subquery_sj.inc 2011-11-07 14:00:01 +0000
@@ -4286,4 +4286,56 @@ DROP TABLE t1, t2, t4;
--echo # End of test for bug#12664936.
+--echo #
+--echo # Bug#13340270: assertion table->sort.record_pointers == __null
+--echo #
+
+CREATE TABLE t1 (
+ pk int NOT NULL,
+ col_int_key int DEFAULT NULL,
+ col_varchar_key varchar(1) DEFAULT NULL,
+ col_varchar_nokey varchar(1) DEFAULT NULL,
+ PRIMARY KEY (pk),
+ KEY col_int_key (col_int_key),
+ KEY col_varchar_key (col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES
+(10,8,'x','x'),
+(11,7,'d','d'),
+(12,1,'r','r'),
+(13,7,'f','f'),
+(14,9,'y','y'),
+(15,NULL,'u','u'),
+(16,1,'m','m'),
+(17,9,NULL,NULL),
+(18,2,'o','o'),
+(19,9,'w','w'),
+(20,2,'m','m'),
+(21,4,'q','q');
+
+let $query=
+ SELECT alias1.col_varchar_nokey AS field1
+ FROM t1 AS alias1 JOIN t1 AS alias2
+ ON alias2.col_int_key = alias1.pk OR
+ alias2.col_int_key = alias1.col_int_key
+ WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+;
+
+eval CREATE TABLE t2
+ $query
+;
+
+eval EXPLAIN SELECT *
+FROM t2
+WHERE (field1) IN ($query);
+
+eval SELECT *
+FROM t2
+WHERE (field1) IN ($query);
+
+DROP TABLE t1, t2;
+
+--echo # End of test for bug#13340270.
+
--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-03 14:17:25 +0000
+++ b/mysql-test/r/subquery_sj_all.result 2011-11-07 14:00:01 +0000
@@ -6948,5 +6948,63 @@ col_int_nokey col_varchar_key
2 q
DROP TABLE t1, t2, t4;
# End of test for bug#12664936.
+#
+# Bug#13340270: assertion table->sort.record_pointers == __null
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(10,8,'x','x'),
+(11,7,'d','d'),
+(12,1,'r','r'),
+(13,7,'f','f'),
+(14,9,'y','y'),
+(15,NULL,'u','u'),
+(16,1,'m','m'),
+(17,9,NULL,NULL),
+(18,2,'o','o'),
+(19,9,'w','w'),
+(20,2,'m','m'),
+(21,4,'q','q');
+CREATE TABLE t2
+SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+
+;
+EXPLAIN SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2
+1 PRIMARY alias1 index_merge PRIMARY,col_int_key,col_varchar_key col_varchar_key,PRIMARY 4,4 NULL 2 Using sort_union(col_varchar_key,PRIMARY); Using where; Start materialize
+1 PRIMARY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2); End materialize
+SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+field1
+o
+o
+DROP TABLE t1, t2;
+# End of test for bug#13340270.
# 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-03 14:17:25 +0000
+++ b/mysql-test/r/subquery_sj_all_bka.result 2011-11-07 14:00:01 +0000
@@ -6949,6 +6949,64 @@ col_int_nokey col_varchar_key
2 q
DROP TABLE t1, t2, t4;
# End of test for bug#12664936.
+#
+# Bug#13340270: assertion table->sort.record_pointers == __null
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(10,8,'x','x'),
+(11,7,'d','d'),
+(12,1,'r','r'),
+(13,7,'f','f'),
+(14,9,'y','y'),
+(15,NULL,'u','u'),
+(16,1,'m','m'),
+(17,9,NULL,NULL),
+(18,2,'o','o'),
+(19,9,'w','w'),
+(20,2,'m','m'),
+(21,4,'q','q');
+CREATE TABLE t2
+SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+
+;
+EXPLAIN SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2
+1 PRIMARY alias1 index_merge PRIMARY,col_int_key,col_varchar_key col_varchar_key,PRIMARY 4,4 NULL 2 Using sort_union(col_varchar_key,PRIMARY); Using where; Start materialize
+1 PRIMARY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2); End materialize
+SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+field1
+o
+o
+DROP TABLE t1, t2;
+# End of test for bug#13340270.
# 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-03 14:17:25 +0000
+++ b/mysql-test/r/subquery_sj_all_bka_nixbnl.result 2011-11-07 14:00:01 +0000
@@ -6949,6 +6949,64 @@ col_int_nokey col_varchar_key
2 q
DROP TABLE t1, t2, t4;
# End of test for bug#12664936.
+#
+# Bug#13340270: assertion table->sort.record_pointers == __null
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(10,8,'x','x'),
+(11,7,'d','d'),
+(12,1,'r','r'),
+(13,7,'f','f'),
+(14,9,'y','y'),
+(15,NULL,'u','u'),
+(16,1,'m','m'),
+(17,9,NULL,NULL),
+(18,2,'o','o'),
+(19,9,'w','w'),
+(20,2,'m','m'),
+(21,4,'q','q');
+CREATE TABLE t2
+SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+
+;
+EXPLAIN SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2
+1 PRIMARY alias1 index_merge PRIMARY,col_int_key,col_varchar_key col_varchar_key,PRIMARY 4,4 NULL 2 Using sort_union(col_varchar_key,PRIMARY); Using where; Start materialize
+1 PRIMARY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2); End materialize
+SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+field1
+o
+o
+DROP TABLE t1, t2;
+# End of test for bug#13340270.
# 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-03 14:17:25 +0000
+++ b/mysql-test/r/subquery_sj_all_bkaunique.result 2011-11-07 14:00:01 +0000
@@ -6950,6 +6950,64 @@ col_int_nokey col_varchar_key
2 q
DROP TABLE t1, t2, t4;
# End of test for bug#12664936.
+#
+# Bug#13340270: assertion table->sort.record_pointers == __null
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(10,8,'x','x'),
+(11,7,'d','d'),
+(12,1,'r','r'),
+(13,7,'f','f'),
+(14,9,'y','y'),
+(15,NULL,'u','u'),
+(16,1,'m','m'),
+(17,9,NULL,NULL),
+(18,2,'o','o'),
+(19,9,'w','w'),
+(20,2,'m','m'),
+(21,4,'q','q');
+CREATE TABLE t2
+SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+
+;
+EXPLAIN SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2
+1 PRIMARY alias1 index_merge PRIMARY,col_int_key,col_varchar_key col_varchar_key,PRIMARY 4,4 NULL 2 Using sort_union(col_varchar_key,PRIMARY); Using where; Start materialize
+1 PRIMARY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2); End materialize
+SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+field1
+o
+o
+DROP TABLE t1, t2;
+# End of test for bug#13340270.
# 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-03 14:17:25 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result 2011-11-07 14:00:01 +0000
@@ -6948,5 +6948,63 @@ col_int_nokey col_varchar_key
2 q
DROP TABLE t1, t2, t4;
# End of test for bug#12664936.
+#
+# Bug#13340270: assertion table->sort.record_pointers == __null
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(10,8,'x','x'),
+(11,7,'d','d'),
+(12,1,'r','r'),
+(13,7,'f','f'),
+(14,9,'y','y'),
+(15,NULL,'u','u'),
+(16,1,'m','m'),
+(17,9,NULL,NULL),
+(18,2,'o','o'),
+(19,9,'w','w'),
+(20,2,'m','m'),
+(21,4,'q','q');
+CREATE TABLE t2
+SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+
+;
+EXPLAIN SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY alias1 index_merge PRIMARY,col_int_key,col_varchar_key col_varchar_key,PRIMARY 4,4 NULL 2 Using sort_union(col_varchar_key,PRIMARY); Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2); End temporary
+SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+field1
+o
+o
+DROP TABLE t1, t2;
+# End of test for bug#13340270.
# 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-03 14:17:25 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka.result 2011-11-07 14:00:01 +0000
@@ -6949,6 +6949,64 @@ col_int_nokey col_varchar_key
2 q
DROP TABLE t1, t2, t4;
# End of test for bug#12664936.
+#
+# Bug#13340270: assertion table->sort.record_pointers == __null
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(10,8,'x','x'),
+(11,7,'d','d'),
+(12,1,'r','r'),
+(13,7,'f','f'),
+(14,9,'y','y'),
+(15,NULL,'u','u'),
+(16,1,'m','m'),
+(17,9,NULL,NULL),
+(18,2,'o','o'),
+(19,9,'w','w'),
+(20,2,'m','m'),
+(21,4,'q','q');
+CREATE TABLE t2
+SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+
+;
+EXPLAIN SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY alias1 index_merge PRIMARY,col_int_key,col_varchar_key col_varchar_key,PRIMARY 4,4 NULL 2 Using sort_union(col_varchar_key,PRIMARY); Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2); End temporary
+SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+field1
+o
+o
+DROP TABLE t1, t2;
+# End of test for bug#13340270.
# 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-03 14:17:25 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result 2011-11-07 14:00:01 +0000
@@ -6949,6 +6949,64 @@ col_int_nokey col_varchar_key
2 q
DROP TABLE t1, t2, t4;
# End of test for bug#12664936.
+#
+# Bug#13340270: assertion table->sort.record_pointers == __null
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(10,8,'x','x'),
+(11,7,'d','d'),
+(12,1,'r','r'),
+(13,7,'f','f'),
+(14,9,'y','y'),
+(15,NULL,'u','u'),
+(16,1,'m','m'),
+(17,9,NULL,NULL),
+(18,2,'o','o'),
+(19,9,'w','w'),
+(20,2,'m','m'),
+(21,4,'q','q');
+CREATE TABLE t2
+SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+
+;
+EXPLAIN SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2
+1 PRIMARY alias1 index_merge PRIMARY,col_int_key,col_varchar_key col_varchar_key,PRIMARY 4,4 NULL 2 Using sort_union(col_varchar_key,PRIMARY); Using where; Start temporary
+1 PRIMARY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2); End temporary
+SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+field1
+o
+o
+DROP TABLE t1, t2;
+# End of test for bug#13340270.
# 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-03 14:17:25 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bkaunique.result 2011-11-07 14:00:01 +0000
@@ -6950,6 +6950,64 @@ col_int_nokey col_varchar_key
2 q
DROP TABLE t1, t2, t4;
# End of test for bug#12664936.
+#
+# Bug#13340270: assertion table->sort.record_pointers == __null
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(10,8,'x','x'),
+(11,7,'d','d'),
+(12,1,'r','r'),
+(13,7,'f','f'),
+(14,9,'y','y'),
+(15,NULL,'u','u'),
+(16,1,'m','m'),
+(17,9,NULL,NULL),
+(18,2,'o','o'),
+(19,9,'w','w'),
+(20,2,'m','m'),
+(21,4,'q','q');
+CREATE TABLE t2
+SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+
+;
+EXPLAIN SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY alias1 index_merge PRIMARY,col_int_key,col_varchar_key col_varchar_key,PRIMARY 4,4 NULL 2 Using sort_union(col_varchar_key,PRIMARY); Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2); End temporary
+SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+field1
+o
+o
+DROP TABLE t1, t2;
+# End of test for bug#13340270.
# 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-03 14:17:25 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result 2011-11-07 14:00:01 +0000
@@ -6949,6 +6949,64 @@ col_int_nokey col_varchar_key
2 q
DROP TABLE t1, t2, t4;
# End of test for bug#12664936.
+#
+# Bug#13340270: assertion table->sort.record_pointers == __null
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(10,8,'x','x'),
+(11,7,'d','d'),
+(12,1,'r','r'),
+(13,7,'f','f'),
+(14,9,'y','y'),
+(15,NULL,'u','u'),
+(16,1,'m','m'),
+(17,9,NULL,NULL),
+(18,2,'o','o'),
+(19,9,'w','w'),
+(20,2,'m','m'),
+(21,4,'q','q');
+CREATE TABLE t2
+SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+
+;
+EXPLAIN SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2
+1 PRIMARY alias1 index_merge PRIMARY,col_int_key,col_varchar_key col_varchar_key,PRIMARY 4,4 NULL 2 Using sort_union(col_varchar_key,PRIMARY); Using where
+1 PRIMARY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2); FirstMatch(t2)
+SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+field1
+o
+o
+DROP TABLE t1, t2;
+# End of test for bug#13340270.
# 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-03 14:17:25 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka.result 2011-11-07 14:00:01 +0000
@@ -6950,6 +6950,64 @@ col_int_nokey col_varchar_key
2 q
DROP TABLE t1, t2, t4;
# End of test for bug#12664936.
+#
+# Bug#13340270: assertion table->sort.record_pointers == __null
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(10,8,'x','x'),
+(11,7,'d','d'),
+(12,1,'r','r'),
+(13,7,'f','f'),
+(14,9,'y','y'),
+(15,NULL,'u','u'),
+(16,1,'m','m'),
+(17,9,NULL,NULL),
+(18,2,'o','o'),
+(19,9,'w','w'),
+(20,2,'m','m'),
+(21,4,'q','q');
+CREATE TABLE t2
+SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+
+;
+EXPLAIN SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2
+1 PRIMARY alias1 index_merge PRIMARY,col_int_key,col_varchar_key col_varchar_key,PRIMARY 4,4 NULL 2 Using sort_union(col_varchar_key,PRIMARY); Using where
+1 PRIMARY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2); FirstMatch(t2)
+SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+field1
+o
+o
+DROP TABLE t1, t2;
+# End of test for bug#13340270.
# 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-03 14:17:25 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result 2011-11-07 14:00:01 +0000
@@ -6950,6 +6950,64 @@ col_int_nokey col_varchar_key
2 q
DROP TABLE t1, t2, t4;
# End of test for bug#12664936.
+#
+# Bug#13340270: assertion table->sort.record_pointers == __null
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(10,8,'x','x'),
+(11,7,'d','d'),
+(12,1,'r','r'),
+(13,7,'f','f'),
+(14,9,'y','y'),
+(15,NULL,'u','u'),
+(16,1,'m','m'),
+(17,9,NULL,NULL),
+(18,2,'o','o'),
+(19,9,'w','w'),
+(20,2,'m','m'),
+(21,4,'q','q');
+CREATE TABLE t2
+SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+
+;
+EXPLAIN SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2
+1 PRIMARY alias1 index_merge PRIMARY,col_int_key,col_varchar_key col_varchar_key,PRIMARY 4,4 NULL 2 Using sort_union(col_varchar_key,PRIMARY); Using where
+1 PRIMARY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2); FirstMatch(t2)
+SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+field1
+o
+o
+DROP TABLE t1, t2;
+# End of test for bug#13340270.
# 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-03 14:17:25 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bkaunique.result 2011-11-07 14:00:01 +0000
@@ -6951,6 +6951,64 @@ col_int_nokey col_varchar_key
2 q
DROP TABLE t1, t2, t4;
# End of test for bug#12664936.
+#
+# Bug#13340270: assertion table->sort.record_pointers == __null
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(10,8,'x','x'),
+(11,7,'d','d'),
+(12,1,'r','r'),
+(13,7,'f','f'),
+(14,9,'y','y'),
+(15,NULL,'u','u'),
+(16,1,'m','m'),
+(17,9,NULL,NULL),
+(18,2,'o','o'),
+(19,9,'w','w'),
+(20,2,'m','m'),
+(21,4,'q','q');
+CREATE TABLE t2
+SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+
+;
+EXPLAIN SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2
+1 PRIMARY alias1 index_merge PRIMARY,col_int_key,col_varchar_key col_varchar_key,PRIMARY 4,4 NULL 2 Using sort_union(col_varchar_key,PRIMARY); Using where
+1 PRIMARY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2); FirstMatch(t2)
+SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+field1
+o
+o
+DROP TABLE t1, t2;
+# End of test for bug#13340270.
# 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-03 14:17:25 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result 2011-11-07 14:00:01 +0000
@@ -6949,5 +6949,63 @@ col_int_nokey col_varchar_key
2 q
DROP TABLE t1, t2, t4;
# End of test for bug#12664936.
+#
+# Bug#13340270: assertion table->sort.record_pointers == __null
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(10,8,'x','x'),
+(11,7,'d','d'),
+(12,1,'r','r'),
+(13,7,'f','f'),
+(14,9,'y','y'),
+(15,NULL,'u','u'),
+(16,1,'m','m'),
+(17,9,NULL,NULL),
+(18,2,'o','o'),
+(19,9,'w','w'),
+(20,2,'m','m'),
+(21,4,'q','q');
+CREATE TABLE t2
+SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+
+;
+EXPLAIN SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY alias1 index_merge PRIMARY,col_int_key,col_varchar_key col_varchar_key,PRIMARY 4,4 NULL 2 Using sort_union(col_varchar_key,PRIMARY); Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2); End temporary
+SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+field1
+o
+o
+DROP TABLE t1, t2;
+# End of test for bug#13340270.
# 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-03 14:17:25 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka.result 2011-11-07 14:00:01 +0000
@@ -6950,6 +6950,64 @@ col_int_nokey col_varchar_key
2 q
DROP TABLE t1, t2, t4;
# End of test for bug#12664936.
+#
+# Bug#13340270: assertion table->sort.record_pointers == __null
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(10,8,'x','x'),
+(11,7,'d','d'),
+(12,1,'r','r'),
+(13,7,'f','f'),
+(14,9,'y','y'),
+(15,NULL,'u','u'),
+(16,1,'m','m'),
+(17,9,NULL,NULL),
+(18,2,'o','o'),
+(19,9,'w','w'),
+(20,2,'m','m'),
+(21,4,'q','q');
+CREATE TABLE t2
+SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+
+;
+EXPLAIN SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY alias1 index_merge PRIMARY,col_int_key,col_varchar_key col_varchar_key,PRIMARY 4,4 NULL 2 Using sort_union(col_varchar_key,PRIMARY); Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2); End temporary
+SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+field1
+o
+o
+DROP TABLE t1, t2;
+# End of test for bug#13340270.
# 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-03 14:17:25 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result 2011-11-07 14:00:01 +0000
@@ -6950,6 +6950,64 @@ col_int_nokey col_varchar_key
2 q
DROP TABLE t1, t2, t4;
# End of test for bug#12664936.
+#
+# Bug#13340270: assertion table->sort.record_pointers == __null
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(10,8,'x','x'),
+(11,7,'d','d'),
+(12,1,'r','r'),
+(13,7,'f','f'),
+(14,9,'y','y'),
+(15,NULL,'u','u'),
+(16,1,'m','m'),
+(17,9,NULL,NULL),
+(18,2,'o','o'),
+(19,9,'w','w'),
+(20,2,'m','m'),
+(21,4,'q','q');
+CREATE TABLE t2
+SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+
+;
+EXPLAIN SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2
+1 PRIMARY alias1 index_merge PRIMARY,col_int_key,col_varchar_key col_varchar_key,PRIMARY 4,4 NULL 2 Using sort_union(col_varchar_key,PRIMARY); Using where; Start temporary
+1 PRIMARY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2); End temporary
+SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+field1
+o
+o
+DROP TABLE t1, t2;
+# End of test for bug#13340270.
# 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-03 14:17:25 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bkaunique.result 2011-11-07 14:00:01 +0000
@@ -6951,6 +6951,64 @@ col_int_nokey col_varchar_key
2 q
DROP TABLE t1, t2, t4;
# End of test for bug#12664936.
+#
+# Bug#13340270: assertion table->sort.record_pointers == __null
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(10,8,'x','x'),
+(11,7,'d','d'),
+(12,1,'r','r'),
+(13,7,'f','f'),
+(14,9,'y','y'),
+(15,NULL,'u','u'),
+(16,1,'m','m'),
+(17,9,NULL,NULL),
+(18,2,'o','o'),
+(19,9,'w','w'),
+(20,2,'m','m'),
+(21,4,'q','q');
+CREATE TABLE t2
+SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+
+;
+EXPLAIN SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY alias1 index_merge PRIMARY,col_int_key,col_varchar_key col_varchar_key,PRIMARY 4,4 NULL 2 Using sort_union(col_varchar_key,PRIMARY); Using where; Using join buffer (Block Nested Loop)
+1 PRIMARY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2); End temporary
+SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+field1
+o
+o
+DROP TABLE t1, t2;
+# End of test for bug#13340270.
# 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-03 14:17:25 +0000
+++ b/mysql-test/r/subquery_sj_mat.result 2011-11-07 14:00:01 +0000
@@ -6948,5 +6948,63 @@ col_int_nokey col_varchar_key
2 q
DROP TABLE t1, t2, t4;
# End of test for bug#12664936.
+#
+# Bug#13340270: assertion table->sort.record_pointers == __null
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(10,8,'x','x'),
+(11,7,'d','d'),
+(12,1,'r','r'),
+(13,7,'f','f'),
+(14,9,'y','y'),
+(15,NULL,'u','u'),
+(16,1,'m','m'),
+(17,9,NULL,NULL),
+(18,2,'o','o'),
+(19,9,'w','w'),
+(20,2,'m','m'),
+(21,4,'q','q');
+CREATE TABLE t2
+SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+
+;
+EXPLAIN SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2
+1 PRIMARY alias1 index_merge PRIMARY,col_int_key,col_varchar_key col_varchar_key,PRIMARY 4,4 NULL 2 Using sort_union(col_varchar_key,PRIMARY); Using where; Start materialize
+1 PRIMARY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2); End materialize
+SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+field1
+o
+o
+DROP TABLE t1, t2;
+# End of test for bug#13340270.
# 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-03 14:17:25 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka.result 2011-11-07 14:00:01 +0000
@@ -6949,6 +6949,64 @@ col_int_nokey col_varchar_key
2 q
DROP TABLE t1, t2, t4;
# End of test for bug#12664936.
+#
+# Bug#13340270: assertion table->sort.record_pointers == __null
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(10,8,'x','x'),
+(11,7,'d','d'),
+(12,1,'r','r'),
+(13,7,'f','f'),
+(14,9,'y','y'),
+(15,NULL,'u','u'),
+(16,1,'m','m'),
+(17,9,NULL,NULL),
+(18,2,'o','o'),
+(19,9,'w','w'),
+(20,2,'m','m'),
+(21,4,'q','q');
+CREATE TABLE t2
+SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+
+;
+EXPLAIN SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2
+1 PRIMARY alias1 index_merge PRIMARY,col_int_key,col_varchar_key col_varchar_key,PRIMARY 4,4 NULL 2 Using sort_union(col_varchar_key,PRIMARY); Using where; Start materialize
+1 PRIMARY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2); End materialize
+SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+field1
+o
+o
+DROP TABLE t1, t2;
+# End of test for bug#13340270.
# 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-03 14:17:25 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka_nixbnl.result 2011-11-07 14:00:01 +0000
@@ -6949,6 +6949,64 @@ col_int_nokey col_varchar_key
2 q
DROP TABLE t1, t2, t4;
# End of test for bug#12664936.
+#
+# Bug#13340270: assertion table->sort.record_pointers == __null
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(10,8,'x','x'),
+(11,7,'d','d'),
+(12,1,'r','r'),
+(13,7,'f','f'),
+(14,9,'y','y'),
+(15,NULL,'u','u'),
+(16,1,'m','m'),
+(17,9,NULL,NULL),
+(18,2,'o','o'),
+(19,9,'w','w'),
+(20,2,'m','m'),
+(21,4,'q','q');
+CREATE TABLE t2
+SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+
+;
+EXPLAIN SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2
+1 PRIMARY alias1 index_merge PRIMARY,col_int_key,col_varchar_key col_varchar_key,PRIMARY 4,4 NULL 2 Using sort_union(col_varchar_key,PRIMARY); Using where; Start materialize
+1 PRIMARY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2); End materialize
+SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+field1
+o
+o
+DROP TABLE t1, t2;
+# End of test for bug#13340270.
# 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-03 14:17:25 +0000
+++ b/mysql-test/r/subquery_sj_mat_bkaunique.result 2011-11-07 14:00:01 +0000
@@ -6950,6 +6950,64 @@ col_int_nokey col_varchar_key
2 q
DROP TABLE t1, t2, t4;
# End of test for bug#12664936.
+#
+# Bug#13340270: assertion table->sort.record_pointers == __null
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(10,8,'x','x'),
+(11,7,'d','d'),
+(12,1,'r','r'),
+(13,7,'f','f'),
+(14,9,'y','y'),
+(15,NULL,'u','u'),
+(16,1,'m','m'),
+(17,9,NULL,NULL),
+(18,2,'o','o'),
+(19,9,'w','w'),
+(20,2,'m','m'),
+(21,4,'q','q');
+CREATE TABLE t2
+SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+
+;
+EXPLAIN SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2
+1 PRIMARY alias1 index_merge PRIMARY,col_int_key,col_varchar_key col_varchar_key,PRIMARY 4,4 NULL 2 Using sort_union(col_varchar_key,PRIMARY); Using where; Start materialize
+1 PRIMARY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2); End materialize
+SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+field1
+o
+o
+DROP TABLE t1, t2;
+# End of test for bug#13340270.
# 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-10-24 14:01:11 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result 2011-11-07 14:00:01 +0000
@@ -7025,5 +7025,63 @@ col_int_nokey col_varchar_key
2 q
DROP TABLE t1, t2, t4;
# End of test for bug#12664936.
+#
+# Bug#13340270: assertion table->sort.record_pointers == __null
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(10,8,'x','x'),
+(11,7,'d','d'),
+(12,1,'r','r'),
+(13,7,'f','f'),
+(14,9,'y','y'),
+(15,NULL,'u','u'),
+(16,1,'m','m'),
+(17,9,NULL,NULL),
+(18,2,'o','o'),
+(19,9,'w','w'),
+(20,2,'m','m'),
+(21,4,'q','q');
+CREATE TABLE t2
+SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+
+;
+EXPLAIN SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
+2 SUBQUERY alias1 index_merge PRIMARY,col_int_key,col_varchar_key col_varchar_key,PRIMARY 4,4 NULL 2 Using sort_union(col_varchar_key,PRIMARY); Using where
+2 SUBQUERY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2)
+SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+field1
+o
+o
+DROP TABLE t1, t2;
+# End of test for bug#13340270.
# 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-10-24 14:01:11 +0000
+++ b/mysql-test/r/subquery_sj_none.result 2011-11-07 14:00:01 +0000
@@ -6960,5 +6960,63 @@ col_int_nokey col_varchar_key
2 q
DROP TABLE t1, t2, t4;
# End of test for bug#12664936.
+#
+# Bug#13340270: assertion table->sort.record_pointers == __null
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(10,8,'x','x'),
+(11,7,'d','d'),
+(12,1,'r','r'),
+(13,7,'f','f'),
+(14,9,'y','y'),
+(15,NULL,'u','u'),
+(16,1,'m','m'),
+(17,9,NULL,NULL),
+(18,2,'o','o'),
+(19,9,'w','w'),
+(20,2,'m','m'),
+(21,4,'q','q');
+CREATE TABLE t2
+SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+
+;
+EXPLAIN SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
+2 DEPENDENT SUBQUERY alias1 index_merge PRIMARY,col_int_key,col_varchar_key col_varchar_key,PRIMARY 4,4 NULL 2 Using sort_union(col_varchar_key,PRIMARY); Using where
+2 DEPENDENT SUBQUERY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2)
+SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+field1
+o
+o
+DROP TABLE t1, t2;
+# End of test for bug#13340270.
# 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-10-27 11:51:33 +0000
+++ b/mysql-test/r/subquery_sj_none_bka.result 2011-11-07 14:00:01 +0000
@@ -6961,6 +6961,64 @@ col_int_nokey col_varchar_key
2 q
DROP TABLE t1, t2, t4;
# End of test for bug#12664936.
+#
+# Bug#13340270: assertion table->sort.record_pointers == __null
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(10,8,'x','x'),
+(11,7,'d','d'),
+(12,1,'r','r'),
+(13,7,'f','f'),
+(14,9,'y','y'),
+(15,NULL,'u','u'),
+(16,1,'m','m'),
+(17,9,NULL,NULL),
+(18,2,'o','o'),
+(19,9,'w','w'),
+(20,2,'m','m'),
+(21,4,'q','q');
+CREATE TABLE t2
+SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+
+;
+EXPLAIN SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
+2 DEPENDENT SUBQUERY alias1 index_merge PRIMARY,col_int_key,col_varchar_key col_varchar_key,PRIMARY 4,4 NULL 2 Using sort_union(col_varchar_key,PRIMARY); Using where
+2 DEPENDENT SUBQUERY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2)
+SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+field1
+o
+o
+DROP TABLE t1, t2;
+# End of test for bug#13340270.
# 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-10-27 11:51:33 +0000
+++ b/mysql-test/r/subquery_sj_none_bka_nixbnl.result 2011-11-07 14:00:01 +0000
@@ -6961,6 +6961,64 @@ col_int_nokey col_varchar_key
2 q
DROP TABLE t1, t2, t4;
# End of test for bug#12664936.
+#
+# Bug#13340270: assertion table->sort.record_pointers == __null
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(10,8,'x','x'),
+(11,7,'d','d'),
+(12,1,'r','r'),
+(13,7,'f','f'),
+(14,9,'y','y'),
+(15,NULL,'u','u'),
+(16,1,'m','m'),
+(17,9,NULL,NULL),
+(18,2,'o','o'),
+(19,9,'w','w'),
+(20,2,'m','m'),
+(21,4,'q','q');
+CREATE TABLE t2
+SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+
+;
+EXPLAIN SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
+2 DEPENDENT SUBQUERY alias1 index_merge PRIMARY,col_int_key,col_varchar_key col_varchar_key,PRIMARY 4,4 NULL 2 Using sort_union(col_varchar_key,PRIMARY); Using where
+2 DEPENDENT SUBQUERY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2)
+SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+field1
+o
+o
+DROP TABLE t1, t2;
+# End of test for bug#13340270.
# 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-10-27 11:51:33 +0000
+++ b/mysql-test/r/subquery_sj_none_bkaunique.result 2011-11-07 14:00:01 +0000
@@ -6962,6 +6962,64 @@ col_int_nokey col_varchar_key
2 q
DROP TABLE t1, t2, t4;
# End of test for bug#12664936.
+#
+# Bug#13340270: assertion table->sort.record_pointers == __null
+#
+CREATE TABLE t1 (
+pk int NOT NULL,
+col_int_key int DEFAULT NULL,
+col_varchar_key varchar(1) DEFAULT NULL,
+col_varchar_nokey varchar(1) DEFAULT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key, col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(10,8,'x','x'),
+(11,7,'d','d'),
+(12,1,'r','r'),
+(13,7,'f','f'),
+(14,9,'y','y'),
+(15,NULL,'u','u'),
+(16,1,'m','m'),
+(17,9,NULL,NULL),
+(18,2,'o','o'),
+(19,9,'w','w'),
+(20,2,'m','m'),
+(21,4,'q','q');
+CREATE TABLE t2
+SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+
+;
+EXPLAIN SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where
+2 DEPENDENT SUBQUERY alias1 index_merge PRIMARY,col_int_key,col_varchar_key col_varchar_key,PRIMARY 4,4 NULL 2 Using sort_union(col_varchar_key,PRIMARY); Using where
+2 DEPENDENT SUBQUERY alias2 ALL col_int_key NULL NULL NULL 12 Range checked for each record (index map: 0x2)
+SELECT *
+FROM t2
+WHERE (field1) IN (SELECT alias1.col_varchar_nokey AS field1
+FROM t1 AS alias1 JOIN t1 AS alias2
+ON alias2.col_int_key = alias1.pk OR
+alias2.col_int_key = alias1.col_int_key
+WHERE alias1.pk = 58 OR alias1.col_varchar_key = 'o'
+);
+field1
+o
+o
+DROP TABLE t1, t2;
+# End of test for bug#13340270.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc 2011-10-27 08:43:56 +0000
+++ b/sql/opt_range.cc 2011-11-07 14:00:01 +0000
@@ -114,6 +114,7 @@
#include <m_ctype.h>
#include "sql_select.h"
#include "opt_trace.h"
+#include "filesort.h" // filesort_free_buffers
#ifndef EXTRA_DEBUG
#define test_rb_tree(A,B) {}
@@ -9296,7 +9297,10 @@ int QUICK_INDEX_MERGE_SELECT::read_keys_
thd->variables.sortbuff_size);
}
else
+ {
unique->reset();
+ filesort_free_buffers(head, true);
+ }
DBUG_ASSERT(file->ref_length == unique->get_size());
DBUG_ASSERT(thd->variables.sortbuff_size == unique->get_max_in_memory_size());
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (roy.lyseng:3474 to 3475) Bug#13340270 | Roy Lyseng | 7 Nov |
