Bug #58628 Incorrect result for 'WHERE NULL NOT IN (<subquery>)
Submitted: 1 Dec 2010 11:16 Modified: 24 Jan 2011 15:14
Reporter: Ole John Aske Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.6.99, 5.5, 5.1 -> OS:Any
Assigned to: Ole John Aske CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[1 Dec 2010 11:16] Ole John Aske
Description:
A where condition of the form :

  'WHERE <null-valued-const-expression> NOT IN (<subquery<)
  
or: 
 
  'WHERE <null-valued-const-expression> IN (<subquery<) IS UNKNOWN

Evaluates incorrectly and may return an incorrect resultset

How to repeat:
CREATE TABLE t1 (pk INT NOT NULL, i INT);
INSERT INTO t1 VALUES (0,NULL), (1,NULL), (2,NULL), (3,NULL);

CREATE TABLE subq (pk INT NOT NULL, i INT NOT NULL, PRIMARY KEY(i,pk));
INSERT INTO subq VALUES (0,0), (1,1), (2,2), (3,3);

## Baseline queries: t1.i contains only NULL and should effectively
## be evaluated as 'WHERE NULL IN'
## .. These return a correct resultset !

SELECT * FROM t1
  WHERE t1.i NOT IN
    (SELECT i FROM subq WHERE subq.pk = t1.pk);

SELECT * FROM t1
  WHERE t1.i IN
    (SELECT i FROM subq WHERE subq.pk = t1.pk) IS UNKNOWN;

## Replaced 't1.i' with some constant expression which 
## also evaluates to NULL. Expected to return same result as above:

SELECT * FROM t1
  WHERE NULL NOT IN
    (SELECT i FROM subq WHERE subq.pk = t1.pk);

SELECT * FROM t1
  WHERE NULL IN
    (SELECT i FROM subq WHERE subq.pk = t1.pk) IS UNKNOWN;

SELECT * FROM t1
  WHERE 1+NULL NOT IN
    (SELECT i FROM subq WHERE subq.pk = t1.pk);
[1 Dec 2010 11:30] Miguel Solorzano
Thank you for the bug report.
[1 Dec 2010 12:21] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/125648

3477 Ole John Aske	2010-12-01
      Fix for bug#58628: Incorrect result for 'WHERE NULL NOT IN (<subquery>)
      
      create_ref_for_key() allowed any constant part of a REF key to be evaluated
      and stored into the 'key_buff' during ::optimize(). These 'store_key*' was
      *not* kept in ref.key_copy[] as they where constant and we assumed we
      would not have to reevaluate them during JOIN::exec()
      
      However, during execute NULL values in REF key has to be detected as they may
      need special attention - as in 'Full scan on NULL key'. This is done by 
      subselect_uniquesubquery_engine::copy_ref_key() which check if any keyparts 
      evaluated to a NULL-value.
      
      As we didn't keep a store_key for a constant value, a NULL-constant was not detected
      by subselect_uniquesubquery_engine::copy_ref_key() !
      
      This fixs modifies create_ref_for_key() to check if a NULL-value constant 
      was produced - In these cases it keeps the store_key, which then will be 
      reevaluated in JOIN::exec() and trigger correct handling of NULL-valued keys.
[1 Dec 2010 12:31] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/125649

3381 Ole John Aske	2010-12-01
      SPJ-scan-scan: Cherry picked fix for bug#58628 Incorrect result for 'WHERE NULL NOT IN (<subquery>)
      
            create_ref_for_key() allowed any constant part of a REF key to be evaluated
            and stored into the 'key_buff' during ::optimize(). These 'store_key*' was
            *not* kept in ref.key_copy[] as they where constant and we assumed we
            would not have to reevaluate them during JOIN::exec()
      
            However, during execute NULL values in REF key has to be detected as they may
            need special attention - as in 'Full scan on NULL key'. This is done by
            subselect_uniquesubquery_engine::copy_ref_key() which check if any keyparts
            evaluated to a NULL-value.
      
            As we didn't keep a store_key for a constant value, a NULL-constant was not detected
            by subselect_uniquesubquery_engine::copy_ref_key() !
      
            This fixs modifies create_ref_for_key() to check if a NULL-value constant
            was produced - In these cases it keeps the store_key, which then will be
            reevaluated in JOIN::exec() and trigger correct handling of NULL-valued keys.
