Re: Examples of SQL anomalies?

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Wed, 02 Jul 2008 15:16:25 -0300
Message-ID: <486bc5fd$0$4049$9a566e8b@news.aliant.net>


Ed Prochak wrote:

> On Jul 2, 8:26 am, Bob Badour <bbad..._at_pei.sympatico.ca> wrote:
> 

>>Ed Prochak wrote:
>>
>>>On Jun 30, 5:54 pm, Marshall <marshall.spi..._at_gmail.com> wrote:
>>
>>>>On Jun 30, 10:31 am, "Brian Selzer" <br..._at_selzer-software.com> wrote:
>>
>>>>>Well, the OP wanted examples of SQL anomalies, and you've just confirmed a
>>>>>big one.
>>
>>>>>If you have a bag that can contain peaches, but doesn't, then the answer to
>>>>>the question "How many peaches are in the bag?" is clearly zero. If you are
>>>>>asked by the accountant, "How much were we billed by AT&T this month?" but
>>>>>AT&T didn't send a bill, then the answer is clearly zero. That SQL's COUNT
>>>>>and SUM are something other than these common sense usages exemplifies their
>>>>>anomalous nature.
>>
>>>>[I meant to say this in my other post, but]
>>
>>>>Brian gets it exactly right here.
>>
>>>>Marshall
>>
>>>I'm not so sure about the AT&T bill. Consider the question may be
>>>badly phrased:
>>>"How much were we billed by AT&T this month?"
>>>Is the amount being sought the amount on the bill received this month?
>>>or the amount for this month's services?
>>>If the question is asked July 1 and we haven't got the bill in the
>>>mail yet, the answer must be "I don't know."
>>>And who is the "we"? Are there multiple entities involved? (multiple
>>>businesses owned by one parent company with separate phone accounts/
>>>bills? or maybe a home business and personal home phone account both
>>>shown on one bill?)
>>
>>>So even in real life NULL (aka "I don't know") happens to be the
>>>correct answer to some questions. It is not as clean cut as you and
>>>Brian would like.
>>> Ed
>>
>>You are suggesting the computer should have some way to evaluate
>>external predicates. The computer cannot. Your argument is a red herring.
> 
> That was just to set the context that it isn't obvious what answer is
> really sought. Context counts for a lot.

I disagree. Asking the question one wants answered is the only thing that counts.

>>If one expects a bill and the total is 0, that's just as useful to alert
>>one that the bill has not yet arrived as a NULL would be.

> 
> No. Consider the case of a credit card bill which does not have a
> repeatable balance month to month.  There are months when the account
> balance is zero. But zero is not the correct answer to give your
> accountant when you haven't yet received the bill. The correct answer
> is "haven't got that bill in yet", aka, NULL  But zero would not tell
> you the difference.
>  Ed

If one wants to know whether the bill has arrived, one can simply ask that instead. The computer has no business trying to second guess the question, which is why I said your example is a red herring. Received on Wed Jul 02 2008 - 13:16:25 CDT

Original text of this message