From c4c2885cbb1803f772e58f6db4c8951d8cd672cd Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 22 Dec 2017 12:08:06 -0500 Subject: [PATCH] Fix UNION/INTERSECT/EXCEPT over no columns. Since 9.4, we've allowed the syntax "select union select" and variants of that. However, the planner wasn't expecting a no-column set operation and ended up treating the set operation as if it were UNION ALL. Turns out it's trivial to fix in v10 and later; we just need to be careful about not generating a Sort node with no sort keys. However, since a weird corner case like this is never going to be exercised by developers, we'd better have thorough regression tests if we want to consider it supported. Per report from Victor Yegorov. Discussion: https://postgr.es/m/CAGnEbojGJrRSOgJwNGM7JSJZpVAf8xXcVPbVrGdhbVEHZ-BUMw@mail.gmail.com --- src/backend/optimizer/plan/createplan.c | 1 - src/backend/optimizer/prep/prepunion.c | 26 +++--- src/test/regress/expected/union.out | 115 ++++++++++++++++++++++++ src/test/regress/sql/union.sql | 43 +++++++++ 4 files changed, 168 insertions(+), 17 deletions(-) diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index 1a0d3a885f..1a9fd82900 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -6326,7 +6326,6 @@ make_setop(SetOpCmd cmd, SetOpStrategy strategy, Plan *lefttree, * convert SortGroupClause list into arrays of attr indexes and equality * operators, as wanted by executor */ - Assert(numCols > 0); dupColIdx = (AttrNumber *) palloc(sizeof(AttrNumber) * numCols); dupOperators = (Oid *) palloc(sizeof(Oid) * numCols); diff --git a/src/backend/optimizer/prep/prepunion.c b/src/backend/optimizer/prep/prepunion.c index a24e8acfa6..f87849ea47 100644 --- a/src/backend/optimizer/prep/prepunion.c +++ b/src/backend/optimizer/prep/prepunion.c @@ -711,10 +711,6 @@ generate_nonunion_path(SetOperationStmt *op, PlannerInfo *root, /* Identify the grouping semantics */ groupList = generate_setop_grouplist(op, tlist); - /* punt if nothing to group on (can this happen?) */ - if (groupList == NIL) - return path; - /* * Estimate number of distinct groups that we'll need hashtable entries * for; this is the size of the left-hand input for EXCEPT, or the smaller @@ -741,7 +737,7 @@ generate_nonunion_path(SetOperationStmt *op, PlannerInfo *root, dNumGroups, dNumOutputRows, (op->op == SETOP_INTERSECT) ? "INTERSECT" : "EXCEPT"); - if (!use_hash) + if (groupList && !use_hash) path = (Path *) create_sort_path(root, result_rel, path, @@ -864,10 +860,6 @@ make_union_unique(SetOperationStmt *op, Path *path, List *tlist, /* Identify the grouping semantics */ groupList = generate_setop_grouplist(op, tlist); - /* punt if nothing to group on (can this happen?) */ - if (groupList == NIL) - return path; - /* * XXX for the moment, take the number of distinct groups as equal to the * total input size, ie, the worst case. This is too conservative, but we @@ -898,13 +890,15 @@ make_union_unique(SetOperationStmt *op, Path *path, List *tlist, else { /* Sort and Unique */ - path = (Path *) create_sort_path(root, - result_rel, - path, - make_pathkeys_for_sortclauses(root, - groupList, - tlist), - -1.0); + if (groupList) + path = (Path *) + create_sort_path(root, + result_rel, + path, + make_pathkeys_for_sortclauses(root, + groupList, + tlist), + -1.0); /* We have to manually jam the right tlist into the path; ick */ path->pathtarget = create_pathtarget(root, tlist); path = (Path *) create_upper_unique_path(root, diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out index ee26b163f7..92d427a690 100644 --- a/src/test/regress/expected/union.out +++ b/src/test/regress/expected/union.out @@ -552,6 +552,121 @@ SELECT q1 FROM int8_tbl EXCEPT (((SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1))) 4567890123456789 | -4567890123456789 (5 rows) +-- +-- Check behavior with empty select list (allowed since 9.4) +-- +select union select; +-- +(1 row) + +select intersect select; +-- +(1 row) + +select except select; +-- +(0 rows) + +-- check hashed implementation +set enable_hashagg = true; +set enable_sort = false; +explain (costs off) +select from generate_series(1,5) union select from generate_series(1,3); + QUERY PLAN +---------------------------------------------------------------- + HashAggregate + -> Append + -> Function Scan on generate_series + -> Function Scan on generate_series generate_series_1 +(4 rows) + +explain (costs off) +select from generate_series(1,5) intersect select from generate_series(1,3); + QUERY PLAN +---------------------------------------------------------------------- + HashSetOp Intersect + -> Append + -> Subquery Scan on "*SELECT* 1" + -> Function Scan on generate_series + -> Subquery Scan on "*SELECT* 2" + -> Function Scan on generate_series generate_series_1 +(6 rows) + +select from generate_series(1,5) union select from generate_series(1,3); +-- +(1 row) + +select from generate_series(1,5) union all select from generate_series(1,3); +-- +(8 rows) + +select from generate_series(1,5) intersect select from generate_series(1,3); +-- +(1 row) + +select from generate_series(1,5) intersect all select from generate_series(1,3); +-- +(3 rows) + +select from generate_series(1,5) except select from generate_series(1,3); +-- +(0 rows) + +select from generate_series(1,5) except all select from generate_series(1,3); +-- +(2 rows) + +-- check sorted implementation +set enable_hashagg = false; +set enable_sort = true; +explain (costs off) +select from generate_series(1,5) union select from generate_series(1,3); + QUERY PLAN +---------------------------------------------------------------- + Unique + -> Append + -> Function Scan on generate_series + -> Function Scan on generate_series generate_series_1 +(4 rows) + +explain (costs off) +select from generate_series(1,5) intersect select from generate_series(1,3); + QUERY PLAN +---------------------------------------------------------------------- + SetOp Intersect + -> Append + -> Subquery Scan on "*SELECT* 1" + -> Function Scan on generate_series + -> Subquery Scan on "*SELECT* 2" + -> Function Scan on generate_series generate_series_1 +(6 rows) + +select from generate_series(1,5) union select from generate_series(1,3); +-- +(1 row) + +select from generate_series(1,5) union all select from generate_series(1,3); +-- +(8 rows) + +select from generate_series(1,5) intersect select from generate_series(1,3); +-- +(1 row) + +select from generate_series(1,5) intersect all select from generate_series(1,3); +-- +(3 rows) + +select from generate_series(1,5) except select from generate_series(1,3); +-- +(0 rows) + +select from generate_series(1,5) except all select from generate_series(1,3); +-- +(2 rows) + +reset enable_hashagg; +reset enable_sort; -- -- Check handling of a case with unknown constants. We don't guarantee -- an undecorated constant will work in all cases, but historically this diff --git a/src/test/regress/sql/union.sql b/src/test/regress/sql/union.sql index c0317cccb4..eed7c8d34b 100644 --- a/src/test/regress/sql/union.sql +++ b/src/test/regress/sql/union.sql @@ -190,6 +190,49 @@ SELECT q1 FROM int8_tbl EXCEPT (((SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1))) (((((select * from int8_tbl))))); +-- +-- Check behavior with empty select list (allowed since 9.4) +-- + +select union select; +select intersect select; +select except select; + +-- check hashed implementation +set enable_hashagg = true; +set enable_sort = false; + +explain (costs off) +select from generate_series(1,5) union select from generate_series(1,3); +explain (costs off) +select from generate_series(1,5) intersect select from generate_series(1,3); + +select from generate_series(1,5) union select from generate_series(1,3); +select from generate_series(1,5) union all select from generate_series(1,3); +select from generate_series(1,5) intersect select from generate_series(1,3); +select from generate_series(1,5) intersect all select from generate_series(1,3); +select from generate_series(1,5) except select from generate_series(1,3); +select from generate_series(1,5) except all select from generate_series(1,3); + +-- check sorted implementation +set enable_hashagg = false; +set enable_sort = true; + +explain (costs off) +select from generate_series(1,5) union select from generate_series(1,3); +explain (costs off) +select from generate_series(1,5) intersect select from generate_series(1,3); + +select from generate_series(1,5) union select from generate_series(1,3); +select from generate_series(1,5) union all select from generate_series(1,3); +select from generate_series(1,5) intersect select from generate_series(1,3); +select from generate_series(1,5) intersect all select from generate_series(1,3); +select from generate_series(1,5) except select from generate_series(1,3); +select from generate_series(1,5) except all select from generate_series(1,3); + +reset enable_hashagg; +reset enable_sort; + -- -- Check handling of a case with unknown constants. We don't guarantee -- an undecorated constant will work in all cases, but historically this -- 2.40.0