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@gene.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@harts.net, GPG:0x25EC91A0