Re: does a table always need a PK?
Date: 3 Sep 2003 04:19:31 GMT
Message-ID: <bj3q4i$eaohu$1_at_ID-125932.news.uni-berlin.de>
Centuries ago, Nostradamus foresaw when "Bob Badour" <bbadour_at_golden.net> would write:
> "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?
It would be quite reasonable for the result of ALL of these queries to be the same:
select sum(a) from t;
NULL
select sum(b) from t;
NULL
select sum(a+b) from t;
NULL
To avoid that, there has got to be an implicit "WHERE CLAUSE;" in
effect:
select sum(a) from t;
is actually
select sum(a) from t where a is not null;
and
select sum(b) from t;
is actually
select sum(b) from t where b is not null
select sum(a+b) from t;
then becomes thorny because there are several ways to regard it:
select sum(SUMS) from
(select sum(a) as SUMS from t where a is not null
union
select sum(b) as SUMS from t where b is not null);
select sum(a+b) from t
where a is not null and b is not null;
select sum(a+b) from t
where a+b is not null; -- This is actually equivalent to the
- preceding query...
This diversity of rational answers to the queries is quite disturbing.
>> > > 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.
-- (reverse (concatenate 'string "ac.notelrac.teneerf" "_at_" "454aa")) http://cbbrowne.com/info/oses.html "I'd say the probability of Windows containing a backdoor is about the same as a spreadsheet containing a flight simulator." -- Phil Hunt <philh_at_vision25.demon.co.uk>Received on Wed Sep 03 2003 - 06:19:31 CEST