You are viewing a plain text version of this content. The canonical link for it is here.
Posted to github@datafusion.apache.org by "geoffreyclaude (via GitHub)" <gi...@apache.org> on 2025/09/02 16:43:37 UTC
[PR] feat: Support `FILTER` clause in aggregate window functions [datafusion]
geoffreyclaude opened a new pull request, #17378:
URL: https://github.com/apache/datafusion/pull/17378
## Which issue does this PR close?
- Closes #674.
## Rationale for this change
This PR adds SQL-standard FILTER support for window aggregates (aligned with SQLite section 2.3 semantics), enabling concise conditional aggregation within window frames without rewriting with CASE. CASE cannot fully replicate FILTER because FILTER removes rows before the aggregate sees them, while CASE only nullifies values:
- ARRAY_AGG example: FILTER excludes rows; CASE injects NULLs into the array.
```sql
ARRAY_AGG(c2) FILTER (WHERE c2 >= 2) OVER (...) -- e.g. [2, 3, 4]
ARRAY_AGG(CASE WHEN c2 >= 2 THEN c2 END) OVER (...) -- e.g. [NULL, NULL, 2, 3, 4]
```
- COUNT example: FILTER keeps the aggregate and intent clear; CASE forces a different aggregate.
```sql
COUNT(*) FILTER (WHERE pred) OVER (...) -- counts gated rows
-- vs
SUM(CASE WHEN pred THEN 1 ELSE 0 END) OVER (...) -- different function, different typing/overflow behavior
```
FILTER also composes correctly with DISTINCT and null-treatments because it gates rows before those steps; CASE introduces NULLs into the input domain, altering semantics.
## What changes are included in this PR?
- Adds an optional FILTER predicate to aggregate window functions and threads it from SQL parsing through logical planning into physical execution.
- Enforces that FILTER is only permitted on aggregate window functions; using it on non-aggregate window functions returns a clear planning error.
- At execution, evaluates the FILTER once per input batch to a boolean mask and applies it to aggregate inputs before update/retract steps, working for both bounded and sliding windows and composing with DISTINCT and NULL-treatments.
- Ensures EXPLAIN and SQL unparsing display the FILTER clause to make intent and plans clear.
## Are these changes tested?
Yes—coverage includes parsing/planning/execution correctness, negative cases for invalid usage, and plan readability via EXPLAIN.
## Are there any user-facing changes?
Yes—users can now write FILTER on aggregate window functions. Example:
```sql
SELECT
c1,
SUM(c2) FILTER (WHERE c2 >= 2)
OVER (ORDER BY c1, c2
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum_ge_2
FROM t
ORDER BY c1, c2;
```
Using FILTER with non-aggregate window functions (e.g., ROW_NUMBER, LAG) yields a planning error. This is additive and does not change existing behavior.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: Support `FILTER` clause in aggregate window functions [datafusion]
Posted by "Jefffrey (via GitHub)" <gi...@apache.org>.
Jefffrey merged PR #17378:
URL: https://github.com/apache/datafusion/pull/17378
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: Support `FILTER` clause in aggregate window functions [datafusion]
Posted by "Jefffrey (via GitHub)" <gi...@apache.org>.
Jefffrey commented on PR #17378:
URL: https://github.com/apache/datafusion/pull/17378#issuecomment-3256506016
> > Would be nice to have a test via DataFrame API if possible. Also for the proto, I think we can raise an issue so we can have it tracked in GitHub.
> > Should we also update somewhere in our SQL reference that we now support FILTERS in windows? I notice we don't mention FILTER support for regular aggregates either 🤔
> > Other than that, seems good to me.
>
> @Jefffrey Thanks for your review!
>
> I've added [ff0b1c8](https://github.com/apache/datafusion/commit/ff0b1c8d7cb5bb4e80c8b7576f819961daa7ab0d) for the DataFrame API test, and [8ce5de3](https://github.com/apache/datafusion/commit/8ce5de39122fb023f4f2de04253cf7bd1d54c883) for the docs if you want to give it another look.
>
> Also opened #17417 (Issue is AI generated, but is way clearer than anything I could have written myself!)
Looks good 👍
Will merge this later today in case anyone else wants to take a look.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: Support `FILTER` clause in aggregate window functions [datafusion]
Posted by "geoffreyclaude (via GitHub)" <gi...@apache.org>.
geoffreyclaude commented on PR #17378:
URL: https://github.com/apache/datafusion/pull/17378#issuecomment-3254277786
> Would be nice to have a test via DataFrame API if possible. Also for the proto, I think we can raise an issue so we can have it tracked in GitHub.
>
> Should we also update somewhere in our SQL reference that we now support FILTERS in windows? I notice we don't mention FILTER support for regular aggregates either 🤔
>
> Other than that, seems good to me.
@Jefffrey Thanks for your review!
I've added https://github.com/apache/datafusion/pull/17378/commits/ff0b1c8d7cb5bb4e80c8b7576f819961daa7ab0d for the DataFrame API test, and https://github.com/apache/datafusion/pull/17378/commits/8ce5de39122fb023f4f2de04253cf7bd1d54c883 for the docs if you want to give it another look.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: Support `FILTER` clause in aggregate window functions [datafusion]
Posted by "geoffreyclaude (via GitHub)" <gi...@apache.org>.
geoffreyclaude commented on code in PR #17378:
URL: https://github.com/apache/datafusion/pull/17378#discussion_r2318941195
##########
datafusion/expr/src/logical_plan/plan.rs:
##########
@@ -2469,6 +2469,20 @@ impl Window {
window_func_dependencies.extend(new_deps);
}
+ // Validate that FILTER if present is only used with aggregate window functions
Review Comment:
How about simply:
```rust
// Validate that FILTER clauses are only used with aggregate window functions
```
##########
datafusion/proto/src/logical_plan/to_proto.rs:
##########
@@ -317,6 +317,7 @@ pub fn serialize_expr(
// TODO: support null treatment in proto
null_treatment: _,
distinct: _,
+ filter: _,
Review Comment:
Hmm... which project might you be thinking about? ;) I agree. There is already the `// TODO: support null treatment in proto` above. I hope whoever implements it will also pick up `distinct` and `filter`!
##########
datafusion/expr/src/expr.rs:
##########
@@ -2993,6 +3006,10 @@ impl Display for SchemaDisplay<'_> {
write!(f, " {null_treatment}")?;
}
+ if let Some(filter) = filter {
+ write!(f, " FILTER (WHERE {filter})")?;
+ }
Review Comment:
good point, `datafusion/sql/src/unparser/expr.rs` is the right place for this test.
##########
datafusion/sqllogictest/test_files/window.slt:
##########
@@ -5898,3 +5898,115 @@ physical_plan
06)----------RepartitionExec: partitioning=Hash([k@1], 2), input_partitions=2
07)------------ProjectionExec: expr=[CAST(v@1 AS Int64) as __common_expr_1, k@0 as k, time@2 as time]
08)--------------DataSourceExec: partitions=2, partition_sizes=[5, 4]
+
+
+# FILTER clause with window functions
Review Comment:
nice suggestions ! I'll add them.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: Support `FILTER` clause in aggregate window functions [datafusion]
Posted by "gabotechs (via GitHub)" <gi...@apache.org>.
gabotechs commented on PR #17378:
URL: https://github.com/apache/datafusion/pull/17378#issuecomment-3252108150
cc @alamb, this one looks good to go
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: Support `FILTER` clause in aggregate window functions [datafusion]
Posted by "gabotechs (via GitHub)" <gi...@apache.org>.
gabotechs commented on code in PR #17378:
URL: https://github.com/apache/datafusion/pull/17378#discussion_r2318690269
##########
datafusion/expr/src/logical_plan/plan.rs:
##########
@@ -2469,6 +2469,20 @@ impl Window {
window_func_dependencies.extend(new_deps);
}
+ // Validate that FILTER if present is only used with aggregate window functions
Review Comment:
```suggestion
// Validate that FILTER is present only if used with aggregate window functions
```
##########
datafusion/sqllogictest/test_files/window.slt:
##########
@@ -5898,3 +5898,115 @@ physical_plan
06)----------RepartitionExec: partitioning=Hash([k@1], 2), input_partitions=2
07)------------ProjectionExec: expr=[CAST(v@1 AS Int64) as __common_expr_1, k@0 as k, time@2 as time]
08)--------------DataSourceExec: partitions=2, partition_sizes=[5, 4]
+
+
+# FILTER clause with window functions
Review Comment:
Giving here some ideas for other tests:
- A test that uses more than 1 expression in the filter
```sql
SELECT
c1,
c2,
SUM(c2) FILTER (WHERE c2 >= 2 AND c1 = 0 AND c2 < 4) OVER (ORDER BY c1, c2 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as sum1,
COUNT(c2) FILTER (WHERE c2 >= 2 AND c1 = 0 AND c2 < 4) OVER (ORDER BY c1, c2 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as count1,
ARRAY_AGG(c2) FILTER (WHERE c2 >= 2 AND c1 = 0 AND c2 < 4) OVER (ORDER BY c1, c2 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as array_agg1,
FROM test
ORDER BY c1, c2
LIMIT 5
```
- A test that performs an invalid comparison inside the filter expression and checks that the error is propagated correctly
```sql
SELECT
c1,
SUM(c2) FILTER (WHERE c2 >= []) OVER (ORDER BY c1, c2 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as sum1,
FROM test
LIMIT 5
```
##########
datafusion/expr/src/expr.rs:
##########
@@ -2993,6 +3006,10 @@ impl Display for SchemaDisplay<'_> {
write!(f, " {null_treatment}")?;
}
+ if let Some(filter) = filter {
+ write!(f, " FILTER (WHERE {filter})")?;
+ }
Review Comment:
Maybe cover this in some test? maybe there some roundtrip tests somewhere that go from SQL -> logical plan -> SQL
##########
datafusion/proto/src/logical_plan/to_proto.rs:
##########
@@ -317,6 +317,7 @@ pub fn serialize_expr(
// TODO: support null treatment in proto
null_treatment: _,
distinct: _,
+ filter: _,
Review Comment:
It would be nice to support this at some point, projects bringing distributed capabilities for DataFusion would find this very useful.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org
Re: [PR] feat: Support `FILTER` clause in aggregate window functions [datafusion]
Posted by "gabotechs (via GitHub)" <gi...@apache.org>.
gabotechs commented on code in PR #17378:
URL: https://github.com/apache/datafusion/pull/17378#discussion_r2318980266
##########
datafusion/proto/src/logical_plan/to_proto.rs:
##########
@@ -317,6 +317,7 @@ pub fn serialize_expr(
// TODO: support null treatment in proto
null_treatment: _,
distinct: _,
+ filter: _,
Review Comment:
> Hmm... which project might you be thinking about? ;)
🤷♂️ who knows.
> There is already the // TODO: support null treatment in proto above. I hope whoever implements it will also pick up distinct and filter!
fair enough 👍
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscribe@datafusion.apache.org
For additional commands, e-mail: github-help@datafusion.apache.org