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 1xMgm4-000000029QW-2LYl for pgsql-bugs@arkaria.postgresql.org; Fri, 30 Oct 2026 07:11:41 +0000 Received: from localhost ([127.0.0.1]:49776 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xMgm4-00000008Q5p-0TMU for pgsql-bugs@arkaria.postgresql.org; Fri, 30 Oct 2026 07:11:40 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]:40964) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xMHzo-00000002WiB-3RFN for pgsql-bugs@lists.postgresql.org; Thu, 29 Oct 2026 04:44:12 +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 1xMHzk-00000001WxP-2WuP for pgsql-bugs@lists.postgresql.org; Thu, 29 Oct 2026 04:44:12 +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=3u8ajq1wdsGgwVAj42uoWlkC96eWtyE5EZhX13OCwPo=; b=iXcZ9Ms5ZYhwzI6tOXz5pSw6RP bzd+CGIJutxw4Qs65WkKrea6PUHeWazLRc8+r2fYqkreI1aYXo8HaXxGRW0d9lElRJ363MKGmhc2/ e2lw9CkvpLKQ6Hhed2PQb541LGgGsjZPWQLQtWkp9F4rA1cn6M8eKP1TfUpDrCMAZ/Idnjgas6uu+ TJpuqocFmwQI9muAkTxuSY8ZGyoE4EYULdEm71spoQ69Bpa4wbVwUUN9Q0TBjVlfuIpXCJuSxfAXh o5RWXvV3FQeEW1Yj4WHoFpgleiX0o0p9mBqJGsc9OskG+8P+1x0WeXB6djmbnwu/nh8hCGj40ICrv 5JRqvgcQ==; 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 1xMHzh-00000004FNp-1cHh for pgsql-bugs@lists.postgresql.org; Thu, 29 Oct 2026 04:44:07 +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 1xMHzf-00000005UW6-0BuU for pgsql-bugs@lists.postgresql.org; Thu, 29 Oct 2026 04:44:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19805: 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: Thu, 29 Oct 2026 04:43:44 +0000 Message-ID: <19805-66e421e032e8a3e2@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: 19805 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. Note this is not a "tie is unspecified" case: in the example below the tied rows are distinguishable on the remaining sort key ((NULL,3) vs (NULL,8)), so the required order is fully determined. Reproduction (self-contained, works on the official postgres:latest Docker image): docker run --rm -d --name pgv -e POSTGRES_PASSWORD=3Dx -e POSTGRES_USER=3Dt= base -p 15440:5432 postgres:latest docker exec -i pgv psql -U tbase -d postgres <<'SQL' 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; SQL Actual output (N =3D NULL): 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 - it is 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 tested (official 18.6 Docker image, REL_18_STABLE tip 606f3db, and master 20devel). 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. Not verified: branches 17 and below (SJE does not exist there), and whether MERGE / INSERT ... SELECT persist wrong rows in practice (the ordering difference is confirmed; the data-persistence variant I did not execute).