Re: does a table always need a PK?

Marshall Spight 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.
>
> > 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 have two problems with that,

+ It means that when NULLs exist in the aggregated results SUM() will always produce NULL, making it mostly useless.

+ SUM() is just one type of aggregate function. The other common ones don't have a corresponding simple operator. The rules should be the same for all aggregate functions.

Also, Codd stated that SUM() is different from +.

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

Yes, you can, but it does make for more complicated, error-prone formulations.

> Marshall
>
> (Please understand that I am not flaming Lee, for whom I have great respect.)

Hi Marshall!

No offense taken. I know full well that NULLs (and 3VL) are controversial.

-- 
Lee Fesperman, FirstSQL, Inc. (http://www.firstsql.com)
==============================================================
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS  (http://www.firstsql.com)