Re: Intersection or zero-column queries

Victor Yegorov <vyegorov(at)gmail(dot)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