nocfbot-0006-Prevent-RPR-window-removal-in-subquery.txt

From 2d48907d52e3af8738b09df69d3c016b6224d619 Mon Sep 17 00:00:00 2001
From: Henson Choi <assam258@gmail.com>
Date: Thu, 2 Apr 2026 11:55:02 +0900
Subject: [PATCH 6/8] Prevent removal of RPR window functions in unused
 subquery outputs

remove_unused_subquery_outputs() replaces unused subquery target
entries with NULL constants. When an RPR window function's result
is not referenced by the outer query, this replacement eliminates
all active window functions for the WindowClause, causing the
planner to omit the WindowAgg node. DEFINE clause expressions
containing RPRNavExpr (PREV/NEXT) then lose their execution
context, leading to an Assert failure in execExpr.c.

Skip the NULL replacement for window functions whose WindowClause
has a defineClause, so the WindowAgg node is preserved and RPR
pattern matching executes correctly.
---
 src/backend/optimizer/path/allpaths.c | 28 +++++++++++++++++++++++
 src/test/regress/expected/rpr.out     | 33 +++++++++++++++++++++++++++
 src/test/regress/sql/rpr.sql          | 26 +++++++++++++++++++++
 3 files changed, 87 insertions(+)

diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index f42a2bae14a..9d3af43a72e 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -4750,6 +4750,34 @@ remove_unused_subquery_outputs(Query *subquery, RelOptInfo *rel,
 		if (contain_volatile_functions(texpr))
 			continue;
 
+		/*
+		 * If it's a window function referencing a window clause with RPR (Row
+		 * Pattern Recognition), don't remove it.  Even when the window
+		 * function result is unused by the outer query, the RPR pattern
+		 * matching (frame reduction via DEFINE/PATTERN) must still execute.
+		 * Replacing this with NULL would leave no active window functions for
+		 * the WindowClause, causing the planner to omit the WindowAgg node
+		 * entirely.
+		 */
+		if (IsA(texpr, WindowFunc))
+		{
+			WindowFunc *wfunc = (WindowFunc *) texpr;
+			ListCell   *wlc;
+
+			foreach(wlc, subquery->windowClause)
+			{
+				WindowClause *wc = lfirst_node(WindowClause, wlc);
+
+				if (wc->winref == wfunc->winref &&
+					wc->defineClause != NIL)
+				{
+					break;
+				}
+			}
+			if (wlc != NULL)
+				continue;
+		}
+
 		/*
 		 * OK, we don't need it.  Replace the expression with a NULL constant.
 		 * Preserve the exposed type of the expression, in case something
diff --git a/src/test/regress/expected/rpr.out b/src/test/regress/expected/rpr.out
index e72171050c7..54a6857bdb8 100644
--- a/src/test/regress/expected/rpr.out
+++ b/src/test/regress/expected/rpr.out
@@ -1604,6 +1604,39 @@ SELECT match_first, match_last, match_len FROM result WHERE match_len > 0;
            0 |      99998 |     99999
 (1 row)
 
+--
+-- Subquery wrapping: RPR window inside outer aggregate.
+-- Tests that WindowAgg is not removed by remove_unused_subquery_outputs()
+-- when DEFINE clause contains PREV/NEXT.
+--
+-- PREV in DEFINE + outer aggregate
+SELECT count(*) FROM (
+  SELECT count(*) OVER w FROM generate_series(1,10) i
+  WINDOW w AS (
+    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+    PATTERN (A+)
+    DEFINE A AS i > PREV(i)
+  )
+) t;
+ count 
+-------
+    10
+(1 row)
+
+-- DEFINE without PREV + outer aggregate (WindowAgg must still be preserved)
+SELECT count(*), sum(c) FROM (
+  SELECT count(*) OVER w AS c FROM generate_series(1,10) i
+  WINDOW w AS (
+    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+    PATTERN (A+)
+    DEFINE A AS TRUE
+  )
+) t;
+ count | sum 
+-------+-----
+    10 |  10
+(1 row)
+
 --
 -- IGNORE NULLS
 --
diff --git a/src/test/regress/sql/rpr.sql b/src/test/regress/sql/rpr.sql
index 95794d409e1..996135634fd 100644
--- a/src/test/regress/sql/rpr.sql
+++ b/src/test/regress/sql/rpr.sql
@@ -764,6 +764,32 @@ result AS (
 -- Should match: A (33333 rows) + B (33333 rows) + C (33333 rows) = 99999 rows
 SELECT match_first, match_last, match_len FROM result WHERE match_len > 0;
 
+--
+-- Subquery wrapping: RPR window inside outer aggregate.
+-- Tests that WindowAgg is not removed by remove_unused_subquery_outputs()
+-- when DEFINE clause contains PREV/NEXT.
+--
+
+-- PREV in DEFINE + outer aggregate
+SELECT count(*) FROM (
+  SELECT count(*) OVER w FROM generate_series(1,10) i
+  WINDOW w AS (
+    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+    PATTERN (A+)
+    DEFINE A AS i > PREV(i)
+  )
+) t;
+
+-- DEFINE without PREV + outer aggregate (WindowAgg must still be preserved)
+SELECT count(*), sum(c) FROM (
+  SELECT count(*) OVER w AS c FROM generate_series(1,10) i
+  WINDOW w AS (
+    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+    PATTERN (A+)
+    DEFINE A AS TRUE
+  )
+) t;
+
 --
 -- IGNORE NULLS
 --
-- 
2.50.1 (Apple Git-155)