greatest/least semantics different between oracle and postgres

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

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

"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