List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:August 26 2010 11:46am
Subject:Re: bzr commit into mysql-next-mr-opt-backporting branch (jorgen.loland:3228)
Bug#49129
View as plain text  
Hi Jørgen,

patch is approved.

Please also consider Guilhem's comments.

I have added a couple of suggestions inline.

Roy
On 13.08.10 16.27, Jorgen Loland wrote:
> 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.

Some comments to the refactoring:

Personal meaning: There is no need to abbreviate "table" to "tbl" (or "tab"). 
"tableno" is only 1 character longer than "tbl_no" and is more readable.
So IMHO sj_tbl_no should be sj_tableno instead.

last_sj_tbl should be last_sj_tab (by convention, variables of type JOIN_TAB * 
seem to end in "_tab".

>
> 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

Please make doxygen @todo.

>> +
>> + "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 */

Proposal: "Join buffering will probably be used"

>> 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?

Please convert to a doxygen @todo. Can you also give the sentence more meaning?

>> + */
>> join_tab->used_fields=fields;
>> join_tab->used_fieldlength=rec_length;
>> join_tab->used_blobs=blobs;
>>
>>
>>
>>
>>
>

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