nocfbot-0012-rpr_integration-B7-recursive-CTE-cases.txt

From cda37c3c806c8ee99e7e2060bfab5b4105b728ad Mon Sep 17 00:00:00 2001
From: Henson Choi <assam258@gmail.com>
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 <with list element> shall not contain a <row pattern
+-- measures> or <row pattern common syntax>.  Per 3)e), every
+-- <with list element> 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 <with list element> shall not contain a <row pattern
+-- measures> or <row pattern common syntax>.  Per 3)e), every
+-- <with list element> 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)