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