Re: does a table always need a PK?

From: Bob Badour <bbadour_at_golden.net>
Date: Wed, 3 Sep 2003 09:39:38 -0400
Message-ID: <Okn5b.420$uF3.43906163@mantis.golden.net>

"Lee Fesperman" <firstsql_at_ix.netcom.com> wrote in message news:3F559CE8.33AA_at_ix.netcom.com...
> 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.

When the user writes a simple formulation that correctly returns NULL, the user will have to think about the issue and decide what the formulation he really meant. When the user writes a simple formulation that incorrectly returns a number, the user will have no reason to question the result and no reason to think whether the result is the desired result.

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

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.

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

I think that's easier said than done. I have met plenty of bright people who have trouble including NULL in their conceptual model of the database. They have a natural tendency to focus on the data and to assume the dbms just takes care of the missing information for them. Received on Wed Sep 03 2003 - 08:39:38 CDT