Re: does a table always need a PK?

Bob Badour wrote:
>
> "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.
> > > > SUM() throws away any NULL results and only sums non-NULL values.
> > > > The + operator yields NULL if either operand is NULL.
> > >
> > > Under the logic you describe, we have two different kinds of addition
> > > where one suffices.
> >
> > I have two problems with that,
> >
> > + It means that when NULLs exist in the aggregated results SUM()
> > will always produce NULL, making it mostly useless.
>
> 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.

Obviously, I think 'incorrect' is too strong. SUM(A) is asking for the SUM of column A in rows where A is definitely known to have a value.

> > 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 believe it was in RM/T in the same area where he discusses a modifier for aggregate functions to specify NULL disposition. Unfortunately, I have not found an online version of RM/T. I do know it was published by ACM SIGMOD in '79. Perhaps, you could check V2.

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

I don't see the simple correct formulations you refer to. I said they will be more complicated. I show some examples in my online paper -- ttp://www.firstsql.com/idefend.htm.

As I pointed out above, I don't see it as an "obviously unexpected" result.

It is a complicated area which users must be informed about.

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