MySQL Bugs: #37761: IN handles NULL differently for table-subquery and value-list

Bug #37761	IN handles NULL differently for table-subquery and value-list
Submitted:	1 Jul 2008 12:30	Modified:	1 Aug 2008 20:32
Reporter:	Roland Bouman	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.1/6.0	OS:	Linux (ubuntu gutsy)
Assigned to:	Gleb Shchepa	CPU Architecture:	Any

Description:
In mysql  5.0.60 and 5.0.66 these queries both return an empty set:

select * 
from (select 1 id union all select 2) a 
where NOT id in (select null union all select 1);

select * 
from (select 1 id union all select 2) a 
where NOT id in (null, 1);

In MySQL 5.1.23 and MySQL 5.1.25, and possibly other versions, the second query using the value list returns one row:

+----+
| id |
+----+
|  2 |
+----+
1 row in set (0.00 sec)

It is not entirely clear what the bug is - it seems very intuitive that 

IN (<value-list>) 

should be the same as 

IN (subquery-equivalent-to-valuelist)

but I am not entirely sure about the subtleties of standard SQL NULL handling to say for sure.

However, it seems clear that MySQL 5 and MySQL 5.1 should behave the same for these queries.

How to repeat:
Compare on MySQL 5.0.66 and MySQL 5.1.25:

select * 
from (select 1 id union all select 2) a 
where NOT id in (select null union all select 1);

select * 
from (select 1 id union all select 2) a 
where NOT id in (null, 1);

Suggested fix:
Make MySQL 5.0 and 5.1 behave the same

select * from (select 1 id union all select 2) a where NOT id IN (null, 1);

"To comply with the SQL standard, IN  returns NULL not only if the expression on the left hand side is NULL, but also if no match is found in the list and one of the expressions in the list is NULL. "

1 IN (null, 1) -> true, and NOT(true) -> false, so 1 should not appear
2 IN (null, 1) -> NULL, and NOT(NULL) -> NULL, so 2 should not appear.

-----------------------------------------------------------------------------

select * from (select 1 id union all select 2) a where NOT id in (select null union all select 1);

"NOT IN is not an alias for <> ANY, but for <>  ALL"

1 <> ALL (null, 1) -> false, so 1 should not appear.
2 <> ALL (null, 2) -> NULL, so 2 should not appear.

Verified as described by using MySQL 5.1 bzr tree.

There is no logic why the result should be 2.

er, that 2 <> ALL (null, 2)  should have been 2 <> ALL (null, 1)

Adding 6.0:

mysql 6.0 > select *
    -> from (select 1 id union all select 2) a
    -> where NOT id in (select null union all select 1);
Empty set (0.03 sec)

mysql 6.0 >
mysql 6.0 > select *
    -> from (select 1 id union all select 2) a
    -> where NOT id in (null, 1);
+----+
| id |
+----+
|  2 |
+----+
1 row in set (0.00 sec)

mysql 6.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/49227

2696 Gleb Shchepa	2008-07-08
      Bug #37761: IN handles NULL differently for table-subquery 
                  and value-list
      
      The server returns unexpected results if a right side of the 
      NOT IN clause consists of NULL value and some constants of
      the same type, for example:
      
        SELECT * FROM t WHERE NOT t.id IN (NULL, 1, 2)
        
      may return 3, 4, 5 etc if a table contains these values.
      
      
      The Item_func_in::val_int method has been modified:
      unnecessary resets of an Item_func_case::has_null field
      value has been moved outside of an argument comparison
      loop. (Also unnecessary re-initialization of the null_value
      field has been moved).

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

2704 Gleb Shchepa	2008-07-15
      after-push patch: partial rollback of bug #37761 fix.
      
      Note: item->null_value is not updated before a call to
      item->store(), item->is_null() is not too (in common case).

Pushed into 5.1.28

Pushed into 5.1.28

Pushed into 6.0.7-alpha  (revid:alik@mysql.com-20080725172155-fnc73o50e4tgl23k) (version source revid:alik@mysql.com-20080725172155-fnc73o50e4tgl23k) (pib:3)

Pushed into 5.1.28  (revid:davi.arnaut@sun.com-20080722182431-0i2f1yc4uocime9q) (version source revid:davi.arnaut@sun.com-20080722182431-0i2f1yc4uocime9q) (pib:3)

Noted in 5.1.28, 6.0.7 changelogs.

Pushed into 6.0.7-alpha  (revid:gshchepa@mysql.com-20080714090649-jil9m8zp0eq1kqh2) (version source revid:john.embretsen@sun.com-20080724122511-9c0oudz1xrdrs6y6) (pib:3)