Re: Is this proper UNION behavior??

On Mon, Nov 13, 2000 at 10:12:39PM -0500, Tom Lane wrote:
> "Edward Q. Bridges" <ed(dot)bridges(at)buzznik(dot)com> writes:
> > ebridges=> (select * from has_some_dupes)
> > ebridges-> UNION
> > ebridges-> (select * from has_some_dupes);
>
> [ produces the same result as "select * from has_some_dupes" ]
>
> Yup, that's a bug in 7.0.* (and a version or two before). There's
> a planning step that thinks it can simplify UNION/INTERSECT/EXCEPT
> expressions as if they were boolean expressions --- in particular,
> since "X or X = X", it thinks "X union X = X". Unfortunately,
> whoever wrote that code hadn't actually bothered to consult the
> SQL spec about the semantics of UNION, INTERSECT, or EXCEPT :-(.
> The spec's rules about duplicate elimination mean that the behavior
> is NOT quite like boolean OR/AND/AND NOT.
>
> Since people don't ordinarily write queries as silly as X union X,
> this bug hasn't been very high priority to fix. But it is fixed
> for 7.1 ...

Of course, the real bug here is in SQL, namely that it allows
duplicates in tables.

Relations don't have duplicates (ask your nearest pet
mathematician). Of course, 'UNION' understands taht relations don't
have duplicates, but bizarrely, SELECT doesn't.

ObPG: I'm no suggesting that Pg change this, by the way, I'm just
ranting pointlessly about one of SQL's stupidities. Maybe Pg ought to
have an option for 'bette-than-SQL', though, which would, among other
thigns, not permit duplicates in relations (and switch back on that
optimisation).

Jules