Re: greatest/least semantics different between oracle and postgres

On Sat, Jun 30, 2007 at 00:15:42 -0400,
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Andrej Ricnik-Bay" <andrej(dot)groups(at)gmail(dot)com> writes:
> > On 6/30/07, Bruno Wolff III <bruno(at)wolff(dot)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.