# Re: Examples of SQL anomalies?

Date: Sat, 28 Jun 2008 23:51:47 -0400

Message-ID: <nFD9k.5753$LG4.2422@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 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 the values to be summed is NULL.

*> In SQL as in Set Theory, equality (=) and grouping are not the same;*

*> the SUM() and the + are not the same. They are for different levels*

*> of abstraction. It makes senses after your first course with*

*> transfinite numbers -- the cardinality of Aleph Null is not the same*

*> as counting all the integers one by one, etc. *

Received on Sat Jun 28 2008 - 22:51:47 CDT