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

Re: BUG #2334: WHERE IN (SUBSELECT) fails when column is null

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Marinos Yannikos <mjy(at)geizhals(dot)at>
Cc: Patrick Narkinsky <patrick(at)narkinsky(dot)com>,pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #2334: WHERE IN (SUBSELECT) fails when column is null
Date: 2006-03-22 14:06:37
Message-ID: 20060322060020.L8100@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-bugs
On Wed, 22 Mar 2006, Marinos Yannikos wrote:

> Stephan Szabo schrieb:
> > AFAICS, our behavior follows SQL.
> >
> > a NOT IN b is NOT(a IN b)
> > IN is defined in terms of = ANY.
> > a =ANY (b) is basically (by my reading of 8.8 anyway):
> >  True if a = bi for some bi in b
> >  False if b is empty or a <> bi for all bi in b
> >  Unknown otherwise
> > Since a <> NULL returns unknown, the second one won't come up, so the
> > whole expression won't ever be true after the negation.  It might be false
> > or it might be unknown.
> >
>
> Not having read 8.8, I encountered this today and found it odd as well.
> It would mean that the old popular optimization, back when "A IN B" was
> much slower, was not correct:
>
> select * from foo where a not in (select b from bar)
>
> used to be written as:
>
> select * from foo where not exists (select 1 from bar where a=b)

Yep, in->exists I believe is the same, but not in->not exists is
different. Exists and subqueries should probably have been done
differently in SQL, but alas.

> These queries have different results now when b is NULL for some rows.
> It doesn't look right to me (but if the Standard requires it, what can
> we do...).

It actually makes some sense if you think about null as an unknown value.

If you ask is 1 in the set (1, 2, unknown), you can definately say yes.
If you ask is 3 in the set (1, 2, unknown), you can't be sure, because
that unknown might be 3.
For any x that's of the correct type for the set, you'll never be able to
say no due to that unknown.

If you ask is 1 not in the set (1, 2, unknown) you can definately say no.
If you ask is 3 not in the set (1, 2, unknown) you again can't be sure.
For any x that's of the correct type for the set, you'll never be able to
say yes due to that unknown.


In response to

pgsql-bugs by date

Next:From: Mike HallerDate: 2006-03-22 16:27:23
Subject: BUG #2354: No admin rights, but still refuses to run
Previous:From: Jim C. NasbyDate: 2006-03-22 13:29:43
Subject: Re: BUG #2337: database connection

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