Skip to content

Commit 7e5f40a

Browse files
adriangbclaude
andauthored
fix: reject window functions in WHERE and HAVING during logical planning (#25139)
## Which issue does this PR close? - Closes #4610 ## Rationale for this change Run these statements in `datafusion-cli`: ```sql CREATE TABLE t(a INT, b INT) AS VALUES (1, 1), (2, 2); SELECT a FROM t GROUP BY a HAVING row_number() OVER () = 1; ``` On `main`, the second statement fails during physical planning. The error is the `Debug` text of the internal `Expr::WindowFunction`. It is one line of 526 characters. It does not give the clause, the function, or the column: ``` This feature is not implemented: Physical plan does not support logical expression WindowFunction(WindowFunction { fun: WindowUDF(WindowUDF { inner: RowNumber { signature: Signature { type_signature: Nullary, volatility: Immutable, parameter_names: None } } }), params: WindowFunctionParams { args: [], partition_by: [], order_by: [], window_frame: WindowFrame { units: Rows, start_bound: Preceding(UInt64(NULL)), end_bound: Following(UInt64(NULL)), is_causal: false }, filter: None, null_treatment: None, distinct: false } }) ``` The query from the issue gives the same type of error. That error is 1,981 characters long. With this PR, the statement fails during logical planning. The error gives the clause and the window function: ``` Error during planning: Window function calls are not allowed in HAVING: 'row_number() ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING' ``` The same applies to `WHERE`: ```sql SELECT a FROM t WHERE row_number() OVER () = 1; ``` ``` Error during planning: Window function calls are not allowed in WHERE: 'row_number() ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING' ``` The error has a `Diagnostic`. Its span points to the window function call in the SQL text, or to the alias that resolves to one. Its help message gives the fix: ``` Move the condition that uses this window function to a QUALIFY clause, which is evaluated after window functions are computed ``` These queries are not valid SQL. DataFusion evaluates a filter before it computes window functions. PostgreSQL rejects the same queries with `window functions are not allowed in HAVING`. These queries continue to work: ```sql -- window function in the SELECT list SELECT a, count(*) AS cnt, sum(count(*)) OVER () AS total FROM t GROUP BY a; -- window function in QUALIFY SELECT a, count(*) AS cnt, sum(count(*)) OVER () AS total FROM t GROUP BY a QUALIFY sum(count(*)) OVER () >= 2; -- window function in an inner query SELECT * FROM (SELECT a, count(*) AS cnt, sum(count(*)) OVER () AS total FROM t GROUP BY a) s WHERE total >= 2; -- window function in a subquery of a WHERE predicate SELECT a FROM t WHERE a IN (SELECT a FROM t QUALIFY row_number() OVER (ORDER BY a) = 1); ``` ## What changes are included in this PR? Five stacked commits: 1. `fix: reject window functions in WHERE and HAVING during logical planning` - Add `datafusion_expr::utils::check_no_window_functions(expr, clause)`. It returns a planning error if `expr` contains a window function. The function does not look into subqueries. It has the same shape as `check_aggregate_and_window_nesting` from #23813. - `Filter::try_new` calls the function with the clause name `filter predicates`. This applies to the `DataFrame` and `LogicalPlanBuilder` paths. `Filter::new` does not do the check. Its documentation says this. - The SQL planner calls the function for `WHERE` and for `HAVING`. The `HAVING` check runs after alias resolution. Thus `HAVING total >= 10`, where `total` is an alias of a window function, is also rejected. 2. `refactor: expose window_function_not_allowed_err from datafusion_expr::utils` - Split the error construction out of `check_no_window_functions` so a caller with better location information can build the same error. 3. `feat: point the window-in-WHERE/HAVING diagnostic at the call, suggest QUALIFY` - The SQL planner takes the span from the sqlparser AST. A planned `Expr` only has spans on columns, so before this the span pointed to the first column inside the call, and `row_number() OVER ()` had no span. Window calls in subqueries are skipped. - The help message tells the user to move the condition to `QUALIFY`. This is the one rewrite that always gives the query its standard meaning. 4. `fix: readable physical planning errors for misplaced window and aggregate functions` - The physical planner is the backstop for every other position, and for plans built with the DataFrame API. Its error for a window or aggregate function now names the function and lists the positions where it is supported, instead of the `Debug` dump. The `LIMIT` and `OFFSET` errors print the expression with `Display`. 5. `fix: reject window functions in JOIN ON, GROUP BY and VALUES during SQL planning` - The same check as for `WHERE` and `HAVING`, with a clause-specific hint. `GROUP BY` is checked after aliases and positions are resolved. The SQL planner checks are for better errors on common mistakes: they carry a span into the SQL text and a hint for the clause. They are not needed for correctness. The physical planner rejects a misplaced window or aggregate function in every position, but it cannot point into the query. ## What is the testing strategy for this PR? - `datafusion/expr/src/utils.rs`: unit test for `check_no_window_functions`. It checks the accepted expressions, the error message, and the diagnostic. - `datafusion/expr/src/logical_plan/builder.rs`: `LogicalPlanBuilder::filter` and `having` reject a window predicate. `window(...).filter(...)` continues to work. - `datafusion/sql/tests/sql_integration.rs`: the `WHERE` and `HAVING` messages, the alias case, and the `QUALIFY` and subquery forms. - `datafusion/sql/tests/cases/diagnostic.rs`: the diagnostic span points to the window function call in the SQL text, to the alias in `HAVING`, and not to a call inside a subquery. The help message is checked. - `datafusion/sqllogictest/test_files/window.slt`: the reproducer from the issue, the `WHERE` variant, the four queries that continue to work, the `JOIN ON`, `GROUP BY` and `VALUES` errors, and the physical planner backstop for `LIMIT` and `OFFSET`. - `datafusion/core/tests/dataframe/mod.rs`: a window function as a DataFrame grouping expression reaches the physical planner backstop. ## Are there any user-facing changes? Yes. A query with a window function in `WHERE` or `HAVING` now fails during logical planning. The error is `Error during planning: Window function calls are not allowed in {WHERE|HAVING}: '<window call>'`. Before, the query failed during physical planning with `This feature is not implemented: Physical plan does not support logical expression WindowFunction(...)`. `Filter::try_new`, `LogicalPlanBuilder::filter` and `LogicalPlanBuilder::having` return the same type of error for such a predicate. Before, they returned a plan that could not be executed. A window or aggregate function in any other position now fails with `Error during planning: Window function '<call>' is not supported in this position. Window functions are supported in the SELECT list, ORDER BY, DISTINCT ON and QUALIFY` (or the aggregate equivalent) instead of `This feature is not implemented: Physical plan does not support logical expression WindowFunction(...)`. The `Unsupported LIMIT expression` and `Unsupported OFFSET expression` errors show the expression as SQL text. New public API: `datafusion_expr::utils::check_no_window_functions` and `datafusion_expr::utils::window_function_not_allowed_err`. 🤖 Generated with [Claude Code](https://claude.com/claude-code) --------- Co-authored-by: Claude Fable 5.1 <[email protected]>
1 parent 4cee381 commit 7e5f40a

13 files changed

Lines changed: 704 additions & 20 deletions

File tree

‎datafusion/core/src/physical_planner.rs‎

Lines changed: 8 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1270,16 +1270,20 @@ impl DefaultPhysicalPlanner {
12701270
LogicalPlan::SubqueryAlias(_) => children.one()?,
12711271
LogicalPlan::Limit(limit) => {
12721272
let input = children.one()?;
1273+
// `get_skip_type` / `get_fetch_type` only return a non literal
1274+
// type for an expression that is present
12731275
let SkipType::Literal(skip) = limit.get_skip_type()? else {
1276+
let skip = limit.skip.as_deref().map(ToString::to_string);
12741277
return not_impl_err!(
1275-
"Unsupported OFFSET expression: {:?}",
1276-
limit.skip
1278+
"Unsupported OFFSET expression: {}",
1279+
skip.unwrap_or_default()
12771280
);
12781281
};
12791282
let FetchType::Literal(fetch) = limit.get_fetch_type()? else {
1283+
let fetch = limit.fetch.as_deref().map(ToString::to_string);
12801284
return not_impl_err!(
1281-
"Unsupported LIMIT expression: {:?}",
1282-
limit.fetch
1285+
"Unsupported LIMIT expression: {}",
1286+
fetch.unwrap_or_default()
12831287
);
12841288
};
12851289

‎datafusion/core/tests/dataframe/mod.rs‎

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5334,6 +5334,25 @@ async fn consecutive_projection_same_schema() -> Result<()> {
53345334
Ok(())
53355335
}
53365336

5337+
#[tokio::test]
5338+
async fn window_function_in_group_by_is_rejected() -> Result<()> {
5339+
// https://github.com/apache/datafusion/issues/4610
5340+
//
5341+
// The SQL planner rejects this placement itself, but the DataFrame API
5342+
// has no such check, so the physical planner's backstop is what reports it
5343+
let err = test_table()
5344+
.await?
5345+
.aggregate(vec![row_number()], vec![count(col("c1"))])?
5346+
.collect()
5347+
.await
5348+
.expect_err("a window function cannot be a grouping expression");
5349+
assert_snapshot!(
5350+
err.strip_backtrace(),
5351+
@"Error during planning: Window function 'row_number() ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING' is not supported in this position. Window functions are supported in the SELECT list, ORDER BY, DISTINCT ON and QUALIFY"
5352+
);
5353+
Ok(())
5354+
}
5355+
53375356
async fn create_test_table(name: &str) -> Result<DataFrame> {
53385357
let schema = Arc::new(Schema::new(vec![
53395358
Field::new("a", DataType::Utf8, false),

‎datafusion/expr/src/logical_plan/builder.rs‎

Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3131,6 +3131,50 @@ mod tests {
31313131
Ok(())
31323132
}
31333133

3134+
#[test]
3135+
fn plan_builder_filter_rejects_window_functions() -> Result<()> {
3136+
// https://github.com/apache/datafusion/issues/4610
3137+
let sum_over = |arg| {
3138+
Expr::from(expr::WindowFunction::new(
3139+
crate::WindowFunctionDefinition::AggregateUDF(
3140+
crate::test::function_stub::sum_udaf(),
3141+
),
3142+
vec![arg],
3143+
))
3144+
};
3145+
let scan = || table_scan(Some("employee_csv"), &employee_schema(), Some(vec![4]));
3146+
3147+
let err = scan()?
3148+
.filter(sum_over(col("salary")).gt(lit(10)))
3149+
.expect_err("window functions in a filter should be rejected");
3150+
assert_snapshot!(
3151+
err.strip_backtrace(),
3152+
@"Error during planning: Window function calls are not allowed in filter predicates: 'sum(employee_csv.salary) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING'"
3153+
);
3154+
3155+
let err = scan()?
3156+
.having(sum_over(col("salary")).gt(lit(10)))
3157+
.expect_err("window functions in a having should be rejected");
3158+
assert_snapshot!(
3159+
err.strip_backtrace(),
3160+
@"Error during planning: Window function calls are not allowed in filter predicates: 'sum(employee_csv.salary) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING'"
3161+
);
3162+
3163+
// computing the window function first and filtering on its result is
3164+
// the supported form
3165+
let plan = scan()?
3166+
.window(vec![sum_over(col("salary")).alias("total")])?
3167+
.filter(col("total").gt(lit(10)))?
3168+
.build()?;
3169+
assert_snapshot!(plan, @r"
3170+
Filter: total > Int32(10)
3171+
WindowAggr: windowExpr=[[sum(employee_csv.salary) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING AS total]]
3172+
TableScan: employee_csv projection=[salary]
3173+
");
3174+
3175+
Ok(())
3176+
}
3177+
31343178
#[test]
31353179
fn test_join_metadata() -> Result<()> {
31363180
let left_schema = DFSchema::new_with_metadata(

‎datafusion/expr/src/logical_plan/plan.rs‎

Lines changed: 16 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -41,9 +41,10 @@ use crate::logical_plan::display::{GraphvizVisitor, IndentVisitor};
4141
use crate::logical_plan::extension::UserDefinedLogicalNode;
4242
use crate::logical_plan::{DmlStatement, Statement, WriteOp};
4343
use crate::utils::{
44-
check_aggregate_and_window_nesting, enumerate_grouping_sets, expr_to_columns,
45-
exprlist_to_fields, find_out_reference_exprs, grouping_set_expr_count,
46-
grouping_set_to_exprlist, merge_schema, split_conjunction,
44+
check_aggregate_and_window_nesting, check_no_window_functions,
45+
enumerate_grouping_sets, expr_to_columns, exprlist_to_fields,
46+
find_out_reference_exprs, grouping_set_expr_count, grouping_set_to_exprlist,
47+
merge_schema, split_conjunction,
4748
};
4849
use crate::{
4950
BinaryExpr, CreateMemoryTable, CreateView, Execute, Expr, ExprSchemable, GroupingSet,
@@ -2822,19 +2823,24 @@ pub struct Filter {
28222823
impl Filter {
28232824
/// Create a new filter operator.
28242825
///
2825-
/// Skips the type-checking and dealiasing done in [Self::try_new].
2826-
/// For internal use in DataFusion only.
2826+
/// Skips the type-checking, window function check and dealiasing done in
2827+
/// [Self::try_new]. For internal use in DataFusion only.
28272828
///
28282829
/// **Preconditions:**
28292830
/// - the `predicate` expression returns a boolean value
28302831
/// - the `predicate` expression is not aliased
2832+
/// - the `predicate` expression contains no window function calls
28312833
#[doc(hidden)]
28322834
pub fn new(predicate: Expr, input: Arc<LogicalPlan>) -> Self {
28332835
Self { predicate, input }
28342836
}
28352837

28362838
/// Create a new filter operator.
28372839
///
2840+
/// Returns an error if the predicate is not boolean or contains a window
2841+
/// function call, which cannot be evaluated by a filter (see
2842+
/// [`check_no_window_functions`]).
2843+
///
28382844
/// Notes: as Aliases have no effect on the output of a filter operator,
28392845
/// they are removed from the predicate expression.
28402846
pub fn try_new(predicate: Expr, input: Arc<LogicalPlan>) -> Result<Self> {
@@ -2853,6 +2859,11 @@ impl Filter {
28532859
}
28542860

28552861
fn try_new_internal(predicate: Expr, input: Arc<LogicalPlan>) -> Result<Self> {
2862+
// Filters are evaluated before window functions are computed, so a
2863+
// window call in the predicate has no physical equivalent. Reject it
2864+
// here rather than failing during physical planning.
2865+
check_no_window_functions(&predicate, "filter predicates")?;
2866+
28562867
// Filter predicates must return a boolean value so we try and validate that here.
28572868
// Note that it is not always possible to resolve the predicate expression during plan
28582869
// construction (such as with correlated subqueries) so we make a best effort here and

‎datafusion/expr/src/utils.rs‎

Lines changed: 107 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -750,6 +750,74 @@ fn first_span(expr: &Expr) -> Option<Span> {
750750
span
751751
}
752752

753+
/// Returns an error if `expr` contains a window function call.
754+
///
755+
/// `clause` names where `expr` came from and completes the message
756+
/// `Window function calls are not allowed in {clause}`, for example `WHERE` or
757+
/// `HAVING`. Filters are evaluated before window functions are computed, so a
758+
/// window call in a filter predicate has no physical equivalent and is not
759+
/// valid SQL either (PostgreSQL: `window functions are not allowed in HAVING`).
760+
/// Rejecting it while the logical plan is built gives an error that names the
761+
/// offending call instead of a late physical planning failure.
762+
///
763+
/// [`Filter::try_new`] calls this for every predicate, so the SQL planner and
764+
/// the `DataFrame`/`LogicalPlanBuilder` paths are covered without callers
765+
/// invoking it directly. Window calls inside subqueries of `expr` are not
766+
/// visited and are legal.
767+
///
768+
/// The error is built by [`window_function_not_allowed_err`] with the best
769+
/// effort span of the call (see [`Expr::spans`]) and a generic help message. A
770+
/// caller that knows where the call is in the original query, such as the SQL
771+
/// planner, can build a more precise error with that function directly.
772+
///
773+
/// [`Filter::try_new`]: crate::logical_plan::Filter::try_new
774+
pub fn check_no_window_functions(expr: &Expr, clause: &str) -> Result<()> {
775+
match first_window_function(expr) {
776+
None => Ok(()),
777+
Some(window) => Err(window_function_not_allowed_err(
778+
window,
779+
clause,
780+
first_span(window),
781+
format!(
782+
"Compute '{window}' first, in a Window node or an inner query, and filter on its result"
783+
),
784+
)),
785+
}
786+
}
787+
788+
/// The first window function call in `expr`, if any. Subqueries are not
789+
/// visited.
790+
fn first_window_function(expr: &Expr) -> Option<&Expr> {
791+
let mut window = None;
792+
expr.apply(|e| {
793+
if matches!(e, Expr::WindowFunction(_)) {
794+
window = Some(e);
795+
Ok(TreeNodeRecursion::Stop)
796+
} else {
797+
Ok(TreeNodeRecursion::Continue)
798+
}
799+
})
800+
.ok()?;
801+
window
802+
}
803+
804+
/// The planning error for `window`, a window function call that appeared in
805+
/// `clause` where it is not allowed (see [`check_no_window_functions`]).
806+
///
807+
/// The message is `Window function calls are not allowed in {clause}:
808+
/// '{window}'`. The error carries a [`Diagnostic`] with the same message,
809+
/// pointing at `span`, and with `help`.
810+
pub fn window_function_not_allowed_err(
811+
window: &Expr,
812+
clause: &str,
813+
span: Option<Span>,
814+
help: impl Into<String>,
815+
) -> DataFusionError {
816+
let message = format!("Window function calls are not allowed in {clause}");
817+
plan_datafusion_err!("{message}: '{window}'")
818+
.with_diagnostic(Diagnostic::new_error(message, span).with_help(help, None))
819+
}
820+
753821
/// Collect all deeply nested `Expr::WindowFunction`. They are returned in order of occurrence
754822
/// (depth first), with duplicates omitted.
755823
pub fn find_window_exprs<'a>(exprs: impl IntoIterator<Item = &'a Expr>) -> Vec<Expr> {
@@ -2102,4 +2170,43 @@ mod tests {
21022170
@"Error during planning: Window function calls cannot be nested: 'sum(a) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING' is nested inside 'sum(sum(a) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING'"
21032171
);
21042172
}
2173+
2174+
#[test]
2175+
fn test_check_no_window_functions() -> Result<()> {
2176+
use crate::test::function_stub::sum;
2177+
use insta::assert_snapshot;
2178+
2179+
// columns, scalar expressions and aggregates are fine
2180+
check_no_window_functions(&col("a"), "WHERE")?;
2181+
check_no_window_functions(&(col("a") + lit(1)).gt(lit(0)), "WHERE")?;
2182+
check_no_window_functions(&sum(col("a")).gt(lit(0)), "HAVING")?;
2183+
2184+
// a bare window call
2185+
let err =
2186+
check_no_window_functions(&sum_over(vec![col("a")]), "WHERE").unwrap_err();
2187+
assert_snapshot!(
2188+
err.strip_backtrace(),
2189+
@"Error during planning: Window function calls are not allowed in WHERE: 'sum(a) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING'"
2190+
);
2191+
let diag = err.diagnostic().expect("diagnostic");
2192+
assert_snapshot!(
2193+
diag.message,
2194+
@"Window function calls are not allowed in WHERE"
2195+
);
2196+
assert_snapshot!(
2197+
diag.helps[0].message,
2198+
@"Compute 'sum(a) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING' first, in a Window node or an inner query, and filter on its result"
2199+
);
2200+
2201+
// a window call over an aggregate, nested below other expressions,
2202+
// names the clause it was given
2203+
let predicate = sum_over(vec![sum(col("a"))]).gt(lit(10)).and(col("b"));
2204+
let err = check_no_window_functions(&predicate, "HAVING").unwrap_err();
2205+
assert_snapshot!(
2206+
err.strip_backtrace(),
2207+
@"Error during planning: Window function calls are not allowed in HAVING: 'sum(sum(a)) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING'"
2208+
);
2209+
2210+
Ok(())
2211+
}
21052212
}

‎datafusion/physical-expr/src/planner.rs‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -689,6 +689,16 @@ pub fn create_physical_expr(
689689
Arc::clone(schema_field),
690690
)))
691691
}
692+
// Window and aggregate functions are evaluated by dedicated plan
693+
// nodes, never by a physical expression. Reaching this point means
694+
// the function is in a position where it cannot be evaluated, so
695+
// report that instead of dumping the internal representation.
696+
Expr::WindowFunction(_) => plan_err!(
697+
"Window function '{e}' is not supported in this position. Window functions are supported in the SELECT list, ORDER BY, DISTINCT ON and QUALIFY"
698+
),
699+
Expr::AggregateFunction(_) => plan_err!(
700+
"Aggregate function '{e}' is not supported in this position. Aggregate functions are supported in the SELECT list, HAVING and ORDER BY of a query with GROUP BY"
701+
),
692702
other => {
693703
not_impl_err!("Physical plan does not support logical expression {other:?}")
694704
}

0 commit comments

Comments
 (0)