# Re: Examples of SQL anomalies?

Date: Sat, 5 Jul 2008 09:46:31 -0700 (PDT)

Message-ID: <a096efd7-9cf0-461c-b8e7-a91a5c5561e2_at_b1g2000hsg.googlegroups.com>

On Jul 4, 8:15 pm, David BL <davi..._at_iinet.net.au> wrote:

> On Jul 5, 8:49 am, Marshall <marshall.spi..._at_gmail.com> wrote:

*>
**> > What can be meaningfully asked is determined by the schema.
**>
**> > If the schema specifies that the weight attribute is nullable,
**> > then the question of how much a shipment weighs in total
**> > is a question that cannot be asked.
**>
**> IMO aggregate functions are only defined on well defined sets and only
**> give well defined answers. SUM should never return NULL.
**> Nevertheless it is meaningful to calculate SUM on a nullable weight
**> attribute. Rather than upset the mathematical simplicity of aggregate
**> functions one can easily make sense of the result by correctly
**> interpreting the intensional definition of the set being aggregated.
**> For example in this case it is the set of *known* weights. It is
**> absurd to not allow this to be calculated, by returning NULL.
*

I am inclined to think that it is just NULL that is absurd, given that better abstractions exist, but I hear you.

The problem (at least, ONE of the problems) in this case is that SQL's SUM() is *not* the aggregated form of SQL's + operator. This is the cause of Bob's inequality:

** SUM(A) + SUM(B) != SUM(A+B)
**
The + operator has NULL as a fixpoint, and the SUM operator
doesn't! Their semantics diverge.

Marshall Received on Sat Jul 05 2008 - 18:46:31 CEST