Thread: Re: [PATCHES] Function's LEAST, GREATEST and DECODE (Oracle vararg polymorphic functions)
Re: [PATCHES] Function's LEAST, GREATEST and DECODE (Oracle vararg polymorphic functions)
From
Tom Lane
Date:
[ 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
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
Re: [PATCHES] Function's LEAST, GREATEST and DECODE (Oracle vararg polymorphic functions)
From
Mike Rylander
Date:
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
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
Re: [PATCHES] Function's LEAST, GREATEST and DECODE (Oracle vararg polymorphic functions)
From
David Fetter
Date:
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!