Re: Examples of SQL anomalies?

From: -CELKO- <jcelko212_at_earthlink.net>
Date: Fri, 27 Jun 2008 11:23:56 -0700 (PDT)
Message-ID: <f219a6bd-9d8e-4cfe-9d60-ce9dcaeff16d@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).

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 Fri Jun 27 2008 - 13:23:56 CDT

Original text of this message