Bug #2096 not in with subselect not working as expected with null
Submitted: 11 Dec 2003 8:38 Modified: 17 Dec 2003 22:23
Reporter: [ name withheld ]
Status: Not a Bug
Category:Server Severity:S3 (Non-critical)
Version:4.1 OS:Linux (Linux)
Assigned to: Alexey Botchkov Target Version:

[11 Dec 2003 8:38] [ name withheld ] 
Description:
not in subselects with a null in the result set do not return any values.

How to repeat:
create temporary table null_test_1 (col1 varchar(255), col2 varchar(255));
create temporary table null_test_2 (col1 varchar(255), col2 varchar(255));
insert into null_test_1 values (1, 1);
insert into null_test_1 values (1, NULL);
insert into null_test_2 values (1, 3);

select * from null_test_2 where col2 not in (select col2 from null_test_1);
Empty set (0.00 sec)

---

by contrast "in" statements do work as expected.
[11 Dec 2003 9:41] Dean Ellis 
Verified against current 4.1.2 development sources.

Thank you.
[17 Dec 2003 22:23] Alexey Botchkov 
It's actually the expected behaviour.
NOT IN subquery will return NULL if there are NULL-s in subquery's results
and no matches. And NULL in WHERE condition works like FALSE.
You can use slightly modified query to get results you want:
SELECT * FROM null_test_2 WHERE col2 NOT IN (SELECT col2 FROM null_test_1 WHERE col2 IS
NOT NULL);