Bug #15873 Cannot use NULL in IN statement

• Submitted: 19 Dec 2005 23:18
• Modified: 20 Dec 2005 14:50
• Reporter: Nathan Tanner
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.16
• OS: Linux (FC3)
• CPU Architecture: Any

[19 Dec 2005 23:18] Nathan Tanner

Description:
Using a NULL value in an IN statement always returns empty set.

This may be more of a feature limitation rather than a bug, since this worked in 4.0 and 4.1.

How to repeat:
create table test (id int);

insert into test values (1),(2),(3),(4);

select * from test where id not in (null);

Empty set (0.06 sec)

Test case showing why this is important (as in, why we shouldn't simply write it as select * from test where id is not null):

create table test (field date);

insert into test values (curdate()),('2005-01-01'),(null),('2006-01-01');

select * from test where field not in (null,'2005-01-01');
Empty set (0.06 sec)

In this case, you want to view all records that are not NULL and not '2005-01-01', but it is impossible to write this SELECT without using separate expressions in the WHERE clause, like:

select * from test where field is not null and field not in ('2005-01-01')

Suggested fix:
Make it so that it treats 'null' in IN statements the same as it did in version 4.0.

[20 Dec 2005 14:50] Valeriy Kravchuk

Thank you for a problem report. Sorry, but current behaviour is simply correct (unlike previous), and documented in http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html:

"In order to comply with the SQL standard, IN returns NULL not only if the expression on the left hand side is NULL, but also if no match is found in the list and one of the expressions in the list is NULL."

So, this result:

mysql> select * from test where id not in (null);
Empty set (0.00 sec)

will not be changed in 5.0.x.