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

On Tue, 2006-01-17 at 22:00 +0000, Daniel Afonso Heisler wrote:
> But, when i run the next query, it don't return TRUE
> # SELECT true WHERE 1 NOT IN (2,NULL,3);

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