pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Matheus Alcantara <matheusssilv97@gmail.com>
To: Andrey Rachitskiy <pl0h0yp1@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: mertkul669@gmail.com
Cc: pgsql-bugs@lists.postgresql.org
Cc: Richard Guo <guofenglinux@gmail.com>
Subject: Re: BUG #19752: GROUP BY on a constant and a cast to a length-limited array type crashes the server while planning
Date: Fri, 23 Oct 2026 12:34:28 -0300
Message-ID: <DMCBT3XQBZFK.2AR8PB94AGT40@gmail.com> (raw)
In-Reply-To: <CAB8bMiv2JLSxs1aYF42G4887y5LdY=1-E8FLj_wGmKFooqWocQ@mail.gmail.com>
References: <19752-928339cd23b58a33@postgresql.org>
	<2530849.1791487908@sss.pgh.pa.us>
	<CAB8bMiuw5ytFDC9eXANQBCybdUWVX5vWYZ6hVHUZbcqo4kHsuw@mail.gmail.com>
	<2533535.1791490239@sss.pgh.pa.us>
	<CAB8bMiv2JLSxs1aYF42G4887y5LdY=1-E8FLj_wGmKFooqWocQ@mail.gmail.com>

On Thu Oct 8, 2026 at 6:21 PM -03, Andrey Rachitskiy wrote:
> пт, 9 окт. 2026 г. в 01:10, Tom Lane <tgl@sss.pgh.pa.us>:
>> > Skipping every GROUP BY item that contains no level-zero Var is too
>> > broad.  The item in that query is a Const, so it contains no Var, and
>> > the commit exists to keep it a candidate.  The constants that must not
>> > be replaced are the other Const nodes that happen to compare equal.
>>
>> We might need to special-case TLEs that are identified this way.
>> The basic problem is how do you tell which Consts are okay to replace,
>> and what I'm saying is "none of them, except the identified TLE".
>>
>>
> Agreed.
>
> None of the Consts should be replaced, except the targetlist
> entry that GROUP BY identified.
>
> That entry already carries a ressortgroupref.  When its expression is a
> Const or Param, substitute_grouped_columns_mutator() replaces the entry
> as a whole.  The match is on ressortgroupref, because groupClauses may
> be a copy after join alias flattening.  These items are skipped in the
> equal() loop, so the same value elsewhere stays a constant.  Param
> takes the same path as Const, since the existing code accepts the two
> together.
>
> select 1 as one group by rollup(one) order by one nulls first still
> returns a null row and then 1.
> select 1 as one, 1+2 as three group by rollup(one) leaves three as 3.
>
> The reported query no longer crashes.
>
> GROUP BY true identified the boolean constant, and the true inside
> ArrayCoerceExpr.elemexpr was a different Const.
>
> Patch attached.

Hi,

Thanks for working on this!

I've reviewed the attached patch and I have a few comments:

1. It seems to me that the test group by cast(500 as int) exercises
nothing. This test pass on master and on 18. I think that it's because
the typmod for varchar(500) is 504, so changing to CAST(504 as int)
trigger the issue.

2. Three of the new tests don't use grouping sets, so I'm wondering if
aggregates.sql may be a better home for them.

3. I think that the check on substitute_grouped_columns_mutator can be
written as:

