BUG #19752: GROUP BY on a constant and a cast to a length-limited array type crashes the server while planning
BUG #19752: GROUP BY on a constant and a cast to a length-limited array type crashes the server while planning
От:
PG Bug reporting form <noreply@postgresql.org>
Дата:
The following bug has been logged on the website:
Bug reference: 19752
Logged by: Mert Kul
Email address: mertkul669@gmail.com
PostgreSQL version: 18.4
Operating system: Ubuntu
Description:
Hi,
I found a planner crash (SIGSEGV) in PostgreSQL 18 with the following
query:
SELECT ARRAY[]::varchar(500)[]
GROUP BY CAST('true' AS boolean);
Tested on 18.6 (REL_18_STABLE at 1370a7832a). The query works on 17.11.
No tables or data are required, and EXPLAIN alone is enough to reproduce
the crash, so the executor proper is never reached. The planner does,
however, call executor expression-evaluation code while constant-folding
the expression.
I first hit the crash with a LEFT JOIN query, but the FROM clause and the
join are not required.
Reproduction (unpatched 18.6)
ARRAY[]::varchar(500)[] GROUP BY CAST('true' AS boolean) crash
ARRAY[]::char(1)[] GROUP BY CAST('true' AS boolean) crash
ARRAY[]::varchar(500)[] without GROUP BY ok
ARRAY[]::varchar[] GROUP BY ... (no typmod) ok
ARRAY[]::text[] GROUP BY ... ok
ARRAY['a']::varchar(500)[] GROUP BY ... (non-empty array) ok
The crash needs both a constant GROUP BY expression and an array whose
element type requires length coercion, such as varchar(n) or char(n).
Backtrace (18.6, assert-enabled, -O0)
#0 slot_getsomeattrs (slot=0x0, attnum=1) at tuptable.h:361
#1 ExecInterpExpr (...) at execExprInterp.c:664
#2 ExecInterpExprStillValid (...) at execExprInterp.c:2307
#3 ExecEvalExprSwitchContext (...) at executor.h:440
#4 evaluate_expr (expr=..., result_type=1015, result_typmod=504,
result_collation=100) at clauses.c:5082
#5 eval_const_expressions_mutator (node=, ...)
at clauses.c:3155
#6 expression_tree_mutator_impl (...)
#7 eval_const_expressions_mutator (...) at clauses.c:3797
#8 expression_tree_mutator_impl (...)
#9 eval_const_expressions_mutator (...) at clauses.c:3797
#10 eval_const_expressions (...) at clauses.c:2295
#11 preprocess_expression (..., kind=1) at planner.c:1348
#12 subquery_planner (...) at planner.c:919
Cause
For ARRAY[]::varchar(500)[], the parser builds an ArrayCoerceExpr whose
elemexpr is a per-element template:
varchar(CaseTestExpr, 500, true)
where the final "true" is the internal isExplicit flag.
substitute_grouped_columns_mutator() in parse_agg.c replaces any
subexpression that equal()s a GROUP BY expression with a Var referencing
the RTE_GROUP RTE. It also descends into elemexpr. The GROUP BY
expression is Const(bool, true), and the internal isExplicit flag is also
Const(bool, true), so the flag is wrongly replaced by a Var. (Dumping
elemexpr at the point of failure on 18.4 shows the third argument is a
VAR rather than a CONST.)
eval_const_expressions_mutator() then sees an ArrayCoerceExpr with a
Const argument and no volatile functions in elemexpr, and folds it
through evaluate_expr(). ExecInitExprRec() assumes elemexpr contains no
Var, but the substitution above violates that assumption. The Var is
evaluated without a tuple slot, which dereferences NULL.
Why 17 is not affected
In 17, check_ungrouped_columns_walker() performs the same equal() check,
but a match only stops the descent; the expression tree is not rewritten.
Commit 247dea89f ("Introduce an RTE for the grouping step") made a match
rewrite the tree, without skipping these template fields.
The ArrayCoerceExpr folding code in clauses.c is identical in 17 and 18,
so the relevant change is on the parser side, in parse_agg.c.
Thanks,
Mert Kul
Re: BUG #19752: GROUP BY on a constant and a cast to a length-limited array type crashes the server while planning
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
PG Bug reporting form writes:
> I found a planner crash (SIGSEGV) in PostgreSQL 18 with the following
> query:
> SELECT ARRAY[]::varchar(500)[]
> GROUP BY CAST('true' AS boolean);
> Cause
> For ARRAY[]::varchar(500)[], the parser builds an ArrayCoerceExpr whose
> elemexpr is a per-element template:
> varchar(CaseTestExpr, 500, true)
> where the final "true" is the internal isExplicit flag.
> substitute_grouped_columns_mutator() in parse_agg.c replaces any
> subexpression that equal()s a GROUP BY expression with a Var referencing
> the RTE_GROUP RTE. It also descends into elemexpr. The GROUP BY
> expression is Const(bool, true), and the internal isExplicit flag is also
> Const(bool, true), so the flag is wrongly replaced by a Var. (Dumping
> elemexpr at the point of failure on 18.4 shows the third argument is a
> VAR rather than a CONST.)
Yeah, that's nasty, and what's nastier is that even non-crash cases
might result in seriously wrong answers. We shouldn't be replacing
constant subexpressions by group Vars ever. I'm inclined to think
that GROUP BY items should not be candidates for this
search-and-replace unless they contain at least one level-zero Var.
But maybe that's too simplistic?
regards, tom lane
Re: BUG #19752: GROUP BY on a constant and a cast to a length-limited array type crashes the server while planning
От:
Andrey Rachitskiy <pl0h0yp1@gmail.com>
Дата:
пт, 9 окт. 2026 г. в 00:32, Tom Lane <tgl@sss.pgh.pa.us>:
PG Bug reporting form <noreply@postgresql.org> writes:
> I found a planner crash (SIGSEGV) in PostgreSQL 18 with the following
> query:
> SELECT ARRAY[]::varchar(500)[]
> GROUP BY CAST('true' AS boolean);
> Cause
> For ARRAY[]::varchar(500)[], the parser builds an ArrayCoerceExpr whose
> elemexpr is a per-element template:
> varchar(CaseTestExpr, 500, true)
> where the final "true" is the internal isExplicit flag.
> substitute_grouped_columns_mutator() in parse_agg.c replaces any
> subexpression that equal()s a GROUP BY expression with a Var referencing
> the RTE_GROUP RTE. It also descends into elemexpr. The GROUP BY
> expression is Const(bool, true), and the internal isExplicit flag is also
> Const(bool, true), so the flag is wrongly replaced by a Var. (Dumping
> elemexpr at the point of failure on 18.4 shows the third argument is a
> VAR rather than a CONST.)
Yeah, that's nasty, and what's nastier is that even non-crash cases
might result in seriously wrong answers. We shouldn't be replacing
constant subexpressions by group Vars ever. I'm inclined to think
that GROUP BY items should not be candidates for this
search-and-replace unless they contain at least one level-zero Var.
But maybe that's too simplistic?
regards, tom lane
Hi, Tom!
If I analyzed the code correctly, the conclusions are as follows:
We should still replace the grouped constant itself. Bisect puts the
crash at f5050f795ae ("Mark expressions nullable by grouping sets"),
whose parent 247dea89f76 does not crash. 247dea89f76 returns on Const
before equal(). f5050f795ae moves that return to after equal(), and
the comment says why. A GROUP BY constant is no longer constant after
the grouping step. The regress query added in that commit is
select 1 as one group by rollup(one) order by one nulls first;
and the expected result is a null row followed by 1.
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.
Regards,
Rachitskiy Andrey
Rachitskiy Andrey
Re: BUG #19752: GROUP BY on a constant and a cast to a length-limited array type crashes the server while planning
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Andrey Rachitskiy writes:
> пт, 9 окт. 2026 г. в 00:32, Tom Lane :
>> Yeah, that's nasty, and what's nastier is that even non-crash cases
>> might result in seriously wrong answers. We shouldn't be replacing
>> constant subexpressions by group Vars ever. I'm inclined to think
>> that GROUP BY items should not be candidates for this
>> search-and-replace unless they contain at least one level-zero Var.
>> But maybe that's too simplistic?
> We should still replace the grouped constant itself. Bisect puts the
> crash at f5050f795ae ("Mark expressions nullable by grouping sets"),
> whose parent 247dea89f76 does not crash. 247dea89f76 returns on Const
> before equal(). f5050f795ae moves that return to after equal(), and
> the comment says why. A GROUP BY constant is no longer constant after
> the grouping step. The regress query added in that commit is
> select 1 as one group by rollup(one) order by one nulls first;
I think that's different: the "group by one" should identify the
first TLE and nothing else. Otherwise we have insanities like
select 1 as one, 1+2 as three group by rollup(one)
affecting both columns. (I see that it does not, right now,
but it's hard to argue why not if you think replacing constants
is okay.)
> 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".
regards, tom lane
Re: BUG #19752: GROUP BY on a constant and a cast to a length-limited array type crashes the server while planning
пт, 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 entryas 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.
ArrayCoerceExpr.elemexpr was a different Const.
Patch attached.
Regards,
Rachitskiy Andrey
Rachitskiy Andrey
Re: BUG #19752: GROUP BY on a constant and a cast to a length-limited array type crashes the server while planning
От:
Andrey Rachitskiy <pl0h0yp1@gmail.com>
Дата:
пн, 12 окт. 2026 г. в 22:48, Mert Kul <mertkul669@gmail.com>:
Thanks for working on this!
Is this patch considered the definitive fix for the underlying issue, or is it more of a temporary workaround?
In particular, do you think matching grouping expressions byressortgroupreffully addresses the problem,
including the potential for incorrect query results, or are further changes needed?
Hi, Mert!
We need Tom to look at my solution, he might not like it :). Although I think I took his wishes into account.
Regards,
Rachitskiy Andrey
Rachitskiy Andrey
Re: BUG #19752: GROUP BY on a constant and a cast to a length-limited array type crashes the server while planning
От:
Andrey Rachitskiy <pl0h0yp1@gmail.com>
Дата:
пн, 12 окт. 2026 г., 22:54 Andrey Rachitskiy <pl0h0yp1@gmail.com>:
пн, 12 окт. 2026 г. в 22:48, Mert Kul <mertkul669@gmail.com>:Thanks for working on this!
Is this patch considered the definitive fix for the underlying issue, or is it more of a temporary workaround?
In particular, do you think matching grouping expressions byressortgroupreffully addresses the problem,
including the potential for incorrect query results, or are further changes needed?Hi, Mert!We need Tom to look at my solution, he might not like it :). Although I think I took his wishes into account.
Hi, Tom!
Could you take a look at the patch and check whether I've chosen the right direction for the fix?
Thanks in advance for your time.
---
Regards,
Rachitskiy Andrey
Re: BUG #19752: GROUP BY on a constant and a cast to a length-limited array type crashes the server while planning
On Thu Oct 8, 2026 at 6:21 PM -03, Andrey Rachitskiy wrote:
> пт, 9 окт. 2026 г. в 01:10, Tom Lane :
>> > 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
Re: BUG #19752: GROUP BY on a constant and a cast to a length-limited array type crashes the server while planning
пт, 23 окт. 2026 г. в 20:34, Matheus Alcantara <matheusssilv97@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
Hi Matheus,
Thanks for the review and the v2. You understood Tom better than I did. v3 keeps the same code.
Thanks for the review and the v2. You understood Tom better than I did. v3 keeps the same code.
The queries that do not use grouping sets moved to aggregates.sql.
Regards,
Rachitskiy Andrey
Rachitskiy Andrey
Re: BUG #19752: GROUP BY on a constant and a cast to a length-limited array type crashes the server while planning
От:
Matheus Alcantara <matheusssilv97@gmail.com>
Дата:
On 26/10/26 06:16, Andrey Rachitskiy wrote: > Thanks for the review and the v2. You understood Tom better than I did. v3 > keeps the same code. > The queries that do not use grouping sets moved to aggregates.sql. > Thanks for the new version. I've created a CF entry for this bug: https://commitfest.postgresql.org/patch/7545/ -- Matheus Alcantara EDB: https://www.enterprisedb.com
Re: BUG #19752: GROUP BY on a constant and a cast to a length-limited array type crashes the server while planning
ср, 28 окт. 2026 г. в 17:26, Matheus Alcantara <matheusssilv97@gmail.com>:
Thanks for the new version.
I've created a CF entry for this bug:
https://commitfest.postgresql.org/patch/7545/
Thanks!
Regards,
Rachitskiy Andrey
Rachitskiy Andrey