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-3CEl for pgsql-bugs@arkaria.postgresql.org; Wed, 11 Nov 2026 10:38:43 +0000 Received: from localhost ([127.0.0.1]:44518 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xR5iz-0000000AE0t-1cLY for pgsql-bugs@arkaria.postgresql.org; Wed, 11 Nov 2026 10:38:41 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]:56022) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xR3nO-00000009bNv-3sIy for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 08:35:07 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xR3nO-00000000LDl-0IxB for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 08:35:06 +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=1VjLf+Ro+oDh4rSA6KvWAKZdCZxYG+P1VNOMk3RygNE=; b=m2ajij+R/RuJkSJjOqTfXZHaMo X0mlu+a+2CYdUPpnrZB07JE4VdpjfTaPvc4IaiFvsrmism8Wg7qbvUaLrYTVL0XYu+cAmZq8XWe0B tCXPNN91Kt0lx+9kSLIOP+Ea8YDP5E8ShUSxEFqi1Jj207hrqY+t9xQmQeW9QKcAgGcm0XOFrxGNF ZC1x95F8GagJxMDJvj7QUOAX9u6Po6grvZbdgr9Cq4zx0M5k7OcYFcT30JXslPrDAw6ic+Tgt4ZQn GJ+b+jMcQTvHbewEDhTrsPeiVJ8vYyNdyNzcInSGhwewZtivEZBRRo1qCYhHZOlB585H3n6sv3Jlb vF9dwtgg==; 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 1xR3nM-00000000wf9-0Lgr for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 08:35: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 1xR3nL-00000002UL2-051E for pgsql-bugs@lists.postgresql.org; Wed, 11 Nov 2026 08:35:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19869: Wrong results: planner silently drops the second ORDER BY sort key when both keys reference the same 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 08:34:31 +0000 Message-ID: <19869-6cb4aeabc803c3a6@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: 19869 Logged by: chunling qin Email address: 303677365@qq.com PostgreSQL version: 18.6 Operating system: Linux x86_64; platform-independent (reproduced on 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 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 (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 =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; -- 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`.