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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Mike Haller | 2006-03-22 16:27:23 | BUG #2354: No admin rights, but still refuses to run |
Previous Message | Jim C. Nasby | 2006-03-22 13:29:43 | Re: BUG #2337: database connection |