Re: Fw: select null + 0 question

On Sun, Jul 13, 2003 at 11:14:15PM -0700, Vincent Hikida wrote:
> Oops forgot to cc the list.
> > I just happened to notice another difference recently between Oracle and
> > Postgresql for the clause
> >
> > WHERE 1 IN (1,2,NULL)
> >
> > In Oracle, this clause is false because 1 compared to a NULL is false.
> > However, Postgresql will return a true. I actually don't know what the
> ANSI
> > standard is for this case. Perhaps someone else on this list will know.
> > Perhaps the standard body never even thought of this. Yes, I was actually
> > stung by this particular while using it in Oracle.

I can;t comment on what the correct answer is, but I beleive the reason it
works in Postgres is because the expression is expanded to:

WHERE (1=1) OR (1=0) OR (1=NULL)

which becomes:

WHERE TRUE OR FALSE OR NULL

which is TRUE. (standard tri-value logic)
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> "the West won the world not by the superiority of its ideas or values or
> religion but rather by its superiority in applying organized violence.
> Westerners often forget this fact, non-Westerners never do."
>   - Samuel P. Huntington