BUG #19873: planner silently drops the second ORDER BY sort key when both keys reference the same expression und

The following bug has been logged on the website:

Bug reference: 19873
Logged by: chunling qin
Email address: 303677365(at)qq(dot)com
PostgreSQL version: 18.6
Operating system: Linux x86_64 (official Docker image); platform-ind
Description:

**Description:**

`ORDER BY x USING <, x USING ~<~` — two sort keys on the *same* expression,
using different btree operator families (the default `text_ops` `<` and the
bytewise `text_pattern_ops` `~<~`). Under a *nondeterministic* collation the
first key has ties (`'a' = 'A'`), and the second key is exactly what the
user asked for to break those ties bytewise. The planner collapses both keys
into one EquivalenceClass and discards the second as redundant, so the
result comes out in the wrong order — and `DISTINCT ON`, `min/max(... ORDER
BY ...)` and aggregates with `ORDER BY` pick different
representatives/arrays depending on which plan is chosen.

**Reproduction (self-contained SQL; verified verbatim on 18.6 and master):**

```sql
DROP TABLE IF EXISTS t, t2 CASCADE;
DROP COLLATION IF EXISTS ci CASCADE;
CREATE COLLATION ci (provider = icu, locale = 'und-u-ks-level2',
deterministic = false);
CREATE TABLE t (x text COLLATE ci);
-- interleaved case variants: under ci 'a'='A' (tie under <), but 'A' < 'a'
bytewise
INSERT INTO t VALUES ('a'),('A'),('a'),('A'),('b'),('B'),('b'),('B');

-- operator semantics: ci is case-insensitive; ~<~ is bytewise
SELECT 'a' = 'A' COLLATE ci AS ci_eq, -- t
'a' < 'A' COLLATE ci AS ci_lt, -- f (tie)
'A' ~<~ 'a' COLLATE ci AS bytewise; -- t (the tie-break)

-- CONTROL: the bytewise tie-break the user asked for, as a single-key
query:
SELECT array_agg(x ORDER BY x USING ~<~) AS bytewise_only FROM t;
-- {A,A,B,B,a,a,b,b} <- the correct tie-broken order

-- CONTROL 2: second key with a DIFFERENT collation -> different EC -> both
keys honored
-- (on master this returns the tie-broken order {A,A,a,a,B,B,b,b};
-- on the 18.6 image tested this control is collapsed to the first key too,
-- i.e. the drop-key face is even wider there):
SELECT array_agg(x ORDER BY x USING <, x COLLATE "C" USING <) AS
control_both_keys FROM t;

-- SUSPECT: second key with a different opfamily on the SAME expression ->
dropped:
SELECT array_agg(x)::text COLLATE "C" AS plain_2keys
FROM (SELECT x FROM t ORDER BY x USING <, x USING ~<~) s;
-- {a,A,a,A,b,B,b,B} <- wrong: only the first key was honored

EXPLAIN (COSTS OFF) SELECT x FROM t ORDER BY x USING <, x USING ~<~;
-- Sort
-- Sort Key: x COLLATE ci <- the second key is simply gone

-- CONTROL for the whole report: deterministic collation -> the second key
is
-- genuinely redundant there, and both plans agree (no divergence):
CREATE TABLE t2 (x text COLLATE "C");
INSERT INTO t2 VALUES ('a'),('A'),('a'),('A'),('b'),('B'),('b'),('B');
SELECT array_agg(x ORDER BY x USING <, x USING ~<~) AS det_control FROM t2;
```

**Additional face observed on a current master build (20devel,
2026-11-09):** ordered aggregates with the same two keys diverge across a
pure planner GUC —

```sql
SET enable_presorted_aggregate = off;
SELECT min(x ORDER BY x USING <, x USING ~<~) AS min_off,
max(x ORDER BY x USING <, x USING ~<~) AS max_off FROM t;
SET enable_presorted_aggregate = on;
SELECT min(x ORDER BY x USING <, x USING ~<~) AS min_on,
max(x ORDER BY x USING <, x USING ~<~) AS max_on FROM t;
-- observed: off returns (min=a, max=b); on returns (min=A, max=B);
-- the correct two-key answer is (min=A, max=b) — the bytewise tie-break
-- picks 'A' first in the a-group and 'b' last in the b-group.
-- So the two plans disagree with each other AND each is wrong.
```

(Do not run this GUC part on a 18.6 build older than 2026-11-04: its `off`
path reaches the executor Datum-sort bug fixed by 91e9e89dc on that date,
which can crash the backend; 18.6 packages updated after 2026-11-04 are
fine.)

**Actual results:** under the nondeterministic collation the second ORDER BY
key is ignored; `EXPLAIN` shows a single Sort Key. The output order and the
representatives chosen by `DISTINCT ON` / ordered aggregates depend on which
plan happens to be picked (an optimization choice), which is the definition
of a wrong result.

**Expected behavior:** two sort keys that use different operator families
(different btree opclasses) must not be treated as redundant: under a
nondeterministic collation the first key can tie, and only the second key
decides the order. The equivalence-class machinery must not merge keys whose
operator families differ (or must at least keep the second key when the
first key's equality is not the identity).

**Related, but distinct:** the recent executor-side fixes "Fix accidental
ignoring of subsequent keys in Sort nodes" (91e9e89dc and master
edbe7ffdc8a, from the thread "Wrong result: count(DISTINCT x COLLATE "C")
with GROUP BY x on a nondeterministic collation") address the *executor*
Datum-sort path when a plan already carries both keys. This report is the
*planner* side: the second key never reaches the plan at all, so those fixes
cannot help.

**Impact:** wrong results for plain `ORDER BY`, `DISTINCT ON`, ordered set
aggregates (`min/max/array_agg/string_agg ... ORDER BY`); on master, results
also flip depending on `enable_presorted_aggregate`.