Skip to content

fix: evaluate window aggregate arguments after FILTER - #24508

Draft
lyne7-sc wants to merge 6 commits into
apache:mainfrom
lyne7-sc:fix/window-filter-argument-evaluation
Draft

lyne7-sc wants to merge 6 commits into
apache:mainfrom
lyne7-sc:fix/window-filter-argument-evaluation

Conversation

@lyne7-sc

Copy link
Copy Markdown
Contributor

Which issue does this PR close?

Rationale for this change

Window aggregates evaluate their arguments before applying FILTER, which can cause errors on rows that should not participate in the aggregate.

This is analogous to #24444 for grouped aggregates, window aggregates use a separate evaluation path.

What changes are included in this PR?

Evaluate the window aggregate filter before evaluating its arguments.

Use the resulting filter mask as a selection when evaluating argument expressions. Selected results are scattered back to the original batch length so that window frame row indices remain aligned.

This applies to both ever-expanding and sliding aggregate window frames.

Are these changes tested?

Yes.

Are there any user-facing changes?

Window aggregate arguments are no longer evaluated for rows rejected by FILTER, no public API changes.

@github-actions github-actions Bot added physical-expr Changes to the physical-expr crates sqllogictest SQL Logic Tests (.slt) labels Aug 20, 2026
@codecov-commenter

codecov-commenter commented Aug 20, 2026 •

Copy link
Copy Markdown

Codecov Report

❌ Patch coverage is 83.96226% with 17 lines in your changes missing coverage. Please review.
✅ Project coverage is 81.44%. Comparing base (3ace5a0) to head (998af0a).
⚠️ Report is 99 commits behind head on main.

Files with missing lines Patch % Lines
datafusion/physical-expr/src/window/window_expr.rs 83.96% 4 Missing and 13 partials ⚠️
Additional details and impacted files
@@            Coverage Diff             @@
##             main   #24508      +/-   ##
==========================================
- Coverage   81.44%   81.44%   -0.01%     
==========================================
  Files        1118     1118              
  Lines      399560   399634      +74     
  Branches   399560   399634      +74     
==========================================
+ Hits       325424   325483      +59     
- Misses      55137    55143       +6     
- Partials    18999    19008       +9     

☔ View full report in Codecov by Harness.
📢 Have feedback on the report? Share it here.

🚀 New features to boost your workflow:
  • ❄️ Test Analytics: Detect flaky tests, report on failures, and find test suite problems.
  • 📦 JS Bundle Analysis: Save yourself from yourself by tracking and limiting bundle sizes in JS merges.

@neilconway neilconway left a comment

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

This approach makes sense and is simple and clean, but I'd be curious to understand how it influences performance, especially for simple and infallible expressions (e.g., bare column references).

@lyne7-sc

Copy link
Copy Markdown
Contributor Author

Thanks for the review! I think this could cause a performance regression when the window argument is a direct column or another cheap expression. In these cases, the cost of filtering the entire batch and scattering the result back to the original row positions may exceed the expression evaluation cost that we skip.

As a low-risk mitigation, we could bypass evaluate_selection for expressions that are known to be safe and cheap to evaluate on the original batch, as follows:

let value = if can_evaluate_window_arg_unfiltered(expr) {
    // These expressions cannot fail because of values in excluded rows.
    expr.evaluate(batch)?
} else {
    // Fallible expressions must only see selected rows.
    expr.evaluate_selection(batch, selection)?
};

This could address the most obvious regressions for direct columns and literals. However, it would only be a temporary mitigation and would not cover other cheap expressions such as column + 1.

One possible long-term direction might be to keep the selected argument values in compact form instead of scattering them back. We could maintain a prefix-count array that maps each boundary in the original row space to the corresponding boundary in the compact argument arrays:

selection:      T F T F
prefix:         0 1 1 2 2

original frame: [start, end)
compact frame:  [prefix[start], prefix[end])

Window frame calculation and output rows could remain in the original row space, while only the ranges passed to the accumulator would be mapped into the compact arrays. This could allow us to skip evaluating expressions for excluded rows while still producing the correct window results. It could also avoid per-argument scatter and the additional filtering before update_batch/retract_batch.

I'm not sure whether this approach would improve performance in practice. does this direction make sense to you?

@neilconway

Copy link
Copy Markdown
Contributor

@lyne7-sc Yep, that all makes sense -- both the short-term mitigation and the longer-term optimization possibility. Should we start by adding a micro-benchmark to cover the window agg + FILTER case and then proceed from there?

@lyne7-sc

lyne7-sc commented Aug 23, 2026 •

Copy link
Copy Markdown
Contributor Author

@neilconway yes, that makes sense. I've added the microbenchmark separately in #24589.

Here are the results from my local:

