#At file:///home/mysql_src/bzrrepos_new/opt-back-52636/ based on revid:guilhem@stripped
3208 Guilhem Bichot 2010-06-28
Fix for Bug#54437 "Extra rows with LEFT JOIN + semijoin (firstmatch and duplicates weedout)":
convergence between evaluate_join_record() and evaluate_null_complemented_record().
@ mysql-test/include/subquery_sj.inc
Without the code fix, main.subquery_sj_dupsweed, main.subquery_sj_firstmatch
and main.subquery_sj_loosescan would return 4 1's instead of 2.
There was no problem at jcl6/7 because the recent fix for BUG 54235 had
already aligned JOIN_CACHE::join_null_complements() with
JOIN_CACHE::join_matching_records().
@ sql/sql_select.cc
During execution, Firstmatch and Duplicates weedout are handled in
evaluate_join_record() (function which applies to matching records), not
in evaluate_null_complemented_record() (function which applies to
non-matching records). Thus, if the semijoin inner expression is a LEFT
JOIN yielding NULLs (see subquery_sj.inc: t3 columns are filled with
NULLs), evaluate_null_complemented_record() for t3 goes to the final
end_send(), without any firstmatch or duplicates weedout treatment, so
duplicate rows are returned.
The easy and safe fix would be to put in
evaluate_null_complemented_record() a copy of the code of
evaluate_join_record() which handles firstmatch and duplicates
weedout. In an attempt to reduce code size and unify execution flows,
the chosen fix is rather to make evaluate_null_complemented_record()
call evaluate_join_record(). The first function is responsible for
creating NULL values for all inner tables at the right of LEFT JOIN, and
for testing pushed down conditions, from then on this record is sent to
the second function for more complete evaluation of it (firstmatch
etc). This should minimize future distortion between the two functions.
Note that the loop over first_unmatched, in
evaluate_null_complemented_record(), which this patch shortens to its
first iteration, is already present in evaluate_join_record().
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_select.cc
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc 2010-06-20 07:49:07 +0000
+++ b/mysql-test/include/subquery_sj.inc 2010-06-28 20:43:32 +0000
@@ -2744,3 +2744,24 @@ explain extended SELECT * FROM t1 WHERE
SELECT * FROM t1 WHERE (t1.i) IN
(SELECT t3.i FROM t3 LEFT JOIN t2 ON t2.i=t3.i);
drop table t1,t2,t3;
+
+
+--echo #
+--echo # Bug#54437 Extra rows with LEFT JOIN + semijoin (firstmatch
+--echo # and duplicates weedout)
+--echo #
+create table t1 (a int);
+create table t2 (a int);
+create table t3 (a int);
+insert into t1 values(1);
+insert into t1 values(1);
+insert into t2 values(1);
+insert into t2 values(1);
+insert into t3 values(2);
+insert into t3 values(2);
+
+let $query=select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+eval explain $query;
+eval $query;
+
+drop table t1,t2,t3;
=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result 2010-06-20 07:16:27 +0000
+++ b/mysql-test/r/subquery_sj_all.result 2010-06-28 20:43:32 +0000
@@ -4496,4 +4496,27 @@ i
1
2
drop table t1,t2,t3;
+#
+# Bug#54437 Extra rows with LEFT JOIN + semijoin (firstmatch
+# and duplicates weedout)
+#
+create table t1 (a int);
+create table t2 (a int);
+create table t3 (a int);
+insert into t1 values(1);
+insert into t1 values(1);
+insert into t2 values(1);
+insert into t2 values(1);
+insert into t3 values(2);
+insert into t3 values(2);
+explain select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start materialize
+1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End materialize
+select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+a
+1
+1
+drop table t1,t2,t3;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_all_jcl6.result'
--- a/mysql-test/r/subquery_sj_all_jcl6.result 2010-06-20 07:16:27 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl6.result 2010-06-28 20:43:32 +0000
@@ -4499,6 +4499,29 @@ i
1
2
drop table t1,t2,t3;
+#
+# Bug#54437 Extra rows with LEFT JOIN + semijoin (firstmatch
+# and duplicates weedout)
+#
+create table t1 (a int);
+create table t2 (a int);
+create table t3 (a int);
+insert into t1 values(1);
+insert into t1 values(1);
+insert into t2 values(1);
+insert into t2 values(1);
+insert into t3 values(2);
+insert into t3 values(2);
+explain select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start materialize
+1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End materialize
+select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+a
+1
+1
+drop table t1,t2,t3;
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-06-20 07:16:27 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl7.result 2010-06-28 20:43:32 +0000
@@ -4499,6 +4499,29 @@ i
1
2
drop table t1,t2,t3;
+#
+# Bug#54437 Extra rows with LEFT JOIN + semijoin (firstmatch
+# and duplicates weedout)
+#
+create table t1 (a int);
+create table t2 (a int);
+create table t3 (a int);
+insert into t1 values(1);
+insert into t1 values(1);
+insert into t2 values(1);
+insert into t2 values(1);
+insert into t3 values(2);
+insert into t3 values(2);
+explain select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start materialize
+1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End materialize
+select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+a
+1
+1
+drop table t1,t2,t3;
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-06-20 07:16:27 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result 2010-06-28 20:43:32 +0000
@@ -4495,4 +4495,27 @@ i
1
2
drop table t1,t2,t3;
+#
+# Bug#54437 Extra rows with LEFT JOIN + semijoin (firstmatch
+# and duplicates weedout)
+#
+create table t1 (a int);
+create table t2 (a int);
+create table t3 (a int);
+insert into t1 values(1);
+insert into t1 values(1);
+insert into t2 values(1);
+insert into t2 values(1);
+insert into t3 values(2);
+insert into t3 values(2);
+explain select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (BNL, regular buffers)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End temporary
+select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+a
+1
+1
+drop table t1,t2,t3;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl6.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl6.result 2010-06-20 07:16:27 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl6.result 2010-06-28 20:43:32 +0000
@@ -4499,6 +4499,29 @@ i
1
2
drop table t1,t2,t3;
+#
+# Bug#54437 Extra rows with LEFT JOIN + semijoin (firstmatch
+# and duplicates weedout)
+#
+create table t1 (a int);
+create table t2 (a int);
+create table t3 (a int);
+insert into t1 values(1);
+insert into t1 values(1);
+insert into t2 values(1);
+insert into t2 values(1);
+insert into t3 values(2);
+insert into t3 values(2);
+explain select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (BNL, incremental buffers)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (BNL, incremental buffers)
+select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+a
+1
+1
+drop table t1,t2,t3;
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-06-20 07:16:27 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl7.result 2010-06-28 20:43:32 +0000
@@ -4499,6 +4499,29 @@ i
1
2
drop table t1,t2,t3;
+#
+# Bug#54437 Extra rows with LEFT JOIN + semijoin (firstmatch
+# and duplicates weedout)
+#
+create table t1 (a int);
+create table t2 (a int);
+create table t3 (a int);
+insert into t1 values(1);
+insert into t1 values(1);
+insert into t2 values(1);
+insert into t2 values(1);
+insert into t3 values(2);
+insert into t3 values(2);
+explain select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (BNL, regular buffers)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (BNL, regular buffers)
+select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+a
+1
+1
+drop table t1,t2,t3;
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-06-20 07:16:27 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result 2010-06-28 20:43:32 +0000
@@ -4496,4 +4496,27 @@ i
1
2
drop table t1,t2,t3;
+#
+# Bug#54437 Extra rows with LEFT JOIN + semijoin (firstmatch
+# and duplicates weedout)
+#
+create table t1 (a int);
+create table t2 (a int);
+create table t3 (a int);
+insert into t1 values(1);
+insert into t1 values(1);
+insert into t2 values(1);
+insert into t2 values(1);
+insert into t3 values(2);
+insert into t3 values(2);
+explain select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (BNL, regular buffers)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End temporary
+select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+a
+1
+1
+drop table t1,t2,t3;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl6.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl6.result 2010-06-20 07:16:27 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl6.result 2010-06-28 20:43:32 +0000
@@ -4500,6 +4500,29 @@ i
1
2
drop table t1,t2,t3;
+#
+# Bug#54437 Extra rows with LEFT JOIN + semijoin (firstmatch
+# and duplicates weedout)
+#
+create table t1 (a int);
+create table t2 (a int);
+create table t3 (a int);
+insert into t1 values(1);
+insert into t1 values(1);
+insert into t2 values(1);
+insert into t2 values(1);
+insert into t3 values(2);
+insert into t3 values(2);
+explain select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (BNL, incremental buffers)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (BNL, incremental buffers)
+select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+a
+1
+1
+drop table t1,t2,t3;
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-06-20 07:16:27 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl7.result 2010-06-28 20:43:32 +0000
@@ -4500,6 +4500,29 @@ i
1
2
drop table t1,t2,t3;
+#
+# Bug#54437 Extra rows with LEFT JOIN + semijoin (firstmatch
+# and duplicates weedout)
+#
+create table t1 (a int);
+create table t2 (a int);
+create table t3 (a int);
+insert into t1 values(1);
+insert into t1 values(1);
+insert into t2 values(1);
+insert into t2 values(1);
+insert into t3 values(2);
+insert into t3 values(2);
+explain select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (BNL, regular buffers)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (BNL, regular buffers)
+select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+a
+1
+1
+drop table t1,t2,t3;
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-06-20 07:16:27 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result 2010-06-28 20:43:32 +0000
@@ -4500,4 +4500,27 @@ i
1
2
drop table t1,t2,t3;
+#
+# Bug#54437 Extra rows with LEFT JOIN + semijoin (firstmatch
+# and duplicates weedout)
+#
+create table t1 (a int);
+create table t2 (a int);
+create table t3 (a int);
+insert into t1 values(1);
+insert into t1 values(1);
+insert into t2 values(1);
+insert into t2 values(1);
+insert into t3 values(2);
+insert into t3 values(2);
+explain select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (BNL, regular buffers)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End temporary
+select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+a
+1
+1
+drop table t1,t2,t3;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl6.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl6.result 2010-06-20 07:16:27 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl6.result 2010-06-28 20:43:32 +0000
@@ -4504,6 +4504,29 @@ i
1
2
drop table t1,t2,t3;
+#
+# Bug#54437 Extra rows with LEFT JOIN + semijoin (firstmatch
+# and duplicates weedout)
+#
+create table t1 (a int);
+create table t2 (a int);
+create table t3 (a int);
+insert into t1 values(1);
+insert into t1 values(1);
+insert into t2 values(1);
+insert into t2 values(1);
+insert into t3 values(2);
+insert into t3 values(2);
+explain select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (BNL, incremental buffers)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (BNL, incremental buffers)
+select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+a
+1
+1
+drop table t1,t2,t3;
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-06-20 07:16:27 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl7.result 2010-06-28 20:43:32 +0000
@@ -4504,6 +4504,29 @@ i
1
2
drop table t1,t2,t3;
+#
+# Bug#54437 Extra rows with LEFT JOIN + semijoin (firstmatch
+# and duplicates weedout)
+#
+create table t1 (a int);
+create table t2 (a int);
+create table t3 (a int);
+insert into t1 values(1);
+insert into t1 values(1);
+insert into t2 values(1);
+insert into t2 values(1);
+insert into t3 values(2);
+insert into t3 values(2);
+explain select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (BNL, regular buffers)
+1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (BNL, regular buffers)
+select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+a
+1
+1
+drop table t1,t2,t3;
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-06-20 07:16:27 +0000
+++ b/mysql-test/r/subquery_sj_mat.result 2010-06-28 20:43:32 +0000
@@ -4496,4 +4496,27 @@ i
1
2
drop table t1,t2,t3;
+#
+# Bug#54437 Extra rows with LEFT JOIN + semijoin (firstmatch
+# and duplicates weedout)
+#
+create table t1 (a int);
+create table t2 (a int);
+create table t3 (a int);
+insert into t1 values(1);
+insert into t1 values(1);
+insert into t2 values(1);
+insert into t2 values(1);
+insert into t3 values(2);
+insert into t3 values(2);
+explain select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start materialize
+1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End materialize
+select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+a
+1
+1
+drop table t1,t2,t3;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_jcl6.result'
--- a/mysql-test/r/subquery_sj_mat_jcl6.result 2010-06-20 07:16:27 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl6.result 2010-06-28 20:43:32 +0000
@@ -4500,6 +4500,29 @@ i
1
2
drop table t1,t2,t3;
+#
+# Bug#54437 Extra rows with LEFT JOIN + semijoin (firstmatch
+# and duplicates weedout)
+#
+create table t1 (a int);
+create table t2 (a int);
+create table t3 (a int);
+insert into t1 values(1);
+insert into t1 values(1);
+insert into t2 values(1);
+insert into t2 values(1);
+insert into t3 values(2);
+insert into t3 values(2);
+explain select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start materialize
+1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End materialize
+select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+a
+1
+1
+drop table t1,t2,t3;
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-06-20 07:16:27 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl7.result 2010-06-28 20:43:32 +0000
@@ -4500,6 +4500,29 @@ i
1
2
drop table t1,t2,t3;
+#
+# Bug#54437 Extra rows with LEFT JOIN + semijoin (firstmatch
+# and duplicates weedout)
+#
+create table t1 (a int);
+create table t2 (a int);
+create table t3 (a int);
+insert into t1 values(1);
+insert into t1 values(1);
+insert into t2 values(1);
+insert into t2 values(1);
+insert into t3 values(2);
+insert into t3 values(2);
+explain select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2
+1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Start materialize
+1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End materialize
+select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+a
+1
+1
+drop table t1,t2,t3;
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-06-20 07:16:27 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result 2010-06-28 20:43:32 +0000
@@ -4700,4 +4700,27 @@ i
1
2
drop table t1,t2,t3;
+#
+# Bug#54437 Extra rows with LEFT JOIN + semijoin (firstmatch
+# and duplicates weedout)
+#
+create table t1 (a int);
+create table t2 (a int);
+create table t3 (a int);
+insert into t1 values(1);
+insert into t1 values(1);
+insert into t2 values(1);
+insert into t2 values(1);
+insert into t3 values(2);
+insert into t3 values(2);
+explain select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
+2 SUBQUERY t2 ALL NULL NULL NULL NULL 2
+2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 Using where
+select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+a
+1
+1
+drop table t1,t2,t3;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result 2010-06-20 07:16:27 +0000
+++ b/mysql-test/r/subquery_sj_none.result 2010-06-28 20:43:32 +0000
@@ -4619,4 +4619,27 @@ i
1
2
drop table t1,t2,t3;
+#
+# Bug#54437 Extra rows with LEFT JOIN + semijoin (firstmatch
+# and duplicates weedout)
+#
+create table t1 (a int);
+create table t2 (a int);
+create table t3 (a int);
+insert into t1 values(1);
+insert into t1 values(1);
+insert into t2 values(1);
+insert into t2 values(1);
+insert into t3 values(2);
+insert into t3 values(2);
+explain select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
+2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 Using where
+select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+a
+1
+1
+drop table t1,t2,t3;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none_jcl6.result'
--- a/mysql-test/r/subquery_sj_none_jcl6.result 2010-06-20 07:16:27 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl6.result 2010-06-28 20:43:32 +0000
@@ -4623,6 +4623,29 @@ i
1
2
drop table t1,t2,t3;
+#
+# Bug#54437 Extra rows with LEFT JOIN + semijoin (firstmatch
+# and duplicates weedout)
+#
+create table t1 (a int);
+create table t2 (a int);
+create table t3 (a int);
+insert into t1 values(1);
+insert into t1 values(1);
+insert into t2 values(1);
+insert into t2 values(1);
+insert into t3 values(2);
+insert into t3 values(2);
+explain select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
+2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (BNL, incremental buffers)
+select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+a
+1
+1
+drop table t1,t2,t3;
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-06-20 07:16:27 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl7.result 2010-06-28 20:43:32 +0000
@@ -4623,6 +4623,29 @@ i
1
2
drop table t1,t2,t3;
+#
+# Bug#54437 Extra rows with LEFT JOIN + semijoin (firstmatch
+# and duplicates weedout)
+#
+create table t1 (a int);
+create table t2 (a int);
+create table t3 (a int);
+insert into t1 values(1);
+insert into t1 values(1);
+insert into t2 values(1);
+insert into t2 values(1);
+insert into t3 values(2);
+insert into t3 values(2);
+explain select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where
+2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where
+2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (BNL, regular buffers)
+select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
+a
+1
+1
+drop table t1,t2,t3;
set optimizer_switch=default;
set optimizer_join_cache_level=default;
show variables like 'optimizer_join_cache_level';
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2010-06-17 09:31:07 +0000
+++ b/sql/sql_select.cc 2010-06-28 20:43:32 +0000
@@ -16992,7 +16992,15 @@ evaluate_join_record(JOIN *join, JOIN_TA
for (JOIN_TAB *tab= first_unmatched; tab <= join_tab; tab++)
{
if (tab->table->reginfo.not_exists_optimize)
+ {
+ /*
+ We have a match in 'tab' but wanted none: no need to explore this
+ record and other records of 'tab', so we return "no records". But
+ as we set 'found' above, evaluate_join_record() at the upper level
+ will not yield a NULL-complemented record.
+ */
DBUG_RETURN(NESTED_LOOP_NO_MORE_ROWS);
+ }
/* Check all predicates that has just been activated. */
/*
Actually all predicates non-guarded by first_unmatched->found
@@ -17165,38 +17173,23 @@ evaluate_null_complemented_join_record(J
}
join_tab--;
/*
- The row complemented by nulls might be the first row
- of embedding outer joins.
- If so, perform the same actions as in the code
- for the first regular outer join row above.
+ From the point of view of the rest of execution, this record matches
+ (it has been built and satisfies conditions, no need to do more evaluation
+ on it). See similar code in evaluate_join_record().
*/
- for ( ; ; )
- {
- JOIN_TAB *first_unmatched= join_tab->first_unmatched;
- if ((first_unmatched= first_unmatched->first_upper) &&
- first_unmatched->last_inner != join_tab)
- first_unmatched= 0;
- join_tab->first_unmatched= first_unmatched;
- if (!first_unmatched)
- break;
- first_unmatched->found= 1;
- for (JOIN_TAB *tab= first_unmatched; tab <= join_tab; tab++)
- {
- if (tab->select_cond && !tab->select_cond->val_int())
- {
- join->return_tab= tab;
- DBUG_RETURN(NESTED_LOOP_OK);
- }
- }
- }
+ JOIN_TAB *first_unmatched= join_tab->first_unmatched;
+ if ((first_unmatched= first_unmatched->first_upper) &&
+ first_unmatched->last_inner != join_tab)
+ first_unmatched= 0;
+ join_tab->first_unmatched= first_unmatched;
/*
The row complemented by nulls satisfies all conditions
attached to inner tables.
- Send the row complemented by nulls to be joined with the
- remaining tables.
+ Finish evaluation of record and send it to be joined with
+ remaining tables:
*/
- enum_nested_loop_state nls= (*join_tab->next_select)(join, join_tab+1, 0);
- DBUG_RETURN(nls);
+ const enum_nested_loop_state rc= evaluate_join_record(join, join_tab, 0);
+ DBUG_RETURN(rc);
}
Attachment: [text/bzr-bundle] bzr/guilhem@mysql.com-20100628204332-5ld8jh3lpja7nyqi.bundle
