#At file:///export/home/jl208045/mysql/mysql-next-mr-opt-backporting-49129/ based on revid:epotemkin@stripped
3232 Jorgen Loland 2010-08-27
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.
@ sql/sql_select.h
Made st_join_table::use_quick an enum
@ sql/sql_test.cc
Made st_join_table::use_quick an enum
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
sql/sql_select.h
sql/sql_test.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-27 12:34:59 +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-27 12:34:59 +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-27 12:34:59 +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-27 12:34:59 +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-27 12:34:59 +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-27 12:34:59 +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-27 12:34:59 +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-27 12:34:59 +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-27 12:34:59 +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-27 12:34:59 +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-27 12:34:59 +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-27 12:34:59 +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-27 12:34:59 +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-27 12:34:59 +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-27 12:34:59 +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-27 12:34:59 +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-27 12:34:59 +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-27 12:34:59 +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-27 12:34:59 +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-27 12:34:59 +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-27 12:34:59 +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-19 16:44:09 +0000
+++ b/sql/sql_select.cc 2010-08-27 12:34:59 +0000
@@ -1236,6 +1236,33 @@ static bool sj_table_is_included(JOIN *j
return TRUE;
}
+/**
+ Check if duplicate generating range for duplicate weedout needs to
+ extend to the first non-const table. This is the case if the
+ duplicate weedout semijoin strategy is applied on tables that are
+ joined using join buffering (BNL/BKA).
+
+ This function is called from setup_semijoin_dups_elimination()
+ before the final decision is made on whether or not buffering is
+ used. It is therefore only a rough test that covers all cases where
+ join buffering might be used, but potentially also some cases where
+ join buffering will not be used.
+
+ @param join_cache_level The join cache level
+ @param sj_tab Table that might be joined by BNL/BKA
+
+ @return
+ true if join buffering might be used, false otherwise
+
+ */
+bool extend_duplicate_generating_range(int join_cache_level,
+ const JOIN_TAB *sj_tab)
+{
+ return (sj_tab->use_quick != QS_DYNAMIC_RANGE &&
+ ((join_cache_level != 0 && sj_tab->type == JT_ALL) ||
+ (join_cache_level > 4 &&
+ (sj_tab->type == JT_REF || sj_tab->type == JT_EQ_REF))));
+}
/**
Setup the strategies to eliminate semi-join duplicates.
@@ -1243,8 +1270,8 @@ static bool sj_table_is_included(JOIN *j
@param join Join to process
@param options Join options (needed to see if join buffering will be
used or not)
- @param no_jbuf_after Another bit of information re where join buffering will
- be used.
+ @param no_jbuf_after Do not use join buffering after the table with this
+ number
@retval FALSE OK
@retval TRUE Out of memory error
@@ -1368,32 +1395,32 @@ static bool sj_table_is_included(JOIN *j
int setup_semijoin_dups_elimination(JOIN *join, ulonglong options,
uint no_jbuf_after)
{
- uint i;
+ uint tableno;
THD *thd= join->thd;
DBUG_ENTER("setup_semijoin_dups_elimination");
- for (i= join->const_tables ; i < join->tables; )
+ for (tableno= join->const_tables ; tableno < join->tables; )
{
- JOIN_TAB *tab=join->join_tab + i;
- POSITION *pos= join->best_positions + i;
+ JOIN_TAB *tab=join->join_tab + tableno;
+ POSITION *pos= join->best_positions + tableno;
uint keylen, keyno;
if (pos->sj_strategy == SJ_OPT_NONE)
{
- i++; // nothing to do
+ tableno++; // nothing to do
continue;
}
- JOIN_TAB *tab_end= tab + pos->n_sj_tables - 1;
+ JOIN_TAB *last_sj_tab= tab + pos->n_sj_tables - 1;
switch (pos->sj_strategy) {
case SJ_OPT_MATERIALIZE_LOOKUP:
case SJ_OPT_MATERIALIZE_SCAN:
/* Do nothing */
- i+= pos->n_sj_tables;
+ tableno+= pos->n_sj_tables;
break;
case SJ_OPT_LOOSE_SCAN:
{
DBUG_ASSERT(tab->emb_sj_nest != NULL); // First table must be inner
/* We jump from the last table to the first one */
- tab->loosescan_match_tab= tab_end;
+ tab->loosescan_match_tab= last_sj_tab;
/* For LooseScan, duplicate elimination is based on rows being sorted
on key. We need to make sure that range select keep the sorted index
@@ -1415,22 +1442,101 @@ int setup_semijoin_dups_elimination(JOIN
tab->loosescan_key_len= keylen;
if (pos->n_sj_tables > 1)
- tab_end->do_firstmatch= tab;
- i+= pos->n_sj_tables;
+ last_sj_tab->do_firstmatch= tab;
+ tableno+= pos->n_sj_tables;
break;
}
case SJ_OPT_DUPS_WEEDOUT:
{
DBUG_ASSERT(tab->emb_sj_nest != NULL); // First table must be inner
/*
- Check for join buffering. If there is one, move the first table
- forwards, but do not destroy other duplicate elimination methods.
+ Consider a semijoin of two outer tables and an inner table,
+ all with two rows. Inner tables are assumed to not be
+ confluent (See sj_opt_materialize_lookup)
+
+ If normal nested loop execution is used, we do not need to
+ include semi-join outer table rowids in the duplicate
+ weedout temp table since NL guarantees that outer table rows
+ are encountered only once. This is because all rows in the
+ temp table are deleted for every new outer table
+ combination:
+
+ ot1.row1|ot2.row1|it1.row1
+ '-> it1.row1 not in temp table - output ot row combination
+ ot1.row1|ot2.row1|it1.row2
+ '-> it1.row2 not in temp table - output ot row combination
+ '-> no more join matches - empty temp table
+ ot1.row1|ot2.row2|it1.row1
+ '-> it1.row1 not in temp table - output ot row combination
+ ...
+
+ Note: this is an optimalization. Including outer table
+ rowids in the temp table is not harmful but wastes memory.
+
+ The join buffering (BNL/BKA) algorithms are different
+ because the "empty temp table" trick of NL does not apply.
+ The output of these are concatenated records:
+
+ ot1.row1|ot2.row1|it1.row1
+ ot1.row1|ot2.row1|it1.row2
+ ot1.row1|ot2.row2|it1.row1
+ ot1.row1|ot2.row2|it1.row2
+ ...
+
+ If the temp table only contained inner table rowids,
+ duplicate elimination on these records would do:
+
+ ot1.row1|ot2.row1|it1.row1
+ '-> it1.row1 not in temp table - output ot row combination
+ ot1.row1|ot2.row1|it1.row2
+ '-> it1.row2 not in temp table - output ot row combination
+ ot1.row1|ot2.row2|it1.row1
+ '-> it1.row1 found in temp table - discard
+ ...
+
+ By putting outer table rowids into the temp table, we can
+ detect that this is a new combination of outer table rows.
+
+ Below we check if join buffering might be used. If so, set
+ first_table to the first non-constant table so that outer
+ table rowids are included in the temp table. Do not destroy
+ other duplicate elimination methods.
*/
- uint first_table= i;
- for (uint j= i; j < i + pos->n_sj_tables; j++)
+ uint first_table= tableno;
+ uint join_cache_level= join->thd->variables.optimizer_join_cache_level;
+ for (uint sj_tableno= tableno;
+ sj_tableno < tableno + pos->n_sj_tables;
+ sj_tableno++)
{
- 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[sj_tableno].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[sj_tableno].use_join_buffer &&
+ sj_tableno <= no_jbuf_after)".
+
+ For now, use a rough criteria:
+ */
+
+ JOIN_TAB *sj_tab=join->join_tab + sj_tableno;
+ if (sj_tableno != join->const_tables &&
+ sj_tableno <= no_jbuf_after &&
+ extend_duplicate_generating_range(join_cache_level, sj_tab))
+
{
+ /* Join buffering will probably be used */
first_table= join->const_tables;
break;
}
@@ -1445,21 +1551,23 @@ int setup_semijoin_dups_elimination(JOIN
- tables that need their rowids to be put into temptable
- the last outer table
*/
- for (JOIN_TAB *j=join->join_tab + first_table; j <= tab_end; j++)
+ for (JOIN_TAB *tab_in_range=join->join_tab + first_table;
+ tab_in_range <= last_sj_tab;
+ tab_in_range++)
{
- if (sj_table_is_included(join, j))
+ if (sj_table_is_included(join, tab_in_range))
{
- last_tab->join_tab= j;
+ last_tab->join_tab= tab_in_range;
last_tab->rowid_offset= jt_rowid_offset;
- jt_rowid_offset += j->table->file->ref_length;
- if (j->table->maybe_null)
+ jt_rowid_offset += tab_in_range->table->file->ref_length;
+ if (tab_in_range->table->maybe_null)
{
last_tab->null_byte= jt_null_bits / 8;
last_tab->null_bit= jt_null_bits++;
}
last_tab++;
- j->table->prepare_for_position();
- j->keep_current_rowid= TRUE;
+ tab_in_range->table->prepare_for_position();
+ tab_in_range->keep_current_rowid= TRUE;
}
}
@@ -1497,16 +1605,16 @@ int setup_semijoin_dups_elimination(JOIN
sjtbl->have_confluent_row= FALSE;
}
join->join_tab[first_table].flush_weedout_table= sjtbl;
- tab_end->check_weed_out_table= sjtbl;
+ last_sj_tab->check_weed_out_table= sjtbl;
- i+= pos->n_sj_tables;
+ tableno+= pos->n_sj_tables;
break;
}
case SJ_OPT_FIRST_MATCH:
{
JOIN_TAB *jump_to= tab - 1;
DBUG_ASSERT(tab->emb_sj_nest != NULL); // First table must be inner
- for (JOIN_TAB *j= tab; j <= tab_end; j++)
+ for (JOIN_TAB *j= tab; j <= last_sj_tab; j++)
{
if (!j->emb_sj_nest)
{
@@ -1522,11 +1630,11 @@ int setup_semijoin_dups_elimination(JOIN
Assign jump target for last table in a consecutive range of
inner tables.
*/
- if (j == tab_end || !(j+1)->emb_sj_nest)
+ if (j == last_sj_tab || !(j+1)->emb_sj_nest)
j->do_firstmatch= jump_to;
}
}
- i+= pos->n_sj_tables;
+ tableno+= pos->n_sj_tables;
break;
}
}
@@ -1537,8 +1645,8 @@ int setup_semijoin_dups_elimination(JOIN
JOIN_TAB *last_sj_inner=
(pos->sj_strategy == SJ_OPT_DUPS_WEEDOUT) ?
/* Range may end with non-inner table so cannot set last_sj_inner_tab */
- NULL : tab_end;
- for (JOIN_TAB *j= tab; j <= tab_end; j++)
+ NULL : last_sj_tab;
+ for (JOIN_TAB *j= tab; j <= last_sj_tab; j++)
{
j->first_sj_inner_tab= tab;
j->last_sj_inner_tab= last_sj_inner;
@@ -8047,6 +8155,10 @@ void calc_used_field_length(THD *thd, JO
(join_tab->table->s->reclength- rec_length));
rec_length+=(uint) max(4,blob_length);
}
+ /**
+ @todo why don't we count the rowids 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;
@@ -9247,7 +9359,7 @@ static bool make_join_select(JOIN *join,
/* Range uses longer key; Use this instead of ref on key */
tab->type=JT_ALL;
use_quick_range=1;
- tab->use_quick=1;
+ tab->use_quick=QS_RANGE;
tab->ref.key= -1;
tab->ref.key_parts=0; // Don't use ref key.
join->best_positions[i].records_read= rows2double(tab->quick->records);
@@ -9440,10 +9552,10 @@ static bool make_join_select(JOIN *join,
(sel->quick_keys.is_clear_all() ||
(sel->quick &&
(sel->quick->records >= 100L)))) ?
- 2 : 1;
+ QS_DYNAMIC_RANGE : QS_RANGE;
sel->read_tables= used_tables & ~current_map;
}
- if (i != join->const_tables && tab->use_quick != 2 &&
+ if (i != join->const_tables && tab->use_quick != QS_DYNAMIC_RANGE &&
!tab->first_inner)
{ /* Read with cache */
if (cond &&
@@ -10165,7 +10277,7 @@ uint check_join_cache_usage(JOIN_TAB *ta
psergey-todo: why the below when execution code seems to handle the
"range checked for each record" case?
*/
- if (tab->use_quick == 2)
+ if (tab->use_quick == QS_DYNAMIC_RANGE)
goto no_join_cache;
/*
@@ -10235,6 +10347,10 @@ uint check_join_cache_usage(JOIN_TAB *ta
!tab->cache->init()))
{
*icp_other_tables_ok= FALSE;
+ // Check that setup of duplicate weedout was aware that join
+ // buffering could be used
+ DBUG_ASSERT(tab_sj_strategy!=SJ_OPT_DUPS_WEEDOUT ||
+ extend_duplicate_generating_range(cache_level, tab));
return JOIN_CACHE::ALG_BNL | force_unlinked_cache;
}
goto no_join_cache;
@@ -10258,6 +10374,10 @@ uint check_join_cache_usage(JOIN_TAB *ta
(tab->cache= new JOIN_CACHE_BKA_UNIQUE(join, tab, flags, prev_cache)))
) && !tab->cache->init())))
{
+ // Check that setup of duplicate weedout was aware that join
+ // buffering could be used
+ DBUG_ASSERT(tab_sj_strategy!=SJ_OPT_DUPS_WEEDOUT ||
+ extend_duplicate_generating_range(cache_level, tab));
if (cache_level <= 6)
return JOIN_CACHE::ALG_BKA | force_unlinked_cache;
return JOIN_CACHE::ALG_BKA_UNIQUE | force_unlinked_cache;
@@ -10735,7 +10855,7 @@ make_join_readinfo(JOIN *join, ulonglong
tab[-1].next_select=sub_select_cache;
}
/* These init changes read_record */
- if (tab->use_quick == 2)
+ if (tab->use_quick == QS_DYNAMIC_RANGE)
{
join->thd->server_status|=SERVER_QUERY_NO_GOOD_INDEX_USED;
tab->read_first_record= join_init_quick_read_record;
@@ -17851,7 +17971,8 @@ test_if_quick_select(JOIN_TAB *tab)
static
bool test_if_use_dynamic_range_scan(JOIN_TAB *join_tab)
{
- return (join_tab->use_quick == 2 && test_if_quick_select(join_tab) > 0);
+ return (join_tab->use_quick == QS_DYNAMIC_RANGE &&
+ test_if_quick_select(join_tab) > 0);
}
int join_init_read_record(JOIN_TAB *tab)
@@ -19544,7 +19665,7 @@ test_if_skip_sort_order(JOIN_TAB *tab,OR
*/
DBUG_ASSERT(tab->select->quick);
tab->type=JT_ALL;
- tab->use_quick=1;
+ tab->use_quick=QS_RANGE;
tab->ref.key= -1;
tab->ref.key_parts=0; // Don't use ref key.
tab->read_first_record= join_init_read_record;
@@ -22345,7 +22466,7 @@ void select_describe(JOIN *join, bool ne
}
if (tab->select)
{
- if (tab->use_quick == 2)
+ if (tab->use_quick == QS_DYNAMIC_RANGE)
{
/* 4 bits per 1 hex digit + terminating '\0' */
char buf[MAX_KEY / 4 + 1];
=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h 2010-08-19 07:10:58 +0000
+++ b/sql/sql_select.h 2010-08-27 12:34:59 +0000
@@ -179,6 +179,8 @@ inline bool sj_is_materialize_strategy(u
return strategy >= SJ_OPT_MATERIALIZE_LOOKUP;
}
+enum quick_type { QS_NONE, QS_RANGE, QS_DYNAMIC_RANGE};
+
typedef struct st_join_table : public Sql_alloc
{
st_join_table();
@@ -244,12 +246,13 @@ typedef struct st_join_table : public Sq
ha_rows read_time;
table_map dependent,key_dependent;
- uint use_quick,index;
+ uint index;
uint status; ///< Save status for cache
uint used_fields,used_fieldlength,used_blobs;
uint used_null_fields;
uint used_rowid_fields;
uint used_uneven_bit_fields;
+ enum quick_type use_quick;
enum join_type type;
bool cached_eq_ref_table,eq_ref_table,not_used_in_distinct;
/* TRUE <=> index-based access method must return records in order */
@@ -417,7 +420,6 @@ st_join_table::st_join_table()
dependent(0),
key_dependent(0),
- use_quick(0),
index(0),
status(0),
used_fields(0),
@@ -426,6 +428,7 @@ st_join_table::st_join_table()
used_null_fields(0),
used_rowid_fields(0),
used_uneven_bit_fields(0),
+ use_quick(QS_NONE),
type(JT_UNKNOWN),
cached_eq_ref_table(FALSE),
eq_ref_table(FALSE),
=== modified file 'sql/sql_test.cc'
--- a/sql/sql_test.cc 2010-08-23 12:05:47 +0000
+++ b/sql/sql_test.cc 2010-08-27 12:34:59 +0000
@@ -215,7 +215,7 @@ TEST_join(JOIN *join)
if (tab->select)
{
char buf[MAX_KEY/8+1];
- if (tab->use_quick == 2)
+ if (tab->use_quick == QS_DYNAMIC_RANGE)
fprintf(DBUG_FILE,
" quick select checked for each record (keys: %s)\n",
tab->select->quick_keys.print(buf));
Attachment: [text/bzr-bundle] bzr/jorgen.loland@oracle.com-20100827123459-q76w4esqt67qdy7r.bundle
