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
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.
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
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.
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
*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.
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