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

[1 Jul 2008 12:30] Roland Bouman
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
[1 Jul 2008 12:52] MySQL Verification Team
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.
[1 Jul 2008 12:52] Susanne Ebrecht
Verified as described by using MySQL 5.1 bzr tree.

There is no logic why the result should be 2.
[1 Jul 2008 12:57] MySQL Verification Team
er, that 2 <> ALL (null, 2)  should have been 2 <> ALL (null, 1)
[1 Jul 2008 13:00] MySQL Verification Team
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 >
[8 Jul 2008 18:01] 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/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).
[15 Jul 2008 12:27] 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/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).
[22 Jul 2008 18:38] Bugs System
Pushed into 5.1.28
[22 Jul 2008 18:39] Bugs System
Pushed into 5.1.28
[28 Jul 2008 14:46] Bugs System
Pushed into 6.0.7-alpha  (revid:alik@mysql.com-20080725172155-fnc73o50e4tgl23k) (version source revid:alik@mysql.com-20080725172155-fnc73o50e4tgl23k) (pib:3)
[28 Jul 2008 16:45] Bugs System
Pushed into 5.1.28  (revid:davi.arnaut@sun.com-20080722182431-0i2f1yc4uocime9q) (version source revid:davi.arnaut@sun.com-20080722182431-0i2f1yc4uocime9q) (pib:3)
[1 Aug 2008 20:32] Paul DuBois
Noted in 5.1.28, 6.0.7 changelogs.
[14 Sep 2008 5:36] Bugs System
Pushed into 6.0.7-alpha  (revid:gshchepa@mysql.com-20080714090649-jil9m8zp0eq1kqh2) (version source revid:john.embretsen@sun.com-20080724122511-9c0oudz1xrdrs6y6) (pib:3)