Skip to content

Commit 1b857a3

Browse files
cool9850311claude
andcommitted
fix: pick up a limit below a window the rule already grew past
Review feedback: one shape of #26659 was still left behind. A window the rule *can* grow past leaves the walk in `Phase::Apply`, and the guard on `get_limit` skipped every limit while in that phase. So on the way down to an inner window, the inner `GlobalLimitExec` was never read; the walk fell through to the cardinality check and dropped the chain at it instead, and the inner sort stayed a full sort although a `LIMIT 5` sat right above it. The phase only has to hold us back at a `SortPreservingMergeExec`, which is both a limit source and an apply target -- reading its fetch while carrying a limit down would overwrite the limit with it. A plain limit node is never an apply target, so reading one is always safe, and it is more current than the chain we were on, which has not found a sort to apply to. The guard now covers just the one node that needs it. `reset_limit` returns the phase to `FindOrGrow` rather than `optimize()` doing it at the call site, which is the one difference from the patch in the review. Starting a chain and abandoning the previous one are the same event, `reset_limit` only ever runs when `get_limit` found a limit, and the doc comment there promised the opposite and had to change either way. Happy to move it back to the call site if you prefer it there. The new case is the previous query with the outer frame changed from RANGE to ROWS, and the change of frame is what makes it fail: a frame the rule cannot grow past resets the state on the way down, so the limit below is seen afterwards, while a frame it can grow past does not. Both levels order by the same column, so `EnforceSorting` elides the outer sort and there is no `apply_limit` in between to reset the state either. Co-Authored-By: Claude Opus 5 <[email protected]>
1 parent 4877863 commit 1b857a3

2 files changed

Lines changed: 72 additions & 4 deletions

File tree

‎datafusion/physical-optimizer/src/limit_pushdown_past_window.rs‎

Lines changed: 12 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -55,11 +55,13 @@ impl TraverseState {
5555
*self = Self::default();
5656
}
5757

58-
/// Start a chain from a limit we just found. The phase is deliberately left
59-
/// alone: this only runs while we are still in [`Phase::FindOrGrow`].
58+
/// Start a chain from a limit we just found, abandoning whatever chain we
59+
/// were walking: that one never found a sort to apply to, and this limit is
60+
/// the one that governs everything below here.
6061
pub fn reset_limit(&mut self, limit: Option<usize>) {
6162
self.limit = limit;
6263
self.lookahead = 0;
64+
self.phase = Phase::FindOrGrow;
6365
}
6466

