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

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

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Aaron Logue <gyro(at)cryogenius(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #2961: NULL values in subselects force NOT IN to false
Date: 2007-02-06 23:48:51
Message-ID: 20070206153719.D14511@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-bugs
On Tue, 6 Feb 2007, Aaron Logue wrote:

> 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 = ?

It depends on what you're trying to do, really. SQL could have defined IN
differently, but they decided to make IN in terms of equality rather than
say distinctness.

In response to

pgsql-bugs by date

Next:From: Gary ChambersDate: 2007-02-07 01:20:08
Subject: Re: BUG #2962: 8.2.1 lo_creat Documentation incorrect?
Previous:From: Aaron LogueDate: 2007-02-06 22:54:38
Subject: Re: BUG #2961: NULL values in subselects force NOT IN to false

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