group                                                                   fix                                    main
-----                                                                   ---                                    -------
window_aggregate_filter/bounded_cumulative/column/10_percent            1.01      9.9±0.38ms        ? ?/sec    1.00      9.8±0.33ms        ? ?/sec
window_aggregate_filter/bounded_cumulative/column/30_percent            1.00      9.2±0.23ms        ? ?/sec    1.03      9.5±0.24ms        ? ?/sec
window_aggregate_filter/bounded_cumulative/column/50_percent            1.00      8.5±0.21ms        ? ?/sec    1.01      8.5±0.28ms        ? ?/sec
window_aggregate_filter/bounded_cumulative/divide/10_percent            1.11     11.7±0.37ms        ? ?/sec    1.00     10.5±0.54ms        ? ?/sec
window_aggregate_filter/bounded_cumulative/divide/30_percent            1.13     10.9±0.27ms        ? ?/sec    1.00      9.7±0.28ms        ? ?/sec
window_aggregate_filter/bounded_cumulative/divide/50_percent            1.14     10.5±0.27ms        ? ?/sec    1.00      9.2±0.33ms        ? ?/sec
window_aggregate_filter/bounded_cumulative/power_udf/10_percent         1.00     11.6±0.32ms        ? ?/sec    1.00     11.6±0.50ms        ? ?/sec
window_aggregate_filter/bounded_cumulative/power_udf/30_percent         1.03     11.2±0.25ms        ? ?/sec    1.00     10.8±0.45ms        ? ?/sec
window_aggregate_filter/bounded_cumulative/power_udf/50_percent         1.08     10.7±0.27ms        ? ?/sec    1.00      9.8±0.25ms        ? ?/sec
window_aggregate_filter/bounded_sliding_10_rows/column/30_percent       1.00     16.5±0.29ms        ? ?/sec    1.03     17.0±0.57ms        ? ?/sec
window_aggregate_filter/bounded_sliding_10_rows/power_udf/30_percent    1.13     20.8±0.49ms        ? ?/sec    1.00     18.4±0.46ms        ? ?/sec
window_aggregate_filter/window_whole_partition/column/30_percent        1.57     36.3±6.73µs        ? ?/sec    1.00     23.0±0.78µs        ? ?/sec
window_aggregate_filter/window_whole_partition/power_udf/30_percent     1.00   153.0±19.04µs        ? ?/sec    1.93    295.3±6.61µs        ? ?/sec

pull Bot pushed a commit to TCeason/arrow-datafusion that referenced this pull request Aug 31, 2026
## Which issue does this PR close?

<!--
We generally require a GitHub issue to be filed for all bug fixes and
enhancements and this helps us generate change logs for our releases.
You can link an issue to this PR using the GitHub syntax. For example
`Closes #123` indicates that this PR will close issue #123.
-->

- Refs apache#24508.

## Rationale for this change

<!--
Why are you proposing this change? If this is already explained clearly
in the issue then this section is not needed.
Explaining clearly why changes are proposed helps reviewers understand
your changes and offer better suggestions for fixes.

Please explain the problem you are trying to solve in terms of the
user-visible
behavior, rather than the implementation.

For example, "The code in `foo.rs` doesn't handle nulls" is a symptom of
the
implementation. "COUNT(DISTINCT) returns wrong results when the column
contains
nulls" is the user-visible problem.
-->

Add benchmark coverage for evaluating the performance impact of the
window aggregate filter argument evaluation changes in apache#24508.

## What changes are included in this PR?

<!--
There is no need to duplicate the description in the issue here, but it
is sometimes worth providing a summary of the individual changes in this
PR.
-->

Adds a Criterion benchmark for window aggregates with `FILTER` over
pre-ordered input.

It covers:

- Cumulative and sliding frames through `BoundedWindowAggExec`.
- Whole-partition frames through `WindowAggExec`.
- Column, division, and `power` arguments.
- Different representative `FILTER` selectivities.

This PR only adds benchmark coverage and does not change behavior.

## Are these changes tested?

<!--
We typically require tests for all PRs in order to:
1. Prevent the code from being accidentally broken by subsequent changes
2. Serve as another way to document the expected behavior of the code

If tests are not included in your PR, please explain why (for example,
are they covered by existing tests)?
-->

Yes.

## Are there any user-facing changes?

<!--
If there are user-facing changes then we may require documentation to be
updated before approving the PR.

If there are any breaking changes to public APIs, please add the `api
change` label.
-->

