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

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