Thread: 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 > > >
On Sun, Jul 13, 2003 at 11:14:15PM -0700, Vincent Hikida wrote: > Oops forgot to cc the list. > > 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. I can;t comment on what the correct answer is, but I beleive the reason it works in Postgres is because the expression is expanded to: WHERE (1=1) OR (1=0) OR (1=NULL) which becomes: WHERE TRUE OR FALSE OR NULL which is TRUE. (standard tri-value logic) -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > "the West won the world not by the superiority of its ideas or values or > religion but rather by its superiority in applying organized violence. > Westerners often forget this fact, non-Westerners never do." > - Samuel P. Huntington
Attachment
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.
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
>
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 > > > > >
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@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 > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings >
On Tuesday 15 Jul 2003 7:38 pm, Vincent Hikida wrote: > 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) > 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). You might like to look over: http://techdocs.postgresql.org/guides/BriefGuideToNulls It's my typing, but I got a lot of input from the list, so it's good stuff. Sorry I didn't post this link earlier but I'm afraid I didn't look into your thread until tonight. -- Richard Huxton