Re: greatest/least semantics different between oracle and postgres

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