# Re: Examples of SQL anomalies?

From: Brian Selzer <brian_at_selzer-software.com>
Date: Mon, 30 Jun 2008 13:31:03 -0400
Message-ID: <rL8ak.30856\$ZE5.3248_at_nlpi061.nbdc.sbc.com>

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

Well, the OP wanted examples of SQL anomalies, and you've just confirmed a big one.

If you have a bag that can contain peaches, but doesn't, then the answer to the question "How many peaches are in the bag?" is clearly zero. If you are asked by the accountant, "How much were we billed by AT&T this month?" but AT&T didn't send a bill, then the answer is clearly zero. That SQL's COUNT and SUM are something other than these common sense usages exemplifies their anomalous nature.

>
> 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 - 19:31:03 CEST

Original text of this message