Re: Examples of SQL anomalies?

paul c <toledobysea_at_ac.ooyah> wrote:

>Bob Badour wrote:
>> Marshall 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.

>> 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!)

[Quoted]      Fabian Pascal would say that he is doing a Celko. Now, I have a sample of my own. Pulling in transfinite numbers to justify SQL's sum() nonsense is nonsense itself.

>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.

      It is pretty simple, but gets sold as being difficult.

>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

     It may have a few surprises, but it is not really that bad.

>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

     Quite.

>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.

     Logic may have a few tricks and traps to it, but it sure beats the alternatives.

>There are a few tricks in FOL that can trip anybody up, but far fewer
>than the rather amateur legalese the SQL standard contains.

      Quite.

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:

     I have preferences.
     You have biases.
     He/She has prejudices.