`PushDownLimit` keeps a redundant `Limit` above a `Sort` it gave a fetch, so every `ORDER BY ... LIMIT n` query runs one extra optimizer pass

[adriangb]

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 (95bb0a0):

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):

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:

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:

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.