Hi Olav,
Patch approved with minor suggests inline.
If you're going to recommit, please consider the non-mandatory
suggestions for the commit message as well.
> 3318 Olav Sandstaa 2011-03-24
> Fix for Bug#11873324 "WRONG RESULT WITH ICP AND STRAIGHT_JOIN"
>
> This bug causes NOT IN subqueries to in some cases return too few
> rows. This can happen when we have a subquery where a table is
> normally access using ref access and we have pushed an index condition
accessed
> down to the storage engine based on this but in some cases the access
> method is changed during execution to use table scan instead of ref
> access. Queries like this are marked with 'Full scan on NULL key' in
> the explain output.
>
> Details about how this situation occurs.
>
> 1. Index condition pushdown: Since the optimizer decides that the
> access method for one of the tables in the subquery should be ref
> access this is candidate for using index condition pushdown. The ICP
> will push part of the table's condition down to the storage engine. As
> part of this it will also remove this part from the table's condition
> that should be evaluated by the server.
"The pushed parts are then removed from the table's condition and will
therefore not be evaluated by the server"?
> Summary for how this produces missing results:
>
> 1. When optimizing the subquery we push and index condition down to InnoDB
> based on that the table should be accessed using ref access.
"an"
"down to InnoDB based on...." -> "down to InnoDB. This is allowed
since ref access is chosen"?
>
> 2. If the outer query produces (in this case) an integer to be checked
"produces a non-null value"?
> === modified file 'sql/sql_select.cc'
> --- a/sql/sql_select.cc 2011-03-22 11:44:40 +0000
> +++ b/sql/sql_select.cc 2011-03-24 08:09:25 +0000
> @@ -10308,13 +10308,17 @@
> condition might then also be applied by the storage engine
> when doing the update part and result in either not finding
> the record to update or updating the wrong record.
> + 4. The jointab is not part of a subquery that has guarded conditions
JOIN_TAB
In addition: Can you please add to the documentation of cond_guards[]
that if a table has one, it means that the table will be accessed in
this special way (switch between ref and scan for null)?
--
Jørgen Løland | Senior Software Engineer | +47 73842138
Oracle MySQL
Trondheim, Norway