6567
pub fn max_lookahead(&mut self, new_val: usize) {
@@ -93,8 +95,14 @@ impl PhysicalOptimizerRule for LimitPushPastWindows {
9395
return reset(node, &mut ctx);
9496
}
9597

96-
// grab the latest limit we see
97-
if ctx.phase == Phase::FindOrGrow && get_limit(&node, &mut ctx) {
98+
// Grab the latest limit we see. The phase only has to hold us back at
99+
// a `SortPreservingMergeExec`, which is both a limit source and an
100+
// apply target; a plain limit node is never an apply target, so
101+
// reading it is always safe -- and it is more current than the chain
102+
// we were on, which has not found a sort to apply to.
103+
if (ctx.phase == Phase::FindOrGrow || !node.is::<SortPreservingMergeExec>())
104+
&& get_limit(&node, &mut ctx)
105+
{
98106
return Ok(Transformed::no(node));
99107
}
100108

‎datafusion/sqllogictest/test_files/window_limits.slt‎

Lines changed: 60 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1118,3 +1118,63 @@ physical_plan
11181118
06)----------BoundedWindowAggExec: wdw=[sum(employees.salary) ORDER BY [employees.empno ASC NULLS LAST] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: Field { "sum(employees.salary) ORDER BY [employees.empno ASC NULLS LAST] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW": nullable UInt64 }, frame: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW], mode=[Sorted]
11191119
07)------------SortExec: TopK(fetch=5), expr=[empno@0 ASC NULLS LAST], preserve_partitioning=[false]
11201120
08)--------------DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/testing/data/csv/aggregate_test_100_with_dates.csv]]}, projection=[empno, salary], file_type=csv, has_header=true
1121+
1122+
# A window the rule *can* grow past must not swallow the limit below it either.
1123+
# Growing leaves the walk in `Phase::Apply`, and the guard on `get_limit` used to
1124+
# skip every limit while in that phase -- so the inner `GlobalLimitExec` below was
1125+
# never read, the chain was dropped at it instead, and the inner window's sort was
1126+
# left a full sort although a `LIMIT 5` sat directly above it.
1127+
#
1128+
# This is the previous query with the outer frame changed from RANGE to ROWS, and
1129+
# the change of frame is what makes it fail: a RANGE frame cannot be grown past, so
1130+
# it resets the state on the way down and the inner limit is seen afterwards. A ROWS
1131+
# frame can, so nothing resets. Three other properties are load-bearing: both levels
1132+
# must order by the same column, or `EnforceSorting` leaves an outer SortExec in
1133+
# between and `apply_limit` resets there; the outer SELECT must reference `s1`, or
1134+
# projection pushdown removes the inner window; and both levels need their own LIMIT.
1135+
1136+
statement ok
1137+
set datafusion.optimizer.enable_window_limits = false;
1138+
1139+
query III
1140+
SELECT empno, s1, sum(empno) OVER (ORDER BY empno ROWS UNBOUNDED PRECEDING) AS s2
1141+
FROM (SELECT empno, salary, sum(salary) OVER (ORDER BY empno ROWS UNBOUNDED PRECEDING) AS s1 FROM employees ORDER BY empno LIMIT 5)
1142+
ORDER BY empno LIMIT 2;
1143+
----
1144+
102 3276123488 102
1145+
299 3304897863 401
1146+
1147+
statement ok
1148+
set datafusion.optimizer.enable_window_limits = true;
1149+
1150+
query III
1151+
SELECT empno, s1, sum(empno) OVER (ORDER BY empno ROWS UNBOUNDED PRECEDING) AS s2
1152+
FROM (SELECT empno, salary, sum(salary) OVER (ORDER BY empno ROWS UNBOUNDED PRECEDING) AS s1 FROM employees ORDER BY empno LIMIT 5)
1153+
ORDER BY empno LIMIT 2;
1154+
----
1155+
102 3276123488 102
1156+
299 3304897863 401
1157+
1158+
query TT
1159+
EXPLAIN
1160+
SELECT empno, s1, sum(empno) OVER (ORDER BY empno ROWS UNBOUNDED PRECEDING) AS s2
1161+
FROM (SELECT empno, salary, sum(salary) OVER (ORDER BY empno ROWS UNBOUNDED PRECEDING) AS s1 FROM employees ORDER BY empno LIMIT 5)
1162+
ORDER BY empno LIMIT 2;
1163+
----
1164+
logical_plan
1165+
01)Sort: employees.empno ASC NULLS LAST, fetch=2
1166+
02)--Projection: employees.empno, s1, sum(employees.empno) ORDER BY [employees.empno ASC NULLS LAST] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW AS s2
1167+
03)----WindowAggr: windowExpr=[[sum(CAST(employees.empno AS Int64)) ORDER BY [employees.empno ASC NULLS LAST] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW]]
1168+
04)------Sort: employees.empno ASC NULLS LAST, fetch=5
1169+
05)--------Projection: employees.empno, sum(employees.salary) ORDER BY [employees.empno ASC NULLS LAST] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW AS s1
1170+
06)----------WindowAggr: windowExpr=[[sum(employees.salary) ORDER BY [employees.empno ASC NULLS LAST] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW]]
1171+
07)------------TableScan: employees projection=[empno, salary]
1172+
physical_plan
1173+
01)ProjectionExec: expr=[empno@0 as empno, s1@1 as s1, sum(employees.empno) ORDER BY [employees.empno ASC NULLS LAST] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW@2 as s2]
1174+
02)--GlobalLimitExec: skip=0, fetch=2
1175+
03)----BoundedWindowAggExec: wdw=[sum(employees.empno) ORDER BY [employees.empno ASC NULLS LAST] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: Field { "sum(employees.empno) ORDER BY [employees.empno ASC NULLS LAST] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW": nullable Int64 }, frame: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW], mode=[Sorted]
1176+
04)------ProjectionExec: expr=[empno@0 as empno, sum(employees.salary) ORDER BY [employees.empno ASC NULLS LAST] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW@2 as s1]
1177+
05)--------GlobalLimitExec: skip=0, fetch=5
1178+
06)----------BoundedWindowAggExec: wdw=[sum(employees.salary) ORDER BY [employees.empno ASC NULLS LAST] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: Field { "sum(employees.salary) ORDER BY [employees.empno ASC NULLS LAST] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW": nullable UInt64 }, frame: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW], mode=[Sorted]
1179+
07)------------SortExec: TopK(fetch=5), expr=[empno@0 ASC NULLS LAST], preserve_partitioning=[false]
1180+
08)--------------DataSourceExec: file_groups={1 group: [[WORKSPACE_ROOT/testing/data/csv/aggregate_test_100_with_dates.csv]]}, projection=[empno, salary], file_type=csv, has_header=true

0 commit comments

Comments
 (0)