No.
CaptainEureka pushed a commit to CaptainEureka/datafusion that referenced this pull request Oct 12, 2026
…gregate with a FILTER (apache#26184)

## Which issue does this PR close?

- Closes apache#26183.

## Rationale for this change

An aggregate with a `FILTER` clause evaluates its arguments only for the
rows that pass the filter, so a fallible argument does not fail on a
rejected row.
When two aggregates have the same argument, `CommonSubexprEliminate`
moves that argument into a projection below the aggregate.
The projection evaluates it for every row, and the query fails on a row
that the filter rejects:

```sql
CREATE TABLE t AS
SELECT * FROM (VALUES (CAST(-950 AS SMALLINT), 0), (CAST(5 AS SMALLINT), 1)) AS v(x, y);

-- Succeeds.
SELECT avg(arrow_cast(x, 'UInt64')) FILTER (WHERE y > 0) AS a FROM t;

-- Fails: Arrow error: Cast error: Can't cast value -950 to type UInt64
SELECT
    sum(arrow_cast(x, 'UInt64')) FILTER (WHERE y > 0) AS s,
    count(arrow_cast(x, 'UInt64')) FILTER (WHERE y > 0) AS c
FROM t;
```

The rule already has a model for this: a conditionally evaluated
subexpression (a `CASE` branch, the right side of `AND` / `OR`, a lazy
function argument) is extracted only if it is also surely evaluated
somewhere else.
The arguments of a filtered aggregate are conditionally evaluated in the
same sense, but the rule did not know that.

## What changes are included in this PR?

1. `ExprCSEController::conditional_children` now reports the arguments
and the `ORDER BY` expressions of an `Expr::AggregateFunction` with a
`FILTER` as conditionally evaluated.
The filter itself stays a normal child, because the aggregate evaluates
it for every row.
   A filter that several aggregates share is still extracted.
2. The same function reports the arguments of an `Expr::WindowFunction`
with a `FILTER` as conditionally evaluated.
The `PARTITION BY` expressions, the `ORDER BY` expressions and the
filter stay normal children.
The window executor does not yet evaluate the filter first (apache#24507,
apache#24508), so this part has no visible effect today.
   It keeps the rule correct when that fix merges.
3. `CSEVisitor::f_down` in `datafusion/common/src/cse.rs` now visits the
children of a node with conditional children in tree order.
Before, it visited all normal children and then all conditional
children.
`CSERewriter` reads `id_array` in pre-order, so that was correct only
when every normal child came before every conditional child in the tree.
The children of an aggregate are `args`, `filter`, `order_by`, so the
normal child is in the middle.
   The `CSEController` trait is unchanged.

The rule keeps every extraction that is safe:

| Query shape | Before | After |
|---|---|---|
| `sum(f(x)) FILTER (WHERE p), count(f(x)) FILTER (WHERE p)` | `f(x)`
and `p` extracted | only `p` extracted |
| `sum(f(x)) FILTER (WHERE p), count(f(x))` | `f(x)` extracted | `f(x)`
extracted (the second aggregate evaluates it for every row) |
| `sum(f(x)) FILTER (WHERE f(x) > 0)` | `f(x)` extracted | `f(x)`
extracted (the filter evaluates it for every row) |
| `sum(f(x)) FILTER (WHERE p) AS a, sum(f(x)) FILTER (WHERE p) AS b` |
one aggregate | one aggregate |

The cost is the first row: when an expression occurs only under filtered
aggregates, each aggregate now evaluates it, on the rows that pass its
filter.
An alternative keeps that extraction when every aggregate has the same
filter, with a guard such as `CASE WHEN p THEN f(x) END` in the
projection.
I did not do that here because it is a larger change with its own plan
shape, and it can be a follow-up.

## What is the testing strategy for this PR?

- Unit tests in `common_subexpr_eliminate.rs`:
`test_aggregate_filter_arguments` (the four rows of the table above),
`test_aggregate_filter_order_by` and `test_window_filter_arguments`.
- `cse.slt`: the reproduction above, with and without `GROUP BY`, the
`EXPLAIN` that shows the cast stays in the aggregate, and the case where
an unfiltered aggregate shares the argument.
- `aggregates_simplify.slt`: one expected plan changes. `SUM(column1 +
1) FILTER (WHERE column1 > 1), SUM(column1 + 1) FILTER (WHERE column1 >
0)` no longer extracts `column1 + 1`.

## Are there any user-facing changes?

Queries that failed with an error from a row that an aggregate `FILTER`
rejects now succeed.
Plans for queries where a subexpression occurs only in the arguments of
filtered aggregates no longer have that subexpression in a
`__common_expr` projection.
There are no API changes.
@github-actions
github-actions Bot marked this pull request as draft October 26, 2026 01:26
@github-actions

Copy link
Copy Markdown

Marked as draft after 14 days without activity: merge conflicts.

Please address these items and mark Ready for review when ready.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

physical-expr Changes to the physical-expr crates sqllogictest SQL Logic Tests (.slt)

Projects

None yet

Development

Successfully merging this pull request may close these issues.

Window aggregate FILTER evaluates arguments for rejected rows

3 participants