Re: greatest/least semantics different between oracle and postgres

[Bruce Momjian <bruce(at)momjian(dot)us>]

Tom Lane wrote:
> 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.

Agreed that we are good by following min/max.  Not sure about a mention
in the docs that we are different from Oracle helps.  Do we mention
other differences?  I see us doing that only for PL/Psql.

-- 
  Bruce Momjian  <bruce(at)momjian(dot)us>          http://momjian.us
  EnterpriseDB                               http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +