Re: Is this proper UNION behavior??

On Tue, Nov 14, 2000 at 12:22:32PM -0300, Cristóvão B. B. Dalla Costa wrote:
> > Of course, the real bug here is in SQL, namely that it allows
> > duplicates in tables.
>
> Or maybe not. Tables are not *always* relations, one may have a table of
> things to do. If one thing has to be done twice, it might have two entries
> on the table.

Hurrah! A nice, juicy, off-topic thread for me to get my teeth into
and annoy the old-timers..

>
> It all depends on the workings of the programs using the database. Just
> imagine you SELECT a subset of a tables's unique rows, but that subset
> doesn't produce unique rows. Should the non-unique ones be removed behind
> your back? I think not.

PostgreSQL is a /relational/ DBMS (with some object features). SQL is
a /relational/ query language (with some flaws). They are based on a
mathematical model (incomplete thought it may be). By and large, SQL
tries to maintain this model: that is why UNION behaves the way it
does. If SQL wasn't trying to be relational UNION would behave like
that ghastly non-distinct version (is it called UNION ALL, maybe?). So
I stick to my point that SELECT is the command which is the except
from the general philsophy.

In answer to your two examples: it is of course quite possible to
model a todo list with duplicate entries in a relational fashion. It
is absolutely correct behaviour that when you project only some
columns, duplicates are eliminated.

Jules