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. [EMAIL PROTECTED], 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 [EMAIL PROTECTED], 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
