MySQL Bugs: #23485: SELECT NULL IN (SELECT nothing) returns NULL instead of FALSE: unoptimized case

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:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	4.1	OS:
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	IN, null, subquery

Description:
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 
[unique_]index_subquery).

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

Analysis1:

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.

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

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.

Patch is to be applied only to 5.0->5.1.

This bug is fixed by fix for BUG#8804.