Re: Examples of SQL anomalies?

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.
>
> 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 Received on Wed Jul 02 2008 - 18:24:29 CEST