Re: [PATCHES] Function's LEAST, GREATEST and DECODE

On Fri, 2005-06-24 at 09:21, Tom Lane wrote:
> [ moving to -hackers for a wider audience ]
>
> Today's issue: should the GREATEST/LEAST functions be strict (return
> null if any input is null) or not (return null only if all inputs are
> null, else return the largest/smallest of the non-null inputs)?
>
> Pavel Stehule <stehule(at)kix(dot)fsv(dot)cvut(dot)cz> writes:
> > On Thu, 23 Jun 2005, Tom Lane wrote:
> >> Pavel Stehule <stehule(at)kix(dot)fsv(dot)cvut(dot)cz> writes:
> >> + /* If any argument is null, then result is null (for GREATEST and LEAST)*/
> >>
> >> Are you sure about that? The only reference I could find says that
> >> these functions are not strict in Oracle:
> >>
> >> http://download-east.oracle.com/otn_hosted_doc/rdb/pdf/sql_ref_v71_vol1.pdf
> >> on page 2-185:
> >>
> >>> The NULL keyword can appear in the list but is ignored. However, not all
> >>> value expressions can be specified as NULL. That is, a non-NULL value
> >>> expression must be in the list so that the data type for the expression
> >>> can be determined.
> >>> The GREATEST and LEAST functions can result in NULL only if at run time
> >>> all value expressions result in NULL.
> >>
> >> The strict interpretation is mathematically cleaner, no doubt, but
> >> offhand it seems less useful.
> >>
>
> > I know it, But when moustly PostgreSQL function is strict I desided so
> > greatest and least will be strict. There is two analogy:
>
> > one, normal comparing which implicate strinct
> > aggregate function which ignore NULL.
>
> > Tom I don't know, what is better. Maybe Oracle,
>
> > because
>
> > least(nullif(col2, +max), nullif(col2, +max)) isn't really readable, but
> > it's "precedens" for PostgreSQL. I selected more conservative solution,
> > but my patches are only start points for discussion (really) :).
>
> > Please, if You think, so Oracle way is good, correct it.
>
> I'm still favoring non-strict but it deserves more than two votes.
> Anybody else have an opinion?
>

If the sql spec has nothing to say on it, then we should probably
support Oracles take, since this seems like an Oracleism anyway.

Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL