Re: greatest/least semantics different between oracle and postgres

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(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)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