Skip to content

Commit 2d171c5

Browse files
committed
Resolve named windows in QUALIFY and ORDER BY, and not inside subqueries
1 parent 522eb1a commit 2d171c5

2 files changed

Lines changed: 124 additions & 42 deletions

File tree

‎datafusion/sql/src/select.rs‎

Lines changed: 69 additions & 42 deletions
Original file line numberDiff line numberDiff line change
@@ -34,7 +34,8 @@ use arrow::datatypes::DataType;
3434
use datafusion_common::error::DataFusionErrorBuilder;
3535
use datafusion_common::tree_node::{TreeNode, TreeNodeRecursion};
3636
use datafusion_common::{
37-
Column, DFSchema, DFSchemaRef, HashMap, Result, not_impl_err, plan_err,
37+
Column, DFSchema, DFSchemaRef, DataFusionError, HashMap, Result, not_impl_err,
38+
plan_err,
3839
};
3940
use datafusion_common::{NullHandling, RecursionUnnestOption, UnnestOptions};
4041
use datafusion_expr::ExprSchemable;
@@ -55,9 +56,9 @@ use datafusion_expr::{
5556

5657
use indexmap::IndexMap;
5758
use sqlparser::ast::{
58-
Distinct, Expr as SQLExpr, GroupByExpr, Ident, NamedWindowExpr, OrderBy,
59-
SelectItemQualifiedWildcardKind, WildcardAdditionalOptions, WindowSpec, WindowType,
60-
visit_expressions_mut,
59+
Distinct, Expr as SQLExpr, GroupByExpr, Ident, NamedWindowExpr, OrderBy, Query,
60+
SelectItemQualifiedWildcardKind, VisitMut, VisitorMut, WildcardAdditionalOptions,
61+
WindowSpec, WindowType,
6162
};
6263
use sqlparser::ast::{NamedWindowDefinition, Select, SelectItem, TableWithJoins};
6364

@@ -99,7 +100,7 @@ impl<S: ContextProvider> SqlToRel<'_, S> {
99100
pub(super) fn select_to_plan(
100101
&self,
101102
mut select: Select,
102-
query_order_by: Option<OrderBy>,
103+
mut query_order_by: Option<OrderBy>,
103104
planner_context: &mut PlannerContext,
104105
) -> Result<LogicalPlan> {
105106
// Check for unsupported syntax first
@@ -131,6 +132,8 @@ impl<S: ContextProvider> SqlToRel<'_, S> {
131132
// Handle named windows before processing the projection expression
132133
check_conflicting_windows(&select.named_window)?;
133134
self.match_window_definitions(&mut select.projection, &select.named_window)?;
135+
self.match_window_definitions(&mut select.qualify, &select.named_window)?;
136+
self.match_window_definitions(&mut query_order_by, &select.named_window)?;
134137

135138
// Process the SELECT expressions
136139
let select_exprs = self.prepare_select_exprs(
@@ -1420,52 +1423,29 @@ impl<S: ContextProvider> SqlToRel<'_, S> {
14201423
})
14211424
}
14221425

1423-
// If the projection is done over a named window, that window
1424-
// name must be defined. Otherwise, it gives an error.
1426+
/// Replaces every window that references a named window, as in `OVER w`
1427+
/// or `OVER (w ...)`, with the resolved window specification.
1428+
///
1429+
/// Subqueries are skipped: each resolves names against its own `WINDOW`
1430+
/// clause when it is planned.
14251431
fn match_window_definitions(
14261432
&self,
1427-
projection: &mut [SelectItem],
1433+
node: &mut impl VisitMut,
14281434
named_windows: &[NamedWindowDefinition],
14291435
) -> Result<()> {
14301436
let named_windows: Vec<(&NamedWindowDefinition, String)> = named_windows
14311437
.iter()
14321438
.map(|w| (w, self.ident_normalizer.normalize(w.0.clone())))
14331439
.collect();
1434-
for proj in projection.iter_mut() {
1435-
if let SelectItem::ExprWithAlias { expr, alias: _ }
1436-
| SelectItem::UnnamedExpr(expr) = proj
1437-
{
1438-
let mut err = None;
1439-
let _ = visit_expressions_mut(expr, |expr| {
1440-
if let SQLExpr::Function(f) = expr {
1441-
let resolved = match &f.over {
1442-
Some(WindowType::NamedWindow(ident)) => {
1443-
Some(self.resolve_named_window(ident, &named_windows))
1444-
}
1445-
Some(WindowType::WindowSpec(spec))
1446-
if spec.window_name.is_some() =>
1447-
{
1448-
Some(self.resolve_window_spec(spec, &named_windows))
1449-
}
1450-
_ => None,
1451-
};
1452-
match resolved {
1453-
Some(Ok(spec)) => f.over = Some(WindowType::WindowSpec(spec)),
1454-
Some(Err(e)) => {
1455-
err = Some(e);
1456-
return ControlFlow::Break(());
1457-
}
1458-
None => {}
1459-
}
1460-
}
1461-
ControlFlow::Continue(())
1462-
});
1463-
if let Some(err) = err {
1464-
return Err(err);
1465-
}
1466-
}
1440+
let mut resolver = NamedWindowResolver {
1441+
planner: self,
1442+
named_windows: &named_windows,
1443+
query_depth: 0,
1444+
};
1445+
match node.visit(&mut resolver) {
1446+
ControlFlow::Break(e) => Err(e),
1447+
ControlFlow::Continue(()) => Ok(()),
14671448
}
1468-
Ok(())
14691449
}
14701450

14711451
/// Returns the window specification of the named window `ident`.
@@ -1539,6 +1519,53 @@ impl<S: ContextProvider> SqlToRel<'_, S> {
15391519
}
15401520
}
15411521

