pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19353: Error XX000 if referencing expanded array in grouping set: variable not found in subplan target list
9+ messages / 5 participants
[nested] [flat]
* BUG #19353: Error XX000 if referencing expanded array in grouping set: variable not found in subplan target list
@ 2025-12-12 10:11 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 9+ messages in thread
From: PG Bug reporting form @ 2025-12-12 10:11 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: marian.muller@serli.com
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.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19353: Error XX000 if referencing expanded array in grouping set: variable not found in subplan target list
@ 2025-12-12 16:54 Tom Lane <tgl@sss.pgh.pa.us>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2025-12-12 16:54 UTC (permalink / raw)
To: marian.muller@serli.com; +Cc: pgsql-bugs@lists.postgresql.org, Richard Guo <guofenglinux@gmail.com>
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19353: Error XX000 if referencing expanded array in grouping set: variable not found in subplan target list
@ 2025-12-13 10:01 Tender Wang <tndrwang@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Tender Wang @ 2025-12-13 10:01 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: marian.muller@serli.com, pgsql-bugs@lists.postgresql.org, Richard Guo <guofenglinux@gmail.com>
Tom Lane <tgl@sss.pgh.pa.us> 于2025年12月13日周六 00:54写道：
> 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
>
>
>
When there is SRF in the query, the grouping_target changes in
grouping_planner when entering the following function:
split_pathtarget_at_srfs(root, grouping_target, scanjoin_target,
&grouping_targets,
&grouping_targets_contain_srfs);
(gdb) pgprint grouping_target
PathTarget [sortgrouprefs=0x556daae88358 cost={startup = 0, per_tuple = 0}
width=72 has_volatile_expr=VOLATILITY_UNKNOWN]
[exprs]
Var [varno=1 varattno=3 vartype=1015 varcollid=100
varreturningtype=VAR_RETURNING_DEFAULT varnosyn=1
varattnosyn=3][varnullingrels] Bitmapset [2]
Var [varno=1 varattno=2 vartype=1015 varcollid=100
varreturningtype=VAR_RETURNING_DEFAULT varnosyn=1
varattnosyn=2][varnullingrels] Bitmapset [2]
Aggref [aggfnoid=2147 aggtype=20 inputcollid=100 aggtranstype=20
aggstar=false aggvariadic=false aggkind=110 'n' aggpresorted=false
agglevelsup=0 aggsplit=AGGSPLIT_SIMPLE
aggno=0 aggtransno=0][aggargtypes] OidList: [1043]
[args]
TargetEntry [resno=1 ressortgroupref=1]
Var [varno=1 varattno=1 vartype=1043 vartypmod=259 varcollid=100
varreturningtype=VAR_RETURNING_DEFAULT varnosyn=1 varattnosyn=1]
[aggdistinct]
SortGroupClause [tleSortGroupRef=1 eqop=98 sortop=664 reverse_sort=false
nulls_first=false hashable=true]
So the targetlist of Agg is :
gdb) pgprint plan->targetlist
TargetEntry [resno=1]
Var [varno=1 varattno=3 vartype=1015 varcollid=100
varreturningtype=VAR_RETURNING_DEFAULT varnosyn=1
varattnosyn=3][varnullingrels] Bitmapset [2]
TargetEntry [resno=2 ressortgroupref=1]
Var [varno=1 varattno=2 vartype=1015 varcollid=100
varreturningtype=VAR_RETURNING_DEFAULT varnosyn=1
varattnosyn=2][varnullingrels] Bitmapset [2]
TargetEntry [resno=3]
Aggref [aggfnoid=2147 aggtype=20 inputcollid=100 aggtranstype=20
aggstar=false aggvariadic=false aggkind=110 'n' aggpresorted=false
agglevelsup=0 aggsplit=AGGSPLIT_SIMPLE
aggno=0 aggtransno=0]
[aggargtypes] OidList: [1043]
[args]
TargetEntry [resno=1 ressortgroupref=1]
Var [varno=1 varattno=1 vartype=1043 vartypmod=259 varcollid=100
varreturningtype=VAR_RETURNING_DEFAULT varnosyn=1 varattnosyn=1]
[aggdistinct]
SortGroupClause [tleSortGroupRef=1 eqop=98 sortop=664 reverse_sort=false
nulls_first=false hashable=true]
But the subplan->targetlist is :
Var [varno=1 varattno=2 vartype=1015 varcollid=100
varreturningtype=VAR_RETURNING_DEFAULT varnosyn=1 varattnosyn=2]
FuncExpr [funcid=2331 funcresulttype=1043 funcretset=true
funcvariadic=false funcformat=COERCE_EXPLICIT_CALL funccollid=100
inputcollid=100]
Var [varno=1 varattno=3 vartype=1015 varcollid=100
varreturningtype=VAR_RETURNING_DEFAULT varnosyn=1 varattnosyn=3]
Var [varno=1 varattno=1 vartype=1043 vartypmod=259 varcollid=100
varreturningtype=VAR_RETURNING_DEFAULT varnosyn=1 varattnosyn=1]
in build_tlist_index(subplan->targetlist), varno = 1 varattno =3 will not
be added into tlist_vinfo array, so "ERROR: variable not found in subplan
target list"
will report because search_indexed_tlist_for_var() returning null
in fix_upper_expr_mutator().
--
Thanks,
Tender Wang
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19353: Error XX000 if referencing expanded array in grouping set: variable not found in subplan target list
@ 2025-12-15 14:28 Richard Guo <guofenglinux@gmail.com>
parent: Tender Wang <tndrwang@gmail.com>
0 siblings, 2 replies; 9+ messages in thread
From: Richard Guo @ 2025-12-15 14:28 UTC (permalink / raw)
To: Tender Wang <tndrwang@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; marian.muller@serli.com; pgsql-bugs@lists.postgresql.org
On Sat, Dec 13, 2025 at 7:02 PM Tender Wang <tndrwang@gmail.com> wrote:
> Tom Lane <tgl@sss.pgh.pa.us> 于2025年12月13日周六 00:54写道：
>> 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
> When there is SRF in the query, the grouping_target changes in grouping_planner when entering the following function:
> split_pathtarget_at_srfs(root, grouping_target, scanjoin_target,
> &grouping_targets,
> &grouping_targets_contain_srfs);
Yeah, the issue happens here. In split_pathtarget_at_srfs(), if we
find a subexpression that matches an expression already computed in
the previous plan level, we should treat it as a Var and should not
split it further. setrefs.c will later replace the expression with a
Var referencing the subplan output.
However, when processing the grouping target for grouping sets, this
function can fail to recognize that an expression is already computed
in the scan/join phase. This happens because the comparison crosses
the grouping boundary: expressions in the grouping target may carry
the grouping nulling bit, while the corresponding expressions in the
scan/join target do not.
This mismatch leads this function to incorrectly assume that the
expression (e.g., unnest(markets)) needs to be re-evaluated from its
arguments, which are often unavailable in the subplan.
To fix, I think we should ignore the grouping nulling bit when
checking if an expression from the grouping target is available in the
pre-grouping input target. This is actually what we do in setrefs.c.
Hence, attached patch.
(I'm currently on vacation and will take a closer look when I return.
Anyone who wants to move this forward before then is welcome.)
- Richard
Attachments:
[application/octet-stream] v1-0001-Fix-planner-error-with-SRFs-and-grouping-sets.patch (13.0K, ../../CAMbWs4_EzdNk+OmXN73VwHHGh2NP1RdWyquzVe-eiWaE=5NfDQ@mail.gmail.com/2-v1-0001-Fix-planner-error-with-SRFs-and-grouping-sets.patch)
download | inline diff:
From 97df144608033862447cbf57d5d090c811af0735 Mon Sep 17 00:00:00 2001
From: Richard Guo <guofenglinux@gmail.com>
Date: Mon, 15 Dec 2025 19:48:10 +0900
Subject: [PATCH v1] Fix planner error with SRFs and grouping sets
If there are any SRFs in a PathTarget, we must separate it into
SRF-computing and SRF-free targets. This is because the executor can
only handle SRFs that appear at the top level of the targetlist of a
ProjectSet plan node.
If we find a subexpression that matches an expression already computed
in the previous plan level, we should treat it like a Var and should
not split it again. setrefs.c will later replace the expression with
a Var referencing the subplan output.
However, when processing the grouping target for grouping sets, the
planner can fail to recognize that an expression is already computed
in the scan/join phase. The root cause is a mismatch in the
nullingrels bits. Expressions in the grouping target carry the
grouping nulling bit in their nullingrels to indicate that they can be
nulled by the grouping step. However, the corresponding expressions
in the scan/join target do not have these bits.
As a result, the exact match check in list_member() fails, leading the
planner to incorrectly believe that the expression needs to be
re-evaluated from its arguments, which are often not available in the
subplan. This can lead to planner errors such as "variable not found
in subplan target list".
To fix, ignore the grouping nulling bit when checking whether an
expression from the grouping target is available in the pre-grouping
input target. This aligns with the matching logic in setrefs.c.
---
src/backend/optimizer/plan/planner.c | 7 +-
src/backend/optimizer/util/tlist.c | 97 ++++++++++++++++++++--
src/include/optimizer/tlist.h | 5 ++
src/test/regress/expected/groupingsets.out | 59 +++++++++++++
src/test/regress/sql/groupingsets.sql | 23 +++++
5 files changed, 182 insertions(+), 9 deletions(-)
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 8b22c30559b..1268ea92b6f 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -1774,9 +1774,10 @@ grouping_planner(PlannerInfo *root, double tuple_fraction,
sort_input_target = linitial_node(PathTarget, sort_input_targets);
Assert(!linitial_int(sort_input_targets_contain_srfs));
/* likewise for grouping_target vs. scanjoin_target */
- split_pathtarget_at_srfs(root, grouping_target, scanjoin_target,
- &grouping_targets,
- &grouping_targets_contain_srfs);
+ split_pathtarget_at_srfs_grouping(root,
+ grouping_target, scanjoin_target,
+ &grouping_targets,
+ &grouping_targets_contain_srfs);
grouping_target = linitial_node(PathTarget, grouping_targets);
Assert(!linitial_int(grouping_targets_contain_srfs));
/* scanjoin_target will not have any SRFs precomputed for it */
diff --git a/src/backend/optimizer/util/tlist.c b/src/backend/optimizer/util/tlist.c
index af7b19be5c7..7d4b36d8f83 100644
--- a/src/backend/optimizer/util/tlist.c
+++ b/src/backend/optimizer/util/tlist.c
@@ -19,6 +19,7 @@
#include "optimizer/cost.h"
#include "optimizer/optimizer.h"
#include "optimizer/tlist.h"
+#include "rewrite/rewriteManip.h"
/*
@@ -45,6 +46,8 @@ typedef struct
typedef struct
{
+ PlannerInfo *root;
+ bool is_grouping_target; /* true if processing grouping target */
/* This is a List of bare expressions: */
List *input_target_exprs; /* exprs available from input */
/* These are Lists of Lists of split_pathtarget_items: */
@@ -59,6 +62,12 @@ typedef struct
Index current_sgref; /* current subexpr's sortgroupref, or 0 */
} split_pathtarget_context;
+static void split_pathtarget_at_srfs_extended(PlannerInfo *root,
+ PathTarget *target,
+ PathTarget *input_target,
+ List **targets,
+ List **targets_contain_srfs,
+ bool is_grouping_target);
static bool split_pathtarget_walker(Node *node,
split_pathtarget_context *context);
static void add_sp_item_to_pathtarget(PathTarget *target,
@@ -822,6 +831,51 @@ apply_pathtarget_labeling_to_tlist(List *tlist, PathTarget *target)
/*
* split_pathtarget_at_srfs
+ * Split given PathTarget into multiple levels to position SRFs safely,
+ * performing exact matching against input_target.
+ *
+ * This is a wrapper for split_pathtarget_at_srfs_extended() that is used when
+ * both targets are on the same side of the grouping boundary (i.e., both are
+ * pre-grouping or both are post-grouping). In this case, no special handling
+ * for the grouping nulling bit is required.
+ *
+ * See split_pathtarget_at_srfs_extended() for more details.
+ */
+void
+split_pathtarget_at_srfs(PlannerInfo *root,
+ PathTarget *target, PathTarget *input_target,
+ List **targets, List **targets_contain_srfs)
+{
+ split_pathtarget_at_srfs_extended(root, target, input_target,
+ targets, targets_contain_srfs,
+ false);
+}
+
+/*
+ * split_pathtarget_at_srfs_grouping
+ * Split given PathTarget into multiple levels to position SRFs safely,
+ * ignoring the grouping nulling bit when matching against input_target.
+ *
+ * This variant is used when the targets cross the grouping boundary (i.e.,
+ * target is post-grouping while input_target is pre-grouping). In this case,
+ * we need to ignore the grouping nulling bit when checking for expression
+ * availability to avoid incorrectly re-evaluating SRFs that have already been
+ * computed in input_target.
+ *
+ * See split_pathtarget_at_srfs_extended() for more details.
+ */
+void
+split_pathtarget_at_srfs_grouping(PlannerInfo *root,
+ PathTarget *target, PathTarget *input_target,
+ List **targets, List **targets_contain_srfs)
+{
+ split_pathtarget_at_srfs_extended(root, target, input_target,
+ targets, targets_contain_srfs,
+ true);
+}
+
+/*
+ * split_pathtarget_at_srfs_extended
* Split given PathTarget into multiple levels to position SRFs safely
*
* The executor can only handle set-returning functions that appear at the
@@ -860,6 +914,13 @@ apply_pathtarget_labeling_to_tlist(List *tlist, PathTarget *target)
* already meant as a reference to a lower subexpression). So, don't expand
* any tlist expressions that appear in input_target, if that's not NULL.
*
+ * This check requires extra care when processing the grouping target
+ * (indicated by the is_grouping_target flag). In this case input_target is
+ * pre-grouping while target is post-grouping, so the latter may carry
+ * nullingrels bits from the grouping step that are absent in the former. We
+ * must ignore those bits to correctly recognize that the tlist expressions are
+ * available in input_target.
+ *
* It's also important that we preserve any sortgroupref annotation appearing
* in the given target, especially on expressions matching input_target items.
*
@@ -877,10 +938,11 @@ apply_pathtarget_labeling_to_tlist(List *tlist, PathTarget *target)
* are only a few possible patterns for which levels contain SRFs.
* But this representation decouples callers from that knowledge.
*/
-void
-split_pathtarget_at_srfs(PlannerInfo *root,
- PathTarget *target, PathTarget *input_target,
- List **targets, List **targets_contain_srfs)
+static void
+split_pathtarget_at_srfs_extended(PlannerInfo *root,
+ PathTarget *target, PathTarget *input_target,
+ List **targets, List **targets_contain_srfs,
+ bool is_grouping_target)
{
split_pathtarget_context context;
int max_depth;
@@ -905,7 +967,12 @@ split_pathtarget_at_srfs(PlannerInfo *root,
return;
}
- /* Pass any input_target exprs down to split_pathtarget_walker() */
+ /*
+ * Pass 'root', the flag indicating whether we're processing the grouping
+ * target, and any input_target exprs down to split_pathtarget_walker().
+ */
+ context.root = root;
+ context.is_grouping_target = is_grouping_target;
context.input_target_exprs = input_target ? input_target->exprs : NIL;
/*
@@ -1076,9 +1143,27 @@ split_pathtarget_at_srfs(PlannerInfo *root,
static bool
split_pathtarget_walker(Node *node, split_pathtarget_context *context)
{
+ Node *sanitized_node = node;
+
if (node == NULL)
return false;
+ /*
+ * If we are crossing the grouping boundary (post-grouping target vs
+ * pre-grouping input_target), we must ignore the grouping nulling bit to
+ * correctly check if the subexpression is available in input_target.
+ * This aligns with the matching logic in set_upper_references().
+ */
+ if (context->is_grouping_target &&
+ context->root->parse->hasGroupRTE &&
+ context->root->parse->groupingSets != NIL)
+ {
+ sanitized_node =
+ remove_nulling_relids(node,
+ bms_make_singleton(context->root->group_rtindex),
+ NULL);
+ }
+
/*
* A subexpression that matches an expression already computed in
* input_target can be treated like a Var (which indeed it will be after
@@ -1087,7 +1172,7 @@ split_pathtarget_walker(Node *node, split_pathtarget_context *context)
* substructure. (Note in particular that this preserves the identity of
* any expressions that appear as sortgrouprefs in input_target.)
*/
- if (list_member(context->input_target_exprs, node))
+ if (list_member(context->input_target_exprs, sanitized_node))
{
split_pathtarget_item *item = palloc_object(split_pathtarget_item);
diff --git a/src/include/optimizer/tlist.h b/src/include/optimizer/tlist.h
index 9c355bf1179..8d2c1ec08ba 100644
--- a/src/include/optimizer/tlist.h
+++ b/src/include/optimizer/tlist.h
@@ -48,6 +48,11 @@ extern void apply_pathtarget_labeling_to_tlist(List *tlist, PathTarget *target);
extern void split_pathtarget_at_srfs(PlannerInfo *root,
PathTarget *target, PathTarget *input_target,
List **targets, List **targets_contain_srfs);
+extern void split_pathtarget_at_srfs_grouping(PlannerInfo *root,
+ PathTarget *target,
+ PathTarget *input_target,
+ List **targets,
+ List **targets_contain_srfs);
/* Convenience macro to get a PathTarget with valid cost/width fields */
#define create_pathtarget(root, tlist) \
diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out
index 39d35a195bc..668df1f3847 100644
--- a/src/test/regress/expected/groupingsets.out
+++ b/src/test/regress/expected/groupingsets.out
@@ -2563,4 +2563,63 @@ group by grouping sets((a, b), (a));
2 | 2 | 4
(4 rows)
+-- test handling of SRFs with grouping sets
+explain (costs off)
+select generate_series(1, a) as g
+from (values (1, 1), (2, 2)) as t (a, b)
+group by rollup(g)
+order by 1;
+ QUERY PLAN
+----------------------------------------------------------
+ Sort
+ Sort Key: (generate_series(1, "*VALUES*".column1))
+ -> MixedAggregate
+ Hash Key: generate_series(1, "*VALUES*".column1)
+ Group Key: ()
+ -> ProjectSet
+ -> Values Scan on "*VALUES*"
+(7 rows)
+
+select generate_series(1, a) as g
+from (values (1, 1), (2, 2)) as t (a, b)
+group by rollup(g)
+order by 1;
+ g
+---
+ 1
+ 2
+
+(3 rows)
+
+explain (costs off)
+select generate_series(1, a) as g, a+b as ab
+from (values (1, 1), (2, 2)) as t (a, b)
+group by rollup(a, ab)
+order by 1, 2;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------
+ Sort
+ Sort Key: (generate_series(1, "*VALUES*".column1)), (("*VALUES*".column1 + "*VALUES*".column2))
+ -> ProjectSet
+ -> MixedAggregate
+ Hash Key: "*VALUES*".column1, ("*VALUES*".column1 + "*VALUES*".column2)
+ Hash Key: "*VALUES*".column1
+ Group Key: ()
+ -> Values Scan on "*VALUES*"
+(8 rows)
+
+select generate_series(1, a) as g, a+b as ab
+from (values (1, 1), (2, 2)) as t (a, b)
+group by rollup(a, ab)
+order by 1, 2;
+ g | ab
+---+----
+ 1 | 2
+ 1 | 4
+ 1 |
+ 1 |
+ 2 | 4
+ 2 |
+(6 rows)
+
-- end
diff --git a/src/test/regress/sql/groupingsets.sql b/src/test/regress/sql/groupingsets.sql
index 6d875475fae..d5326872e1a 100644
--- a/src/test/regress/sql/groupingsets.sql
+++ b/src/test/regress/sql/groupingsets.sql
@@ -721,4 +721,27 @@ select a, b, row_number() over (order by a, b nulls first)
from (values (1, 1), (2, 2)) as t (a, b) where a = b
group by grouping sets((a, b), (a));
+-- test handling of SRFs with grouping sets
+explain (costs off)
+select generate_series(1, a) as g
+from (values (1, 1), (2, 2)) as t (a, b)
+group by rollup(g)
+order by 1;
+
+select generate_series(1, a) as g
+from (values (1, 1), (2, 2)) as t (a, b)
+group by rollup(g)
+order by 1;
+
+explain (costs off)
+select generate_series(1, a) as g, a+b as ab
+from (values (1, 1), (2, 2)) as t (a, b)
+group by rollup(a, ab)
+order by 1, 2;
+
+select generate_series(1, a) as g, a+b as ab
+from (values (1, 1), (2, 2)) as t (a, b)
+group by rollup(a, ab)
+order by 1, 2;
+
-- end
--
2.39.5 (Apple Git-154)
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19353: Error XX000 if referencing expanded array in grouping set: variable not found in subplan target list
@ 2025-12-16 11:30 Tender Wang <tndrwang@gmail.com>
parent: Richard Guo <guofenglinux@gmail.com>
1 sibling, 0 replies; 9+ messages in thread
From: Tender Wang @ 2025-12-16 11:30 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; marian.muller@serli.com; pgsql-bugs@lists.postgresql.org
Richard Guo <guofenglinux@gmail.com> 于2025年12月15日周一 22:29写道：
> On Sat, Dec 13, 2025 at 7:02 PM Tender Wang <tndrwang@gmail.com> wrote:
> > Tom Lane <tgl@sss.pgh.pa.us> 于2025年12月13日周六 00:54写道：
> >> 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
>
> > When there is SRF in the query, the grouping_target changes in
> grouping_planner when entering the following function:
> > split_pathtarget_at_srfs(root, grouping_target, scanjoin_target,
> > &grouping_targets,
> > &grouping_targets_contain_srfs);
>
> Yeah, the issue happens here. In split_pathtarget_at_srfs(), if we
> find a subexpression that matches an expression already computed in
> the previous plan level, we should treat it as a Var and should not
> split it further. setrefs.c will later replace the expression with a
> Var referencing the subplan output.
>
> However, when processing the grouping target for grouping sets, this
> function can fail to recognize that an expression is already computed
> in the scan/join phase. This happens because the comparison crosses
> the grouping boundary: expressions in the grouping target may carry
> the grouping nulling bit, while the corresponding expressions in the
> scan/join target do not.
>
Yes， grouping target containing nulling bits makes
equal(var_grouping_target, var_in_scan_join_target) return false.
The nulling bits in the grouping target should be ignored when processing
across the grouping boundary.
> This mismatch leads this function to incorrectly assume that the
> expression (e.g., unnest(markets)) needs to be re-evaluated from its
> arguments, which are often unavailable in the subplan.
>
> To fix, I think we should ignore the grouping nulling bit when
> checking if an expression from the grouping target is available in the
> pre-grouping input target. This is actually what we do in setrefs.c.
>
> Hence, attached patch.
>
The patch works for me. All regression tests pass with this patch.
>
> (I'm currently on vacation and will take a closer look when I return.
> Anyone who wants to move this forward before then is welcome.)
>
Enjoy your vacation!
--
Thanks,
Tender Wang
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19353: Error XX000 if referencing expanded array in grouping set: variable not found in subplan target list
@ 2025-12-24 02:15 Richard Guo <guofenglinux@gmail.com>
parent: Richard Guo <guofenglinux@gmail.com>
1 sibling, 1 reply; 9+ messages in thread
From: Richard Guo @ 2025-12-24 02:15 UTC (permalink / raw)
To: Tender Wang <tndrwang@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; marian.muller@serli.com; pgsql-bugs@lists.postgresql.org
On Mon, Dec 15, 2025 at 11:28 PM Richard Guo <guofenglinux@gmail.com> wrote:
> To fix, I think we should ignore the grouping nulling bit when
> checking if an expression from the grouping target is available in the
> pre-grouping input target. This is actually what we do in setrefs.c.
>
> Hence, attached patch.
Regarding back-patching, we need to pass a new parameter to
split_pathtarget_at_srfs() to indicate whether we are processing a
grouping target. But doing that would break ABI compatibility.
To avoid this, I refactored the logic into a new static workhorse
function which accepts the new parameter. The original
split_pathtarget_at_srfs() is preserved as a wrapper that calls this
static function. I believe this is ABI safe.
The patch also introduces a new extern function that calls the
workhorse with the grouping flag enabled. Additionally, it adds two
new fields to struct split_pathtarget_context, but that struct is
defined entirely within the .c file.
Could someone confirm if this is ABI safe? I don't want to cause ABI
troubles in back branches.
- Richard
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19353: Error XX000 if referencing expanded array in grouping set: variable not found in subplan target list
@ 2025-12-24 02:40 Tender Wang <tndrwang@gmail.com>
parent: Richard Guo <guofenglinux@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Tender Wang @ 2025-12-24 02:40 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; marian.muller@serli.com; pgsql-bugs@lists.postgresql.org
Richard Guo <guofenglinux@gmail.com> 于2025年12月24日周三 10:15写道：
> On Mon, Dec 15, 2025 at 11:28 PM Richard Guo <guofenglinux@gmail.com>
> wrote:
> > To fix, I think we should ignore the grouping nulling bit when
> > checking if an expression from the grouping target is available in the
> > pre-grouping input target. This is actually what we do in setrefs.c.
> >
> > Hence, attached patch.
>
> Regarding back-patching, we need to pass a new parameter to
> split_pathtarget_at_srfs() to indicate whether we are processing a
> grouping target. But doing that would break ABI compatibility.
>
Indeed. I ignored this issue.
>
> To avoid this, I refactored the logic into a new static workhorse
> function which accepts the new parameter. The original
> split_pathtarget_at_srfs() is preserved as a wrapper that calls this
> static function. I believe this is ABI safe.
>
Yeah, A wrapper function can make the ABI safe.
>
> The patch also introduces a new extern function that calls the
> workhorse with the grouping flag enabled. Additionally, it adds two
> new fields to struct split_pathtarget_context, but that struct is
> defined entirely within the .c file.
>
It is safe if the struct is only used in a .c file. It would be better to
add a note in the comment for the struct to notice.
> Could someone confirm if this is ABI safe? I don't want to cause ABI
> troubles in back branches.
>
> - Richard
>
--
Thanks,
Tender Wang
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19353: Error XX000 if referencing expanded array in grouping set: variable not found in subplan target list
@ 2025-12-25 03:58 Richard Guo <guofenglinux@gmail.com>
parent: Tender Wang <tndrwang@gmail.com>
0 siblings, 1 reply; 9+ messages in thread
From: Richard Guo @ 2025-12-25 03:58 UTC (permalink / raw)
To: Tender Wang <tndrwang@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; marian.muller@serli.com; pgsql-bugs@lists.postgresql.org
On Wed, Dec 24, 2025 at 11:40 AM Tender Wang <tndrwang@gmail.com> wrote:
> Richard Guo <guofenglinux@gmail.com> 于2025年12月24日周三 10:15写道：
>> The patch also introduces a new extern function that calls the
>> workhorse with the grouping flag enabled. Additionally, it adds two
>> new fields to struct split_pathtarget_context, but that struct is
>> defined entirely within the .c file.
> It is safe if the struct is only used in a .c file. It would be better to add a note in the comment for the struct to notice.
Yeah, a struct defined in .c file should be safe to modify. My main
concern was adding a new extern function to back-branches, as it can
break forward compatibility (like an extension built on 18.2 not
working on 18.1).
After a quick discussion on Discord, I believe it is safe to proceed.
Forward compatibility issues can only happen if a new extern function
is added to core and meanwhile called by an extension, which is quite
rare.
Thus, I have pushed and back-patched this fix.
Thank you, Marian, for the report. It was a good find. The fix will
be available in 18.2, which is scheduled for release in February.
- Richard
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: BUG #19353: Error XX000 if referencing expanded array in grouping set: variable not found in subplan target list
@ 2026-01-05 08:31 Marian Muller Rebeyrol <marian.muller@serli.com>
parent: Richard Guo <guofenglinux@gmail.com>
0 siblings, 0 replies; 9+ messages in thread
From: Marian Muller Rebeyrol @ 2026-01-05 08:31 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; +Cc: Tender Wang <tndrwang@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org
Thanks for the incredibly efficient process of reporting and blazing-fast
fix!
I can also confirm this fixes my actual business use-case.
--
Marian Muller Rebeyrol
SERLI
On Thu, Dec 25, 2025 at 4:58 AM Richard Guo <guofenglinux@gmail.com> wrote:
> On Wed, Dec 24, 2025 at 11:40 AM Tender Wang <tndrwang@gmail.com> wrote:
> > Richard Guo <guofenglinux@gmail.com> 于2025年12月24日周三 10:15写道：
> >> The patch also introduces a new extern function that calls the
> >> workhorse with the grouping flag enabled. Additionally, it adds two
> >> new fields to struct split_pathtarget_context, but that struct is
> >> defined entirely within the .c file.
>
> > It is safe if the struct is only used in a .c file. It would be better
> to add a note in the comment for the struct to notice.
>
> Yeah, a struct defined in .c file should be safe to modify. My main
> concern was adding a new extern function to back-branches, as it can
> break forward compatibility (like an extension built on 18.2 not
> working on 18.1).
>
> After a quick discussion on Discord, I believe it is safe to proceed.
> Forward compatibility issues can only happen if a new extern function
> is added to core and meanwhile called by an extension, which is quite
> rare.
>
> Thus, I have pushed and back-patched this fix.
>
> Thank you, Marian, for the report. It was a good find. The fix will
> be available in 18.2, which is scheduled for release in February.
>
> - Richard
>
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2026-01-05 08:31 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-12-12 10:11 BUG #19353: Error XX000 if referencing expanded array in grouping set: variable not found in subplan target list PG Bug reporting form <noreply@postgresql.org>
2025-12-12 16:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2025-12-13 10:01 ` Tender Wang <tndrwang@gmail.com>
2025-12-15 14:28 ` Richard Guo <guofenglinux@gmail.com>
2025-12-16 11:30 ` Tender Wang <tndrwang@gmail.com>
2025-12-24 02:15 ` Richard Guo <guofenglinux@gmail.com>
2025-12-24 02:40 ` Tender Wang <tndrwang@gmail.com>
2025-12-25 03:58 ` Richard Guo <guofenglinux@gmail.com>
2026-01-05 08:31 ` Marian Muller Rebeyrol <marian.muller@serli.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