Обсуждение: Re: [PATCHES] Function's LEAST, GREATEST and DECODE (Oracle vararg polymorphic functions)
Re: [PATCHES] Function's LEAST, GREATEST and DECODE (Oracle vararg polymorphic functions)
От
"John Hansen"
Дата:
I'd vote that these functions should follow the semantics of the <, and > operators. (NULL < x) is NULL; ... John > -----Original Message----- > From: pgsql-hackers-owner@postgresql.org > [mailto:pgsql-hackers-owner@postgresql.org] 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 <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_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 > >
"John Hansen" <john@geeknet.com.au> 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