From: Roy Lyseng Date: May 5 2011 7:41am Subject: Re: bzr commit into mysql-trunk branch (roy.lyseng:3367) Bug#56881 Bug#11764086 List-Archive: http://lists.mysql.com/commits/136713 Message-Id: <4DC2549F.30008@oracle.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit Hi Jørgen, On 03.05.11 13.04, Jorgen Loland wrote: > > > 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"? "Control query" deleted. > >> + >> +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? > Just plain sloppyness, I'm afraid... Please see new patch. Thanks, Roy