From: | Marinos Yannikos <mjy(at)geizhals(dot)at> |
---|---|
To: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> |
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 04:57:16 |
Message-ID: | 4420D92C.9090606@geizhals.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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)
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...).
Regards,
Marinos
From | Date | Subject | |
---|---|---|---|
Next Message | Qingqing Zhou | 2006-03-22 06:35:06 | inpricise checkpoint stats |
Previous Message | Support FireDigit | 2006-03-21 22:42:19 | Re: BUG #2343: Silent installers fails |