Thread: greatest/least semantics different between oracle and postgres
The following is just FYI. I was recently doing some stuff with greatest() on oracle (9.2.0.8.0) and noticed that it returned null if ANY of the arguments were null. Out of curiosity I checked postgres' definition of that function and found that it returns null only if ALL of the arguments are null.
On 6/30/07, Bruno Wolff III <bruno@wolff.to> wrote: > The following is just FYI. > I was recently doing some stuff with greatest() on oracle (9.2.0.8.0) and > noticed that it returned null if ANY of the arguments were null. Out of > curiosity I checked postgres' definition of that function and found that it > returns null only if ALL of the arguments are null. W/o knowing the SQL standard (just from what I'd perceive as sensible) I'd say Oracle is broken. :} -- Cheers, Andrej
"Andrej Ricnik-Bay" <andrej.groups@gmail.com> writes:
> On 6/30/07, Bruno Wolff III <bruno@wolff.to> wrote:
>> I was recently doing some stuff with greatest() on oracle (9.2.0.8.0) and
>> noticed that it returned null if ANY of the arguments were null. Out of
>> curiosity I checked postgres' definition of that function and found that it
>> returns null only if ALL of the arguments are null.
> W/o knowing the SQL standard (just from what I'd perceive
> as sensible) I'd say Oracle is broken. :}
Hmm ... I fear Oracle's behavior is more correct, because if any
argument is null (ie, unknown), then who can say what the greatest or
least value is? It's unknown (ie, null). But I suspect our behavior
is more useful. Comments?
regards, tom lane
On 6/30/07, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Hmm ... I fear Oracle's behavior is more correct, because if any > argument is null (ie, unknown), then who can say what the greatest or > least value is? It's unknown (ie, null). But I suspect our behavior > is more useful. Comments? But in min/max scenarios NULL values are simply ignored, too, no? > regards, tom lane Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm
On Sat, Jun 30, 2007 at 00:15:42 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Andrej Ricnik-Bay" <andrej.groups@gmail.com> writes: > > On 6/30/07, Bruno Wolff III <bruno@wolff.to> wrote: > >> I was recently doing some stuff with greatest() on oracle (9.2.0.8.0) and > >> noticed that it returned null if ANY of the arguments were null. Out of > >> curiosity I checked postgres' definition of that function and found that it > >> returns null only if ALL of the arguments are null. > > > W/o knowing the SQL standard (just from what I'd perceive > > as sensible) I'd say Oracle is broken. :} > > Hmm ... I fear Oracle's behavior is more correct, because if any > argument is null (ie, unknown), then who can say what the greatest or > least value is? It's unknown (ie, null). But I suspect our behavior > is more useful. Comments? In my case I would have prefered Postgres' behavior. I wanted to take the max of values coming from two columns by taking the greatest of two subselects. I ended up rewriting the query to take the max of a union. The annoying thing was I didn't have a good way to use coalesce as I wanted to get a null if both subselects were empty. Also what value should I have used in a coalesce to guaranty still getting the maximum? I think having it work like aggregates and ignoring null values is more convenient. However if the feature was added for oracle compatibility then not working the same is an issue. I was just hoping that perhaps the fact that the semantics are different between oracle and postgres would get noted somewhere so people porting would have a better chance to become aware of the issue.
On Jun 29, 2007, at 9:15 PM, Tom Lane wrote: > Hmm ... I fear Oracle's behavior is more correct, because if any > argument is null (ie, unknown), then who can say what the greatest or > least value is? It's unknown (ie, null). But I suspect our behavior > is more useful. Comments? I agree with you. I don't know what the spec says, but it seems clear Oracle is doing the proper thing and Postgres is doing the useful thing.
I believe the spec says nulls are ignored for min/max. Postgres is as far
as I know behaving according to spec.
But I question the original poster's report of Oracle's behavior. I don't
have 9.2.0.8 to test, but on 9.2.0.7:
SQL> select f1, case when f1 is not null then 'not null' else 'null' end if
from t;
F1 IF
---------- ------------------------
1 not null
2 not null
null
SQL> select max(f1) from t;
MAX(F1)
----------
2
SQL> select version from v$instance;
VERSION
---------------------------------------------------
9.2.0.7.0
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Ben
> Sent: Friday, June 29, 2007 10:18 PM
> To: Tom Lane
> Cc: PostgreSQL General ((EN))
> Subject: Re: [GENERAL] greatest/least semantics different between oracle
> and postgres
>
> On Jun 29, 2007, at 9:15 PM, Tom Lane wrote:
>
> > Hmm ... I fear Oracle's behavior is more correct, because if any
> > argument is null (ie, unknown), then who can say what the greatest or
> > least value is? It's unknown (ie, null). But I suspect our behavior
> > is more useful. Comments?
>
> I agree with you. I don't know what the spec says, but it seems clear
> Oracle is doing the proper thing and Postgres is doing the useful thing.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
Ben <bench@silentmedia.com> writes:
> On Jun 29, 2007, at 9:15 PM, Tom Lane wrote:
>> Hmm ... I fear Oracle's behavior is more correct, because if any
>> argument is null (ie, unknown), then who can say what the greatest or
>> least value is? It's unknown (ie, null). But I suspect our behavior
>> is more useful. Comments?
> I agree with you. I don't know what the spec says, but it seems clear
> Oracle is doing the proper thing and Postgres is doing the useful thing.
GREATEST/LEAST aren't in the spec, so there's not much help there.
Except ... if they ever do get added to the spec, what do you think
the spec will say? The odds it'd contradict Oracle seem about nil.
regards, tom lane
"paul rivers" <rivers.paul@gmail.com> writes:
> But I question the original poster's report of Oracle's behavior. I don't
> have 9.2.0.8 to test, but on 9.2.0.7:
Er ... your example doesn't actually seem to involve greatest() or
least()?
regards, tom lane
> > Er ... your example doesn't actually seem to involve greatest() or > least()? > So sorry, it's been a long day, I misread. Yes, greatest/least definitely does work on Oracle as the OP said. Apologies again.
Hello, I have not Oracle, so I cannot test it, but PostgreSQL implementation respect Oracle: http://archives.postgresql.org/pgsql-patches/2005-06/msg00431.php Regards Pavel Stehule 2007/6/30, Bruno Wolff III <bruno@wolff.to>: > The following is just FYI. > I was recently doing some stuff with greatest() on oracle (9.2.0.8.0) and > noticed that it returned null if ANY of the arguments were null. Out of > curiosity I checked postgres' definition of that function and found that it > returns null only if ALL of the arguments are null. > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
"Bruno Wolff III" <bruno@wolff.to> writes: > Also what value should I have used in a coalesce to guaranty still getting > the maximum? I would expect coalesce(coalesce(greatest(a,b),a),b) to do what you want. It's not terribly legible though and if a and b are subselects I would worry a little about the optimizer rerunning them unnecessarily. Perhaps coalesce(greatest(a,b), coalesce(a,b)) is more legible? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
"Tom Lane" <tgl@sss.pgh.pa.us> writes: > GREATEST/LEAST aren't in the spec, so there's not much help there. > > Except ... if they ever do get added to the spec, what do you think > the spec will say? The odds it'd contradict Oracle seem about nil. Fwiw even in the min/max/sum case the spec is moving away from having aggregates ignore NULL values. You now get a warning in Oracle if your aggregate includes any NULL inputs. Actually I think it's not exactly a warning but a weird kind of non-fatal error. You still get your result set but the driver treats it as an error which has to be explicitly handled to see the results. I'm not entirely clear on what's going on though. I know that there was some version of their sql command-line tool which *didn't* handle it and therefore treated it as a fatal error and that pissed off a lot of people. I think it now prints the warning and the result set. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
Gregory Stark wrote: > "Tom Lane" <tgl@sss.pgh.pa.us> writes: > > > GREATEST/LEAST aren't in the spec, so there's not much help there. > > > > Except ... if they ever do get added to the spec, what do you think > > the spec will say? The odds it'd contradict Oracle seem about nil. > > Fwiw even in the min/max/sum case the spec is moving away from having > aggregates ignore NULL values. You now get a warning in Oracle if your > aggregate includes any NULL inputs. How does Oracle's new behavior relate to the standard moving? -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Gregory Stark <stark@enterprisedb.com> writes:
> "Bruno Wolff III" <bruno@wolff.to> writes:
>> Also what value should I have used in a coalesce to guaranty still getting
>> the maximum?
> I would expect coalesce(coalesce(greatest(a,b),a),b) to do what you want. It's
> not terribly legible though and if a and b are subselects I would worry a
> little about the optimizer rerunning them unnecessarily.
That does not work correctly for volatile functions, and it does not
scale to more than two inputs either -- you'd get the first nonnull
not the largest one.
regards, tom lane
Gregory Stark <stark@enterprisedb.com> writes:
> Fwiw even in the min/max/sum case the spec is moving away from having
> aggregates ignore NULL values. You now get a warning in Oracle if your
> aggregate includes any NULL inputs.
I don't think there's any "moving" involved; as far back as SQL92 the
definition of aggregates (except COUNT) said
b) Otherwise, let TX be the single-column table that is the
result of applying the <value expression> to each row of T
and eliminating null values. If one or more null values are
eliminated, then a completion condition is raised: warning-
null value eliminated in set function.
We pretty much ignore the spec's concept of non-error completion
conditions, but it sounds like Oracle tries to support it.
Anyway, there's no doubt that we can point to the behavior of MAX/MIN
as defense for what we made GREATEST/LEAST do, so I'm inclined to leave
their behavior alone, at least until such time as they're actually
standardized. But a note in the manual pointing out the difference from
Oracle seems in order.
BTW, it seems that mysql follows Oracle on this:
mysql> select greatest(1,4,8);
+-----------------+
| greatest(1,4,8) |
+-----------------+
| 8 |
+-----------------+
1 row in set (0.00 sec)
mysql> select greatest(1,4,null);
+--------------------+
| greatest(1,4,null) |
+--------------------+
| NULL |
+--------------------+
1 row in set (0.00 sec)
and if you want a laugh:
mysql> select greatest (1,4,8);
ERROR 1305 (42000): FUNCTION test.greatest does not exist
regards, tom lane
"Bruce Momjian" <bruce@momjian.us> writes: >> Fwiw even in the min/max/sum case the spec is moving away from having >> aggregates ignore NULL values. You now get a warning in Oracle if your >> aggregate includes any NULL inputs. > > How does Oracle's new behavior relate to the standard moving? Sorry I noticed that editing error only after I sent it. I should have changed that to say Oracle was moving in that direction. There's nothing of the sort in SQL2003 that I can find. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
Tom Lane wrote: > Gregory Stark <stark@enterprisedb.com> writes: > > Fwiw even in the min/max/sum case the spec is moving away from having > > aggregates ignore NULL values. You now get a warning in Oracle if your > > aggregate includes any NULL inputs. > > I don't think there's any "moving" involved; as far back as SQL92 the > definition of aggregates (except COUNT) said > > b) Otherwise, let TX be the single-column table that is the > result of applying the <value expression> to each row of T > and eliminating null values. If one or more null values are > eliminated, then a completion condition is raised: warning- > null value eliminated in set function. > > We pretty much ignore the spec's concept of non-error completion > conditions, but it sounds like Oracle tries to support it. > > Anyway, there's no doubt that we can point to the behavior of MAX/MIN > as defense for what we made GREATEST/LEAST do, so I'm inclined to leave > their behavior alone, at least until such time as they're actually > standardized. But a note in the manual pointing out the difference from > Oracle seems in order. Agreed that we are good by following min/max. Not sure about a mention in the docs that we are different from Oracle helps. Do we mention other differences? I see us doing that only for PL/Psql. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
"Tom Lane" <tgl@sss.pgh.pa.us> writes: > Gregory Stark <stark@enterprisedb.com> writes: >> "Bruno Wolff III" <bruno@wolff.to> writes: >>> Also what value should I have used in a coalesce to guaranty still getting >>> the maximum? > >> I would expect coalesce(coalesce(greatest(a,b),a),b) to do what you want. It's >> not terribly legible though and if a and b are subselects I would worry a >> little about the optimizer rerunning them unnecessarily. > > That does not work correctly for volatile functions, and it does not > scale to more than two inputs either -- you'd get the first nonnull > not the largest one. Both true. There is another option too if you have a minimum value below which you know no values will exist: SELECT nullif(greatest(coalesce(a,-1), coalesce(b,-1), coalesce(c,-1)), -1) Does Oracle even have nullif() these days? If not you would have to use decode() but I think it suffers from the same problem of repeated evaluation. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
Bruce Momjian <bruce@momjian.us> writes:
> Tom Lane wrote:
>> Anyway, there's no doubt that we can point to the behavior of MAX/MIN
>> as defense for what we made GREATEST/LEAST do, so I'm inclined to leave
>> their behavior alone, at least until such time as they're actually
>> standardized. But a note in the manual pointing out the difference from
>> Oracle seems in order.
> Agreed that we are good by following min/max. Not sure about a mention
> in the docs that we are different from Oracle helps. Do we mention
> other differences? I see us doing that only for PL/Psql.
We tend not to mention Oracle by name, but there are various places
saying that we do X while "some other databases" do Y. In view of the
mysql behavior I think I'd use that same wording here.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > Tom Lane wrote: > >> Anyway, there's no doubt that we can point to the behavior of MAX/MIN > >> as defense for what we made GREATEST/LEAST do, so I'm inclined to leave > >> their behavior alone, at least until such time as they're actually > >> standardized. But a note in the manual pointing out the difference from > >> Oracle seems in order. > > > Agreed that we are good by following min/max. Not sure about a mention > > in the docs that we are different from Oracle helps. Do we mention > > other differences? I see us doing that only for PL/Psql. > > We tend not to mention Oracle by name, but there are various places > saying that we do X while "some other databases" do Y. In view of the > mysql behavior I think I'd use that same wording here. OK, I like the generic approach. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Tom Lane wrote: > Anyway, there's no doubt that we can point to the behavior of MAX/MIN > as defense for what we made GREATEST/LEAST do, so I'm inclined to > leave > their behavior alone, at least until such time as they're actually > standardized. I don't think I buy this - MIN and MAX are aggregates, GREATEST is just a function, yes? There would seem to be a very strong analogy with SUM and the addition function, yet 2 + NULL is NULL, while SUM ignores NULLs. (Not in front of a console, sorry if I'm mistaken.) - John Burger MITRE
On Sat, Jun 30, 2007 at 09:29:23 +0200, Pavel Stehule <pavel.stehule@gmail.com> wrote: > Hello, > > I have not Oracle, so I cannot test it, but PostgreSQL implementation > respect Oracle: > > http://archives.postgresql.org/pgsql-patches/2005-06/msg00431.php Maybe that reference was for an earlier version of Oracle and the definition changed at some point? I only have access to version 9 and greatest and lest are strict there.
> > We pretty much ignore the spec's concept of non-error completion > conditions, but it sounds like Oracle tries to support it. > > Anyway, there's no doubt that we can point to the behavior of MAX/MIN > as defense for what we made GREATEST/LEAST do, so I'm inclined to leave > their behavior alone, at least until such time as they're actually > standardized. But a note in the manual pointing out the difference from > Oracle seems in order. > > BTW, it seems that mysql follows Oracle on this: > > mysql> select greatest(1,4,8); > +-----------------+ > | greatest(1,4,8) | > +-----------------+ > | 8 | > +-----------------+ > 1 row in set (0.00 sec) > > mysql> select greatest(1,4,null); > +--------------------+ > | greatest(1,4,null) | > +--------------------+ > | NULL | > +--------------------+ > 1 row in set (0.00 sec) > > and if you want a laugh: > > mysql> select greatest (1,4,8); > ERROR 1305 (42000): FUNCTION test.greatest does not exist > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > Hello some notes about it http://bugs.mysql.com/bug.php?id=12791 http://bugs.mysql.com/bug.php?id=15610 Regards Pavel
> Maybe that reference was for an earlier version of Oracle and the definition > changed at some point? I only have access to version 9 and greatest and > lest are strict there. > I am installing OracleXE and I'll test it. Pavel
At risk of putting my foot in my mouth again, greatest() returns null if one
or more expressions are null for Oracle enterprise 9.2.0.7 and 10.2.0.3.
The docs for greatest() don't talk of NULL:
SQL> select greatest(1,2,null,3) from dual;
GREATEST(1,2,NULL,3)
--------------------
SQL> select greatest(1,2,3) from dual;
GREATEST(1,2,3)
---------------
3
SQL> select version from v$instance;
VERSION
---------------------------------------------------
9.2.0.7.0
SQL> select greatest(1,2,null,3) from dual;
GREATEST(1,2,NULL,3)
--------------------
SQL> select greatest(1,2,3) from dual;
GREATEST(1,2,3)
---------------
3
SQL> select version from v$instance;
VERSION
---------------------------------------------------
10.2.0.3.0
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Pavel Stehule
> Sent: Saturday, June 30, 2007 10:37 AM
> To: Bruno Wolff III; Pavel Stehule; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] greatest/least semantics different between oracle
> and postgres
>
> > Maybe that reference was for an earlier version of Oracle and the
> definition
> > changed at some point? I only have access to version 9 and greatest and
> > lest are strict there.
> >
>
> I am installing OracleXE and I'll test it.
>
> Pavel
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
> -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] On Behalf Of Pavel Stehule > Sent: Saturday, June 30, 2007 10:37 AM > To: Bruno Wolff III; Pavel Stehule; pgsql-general@postgresql.org > Subject: Re: [GENERAL] greatest/least semantics different between oracle > and postgres > > > Maybe that reference was for an earlier version of Oracle and the > definition > > changed at some point? I only have access to version 9 and greatest and > > lest are strict there. > > > > I am installing OracleXE and I'll test it. > > Pavel > At risk of putting my foot in my mouth again, greatest() returns null if one or more expressions are null for Oracle enterprise 9.2.0.7 and 10.2.0.3. The docs for greatest() don't talk of NULL: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions060. htm#SQLRF00645 There are metalink documents that do seem to make it clear greatest/least are defined to return null if one or more expressions has a null. (see doc 207279.999 for example) SQL> select greatest(1,2,null,3) from dual; GREATEST(1,2,NULL,3) -------------------- SQL> select greatest(1,2,3) from dual; GREATEST(1,2,3) --------------- 3 SQL> select version from v$instance; VERSION --------------------------------------------------- 9.2.0.7.0 SQL> select greatest(1,2,null,3) from dual; GREATEST(1,2,NULL,3) -------------------- SQL> select greatest(1,2,3) from dual; GREATEST(1,2,3) --------------- 3 SQL> select version from v$instance; VERSION --------------------------------------------------- 10.2.0.3.0
You are correct. PostgreSQL implementation isn't compatible with Oracle. It's question if the behave can be changed now. Pavel 2007/6/30, paul rivers <rivers.paul@gmail.com>: > > > -----Original Message----- > > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > > owner@postgresql.org] On Behalf Of Pavel Stehule > > Sent: Saturday, June 30, 2007 10:37 AM > > To: Bruno Wolff III; Pavel Stehule; pgsql-general@postgresql.org > > Subject: Re: [GENERAL] greatest/least semantics different between oracle > > and postgres > > > > > Maybe that reference was for an earlier version of Oracle and the > > definition > > > changed at some point? I only have access to version 9 and greatest and > > > lest are strict there. > > > > > > > I am installing OracleXE and I'll test it. > > > > Pavel > > > > At risk of putting my foot in my mouth again, greatest() returns null if one > or more expressions are null for Oracle enterprise 9.2.0.7 and 10.2.0.3. > > The docs for greatest() don't talk of NULL: > > http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions060. > htm#SQLRF00645 > > There are metalink documents that do seem to make it clear greatest/least > are defined to return null if one or more expressions has a null. (see doc > 207279.999 for example) > > SQL> select greatest(1,2,null,3) from dual; > > GREATEST(1,2,NULL,3) > -------------------- > > > SQL> select greatest(1,2,3) from dual; > > GREATEST(1,2,3) > --------------- > 3 > > SQL> select version from v$instance; > > VERSION > --------------------------------------------------- > 9.2.0.7.0 > > > > > SQL> select greatest(1,2,null,3) from dual; > > GREATEST(1,2,NULL,3) > -------------------- > > > SQL> select greatest(1,2,3) from dual; > > GREATEST(1,2,3) > --------------- > 3 > > SQL> select version from v$instance; > > VERSION > --------------------------------------------------- > 10.2.0.3.0 > > > >
On Saturday 30 June 2007 14:13, paul rivers wrote:
> > -----Original Message-----
> > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> > owner@postgresql.org] On Behalf Of Pavel Stehule
> > Sent: Saturday, June 30, 2007 10:37 AM
> > To: Bruno Wolff III; Pavel Stehule; pgsql-general@postgresql.org
> > Subject: Re: [GENERAL] greatest/least semantics different between oracle
> > and postgres
> >
> > > Maybe that reference was for an earlier version of Oracle and the
> >
> > definition
> >
> > > changed at some point? I only have access to version 9 and greatest and
> > > lest are strict there.
> >
> > I am installing OracleXE and I'll test it.
> >
> > Pavel
>
> At risk of putting my foot in my mouth again, greatest() returns null if
> one or more expressions are null for Oracle enterprise 9.2.0.7 and
> 10.2.0.3.
>
<snip examples>
Confirmed on Oracle 8.1.7.4.0 as well, so if it changed it was a ways back.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL