Re: does a table always need a PK?

Bob Badour wrote:
>
> "Lee Fesperman" <firstsql_at_ix.netcom.com> wrote in message
> news:3F553AD7.7C3_at_ix.netcom.com..
> > 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.
>
> Unless I am mistaken, the following queries return the following results:
>
> Select SUM(A) from T;
> SUM(A)
> ======
> 4
>
> Select SUM(B) from T;
> SUM(B)
> ======
> 6
>
> Select SUM(A), SUM(B) from T;
>
> SUM(A) SUM(B)
> ====== ======
> 1 2
>
> The last result comes about by SQL discarding every row with a NULL that
> would affect the result. The first query includes 3 because the query does
> not reference B. The second query includes 4 because the query does not
> reference A. The third query includes neither because the query references
> both A and B. Does FirstSQL behave differently? Am I mistaken about SQL?

Now I understand your comment. No, that is not correct. The aggregate function only considers NULLs occuring in its argument.

> > > > 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.
>
> Fair enough. Or it could become a discussion of which technique for missing
> information most reliably alerts the user to the cognitive burden.

Perhaps, but that seems to only consider the user that is reading and understanding the formulation, not the user who is formulating it.

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