nocfbot-0006-disable-frame-optimization-for-rpr-windows.txt
text/plain
Filename: nocfbot-0006-disable-frame-optimization-for-rpr-windows.txt
Type: text/plain
Part: 5
Message:
Re: Row pattern recognition
From 08910348e4c3770880fa098b50596805b15e38f7 Mon Sep 17 00:00:00 2001
From: Henson Choi <assam258@gmail.com>
Date: Wed, 4 Mar 2026 15:59:45 +0900
Subject: [PATCH 6/8] Disable frame optimization for RPR windows
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 04faf919033..29a02f3affb 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -5907,6 +5907,14 @@ optimize_window_clauses(PlannerInfo *root, WindowFuncLists *wflists)
if (wflists->windowFuncs[wc->winref] == NIL)
continue;
+ /*
+ * If a DEFINE clause exists, do not let support functions replace the
+ * frame with a non-RPR-compatible one. RPR windows require ROWS
+ * BETWEEN CURRENT ROW AND ...
+ */
+ if (wc->defineClause != NIL)
+ continue;
+
foreach(lc2, wflists->windowFuncs[wc->winref])
{
SupportRequestOptimizeWindowClause req;
diff --git a/src/test/regress/expected/rpr_explain.out b/src/test/regress/expected/rpr_explain.out
index 3c70a12874a..ef184b7950b 100644
--- a/src/test/regress/expected/rpr_explain.out
+++ b/src/test/regress/expected/rpr_explain.out
@@ -3821,3 +3821,117 @@ WINDOW w AS (
-> Function Scan on generate_series s (actual rows=500.00 loops=1)
(9 rows)
+--
+-- Planner optimization: optimize_window_clauses must not alter RPR frame
+--
+-- optimize_window_clauses() replaces frame options via prosupport functions.
+-- Affected functions: row_number, rank, dense_rank, percent_rank, cume_dist,
+-- ntile. All would change the frame to ROWS UNBOUNDED PRECEDING, breaking
+-- RPR's required ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.
+-- Test with row_number() as representative case.
+--
+-- Without RPR: row_number() frame is optimized to ROWS UNBOUNDED PRECEDING
+CREATE VIEW rpr_ev87 AS
+SELECT row_number() OVER w
+FROM generate_series(1, 10) AS s(v)
+WINDOW w AS (
+ ORDER BY v
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+);
+EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev87;
+ QUERY PLAN
+--------------------------------------------------------------
+ Subquery Scan on rpr_ev87
+ -> WindowAgg
+ Window: w AS (ORDER BY s.v ROWS UNBOUNDED PRECEDING)
+ -> Sort
+ Sort Key: s.v
+ -> Function Scan on generate_series s
+(6 rows)
+
+-- With RPR: frame must remain ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+CREATE VIEW rpr_ev88 AS
+SELECT row_number() OVER w
+FROM generate_series(1, 10) AS s(v)
+WINDOW w AS (
+ ORDER BY v
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ PATTERN (A B+)
+ DEFINE
+ B AS v > PREV(v)
+);
+EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev88;
+ QUERY PLAN
+--------------------------------------------------------------------------------------
+ Subquery Scan on rpr_ev88
+ -> WindowAgg
+ Window: w AS (ORDER BY s.v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+ Pattern: a b+
+ -> Sort
+ Sort Key: s.v
+ -> Function Scan on generate_series s
+(7 rows)
+
+--
+-- Planner optimization: find_window_run_conditions must not push down
+-- RPR window function results as Run Conditions.
+--
+-- find_window_run_conditions() pushes WHERE filters on monotonic window
+-- functions into WindowAgg as Run Conditions for early termination.
+-- With RPR's required frame (ROWS BETWEEN CURRENT ROW AND UNBOUNDED
+-- FOLLOWING), the monotonic direction determines which operators trigger
+-- Run Condition pushdown:
+-- INCREASING (<=): row_number, rank, dense_rank, percent_rank,
+-- cume_dist, ntile
+-- DECREASING (>): count(*) (via int8inc, END_UNBOUNDED_FOLLOWING)
+-- RPR window function results are match-dependent, not monotonic.
+-- Test with count(*) > 0 as representative case.
+--
+-- Without RPR: count(*) > 0 is pushed down as Run Condition
+EXPLAIN (COSTS OFF)
+SELECT * FROM (
+ SELECT count(*) OVER w AS cnt
+ FROM generate_series(1, 10) AS s(v)
+ WINDOW w AS (
+ ORDER BY v
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ )
+) t WHERE cnt > 0;
+ QUERY PLAN
+--------------------------------------------------------------------------------------
+ Subquery Scan on t
+ -> WindowAgg
+ Window: w AS (ORDER BY s.v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+ Run Condition: (count(*) OVER w > 0)
+ -> Sort
+ Sort Key: s.v
+ -> Function Scan on generate_series s
+(7 rows)
+
+-- With RPR: count(*) > 0 must not be pushed down as Run Condition
+EXPLAIN (COSTS OFF)
+SELECT * FROM (
+ SELECT count(*) OVER w AS cnt
+ FROM generate_series(1, 10) AS s(v)
+ WINDOW w AS (
+ ORDER BY v
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ PATTERN (A B+)
+ DEFINE
+ B AS v > PREV(v)
+ )
+) t WHERE cnt > 0;
+ QUERY PLAN
+--------------------------------------------------------------------------------------
+ Subquery Scan on t
+ Filter: (t.cnt > 0)
+ -> WindowAgg
+ Window: w AS (ORDER BY s.v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+ Pattern: a b+
+ -> Sort
+ Sort Key: s.v
+ -> Function Scan on generate_series s
+(8 rows)
+
diff --git a/src/test/regress/sql/rpr_explain.sql b/src/test/regress/sql/rpr_explain.sql
index 8e22382a68e..640d328957b 100644
--- a/src/test/regress/sql/rpr_explain.sql
+++ b/src/test/regress/sql/rpr_explain.sql
@@ -2196,3 +2196,81 @@ WINDOW w AS (
E AS v % 100 = 5
);');
+--
+-- Planner optimization: optimize_window_clauses must not alter RPR frame
+--
+-- optimize_window_clauses() replaces frame options via prosupport functions.
+-- Affected functions: row_number, rank, dense_rank, percent_rank, cume_dist,
+-- ntile. All would change the frame to ROWS UNBOUNDED PRECEDING, breaking
+-- RPR's required ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.
+-- Test with row_number() as representative case.
+--
+
+-- Without RPR: row_number() frame is optimized to ROWS UNBOUNDED PRECEDING
+CREATE VIEW rpr_ev87 AS
+SELECT row_number() OVER w
+FROM generate_series(1, 10) AS s(v)
+WINDOW w AS (
+ ORDER BY v
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+);
+
+EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev87;
+
+-- With RPR: frame must remain ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+CREATE VIEW rpr_ev88 AS
+SELECT row_number() OVER w
+FROM generate_series(1, 10) AS s(v)
+WINDOW w AS (
+ ORDER BY v
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ PATTERN (A B+)
+ DEFINE
+ B AS v > PREV(v)
+);
+
+EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev88;
+
+--
+-- Planner optimization: find_window_run_conditions must not push down
+-- RPR window function results as Run Conditions.
+--
+-- find_window_run_conditions() pushes WHERE filters on monotonic window
+-- functions into WindowAgg as Run Conditions for early termination.
+-- With RPR's required frame (ROWS BETWEEN CURRENT ROW AND UNBOUNDED
+-- FOLLOWING), the monotonic direction determines which operators trigger
+-- Run Condition pushdown:
+-- INCREASING (<=): row_number, rank, dense_rank, percent_rank,
+-- cume_dist, ntile
+-- DECREASING (>): count(*) (via int8inc, END_UNBOUNDED_FOLLOWING)
+-- RPR window function results are match-dependent, not monotonic.
+-- Test with count(*) > 0 as representative case.
+--
+
+-- Without RPR: count(*) > 0 is pushed down as Run Condition
+EXPLAIN (COSTS OFF)
+SELECT * FROM (
+ SELECT count(*) OVER w AS cnt
+ FROM generate_series(1, 10) AS s(v)
+ WINDOW w AS (
+ ORDER BY v
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ )
+) t WHERE cnt > 0;
+
+-- With RPR: count(*) > 0 must not be pushed down as Run Condition
+EXPLAIN (COSTS OFF)
+SELECT * FROM (
+ SELECT count(*) OVER w AS cnt
+ FROM generate_series(1, 10) AS s(v)
+ WINDOW w AS (
+ ORDER BY v
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ PATTERN (A B+)
+ DEFINE
+ B AS v > PREV(v)
+ )
+) t WHERE cnt > 0;
+
--
2.50.1 (Apple Git-155)