Skip to content

Commit c4f5a9e

Browse files
adriangbclaudenuno-faria
authored
fix: do not push filters on volatile group keys below Aggregate (apache#25416)
## Which issue does this PR close? - Closes apache#25415. ## Rationale for this change A filter on a volatile `GROUP BY` key returns rows that do not satisfy the filter: ```sql CREATE TABLE v AS SELECT value AS a FROM generate_series(1, 10000); SELECT k, c FROM (SELECT random() < 0.5 AS k, count(*) AS c FROM v GROUP BY random() < 0.5) WHERE k; ``` ``` +-------+------+ | k | c | +-------+------+ | false | 2525 | <-- does not satisfy `WHERE k` | true | 2494 | +-------+------+ ``` `PushDownFilter` moves the predicate below the aggregate and replaces `k` with `random() < 0.5`. The filter then calculates `random()` again, and it gets a different value than the one that the aggregate uses for grouping. PostgreSQL keeps the filter above the aggregate and returns only the `true` row. ## What changes are included in this PR? In the `LogicalPlan::Aggregate` branch of `PushDownFilter`, a predicate is pushed below the aggregate only if it references non-volatile group keys. Predicates on volatile group keys stay above the aggregate. The `Projection` branch already does the same for volatile expressions. ## What is the testing strategy for this PR? - Unit test `test_filter_on_volatile_group_key_not_pushed_below_aggregate`: a non-volatile key predicate is still pushed, and the volatile key predicate stays above. The test fails without the fix. - `push_down_filter_regression.slt`: `EXPLAIN` plus three result checks (`WHERE k`, `WHERE NOT k` with `GROUP BY k`, and `WHERE k OR NOT k`, which must count all 10000 rows). ## Are there any user-facing changes? Queries that filter on a volatile grouping key now return correct results. Plans for non-volatile keys do not change. 🤖 Generated with [Claude Code](https://claude.com/claude-code) --------- Co-authored-by: Claude Opus 5 <[email protected]> Co-authored-by: Nuno Faria <[email protected]>
1 parent 3ab72e6 commit c4f5a9e

2 files changed

Lines changed: 72 additions & 1 deletion

File tree

‎datafusion/optimizer/src/push_down_filter.rs‎

Lines changed: 38 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -997,7 +997,16 @@ impl OptimizerRule for PushDownFilter {
997997
}
998998
LogicalPlan::Aggregate(mut agg) => {
999999
// We can push down Predicate which in groupby_expr.
1000-
let group_expr_columns = expr_columns(&agg.group_expr);
1000+
// Volatile group keys are excluded: below the aggregate, the
1001+
// predicate would evaluate the key again and see a different
1002+
// value than the one used for grouping.
1003+
let non_volatile_group_exprs: Vec<Expr> = agg
1004+
.group_expr
1005+
.iter()
1006+
.filter(|expr| !expr.is_volatile())
1007+
.cloned()
1008+
.collect();
1009+
let group_expr_columns = expr_columns(&non_volatile_group_exprs);
10011010

10021011
// As for plan Filter: Column(a+b) > 0 -- Agg: groupby:[Column(a)+Column(b)]
10031012
// After push, we need to replace `a+b` with Column(a)+Column(b)
@@ -4091,6 +4100,34 @@ mod tests {
40914100
)
40924101
}
40934102

4103+
#[test]
4104+
fn test_filter_on_volatile_group_key_not_pushed_below_aggregate() -> Result<()> {
4105+
// SELECT r, sum(b) FROM test1 GROUP BY a, TestScalarUDF() + 1 AS r HAVING a > 5 AND r > 0.5
4106+
let table_scan = test_table_scan_with_name("test1")?;
4107+
let fun = ScalarUDF::new_from_impl(TestScalarUDF {
4108+
signature: Signature::exact(vec![], Volatility::Volatile),
4109+
});
4110+
let expr = Expr::ScalarFunction(ScalarFunction::new_udf(Arc::new(fun), vec![]));
4111+
4112+
let plan = LogicalPlanBuilder::from(table_scan)
4113+
.aggregate(
4114+
vec![col("a"), add(expr, lit(1)).alias("r")],
4115+
vec![sum(col("b"))],
4116+
)?
4117+
.filter(col("a").gt(lit(5)).and(col("r").gt(lit(0.5))))?
4118+
.build()?;
4119+
4120+
// `a > 5` is pushed below the aggregate, `r > 0.5` must stay above it
4121+
assert_optimized_plan_equal!(
4122+
plan,
4123+
@r"
4124+
Filter: r > Float64(0.5)
4125+
Aggregate: groupBy=[[test1.a, TestScalarUDF() + Int32(1) AS r]], aggr=[[sum(test1.b)]]
4126+
TableScan: test1, full_filters=[test1.a > Int32(5)]
4127+
"
4128+
)
4129+
}
4130+
40944131
#[test]
40954132
fn test_push_down_volatile_function_in_join() -> Result<()> {
40964133
// SELECT t.a, t.r FROM (SELECT test1.a AS a, TestScalarUDF() AS r FROM test1 join test2 ON test1.a = test2.a) AS t WHERE t.r > 0.5;

‎datafusion/sqllogictest/test_files/push_down_filter_regression.slt‎

Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -640,3 +640,37 @@ drop table t1;
640640

641641
statement ok
642642
drop table t2;
643+
644+
# A filter on a volatile group key must stay above the aggregate. Below the
645+
# aggregate, the filter evaluates the key again and sees a different value.
646+
query TT
647+
EXPLAIN SELECT k, c FROM (SELECT random() < 0.5 AS k, count(*) AS c FROM generate_series(1, 10000) GROUP BY 1) WHERE k;
648+
----
649+
logical_plan
650+
01)Projection: random() < Float64(0.5) AS k, count(Int64(1)) AS c
651+
02)--Filter: random() < Float64(0.5)
652+
03)----Aggregate: groupBy=[[random() < Float64(0.5)]], aggr=[[count(Int64(1))]]
653+
04)------TableScan: generate_series() projection=[]
654+
physical_plan
655+
01)ProjectionExec: expr=[random() < Float64(0.5)@0 as k, count(Int64(1))@1 as c]
656+
02)--FilterExec: random() < Float64(0.5)@0
657+
03)----AggregateExec: mode=FinalPartitioned, gby=[random() < Float64(0.5)@0 as random() < Float64(0.5)], aggr=[count(Int64(1))]
658+
04)------RepartitionExec: partitioning=Hash([random() < Float64(0.5)@0], 4), input_partitions=4
659+
05)--------AggregateExec: mode=Partial, gby=[random() < 0.5 as random() < Float64(0.5)], aggr=[count(Int64(1))]
660+
06)----------RepartitionExec: partitioning=RoundRobinBatch(4), input_partitions=1
661+
07)------------LazyMemoryExec: partitions=1, batch_generators=[generate_series: start=1, end=10000, batch_size=8192]
662+
663+
query B
664+
SELECT k FROM (SELECT random() < 0.5 AS k, count(*) AS c FROM generate_series(1, 10000) GROUP BY random() < 0.5) WHERE k;
665+
----
666+
true
667+
668+
query B
669+
SELECT k FROM (SELECT random() < 0.5 AS k, count(*) AS c FROM generate_series(1, 10000) GROUP BY k) WHERE NOT k;
670+
----
671+
false
672+
673+
query I
674+
SELECT sum(c) FROM (SELECT random() < 0.5 AS k, count(*) AS c FROM generate_series(1, 10000) GROUP BY random() < 0.5) WHERE k OR NOT k;
675+
----
676+
10000

0 commit comments

Comments
 (0)