Bug #2096 not in with subselect not working as expected with null

• Submitted: 11 Dec 2003 7:38
• Modified: 17 Dec 2003 21:23
• Reporter: [ name withheld ]
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1
• OS: Linux (Linux)
• Assigned to: Alexey Botchkov
• CPU Architecture: Any

[11 Dec 2003 7: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 8:41] Dean Ellis

Verified against current 4.1.2 development sources.

Thank you.

[17 Dec 2003 21: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);