Re: Examples of SQL anomalies?

From: David Cressey <cressey73_at_verizon.net>
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. Received on Sat Jul 05 2008 - 14:20:59 CEST

Original text of this message