Bug #24085 Wrong query result for "NULL IN (SELECT ... UNION SELECT ...)"
Submitted: 8 Nov 2006 12:15 Modified: 3 Feb 2007 2:41
Reporter: Sergey Petrunya Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version: OS:
Assigned to: Sergey Petrunya CPU Architecture:Any
Tags: NULL IN, subquery, UNION

[8 Nov 2006 12:15] Sergey Petrunya
Description:
Queries that evaluate "NULL IN (SELECT ... UNION SELECT ...)" may produce wrong result (FALSE instead of NULL).
This seems to be some case not covered by fix for the BUG#8804.

How to repeat:
create table t1 (a int, b int, primary key (a));
insert into t1 values (1,1), (3,1),(100,1);
create table t2 (a int, b int);
insert into t2 values (1,1),(2,1),(NULL,1),(NULL,0);

mysql> select a,b, a in (select a from t1 where t1.b = t2.b union select a from t1 where t1.b = t2.b) Z from t2 ;
+------+------+------+
| a    | b    | Z    |
+------+------+------+
|    1 |    1 |    1 | 
|    2 |    1 |    0 | 
| NULL |    1 |    0 | 
| NULL |    0 |    0 | 
+------+------+------+
4 rows in set (0.00 sec)

mysql> select a,b, a in (select a from t1 where t1.b = t2.b) Z from t2 ;
+------+------+------+
| a    | b    | Z    |
+------+------+------+
|    1 |    1 |    1 | 
|    2 |    1 |    0 | 
| NULL |    1 | NULL | 
| NULL |    0 |    0 | 
+------+------+------+
4 rows in set (0.00 sec)

Suggested fix:
We need to investigate why BUG#8804 fix code doesn't handle this case. It should be able to.
[8 Nov 2006 14:05] Sergey Petrunya
The problem seems to have to do something with whether the part of the subquery select list can be NULL or not.

Testcase:
drop table t1, t2, t3;
create table t1 (a int, b int, key(a));
insert into t1 values 
  (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);

create table t2 like t1;
insert into t2 select * from t1;
update t2 set b=1;

create table t3 (a int, oref int);
insert into t3 values (1, 1), (NULL,1), (NULL,0);
select a, oref, 
       t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z 
from t3;
+------+------+------+
| a    | oref | Z    |
+------+------+------+
|    1 |    1 |    1 | 
| NULL |    1 | NULL | 
| NULL |    0 |    0 | 
+------+------+------+
This result is correct.

Now add NOT NULL to tables used inside subquery:
create table t1 (a int NOT NULL, b int NULL, key(a));
and run all the same queries, and you get an incorrect result:
+------+------+------+
| a    | oref | Z    |
+------+------+------+
|    1 |    1 |    1 | 
| NULL |    1 |    0 | 
| NULL |    0 |    0 | 
+------+------+------+
[9 Nov 2006 8: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/15067

ChangeSet@1.2303, 2006-11-09 11:31:31+03:00, sergefp@mysql.com +4 -0
  BUG#24085: Wrong result for NULL IN (SELECT not_null_val FROM ...)
  When transforming "oe IN (SELECT ie ...)" wrap the pushed-down predicates
  iff "oe can be null", not "ie can be null".
   
  The fix doesn't cover row-based subqueries, those will be fixed in #24085.
[12 Jan 2007 19:12] 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/18064

ChangeSet@1.2375, 2007-01-12 22:11:40+03:00, sergefp@mysql.com +4 -0
  BUG#24085: Wrong result for NULL IN (SELECT not_null_val FROM ...)
  When transforming "oe IN (SELECT ie ...)" wrap the pushed-down predicates
  iff "oe can be null", not "ie can be null".
  The fix doesn't cover row-based subqueries, those will be fixed in #24127.
[28 Jan 2007 2:19] Igor Babaev
The fix has been pushed into 5.0.36, 5.1.16-beta main trees.
[3 Feb 2007 2:41] Paul Dubois
Noted in 5.0.36, 5.1.16 changelogs.

Queries that evaluate NULL IN (SELECT ... UNION SELECT ...) could
produce an incorrect result (FALSE instead of NULL).