MySQL Bugs: #24085: Wrong query result for "NULL IN (SELECT ... UNION SELECT ...)"

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

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.

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

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.

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.

The fix has been pushed into 5.0.36, 5.1.16-beta main trees.

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