Re: Is this proper UNION behavior??

Jules Bean <jules(at)jellybean(dot)co(dot)uk> writes:
> 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).

Just to clarify: it was not really an optimization, at least not to
my mind. How many people are ever going to write "X union X" in real
queries? As an optimization attempt, it was a complete waste of time
to be looking for this case --- the savings on the once-in-a-blue-moon
silly query would never pay for the cycles burned to make the test on
every other query.

The reason that code was there in the first place is that the pre-7.1
implementation method for UNION/INTERSECT/EXCEPT could only cope with
query trees in which all the UNIONs were at the top. So what was
*really* being done was a transformation to disjunctive normal form,
eg,
(X union Y) intersect Z
becomes
(X intersect Z) union (Y intersect Z)
The simplication of cases like X union X was a byproduct of the DNF
transformation routine, which was an algorithm designed for boolean
expressions in which such a transformation is completely legitimate.

Now of course what will leap out at you here is that the transformed
query is likely to be considerably *more* work than the original.
So for real queries, this wasn't an optimization at all, but a
pessimization. I have no intention of putting it back regardless of
debates about better-than-SQL semantics ;-)

regards, tom lane