List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:August 13 2010 2:27pm
Subject:Re: bzr commit into mysql-next-mr-opt-backporting branch (jorgen.loland:3228)
Bug#49129
View as plain text  
Reviewers: I'll commit a mini-refactoring patch once I have a 'go' on this fix. 
It will rename a few variables in setup_sj...() like 'i', 'j' etc to names with 
meaning.

On 08/13/2010 02:49 PM, Jorgen Loland wrote:
> #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;
>
>
>
>
>

-- 
Jørgen Løland | Senior Software Engineer | +47 73842138
Oracle MySQL
Trondheim, Norway
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