Re: Examples of SQL anomalies?

"Marshall" <marshall.spight_at_gmail.com> wrote in message news:ee7d4b1f-4e31-456b-9273-f27daeeeebd5_at_2g2000hsn.googlegroups.com...
> On Jul 4, 8:25 pm, "Brian Selzer" <br..._at_selzer-software.com> wrote:
>> "Marshall" <marshall.spi..._at_gmail.com> wrote in message
>>
>> news:6ad51b62-e66a-4daa-b21c-c361fd6b22f8_at_8g2000hse.googlegroups.com...
>>
>> > 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