if (context->sublevels_up == 0 &&
    context->have_non_var_grouping &&
    IsA(node, TargetEntry) &&
    ((TargetEntry *) node)->ressortgroupref != 0)
{
    TargetEntry *tle = (TargetEntry *) node;
    int          attnum = 0;

    foreach(gl, context->groupClauses)
    {
        TargetEntry *grouptle = (TargetEntry *) lfirst(gl);

        attnum++;
        if (tle->ressortgroupref == grouptle->ressortgroupref &&
            (IsA(grouptle->expr, Const) || IsA(grouptle->expr, Param)))
        ...

Because most targetlist entries aren't GROUP BY, ORDER BY or DISTINCT
keys, so their ressortgroupref is 0. For those, the loop can never
match, but it still walks the whole groupClauses list. The
grouptle->ressortgroupref != 0 test inside the loop seems also redundant
since an item in groupClauses always has a nonzero ressortgroupref,
because that's how it was looked up. What it guards against is
tle->ressortgroupref being 0, and that can be checked once, outside the
loop.

4. I think that the patch only covers part of what Tom suggested. IIUC
his first suggestion was that GROUP BY items should not be candidates
for the search-and-replace unless they contain at least one level-zero
Var, and then "none of them, except the identified TLE". The patch
applies this only to plain Const and Param nodes, so other Var-free
items like 1+2, now(), an uncorrelated sublink or an outer reference
still go through the equal() matching. For example, with:

select 1+2 as three, (1+2)*10 as x group by rollup(three);

the 1+2 inside x is still replaced by a nullable grouped Var on the
parse tree. The result is correct today only because the planner happens
to not null it, which IIUC is the "it does not, right now" case that Tom
mentioned.

The crash itself can't come back through these items, since elemexpr
only contains CaseTestExpr and Consts, but it seems to me that the rule
should be based on what the item contains rather than on its node type.

Based on this I'm attaching a v2 that implements Tom's suggestion. It's
based on the same idea of matching the identified TLE by ressortgroupref
from your patch. The main changes are:

- parseCheckAggregates() records in a Bitmapset which GROUP BY items
  contain no Vars of the current query level, using
  contain_vars_of_level(expr, 0).

- For those items, only the targetlist entry that GROUP BY identified
  is replaced as a whole, and they are skipped in the equal() loop at
  every sublevel.

- Since a Const or Param can no longer match any grouping item, the
  early return for them is moved back to the top of the mutator, as it
  was before f5050f795ae.

--
Matheus Alcantara
EDB: https://www.enterprisedb.com

Attachments:

v2-0001-Don-t-replace-Var-free-subexpressions-with-groupe.patch (text/plain, 11.2K) download From a612a22486486eed0511de5fb72762596d3d6209 Mon Sep 17 00:00:00 2001 From: Matheus Alcantara <mths.dev@pm.me> Date: Fri, 23 Oct 2026 12:16:50 -0300 Subject: [PATCH v2] Don't replace Var-free subexpressions with grouped Vars Since f5050f795ae, substitute_grouped_columns_mutator() checks every subexpression against the GROUP BY items with equal() before accepting Consts and Params, so that a constant grouping item is marked nullable by grouping sets. But that also replaces any other Const that merely happens to be equal to a constant grouping item. Such a Const may be in a place where Vars are not allowed, such as the elemexpr of an ArrayCoerceExpr, where the isExplicit flag of a length-coercion function is a boolean Const. For example: SELECT ARRAY[]::varchar(500)[] GROUP BY CAST('true' AS boolean); crashed the planner, because constant-folding evaluated a Var without a tuple slot. A grouping expression that contains no Vars of the current query level is not a reference to any column of the input, so an equal expression elsewhere in the query is not a reference to that grouping column. Treat such grouping expressions as referring only to the targetlist entry that GROUP BY identified: replace that entry as a whole, and leave them out of the equal() matching altogether. This covers constants, Params, outer references and Var-free expressions such as 1+2. With that, a Const or Param node can never match a grouping item, so accept them at the top of the mutator again, as before f5050f795ae. Author: Andrey Rachitskiy <pl0h0yp1@gmail.com> Co-authored-by: Matheus Alcantara <matheusssilv97@gmail.com> --- src/backend/parser/parse_agg.c | 74 +++++++++++++++++++--- src/test/regress/expected/groupingsets.out | 56 ++++++++++++++++ src/test/regress/sql/groupingsets.sql | 20 ++++++ 3 files changed, 140 insertions(+), 10 deletions(-) diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c index a5424c9d24c..af54d6d576a 100644 --- a/src/backend/parser/parse_agg.c +++ b/src/backend/parser/parse_agg.c @@ -53,6 +53,8 @@ typedef struct List *groupClauseSubLevels; /* list of lists of TargetEntry */ List *gset_common; /* integer list of sortgrouprefs */ bool have_non_var_grouping; + Bitmapset *varfree_grouping; /* groupClauses items with no level-zero + * Vars, by position */ List **func_grouped_rels; int sublevels_up; bool in_agg_direct_args; @@ -69,6 +71,7 @@ static Node *substitute_grouped_columns(Node *node, ParseState *pstate, Query *q List *groupClauses, List *groupClauseCommonVars, List *gset_common, bool have_non_var_grouping, + Bitmapset *varfree_grouping, List **func_grouped_rels); static Node *substitute_grouped_columns_mutator(Node *node, substitute_grouped_columns_context *context); @@ -1142,8 +1145,10 @@ parseCheckAggregates(ParseState *pstate, Query *qry) List *groupClauses = NIL; List *groupClauseCommonVars = NIL; bool have_non_var_grouping; + Bitmapset *varfree_grouping = NULL; List *func_grouped_rels = NIL; ListCell *l; + int attnum; bool hasJoinRTEs; bool hasSelfRefRTEs; Node *clause; @@ -1267,12 +1272,22 @@ parseCheckAggregates(ParseState *pstate, Query *qry) * Track Vars that are included in all grouping sets separately in * groupClauseCommonVars, since these are the only ones we can use to * check for functional dependencies. + * + * Also remember which grouping expressions contain no Vars of this query + * level (constants, Params, outer references and the like). Those can + * only be referenced through the targetlist entry that GROUP BY + * identified; see substitute_grouped_columns_mutator. */ have_non_var_grouping = false; + attnum = 0; foreach(l, groupClauses) { TargetEntry *tle = lfirst(l); + attnum++; + if (!contain_vars_of_level((Node *) tle->expr, 0)) + varfree_grouping = bms_add_member(varfree_grouping, attnum); + if (!IsA(tle->expr, Var)) { have_non_var_grouping = true; @@ -1308,6 +1323,7 @@ parseCheckAggregates(ParseState *pstate, Query *qry) groupClauses, groupClauseCommonVars, gset_common, have_non_var_grouping, + varfree_grouping, &func_grouped_rels); clause = (Node *) qry->havingQual; @@ -1321,6 +1337,7 @@ parseCheckAggregates(ParseState *pstate, Query *qry) groupClauses, groupClauseCommonVars, gset_common, have_non_var_grouping, + varfree_grouping, &func_grouped_rels); /* @@ -1351,6 +1368,7 @@ substitute_grouped_columns(Node *node, ParseState *pstate, Query *qry, List *groupClauses, List *groupClauseCommonVars, List *gset_common, bool have_non_var_grouping, + Bitmapset *varfree_grouping, List **func_grouped_rels) { substitute_grouped_columns_context context; @@ -1363,6 +1381,7 @@ substitute_grouped_columns(Node *node, ParseState *pstate, Query *qry, context.groupClauseSubLevels = NIL; context.gset_common = gset_common; context.have_non_var_grouping = have_non_var_grouping; + context.varfree_grouping = varfree_grouping; context.func_grouped_rels = func_grouped_rels; context.sublevels_up = 0; context.in_agg_direct_args = false; @@ -1377,6 +1396,9 @@ substitute_grouped_columns_mutator(Node *node, if (node == NULL) return NULL; + if (IsA(node, Const) || + IsA(node, Param)) + return node; /* constants are always acceptable */ if (IsA(node, Aggref)) { @@ -1424,6 +1446,42 @@ substitute_grouped_columns_mutator(Node *node, return node; } + /* + * A grouping expression that contains no Vars of this query level, such + * as a constant, refers only to the targetlist entry that GROUP BY + * identified. Other occurrences of an equal expression are not + * references to that grouping column, so they are not candidates for the + * equal() matching below. Replacing them would be wrong anyway: they can + * appear in places where Vars are not allowed, such as the elemexpr of an + * ArrayCoerceExpr. + */ + if (IsA(node, TargetEntry) && + ((TargetEntry *) node)->ressortgroupref != 0 && + context->have_non_var_grouping && + context->sublevels_up == 0) + { + TargetEntry *tle = (TargetEntry *) node; + int attnum = 0; + + foreach(gl, context->groupClauses) + { + TargetEntry *grouptle = (TargetEntry *) lfirst(gl); + + attnum++; + if (grouptle->ressortgroupref != tle->ressortgroupref) + continue; + if (bms_is_member(attnum, context->varfree_grouping)) + { + tle = flatCopyTargetEntry(tle); + tle->expr = (Expr *) buildGroupedVar(attnum, + tle->ressortgroupref, + context); + return (Node *) tle; + } + break; + } + } + /* * If we have any GROUP BY items that are not simple Vars, check to see if * subexpression as a whole matches any GROUP BY item. We need to do this @@ -1448,6 +1506,11 @@ substitute_grouped_columns_mutator(Node *node, TargetEntry *tle = (TargetEntry *) lfirst(gl); attnum++; + + /* Var-free items are matched only by targetlist entry, above */ + if (bms_is_member(attnum, context->varfree_grouping)) + continue; + if (equal(node, tle->expr)) { /* acceptable, replace it with a GROUP Var */ @@ -1458,16 +1521,6 @@ substitute_grouped_columns_mutator(Node *node, } } - /* - * Constants are always acceptable. We have to do this after we checked - * the subexpression as a whole for a match, because it is possible that - * we have GROUP BY items that are constants, and the constants would - * become not so constant after the grouping step. - */ - if (IsA(node, Const) || - IsA(node, Param)) - return node; - /* * If we have an ungrouped Var of the original query level, we have a * failure. Vars below the original query level are not a problem, and @@ -1624,6 +1677,7 @@ finalize_grouping_exprs(Node *node, ParseState *pstate, Query *qry, context.groupClauseSubLevels = NIL; context.gset_common = NIL; context.have_non_var_grouping = have_non_var_grouping; + context.varfree_grouping = NULL; context.func_grouped_rels = NULL; context.sublevels_up = 0; context.in_agg_direct_args = false; diff --git a/src/test/regress/expected/groupingsets.out b/src/test/regress/expected/groupingsets.out index 08d5ce3156d..7725dec0a59 100644 --- a/src/test/regress/expected/groupingsets.out +++ b/src/test/regress/expected/groupingsets.out @@ -2667,6 +2667,62 @@ select 1 as one group by rollup(one) order by one nulls first; 1 (2 rows) +-- A grouping expression containing no Vars of the query level refers only +-- to the targetlist entry GROUP BY identified; equal expressions elsewhere +-- must not be replaced by grouped Vars (this used to crash) +select array[]::varchar(500)[] as a group by cast('true' as boolean); + a +---- + {} +(1 row) + +select array[]::varchar(500)[] as a group by 504::int4; + a +---- + {} +(1 row) + +select (select array[]::varchar(500)[]) as a +group by rollup(cast('true' as boolean)); + a +---- + {} + {} +(2 rows) + +explain (verbose, costs off) +select 1 as a, 1 as b, 1+2 as c, (1+2)*10 as d +group by rollup(a, c); + QUERY PLAN +--------------------------- + MixedAggregate + Output: (1), 1, (3), 30 + Hash Key: 1, 3 + Hash Key: 1 + Group Key: () + -> Result + Output: 1, 3 +(7 rows) + +select 1 as a, 1 as b, 1+2 as c, (1+2)*10 as d +group by rollup(a, c) +order by a nulls first, c nulls first; + a | b | c | d +---+---+---+---- + | 1 | | 30 + 1 | 1 | | 30 + 1 | 1 | 3 | 30 +(3 rows) + +-- likewise for an outer reference used as a grouping expression +select array(select o.a as k from (values (1)) as i (x) + group by rollup(o.a, x+0) order by 1 nulls first) +from (values (10)) as o (a); + array +-------------- + {NULL,10,10} +(1 row) + explain (costs off) 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 diff --git a/src/test/regress/sql/groupingsets.sql b/src/test/regress/sql/groupingsets.sql index c5d4ed2eb57..22b8c517feb 100644 --- a/src/test/regress/sql/groupingsets.sql +++ b/src/test/regress/sql/groupingsets.sql @@ -771,6 +771,26 @@ explain (costs off) select 1 as one group by rollup(one) order by one nulls first; select 1 as one group by rollup(one) order by one nulls first; +-- A grouping expression containing no Vars of the query level refers only +-- to the targetlist entry GROUP BY identified; equal expressions elsewhere +-- must not be replaced by grouped Vars (this used to crash) +select array[]::varchar(500)[] as a group by cast('true' as boolean); +select array[]::varchar(500)[] as a group by 504::int4; +select (select array[]::varchar(500)[]) as a +group by rollup(cast('true' as boolean)); + +explain (verbose, costs off) +select 1 as a, 1 as b, 1+2 as c, (1+2)*10 as d +group by rollup(a, c); +select 1 as a, 1 as b, 1+2 as c, (1+2)*10 as d +group by rollup(a, c) +order by a nulls first, c nulls first; + +-- likewise for an outer reference used as a grouping expression +select array(select o.a as k from (values (1)) as i (x) + group by rollup(o.a, x+0) order by 1 nulls first) +from (values (10)) as o (a); + explain (costs off) 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 -- 2.50.1 (Apple Git-155)

view thread (11+ messages)  latest in thread

Message-ID: <DMCBT3XQBZFK.2AR8PB94AGT40@gmail.com>
Permalink:  ../DMCBT3XQBZFK.2AR8PB94AGT40@gmail.com/
Also on:    postgresql.org/message-id/DMCBT3XQBZFK.2AR8PB94AGT40@gmail.com

 ·  · 

reply

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: matheusssilv97@gmail.com, pl0h0yp1@gmail.com, tgl@sss.pgh.pa.us, mertkul669@gmail.com, guofenglinux@gmail.com
  Subject: Re: BUG #19752: GROUP BY on a constant and a cast to a length-limited array type crashes the server while planning
  In-Reply-To: <DMCBT3XQBZFK.2AR8PB94AGT40@gmail.com>
  References: <19752-928339cd23b58a33@postgresql.org> <2530849.1791487908@sss.pgh.pa.us> <CAB8bMiuw5ytFDC9eXANQBCybdUWVX5vWYZ6hVHUZbcqo4kHsuw@mail.gmail.com> <2533535.1791490239@sss.pgh.pa.us> <CAB8bMiv2JLSxs1aYF42G4887y5LdY=1-E8FLj_wGmKFooqWocQ@mail.gmail.com> <DMCBT3XQBZFK.2AR8PB94AGT40@gmail.com>

* 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