1522+
/// Resolves named window references in the expressions of a single `SELECT`.
1523+
struct NamedWindowResolver<'a, 'b, S: ContextProvider> {
1524+
planner: &'a SqlToRel<'b, S>,
1525+
named_windows: &'a [(&'a NamedWindowDefinition, String)],
1526+
/// Number of subqueries enclosing the expression being visited.
1527+
query_depth: usize,
1528+
}
1529+
1530+
impl<S: ContextProvider> VisitorMut for NamedWindowResolver<'_, '_, S> {
1531+
type Break = DataFusionError;
1532+
1533+
fn pre_visit_query(&mut self, _query: &mut Query) -> ControlFlow<Self::Break> {
1534+
self.query_depth += 1;
1535+
ControlFlow::Continue(())
1536+
}
1537+
1538+
fn post_visit_query(&mut self, _query: &mut Query) -> ControlFlow<Self::Break> {
1539+
self.query_depth -= 1;
1540+
ControlFlow::Continue(())
1541+
}
1542+
1543+
fn post_visit_expr(&mut self, expr: &mut SQLExpr) -> ControlFlow<Self::Break> {
1544+
if self.query_depth > 0 {
1545+
return ControlFlow::Continue(());
1546+
}
1547+
let SQLExpr::Function(f) = expr else {
1548+
return ControlFlow::Continue(());
1549+
};
1550+
let resolved = match &f.over {
1551+
Some(WindowType::NamedWindow(ident)) => {
1552+
self.planner.resolve_named_window(ident, self.named_windows)
1553+
}
1554+
Some(WindowType::WindowSpec(spec)) if spec.window_name.is_some() => {
1555+
self.planner.resolve_window_spec(spec, self.named_windows)
1556+
}
1557+
_ => return ControlFlow::Continue(()),
1558+
};
1559+
match resolved {
1560+
Ok(spec) => {
1561+
f.over = Some(WindowType::WindowSpec(spec));
1562+
ControlFlow::Continue(())
1563+
}
1564+
Err(e) => ControlFlow::Break(e),
1565+
}
1566+
}
1567+
}
1568+
15421569
// If there are any multiple-defined windows, we raise an error.
15431570
fn check_conflicting_windows(window_defs: &[NamedWindowDefinition]) -> Result<()> {
15441571
for (i, window_def_i) in window_defs.iter().enumerate() {

‎datafusion/sqllogictest/test_files/window.slt‎

Lines changed: 55 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3574,6 +3574,61 @@ FROM named_window_prices
35743574
WINDOW w1 AS (w2 ROWS UNBOUNDED PRECEDING),
35753575
w2 AS (PARTITION BY ticker ORDER BY ts)
35763576

3577+
# Named windows are resolved in QUALIFY
3578+
query TIR
3579+
SELECT ticker, ts, price
3580+
FROM named_window_prices
3581+
QUALIFY MAX(price) OVER (w ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) = price
3582+
WINDOW w AS (PARTITION BY ticker ORDER BY ts)
3583+
ORDER BY ticker, ts
3584+
----
3585+
BTC 1 100
3586+
BTC 2 105
3587+
DOGE 1 0.1
3588+
DOGE 2 0.2
3589+
3590+
query TI
3591+
SELECT ticker, ts
3592+
FROM named_window_prices
3593+
QUALIFY ROW_NUMBER() OVER w = 1
3594+
WINDOW w AS (PARTITION BY ticker ORDER BY ts)
3595+
ORDER BY ticker
3596+
----
3597+
BTC 1
3598+
DOGE 1
3599+
3600+
# Named windows are resolved in ORDER BY
3601+
query TI
3602+
SELECT ticker, ts
3603+
FROM named_window_prices
3604+
WINDOW w AS (PARTITION BY ticker ORDER BY ts)
3605+
ORDER BY ROW_NUMBER() OVER (w ROWS UNBOUNDED PRECEDING) DESC, ticker
3606+
----
3607+
BTC 3
3608+
DOGE 3
3609+
BTC 2
3610+
DOGE 2
3611+
BTC 1
3612+
DOGE 1
3613+
3614+
# A subquery resolves window names against its own WINDOW clause
3615+
query I
3616+
SELECT (SELECT MAX(rn) FROM (
3617+
SELECT ROW_NUMBER() OVER (w ROWS UNBOUNDED PRECEDING) AS rn
3618+
FROM named_window_prices
3619+
WINDOW w AS (PARTITION BY ticker ORDER BY ts)))
3620+
FROM named_window_prices
3621+
WINDOW w AS (ORDER BY ts)
3622+
LIMIT 1
3623+
----
3624+
3
3625+
3626+
statement error DataFusion error: Error during planning: The window w is not defined!
3627+
SELECT (SELECT MAX(rn) FROM (
3628+
SELECT ROW_NUMBER() OVER w AS rn FROM named_window_prices))
3629+
FROM named_window_prices
3630+
WINDOW w AS (ORDER BY ts)
3631+
35773632
statement ok
35783633
DROP TABLE named_window_prices;
35793634

0 commit comments

Comments
 (0)