From e2702f72ab4933c531698b263e946005fc5d415e Mon Sep 17 00:00:00 2001 From: Henson Choi Date: Mon, 10 Aug 2026 16:07:21 +0900 Subject: [PATCH] Section the trailing RPR integration tests and gather the planner guards Tests had accumulated at the end of rpr_integration.sql without the numbered section headers the rest of the file uses, and the header index at the top listed only B10. Add B11 through B14, name what B10 actually covers, and reword the comments of the tests thus grouped, following the file's Plan:/Result: convention where the test has a plan claim to make. The same planner properties were also asserted from two other files. rpr_explain.sql carried a hundred-line block of planner guards inside its Large Scale Statistics Verification section: the block asserts nothing about the NFA statistics that file exists to check, and it is not in the file's own index. A1 and A2 here already assert the frame-optimization and run-condition properties, so drop the two redundant pairs and move the function lists they carried into the A1 and A2 comments, where a reader looks for them. Keep rpr_ev_opt_mixed, the one case A3 does not cover: its two windows start from different frames and converge only after the non-RPR one is rewritten, so the query shows in one plan that the RPR window is preserved while the other is not. It moves here as a view and is left undropped on purpose, because it is also the only view in the tree that serializes an RPR window together with a non-RPR one, which pg_upgrade and pg_dump need in order to exercise that round trip. The file header says so, since the file otherwise drops what it creates. rpr_base.sql's Window Deduplication Tests section held a single value-level query for the property A3 states; move it to A3 as the result-level companion and drop the emptied section and its index entry. Two comment blocks that restate what the code or the test below them already says are cut down. The inverse-transition note now names the mechanism that actually causes the bypass, rpr_is_defined() forcing peraggstate->restart, rather than a navigation-mark separation that is not what keeps the two apart; the logging aggregate immediately below already asserts the bypass directly. The recursive-query block points at parse_cte.c for the standard citation instead of copying it. --- src/test/regress/expected/rpr_base.out | 26 --- src/test/regress/expected/rpr_explain.out | 149 ---------------- src/test/regress/expected/rpr_integration.out | 160 +++++++++++++----- src/test/regress/sql/rpr_base.sql | 20 --- src/test/regress/sql/rpr_explain.sql | 100 ----------- src/test/regress/sql/rpr_integration.sql | 143 +++++++++++----- 6 files changed, 221 insertions(+), 377 deletions(-) diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out index 3186ceae442..827dd5aac45 100644 --- a/src/test/regress/expected/rpr_base.out +++ b/src/test/regress/expected/rpr_base.out @@ -15,7 +15,6 @@ -- Serialization/Deserialization Tests -- Glued Quantifier / Alternation Tests -- Error Cases Tests --- Window Deduplication Tests -- -- Planner Layer: -- Pattern Optimization Tests @@ -4934,31 +4933,6 @@ WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 0 (5 rows) --- ============================================================ --- Window Deduplication Tests --- ============================================================ --- non-RPR and RPR windows with identical base frame are kept separate. -SELECT id, val, - first_value(id) OVER ( - ORDER BY id - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - ) AS fv_normal, - first_value(id) OVER w1 AS fv_rpr -FROM (VALUES (1, 10), (2, 20), (3, 30), (4, 40)) AS t(id, val) -WINDOW w1 AS ( - ORDER BY id - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - PATTERN (A+) - DEFINE A AS val > 10 -); - id | val | fv_normal | fv_rpr -----+-----+-----------+-------- - 1 | 10 | 1 | - 2 | 20 | 2 | 2 - 3 | 30 | 3 | - 4 | 40 | 4 | -(4 rows) - -- ============================================================ -- Pattern Optimization Tests -- ============================================================ diff --git a/src/test/regress/expected/rpr_explain.out b/src/test/regress/expected/rpr_explain.out index 19025127b8d..ab7456454f1 100644 --- a/src/test/regress/expected/rpr_explain.out +++ b/src/test/regress/expected/rpr_explain.out @@ -6083,155 +6083,6 @@ 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_ev_opt_no_rpr 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_ev_opt_no_rpr; - QUERY PLAN --------------------------------------------------------------- - Subquery Scan on rpr_ev_opt_no_rpr - -> 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_ev_opt_with_rpr 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_ev_opt_with_rpr; - QUERY PLAN --------------------------------------------------------------------------------------- - Subquery Scan on rpr_ev_opt_with_rpr - -> WindowAgg - Window: w AS (ORDER BY s.v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a b+ - Nav Mark Lookback: 1 - -> Sort - Sort Key: s.v - -> Function Scan on generate_series s -(8 rows) - --- --- Planner optimization: non-RPR and RPR windows that share the same base frame --- after frame optimization are kept as separate WindowAgg nodes. --- -CREATE VIEW rpr_ev_opt_mixed AS -SELECT - row_number() OVER w_normal AS rn_normal, - row_number() OVER w_rpr AS rn_rpr -FROM generate_series(1, 5) AS s(v) -WINDOW - w_normal AS (ORDER BY v RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - w_rpr AS ( - ORDER BY v - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - PATTERN (A+) - DEFINE A AS v > 1 - ); -EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev_opt_mixed; - QUERY PLAN ------------------------------------------------------------------------------------------- - Subquery Scan on rpr_ev_opt_mixed - -> WindowAgg - Window: w_rpr AS (ORDER BY s.v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+# - -> WindowAgg - Window: w_normal AS (ORDER BY s.v ROWS UNBOUNDED PRECEDING) - -> Sort - Sort Key: s.v - -> Function Scan on generate_series s -(9 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 of the window function determines --- which comparison operators allow pushdown: --- INCREASING (<=): row_number, rank, dense_rank, percent_rank, --- cume_dist, ntile --- DECREASING (>): count(*). As the current row advances, the frame --- (which ends at UNBOUNDED FOLLOWING) shrinks, so the --- count decreases. --- RPR window function results are match-dependent, not monotonic, so this --- pushdown does not apply. Test with count(*) > 0 as a 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+ - Nav Mark Lookback: 1 - -> Sort - Sort Key: s.v - -> Function Scan on generate_series s -(9 rows) - -- ============================================================ -- Nav Mark Lookback/Lookahead Tests -- Verifies planner-computed navigation offsets for tuplestore trim. diff --git a/src/test/regress/expected/rpr_integration.out b/src/test/regress/expected/rpr_integration.out index d8ccf291060..2d5edebb128 100644 --- a/src/test/regress/expected/rpr_integration.out +++ b/src/test/regress/expected/rpr_integration.out @@ -7,6 +7,10 @@ -- Even if individual optimizations are tested elsewhere, this file -- provides a single checkpoint for all planner/RPR interactions. -- +-- The file drops what it creates, with one exception: A3 leaves the view +-- rpr_ev_opt_mixed behind, so that pg_upgrade and pg_dump exercise an RPR +-- window serialized together with a non-RPR one. +-- -- A. Planner Optimization Protection Tests -- A1. Frame optimization bypass -- A2. Run condition pushdown bypass @@ -29,7 +33,11 @@ -- B7. RPR + Recursive CTE -- B8. RPR + Incremental sort -- B9. RPR + Volatile function in DEFINE --- B10. RPR + Correlated subquery +-- B10. RPR + Correlated subquery in WHERE +-- B11. RPR + Junk targetlist pruning +-- B12. RPR + Correlated navigation offsets +-- B13. RPR + DEFINE-only parameter caching +-- B14. RPR + Multiple window definitions -- CREATE TABLE rpr_integ (id INT, val INT); INSERT INTO rpr_integ VALUES @@ -47,6 +55,10 @@ INSERT INTO rpr_integ VALUES -- UNBOUNDED PRECEDING, while in the RPR case the guard in -- optimize_window_clauses() blocks the rewrite and the frame is -- preserved as specified. +-- 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. -- Non-RPR baseline: the planner rewrites the frame to ROWS UNBOUNDED PRECEDING. EXPLAIN (COSTS OFF) SELECT row_number() OVER w FROM rpr_integ @@ -87,6 +99,16 @@ WINDOW w AS (ORDER BY id -- determined by pattern matching rather than by a monotonic -- accumulation over the frame, so a filter such as "cnt > 0" cannot be -- used to stop evaluating the window function early. +-- With RPR's required frame (ROWS BETWEEN CURRENT ROW AND UNBOUNDED +-- FOLLOWING), the monotonic direction of the window function determines +-- which comparison operators allow pushdown: +-- INCREASING (<=): row_number, rank, dense_rank, percent_rank, +-- cume_dist, ntile +-- DECREASING (>): count(*). As the current row advances, the frame +-- (which ends at UNBOUNDED FOLLOWING) shrinks, so the +-- count decreases. +-- RPR window function results are match-dependent, not monotonic, so this +-- pushdown does not apply. -- Non-RPR baseline: the filter is expected to appear as a Run Condition. EXPLAIN (COSTS OFF) SELECT * FROM ( @@ -230,6 +252,62 @@ ORDER BY id; 10 | 45 | 0 | 1 (10 rows) +-- Result level: if the two windows had been merged, fv_normal and fv_rpr +-- would agree on every row. They do not, so the windows stayed separate. +SELECT id, val, + first_value(id) OVER ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + ) AS fv_normal, + first_value(id) OVER w1 AS fv_rpr +FROM (VALUES (1, 10), (2, 20), (3, 30), (4, 40)) AS t(id, val) +WINDOW w1 AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS val > 10 +); + id | val | fv_normal | fv_rpr +----+-----+-----------+-------- + 1 | 10 | 1 | + 2 | 20 | 2 | 2 + 3 | 30 | 3 | + 4 | 40 | 4 | +(4 rows) + +-- The two windows above start from the same frame. These two do not: +-- they converge only after frame optimization rewrites the non-RPR one, +-- and the RPR window is preserved, so they still must not be merged. +-- The view is deliberately left undropped: it is the only one in the +-- tree that serializes an RPR window and a non-RPR window together, so +-- pg_upgrade/pg_dump needs it to exercise that round trip. +CREATE VIEW rpr_ev_opt_mixed AS +SELECT + row_number() OVER w_normal AS rn_normal, + row_number() OVER w_rpr AS rn_rpr +FROM generate_series(1, 5) AS s(v) +WINDOW + w_normal AS (ORDER BY v RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + w_rpr AS ( + ORDER BY v + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS v > 1 + ); +EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev_opt_mixed; + QUERY PLAN +------------------------------------------------------------------------------------------ + Subquery Scan on rpr_ev_opt_mixed + -> WindowAgg + Window: w_rpr AS (ORDER BY s.v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+# + -> WindowAgg + Window: w_normal AS (ORDER BY s.v ROWS UNBOUNDED PRECEDING) + -> Sort + Sort Key: s.v + -> Function Scan on generate_series s +(9 rows) + -- ============================================================ -- A4. Window dedup prevention (same PATTERN, different DEFINE) -- ============================================================ @@ -518,17 +596,10 @@ SELECT sum(c) FROM ( -- reduced frame is not a sliding window; the set of rows included in -- the frame is determined by pattern matching and cannot be derived -- incrementally from the previous frame. --- sum() would normally be eligible for the moving aggregate --- optimization; under RPR it must be computed from scratch over each --- reduced frame, and the returned values must match the pattern. --- Note: inverse-transition selection is not exposed in the plan, so --- there is no direct EXPLAIN assertion for it. The structural --- guarantee is that RPR uses its own navigation mark, distinct from --- the moving-aggregate mark, so the inverse-transition path is --- never reached on the RPR side. This test verifies that --- separation indirectly: if inverse transition leaked into the RPR --- path, state would mix across match boundaries and pattern_sum --- would diverge from the expected output, failing the regression. +-- sum() is inverse-transition eligible in a plain window; over an RPR reduced +-- frame rpr_is_defined() forces peraggstate->restart, so the aggregate is +-- recomputed from scratch. The logging aggregate below asserts the bypass +-- directly. SELECT id, val, sum(val) OVER w AS pattern_sum FROM rpr_integ @@ -1398,16 +1469,8 @@ DROP TABLE rpr_lat_o, rpr_lat_i; -- ============================================================ -- B7. RPR + Recursive CTE -- ============================================================ --- Verify that RPR is rejected inside a recursive query. --- ISO/IEC 19075-5 6.17.5 (R020) and 4.18.5 (R010) cite CREATE --- RECURSIVE VIEW examples and state that "row pattern matching --- is prohibited in recursive queries". The formal rule lives in --- ISO/IEC 9075-2:2016 7.17 Syntax Rule 3)f): a potentially --- recursive shall not contain a or . Per 3)e), every --- under WITH RECURSIVE is "potentially --- recursive", so the rejection covers the base (non-recursive) --- leg too, not just the self-referencing leg. +-- RPR is rejected in every leg of a recursive query, including the +-- non-recursive leg (see parse_cte.c for the standard citation). -- WITH RECURSIVE: RPR in the base leg is rejected even though the -- base leg never references the recursive CTE name. WITH RECURSIVE seq AS ( @@ -1608,13 +1671,18 @@ ORDER BY o.id; 10 | 45 | 2 (10 rows) --- A column referenced only by DEFINE must not keep an unrelated column that --- merely shares its attribute number. DEFINE references a (rpr_over1); c --- (rpr_over2) has the same attno but is unused, so it must be dropped. +-- ============================================================ +-- B11. RPR + Junk targetlist pruning +-- ============================================================ +-- Verify that the junk targetlist entry planted for a DEFINE-only +-- column does not keep an unrelated column alive. DEFINE references +-- a (rpr_over1); c (rpr_over2) carries the same attribute number but +-- is unused, so the plan must drop it. CREATE TABLE rpr_over1 (a int); CREATE TABLE rpr_over2 (c int); INSERT INTO rpr_over1 VALUES (1),(2),(3); INSERT INTO rpr_over2 VALUES (1),(2),(3); +-- Plan: only the DEFINE column survives in the subquery output. EXPLAIN (VERBOSE, COSTS OFF) SELECT cnt FROM ( SELECT a AS oa, c AS oc, count(*) OVER w AS cnt @@ -1642,6 +1710,9 @@ SELECT cnt FROM ( (15 rows) DROP TABLE rpr_over1, rpr_over2; +-- ============================================================ +-- B12. RPR + Correlated navigation offsets +-- ============================================================ -- A row pattern navigation offset that resolves to a correlated PARAM_EXEC -- (here through SRF inlining of rpr_srf_prev(g.n)) must be re-resolved on every -- rescan, not frozen at executor init. The inlined WindowAgg is the inner @@ -1655,8 +1726,8 @@ CREATE FUNCTION rpr_srf_prev(k int) RETURNS SETOF bigint AS $$ WINDOW w AS (ORDER BY v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A+) DEFINE A AS v > PREV(v, k)) $$ LANGUAGE sql STABLE; --- The offset reads "runtime"; the WindowAgg inlines into the nestloop and is --- rescanned per outer row. +-- Plan: the offset reads "runtime"; the WindowAgg inlines into the +-- nestloop and is rescanned per outer row. EXPLAIN (COSTS OFF) SELECT g.n, max(s) FROM (VALUES (1), (2), (3)) g(n), LATERAL rpr_srf_prev(g.n) s GROUP BY g.n ORDER BY g.n; @@ -1687,18 +1758,18 @@ GROUP BY g.n ORDER BY g.n; 3 | 7 (3 rows) --- A forward FIRST-family offset with a correlated PARAM_EXEC must likewise be --- re-resolved per scan (navFirstOffset / navFirstOffsetKind), not frozen at init. --- PATTERN (B A+) anchors the match start at B so A can reference FIRST(v, k) --- k rows ahead; each outer k yields its own forward offset (k=0 matches all --- ten rows, k>=1 makes the first A fail), proving per-scan re-resolution. +-- A forward FIRST-family offset must likewise be re-resolved per scan +-- (navFirstOffset / navFirstOffsetKind). PATTERN (B A+) anchors the +-- match start at B so A can reference FIRST(v, k) k rows ahead, and +-- each outer k yields its own forward offset. CREATE FUNCTION rpr_srf_first(k int) RETURNS SETOF bigint AS $$ SELECT count(*) OVER w FROM rpr_srf WINDOW w AS (ORDER BY v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (B A+) DEFINE A AS v > FIRST(v, k)) $$ LANGUAGE sql STABLE; --- The forward offset reads "runtime" and the WindowAgg inlines into the nestloop. +-- Plan: the forward offset reads "runtime" and the WindowAgg inlines +-- into the nestloop. EXPLAIN (COSTS OFF) SELECT g.n, max(s) FROM (VALUES (0), (1), (2)) g(n), LATERAL rpr_srf_first(g.n) s GROUP BY g.n ORDER BY g.n; @@ -1719,8 +1790,8 @@ GROUP BY g.n ORDER BY g.n; -> Seq Scan on rpr_srf (13 rows) --- k=0 -> 10, k>=1 -> 0: distinct per outer row, so the forward offset is not --- frozen at ExecInit. +-- Result: k=0 matches all ten rows and k>=1 makes the first A fail, +-- so the forward offset is not frozen at ExecInit. SELECT g.n, max(s) AS m FROM (VALUES (0), (1), (2)) g(n), LATERAL rpr_srf_first(g.n) s GROUP BY g.n ORDER BY g.n; n | m @@ -1731,18 +1802,19 @@ GROUP BY g.n ORDER BY g.n; (3 rows) DROP FUNCTION rpr_srf_first(int); --- A compound navigation whose OUTER offset is a correlated PARAM_EXEC must be --- re-resolved per scan as well. The last offset overflows int64, so that --- scan's navigation has no target row at all: k=1 -> 9, k=3 -> 7, --- k=overflow -> 0. Three answers from one plan is what says each rescan --- resolved its own outer offset. The trim kind a scan settles on does not --- show in a count; rpr_explain reads it out of EXPLAIN ANALYZE instead. +-- A compound navigation's OUTER offset must be re-resolved per scan +-- as well. The last offset overflows int64, so that scan's navigation +-- has no target row at all. CREATE FUNCTION rpr_srf_cmp(k int8) RETURNS SETOF bigint AS $$ SELECT count(*) OVER w FROM rpr_srf WINDOW w AS (ORDER BY v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A B+) DEFINE B AS v > PREV(LAST(v, 1), k)) $$ LANGUAGE sql STABLE; +-- Result: k=1 -> 9, k=3 -> 7, overflow -> 0. Three answers from one plan +-- is what says each rescan resolved its own outer offset. The trim kind a +-- scan settles on does not show in a count; rpr_explain reads it out of +-- EXPLAIN ANALYZE instead. SELECT g.n, max(s) AS m FROM (VALUES (1::int8), (3::int8), (9223372036854775807::int8)) g(n), LATERAL rpr_srf_cmp(g.n) s @@ -1757,6 +1829,9 @@ GROUP BY g.n ORDER BY g.n; DROP FUNCTION rpr_srf_cmp(int8); DROP FUNCTION rpr_srf_prev(int); DROP TABLE rpr_srf; +-- ============================================================ +-- B13. RPR + DEFINE-only parameter caching +-- ============================================================ -- A correlated PARAM_EXEC used only inside DEFINE must reach the WindowAgg's -- extParam. Otherwise chgParam never gets to the HashAgg that DISTINCT plans -- above it, and its hash table for the first outer row is re-served. @@ -1784,6 +1859,9 @@ ORDER BY 1, 2; DROP FUNCTION rpr_hcache_fn(int); DROP TABLE rpr_hcache_thr, rpr_hcache_stock; +-- ============================================================ +-- B14. RPR + Multiple window definitions +-- ============================================================ -- A DEFINE-only column and a later window's sort key both become junk -- targetlist entries. Each draws its resno from p_next_resno, which is what -- keeps the two distinct: a targetlist that gives one resno to two entries is diff --git a/src/test/regress/sql/rpr_base.sql b/src/test/regress/sql/rpr_base.sql index 4bf25eabd17..e5da7b92ecf 100644 --- a/src/test/regress/sql/rpr_base.sql +++ b/src/test/regress/sql/rpr_base.sql @@ -15,7 +15,6 @@ -- Serialization/Deserialization Tests -- Glued Quantifier / Alternation Tests -- Error Cases Tests --- Window Deduplication Tests -- -- Planner Layer: -- Pattern Optimization Tests @@ -3189,25 +3188,6 @@ FROM generate_series(1,5) s(v) WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A+) DEFINE A AS PREV(v, 0) = v); --- ============================================================ --- Window Deduplication Tests --- ============================================================ - --- non-RPR and RPR windows with identical base frame are kept separate. -SELECT id, val, - first_value(id) OVER ( - ORDER BY id - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - ) AS fv_normal, - first_value(id) OVER w1 AS fv_rpr -FROM (VALUES (1, 10), (2, 20), (3, 30), (4, 40)) AS t(id, val) -WINDOW w1 AS ( - ORDER BY id - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - PATTERN (A+) - DEFINE A AS val > 10 -); - -- ============================================================ -- Pattern Optimization Tests -- ============================================================ diff --git a/src/test/regress/sql/rpr_explain.sql b/src/test/regress/sql/rpr_explain.sql index 6bda8db16f1..4b1a76ac278 100644 --- a/src/test/regress/sql/rpr_explain.sql +++ b/src/test/regress/sql/rpr_explain.sql @@ -3467,106 +3467,6 @@ 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_ev_opt_no_rpr 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_ev_opt_no_rpr; - --- With RPR: frame must remain ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING -CREATE VIEW rpr_ev_opt_with_rpr 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_ev_opt_with_rpr; - --- --- Planner optimization: non-RPR and RPR windows that share the same base frame --- after frame optimization are kept as separate WindowAgg nodes. --- -CREATE VIEW rpr_ev_opt_mixed AS -SELECT - row_number() OVER w_normal AS rn_normal, - row_number() OVER w_rpr AS rn_rpr -FROM generate_series(1, 5) AS s(v) -WINDOW - w_normal AS (ORDER BY v RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), - w_rpr AS ( - ORDER BY v - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - PATTERN (A+) - DEFINE A AS v > 1 - ); - -EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev_opt_mixed; - --- --- 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 of the window function determines --- which comparison operators allow pushdown: --- INCREASING (<=): row_number, rank, dense_rank, percent_rank, --- cume_dist, ntile --- DECREASING (>): count(*). As the current row advances, the frame --- (which ends at UNBOUNDED FOLLOWING) shrinks, so the --- count decreases. --- RPR window function results are match-dependent, not monotonic, so this --- pushdown does not apply. Test with count(*) > 0 as a 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; - -- ============================================================ -- Nav Mark Lookback/Lookahead Tests -- Verifies planner-computed navigation offsets for tuplestore trim. diff --git a/src/test/regress/sql/rpr_integration.sql b/src/test/regress/sql/rpr_integration.sql index b0f9322ba62..4d60545c6cb 100644 --- a/src/test/regress/sql/rpr_integration.sql +++ b/src/test/regress/sql/rpr_integration.sql @@ -7,6 +7,10 @@ -- Even if individual optimizations are tested elsewhere, this file -- provides a single checkpoint for all planner/RPR interactions. -- +-- The file drops what it creates, with one exception: A3 leaves the view +-- rpr_ev_opt_mixed behind, so that pg_upgrade and pg_dump exercise an RPR +-- window serialized together with a non-RPR one. +-- -- A. Planner Optimization Protection Tests -- A1. Frame optimization bypass -- A2. Run condition pushdown bypass @@ -29,7 +33,11 @@ -- B7. RPR + Recursive CTE -- B8. RPR + Incremental sort -- B9. RPR + Volatile function in DEFINE --- B10. RPR + Correlated subquery +-- B10. RPR + Correlated subquery in WHERE +-- B11. RPR + Junk targetlist pruning +-- B12. RPR + Correlated navigation offsets +-- B13. RPR + DEFINE-only parameter caching +-- B14. RPR + Multiple window definitions -- CREATE TABLE rpr_integ (id INT, val INT); @@ -49,6 +57,10 @@ INSERT INTO rpr_integ VALUES -- UNBOUNDED PRECEDING, while in the RPR case the guard in -- optimize_window_clauses() blocks the rewrite and the frame is -- preserved as specified. +-- 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. -- Non-RPR baseline: the planner rewrites the frame to ROWS UNBOUNDED PRECEDING. EXPLAIN (COSTS OFF) @@ -72,6 +84,16 @@ WINDOW w AS (ORDER BY id -- determined by pattern matching rather than by a monotonic -- accumulation over the frame, so a filter such as "cnt > 0" cannot be -- used to stop evaluating the window function early. +-- With RPR's required frame (ROWS BETWEEN CURRENT ROW AND UNBOUNDED +-- FOLLOWING), the monotonic direction of the window function determines +-- which comparison operators allow pushdown: +-- INCREASING (<=): row_number, rank, dense_rank, percent_rank, +-- cume_dist, ntile +-- DECREASING (>): count(*). As the current row advances, the frame +-- (which ends at UNBOUNDED FOLLOWING) shrinks, so the +-- count decreases. +-- RPR window function results are match-dependent, not monotonic, so this +-- pushdown does not apply. -- Non-RPR baseline: the filter is expected to appear as a Run Condition. EXPLAIN (COSTS OFF) @@ -155,6 +177,44 @@ SELECT FROM rpr_integ ORDER BY id; +-- Result level: if the two windows had been merged, fv_normal and fv_rpr +-- would agree on every row. They do not, so the windows stayed separate. +SELECT id, val, + first_value(id) OVER ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + ) AS fv_normal, + first_value(id) OVER w1 AS fv_rpr +FROM (VALUES (1, 10), (2, 20), (3, 30), (4, 40)) AS t(id, val) +WINDOW w1 AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS val > 10 +); + +-- The two windows above start from the same frame. These two do not: +-- they converge only after frame optimization rewrites the non-RPR one, +-- and the RPR window is preserved, so they still must not be merged. +-- The view is deliberately left undropped: it is the only one in the +-- tree that serializes an RPR window and a non-RPR window together, so +-- pg_upgrade/pg_dump needs it to exercise that round trip. +CREATE VIEW rpr_ev_opt_mixed AS +SELECT + row_number() OVER w_normal AS rn_normal, + row_number() OVER w_rpr AS rn_rpr +FROM generate_series(1, 5) AS s(v) +WINDOW + w_normal AS (ORDER BY v RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), + w_rpr AS ( + ORDER BY v + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS v > 1 + ); + +EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev_opt_mixed; + -- ============================================================ -- A4. Window dedup prevention (same PATTERN, different DEFINE) -- ============================================================ @@ -337,17 +397,10 @@ SELECT sum(c) FROM ( -- the frame is determined by pattern matching and cannot be derived -- incrementally from the previous frame. --- sum() would normally be eligible for the moving aggregate --- optimization; under RPR it must be computed from scratch over each --- reduced frame, and the returned values must match the pattern. --- Note: inverse-transition selection is not exposed in the plan, so --- there is no direct EXPLAIN assertion for it. The structural --- guarantee is that RPR uses its own navigation mark, distinct from --- the moving-aggregate mark, so the inverse-transition path is --- never reached on the RPR side. This test verifies that --- separation indirectly: if inverse transition leaked into the RPR --- path, state would mix across match boundaries and pattern_sum --- would diverge from the expected output, failing the regression. +-- sum() is inverse-transition eligible in a plain window; over an RPR reduced +-- frame rpr_is_defined() forces peraggstate->restart, so the aggregate is +-- recomputed from scratch. The logging aggregate below asserts the bypass +-- directly. SELECT id, val, sum(val) OVER w AS pattern_sum FROM rpr_integ @@ -877,16 +930,8 @@ DROP TABLE rpr_lat_o, rpr_lat_i; -- ============================================================ -- B7. RPR + Recursive CTE -- ============================================================ --- Verify that RPR is rejected inside a recursive query. --- ISO/IEC 19075-5 6.17.5 (R020) and 4.18.5 (R010) cite CREATE --- RECURSIVE VIEW examples and state that "row pattern matching --- is prohibited in recursive queries". The formal rule lives in --- ISO/IEC 9075-2:2016 7.17 Syntax Rule 3)f): a potentially --- recursive shall not contain a or . Per 3)e), every --- under WITH RECURSIVE is "potentially --- recursive", so the rejection covers the base (non-recursive) --- leg too, not just the self-referencing leg. +-- RPR is rejected in every leg of a recursive query, including the +-- non-recursive leg (see parse_cte.c for the standard citation). -- WITH RECURSIVE: RPR in the base leg is rejected even though the -- base leg never references the recursive CTE name. @@ -1023,13 +1068,19 @@ SELECT o.id, o.val, FROM rpr_integ o ORDER BY o.id; --- A column referenced only by DEFINE must not keep an unrelated column that --- merely shares its attribute number. DEFINE references a (rpr_over1); c --- (rpr_over2) has the same attno but is unused, so it must be dropped. +-- ============================================================ +-- B11. RPR + Junk targetlist pruning +-- ============================================================ +-- Verify that the junk targetlist entry planted for a DEFINE-only +-- column does not keep an unrelated column alive. DEFINE references +-- a (rpr_over1); c (rpr_over2) carries the same attribute number but +-- is unused, so the plan must drop it. CREATE TABLE rpr_over1 (a int); CREATE TABLE rpr_over2 (c int); INSERT INTO rpr_over1 VALUES (1),(2),(3); INSERT INTO rpr_over2 VALUES (1),(2),(3); + +-- Plan: only the DEFINE column survives in the subquery output. EXPLAIN (VERBOSE, COSTS OFF) SELECT cnt FROM ( SELECT a AS oa, c AS oc, count(*) OVER w AS cnt @@ -1039,6 +1090,9 @@ SELECT cnt FROM ( ) s; DROP TABLE rpr_over1, rpr_over2; +-- ============================================================ +-- B12. RPR + Correlated navigation offsets +-- ============================================================ -- A row pattern navigation offset that resolves to a correlated PARAM_EXEC -- (here through SRF inlining of rpr_srf_prev(g.n)) must be re-resolved on every -- rescan, not frozen at executor init. The inlined WindowAgg is the inner @@ -1052,8 +1106,8 @@ CREATE FUNCTION rpr_srf_prev(k int) RETURNS SETOF bigint AS $$ WINDOW w AS (ORDER BY v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A+) DEFINE A AS v > PREV(v, k)) $$ LANGUAGE sql STABLE; --- The offset reads "runtime"; the WindowAgg inlines into the nestloop and is --- rescanned per outer row. +-- Plan: the offset reads "runtime"; the WindowAgg inlines into the +-- nestloop and is rescanned per outer row. EXPLAIN (COSTS OFF) SELECT g.n, max(s) FROM (VALUES (1), (2), (3)) g(n), LATERAL rpr_srf_prev(g.n) s GROUP BY g.n ORDER BY g.n; @@ -1061,39 +1115,40 @@ GROUP BY g.n ORDER BY g.n; SELECT g.n, max(s) AS m FROM (VALUES (1), (2), (3)) g(n), LATERAL rpr_srf_prev(g.n) s GROUP BY g.n ORDER BY g.n; --- A forward FIRST-family offset with a correlated PARAM_EXEC must likewise be --- re-resolved per scan (navFirstOffset / navFirstOffsetKind), not frozen at init. --- PATTERN (B A+) anchors the match start at B so A can reference FIRST(v, k) --- k rows ahead; each outer k yields its own forward offset (k=0 matches all --- ten rows, k>=1 makes the first A fail), proving per-scan re-resolution. +-- A forward FIRST-family offset must likewise be re-resolved per scan +-- (navFirstOffset / navFirstOffsetKind). PATTERN (B A+) anchors the +-- match start at B so A can reference FIRST(v, k) k rows ahead, and +-- each outer k yields its own forward offset. CREATE FUNCTION rpr_srf_first(k int) RETURNS SETOF bigint AS $$ SELECT count(*) OVER w FROM rpr_srf WINDOW w AS (ORDER BY v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (B A+) DEFINE A AS v > FIRST(v, k)) $$ LANGUAGE sql STABLE; --- The forward offset reads "runtime" and the WindowAgg inlines into the nestloop. +-- Plan: the forward offset reads "runtime" and the WindowAgg inlines +-- into the nestloop. EXPLAIN (COSTS OFF) SELECT g.n, max(s) FROM (VALUES (0), (1), (2)) g(n), LATERAL rpr_srf_first(g.n) s GROUP BY g.n ORDER BY g.n; --- k=0 -> 10, k>=1 -> 0: distinct per outer row, so the forward offset is not --- frozen at ExecInit. +-- Result: k=0 matches all ten rows and k>=1 makes the first A fail, +-- so the forward offset is not frozen at ExecInit. SELECT g.n, max(s) AS m FROM (VALUES (0), (1), (2)) g(n), LATERAL rpr_srf_first(g.n) s GROUP BY g.n ORDER BY g.n; DROP FUNCTION rpr_srf_first(int); --- A compound navigation whose OUTER offset is a correlated PARAM_EXEC must be --- re-resolved per scan as well. The last offset overflows int64, so that --- scan's navigation has no target row at all: k=1 -> 9, k=3 -> 7, --- k=overflow -> 0. Three answers from one plan is what says each rescan --- resolved its own outer offset. The trim kind a scan settles on does not --- show in a count; rpr_explain reads it out of EXPLAIN ANALYZE instead. +-- A compound navigation's OUTER offset must be re-resolved per scan +-- as well. The last offset overflows int64, so that scan's navigation +-- has no target row at all. CREATE FUNCTION rpr_srf_cmp(k int8) RETURNS SETOF bigint AS $$ SELECT count(*) OVER w FROM rpr_srf WINDOW w AS (ORDER BY v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A B+) DEFINE B AS v > PREV(LAST(v, 1), k)) $$ LANGUAGE sql STABLE; +-- Result: k=1 -> 9, k=3 -> 7, overflow -> 0. Three answers from one plan +-- is what says each rescan resolved its own outer offset. The trim kind a +-- scan settles on does not show in a count; rpr_explain reads it out of +-- EXPLAIN ANALYZE instead. SELECT g.n, max(s) AS m FROM (VALUES (1::int8), (3::int8), (9223372036854775807::int8)) g(n), LATERAL rpr_srf_cmp(g.n) s @@ -1102,6 +1157,9 @@ DROP FUNCTION rpr_srf_cmp(int8); DROP FUNCTION rpr_srf_prev(int); DROP TABLE rpr_srf; +-- ============================================================ +-- B13. RPR + DEFINE-only parameter caching +-- ============================================================ -- A correlated PARAM_EXEC used only inside DEFINE must reach the WindowAgg's -- extParam. Otherwise chgParam never gets to the HashAgg that DISTINCT plans -- above it, and its hash table for the first outer row is re-served. @@ -1123,6 +1181,9 @@ ORDER BY 1, 2; DROP FUNCTION rpr_hcache_fn(int); DROP TABLE rpr_hcache_thr, rpr_hcache_stock; +-- ============================================================ +-- B14. RPR + Multiple window definitions +-- ============================================================ -- A DEFINE-only column and a later window's sort key both become junk -- targetlist entries. Each draws its resno from p_next_resno, which is what -- keeps the two distinct: a targetlist that gives one resno to two entries is