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
