Bug #40035 Select not in with null value

• Submitted: 15 Oct 2008 5:52
• Modified: 15 Oct 2008 6:51
• Reporter: Olivier Goossens Bara
• Status: Not a Bug
• Category: MySQL Server: General
• Severity: S2 (Serious)
• Version: 5.1.28 and before
• OS: Any
• CPU Architecture: Any
• Tags: NOT IN, null

[15 Oct 2008 5:52] Olivier Goossens Bara

Description:
Context :
Table A contains 2000 distinct ID
Table B contains 100 distinct ID subset of Table A

select distinct 
ID
from TableA
where
(ID  not  in (select ID from TableB))

Returns NULL if table B contains a NULL ID

How to repeat:
select distinct 
ID
from TableA
where
(ID  not  in (select ID from TableB))

Returns NULL if table B contains a NULL ID

Suggested fix:
Change interpretation logic on NULL ID

[15 Oct 2008 6:01] Sveta Smirnova

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

According to http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html#function_in:

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.

[15 Oct 2008 6:51] Olivier Goossens Bara

Hello 

I'm sorry but I think there is a misunderstand

TableA contains 2000 ID
TableB contains 100 ID

If I do

select 
ID
From tableA
where ID NOT IN 
(Select ID from TableB)

I get 1900 ID : normal

If I add ONE NULL ID in table B the same select return 
Nothing
0 records

I do not thing it is normal !