3517 Roy Lyseng 2012-01-12
Bug#13576391: Missing rows on select with in-subquery and BKA=on and semijoin
The problem here is that we attempt filesort on a table that is an
outer table in a semi-join duplicate-weedout operation. During the
regular filesort used by SELECT statements, rowids are not carried
through, but they are needed for duplicate-weedout uniqueness test.
The solution to the problem is to enable filesort with positions for
all filesort operations where rowids are required after the sorting.
This is the same strategy that is used for filesort used in update
and delete operations.
Why did this problem happen when enabling BKA, even though BKA is
not used in the plan? setup_semijoin_dups_elimination() calls
might_do_join_buffering() that tells that join buffering might be
used, and hence the duplicate weedout range is extended to include
table ot. Later, the MRR default implementation is decided to be used,
which prevents BKA from being used (setup_join_buffering()).
mysql-test/include/subquery_sj.inc
Added test case for bug#13576391.
Test case is run for all three major storage engines, to see that
they all support use of rowids in this situation, and that the
output is properly ordered.
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#13576391.
sql/filesort.cc
Updated comment of filesort() for argument sort_positions
sql/sql_executor.cc
In create_sort_index(), make sure that filesort() keeps rowids
throughout sorting, when rowids are needed for the table.
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/filesort.cc
sql/sql_executor.cc
3516 Jorgen Loland 2012-01-12
Small refactoring of create_duplicate_weedout_tmp_table():
Simplify logic and remove dead code
@ sql/sql_tmp_table.cc
Small refactoring of create_duplicate_weedout_tmp_table():
Simplify logic and remove dead code
modified:
sql/sql_tmp_table.cc
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc 2012-01-11 13:29:29 +0000
+++ b/mysql-test/include/subquery_sj.inc 2012-01-12 14:53:51 +0000
@@ -4716,4 +4716,60 @@ DROP TABLE t1, t2;
--echo # End of test for bug#13541406.
+--echo #
+--echo # Bug#13576391: Missing rows on select with in-subquery and
+--echo # batched-key-access=on and semijoin
+--echo #
+
+CREATE TABLE t1 (
+ col_int_nokey int NOT NULL,
+ col_varchar_key varchar(1) NOT NULL,
+ KEY col_varchar_key (col_varchar_key)
+) engine=InnoDB;
+
+INSERT INTO t1 VALUES
+ (1,'v'), (7,'s'), (4,'l'), (7,'y'), (0,'c'), (2,'i'), (9,'h'), (4,'q'),
+ (0,'a'), (9,'v'), (1,'u'), (3,'s'), (8,'y'), (8,'z'), (18,'h'), (84,'p'),
+ (6,'e'), (3,'i'), (6,'y'), (6,'w');
+
+CREATE TABLE t2 (
+ col_int_nokey int NOT NULL,
+ col_varchar_nokey varchar(1) NOT NULL
+) engine=InnoDB;
+
+INSERT INTO t2 VALUES
+ (4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+ (3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+ (1,'q'), (6,'w'), (2,'d'), (9,'e');
+
+let $query=
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+ SELECT col_varchar_key
+ FROM t1 AS it
+ WHERE it.col_int_nokey <= it.col_int_nokey
+ AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+
+eval explain $query;
+eval $query;
+
+ALTER TABLE t1 ENGINE=MyISAM;
+ALTER TABLE t2 ENGINE=MyISAM;
+
+eval explain $query;
+eval $query;
+
+ALTER TABLE t1 ENGINE=Memory;
+ALTER TABLE t2 ENGINE=Memory;
+
+eval explain $query;
+eval $query;
+
+DROP TABLE t1, t2;
+
+--echo # End of test for bug#13576391.
+
--echo # End of 5.6 tests
=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result 2012-01-11 13:29:29 +0000
+++ b/mysql-test/r/subquery_sj_all.result 2012-01-12 14:53:51 +0000
@@ -7456,5 +7456,119 @@ field1
9
DROP TABLE t1, t2;
# End of test for bug#13541406.
+#
+# Bug#13576391: Missing rows on select with in-subquery and
+# batched-key-access=on and semijoin
+#
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) engine=InnoDB;
+INSERT INTO t1 VALUES
+(1,'v'), (7,'s'), (4,'l'), (7,'y'), (0,'c'), (2,'i'), (9,'h'), (4,'q'),
+(0,'a'), (9,'v'), (1,'u'), (3,'s'), (8,'y'), (8,'z'), (18,'h'), (84,'p'),
+(6,'e'), (3,'i'), (6,'y'), (6,'w');
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) engine=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 1 FirstMatch(ot)
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=MyISAM;
+ALTER TABLE t2 ENGINE=MyISAM;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 FirstMatch(ot)
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=Memory;
+ALTER TABLE t2 ENGINE=Memory;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 FirstMatch(ot)
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+DROP TABLE t1, t2;
+# End of test for bug#13576391.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_all_bka.result'
--- a/mysql-test/r/subquery_sj_all_bka.result 2012-01-11 13:29:29 +0000
+++ b/mysql-test/r/subquery_sj_all_bka.result 2012-01-12 14:53:51 +0000
@@ -7457,6 +7457,120 @@ field1
9
DROP TABLE t1, t2;
# End of test for bug#13541406.
+#
+# Bug#13576391: Missing rows on select with in-subquery and
+# batched-key-access=on and semijoin
+#
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) engine=InnoDB;
+INSERT INTO t1 VALUES
+(1,'v'), (7,'s'), (4,'l'), (7,'y'), (0,'c'), (2,'i'), (9,'h'), (4,'q'),
+(0,'a'), (9,'v'), (1,'u'), (3,'s'), (8,'y'), (8,'z'), (18,'h'), (84,'p'),
+(6,'e'), (3,'i'), (6,'y'), (6,'w');
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) engine=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using temporary; Using filesort
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 1 FirstMatch(ot); Using join buffer (Batched Key Access)
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=MyISAM;
+ALTER TABLE t2 ENGINE=MyISAM;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using temporary; Using filesort
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 FirstMatch(ot); Using join buffer (Batched Key Access)
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=Memory;
+ALTER TABLE t2 ENGINE=Memory;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 FirstMatch(ot)
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+DROP TABLE t1, t2;
+# End of test for bug#13576391.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_all_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_all_bka_nixbnl.result 2012-01-11 13:29:29 +0000
+++ b/mysql-test/r/subquery_sj_all_bka_nixbnl.result 2012-01-12 14:53:51 +0000
@@ -7457,6 +7457,120 @@ field1
9
DROP TABLE t1, t2;
# End of test for bug#13541406.
+#
+# Bug#13576391: Missing rows on select with in-subquery and
+# batched-key-access=on and semijoin
+#
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) engine=InnoDB;
+INSERT INTO t1 VALUES
+(1,'v'), (7,'s'), (4,'l'), (7,'y'), (0,'c'), (2,'i'), (9,'h'), (4,'q'),
+(0,'a'), (9,'v'), (1,'u'), (3,'s'), (8,'y'), (8,'z'), (18,'h'), (84,'p'),
+(6,'e'), (3,'i'), (6,'y'), (6,'w');
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) engine=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using temporary; Using filesort
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 1 FirstMatch(ot); Using join buffer (Batched Key Access)
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=MyISAM;
+ALTER TABLE t2 ENGINE=MyISAM;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using temporary; Using filesort
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 FirstMatch(ot); Using join buffer (Batched Key Access)
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=Memory;
+ALTER TABLE t2 ENGINE=Memory;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 FirstMatch(ot)
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+DROP TABLE t1, t2;
+# End of test for bug#13576391.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_all_bkaunique.result'
--- a/mysql-test/r/subquery_sj_all_bkaunique.result 2012-01-11 13:29:29 +0000
+++ b/mysql-test/r/subquery_sj_all_bkaunique.result 2012-01-12 14:53:51 +0000
@@ -7458,6 +7458,120 @@ field1
9
DROP TABLE t1, t2;
# End of test for bug#13541406.
+#
+# Bug#13576391: Missing rows on select with in-subquery and
+# batched-key-access=on and semijoin
+#
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) engine=InnoDB;
+INSERT INTO t1 VALUES
+(1,'v'), (7,'s'), (4,'l'), (7,'y'), (0,'c'), (2,'i'), (9,'h'), (4,'q'),
+(0,'a'), (9,'v'), (1,'u'), (3,'s'), (8,'y'), (8,'z'), (18,'h'), (84,'p'),
+(6,'e'), (3,'i'), (6,'y'), (6,'w');
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) engine=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using temporary; Using filesort
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 1 FirstMatch(ot); Using join buffer (Batched Key Access (unique))
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=MyISAM;
+ALTER TABLE t2 ENGINE=MyISAM;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using temporary; Using filesort
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 FirstMatch(ot); Using join buffer (Batched Key Access (unique))
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=Memory;
+ALTER TABLE t2 ENGINE=Memory;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 FirstMatch(ot)
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+DROP TABLE t1, t2;
+# End of test for bug#13576391.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result 2012-01-11 13:29:29 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result 2012-01-12 14:53:51 +0000
@@ -7455,5 +7455,119 @@ field1
9
DROP TABLE t1, t2;
# End of test for bug#13541406.
+#
+# Bug#13576391: Missing rows on select with in-subquery and
+# batched-key-access=on and semijoin
+#
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) engine=InnoDB;
+INSERT INTO t1 VALUES
+(1,'v'), (7,'s'), (4,'l'), (7,'y'), (0,'c'), (2,'i'), (9,'h'), (4,'q'),
+(0,'a'), (9,'v'), (1,'u'), (3,'s'), (8,'y'), (8,'z'), (18,'h'), (84,'p'),
+(6,'e'), (3,'i'), (6,'y'), (6,'w');
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) engine=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 1 Start temporary; End temporary
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=MyISAM;
+ALTER TABLE t2 ENGINE=MyISAM;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 Start temporary; End temporary
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=Memory;
+ALTER TABLE t2 ENGINE=Memory;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 Start temporary; End temporary
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+DROP TABLE t1, t2;
+# End of test for bug#13576391.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka.result 2012-01-11 13:29:29 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka.result 2012-01-12 14:53:51 +0000
@@ -7456,6 +7456,120 @@ field1
9
DROP TABLE t1, t2;
# End of test for bug#13541406.
+#
+# Bug#13576391: Missing rows on select with in-subquery and
+# batched-key-access=on and semijoin
+#
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) engine=InnoDB;
+INSERT INTO t1 VALUES
+(1,'v'), (7,'s'), (4,'l'), (7,'y'), (0,'c'), (2,'i'), (9,'h'), (4,'q'),
+(0,'a'), (9,'v'), (1,'u'), (3,'s'), (8,'y'), (8,'z'), (18,'h'), (84,'p'),
+(6,'e'), (3,'i'), (6,'y'), (6,'w');
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) engine=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort; Start temporary
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 1 End temporary
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=MyISAM;
+ALTER TABLE t2 ENGINE=MyISAM;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort; Start temporary
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 End temporary
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=Memory;
+ALTER TABLE t2 ENGINE=Memory;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort; Start temporary
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 End temporary
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+DROP TABLE t1, t2;
+# End of test for bug#13576391.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result 2012-01-11 13:29:29 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result 2012-01-12 14:53:51 +0000
@@ -7456,6 +7456,120 @@ field1
9
DROP TABLE t1, t2;
# End of test for bug#13541406.
+#
+# Bug#13576391: Missing rows on select with in-subquery and
+# batched-key-access=on and semijoin
+#
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) engine=InnoDB;
+INSERT INTO t1 VALUES
+(1,'v'), (7,'s'), (4,'l'), (7,'y'), (0,'c'), (2,'i'), (9,'h'), (4,'q'),
+(0,'a'), (9,'v'), (1,'u'), (3,'s'), (8,'y'), (8,'z'), (18,'h'), (84,'p'),
+(6,'e'), (3,'i'), (6,'y'), (6,'w');
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) engine=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort; Start temporary
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 1 End temporary
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=MyISAM;
+ALTER TABLE t2 ENGINE=MyISAM;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort; Start temporary
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 End temporary
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=Memory;
+ALTER TABLE t2 ENGINE=Memory;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort; Start temporary
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 End temporary
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+DROP TABLE t1, t2;
+# End of test for bug#13576391.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed_bkaunique.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bkaunique.result 2012-01-11 13:29:29 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bkaunique.result 2012-01-12 14:53:51 +0000
@@ -7457,6 +7457,120 @@ field1
9
DROP TABLE t1, t2;
# End of test for bug#13541406.
+#
+# Bug#13576391: Missing rows on select with in-subquery and
+# batched-key-access=on and semijoin
+#
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) engine=InnoDB;
+INSERT INTO t1 VALUES
+(1,'v'), (7,'s'), (4,'l'), (7,'y'), (0,'c'), (2,'i'), (9,'h'), (4,'q'),
+(0,'a'), (9,'v'), (1,'u'), (3,'s'), (8,'y'), (8,'z'), (18,'h'), (84,'p'),
+(6,'e'), (3,'i'), (6,'y'), (6,'w');
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) engine=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort; Start temporary
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 1 End temporary
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=MyISAM;
+ALTER TABLE t2 ENGINE=MyISAM;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort; Start temporary
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 End temporary
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=Memory;
+ALTER TABLE t2 ENGINE=Memory;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort; Start temporary
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 End temporary
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+DROP TABLE t1, t2;
+# End of test for bug#13576391.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result 2012-01-11 13:29:29 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result 2012-01-12 14:53:51 +0000
@@ -7456,6 +7456,120 @@ field1
9
DROP TABLE t1, t2;
# End of test for bug#13541406.
+#
+# Bug#13576391: Missing rows on select with in-subquery and
+# batched-key-access=on and semijoin
+#
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) engine=InnoDB;
+INSERT INTO t1 VALUES
+(1,'v'), (7,'s'), (4,'l'), (7,'y'), (0,'c'), (2,'i'), (9,'h'), (4,'q'),
+(0,'a'), (9,'v'), (1,'u'), (3,'s'), (8,'y'), (8,'z'), (18,'h'), (84,'p'),
+(6,'e'), (3,'i'), (6,'y'), (6,'w');
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) engine=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 1 FirstMatch(ot)
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=MyISAM;
+ALTER TABLE t2 ENGINE=MyISAM;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 FirstMatch(ot)
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=Memory;
+ALTER TABLE t2 ENGINE=Memory;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 FirstMatch(ot)
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+DROP TABLE t1, t2;
+# End of test for bug#13576391.
# End of 5.6 tests
#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka.result 2012-01-11 13:29:29 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka.result 2012-01-12 14:53:51 +0000
@@ -7457,6 +7457,120 @@ field1
9
DROP TABLE t1, t2;
# End of test for bug#13541406.
+#
+# Bug#13576391: Missing rows on select with in-subquery and
+# batched-key-access=on and semijoin
+#
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) engine=InnoDB;
+INSERT INTO t1 VALUES
+(1,'v'), (7,'s'), (4,'l'), (7,'y'), (0,'c'), (2,'i'), (9,'h'), (4,'q'),
+(0,'a'), (9,'v'), (1,'u'), (3,'s'), (8,'y'), (8,'z'), (18,'h'), (84,'p'),
+(6,'e'), (3,'i'), (6,'y'), (6,'w');
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) engine=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 1 FirstMatch(ot)
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=MyISAM;
+ALTER TABLE t2 ENGINE=MyISAM;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 FirstMatch(ot)
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=Memory;
+ALTER TABLE t2 ENGINE=Memory;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 FirstMatch(ot)
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+DROP TABLE t1, t2;
+# End of test for bug#13576391.
# End of 5.6 tests
#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result 2012-01-11 13:29:29 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result 2012-01-12 14:53:51 +0000
@@ -7457,6 +7457,120 @@ field1
9
DROP TABLE t1, t2;
# End of test for bug#13541406.
+#
+# Bug#13576391: Missing rows on select with in-subquery and
+# batched-key-access=on and semijoin
+#
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) engine=InnoDB;
+INSERT INTO t1 VALUES
+(1,'v'), (7,'s'), (4,'l'), (7,'y'), (0,'c'), (2,'i'), (9,'h'), (4,'q'),
+(0,'a'), (9,'v'), (1,'u'), (3,'s'), (8,'y'), (8,'z'), (18,'h'), (84,'p'),
+(6,'e'), (3,'i'), (6,'y'), (6,'w');
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) engine=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 1 FirstMatch(ot)
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=MyISAM;
+ALTER TABLE t2 ENGINE=MyISAM;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 FirstMatch(ot)
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=Memory;
+ALTER TABLE t2 ENGINE=Memory;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 FirstMatch(ot)
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+DROP TABLE t1, t2;
+# End of test for bug#13576391.
# End of 5.6 tests
#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
=== modified file 'mysql-test/r/subquery_sj_firstmatch_bkaunique.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bkaunique.result 2012-01-11 13:29:29 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bkaunique.result 2012-01-12 14:53:51 +0000
@@ -7458,6 +7458,120 @@ field1
9
DROP TABLE t1, t2;
# End of test for bug#13541406.
+#
+# Bug#13576391: Missing rows on select with in-subquery and
+# batched-key-access=on and semijoin
+#
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) engine=InnoDB;
+INSERT INTO t1 VALUES
+(1,'v'), (7,'s'), (4,'l'), (7,'y'), (0,'c'), (2,'i'), (9,'h'), (4,'q'),
+(0,'a'), (9,'v'), (1,'u'), (3,'s'), (8,'y'), (8,'z'), (18,'h'), (84,'p'),
+(6,'e'), (3,'i'), (6,'y'), (6,'w');
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) engine=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 1 FirstMatch(ot)
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=MyISAM;
+ALTER TABLE t2 ENGINE=MyISAM;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 FirstMatch(ot)
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=Memory;
+ALTER TABLE t2 ENGINE=Memory;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 FirstMatch(ot)
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+DROP TABLE t1, t2;
+# End of test for bug#13576391.
# End of 5.6 tests
#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result 2012-01-11 13:29:29 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result 2012-01-12 14:53:51 +0000
@@ -7456,5 +7456,119 @@ field1
9
DROP TABLE t1, t2;
# End of test for bug#13541406.
+#
+# Bug#13576391: Missing rows on select with in-subquery and
+# batched-key-access=on and semijoin
+#
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) engine=InnoDB;
+INSERT INTO t1 VALUES
+(1,'v'), (7,'s'), (4,'l'), (7,'y'), (0,'c'), (2,'i'), (9,'h'), (4,'q'),
+(0,'a'), (9,'v'), (1,'u'), (3,'s'), (8,'y'), (8,'z'), (18,'h'), (84,'p'),
+(6,'e'), (3,'i'), (6,'y'), (6,'w');
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) engine=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 1 Start temporary; End temporary
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=MyISAM;
+ALTER TABLE t2 ENGINE=MyISAM;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 Start temporary; End temporary
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=Memory;
+ALTER TABLE t2 ENGINE=Memory;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 Start temporary; End temporary
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+DROP TABLE t1, t2;
+# End of test for bug#13576391.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_loosescan_bka.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka.result 2012-01-11 13:29:29 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka.result 2012-01-12 14:53:51 +0000
@@ -7457,6 +7457,120 @@ field1
9
DROP TABLE t1, t2;
# End of test for bug#13541406.
+#
+# Bug#13576391: Missing rows on select with in-subquery and
+# batched-key-access=on and semijoin
+#
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) engine=InnoDB;
+INSERT INTO t1 VALUES
+(1,'v'), (7,'s'), (4,'l'), (7,'y'), (0,'c'), (2,'i'), (9,'h'), (4,'q'),
+(0,'a'), (9,'v'), (1,'u'), (3,'s'), (8,'y'), (8,'z'), (18,'h'), (84,'p'),
+(6,'e'), (3,'i'), (6,'y'), (6,'w');
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) engine=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort; Start temporary
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 1 End temporary
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=MyISAM;
+ALTER TABLE t2 ENGINE=MyISAM;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort; Start temporary
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 End temporary
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=Memory;
+ALTER TABLE t2 ENGINE=Memory;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort; Start temporary
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 End temporary
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+DROP TABLE t1, t2;
+# End of test for bug#13576391.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result 2012-01-11 13:29:29 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result 2012-01-12 14:53:51 +0000
@@ -7457,6 +7457,120 @@ field1
9
DROP TABLE t1, t2;
# End of test for bug#13541406.
+#
+# Bug#13576391: Missing rows on select with in-subquery and
+# batched-key-access=on and semijoin
+#
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) engine=InnoDB;
+INSERT INTO t1 VALUES
+(1,'v'), (7,'s'), (4,'l'), (7,'y'), (0,'c'), (2,'i'), (9,'h'), (4,'q'),
+(0,'a'), (9,'v'), (1,'u'), (3,'s'), (8,'y'), (8,'z'), (18,'h'), (84,'p'),
+(6,'e'), (3,'i'), (6,'y'), (6,'w');
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) engine=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort; Start temporary
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 1 End temporary
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=MyISAM;
+ALTER TABLE t2 ENGINE=MyISAM;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort; Start temporary
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 End temporary
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=Memory;
+ALTER TABLE t2 ENGINE=Memory;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort; Start temporary
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 End temporary
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+DROP TABLE t1, t2;
+# End of test for bug#13576391.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_loosescan_bkaunique.result'
--- a/mysql-test/r/subquery_sj_loosescan_bkaunique.result 2012-01-11 13:29:29 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bkaunique.result 2012-01-12 14:53:51 +0000
@@ -7458,6 +7458,120 @@ field1
9
DROP TABLE t1, t2;
# End of test for bug#13541406.
+#
+# Bug#13576391: Missing rows on select with in-subquery and
+# batched-key-access=on and semijoin
+#
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) engine=InnoDB;
+INSERT INTO t1 VALUES
+(1,'v'), (7,'s'), (4,'l'), (7,'y'), (0,'c'), (2,'i'), (9,'h'), (4,'q'),
+(0,'a'), (9,'v'), (1,'u'), (3,'s'), (8,'y'), (8,'z'), (18,'h'), (84,'p'),
+(6,'e'), (3,'i'), (6,'y'), (6,'w');
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) engine=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort; Start temporary
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 1 End temporary
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=MyISAM;
+ALTER TABLE t2 ENGINE=MyISAM;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort; Start temporary
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 End temporary
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=Memory;
+ALTER TABLE t2 ENGINE=Memory;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort; Start temporary
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 End temporary
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+DROP TABLE t1, t2;
+# End of test for bug#13576391.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result 2012-01-11 13:29:29 +0000
+++ b/mysql-test/r/subquery_sj_mat.result 2012-01-12 14:53:51 +0000
@@ -7456,5 +7456,119 @@ field1
9
DROP TABLE t1, t2;
# End of test for bug#13541406.
+#
+# Bug#13576391: Missing rows on select with in-subquery and
+# batched-key-access=on and semijoin
+#
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) engine=InnoDB;
+INSERT INTO t1 VALUES
+(1,'v'), (7,'s'), (4,'l'), (7,'y'), (0,'c'), (2,'i'), (9,'h'), (4,'q'),
+(0,'a'), (9,'v'), (1,'u'), (3,'s'), (8,'y'), (8,'z'), (18,'h'), (84,'p'),
+(6,'e'), (3,'i'), (6,'y'), (6,'w');
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) engine=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 1 Start temporary; End temporary
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=MyISAM;
+ALTER TABLE t2 ENGINE=MyISAM;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 Start temporary; End temporary
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=Memory;
+ALTER TABLE t2 ENGINE=Memory;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 Start temporary; End temporary
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+DROP TABLE t1, t2;
+# End of test for bug#13576391.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_bka.result'
--- a/mysql-test/r/subquery_sj_mat_bka.result 2012-01-11 13:29:29 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka.result 2012-01-12 14:53:51 +0000
@@ -7457,6 +7457,120 @@ field1
9
DROP TABLE t1, t2;
# End of test for bug#13541406.
+#
+# Bug#13576391: Missing rows on select with in-subquery and
+# batched-key-access=on and semijoin
+#
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) engine=InnoDB;
+INSERT INTO t1 VALUES
+(1,'v'), (7,'s'), (4,'l'), (7,'y'), (0,'c'), (2,'i'), (9,'h'), (4,'q'),
+(0,'a'), (9,'v'), (1,'u'), (3,'s'), (8,'y'), (8,'z'), (18,'h'), (84,'p'),
+(6,'e'), (3,'i'), (6,'y'), (6,'w');
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) engine=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort; Start temporary
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 1 End temporary
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=MyISAM;
+ALTER TABLE t2 ENGINE=MyISAM;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort; Start temporary
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 End temporary
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=Memory;
+ALTER TABLE t2 ENGINE=Memory;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort; Start temporary
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 End temporary
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+DROP TABLE t1, t2;
+# End of test for bug#13576391.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_mat_bka_nixbnl.result 2012-01-11 13:29:29 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka_nixbnl.result 2012-01-12 14:53:51 +0000
@@ -7457,6 +7457,120 @@ field1
9
DROP TABLE t1, t2;
# End of test for bug#13541406.
+#
+# Bug#13576391: Missing rows on select with in-subquery and
+# batched-key-access=on and semijoin
+#
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) engine=InnoDB;
+INSERT INTO t1 VALUES
+(1,'v'), (7,'s'), (4,'l'), (7,'y'), (0,'c'), (2,'i'), (9,'h'), (4,'q'),
+(0,'a'), (9,'v'), (1,'u'), (3,'s'), (8,'y'), (8,'z'), (18,'h'), (84,'p'),
+(6,'e'), (3,'i'), (6,'y'), (6,'w');
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) engine=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort; Start temporary
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 1 End temporary
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=MyISAM;
+ALTER TABLE t2 ENGINE=MyISAM;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort; Start temporary
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 End temporary
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=Memory;
+ALTER TABLE t2 ENGINE=Memory;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort; Start temporary
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 End temporary
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+DROP TABLE t1, t2;
+# End of test for bug#13576391.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_bkaunique.result'
--- a/mysql-test/r/subquery_sj_mat_bkaunique.result 2012-01-11 13:29:29 +0000
+++ b/mysql-test/r/subquery_sj_mat_bkaunique.result 2012-01-12 14:53:51 +0000
@@ -7458,6 +7458,120 @@ field1
9
DROP TABLE t1, t2;
# End of test for bug#13541406.
+#
+# Bug#13576391: Missing rows on select with in-subquery and
+# batched-key-access=on and semijoin
+#
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) engine=InnoDB;
+INSERT INTO t1 VALUES
+(1,'v'), (7,'s'), (4,'l'), (7,'y'), (0,'c'), (2,'i'), (9,'h'), (4,'q'),
+(0,'a'), (9,'v'), (1,'u'), (3,'s'), (8,'y'), (8,'z'), (18,'h'), (84,'p'),
+(6,'e'), (3,'i'), (6,'y'), (6,'w');
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) engine=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort; Start temporary
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 1 End temporary
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=MyISAM;
+ALTER TABLE t2 ENGINE=MyISAM;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort; Start temporary
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 End temporary
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=Memory;
+ALTER TABLE t2 ENGINE=Memory;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort; Start temporary
+1 PRIMARY it ref col_varchar_key col_varchar_key 3 test.ot.col_varchar_nokey 2 End temporary
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+DROP TABLE t1, t2;
+# End of test for bug#13576391.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
--- a/mysql-test/r/subquery_sj_mat_nosj.result 2012-01-11 13:29:29 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result 2012-01-12 14:53:51 +0000
@@ -7532,5 +7532,119 @@ field1
9
DROP TABLE t1, t2;
# End of test for bug#13541406.
+#
+# Bug#13576391: Missing rows on select with in-subquery and
+# batched-key-access=on and semijoin
+#
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) engine=InnoDB;
+INSERT INTO t1 VALUES
+(1,'v'), (7,'s'), (4,'l'), (7,'y'), (0,'c'), (2,'i'), (9,'h'), (4,'q'),
+(0,'a'), (9,'v'), (1,'u'), (3,'s'), (8,'y'), (8,'z'), (18,'h'), (84,'p'),
+(6,'e'), (3,'i'), (6,'y'), (6,'w');
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) engine=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+2 DEPENDENT SUBQUERY it index_subquery col_varchar_key col_varchar_key 3 func 1 Using where
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=MyISAM;
+ALTER TABLE t2 ENGINE=MyISAM;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+2 DEPENDENT SUBQUERY it index_subquery col_varchar_key col_varchar_key 3 func 2 Using where
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=Memory;
+ALTER TABLE t2 ENGINE=Memory;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+2 DEPENDENT SUBQUERY it index_subquery col_varchar_key col_varchar_key 3 func 2 Using where
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+DROP TABLE t1, t2;
+# End of test for bug#13576391.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result 2012-01-11 13:29:29 +0000
+++ b/mysql-test/r/subquery_sj_none.result 2012-01-12 14:53:51 +0000
@@ -7467,5 +7467,119 @@ field1
9
DROP TABLE t1, t2;
# End of test for bug#13541406.
+#
+# Bug#13576391: Missing rows on select with in-subquery and
+# batched-key-access=on and semijoin
+#
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) engine=InnoDB;
+INSERT INTO t1 VALUES
+(1,'v'), (7,'s'), (4,'l'), (7,'y'), (0,'c'), (2,'i'), (9,'h'), (4,'q'),
+(0,'a'), (9,'v'), (1,'u'), (3,'s'), (8,'y'), (8,'z'), (18,'h'), (84,'p'),
+(6,'e'), (3,'i'), (6,'y'), (6,'w');
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) engine=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+2 DEPENDENT SUBQUERY it index_subquery col_varchar_key col_varchar_key 3 func 1 Using where
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=MyISAM;
+ALTER TABLE t2 ENGINE=MyISAM;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+2 DEPENDENT SUBQUERY it index_subquery col_varchar_key col_varchar_key 3 func 2 Using where
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=Memory;
+ALTER TABLE t2 ENGINE=Memory;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+2 DEPENDENT SUBQUERY it index_subquery col_varchar_key col_varchar_key 3 func 2 Using where
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+DROP TABLE t1, t2;
+# End of test for bug#13576391.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none_bka.result'
--- a/mysql-test/r/subquery_sj_none_bka.result 2012-01-11 13:29:29 +0000
+++ b/mysql-test/r/subquery_sj_none_bka.result 2012-01-12 14:53:51 +0000
@@ -7468,6 +7468,120 @@ field1
9
DROP TABLE t1, t2;
# End of test for bug#13541406.
+#
+# Bug#13576391: Missing rows on select with in-subquery and
+# batched-key-access=on and semijoin
+#
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) engine=InnoDB;
+INSERT INTO t1 VALUES
+(1,'v'), (7,'s'), (4,'l'), (7,'y'), (0,'c'), (2,'i'), (9,'h'), (4,'q'),
+(0,'a'), (9,'v'), (1,'u'), (3,'s'), (8,'y'), (8,'z'), (18,'h'), (84,'p'),
+(6,'e'), (3,'i'), (6,'y'), (6,'w');
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) engine=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+2 DEPENDENT SUBQUERY it index_subquery col_varchar_key col_varchar_key 3 func 1 Using where
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=MyISAM;
+ALTER TABLE t2 ENGINE=MyISAM;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+2 DEPENDENT SUBQUERY it index_subquery col_varchar_key col_varchar_key 3 func 2 Using where
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=Memory;
+ALTER TABLE t2 ENGINE=Memory;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+2 DEPENDENT SUBQUERY it index_subquery col_varchar_key col_varchar_key 3 func 2 Using where
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+DROP TABLE t1, t2;
+# End of test for bug#13576391.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_none_bka_nixbnl.result 2012-01-11 13:29:29 +0000
+++ b/mysql-test/r/subquery_sj_none_bka_nixbnl.result 2012-01-12 14:53:51 +0000
@@ -7468,6 +7468,120 @@ field1
9
DROP TABLE t1, t2;
# End of test for bug#13541406.
+#
+# Bug#13576391: Missing rows on select with in-subquery and
+# batched-key-access=on and semijoin
+#
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) engine=InnoDB;
+INSERT INTO t1 VALUES
+(1,'v'), (7,'s'), (4,'l'), (7,'y'), (0,'c'), (2,'i'), (9,'h'), (4,'q'),
+(0,'a'), (9,'v'), (1,'u'), (3,'s'), (8,'y'), (8,'z'), (18,'h'), (84,'p'),
+(6,'e'), (3,'i'), (6,'y'), (6,'w');
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) engine=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+2 DEPENDENT SUBQUERY it index_subquery col_varchar_key col_varchar_key 3 func 1 Using where
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=MyISAM;
+ALTER TABLE t2 ENGINE=MyISAM;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+2 DEPENDENT SUBQUERY it index_subquery col_varchar_key col_varchar_key 3 func 2 Using where
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=Memory;
+ALTER TABLE t2 ENGINE=Memory;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+2 DEPENDENT SUBQUERY it index_subquery col_varchar_key col_varchar_key 3 func 2 Using where
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+DROP TABLE t1, t2;
+# End of test for bug#13576391.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none_bkaunique.result'
--- a/mysql-test/r/subquery_sj_none_bkaunique.result 2012-01-11 13:29:29 +0000
+++ b/mysql-test/r/subquery_sj_none_bkaunique.result 2012-01-12 14:53:51 +0000
@@ -7469,6 +7469,120 @@ field1
9
DROP TABLE t1, t2;
# End of test for bug#13541406.
+#
+# Bug#13576391: Missing rows on select with in-subquery and
+# batched-key-access=on and semijoin
+#
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_varchar_key varchar(1) NOT NULL,
+KEY col_varchar_key (col_varchar_key)
+) engine=InnoDB;
+INSERT INTO t1 VALUES
+(1,'v'), (7,'s'), (4,'l'), (7,'y'), (0,'c'), (2,'i'), (9,'h'), (4,'q'),
+(0,'a'), (9,'v'), (1,'u'), (3,'s'), (8,'y'), (8,'z'), (18,'h'), (84,'p'),
+(6,'e'), (3,'i'), (6,'y'), (6,'w');
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_varchar_nokey varchar(1) NOT NULL
+) engine=InnoDB;
+INSERT INTO t2 VALUES
+(4,'j'), (6,'v'), (3,'c'), (5,'m'), (3,'d'), (246,'d'), (2,'y'), (9,'t'),
+(3,'d'), (8,'s'), (1,'r'), (8,'m'), (8,'b'), (5,'x'), (7,'g'), (5,'p'),
+(1,'q'), (6,'w'), (2,'d'), (9,'e');
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+2 DEPENDENT SUBQUERY it index_subquery col_varchar_key col_varchar_key 3 func 1 Using where
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=MyISAM;
+ALTER TABLE t2 ENGINE=MyISAM;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+2 DEPENDENT SUBQUERY it index_subquery col_varchar_key col_varchar_key 3 func 2 Using where
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+ALTER TABLE t1 ENGINE=Memory;
+ALTER TABLE t2 ENGINE=Memory;
+explain SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot ALL NULL NULL NULL NULL 20 Using where; Using filesort
+2 DEPENDENT SUBQUERY it index_subquery col_varchar_key col_varchar_key 3 func 2 Using where
+SELECT col_varchar_nokey
+FROM t2 AS ot
+WHERE col_varchar_nokey IN (
+SELECT col_varchar_key
+FROM t1 AS it
+WHERE it.col_int_nokey <= it.col_int_nokey
+AND NOT ot.col_int_nokey < 2
+)
+ORDER BY col_varchar_nokey;
+col_varchar_nokey
+c
+e
+p
+s
+v
+w
+y
+DROP TABLE t1, t2;
+# End of test for bug#13576391.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'sql/filesort.cc'
--- a/sql/filesort.cc 2011-11-21 11:58:25 +0000
+++ b/sql/filesort.cc 2012-01-12 14:53:51 +0000
@@ -154,7 +154,8 @@ static void trace_filesort_information(O
@param select Condition to apply to the rows
@param max_rows Return only this many rows
@param sort_positions Set to TRUE if we want to force sorting by position
- (Needed by UPDATE/INSERT or ALTER TABLE)
+ (Needed by UPDATE/INSERT or ALTER TABLE or
+ when rowids are required by executor)
@param[out] examined_rows Store number of examined rows here
@param[out] found_rows Store the number of found rows here.
=== modified file 'sql/sql_executor.cc'
--- a/sql/sql_executor.cc 2012-01-05 09:37:44 +0000
+++ b/sql/sql_executor.cc 2012-01-12 14:53:51 +0000
@@ -3916,7 +3916,7 @@ create_sort_index(THD *thd, JOIN *join,
if (table->s->tmp_table)
table->file->info(HA_STATUS_VARIABLE); // Get record count
filesort_retval= filesort(thd, table, join->sortorder, length,
- select, filesort_limit, 0,
+ select, filesort_limit, tab->keep_current_rowid,
&examined_rows, &found_rows);
table->sort.found_records= filesort_retval;
tab->records= found_rows; // For SQL_CALC_ROWS
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (roy.lyseng:3516 to 3517) Bug#13576391 | Roy Lyseng | 12 Jan |
