List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:March 24 2011 12:18pm
Subject:Re: bzr commit into mysql-trunk branch (olav.sandstaa:3318) Bug#11873324
View as plain text  
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
Thread
bzr commit into mysql-trunk branch (olav.sandstaa:3318) Bug#11873324Olav Sandstaa24 Mar
  • Re: bzr commit into mysql-trunk branch (olav.sandstaa:3318) Bug#11873324Jorgen Loland24 Mar
    • Re: bzr commit into mysql-trunk branch (olav.sandstaa:3318) Bug#11873324Olav Sandstaa24 Mar