Re: Examples of SQL anomalies?

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Thu, 10 Jul 2008 13:00:17 -0300
Message-ID: <48763214$0$4072$9a566e8b@news.aliant.net>


David Cressey wrote:

> "Bob Badour" <bbadour_at_pei.sympatico.ca> wrote in message  news:48760971$0$4026$9a566e8b_at_news.aliant.net...
> 

>>David Cressey wrote:
>>
>>>"Brian Selzer" <brian_at_selzer-software.com> wrote in message
>>>news: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.
>>>
>>>I disagree. Your interpretation is that a package number with no recorded
>>>weight does, in fact, have a weight drawn for the set of all weights is an
>>>interpretation on your poart of the real world. It isn't inherent in the
>>>database schema.
>>>
>>>There could be a database schema where a NULL in the wieght column is
>>>intended to convey that weight is inapplicable to this given package number.
> 

>>>This is the same information that would be conveyed by omitting a row in a
>>>table that consists only of {PackageNumber, Weight}.
>>>
>>>NULL means that there is no data here. It doesn't necessarily mean that the
>>>value is unknown.
>>
>>The problem with that is the rules for NULL in SQL are inconsistent with
>>"inapplicable".
> 
> I'm not sure what you mean by this.
> 
> Assuming you are right, I covered this ground earlier when I suggested that
> there could be a disconnect between Brian and SQL.  I did not intend to
> imply that Brian was necessarily wrong if there is such a disconnect.  There
> is even the possibility that SQL is wrong, Brian is also wrong,  and there
> is also a disconnect between Brian and SQL.

I would say it is a little more than a possibility. Received on Thu Jul 10 2008 - 11:00:17 CDT

Original text of this message