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: [EMAIL PROTECTED] > 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 ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq