Re: Examples of SQL anomalies?

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.)

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.

Marshall Received on Sat Jul 05 2008 - 18:25:20 CEST