From bd0f11a6a80004168ff0aef512da58d0f3fe137a Mon Sep 17 00:00:00 2001 From: Henson Choi Date: Sat, 9 May 2026 13:47:49 +0900 Subject: [PATCH 11/11] 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 58ebd7d24b8..228d3b063db 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)