The following bug has been logged online:

Bug reference: 2178
Logged by: Daniel Afonso Heisler
Email address: daniel (AT) solis (DOT) coop.br
PostgreSQL version: 8.1.X
Operating system: Linux
Description: NOT IN command don't work
Details:

When i run the following query, postgreSQL return TRUE.
# SELECT true WHERE 1 NOT IN (2,3);

But, when i run the next query, it don't return TRUE
# SELECT true WHERE 1 NOT IN (2,NULL,3);

In theory, that is not correctly. The operator IN work with normally. See
the next example:

# SELECT true WHERE 1 IN (1,2,NULL,3);

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

On Tue, 17 Jan 2006, Daniel Afonso Heisler wrote:

This is not a bug, and the above is correct by spec.

select 1 in (NULL,2,3) is null;
- t
select 1 not in (NULL,2,3) is null
- t

IIRC, the short form is:
a NOT IN b => NOT (a IN b) => NOT (a = ANY b)

a = ANY b returns true if a=b returns true for any value in b
a = ANY b returns false if a=b returns false for every value in b
a = ANY b returns NULL otherwise

1 = 2 returns false
1 = NULL returns NULL
1 = 3 returns false

1 IN (2, NULL, 3) = NULL
NOT (1 IN (2,NULL,3)) = NULL
1 NOT IN (2,NULL,3) = NULL


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Tue, Jan 17, 2006 at 10:00:28PM +0000, Daniel Afonso Heisler wrote:
The expression "1 NOT IN (2,NULL,3)" evaluates to NULL because NULL
means "unknown." This comes up occasionally; see the archives for
past discussion.

http://archives.postgresql.org/pgsql...2/msg00219.php
http://archives.postgresql.org/pgsql...0/msg00227.php

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Tue, 2006-01-17 at 22:00 +0000, Daniel Afonso Heisler wrote:
These are not bugs.


The first statement is equivalent to

# select true where (1 != 2) and (1 != NULL) and (1 != 3);

1 != NULL is itself NULL (not false!). Similarly, TRUE AND NULL AND
TRUE evaluates to NULL, and therefore you've really written

# select true where NULL;

which, of course, should and does print nothing.


Your second query
# SELECT true WHERE 1 IN (1,2,NULL,3);

is equivalent to
# select true where (1=1) or (1=2) or (1=NULL) or (1=3);

which should and does return true.


Try these:
# select 1=1 and null;
# select 1=1 or null;
# select 1!=1 and null;
# select 1!=1 or null;


For more info, google for `sql not in NULL'. You'll see references like
http://www.metrokc.gov/gis/kb/Content/SQLTipNull.htm


-Reece

--
Reece Hart, Ph.D. rkh (AT) gene (DOT) com, http://www.gene.com/
Genentech, Inc. 650-225-6133 (voice), -5389 (fax)
Bioinformatics and Protein Engineering
1 DNA Way, MS-93 http://harts.net/reece/
South San Francisco, CA 94080-4990 reece (AT) harts (DOT) net, GPG:0x25EC91A0


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend