BUG #19783: Sorted GROUPING SETS return a grouping column's value in a set that leaves it out

Поиск
Список
Период
Сортировка
Искать

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>
Дата:
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




Re: BUG #19783: Sorted GROUPING SETS return a grouping column's value in a set that leaves it out

От:
Ayush Tiwari <ayushtiwari.slg01@gmail.com>
Дата:
Hi,

On Thu, 22 Oct 2026 at 11:55, PG Bug reporting form
 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


Re: BUG #19783: Sorted GROUPING SETS return a grouping column's value in a set that leaves it out

От:
David Rowley <dgrowleyml@gmail.com>
Дата:
On Mon, 26 Oct 2026 at 03:03, Ayush Tiwari  wrote:
> ccing David, should we add this to the PG 19 open items?

I'm looking now. I've added to open items.

David


Re: BUG #19783: Sorted GROUPING SETS return a grouping column's value in a set that leaves it out

От:
Ayush Tiwari <ayushtiwari.slg01@gmail.com>
Дата:
Hi,

On Tue, 27 Oct 2026 at 09:42, David Rowley  wrote:
>
> On Thu, 22 Oct 2026 at 19:25, PG Bug reporting form
>  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


Re: BUG #19783: Sorted GROUPING SETS return a grouping column's value in a set that leaves it out

От:
David Rowley <dgrowleyml@gmail.com>
Дата:
On Wed, 28 Oct 2026 at 18:41, Ayush Tiwari  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


FAQ