Re: Examples of SQL anomalies?

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Fri, 04 Jul 2008 18:59:57 -0300
Message-ID: <486e9d62$0$4066$9a566e8b_at_news.aliant.net>

David Cressey wrote:

> "Brian Selzer" <brian_at_selzer-software.com> wrote in message
> news:zOvbk.31378$ZE5.24693_at_nlpi061.nbdc.sbc.com...
> 

>>"David Cressey" <cressey73_at_verizon.net> wrote in message
>>news:D8ubk.240$rb1.211_at_trndny08...
>>
>>>"Brian Selzer" <brian_at_selzer-software.com> wrote in message
>>>news:gI6bk.11000$cW3.8591_at_nlpi064.nbdc.sbc.com...
>>>
>>>>"David Cressey" <cressey73_at_verizon.net> wrote in message
>>>>news:wq4bk.28$0V1.10_at_trndny01...
>>>>
>>>>>"Brian Selzer" <brian_at_selzer-software.com> wrote in message
>>>>>news:nFD9k.5753$LG4.2422_at_nlpi065.nbdc.sbc.com...
>>>>>
>>>>>>"-CELKO-" <jcelko212_at_earthlink.net> wrote in message
>>>>>>
>>>

> news:f219a6bd-9d8e-4cfe-9d60-ce9dcaeff16d_at_z66g2000hsc.googlegroups.com...
> 


>>>>>>>>>The question is, if these issues are due to the SQL  specification or

>>>>>>>>>simply due to a problem in a specific SQL product. Or could it  be, that

>>>>>>>>>the definition is not precise enough in some points, so that  database

>>>>>>>>>vendors implemented it differently? <<

>>>>>>>

>>>>>>>Nope, it is the specs.  All aggregate (set) functions begin by

>>>>>>>removing the NULLs from their parameter set, then if there is a

>>>>>>>DISTINCT option on the parameter, they remove redundant duplicates and
>>>>>>>finally do the operation (MIN, MAX, AVG, SUM, COUNT on what is left.

> 


>>>>>>>Since an empty set has no elements upon which to apply an  operation,

>>>>>>>SQL returns a NULL (okay, it should be an "undefined" if we were  mathematically correct).

>>>>>>

>>>>>>MIN, MAX and AVG are meaningless when applied to an empty bag, but  it seems

>>>>>>to me that COUNT should always return 0 when the bag is empty, and

>>>>>>similarly, SUM should return 0.  SUM should only return NULL if one of the

>>>>>>values to be summed is NULL.

>>>>>

>>>>>By definition, none of the values to be summed are NULL.


>>>>

>>>>OK.  OK. Yes, I know.  NULL is not a value.  I guess to be precise, I  should

>>>>have said "if the cardinality of the set of rows targeted by the query  is

>>>>greater than the number of values to be summed, then SUM should return

>>>>NULL," but I think that would have caused more confusion.  Bottom line:  if

>>>>not all of the amounts are known, then the total amount is suspect.

>>>

>>>This has to do with a continuing disagreement between you and me.  In my

>>>view there is no such thing as the "set of rows targeted by the query".

>>>There is a set of data targeted by the query.  That is all.  Rows that  do

>>>not contain any data with regard to the current query are not part of  the

>>>target, by definition.

>>>

>>>Thus,  sum (SALARY) from FUBAR  is the sum of a bag of salaries.  It's not

>>>the sum of a bag of rows from FUBAR. Rows in FUBAR that don't contain a
>>>SALARY are not part of the sum. That is all.
>>
>>What about sum (SALARY) from FUBAR where Fu = 'Bar'? Doesn't "where Fu =
>>'Bar'" restrict the query to the result of evaluating "where Fu = 'Bar'"?
>>Clearly if COUNT(*) where Fu = 'Bar' is greater than COUNT(*) where Fu =
>>'Bar' and SALARY is not NULL, then it is obvious that some salaries aren't
>>known, hence the total of those salaries, sum (SALARY) where Fu = 'Bar' is
>>suspect.
>>
>>I can't understand that you don't get this since it is just so simple.
>
> It is simple, I do get it, and you are wrong.

David, you would do well to contemplate Date's _Principle of Incoherence_: "It is very difficult to respond coherently to that which is incoherent."

As it is, when you reply to Selzer you completely ignore some of his most blatant nonsense and absurd contradictions, and in the end, you accuse him of being wrong for the tiny speck he got right. Received on Fri Jul 04 2008 - 23:59:57 CEST