[branch-56] fix: SanityCheckPlan error when a per-partition constant leads an ordering (backport #27191) - #27368

…rdering (apache#27191)

- Closes apache#26762.

Queries that planned in earlier releases fail with `SanityCheckPlan`
when a window's input ordering starts with a per-partition constant or
is proven through an equality such as `a = CAST(c AS DATE)`. Across
7,440 generated query runs, `main` has 62 failures on queries that plan
on 55.2.0, and this PR has none, with the same rows wherever both plan.

There are three causes. In each one, window planning and
`SanityCheckPlan` reach different answers about whether the input is
sorted:

| Introduced by | Example | Why they disagree |
|---|---|---|
| apache#23528 (in 55.0.0) | The apache#26762 query | Normalized orderings keep
per-partition constants, but `add_constants` drops them, so window
planning sees `[total]` and `SanityCheckPlan` sees `[k, total]` |
| apache#25922 (on `branch-56`) | A window on `c` over a TopK on `(k, a, c)`,
with `k` a per-partition constant and `a = CAST(c AS DATE)` |
`add_constants` discovers that `[a, c]` implies `[c]`, but an ordering
added after the equality doesn't |
| apache#26747 (on `branch-56`) | A window on `(k, c)` over a TopK on `(k, a,
c)`, where `a` isn't selected | The sort is removed below a projection
because its input has the ordering, and the projection can't prove it |

This PR replaces apache#27140 and keeps its commit.

Start reading at `add_orderings`. It is the decision I most want
reviewed, because it makes the optimizer claim more orderings, and a
wrong claim means wrong results.

| Where | Change | Fixes |
|---|---|---|
| `SortKeyWalk::new` | An ordering's head skips leading fixed
expressions before the first key is pinned | apache#23528 |
| `add_orderings` | Discover the orderings a new ordering implies
through known equalities, as `add_constants` and `add_equal_conditions`
already do, for any key and past the constants an ordering keeps: `[k,
a, c]` with `a = CAST(c AS DATE)` implies `[k, c]` | apache#25922, apache#26747 |
| `handle_projection_pushdown` | If the input has the ordering but the
projection can't prove it, keep the sort above the projection | The
failures the first row would otherwise add, when a projection drops a
leading per-partition constant |
| `RepartitionExec` | Clear per-partition constants before adding the
merge ordering, as `SortPreservingMergeExec` does | Otherwise the second
row keeps `[c]` from `[k, a, c]` after a merge, where it holds only
within each partition |
| `SortKeyWalk::is_fixed` | Return early for a column that isn't pinned
or constant | The first row made single-key checks about 30% slower |

**Who is affected.** Plans with an ordering that starts with a
per-partition constant, or that is proven through an equality or a
column a projection drops. It can't stop halfway.

**No API change.** Discovery in `add_orderings` can fail only on an
expression that doesn't match the schema. That implied ordering is then
left out, which can cost a sort but not change results.

**Worst case.** 4 of the 7,440 generated runs plan a sort that `main`
doesn't: a `GROUP BY k, c` over a TopK on `(a, c)` with `a = CAST(c AS
DATE)`, at 4 partitions. Knowing `[c]`, the aggregate streams on `c` and
sorts on `c` after its hash repartition. `main` picks the same aggregate
when the ordering is known before the equality. 392 runs have fewer
sorts.

- `window.slt` covers each cause, including a merge of partitions above
the sort, and checks the plans. Removing any one of the first three
changes, limiting discovery to the first key, or not skipping constants
makes tests fail, in `window.slt` or in the unit tests for the walk and
for implied orderings. A `RepartitionExec` unit test covers the merge.
- All SLT files pass with no existing plan changing, as do the
`physical_optimizer` integration tests and the equivalence fuzz tests.
- A sweep of functions that look order-preserving but aren't found no
wrong ordering. Details below.

<details>
<summary>Generated query runs, benchmarks, rejected alternatives, and
queries that still fail</summary>

**Generated runs.** Each sweep runs at `target_partitions` 1 and 4. A
regression plans on the reference build and fails on the other build.

| Sweep | Runs | `main`, vs 55.2.0 | `main`, vs before apache#23528 | This PR,
vs both |
|---|---|---|---|---|
| Per-partition constants: 6 inputs, 3 orderings, 5 projections, up to
12 consumers | 2,016 | 0 | 4 | 0 |
| Orderings proven through `a = CAST(c AS DATE)` | 3,120 | 56 | 56 | 0 |
| An operator between the ordering and the consumer: joins of each type
on either side, filter, aggregate, union | 1,584 | 6 | 36 | 0 |
| `head = f(arg)` for `CAST(x AS VARCHAR)`, `ABS`, negation, modulo,
`upper` and order-preserving functions, in all 4 sort directions | 720 |
0 | 0 | 0, and no wrong ordering |

The results are the same with this patch on `branch-56`, where all SLT
files pass as well. Compared with `main` on the runs that plan on both,
the rows are the same, 4 runs have more sorts (see the worst case above)
and 392 have fewer.

**Benchmarks.** `equivalence_properties`, best of 10 alternating runs on
a busy laptop, so approximate: single-key checks are 6-12% slower (about
20-40 ns), and window-heavy orderings are 24-33% faster.

| Benchmark | `main` | This PR |
|---|---|---|
| `ordering_satisfy/1_key/8` | 359 ns | 380 ns |
| `ordering_satisfy_requirement/1_key/8` | 307 ns | 329 ns |
| `find_longest_permutation/1_expr/32` | 306 ns | 344 ns |
| `window/4` | 1,543 ns | 1,181 ns |
| `window/32` | 7,319 ns | 4,889 ns |

**Rejected.**

- Having `add_constants` keep per-partition constants fails
`test_eliminate_redundant_monotonic_sorts`.
- Labelling `lower(g)` as a uniform constant isn't enough, because a
constant from an aggregate without `GROUP BY` really is per-partition.
- Having the projection leave a per-partition constant it drops out of
its orderings, instead of the check in `handle_projection_pushdown`,
makes 2 generated queries fail `SanityCheckPlan`: a window over a `GROUP
BY` whose input ordering leads with such a constant.
- Applying the projection check to every node in `pushdown_sorts_helper`
only fixes one more query, and that query already fails on every
release, so I kept the narrower check.

**Still failing, not regressions.** A window over a `UNION ALL` of two
TopKs that have `v AS w` fails on every build I tried, including before

```sql
SELECT *, ROW_NUMBER() OVER (ORDER BY w) AS r FROM (
  SELECT k, v, w FROM (SELECT k, v, v AS w FROM (SELECT lower(g) AS k, v FROM t5 WHERE g = 'a') ORDER BY k, v LIMIT 30)
  UNION ALL
  SELECT k, v, w FROM (SELECT k, v, v AS w FROM (SELECT lower(g) AS k, v FROM t5 WHERE g = 'a') ORDER BY k, v LIMIT 30)
) ORDER BY r;
```

</details>

Affected queries plan again. This is a candidate for `branch-56`, where
it applies without conflicts.

---------

Co-authored-by: Andrew Lamb <[email protected]>
Co-authored-by: Claude Fable 5.1 <[email protected]>
(cherry picked from commit ac1a63d)