Bug #23485 SELECT NULL IN (SELECT nothing) returns NULL instead of FALSE: unoptimized case
Submitted: 20 Oct 2006 1:01 Modified: 31 Oct 2006 22:47
Reporter: Sergey Petrunya Email Updates:
Status: Duplicate Impact on me:
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:4.1 OS:
Assigned to: Assigned Account
Tags: IN, null, subquery

[20 Oct 2006 1:01] Sergey Petrunya
SELECT NULL IN (SELECT nothing) returns NULL instead of FALSE. This bug covers the case where SELECT nothing is not optimized (i.e. EXPLAIN output doesn't contain 

I'm creating this bug to chop off a part from BUG#8804.

How to repeat:
create table t100 (a int, b int);
insert into t100 values (NULL, NULL),(NULL, 0),(0, NULL),(0, 0),(1, 1);

create table t101 (a int, b int);
insert into t101 values (0,0), (2,2); 

select t100.a, t100.b, 
       t100.a in (select t101.a from t101 where t101.b=t100.b) as SUBQ 
from t100;
| a    | b    | SUBQ |
| NULL | NULL | NULL | "NULL in (empty)" must be FALSE.
| NULL |    0 | NULL | OK
|    0 | NULL |    0 | OK
|    0 |    0 |    1 | OK
|    1 |    1 |    0 | OK
[20 Oct 2006 1:05] Sergey Petrunya

The subquery is evaluated using class Item_in_optimizer::val_int(). The comment for that function says:
    Item_in_optimizer item is special boolean function. On value request 
    (one of val, val_int or val_str methods) it evaluate left expression 
    of IN by storing it value in cache item (one of Item_cache* items), 
    then it test cache is it NULL. If left expression (cache) is NULL then
    Item_in_optimizer return NULL, else it evaluate Item_in_subselect.
The code follows the comment. The part marked with ^^^ is incorrect, as 
 NULL IN (SELECT * FROM empty_rowset) should be FALSE.
[20 Oct 2006 20:43] 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:


ChangeSet@1.2533, 2006-10-21 00:48:40+04:00, sergefp@mysql.com +5 -0
  BUG#23485: wrong result for "SELECT NULL IN (SELECT nothing)", direct subquery execution case
  - Make the direct subquery execution engine to evaluate NULL IN (SELECT ...) in a special way
    that produces correct results.
  Note: This breaks [unique_]index_subquery, will fix in forthcoming commit.
[31 Oct 2006 21:03] Igor Babaev
Patch is to be applied only to 5.0->5.1.
[31 Oct 2006 22:47] Sergey Petrunya
This bug is fixed by fix for BUG#8804.