From: Jorgen Loland Date: May 3 2011 11:04am Subject: Re: bzr commit into mysql-trunk branch (roy.lyseng:3367) Bug#56881 Bug#11764086 List-Archive: http://lists.mysql.com/commits/136560 Message-Id: <4DBFE13D.1070308@oracle.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit On 05/02/2011 12:57 PM, Roy Lyseng wrote: > #At file:///home/rl136806/mysql/repo/mysql-work3/ based on revid:tor.didriksen@stripped > > 3367 Roy Lyseng 2011-05-02 > Bug#11764086 Bug#56881: Null left operand to NOT IN in WHERE clause > behaves differently than real NULL > > The query that triggers this problem is an outer join query with > a NOT IN subquery predicate in the WHERE clause. > One of the tables in the outer join contains only one row, and because > it is a MyISAM table, const table optimization is applied to it. > > Now the problem: not_null_tables() for the NOT IN predicate reports > that we can ignore NULL-complemented rows for table child, hence the > query is converted from a left join to an inner join. This leads > the query executor to omit the row with id 2 from the evaluation. > > As for why the not_null_tables() result for NOT IN is wrong: > NOT IN may return TRUE for a row, even if the left-hand expression > to NOT IN is NULL (as in NULL NOT IN ()). > This is what happens when the query executor evaluates the row with > id 2 from the parent table: There is no corresponding row in the > child table, a NULL-complemented row should be added, and the > predicate NULL NOT IN ( is evaluated (and it should > return TRUE). > > The solution to the problem is to implement not_null_tables() > for Item_in_optimizer. If the Item_in_subselect member is "top level", > meaning that the original query is an IN predicate, return the > accumulated not-null information for the object, otherwise > (the predicate is NOT IN) remove the set of tables referred from the > left-hand expression from the accumulated not-null information. > > mysql-test/include/subquery.inc > Added test case for bug#11764086. > > mysql-test/r/subquery_nomat_nosj.result > Added test results for bug#11764086. > > mysql-test/r/subquery_none.result > Added test results for bug#11764086. > > sql/item_cmpfunc.cc > Added implementation for Item_in_optimizer::not_null_tables(). > > sql/item_cmpfunc.h > Added interface for Item_in_optimizer::not_null_tables(). > > 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/item_cmpfunc.cc > sql/item_cmpfunc.h > sql/item_func.h > === modified file 'mysql-test/include/subquery.inc' > --- a/mysql-test/include/subquery.inc 2011-04-15 08:11:49 +0000 > +++ b/mysql-test/include/subquery.inc 2011-05-02 10:57:09 +0000 > @@ -4740,8 +4740,6 @@ CREATE VIEW v1 AS SELECT 1 LIKE ( 1 IN ( > CREATE VIEW v2 AS SELECT 1 LIKE '%' ESCAPE ( 1 IN ( SELECT 1 ) ); > DROP VIEW v1, v2; > > ---echo End of 5.1 tests. > - > # > # Bug #47904 Incorrect results w/ table subquery, derived SQs, and LEFT JOIN on index > # > @@ -5101,6 +5099,66 @@ SELECT 1 FROM > > DROP TABLE t1; > > +--echo # > +--echo # Bug#11764086: Null left operand to NOT IN in WHERE clause > +--echo # behaves differently than real NULL > +--echo # > + > +CREATE TABLE parent (id int); > +INSERT INTO parent VALUES (1), (2); > + > +CREATE TABLE child (parent_id int, other int); > +INSERT INTO child VALUES (1,NULL); > + > +--echo # Control query (c.other is always NULL) This is the same query as Offending query. Did you mean "c.other NOT IN"? > + > +SELECT p.id, c.parent_id > +FROM parent p > +LEFT JOIN child c > +ON p.id = c.parent_id > +WHERE c.parent_id NOT IN ( > + SELECT parent_id > + FROM child > + WHERE parent_id = 3 > + ); > + > +--echo # Offending query (c.parent_id is NULL for null-complemented rows only) > + > +SELECT p.id, c.parent_id > +FROM parent p > +LEFT JOIN child c > +ON p.id = c.parent_id > +WHERE c.parent_id NOT IN ( > + SELECT parent_id > + FROM child > + WHERE parent_id = 3 > + ); > + ... > === modified file 'sql/item_cmpfunc.cc' > --- a/sql/item_cmpfunc.cc 2011-04-26 08:49:10 +0000 > +++ b/sql/item_cmpfunc.cc 2011-05-02 10:57:09 +0000 > @@ -1791,6 +1791,24 @@ void Item_in_optimizer::fix_after_pullou > const_item_cache&= args[1]->const_item(); > } > > +table_map Item_in_optimizer::not_null_tables() const > +{ > + table_map not_null_tables_value= Item_bool_func::not_null_tables(); > + > + if (!(static_cast(args[1]))->is_top_level_item()) > + { > + /* > + This is a NOT IN subquery predicate (or equivalent). Null values passed > + from outer tables and used in the left-hand expression of the predicate > + must be considered in the evaluation, hence filter out these tables > + from the set of null-rejecting tables. > + */ > + not_null_tables_value&= ~args[0]->not_null_tables(); > + } > + return not_null_tables_value; > +} > + > + It seems like not_null_tables_cache is calculated in Item*::fix_fields() for most (all?) other items. Is there a reason why you don't calculate not_null_tables_cache in Item_in_optimizer::fix_fields() instead of implementing the Item_in_optimizer::not_null_tables() specialization? -- Jørgen Løland | Senior Software Engineer | +47 73842138 Oracle MySQL Trondheim, Norway