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

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.