| 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: | |
| 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 |
[1 Dec 2010 11:30]
MySQL Verification Team
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;
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);