[1 Dec 2010 21:35] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/125715

3382 Ole John Aske	2010-12-01
      Addendum patch for bug#58628: Update of result file due 
      to changed (improved) behaviour when constructing a REF 
      key from 'out of bound' values. 
      
      As the main fix will cause failing const key value ::copy() 
      to be reevaluate inside JOIN::exec(), we will detect out of bound
      values (which will form an illegal/undefined REF key) before 
      executing the access request. Previously these const REF keys 
      was produced by JOIN::optimize() and any conversion/range errors 
      was ignored. - Possibly causing undefined keys to be produced.
      
      These access request may then be ignored wo/ a Handler_read_key request
      being executed.
[9 Dec 2010 13:52] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/126424

3207 Ole John Aske	2010-12-09
      Provide next-mr version of fix for 
      bug#58628 'Incorrect result for 'WHERE NULL NOT IN (<subquery>)'
      on request from reviewers.
      
      MTR testcase is *not* included as the structure of the MTR tests seems to have changed
      from 5.1 -> 5.6.x - These should be picked from original commit and manually adopted later .
      
      ---- Original commit comments -------
            
            create_ref_for_key() allowed any constant part of a REF key to be evaluated
            and stored into the 'key_buff' during ::optimize(). These 'store_key*' was
            *not* kept in ref.key_copy[] as they where constant and we assumed we
            would not have to reevaluate them during JOIN::exec()
            
            However, during execute NULL values in REF key has to be detected as they may
            need special attention - as in 'Full scan on NULL key'. This is done by 
            subselect_uniquesubquery_engine::copy_ref_key() which check if any keyparts 
            evaluated to a NULL-value.
            
            As we didn't keep a store_key for a constant value, a NULL-constant was not detected
            by subselect_uniquesubquery_engine::copy_ref_key() !
            
            This fixs modifies create_ref_for_key() to check if a NULL-value constant 
            was produced - In these cases it keeps the store_key, which then will be 
            reevaluated in JOIN::exec() and trigger correct handling of NULL-valued keys.
[14 Jan 2011 8:20] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/128698

3503 Ole John Aske	2011-01-14
      Fix for bug#58628: Incorrect result for 'WHERE NULL NOT IN (<subquery>)
            
      create_ref_for_key() allowed any constant part of a REF key to be evaluated
      and stored into the 'key_buff' during ::optimize(). These 'store_key*' was
      *not* kept in ref.key_copy[] as they where constant and we assumed we
      would not have to reevaluate them during JOIN::exec()
            
      However, during execute NULL values in REF key has to be detected as they may
      need special attention - as in 'Full scan on NULL key'. This is done by 
      subselect_uniquesubquery_engine::copy_ref_key() which check if any keyparts 
      evaluated to a NULL-value.
            
      As we didn't keep a store_key for a constant value, a NULL-constant was not
      detected by subselect_uniquesubquery_engine::copy_ref_key() !
            
      This fixs modifies create_ref_for_key() to check if a NULL-value constant 
      was produced - In these cases it keeps the store_key, which then will be 
      reevaluated in JOIN::exec() and trigger correct handling of NULL-valued keys.
     @ mysql-test/r/update.result
        Update of result file due to changed (improved)
        behaviour when constructing a REF key from 'out of bound' values. 
              
        As the main fix will cause failing const key value ::copy() 
        to be reevaluate inside JOIN::exec(), we will detect out of bound
        values (which will form an illegal/undefined REF key) before 
        executing the access request. Previously these const REF keys 
        was produced by JOIN::optimize() and any conversion/range errors 
        was ignored. - Possibly causing undefined keys to be produced.
        
              
              These access request may then be ignored wo/ a Handler_read_key request
              being executed.
