Thread: greatest/least semantics different between oracle and postgres

greatest/least semantics different between oracle and postgres

From
Bruno Wolff III
Date:
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.

Re: greatest/least semantics different between oracle and postgres

From
"Andrej Ricnik-Bay"
Date:
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

Re: greatest/least semantics different between oracle and postgres

From
Tom Lane
Date:
"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

Re: greatest/least semantics different between oracle and postgres

From
"Andrej Ricnik-Bay"
Date:
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

Re: greatest/least semantics different between oracle and postgres

From
Bruno Wolff III
Date:
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.

Re: greatest/least semantics different between oracle and postgres

From
"paul rivers"
Date:
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


Re: greatest/least semantics different between oracle and postgres

From
Tom Lane
Date:
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

Re: greatest/least semantics different between oracle and postgres

From
Tom Lane
Date:
"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

Re: greatest/least semantics different between oracle and postgres

From
"paul rivers"
Date:

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




Re: greatest/least semantics different between oracle and postgres

From
"Pavel Stehule"
Date:
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
>

Re: greatest/least semantics different between oracle and postgres

From
Gregory Stark
Date:
"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


Re: greatest/least semantics different between oracle and postgres

From
Gregory Stark
Date:
"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


Re: greatest/least semantics different between oracle and postgres

From
Bruce Momjian
Date:
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. +

Re: greatest/least semantics different between oracle and postgres

From
Tom Lane
Date:
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

Re: greatest/least semantics different between oracle and postgres

From
Tom Lane
Date:
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

Re: greatest/least semantics different between oracle and postgres

From
Gregory Stark
Date:
"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


Re: greatest/least semantics different between oracle and postgres

From
Bruce Momjian
Date:
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. +

Re: greatest/least semantics different between oracle and postgres

From
Gregory Stark
Date:
"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


Re: greatest/least semantics different between oracle and postgres

From
Tom Lane
Date:
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

Re: greatest/least semantics different between oracle and postgres

From
Bruce Momjian
Date:
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. +

Re: greatest/least semantics different between oracle and postgres

From
"John D. Burger"
Date:
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

Re: greatest/least semantics different between oracle and postgres

From
Bruno Wolff III
Date:
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.

Re: greatest/least semantics different between oracle and postgres

From
"Pavel Stehule"
Date:
>
> 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

Re: greatest/least semantics different between oracle and postgres

From
"Pavel Stehule"
Date:
> 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

Re: greatest/least semantics different between oracle and postgres

From
"paul rivers"
Date:
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


Re: greatest/least semantics different between oracle and postgres

From
"paul rivers"
Date:
> -----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




Re: greatest/least semantics different between oracle and postgres

From
"Pavel Stehule"
Date:
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
>
>
>
>

Re: greatest/least semantics different between oracle and postgres

From
Robert Treat
Date:
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