From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: 125atishprak@gmail.com
Subject: BUG #19835: view with GROUP BY ROLLUP over duplicate columns changes results after dump/restore
Date: Fri, 06 Nov 2026 17:17:36 +0000
Message-ID: <19835-65b2938eff695dc6@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 19835
Logged by: Atish Prakash
Email address: 125atishprak@gmail.com
PostgreSQL version: 19rc1
Operating system: Linux
Description:
pg_get_viewdef() prints a grouping-set member as its expression. If an
earlier output column has
an equal expression, the parser binds the printed expression to that earlier
column. The two
columns are not interchangeable under grouping sets, so a re-created view
can silently return
different rows. pg_dump uses this output, so a dump and restore changes the
view with no error.
Reproducer
CREATE TABLE t (a int);
INSERT INTO t VALUES (1), (2);
CREATE VIEW v AS SELECT a AS x, a AS y, count(*) AS n FROM t GROUP BY
ROLLUP(1, 2);
SELECT * FROM v ORDER BY 1, 2;
x | y | n
---+---+---
1 | 1 | 1
1 | | 1
2 | 2 | 1
2 | | 1
| | 2
SELECT pg_get_viewdef('v'::regclass);
-- ... GROUP BY ROLLUP(a, a);
After pg_dump and restore into a new database:
SELECT * FROM v ORDER BY 1, 2;
x | y | n
1 | 1 | 1
1 | 1 | 1
2 | 2 | 1
2 | 2 | 1
| | 2
CUBE and GROUPING SETS deparse the same way.
Cause
get_rule_groupingset() calls get_rule_sortgroupclause() with force_colno =
false. The parser
(findTargetlistEntrySQL99) resolves an expression to the first equal
targetlist entry, so the
second member's sortgroupref moves to the first column.
Proposed fix
print the column number for a grouping-set member when an earlier non-junk
targetlist
entry has an equal expression. This gives GROUP BY ROLLUP(a, 2). All
regression tests pass with
the patch, and a new test is included in groupingsets.sql.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, 125atishprak@gmail.com
Subject: Re: BUG #19835: view with GROUP BY ROLLUP over duplicate columns changes results after dump/restore
In-Reply-To: <19835-65b2938eff695dc6@postgresql.org>
References: <19835-65b2938eff695dc6@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox