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