pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: 303677365@qq.com
Subject: BUG #19869: Wrong results: planner silently drops the second ORDER BY sort key when both keys reference the same
Date: Wed, 11 Nov 2026 08:34:31 +0000
Message-ID: <19869-6cb4aeabc803c3a6@postgresql.org> (raw)

The following bug has been logged on the website:

Bug reference:      19869
Logged by:          chunling qin
Email address:      303677365@qq.com
PostgreSQL version: 18.6
Operating system:   Linux x86_64; platform-independent (reproduced on
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 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 (master): second key with a DIFFERENT collation -> different EC
-> both keys honored
-- (on master this control sorts bytewise; on 18.6 even this control is
collapsed
--  to the first key only - an even wider face of the same
pathkey-redundancy rule):
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 x FROM t ORDER BY x USING <, x USING ~<~;
-- returns rows in the first-key order only ({a,A,a,A,b,B,b,B} in practice),
-- not the bytewise tie-break; EXPLAIN shows a single Sort Key.
-- Same face via a subquery aggregate:
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 (compare with control_both_keys above)

-- On master, the aggregate/presorted face also diverges across a GUC:
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;
-- off honors both keys; on drops the second (observed min/max diverge)

-- 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 ~<~) FROM t2;   -- same
either way
```

**Actual results:** under the nondeterministic collation the second ORDER BY
key is ignored; 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).

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








Message-ID: <19869-6cb4aeabc803c3a6@postgresql.org>
Permalink:  ../19869-6cb4aeabc803c3a6@postgresql.org/
Also on:    postgresql.org/message-id/19869-6cb4aeabc803c3a6@postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, 303677365@qq.com
  Subject: Re: BUG #19869: Wrong results: planner silently drops the second ORDER BY sort key when both keys reference the same
  In-Reply-To: <19869-6cb4aeabc803c3a6@postgresql.org>
  References: <19869-6cb4aeabc803c3a6@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox