Re: BUG #2178: NOT IN command don't work

The expected behavior can be obtained by filtering out the null in
the subquery or by using "not exists" instead of "not in".

Here is an example:

CREATE TEMPORARY TABLE subquerytable (column1 INTEGER);
INSERT INTO subquerytable VALUES(2);
INSERT INTO subquerytable VALUES(NULL);
INSERT INTO subquerytable VALUES(3);

SELECT true WHERE 1 NOT IN (SELECT column1 FROM subquerytable); -- Wrong
SELECT true WHERE 1 NOT IN (SELECT column1 FROM subquerytable WHERE column1
IS NOT NULL); -- Ok
SELECT true WHERE NOT EXISTS(SELECT * FROM subquerytable WHERE 1 =
column1); -- Ok

It's not clear to me why "not exists" and "not in" return a different result
but it must be per SQL spec
as all DBMS I have seen do that (Oracle, SQL Server, MYSQL, ...)

In most queries I have seen column1 is NOT NULL so IN or EXISTS can both be
used safely.

Jean-Pierre Pelletier
e-djuster