Re: Fw: select null + 0 question

On Sun, 13 Jul 2003, Vincent Hikida wrote:

> Oops forgot to cc the list.
>
> > Unfortunately, intra-row functions using nulls return nulls. Inter-row
> > functions "usually" ignore the nulls. I think there may be a few
> exceptions.
> > Though there is a relational theory which has is rigorously consistent,
> > nulls are not part of the theory. Nulls are basically what
> someone/somewhere
> > thought of as a convenient tool (which it is) but has no theoretical
> > underpinning and is dangerous. I use it because I feel that I have enough
> > experience but perhaps I'll be stung one day.
> >
> > It has been discussed on the list before that in Oracle that in Oracle an
> > empty string and null are the same. However Postgres treats an empty
> string
> > '' as an actual value and not as null.
> >
> > 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.

If this is really true, then I believe Oracle does not implement this
feature correctly. By my reading of SQL92, RVC IN IPV is equivalent to
RVC =ANY IPV and in 8.7 GR2c, "If the implied <comparison predicate> is
true for at least one row RT in T, then R <comp op> <some> T" is true
which I believe holds for the case above.