This is an automated email from the ASF dual-hosted git repository.
yjhjstz 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 ce842199735 ORCA: keep HAVING above a scalar GbAgg in
CNormalizer::FPushable
ce842199735 is described below
commit ce8421997352269c2e6d950225844a7539daaae2
Author: Jianghua Yang <[email protected]>
AuthorDate: Wed Apr 22 22:37:45 2026 +0800
ORCA: keep HAVING above a scalar GbAgg in CNormalizer::FPushable
A scalar (plain) aggregate with no grouping columns always emits
exactly one row regardless of input cardinality. Predicates above it
(from a HAVING clause) filter that output row, so they cannot be
moved onto the aggregate's input without changing semantics:
SELECT count(*) FROM t HAVING false -- 0 rows
SELECT count(*) FROM t WHERE false -- 1 row (count=0)
CNormalizer::FPushable previously only blocked pushing volatile
predicates below a GbAgg. Any other predicate -- including a constant
false -- was considered pushable because its used-column set was
trivially contained in the aggregate's output columns. The normalizer
then routed the Select's predicate through the GbAgg and down into
its logical child, dropping HAVING semantics for scalar aggregates.
---
.../gporca/libgpopt/src/operators/CNormalizer.cpp | 13 +++++++++++++
src/test/regress/expected/groupingsets.out | 19 +++++++++++++++++++
src/test/regress/expected/groupingsets_optimizer.out | 16 ++++++++++++++++
src/test/regress/sql/groupingsets.sql | 6 ++++++
4 files changed, 54 insertions(+)
diff --git a/src/backend/gporca/libgpopt/src/operators/CNormalizer.cpp
b/src/backend/gporca/libgpopt/src/operators/CNormalizer.cpp
index 97fb97a1409..38c8a93a9ab 100644
--- a/src/backend/gporca/libgpopt/src/operators/CNormalizer.cpp
+++ b/src/backend/gporca/libgpopt/src/operators/CNormalizer.cpp
@@ -18,6 +18,7 @@
#include "gpopt/base/CUtils.h"
#include "gpopt/operators/CLogical.h"
#include "gpopt/operators/CLogicalConstTableGet.h"
+#include "gpopt/operators/CLogicalGbAgg.h"
#include "gpopt/operators/CLogicalInnerJoin.h"
#include "gpopt/operators/CLogicalLeftOuterCorrelatedApply.h"
#include "gpopt/operators/CLogicalLeftOuterJoin.h"
@@ -126,6 +127,18 @@ CNormalizer::FPushable(CExpression *pexprLogical,
CExpression *pexprPred)
return false;
}
+ // do not push predicates below a scalar (plain) aggregate, i.e. one
with
+ // no grouping columns. A scalar aggregate produces exactly one output
row
+ // regardless of input cardinality, so a predicate above it (HAVING
clause)
+ // must be evaluated against that output row, not the aggregate's input.
+ // Pushing e.g. "HAVING false" below would leave the agg emitting one
row
+ // (e.g. count = 0) instead of zero rows.
+ if (COperator::EopLogicalGbAgg == pexprLogical->Pop()->Eopid() &&
+ 0 ==
CLogicalGbAgg::PopConvert(pexprLogical->Pop())->Pdrgpcr()->Size())
+ {
+ return false;
+ }
+
CColRefSet *pcrsUsed = pexprPred->DeriveUsedColumns();
CColRefSet *pcrsOutput = pexprLogical->DeriveOutputColumns();
diff --git a/src/test/regress/expected/groupingsets.out
b/src/test/regress/expected/groupingsets.out
index f7eefa2c8eb..5222cc22b70 100644
--- a/src/test/regress/expected/groupingsets.out
+++ b/src/test/regress/expected/groupingsets.out
@@ -958,6 +958,25 @@ explain (costs off)
Optimizer: Postgres query optimizer
(13 rows)
+-- HAVING with constant-false predicate on an empty grouping set must emit
+-- zero rows, not the default scalar-aggregate row.
+select count(*) from gstest2 group by grouping sets (()) having false;
+ count
+-------
+(0 rows)
+
+explain (costs off)
+ select count(*) from gstest2 group by grouping sets (()) having false;
+ QUERY PLAN
+-------------------------------------
+ Aggregate
+ Group Key: ()
+ Filter: false
+ -> Result
+ One-Time Filter: false
+ Optimizer: Postgres query optimizer
+(6 rows)
+
-- HAVING with GROUPING queries
select ten, grouping(ten) from onek
group by grouping sets(ten) having grouping(ten) >= 0
diff --git a/src/test/regress/expected/groupingsets_optimizer.out
b/src/test/regress/expected/groupingsets_optimizer.out
index 3718069c36c..a07017eca32 100644
--- a/src/test/regress/expected/groupingsets_optimizer.out
+++ b/src/test/regress/expected/groupingsets_optimizer.out
@@ -975,6 +975,22 @@ explain (costs off)
Optimizer: GPORCA
(13 rows)
+-- HAVING with constant-false predicate on an empty grouping set must emit
+-- zero rows, not the default scalar-aggregate row.
+select count(*) from gstest2 group by grouping sets (()) having false;
+ count
+-------
+(0 rows)
+
+explain (costs off)
+ select count(*) from gstest2 group by grouping sets (()) having false;
+ QUERY PLAN
+--------------------------
+ Result
+ One-Time Filter: false
+ Optimizer: GPORCA
+(3 rows)
+
-- HAVING with GROUPING queries
select ten, grouping(ten) from onek
group by grouping sets(ten) having grouping(ten) >= 0
diff --git a/src/test/regress/sql/groupingsets.sql
b/src/test/regress/sql/groupingsets.sql
index 6907b5f1f55..851a1eea6bb 100644
--- a/src/test/regress/sql/groupingsets.sql
+++ b/src/test/regress/sql/groupingsets.sql
@@ -353,6 +353,12 @@ explain (costs off)
select v.c, (select count(*) from gstest2 group by () having v.c)
from (values (false),(true)) v(c) order by v.c;
+-- HAVING with constant-false predicate on an empty grouping set must emit
+-- zero rows, not the default scalar-aggregate row.
+select count(*) from gstest2 group by grouping sets (()) having false;
+explain (costs off)
+ select count(*) from gstest2 group by grouping sets (()) having false;
+
-- HAVING with GROUPING queries
select ten, grouping(ten) from onek
group by grouping sets(ten) having grouping(ten) >= 0
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
