Re: greatest/least semantics different between oracle and postgres

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Pavel Stehule
> Sent: Saturday, June 30, 2007 10:37 AM
> To: Bruno Wolff III; Pavel Stehule; pgsql-general(at)postgresql(dot)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