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

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.