Re: Fw: select null + 0 question

This was executed via sql+ on an Oracle 9i installation:

SQL> select 1 from dual where 1 in (1,2,null);

         1
----------
         1

SQL> select 1 from dual where 1 in (null);

no rows selected


I would say the Oracle implementation is correct and the same as in
Postgres. For your problem I would check the other parts of the query.

Cheers,
Csaba.


On Mon, 2003-07-14 at 08:14, 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.
> > 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.
> >
> > Vincent Hikida,
> > Member of Technical Staff - Urbana Software, Inc.
> > "A Personalized Learning Experience"
> >
> > www.UrbanaSoft.com
> >
> > ----- Original Message -----
> > From: "Jean-Christian Imbeault" <jc@mega-bucks.co.jp>
> > To: <pgsql-general@postgresql.org>
> > Sent: Sunday, July 13, 2003 10:42 PM
> > Subject: [GENERAL] select null + 0 question
> >
> >
> > > Why is it that "select null + 1" gives null but "select sum(a) from
> > > table" where there are null entries returns an integer?
> > >
> > > Shouldn't the sum() and "+" operators behave the same?
> > >
> > > TAL=# select null + 0;
> > >   ?column?
> > > ----------
> > >
> > > (1 row)
> > >
> > > TAL=# select * from a;
> > >   a
> > > ---
> > >
> > >
> > >   1
> > > (3 rows)
> > >
> > > TAL=# select sum(a) from a;
> > >   sum
> > > -----
> > >     1
> > > (1 row)
> > >
> > >
> > > Thanks,
> > >
> > > Jean-Christian Imbeault
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 8: explain analyze is your friend
> > >
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>