List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:January 11 2012 1:58pm
Subject:bzr push into mysql-trunk branch (roy.lyseng:3514 to 3515) Bug#13541406
View as plain text  
 3515 Roy Lyseng	2012-01-11
      Bug#13541406: Wrong result with loosescan on select .. where .. in 
      
      To explain this problem, let's look at the query in the attached test:
      
      SELECT ot1.col_int_key AS field1
      FROM t2 AS ot1, t2 AS ot2
      WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
          SELECT it2.col_varchar_nokey, it1.col_varchar_key
          FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
      
      The selected plan for this query is (abbreviated):
       ot2  ALL
       it1  index                      col_varchar_key  ... LooseScan
       it2  ref(it1.pk)                col_int_key      FirstMatch(it1)
       ot1  ref(it2.col_varchar_nokey) col_varchar_key  Using index
      
      In English, this means:
      - The outermost loop is over the outer table ot2.
      - For each row of ot2, we perform a loose scan over it1.
        (Notice that we could have used a ref access using index
         col_varchar_key, but loosescan and ref access cannot be combined yet).
      - We use FirstMatch on table it2 to weed out duplicate rows from the
        subquery.
      - We use a ref access to look up rows in ot1, based on values from it2.
      
      However, this means that there is a dependency from it2 to ot1 and
      FirstMatch is only able to handle dependencies to outer tables that
      are earlier in the join prefix. Hence, LooseScan (combined with FirstMatch)
      is not a valid strategy for this query.
      
      The source of the problem is that get_bound_sj_equalities() sets the
      zeroth bit in bound_sj_equalities, even though the Loose_scan_opt
      function add_keyuse() performs a complete analysis of bound_sj_equalities
      and handled_sj_equalities later. Setting the zeroth bit has fatal
      consequences in few cases, because it is later overwritten or ignored,
      but it did have consequences in this case.
      
      Thus, the problem is solved by removing get_bound_sj_equalities() and
      initializing bound_sj_equalities to the empty set. As for the question
      on equality propagation inside the function, I do not think that is a
      problem because separate keyuse objects are set up for individual
      fields of a multiple equality.
      
      mysql-test/include/subquery_sj.inc
        Added test case for bug#13541406.
        Make sure that LooseScan is not selected.
      
      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#13541406.
      
      sql/sql_planner.cc
        Removed the function get_bound_sj_equalities() and initialized
        bound_sj_equalities to an empty set.
        Comment updates.
      
      sql/sql_select.h
        Comment updates in allowed table patterns for semi-join strategies.
      
      sql/sql_select.h
        Fixed a minor documentation inconsistency.

    modified:
      mysql-test/include/subquery_sj.inc
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_bka.result
      mysql-test/r/subquery_sj_all_bka_nixbnl.result
      mysql-test/r/subquery_sj_all_bkaunique.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_bka.result
      mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
      mysql-test/r/subquery_sj_dupsweed_bkaunique.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_bka.result
      mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
      mysql-test/r/subquery_sj_firstmatch_bkaunique.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_bka.result
      mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
      mysql-test/r/subquery_sj_loosescan_bkaunique.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_bka.result
      mysql-test/r/subquery_sj_mat_bka_nixbnl.result
      mysql-test/r/subquery_sj_mat_bkaunique.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_bka.result
      mysql-test/r/subquery_sj_none_bka_nixbnl.result
      mysql-test/r/subquery_sj_none_bkaunique.result
      sql/sql_planner.cc
      sql/sql_select.cc
      sql/sql_select.h
 3514 Guilhem Bichot	2012-01-11
      fix for pb2 after the last push: ORDER BY <alias> is now printed with alias.

    modified:
      mysql-test/r/archive_gis.result
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc	2012-01-05 09:37:44 +0000
+++ b/mysql-test/include/subquery_sj.inc	2012-01-11 13:29:29 +0000
@@ -4626,8 +4626,6 @@ DROP TABLE t1, t2, t3;
 
 --echo # End of test for bug#13545215.
 
---echo # End of 5.6 tests
-
 --echo #
 --echo # BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
 --echo # MATERIALIZATION + SEMIJOIN ON
@@ -4668,3 +4666,54 @@ eval EXPLAIN $query;
 eval $query;
 
 DROP TABLE t1;
