Re: Examples of SQL anomalies?

>> 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, <<

Which COUNT?; we have two different functions in SQL that look too much alike:

1. COUNT([DISTINCT] <exp>) = inside the set, throws away NULLs, optionally converts a bag to a set. If he has nothing to count, he is not defined and we use a NULL. See another posting to Gene about "no cats" versus "zero cats" and the conceptual problems that mankind had before Cantor.
2. COUNT(*) = works at the set level. This is not counting; it is cardinality. A better notation from a pure mathematical viewpoint would have been "CARD (<table expression>)" in the same style as "[NOT] EXISTS (<table expression>)". If the set is empty, then the cardinality is zero by definition.

>> .. and similarly, SUM should return 0. SUM should only return NULL if one of the values to be summed is NULL. <<

You can fake it with a little code:

CASE WHEN COUNT(*) = COUNT(<exp>) --no nulls in set

     THEN SUM(<exp>) -- usual behavior
     ELSE NULL END -- weird behavior

SQL requires a warning be issued when a NULL is dropped from an aggregate result. You can test for it in your code. The bad news is that when you use a cursor, when that warning comes is implementation defined. It can be done at DECLARE CURSOR, OPEN, the first FETCH or the FETCH that gets the grouping with the NULL dropped from it. Received on Mon Jun 30 2008 - 18:08:36 CEST