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 1xLd9V-00000001c3a-2lKy for pgsql-bugs@arkaria.postgresql.org; Tue, 27 Oct 2026 09:07:30 +0000 Received: from localhost ([127.0.0.1]:35240 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xLd9V-00000009z72-0aX9 for pgsql-bugs@arkaria.postgresql.org; Tue, 27 Oct 2026 09:07:29 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]:50100) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xLbk3-00000009lLK-2gWs for pgsql-bugs@lists.postgresql.org; Tue, 27 Oct 2026 07:37: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 1xLbk2-000000019WC-0LDg for pgsql-bugs@lists.postgresql.org; Tue, 27 Oct 2026 07:37: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=cRh5DXmGMm0qqcccSGgCUIlwu9Eij/BMO/iCeA3EA1k=; b=u5jWvLSHgVSpEac0kXx46MCAJ3 fEAAhAQ5gnZRwZx+H52TpQ8W/W/ORFDd/fjpxwdcBM9JgwDHamy2WWZtIE67ZsWUsOU1QoN2eE4g6 hOm5R3y0ohcPtTwPcW1DGvwQnJudyOuZf2hGrw/HoIxlikS6YC08q8VbFBvz6sggJ2RWUphj8KH7S oxcA+659EYOoowHGcYwd+JJS65CFm688vF+ndTf8oHJeYJOhKmFngRtpNqWu5o0J+P2LUbIhf5IqI w4qvie8HNHE9T7fopl4DW4RX2EUuMUCi9Q4blNrMmwbHnJHSVmKU2aL6Pvx8xF2r0YHEDkED7bEXU 6CSzrWQw==; 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 1xLbk1-00000003M0n-0srF for pgsql-bugs@lists.postgresql.org; Tue, 27 Oct 2026 07:37: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 1xLbjy-00000002OUi-42CO for pgsql-bugs@lists.postgresql.org; Tue, 27 Oct 2026 07:37:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19800: Wrong results: self-join elimination drops an ORDER BY sort key with GROUPING SETS / CUBE / ROLLUP 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: Tue, 27 Oct 2026 07:36:27 +0000 Message-ID: <19800-2f4fc7fcfb9a17a1@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: 19800 Logged by: chunling qin Email address: 303677365@qq.com PostgreSQL version: 18.6 Operating system: Linux x86_64 Description: =20 Self-join elimination (SJE, new in v18) rewrites the Vars of the removed relation to the kept one. When both sides of a grouping-sets query are joined on a unique key, the two output columns end up as the same Var, so their two ORDER BY keys land in the same equivalence class and the second key is discarded as redundant (pathkey_is_redundant()). That reasoning assumes EC members are always equal. After grouping sets it is false: grouping sets emit NULLs per set, so two columns that share an expression do not share values. Losing the second key leaves rows that tie on the first key in an arbitrary order, which violates ORDER BY. This is not a "tie is unspecified" case: in the example the tied rows are distinguishable on the remaining sort key ((NULL,3) vs (NULL,8)), so the required order is fully determined. Reproduction (N =3D NULL): CREATE TABLE m2 (a int UNIQUE); INSERT INTO m2 VALUES (1), (2); ANALYZE m2; SET enable_self_join_elimination =3D on; -- default SELECT s1.a, s2.a FROM m2 s1 JOIN m2 s2 ON s1.a =3D s2.a GROUP BY CUBE (s1.a, s2.a) ORDER BY 1, 2; SET enable_self_join_elimination =3D off; SELECT s1.a, s2.a FROM m2 s1 JOIN m2 s2 ON s1.a =3D s2.a GROUP BY CUBE (s1.a, s2.a) ORDER BY 1, 2; Actual output: SJE on : (1,N) (1,1) (2,2) (2,N) (N,N) (N,1) (N,2) SJE off: (1,1) (1,N) (2,2) (2,N) (N,1) (N,2) (N,N) Expected: the SJE-off ordering. With ORDER BY 1, 2 (ASC, NULLS LAST) the pair (1,1) must precede (1,N), and (N,N) must come after (N,1) and (N,2). The SJE-on output inverts both. Plans (EXPLAIN (COSTS OFF, VERBOSE)): SJE on : Sort Key: s2.a Output: s2.a, s2.a <- one key; both columns are the same Var SJE off: Sort Key: s1.a, s2.a Output: s1.a, s2.a With 10 rows the effect is unambiguous: the group whose first column is NULL comes out as 3 8 10 9 7 1 5 4 2 6 with SJE on, and 1 2 3 4 5 6 7 8 9 10 with SJE off. The sequence is deterministic - identical across sessions and across all three builds tested. Impact is not limited to cosmetic ordering: with LIMIT 1 OFFSET 4 on the 2-row example, SJE on returns (NULL,NULL) and SJE off returns (NULL,1) - different rows, so paging and any INSERT ... SELECT ... ORDER BY ... LIMIT can materialise wrong data. Source: remove_self_join_rel() in analyzejoins.c rewrites root->parse via ChangeVarNodes() without awareness of grouping-set semantics; the key is then dropped by make_pathkeys_for_sortclauses() -> pathkey_is_redundant() in pathkeys.c. Related: the same underlying hazard was reported in 2021 ("Sort keys are omitted incorrectly after grouping sets", Richard Guo, pgsql-hackers), where the hand-written form SELECT a, b FROM foo WHERE a =3D b GROUP BY CUBE (a,b) ORDER BY a, b lost a sort key. That form is fixed today - I verified it produces Sort Key: a, b and correct output on all three builds. The grouping-sets special case in pathkeys.c does not appear to cover the shape SJE produces, so v18 can silently transform a safe query into the unsafe one. Tested on: - PostgreSQL 18.6, official Docker image postgres:latest (Debian 18.6-1.pgdg13+2) - PostgreSQL 18.6, build of REL_18_STABLE tip (commit 606f3db) - PostgreSQL 20devel, build of master All three reproduce. enable_hashagg=3Doff does not change the result.