[ 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