Re: Examples of SQL anomalies?

From: Brian Selzer <>
Date: Sat, 5 Jul 2008 14:05:46 -0400
Message-ID: <%JObk.14348$>

"Marshall" <> wrote in message
> On Jul 4, 8:25 pm, "Brian Selzer" <> wrote:
>> "Marshall" <> wrote in message
>> > 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.
>> I would agree except that in the exceptional case where all of the
>> weights
>> have been supplied, the question can be answered. That's why I would
>> return
>> NULL for SUM except in such execptional cases. That way you get an
>> indeterminate result exactly when the result cannot be determined.
> Well. Strictly speaking, what can be asked and what the answer
> looks like, as well as the semantics for each, depend on the
> schema (and its semantics) and the system it's embedded in.
> And both are static constructs; they do not depend on the
> rows of the table at the time.
> With SQL's semantics and a nullable weight column, the question
> of total weight cannot be asked. What can be asked is, what
> is the total of the non-null weights. In the case where all rows
> have non-null weights, the answer will happen to correspond to
> the answer you would get if the schema had non-nullable
> weights. (In which case, asking total weight *would* be possible.)

I think I originally said that SQL's semantics for SUM was screwed up--or something to that effect.

> With your proposed SUM() semantics, we have yet another
> situation, one in which the question of total weight can be
> asked, but the answer will sometimes be NULL.

Yes. The output would be NULL whenever at least one of the inputs is NULL. This is in line with the idea that NULL should only ever indicate "there should be a value here but it hasn't been supplied." It obviously doesn't line up with the idea that NULL can indicate "there shouldn't be a value here." Received on Sat Jul 05 2008 - 20:05:46 CEST

Original text of this message