Re: bzr commit into mysql-next-mr-opt-backporting branch (guilhem:3208) Bug#54437

[Jørgen Løland]

Guilhem,

I'm doing my best to understand the consequences of removing the for(;;) loop 
and tried a few queries. Here's what I found so far:

#Your query
select t2.a from t2 left join t3 on t2.a=t3.a;
a
1
1
select * from t1 where t1.a in (select t2.a from t2 left join t3 on t2.a=t3.a);
a
1
1

#Modified query with inner cross-join - should be same result
select t2.a from t2 left join (t2 as t2inner,t3) on t2.a=t3.a;
a
1
1
select * from t1 where t1.a in (select t2.a from t2 left join (t2 as t2inner,t3) 
on t2.a=t3.a);
a
1

Do you agree that the result of the last query misses a '1' row?

Guilhem Bichot wrote:
> #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


-- 
Jørgen Løland