Fw: select null + 0 question

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
> >
>