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"?
> +
> +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?
--
Jørgen Løland | Senior Software Engineer | +47 73842138
Oracle MySQL
Trondheim, Norway
