Re: Fw: select null + 0 question - Mailing list pgsql-general

Thanks for checkin on this Csaba. I should have tried this again myself. I
have 9i on my machine and it works as you said. Of course I had this problem
on 8i perhaps I was mistaken. If someone else has 8i I would appreciate them
testing this.

Vincent Hikida,
Member of Technical Staff - Urbana Software, Inc.
"A Personalized Learning Experience"

www.UrbanaSoft.com

----- Original Message -----
From: "Csaba Nagy" <nagy@ecircle-ag.com>
To: "Vincent Hikida" <vhikida@inreach.com>
Cc: "Postgres general mailing list" <pgsql-general@postgresql.org>
Sent: Monday, July 14, 2003 1:50 AM
Subject: Re: Fw: [GENERAL] 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
> >
>
>
>