pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19783: Sorted GROUPING SETS return a grouping column's value in a set that leaves it out
6+ messages / 3 participants
[nested] [flat]
Commits from this thread:
2026-10-29 93ec486803 04e0891fe0 Fix issue with code manually adjusting a TupleTableSlot
* BUG #19783: Sorted GROUPING SETS return a grouping column's value in a set that leaves it out
@ 2026-10-21 19:59 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 2 replies; 6+ messages in thread
From: PG Bug reporting form @ 2026-10-21 19:59 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: alpkeles99@gmail.com
The following bug has been logged on the website:
Bug reference: 19783
Logged by: Alperen Keles
Email address: alpkeles99@gmail.com
PostgreSQL version: 19rc1
Operating system: macOS 26 arm64
Description:
Hello,
With a sorted grouping-sets plan (GroupAggregate), a column that is not
in the current grouping set can show its real value instead of NULL,
while GROUPING() for the same row says it is not grouped. It happens
when the row has a NULL somewhere and the target list reads a column
that comes after the grouping columns in the Agg's input:
CREATE TABLE t (id int PRIMARY KEY, a int, b int, c int);
INSERT INTO t VALUES (1, 10, NULL, 100);
SET enable_hashagg = off;
SELECT a, b, c, GROUPING(a, b) AS g
FROM t GROUP BY GROUPING SETS ((id, a), (id, b));
a | b | c | g
----+---+-----+---
10 | | 100 | 1
10 | | 100 | 2
(2 rows)
The second row belongs to the set (id, b), so a should be NULL there
(g = 2 says so). 18.6 and 14.24, and master with enable_hashagg = on,
give
a | b | c | g
----+---+-----+---
10 | | 100 | 1
| | 100 | 2
With b = 20 instead of NULL, or without c in the select list, the
result is right. The plan is
GroupAggregate
Output: a, b, c, GROUPING(a, b), id
Group Key: t.id, t.a
Sort Key: t.id, t.b
Group Key: t.id, t.b
-> Sort
Output: a, b, id, c
Sort Key: t.id, t.a
Reproduced on master dd568b047ac (2026-10-21) and 158b6d5109f,
macOS arm64, and on master a261fc2b722 and 19rc1 (REL_19_STABLE
1212d5d33b1), Ubuntu 24.04 x86_64, all --enable-cassert. Not on 18.6
(REL_18_STABLE a85b5523e67 on macOS, dfbc1507cdb on Ubuntu) or 14.24
(REL_14_STABLE 14904052e65, Ubuntu).
As far as I can tell, prepare_projection_slot() in nodeAgg.c deforms
the input slot up to the last grouping column and then sets
tts_isnull[] to true for the grouping columns not in the current set.
Projecting c then deforms the slot further. Since c456e391138
("Optimize tuple deformation"), slot_deform_heap_tuple() calls
populate_isnull_array() from attribute 0 whenever the tuple has a null
bitmap, so tts_isnull[] for the columns already deformed is rewritten
from the bitmap, and a's forced NULL is lost. The value in
tts_values[] is still a's. This looks like the same overwrite as in
the "Whole row var issue" thread, fixed for missing attributes in
d260d7d003f; master with that commit still gives the result above.
Found with a query fuzzer.
Regards,
Alperen Keles
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19783: Sorted GROUPING SETS return a grouping column's value in a set that leaves it out
@ 2026-10-25 14:03 Ayush Tiwari <ayushtiwari.slg01@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 1 reply; 6+ messages in thread
From: Ayush Tiwari @ 2026-10-25 14:03 UTC (permalink / raw)
To: alpkeles99@gmail.com, pgsql-bugs@lists.postgresql.org, David Rowley <dgrowleyml@gmail.com>
Hi,
On Thu, 22 Oct 2026 at 11:55, PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 19783
> Logged by: Alperen Keles
> Email address: alpkeles99@gmail.com
> PostgreSQL version: 19rc1
> Operating system: macOS 26 arm64
> Description:
>
> Hello,
>
> With a sorted grouping-sets plan (GroupAggregate), a column that is not
> in the current grouping set can show its real value instead of NULL,
> while GROUPING() for the same row says it is not grouped. It happens
> when the row has a NULL somewhere and the target list reads a column
> that comes after the grouping columns in the Agg's input:
>
> CREATE TABLE t (id int PRIMARY KEY, a int, b int, c int);
> INSERT INTO t VALUES (1, 10, NULL, 100);
> SET enable_hashagg = off;
> SELECT a, b, c, GROUPING(a, b) AS g
> FROM t GROUP BY GROUPING SETS ((id, a), (id, b));
>
> a | b | c | g
> ----+---+-----+---
> 10 | | 100 | 1
> 10 | | 100 | 2
> (2 rows)
>
> The second row belongs to the set (id, b), so a should be NULL there
> (g = 2 says so). 18.6 and 14.24, and master with enable_hashagg = on,
> give
>
> a | b | c | g
> ----+---+-----+---
> 10 | | 100 | 1
> | | 100 | 2
>
> With b = 20 instead of NULL, or without c in the select list, the
> result is right. The plan is
>
> GroupAggregate
> Output: a, b, c, GROUPING(a, b), id
> Group Key: t.id, t.a
> Sort Key: t.id, t.b
> Group Key: t.id, t.b
> -> Sort
> Output: a, b, id, c
> Sort Key: t.id, t.a
>
Thanks for the report and repro, I could repro it in master.
I think we're losing the NULL set by prepare_projection_slot() when
we fetch c. That deforms the slot again and seems to put a's original
NULL flag back, so we get 10 instead of NULL.
I tried slot_getallattrs() before setting the NULLs and it fixes the
example. But I'm unsure if it will be reasonable here, since it would
increase the cost of fetching columns which are not really required.
ccing David, should we add this to the PG 19 open items?
Regards,
Ayush
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19783: Sorted GROUPING SETS return a grouping column's value in a set that leaves it out
@ 2026-10-25 21:27 David Rowley <dgrowleyml@gmail.com>
parent: Ayush Tiwari <ayushtiwari.slg01@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: David Rowley @ 2026-10-25 21:27 UTC (permalink / raw)
To: Ayush Tiwari <ayushtiwari.slg01@gmail.com>; +Cc: alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org
On Mon, 26 Oct 2026 at 03:03, Ayush Tiwari <ayushtiwari.slg01@gmail.com> wrote:
> ccing David, should we add this to the PG 19 open items?
I'm looking now. I've added to open items.
David
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19783: Sorted GROUPING SETS return a grouping column's value in a set that leaves it out
@ 2026-10-27 04:12 David Rowley <dgrowleyml@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 1 reply; 6+ messages in thread
From: David Rowley @ 2026-10-27 04:12 UTC (permalink / raw)
To: alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org
On Thu, 22 Oct 2026 at 19:25, PG Bug reporting form
<noreply@postgresql.org> wrote:
> With a sorted grouping-sets plan (GroupAggregate), a column that is not
> in the current grouping set can show its real value instead of NULL,
> while GROUPING() for the same row says it is not grouped. It happens
> when the row has a NULL somewhere and the target list reads a column
> that comes after the grouping columns in the Agg's input:
>
> CREATE TABLE t (id int PRIMARY KEY, a int, b int, c int);
> INSERT INTO t VALUES (1, 10, NULL, 100);
> SET enable_hashagg = off;
> SELECT a, b, c, GROUPING(a, b) AS g
> FROM t GROUP BY GROUPING SETS ((id, a), (id, b));
>
> a | b | c | g
> ----+---+-----+---
> 10 | | 100 | 1
> 10 | | 100 | 2
> (2 rows)
Many thanks for reporting this.
What's happening here is due to the change c456e3911 made, as it now
uses a sort of slice-by-eight method of deforming the tuple's NULL
bitmap into the slot's tts_values array. When working on that, I
considered if the incremental deform should be careful to pick up
where it left off or not. I opted not, as the masking out of previous
bits or tts_isnull elements would, in most cases, cost more than it
would save.
The problem is that prepare_projection_slot() overwrites the slot's
tts_isnull elements to represent the rollup rows, but if any other
column is required from the tuple, in this case "c", then that will
overwrite the manually adjusted NULL when performing additional
deforming on that slot.
The reported case allows "c" to be in the SELECT list because it's
functionally dependent on the primary key. Ordinarily, it would be in
the GROUP BY clause or in an aggregate parameter, in which case we'd
have deformed it already. The heap tuple also needs to have at least 1
NULL value, else we wouldn't be deforming the NULL bitmask. The
deforming code would just "isnull[attnum] = false" each element as
they're deformed, and that wouldn't overwrite any previously deformed
attribute's null property.
I thought of a few ways to fix this:
1. Modify populate_isnull_array() to pick up where it left off. Likely
by adjusting the loop start byte, combined with adjusting the memcpy()
to not overwrite bytes that are previously deformed.
2. Modify ExprState code to remember the last outer varattno required,
then deform to there in prepare_projection_slot().
One problem with #2 is that if some very high varattno is needed for
the projection, but some lesser varattno is needed for the HAVING
qual, then we might deform more of the tuple than we need to when the
HAVING qual filters out the tuple. Ultimately, we'd not perform the
projection step in that case, therefore not deform the remaining
attributes. I was less concerned about that when I saw the hashed
version does this anyway. I understand it's possible to craft a case
where this might make a difference, but I suspect in practice that
it's not going to be an issue.
I found another way of doing #2 (attached) without the mods to
ExprState by reusing some existing code in nodeAgg.c which walks the
targetlist and Aggrefs looking for columns. That was only being
executed for the hashing code path, so I added some code to execute
this for the non-hash version.
David
Attachments:
v1-0001-Fix-issue-with-code-manually-adjusting-a-TupleTab.patch (application/octet-stream, 6.1K) download
From d1e28c5eb8f937b382eab7df0e6869508ffae5fb Mon Sep 17 00:00:00 2001
From: David Rowley <dgrowley@gmail.com>
Date: Tue, 27 Oct 2026 12:47:08 +1300
Subject: [PATCH v1] Fix issue with code manually adjusting a TupleTableSlot
In prepare_projection_slot() when grouped_cols was non-empty, the code
would manually put a NULL into grouped columns to represent the rollup
row. This relied on the old pre-c456e3911 tuple deforming code which,
when performing incremental deforming, would only touch tts_isnull
elements for attributes that were yet to be deformed. c456e3911 changed
that so that deforming the NULL bitmap was done again on every call as
masking out previously deformed attributes would likely be more effort
than it would ever save. Due to the changes made in c456e3911, if any
other attributes were required from the slot after the
slot_getsomeattrs() call in prepare_projection_slot(), then the
deformation done for those would have overwritten the manually placed
NULL value.
Here we fix this by changing the slot_getsomeattrs() call to always
deform everything we need for this tuple. That means there will be no
additional tuple deforms for this tuple.
Bug: #19783
Reported-by: Alperen Keles <alpkeles99@gmail.com>
Author: David Rowley <dgrowleyml@gmail.com>
Discussion: https://postgr.es/m/19783-070e4bf0c73c1076@postgresql.org
Backpatch-through: 19
---
src/backend/executor/nodeAgg.c | 29 +++++++++++++++++++---
src/test/regress/expected/groupingsets.out | 14 +++++++++++
src/test/regress/sql/groupingsets.sql | 9 +++++++
3 files changed, 49 insertions(+), 3 deletions(-)
diff --git a/src/backend/executor/nodeAgg.c b/src/backend/executor/nodeAgg.c
index 29037cf3122..2d3174dff21 100644
--- a/src/backend/executor/nodeAgg.c
+++ b/src/backend/executor/nodeAgg.c
@@ -1229,7 +1229,7 @@ prepare_hash_slot(AggStatePerHash perhash,
* read as null in the context of the current grouping set. Also stash the
* current group bitmap where GroupingExpr can get at it.
*
- * This relies on three conditions:
+ * This relies on four conditions:
*
* 1) Nothing is ever going to try and extract the whole tuple from this slot,
* only reference it in evaluations, which will only access individual
@@ -1242,6 +1242,12 @@ prepare_hash_slot(AggStatePerHash perhash,
* 3) Within a given phase, we never need to recover the value of an attribute
* once it has been set to null.
*
+ * 4) We must extract all remaining required attributes from the tuple before
+ * manually adjusting the tts_isnull elements. This is required to prevent
+ * further deformations that might be required for the HAVING clause or
+ * projection step from re-extracting the NULL bitmap into the tts_isnull
+ * array and overwriting our manual adjustments.
+ *
* Poking into the slot this way is a bit ugly, but the consensus is that the
* alternative was worse.
*/
@@ -1267,8 +1273,12 @@ prepare_projection_slot(AggState *aggstate, TupleTableSlot *slot, int currentSet
{
ListCell *lc;
- /* all_grouped_cols is arranged in desc order */
- slot_getsomeattrs(slot, linitial_int(aggstate->all_grouped_cols));
+ /* Sanity check on max_colno_needed value */
+ Assert(aggstate->max_colno_needed >=
+ linitial_int(aggstate->all_grouped_cols));
+
+ /* deform all remaining columns, per #4 above */
+ slot_getsomeattrs(slot, aggstate->max_colno_needed);
foreach(lc, aggstate->all_grouped_cols)
{
@@ -3721,6 +3731,19 @@ ExecInitAgg(Agg *node, EState *estate, int eflags)
/* Initialize this to 1, meaning nothing spilled, yet */
aggstate->hash_batches_used = 1;
}
+ else
+ {
+ Bitmapset *varattnos;
+ Bitmapset *aggvarattnos;
+ int max_varattno;
+
+ /* Figure out the highest outer-side varattno required. */
+ find_cols(aggstate, &aggvarattnos, &varattnos);
+ max_varattno = bms_prev_member(varattnos, -1);
+ max_varattno = Max(max_varattno, bms_prev_member(aggvarattnos, -1));
+
+ aggstate->max_colno_needed = max_varattno;
+ }
/*
* Initialize current phase-dependent values to initial phase. The initial
diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out
index 08d5ce3156d..393d63cfb0c 100644
--- a/src/test/regress/expected/groupingsets.out
+++ b/src/test/regress/expected/groupingsets.out
@@ -1784,6 +1784,20 @@ SELECT a, b, count(*), max(a), max(b) FROM gstest3 GROUP BY GROUPING SETS(a, b,(
(5 rows)
COMMIT;
+-- Verify handling of injecting NULL values for the rollup row works correctly
+-- when additional columns need to be deformed from the tuple after the NULL
+-- is added.
+begin;
+insert into gstest3 values(3,4,null,5);
+set enable_hashagg = false;
+select b, c, d, grouping(b, c) AS g from gstest3 where a = 3 group by grouping sets ((a, b), (a, c));
+ b | c | d | g
+---+---+---+---
+ 4 | | 5 | 1
+ | | 5 | 2
+(2 rows)
+
+rollback;
-- More rescan tests
select * from (values (1),(2)) v(a) left join lateral (select v.a, four, ten, count(*) from onek group by cube(four,ten)) s on true order by v.a,four,ten;
a | a | four | ten | count
diff --git a/src/test/regress/sql/groupingsets.sql b/src/test/regress/sql/groupingsets.sql
index c5d4ed2eb57..91c05adf432 100644
--- a/src/test/regress/sql/groupingsets.sql
+++ b/src/test/regress/sql/groupingsets.sql
@@ -528,6 +528,15 @@ EXPLAIN (COSTS OFF) SELECT a, b, count(*), max(a), max(b) FROM gstest3 GROUP BY
SELECT a, b, count(*), max(a), max(b) FROM gstest3 GROUP BY GROUPING SETS(a, b,()) ORDER BY a, b;
COMMIT;
+-- Verify handling of injecting NULL values for the rollup row works correctly
+-- when additional columns need to be deformed from the tuple after the NULL
+-- is added.
+begin;
+insert into gstest3 values(3,4,null,5);
+set enable_hashagg = false;
+select b, c, d, grouping(b, c) AS g from gstest3 where a = 3 group by grouping sets ((a, b), (a, c));
+rollback;
+
-- More rescan tests
select * from (values (1),(2)) v(a) left join lateral (select v.a, four, ten, count(*) from onek group by cube(four,ten)) s on true order by v.a,four,ten;
select array(select row(v.a,s1.*) from (select two,four, count(*) from onek group by cube(two,four) order by two,four) s1) from (values (1),(2)) v(a);
--
2.53.0
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19783: Sorted GROUPING SETS return a grouping column's value in a set that leaves it out
@ 2026-10-28 05:40 Ayush Tiwari <ayushtiwari.slg01@gmail.com>
parent: David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Ayush Tiwari @ 2026-10-28 05:40 UTC (permalink / raw)
To: David Rowley <dgrowleyml@gmail.com>; +Cc: alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org
Hi,
On Tue, 27 Oct 2026 at 09:42, David Rowley <dgrowleyml@gmail.com> wrote:
>
> On Thu, 22 Oct 2026 at 19:25, PG Bug reporting form
> <noreply@postgresql.org> wrote:
> > With a sorted grouping-sets plan (GroupAggregate), a column that is not
> > in the current grouping set can show its real value instead of NULL,
> > while GROUPING() for the same row says it is not grouped. It happens
> > when the row has a NULL somewhere and the target list reads a column
> > that comes after the grouping columns in the Agg's input:
> >
> > CREATE TABLE t (id int PRIMARY KEY, a int, b int, c int);
> > INSERT INTO t VALUES (1, 10, NULL, 100);
> > SET enable_hashagg = off;
> > SELECT a, b, c, GROUPING(a, b) AS g
> > FROM t GROUP BY GROUPING SETS ((id, a), (id, b));
> >
> > a | b | c | g
> > ----+---+-----+---
> > 10 | | 100 | 1
> > 10 | | 100 | 2
> > (2 rows)
>
> Many thanks for reporting this.
>
> What's happening here is due to the change c456e3911 made, as it now
> uses a sort of slice-by-eight method of deforming the tuple's NULL
> bitmap into the slot's tts_values array. When working on that, I
> considered if the incremental deform should be careful to pick up
> where it left off or not. I opted not, as the masking out of previous
> bits or tts_isnull elements would, in most cases, cost more than it
> would save.
>
> The problem is that prepare_projection_slot() overwrites the slot's
> tts_isnull elements to represent the rollup rows, but if any other
> column is required from the tuple, in this case "c", then that will
> overwrite the manually adjusted NULL when performing additional
> deforming on that slot.
>
> The reported case allows "c" to be in the SELECT list because it's
> functionally dependent on the primary key. Ordinarily, it would be in
> the GROUP BY clause or in an aggregate parameter, in which case we'd
> have deformed it already. The heap tuple also needs to have at least 1
> NULL value, else we wouldn't be deforming the NULL bitmask. The
> deforming code would just "isnull[attnum] = false" each element as
> they're deformed, and that wouldn't overwrite any previously deformed
> attribute's null property.
>
> I thought of a few ways to fix this:
>
> 1. Modify populate_isnull_array() to pick up where it left off. Likely
> by adjusting the loop start byte, combined with adjusting the memcpy()
> to not overwrite bytes that are previously deformed.
> 2. Modify ExprState code to remember the last outer varattno required,
> then deform to there in prepare_projection_slot().
>
> One problem with #2 is that if some very high varattno is needed for
> the projection, but some lesser varattno is needed for the HAVING
> qual, then we might deform more of the tuple than we need to when the
> HAVING qual filters out the tuple. Ultimately, we'd not perform the
> projection step in that case, therefore not deform the remaining
> attributes. I was less concerned about that when I saw the hashed
> version does this anyway. I understand it's possible to craft a case
> where this might make a difference, but I suspect in practice that
> it's not going to be an issue.
>
> I found another way of doing #2 (attached) without the mods to
> ExprState by reusing some existing code in nodeAgg.c which walks the
> targetlist and Aggrefs looking for columns. That was only being
> executed for the hashing code path, so I added some code to execute
> this for the non-hash version.
Thanks for the patch, LGTM.
Is there a reason though for not using else if (node->groupingSets)
for the new block instead of just else?
Regards,
Ayush
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19783: Sorted GROUPING SETS return a grouping column's value in a set that leaves it out
@ 2026-10-29 20:57 David Rowley <dgrowleyml@gmail.com>
parent: Ayush Tiwari <ayushtiwari.slg01@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: David Rowley @ 2026-10-29 20:57 UTC (permalink / raw)
To: Ayush Tiwari <ayushtiwari.slg01@gmail.com>; +Cc: alpkeles99@gmail.com; pgsql-bugs@lists.postgresql.org
On Wed, 28 Oct 2026 at 18:41, Ayush Tiwari <ayushtiwari.slg01@gmail.com> wrote:
> Thanks for the patch, LGTM.
>
> Is there a reason though for not using else if (node->groupingSets)
> for the new block instead of just else?
I went with "else if (aggstate->all_grouped_cols != NIL)". Pushed
after spending a bit more time on the commit message.
Thanks for looking.
David
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2026-10-29 20:57 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-21 19:59 BUG #19783: Sorted GROUPING SETS return a grouping column's value in a set that leaves it out PG Bug reporting form <noreply@postgresql.org>
2026-10-25 14:03 ` Ayush Tiwari <ayushtiwari.slg01@gmail.com>
2026-10-25 21:27 ` David Rowley <dgrowleyml@gmail.com>
2026-10-27 04:12 ` David Rowley <dgrowleyml@gmail.com>
2026-10-28 05:40 ` Ayush Tiwari <ayushtiwari.slg01@gmail.com>
2026-10-29 20:57 ` David Rowley <dgrowleyml@gmail.com>
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