Path: utzoo!utgpu!news-server.csri.toronto.edu!rutgers!cs.utexas.edu!usc!apple!voder!blia!miket
From: miket@blia.BLI.COM (Mike Tossy)
Newsgroups: comp.databases
Subject: Re: Ingres Question: Duplicate Rows
Message-ID: <12314@blia.BLI.COM>
Date: 10 Aug 90 20:29:43 GMT
References: <1990Aug8.230913.2897@agate.berkeley.edu> <26867@pasteur.Berkeley.EDU>
Organization: Britton Lee, Los Gatos, CA
Lines: 38

In article <26867@pasteur.Berkeley.EDU>, mao@eden (Mike Olson) writes:
 
> i can't comment on what ingres is doing in its heap access method, but
> as a general statement, duplicate rows in relational systems are a major
> no-no.  codd explains why in grueling detail in his books; basically,
> it comes down to the fact that different access paths can yield different
> results if duplicates are permitted.  the fact that most commercial systems
> (well, okay, all commercial systems) permit dups is testimony to the
> difficulty of detecting and removing them, and not to their desirability.

The ANSI Standard specificly allows duplicates. This is probably unfortunate.
(Luckily :->  they aren't very useful since that the standard provides no 
way to interactively update only one of them.)  This is one of those areas
where QUEL semantics and SQL semantics are incompatable.  We (in Britton Lee
days) also had a QUEL based engine and this kind of problem was common.  I
suspect that, like ourselves, the Ingres folks are having to fix their RDBMS
engine to not drop duplicates.

(I'm not sure I like the ANSI SQL semantics but at least they are consistant;
something the QUEL semantics never were.)
 
> you shouldn't be encoding meaning into dups.  you can add a "number of
> occurrences" column to rows in the table if you really want to know how
> many occurrences there are.

Absolutely correct!  Duplicates in base tables are a poor idea even when
permitted.  However unless you really pay attention, it is fairly easy to
create temporary derived tables (say for decision support) which do have
duplicates because you didn't include all the base tables' primary keys.
The SQL semantics are much more likely to give you the desiered answer in
this case.
                                                Teradata Corporation
Mike Tossy                                      ShareBase Division
miket@blia.bli.com                              14600 Wichester Blvd
(408) 378-7575 ext2200                          Los Gatos, CA 95030
					(Formerly: Britton Lee, Inc.)

	 These are only my opinions.