From 4141670cfe47e9aa2ceac717dba0d09015fa89c1 Mon Sep 17 00:00:00 2001 From: Henson Choi Date: Thu, 2 Apr 2026 14:09:53 +0900 Subject: [PATCH] Add RPR planner integration tests Add rpr_integration.sql covering planner optimization interactions with RPR windows: frame optimization, run condition pushdown, window deduplication, unused window removal, inverse transition, cost estimation, subquery flattening, DEFINE expression propagation, and LIMIT. Also covers integration scenarios with partitioned tables, LATERAL, recursive CTEs, incremental sort, volatile functions, and correlated subqueries. --- src/test/regress/expected/rpr_integration.out | 885 ++++++++++++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/sql/rpr_integration.sql | 537 +++++++++++ 3 files changed, 1423 insertions(+), 1 deletion(-) create mode 100644 src/test/regress/expected/rpr_integration.out create mode 100644 src/test/regress/sql/rpr_integration.sql diff --git a/src/test/regress/expected/rpr_integration.out b/src/test/regress/expected/rpr_integration.out new file mode 100644 index 00000000000..a21ac5a8588 --- /dev/null +++ b/src/test/regress/expected/rpr_integration.out @@ -0,0 +1,885 @@ +-- ============================================================ +-- RPR Integration Tests +-- Planner optimization interaction tests for Row Pattern Recognition +-- ============================================================ +-- +-- Verifies that each planner optimization correctly handles RPR windows. +-- Even if individual optimizations are tested elsewhere, this file +-- provides a single checkpoint for all planner/RPR interactions. +-- +-- A. Planner Optimization Protection Tests +-- A1. Frame optimization bypass +-- A2. Run condition pushdown bypass +-- A3. Window dedup prevention (planner) +-- A4. Window dedup prevention (parser) +-- A5. Unused window removal prevention +-- A6. Inverse transition bypass +-- A7. Cost estimation RPR awareness +-- A8. Subquery flattening prevention +-- A9. DEFINE expression non-propagation +-- A10. RPR + LIMIT +-- +-- B. Integration Scenario Tests +-- B1. RPR + CTE +-- B2. RPR + JOIN +-- B3. RPR + Set operations +-- B4. RPR + Prepared statements +-- B5. RPR + Partitioned table +-- B6. RPR + LATERAL +-- B7. RPR + Recursive CTE +-- B8. RPR + Incremental sort +-- B9. RPR + Volatile function in DEFINE +-- B10. RPR + Correlated subquery +-- +CREATE TABLE rpr_integ (id INT, val INT); +INSERT INTO rpr_integ VALUES + (1, 10), (2, 20), (3, 15), (4, 25), (5, 5), + (6, 30), (7, 35), (8, 20), (9, 40), (10, 45); +-- ============================================================ +-- A1. Frame optimization bypass +-- ============================================================ +-- optimize_window_clauses() must not apply frame optimization to RPR windows. +-- Non-RPR: frame is optimized (RANGE -> ROWS conversion, etc.) +-- RPR: frame must stay as specified (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) +-- Non-RPR window with default frame -> frame optimization applied +EXPLAIN (COSTS OFF) +SELECT count(*) OVER w FROM rpr_integ +WINDOW w AS (ORDER BY id); + QUERY PLAN +----------------------------------- + WindowAgg + Window: w AS (ORDER BY id) + -> Sort + Sort Key: id + -> Seq Scan on rpr_integ +(5 rows) + +-- RPR window -> frame optimization must NOT change the frame +EXPLAIN (COSTS OFF) +SELECT count(*) OVER w FROM rpr_integ +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)); + QUERY PLAN +------------------------------------------------------------------------------- + WindowAgg + Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a b+ + -> Sort + Sort Key: id + -> Seq Scan on rpr_integ +(6 rows) + +-- ============================================================ +-- A2. Run condition pushdown bypass +-- ============================================================ +-- find_window_run_conditions() must not push monotonic filters as Run Conditions +-- for RPR windows, because RPR results are match-dependent, not monotonic. +-- Non-RPR: count(*) > 0 pushed down as Run Condition +EXPLAIN (COSTS OFF) +SELECT * FROM ( + SELECT count(*) OVER w AS cnt + FROM rpr_integ + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) +) t WHERE cnt > 0; + QUERY PLAN +----------------------------------------------------------------------------------------------- + Subquery Scan on t + -> WindowAgg + Window: w AS (ORDER BY rpr_integ.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Run Condition: (count(*) OVER w > 0) + -> Sort + Sort Key: rpr_integ.id + -> Seq Scan on rpr_integ +(7 rows) + +-- RPR: count(*) > 0 must NOT be pushed down (appears as Filter, not Run Condition) +EXPLAIN (COSTS OFF) +SELECT * FROM ( + SELECT count(*) OVER w AS cnt + FROM rpr_integ + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) +) t WHERE cnt > 0; + QUERY PLAN +----------------------------------------------------------------------------------------------- + Subquery Scan on t + Filter: (t.cnt > 0) + -> WindowAgg + Window: w AS (ORDER BY rpr_integ.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a b+ + -> Sort + Sort Key: rpr_integ.id + -> Seq Scan on rpr_integ +(8 rows) + +-- Verify results are correct +SELECT * FROM ( + SELECT id, val, count(*) OVER w AS cnt + FROM rpr_integ + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) +) t WHERE cnt > 0 +ORDER BY id; + id | val | cnt +----+-----+----- + 1 | 10 | 2 + 3 | 15 | 2 + 5 | 5 | 3 + 8 | 20 | 3 +(4 rows) + +-- ============================================================ +-- A3. Window dedup prevention (planner) +-- ============================================================ +-- optimize_window_clauses() must not merge RPR and non-RPR windows +-- even if they share the same base frame. +-- Two windows with same ORDER BY: one RPR, one non-RPR -> separate WindowAgg nodes +EXPLAIN (COSTS OFF) +SELECT + count(*) OVER w_rpr AS rpr_cnt, + count(*) OVER w_normal AS normal_cnt +FROM rpr_integ +WINDOW + w_rpr AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)), + w_normal AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING); + QUERY PLAN +----------------------------------------------------------------------------------------- + WindowAgg + Window: w_normal AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + -> WindowAgg + Window: w_rpr AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a b+ + -> Sort + Sort Key: id + -> Seq Scan on rpr_integ +(8 rows) + +-- Verify both produce different results +SELECT + id, val, + count(*) OVER w_rpr AS rpr_cnt, + count(*) OVER w_normal AS normal_cnt +FROM rpr_integ +WINDOW + w_rpr AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)), + w_normal AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) +ORDER BY id; + id | val | rpr_cnt | normal_cnt +----+-----+---------+------------ + 1 | 10 | 2 | 10 + 2 | 20 | 0 | 9 + 3 | 15 | 2 | 8 + 4 | 25 | 0 | 7 + 5 | 5 | 3 | 6 + 6 | 30 | 0 | 5 + 7 | 35 | 0 | 4 + 8 | 20 | 3 | 3 + 9 | 40 | 0 | 2 + 10 | 45 | 0 | 1 +(10 rows) + +-- ============================================================ +-- A4. Window dedup prevention (parser) +-- ============================================================ +-- transformWindowFuncCall() must not merge two RPR windows with different DEFINE. +-- Two RPR windows: same PATTERN structure, different DEFINE conditions. +-- EXPLAIN: must show two separate WindowAgg nodes +EXPLAIN (COSTS OFF) +SELECT + count(*) OVER w1 AS cnt_up, + count(*) OVER w2 AS cnt_down +FROM rpr_integ +WINDOW + w1 AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)), + w2 AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val < PREV(val)); + QUERY PLAN +-------------------------------------------------------------------------------------- + WindowAgg + Window: w2 AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a b+ + -> WindowAgg + Window: w1 AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a b+ + -> Sort + Sort Key: id + -> Seq Scan on rpr_integ +(9 rows) + +-- Verify results differ +SELECT + id, val, + count(*) OVER w1 AS cnt_up, + count(*) OVER w2 AS cnt_down +FROM rpr_integ +WINDOW + w1 AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)), + w2 AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val < PREV(val)) +ORDER BY id; + id | val | cnt_up | cnt_down +----+-----+--------+---------- + 1 | 10 | 2 | 0 + 2 | 20 | 0 | 2 + 3 | 15 | 2 | 0 + 4 | 25 | 0 | 2 + 5 | 5 | 3 | 0 + 6 | 30 | 0 | 0 + 7 | 35 | 0 | 2 + 8 | 20 | 3 | 0 + 9 | 40 | 0 | 0 + 10 | 45 | 0 | 0 +(10 rows) + +-- ============================================================ +-- A5. Unused window removal prevention +-- ============================================================ +-- remove_unused_subquery_outputs() must not remove RPR window functions +-- even when their output columns are not referenced by the outer query. +-- RPR window output not directly referenced, but pattern matching must still execute +SELECT count(*) FROM ( + SELECT count(*) OVER w FROM rpr_integ + WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS val > PREV(val)) +) t; + count +------- + 10 +(1 row) + +-- With PREV in DEFINE: window must be preserved +SELECT count(*), sum(c) FROM ( + SELECT count(*) OVER w AS c FROM rpr_integ + WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS val > PREV(val)) +) t; + count | sum +-------+----- + 10 | 6 +(1 row) + +-- Without PREV but with DEFINE: window must still be preserved +SELECT count(*), sum(c) FROM ( + SELECT count(*) OVER w AS c FROM rpr_integ + WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS TRUE) +) t; + count | sum +-------+----- + 10 | 10 +(1 row) + +-- ============================================================ +-- A6. Inverse transition bypass +-- ============================================================ +-- RPR windows must not use moving aggregate (inverse transition) optimization. +-- Moving aggregates recalculate by subtracting leaving rows, but RPR's +-- reduced frames make this invalid. +-- sum() supports inverse transition. With RPR, it must not be used. +-- Verify correctness: sum with RPR pattern matching +SELECT id, val, + sum(val) OVER w AS pattern_sum +FROM rpr_integ +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) +ORDER BY id; + id | val | pattern_sum +----+-----+------------- + 1 | 10 | 30 + 2 | 20 | + 3 | 15 | 40 + 4 | 25 | + 5 | 5 | 70 + 6 | 30 | + 7 | 35 | + 8 | 20 | 105 + 9 | 40 | + 10 | 45 | +(10 rows) + +-- ============================================================ +-- A7. Cost estimation RPR awareness +-- ============================================================ +-- cost_windowagg() must account for DEFINE expression evaluation cost. +-- Verify RPR WindowAgg cost > non-RPR WindowAgg cost. +CREATE FUNCTION get_windowagg_cost(query text) RETURNS numeric AS $$ +DECLARE + plan json; + cost numeric; +BEGIN + EXECUTE 'EXPLAIN (FORMAT JSON) ' || query INTO plan; + cost := (plan->0->'Plan'->>'Total Cost')::numeric; + RETURN cost; +END; +$$ LANGUAGE plpgsql; +SELECT get_windowagg_cost( + 'SELECT count(*) OVER w FROM rpr_integ + WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+ C+) DEFINE B AS val > PREV(val), C AS val < PREV(val))') + > + get_windowagg_cost( + 'SELECT count(*) OVER w FROM rpr_integ + WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)') + AS rpr_cost_is_higher; + rpr_cost_is_higher +-------------------- + t +(1 row) + +DROP FUNCTION get_windowagg_cost(text); +-- ============================================================ +-- A8. Subquery flattening prevention +-- ============================================================ +-- is_simple_subquery() prevents pullup of subqueries with window functions. +-- RPR subquery must not be flattened into the outer query. +-- EXPLAIN must show Subquery Scan (not flattened). +EXPLAIN (COSTS OFF) +SELECT * FROM ( + SELECT id, val, count(*) OVER w AS cnt + FROM rpr_integ + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) +) sub +WHERE cnt > 0; + QUERY PLAN +----------------------------------------------------------------------------------------------- + Subquery Scan on sub + Filter: (sub.cnt > 0) + -> WindowAgg + Window: w AS (ORDER BY rpr_integ.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a b+ + -> Sort + Sort Key: rpr_integ.id + -> Seq Scan on rpr_integ +(8 rows) + +-- ============================================================ +-- A9. DEFINE expression non-propagation +-- ============================================================ +-- DEFINE expressions must not appear in upper WindowAgg's targetlist. +-- Only Var nodes should be propagated, not the full DEFINE expression. +-- EXPLAIN VERBOSE: outer WindowAgg output must NOT contain DEFINE expression. +EXPLAIN (VERBOSE, COSTS OFF) +SELECT + count(*) OVER w_rpr AS rpr_cnt, + count(*) OVER w_normal AS normal_cnt +FROM rpr_integ +WINDOW + w_rpr AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)), + w_normal AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING); + QUERY PLAN +--------------------------------------------------------------------------------------------------- + WindowAgg + Output: (count(*) OVER w_rpr), count(*) OVER w_normal, id, val + Window: w_normal AS (ORDER BY rpr_integ.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + -> WindowAgg + Output: id, val, count(*) OVER w_rpr + Window: w_rpr AS (ORDER BY rpr_integ.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a b+ + -> Sort + Output: id, val + Sort Key: rpr_integ.id + -> Seq Scan on public.rpr_integ + Output: id, val +(12 rows) + +-- ============================================================ +-- A10. RPR + LIMIT +-- ============================================================ +-- LIMIT must not interfere with RPR pattern matching. +-- The result should be a prefix of the unlimited query. +SELECT id, val, count(*) OVER w AS cnt +FROM rpr_integ +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) +ORDER BY id +LIMIT 5; + id | val | cnt +----+-----+----- + 1 | 10 | 2 + 2 | 20 | 0 + 3 | 15 | 2 + 4 | 25 | 0 + 5 | 5 | 3 +(5 rows) + +-- ============================================================ +-- B1. RPR + CTE +-- ============================================================ +-- CTE with RPR, outer query aggregates +WITH rpr_result AS ( + SELECT id, val, count(*) OVER w AS cnt + FROM rpr_integ + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) +) +SELECT count(*) AS total_rows, + count(*) FILTER (WHERE cnt > 0) AS matched_rows, + max(cnt) AS max_match_len +FROM rpr_result; + total_rows | matched_rows | max_match_len +------------+--------------+--------------- + 10 | 4 | 3 +(1 row) + +-- Multiple CTE references +WITH rpr_result AS ( + SELECT id, val, count(*) OVER w AS cnt + FROM rpr_integ + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) +) +SELECT r1.id, r1.cnt +FROM rpr_result r1 +JOIN rpr_result r2 ON r1.id = r2.id AND r1.cnt = r2.cnt +WHERE r1.cnt > 0 +ORDER BY r1.id; + id | cnt +----+----- + 1 | 2 + 3 | 2 + 5 | 3 + 8 | 3 +(4 rows) + +-- ============================================================ +-- B2. RPR + JOIN +-- ============================================================ +CREATE TABLE rpr_integ2 (id INT, label TEXT); +INSERT INTO rpr_integ2 VALUES + (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'), (5, 'e'), + (6, 'f'), (7, 'g'), (8, 'h'), (9, 'i'), (10, 'j'); +-- RPR subquery joined with another table +SELECT r.id, r.cnt, j.label +FROM ( + SELECT id, count(*) OVER w AS cnt + FROM rpr_integ + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) +) r +JOIN rpr_integ2 j ON r.id = j.id +WHERE r.cnt > 0 +ORDER BY r.id; + id | cnt | label +----+-----+------- + 1 | 2 | a + 3 | 2 | c + 5 | 3 | e + 8 | 3 | h +(4 rows) + +-- ============================================================ +-- B3. RPR + Set operations +-- ============================================================ +-- UNION of RPR and non-RPR results +SELECT id, cnt, 'rpr' AS source FROM ( + SELECT id, count(*) OVER w AS cnt + FROM rpr_integ + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) +) t WHERE cnt > 0 +UNION ALL +SELECT id, count(*) OVER (ORDER BY id) AS cnt, 'normal' AS source +FROM rpr_integ +ORDER BY source, id; + id | cnt | source +----+-----+-------- + 1 | 1 | normal + 2 | 2 | normal + 3 | 3 | normal + 4 | 4 | normal + 5 | 5 | normal + 6 | 6 | normal + 7 | 7 | normal + 8 | 8 | normal + 9 | 9 | normal + 10 | 10 | normal + 1 | 2 | rpr + 3 | 2 | rpr + 5 | 3 | rpr + 8 | 3 | rpr +(14 rows) + +-- ============================================================ +-- B4. RPR + Prepared statements +-- ============================================================ +-- Plan caching must correctly serialize/deserialize RPR fields +PREPARE rpr_prep AS +SELECT id, val, count(*) OVER w AS cnt +FROM rpr_integ +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) +ORDER BY id; +-- First execution (custom plan) +EXECUTE rpr_prep; + id | val | cnt +----+-----+----- + 1 | 10 | 2 + 2 | 20 | 0 + 3 | 15 | 2 + 4 | 25 | 0 + 5 | 5 | 3 + 6 | 30 | 0 + 7 | 35 | 0 + 8 | 20 | 3 + 9 | 40 | 0 + 10 | 45 | 0 +(10 rows) + +-- Subsequent executions (may use cached plan) +EXECUTE rpr_prep; + id | val | cnt +----+-----+----- + 1 | 10 | 2 + 2 | 20 | 0 + 3 | 15 | 2 + 4 | 25 | 0 + 5 | 5 | 3 + 6 | 30 | 0 + 7 | 35 | 0 + 8 | 20 | 3 + 9 | 40 | 0 + 10 | 45 | 0 +(10 rows) + +EXECUTE rpr_prep; + id | val | cnt +----+-----+----- + 1 | 10 | 2 + 2 | 20 | 0 + 3 | 15 | 2 + 4 | 25 | 0 + 5 | 5 | 3 + 6 | 30 | 0 + 7 | 35 | 0 + 8 | 20 | 3 + 9 | 40 | 0 + 10 | 45 | 0 +(10 rows) + +EXECUTE rpr_prep; + id | val | cnt +----+-----+----- + 1 | 10 | 2 + 2 | 20 | 0 + 3 | 15 | 2 + 4 | 25 | 0 + 5 | 5 | 3 + 6 | 30 | 0 + 7 | 35 | 0 + 8 | 20 | 3 + 9 | 40 | 0 + 10 | 45 | 0 +(10 rows) + +EXECUTE rpr_prep; + id | val | cnt +----+-----+----- + 1 | 10 | 2 + 2 | 20 | 0 + 3 | 15 | 2 + 4 | 25 | 0 + 5 | 5 | 3 + 6 | 30 | 0 + 7 | 35 | 0 + 8 | 20 | 3 + 9 | 40 | 0 + 10 | 45 | 0 +(10 rows) + +EXECUTE rpr_prep; + id | val | cnt +----+-----+----- + 1 | 10 | 2 + 2 | 20 | 0 + 3 | 15 | 2 + 4 | 25 | 0 + 5 | 5 | 3 + 6 | 30 | 0 + 7 | 35 | 0 + 8 | 20 | 3 + 9 | 40 | 0 + 10 | 45 | 0 +(10 rows) + +-- Verify cached plan produces same result +EXECUTE rpr_prep; + id | val | cnt +----+-----+----- + 1 | 10 | 2 + 2 | 20 | 0 + 3 | 15 | 2 + 4 | 25 | 0 + 5 | 5 | 3 + 6 | 30 | 0 + 7 | 35 | 0 + 8 | 20 | 3 + 9 | 40 | 0 + 10 | 45 | 0 +(10 rows) + +DEALLOCATE rpr_prep; +-- ============================================================ +-- B5. RPR + Partitioned table +-- ============================================================ +-- RPR pattern matching must work correctly across partitions. +CREATE TABLE rpr_part (id INT, val INT) PARTITION BY RANGE (id); +CREATE TABLE rpr_part_1 PARTITION OF rpr_part FOR VALUES FROM (1) TO (6); +CREATE TABLE rpr_part_2 PARTITION OF rpr_part FOR VALUES FROM (6) TO (11); +INSERT INTO rpr_part SELECT id, val FROM rpr_integ; +-- Same query on partitioned vs non-partitioned should produce identical results +SELECT id, val, count(*) OVER w AS cnt +FROM rpr_part +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) +ORDER BY id; + id | val | cnt +----+-----+----- + 1 | 10 | 2 + 2 | 20 | 0 + 3 | 15 | 2 + 4 | 25 | 0 + 5 | 5 | 3 + 6 | 30 | 0 + 7 | 35 | 0 + 8 | 20 | 3 + 9 | 40 | 0 + 10 | 45 | 0 +(10 rows) + +-- EXPLAIN: Append under Sort under WindowAgg +EXPLAIN (COSTS OFF) +SELECT count(*) OVER w FROM rpr_part +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)); + QUERY PLAN +---------------------------------------------------------------------------------------- + WindowAgg + Window: w AS (ORDER BY rpr_part.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a b+ + -> Sort + Sort Key: rpr_part.id + -> Append + -> Seq Scan on rpr_part_1 + -> Seq Scan on rpr_part_2 +(8 rows) + +DROP TABLE rpr_part; +-- ============================================================ +-- B6. RPR + LATERAL +-- ============================================================ +-- RPR inside a LATERAL subquery. Qualified column references from the +-- outer query are not yet supported in DEFINE, so this tests the basic +-- case where LATERAL provides filtering but DEFINE uses only local columns. +SELECT o.id AS outer_id, r.id, r.cnt +FROM rpr_integ o, +LATERAL ( + SELECT id, count(*) OVER w AS cnt + FROM rpr_integ + WHERE id <= o.id + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) +) r +WHERE r.cnt > 0 AND o.id IN (5, 10) +ORDER BY o.id, r.id; + outer_id | id | cnt +----------+----+----- + 5 | 1 | 2 + 5 | 3 | 2 + 10 | 1 | 2 + 10 | 3 | 2 + 10 | 5 | 3 + 10 | 8 | 3 +(6 rows) + +-- ============================================================ +-- B7. RPR + Recursive CTE +-- ============================================================ +-- RPR applied to results of a recursive CTE. +WITH RECURSIVE seq AS ( + SELECT 1 AS id, 10 AS val + UNION ALL + SELECT id + 1, val + (CASE WHEN id % 3 = 0 THEN -15 ELSE 10 END) + FROM seq WHERE id < 10 +) +SELECT id, val, count(*) OVER w AS cnt +FROM seq +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) +ORDER BY id; + id | val | cnt +----+-----+----- + 1 | 10 | 3 + 2 | 20 | 0 + 3 | 30 | 0 + 4 | 15 | 3 + 5 | 25 | 0 + 6 | 35 | 0 + 7 | 20 | 3 + 8 | 30 | 0 + 9 | 40 | 0 + 10 | 25 | 0 +(10 rows) + +-- ============================================================ +-- B8. RPR + Incremental sort +-- ============================================================ +-- Incremental sort may be used when data is partially sorted. +CREATE INDEX rpr_integ_id_idx ON rpr_integ (id); +SET enable_seqscan = off; +EXPLAIN (COSTS OFF) +SELECT id, val, count(*) OVER w AS cnt +FROM rpr_integ +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)); + QUERY PLAN +------------------------------------------------------------------------------- + WindowAgg + Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a b+ + -> Index Scan using rpr_integ_id_idx on rpr_integ +(4 rows) + +SELECT id, val, count(*) OVER w AS cnt +FROM rpr_integ +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) +ORDER BY id; + id | val | cnt +----+-----+----- + 1 | 10 | 2 + 2 | 20 | 0 + 3 | 15 | 2 + 4 | 25 | 0 + 5 | 5 | 3 + 6 | 30 | 0 + 7 | 35 | 0 + 8 | 20 | 3 + 9 | 40 | 0 + 10 | 45 | 0 +(10 rows) + +RESET enable_seqscan; +DROP INDEX rpr_integ_id_idx; +-- ============================================================ +-- B9. RPR + Volatile function in DEFINE +-- ============================================================ +-- Volatile functions must be evaluated per-row, not optimized away. +SELECT id, val, count(*) OVER w AS cnt +FROM rpr_integ +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val) AND random() >= 0.0) +ORDER BY id; + id | val | cnt +----+-----+----- + 1 | 10 | 2 + 2 | 20 | 0 + 3 | 15 | 2 + 4 | 25 | 0 + 5 | 5 | 3 + 6 | 30 | 0 + 7 | 35 | 0 + 8 | 20 | 3 + 9 | 40 | 0 + 10 | 45 | 0 +(10 rows) + +-- ============================================================ +-- B10. RPR + Correlated subquery in WHERE +-- ============================================================ +-- RPR window in a correlated subquery. DEFINE references only local +-- columns (no qualified refs), while correlation is in WHERE. +SELECT o.id, o.val, + (SELECT count(*) OVER w + FROM rpr_integ i + WHERE i.id <= o.id + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) + ORDER BY id + LIMIT 1) AS first_cnt +FROM rpr_integ o +ORDER BY o.id; + id | val | first_cnt +----+-----+----------- + 1 | 10 | 0 + 2 | 20 | 2 + 3 | 15 | 2 + 4 | 25 | 2 + 5 | 5 | 2 + 6 | 30 | 2 + 7 | 35 | 2 + 8 | 20 | 2 + 9 | 40 | 2 + 10 | 45 | 2 +(10 rows) + +-- Cleanup +DROP TABLE rpr_integ; +DROP TABLE rpr_integ2; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 6a2d19d4410..dbfa3e8f2cc 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -107,7 +107,7 @@ test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combo # ---------- # Row Pattern Recognition tests # ---------- -test: rpr rpr_base rpr_explain rpr_nfa +test: rpr rpr_base rpr_explain rpr_nfa rpr_integration # ---------- # Another group of parallel tests (JSON related) diff --git a/src/test/regress/sql/rpr_integration.sql b/src/test/regress/sql/rpr_integration.sql new file mode 100644 index 00000000000..2ae42c92a27 --- /dev/null +++ b/src/test/regress/sql/rpr_integration.sql @@ -0,0 +1,537 @@ +-- ============================================================ +-- RPR Integration Tests +-- Planner optimization interaction tests for Row Pattern Recognition +-- ============================================================ +-- +-- Verifies that each planner optimization correctly handles RPR windows. +-- Even if individual optimizations are tested elsewhere, this file +-- provides a single checkpoint for all planner/RPR interactions. +-- +-- A. Planner Optimization Protection Tests +-- A1. Frame optimization bypass +-- A2. Run condition pushdown bypass +-- A3. Window dedup prevention (planner) +-- A4. Window dedup prevention (parser) +-- A5. Unused window removal prevention +-- A6. Inverse transition bypass +-- A7. Cost estimation RPR awareness +-- A8. Subquery flattening prevention +-- A9. DEFINE expression non-propagation +-- A10. RPR + LIMIT +-- +-- B. Integration Scenario Tests +-- B1. RPR + CTE +-- B2. RPR + JOIN +-- B3. RPR + Set operations +-- B4. RPR + Prepared statements +-- B5. RPR + Partitioned table +-- B6. RPR + LATERAL +-- B7. RPR + Recursive CTE +-- B8. RPR + Incremental sort +-- B9. RPR + Volatile function in DEFINE +-- B10. RPR + Correlated subquery +-- + +CREATE TABLE rpr_integ (id INT, val INT); +INSERT INTO rpr_integ VALUES + (1, 10), (2, 20), (3, 15), (4, 25), (5, 5), + (6, 30), (7, 35), (8, 20), (9, 40), (10, 45); + +-- ============================================================ +-- A1. Frame optimization bypass +-- ============================================================ +-- optimize_window_clauses() must not apply frame optimization to RPR windows. +-- Non-RPR: frame is optimized (RANGE -> ROWS conversion, etc.) +-- RPR: frame must stay as specified (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + +-- Non-RPR window with default frame -> frame optimization applied +EXPLAIN (COSTS OFF) +SELECT count(*) OVER w FROM rpr_integ +WINDOW w AS (ORDER BY id); + +-- RPR window -> frame optimization must NOT change the frame +EXPLAIN (COSTS OFF) +SELECT count(*) OVER w FROM rpr_integ +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)); + +-- ============================================================ +-- A2. Run condition pushdown bypass +-- ============================================================ +-- find_window_run_conditions() must not push monotonic filters as Run Conditions +-- for RPR windows, because RPR results are match-dependent, not monotonic. + +-- Non-RPR: count(*) > 0 pushed down as Run Condition +EXPLAIN (COSTS OFF) +SELECT * FROM ( + SELECT count(*) OVER w AS cnt + FROM rpr_integ + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) +) t WHERE cnt > 0; + +-- RPR: count(*) > 0 must NOT be pushed down (appears as Filter, not Run Condition) +EXPLAIN (COSTS OFF) +SELECT * FROM ( + SELECT count(*) OVER w AS cnt + FROM rpr_integ + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) +) t WHERE cnt > 0; + +-- Verify results are correct +SELECT * FROM ( + SELECT id, val, count(*) OVER w AS cnt + FROM rpr_integ + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) +) t WHERE cnt > 0 +ORDER BY id; + +-- ============================================================ +-- A3. Window dedup prevention (planner) +-- ============================================================ +-- optimize_window_clauses() must not merge RPR and non-RPR windows +-- even if they share the same base frame. + +-- Two windows with same ORDER BY: one RPR, one non-RPR -> separate WindowAgg nodes +EXPLAIN (COSTS OFF) +SELECT + count(*) OVER w_rpr AS rpr_cnt, + count(*) OVER w_normal AS normal_cnt +FROM rpr_integ +WINDOW + w_rpr AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)), + w_normal AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING); + +-- Verify both produce different results +SELECT + id, val, + count(*) OVER w_rpr AS rpr_cnt, + count(*) OVER w_normal AS normal_cnt +FROM rpr_integ +WINDOW + w_rpr AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)), + w_normal AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) +ORDER BY id; + +-- ============================================================ +-- A4. Window dedup prevention (parser) +-- ============================================================ +-- transformWindowFuncCall() must not merge two RPR windows with different DEFINE. +-- Two RPR windows: same PATTERN structure, different DEFINE conditions. + +-- EXPLAIN: must show two separate WindowAgg nodes +EXPLAIN (COSTS OFF) +SELECT + count(*) OVER w1 AS cnt_up, + count(*) OVER w2 AS cnt_down +FROM rpr_integ +WINDOW + w1 AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)), + w2 AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val < PREV(val)); + +-- Verify results differ +SELECT + id, val, + count(*) OVER w1 AS cnt_up, + count(*) OVER w2 AS cnt_down +FROM rpr_integ +WINDOW + w1 AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)), + w2 AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val < PREV(val)) +ORDER BY id; + +-- ============================================================ +-- A5. Unused window removal prevention +-- ============================================================ +-- remove_unused_subquery_outputs() must not remove RPR window functions +-- even when their output columns are not referenced by the outer query. + +-- RPR window output not directly referenced, but pattern matching must still execute +SELECT count(*) FROM ( + SELECT count(*) OVER w FROM rpr_integ + WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS val > PREV(val)) +) t; + +-- With PREV in DEFINE: window must be preserved +SELECT count(*), sum(c) FROM ( + SELECT count(*) OVER w AS c FROM rpr_integ + WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS val > PREV(val)) +) t; + +-- Without PREV but with DEFINE: window must still be preserved +SELECT count(*), sum(c) FROM ( + SELECT count(*) OVER w AS c FROM rpr_integ + WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS TRUE) +) t; + +-- ============================================================ +-- A6. Inverse transition bypass +-- ============================================================ +-- RPR windows must not use moving aggregate (inverse transition) optimization. +-- Moving aggregates recalculate by subtracting leaving rows, but RPR's +-- reduced frames make this invalid. + +-- sum() supports inverse transition. With RPR, it must not be used. +-- Verify correctness: sum with RPR pattern matching +SELECT id, val, + sum(val) OVER w AS pattern_sum +FROM rpr_integ +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) +ORDER BY id; + +-- ============================================================ +-- A7. Cost estimation RPR awareness +-- ============================================================ +-- cost_windowagg() must account for DEFINE expression evaluation cost. +-- Verify RPR WindowAgg cost > non-RPR WindowAgg cost. + +CREATE FUNCTION get_windowagg_cost(query text) RETURNS numeric AS $$ +DECLARE + plan json; + cost numeric; +BEGIN + EXECUTE 'EXPLAIN (FORMAT JSON) ' || query INTO plan; + cost := (plan->0->'Plan'->>'Total Cost')::numeric; + RETURN cost; +END; +$$ LANGUAGE plpgsql; + +SELECT get_windowagg_cost( + 'SELECT count(*) OVER w FROM rpr_integ + WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+ C+) DEFINE B AS val > PREV(val), C AS val < PREV(val))') + > + get_windowagg_cost( + 'SELECT count(*) OVER w FROM rpr_integ + WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)') + AS rpr_cost_is_higher; + +DROP FUNCTION get_windowagg_cost(text); + +-- ============================================================ +-- A8. Subquery flattening prevention +-- ============================================================ +-- is_simple_subquery() prevents pullup of subqueries with window functions. +-- RPR subquery must not be flattened into the outer query. +-- EXPLAIN must show Subquery Scan (not flattened). + +EXPLAIN (COSTS OFF) +SELECT * FROM ( + SELECT id, val, count(*) OVER w AS cnt + FROM rpr_integ + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) +) sub +WHERE cnt > 0; + +-- ============================================================ +-- A9. DEFINE expression non-propagation +-- ============================================================ +-- DEFINE expressions must not appear in upper WindowAgg's targetlist. +-- Only Var nodes should be propagated, not the full DEFINE expression. +-- EXPLAIN VERBOSE: outer WindowAgg output must NOT contain DEFINE expression. + +EXPLAIN (VERBOSE, COSTS OFF) +SELECT + count(*) OVER w_rpr AS rpr_cnt, + count(*) OVER w_normal AS normal_cnt +FROM rpr_integ +WINDOW + w_rpr AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)), + w_normal AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING); + +-- ============================================================ +-- A10. RPR + LIMIT +-- ============================================================ +-- LIMIT must not interfere with RPR pattern matching. +-- The result should be a prefix of the unlimited query. + +SELECT id, val, count(*) OVER w AS cnt +FROM rpr_integ +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) +ORDER BY id +LIMIT 5; + +-- ============================================================ +-- B1. RPR + CTE +-- ============================================================ + +-- CTE with RPR, outer query aggregates +WITH rpr_result AS ( + SELECT id, val, count(*) OVER w AS cnt + FROM rpr_integ + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) +) +SELECT count(*) AS total_rows, + count(*) FILTER (WHERE cnt > 0) AS matched_rows, + max(cnt) AS max_match_len +FROM rpr_result; + +-- Multiple CTE references +WITH rpr_result AS ( + SELECT id, val, count(*) OVER w AS cnt + FROM rpr_integ + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) +) +SELECT r1.id, r1.cnt +FROM rpr_result r1 +JOIN rpr_result r2 ON r1.id = r2.id AND r1.cnt = r2.cnt +WHERE r1.cnt > 0 +ORDER BY r1.id; + +-- ============================================================ +-- B2. RPR + JOIN +-- ============================================================ + +CREATE TABLE rpr_integ2 (id INT, label TEXT); +INSERT INTO rpr_integ2 VALUES + (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'), (5, 'e'), + (6, 'f'), (7, 'g'), (8, 'h'), (9, 'i'), (10, 'j'); + +-- RPR subquery joined with another table +SELECT r.id, r.cnt, j.label +FROM ( + SELECT id, count(*) OVER w AS cnt + FROM rpr_integ + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) +) r +JOIN rpr_integ2 j ON r.id = j.id +WHERE r.cnt > 0 +ORDER BY r.id; + +-- ============================================================ +-- B3. RPR + Set operations +-- ============================================================ + +-- UNION of RPR and non-RPR results +SELECT id, cnt, 'rpr' AS source FROM ( + SELECT id, count(*) OVER w AS cnt + FROM rpr_integ + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) +) t WHERE cnt > 0 +UNION ALL +SELECT id, count(*) OVER (ORDER BY id) AS cnt, 'normal' AS source +FROM rpr_integ +ORDER BY source, id; + +-- ============================================================ +-- B4. RPR + Prepared statements +-- ============================================================ + +-- Plan caching must correctly serialize/deserialize RPR fields +PREPARE rpr_prep AS +SELECT id, val, count(*) OVER w AS cnt +FROM rpr_integ +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) +ORDER BY id; + +-- First execution (custom plan) +EXECUTE rpr_prep; + +-- Subsequent executions (may use cached plan) +EXECUTE rpr_prep; +EXECUTE rpr_prep; +EXECUTE rpr_prep; +EXECUTE rpr_prep; +EXECUTE rpr_prep; + +-- Verify cached plan produces same result +EXECUTE rpr_prep; + +DEALLOCATE rpr_prep; + +-- ============================================================ +-- B5. RPR + Partitioned table +-- ============================================================ +-- RPR pattern matching must work correctly across partitions. + +CREATE TABLE rpr_part (id INT, val INT) PARTITION BY RANGE (id); +CREATE TABLE rpr_part_1 PARTITION OF rpr_part FOR VALUES FROM (1) TO (6); +CREATE TABLE rpr_part_2 PARTITION OF rpr_part FOR VALUES FROM (6) TO (11); +INSERT INTO rpr_part SELECT id, val FROM rpr_integ; + +-- Same query on partitioned vs non-partitioned should produce identical results +SELECT id, val, count(*) OVER w AS cnt +FROM rpr_part +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) +ORDER BY id; + +-- EXPLAIN: Append under Sort under WindowAgg +EXPLAIN (COSTS OFF) +SELECT count(*) OVER w FROM rpr_part +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)); + +DROP TABLE rpr_part; + +-- ============================================================ +-- B6. RPR + LATERAL +-- ============================================================ +-- RPR inside a LATERAL subquery. Qualified column references from the +-- outer query are not yet supported in DEFINE, so this tests the basic +-- case where LATERAL provides filtering but DEFINE uses only local columns. + +SELECT o.id AS outer_id, r.id, r.cnt +FROM rpr_integ o, +LATERAL ( + SELECT id, count(*) OVER w AS cnt + FROM rpr_integ + WHERE id <= o.id + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) +) r +WHERE r.cnt > 0 AND o.id IN (5, 10) +ORDER BY o.id, r.id; + +-- ============================================================ +-- B7. RPR + Recursive CTE +-- ============================================================ +-- RPR applied to results of a recursive CTE. + +WITH RECURSIVE seq AS ( + SELECT 1 AS id, 10 AS val + UNION ALL + SELECT id + 1, val + (CASE WHEN id % 3 = 0 THEN -15 ELSE 10 END) + FROM seq WHERE id < 10 +) +SELECT id, val, count(*) OVER w AS cnt +FROM seq +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) +ORDER BY id; + +-- ============================================================ +-- B8. RPR + Incremental sort +-- ============================================================ +-- Incremental sort may be used when data is partially sorted. + +CREATE INDEX rpr_integ_id_idx ON rpr_integ (id); +SET enable_seqscan = off; + +EXPLAIN (COSTS OFF) +SELECT id, val, count(*) OVER w AS cnt +FROM rpr_integ +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)); + +SELECT id, val, count(*) OVER w AS cnt +FROM rpr_integ +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) +ORDER BY id; + +RESET enable_seqscan; +DROP INDEX rpr_integ_id_idx; + +-- ============================================================ +-- B9. RPR + Volatile function in DEFINE +-- ============================================================ +-- Volatile functions must be evaluated per-row, not optimized away. + +SELECT id, val, count(*) OVER w AS cnt +FROM rpr_integ +WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val) AND random() >= 0.0) +ORDER BY id; + +-- ============================================================ +-- B10. RPR + Correlated subquery in WHERE +-- ============================================================ +-- RPR window in a correlated subquery. DEFINE references only local +-- columns (no qualified refs), while correlation is in WHERE. + +SELECT o.id, o.val, + (SELECT count(*) OVER w + FROM rpr_integ i + WHERE i.id <= o.id + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE B AS val > PREV(val)) + ORDER BY id + LIMIT 1) AS first_cnt +FROM rpr_integ o +ORDER BY o.id; + +-- Cleanup +DROP TABLE rpr_integ; +DROP TABLE rpr_integ2; -- 2.50.1 (Apple Git-155)