List:Commits« Previous MessageNext Message »
From:Guilhem Bichot Date:June 28 2010 8:43pm
Subject:bzr commit into mysql-next-mr-opt-backporting branch (guilhem:3208) Bug#54437
View as plain text  
#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
Thread
bzr commit into mysql-next-mr-opt-backporting branch (guilhem:3208) Bug#54437Guilhem Bichot28 Jun
Re: bzr commit into mysql-next-mr-opt-backporting branch(guilhem:3208) Bug#54437Jørgen Løland30 Jun
  • Re: bzr commit into mysql-next-mr-opt-backporting branch(guilhem:3208) Bug#54437Guilhem Bichot30 Jun
  • Re: bzr commit into mysql-next-mr-opt-backporting branch(guilhem:3208) Bug#54437Guilhem Bichot1 Jul
    • Re: bzr commit into mysql-next-mr-opt-backporting branch(guilhem:3208) Bug#54437Guilhem Bichot7 Jul
      • Re: bzr commit into mysql-next-mr-opt-backporting branch (guilhem:3208)Bug#54437Guilhem Bichot12 Aug
  • Re: bzr commit into mysql-next-mr-opt-backporting branch (guilhem:3208)Bug#54437Jorgen Loland9 Aug
Re: bzr commit into mysql-next-mr-opt-backporting branch (guilhem:3208)Bug#54437Roy Lyseng9 Aug
  • Re: bzr commit into mysql-next-mr-opt-backporting branch (guilhem:3208)Bug#54437Guilhem Bichot11 Aug
    • Re: bzr commit into mysql-next-mr-opt-backporting branch (guilhem:3208)Bug#54437Roy Lyseng11 Aug
      • Re: bzr commit into mysql-next-mr-opt-backporting branch (guilhem:3208)Bug#54437Guilhem Bichot11 Aug
        • Re: bzr commit into mysql-next-mr-opt-backporting branch (guilhem:3208)Bug#54437Guilhem Bichot12 Aug