Re: [PATCHES] Function's LEAST, GREATEST and DECODE (Oracle vararg polymorphic functions)

I'd vote that these functions should follow the semantics of the <, and
> operators.

(NULL < x) is NULL;

... John

> -----Original Message-----
> From: pgsql-hackers-owner(at)postgresql(dot)org
> [mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Tom Lane
> Sent: Friday, June 24, 2005 11:21 PM
> To: Pavel Stehule
> Cc: pgsql-hackers(at)postgresql(dot)org
> Subject: Re: [HACKERS] [PATCHES] Function's LEAST, GREATEST
> and DECODE (Oracle vararg polymorphic functions)
>
> [ 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_vo
> >> l1.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?
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
>