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
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index 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;
}