Re: does a table always need a PK?

From: Christopher Browne <cbbrowne_at_acm.org>
Date: 3 Sep 2003 04:19:31 GMT
Message-ID: <bj3q4i$eaohu$1@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?

You are omitting the other rational possibility, which is that NULL is a "contagion" that NULLs out anything it touches.

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

What I would realistically expect to see from SQL is something resembling the latter two queries. But note that ALL of these options are rational.

Including the one where, since there are NULL values in domains a and b, the result of the query is NULL, since NULL is a fatal contagion :-).

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.

Of course, for every query value touched with the NULL contagion to become NULL would even more reliably alert the user to the problem.

-- 
(reverse (concatenate 'string "ac.notelrac.teneerf" "@" "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 Tue Sep 02 2003 - 23:19:31 CDT