postgres.git / summary / log / commit / refs

commit    0c6e0437110dd6ceb14fdca5b323bc1b058a13cf
Author:   Richard Guo <rguo@postgresql.org>
Date:     Thu Oct 29 06:32:40 2026 +0000
First released in: 19.0

    Fix eager aggregation below volatile join clauses
    
    Eager aggregation could push a partial aggregation down to a relation
    that is later joined using a join clause containing volatile
    functions.  The clause is then evaluated once per group rather than
    once per input row, which changes the number of times the function is
    executed.
    
    To fix, refuse to build grouped paths for a relation if any of its
    join clauses contains volatile functions.
    
    Back-patch to v19, where eager aggregation was introduced.
    
    Author: shihao zhong <zhong950419@gmail.com>
    Reviewed-by: Manu <manuelreyesbravo@gmail.com>
    Discussion: https://postgr.es/m/CAGRkXqSc_qtpuPm34qoS1z0LVx4+Hc+JcJkbDEVC=z2ai4VQ1w@mail.gmail.com
    Backpatch-through: 19


src/backend/optimizer/util/relnode.c | 13 +++++++++++++ src/test/regress/expected/eager_aggregate.out | 20 ++++++++++++++++++++ src/test/regress/sql/eager_aggregate.sql | 7 +++++++ 3 files changed, 40 insertions(+) diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c index e7835de7377..aa7d92ac6df 100644 --- a/src/backend/optimizer/util/relnode.c +++ b/src/backend/optimizer/util/relnode.c @@ -2988,6 +2988,19 @@ eager_aggregation_possible_for_relation(PlannerInfo *root, RelOptInfo *rel) return false; } + /* + * We cannot push a partial aggregation down below a join clause that + * contains volatile functions, because the clause would then be evaluated + * once per group rather than once per input row. + */ + foreach(lc, rel->joininfo) + { + RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc); + + if (contain_volatile_functions((Node *) rinfo)) + return false; + } + /* * For now we don't try to support PlaceHolderVars. */ diff --git a/src/test/regress/expected/eager_aggregate.out b/src/test/regress/expected/eager_aggregate.out index b420a125e1c..bdda7da68f0 100644 --- a/src/test/regress/expected/eager_aggregate.out +++ b/src/test/regress/expected/eager_aggregate.out @@ -466,6 +466,26 @@ GROUP BY t1.a ORDER BY t1.a; -> Seq Scan on eager_agg_t1 t1 (9 rows) +-- Ensure eager aggregation is not applied below a volatile join clause +EXPLAIN (COSTS OFF) +SELECT t1.a, avg(t2.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b AND t1.c > t2.b * random() +GROUP BY t1.a ORDER BY t1.a; + QUERY PLAN +--------------------------------------------------------------------------- + GroupAggregate + Group Key: t1.a + -> Sort + Sort Key: t1.a + -> Hash Join + Hash Cond: (t2.b = t1.b) + Join Filter: (t1.c > ((t2.b)::double precision * random())) + -> Seq Scan on eager_agg_t2 t2 + -> Hash + -> Seq Scan on eager_agg_t1 t1 +(10 rows) + -- Eager aggregation must not push a partial aggregate onto the inner side of a -- SEMI or ANTI join EXPLAIN (VERBOSE, COSTS OFF) diff --git a/src/test/regress/sql/eager_aggregate.sql b/src/test/regress/sql/eager_aggregate.sql index 7e3623aa6fd..ab3592b67dd 100644 --- a/src/test/regress/sql/eager_aggregate.sql +++ b/src/test/regress/sql/eager_aggregate.sql @@ -177,6 +177,13 @@ SELECT t1.a, avg(t2.c) FILTER (WHERE random() > 0.5) JOIN eager_agg_t2 t2 ON t1.b = t2.b GROUP BY t1.a ORDER BY t1.a; +-- Ensure eager aggregation is not applied below a volatile join clause +EXPLAIN (COSTS OFF) +SELECT t1.a, avg(t2.c) + FROM eager_agg_t1 t1 + JOIN eager_agg_t2 t2 ON t1.b = t2.b AND t1.c > t2.b * random() +GROUP BY t1.a ORDER BY t1.a; + -- Eager aggregation must not push a partial aggregate onto the inner side of a -- SEMI or ANTI join EXPLAIN (VERBOSE, COSTS OFF) [parent: 960626b9367f]