Re: does a table always need a PK?

Quoth Lee Fesperman <firstsql_at_ix.netcom.com>:
> Bob Badour wrote:
>> If the user expects a number but gets a NULL, the user will find the
>> unexpectedness obvious. If the user expects one number but gets another
>> number, the user will find the unexpectedness less obvious.
>
> Ok, I guess I was being dense. I understand what you are getting at.
>
> However, it does not cover the general case, making it a bad choice.
>
> For example,
>
> select AVG(A), AVG(B) from T;
>
> ... where I want AVG(A) computed only for rows where A definitely has a value and the
> same for AVG(B).

In a "contagious NULLs" scenario, that means that if you don't want the results to be potentially all NULL, you need to select...

 select AVG(A), AVG(B) from T where A is not null and B is not null;

The other query that might be wanted would be:

 select 'A', AVG(A) from T where A is not null    union
 select 'B', AVG(B) from T where B is not null;

> Note: for the show-offs out there, here's a meater example:
>
> select C, AVG(A), AVG(B) from T group by C;

The more "behind-your-back" work that goes on, the less likely this is to actually be correct. The "magically right thing" for this one probably isn't... STDDEV() or VARIANCE() are liable to be even more troublesome...

> I readily admit that I have not studied TTM, so I don't know D&D's current take on
> missing information. The last I heard they were still clinging to the Default Values
> solution, which is bashed on www.firstsql.com.

The right Default Value for addition aggregates would be 0.

The right Default Value for multiplicative aggregates would be 1.

One might use this to indicate that DV = 0 and DV = 1, and that therefore 0 = 1. (Takes me back to the UW "MathNews: Proof of the Week" feature :-) ...)

Which doesn't bode very well for Default Values.

I find that I increasingly approve of languages that put Downright Ridiculous Defaults into uninitialized variables. :-)

-- 
wm(X,Y):-write(X),write('_at_'),write(Y). wm('cbbrowne','cbbrowne.com').
http://cbbrowne.com/info/linuxxian.html
Jumping  off a  cliff doesn't  kill you!  It's only  when you  hit the
ground...