Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xR5j1-00000000fn8-47r6 for pgsql-bugs@arkaria.postgresql.org; Wed, 11 Nov 2026 10:38:44 +0000 Received: from localhost ([127.0.0.1]:44524 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xR5iz-0000000AE14-1nTQ for pgsql-bugs@arkaria.postgresql.org; Wed, 11 Nov 2026 10:38:41 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]:42676) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xR4ao-00000009q7p-1WQf for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 09:26:10 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xR4al-00000000R3k-20Ex for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 09:26:10 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: Content-Transfer-Encoding:MIME-Version:Content-Type:Sender:Content-ID: Content-Description:In-Reply-To:References; bh=dAYocaEx1giH5y4ScveAj1o4VtGND+94X/Qx0UBOqB8=; b=4M9Z2Vo9hiZH0UtrvwzBMFP7PF BCnXZYGlfgaWfwE8NpdtG2VEfRUs8yJStSc1holZTqdmHNloklrQkMik0s+R67/GajnEoeR7nlF1/ drFALh+ODt+x56MSuAyW5nQkF99rzvnNoM1klFJyTC5rXKEx2vnT3FcFYSG2anOohK63ymerWFniM kBIZIHOD/w38tFiI8zu0Bxeelolgs1bD6OeZz+MHrmv0x4yyfscwX7wjwq1N90xGHmsnNd1KWd6hS SgMYoReoe/aFTykO+AYoX+CPoOXpp5HlM4mTNcxVaHk7PHcQNVv7g+jLIkPRtsaEwfPC2UuAV4AkQ 34GCB2bA==; Received: from wrigleys.postgresql.org ([2a02:16a8:dc51::60]) by mahout.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xR4ai-00000000xiP-30gR for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 09:26:05 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xR4ah-00000002YSt-2uy9 for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 09:26:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19873: planner silently drops the second ORDER BY sort key when both keys reference the same expression und To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: 303677365@qq.com Reply-To: 303677365@qq.com, pgsql-bugs@lists.postgresql.org Date: Wed, 11 Nov 2026 09:25:17 +0000 Message-ID: <19873-2b5e1e0dd7b9eac0@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk The following bug has been logged on the website: Bug reference: 19873 Logged by: chunling qin Email address: 303677365@qq.com PostgreSQL version: 18.6 Operating system: Linux x86_64 (official Docker image); platform-ind Description: =20 **Description:** `ORDER BY x USING <, x USING ~<~` =E2=80=94 two sort keys on the *same* exp= ression, 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' =3D '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 =E2=80=94 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 =3D icu, locale =3D 'und-u-ks-level2', deterministic =3D false); CREATE TABLE t (x text COLLATE ci); -- interleaved case variants: under ci 'a'=3D'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' =3D '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 =E2=80=94 ```sql SET enable_presorted_aggregate =3D 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 =3D 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=3Da, max=3Db); on returns (min=3DA, max=3DB); -- the correct two-key answer is (min=3DA, max=3Db) =E2=80=94 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`.