Re: Examples of SQL anomalies?

From: Bob Badour <>
Date: Mon, 30 Jun 2008 23:48:53 -0300
Message-ID: <48699b17$0$4035$>

paul c wrote:

> Bob Badour wrote:

>> Marshall wrote:
>>> On Jun 30, 10:31 am, "Brian Selzer" <> 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
>> Well, except that SQL adds the additional anomaly that
>> count(*) != sum(1)
>> ie. SQL can count zero rows with count but not with sum.

> I was hoping you two would pipe up about this because as usual, it is
> completely unclear whether CELKO is trying to answer the OP's question
> or whether he is trying to justify SQL's myriad behaviours. (Not
> claiming this is deliberate, though I do have my suspicions!)
> Not that I know much about SQL, having given up on it many years ago.
> On the matter of 'common sense', I've learned not to trust it, at least
> as a first principle, as it often turns out to be a cover phrase meaning
> 'that's what I thought in the first place' and thus varies dramatically
> depending on the person, the moment and the lingo in play. For sure,
> bringing up intricate arbitrary logics like Johnston's is phony in this
> case. Elementary logic such as described, say, by Bertrand Russell (who
> had far fewer axes to grind than Celko and Johnston), in which the empty
> set has zero members, happens to agree with Brian S's common sense, so
> everybody except of course for the technocratic apologists and
> opportunists ought to be content that SQL once again paints itself into
> a corner.
> OTOH, it may be that elementary logic is obscure to many and will
> always be so. I remember a first-year logic course where fully three
> quarters of 300 exam candidates walked out of the hall in the first five
> minutes. I know they weren't finished. (Not sure if it had anything to
> do with the professor who was often hospitalized or on crutches because
> of his habit of not looking when he crossed the road.) However Celko is
> devious by suggesting that clear explanation lies in the further
> complications he touts. He is actually promoting unnecessary and faux
> elitism. Also, if it is indeed the case that the average programmer who
> has grown up at the knees of mostly physical concepts is incapable of
> understanding Russell, that can only be an argument for avoiding the
> arbitrariness of SQL.
> There are a few tricks in FOL that can trip anybody up, but far fewer
> than the rather amateur legalese the SQL standard contains.

The rules for aggregation don't even have all that much to do with FOL.

I remember the time Joe tried to argue that an aggregate over zero elements is undefined and cited Graham, Knuth et al. In fact, he cited the page before the one that stated point blank that an aggregate over zero elements is defined as the identity element for the base operation.

He's an idiot. Received on Tue Jul 01 2008 - 04:48:53 CEST

Original text of this message