Re: Examples of SQL anomalies?

From: Brian Selzer <brian_at_selzer-software.com>
Date: Wed, 9 Jul 2008 17:42:52 -0400
Message-ID: <whadk.31863$ZE5.6401_at_nlpi061.nbdc.sbc.com>


"David Cressey" <cressey73_at_verizon.net> wrote in message news:oe9dk.1109$Ae3.832_at_trnddc05...
>
> "Marshall" <marshall.spight_at_gmail.com> wrote in message
> news:19040b82-0130-479e-ab80-dc1f1597ac02_at_56g2000hsm.googlegroups.com...
>> On Jul 7, 1:21 pm, JOG <j..._at_cs.nott.ac.uk> wrote:
>> > On Jul 5, 1:09 pm, "David Cressey" <cresse..._at_verizon.net> 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.
>> >
>> > > > Marshall
>> >
>> > > Bingo!
>> >
>> > However, one /can/ validly ask "please give me the minimum the
>> > shipment weighs" and this may still be very useful.
>>
>> Well, that's assuming that all the shipments have a positive
>> weight. What if we might ship helium baloons? Then you
>> can't set the minimum.
>>
>> OK I was just having some fun there.
>>
>>
>> > This is of course
>> > not a defence of null markers (obviously not. its me), but rather just
>> > a precaution against ruling out all questions of irregular data in
>> > blanket fashion. Our aim should be to provide frameworks that allows
>> > us to ask these questions with syntactic correctness /and/ as
>> > parsimoniously as possible.
>>
>> Sure.
>>
>> My idea of the phrasing of the sum() over a nullable weight
>> is "what is the sum of the weights of the items in the shipment
>> for which the weight has been entered into the db?"
>>
>>
> That is, IIUYC , the sum of all the wieghts, as far as the db knows them,
> right?
>
> This amounts to the "open world assumption" with regard to weights of
> items.
>
> If one is going to adopt a "closed world assumption" with regard to
> weights
> of entered items, then it would seem to me that the weights column would
> have to be declared not nullable. Meaning that, if an item is entered
> nto
> a row, the weight column may not be left null.
>
> In the case of not nullable columns, this entire subthread is moot.
>
> Brian seems to be willing to bounce back and forth between OWA and CWA,
> without making any entries in the schema to reflect which assumption is
> operative. Or maybe it's just a disconnect between Brian and SQL.

I don't think that's right.

CWA: "If there ain't no row, then it ain't so."

But there is a row:

Package {{PackageNumber:12341, Weight:NULL}}

Translation: there is a Package with a PackageNumber that is an element of the set of all of PackageNumbers and with a Weight that is an element of the set of all Weights; oh, and by the way, the PackageNumber happens to be 12341.

The statement represented by the row is true--even though the weight has not been supplied. Therefore, the row above is in accord with the closed world assumption because there is a package with PackageNumber 12341 that has a Weight that is one of the elements of the set of all Weights. In other words: there is a row, and thus it's so.

An open world interpretation would be something like

Package {{PackageNumber:12341}}
PackageWeight {}

Because we know that there is a package, and we know that the package has a weight, but that that weight hasn't been supplied, the relation PackageWeight must follow the open world interpretation because one of the possible combinations of values drawn from the PackageNumbers and Weights domains should be there but isn't, and therefore the statement that is represented by that combination--whatever it happens to be--is true. Under the closed world assumption, however, because that row does not appear in the relation, the statement it represents must be false. And we know that that can't be the case. Received on Wed Jul 09 2008 - 23:42:52 CEST

Original text of this message