Incorrect results: ORDER BY drops a sort key based on a nullable UNIQUE constraint

[alamb]

Is your feature request related to a problem or challenge?

Follow-up to #23634: the same nullable-UNIQUE confusion also affects ORDER BY.

A trailing sort key is dropped when an earlier key functionally determines it. That is valid for a PRIMARY KEY, but not for a UNIQUE column, which permits multiple NULL rows and therefore does not determine anything across them.

CREATE TABLE t (x INT UNIQUE, y INT) AS VALUES (NULL, 2), (NULL, 1), (1, 3);
SELECT x, y FROM t ORDER BY x NULLS LAST, y;
-- returns:
-- 1    3
-- NULL 2
-- NULL 1
--
-- should be:
-- 1    3
-- NULL 1
-- NULL 2

The plan shows the y sort key is gone entirely:

EXPLAIN SELECT x, y FROM t ORDER BY x NULLS LAST, y;
-- logical_plan
-- 01)Sort: t.x ASC NULLS LAST            <-- `y` dropped
-- 02)--TableScan: t projection=[x, y]
-- physical_plan
-- 01)SortExec: expr=[x@0 ASC NULLS LAST], preserve_partitioning=[false]
-- 02)--DataSourceExec: partitions=1, partition_sizes=[1]

Verified against duckdb:

❯ duckdb -c "create table t (x int unique, y int); insert into t values (null,2),(null,1),(1,3); select x, y from t order by x nulls last, y;"
┌───────┬───────┐
│   x   │   y   │
│ int32 │ int32 │
├───────┼───────┤
│     1 │     3 │
│  NULL │     1 │
│  NULL │     2 │
└───────┴───────┘

Note the two NULL rows are stored with y descending on purpose — with them in ascending order the answer comes out right by accident, even though the plan is equally wrong.

Describe the solution you'd like

Fix the bug.

Describe alternatives you've considered

Additional context

Related:

• Incorrect results with unique constraints #23634
• fix: do not remove DISTINCT when a unique key was downgraded by a join #23548
• fix: Do not mark GROUP BY derived unique keys as nullable #23636

[buraksenn]

take