Re: subquery fails when a NOT IN operator tests a subset with NULL values

Giuseppe Maxia wrote:

> mysql> select t1.* from t1 where c1 not in (select distinct c2 from t2);
> Empty set (0.01 sec)
>
> # NOT OK. This query should have returned the same result as the 
> previous one
>
> mysql> select t1.* from t1 where c1 not in (select distinct c2 from t2 
> where c2 is not null);
> +----+------+
> | id | c1   |
> +----+------+
> |  1 |    1 |
> +----+------+
> 1 row in set (0.01 sec)


This is NOT a bug. Comparisons between NULLs are not possible. SELECT 1 
= NULL results in NULL. SELECT NULL=NULL results in NULL as well. The 
exact same thing happens with PostgreSQL and other databases (like MS 
SQL) as well.

template1=# select t1.* from t1 where c1 not in (select distinct c2 from 
t2);
 id | c1
----+----
(0 rows)

template1=# select t1.* from t1 where c1 not in (select distinct c2 from 
t2 where c2 is not null);
 id | c1
----+----
  1 |  1
(1 row)


- Adam



-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]