List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:August 13 2010 12:49pm
Subject:bzr commit into mysql-next-mr-opt-backporting branch (jorgen.loland:3228)
Bug#49129
View as plain text  
#At file:///export/home/jl208045/mysql/mysql-next-mr-opt-backporting-49129/ based on revid:guilhem.bichot@stripped

 3228 Jorgen Loland	2010-08-13
      Bug#49129 - Wrong result with IN-subquery with join_cache_level=6 
                  and firstmatch=off
      
      Patch based on contribution from Sergey Petrunia.
      
      Consider the query:
      
      SELECT * FROM t0 WHERE t0.a IN (
         SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
      
      With join cache level 6, this query only returns the first tuple
      from t0 that has a match in the subquery. Consider the relevant part
      of EXPLAIN:
      
      t0    | Using where                                   |
      t1    | Start temporary; Using join buffer            |
      t2    | Using where; End temporary; Using join buffer |
      
      When the optimizer decides to use join buffers, temporary tables
      created for duplicate weedout should extend to the first table
      after const tables. I.e., Start temporary should be printed for
      t0 above.
      
      The reason for the bug is that setup_semijoin_dups_elimination()
      is called before the final decision is made in
      check_join_cache_usage() on whether or not to use join buffering.
      In this case, use_join_buffer==false for t1 and t2 during
      setup_semijoin_dups_elimination(), and the range of tables to
      buffer is therefore not extended to t0.
      
      Since check_join_cache_usage() needs to know if duplicate weedout
      is used, so moving setup_semijoin_dups_elimination() from before
      check_join_cache_usage() to after it is not possible. 
      
      The temporary fix of this patch is to use a rough estimate of
      whether join buffering will be used in
      setup_semijoin_dups_elimination(). This rough test covers more 
      cases than actually end up with join buffering, and in these cases 
      we now extend the temporary table to store rowids for more tables 
      than strictly required, i.e., the first table up to the start of 
      the semijoin. A proper (but much more costly to do) fix would be to 
      merge the loops of setup_semijoin_dups_elimination() and 
      make_join_readinfo() (which calls check_join_cache_usage()).
     @ mysql-test/include/subquery_sj.inc
        Added test for BUG#49129
     @ mysql-test/r/subquery_sj_all.result
        Added test for BUG#49129 and recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_all_jcl6.result
        Added test for BUG#49129 and recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_all_jcl7.result
        Added test for BUG#49129 and recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_dupsweed.result
        Added test for BUG#49129 and recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_dupsweed_jcl6.result
        Added test for BUG#49129 and recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_dupsweed_jcl7.result
        Added test for BUG#49129 and recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_firstmatch.result
        Added test for BUG#49129 and recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_firstmatch_jcl6.result
        Added test for BUG#49129 and recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_firstmatch_jcl7.result
        Added test for BUG#49129 and recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_loosescan.result
        Added test for BUG#49129 and recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_loosescan_jcl6.result
        Added test for BUG#49129 and recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_loosescan_jcl7.result
        Added test for BUG#49129 and recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_mat.result
        Added test for BUG#49129 and recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_mat_jcl6.result
        Added test for BUG#49129 and recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_mat_jcl7.result
        Added test for BUG#49129 and recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_mat_nosj.result
        Added test for BUG#49129 and recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_none.result
        Added test for BUG#49129 and recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_none_jcl6.result
        Added test for BUG#49129 and recorded new explain output for queries using duplicate weedout.
     @ mysql-test/r/subquery_sj_none_jcl7.result
        Added test for BUG#49129 and recorded new explain output for queries using duplicate weedout.
     @ sql/sql_select.cc
        setup_semijoin_dups_elimination() relied on
        best_position[i].use_join_buffer when checking whether temporary
        tables for duplicate weedout should be extended to the first
        table of the execution plan. However, use_join_buffer is not
        reliable at this stage, so setup_semijoin_dups_elimination()
        needs to use a rough estimate instead.

    modified:
      mysql-test/include/subquery_sj.inc
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_jcl6.result
      mysql-test/r/subquery_sj_all_jcl7.result
      mysql-test/r/subquery_sj_dupsweed.result
      mysql-test/r/subquery_sj_dupsweed_jcl6.result
      mysql-test/r/subquery_sj_dupsweed_jcl7.result
      mysql-test/r/subquery_sj_firstmatch.result
      mysql-test/r/subquery_sj_firstmatch_jcl6.result
      mysql-test/r/subquery_sj_firstmatch_jcl7.result
      mysql-test/r/subquery_sj_loosescan.result
      mysql-test/r/subquery_sj_loosescan_jcl6.result
      mysql-test/r/subquery_sj_loosescan_jcl7.result
      mysql-test/r/subquery_sj_mat.result
      mysql-test/r/subquery_sj_mat_jcl6.result
      mysql-test/r/subquery_sj_mat_jcl7.result
      mysql-test/r/subquery_sj_mat_nosj.result
      mysql-test/r/subquery_sj_none.result
      mysql-test/r/subquery_sj_none_jcl6.result
      mysql-test/r/subquery_sj_none_jcl7.result
      sql/sql_join_cache.cc
      sql/sql_select.cc
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc	2010-08-12 09:02:11 +0000
+++ b/mysql-test/include/subquery_sj.inc	2010-08-13 12:49:50 +0000
@@ -2790,3 +2790,30 @@ eval explain $query;
 eval $query;
 
 drop table t1,t2,t3;
+
+--echo # 
+--echo # BUG#49129: Wrong result with IN-subquery with join_cache_level=6
+--echo #            and firstmatch=off
+--echo # 
+
+CREATE TABLE t0 (a INT);
+CREATE TABLE t1 (a INT, b INT, KEY(a));
+CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
+
+INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
+INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
+INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
+
+--echo
+--echo # Should contain records 0..3
+SELECT * FROM t0 WHERE t0.a IN 
+  (SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+
+--echo
+EXPLAIN 
+SELECT * FROM t0 WHERE t0.a IN 
+  (SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+
+DROP TABLE t0, t1, t2;
+
+--echo # End BUG#49129

=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result	2010-08-12 11:48:10 +0000
+++ b/mysql-test/r/subquery_sj_all.result	2010-08-13 12:49:50 +0000
@@ -2577,8 +2577,8 @@ explain select * 
 from t0 where a in
 (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
-1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Start temporary; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Start temporary
+1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Using join buffer (BNL, regular buffers)
 1	PRIMARY	t2	ref	a	a	5	test.t1.a	1	Using index
 1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End temporary
 drop table t0, t1,t2,t3;
@@ -4507,4 +4507,33 @@ a
 1
 1
 drop table t1,t2,t3;
+# 
+# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
+#            and firstmatch=off
+# 
+CREATE TABLE t0 (a INT);
+CREATE TABLE t1 (a INT, b INT, KEY(a));
+CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
+INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
+INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
+INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
+
+# Should contain records 0..3
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+a
+0
+1
+2
+3
+
+EXPLAIN 
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	Using where
+1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t0.a	1	
+1	PRIMARY	t1	ref	a	a	5	test.t2.a	2	Using index condition; Using where; FirstMatch(t2)
+DROP TABLE t0, t1, t2;
+# End BUG#49129
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_all_jcl6.result'
--- a/mysql-test/r/subquery_sj_all_jcl6.result	2010-08-12 11:48:10 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl6.result	2010-08-13 12:49:50 +0000
@@ -2581,8 +2581,8 @@ explain select * 
 from t0 where a in
 (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
-1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Start temporary; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Start temporary
+1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Using join buffer (BNL, incremental buffers)
 1	PRIMARY	t2	ref	a	a	5	test.t1.a	1	Using index
 1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End temporary
 drop table t0, t1,t2,t3;
@@ -4052,8 +4052,8 @@ explain extended select * from t0
 where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and
 t1.b=t2.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
-1	PRIMARY	t1	ref	a	a	5	test.t0.a	1	100.00	Start temporary; Using join buffer (BKA, incremental buffers)
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Start temporary
+1	PRIMARY	t1	ref	a	a	5	test.t0.a	1	100.00	Using join buffer (BKA, incremental buffers)
 1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t0.a	1	100.00	Using where; End temporary; Using join buffer (BKA, incremental buffers)
 Warnings:
 Note	1276	Field or reference 'test.t0.a' of SELECT #2 was resolved in SELECT #1
@@ -4064,11 +4064,12 @@ create temporary table tmp select * from
 (select t1.a from t1, t2 where t2.a=t0.a and t1.b=t2.b);
 create temporary table tmp_as_ref (a int);
 insert into tmp_as_ref values(0),(1),(2),(3);
-
-# The result below is wrong due to Bug#49129
 select * from tmp;
 a
 0
+1
+2
+3
 drop table t0, t1, t2, tmp, tmp_as_ref;
 CREATE TABLE t1 (
 id int(11) NOT NULL,
@@ -4510,6 +4511,35 @@ a
 1
 1
 drop table t1,t2,t3;
+# 
+# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
+#            and firstmatch=off
+# 
+CREATE TABLE t0 (a INT);
+CREATE TABLE t1 (a INT, b INT, KEY(a));
+CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
+INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
+INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
+INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
+
+# Should contain records 0..3
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+a
+0
+1
+2
+3
+
+EXPLAIN 
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	Using where
+1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t0.a	1	Using join buffer (BKA, incremental buffers)
+1	PRIMARY	t1	ref	a	a	5	test.t2.a	2	Using index condition; Using where; FirstMatch(t2); Using join buffer (BKA, incremental buffers)
+DROP TABLE t0, t1, t2;
+# End BUG#49129
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_all_jcl7.result'
--- a/mysql-test/r/subquery_sj_all_jcl7.result	2010-08-12 11:48:10 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl7.result	2010-08-13 12:49:50 +0000
@@ -2581,8 +2581,8 @@ explain select * 
 from t0 where a in
 (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
-1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Start temporary; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Start temporary
+1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Using join buffer (BNL, regular buffers)
 1	PRIMARY	t2	ref	a	a	5	test.t1.a	1	Using index
 1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End temporary
 drop table t0, t1,t2,t3;
@@ -4052,8 +4052,8 @@ explain extended select * from t0
 where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and
 t1.b=t2.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
-1	PRIMARY	t1	ref	a	a	5	test.t0.a	1	100.00	Start temporary; Using join buffer (BKA_UNIQUE, regular buffers)
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Start temporary
+1	PRIMARY	t1	ref	a	a	5	test.t0.a	1	100.00	Using join buffer (BKA_UNIQUE, regular buffers)
 1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t0.a	1	100.00	Using where; End temporary; Using join buffer (BKA_UNIQUE, regular buffers)
 Warnings:
 Note	1276	Field or reference 'test.t0.a' of SELECT #2 was resolved in SELECT #1
@@ -4064,11 +4064,12 @@ create temporary table tmp select * from
 (select t1.a from t1, t2 where t2.a=t0.a and t1.b=t2.b);
 create temporary table tmp_as_ref (a int);
 insert into tmp_as_ref values(0),(1),(2),(3);
-
-# The result below is wrong due to Bug#49129
 select * from tmp;
 a
 0
+1
+2
+3
 drop table t0, t1, t2, tmp, tmp_as_ref;
 CREATE TABLE t1 (
 id int(11) NOT NULL,
@@ -4510,6 +4511,35 @@ a
 1
 1
 drop table t1,t2,t3;
+# 
+# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
+#            and firstmatch=off
+# 
+CREATE TABLE t0 (a INT);
+CREATE TABLE t1 (a INT, b INT, KEY(a));
+CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
+INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
+INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
+INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
+
+# Should contain records 0..3
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+a
+0
+1
+2
+3
+
+EXPLAIN 
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	Using where
+1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t0.a	1	Using join buffer (BKA_UNIQUE, regular buffers)
+1	PRIMARY	t1	ref	a	a	5	test.t2.a	2	Using index condition; Using where; FirstMatch(t2); Using join buffer (BKA_UNIQUE, regular buffers)
+DROP TABLE t0, t1, t2;
+# End BUG#49129
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result	2010-08-12 11:48:10 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result	2010-08-13 12:49:50 +0000
@@ -1686,8 +1686,8 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary; End temporary
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary
 SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -1727,8 +1727,8 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary; End temporary
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -1833,8 +1833,8 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary; End temporary
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary
 SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 i
@@ -2576,8 +2576,8 @@ explain select * 
 from t0 where a in
 (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
-1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Start temporary; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Start temporary
+1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Using join buffer (BNL, regular buffers)
 1	PRIMARY	t2	ref	a	a	5	test.t1.a	1	Using index
 1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End temporary
 drop table t0, t1,t2,t3;
@@ -4506,4 +4506,33 @@ a
 1
 1
 drop table t1,t2,t3;
+# 
+# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
+#            and firstmatch=off
+# 
+CREATE TABLE t0 (a INT);
+CREATE TABLE t1 (a INT, b INT, KEY(a));
+CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
+INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
+INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
+INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
+
+# Should contain records 0..3
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+a
+0
+1
+2
+3
+
+EXPLAIN 
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	Using where; Start temporary
+1	PRIMARY	t1	ALL	a	NULL	NULL	NULL	5	Using where; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t0.a	1	Using where; End temporary
+DROP TABLE t0, t1, t2;
+# End BUG#49129
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl6.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl6.result	2010-08-12 11:48:10 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl6.result	2010-08-13 12:49:50 +0000
@@ -59,8 +59,8 @@ a
 4
 explain extended select * from t2 where t2.a in (select a from t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t2	index	a	a	5	NULL	4	100.00	Using where; Using index
-1	PRIMARY	t1	ref	a	a	5	test.t2.a	101	100.00	Using index; Start temporary; End temporary
+1	PRIMARY	t2	index	a	a	5	NULL	4	100.00	Using where; Using index; Start temporary
+1	PRIMARY	t1	ref	a	a	5	test.t2.a	101	100.00	Using index; End temporary
 Warnings:
 Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where (`test`.`t1`.`a` = `test`.`t2`.`a`)
 select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
@@ -69,8 +69,8 @@ a
 4
 explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t2	index	a	a	5	NULL	4	100.00	Using where; Using index
-1	PRIMARY	t1	ref	a	a	5	test.t2.a	101	100.00	Using where; Using index; Start temporary; End temporary
+1	PRIMARY	t2	index	a	a	5	NULL	4	100.00	Using where; Using index; Start temporary
+1	PRIMARY	t1	ref	a	a	5	test.t2.a	101	100.00	Using where; Using index; End temporary
 Warnings:
 Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30))
 select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
@@ -96,8 +96,8 @@ a
 4
 explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t2	index	a	a	5	NULL	4	100.00	Using where; Using index
-1	PRIMARY	t1	ref	a	a	5	test.t2.a	101	100.00	Using where; Using index; Start temporary; End temporary
+1	PRIMARY	t2	index	a	a	5	NULL	4	100.00	Using where; Using index; Start temporary
+1	PRIMARY	t1	ref	a	a	5	test.t2.a	101	100.00	Using where; Using index; End temporary
 Warnings:
 Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30))
 drop table t0, t1, t2, t3;
@@ -1690,8 +1690,8 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary; End temporary; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -1731,8 +1731,8 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary; End temporary; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -1837,8 +1837,8 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary; End temporary; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 i
@@ -2179,8 +2179,8 @@ create table t3 (a int);
 insert into t3 select A.a + 10*B.a from t0 A, t0 B;
 explain select * from t3 where a in (select kp1 from t1 where kp1<20);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	Using where
-1	PRIMARY	t1	ref	kp1	kp1	5	test.t3.a	1	Using index; Start temporary; End temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	Using where; Start temporary
+1	PRIMARY	t1	ref	kp1	kp1	5	test.t3.a	1	Using index; End temporary
 select * from t3 where a in (select kp1 from t1 where kp1<20);
 a
 0
@@ -2208,8 +2208,8 @@ insert into t4 select a from t3;
 explain select * from t3 where a in 
 (select t1.kp1 from t1,t4 where kp1<20 and t4.pk=t1.c);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	Using where
-1	PRIMARY	t1	ref	kp1	kp1	5	test.t3.a	1	Using where; Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	Using where; Start temporary
+1	PRIMARY	t1	ref	kp1	kp1	5	test.t3.a	1	Using where
 1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t1.c	1	Using index; End temporary
 select * from t3 where a in 
 (select t1.kp1 from t1,t4 where kp1<20 and t4.pk=t1.c);
@@ -2370,12 +2370,12 @@ insert into t1 select A.a, B.a, 'filler'
 create table t2 as select * from t1;
 explain select * from t2 where a in (select b from t1 where a=3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	100	Using where
-1	PRIMARY	t1	ref	a	a	10	const,test.t2.a	1	Using index; Start temporary; End temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	100	Using where; Start temporary
+1	PRIMARY	t1	ref	a	a	10	const,test.t2.a	1	Using index; End temporary
 explain select * from t2 where (b,a) in (select a,b from t1 where a=3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	100	Using where
-1	PRIMARY	t1	ref	a	a	10	const,test.t2.a	1	Using index; Start temporary; End temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	100	Using where; Start temporary
+1	PRIMARY	t1	ref	a	a	10	const,test.t2.a	1	Using index; End temporary
 drop table t1,t2;
 create table t1 (a int, b int);
 insert into t1 select a,a from t0;
@@ -2580,8 +2580,8 @@ explain select * 
 from t0 where a in
 (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
-1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Start temporary; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Start temporary
+1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Using join buffer (BNL, incremental buffers)
 1	PRIMARY	t2	ref	a	a	5	test.t1.a	1	Using index
 1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End temporary
 drop table t0, t1,t2,t3;
@@ -4029,8 +4029,8 @@ EXPLAIN SELECT Name FROM t1 
 WHERE t1.Code IN (
 SELECT t2.CountryCode FROM t2 WHERE Population > 5000000);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	31	
-1	PRIMARY	t2	ref	CountryCode	CountryCode	3	test.t1.Code	18	Using where; Start temporary; End temporary
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	31	Start temporary
+1	PRIMARY	t2	ref	CountryCode	CountryCode	3	test.t1.Code	18	Using where; End temporary
 SELECT Name FROM t1 
 WHERE t1.Code IN (
 SELECT t2.CountryCode FROM t2 WHERE Population > 5000000);
@@ -4051,8 +4051,8 @@ explain extended select * from t0
 where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and
 t1.b=t2.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
-1	PRIMARY	t1	ref	a	a	5	test.t0.a	1	100.00	Start temporary
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Start temporary
+1	PRIMARY	t1	ref	a	a	5	test.t0.a	1	100.00	
 1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t0.a	1	100.00	Using where; End temporary
 Warnings:
 Note	1276	Field or reference 'test.t0.a' of SELECT #2 was resolved in SELECT #1
@@ -4307,8 +4307,8 @@ alter table t3 add primary key(id), add 
 The following must use loose index scan over t3, key a:
 explain select count(a) from t2 where a in ( SELECT  a FROM t3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	index	a	a	5	NULL	1000	Using where; Using index
-1	PRIMARY	t3	ref	a	a	5	test.t2.a	30	Using index; Start temporary; End temporary
+1	PRIMARY	t2	index	a	a	5	NULL	1000	Using where; Using index; Start temporary
+1	PRIMARY	t3	ref	a	a	5	test.t2.a	30	Using index; End temporary
 select count(a) from t2 where a in ( SELECT  a FROM t3);
 count(a)
 1000
@@ -4510,6 +4510,35 @@ a
 1
 1
 drop table t1,t2,t3;
+# 
+# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
+#            and firstmatch=off
+# 
+CREATE TABLE t0 (a INT);
+CREATE TABLE t1 (a INT, b INT, KEY(a));
+CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
+INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
+INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
+INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
+
+# Should contain records 0..3
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+a
+0
+1
+2
+3
+
+EXPLAIN 
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	Using where; Start temporary
+1	PRIMARY	t1	ALL	a	NULL	NULL	NULL	5	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t0.a	1	Using where; End temporary
+DROP TABLE t0, t1, t2;
+# End BUG#49129
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl7.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl7.result	2010-08-12 11:48:10 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl7.result	2010-08-13 12:49:50 +0000
@@ -59,8 +59,8 @@ a
 4
 explain extended select * from t2 where t2.a in (select a from t1);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t2	index	a	a	5	NULL	4	100.00	Using where; Using index
-1	PRIMARY	t1	ref	a	a	5	test.t2.a	101	100.00	Using index; Start temporary; End temporary
+1	PRIMARY	t2	index	a	a	5	NULL	4	100.00	Using where; Using index; Start temporary
+1	PRIMARY	t1	ref	a	a	5	test.t2.a	101	100.00	Using index; End temporary
 Warnings:
 Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where (`test`.`t1`.`a` = `test`.`t2`.`a`)
 select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
@@ -69,8 +69,8 @@ a
 4
 explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t2	index	a	a	5	NULL	4	100.00	Using where; Using index
-1	PRIMARY	t1	ref	a	a	5	test.t2.a	101	100.00	Using where; Using index; Start temporary; End temporary
+1	PRIMARY	t2	index	a	a	5	NULL	4	100.00	Using where; Using index; Start temporary
+1	PRIMARY	t1	ref	a	a	5	test.t2.a	101	100.00	Using where; Using index; End temporary
 Warnings:
 Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30))
 select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
@@ -96,8 +96,8 @@ a
 4
 explain extended select * from t2 where t2.a in (select a from t1 where t1.b <> 30);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t2	index	a	a	5	NULL	4	100.00	Using where; Using index
-1	PRIMARY	t1	ref	a	a	5	test.t2.a	101	100.00	Using where; Using index; Start temporary; End temporary
+1	PRIMARY	t2	index	a	a	5	NULL	4	100.00	Using where; Using index; Start temporary
+1	PRIMARY	t1	ref	a	a	5	test.t2.a	101	100.00	Using where; Using index; End temporary
 Warnings:
 Note	1003	select `test`.`t2`.`a` AS `a` from `test`.`t2` semi join (`test`.`t1`) where ((`test`.`t1`.`a` = `test`.`t2`.`a`) and (`test`.`t1`.`b` <> 30))
 drop table t0, t1, t2, t3;
@@ -1690,8 +1690,8 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary; End temporary; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, regular buffers)
 SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -1731,8 +1731,8 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary; End temporary; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, regular buffers)
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -1837,8 +1837,8 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary; End temporary; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, regular buffers)
 SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 i
@@ -2179,8 +2179,8 @@ create table t3 (a int);
 insert into t3 select A.a + 10*B.a from t0 A, t0 B;
 explain select * from t3 where a in (select kp1 from t1 where kp1<20);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	Using where
-1	PRIMARY	t1	ref	kp1	kp1	5	test.t3.a	1	Using index; Start temporary; End temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	Using where; Start temporary
+1	PRIMARY	t1	ref	kp1	kp1	5	test.t3.a	1	Using index; End temporary
 select * from t3 where a in (select kp1 from t1 where kp1<20);
 a
 0
@@ -2208,8 +2208,8 @@ insert into t4 select a from t3;
 explain select * from t3 where a in 
 (select t1.kp1 from t1,t4 where kp1<20 and t4.pk=t1.c);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	Using where
-1	PRIMARY	t1	ref	kp1	kp1	5	test.t3.a	1	Using where; Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	Using where; Start temporary
+1	PRIMARY	t1	ref	kp1	kp1	5	test.t3.a	1	Using where
 1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t1.c	1	Using index; End temporary
 select * from t3 where a in 
 (select t1.kp1 from t1,t4 where kp1<20 and t4.pk=t1.c);
@@ -2370,12 +2370,12 @@ insert into t1 select A.a, B.a, 'filler'
 create table t2 as select * from t1;
 explain select * from t2 where a in (select b from t1 where a=3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	100	Using where
-1	PRIMARY	t1	ref	a	a	10	const,test.t2.a	1	Using index; Start temporary; End temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	100	Using where; Start temporary
+1	PRIMARY	t1	ref	a	a	10	const,test.t2.a	1	Using index; End temporary
 explain select * from t2 where (b,a) in (select a,b from t1 where a=3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	100	Using where
-1	PRIMARY	t1	ref	a	a	10	const,test.t2.a	1	Using index; Start temporary; End temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	100	Using where; Start temporary
+1	PRIMARY	t1	ref	a	a	10	const,test.t2.a	1	Using index; End temporary
 drop table t1,t2;
 create table t1 (a int, b int);
 insert into t1 select a,a from t0;
@@ -2580,8 +2580,8 @@ explain select * 
 from t0 where a in
 (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
-1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Start temporary; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Start temporary
+1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Using join buffer (BNL, regular buffers)
 1	PRIMARY	t2	ref	a	a	5	test.t1.a	1	Using index
 1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End temporary
 drop table t0, t1,t2,t3;
@@ -4029,8 +4029,8 @@ EXPLAIN SELECT Name FROM t1 
 WHERE t1.Code IN (
 SELECT t2.CountryCode FROM t2 WHERE Population > 5000000);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	31	
-1	PRIMARY	t2	ref	CountryCode	CountryCode	3	test.t1.Code	18	Using where; Start temporary; End temporary
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	31	Start temporary
+1	PRIMARY	t2	ref	CountryCode	CountryCode	3	test.t1.Code	18	Using where; End temporary
 SELECT Name FROM t1 
 WHERE t1.Code IN (
 SELECT t2.CountryCode FROM t2 WHERE Population > 5000000);
@@ -4051,8 +4051,8 @@ explain extended select * from t0
 where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and
 t1.b=t2.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
-1	PRIMARY	t1	ref	a	a	5	test.t0.a	1	100.00	Start temporary
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Start temporary
+1	PRIMARY	t1	ref	a	a	5	test.t0.a	1	100.00	
 1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t0.a	1	100.00	Using where; End temporary
 Warnings:
 Note	1276	Field or reference 'test.t0.a' of SELECT #2 was resolved in SELECT #1
@@ -4307,8 +4307,8 @@ alter table t3 add primary key(id), add 
 The following must use loose index scan over t3, key a:
 explain select count(a) from t2 where a in ( SELECT  a FROM t3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	index	a	a	5	NULL	1000	Using where; Using index
-1	PRIMARY	t3	ref	a	a	5	test.t2.a	30	Using index; Start temporary; End temporary
+1	PRIMARY	t2	index	a	a	5	NULL	1000	Using where; Using index; Start temporary
+1	PRIMARY	t3	ref	a	a	5	test.t2.a	30	Using index; End temporary
 select count(a) from t2 where a in ( SELECT  a FROM t3);
 count(a)
 1000
@@ -4510,6 +4510,35 @@ a
 1
 1
 drop table t1,t2,t3;
+# 
+# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
+#            and firstmatch=off
+# 
+CREATE TABLE t0 (a INT);
+CREATE TABLE t1 (a INT, b INT, KEY(a));
+CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
+INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
+INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
+INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
+
+# Should contain records 0..3
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+a
+0
+1
+2
+3
+
+EXPLAIN 
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	Using where; Start temporary
+1	PRIMARY	t1	ALL	a	NULL	NULL	NULL	5	Using where; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t0.a	1	Using where; End temporary
+DROP TABLE t0, t1, t2;
+# End BUG#49129
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result	2010-08-12 11:48:10 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result	2010-08-13 12:49:50 +0000
@@ -2577,8 +2577,8 @@ explain select * 
 from t0 where a in
 (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
-1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Start temporary; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Start temporary
+1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Using join buffer (BNL, regular buffers)
 1	PRIMARY	t2	ref	a	a	5	test.t1.a	1	Using index
 1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End temporary
 drop table t0, t1,t2,t3;
@@ -4507,4 +4507,33 @@ a
 1
 1
 drop table t1,t2,t3;
+# 
+# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
+#            and firstmatch=off
+# 
+CREATE TABLE t0 (a INT);
+CREATE TABLE t1 (a INT, b INT, KEY(a));
+CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
+INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
+INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
+INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
+
+# Should contain records 0..3
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+a
+0
+1
+2
+3
+
+EXPLAIN 
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	Using where
+1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t0.a	1	
+1	PRIMARY	t1	ref	a	a	5	test.t2.a	2	Using where; FirstMatch(t2)
+DROP TABLE t0, t1, t2;
+# End BUG#49129
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl6.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl6.result	2010-08-12 11:48:10 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl6.result	2010-08-13 12:49:50 +0000
@@ -2581,8 +2581,8 @@ explain select * 
 from t0 where a in
 (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
-1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Start temporary; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Start temporary
+1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Using join buffer (BNL, incremental buffers)
 1	PRIMARY	t2	ref	a	a	5	test.t1.a	1	Using index
 1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End temporary
 drop table t0, t1,t2,t3;
@@ -4052,8 +4052,8 @@ explain extended select * from t0
 where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and
 t1.b=t2.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
-1	PRIMARY	t1	ref	a	a	5	test.t0.a	1	100.00	Start temporary
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Start temporary
+1	PRIMARY	t1	ref	a	a	5	test.t0.a	1	100.00	
 1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t0.a	1	100.00	Using where; End temporary
 Warnings:
 Note	1276	Field or reference 'test.t0.a' of SELECT #2 was resolved in SELECT #1
@@ -4511,6 +4511,35 @@ a
 1
 1
 drop table t1,t2,t3;
+# 
+# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
+#            and firstmatch=off
+# 
+CREATE TABLE t0 (a INT);
+CREATE TABLE t1 (a INT, b INT, KEY(a));
+CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
+INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
+INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
+INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
+
+# Should contain records 0..3
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+a
+0
+1
+2
+3
+
+EXPLAIN 
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	Using where
+1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t0.a	1	
+1	PRIMARY	t1	ref	a	a	5	test.t2.a	2	Using where; FirstMatch(t2)
+DROP TABLE t0, t1, t2;
+# End BUG#49129
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl7.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl7.result	2010-08-12 11:48:10 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl7.result	2010-08-13 12:49:50 +0000
@@ -2581,8 +2581,8 @@ explain select * 
 from t0 where a in
 (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
-1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Start temporary; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Start temporary
+1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Using join buffer (BNL, regular buffers)
 1	PRIMARY	t2	ref	a	a	5	test.t1.a	1	Using index
 1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End temporary
 drop table t0, t1,t2,t3;
@@ -4052,8 +4052,8 @@ explain extended select * from t0
 where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and
 t1.b=t2.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
-1	PRIMARY	t1	ref	a	a	5	test.t0.a	1	100.00	Start temporary
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Start temporary
+1	PRIMARY	t1	ref	a	a	5	test.t0.a	1	100.00	
 1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t0.a	1	100.00	Using where; End temporary
 Warnings:
 Note	1276	Field or reference 'test.t0.a' of SELECT #2 was resolved in SELECT #1
@@ -4511,6 +4511,35 @@ a
 1
 1
 drop table t1,t2,t3;
+# 
+# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
+#            and firstmatch=off
+# 
+CREATE TABLE t0 (a INT);
+CREATE TABLE t1 (a INT, b INT, KEY(a));
+CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
+INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
+INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
+INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
+
+# Should contain records 0..3
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+a
+0
+1
+2
+3
+
+EXPLAIN 
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	Using where
+1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t0.a	1	
+1	PRIMARY	t1	ref	a	a	5	test.t2.a	2	Using where; FirstMatch(t2)
+DROP TABLE t0, t1, t2;
+# End BUG#49129
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result	2010-08-12 11:48:10 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result	2010-08-13 12:49:50 +0000
@@ -1687,8 +1687,8 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary; End temporary
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary
 SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -1728,8 +1728,8 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary; End temporary
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -1834,8 +1834,8 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary; End temporary
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary
 SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 i
@@ -2577,8 +2577,8 @@ explain select * 
 from t0 where a in
 (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
-1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Start temporary; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Start temporary
+1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Using join buffer (BNL, regular buffers)
 1	PRIMARY	t2	ref	a	a	5	test.t1.a	1	Using index
 1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End temporary
 drop table t0, t1,t2,t3;
@@ -4511,4 +4511,33 @@ a
 1
 1
 drop table t1,t2,t3;
+# 
+# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
+#            and firstmatch=off
+# 
+CREATE TABLE t0 (a INT);
+CREATE TABLE t1 (a INT, b INT, KEY(a));
+CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
+INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
+INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
+INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
+
+# Should contain records 0..3
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+a
+0
+1
+2
+3
+
+EXPLAIN 
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	Using where; Start temporary
+1	PRIMARY	t1	ALL	a	NULL	NULL	NULL	5	Using where; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t0.a	1	Using where; End temporary
+DROP TABLE t0, t1, t2;
+# End BUG#49129
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl6.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl6.result	2010-08-12 09:02:11 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl6.result	2010-08-13 12:49:50 +0000
@@ -1691,8 +1691,8 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary; End temporary; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -1732,8 +1732,8 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary; End temporary; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -1838,8 +1838,8 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary; End temporary; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 i
@@ -2581,8 +2581,8 @@ explain select * 
 from t0 where a in
 (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
-1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Start temporary; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Start temporary
+1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Using join buffer (BNL, incremental buffers)
 1	PRIMARY	t2	ref	a	a	5	test.t1.a	1	Using index
 1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End temporary
 drop table t0, t1,t2,t3;
@@ -4034,8 +4034,8 @@ EXPLAIN SELECT Name FROM t1 
 WHERE t1.Code IN (
 SELECT t2.CountryCode FROM t2 WHERE Population > 5000000);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	31	
-1	PRIMARY	t2	ref	CountryCode	CountryCode	3	test.t1.Code	18	Using where; Start temporary; End temporary
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	31	Start temporary
+1	PRIMARY	t2	ref	CountryCode	CountryCode	3	test.t1.Code	18	Using where; End temporary
 SELECT Name FROM t1 
 WHERE t1.Code IN (
 SELECT t2.CountryCode FROM t2 WHERE Population > 5000000);
@@ -4056,8 +4056,8 @@ explain extended select * from t0
 where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and
 t1.b=t2.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
-1	PRIMARY	t1	ref	a	a	5	test.t0.a	1	100.00	Start temporary
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Start temporary
+1	PRIMARY	t1	ref	a	a	5	test.t0.a	1	100.00	
 1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t0.a	1	100.00	Using where; End temporary
 Warnings:
 Note	1276	Field or reference 'test.t0.a' of SELECT #2 was resolved in SELECT #1
@@ -4515,6 +4515,35 @@ a
 1
 1
 drop table t1,t2,t3;
+# 
+# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
+#            and firstmatch=off
+# 
+CREATE TABLE t0 (a INT);
+CREATE TABLE t1 (a INT, b INT, KEY(a));
+CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
+INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
+INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
+INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
+
+# Should contain records 0..3
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+a
+0
+1
+2
+3
+
+EXPLAIN 
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	Using where; Start temporary
+1	PRIMARY	t1	ALL	a	NULL	NULL	NULL	5	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t0.a	1	Using where; End temporary
+DROP TABLE t0, t1, t2;
+# End BUG#49129
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl7.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl7.result	2010-08-12 09:02:11 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl7.result	2010-08-13 12:49:50 +0000
@@ -1691,8 +1691,8 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary; End temporary; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, regular buffers)
 SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i);
 i
@@ -1732,8 +1732,8 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary; End temporary; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, regular buffers)
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -1838,8 +1838,8 @@ EXPLAIN SELECT * FROM t1 WHERE (t1.i) IN
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary; End temporary; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, regular buffers)
 SELECT * FROM t1 WHERE (t1.i) IN 
 (SELECT t3.i FROM t2 RIGHT JOIN t3 ON t2.i=t3.i);
 i
@@ -2581,8 +2581,8 @@ explain select * 
 from t0 where a in
 (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
-1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Start temporary; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Start temporary
+1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Using join buffer (BNL, regular buffers)
 1	PRIMARY	t2	ref	a	a	5	test.t1.a	1	Using index
 1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End temporary
 drop table t0, t1,t2,t3;
@@ -4034,8 +4034,8 @@ EXPLAIN SELECT Name FROM t1 
 WHERE t1.Code IN (
 SELECT t2.CountryCode FROM t2 WHERE Population > 5000000);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	31	
-1	PRIMARY	t2	ref	CountryCode	CountryCode	3	test.t1.Code	18	Using where; Start temporary; End temporary
+1	PRIMARY	t1	ALL	PRIMARY	NULL	NULL	NULL	31	Start temporary
+1	PRIMARY	t2	ref	CountryCode	CountryCode	3	test.t1.Code	18	Using where; End temporary
 SELECT Name FROM t1 
 WHERE t1.Code IN (
 SELECT t2.CountryCode FROM t2 WHERE Population > 5000000);
@@ -4056,8 +4056,8 @@ explain extended select * from t0
 where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and
 t1.b=t2.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
-1	PRIMARY	t1	ref	a	a	5	test.t0.a	1	100.00	Start temporary
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Start temporary
+1	PRIMARY	t1	ref	a	a	5	test.t0.a	1	100.00	
 1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t0.a	1	100.00	Using where; End temporary
 Warnings:
 Note	1276	Field or reference 'test.t0.a' of SELECT #2 was resolved in SELECT #1
@@ -4515,6 +4515,35 @@ a
 1
 1
 drop table t1,t2,t3;
+# 
+# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
+#            and firstmatch=off
+# 
+CREATE TABLE t0 (a INT);
+CREATE TABLE t1 (a INT, b INT, KEY(a));
+CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
+INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
+INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
+INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
+
+# Should contain records 0..3
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+a
+0
+1
+2
+3
+
+EXPLAIN 
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	Using where; Start temporary
+1	PRIMARY	t1	ALL	a	NULL	NULL	NULL	5	Using where; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t0.a	1	Using where; End temporary
+DROP TABLE t0, t1, t2;
+# End BUG#49129
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result	2010-08-12 09:02:11 +0000
+++ b/mysql-test/r/subquery_sj_mat.result	2010-08-13 12:49:50 +0000
@@ -1728,8 +1728,8 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary; End temporary
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -2577,8 +2577,8 @@ explain select * 
 from t0 where a in
 (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
-1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Start temporary; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Start temporary
+1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Using join buffer (BNL, regular buffers)
 1	PRIMARY	t2	ref	a	a	5	test.t1.a	1	Using index
 1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End temporary
 drop table t0, t1,t2,t3;
@@ -4507,4 +4507,33 @@ a
 1
 1
 drop table t1,t2,t3;
+# 
+# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
+#            and firstmatch=off
+# 
+CREATE TABLE t0 (a INT);
+CREATE TABLE t1 (a INT, b INT, KEY(a));
+CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
+INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
+INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
+INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
+
+# Should contain records 0..3
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+a
+0
+1
+2
+3
+
+EXPLAIN 
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	Using where; Start temporary
+1	PRIMARY	t1	ALL	a	NULL	NULL	NULL	5	Using where; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t0.a	1	Using where; End temporary
+DROP TABLE t0, t1, t2;
+# End BUG#49129
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_jcl6.result'
--- a/mysql-test/r/subquery_sj_mat_jcl6.result	2010-08-12 09:02:11 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl6.result	2010-08-13 12:49:50 +0000
@@ -1732,8 +1732,8 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary; End temporary; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, incremental buffers)
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -2581,8 +2581,8 @@ explain select * 
 from t0 where a in
 (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
-1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Start temporary; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Start temporary
+1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Using join buffer (BNL, incremental buffers)
 1	PRIMARY	t2	ref	a	a	5	test.t1.a	1	Using index
 1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End temporary
 drop table t0, t1,t2,t3;
@@ -4052,8 +4052,8 @@ explain extended select * from t0
 where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and
 t1.b=t2.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
-1	PRIMARY	t1	ref	a	a	5	test.t0.a	1	100.00	Start temporary
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Start temporary
+1	PRIMARY	t1	ref	a	a	5	test.t0.a	1	100.00	
 1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t0.a	1	100.00	Using where; End temporary
 Warnings:
 Note	1276	Field or reference 'test.t0.a' of SELECT #2 was resolved in SELECT #1
@@ -4511,6 +4511,35 @@ a
 1
 1
 drop table t1,t2,t3;
+# 
+# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
+#            and firstmatch=off
+# 
+CREATE TABLE t0 (a INT);
+CREATE TABLE t1 (a INT, b INT, KEY(a));
+CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
+INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
+INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
+INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
+
+# Should contain records 0..3
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+a
+0
+1
+2
+3
+
+EXPLAIN 
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	Using where; Start temporary
+1	PRIMARY	t1	ALL	a	NULL	NULL	NULL	5	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t0.a	1	Using where; End temporary
+DROP TABLE t0, t1, t2;
+# End BUG#49129
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_mat_jcl7.result'
--- a/mysql-test/r/subquery_sj_mat_jcl7.result	2010-08-12 09:02:11 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl7.result	2010-08-13 12:49:50 +0000
@@ -1732,8 +1732,8 @@ EXPLAIN SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
-1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary; End temporary; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, regular buffers)
 SELECT * FROM t1 WHERE (11) IN 
 (SELECT t3.i FROM t2 LEFT JOIN t3 ON t2.i=t3.i WHERE t1.i);
 i
@@ -2581,8 +2581,8 @@ explain select * 
 from t0 where a in
 (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
-1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Start temporary; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Start temporary
+1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Using join buffer (BNL, regular buffers)
 1	PRIMARY	t2	ref	a	a	5	test.t1.a	1	Using index
 1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End temporary
 drop table t0, t1,t2,t3;
@@ -4052,8 +4052,8 @@ explain extended select * from t0
 where t0.a in ( select t1.a from t1,t2 where t2.a=t0.a and
 t1.b=t2.b);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
-1	PRIMARY	t1	ref	a	a	5	test.t0.a	1	100.00	Start temporary
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Start temporary
+1	PRIMARY	t1	ref	a	a	5	test.t0.a	1	100.00	
 1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t0.a	1	100.00	Using where; End temporary
 Warnings:
 Note	1276	Field or reference 'test.t0.a' of SELECT #2 was resolved in SELECT #1
@@ -4511,6 +4511,35 @@ a
 1
 1
 drop table t1,t2,t3;
+# 
+# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
+#            and firstmatch=off
+# 
+CREATE TABLE t0 (a INT);
+CREATE TABLE t1 (a INT, b INT, KEY(a));
+CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
+INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
+INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
+INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
+
+# Should contain records 0..3
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+a
+0
+1
+2
+3
+
+EXPLAIN 
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	Using where; Start temporary
+1	PRIMARY	t1	ALL	a	NULL	NULL	NULL	5	Using where; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t0.a	1	Using where; End temporary
+DROP TABLE t0, t1, t2;
+# End BUG#49129
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
--- a/mysql-test/r/subquery_sj_mat_nosj.result	2010-08-12 09:02:11 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result	2010-08-13 12:49:50 +0000
@@ -4729,4 +4729,33 @@ a
 1
 1
 drop table t1,t2,t3;
+# 
+# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
+#            and firstmatch=off
+# 
+CREATE TABLE t0 (a INT);
+CREATE TABLE t1 (a INT, b INT, KEY(a));
+CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
+INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
+INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
+INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
+
+# Should contain records 0..3
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+a
+0
+1
+2
+3
+
+EXPLAIN 
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	Using where
+2	DEPENDENT SUBQUERY	t1	ref	a	a	5	func	2	
+2	DEPENDENT SUBQUERY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t0.a	1	Using where
+DROP TABLE t0, t1, t2;
+# End BUG#49129
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result	2010-08-12 09:02:11 +0000
+++ b/mysql-test/r/subquery_sj_none.result	2010-08-13 12:49:50 +0000
@@ -4655,4 +4655,33 @@ a
 1
 1
 drop table t1,t2,t3;
+# 
+# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
+#            and firstmatch=off
+# 
+CREATE TABLE t0 (a INT);
+CREATE TABLE t1 (a INT, b INT, KEY(a));
+CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
+INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
+INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
+INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
+
+# Should contain records 0..3
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+a
+0
+1
+2
+3
+
+EXPLAIN 
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	Using where
+2	DEPENDENT SUBQUERY	t1	ref	a	a	5	func	2	
+2	DEPENDENT SUBQUERY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t0.a	1	Using where
+DROP TABLE t0, t1, t2;
+# End BUG#49129
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none_jcl6.result'
--- a/mysql-test/r/subquery_sj_none_jcl6.result	2010-08-12 09:02:11 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl6.result	2010-08-13 12:49:50 +0000
@@ -4659,6 +4659,35 @@ a
 1
 1
 drop table t1,t2,t3;
+# 
+# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
+#            and firstmatch=off
+# 
+CREATE TABLE t0 (a INT);
+CREATE TABLE t1 (a INT, b INT, KEY(a));
+CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
+INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
+INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
+INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
+
+# Should contain records 0..3
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+a
+0
+1
+2
+3
+
+EXPLAIN 
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	Using where
+2	DEPENDENT SUBQUERY	t1	ref	a	a	5	func	2	
+2	DEPENDENT SUBQUERY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t0.a	1	Using where
+DROP TABLE t0, t1, t2;
+# End BUG#49129
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_none_jcl7.result'
--- a/mysql-test/r/subquery_sj_none_jcl7.result	2010-08-12 09:02:11 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl7.result	2010-08-13 12:49:50 +0000
@@ -4659,6 +4659,35 @@ a
 1
 1
 drop table t1,t2,t3;
+# 
+# BUG#49129: Wrong result with IN-subquery with join_cache_level=6
+#            and firstmatch=off
+# 
+CREATE TABLE t0 (a INT);
+CREATE TABLE t1 (a INT, b INT, KEY(a));
+CREATE TABLE t2 (a INT, b INT, PRIMARY KEY(a));
+INSERT INTO t0 VALUES (0),(1),(2),(3),(4);
+INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(3,11);
+INSERT INTO t2 VALUES (0,0),(1,1),(2,2),(3,11),(4,4);
+
+# Should contain records 0..3
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+a
+0
+1
+2
+3
+
+EXPLAIN 
+SELECT * FROM t0 WHERE t0.a IN 
+(SELECT t1.a FROM t1, t2 WHERE t2.a=t0.a AND t1.b=t2.b);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	5	Using where
+2	DEPENDENT SUBQUERY	t1	ref	a	a	5	func	2	
+2	DEPENDENT SUBQUERY	t2	eq_ref	PRIMARY	PRIMARY	4	test.t0.a	1	Using where
+DROP TABLE t0, t1, t2;
+# End BUG#49129
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'sql/sql_join_cache.cc'
--- a/sql/sql_join_cache.cc	2010-07-23 17:51:11 +0000
+++ b/sql/sql_join_cache.cc	2010-08-13 12:49:50 +0000
@@ -612,7 +612,12 @@ int JOIN_CACHE_BKA::init()
       copy_end= cache->field_descr+cache->fields;
       for (copy= cache->field_descr+cache->flag_fields; copy < copy_end; copy++)
       {
-        if (copy->field->table == tab->table &&
+        /*
+          (1) - when we store rowids for DuplicateWeedout, they have
+                copy->field==NULL
+        */
+        if (copy->field &&  // (1)
+            copy->field->table == tab->table &&
             bitmap_is_set(key_read_set, copy->field->field_index))
         {
           *copy_ptr++= copy; 

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-08-12 09:02:11 +0000
+++ b/sql/sql_select.cc	2010-08-13 12:49:50 +0000
@@ -1430,10 +1430,38 @@ int setup_semijoin_dups_elimination(JOIN
           forwards, but do not destroy other duplicate elimination methods.
         */
         uint first_table= i;
+        uint join_cache_level= join->thd->variables.optimizer_join_cache_level;
         for (uint j= i; j < i + pos->n_sj_tables; j++)
         {
-          if (join->best_positions[j].use_join_buffer && j <= no_jbuf_after)
+          /*
+            The final decision on whether or not join buffering will
+            be used is done in check_join_cache_usage(), which is
+            called from make_join_readinfo()'s main loop.
+            check_join_cache_usage() needs to know if duplicate
+            weedout is used, so moving
+            setup_semijoin_dups_elimination() from before the main
+            loop to after it is not possible. I.e.,
+            join->best_positions[j].use_join_buffer is not
+            trustworthy at this point.
+
+            TODO: merge make_join_readinfo() and
+            setup_semijoin_dups_elimination() loops and change the
+            following 'if' to
+
+            "if (join->best_positions[j].use_join_buffer && 
+                 j <= no_jbuf_after)".
+
+            For now, use a rough criteria:
+          */
+          JOIN_TAB *sj_tab=join->join_tab + j; 
+          if (j != join->const_tables && 
+              sj_tab->use_quick != 2 && 
+              j <= no_jbuf_after &&
+              ((sj_tab->type == JT_ALL && join_cache_level != 0) ||
+               (join_cache_level > 4 && (tab->type == JT_REF || 
+                                         tab->type == JT_EQ_REF))))
           {
+            /* Looks like we'll be using join buffer */
             first_table= join->const_tables;
             break;
           }
@@ -8080,6 +8108,10 @@ void calc_used_field_length(THD *thd, JO
 			     (join_tab->table->s->reclength- rec_length));
     rec_length+=(uint) max(4,blob_length);
   }
+  /*
+    psergey-todo: why we don't count here rowid that we might need to store
+    when using DuplicateElimination?
+  */
   join_tab->used_fields=fields;
   join_tab->used_fieldlength=rec_length;
   join_tab->used_blobs=blobs;


Attachment: [text/bzr-bundle] bzr/jorgen.loland@sun.com-20100813124950-wm792z59kzgcam39.bundle
Thread
bzr commit into mysql-next-mr-opt-backporting branch (jorgen.loland:3228)Bug#49129Jorgen Loland13 Aug
  • Re: bzr commit into mysql-next-mr-opt-backporting branch (jorgen.loland:3228)Bug#49129Jorgen Loland13 Aug
    • Re: bzr commit into mysql-next-mr-opt-backporting branch (jorgen.loland:3228)Bug#49129Roy Lyseng26 Aug
  • Re: bzr commit into mysql-next-mr-opt-backporting branch (jorgen.loland:3228)Bug#49129Guilhem Bichot23 Aug
    • Re: bzr commit into mysql-next-mr-opt-backporting branch (jorgen.loland:3228)Bug#49129Jorgen Loland27 Aug