BUG #19837: ORDER BY/DISTINCT ON use wrong column with grouping set duplicates

The following bug has been logged on the website:

Bug reference: 19837
Logged by: Atish Prakash
Email address: 125atishprak(at)gmail(dot)com
PostgreSQL version: 19rc1
Operating system: Linux x86_64 (kernel 7.0.0, glibc 2.43)
Description:

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=-O1 -g, --enable-cassert --enable-debug
--without-icu --without-readline --without-libxml --without-openssl
Non-default server settings: fsync=off, shared_buffers=48MB, lc_messages=C
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 = NULL twice, y = 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 = NULL twice, y = 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)