Re: does a table always need a PK?

"Marshall Spight" <mspight_at_dnai.com> wrote:
> "Lee Fesperman" <firstsql_at_ix.netcom.com> wrote in message news:3F5452A0.1AB@ix.netcom.com...
>>
>> ... because the SUM() function and the '+' operator, while similar,
>> are different operations.
>
> Yuck. Sum() should be nothing more than the reduction of + over the
> attribute. Anything else is an unnecessary complication.

That's a good way of understanding SUM() and other aggregates, which implies that understanding aggregates requires having passing familiarity with some of the ancient concepts of APL.

But it is /not/ obvious that the "+" operator represents a reduction operator.

Consider:

 select count(a + b) from table_t;

The nature of the addition there is different from the nature of the COUNT()ing. Perhaps "a + b" should be resolved by reduction of + over a and b. But that's only "perhaps." If the thing inside the aggregate was (a*a - b*b + 25), it is no longer obvious that it is appropriate to recursively use reduction on the components.

>> SUM() throws away any NULL results and only sums non-NULL values. The +
>> operator yields NULL if either operand is NULL.
>
> I don't see how this can be considered valid. (I *can* see how it could be
> the SQL standard, but that's another story.) If null specifies missing data,
> then sum() over an attribute where the attribute value is null in any tuple
> is necessarily null.
>
> Under the logic you describe, we have two different kinds of addition
> where one suffices.
>
> I am no fan of null, but even if we accept the idea of null, isn't
> it possible to get whatever behavior we need by an appropriate use
> of CASE or WHERE NOT NULL or some such?

I'm sure it is possible; that still is quite ugly.

-- 
"cbbrowne","_at_","acm.org"
http://www3.sympatico.ca/cbbrowne/linux.html
Never lend your car to anyone  to whom you have given birth to. 
--Erma Bombeck