Re: Examples of SQL anomalies?

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Thu, 03 Jul 2008 11:19:41 -0300
Message-ID: <486ce002$0$4032$9a566e8b_at_news.aliant.net>

David Cressey wrote:

> "Brian Selzer" <brian_at_selzer-software.com> wrote in message
> news:nFD9k.5753$LG4.2422_at_nlpi065.nbdc.sbc.com...
> 

>>"-CELKO-" <jcelko212_at_earthlink.net> wrote in message
>>news:f219a6bd-9d8e-4cfe-9d60-ce9dcaeff16d_at_z66g2000hsc.googlegroups.com...
>>
>>>>>The question is, if these issues are due to the SQL specification or
>>>>>simply due to a problem in a specific SQL product. Or could it be,

> 
> that
> 


>>>>>the definition is not precise enough in some points, so that database

>>>>>vendors implemented it differently? <<

>>>

>>>Nope, it is the specs.  All aggregate (set) functions begin by

>>>removing the NULLs from their parameter set, then if there is a

>>>DISTINCT option on the parameter, they remove redundant duplicates and

>>>finally do the operation (MIN, MAX, AVG, SUM, COUNT on what is left.

>>>Since an empty set has no elements upon which to apply an operation,

>>>SQL returns a NULL (okay, it should be an "undefined" if we were

>>>mathematically correct).
>>>
>>
>>MIN, MAX and AVG are meaningless when applied to an empty bag, but it seems

Bullshit! The identity element for min is the largest representable value. The identity element for max is the smallest representable element.

AVG, on the other hand, is indeterminate because it is simply a shorthand for sum/count. Received on Thu Jul 03 2008 - 16:19:41 CEST