Re: Intersection or zero-column queries

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