This is an automated email from the ASF dual-hosted git repository.
github-merge-queue[bot] pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/datafusion.git
The following commit(s) were added to refs/heads/main by this push:
new 7e5f40a69a fix: reject window functions in WHERE and HAVING during
logical planning (#25139)
7e5f40a69a is described below
commit 7e5f40a69aae9199c3c682806750e40d6bebb398
Author: Adrian Garcia Badaracco <[email protected]>
AuthorDate: Thu Sep 10 22:23:13 2026 +0000
fix: reject window functions in WHERE and HAVING during logical planning
(#25139)
## Which issue does this PR close?
- Closes https://github.com/apache/datafusion/issues/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_ [...]
```
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]>
---
datafusion/core/src/physical_planner.rs | 12 ++-
datafusion/core/tests/dataframe/mod.rs | 19 ++++
datafusion/expr/src/logical_plan/builder.rs | 44 ++++++++
datafusion/expr/src/logical_plan/plan.rs | 21 +++-
datafusion/expr/src/utils.rs | 107 +++++++++++++++++++
datafusion/physical-expr/src/planner.rs | 10 ++
datafusion/sql/src/expr/mod.rs | 99 +++++++++++++++++-
datafusion/sql/src/relation/join.rs | 14 ++-
datafusion/sql/src/select.rs | 47 +++++++--
datafusion/sql/src/values.rs | 16 ++-
datafusion/sql/tests/cases/diagnostic.rs | 83 +++++++++++++++
datafusion/sql/tests/sql_integration.rs | 141 ++++++++++++++++++++++++++
datafusion/sqllogictest/test_files/window.slt | 111 ++++++++++++++++++++
13 files changed, 704 insertions(+), 20 deletions(-)
diff --git a/datafusion/core/src/physical_planner.rs
b/datafusion/core/src/physical_planner.rs
index 882956a63c..f00a1b555a 100644
--- a/datafusion/core/src/physical_planner.rs
+++ b/datafusion/core/src/physical_planner.rs
@@ -1270,16 +1270,20 @@ impl DefaultPhysicalPlanner {
LogicalPlan::SubqueryAlias(_) => children.one()?,
LogicalPlan::Limit(limit) => {
let input = children.one()?;
+ // `get_skip_type` / `get_fetch_type` only return a non literal
+ // type for an expression that is present
let SkipType::Literal(skip) = limit.get_skip_type()? else {
+ let skip = limit.skip.as_deref().map(ToString::to_string);
return not_impl_err!(
- "Unsupported OFFSET expression: {:?}",
- limit.skip
+ "Unsupported OFFSET expression: {}",
+ skip.unwrap_or_default()
);
};
let FetchType::Literal(fetch) = limit.get_fetch_type()? else {
+ let fetch =
limit.fetch.as_deref().map(ToString::to_string);
return not_impl_err!(
- "Unsupported LIMIT expression: {:?}",
- limit.fetch
+ "Unsupported LIMIT expression: {}",
+ fetch.unwrap_or_default()
);
};
diff --git a/datafusion/core/tests/dataframe/mod.rs
b/datafusion/core/tests/dataframe/mod.rs
index 9b0ca1b276..392a6391bd 100644
--- a/datafusion/core/tests/dataframe/mod.rs
+++ b/datafusion/core/tests/dataframe/mod.rs
@@ -5334,6 +5334,25 @@ async fn consecutive_projection_same_schema() ->
Result<()> {
Ok(())
}
+#[tokio::test]
+async fn window_function_in_group_by_is_rejected() -> Result<()> {
+ // https://github.com/apache/datafusion/issues/4610
+ //
+ // The SQL planner rejects this placement itself, but the DataFrame API
+ // has no such check, so the physical planner's backstop is what reports it
+ let err = test_table()
+ .await?
+ .aggregate(vec![row_number()], vec![count(col("c1"))])?
+ .collect()
+ .await
+ .expect_err("a window function cannot be a grouping expression");
+ assert_snapshot!(
+ err.strip_backtrace(),
+ @"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"
+ );
+ Ok(())
+}
+
async fn create_test_table(name: &str) -> Result<DataFrame> {
let schema = Arc::new(Schema::new(vec![
Field::new("a", DataType::Utf8, false),
diff --git a/datafusion/expr/src/logical_plan/builder.rs
b/datafusion/expr/src/logical_plan/builder.rs
index b2522c8533..36aa67bbe7 100644
--- a/datafusion/expr/src/logical_plan/builder.rs
+++ b/datafusion/expr/src/logical_plan/builder.rs
@@ -3131,6 +3131,50 @@ mod tests {
Ok(())
}
+ #[test]
+ fn plan_builder_filter_rejects_window_functions() -> Result<()> {
+ // https://github.com/apache/datafusion/issues/4610
+ let sum_over = |arg| {
+ Expr::from(expr::WindowFunction::new(
+ crate::WindowFunctionDefinition::AggregateUDF(
+ crate::test::function_stub::sum_udaf(),
+ ),
+ vec![arg],
+ ))
+ };
+ let scan = || table_scan(Some("employee_csv"), &employee_schema(),
Some(vec![4]));
+
+ let err = scan()?
+ .filter(sum_over(col("salary")).gt(lit(10)))
+ .expect_err("window functions in a filter should be rejected");
+ assert_snapshot!(
+ err.strip_backtrace(),
+ @"Error during planning: Window function calls are not allowed in
filter predicates: 'sum(employee_csv.salary) ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING'"
+ );
+
+ let err = scan()?
+ .having(sum_over(col("salary")).gt(lit(10)))
+ .expect_err("window functions in a having should be rejected");
+ assert_snapshot!(
+ err.strip_backtrace(),
+ @"Error during planning: Window function calls are not allowed in
filter predicates: 'sum(employee_csv.salary) ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING'"
+ );
+
+ // computing the window function first and filtering on its result is
+ // the supported form
+ let plan = scan()?
+ .window(vec![sum_over(col("salary")).alias("total")])?
+ .filter(col("total").gt(lit(10)))?
+ .build()?;
+ assert_snapshot!(plan, @r"
+ Filter: total > Int32(10)
+ WindowAggr: windowExpr=[[sum(employee_csv.salary) ROWS BETWEEN
UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING AS total]]
+ TableScan: employee_csv projection=[salary]
+ ");
+
+ Ok(())
+ }
+
#[test]
fn test_join_metadata() -> Result<()> {
let left_schema = DFSchema::new_with_metadata(
diff --git a/datafusion/expr/src/logical_plan/plan.rs
b/datafusion/expr/src/logical_plan/plan.rs
index ab1ee1cdf4..cd0aee1322 100644
--- a/datafusion/expr/src/logical_plan/plan.rs
+++ b/datafusion/expr/src/logical_plan/plan.rs
@@ -41,9 +41,10 @@ use crate::logical_plan::display::{GraphvizVisitor,
IndentVisitor};
use crate::logical_plan::extension::UserDefinedLogicalNode;
use crate::logical_plan::{DmlStatement, Statement, WriteOp};
use crate::utils::{
- check_aggregate_and_window_nesting, enumerate_grouping_sets,
expr_to_columns,
- exprlist_to_fields, find_out_reference_exprs, grouping_set_expr_count,
- grouping_set_to_exprlist, merge_schema, split_conjunction,
+ check_aggregate_and_window_nesting, check_no_window_functions,
+ enumerate_grouping_sets, expr_to_columns, exprlist_to_fields,
+ find_out_reference_exprs, grouping_set_expr_count,
grouping_set_to_exprlist,
+ merge_schema, split_conjunction,
};
use crate::{
BinaryExpr, CreateMemoryTable, CreateView, Execute, Expr, ExprSchemable,
GroupingSet,
@@ -2822,12 +2823,13 @@ pub struct Filter {
impl Filter {
/// Create a new filter operator.
///
- /// Skips the type-checking and dealiasing done in [Self::try_new].
- /// For internal use in DataFusion only.
+ /// Skips the type-checking, window function check and dealiasing done in
+ /// [Self::try_new]. For internal use in DataFusion only.
///
/// **Preconditions:**
/// - the `predicate` expression returns a boolean value
/// - the `predicate` expression is not aliased
+ /// - the `predicate` expression contains no window function calls
#[doc(hidden)]
pub fn new(predicate: Expr, input: Arc<LogicalPlan>) -> Self {
Self { predicate, input }
@@ -2835,6 +2837,10 @@ impl Filter {
/// Create a new filter operator.
///
+ /// Returns an error if the predicate is not boolean or contains a window
+ /// function call, which cannot be evaluated by a filter (see
+ /// [`check_no_window_functions`]).
+ ///
/// Notes: as Aliases have no effect on the output of a filter operator,
/// they are removed from the predicate expression.
pub fn try_new(predicate: Expr, input: Arc<LogicalPlan>) -> Result<Self> {
@@ -2853,6 +2859,11 @@ impl Filter {
}
fn try_new_internal(predicate: Expr, input: Arc<LogicalPlan>) ->
Result<Self> {
+ // Filters are evaluated before window functions are computed, so a
+ // window call in the predicate has no physical equivalent. Reject it
+ // here rather than failing during physical planning.
+ check_no_window_functions(&predicate, "filter predicates")?;
+
// Filter predicates must return a boolean value so we try and
validate that here.
// Note that it is not always possible to resolve the predicate
expression during plan
// construction (such as with correlated subqueries) so we make a best
effort here and
diff --git a/datafusion/expr/src/utils.rs b/datafusion/expr/src/utils.rs
index 6c1dbacf4b..4ca12c5e03 100644
--- a/datafusion/expr/src/utils.rs
+++ b/datafusion/expr/src/utils.rs
@@ -750,6 +750,74 @@ fn first_span(expr: &Expr) -> Option<Span> {
span
}
+/// Returns an error if `expr` contains a window function call.
+///
+/// `clause` names where `expr` came from and completes the message
+/// `Window function calls are not allowed in {clause}`, for example `WHERE` or
+/// `HAVING`. Filters are evaluated before window functions are computed, so a
+/// window call in a filter predicate has no physical equivalent and is not
+/// valid SQL either (PostgreSQL: `window functions are not allowed in
HAVING`).
+/// Rejecting it while the logical plan is built gives an error that names the
+/// offending call instead of a late physical planning failure.
+///
+/// [`Filter::try_new`] calls this for every predicate, so the SQL planner and
+/// the `DataFrame`/`LogicalPlanBuilder` paths are covered without callers
+/// invoking it directly. Window calls inside subqueries of `expr` are not
+/// visited and are legal.
+///
+/// The error is built by [`window_function_not_allowed_err`] with the best
+/// effort span of the call (see [`Expr::spans`]) and a generic help message. A
+/// caller that knows where the call is in the original query, such as the SQL
+/// planner, can build a more precise error with that function directly.
+///
+/// [`Filter::try_new`]: crate::logical_plan::Filter::try_new
+pub fn check_no_window_functions(expr: &Expr, clause: &str) -> Result<()> {
+ match first_window_function(expr) {
+ None => Ok(()),
+ Some(window) => Err(window_function_not_allowed_err(
+ window,
+ clause,
+ first_span(window),
+ format!(
+ "Compute '{window}' first, in a Window node or an inner query,
and filter on its result"
+ ),
+ )),
+ }
+}
+
+/// The first window function call in `expr`, if any. Subqueries are not
+/// visited.
+fn first_window_function(expr: &Expr) -> Option<&Expr> {
+ let mut window = None;
+ expr.apply(|e| {
+ if matches!(e, Expr::WindowFunction(_)) {
+ window = Some(e);
+ Ok(TreeNodeRecursion::Stop)
+ } else {
+ Ok(TreeNodeRecursion::Continue)
+ }
+ })
+ .ok()?;
+ window
+}
+
+/// The planning error for `window`, a window function call that appeared in
+/// `clause` where it is not allowed (see [`check_no_window_functions`]).
+///
+/// The message is `Window function calls are not allowed in {clause}:
+/// '{window}'`. The error carries a [`Diagnostic`] with the same message,
+/// pointing at `span`, and with `help`.
+pub fn window_function_not_allowed_err(
+ window: &Expr,
+ clause: &str,
+ span: Option<Span>,
+ help: impl Into<String>,
+) -> DataFusionError {
+ let message = format!("Window function calls are not allowed in {clause}");
+ plan_datafusion_err!("{message}: '{window}'")
+ .with_diagnostic(Diagnostic::new_error(message, span).with_help(help,
None))
+}
+
/// Collect all deeply nested `Expr::WindowFunction`. They are returned in
order of occurrence
/// (depth first), with duplicates omitted.
pub fn find_window_exprs<'a>(exprs: impl IntoIterator<Item = &'a Expr>) ->
Vec<Expr> {
@@ -2102,4 +2170,43 @@ mod tests {
@"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'"
);
}
+
+ #[test]
+ fn test_check_no_window_functions() -> Result<()> {
+ use crate::test::function_stub::sum;
+ use insta::assert_snapshot;
+
+ // columns, scalar expressions and aggregates are fine
+ check_no_window_functions(&col("a"), "WHERE")?;
+ check_no_window_functions(&(col("a") + lit(1)).gt(lit(0)), "WHERE")?;
+ check_no_window_functions(&sum(col("a")).gt(lit(0)), "HAVING")?;
+
+ // a bare window call
+ let err =
+ check_no_window_functions(&sum_over(vec![col("a")]),
"WHERE").unwrap_err();
+ assert_snapshot!(
+ err.strip_backtrace(),
+ @"Error during planning: Window function calls are not allowed in
WHERE: 'sum(a) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING'"
+ );
+ let diag = err.diagnostic().expect("diagnostic");
+ assert_snapshot!(
+ diag.message,
+ @"Window function calls are not allowed in WHERE"
+ );
+ assert_snapshot!(
+ diag.helps[0].message,
+ @"Compute 'sum(a) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING' first, in a Window node or an inner query, and filter on its result"
+ );
+
+ // a window call over an aggregate, nested below other expressions,
+ // names the clause it was given
+ let predicate =
sum_over(vec![sum(col("a"))]).gt(lit(10)).and(col("b"));
+ let err = check_no_window_functions(&predicate, "HAVING").unwrap_err();
+ assert_snapshot!(
+ err.strip_backtrace(),
+ @"Error during planning: Window function calls are not allowed in
HAVING: 'sum(sum(a)) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING'"
+ );
+
+ Ok(())
+ }
}
diff --git a/datafusion/physical-expr/src/planner.rs
b/datafusion/physical-expr/src/planner.rs
index c5b67cde8e..9522b0910c 100644
--- a/datafusion/physical-expr/src/planner.rs
+++ b/datafusion/physical-expr/src/planner.rs
@@ -689,6 +689,16 @@ pub fn create_physical_expr(
Arc::clone(schema_field),
)))
}
+ // Window and aggregate functions are evaluated by dedicated plan
+ // nodes, never by a physical expression. Reaching this point means
+ // the function is in a position where it cannot be evaluated, so
+ // report that instead of dumping the internal representation.
+ Expr::WindowFunction(_) => plan_err!(
+ "Window function '{e}' is not supported in this position. Window
functions are supported in the SELECT list, ORDER BY, DISTINCT ON and QUALIFY"
+ ),
+ Expr::AggregateFunction(_) => plan_err!(
+ "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"
+ ),
other => {
not_impl_err!("Physical plan does not support logical expression
{other:?}")
}
diff --git a/datafusion/sql/src/expr/mod.rs b/datafusion/sql/src/expr/mod.rs
index f1661028ed..0fa1fc16bf 100644
--- a/datafusion/sql/src/expr/mod.rs
+++ b/datafusion/sql/src/expr/mod.rs
@@ -30,13 +30,14 @@ use sqlparser::ast::{
use sqlparser::ast::{Query, Visit, Visitor};
use datafusion_common::{
- DFSchema, Diagnostic, Result, ScalarValue, Span, internal_datafusion_err,
+ DFSchema, Diagnostic, HashMap, Result, ScalarValue, Span,
internal_datafusion_err,
internal_err, not_impl_err, plan_err,
};
use datafusion_expr::expr::ScalarFunction;
use datafusion_expr::expr::SetQuantifier;
use datafusion_expr::expr::{InList, WildcardOptions};
+use datafusion_expr::utils::{find_window_exprs,
window_function_not_allowed_err};
use datafusion_expr::{
Between, BinaryExpr, Cast, Expr, ExprSchemable, GetFieldAccess, Like,
Literal,
Operator, TryCast, lit, when,
@@ -137,12 +138,108 @@ impl<S: ContextProvider> Visitor for
NullEqualityPredicateVisitor<'_, '_, S> {
}
}
+/// Finds the location of the first window function call in a SQL expression.
+/// An identifier that is an alias of a `SELECT` expression containing a window
+/// function call counts as well, since aliases are resolved before the window
+/// function check (`HAVING total > 0` where `total` is `sum(x) OVER ()`).
+/// Subqueries are skipped: window functions are legal there.
+struct WindowFunctionSpanVisitor<'a, 'b, S: ContextProvider> {
+ sql_to_rel: &'a SqlToRel<'b, S>,
+ aliases: &'a HashMap<String, Expr>,
+ subquery_depth: usize,
+ span: Option<sqlparser::tokenizer::Span>,
+}
+
+impl<S: ContextProvider> Visitor for WindowFunctionSpanVisitor<'_, '_, S> {
+ type Break = ();
+
+ fn pre_visit_query(&mut self, _query: &Query) -> ControlFlow<Self::Break> {
+ self.subquery_depth += 1;
+ ControlFlow::Continue(())
+ }
+
+ fn post_visit_query(&mut self, _query: &Query) -> ControlFlow<Self::Break>
{
+ self.subquery_depth -= 1;
+ ControlFlow::Continue(())
+ }
+
+ fn pre_visit_expr(&mut self, expr: &SQLExpr) -> ControlFlow<Self::Break> {
+ if self.subquery_depth > 0 {
+ return ControlFlow::Continue(());
+ }
+ let found = match expr {
+ SQLExpr::Function(function) => function.over.is_some(),
+ SQLExpr::Identifier(ident) => {
+ let name =
self.sql_to_rel.ident_normalizer.normalize(ident.clone());
+ self.aliases
+ .get(&name)
+ .is_some_and(|aliased|
!find_window_exprs([aliased]).is_empty())
+ }
+ _ => false,
+ };
+ if found {
+ self.span = Some(expr.span());
+ ControlFlow::Break(())
+ } else {
+ ControlFlow::Continue(())
+ }
+ }
+}
+
+/// Help for a window function in `WHERE` or `HAVING`: `QUALIFY` is the clause
+/// that filters on window function results.
+pub(crate) const QUALIFY_HELP: &str = "Move the condition that uses this
window function to a QUALIFY clause, which is evaluated after window functions
are computed";
+
+/// Returns an error if `expr`, planned from a clause where window functions
+/// cannot be evaluated, contains a window function call. `clause` names it in
+/// the message, `help` says how to rewrite the query and `span` is the
location
+/// of the call in the SQL text, see [`SqlToRel::window_function_span`].
+///
+/// The physical planner rejects such expressions in every position, but
+/// without a span or a clause-specific hint. This check exists to give a
+/// better error for the common mistakes.
+pub(crate) fn reject_window_functions(
+ expr: &Expr,
+ clause: &str,
+ help: &str,
+ span: Option<Span>,
+) -> Result<()> {
+ match find_window_exprs([expr]).into_iter().next() {
+ None => Ok(()),
+ Some(window) => Err(window_function_not_allowed_err(&window, clause,
span, help)),
+ }
+}
+
impl<S: ContextProvider> SqlToRel<'_, S> {
pub(crate) fn warn_on_null_equality_predicate(&self, predicate: &SQLExpr) {
let mut visitor = NullEqualityPredicateVisitor::new(self);
let _ = predicate.visit(&mut visitor);
}
+ /// The location in the SQL text of the first window function call in
+ /// `expr`, or of the first identifier that is an alias (in `aliases`) of a
+ /// `SELECT` expression containing one. Falls back to the location of the
+ /// whole of `expr`, and returns `None` only if the parser recorded no
+ /// spans.
+ ///
+ /// Used to point the [`Diagnostic`] for a window function in `WHERE` or
+ /// `HAVING` at the offending call, which the planned [`Expr`] cannot do:
+ /// only columns carry spans there.
+ pub(crate) fn window_function_span(
+ &self,
+ expr: &SQLExpr,
+ aliases: &HashMap<String, Expr>,
+ ) -> Option<Span> {
+ let mut visitor = WindowFunctionSpanVisitor {
+ sql_to_rel: self,
+ aliases,
+ subquery_depth: 0,
+ span: None,
+ };
+ let _ = expr.visit(&mut visitor);
+ Span::try_from_sqlparser_span(visitor.span.unwrap_or_else(||
expr.span()))
+ }
+
pub(crate) fn sql_expr_to_logical_expr_with_alias(
&self,
sql: SQLExprWithAlias,
diff --git a/datafusion/sql/src/relation/join.rs
b/datafusion/sql/src/relation/join.rs
index 73c2f05504..d8085133e5 100644
--- a/datafusion/sql/src/relation/join.rs
+++ b/datafusion/sql/src/relation/join.rs
@@ -15,14 +15,20 @@
// specific language governing permissions and limitations
// under the License.
+use crate::expr::reject_window_functions;
use crate::planner::{ContextProvider, PlannerContext, SqlToRel};
-use datafusion_common::{Column, Result, not_impl_err, plan_datafusion_err,
plan_err};
+use datafusion_common::{
+ Column, HashMap, Result, not_impl_err, plan_datafusion_err, plan_err,
+};
use datafusion_expr::{JoinType, LogicalPlan, LogicalPlanBuilder};
use sqlparser::ast::{
Join, JoinConstraint, JoinOperator, ObjectName, TableFactor,
TableWithJoins,
};
use std::collections::HashSet;
+const JOIN_ON_HELP: &str =
+ "Compute the window function in a subquery and join on its result";
+
impl<S: ContextProvider> SqlToRel<'_, S> {
pub(crate) fn plan_table_with_joins(
&self,
@@ -126,7 +132,9 @@ impl<S: ContextProvider> SqlToRel<'_, S> {
match constraint {
JoinConstraint::On(sql_on) => {
+ let window_span = self.window_function_span(&sql_on,
&HashMap::new());
let on = self.sql_to_expr(sql_on, &join_schema,
planner_context)?;
+ reject_window_functions(&on, "JOIN ON", JOIN_ON_HELP,
window_span)?;
LogicalPlanBuilder::from(left)
.asof_join_on(right, Some(on), match_condition)?
.build()
@@ -188,7 +196,11 @@ impl<S: ContextProvider> SqlToRel<'_, S> {
let join_schema = left.schema().join(right.schema())?;
// parse ON expression
self.warn_on_null_equality_predicate(&sql_expr);
+ let window_span = self.window_function_span(&sql_expr,
&HashMap::new());
let expr = self.sql_to_expr(sql_expr, &join_schema,
planner_context)?;
+ // A join condition is evaluated before window functions are
+ // computed, so they may not appear in it
+ reject_window_functions(&expr, "JOIN ON", JOIN_ON_HELP,
window_span)?;
LogicalPlanBuilder::from(left)
.join_on(right, join_type, Some(expr))?
.build()
diff --git a/datafusion/sql/src/select.rs b/datafusion/sql/src/select.rs
index bbd9d203eb..86916c5693 100644
--- a/datafusion/sql/src/select.rs
+++ b/datafusion/sql/src/select.rs
@@ -19,6 +19,7 @@ use std::collections::HashSet;
use std::ops::ControlFlow;
use std::sync::Arc;
+use crate::expr::{QUALIFY_HELP, reject_window_functions};
use crate::planner::{ContextProvider, PlannerContext, SqlToRel};
use crate::query::to_order_by_exprs_with_select;
use crate::utils::{
@@ -32,7 +33,9 @@ use crate::utils::{
use arrow::datatypes::DataType;
use datafusion_common::error::DataFusionErrorBuilder;
use datafusion_common::tree_node::{TreeNode, TreeNodeRecursion};
-use datafusion_common::{Column, DFSchema, DFSchemaRef, Result, not_impl_err,
plan_err};
+use datafusion_common::{
+ Column, DFSchema, DFSchemaRef, HashMap, Result, not_impl_err, plan_err,
+};
use datafusion_common::{NullHandling, RecursionUnnestOption, UnnestOptions};
use datafusion_expr::ExprSchemable;
use datafusion_expr::builder::get_struct_unnested_columns;
@@ -202,6 +205,7 @@ impl<S: ContextProvider> SqlToRel<'_, S> {
.having
.map::<Result<Expr>, _>(|having_expr| {
self.warn_on_null_equality_predicate(&having_expr);
+ let window_span = self.window_function_span(&having_expr,
&alias_map);
let having_expr = self.sql_expr_to_logical_expr(
having_expr,
&combined_schema,
@@ -221,6 +225,15 @@ impl<S: ContextProvider> SqlToRel<'_, S> {
// SELECT c1, MAX(c2) AS m FROM t GROUP BY c1 HAVING MAX(c2)
> 10;
//
let having_expr = resolve_aliases_to_exprs(having_expr,
&alias_map)?;
+ // HAVING is evaluated before window functions are computed, so
+ // they may not appear there (checked after alias resolution so
+ // that an alias of a window function is rejected too)
+ reject_window_functions(
+ &having_expr,
+ "HAVING",
+ QUALIFY_HELP,
+ window_span,
+ )?;
let having_expr = normalize_col(having_expr, &projected_plan)?;
let (having_expr, _) =
having_expr.infer_placeholder_types(&combined_schema)?;
@@ -233,21 +246,31 @@ impl<S: ContextProvider> SqlToRel<'_, S> {
exprs
.into_iter()
.map(|e| {
- let group_by_expr = self.sql_expr_to_logical_expr(
- e,
- &combined_schema,
- planner_context,
- )?;
-
// Aliases from the projection can conflict with
same-named expressions in the input
let mut alias_map = alias_map.clone();
for f in base_plan.schema().fields() {
alias_map.remove(f.name());
}
+ let window_span = self.window_function_span(&e,
&alias_map);
+
+ let group_by_expr = self.sql_expr_to_logical_expr(
+ e,
+ &combined_schema,
+ planner_context,
+ )?;
let group_by_expr =
resolve_aliases_to_exprs(group_by_expr, &alias_map)?;
let group_by_expr =
resolve_positions_to_exprs(group_by_expr,
&select_exprs)?;
+ // Window functions are computed after grouping, so they
may
+ // not be grouped on (checked after aliases and positions
are
+ // resolved so `GROUP BY rn` / `GROUP BY 1` are rejected
too)
+ reject_window_functions(
+ &group_by_expr,
+ "GROUP BY",
+ "Compute the window function in a subquery and group
by its result",
+ window_span,
+ )?;
let group_by_expr = normalize_col(group_by_expr,
&projected_plan)?;
self.validate_schema_satisfies_exprs(
base_plan.schema(),
@@ -885,6 +908,8 @@ impl<S: ContextProvider> SqlToRel<'_, S> {
let fallback_schemas = plan.fallback_normalize_schemas();
self.warn_on_null_equality_predicate(&predicate_expr);
+ let window_span =
+ self.window_function_span(&predicate_expr,
&HashMap::new());
let filter_expr =
self.sql_to_expr(predicate_expr, plan.schema(),
planner_context)?;
@@ -896,6 +921,14 @@ impl<S: ContextProvider> SqlToRel<'_, S> {
"Aggregate functions are not allowed in the WHERE
clause. Consider using HAVING instead"
);
}
+ // WHERE is evaluated before window functions are computed, so
+ // they may not appear there either
+ reject_window_functions(
+ &filter_expr,
+ "WHERE",
+ QUALIFY_HELP,
+ window_span,
+ )?;
let mut using_columns = HashSet::new();
expr_to_columns(&filter_expr, &mut using_columns)?;
diff --git a/datafusion/sql/src/values.rs b/datafusion/sql/src/values.rs
index a1df1fe1b1..2f15785520 100644
--- a/datafusion/sql/src/values.rs
+++ b/datafusion/sql/src/values.rs
@@ -17,8 +17,9 @@
use std::sync::Arc;
+use crate::expr::reject_window_functions;
use crate::planner::{ContextProvider, PlannerContext, SqlToRel};
-use datafusion_common::{DFSchema, Result, not_impl_err};
+use datafusion_common::{DFSchema, HashMap, Result, not_impl_err};
use datafusion_expr::{LogicalPlan, LogicalPlanBuilder};
use sqlparser::ast::Values as SQLValues;
@@ -45,7 +46,18 @@ impl<S: ContextProvider> SqlToRel<'_, S> {
.map(|row| {
row.content
.into_iter()
- .map(|v| self.sql_to_expr(v, &empty_schema,
planner_context))
+ .map(|v| {
+ let window_span = self.window_function_span(&v,
&HashMap::new());
+ let expr = self.sql_to_expr(v, &empty_schema,
planner_context)?;
+ // A VALUES row has no input rows to compute a window
over
+ reject_window_functions(
+ &expr,
+ "VALUES",
+ "Compute the window function in a SELECT over a
table or subquery instead",
+ window_span,
+ )?;
+ Ok(expr)
+ })
.collect::<Result<Vec<_>>>()
})
.collect::<Result<Vec<_>>>()?;
diff --git a/datafusion/sql/tests/cases/diagnostic.rs
b/datafusion/sql/tests/cases/diagnostic.rs
index c385393bed..2a6983169c 100644
--- a/datafusion/sql/tests/cases/diagnostic.rs
+++ b/datafusion/sql/tests/cases/diagnostic.rs
@@ -708,3 +708,86 @@ fn test_nested_window_function() -> Result<()> {
assert_eq!(diag.span, Some(spans["a"]));
Ok(())
}
+
+#[test]
+fn test_window_function_in_where() -> Result<()> {
+ // https://github.com/apache/datafusion/issues/4610
+ //
+ // The span is taken from the SQL text and covers the window function call.
+ // sqlparser's span for a function call ends at its last argument, so the
+ // `) OVER ()` part is not included.
+ let query = "SELECT id FROM person WHERE /*a*/sum(age/*a*/) OVER () > 0";
+ let spans = get_spans(query);
+ let diag = do_query(query);
+ assert_snapshot!(diag.message, @"Window function calls are not allowed in
WHERE");
+ assert_eq!(diag.span, Some(spans["a"]));
+ assert_snapshot!(
+ diag.helps[0].message,
+ @"Move the condition that uses this window function to a QUALIFY
clause, which is evaluated after window functions are computed"
+ );
+ Ok(())
+}
+
+#[test]
+fn test_window_function_in_where_after_subquery() -> Result<()> {
+ // A window function inside a subquery is legal and must not be the one the
+ // span points at
+ let query = "SELECT id FROM person WHERE id IN (SELECT id FROM person
QUALIFY sum(age) OVER () > 0) AND /*a*/sum(age/*a*/) OVER () > 0";
+ let spans = get_spans(query);
+ let diag = do_query(query);
+ assert_snapshot!(diag.message, @"Window function calls are not allowed in
WHERE");
+ assert_eq!(diag.span, Some(spans["a"]));
+ Ok(())
+}
+
+#[test]
+fn test_window_function_in_having() -> Result<()> {
+ // https://github.com/apache/datafusion/issues/4610
+ let query = "SELECT first_name FROM person GROUP BY first_name HAVING
/*a*/sum(sum(age/*a*/)) OVER () > 0";
+ let spans = get_spans(query);
+ let diag = do_query(query);
+ assert_snapshot!(diag.message, @"Window function calls are not allowed in
HAVING");
+ assert_eq!(diag.span, Some(spans["a"]));
+ Ok(())
+}
+
+#[test]
+fn test_window_function_in_join_on() -> Result<()> {
+ let query = "SELECT p.id FROM person p JOIN person q ON p.id = q.id AND
/*a*/sum(p.age/*a*/) OVER () > 0";
+ let spans = get_spans(query);
+ let diag = do_query(query);
+ assert_snapshot!(diag.message, @"Window function calls are not allowed in
JOIN ON");
+ assert_eq!(diag.span, Some(spans["a"]));
+ assert_snapshot!(
+ diag.helps[0].message,
+ @"Compute the window function in a subquery and join on its result"
+ );
+ Ok(())
+}
+
+#[test]
+fn test_window_function_in_group_by() -> Result<()> {
+ let query =
+ "SELECT first_name FROM person GROUP BY first_name, /*a*/sum(age/*a*/)
OVER ()";
+ let spans = get_spans(query);
+ let diag = do_query(query);
+ assert_snapshot!(diag.message, @"Window function calls are not allowed in
GROUP BY");
+ assert_eq!(diag.span, Some(spans["a"]));
+ assert_snapshot!(
+ diag.helps[0].message,
+ @"Compute the window function in a subquery and group by its result"
+ );
+ Ok(())
+}
+
+#[test]
+fn test_window_function_alias_in_having() -> Result<()> {
+ // An alias of a window function is resolved before the check; the span
+ // points at the alias
+ let query = "SELECT first_name, sum(sum(age)) OVER () AS total FROM person
GROUP BY first_name HAVING /*a*/total/*a*/ > 0";
+ let spans = get_spans(query);
+ let diag = do_query(query);
+ assert_snapshot!(diag.message, @"Window function calls are not allowed in
HAVING");
+ assert_eq!(diag.span, Some(spans["a"]));
+ Ok(())
+}
diff --git a/datafusion/sql/tests/sql_integration.rs
b/datafusion/sql/tests/sql_integration.rs
index 1fadca5eff..2bd06ee4ba 100644
--- a/datafusion/sql/tests/sql_integration.rs
+++ b/datafusion/sql/tests/sql_integration.rs
@@ -1972,6 +1972,147 @@ fn select_nested_window_function() {
);
}
+#[test]
+fn select_window_function_in_where() {
+ // https://github.com/apache/datafusion/issues/4610
+ let err = logical_plan("SELECT id FROM person WHERE sum(age) OVER () > 0")
+ .expect_err("query should have failed");
+ assert_snapshot!(
+ err.strip_backtrace(),
+ @"Error during planning: Window function calls are not allowed in
WHERE: 'sum(person.age) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING'"
+ );
+
+ // a window function inside a subquery of the predicate is legal
+ let plan = logical_plan(
+ "SELECT id FROM person WHERE id IN (SELECT id FROM person QUALIFY
sum(age) OVER () > 0)",
+ )
+ .unwrap();
+ assert_snapshot!(
+ plan,
+ @r"
+ Projection: person.id
+ Filter: person.id IN (<subquery>)
+ Subquery:
+ Projection: person.id
+ Filter: sum(person.age) ROWS BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING > Int64(0)
+ WindowAggr: windowExpr=[[sum(person.age) ROWS BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING]]
+ TableScan: person
+ TableScan: person
+ "
+ );
+}
+
+#[test]
+fn select_window_function_in_having() {
+ // https://github.com/apache/datafusion/issues/4610
+ let err = logical_plan(
+ "SELECT state, sum(count(age)) OVER () AS total FROM person GROUP BY
state HAVING sum(count(age)) OVER () > 0",
+ )
+ .expect_err("query should have failed");
+ assert_snapshot!(
+ err.strip_backtrace(),
+ @"Error during planning: Window function calls are not allowed in
HAVING: 'sum(count(person.age)) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING'"
+ );
+
+ // an alias of a window function is resolved before the check
+ let err = logical_plan(
+ "SELECT state, sum(count(age)) OVER () AS total FROM person GROUP BY
state HAVING total > 0",
+ )
+ .expect_err("query should have failed");
+ assert_snapshot!(
+ err.strip_backtrace(),
+ @"Error during planning: Window function calls are not allowed in
HAVING: 'sum(count(person.age)) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING'"
+ );
+
+ // the same window function is legal in the SELECT list and in QUALIFY
+ let plan = logical_plan(
+ "SELECT state, sum(count(age)) OVER () AS total FROM person GROUP BY
state QUALIFY sum(count(age)) OVER () > 0",
+ )
+ .unwrap();
+ assert_snapshot!(
+ plan,
+ @r"
+ Projection: person.state, sum(count(person.age)) ROWS BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING AS total
+ Filter: sum(count(person.age)) ROWS BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING > Int64(0)
+ WindowAggr: windowExpr=[[sum(count(person.age)) ROWS BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING]]
+ Aggregate: groupBy=[[person.state]], aggr=[[count(person.age)]]
+ TableScan: person
+ "
+ );
+}
+
+#[test]
+fn select_window_function_in_join_on() {
+ // https://github.com/apache/datafusion/issues/4610
+ let err = logical_plan(
+ "SELECT p.id FROM person p JOIN person q ON p.id = q.id AND sum(p.age)
OVER () > 0",
+ )
+ .expect_err("query should have failed");
+ assert_snapshot!(
+ err.strip_backtrace(),
+ @"Error during planning: Window function calls are not allowed in JOIN
ON: 'sum(p.age) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING'"
+ );
+
+ // a window function in a joined subquery is legal
+ let plan = logical_plan(
+ "SELECT p.id FROM person p JOIN (SELECT id FROM person QUALIFY
sum(age) OVER () > 0) q ON p.id = q.id",
+ )
+ .unwrap();
+ assert_snapshot!(
+ plan,
+ @r"
+ Projection: p.id
+ Inner Join: Filter: p.id = q.id
+ SubqueryAlias: p
+ TableScan: person
+ SubqueryAlias: q
+ Projection: person.id
+ Filter: sum(person.age) ROWS BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING > Int64(0)
+ WindowAggr: windowExpr=[[sum(person.age) ROWS BETWEEN UNBOUNDED
PRECEDING AND UNBOUNDED FOLLOWING]]
+ TableScan: person
+ "
+ );
+}
+
+#[test]
+fn select_window_function_in_group_by() {
+ // https://github.com/apache/datafusion/issues/4610
+ let err = logical_plan("SELECT count(*) FROM person GROUP BY sum(age) OVER
()")
+ .expect_err("query should have failed");
+ assert_snapshot!(
+ err.strip_backtrace(),
+ @"Error during planning: Window function calls are not allowed in
GROUP BY: 'sum(person.age) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING'"
+ );
+
+ // through an alias of a select expression
+ let err =
+ logical_plan("SELECT sum(age) OVER () AS w, count(*) FROM person GROUP
BY w")
+ .expect_err("query should have failed");
+ assert_snapshot!(
+ err.strip_backtrace(),
+ @"Error during planning: Window function calls are not allowed in
GROUP BY: 'sum(person.age) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING'"
+ );
+
+ // through the position of a select expression
+ let err = logical_plan("SELECT sum(age) OVER (), count(*) FROM person
GROUP BY 1")
+ .expect_err("query should have failed");
+ assert_snapshot!(
+ err.strip_backtrace(),
+ @"Error during planning: Window function calls are not allowed in
GROUP BY: 'sum(person.age) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING'"
+ );
+}
+
+#[test]
+fn values_window_function() {
+ // https://github.com/apache/datafusion/issues/4610
+ let err =
+ logical_plan("VALUES (1, sum(2) OVER ())").expect_err("query should
have failed");
+ assert_snapshot!(
+ err.strip_backtrace(),
+ @"Error during planning: Window function calls are not allowed in
VALUES: 'sum(Int64(2)) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING'"
+ );
+}
+
#[test]
fn select_aggregate_inside_window_function() {
// an aggregate as the argument of a window function is legal: the window
diff --git a/datafusion/sqllogictest/test_files/window.slt
b/datafusion/sqllogictest/test_files/window.slt
index bd3eb6ba39..3d6620fc8a 100644
--- a/datafusion/sqllogictest/test_files/window.slt
+++ b/datafusion/sqllogictest/test_files/window.slt
@@ -7093,3 +7093,114 @@ SELECT last_value() OVER () FROM (VALUES (1), (2), (3))
t(x)
statement error DataFusion error: Error during planning: 'nth_value' does not
support zero arguments
SELECT nth_value() OVER () FROM (VALUES (1), (2), (3)) t(x)
+
+# Window functions are not allowed in WHERE or HAVING: both are evaluated
before
+# window functions are computed, so they are rejected during planning
+# issue: https://github.com/apache/datafusion/issues/4610
+statement ok
+CREATE TABLE window_in_filter_t(a INT, b INT) AS VALUES (1, 1), (2, 2);
+
+statement error DataFusion error: Error during planning: Window function calls
are not allowed in HAVING: 'sum\(count\(Int64\(1\)\) AS count\(\*\)\) ROWS
BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING'
+SELECT a, count(*) AS cnt, sum(count(*)) OVER () AS total
+FROM window_in_filter_t
+GROUP BY a
+HAVING sum(count(*)) OVER () >= 10;
+
+# ... also through an alias of the window function
+statement error DataFusion error: Error during planning: Window function calls
are not allowed in HAVING: 'sum\(count\(Int64\(1\)\) AS count\(\*\)\) ROWS
BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING'
+SELECT a, count(*) AS cnt, sum(count(*)) OVER () AS total
+FROM window_in_filter_t
+GROUP BY a
+HAVING total >= 10;
+
+statement error DataFusion error: Error during planning: Window function calls
are not allowed in HAVING: 'row_number\(\) ROWS BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING'
+SELECT a FROM window_in_filter_t GROUP BY a HAVING row_number() OVER () = 1;
+
+statement error DataFusion error: Error during planning: Window function calls
are not allowed in WHERE: 'row_number\(\) ROWS BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING'
+SELECT a FROM window_in_filter_t WHERE row_number() OVER () = 1;
+
+# The same window function is legal in the SELECT list ...
+query III
+SELECT a, count(*) AS cnt, sum(count(*)) OVER () AS total
+FROM window_in_filter_t
+GROUP BY a
+ORDER BY a;
+----
+1 1 2
+2 1 2
+
+# ... in QUALIFY, which is the clause for filtering on a window function ...
+query III
+SELECT a, count(*) AS cnt, sum(count(*)) OVER () AS total
+FROM window_in_filter_t
+GROUP BY a
+QUALIFY sum(count(*)) OVER () >= 2
+ORDER BY a;
+----
+1 1 2
+2 1 2
+
+# ... in the WHERE of an outer query ...
+query III
+SELECT * FROM (
+ SELECT a, count(*) AS cnt, sum(count(*)) OVER () AS total
+ FROM window_in_filter_t
+ GROUP BY a
+) s
+WHERE total >= 2
+ORDER BY a;
+----
+1 1 2
+2 1 2
+
+# ... and inside a subquery of a WHERE predicate
+query I
+SELECT a FROM window_in_filter_t
+WHERE a IN (SELECT a FROM window_in_filter_t QUALIFY row_number() OVER (ORDER
BY a) = 1);
+----
+1
+
+# Window functions are rejected during planning in JOIN ON, GROUP BY and VALUES
+# as well
+statement error DataFusion error: Error during planning: Window function calls
are not allowed in JOIN ON: 'row_number\(\) ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING'
+SELECT * FROM window_in_filter_t x JOIN window_in_filter_t y ON x.a = y.a AND
row_number() OVER () = 1;
+
+statement error DataFusion error: Error during planning: Window function calls
are not allowed in GROUP BY: 'row_number\(\) ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING'
+SELECT count(*) FROM window_in_filter_t GROUP BY row_number() OVER ();
+
+# ... also through an alias or the position of a select expression
+statement error DataFusion error: Error during planning: Window function calls
are not allowed in GROUP BY: 'row_number\(\) ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING'
+SELECT row_number() OVER () AS rn, count(*) FROM window_in_filter_t GROUP BY
rn;
+
+statement error DataFusion error: Error during planning: Window function calls
are not allowed in GROUP BY: 'row_number\(\) ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING'
+SELECT row_number() OVER (), count(*) FROM window_in_filter_t GROUP BY 1;
+
+statement error DataFusion error: Error during planning: Window function calls
are not allowed in VALUES: 'row_number\(\) ROWS BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING'
+VALUES (row_number() OVER ());
+
+statement error DataFusion error: Error during planning: Window function calls
are not allowed in VALUES: 'row_number\(\) ROWS BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING'
+INSERT INTO window_in_filter_t VALUES (row_number() OVER (), 1);
+
+# A window function in a joined subquery is legal
+query II
+SELECT x.a, y.total FROM window_in_filter_t x
+JOIN (SELECT a, sum(b) OVER () AS total FROM window_in_filter_t) y ON x.a = y.a
+ORDER BY x.a;
+----
+1 3
+2 3
+
+# Positions with no dedicated check are reported by the physical planner, which
+# names the function instead of dumping its internal representation
+statement error DataFusion error: This feature is not implemented: Unsupported
LIMIT expression: CAST\(row_number\(\) ROWS BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING AS Int64\)
+SELECT a FROM window_in_filter_t LIMIT row_number() OVER ();
+
+statement error DataFusion error: This feature is not implemented: Unsupported
OFFSET expression: CAST\(row_number\(\) ROWS BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING AS Int64\)
+SELECT a FROM window_in_filter_t OFFSET row_number() OVER ();
+
+# ... and the same for an aggregate function
+statement error DataFusion error: This feature is not implemented: Unsupported
LIMIT expression: count\(Int64\(1\)\) AS count\(\*\)
+SELECT a FROM window_in_filter_t LIMIT count(*);
+
+statement ok
+DROP TABLE window_in_filter_t;
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