[14 Jan 2011 8:21] Bugs System
Pushed into mysql-trunk 5.6.2 (revid:ole.john.aske@oracle.com-20110114082008-77kfco4xs4geosam) (version source revid:ole.john.aske@oracle.com-20110114082008-77kfco4xs4geosam) (merge vers: 5.6.2) (pib:24)
[14 Jan 2011 8:44] Ole John Aske
Pushed into mysql-trunk
[14 Jan 2011 12:50] Bugs System
Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.21 (revid:ole.john.aske@oracle.com-20110114124925-6ulnj879s5ru437t) (version source revid:ole.john.aske@oracle.com-20110114124925-6ulnj879s5ru437t) (merge vers: 5.1.51-ndb-7.0.21) (pib:24)
[22 Jan 2011 22:39] Paul Dubois
Noted in 5.1.51-ndb-7.0.21, 5.6.2 changelogs.

WHERE conditions of the following forms were evaluated incorrectl and
could return incorrect results:

WHERE null-valued-const-expression NOT IN (subquery)
WHERE null-valued-const-expression IN (subquery) IS UNKNOWN

Setting report to Need Merge pending push to 5.5.x.
[23 Jan 2011 12:10] Ole John Aske
Regarding: 'Setting report to Need Merge pending push to 5.5.x':

This bug has been triaged as 'SRMRTBD' and is was my understanding that these fixes should be pushed to mysql-trunk only? Please correctly me if I am wrong, or retriage if it should be merged to 55.
[23 Jan 2011 16:32] Ole John Aske
Set to 'Documenting' - see previous comment.
[24 Jan 2011 15:14] Paul Dubois
Closing - no push to 5.5 per previous comment.
[9 Feb 2011 14:45] Ole John Aske
The reported regression bug#59650 turns out to be a test problem uncovered by this fix - So there are no regressions.
[17 Sep 2012 10:57] Timour Katchaounov
This bug is not fully fixed in MySQL 5.6. The following test case fails.
Notice that the only real difference from the original test case is the
index on table t2. Another difference is that there are no NULLs inside
the tables, because the NULLs in the tables are irrelevant. Works fine
in MariaDB 5.3/5.5.

CREATE TABLE t2d (pk INT NOT NULL, i INT NOT NULL, PRIMARY KEY(i)) engine=myisam;
INSERT INTO t2d VALUES (0,0), (1,1), (2,2), (3,3);

CREATE TABLE t2e (pk INT NOT NULL, i INT NOT NULL, PRIMARY KEY(i)) engine=innodb;
INSERT INTO t2e VALUES (0,0), (1,1), (2,2), (3,3);

EXPLAIN
SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2d.i FROM t2d WHERE t2d.pk = t1.pk);
SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2d.i FROM t2d WHERE t2d.pk = t1.pk);
SELECT * FROM t1 WHERE NULL     IN (SELECT t2d.i FROM t2d WHERE t2d.pk = t1.pk) IS UNKNOWN;
SELECT t1.pk, NULL NOT IN (SELECT t2d.i FROM t2d  WHERE t2d.pk = t1.pk) FROM t1;

EXPLAIN
SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2e.i FROM t2e WHERE t2e.pk = t1.pk);
SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2e.i FROM t2e WHERE t2e.pk = t1.pk);
SELECT * FROM t1 WHERE NULL     IN (SELECT t2e.i FROM t2e WHERE t2e.pk = t1.pk) IS UNKNOWN;
SELECT t1.pk, NULL NOT IN (SELECT t2e.i FROM t2e  WHERE t2e.pk = t1.pk) FROM t1;