Thread: Re: [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@kix.fsv.cvut.cz> writes:
> 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.

> 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


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

From
Andrew Dunstan
Date:

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)?
>
>  
>

My initial reaction was to say "not strict", and since that's apparently 
what Oracle does that reinforces it for me.

cheers

andrew


On 6/24/05, Tom Lane <tgl@sss.pgh.pa.us> 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)?
>

[snip]

>
> > 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
>

My $0.02: I'd prefer the non-strict version.

--
Mike Rylander
mrylander@gmail.com
GPLS -- PINES Development
Database Developer
http://open-ils.org


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

From
Robert Treat
Date:
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@kix.fsv.cvut.cz> writes:
> > 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.
> 
> > 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



On Fri, Jun 24, 2005 at 09:21:25AM -0400, 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)?

I'd say non-strict unless SQL:2003 says different.

Cheers,
D
-- 
David Fetter david@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!