adriangb opened a new issue, #25671:
URL: https://github.com/apache/datafusion/issues/25671
### Describe the bug
When `PushDownLimit` visits `Limit(skip=0, fetch=n)` over a `Sort` without a
fetch, it sets `Sort.fetch = n` but keeps the `Limit` node. The rule removes
the `Limit` only the next time it visits it, which is in the next optimizer
pass. That pass reports a change, so the optimizer then runs one more pass to
confirm the fixed point.
The result: every query of the shape `SELECT ... ORDER BY x LIMIT n` runs 3
logical optimizer passes (the `datafusion.optimizer.max_passes` default), where
2 are enough. The final plan is correct. Only planning time is affected.
A query engine that wraps every query in a row-cap `LIMIT` over an `ORDER
BY` hits this on nearly every query.
### To Reproduce
`datafusion-cli` built from `main` (95bb0a0dfa):
```sql
CREATE TABLE t (a INT, ts TIMESTAMP) AS VALUES (1, TIMESTAMP
'2026-01-01T00:00:00');
EXPLAIN VERBOSE SELECT a FROM t ORDER BY ts DESC LIMIT 10;
```
The rules that change the plan, per pass (the `logical_plan after <rule>`
rows that are not `SAME TEXT AS ABOVE`):
```text
pass 0: push_down_limit, optimize_projections
pass 1: push_down_limit <- removes the Limit it kept in pass 0
pass 2: (no change)
```
`logical_plan after push_down_limit`, pass 0:
```text
Projection: t.a
Limit: skip=0, fetch=10
Sort: t.ts DESC NULLS FIRST, fetch=10
Projection: t.a, t.ts
TableScan: t
```
`logical_plan after push_down_limit`, pass 1:
```text
Projection: t.a
Sort: t.ts DESC NULLS FIRST, fetch=10
TableScan: t projection=[a, ts]
```
### Expected behavior
With `skip = 0`, a `Sort` with `fetch = n` returns at most `n` rows, so the
`Limit` is redundant as soon as the rule sets the fetch. The rule already drops
it on the next visit (the `new_fetch == sort.fetch` branch with `skip == 0`).
It should drop it in the same visit, so the query settles in pass 0 and the
optimizer stops after pass 1.
### Additional context
The same plan shape with a filter and a subquery (`SELECT a, s FROM (SELECT
* FROM t WHERE s != 'pending') AS t WHERE s = 'x' ORDER BY ts DESC LIMIT 10`)
shows the same extra pass. There, `push_down_limit` is again the only rule that
changes the plan in pass 1.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
