Wrong results: ORDER BY ... NULLS LAST on a running MAX window function puts the NULL row first #27267

Describe the bug

A query that computes MAX(x) OVER (ORDER BY ts) and then sorts on that column with ASC NULLS LAST returns rows in the wrong order when x contains NULLs. The NULL row is returned first, where the query asked for it last.

The same wrong order appears for:

• MIN(x) OVER (ORDER BY ts) sorted DESC NULLS LAST
• MAX(x) OVER (ORDER BY ts ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) sorted DESC NULLS FIRST

To Reproduce

set datafusion.execution.target_partitions = 1;

CREATE TABLE t(ts INT, x INT) AS VALUES (1, NULL), (2, 3), (3, 1), (4, 5);

SELECT ts, m
FROM (
  SELECT ts, MAX(x) OVER (ORDER BY ts) AS m
  FROM (SELECT * FROM t ORDER BY ts)
)
ORDER BY m ASC NULLS LAST;

Returns:

+----+------+
| ts | m    |
+----+------+
| 1  | NULL |     <-- NULL is first, not last!
| 2  | 3    |
| 3  | 3    |
| 4  | 5    |
+----+------+

Expected behavior

NULLS LAST means the NULL row comes after every non-NULL value:

+----+------+
| ts | m    |
+----+------+
| 2  | 3    |
| 3  | 3    |
| 4  | 5    |
| 1  | NULL |
+----+------+

Additional context

EXPLAIN shows the final sort on m is removed, so the planner believes the window output already satisfies m ASC NULLS LAST. Reproduced on main at 94486b1.

Found by @jayzhan211 while reviewing the EquivalenceProperties docs in #27250. The original report and repro are in this review comment: #27250 (comment)