From: Tom Lane <tgl@sss.pgh.pa.us>
To: dllggyx@outlook.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19055: Server crash at ExecInterpExpr
Date: Wed, 17 Sep 2025 14:02:09 -0400
Message-ID: <1652186.1758132129@sss.pgh.pa.us> (raw)
In-Reply-To: <19055-6970cfa8556a394d@postgresql.org>
References: <19055-6970cfa8556a394d@postgresql.org>
PG Bug reporting form <noreply@postgresql.org> writes:
> PoC:
> SELECT FROM ( SELECT generate_series ( 1 , '31' ) x ) GROUP BY ( x ) WINDOW
> w AS ( ORDER BY ( WITH x AS ( WITH x AS ( SELECT sum ( x ) ) SELECT DISTINCT
> * FROM x ) ( SELECT ( count ( ( SELECT x FROM x ) ) ) ) ) )
Interesting example. De-obfuscating a little bit, we have
SELECT 1 FROM ( SELECT generate_series ( 1 , '31' ) gs ) ss
GROUP BY ( gs )
WINDOW w AS ( ORDER BY (
WITH x1 AS -- MATERIALIZED
( WITH x2 AS ( SELECT sum ( gs ) )
SELECT DISTINCT * FROM x2 )
SELECT ( count ( ( SELECT gs FROM x1 ) ) )
) );
If you stick in MATERIALIZED where I show, then instead of an
executor assertion failure you get
ERROR: could not find CTE "x1"
which is also what happens in branches pre-dating default inlining
of CTEs.
The problem appears to be that the count() aggregate is assigned the
wrong agglevelsup: it's labeled with agglevelsup = 1, implying that
it belongs to the outer query level (which is where its "gs" input
comes from). Then when we try to pull it up to the outer level,
the contained reference to the x1 CTE becomes dangling --- the planner
can't find any x1 in that level. Or, if we don't say MATERIALIZED,
the planner tries to inline x1 and just botches things entirely.
I suspect it's getting confused about which level "sum(gs)"
belongs to, but I didn't bother running down the details.
In any case, this is the parser's fault. Because the count()
references x1, it should not be given an agglevelsup higher than
where x1 is. The attached seems to fix it. I need to think
of a test case with less extraneous crud, though...
Thanks for the report!
regards, tom lane
Attachments:
[text/x-diff] fix-agglevelsup-calculation-with-CTEs.patch (1.5K, ../1652186.1758132129@sss.pgh.pa.us/2-fix-agglevelsup-calculation-with-CTEs.patch)
download | inline diff:diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.cindex 0ac8966e30f..2bef05b4833 100644--- a/src/backend/parser/parse_agg.c+++ b/src/backend/parser/parse_agg.c@@ -791,6 +791,33 @@ check_agg_arguments_walker(Node *node,
parser_errposition(context->pstate,
((WindowFunc *) node)->location)));
}
++ if (IsA(node, RangeTblEntry))+ {+ /*+ * CTE references act as though they were Vars of the CTE's level.+ * Typically this doesn't matter because the sub-select containing the+ * CTE RTE will also contain Vars referencing that RTE; but if it does+ * not, we end with an invalid query tree if we don't do this.+ */+ RangeTblEntry *rte = (RangeTblEntry *) node;++ if (rte->rtekind == RTE_CTE)+ {+ int ctelevelsup = rte->ctelevelsup;++ /* convert levelsup to frame of reference of original query */+ ctelevelsup -= context->sublevels_up;+ /* ignore local CTEs of subqueries */+ if (ctelevelsup >= 0)+ {+ if (context->min_varlevel < 0 ||+ context->min_varlevel > ctelevelsup)+ context->min_varlevel = ctelevelsup;+ }+ }+ return false; /* allow range_table_walker to continue */+ }
if (IsA(node, Query))
{
/* Recurse into subselects */
@@ -800,7 +827,7 @@ check_agg_arguments_walker(Node *node,
result = query_tree_walker((Query *) node,
check_agg_arguments_walker,
context,
- 0);+ QTW_EXAMINE_RTES_BEFORE);
context->sublevels_up--;
return result;
}
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 using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, dllggyx@outlook.com, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19055: Server crash at ExecInterpExpr
In-Reply-To: <1652186.1758132129@sss.pgh.pa.us>
* 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