Re: Function's LEAST, GREATEST and DECODE (Oracle vararg - Mailing list pgsql-patches

From Pavel Stehule
Subject Re: Function's LEAST, GREATEST and DECODE (Oracle vararg
Date
Msg-id Pine.LNX.4.44.0506240712360.22636-100000@kix.fsv.cvut.cz
Whole thread Raw
In response to Re: Function's LEAST, GREATEST and DECODE (Oracle vararg polymorphic functions)  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-patches
On Thu, 23 Jun 2005, Tom Lane wrote:

> Pavel Stehule <stehule@kix.fsv.cvut.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.

what I have to chose? For compatibility there isn't biggeer changes. Only

//if (*isNull)
//  return value;
if (result && *isNull == false)
{
   locfcinfo.arg[0] = result;
   ...
}

-----
foreach(arg, ..)
{
    if (IsA(e, Const))
        if (!((Const *) e)->constisnull)
            newargs = lappend(newargs, e);
}
if (newargs == NULL)
    return (Node *) makeNullConst(varargexpr->..);

-----

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.

Best regards
Pavel


pgsql-patches by date:

Previous
From: "Luke Lonergan"
Date:
Subject: Re: COPY FROM performance improvements
Next
From: Bruce Momjian
Date:
Subject: Re: hash index work