List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:May 5 2011 7:41am
Subject:Re: bzr commit into mysql-trunk branch (roy.lyseng:3367) Bug#56881
Bug#11764086
View as plain text  
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 (<empty subquery>)).
>> 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 (<empty subquery> 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<Item_in_subselect*>(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
Thread
bzr commit into mysql-trunk branch (roy.lyseng:3367) Bug#56881 Bug#11764086Roy Lyseng2 May
  • Re: bzr commit into mysql-trunk branch (roy.lyseng:3367) Bug#56881Bug#11764086Jorgen Loland3 May
    • Re: bzr commit into mysql-trunk branch (roy.lyseng:3367) Bug#56881Bug#11764086Roy Lyseng5 May