From cda37c3c806c8ee99e7e2060bfab5b4105b728ad Mon Sep 17 00:00:00 2001 From: Henson Choi Date: Sun, 10 May 2026 13:51:45 +0900 Subject: [PATCH 12/15] Add rpr_integration B7 cases for RPR in recursive query Replace the prior B7 test (which asserted that an RPR window works in the base leg of a recursive CTE) with two cases the recursive-RPR prohibition needs to cover: WITH RECURSIVE with RPR in the base leg, and CREATE RECURSIVE VIEW with an RPR window. Cite ISO/IEC 19075-5 6.17.5 (R020) and 4.18.5 (R010), and the formal rule in ISO/IEC 9075-2:2016 7.17 Syntax Rule 3)e)f), and drop the deferred XXX comment that left this case open to community input. Expected output still matches the current (pre-rejection) behavior; a follow-up patch adds the rejection in parse_cte.c and flips both queries to ERROR. --- src/test/regress/expected/rpr_integration.out | 71 ++++++------------- src/test/regress/sql/rpr_integration.sql | 47 ++++++------ 2 files changed, 43 insertions(+), 75 deletions(-) diff --git a/src/test/regress/expected/rpr_integration.out b/src/test/regress/expected/rpr_integration.out index 7cbeed3347e..0b05a826a27 100644 --- a/src/test/regress/expected/rpr_integration.out +++ b/src/test/regress/expected/rpr_integration.out @@ -1269,54 +1269,18 @@ ORDER BY o.id, r.id; -- ============================================================ -- B7. RPR + Recursive CTE -- ============================================================ --- Verify that an RPR window can appear inside the non-recursive --- (base) leg of a recursive CTE. The plan must show the RPR --- WindowAgg sitting under the Recursive Union as the base-leg --- child, with the WorkTable Scan feeding the recursive leg above --- it. This confirms that RPR output can seed a recursive CTE --- (window functions cannot appear in the recursive leg itself, a --- PostgreSQL restriction, so this is the natural place to exercise --- "RPR under Recursive Union"). --- --- XXX: Whether this case falls under the ISO/IEC 19075-5 6.17.5 / --- 4.18.5 prohibition is not something I can judge. If this case --- is not prohibited, the open question is whether a query that --- does trigger the prohibition can be constructed at all. --- Whether to prohibit this case is left to the community. --- Plan: Recursive Union with the RPR WindowAgg on the base leg and --- the WorkTable Scan on the recursive leg. -EXPLAIN (COSTS OFF) -WITH RECURSIVE seq 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)) - UNION ALL - SELECT id + 100, val, cnt FROM seq WHERE id < 3 -) -SELECT id, val, cnt FROM seq ORDER BY id; - QUERY PLAN -------------------------------------------------------------------------------------------------------- - Sort - Sort Key: seq.id - CTE seq - -> Recursive Union - -> WindowAgg - Window: w AS (ORDER BY rpr_integ.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a b+ - Nav Mark Lookback: 1 - -> Sort - Sort Key: rpr_integ.id - -> Seq Scan on rpr_integ - -> WorkTable Scan on seq seq_1 - Filter: (id < 3) - -> CTE Scan on seq -(14 rows) - --- Result: the base leg contributes the RPR match counts; the --- recursive leg propagates those counts with shifted ids. +-- 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. +-- WITH RECURSIVE: RPR in the base leg is rejected even though the +-- base leg never references the recursive CTE name. WITH RECURSIVE seq AS ( SELECT id, val, count(*) OVER w AS cnt FROM rpr_integ @@ -1344,6 +1308,17 @@ SELECT id, val, cnt FROM seq ORDER BY id; 102 | 20 | 0 (12 rows) +-- CREATE RECURSIVE VIEW: rewritten by makeRecursiveViewSelect() +-- into WITH RECURSIVE, so the same rejection applies. This is +-- the form ISO/IEC 19075-5 6.17.5 cites verbatim. +CREATE RECURSIVE VIEW rpr_recv(id, val, cnt) AS + SELECT id, val, 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)); +DROP VIEW rpr_recv; -- ============================================================ -- B8. RPR + Incremental sort -- ============================================================ diff --git a/src/test/regress/sql/rpr_integration.sql b/src/test/regress/sql/rpr_integration.sql index f4267c74645..bc8f4712bcb 100644 --- a/src/test/regress/sql/rpr_integration.sql +++ b/src/test/regress/sql/rpr_integration.sql @@ -783,24 +783,19 @@ ORDER BY o.id, r.id; -- ============================================================ -- B7. RPR + Recursive CTE -- ============================================================ --- Verify that an RPR window can appear inside the non-recursive --- (base) leg of a recursive CTE. The plan must show the RPR --- WindowAgg sitting under the Recursive Union as the base-leg --- child, with the WorkTable Scan feeding the recursive leg above --- it. This confirms that RPR output can seed a recursive CTE --- (window functions cannot appear in the recursive leg itself, a --- PostgreSQL restriction, so this is the natural place to exercise --- "RPR under Recursive Union"). --- --- XXX: Whether this case falls under the ISO/IEC 19075-5 6.17.5 / --- 4.18.5 prohibition is not something I can judge. If this case --- is not prohibited, the open question is whether a query that --- does trigger the prohibition can be constructed at all. --- Whether to prohibit this case is left to the community. - --- Plan: Recursive Union with the RPR WindowAgg on the base leg and --- the WorkTable Scan on the recursive leg. -EXPLAIN (COSTS OFF) +-- 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. + +-- WITH RECURSIVE: RPR in the base leg is rejected even though the +-- base leg never references the recursive CTE name. WITH RECURSIVE seq AS ( SELECT id, val, count(*) OVER w AS cnt FROM rpr_integ @@ -813,19 +808,17 @@ WITH RECURSIVE seq AS ( ) SELECT id, val, cnt FROM seq ORDER BY id; --- Result: the base leg contributes the RPR match counts; the --- recursive leg propagates those counts with shifted ids. -WITH RECURSIVE seq AS ( - SELECT id, val, count(*) OVER w AS cnt +-- CREATE RECURSIVE VIEW: rewritten by makeRecursiveViewSelect() +-- into WITH RECURSIVE, so the same rejection applies. This is +-- the form ISO/IEC 19075-5 6.17.5 cites verbatim. +CREATE RECURSIVE VIEW rpr_recv(id, val, cnt) AS + SELECT id, val, 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)) - UNION ALL - SELECT id + 100, val, cnt FROM seq WHERE id < 3 -) -SELECT id, val, cnt FROM seq ORDER BY id; + DEFINE B AS val > PREV(val)); +DROP VIEW rpr_recv; -- ============================================================ -- B8. RPR + Incremental sort -- 2.50.1 (Apple Git-155)