Обсуждение: BUG #19353: Error XX000 if referencing expanded array in grouping set: variable not found in subplan target list

The following bug has been logged on the website:

Bug reference:      19353
Logged by:          Marian MULLER REBEYROL
Email address:      marian.muller@serli.com
PostgreSQL version: 18.1
Operating system:   Linux
Description:

Hi,

Let me start by thanking you for providing such a great tool!

After upgrading to Postgres 18 I've come across an error I wasn't getting
beforehand. Here's a minimal way to reproduce the issue, that used to work
well in Postgres 12 and 17 at least.

Table and data:
```
create table items (
  id      varchar(255) primary key,
  brands  varchar array,
  markets varchar array
);
insert into items values ('Item1', '{Brand A,Brand B}', '{Market A}');
insert into items values ('Item2', '{Brand B}', '{Market B,Market A}');
insert into items values ('Item3', '{Brand A,Brand C}', '{Market B}');
```

When querying this table using grouping sets, I get an internal error as
soon as the grouping sets reference an expanded array, for instance this
query works:
```
SELECT brands, markets, count(distinct id) count
FROM items
GROUP BY GROUPING SETS (brands, markets, ())
ORDER BY brands asc, markets asc;
```

But this one triggers an error:
```
SELECT brands, unnest(markets) as market, count(distinct id) count
FROM items
GROUP BY GROUPING SETS (brands, market, ())
ORDER BY brands asc, market asc;
```

This gives me the following error on the latest release (PostgreSQL 18.1 on
x86_64-pc-linux-gnu, compiled by gcc (GCC) 15.2.1 20251112, 64-bit):
```
ERROR:  XX000: variable not found in subplan target list
LOCATION:  fix_upper_expr_mutator, setrefs.c:3314
```

I've confirmed the error still exists with a vanilla freshly-compiled
Postgres (PostgreSQL 19devel on x86_64-pc-linux-gnu, compiled by gcc (GCC)
15.2.1 20251112, 64-bit ; latest commit at the time: b65f1ad):
```
2025-12-12 10:39:51.151 CET [347004] ERROR:  variable not found in subplan
target list
2025-12-12 10:39:51.151 CET [347004] STATEMENT:  SELECT brands,
unnest(markets) as market, count(distinct id) count
        FROM items
        GROUP BY GROUPING SETS (brands, market, ())
        ORDER BY brands asc, market asc;
ERROR:  XX000: variable not found in subplan target list
LOCATION:  fix_upper_expr_mutator, setrefs.c:3335
```

While this query used to work in previous versions, I'm unsure from the
documentation whether it is expected to work or not.

My platform is GNU/Linux (up-to-date Manjaro), kernel 6.16.8, on x86_64.
I've also experienced the error on a cloud-hosted Postgres instance.

I've tried to be exhaustive but will gladly provide more information if
necessary.

Thanks.

PG Bug reporting form <noreply@postgresql.org> writes:
> After upgrading to Postgres 18 I've come across an error I wasn't getting
> beforehand. Here's a minimal way to reproduce the issue, that used to work
> well in Postgres 12 and 17 at least.

Thank you for this well-crafted bug report!  Bisecting shows that
it broke at

f5050f795aea67dfc40bbc429c8934e9439e22e7 is the first bad commit
commit f5050f795aea67dfc40bbc429c8934e9439e22e7 (HEAD)
Author: Richard Guo <rguo@postgresql.org>
Date:   Tue Sep 10 12:36:48 2024 +0900

    Mark expressions nullable by grouping sets

I have not dug any deeper than that.

            regards, tom lane