This is an automated email from the ASF dual-hosted git repository.
tuhaihe pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/cloudberry.git
The following commit(s) were added to refs/heads/main by this push:
new 268e9b05b21 Fix constant GROUP BY on empty input
268e9b05b21 is described below
commit 268e9b05b21662b77f5391ccac94efb47267dece
Author: krylosov-aa <[email protected]>
AuthorDate: Fri Oct 2 16:02:44 2026 +0300
Fix constant GROUP BY on empty input
With optimizer=off and multi-phase aggregation, a query grouped only
by constants, such as SELECT count(*) FROM t GROUP BY 'x'::text,
returned one row for empty input instead of none. The planner removes
constant grouping keys, and cdbgroupingpaths.c then chose AGG_PLAIN as
if there were no GROUP BY.
Choose the aggregation strategy from the original GROUP BY clause.
This also fixes an assertion failure for constant GROUP BY without
aggregates. Skip the TupleSplit plan in this case when every DISTINCT
aggregate has a FILTER, since it could lose the only group.
See: Issue#2025 <https://github.com/apache/cloudberry/issues/2025>
---
.../src/test/regress/expected/bfv_aggregate.out | 2 +-
src/backend/cdb/cdbgroupingpaths.c | 74 +++++-
src/test/regress/expected/bfv_aggregate.out | 2 +-
src/test/regress/expected/gp_group_by_constant.out | 295 +++++++++++++++++++++
src/test/regress/greenplum_schedule | 1 +
src/test/regress/sql/gp_group_by_constant.sql | 114 ++++++++
6 files changed, 473 insertions(+), 15 deletions(-)
diff --git a/contrib/pax_storage/src/test/regress/expected/bfv_aggregate.out
b/contrib/pax_storage/src/test/regress/expected/bfv_aggregate.out
index 78807ff4085..9d00af5ae60 100644
--- a/contrib/pax_storage/src/test/regress/expected/bfv_aggregate.out
+++ b/contrib/pax_storage/src/test/regress/expected/bfv_aggregate.out
@@ -1777,7 +1777,7 @@ explain (costs off)
select 1, sum(col1) from group_by_const group by 1;
QUERY PLAN
------------------------------------------------
- Finalize Aggregate
+ Finalize GroupAggregate
-> Gather Motion 3:1 (slice1; segments: 3)
-> Partial GroupAggregate
-> Seq Scan on group_by_const
diff --git a/src/backend/cdb/cdbgroupingpaths.c
b/src/backend/cdb/cdbgroupingpaths.c
index 2ebe136d9f0..1db2d15e8d4 100644
--- a/src/backend/cdb/cdbgroupingpaths.c
+++ b/src/backend/cdb/cdbgroupingpaths.c
@@ -514,6 +514,37 @@ cdb_create_multistage_grouping_paths(PlannerInfo *root,
break;
case MULTI_DQAS:
{
+ ListCell *lc;
+
+ /*
+ * If all aggregate FILTER conditions are
false, TupleSplit
+ * returns no rows even though the input is
nonempty. A
+ * constant GROUP BY must still return one
group in this
+ * case, but the GroupAggregate nodes in this
plan would
+ * return none.
+ *
+ * Do not build this plan when every DQA has a
FILTER. An
+ * unfiltered DQA ensures that TupleSplit
produces rows for
+ * nonempty input.
+ */
+ if (ctx.parseGroupClause && !ctx.groupClause)
+ {
+ bool has_unfiltered_agg =
false;
+
+ foreach(lc, agg_costs->distinctAggrefs)
+ {
+ Aggref *aggref =
lfirst_node(Aggref, lc);
+
+ if (!aggref->aggfilter)
+ {
+ has_unfiltered_agg =
true;
+ break;
+ }
+ }
+ if (!has_unfiltered_agg)
+ break;
+ }
+
fetch_multi_dqas_info(root, cheapest_path,
&ctx, &info);
/*
* GPDB_14_MERGE_FIXME: We have done some copy
job in
@@ -530,7 +561,6 @@ cdb_create_multistage_grouping_paths(PlannerInfo *root,
* removing the origin plan's aggfilter can
work around
* this problem. We'll look at it again later.
*/
- ListCell *lc;
foreach(lc, root->agginfos)
{
AggInfo *agginfo = (AggInfo *)
lfirst(lc);
@@ -1102,7 +1132,7 @@ add_first_stage_group_agg_path(PlannerInfo *root,
ctx->agg_partial_costs);
add_path(ctx->partial_rel, first_stage_agg_path, root);
}
- else if (ctx->hasAggs || ctx->groupClause || ctx->hasDistinctOn)
+ else if (ctx->hasAggs || ctx->parseGroupClause || ctx->hasDistinctOn)
{
add_path(ctx->partial_rel,
(Path *) create_agg_path(root,
@@ -1141,10 +1171,19 @@ add_second_stage_group_agg_path(PlannerInfo *root,
CdbPathLocus singleQE_locus;
CdbPathLocus group_locus;
bool need_redistribute;
+ AggStrategy aggstrategy;
/* The input should be distributed, otherwise no point in a two-stage
Agg. */
Assert(CdbPathLocus_IsPartitioned(initial_agg_path->locus));
+ /*
+ * GROUP BY must return no rows for empty input, even if all grouping
+ * keys were removed as redundant. Use AGG_SORTED to preserve this
+ * behavior; AGG_PLAIN would produce one row.
+ */
+ aggstrategy = (ctx->parseGroupClause != NIL ||
+ ctx->final_groupClause != NIL) ? AGG_SORTED
: AGG_PLAIN;
+
group_locus = choose_grouping_locus(root,
initial_agg_path,
ctx->final_group_tles,
@@ -1189,7 +1228,7 @@ add_second_stage_group_agg_path(PlannerInfo *root,
output_rel,
path,
ctx->target,
-
(ctx->final_groupClause ? AGG_SORTED : AGG_PLAIN),
+
aggstrategy,
ctx->hasAggs ? AGGSPLIT_FINAL_DESERIAL : AGGSPLIT_SIMPLE,
false, /* streaming */
ctx->final_groupClause,
@@ -1227,7 +1266,7 @@ add_second_stage_group_agg_path(PlannerInfo *root,
output_rel,
path,
ctx->target,
-
(ctx->final_groupClause ? AGG_SORTED : AGG_PLAIN),
+ aggstrategy,
ctx->hasAggs ?
AGGSPLIT_FINAL_DESERIAL : AGGSPLIT_SIMPLE,
false, /*
streaming */
ctx->final_groupClause,
@@ -1440,6 +1479,7 @@ static void
add_single_mixed_dqa_hash_agg_path(PlannerInfo *root,
CdbPathLocus distinct_locus;
bool distinct_need_redistribute;
CdbPathLocus singleQE_locus;
+ AggStrategy aggstrategy;
if (!gp_enable_agg_distinct)
return;
@@ -1447,6 +1487,8 @@ static void
add_single_mixed_dqa_hash_agg_path(PlannerInfo *root,
if (ctx->groupClause)
return;
+ aggstrategy = ctx->parseGroupClause ? AGG_SORTED : AGG_PLAIN;
+
/*
* If subpath is projection capable, we do not want to generate a
* projection plan. The reason is that the projection plan does not
@@ -1471,7 +1513,7 @@ static void
add_single_mixed_dqa_hash_agg_path(PlannerInfo *root,
output_rel,
path,
ctx->partial_grouping_target,
-
AGG_PLAIN,
+
aggstrategy,
AGGSPLIT_INITIAL_SERIAL,
false,
/* streaming */
ctx->groupClause,
@@ -1487,7 +1529,7 @@ static void
add_single_mixed_dqa_hash_agg_path(PlannerInfo *root,
output_rel,
path,
ctx->target,
-
AGG_PLAIN,
+
aggstrategy,
AGGSPLIT_FINAL_DESERIAL,
false,
/* streaming */
ctx->groupClause,
@@ -1517,10 +1559,16 @@ add_single_dqa_hash_agg_path(PlannerInfo *root,
bool group_need_redistribute;
CdbPathLocus distinct_locus;
bool distinct_need_redistribute;
+ AggStrategy aggstrategy;
if (!gp_enable_agg_distinct)
return;
+ if (ctx->groupClause)
+ aggstrategy = AGG_HASHED;
+ else
+ aggstrategy = ctx->parseGroupClause ? AGG_SORTED : AGG_PLAIN;
+
/*
* If subpath is projection capable, we do not want to generate a
* projection plan. The reason is that the projection plan does not
@@ -1598,7 +1646,7 @@ add_single_dqa_hash_agg_path(PlannerInfo *root,
output_rel,
path,
ctx->target,
-
ctx->groupClause ? AGG_HASHED : AGG_PLAIN,
+
aggstrategy,
AGGSPLIT_DEDUPLICATED,
false, /* streaming */
ctx->groupClause,
@@ -1630,7 +1678,7 @@ add_single_dqa_hash_agg_path(PlannerInfo *root,
output_rel,
path,
strip_aggdistinct(ctx->partial_grouping_target),
-
ctx->groupClause ? AGG_HASHED : AGG_PLAIN,
+
aggstrategy,
AGGSPLIT_INITIAL_SERIAL | AGGSPLITOP_DEDUPLICATED,
false, /* streaming */
ctx->groupClause,
@@ -1645,7 +1693,7 @@ add_single_dqa_hash_agg_path(PlannerInfo *root,
output_rel,
path,
ctx->target,
-
ctx->groupClause ? AGG_HASHED : AGG_PLAIN,
+
aggstrategy,
AGGSPLIT_FINAL_DESERIAL | AGGSPLITOP_DEDUPLICATED,
false, /* streaming */
ctx->groupClause,
@@ -1714,7 +1762,7 @@ add_single_dqa_hash_agg_path(PlannerInfo *root,
output_rel,
path,
ctx->target,
-
ctx->groupClause ? AGG_HASHED : AGG_PLAIN,
+
aggstrategy,
AGGSPLIT_DEDUPLICATED,
false, /* streaming */
ctx->groupClause,
@@ -1767,7 +1815,7 @@ add_single_dqa_hash_agg_path(PlannerInfo *root,
output_rel,
path,
strip_aggdistinct(ctx->partial_grouping_target),
-
ctx->groupClause ? AGG_HASHED : AGG_PLAIN,
+
aggstrategy,
AGGSPLIT_INITIAL_SERIAL | AGGSPLITOP_DEDUPLICATED,
false, /* streaming */
ctx->groupClause,
@@ -1781,7 +1829,7 @@ add_single_dqa_hash_agg_path(PlannerInfo *root,
output_rel,
path,
ctx->target,
-
ctx->groupClause ? AGG_HASHED : AGG_PLAIN,
+
aggstrategy,
AGGSPLIT_FINAL_DESERIAL | AGGSPLITOP_DEDUPLICATED,
false, /* streaming */
ctx->groupClause,
@@ -1899,7 +1947,7 @@ add_multi_dqas_hash_agg_path(PlannerInfo *root,
path = cdbpath_create_motion_path(root, path, NIL, false,
distinct_locus);
- AggStrategy split = AGG_PLAIN;
+ AggStrategy split = ctx->parseGroupClause ? AGG_SORTED : AGG_PLAIN;
unsigned long DEDUPLICATED_FLAG = 0;
PathTarget *partial_target = info->partial_target;
double input_rows = path->rows;
diff --git a/src/test/regress/expected/bfv_aggregate.out
b/src/test/regress/expected/bfv_aggregate.out
index d192e787b48..5289633b0cf 100644
--- a/src/test/regress/expected/bfv_aggregate.out
+++ b/src/test/regress/expected/bfv_aggregate.out
@@ -1777,7 +1777,7 @@ explain (costs off)
select 1, sum(col1) from group_by_const group by 1;
QUERY PLAN
------------------------------------------------
- Finalize Aggregate
+ Finalize GroupAggregate
-> Gather Motion 3:1 (slice1; segments: 3)
-> Partial GroupAggregate
-> Seq Scan on group_by_const
diff --git a/src/test/regress/expected/gp_group_by_constant.out
b/src/test/regress/expected/gp_group_by_constant.out
new file mode 100644
index 00000000000..148f94ff76a
--- /dev/null
+++ b/src/test/regress/expected/gp_group_by_constant.out
@@ -0,0 +1,295 @@
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied. See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+BEGIN;
+SET LOCAL optimizer = off;
+SET LOCAL enable_parallel = off;
+SET LOCAL gp_enable_multiphase_agg = on;
+CREATE TABLE group_by_constant_empty (n int, c0 boolean)
+ WITH (parallel_workers = 2) DISTRIBUTED BY (n);
+CREATE TABLE group_by_constant_data (n int, c0 boolean)
+ WITH (parallel_workers = 2) DISTRIBUTED BY (n);
+INSERT INTO group_by_constant_data
+ SELECT n, false FROM generate_series(1, 10000) n;
+ANALYZE group_by_constant_data;
+SELECT count(DISTINCT gp_segment_id) > 1 AS multiple_segments
+ FROM group_by_constant_data;
+ multiple_segments
+-------------------
+ t
+(1 row)
+
+-- Removing all physical grouping keys must not create a group on empty input.
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM group_by_constant_empty GROUP BY 'x'::text;
+ QUERY PLAN
+-------------------------------------------------------
+ Finalize GroupAggregate
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Partial GroupAggregate
+ -> Seq Scan on group_by_constant_empty
+ Optimizer: Postgres query optimizer
+(5 rows)
+
+SELECT count(*) FROM group_by_constant_empty GROUP BY 'x'::text;
+ count
+-------
+(0 rows)
+
+SELECT 1 FROM group_by_constant_empty
+ GROUP BY (0.25)::money HAVING count(*) = 0;
+ ?column?
+----------
+(0 rows)
+
+-- Input can also become empty after a WHERE clause on a nonempty table.
+SELECT count(*) FROM group_by_constant_data WHERE n % 10000 = -1
+ GROUP BY 'x'::text;
+ count
+-------
+(0 rows)
+
+-- The grouping key is not a literal, but WHERE n % 2 = 0 makes it constant.
+SELECT count(*) FROM group_by_constant_empty
+ WHERE n % 2 = 0 GROUP BY n % 2;
+ count
+-------
+(0 rows)
+
+-- All segments contribute; then only one segment contributes.
+SELECT count(*), sum(n) FROM group_by_constant_data GROUP BY 'x'::text;
+ count | sum
+-------+----------
+ 10000 | 50005000
+(1 row)
+
+SELECT count(*), sum(n) FROM group_by_constant_data WHERE n % 10000 = 1
+ GROUP BY 'x'::text;
+ count | sum
+-------+-----
+ 1 | 1
+(1 row)
+
+-- Global aggregates and empty grouping sets still produce a row.
+SELECT count(*) FROM group_by_constant_empty;
+ count
+-------
+ 0
+(1 row)
+
+SELECT count(*) FROM group_by_constant_empty GROUP BY GROUPING SETS ((), ());
+ count
+-------
+ 0
+ 0
+(2 rows)
+
+SELECT 'x'::text FROM group_by_constant_empty GROUP BY 'x'::text;
+ text
+------
+(0 rows)
+
+SELECT DISTINCT 'x'::text FROM group_by_constant_empty;
+ text
+------
+(0 rows)
+
+SELECT 'x'::text FROM group_by_constant_data GROUP BY 'x'::text;
+ text
+------
+ x
+(1 row)
+
+-- DISTINCT arguments may require redistribution before aggregation.
+EXPLAIN (COSTS OFF)
+SELECT count(DISTINCT n) FROM group_by_constant_empty GROUP BY 'x'::text;
+ QUERY PLAN
+-------------------------------------------------------------
+ Finalize GroupAggregate
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Partial GroupAggregate
+ -> HashAggregate
+ Group Key: n
+ -> Seq Scan on group_by_constant_empty
+ Optimizer: Postgres query optimizer
+(7 rows)
+
+SELECT count(DISTINCT n) FROM group_by_constant_empty GROUP BY 'x'::text;
+ count
+-------
+(0 rows)
+
+SELECT count(DISTINCT c0) FROM group_by_constant_empty GROUP BY 'x'::text;
+ count
+-------
+(0 rows)
+
+SELECT count(DISTINCT c0), sum(n) FROM group_by_constant_empty
+ GROUP BY 'x'::text;
+ count | sum
+-------+-----
+(0 rows)
+
+SELECT count(DISTINCT n), count(DISTINCT c0) FROM group_by_constant_empty
+ GROUP BY 'x'::text;
+ count | count
+-------+-------
+(0 rows)
+
+SELECT count(DISTINCT n) FROM group_by_constant_data GROUP BY 'x'::text;
+ count
+-------
+ 10000
+(1 row)
+
+SELECT count(DISTINCT c0) FROM group_by_constant_data GROUP BY 'x'::text;
+ count
+-------
+ 1
+(1 row)
+
+SELECT count(DISTINCT c0), sum(n) FROM group_by_constant_data
+ GROUP BY 'x'::text;
+ count | sum
+-------+----------
+ 1 | 50005000
+(1 row)
+
+SELECT count(DISTINCT n), count(DISTINCT c0) FROM group_by_constant_data
+ GROUP BY 'x'::text;
+ count | count
+-------+-------
+ 10000 | 1
+(1 row)
+
+SELECT count(DISTINCT n) FROM group_by_constant_empty;
+ count
+-------
+ 0
+(1 row)
+
+-- Filtering all aggregate arguments must not remove an existing group.
+SELECT count(DISTINCT n) FILTER (WHERE n < 0),
+ count(DISTINCT c0) FILTER (WHERE n < 0)
+ FROM group_by_constant_data GROUP BY 'x'::text;
+ count | count
+-------+-------
+ 0 | 0
+(1 row)
+
+-- TupleSplit is still used when one DQA has no FILTER.
+EXPLAIN (COSTS OFF)
+SELECT count(DISTINCT n) FILTER (WHERE n < 0), count(DISTINCT c0)
+ FROM group_by_constant_data GROUP BY 'x'::text;
+ QUERY PLAN
+--------------------------------------------------------------------------------
+ Finalize GroupAggregate
+ -> Gather Motion 3:1 (slice1; segments: 3)
+ -> Partial GroupAggregate
+ -> Redistribute Motion 3:3 (slice2; segments: 3)
+ Hash Key: n, c0, (AggExprId)
+ -> Streaming HashAggregate
+ Group Key: AggExprId, n, c0
+ -> TupleSplit
+ Split by Col: (n) FILTER (WHERE (n < 0)), (c0)
+ -> Seq Scan on group_by_constant_data
+ Optimizer: Postgres query optimizer
+(11 rows)
+
+SELECT count(DISTINCT n) FILTER (WHERE n < 0), count(DISTINCT c0)
+ FROM group_by_constant_data GROUP BY 'x'::text;
+ count | count
+-------+-------
+ 0 | 1
+(1 row)
+
+-- Compare with single-phase aggregation and ORCA.
+SET LOCAL gp_enable_multiphase_agg = off;
+SELECT count(*) FROM group_by_constant_empty GROUP BY 'x'::text;
+ count
+-------
+(0 rows)
+
+SET LOCAL optimizer = on;
+SET LOCAL gp_enable_multiphase_agg = on;
+SELECT count(*) FROM group_by_constant_empty GROUP BY 'x'::text;
+ count
+-------
+(0 rows)
+
+-- Exercise worker partial aggregation as well as the final MPP stage.
+SET LOCAL optimizer = off;
+SET LOCAL enable_parallel = on;
+SET LOCAL min_parallel_table_scan_size = 0;
+SET LOCAL max_parallel_workers_per_gather = 2;
+SET LOCAL parallel_setup_cost = 0;
+SET LOCAL parallel_tuple_cost = 0;
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM group_by_constant_data WHERE n % 10000 = -1
+ GROUP BY 'x'::text;
+ QUERY PLAN
+---------------------------------------------------------------
+ Finalize GroupAggregate
+ -> Gather Motion 6:1 (slice1; segments: 6)
+ -> Partial GroupAggregate
+ -> Parallel Seq Scan on group_by_constant_data
+ Filter: ((n % 10000) = '-1'::integer)
+ Optimizer: Postgres query optimizer
+(6 rows)
+
+SELECT count(*) FROM group_by_constant_data WHERE n % 10000 = -1
+ GROUP BY 'x'::text;
+ count
+-------
+(0 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM group_by_constant_empty
+ WHERE n % 2 = 0 GROUP BY n % 2;
+ QUERY PLAN
+----------------------------------------------------------------
+ Finalize GroupAggregate
+ -> Gather Motion 6:1 (slice1; segments: 6)
+ -> Partial GroupAggregate
+ -> Parallel Seq Scan on group_by_constant_empty
+ Filter: ((n % 2) = 0)
+ Optimizer: Postgres query optimizer
+(6 rows)
+
+SELECT count(*) FROM group_by_constant_empty
+ WHERE n % 2 = 0 GROUP BY n % 2;
+ count
+-------
+(0 rows)
+
+SELECT count(*), sum(n) FROM group_by_constant_data GROUP BY 'x'::text;
+ count | sum
+-------+----------
+ 10000 | 50005000
+(1 row)
+
+SELECT 'x'::text FROM group_by_constant_empty GROUP BY 'x'::text;
+ text
+------
+(0 rows)
+
+SELECT 'x'::text FROM group_by_constant_data GROUP BY 'x'::text;
+ text
+------
+ x
+(1 row)
+
+COMMIT;
diff --git a/src/test/regress/greenplum_schedule
b/src/test/regress/greenplum_schedule
index 84e8766844b..5e2a83ee5a5 100755
--- a/src/test/regress/greenplum_schedule
+++ b/src/test/regress/greenplum_schedule
@@ -51,6 +51,7 @@ test: instr_in_shmem
test: createdb
test: gp_aggregates gp_aggregates_costs gp_metadata variadic_parameters
default_parameters function_extensions spi gp_xml shared_scan update_gp
triggers_gp returning_gp resource_queue_with_rule gp_types gp_index cluster_gp
combocid_gp gp_sort gp_prepared_xacts gp_backend_info gp_locale
+test: gp_group_by_constant
test: foreign_key_gp
test: spi_processed64bit
test: gp_tablespace_with_faults
diff --git a/src/test/regress/sql/gp_group_by_constant.sql
b/src/test/regress/sql/gp_group_by_constant.sql
new file mode 100644
index 00000000000..5aa5b15c371
--- /dev/null
+++ b/src/test/regress/sql/gp_group_by_constant.sql
@@ -0,0 +1,114 @@
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied. See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+
+BEGIN;
+SET LOCAL optimizer = off;
+SET LOCAL enable_parallel = off;
+SET LOCAL gp_enable_multiphase_agg = on;
+
+CREATE TABLE group_by_constant_empty (n int, c0 boolean)
+ WITH (parallel_workers = 2) DISTRIBUTED BY (n);
+CREATE TABLE group_by_constant_data (n int, c0 boolean)
+ WITH (parallel_workers = 2) DISTRIBUTED BY (n);
+INSERT INTO group_by_constant_data
+ SELECT n, false FROM generate_series(1, 10000) n;
+ANALYZE group_by_constant_data;
+SELECT count(DISTINCT gp_segment_id) > 1 AS multiple_segments
+ FROM group_by_constant_data;
+
+-- Removing all physical grouping keys must not create a group on empty input.
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM group_by_constant_empty GROUP BY 'x'::text;
+SELECT count(*) FROM group_by_constant_empty GROUP BY 'x'::text;
+SELECT 1 FROM group_by_constant_empty
+ GROUP BY (0.25)::money HAVING count(*) = 0;
+-- Input can also become empty after a WHERE clause on a nonempty table.
+SELECT count(*) FROM group_by_constant_data WHERE n % 10000 = -1
+ GROUP BY 'x'::text;
+-- The grouping key is not a literal, but WHERE n % 2 = 0 makes it constant.
+SELECT count(*) FROM group_by_constant_empty
+ WHERE n % 2 = 0 GROUP BY n % 2;
+
+-- All segments contribute; then only one segment contributes.
+SELECT count(*), sum(n) FROM group_by_constant_data GROUP BY 'x'::text;
+SELECT count(*), sum(n) FROM group_by_constant_data WHERE n % 10000 = 1
+ GROUP BY 'x'::text;
+
+-- Global aggregates and empty grouping sets still produce a row.
+SELECT count(*) FROM group_by_constant_empty;
+SELECT count(*) FROM group_by_constant_empty GROUP BY GROUPING SETS ((), ());
+
+SELECT 'x'::text FROM group_by_constant_empty GROUP BY 'x'::text;
+SELECT DISTINCT 'x'::text FROM group_by_constant_empty;
+SELECT 'x'::text FROM group_by_constant_data GROUP BY 'x'::text;
+
+-- DISTINCT arguments may require redistribution before aggregation.
+EXPLAIN (COSTS OFF)
+SELECT count(DISTINCT n) FROM group_by_constant_empty GROUP BY 'x'::text;
+SELECT count(DISTINCT n) FROM group_by_constant_empty GROUP BY 'x'::text;
+SELECT count(DISTINCT c0) FROM group_by_constant_empty GROUP BY 'x'::text;
+SELECT count(DISTINCT c0), sum(n) FROM group_by_constant_empty
+ GROUP BY 'x'::text;
+SELECT count(DISTINCT n), count(DISTINCT c0) FROM group_by_constant_empty
+ GROUP BY 'x'::text;
+SELECT count(DISTINCT n) FROM group_by_constant_data GROUP BY 'x'::text;
+SELECT count(DISTINCT c0) FROM group_by_constant_data GROUP BY 'x'::text;
+SELECT count(DISTINCT c0), sum(n) FROM group_by_constant_data
+ GROUP BY 'x'::text;
+SELECT count(DISTINCT n), count(DISTINCT c0) FROM group_by_constant_data
+ GROUP BY 'x'::text;
+SELECT count(DISTINCT n) FROM group_by_constant_empty;
+
+-- Filtering all aggregate arguments must not remove an existing group.
+SELECT count(DISTINCT n) FILTER (WHERE n < 0),
+ count(DISTINCT c0) FILTER (WHERE n < 0)
+ FROM group_by_constant_data GROUP BY 'x'::text;
+-- TupleSplit is still used when one DQA has no FILTER.
+EXPLAIN (COSTS OFF)
+SELECT count(DISTINCT n) FILTER (WHERE n < 0), count(DISTINCT c0)
+ FROM group_by_constant_data GROUP BY 'x'::text;
+SELECT count(DISTINCT n) FILTER (WHERE n < 0), count(DISTINCT c0)
+ FROM group_by_constant_data GROUP BY 'x'::text;
+
+-- Compare with single-phase aggregation and ORCA.
+SET LOCAL gp_enable_multiphase_agg = off;
+SELECT count(*) FROM group_by_constant_empty GROUP BY 'x'::text;
+SET LOCAL optimizer = on;
+SET LOCAL gp_enable_multiphase_agg = on;
+SELECT count(*) FROM group_by_constant_empty GROUP BY 'x'::text;
+
+-- Exercise worker partial aggregation as well as the final MPP stage.
+SET LOCAL optimizer = off;
+SET LOCAL enable_parallel = on;
+SET LOCAL min_parallel_table_scan_size = 0;
+SET LOCAL max_parallel_workers_per_gather = 2;
+SET LOCAL parallel_setup_cost = 0;
+SET LOCAL parallel_tuple_cost = 0;
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM group_by_constant_data WHERE n % 10000 = -1
+ GROUP BY 'x'::text;
+SELECT count(*) FROM group_by_constant_data WHERE n % 10000 = -1
+ GROUP BY 'x'::text;
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM group_by_constant_empty
+ WHERE n % 2 = 0 GROUP BY n % 2;
+SELECT count(*) FROM group_by_constant_empty
+ WHERE n % 2 = 0 GROUP BY n % 2;
+SELECT count(*), sum(n) FROM group_by_constant_data GROUP BY 'x'::text;
+SELECT 'x'::text FROM group_by_constant_empty GROUP BY 'x'::text;
+SELECT 'x'::text FROM group_by_constant_data GROUP BY 'x'::text;
+COMMIT;
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
