Re: Fw: select null + 0 question

OK. This has been bothering me. I finally recreated what I remember as an
anomoly in Oracle involving nulls. It also works the same way in PostgreSQL.
I guess in mind I extrapolated that there was a problem with "WHERE 1 IN
(1,2,NULL)" which there isn't.

The following is my query in 9i:

SQL> select 1 from dual where 2 not in (1,null);
no rows selected

The following is in PostgreSQL:

select 1 where 2 not in (1,null)
?column?
------------
(0 rows)

Of course this is just a simple test. The actual query that was being used
when we found the anomoly was more practical:

select ... from tablex where x.a not in (select y.a from tabley);

We finally did something like this to get what we wanted:

select .. from tablex where not exists (select null from tabley where y.a =
x.a);

Now that I think about it a little, SQL does make sense. We are sure that "1
is in (1,2,NULL)". However we cannot be sure that in 3 is not in (1,2, NULL)
because the NULL maybe 3 (if you interpret null as being unknown). However,
the SQL does not work if you have other interpretations of NULL (e.g. Null
means not applicable, Null means divide by 0).

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

www.UrbanaSoft.com

----- Original Message -----
From: "Csaba Nagy" <nagy(at)ecircle-ag(dot)com>
To: "Vincent Hikida" <vhikida(at)inreach(dot)com>
Cc: "Postgres general mailing list" <pgsql-general(at)postgresql(dot)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(at)mega-bucks(dot)co(dot)jp>
> > > To: <pgsql-general(at)postgresql(dot)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
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>