Horton

Thread

Commits

  1. Disallow UNION/INTERSECT/EXCEPT over no columns.

  2. Fix UNION/INTERSECT/EXCEPT over no columns.

  1. Intersection or zero-column queries

    Victor Yegorov <vyegorov@gmail.com> — 2017-12-21T23:53:35Z

    Greetings.

One can issue an empty `SELECT` statement and 1 row without columns will be
returned:

    postgres=# select;
    --
    (1 row)

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?
Should this be reported as a bug?.. ;)


-- 
Victor Yegorov

  2. Re: Intersection or zero-column queries

    David G. Johnston <david.g.johnston@gmail.com> — 2017-12-22T00:03:35Z

    On Thu, Dec 21, 2017 at 4:53 PM, Victor Yegorov <vyegorov@gmail.com> wrote:

>     postgres=# select except select;
>     --
>     (2 rows)
>     postgres=# select intersect all select;
>     --
>     (2 rows)
>
> Why is it so?
> Should this be reported as a bug?.. ;)
>

​The intersection case seems correct - one row from each sub-relation is
returned since ALL is specified and both results as the same.

The except case looks like a bug because there should never be more rows
returned from the combined query than the upper sub-query returns alone.
Based upon the result of intersect it should in fact return zero rows -
unless this one of those null-like scenarios where it is both equal and not
equal at the same time...

David J.
​

  3. Re: Intersection or zero-column queries

    Victor Yegorov <vyegorov@gmail.com> — 2017-12-22T00:08:55Z

    2017-12-22 2:03 GMT+02:00 David G. Johnston <david.g.johnston@gmail.com>:

> On Thu, Dec 21, 2017 at 4:53 PM, Victor Yegorov <vyegorov@gmail.com>
> wrote:
>
>>     postgres=# select except select;
>>     --
>>     (2 rows)
>>     postgres=# select intersect all select;
>>     --
>>     (2 rows)
>>
>> Why is it so?
>> Should this be reported as a bug?.. ;)
>>
>
> ​The intersection case seems correct - one row from each sub-relation is
> returned since ALL is specified and both results as the same.
>

Actually, result will not change with or without `ALL` for both, EXCEPT and
INTERSECT.

Also, intersection should not return more rows, than there're in the
sub-relations.


-- 
Victor Yegorov

  4. Re: Intersection or zero-column queries

    David G. Johnston <david.g.johnston@gmail.com> — 2017-12-22T00:13:07Z

    On Thu, Dec 21, 2017 at 5:08 PM, Victor Yegorov <vyegorov@gmail.com> wrote:

>
> Also, intersection should not return more rows, than there're in the
> sub-relations.
>
>
Doh!, I think I got UNION into my mind somewhere in that...

David J.

  5. Re: Intersection or zero-column queries

    Tom Lane <tgl@sss.pgh.pa.us> — 2017-12-22T02:34:48Z

    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

  6. Re: Intersection or zero-column queries

    David G. Johnston <david.g.johnston@gmail.com> — 2017-12-22T02:42:36Z

    On Thursday, December 21, 2017, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> 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?
>

How about just erroring out?

David J.

  7. Re: Intersection or zero-column queries

    Tom Lane <tgl@sss.pgh.pa.us> — 2017-12-22T02:59:46Z

    "David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thursday, December 21, 2017, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> 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?

> How about just erroring out?

Hm, yeah, inserting a FEATURE_NOT_SUPPORTED error might be an
appropriate amount of effort.

			regards, tom lane

  8. Re: Intersection or zero-column queries

    Ken Tanzer <ken.tanzer@gmail.com> — 2017-12-22T07:56:23Z

    I noticed I get this behavior in 9.6, but in 9.2 an empty select results in
a syntax error.  Which just got me curious what caused the change, if it
was deliberate, and if one or the other is more proper behavior.

Cheers,
Ken

-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken.tanzer@agency-software.org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.

  9. Re: Intersection or zero-column queries

    Tom Lane <tgl@sss.pgh.pa.us> — 2017-12-22T15:08:56Z

    Ken Tanzer <ken.tanzer@gmail.com> writes:
> I noticed I get this behavior in 9.6, but in 9.2 an empty select results in
> a syntax error.  Which just got me curious what caused the change, if it
> was deliberate, and if one or the other is more proper behavior.

Yes, it was an intentional change, see
https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=1b4f7f93b

There should be something about it in the 9.4 release notes.

			regards, tom lane

  10. Re: Intersection or zero-column queries

    Tom Lane <tgl@sss.pgh.pa.us> — 2017-12-22T17:11:35Z

    I wrote:
> "David G. Johnston" <david.g.johnston@gmail.com> writes:
>> How about just erroring out?

> Hm, yeah, inserting a FEATURE_NOT_SUPPORTED error might be an
> appropriate amount of effort.

When I looked into this more closely, it turns out that in v10/HEAD
it takes less code to fix it than to throw an error ;-).  So I just
fixed it and added some regression tests.  But 9.6 blows up somewhere
in the executor, and it didn't seem worth trying to deal with that.
So in 9.4-9.6 I just made the case throw an error.

			regards, tom lane