Victor Yegorov <vyegorov@gmail.com> writes:
> However, if I'll do `EXCPET` or `INTERSECT` of such queries, I'll get 2
> rows:
> postgres=# select except select;
> --
> (2 rows)
> postgres=# select intersect all select;
> --
> (2 rows)
> Why is it so?
The UNION case seems wrong as well:
regression=# select union select;
--
(2 rows)
The reason is that the planner hasn't spent any time thinking about this
case:
/* Identify the grouping semantics */
groupList = generate_setop_grouplist(op, tlist);
/* punt if nothing to group on (can this happen?) */
if (groupList == NIL)
return path;
so what you actually get for any of these queries is a plan that
just appends the inputs and forgets to do any de-duplication:
regression=# explain select except select;
QUERY PLAN
-----------------------------------------------------------------------
Append (cost=0.00..0.04 rows=2 width=4)
-> Subquery Scan on "*SELECT* 1" (cost=0.00..0.02 rows=1 width=4)
-> Result (cost=0.00..0.01 rows=1 width=0)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..0.02 rows=1 width=4)
-> Result (cost=0.00..0.01 rows=1 width=0)
(5 rows)
which would only be the right plan for UNION ALL.
So yeah, it's wrong ... but personally I'm not terribly excited
about fixing it. Maybe somebody else wants to; but what's the
practical use?
regards, tom lane
