Skip site navigation (1) Skip section navigation (2)

Re: BUG #2178: NOT IN command don't work

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Daniel Afonso Heisler <daniel(at)solis(dot)coop(dot)br>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #2178: NOT IN command don't work
Date: 2006-01-19 20:55:32
Message-ID: 20060119125059.P31364@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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(at)solis(dot)coop(dot)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


In response to

pgsql-bugs by date

Next:From: Jaime CasanovaDate: 2006-01-19 21:10:57
Subject: Re: BUG #2185: function compilation error with "Create [TEMP] table?
Previous:From: Bruce MomjianDate: 2006-01-19 20:38:33
Subject: Re: BUG #2183: Cannot enter Paragraph Type data

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group