Thread: BUG #2178: NOT IN command don't work
The following bug has been logged online: Bug reference: 2178 Logged by: Daniel Afonso Heisler Email address: daniel@solis.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);
On Tue, 17 Jan 2006, Daniel Afonso Heisler wrote: > > The following bug has been logged online: > > Bug reference: 2178 > Logged by: Daniel Afonso Heisler > Email address: daniel@solis.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); 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
On Tue, Jan 17, 2006 at 10:00:28PM +0000, Daniel Afonso Heisler wrote: > 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); 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-sql/2005-12/msg00219.php http://archives.postgresql.org/pgsql-sql/2005-10/msg00227.php -- Michael Fuhr
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
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