Re: Examples of SQL anomalies?

On Jul 2, 6:15 am, Ed Prochak <edproc..._at_gmail.com> 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.
>
> 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."

That turns out not to be correct.

We can ask the question, have we received a bill in July? and expect a sensible, yes/no answer.

We can ask, what is the total amount for some set of numbers, and expect a zero if that set is empty, because that is the mathematically correct answer.

We can meaningfully ask any question that is consistent with the schema. We have the CWA to help us understand what the results mean. Asking questions of the future is no different than asking questions for some other set of data the database doesn't have.

> So even in real life NULL (aka "I don't know") happens to be the
> correct answer to some questions.

We are discussing the behavior of formal systems, though, not real life.

> It is not as clean cut as you and
> Brian would like.

I haven't yet heard a reason to think it isn't.

Marshall Received on Wed Jul 02 2008 - 18:09:02 CEST