Thread: Intersection or zero-column queries
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
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.
2017-12-22 2:03 GMT+02:00 David G. Johnston <david.g.johnston@gmail.com>:
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
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.
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
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.
"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
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
(253) 245-3801
learn more about AGENCY or
follow the discussion.
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
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