Calculate agglevelsup correctly when Aggref contains a CTE.

tglsfdc · tglsfdc · commit 4eab456494e3 · 2025-09-17T16:32:57.000-04:00
If an aggregate function call contains a sub-select that has an RTE referencing a CTE outside the aggregate, we must treat that reference like a Var referencing the CTE's query level for purposes of determining the aggregate's level. Otherwise we might reach the nonsensical conclusion that the aggregate should be evaluated at some query level higher than the CTE, ending in a planner error or a broken plan tree that causes executor failures. Bug: #19055 Reported-by: BugForge <dllggyx@outlook.com> Author: Tom Lane <tgl@sss.pgh.pa.us> Discussion: https://postgr.es/m/19055-6970cfa8556a394d@postgresql.org Backpatch-through: 13
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
@@ -791,6 +791,32 @@ check_agg_arguments_walker(Node *node,
 					 parser_errposition(context->pstate,
 										((WindowFunc *) node)->location)));
 	}
+
+	if (IsA(node, RangeTblEntry))
+	{
+		/*
+		 * CTE references act similarly to Vars of the CTE's level.  Without
+		 * this we might conclude that the Agg can be evaluated above the CTE,
+		 * leading to trouble.
+		 */
+		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 +826,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;
 	}
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
@@ -2296,6 +2296,40 @@ from int4_tbl;
  -2147483647
 (5 rows)
 
+--
+-- test for bug #19055: interaction of WITH with aggregates
+--
+-- The reference to cte1 must determine the aggregate's level,
+-- even though it contains no Vars referencing cte1
+explain (verbose, costs off)
+select f1, (with cte1(x,y) as (select 1,2)
+            select count((select i4.f1 from cte1))) as ss
+from int4_tbl i4;
+                 QUERY PLAN                 
+--------------------------------------------
+ Seq Scan on public.int4_tbl i4
+   Output: i4.f1, (SubPlan 2)
+   SubPlan 2
+     ->  Aggregate
+           Output: count((InitPlan 1).col1)
+           InitPlan 1
+             ->  Result
+                   Output: i4.f1
+           ->  Result
+(9 rows)
+
+select f1, (with cte1(x,y) as (select 1,2)
+            select count((select i4.f1 from cte1))) as ss
+from int4_tbl i4;
+     f1      | ss 
+-------------+----
+           0 |  1
+      123456 |  1
+     -123456 |  1
+  2147483647 |  1
+ -2147483647 |  1
+(5 rows)
+
 --
 -- test for nested-recursive-WITH bug
 --
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql
@@ -1097,6 +1097,20 @@ select ( with cte(foo) as ( values(f1) )
           values((select foo from cte)) )
 from int4_tbl;
 
+--
+-- test for bug #19055: interaction of WITH with aggregates
+--
+-- The reference to cte1 must determine the aggregate's level,
+-- even though it contains no Vars referencing cte1
+explain (verbose, costs off)
+select f1, (with cte1(x,y) as (select 1,2)
+            select count((select i4.f1 from cte1))) as ss
+from int4_tbl i4;
+
+select f1, (with cte1(x,y) as (select 1,2)
+            select count((select i4.f1 from cte1))) as ss
+from int4_tbl i4;
+
 --
 -- test for nested-recursive-WITH bug
 --
