Skip to content

Commit e4ae236

Browse files
authored
fix: Coerce aggregate FILTER predicates to boolean (apache#22774)
## Which issue does this PR close? - Closes apache#22773 . ## Rationale for this change Aggregate and window aggregate `FILTER` clauses currently fail with an internal error when the filter condition is `NULL`, even though the equivalent boolean-typed expression (e.g. `CAST(NULL AS BOOLEAN)`) works correctly. This occurs because `FILTER` predicates are not being coerced to `BOOLEAN` during type coercion. ## What changes are included in this PR? * Coerce aggregate `FILTER` predicates to `BOOLEAN` during type coercion. * Apply the same coercion to window aggregate `FILTER` predicates. * Add SQL logic tests covering `FILTER (WHERE NULL)` for both aggregate and window aggregate functions. ## Are these changes tested? Yes. Added SQL logic tests covering: * `COUNT(*) FILTER (WHERE NULL)` * `COUNT(1) FILTER (WHERE NULL)` * `SUM(1) FILTER (WHERE NULL)` * `AVG(1) FILTER (WHERE NULL)` * Window aggregate variants using `FILTER (WHERE NULL)` ## Are there any user-facing changes? Yes. Queries using `FILTER (WHERE NULL)` no longer fail with an internal error and now return the expected results. Also, no changes were made to any public APIs
1 parent c83a981 commit e4ae236

2 files changed

Lines changed: 45 additions & 0 deletions

File tree

‎datafusion/optimizer/src/analyzer/type_coercion.rs‎

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -715,6 +715,12 @@ impl TreeNodeRewriter for TypeCoercionRewriter<'_> {
715715
}) => {
716716
let new_expr =
717717
coerce_arguments_for_signature(args, self.schema, func.as_ref())?;
718+
719+
let filter = filter
720+
.map(|filter| filter.cast_to(&DataType::Boolean, self.schema))
721+
.transpose()?
722+
.map(Box::new);
723+
718724
Ok(Transformed::yes(Expr::AggregateFunction(
719725
expr::AggregateFunction::new_udf(
720726
func,
@@ -752,6 +758,11 @@ impl TreeNodeRewriter for TypeCoercionRewriter<'_> {
752758
}
753759
};
754760

761+
let filter = filter
762+
.map(|filter| filter.cast_to(&DataType::Boolean, self.schema))
763+
.transpose()?
764+
.map(Box::new);
765+
755766
let new_expr = Expr::from(WindowFunction {
756767
fun,
757768
params: expr::WindowFunctionParams {

‎datafusion/sqllogictest/test_files/aggregate.slt‎

Lines changed: 34 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6339,6 +6339,40 @@ GROUP BY g
63396339
----
63406340
0 0
63416341

6342+
# query_with_untyped_null_filter
6343+
query I
6344+
SELECT count(*) FILTER (WHERE NULL)
6345+
----
6346+
0
6347+
6348+
query I
6349+
SELECT count(1) FILTER (WHERE NULL)
6350+
----
6351+
0
6352+
6353+
query I
6354+
SELECT sum(1) FILTER (WHERE NULL)
6355+
----
6356+
NULL
6357+
6358+
query R
6359+
SELECT avg(1) FILTER (WHERE NULL)
6360+
----
6361+
NULL
6362+
6363+
# window_aggregate_with_untyped_null_filter
6364+
query I
6365+
SELECT count(*) FILTER (WHERE NULL) OVER ()
6366+
FROM (VALUES (1)) AS t(x)
6367+
----
6368+
0
6369+
6370+
query I
6371+
SELECT sum(1) FILTER (WHERE NULL) OVER ()
6372+
FROM (VALUES (1)) AS t(x)
6373+
----
6374+
NULL
6375+
63426376
# query_with_and_without_filter
63436377
query III rowsort
63446378
SELECT

0 commit comments

Comments
 (0)