Re: [HACKERS] [PATCHES] Function's LEAST, GREATEST and DECODE (Oracle vararg polymorphic functions)
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 [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] Function's LEAST, GREATEST and DECODE (Oracle vararg polymorphic functions)
"John Hansen" <[EMAIL PROTECTED]> writes: > I'd vote that these functions should follow the semantics of the <, and >> operators. > (NULL < x) is NULL; Well, that's a fair analogy, but then so is the analogy to MAX/MIN ... so it seems about a wash to me. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [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: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane > Sent: Friday, June 24, 2005 11:21 PM > To: Pavel Stehule > Cc: pgsql-hackers@postgresql.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 <[EMAIL PROTECTED]> writes: > > On Thu, 23 Jun 2005, Tom Lane wrote: > >> Pavel Stehule <[EMAIL PROTECTED]> 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 > > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] [PATCHES] Function's LEAST, GREATEST and DECODE (Oracle vararg polymorphic functions)
On 6/24/05, Tom Lane <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
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 <[EMAIL PROTECTED]> writes: > On Thu, 23 Jun 2005, Tom Lane wrote: >> Pavel Stehule <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