+
+--echo #
+--echo # Bug#13541406: Wrong result with loosescan on select .. where .. in
+--echo #
+
+CREATE TABLE t1 (
+  col_int_key INT NOT NULL,
+  col_varchar_nokey VARCHAR(1) NOT NULL,
+  KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+
+INSERT INTO t1 VALUES
+ (7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+ (1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+ (9,'e'), (5,'i'), (0,'y'), (3,'w');
+
+CREATE TABLE t2 (
+  pk INT NOT NULL,
+  col_int_key INT NOT NULL,
+  col_varchar_key VARCHAR(1) NOT NULL,
+  col_varchar_nokey VARCHAR(1) NOT NULL,
+  PRIMARY KEY (pk),
+  KEY col_int_key (col_int_key),
+  KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+
+INSERT INTO t2 VALUES
+ (1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+ (5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+ (9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+ (13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+ (17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+
+let $query=
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+    SELECT it2.col_varchar_nokey, it1.col_varchar_key
+    FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+
+--echo # This query should never use a LooseScan strategy
+
+eval explain $query;
+--sorted_result
+eval $query;
+
+DROP TABLE t1, t2;
+
+--echo # End of test for bug#13541406.
+
+--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-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_all.result	2012-01-11 13:29:29 +0000
@@ -7331,7 +7331,6 @@ x
 x
 DROP TABLE t1, t2, t3;
 # End of test for bug#13545215.
-# End of 5.6 tests
 #
 # BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
 # MATERIALIZATION + SEMIJOIN ON
@@ -7395,4 +7394,67 @@ v	v	v
 v	v	v
 v	v	v
 DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	20	
+1	PRIMARY	it1	ref	PRIMARY,col_varchar_key	col_varchar_key	3	test.ot2.col_varchar_nokey	1	Using index; Start temporary
+1	PRIMARY	it2	ref	col_int_key	col_int_key	4	test.it1.pk	1	
+1	PRIMARY	ot1	ref	col_varchar_key	col_varchar_key	3	test.it2.col_varchar_nokey	1	Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# 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-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_all_bka.result	2012-01-11 13:29:29 +0000
@@ -7332,7 +7332,6 @@ x
 x
 DROP TABLE t1, t2, t3;
 # End of test for bug#13545215.
-# End of 5.6 tests
 #
 # BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
 # MATERIALIZATION + SEMIJOIN ON
@@ -7396,5 +7395,68 @@ v	v	v
 v	v	v
 v	v	v
 DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	20	Start temporary
+1	PRIMARY	it1	ref	PRIMARY,col_varchar_key	col_varchar_key	3	test.ot2.col_varchar_nokey	1	Using index
+1	PRIMARY	it2	ref	col_int_key	col_int_key	4	test.it1.pk	1	Using join buffer (Batched Key Access)
+1	PRIMARY	ot1	ref	col_varchar_key	col_varchar_key	3	test.it2.col_varchar_nokey	1	Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# 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-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_all_bka_nixbnl.result	2012-01-11 13:29:29 +0000
@@ -7332,7 +7332,6 @@ x
 x
 DROP TABLE t1, t2, t3;
 # End of test for bug#13545215.
-# End of 5.6 tests
 #
 # BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
 # MATERIALIZATION + SEMIJOIN ON
@@ -7396,5 +7395,68 @@ v	v	v
 v	v	v
 v	v	v
 DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	20	Start temporary
+1	PRIMARY	it1	ref	PRIMARY,col_varchar_key	col_varchar_key	3	test.ot2.col_varchar_nokey	1	Using index
+1	PRIMARY	it2	ref	col_int_key	col_int_key	4	test.it1.pk	1	Using join buffer (Batched Key Access)
+1	PRIMARY	ot1	ref	col_varchar_key	col_varchar_key	3	test.it2.col_varchar_nokey	1	Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# 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-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_all_bkaunique.result	2012-01-11 13:29:29 +0000
@@ -7333,7 +7333,6 @@ x
 x
 DROP TABLE t1, t2, t3;
 # End of test for bug#13545215.
-# End of 5.6 tests
 #
 # BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
 # MATERIALIZATION + SEMIJOIN ON
@@ -7397,5 +7396,68 @@ v	v	v
 v	v	v
 v	v	v
 DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	20	Start temporary
+1	PRIMARY	it1	ref	PRIMARY,col_varchar_key	col_varchar_key	3	test.ot2.col_varchar_nokey	1	Using index
+1	PRIMARY	it2	ref	col_int_key	col_int_key	4	test.it1.pk	1	Using join buffer (Batched Key Access (unique))
+1	PRIMARY	ot1	ref	col_varchar_key	col_varchar_key	3	test.it2.col_varchar_nokey	1	Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# 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-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result	2012-01-11 13:29:29 +0000
@@ -7330,7 +7330,6 @@ x
 x
 DROP TABLE t1, t2, t3;
 # End of test for bug#13545215.
-# End of 5.6 tests
 #
 # BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
 # MATERIALIZATION + SEMIJOIN ON
@@ -7394,4 +7393,67 @@ v	v	v
 v	v	v
 v	v	v
 DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	20	
+1	PRIMARY	it1	ref	PRIMARY,col_varchar_key	col_varchar_key	3	test.ot2.col_varchar_nokey	1	Using index; Start temporary
+1	PRIMARY	it2	ref	col_int_key	col_int_key	4	test.it1.pk	1	
+1	PRIMARY	ot1	ref	col_varchar_key	col_varchar_key	3	test.it2.col_varchar_nokey	1	Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# 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-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka.result	2012-01-11 13:29:29 +0000
@@ -7331,7 +7331,6 @@ x
 x
 DROP TABLE t1, t2, t3;
 # End of test for bug#13545215.
-# End of 5.6 tests
 #
 # BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
 # MATERIALIZATION + SEMIJOIN ON
@@ -7395,5 +7394,68 @@ v	v	v
 v	v	v
 v	v	v
 DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	20	Start temporary
+1	PRIMARY	it1	ref	PRIMARY,col_varchar_key	col_varchar_key	3	test.ot2.col_varchar_nokey	1	Using index
+1	PRIMARY	it2	ref	col_int_key	col_int_key	4	test.it1.pk	1	
+1	PRIMARY	ot1	ref	col_varchar_key	col_varchar_key	3	test.it2.col_varchar_nokey	1	Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# 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-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result	2012-01-11 13:29:29 +0000
@@ -7331,7 +7331,6 @@ x
 x
 DROP TABLE t1, t2, t3;
 # End of test for bug#13545215.
-# End of 5.6 tests
 #
 # BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
 # MATERIALIZATION + SEMIJOIN ON
@@ -7395,5 +7394,68 @@ v	v	v
 v	v	v
 v	v	v
 DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	20	Start temporary
+1	PRIMARY	it1	ref	PRIMARY,col_varchar_key	col_varchar_key	3	test.ot2.col_varchar_nokey	1	Using index
+1	PRIMARY	it2	ref	col_int_key	col_int_key	4	test.it1.pk	1	
+1	PRIMARY	ot1	ref	col_varchar_key	col_varchar_key	3	test.it2.col_varchar_nokey	1	Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# 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-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bkaunique.result	2012-01-11 13:29:29 +0000
@@ -7332,7 +7332,6 @@ x
 x
 DROP TABLE t1, t2, t3;
 # End of test for bug#13545215.
-# End of 5.6 tests
 #
 # BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
 # MATERIALIZATION + SEMIJOIN ON
@@ -7396,5 +7395,68 @@ v	v	v
 v	v	v
 v	v	v
 DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	20	Start temporary
+1	PRIMARY	it1	ref	PRIMARY,col_varchar_key	col_varchar_key	3	test.ot2.col_varchar_nokey	1	Using index
+1	PRIMARY	it2	ref	col_int_key	col_int_key	4	test.it1.pk	1	
+1	PRIMARY	ot1	ref	col_varchar_key	col_varchar_key	3	test.it2.col_varchar_nokey	1	Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# 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-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result	2012-01-11 13:29:29 +0000
@@ -7331,7 +7331,6 @@ x
 x
 DROP TABLE t1, t2, t3;
 # End of test for bug#13545215.
-# End of 5.6 tests
 #
 # BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
 # MATERIALIZATION + SEMIJOIN ON
@@ -7396,6 +7395,69 @@ v	v	v
 v	v	v
 DROP TABLE t1;
 #
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	20	
+1	PRIMARY	it1	ref	PRIMARY,col_varchar_key	col_varchar_key	3	test.ot2.col_varchar_nokey	1	Using index; Start temporary
+1	PRIMARY	it2	ref	col_int_key	col_int_key	4	test.it1.pk	1	
+1	PRIMARY	ot1	ref	col_varchar_key	col_varchar_key	3	test.it2.col_varchar_nokey	1	Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# End of 5.6 tests
+#
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for
 #           certain query plans
 #

=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka.result	2012-01-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka.result	2012-01-11 13:29:29 +0000
@@ -7332,7 +7332,6 @@ x
 x
 DROP TABLE t1, t2, t3;
 # End of test for bug#13545215.
-# End of 5.6 tests
 #
 # BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
 # MATERIALIZATION + SEMIJOIN ON
@@ -7397,6 +7396,69 @@ v	v	v
 v	v	v
 DROP TABLE t1;
 #
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	20	Start temporary
+1	PRIMARY	it1	ref	PRIMARY,col_varchar_key	col_varchar_key	3	test.ot2.col_varchar_nokey	1	Using index
+1	PRIMARY	it2	ref	col_int_key	col_int_key	4	test.it1.pk	1	
+1	PRIMARY	ot1	ref	col_varchar_key	col_varchar_key	3	test.it2.col_varchar_nokey	1	Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# End of 5.6 tests
+#
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for
 #           certain query plans
 #

=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result	2012-01-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result	2012-01-11 13:29:29 +0000
@@ -7332,7 +7332,6 @@ x
 x
 DROP TABLE t1, t2, t3;
 # End of test for bug#13545215.
-# End of 5.6 tests
 #
 # BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
 # MATERIALIZATION + SEMIJOIN ON
@@ -7397,6 +7396,69 @@ v	v	v
 v	v	v
 DROP TABLE t1;
 #
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	20	Start temporary
+1	PRIMARY	it1	ref	PRIMARY,col_varchar_key	col_varchar_key	3	test.ot2.col_varchar_nokey	1	Using index
+1	PRIMARY	it2	ref	col_int_key	col_int_key	4	test.it1.pk	1	
+1	PRIMARY	ot1	ref	col_varchar_key	col_varchar_key	3	test.it2.col_varchar_nokey	1	Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# End of 5.6 tests
+#
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for
 #           certain query plans
 #

=== modified file 'mysql-test/r/subquery_sj_firstmatch_bkaunique.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bkaunique.result	2012-01-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bkaunique.result	2012-01-11 13:29:29 +0000
@@ -7333,7 +7333,6 @@ x
 x
 DROP TABLE t1, t2, t3;
 # End of test for bug#13545215.
-# End of 5.6 tests
 #
 # BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
 # MATERIALIZATION + SEMIJOIN ON
@@ -7398,6 +7397,69 @@ v	v	v
 v	v	v
 DROP TABLE t1;
 #
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	20	Start temporary
+1	PRIMARY	it1	ref	PRIMARY,col_varchar_key	col_varchar_key	3	test.ot2.col_varchar_nokey	1	Using index
+1	PRIMARY	it2	ref	col_int_key	col_int_key	4	test.it1.pk	1	
+1	PRIMARY	ot1	ref	col_varchar_key	col_varchar_key	3	test.it2.col_varchar_nokey	1	Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# End of 5.6 tests
+#
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for
 #           certain query plans
 #

=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result	2012-01-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result	2012-01-11 13:29:29 +0000
@@ -7331,7 +7331,6 @@ x
 x
 DROP TABLE t1, t2, t3;
 # End of test for bug#13545215.
-# End of 5.6 tests
 #
 # BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
 # MATERIALIZATION + SEMIJOIN ON
@@ -7395,4 +7394,67 @@ v	v	v
 v	v	v
 v	v	v
 DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	20	
+1	PRIMARY	it1	ref	PRIMARY,col_varchar_key	col_varchar_key	3	test.ot2.col_varchar_nokey	1	Using index; Start temporary
+1	PRIMARY	it2	ref	col_int_key	col_int_key	4	test.it1.pk	1	
+1	PRIMARY	ot1	ref	col_varchar_key	col_varchar_key	3	test.it2.col_varchar_nokey	1	Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# 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-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka.result	2012-01-11 13:29:29 +0000
@@ -7332,7 +7332,6 @@ x
 x
 DROP TABLE t1, t2, t3;
 # End of test for bug#13545215.
-# End of 5.6 tests
 #
 # BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
 # MATERIALIZATION + SEMIJOIN ON
@@ -7396,5 +7395,68 @@ v	v	v
 v	v	v
 v	v	v
 DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	20	Start temporary
+1	PRIMARY	it1	ref	PRIMARY,col_varchar_key	col_varchar_key	3	test.ot2.col_varchar_nokey	1	Using index
+1	PRIMARY	it2	ref	col_int_key	col_int_key	4	test.it1.pk	1	
+1	PRIMARY	ot1	ref	col_varchar_key	col_varchar_key	3	test.it2.col_varchar_nokey	1	Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# 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-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result	2012-01-11 13:29:29 +0000
@@ -7332,7 +7332,6 @@ x
 x
 DROP TABLE t1, t2, t3;
 # End of test for bug#13545215.
-# End of 5.6 tests
 #
 # BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
 # MATERIALIZATION + SEMIJOIN ON
@@ -7396,5 +7395,68 @@ v	v	v
 v	v	v
 v	v	v
 DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	20	Start temporary
+1	PRIMARY	it1	ref	PRIMARY,col_varchar_key	col_varchar_key	3	test.ot2.col_varchar_nokey	1	Using index
+1	PRIMARY	it2	ref	col_int_key	col_int_key	4	test.it1.pk	1	
+1	PRIMARY	ot1	ref	col_varchar_key	col_varchar_key	3	test.it2.col_varchar_nokey	1	Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# 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-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bkaunique.result	2012-01-11 13:29:29 +0000
@@ -7333,7 +7333,6 @@ x
 x
 DROP TABLE t1, t2, t3;
 # End of test for bug#13545215.
-# End of 5.6 tests
 #
 # BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
 # MATERIALIZATION + SEMIJOIN ON
@@ -7397,5 +7396,68 @@ v	v	v
 v	v	v
 v	v	v
 DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	20	Start temporary
+1	PRIMARY	it1	ref	PRIMARY,col_varchar_key	col_varchar_key	3	test.ot2.col_varchar_nokey	1	Using index
+1	PRIMARY	it2	ref	col_int_key	col_int_key	4	test.it1.pk	1	
+1	PRIMARY	ot1	ref	col_varchar_key	col_varchar_key	3	test.it2.col_varchar_nokey	1	Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# 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-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_mat.result	2012-01-11 13:29:29 +0000
@@ -7331,7 +7331,6 @@ x
 x
 DROP TABLE t1, t2, t3;
 # End of test for bug#13545215.
-# End of 5.6 tests
 #
 # BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
 # MATERIALIZATION + SEMIJOIN ON
@@ -7395,4 +7394,67 @@ v	v	v
 v	v	v
 v	v	v
 DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	20	
+1	PRIMARY	it1	ref	PRIMARY,col_varchar_key	col_varchar_key	3	test.ot2.col_varchar_nokey	1	Using index; Start temporary
+1	PRIMARY	it2	ref	col_int_key	col_int_key	4	test.it1.pk	1	
+1	PRIMARY	ot1	ref	col_varchar_key	col_varchar_key	3	test.it2.col_varchar_nokey	1	Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# 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-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka.result	2012-01-11 13:29:29 +0000
@@ -7332,7 +7332,6 @@ x
 x
 DROP TABLE t1, t2, t3;
 # End of test for bug#13545215.
-# End of 5.6 tests
 #
 # BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
 # MATERIALIZATION + SEMIJOIN ON
@@ -7396,5 +7395,68 @@ v	v	v
 v	v	v
 v	v	v
 DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	20	Start temporary
+1	PRIMARY	it1	ref	PRIMARY,col_varchar_key	col_varchar_key	3	test.ot2.col_varchar_nokey	1	Using index
+1	PRIMARY	it2	ref	col_int_key	col_int_key	4	test.it1.pk	1	
+1	PRIMARY	ot1	ref	col_varchar_key	col_varchar_key	3	test.it2.col_varchar_nokey	1	Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# 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-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka_nixbnl.result	2012-01-11 13:29:29 +0000
@@ -7332,7 +7332,6 @@ x
 x
 DROP TABLE t1, t2, t3;
 # End of test for bug#13545215.
-# End of 5.6 tests
 #
 # BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
 # MATERIALIZATION + SEMIJOIN ON
@@ -7396,5 +7395,68 @@ v	v	v
 v	v	v
 v	v	v
 DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	20	Start temporary
+1	PRIMARY	it1	ref	PRIMARY,col_varchar_key	col_varchar_key	3	test.ot2.col_varchar_nokey	1	Using index
+1	PRIMARY	it2	ref	col_int_key	col_int_key	4	test.it1.pk	1	
+1	PRIMARY	ot1	ref	col_varchar_key	col_varchar_key	3	test.it2.col_varchar_nokey	1	Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# 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-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_mat_bkaunique.result	2012-01-11 13:29:29 +0000
@@ -7333,7 +7333,6 @@ x
 x
 DROP TABLE t1, t2, t3;
 # End of test for bug#13545215.
-# End of 5.6 tests
 #
 # BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
 # MATERIALIZATION + SEMIJOIN ON
@@ -7397,5 +7396,68 @@ v	v	v
 v	v	v
 v	v	v
 DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	20	Start temporary
+1	PRIMARY	it1	ref	PRIMARY,col_varchar_key	col_varchar_key	3	test.ot2.col_varchar_nokey	1	Using index
+1	PRIMARY	it2	ref	col_int_key	col_int_key	4	test.it1.pk	1	
+1	PRIMARY	ot1	ref	col_varchar_key	col_varchar_key	3	test.it2.col_varchar_nokey	1	Using index; End temporary
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# 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-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result	2012-01-11 13:29:29 +0000
@@ -7407,7 +7407,6 @@ x
 x
 DROP TABLE t1, t2, t3;
 # End of test for bug#13545215.
-# End of 5.6 tests
 #
 # BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
 # MATERIALIZATION + SEMIJOIN ON
@@ -7471,4 +7470,67 @@ v	v	v
 v	v	v
 v	v	v
 DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	index	NULL	col_varchar_key	7	NULL	20	Using index
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	20	Using where; Using join buffer (Block Nested Loop)
+2	SUBQUERY	it1	index	PRIMARY	col_varchar_key	7	NULL	20	Using index
+2	SUBQUERY	it2	ref	col_int_key	col_int_key	4	test.it1.pk	1	
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# 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-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_none.result	2012-01-11 13:29:29 +0000
@@ -7342,7 +7342,6 @@ x
 x
 DROP TABLE t1, t2, t3;
 # End of test for bug#13545215.
-# End of 5.6 tests
 #
 # BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
 # MATERIALIZATION + SEMIJOIN ON
@@ -7406,4 +7405,67 @@ v	v	v
 v	v	v
 v	v	v
 DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	index	NULL	col_varchar_key	7	NULL	20	Using index
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	20	Using where; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	it1	ref	PRIMARY,col_varchar_key	col_varchar_key	3	func	1	Using index
+2	DEPENDENT SUBQUERY	it2	ref	col_int_key	col_int_key	4	test.it1.pk	1	Using where
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# 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-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_none_bka.result	2012-01-11 13:29:29 +0000
@@ -7343,7 +7343,6 @@ x
 x
 DROP TABLE t1, t2, t3;
 # End of test for bug#13545215.
-# End of 5.6 tests
 #
 # BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
 # MATERIALIZATION + SEMIJOIN ON
@@ -7407,5 +7406,68 @@ v	v	v
 v	v	v
 v	v	v
 DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	index	NULL	col_varchar_key	7	NULL	20	Using index
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	20	Using where; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	it1	ref	PRIMARY,col_varchar_key	col_varchar_key	3	func	1	Using index
+2	DEPENDENT SUBQUERY	it2	ref	col_int_key	col_int_key	4	test.it1.pk	1	Using where
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# 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-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_none_bka_nixbnl.result	2012-01-11 13:29:29 +0000
@@ -7343,7 +7343,6 @@ x
 x
 DROP TABLE t1, t2, t3;
 # End of test for bug#13545215.
-# End of 5.6 tests
 #
 # BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
 # MATERIALIZATION + SEMIJOIN ON
@@ -7407,5 +7406,68 @@ v	v	v
 v	v	v
 v	v	v
 DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	index	NULL	col_varchar_key	7	NULL	20	Using index
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	20	Using where
+2	DEPENDENT SUBQUERY	it1	ref	PRIMARY,col_varchar_key	col_varchar_key	3	func	1	Using index
+2	DEPENDENT SUBQUERY	it2	ref	col_int_key	col_int_key	4	test.it1.pk	1	Using where
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# 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-05 09:37:44 +0000
+++ b/mysql-test/r/subquery_sj_none_bkaunique.result	2012-01-11 13:29:29 +0000
@@ -7344,7 +7344,6 @@ x
 x
 DROP TABLE t1, t2, t3;
 # End of test for bug#13545215.
-# End of 5.6 tests
 #
 # BUG#13553211 - MISSING ROWS ON SELECT WITH IN-SUBQUERY AND
 # MATERIALIZATION + SEMIJOIN ON
@@ -7408,5 +7407,68 @@ v	v	v
 v	v	v
 v	v	v
 DROP TABLE t1;
+#
+# Bug#13541406: Wrong result with loosescan on select .. where .. in
+#
+CREATE TABLE t1 (
+col_int_key INT NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+KEY col_int_key (col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t1 VALUES
+(7,'v'), (0,'s'), (9,'l'), (3,'y'), (4,'c'), (2,'i'), (5,'h'), (3,'q'),
+(1,'a'), (3,'v'), (6,'u'), (7,'s'), (5,'y'), (1,'z'), (204,'h'), (224,'p'),
+(9,'e'), (5,'i'), (0,'y'), (3,'w');
+CREATE TABLE t2 (
+pk INT NOT NULL,
+col_int_key INT NOT NULL,
+col_varchar_key VARCHAR(1) NOT NULL,
+col_varchar_nokey VARCHAR(1) NOT NULL,
+PRIMARY KEY (pk),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ENGINE=InnoDB;
+INSERT INTO t2 VALUES
+(1,0,'j','j'), (2,8,'v','v'), (3,1,'c','c'), (4,8,'m','m'),
+(5,9,'d','d'), (6,24,'d','d'), (7,6,'y','y'), (8,1,'t','t'),
+(9,6,'d','d'), (10,2,'s','s'), (11,4,'r','r'), (12,8,'m','m'),
+(13,4,'b','b'), (14,4,'x','x'), (15,7,'g','g'), (16,4,'p','p'),
+(17,1,'q','q'), (18,9,'w','w'), (19,4,'d','d'), (20,8,'e','e');
+# This query should never use a LooseScan strategy
+explain SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	index	NULL	col_varchar_key	7	NULL	20	Using index
+1	PRIMARY	ot2	ALL	NULL	NULL	NULL	NULL	20	Using where; Using join buffer (Block Nested Loop)
+2	DEPENDENT SUBQUERY	it1	ref	PRIMARY,col_varchar_key	col_varchar_key	3	func	1	Using index
+2	DEPENDENT SUBQUERY	it2	ref	col_int_key	col_int_key	4	test.it1.pk	1	Using where
+SELECT ot1.col_int_key AS field1
+FROM t2 AS ot1, t2 AS ot2
+WHERE (ot1.col_varchar_key, ot2.col_varchar_nokey) IN (
+SELECT it2.col_varchar_nokey, it1.col_varchar_key
+FROM t2 AS it1 JOIN t1 AS it2 ON it2.col_int_key = it1.pk);
+field1
+1
+1
+1
+2
+6
+6
+6
+6
+6
+8
+8
+8
+8
+8
+8
+9
+DROP TABLE t1, t2;
+# End of test for bug#13541406.
+# End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'sql/sql_planner.cc'
--- a/sql/sql_planner.cc	2011-12-16 14:39:02 +0000
+++ b/sql/sql_planner.cc	2012-01-11 13:29:29 +0000
@@ -138,47 +138,6 @@ join_tab_cmp_embedded_first(const void *
 
 
 /*
-  Given a semi-join nest, find out which of the IN-equalities are bound
-
-  SYNOPSIS
-    get_bound_sj_equalities()
-      sj_nest           Semi-join nest
-      remaining_tables  Tables that are not yet bound
-
-  DESCRIPTION
-    Given a semi-join nest, find out which of the IN-equalities have their
-    left part expression bound (i.e. the said expression doesn't refer to
-    any of remaining_tables and can be evaluated).
-
-  RETURN
-    Bitmap of bound IN-equalities.
-*/
-
-static ulonglong get_bound_sj_equalities(TABLE_LIST *sj_nest, 
-                                         table_map remaining_tables)
-{
-  List_iterator<Item> li(sj_nest->nested_join->sj_outer_exprs);
-  Item *item;
-  uint i= 0;
-  ulonglong res= 0;
-  while ((item= li++))
-  {
-    /*
-      Q: should this take into account equality propagation and how?
-      A: If e->outer_side is an Item_field, walk over the equality
-         class and see if there is an element that is bound?
-      (this is an optional feature)
-    */
-    if (!(item->used_tables() & remaining_tables))
-    {
-      res |= 1ULL << i;
-    }
-  }
-  return res;
-}
-
-
-/*
   This is a class for considering possible loose index scan optimizations.
   It's usage pattern is as follows:
     best_access_path()
@@ -265,36 +224,35 @@ public:
             table_map cur_sj_inner_tables, bool is_sjm_nest)
   {
     /*
-      Discover the bound equalities. We need to do this if
+      We may consider the LooseScan strategy if
         1. The next table is an SJ-inner table, and
-        2. It is the first table from that semijoin, and
-        3. We're not within a semi-join range (i.e. all semi-joins either have
+        2, We have no more than 64 IN expressions (must fit in bitmap), and
+        3. It is the first table from that semijoin, and
+        4. We're not within a semi-join range (i.e. all semi-joins either have
            all or none of their tables in join_table_map), except
-           s->emb_sj_nest (which we've just entered, see #2).
-        4. All non-IN-equality correlation references from this sj-nest are 
-           bound
-        5. But some of the IN-equalities aren't (so this can't be handled by 
-           FirstMatch strategy)
-        6. Not a derived table/view. (a temporary restriction)
+           s->emb_sj_nest (which we've just entered, see #2), and
+        5. All non-IN-equality correlation references from this sj-nest are 
+           bound, and
+        6. But some of the IN-equalities aren't (so this can't be handled by 
+           FirstMatch strategy), and
+        7. LooseScan is not disabled, and
+        8. Not a derived table/view. (a temporary restriction)
     */
     best_loose_scan_cost= DBL_MAX;
     if (s->emb_sj_nest && !is_sjm_nest &&                               // (1)
-        s->emb_sj_nest->nested_join->sj_inner_exprs.elements < 64 && 
-        ((remaining_tables & s->emb_sj_nest->sj_inner_tables) ==        // (2)
-         s->emb_sj_nest->sj_inner_tables) &&                            // (2)
-        cur_sj_inner_tables == 0 &&                                     // (3)
+        s->emb_sj_nest->nested_join->sj_inner_exprs.elements <= 64 &&   // (2)
+        ((remaining_tables & s->emb_sj_nest->sj_inner_tables) ==        // (3)
+         s->emb_sj_nest->sj_inner_tables) &&                            // (3)
+        cur_sj_inner_tables == 0 &&                                     // (4)
         !(remaining_tables & 
-          s->emb_sj_nest->nested_join->sj_corr_tables) &&               // (4)
-        (remaining_tables & s->emb_sj_nest->nested_join->sj_depends_on) &&// (5)
-        s->join->thd->optimizer_switch_flag(OPTIMIZER_SWITCH_LOOSE_SCAN) &&
-        !s->table->pos_in_table_list->uses_materialization())
-    {
-      /* This table is an LooseScan scan candidate */
-      bound_sj_equalities= get_bound_sj_equalities(s->emb_sj_nest, 
-                                                   remaining_tables);
-      try_loosescan= TRUE;
-      DBUG_PRINT("info", ("Will try LooseScan scan, bound_map=%llx",
-                          (longlong)bound_sj_equalities));
+          s->emb_sj_nest->nested_join->sj_corr_tables) &&               // (5)
+        (remaining_tables & s->emb_sj_nest->nested_join->sj_depends_on) && //(6)
+        s->join->thd->optimizer_switch_flag(OPTIMIZER_SWITCH_LOOSE_SCAN) &&//(7)
+        !s->table->pos_in_table_list->uses_materialization())           // (8)
+    {
+      try_loosescan= true;      // This table is a LooseScan scan candidate
+      bound_sj_equalities= 0;   // These equalities are populated later
+      DBUG_PRINT("info", ("Will try LooseScan scan"));
     }
   }
 

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2012-01-05 14:20:14 +0000
+++ b/sql/sql_select.cc	2012-01-11 13:29:29 +0000
@@ -328,10 +328,15 @@ static bool might_do_join_buffering(uint
             that the correlation is not just through the IN-equality).
       
       (2) - Inner table for which the LooseScan scan is performed.
+            Notice that special requirements for existence of certain indexes
+            apply to this table, @see class Loose_scan_opt.
 
       (3) - The remainder of the duplicate-generating range. It is served by 
-            application of FirstMatch strategy, with the exception that
-            outer IN-correlated tables are considered to be non-correlated.
+            application of FirstMatch strategy. Outer IN-correlated tables
+            must be correlated to the LooseScan table but not to the inner
+            tables in this range. (Currently, there can be no outer tables
+            in this range because of implementation restrictions,
+            @see Optimize_table_order::advance_sj_state()).
 
       (4) - The suffix of outer correlated and non-correlated tables.
 

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2011-12-15 15:15:37 +0000
+++ b/sql/sql_select.h	2012-01-11 13:29:29 +0000
@@ -101,7 +101,7 @@ public:
   */
   bool *cond_guard;
   /**
-     0..64    <=> This was created from semi-join IN-equality # sj_pred_no.
+     0..63    <=> This was created from semi-join IN-equality # sj_pred_no.
      UINT_MAX  Otherwise
 
      Not used if the index is fulltext (such index cannot be used for

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (roy.lyseng:3514 to 3515) Bug#13541406Roy Lyseng11 Jan