# Re: Examples of SQL anomalies?

Date: Sat, 05 Jul 2008 10:47:43 -0300

Message-ID: <486f7b85$0$4047$9a566e8b_at_news.aliant.net>

David Cressey wrote:

> "David BL" <davidbl_at_iinet.net.au> wrote in message

*> news:cfb587f5-ff58-48c7-ad1d-53540c7b8d7d_at_e39g2000hsf.googlegroups.com...
**>
*

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

*>*

*>*

> This is degenerating into yet another philosophical exercise on the meaning

*> of NULL.*

*>*

*> The only way to make sense out of all this is as follows:*

*>*

*> Consider a column the might actually contain NULLS to be (part of) a*

*> materialized OUTER JOIN. Decompose the tables back to the point where all*

*> columns of all tables can be required data. (I guess, from prior*

*> discussions, that this is 6th normal form. Someone could check this for*

*> me). Compute how the column with missing weights appears in this equivalent*

*> 6th normal form representation. Figure out what the SUM aggregate does (or*

*> alternatively, figure out what it ought to do) with the data in 6th normal*

*> form. Cause the SUM aggregate to do the same thing with the table in its*

*> original form.*

*>*

*> I haven't done the computation, but I'm guessing that you'll end up with*

*> exactly the same behavior you get from the present SQL SUM aggregate.*

Actuall, no, you won't. If you do not join all of the attributes in the original table, you are really asking a different question. If you do and some of the values are unknown, the total is unknown, but that is not the result SQL gives.

> Missing data, which shows up as NULL values in the original form (original

*> only in the sense that that's where we started the discussion) will show up
**> in the form of missing rows in the 6th normal form equivalent.
**>
**> As far as getting "right" answers when some rows are missing, that's beyond
**> the province of the DBMS.
*

One has to know what one is asking. Received on Sat Jul 05 2008 - 15:47:43 CEST