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 1xPirF-00000000rTZ-36fg for pgsql-bugs@arkaria.postgresql.org; Sat, 07 Nov 2026 16:01:35 +0000 Received: from localhost ([127.0.0.1]:57122 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xPirF-000000049lg-3y4n for pgsql-bugs@arkaria.postgresql.org; Sat, 07 Nov 2026 16:01:33 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]:39388) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xPPXi-00000001GSb-2wZg for pgsql-bugs@lists.postgresql.org; Fri, 06 Nov 2026 19:24:06 +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 1xPPXf-00000000bPW-2pmG for pgsql-bugs@lists.postgresql.org; Fri, 06 Nov 2026 19:24:05 +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=QL5tGAvFUiv+hJz1BAH4nhN/6BjnPgdD/Y7Ku17X4So=; b=xC7SCkONxH8UlVvk875zPqPU1F YKUH5IX5rlOqpfd8GHzxhMpBjARhIU96NKBkE4D8oOLYsBWCo3q4jIPle1MqhzMKXtQojQgfVci2S fsDKOCjDU7spzRHwxoZ3+vyBqkhbtk+MPvy0+nH3MrMb9V4MxgDYnxqD8v0+AOygwC4rAwyXu85ht TQwqys+cXmOQqmxlns4CzotQyRO5MmQBG50YS0wXTEmPBn9JG/F8WUgOqA47avfcnGkbtEHsKaEph N2/tnC/MN6QTJsVJwZnBi/XPqtksXgmDwEVqZNPyWnYYYjqHPdH+RrGhaedXezjz5/dmMEFtQWrWI A9l6AeIw==; 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 1xPPXf-00000001dVD-2iiP for pgsql-bugs@lists.postgresql.org; Fri, 06 Nov 2026 19:24:04 +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 1xPPXe-000000048Ie-3E2H for pgsql-bugs@lists.postgresql.org; Fri, 06 Nov 2026 19:24:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19837: ORDER BY/DISTINCT ON use wrong column with grouping set duplicates To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: 125atishprak@gmail.com Reply-To: 125atishprak@gmail.com, pgsql-bugs@lists.postgresql.org Date: Fri, 06 Nov 2026 19:23:11 +0000 Message-ID: <19837-92a0d7bbd05b30d8@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: 19837 Logged by: Atish Prakash Email address: 125atishprak@gmail.com PostgreSQL version: 19rc1 Operating system: Linux x86_64 (kernel 7.0.0, glibc 2.43) Description: =20 When the same expression is selected twice and the two outputs are separate grouping-set members, they can hold different values, because each grouping set nulls only one of them. ORDER BY and DISTINCT ON on the second output still act on the first. The result is wrongly ordered, LIMIT returns the wrong row, and DISTINCT ON loses a row. No error is raised. Minimal example: CREATE TABLE t (a int); INSERT INTO t VALUES (1), (2); SELECT a AS x, a AS y FROM t GROUP BY GROUPING SETS ((1), (2)) ORDER BY 2; Actual output, with y not sorted: x | y ------+------ 1 | NULL 2 | NULL NULL | 2 NULL | 1 Expected output, which is what the same query returns behind an OFFSET 0 barrier: x | y ------+------ NULL | 1 NULL | 2 2 | NULL 1 | NULL The EXPLAIN output shows one sort key, t.a. I have not traced the cause. My guess is that both outputs are the same expression with the same nullingrels, so they share an EquivalenceClass and the sort uses the first matching targetlist entry. Related: - The query in this 2022 thread still returns the wrong order (case 4 below): https://www.postgresql.org/message-id/3642474.1666568956@sss.pgh.pa.us This may be the known issue described there, but I could not find a report or fix for the current behaviour. - BUG #19800 and #19805 look similar but need self-join elimination. This query has no join. - I reported a related deparse problem separately: "view with GROUP BY ROLLUP over duplicate columns changes results after dump/restore". I have no patch for this. I found it with a SQL fuzzer. Versions tested, all with identical output: - 18.6 (REL_18_STABLE at 813a74bc5a) - 19rc1 (REL_19_STABLE at 0b1eb8b917) - master at 72b9498df9, plus my unrelated uncommitted patch to ruleutils.c Platform: Linux 7.0.0-42-generic x86_64, glibc 2.43, Intel i9-13900HX, 14 GiB RAM Client and server: psql and postgres from the same build, Unix socket, psql -X Build: clang 21.1.8, CFLAGS=3D-O1 -g, --enable-cassert --enable-debug --without-icu --without-readline --without-libxml --without-openssl Non-default server settings: fsync=3Doff, shared_buffers=3D48MB, lc_message= s=3DC Install: built from git with no other deviations from the instructions Reproducer, run with psql -X -f in a new database: \pset null NULL CREATE TABLE t (a int); INSERT INTO t VALUES (1), (2); \echo 1. ORDER BY y: y is not sorted SELECT a AS x, a AS y FROM t GROUP BY GROUPING SETS ((1), (2)) ORDER BY 2; EXPLAIN (COSTS OFF, VERBOSE) SELECT a AS x, a AS y FROM t GROUP BY GROUPING SETS ((1), (2)) ORDER BY 2; \echo Same rows behind a subquery barrier, correctly sorted SELECT * FROM (SELECT a AS x, a AS y FROM t GROUP BY GROUPING SETS ((1), (2)) OFFSET 0) s ORDER BY y; \echo 2. ORDER BY y LIMIT 1: wrong row SELECT a AS x, a AS y FROM t GROUP BY GROUPING SETS ((1), (2)) ORDER BY 2 LIMIT 1; SELECT * FROM (SELECT a AS x, a AS y FROM t GROUP BY GROUPING SETS ((1), (2)) OFFSET 0) s ORDER BY y LIMIT 1; \echo 3. DISTINCT ON (y): y =3D NULL twice, y =3D 1 lost SELECT DISTINCT ON (2) a AS x, a AS y FROM t GROUP BY GROUPING SETS ((1), (2)) ORDER BY 2; SELECT DISTINCT ON (y) x, y FROM (SELECT a AS x, a AS y FROM t GROUP BY GROUPING SETS ((1), (2)) OFFSET 0) s ORDER BY y; \echo 4. Query from the 2022 thread: ai1 is not sorted, NULLs are interleaved SELECT i AS ai1, i AS ai2 FROM generate_series(1,3) i GROUP BY ai2, ROLLUP(ai1) ORDER BY ai1, ai2; Full output: Null display is "NULL". CREATE TABLE INSERT 0 2 1. ORDER BY y: y is not sorted x | y ------+------ 1 | NULL 2 | NULL NULL | 2 NULL | 1 (4 rows) QUERY PLAN ---------------------------------- Sort Output: a, a Sort Key: t.a -> HashAggregate Output: a, a Hash Key: t.a Hash Key: t.a -> Seq Scan on public.t Output: a, a (9 rows) Same rows behind a subquery barrier, correctly sorted x | y ------+------ NULL | 1 NULL | 2 2 | NULL 1 | NULL (4 rows) 2. ORDER BY y LIMIT 1: wrong row x | y ---+------ 1 | NULL (1 row) x | y ------+--- NULL | 1 (1 row) 3. DISTINCT ON (y): y =3D NULL twice, y =3D 1 lost x | y ------+------ 1 | NULL 2 | NULL NULL | 2 (3 rows) x | y ------+------ NULL | 1 NULL | 2 2 | NULL (3 rows) 4. Query from the 2022 thread: ai1 is not sorted, NULLs are interleaved ai1 | ai2 ------+----- 1 | 1 NULL | 1 2 | 2 NULL | 2 3 | 3 NULL | 3 (6 rows)