Bug #37156 sql grammer "Not IN" or "IN"

• Submitted: 3 Jun 2008 11:05
• Modified: 3 Jun 2008 11:39
• Reporter: kinfei lee
• Status: Not a Bug
• Category: MySQL Websites: bugs.mysql.com
• Severity: S3 (Non-critical)
• Version: 5
• OS: Windows
• CPU Architecture: Any

[3 Jun 2008 11:05] kinfei lee

Description:
SELECT * FROM (
( SELECT '1' AS ID ) UNION
( SELECT '2') ) a
WHERE a.ID NOT IN ( '1', null )

will return nothing

SELECT * FROM (
( SELECT '1' AS ID ) UNION
( SELECT '2') ) a
WHERE a.ID NOT IN ( '1' )

will return '2'

Is that the correct behavior or a DBSM?

How to repeat:
SELECT * FROM (
( SELECT '1' AS ID ) UNION
( SELECT '2') ) a
WHERE a.ID NOT IN ( '1', null )

[3 Jun 2008 11:39] Susanne Ebrecht

Many thanks for writing a bug report.

This is an expected behaviour and you can find it at other RDBMS as well.

The problem here is NULL.

The "IN" always is looking for true/false as result, but when you compare something with NULL then the result is NULL.

your syntax: not in ('1', NULL)

this means: where ((id not = 1) AND (id not = NULL)) this has to get true for row selection.

So the result is (true/false AND NULL) => neither true or false because you don't know what is NULL. For an AND logic both parts has to get true for getting true at the end.

Comparations with NULL are only possible in SQL by using the "IS (NOT) NULL" syntax.