Re: BUG #2961: NULL values in subselects force NOT IN to false

On Mon, 5 Feb 2007, Stephan Szabo wrote:
> On Fri, 2 Feb 2007, Aaron Logue wrote:
> > The following bug has been logged online:
> >
> > Bug reference: 2961
> > Logged by: Aaron Logue
> > Email address: gyro(at)cryogenius(dot)com
> > PostgreSQL version: 8.2.1
> > Operating system: Linux (various flavors)
> > Description: NULL values in subselects force NOT IN to false
> > Details:
> >
> > SELECT X FROM (SELECT 42 AS X) AS FOO WHERE X NOT IN (7,NULL);
> >
> > returns 0 rows. Shouldn't "X NOT IN (7,NULL)" be
> > true if X is neither 7 nor NULL? Removing the NULL causes the row to be
> > returned.
>
> NOT IN with NULLs is defined by spec in a way that most people do not
> expect if they aren't thinking about three valued logic.
>
> x NOT IN RVC is effectively NOT(x = ANY RVC).
> ...

Shouldn't IS be used to compare x with a NULL rather than = ?