Re: Is this proper UNION behavior??

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

regards, tom lane