Re: Examples of SQL anomalies?
Date: Sat, 05 Jul 2008 12:20:59 GMT
Message-ID: <LGJbk.201$iU.196_at_trndny02>
"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.
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.