Thanks for your review and your suggestions. I have made an updated
patch containing these:
and are going to push this to trunk.
On 03/24/11 01:18 PM, Jorgen Loland wrote:
> 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
> > down to the storage engine based on this but in some cases the
> > 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
> > 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.
> "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
> "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
> 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)?