List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:January 12 2012 2:54pm
Subject:bzr push into mysql-trunk branch (roy.lyseng:3516 to 3517) Bug#13576391
View as plain text  
 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#13576391Roy Lyseng12 Jan