Re: greatest/least semantics different between oracle and postgres

Gregory Stark <stark(at)enterprisedb(dot)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