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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
