List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:May 3 2011 11:04am
Subject:Re: bzr commit into mysql-trunk branch (roy.lyseng:3367) Bug#56881
Bug#11764086
View as plain text  

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
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