nocfbot-0011-Normalize-SQL-RPR-standard-references.txt
text/plain
Filename: nocfbot-0011-Normalize-SQL-RPR-standard-references.txt
Type: text/plain
Part: 10
Message:
Re: Row pattern recognition
From 03b98374a3adc253e81e27eb7f7da268b86664bf Mon Sep 17 00:00:00 2001
From: Henson Choi <assam258@gmail.com>
Date: Sat, 9 May 2026 13:47:49 +0900
Subject: [PATCH 11/26] Normalize SQL/RPR standard references
Make every reference cite ISO/IEC 19075-5 explicitly across RPR code
and regress tests. Prefix bare "19075-5" / "SQL standard" forms,
pin STR06 to its source (7.2.8), and where a clause is mirrored in
both Chapter 4 (FROM) and Chapter 6 (WINDOW), cite the Chapter 6
subclause first because this implementation targets Feature R020.
Document the citation policy in README.rpr.
---
src/backend/executor/README.rpr | 8 +++++++-
src/backend/executor/execRPR.c | 5 +++--
src/backend/optimizer/plan/rpr.c | 8 ++++----
src/backend/parser/parse_expr.c | 11 ++++++-----
src/backend/parser/parse_rpr.c | 2 +-
src/test/regress/expected/rpr_base.out | 6 +++---
src/test/regress/expected/rpr_explain.out | 2 +-
src/test/regress/expected/rpr_integration.out | 4 ++--
src/test/regress/expected/rpr_nfa.out | 4 ++--
src/test/regress/sql/rpr_base.sql | 6 +++---
src/test/regress/sql/rpr_explain.sql | 2 +-
src/test/regress/sql/rpr_integration.sql | 4 ++--
src/test/regress/sql/rpr_nfa.sql | 4 ++--
13 files changed, 37 insertions(+), 29 deletions(-)
diff --git a/src/backend/executor/README.rpr b/src/backend/executor/README.rpr
index 52bcd77390c..e64efe0c7fc 100644
--- a/src/backend/executor/README.rpr
+++ b/src/backend/executor/README.rpr
@@ -38,6 +38,12 @@ What is a Flat-Array Stream NFA?
Chapter I Row Pattern Recognition Overview
============================================================================
+Normative reference: ISO/IEC 19075-5 (SQL Technical Report, Part 5: Row
+pattern recognition in SQL). Subclause numbers cited throughout this code
+base refer to that document. Where Chapters 4 (FROM clause) and 6 (WINDOW
+clause) describe parallel material, this implementation cites the Chapter 6
+subclause first because it targets Feature R020.
+
Row Pattern Recognition (hereafter RPR) is a feature introduced in SQL:2016
that matches regex-based patterns against ordered row sets.
@@ -1033,7 +1039,7 @@ match:
X-3. INITIAL vs SEEK
- Standard definition (section 6.12):
+ Standard definition (ISO/IEC 19075-5 6.12):
INITIAL: "is used to look for a match whose first row is R."
SEEK: "is used to permit a search for the first match anywhere
from R through the end of the full window frame."
diff --git a/src/backend/executor/execRPR.c b/src/backend/executor/execRPR.c
index 1e6196d6960..e1caa7bb528 100644
--- a/src/backend/executor/execRPR.c
+++ b/src/backend/executor/execRPR.c
@@ -736,8 +736,9 @@ nfa_absorb_contexts(WindowAggState *winstate)
* once per row by evaluating all DEFINE expressions. NULL means no DEFINE
* clauses exist (only possible during early development/testing).
*
- * Per SQL:2016 R020, pattern variables not listed in DEFINE are implicitly
- * TRUE -- they match every row. This is checked via varId >= list_length.
+ * Per ISO/IEC 19075-5 Feature R020, pattern variables not listed in DEFINE
+ * are implicitly TRUE -- they match every row. This is checked via
+ * varId >= list_length.
*/
static bool
nfa_eval_var_match(WindowAggState *winstate, RPRPatternElement *elem,
diff --git a/src/backend/optimizer/plan/rpr.c b/src/backend/optimizer/plan/rpr.c
index ed8b6c3414c..c65681463b3 100644
--- a/src/backend/optimizer/plan/rpr.c
+++ b/src/backend/optimizer/plan/rpr.c
@@ -1050,10 +1050,10 @@ scanRPRPatternRecursive(RPRPatternNode *node, char **varNames, int *numVars,
}
/*
- * Variable not in DEFINE clause - this is valid per SQL standard.
- * Such variables are implicitly TRUE. Add to varNames so they get
- * a varId >= defineVariableList length, which executor treats as
- * TRUE.
+ * Variable not in DEFINE clause - this is valid per ISO/IEC
+ * 19075-5 Feature R020. Such variables are implicitly TRUE. Add
+ * to varNames so they get a varId >= defineVariableList length,
+ * which executor treats as TRUE.
*/
Assert(*numVars < RPR_VARID_MAX);
varNames[(*numVars)++] = node->varName;
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 10edccb8afb..78abdc88f86 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -631,11 +631,12 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref)
/*----------
* Qualified references in DEFINE need a tri-classification:
*
- * pattern variable qualifier (e.g. UP.price): valid per 19075-5 4.16
- * but not yet implemented -- raise FEATURE_NOT_SUPPORTED.
+ * pattern variable qualifier (e.g. UP.price): valid per
+ * ISO/IEC 19075-5 6.15 / 4.16 but not yet implemented --
+ * raise FEATURE_NOT_SUPPORTED.
*
- * FROM-clause range variable qualifier: prohibited by 19075-5 6.5
- * -- raise SYNTAX_ERROR.
+ * FROM-clause range variable qualifier: prohibited by
+ * ISO/IEC 19075-5 6.5 -- raise SYNTAX_ERROR.
*
* any other qualifier (typo, undefined name): fall through and let
* normal column resolution produce a sensible error.
@@ -1946,7 +1947,7 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
break;
/*----------
- * XXX SQL/RPR (19075-5 4.18.4 / 6.17.4; R010 / R020)
+ * XXX SQL/RPR (ISO/IEC 19075-5 6.17.4 / 4.18.4; R020 / R010)
* permits a subquery nested in a DEFINE expression provided
* that:
* (a) the subquery does not itself perform row pattern
diff --git a/src/backend/parser/parse_rpr.c b/src/backend/parser/parse_rpr.c
index bba887f17ce..d2ed6c14811 100644
--- a/src/backend/parser/parse_rpr.c
+++ b/src/backend/parser/parse_rpr.c
@@ -467,7 +467,7 @@ transformDefineClause(ParseState *pstate, WindowClause *wc, WindowDef *windef,
* (RPR's NFA may evaluate the same row's predicate multiple times
* during backtracking, so a volatile result would make matching
* non-deterministic).
- * - For each outer RPRNavExpr (per SQL 5.6.4 nesting rules):
+ * - For each outer RPRNavExpr (per ISO/IEC 19075-5 5.6.4 nesting rules):
* * arg must contain at least one column reference
* * PREV/NEXT wrapping FIRST/LAST flattens to a compound kind
* * Other nestings are rejected (FIRST(PREV()), PREV(PREV()), ...)
diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out
index 86abb96c177..cfd2645bbed 100644
--- a/src/test/regress/expected/rpr_base.out
+++ b/src/test/regress/expected/rpr_base.out
@@ -1,6 +1,6 @@
-- ============================================================
-- RPR Base Tests
--- Tests for Row Pattern Recognition (ISO/IEC 19075-5:2016)
+-- Tests for Row Pattern Recognition (ISO/IEC 19075-5)
-- ============================================================
--
-- Parser Layer:
@@ -3065,7 +3065,7 @@ LINE 6: DEFINE A AS val > 0
^
-- Expected: Syntax error
-- Qualified column references (NOT SUPPORTED)
--- Pattern variable qualified name: not supported (valid per SQL standard 4.16, not yet implemented)
+-- Pattern variable qualified name: not supported (valid per ISO/IEC 19075-5 6.15 / 4.16, not yet implemented)
SELECT COUNT(*) OVER w
FROM rpr_err
WINDOW w AS (
@@ -3104,7 +3104,7 @@ ERROR: DEFINE variable "b" is not used in PATTERN
LINE 7: DEFINE A AS val > 0, B AS B.val > 0
^
-- Expected: ERROR: pattern variable qualified expression "b.val" is not supported
--- FROM-clause range variable qualified name: not allowed (prohibited by SQL standard 6.5)
+-- FROM-clause range variable qualified name: not allowed (prohibited by ISO/IEC 19075-5 6.5)
SELECT COUNT(*) OVER w
FROM rpr_err
WINDOW w AS (
diff --git a/src/test/regress/expected/rpr_explain.out b/src/test/regress/expected/rpr_explain.out
index c4516d3c756..77079d5e8c9 100644
--- a/src/test/regress/expected/rpr_explain.out
+++ b/src/test/regress/expected/rpr_explain.out
@@ -2185,7 +2185,7 @@ WINDOW w AS (
-> Function Scan on generate_series s (actual rows=3.00 loops=1)
(9 rows)
--- (A?){2,3}: min=2 (SQL:2016 STR06 = STRE STRE) -> 3 length-0 matches
+-- (A?){2,3}: min=2 (ISO/IEC 19075-5 7.2.8 STR06 = STRE STRE) -> 3 length-0 matches
CREATE VIEW rpr_ev_edge_empty_match_min2 AS
SELECT count(*) OVER w
FROM generate_series(1, 3) AS s(v)
diff --git a/src/test/regress/expected/rpr_integration.out b/src/test/regress/expected/rpr_integration.out
index 905bd3538de..7cbeed3347e 100644
--- a/src/test/regress/expected/rpr_integration.out
+++ b/src/test/regress/expected/rpr_integration.out
@@ -1278,8 +1278,8 @@ ORDER BY o.id, r.id;
-- PostgreSQL restriction, so this is the natural place to exercise
-- "RPR under Recursive Union").
--
--- XXX: Whether this case falls under the ISO/IEC 9075-2 4.18.5 /
--- 6.17.5 prohibition is not something I can judge. If this case
+-- 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.
diff --git a/src/test/regress/expected/rpr_nfa.out b/src/test/regress/expected/rpr_nfa.out
index 4cff7cfbbd7..fe5bb324df0 100644
--- a/src/test/regress/expected/rpr_nfa.out
+++ b/src/test/regress/expected/rpr_nfa.out
@@ -4083,7 +4083,7 @@ WINDOW w AS (
-- ============================================================
-- Standard Clause 7: Formal Pattern Matching Rules
--- ISO/IEC 19075-5:2021, Clause 7
+-- ISO/IEC 19075-5, Clause 7
-- ============================================================
-- ------------------------------------------------------------
-- 7.2.2 Alternation: first alternative is preferred
@@ -4453,7 +4453,7 @@ WINDOW w AS (
3 | {B} | |
(3 rows)
--- (A?){2,3}: min=2, nullable inner. Per SQL:2016 STR06 = (STRE STRE)
+-- (A?){2,3}: min=2, nullable inner. Per ISO/IEC 19075-5 7.2.8 STR06 = (STRE STRE)
-- is valid: two empty iterations satisfy min=2.
-- NFA reports 3 length-0 matches; first/last_value NULL over empty frame.
WITH test_728_min2 AS (
diff --git a/src/test/regress/sql/rpr_base.sql b/src/test/regress/sql/rpr_base.sql
index e8c72706720..fd289d7cf67 100644
--- a/src/test/regress/sql/rpr_base.sql
+++ b/src/test/regress/sql/rpr_base.sql
@@ -1,6 +1,6 @@
-- ============================================================
-- RPR Base Tests
--- Tests for Row Pattern Recognition (ISO/IEC 19075-5:2016)
+-- Tests for Row Pattern Recognition (ISO/IEC 19075-5)
-- ============================================================
--
-- Parser Layer:
@@ -2083,7 +2083,7 @@ WINDOW w AS (
-- Qualified column references (NOT SUPPORTED)
--- Pattern variable qualified name: not supported (valid per SQL standard 4.16, not yet implemented)
+-- Pattern variable qualified name: not supported (valid per ISO/IEC 19075-5 6.15 / 4.16, not yet implemented)
SELECT COUNT(*) OVER w
FROM rpr_err
WINDOW w AS (
@@ -2116,7 +2116,7 @@ WINDOW w AS (
);
-- Expected: ERROR: pattern variable qualified expression "b.val" is not supported
--- FROM-clause range variable qualified name: not allowed (prohibited by SQL standard 6.5)
+-- FROM-clause range variable qualified name: not allowed (prohibited by ISO/IEC 19075-5 6.5)
SELECT COUNT(*) OVER w
FROM rpr_err
WINDOW w AS (
diff --git a/src/test/regress/sql/rpr_explain.sql b/src/test/regress/sql/rpr_explain.sql
index d339a80a673..a527615849a 100644
--- a/src/test/regress/sql/rpr_explain.sql
+++ b/src/test/regress/sql/rpr_explain.sql
@@ -1228,7 +1228,7 @@ WINDOW w AS (
DEFINE A AS FALSE
);');
--- (A?){2,3}: min=2 (SQL:2016 STR06 = STRE STRE) -> 3 length-0 matches
+-- (A?){2,3}: min=2 (ISO/IEC 19075-5 7.2.8 STR06 = STRE STRE) -> 3 length-0 matches
CREATE VIEW rpr_ev_edge_empty_match_min2 AS
SELECT count(*) OVER w
FROM generate_series(1, 3) AS s(v)
diff --git a/src/test/regress/sql/rpr_integration.sql b/src/test/regress/sql/rpr_integration.sql
index 29b2db2f7bb..f4267c74645 100644
--- a/src/test/regress/sql/rpr_integration.sql
+++ b/src/test/regress/sql/rpr_integration.sql
@@ -792,8 +792,8 @@ ORDER BY o.id, r.id;
-- PostgreSQL restriction, so this is the natural place to exercise
-- "RPR under Recursive Union").
--
--- XXX: Whether this case falls under the ISO/IEC 9075-2 4.18.5 /
--- 6.17.5 prohibition is not something I can judge. If this case
+-- 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.
diff --git a/src/test/regress/sql/rpr_nfa.sql b/src/test/regress/sql/rpr_nfa.sql
index 29ec4a9dacb..7a5b5c41b24 100644
--- a/src/test/regress/sql/rpr_nfa.sql
+++ b/src/test/regress/sql/rpr_nfa.sql
@@ -2976,7 +2976,7 @@ WINDOW w AS (
-- ============================================================
-- Standard Clause 7: Formal Pattern Matching Rules
--- ISO/IEC 19075-5:2021, Clause 7
+-- ISO/IEC 19075-5, Clause 7
-- ============================================================
-- ------------------------------------------------------------
@@ -3280,7 +3280,7 @@ WINDOW w AS (
A AS 'A' = ANY(flags)
);
--- (A?){2,3}: min=2, nullable inner. Per SQL:2016 STR06 = (STRE STRE)
+-- (A?){2,3}: min=2, nullable inner. Per ISO/IEC 19075-5 7.2.8 STR06 = (STRE STRE)
-- is valid: two empty iterations satisfy min=2.
-- NFA reports 3 length-0 matches; first/last_value NULL over empty frame.
WITH test_728_min2 AS (
--
2.50.1 (Apple Git-155)