From 08910348e4c3770880fa098b50596805b15e38f7 Mon Sep 17 00:00:00 2001 From: Henson Choi 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)