aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/backend/optimizer/plan/createplan.c1
-rw-r--r--src/backend/optimizer/prep/prepunion.c26
-rw-r--r--src/test/regress/expected/union.out115
-rw-r--r--src/test/regress/sql/union.sql43
4 files changed, 168 insertions, 17 deletions
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 1a0d3a885f9..1a9fd829008 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 a24e8acfa6c..f87849ea476 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 ee26b163f7d..92d427a690f 100644
--- a/src/test/regress/expected/union.out
+++ b/src/test/regress/expected/union.out
@@ -553,6 +553,121 @@ SELECT q1 FROM int8_tbl EXCEPT (((SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1)))
(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
-- usage has worked, so test we don't break it.
diff --git a/src/test/regress/sql/union.sql b/src/test/regress/sql/union.sql
index c0317cccb4b..eed7c8d34be 100644
--- a/src/test/regress/sql/union.sql
+++ b/src/test/regress/sql/union.sql
@@ -191,6 +191,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
-- usage has worked, so test we don't break it.
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-28 15:08:58 +0000