Re: Examples of SQL anomalies?
Date: Thu, 3 Jul 2008 11:59:50 -0400
"David Cressey" <cressey73_at_verizon.net> wrote in message
> "Brian Selzer" <brian_at_selzer-software.com> wrote in message
>> "-CELKO-" <jcelko212_at_earthlink.net> wrote in message
>> >>> 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,
>> >>> 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
>> to me that COUNT should always return 0 when the bag is empty, and
>> similarly, SUM should return 0. SUM should only return NULL if one of
>> values to be summed is NULL.
> By definition, none of the values to be summed are NULL.
OK. OK. Yes, I know. NULL is not a value. I guess to be precise, I should have said "if the cardinality of the set of rows targeted by the query is greater than the number of values to be summed, then SUM should return NULL," but I think that would have caused more confusion. Bottom line: if not all of the amounts are known, then the total amount is suspect.
> I agree about COUNT and SUM on an empty bag. BTW, the DISTINCT keyword
> be used with aggregates to obtain a projection: select count (distinct
> zip_code) from employees .
Received on Thu Jul 03 2008 - 17:59:50 CEST