v1-fix-CTEs-in-aggregates-some-more.patch

diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index 3254c83cc6c..5de07a2aa9a 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -795,9 +795,16 @@ check_agg_arguments_walker(Node *node,
 	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.
+		 * If we find a nonlocal CTE reference, force the Agg to be given
+		 * level zero, i.e. make its semantic level match its syntactic level.
+		 * Without this we might conclude that the Agg can be evaluated above
+		 * the CTE (if it contains only Vars from above the CTE), leading to
+		 * trouble.  On the other hand, we shouldn't allow a CTE reference by
+		 * itself to promote an Agg to be an outer Agg; that fails for
+		 * cross-references to sibling CTEs.  The best solution seems to be to
+		 * force the Agg to level zero.  (The SQL spec provides no guidance
+		 * here; they disallow subqueries within aggregates, and therefore
+		 * don't reach the question of what to do with CTE references.)
 		 */
 		RangeTblEntry *rte = (RangeTblEntry *) node;
 
@@ -810,9 +817,8 @@ check_agg_arguments_walker(Node *node,
 			/* ignore local CTEs of subqueries */
 			if (ctelevelsup >= 0)
 			{
-				if (context->min_varlevel < 0 ||
-					context->min_varlevel > ctelevelsup)
-					context->min_varlevel = ctelevelsup;
+				/* nonlocal CTE, so force Agg to level zero */
+				context->min_varlevel = 0;
 			}
 		}
 		return false;			/* allow range_table_walker to continue */
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index 86fdb85c6c5..bb45cb83c62 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -2331,6 +2331,34 @@ from int4_tbl i4;
  -2147483647 |  1
 (5 rows)
 
+--
+-- test for bug #19106: interaction of WITH with aggregates
+--
+-- the fix for #19055 was too aggressive and broke this case
+explain (verbose, costs off)
+with a as ( select id from (values (1), (2)) as v(id) ),
+     b as ( select max((select sum(id) from a)) as agg )
+select agg from b;
+                 QUERY PLAN                 
+--------------------------------------------
+ Aggregate
+   Output: max((InitPlan expr_1).col1)
+   InitPlan expr_1
+     ->  Aggregate
+           Output: sum("*VALUES*".column1)
+           ->  Values Scan on "*VALUES*"
+                 Output: "*VALUES*".column1
+   ->  Result
+(8 rows)
+
+with a as ( select id from (values (1), (2)) as v(id) ),
+     b as ( select max((select sum(id) from a)) as agg )
+select agg from b;
+ agg 
+-----
+   3
+(1 row)
+
 --
 -- test for nested-recursive-WITH bug
 --
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql
index d88d5abb91a..e199662edd3 100644
--- a/src/test/regress/sql/with.sql
+++ b/src/test/regress/sql/with.sql
@@ -1111,6 +1111,19 @@ 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 bug #19106: interaction of WITH with aggregates
+--
+-- the fix for #19055 was too aggressive and broke this case
+explain (verbose, costs off)
+with a as ( select id from (values (1), (2)) as v(id) ),
+     b as ( select max((select sum(id) from a)) as agg )
+select agg from b;
+
+with a as ( select id from (values (1), (2)) as v(id) ),
+     b as ( select max((select sum(id) from a)) as agg )
+select agg from b;
+
 --
 -- test for nested-recursive-WITH bug
 --