| From: | Andrey Rachitskiy <pl0h0yp1(at)gmail(dot)com> |
|---|---|
| To: | 125atishprak(at)gmail(dot)com, pgsql-bugs(at)lists(dot)postgresql(dot)org |
| Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
| Subject: | Re: BUG #19837: ORDER BY/DISTINCT ON use wrong column with grouping set duplicates |
| Date: | 2026-11-13 21:17:05 |
| Message-ID: | CAB8bMittE=RMtXk50pRA4bE6nTg379v=86YYmGN0OcN5p0y39A@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
сб, 7 нояб. 2026 г. в 20:54, PG Bug reporting form <noreply(at)postgresql(dot)org>:
> 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.
>
>
> Hi, Atish!
Thanks for the report.
While investigating this bug, I also found a ROLLUP bug.
SELECT i AS ai1, i AS ai2
FROM generate_series(1, 3) i
GROUP BY ai2, ROLLUP(ai1)
ORDER BY ai1, ai2;
With a sorted aggregate the ORDER BY Sort is not added. The rows come
out in grouping order, by ai2.
0001 fixes the ROLLUP query. The nulling bit from f5050f795ae was
attached to ai2, which is in every grouping set, so ai1 never got it.
0002 fixes GROUPING SETS ((x), (y)). A column that only some grouping
sets null gets its own EquivalenceClass and is matched by sortref.
Volatile ORDER BY keys are already matched that way.
| Attachment | Content-Type | Size |
|---|---|---|
| 0002-Keep-separate-equivalence-classes-for-grouping-set-o.patch | text/x-patch | 25.2 KB |
| 0001-Bind-repeated-grouping-expressions-to-their-own-sort.patch | text/x-patch | 8.7 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Masahiko Sawada | 2026-11-14 01:44:27 | Re: BUG #19884: pg_publication_tables omits a detach-pending partition in root mode |
| Previous Message | Manu | 2026-11-13 19:54:56 | Re: BUG #19602: Vuln46: citext split_part silently returns NULL for a zero field position instead of raising core sp |