Re: does a table always need a PK?

Bob Badour wrote:
>
> "Lee Fesperman" <firstsql_at_ix.netcom.com> wrote in message
> news:3F5452A0.1AB_at_ix.netcom.com..
> > If I do a query on T and add (A+B) as a calculated attribute:
> >
> > select A, B, (A+B) as C from T;
> >
> > I get:
> >
> > A B C
> > = = =
> > 1 2 3
> > 3 NULL NULL
> > NULL 4 NULL
> >
> > Applying the SUM() function to each result attribute:
> >
> > select SUM(A), SUM(B), SUM(C) from (select A, B, (A+B) as C from T);
> >
> > produces:
> >
> > SUM(A) SUM(B) SUM(C)
> > ====== ====== ======
> > 4 6 3
> >
> > (SUM(A) + SUM(B)) is not equal to SUM(C)
>
> That's not what I would expect. I was under the impression SQL would simply
> omit any rows with a NULL in any SUM expression. In this case, I would
> expect the sums to result in 1+2=3 preserving the identity. It is only when
> evaluating the sums separately that I would expect the identity to fail.

I'm sorry. I don't follow you here.

> > Using coalesce():
> >
> > SUM(coalesce(A,0)) + SUM(coalesce(B,0)) =
> SUM(coalesce(A,0)+coalesce(B,0))
> >
> > evaluates to True.
>
> Would you agree, then, that NULL and coalesce might place an additional
> cognitive burden on the user ?

Certainly. Though, I would argue that the additional complexity was inherent when dealing with missing information. It then becomes a discussion of which technique for missing information places the least cognitive burden on the user.

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