Guilhem,
Patch approved.
On 06/21/2011 10:01 AM, Guilhem Bichot wrote:
> #At file:///home/mysql_src/bzrrepos_new/mysql-next-mr-opt-backporting/ based on
> revid:guilhem.bichot@stripped
>
> 3387 Guilhem Bichot 2011-06-21
> Fix for BUG#12616253 - WRONG RESULT WITH EXISTS(SUBQUERY) (MISSING ROWS)
> QUICK_GROUP_MIN_MAX_SELECT::reset() didn't reset the "index scan"
> to start from the first key.
> @ mysql-test/r/subquery_all.result
> without the code fix, this and other results showed only (v,1) and (s,1)
> (two rows instead of twenty).
> @ sql/opt_range.cc
> QUICK_GROUP_MIN_MAX_SELECT is used (see EXPLAIN output in result
> files). Execution happens as described below.
> 1) First row of "table1 JOIN table2" gives WHERE condition
> "<>j AND>=v" for subquery.
> When subq execution starts, sub_select() calls join_init_read_record()
> which calls QUICK_GROUP_MIN_MAX_SELECT::reset().
> Then keys are scanned using QUICK_GROUP_MIN_MAX_SELECT::get_next() and
> QUICK_GROUP_MIN_MAX_SELECT::next_prefix(): key "c" is read,
> "d", etc, none matches WHERE clause (as determined in
> evaluate_join_record()), until key "v" is read, which
> matches. EXISTS() is thus true, first row of "table2 JOIN table1" is
> emitted.
> 2) Second row of "table1 JOIN table2" gives WHERE condition
> "<>v AND>=v" for subquery.
> When subq execution starts, QUICK_GROUP_MIN_MAX_SELECT::reset() is called.
> Then keys are scanned, but QUICK_GROUP_MIN_MAX_SELECT::next_prefix()
> starts where it left off at end of previous subq execution: it left at
> "v" so (see index_next_different()) it finds "y", which
> matches. EXISTS() is thus true, second row of "table1 JOIN table2" is
> emitted.
> 3) Third row of "table1 JOIN table2".
> When subq execution, QUICK_GROUP_MIN_MAX_SELECT::reset() is called.
> Then keys are scanned, but starting where it left off at end of
> previous subq execution: it left at "y" so index_next_different()
> finds nothing (there is nothing after "y"). Row of "table1 JOIN
> table2" is not emitted. Same for all subsequent rows.
>
> Fix: reset seen_first_key in reset().
> The description above was without block-level nested loop join. With
> BNL, rows of "table1 JOIN table2" are created in different order, so
> the two first rows for which the subq is executed are different. But
> the bug is the same.
>
> modified:
> mysql-test/include/subquery.inc
> mysql-test/r/subquery_all.result
> mysql-test/r/subquery_all_jcl6.result
> mysql-test/r/subquery_nomat_nosj.result
> mysql-test/r/subquery_nomat_nosj_jcl6.result
> mysql-test/r/subquery_none.result
> mysql-test/r/subquery_none_jcl6.result
> sql/opt_range.cc
--
Jørgen Løland | Senior Software Engineer | +47 73842138
Oracle MySQL
Trondheim, Norway
