Re: does a table always need a PK?

"Lee Fesperman" <firstsql_at_ix.netcom.com> wrote in message news:3F553DDD.50EB_at_ix.netcom.com...
> Marshall Spight wrote:
> >
> > "Lee Fesperman" <firstsql_at_ix.netcom.com> wrote in message
news:3F5452A0.1AB_at_ix.netcom.com..
> > >
> > > ... because the SUM() function and the '+' operator, while similar,
are different
> > > operations.
> >
> > Yuck. Sum() should be nothing more than the reduction of + over the
attribute.
> > Anything else is an unnecessary complication.
> >
> > > SUM() throws away any NULL results and only sums non-NULL values. The
+
> > > operator yields NULL if either operand is NULL.
> >
> > I don't see how this can be considered valid. (I *can* see how it could
be
> > the SQL standard, but that's another story.) If null specifies missing
data,
> > then sum() over an attribute where the attribute value is null in any
tuple
> > is necessarily null.
> >
> > Under the logic you describe, we have two different kinds of addition
> > where one suffices.

>

I suggest that providing a subtly incorrect number when the user expects a number is less usable than providing a correct NULL when the user expects a number. Also, given that the dbms can just stop adding for a sum after it encounters the first NULL for a sum, providing the correct NULL will cost less than providing the subtly incorrect number making the cost of rerunning the query with the correct predicate less of an issue.

> + SUM() is just one type of aggregate function. The other common ones
don't have a
> corresponding simple operator. The rules should be the same for all
aggregate
> functions.

Yes, they should be the same. The standard deviation of unknown data is unknown. The average of unknown data is unknown. The minimum of unknown data is unknown unless the minimum value of the domain appears in the relation. etc.

> Also, Codd stated that SUM() is different from +.

I don't recall that. Where did he state it? I would like to look it up.

> > I am no fan of null, but even if we accept the idea of null, isn't it
possible
> > to get whatever behavior we need by an appropriate use of CASE or WHERE
> > NOT NULL or some such?
>
> Yes, you can, but it does make for more complicated, error-prone
formulations.

I suggest that it is better to have complicated error-prone formulations with simple correct formulations than simple error-prone formulations. I also suggest it is better to offer an obviously unexpected result when the result is likely unexpected. Received on Wed Sep 03 2003 - 03:40:22 CEST