From 5221784ac469bb213fae2853beab7f5b2b4562cd Mon Sep 17 00:00:00 2001 From: Henson Choi Date: Mon, 8 Jun 2026 16:22:16 +0900 Subject: [PATCH 66/68] Tidy up row pattern recognition regression test comments Drop the "-- Expected: ERROR: ..." comments that repeat exact error text (they go stale; the .out already has the real error) and reword comments that embedded literal limits. Rename the "Jacob's Patterns" section and clarify the serialization section's deparse/re-parse round-trip intent. Test comments only; rpr_base.out regenerated, no output changes. --- src/test/regress/expected/rpr_base.out | 169 +++++++---------------- src/test/regress/sql/rpr_base.sql | 184 +++++++------------------ 2 files changed, 102 insertions(+), 251 deletions(-) diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out index 1fcb2ce22f0..f1767305d06 100644 --- a/src/test/regress/expected/rpr_base.out +++ b/src/test/regress/expected/rpr_base.out @@ -12,7 +12,7 @@ -- Quantifiers Tests -- Navigation Functions Tests -- SKIP TO / INITIAL Tests --- Serialization/Deserialization Tests (objects kept for pg_upgrade/pg_dump) +-- Serialization/Deserialization Tests -- Glued Quantifier / Alternation Tests -- Error Cases Tests -- Window Deduplication Tests @@ -34,7 +34,7 @@ -- Error Limit Tests -- -- Contributed Tests: --- Jacob's Patterns +-- Basic Pattern Matching -- Pathological Patterns -- ============================================================ SET client_min_messages = WARNING; @@ -50,6 +50,7 @@ CREATE TABLE rpr_keywords ( past INT, -- PAST keyword pattern INT, -- PATTERN keyword seek INT, -- SEEK keyword +-- ERROR: SEEK is not supported skip INT -- SKIP keyword (pre-existing) ); INSERT INTO rpr_keywords VALUES (1, 10, 20, 30, 40, 50, 60); @@ -224,7 +225,7 @@ DROP TABLE rpr_auto; -- Duplicate variable names CREATE TABLE rpr_dup (id INT); INSERT INTO rpr_dup VALUES (1), (2); --- Duplicate DEFINE entries +-- Duplicate DEFINE variable name is not allowed SELECT COUNT(*) OVER w FROM rpr_dup WINDOW w AS ( @@ -236,12 +237,11 @@ WINDOW w AS ( ERROR: DEFINE variable "a" appears more than once LINE 7: DEFINE A AS id > 0, A AS id < 10 ^ --- Expected: ERROR: row pattern definition variable name "a" appears more than once in DEFINE clause DROP TABLE rpr_dup; -- Boolean coercion CREATE TABLE rpr_bool (id INT, flag BOOLEAN); INSERT INTO rpr_bool VALUES (1, true), (2, false); --- Non-boolean expression +-- DEFINE clause must be a boolean expression SELECT COUNT(*) OVER w FROM rpr_bool WINDOW w AS ( @@ -253,7 +253,6 @@ WINDOW w AS ( ERROR: argument of DEFINE must be type boolean, not type integer LINE 7: DEFINE A AS id ^ --- Expected: ERROR: argument of DEFINE must be type boolean -- Boolean column reference SELECT id, flag, COUNT(*) OVER w as cnt FROM rpr_bool @@ -393,8 +392,7 @@ ORDER BY id; 6 | 30 | 1 (6 rows) --- Invalid frame start positions --- Not starting at CURRENT ROW +-- ERROR: frame must start at current row when row pattern recognition is used SELECT COUNT(*) OVER w FROM rpr_frame WINDOW w AS ( @@ -408,7 +406,6 @@ LINE 5: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ^ DETAIL: Current frame starts with UNBOUNDED PRECEDING. HINT: Use: ROWS BETWEEN CURRENT ROW AND ... --- Expected: ERROR: FRAME must start at current row when row pattern recognition is used -- EXCLUDE options -- EXCLUDE not permitted SELECT COUNT(*) OVER w @@ -425,7 +422,6 @@ LINE 6: EXCLUDE CURRENT ROW ^ DETAIL: Frame definition includes EXCLUDE CURRENT ROW. HINT: Remove the EXCLUDE clause from the window definition. --- Expected: ERROR: cannot use EXCLUDE options with row pattern recognition -- EXCLUDE GROUP not permitted SELECT COUNT(*) OVER w FROM rpr_frame @@ -441,7 +437,6 @@ LINE 6: EXCLUDE GROUP ^ DETAIL: Frame definition includes EXCLUDE GROUP. HINT: Remove the EXCLUDE clause from the window definition. --- Expected: ERROR: cannot use EXCLUDE options with row pattern recognition -- EXCLUDE TIES not permitted SELECT COUNT(*) OVER w FROM rpr_frame @@ -457,8 +452,7 @@ LINE 6: EXCLUDE TIES ^ DETAIL: Frame definition includes EXCLUDE TIES. HINT: Remove the EXCLUDE clause from the window definition. --- Expected: ERROR: cannot use EXCLUDE options with row pattern recognition --- RANGE frame not starting at CURRENT ROW +-- range frame is not allowed with RPR SELECT COUNT(*) OVER w FROM rpr_frame WINDOW w AS ( @@ -471,8 +465,7 @@ ERROR: cannot use FRAME option RANGE with row pattern recognition LINE 5: RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWIN... ^ HINT: Use ROWS instead. --- Expected: ERROR: cannot use FRAME option RANGE with row pattern recognition --- GROUPS frame not starting at CURRENT ROW +-- GROUPS frame is not allowed with RPR SELECT COUNT(*) OVER w FROM rpr_frame WINDOW w AS ( @@ -485,8 +478,7 @@ ERROR: cannot use FRAME option GROUPS with row pattern recognition LINE 5: GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWI... ^ HINT: Use ROWS instead. --- Expected: ERROR: cannot use FRAME option GROUPS with row pattern recognition --- Starting with N PRECEDING +-- ERROR: frame must start at current row when row pattern recognition is used SELECT COUNT(*) OVER w FROM rpr_frame WINDOW w AS ( @@ -500,8 +492,7 @@ LINE 5: ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING ^ DETAIL: Current frame starts with offset PRECEDING. HINT: Use: ROWS BETWEEN CURRENT ROW AND ... --- Expected: ERROR: FRAME must start at current row when row pattern recognition is used --- Starting with N FOLLOWING +-- ERROR: frame must start at current row with RPR SELECT COUNT(*) OVER w FROM rpr_frame WINDOW w AS ( @@ -515,9 +506,7 @@ LINE 5: ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING ^ DETAIL: Current frame starts with offset FOLLOWING. HINT: Use: ROWS BETWEEN CURRENT ROW AND ... --- Expected: ERROR: FRAME must start at current row when row pattern recognition is used --- Frame end bound edge cases --- End before start: CURRENT ROW AND 1 PRECEDING +-- ERROR: end before start: CURRENT ROW AND 1 PRECEDING SELECT COUNT(*) OVER w FROM rpr_frame WINDOW w AS ( @@ -529,8 +518,7 @@ WINDOW w AS ( ERROR: frame starting from current row cannot have preceding rows LINE 5: ROWS BETWEEN CURRENT ROW AND 1 PRECEDING ^ --- Expected: ERROR: frame starting from current row cannot have preceding rows --- End before start: CURRENT ROW AND UNBOUNDED PRECEDING +-- ERROR: end before start: CURRENT ROW AND UNBOUNDED PRECEDING SELECT COUNT(*) OVER w FROM rpr_frame WINDOW w AS ( @@ -542,7 +530,6 @@ WINDOW w AS ( ERROR: frame end cannot be UNBOUNDED PRECEDING LINE 5: ROWS BETWEEN CURRENT ROW AND UNBOUNDED PRECEDING ^ --- Expected: ERROR: frame end cannot be UNBOUNDED PRECEDING -- Single row frame: CURRENT ROW AND CURRENT ROW is rejected (the standard -- allows only UNBOUNDED FOLLOWING or a positive offset FOLLOWING). SELECT id, val, COUNT(*) OVER w as cnt @@ -559,7 +546,6 @@ ERROR: cannot use CURRENT ROW as frame end with row pattern recognition LINE 5: ROWS BETWEEN CURRENT ROW AND CURRENT ROW ^ HINT: Use UNBOUNDED FOLLOWING or a positive offset FOLLOWING. --- Expected: ERROR: cannot use CURRENT ROW as frame end with row pattern recognition -- Zero offset: CURRENT ROW AND 0 FOLLOWING denotes the same one-row frame -- and is likewise rejected (caught at execution time). SELECT id, val, COUNT(*) OVER w as cnt @@ -573,7 +559,6 @@ WINDOW w AS ( ) ORDER BY id; ERROR: frame ending offset must be positive with row pattern recognition --- Expected: ERROR: frame ending offset must be positive with row pattern recognition -- A non-constant frame end offset is allowed; a zero value is still rejected, -- this time at execution time (a literal cannot exercise that path). PREPARE rpr_end_offset(int8) AS @@ -600,7 +585,6 @@ EXECUTE rpr_end_offset(2); EXECUTE rpr_end_offset(0); ERROR: frame ending offset must be positive with row pattern recognition --- Expected: ERROR: frame ending offset must be positive with row pattern recognition DEALLOCATE rpr_end_offset; -- Large offset: CURRENT ROW AND 1000 FOLLOWING SELECT id, val, COUNT(*) OVER w as cnt @@ -669,7 +653,7 @@ ORDER BY id; 6 | 30 | 1 (6 rows) --- RANGE frame with RPR (not permitted) +-- range frame is not allowed with RPR SELECT id, val, COUNT(*) OVER w as cnt FROM rpr_frame WINDOW w AS ( @@ -684,7 +668,6 @@ ERROR: cannot use FRAME option RANGE with row pattern recognition LINE 5: RANGE BETWEEN CURRENT ROW AND 10 FOLLOWING ^ HINT: Use ROWS instead. --- Expected: ERROR: cannot use FRAME option RANGE with row pattern recognition -- GROUPS frame with RPR (not permitted) SELECT id, val, COUNT(*) OVER w as cnt FROM rpr_frame @@ -700,7 +683,6 @@ ERROR: cannot use FRAME option GROUPS with row pattern recognition LINE 5: GROUPS BETWEEN CURRENT ROW AND 1 FOLLOWING ^ HINT: Use ROWS instead. --- Expected: ERROR: cannot use FRAME option GROUPS with row pattern recognition DROP TABLE rpr_frame; -- ============================================================ -- PARTITION BY + FRAME Tests @@ -749,7 +731,6 @@ ERROR: cannot use FRAME option RANGE with row pattern recognition LINE 6: RANGE BETWEEN CURRENT ROW AND 10 FOLLOWING ^ HINT: Use ROWS instead. --- Expected: ERROR: cannot use FRAME option RANGE with row pattern recognition DROP TABLE rpr_partition; -- ============================================================ -- PATTERN Syntax Tests @@ -1011,7 +992,6 @@ ORDER BY id; ERROR: quantifier bound must be between 1 and 2147483646 LINE 6: PATTERN (A{0} B) ^ --- Expected: ERROR: quantifier bound must be between 1 and 2147483646 -- {0,0} is not allowed (max must be >= 1) SELECT id, val, COUNT(*) OVER w as cnt FROM rpr_quant @@ -1025,7 +1005,6 @@ ORDER BY id; ERROR: quantifier bounds must be between 0 and 2147483646 with max >= 1 LINE 6: PATTERN (A{0,0} B) ^ --- Expected: ERROR: quantifier bounds must be between 0 and 2147483646 with max >= 1 -- {0,1} (equivalent to ?) SELECT id, val, COUNT(*) OVER w as cnt FROM rpr_quant @@ -1155,6 +1134,7 @@ DROP TABLE rpr_quant; CREATE TABLE rpr_reluctant (id INT, val INT); INSERT INTO rpr_reluctant VALUES (1, 10), (2, 20), (3, 30); -- *? (zero or more, reluctant) +-- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1170,8 +1150,8 @@ WINDOW w AS ( 0 (3 rows) --- Reluctant quantifier: prefer shortest match -- +? (one or more, reluctant) +-- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1187,8 +1167,8 @@ WINDOW w AS ( 1 (3 rows) --- Reluctant quantifier: prefer shortest match -- ?? (zero or one, reluctant) +-- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1204,8 +1184,8 @@ WINDOW w AS ( 0 (3 rows) --- Reluctant quantifier: prefer shortest match -- {n,}? (n or more, reluctant) +-- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1221,8 +1201,8 @@ WINDOW w AS ( 0 (3 rows) --- Reluctant quantifier: prefer shortest match -- {n,m}? (n to m, reluctant) +-- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1238,8 +1218,8 @@ WINDOW w AS ( 1 (3 rows) --- Reluctant quantifier: prefer shortest match -- {n}? (exactly n, reluctant) +-- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1255,8 +1235,8 @@ WINDOW w AS ( 0 (3 rows) --- Reluctant quantifier: prefer shortest match -- {,m}? (up to m, reluctant) - COMPLETELY UNTESTED RULE! +-- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1272,8 +1252,6 @@ WINDOW w AS ( 0 (3 rows) --- Reluctant quantifier: prefer shortest match --- Invalid reluctant patterns (wrong token after quantifier) -- {2}+ (should be {2}? not {2}+) SELECT COUNT(*) OVER w FROM rpr_reluctant @@ -1286,7 +1264,6 @@ WINDOW w AS ( ERROR: syntax error at or near "+" LINE 6: PATTERN (A{2}+) ^ --- Expected: ERROR: syntax error at or near "+" -- {2,}* (should be {2,}? not {2,}*) SELECT COUNT(*) OVER w FROM rpr_reluctant @@ -1299,7 +1276,6 @@ WINDOW w AS ( ERROR: syntax error at or near "*" LINE 6: PATTERN (A{2,}*) ^ --- Expected: ERROR: syntax error at or near "*" -- {,3}* (should be {,3}? not {,3}*) SELECT COUNT(*) OVER w FROM rpr_reluctant @@ -1312,7 +1288,6 @@ WINDOW w AS ( ERROR: syntax error at or near "*" LINE 6: PATTERN (A{,3}*) ^ --- Expected: ERROR: syntax error at or near "*" -- {1,3}+ (should be {1,3}? not {1,3}+) SELECT COUNT(*) OVER w FROM rpr_reluctant @@ -1325,9 +1300,8 @@ WINDOW w AS ( ERROR: syntax error at or near "+" LINE 6: PATTERN (A{1,3}+) ^ --- Expected: ERROR: syntax error at or near "+" -- Boundary errors in reluctant quantifiers --- {-1}? (negative bound) +-- negative bound is not allowed SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1339,8 +1313,7 @@ WINDOW w AS ( ERROR: syntax error at or near "-" LINE 6: PATTERN (A{-1}?) ^ --- Expected: ERROR: syntax error at or near "-" --- {2147483647}? (INT_MAX) +-- ERROR: quantifier bound exceeds limits SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1352,8 +1325,7 @@ WINDOW w AS ( ERROR: quantifier bound must be between 1 and 2147483646 LINE 6: PATTERN (A{2147483647}?) ^ --- Expected: ERROR: quantifier bound must be between 1 and 2147483646 --- {-1,}? (negative lower bound) +-- negative lower bound is not allowed SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1365,8 +1337,7 @@ WINDOW w AS ( ERROR: syntax error at or near "-" LINE 6: PATTERN (A{-1,}?) ^ --- Expected: ERROR: syntax error at or near "-" --- {2147483647,}? (INT_MAX lower bound) +-- ERROR: quantifier lower bound exceeds limits SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1378,8 +1349,7 @@ WINDOW w AS ( ERROR: quantifier bound must be between 0 and 2147483646 LINE 6: PATTERN (A{2147483647,}?) ^ --- Expected: ERROR: quantifier bound must be between 0 and 2147483646 --- {,0}? (zero upper bound) +-- zero upper bound is not allowed SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1391,8 +1361,7 @@ WINDOW w AS ( ERROR: quantifier bound must be between 1 and 2147483646 LINE 6: PATTERN (A{,0}?) ^ --- Expected: ERROR: quantifier bound must be between 1 and 2147483646 --- {,2147483647}? (INT_MAX upper bound) +-- ERROR: {,2147483647}? (upper bound in range exceeds limits) SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1404,8 +1373,7 @@ WINDOW w AS ( ERROR: quantifier bound must be between 1 and 2147483646 LINE 6: PATTERN (A{,2147483647}?) ^ --- Expected: ERROR: quantifier bound must be between 1 and 2147483646 --- {-1,3}? (negative lower in range) +-- ERROR: {-1,3}? (negative lower bound in range is not allowed) SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1417,8 +1385,7 @@ WINDOW w AS ( ERROR: syntax error at or near "-" LINE 6: PATTERN (A{-1,3}?) ^ --- Expected: ERROR: syntax error at or near "-" --- {1,2147483647}? (INT_MAX upper in range) +-- ERROR: {1,2147483647}? (upper bound in range exceeds limits) SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1430,8 +1397,7 @@ WINDOW w AS ( ERROR: quantifier bounds must be between 0 and 2147483646 with max >= 1 LINE 6: PATTERN (A{1,2147483647}?) ^ --- Expected: ERROR: quantifier bounds must be between 0 and 2147483646 with max >= 1 --- {5,3}? (min > max) +-- ERROR: {5,3}? (min > max is not allowed) SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1443,10 +1409,10 @@ WINDOW w AS ( ERROR: quantifier minimum bound must not exceed maximum LINE 6: PATTERN (A{5,3}?) ^ --- Expected: ERROR: quantifier minimum bound must not exceed maximum -- Token-separated reluctant quantifiers (space between quantifier and ?) -- These may be tokenized differently by the lexer -- * ? (token separated) +-- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1462,8 +1428,8 @@ WINDOW w AS ( 0 (3 rows) --- Reluctant quantifier: prefer shortest match -- + ? (token separated) +-- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1479,8 +1445,8 @@ WINDOW w AS ( 1 (3 rows) --- Reluctant quantifier: prefer shortest match -- {2,} ? (token separated) +-- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1496,8 +1462,6 @@ WINDOW w AS ( 0 (3 rows) --- Reluctant quantifier: prefer shortest match --- Invalid token combinations -- * + (invalid combination) SELECT COUNT(*) OVER w FROM rpr_reluctant @@ -1510,7 +1474,6 @@ WINDOW w AS ( ERROR: syntax error at or near "+" LINE 6: PATTERN (A* +) ^ --- Expected: ERROR: syntax error at or near "+" -- + * (invalid combination) SELECT COUNT(*) OVER w FROM rpr_reluctant @@ -1523,8 +1486,8 @@ WINDOW w AS ( ERROR: syntax error at or near "*" LINE 6: PATTERN (A+ *) ^ --- Expected: ERROR: syntax error at or near "*" -- ? ? (parsed as ?? reluctant quantifier) +-- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1540,12 +1503,11 @@ WINDOW w AS ( 0 (3 rows) --- Reluctant quantifier: prefer shortest match DROP TABLE rpr_reluctant; -- Quantifier boundary conditions CREATE TABLE rpr_bounds (id INT); INSERT INTO rpr_bounds VALUES (1), (2); --- min > max +-- ERROR: quantifier lower bound must not exceed upper bound SELECT COUNT(*) OVER w FROM rpr_bounds WINDOW w AS ( @@ -1557,7 +1519,6 @@ WINDOW w AS ( ERROR: quantifier minimum bound must not exceed maximum LINE 6: PATTERN (A{5,3}) ^ --- Expected: ERROR: quantifier minimum bound must not exceed maximum -- Large bounds SELECT COUNT(*) OVER w FROM rpr_bounds @@ -1603,7 +1564,7 @@ WINDOW w AS ( 0 (2 rows) --- INT_MAX = 2147483647 (over limit) +-- ERROR: quantifier bound exceeds limits SELECT COUNT(*) OVER w FROM rpr_bounds WINDOW w AS ( @@ -1615,9 +1576,8 @@ WINDOW w AS ( ERROR: quantifier bound must be between 1 and 2147483646 LINE 6: PATTERN (A{2147483647}) ^ --- Expected: ERROR: quantifier bound must be between 1 and 2147483646 -- {n,} boundary errors --- Negative lower bound in {n,} +-- ERROR: negative lower bound in {n,} is not allowed SELECT COUNT(*) OVER w FROM rpr_bounds WINDOW w AS ( @@ -1629,8 +1589,7 @@ WINDOW w AS ( ERROR: syntax error at or near "-" LINE 6: PATTERN (A{-1,}) ^ --- Expected: ERROR: syntax error at or near "-" --- INT_MAX in {n,} +-- ERROR: quantifier lower bound exceeds limits SELECT COUNT(*) OVER w FROM rpr_bounds WINDOW w AS ( @@ -1642,7 +1601,6 @@ WINDOW w AS ( ERROR: quantifier bound must be between 0 and 2147483646 LINE 6: PATTERN (A{2147483647,}) ^ --- Expected: ERROR: quantifier bound must be between 0 and 2147483646 -- {,m} boundary errors -- Zero upper bound in {,m} SELECT COUNT(*) OVER w @@ -1656,8 +1614,7 @@ WINDOW w AS ( ERROR: quantifier bound must be between 1 and 2147483646 LINE 6: PATTERN (A{,0}) ^ --- Expected: ERROR: quantifier bound must be between 1 and 2147483646 --- INT_MAX in {,m} +-- ERROR: quantifier upper bound exceeds limits SELECT COUNT(*) OVER w FROM rpr_bounds WINDOW w AS ( @@ -1669,7 +1626,6 @@ WINDOW w AS ( ERROR: quantifier bound must be between 1 and 2147483646 LINE 6: PATTERN (A{,2147483647}) ^ --- Expected: ERROR: quantifier bound must be between 1 and 2147483646 DROP TABLE rpr_bounds; -- ============================================================ -- Navigation Functions Tests (PREV / NEXT / FIRST / LAST) @@ -1756,7 +1712,6 @@ ORDER BY id; ERROR: cannot use prev outside a DEFINE clause LINE 1: SELECT PREV(id), id, val, COUNT(*) OVER w as cnt ^ --- Expected: ERROR: cannot use prev outside a DEFINE clause -- NEXT function cannot be used other than in DEFINE SELECT NEXT(id), id, val, COUNT(*) OVER w as cnt FROM rpr_nav @@ -1772,7 +1727,6 @@ ORDER BY id; ERROR: cannot use next outside a DEFINE clause LINE 1: SELECT NEXT(id), id, val, COUNT(*) OVER w as cnt ^ --- Expected: ERROR: cannot use next outside a DEFINE clause -- FIRST function - reference match_start row SELECT id, val, COUNT(*) OVER w as cnt FROM rpr_nav @@ -1841,13 +1795,11 @@ SELECT FIRST(id), id, val FROM rpr_nav; ERROR: cannot use first outside a DEFINE clause LINE 1: SELECT FIRST(id), id, val FROM rpr_nav; ^ --- Expected: ERROR: cannot use first outside a DEFINE clause -- LAST function cannot be used other than in DEFINE SELECT LAST(id), id, val FROM rpr_nav; ERROR: cannot use last outside a DEFINE clause LINE 1: SELECT LAST(id), id, val FROM rpr_nav; ^ --- Expected: ERROR: cannot use last outside a DEFINE clause DROP TABLE rpr_nav; -- ============================================================ -- SKIP TO / INITIAL Tests @@ -2033,12 +1985,13 @@ ERROR: SEEK is not supported LINE 6: SEEK ^ HINT: Use INITIAL instead. --- Expected: ERROR: SEEK is not supported --- HINT: Use INITIAL instead. DROP TABLE rpr_seek; -- ============================================================ -- Serialization/Deserialization Tests -- ============================================================ +-- RPR-defining views and tables here are intentionally left in place (not +-- dropped) so that pg_dump/pg_upgrade exercise the deparse-then-re-parse +-- round-trip of the RPR window clause. -- View creation and deparsing CREATE TABLE rpr_serial (id INT, val INT); INSERT INTO rpr_serial VALUES @@ -3317,7 +3270,6 @@ DROP TABLE rpr_glue; DROP TABLE IF EXISTS rpr_err; CREATE TABLE rpr_err (id INT, val INT); INSERT INTO rpr_err VALUES (1, 10), (2, 20); --- Syntax errors -- Invalid quantifier syntax SELECT COUNT(*) OVER w FROM rpr_err @@ -3346,7 +3298,7 @@ EXCEPTION END $$; NOTICE: Unmatched parentheses: EXPECTED ERROR - syntax error at or near "AS" SET client_min_messages = WARNING; --- Empty DEFINE +-- ERROR: empty DEFINE not allowed SELECT COUNT(*) OVER w FROM rpr_err WINDOW w AS ( @@ -3359,7 +3311,7 @@ ERROR: syntax error at or near ")" LINE 8: ); ^ -- Expected: Syntax error --- Empty PATTERN +-- ERROR: empty PATTERN not allowed SELECT COUNT(*) OVER w FROM rpr_err WINDOW w AS ( @@ -3372,7 +3324,7 @@ ERROR: syntax error at or near ")" LINE 6: PATTERN () ^ -- Expected: Syntax error --- DEFINE without PATTERN (PATTERN and DEFINE must be used together) +-- ERROR: DEFINE without PATTERN (PATTERN and DEFINE must be used together) SELECT COUNT(*) OVER w FROM rpr_err WINDOW w AS ( @@ -3397,7 +3349,6 @@ WINDOW w AS ( ERROR: pattern variable qualified expression "a.val" is not supported in DEFINE clause LINE 7: DEFINE A AS A.val > 0 ^ --- Expected: ERROR: pattern variable qualified expression "a.val" is not supported -- PATTERN-only variable qualified name: not supported even without DEFINE entry SELECT COUNT(*) OVER w FROM rpr_err @@ -3410,7 +3361,6 @@ WINDOW w AS ( ERROR: pattern variable qualified expression "b.val" is not supported in DEFINE clause LINE 7: DEFINE A AS B.val > 0 ^ --- Expected: ERROR: pattern variable qualified expression "b.val" is not supported -- DEFINE-only variable qualified name: still a pattern variable, not a range variable SELECT COUNT(*) OVER w FROM rpr_err @@ -3423,7 +3373,6 @@ WINDOW w AS ( 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 ISO/IEC 19075-5 6.5) SELECT COUNT(*) OVER w FROM rpr_err @@ -3436,7 +3385,6 @@ WINDOW w AS ( ERROR: range variable qualified expression "rpr_err.val" is not allowed in DEFINE clause LINE 7: DEFINE A AS rpr_err.val > 0 ^ --- Expected: ERROR: range variable qualified expression "rpr_err.val" is not allowed -- Unknown qualifier (neither pattern var nor range var): the DEFINE pre-check -- must fall through so that normal column resolution produces a sensible error. SELECT COUNT(*) OVER w @@ -3450,7 +3398,6 @@ WINDOW w AS ( ERROR: missing FROM-clause entry for table "nosuch" LINE 7: DEFINE A AS nosuch.val > 0 ^ --- Expected: ERROR: missing FROM-clause entry for table "nosuch" -- Unqualified composite field access in DEFINE works: no qualifier means no -- pattern/range-var navigation, so the pre-check skips and normal resolution -- handles "(items).amount" via A_Indirection on the current row. @@ -3488,7 +3435,6 @@ WINDOW w AS ( ERROR: pattern variable qualified expression "a.items" is not supported in DEFINE clause LINE 7: DEFINE A AS (A.items).amount > 10 ^ --- Expected: ERROR: pattern variable qualified expression "a.items" is not supported SELECT COUNT(*) OVER w FROM rpr_composite WINDOW w AS ( @@ -3500,11 +3446,9 @@ WINDOW w AS ( ERROR: range variable qualified expression "rpr_composite.items" is not allowed in DEFINE clause LINE 7: DEFINE A AS (rpr_composite.items).amount > 10 ^ --- Expected: ERROR: range variable qualified expression "rpr_composite.items" is not allowed DROP TABLE rpr_composite; DROP TYPE rpr_item; --- Semantic errors --- Undefined column in DEFINE +-- ERROR: undefined column in DEFINE SELECT COUNT(*) OVER w FROM rpr_err WINDOW w AS ( @@ -3516,8 +3460,7 @@ WINDOW w AS ( ERROR: column "nonexistent_column" does not exist LINE 7: DEFINE A AS nonexistent_column > 0 ^ --- Expected: ERROR: column "nonexistent_column" does not exist --- Type mismatch +-- ERROR: type mismatch SELECT COUNT(*) OVER w FROM rpr_err WINDOW w AS ( @@ -3529,8 +3472,7 @@ WINDOW w AS ( ERROR: invalid input syntax for type integer: "string" LINE 7: DEFINE A AS val > 'string' ^ --- Expected: ERROR: invalid input syntax for type integer: "string" --- Aggregate function in DEFINE (if not allowed) +-- ERROR: aggregate function in DEFINE is not supported SELECT COUNT(*) OVER w FROM rpr_err WINDOW w AS ( @@ -3542,8 +3484,7 @@ WINDOW w AS ( ERROR: aggregate functions are not allowed in DEFINE LINE 7: DEFINE A AS COUNT(*) > 0 ^ --- Expected: ERROR: aggregate functions are not allowed in DEFINE --- Subquery in DEFINE (NOT SUPPORTED) +-- Subquery in DEFINE is not supported SELECT COUNT(*) OVER w FROM rpr_err WINDOW w AS ( @@ -3555,8 +3496,6 @@ WINDOW w AS ( ERROR: cannot use subquery in DEFINE expression LINE 7: DEFINE A AS val > (SELECT max(val) FROM rpr_err) ^ --- Expected: ERROR: cannot use subquery in DEFINE expression --- Edge cases -- Pattern variable not used (should work, extra vars ignored) SELECT id, val, COUNT(*) OVER w as cnt FROM rpr_err @@ -5703,7 +5642,6 @@ WINDOW w AS ( ERROR: quantifier bounds must be between 0 and 2147483646 with max >= 1 LINE 6: PATTERN ((A{2000000000,2147483647}){2}) ^ --- Expected: ERROR at parse time before optimization -- Test: nested unbounded with large min causes overflow fallback EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_fallback @@ -5947,7 +5885,6 @@ ORDER BY category; ERROR: syntax error at or near "GROUP" LINE 12: GROUP BY category ^ --- Expected: ERROR: syntax error at or near "GROUP" -- (GROUP BY after WINDOW clause is not valid SQL syntax) -- ============================================================ -- Subquery and CTE Tests @@ -6427,7 +6364,6 @@ INSERT INTO rpr_sort VALUES (1, 'A', 30), (2, 'B', 20), (3, 'A', 10), (4, 'B', 40), (5, 'A', 50), (6, 'B', 60); -- RPR with GROUP BY (aggregate in DEFINE -> ERROR before GROUP BY interaction) --- Expected: ERROR: aggregate functions are not allowed in DEFINE SELECT category, COUNT(*) as group_cnt, MAX(val) as max_val, @@ -6445,7 +6381,6 @@ ERROR: aggregate functions are not allowed in DEFINE LINE 11: DEFINE A AS COUNT(*) > 0 ^ -- RPR with HAVING (same aggregate-in-DEFINE error) --- Expected: ERROR: aggregate functions are not allowed in DEFINE SELECT category, COUNT(*) as group_cnt, COUNT(*) OVER w as window_cnt @@ -6758,7 +6693,7 @@ WINDOW w AS ( (2 rows) -- Expected: Success - exactly at RPR_VARID_MAX boundary --- Test: 241 variables in PATTERN, 240 in DEFINE (exceeds limit with implicit TRUE) +-- ERROR: 241 variables in PATTERN, 240 in DEFINE (exceeds limit with implicit TRUE) SELECT COUNT(*) OVER w FROM rpr_errors WINDOW w AS ( ORDER BY id @@ -6794,7 +6729,6 @@ ERROR: too many pattern variables LINE 5: ...V230 V231 V232 V233 V234 V235 V236 V237 V238 V239 V240 V241) ^ DETAIL: Maximum is 240. --- Expected: ERROR - too many pattern variables (Maximum is 240) -- Test: Pattern nesting at maximum depth (depth 253) -- Note: 253 nested GROUP{3,7}? quantifiers; reluctant quantifiers are not -- subject to quantifier multiplication, so the nesting (and depth 253) is @@ -6826,12 +6760,10 @@ WINDOW w AS ( ); ERROR: pattern nesting too deep DETAIL: Pattern nesting depth 254 exceeds maximum 253. --- Expected: ERROR - pattern nesting too deep DROP TABLE rpr_errors; -- ============================================================ --- Jacob's Patterns +-- Basic Pattern Matching -- ============================================================ --- Basic pattern matching tests from jacob branch -- Test: A? (optional, greedy) SELECT id, val, count(*) OVER w AS c FROM rpr_plan @@ -7194,3 +7126,4 @@ FROM (SELECT id, val, (2 rows) DROP TABLE rpr_plan; +RESET client_min_messages; diff --git a/src/test/regress/sql/rpr_base.sql b/src/test/regress/sql/rpr_base.sql index cc79843aeb7..7dc13232d89 100644 --- a/src/test/regress/sql/rpr_base.sql +++ b/src/test/regress/sql/rpr_base.sql @@ -12,7 +12,7 @@ -- Quantifiers Tests -- Navigation Functions Tests -- SKIP TO / INITIAL Tests --- Serialization/Deserialization Tests (objects kept for pg_upgrade/pg_dump) +-- Serialization/Deserialization Tests -- Glued Quantifier / Alternation Tests -- Error Cases Tests -- Window Deduplication Tests @@ -34,7 +34,7 @@ -- Error Limit Tests -- -- Contributed Tests: --- Jacob's Patterns +-- Basic Pattern Matching -- Pathological Patterns -- ============================================================ @@ -54,6 +54,7 @@ CREATE TABLE rpr_keywords ( past INT, -- PAST keyword pattern INT, -- PATTERN keyword seek INT, -- SEEK keyword +-- ERROR: SEEK is not supported skip INT -- SKIP keyword (pre-existing) ); @@ -69,7 +70,6 @@ DROP TABLE rpr_keywords; -- DEFINE Clause Tests -- ============================================================ - -- Simple column references CREATE TABLE stock_price ( dt DATE, @@ -182,7 +182,7 @@ DROP TABLE rpr_auto; CREATE TABLE rpr_dup (id INT); INSERT INTO rpr_dup VALUES (1), (2); --- Duplicate DEFINE entries +-- Duplicate DEFINE variable name is not allowed SELECT COUNT(*) OVER w FROM rpr_dup WINDOW w AS ( @@ -191,7 +191,6 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS id > 0, A AS id < 10 ); --- Expected: ERROR: row pattern definition variable name "a" appears more than once in DEFINE clause DROP TABLE rpr_dup; @@ -199,7 +198,7 @@ DROP TABLE rpr_dup; CREATE TABLE rpr_bool (id INT, flag BOOLEAN); INSERT INTO rpr_bool VALUES (1, true), (2, false); --- Non-boolean expression +-- DEFINE clause must be a boolean expression SELECT COUNT(*) OVER w FROM rpr_bool WINDOW w AS ( @@ -208,7 +207,6 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS id ); --- Expected: ERROR: argument of DEFINE must be type boolean -- Boolean column reference SELECT id, flag, COUNT(*) OVER w as cnt @@ -301,7 +299,6 @@ DROP TABLE rpr_unused; -- FRAME Options Tests -- ============================================================ - CREATE TABLE rpr_frame (id INT, val INT); INSERT INTO rpr_frame VALUES (1, 10), (2, 10), (3, 10), -- Same val: 10 @@ -327,9 +324,7 @@ WINDOW w AS ( ) ORDER BY id; --- Invalid frame start positions - --- Not starting at CURRENT ROW +-- ERROR: frame must start at current row when row pattern recognition is used SELECT COUNT(*) OVER w FROM rpr_frame WINDOW w AS ( @@ -338,7 +333,6 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS val > 0 ); --- Expected: ERROR: FRAME must start at current row when row pattern recognition is used -- EXCLUDE options @@ -352,7 +346,6 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS val > 0 ); --- Expected: ERROR: cannot use EXCLUDE options with row pattern recognition -- EXCLUDE GROUP not permitted SELECT COUNT(*) OVER w @@ -364,7 +357,6 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS val > 0 ); --- Expected: ERROR: cannot use EXCLUDE options with row pattern recognition -- EXCLUDE TIES not permitted SELECT COUNT(*) OVER w @@ -376,9 +368,8 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS val > 0 ); --- Expected: ERROR: cannot use EXCLUDE options with row pattern recognition --- RANGE frame not starting at CURRENT ROW +-- range frame is not allowed with RPR SELECT COUNT(*) OVER w FROM rpr_frame WINDOW w AS ( @@ -387,9 +378,8 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS val > 0 ); --- Expected: ERROR: cannot use FRAME option RANGE with row pattern recognition --- GROUPS frame not starting at CURRENT ROW +-- GROUPS frame is not allowed with RPR SELECT COUNT(*) OVER w FROM rpr_frame WINDOW w AS ( @@ -398,9 +388,8 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS val > 0 ); --- Expected: ERROR: cannot use FRAME option GROUPS with row pattern recognition --- Starting with N PRECEDING +-- ERROR: frame must start at current row when row pattern recognition is used SELECT COUNT(*) OVER w FROM rpr_frame WINDOW w AS ( @@ -409,9 +398,8 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS val > 0 ); --- Expected: ERROR: FRAME must start at current row when row pattern recognition is used --- Starting with N FOLLOWING +-- ERROR: frame must start at current row with RPR SELECT COUNT(*) OVER w FROM rpr_frame WINDOW w AS ( @@ -420,11 +408,8 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS val > 0 ); --- Expected: ERROR: FRAME must start at current row when row pattern recognition is used --- Frame end bound edge cases - --- End before start: CURRENT ROW AND 1 PRECEDING +-- ERROR: end before start: CURRENT ROW AND 1 PRECEDING SELECT COUNT(*) OVER w FROM rpr_frame WINDOW w AS ( @@ -433,9 +418,8 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS val > 0 ); --- Expected: ERROR: frame starting from current row cannot have preceding rows --- End before start: CURRENT ROW AND UNBOUNDED PRECEDING +-- ERROR: end before start: CURRENT ROW AND UNBOUNDED PRECEDING SELECT COUNT(*) OVER w FROM rpr_frame WINDOW w AS ( @@ -444,7 +428,6 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS val > 0 ); --- Expected: ERROR: frame end cannot be UNBOUNDED PRECEDING -- Single row frame: CURRENT ROW AND CURRENT ROW is rejected (the standard -- allows only UNBOUNDED FOLLOWING or a positive offset FOLLOWING). @@ -458,7 +441,6 @@ WINDOW w AS ( DEFINE A AS val > 0 ) ORDER BY id; --- Expected: ERROR: cannot use CURRENT ROW as frame end with row pattern recognition -- Zero offset: CURRENT ROW AND 0 FOLLOWING denotes the same one-row frame -- and is likewise rejected (caught at execution time). @@ -472,7 +454,6 @@ WINDOW w AS ( DEFINE A AS val > 0 ) ORDER BY id; --- Expected: ERROR: frame ending offset must be positive with row pattern recognition -- A non-constant frame end offset is allowed; a zero value is still rejected, -- this time at execution time (a literal cannot exercise that path). @@ -489,7 +470,6 @@ WINDOW w AS ( ORDER BY id; EXECUTE rpr_end_offset(2); EXECUTE rpr_end_offset(0); --- Expected: ERROR: frame ending offset must be positive with row pattern recognition DEALLOCATE rpr_end_offset; -- Large offset: CURRENT ROW AND 1000 FOLLOWING @@ -532,7 +512,7 @@ WINDOW w AS ( ) ORDER BY id; --- RANGE frame with RPR (not permitted) +-- range frame is not allowed with RPR SELECT id, val, COUNT(*) OVER w as cnt FROM rpr_frame WINDOW w AS ( @@ -543,7 +523,6 @@ WINDOW w AS ( DEFINE A AS val >= 0, B AS val >= 0 ) ORDER BY id; --- Expected: ERROR: cannot use FRAME option RANGE with row pattern recognition -- GROUPS frame with RPR (not permitted) SELECT id, val, COUNT(*) OVER w as cnt @@ -556,7 +535,6 @@ WINDOW w AS ( DEFINE A AS val >= 0, B AS val >= 0 ) ORDER BY id; --- Expected: ERROR: cannot use FRAME option GROUPS with row pattern recognition DROP TABLE rpr_frame; @@ -596,7 +574,6 @@ WINDOW w AS ( DEFINE A AS val >= 10, B AS val >= 20 ) ORDER BY id; --- Expected: ERROR: cannot use FRAME option RANGE with row pattern recognition DROP TABLE rpr_partition; @@ -604,7 +581,6 @@ DROP TABLE rpr_partition; -- PATTERN Syntax Tests -- ============================================================ - CREATE TABLE rpr_pattern (id INT, val INT); INSERT INTO rpr_pattern VALUES (1, 5), (2, 10), (3, 15), (4, 20), (5, 25), @@ -700,7 +676,6 @@ DROP TABLE rpr_pattern; -- Quantifiers Tests -- ============================================================ - CREATE TABLE rpr_quant (id INT, val INT); INSERT INTO rpr_quant VALUES (1, 10), (2, 20), (3, 30), (4, 40), (5, 50), @@ -753,7 +728,6 @@ WINDOW w AS ( DEFINE A AS val > 1000, B AS val > 0 ) ORDER BY id; --- Expected: ERROR: quantifier bound must be between 1 and 2147483646 -- {0,0} is not allowed (max must be >= 1) SELECT id, val, COUNT(*) OVER w as cnt @@ -765,7 +739,6 @@ WINDOW w AS ( DEFINE A AS val > 1000, B AS val > 0 ) ORDER BY id; --- Expected: ERROR: quantifier bounds must be between 0 and 2147483646 with max >= 1 -- {0,1} (equivalent to ?) SELECT id, val, COUNT(*) OVER w as cnt @@ -837,6 +810,7 @@ CREATE TABLE rpr_reluctant (id INT, val INT); INSERT INTO rpr_reluctant VALUES (1, 10), (2, 20), (3, 30); -- *? (zero or more, reluctant) +-- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -845,9 +819,9 @@ WINDOW w AS ( PATTERN (A*?) DEFINE A AS val > 0 ); --- Reluctant quantifier: prefer shortest match -- +? (one or more, reluctant) +-- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -856,9 +830,9 @@ WINDOW w AS ( PATTERN (A+?) DEFINE A AS val > 0 ); --- Reluctant quantifier: prefer shortest match -- ?? (zero or one, reluctant) +-- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -867,9 +841,9 @@ WINDOW w AS ( PATTERN (A??) DEFINE A AS val > 0 ); --- Reluctant quantifier: prefer shortest match -- {n,}? (n or more, reluctant) +-- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -878,9 +852,9 @@ WINDOW w AS ( PATTERN (A{2,}?) DEFINE A AS val > 0 ); --- Reluctant quantifier: prefer shortest match -- {n,m}? (n to m, reluctant) +-- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -889,9 +863,9 @@ WINDOW w AS ( PATTERN (A{1,3}?) DEFINE A AS val > 0 ); --- Reluctant quantifier: prefer shortest match -- {n}? (exactly n, reluctant) +-- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -900,9 +874,9 @@ WINDOW w AS ( PATTERN (A{2}?) DEFINE A AS val > 0 ); --- Reluctant quantifier: prefer shortest match -- {,m}? (up to m, reluctant) - COMPLETELY UNTESTED RULE! +-- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -911,9 +885,6 @@ WINDOW w AS ( PATTERN (A{,3}?) DEFINE A AS val > 0 ); --- Reluctant quantifier: prefer shortest match - --- Invalid reluctant patterns (wrong token after quantifier) -- {2}+ (should be {2}? not {2}+) SELECT COUNT(*) OVER w @@ -924,7 +895,6 @@ WINDOW w AS ( PATTERN (A{2}+) DEFINE A AS val > 0 ); --- Expected: ERROR: syntax error at or near "+" -- {2,}* (should be {2,}? not {2,}*) SELECT COUNT(*) OVER w @@ -935,7 +905,6 @@ WINDOW w AS ( PATTERN (A{2,}*) DEFINE A AS val > 0 ); --- Expected: ERROR: syntax error at or near "*" -- {,3}* (should be {,3}? not {,3}*) SELECT COUNT(*) OVER w @@ -946,7 +915,6 @@ WINDOW w AS ( PATTERN (A{,3}*) DEFINE A AS val > 0 ); --- Expected: ERROR: syntax error at or near "*" -- {1,3}+ (should be {1,3}? not {1,3}+) SELECT COUNT(*) OVER w @@ -957,11 +925,10 @@ WINDOW w AS ( PATTERN (A{1,3}+) DEFINE A AS val > 0 ); --- Expected: ERROR: syntax error at or near "+" -- Boundary errors in reluctant quantifiers --- {-1}? (negative bound) +-- negative bound is not allowed SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -970,9 +937,8 @@ WINDOW w AS ( PATTERN (A{-1}?) DEFINE A AS val > 0 ); --- Expected: ERROR: syntax error at or near "-" --- {2147483647}? (INT_MAX) +-- ERROR: quantifier bound exceeds limits SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -981,9 +947,8 @@ WINDOW w AS ( PATTERN (A{2147483647}?) DEFINE A AS val > 0 ); --- Expected: ERROR: quantifier bound must be between 1 and 2147483646 --- {-1,}? (negative lower bound) +-- negative lower bound is not allowed SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -992,9 +957,8 @@ WINDOW w AS ( PATTERN (A{-1,}?) DEFINE A AS val > 0 ); --- Expected: ERROR: syntax error at or near "-" --- {2147483647,}? (INT_MAX lower bound) +-- ERROR: quantifier lower bound exceeds limits SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1003,9 +967,8 @@ WINDOW w AS ( PATTERN (A{2147483647,}?) DEFINE A AS val > 0 ); --- Expected: ERROR: quantifier bound must be between 0 and 2147483646 --- {,0}? (zero upper bound) +-- zero upper bound is not allowed SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1014,9 +977,8 @@ WINDOW w AS ( PATTERN (A{,0}?) DEFINE A AS val > 0 ); --- Expected: ERROR: quantifier bound must be between 1 and 2147483646 --- {,2147483647}? (INT_MAX upper bound) +-- ERROR: {,2147483647}? (upper bound in range exceeds limits) SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1025,9 +987,8 @@ WINDOW w AS ( PATTERN (A{,2147483647}?) DEFINE A AS val > 0 ); --- Expected: ERROR: quantifier bound must be between 1 and 2147483646 --- {-1,3}? (negative lower in range) +-- ERROR: {-1,3}? (negative lower bound in range is not allowed) SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1036,9 +997,8 @@ WINDOW w AS ( PATTERN (A{-1,3}?) DEFINE A AS val > 0 ); --- Expected: ERROR: syntax error at or near "-" --- {1,2147483647}? (INT_MAX upper in range) +-- ERROR: {1,2147483647}? (upper bound in range exceeds limits) SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1047,9 +1007,8 @@ WINDOW w AS ( PATTERN (A{1,2147483647}?) DEFINE A AS val > 0 ); --- Expected: ERROR: quantifier bounds must be between 0 and 2147483646 with max >= 1 --- {5,3}? (min > max) +-- ERROR: {5,3}? (min > max is not allowed) SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1058,12 +1017,12 @@ WINDOW w AS ( PATTERN (A{5,3}?) DEFINE A AS val > 0 ); --- Expected: ERROR: quantifier minimum bound must not exceed maximum -- Token-separated reluctant quantifiers (space between quantifier and ?) -- These may be tokenized differently by the lexer -- * ? (token separated) +-- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1072,9 +1031,9 @@ WINDOW w AS ( PATTERN (A* ?) DEFINE A AS val > 0 ); --- Reluctant quantifier: prefer shortest match -- + ? (token separated) +-- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1083,9 +1042,9 @@ WINDOW w AS ( PATTERN (A+ ?) DEFINE A AS val > 0 ); --- Reluctant quantifier: prefer shortest match -- {2,} ? (token separated) +-- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1094,9 +1053,6 @@ WINDOW w AS ( PATTERN (A{2,} ?) DEFINE A AS val > 0 ); --- Reluctant quantifier: prefer shortest match - --- Invalid token combinations -- * + (invalid combination) SELECT COUNT(*) OVER w @@ -1107,7 +1063,6 @@ WINDOW w AS ( PATTERN (A* +) DEFINE A AS val > 0 ); --- Expected: ERROR: syntax error at or near "+" -- + * (invalid combination) SELECT COUNT(*) OVER w @@ -1118,9 +1073,9 @@ WINDOW w AS ( PATTERN (A+ *) DEFINE A AS val > 0 ); --- Expected: ERROR: syntax error at or near "*" -- ? ? (parsed as ?? reluctant quantifier) +-- Reluctant quantifier: prefer shortest match SELECT COUNT(*) OVER w FROM rpr_reluctant WINDOW w AS ( @@ -1129,7 +1084,6 @@ WINDOW w AS ( PATTERN (A? ?) DEFINE A AS val > 0 ); --- Reluctant quantifier: prefer shortest match DROP TABLE rpr_reluctant; @@ -1138,7 +1092,7 @@ DROP TABLE rpr_reluctant; CREATE TABLE rpr_bounds (id INT); INSERT INTO rpr_bounds VALUES (1), (2); --- min > max +-- ERROR: quantifier lower bound must not exceed upper bound SELECT COUNT(*) OVER w FROM rpr_bounds WINDOW w AS ( @@ -1147,7 +1101,6 @@ WINDOW w AS ( PATTERN (A{5,3}) DEFINE A AS id > 0 ); --- Expected: ERROR: quantifier minimum bound must not exceed maximum -- Large bounds SELECT COUNT(*) OVER w @@ -1179,7 +1132,7 @@ WINDOW w AS ( DEFINE A AS id > 0 ); --- INT_MAX = 2147483647 (over limit) +-- ERROR: quantifier bound exceeds limits SELECT COUNT(*) OVER w FROM rpr_bounds WINDOW w AS ( @@ -1188,11 +1141,10 @@ WINDOW w AS ( PATTERN (A{2147483647}) DEFINE A AS id > 0 ); --- Expected: ERROR: quantifier bound must be between 1 and 2147483646 -- {n,} boundary errors --- Negative lower bound in {n,} +-- ERROR: negative lower bound in {n,} is not allowed SELECT COUNT(*) OVER w FROM rpr_bounds WINDOW w AS ( @@ -1201,9 +1153,8 @@ WINDOW w AS ( PATTERN (A{-1,}) DEFINE A AS id > 0 ); --- Expected: ERROR: syntax error at or near "-" --- INT_MAX in {n,} +-- ERROR: quantifier lower bound exceeds limits SELECT COUNT(*) OVER w FROM rpr_bounds WINDOW w AS ( @@ -1212,7 +1163,6 @@ WINDOW w AS ( PATTERN (A{2147483647,}) DEFINE A AS id > 0 ); --- Expected: ERROR: quantifier bound must be between 0 and 2147483646 -- {,m} boundary errors @@ -1225,9 +1175,8 @@ WINDOW w AS ( PATTERN (A{,0}) DEFINE A AS id > 0 ); --- Expected: ERROR: quantifier bound must be between 1 and 2147483646 --- INT_MAX in {,m} +-- ERROR: quantifier upper bound exceeds limits SELECT COUNT(*) OVER w FROM rpr_bounds WINDOW w AS ( @@ -1236,7 +1185,6 @@ WINDOW w AS ( PATTERN (A{,2147483647}) DEFINE A AS id > 0 ); --- Expected: ERROR: quantifier bound must be between 1 and 2147483646 DROP TABLE rpr_bounds; @@ -1244,7 +1192,6 @@ DROP TABLE rpr_bounds; -- Navigation Functions Tests (PREV / NEXT / FIRST / LAST) -- ============================================================ - CREATE TABLE rpr_nav (id INT, val INT); INSERT INTO rpr_nav VALUES (1, 10), (2, 20), (3, 15), (4, 25), (5, 30); @@ -1301,7 +1248,6 @@ WINDOW w AS ( B AS val > PREV(val) ) ORDER BY id; --- Expected: ERROR: cannot use prev outside a DEFINE clause -- NEXT function cannot be used other than in DEFINE SELECT NEXT(id), id, val, COUNT(*) OVER w as cnt @@ -1315,7 +1261,6 @@ WINDOW w AS ( B AS val > PREV(val) ) ORDER BY id; --- Expected: ERROR: cannot use next outside a DEFINE clause -- FIRST function - reference match_start row SELECT id, val, COUNT(*) OVER w as cnt @@ -1358,11 +1303,9 @@ ORDER BY id; -- FIRST function cannot be used other than in DEFINE SELECT FIRST(id), id, val FROM rpr_nav; --- Expected: ERROR: cannot use first outside a DEFINE clause -- LAST function cannot be used other than in DEFINE SELECT LAST(id), id, val FROM rpr_nav; --- Expected: ERROR: cannot use last outside a DEFINE clause DROP TABLE rpr_nav; @@ -1370,7 +1313,6 @@ DROP TABLE rpr_nav; -- SKIP TO / INITIAL Tests -- ============================================================ - CREATE TABLE rpr_skip (id INT, val INT); INSERT INTO rpr_skip VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), @@ -1492,15 +1434,15 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS val > 0 ); --- Expected: ERROR: SEEK is not supported --- HINT: Use INITIAL instead. DROP TABLE rpr_seek; -- ============================================================ -- Serialization/Deserialization Tests -- ============================================================ - +-- RPR-defining views and tables here are intentionally left in place (not +-- dropped) so that pg_dump/pg_upgrade exercise the deparse-then-re-parse +-- round-trip of the RPR window clause. -- View creation and deparsing @@ -2190,13 +2132,10 @@ DROP TABLE rpr_glue; -- Error Cases Tests -- ============================================================ - DROP TABLE IF EXISTS rpr_err; CREATE TABLE rpr_err (id INT, val INT); INSERT INTO rpr_err VALUES (1, 10), (2, 20); --- Syntax errors - -- Invalid quantifier syntax SELECT COUNT(*) OVER w FROM rpr_err @@ -2222,7 +2161,7 @@ EXCEPTION END $$; SET client_min_messages = WARNING; --- Empty DEFINE +-- ERROR: empty DEFINE not allowed SELECT COUNT(*) OVER w FROM rpr_err WINDOW w AS ( @@ -2233,7 +2172,7 @@ WINDOW w AS ( ); -- Expected: Syntax error --- Empty PATTERN +-- ERROR: empty PATTERN not allowed SELECT COUNT(*) OVER w FROM rpr_err WINDOW w AS ( @@ -2244,7 +2183,7 @@ WINDOW w AS ( ); -- Expected: Syntax error --- DEFINE without PATTERN (PATTERN and DEFINE must be used together) +-- ERROR: DEFINE without PATTERN (PATTERN and DEFINE must be used together) SELECT COUNT(*) OVER w FROM rpr_err WINDOW w AS ( @@ -2265,7 +2204,6 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS A.val > 0 ); --- Expected: ERROR: pattern variable qualified expression "a.val" is not supported -- PATTERN-only variable qualified name: not supported even without DEFINE entry SELECT COUNT(*) OVER w @@ -2276,7 +2214,6 @@ WINDOW w AS ( PATTERN (A+ B+) DEFINE A AS B.val > 0 ); --- Expected: ERROR: pattern variable qualified expression "b.val" is not supported -- DEFINE-only variable qualified name: still a pattern variable, not a range variable SELECT COUNT(*) OVER w @@ -2287,7 +2224,6 @@ WINDOW w AS ( PATTERN (A+) 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 ISO/IEC 19075-5 6.5) SELECT COUNT(*) OVER w @@ -2298,7 +2234,6 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS rpr_err.val > 0 ); --- Expected: ERROR: range variable qualified expression "rpr_err.val" is not allowed -- Unknown qualifier (neither pattern var nor range var): the DEFINE pre-check -- must fall through so that normal column resolution produces a sensible error. @@ -2310,7 +2245,6 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS nosuch.val > 0 ); --- Expected: ERROR: missing FROM-clause entry for table "nosuch" -- Unqualified composite field access in DEFINE works: no qualifier means no -- pattern/range-var navigation, so the pre-check skips and normal resolution @@ -2340,7 +2274,6 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS (A.items).amount > 10 ); --- Expected: ERROR: pattern variable qualified expression "a.items" is not supported SELECT COUNT(*) OVER w FROM rpr_composite WINDOW w AS ( @@ -2349,13 +2282,10 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS (rpr_composite.items).amount > 10 ); --- Expected: ERROR: range variable qualified expression "rpr_composite.items" is not allowed DROP TABLE rpr_composite; DROP TYPE rpr_item; --- Semantic errors - --- Undefined column in DEFINE +-- ERROR: undefined column in DEFINE SELECT COUNT(*) OVER w FROM rpr_err WINDOW w AS ( @@ -2364,9 +2294,8 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS nonexistent_column > 0 ); --- Expected: ERROR: column "nonexistent_column" does not exist --- Type mismatch +-- ERROR: type mismatch SELECT COUNT(*) OVER w FROM rpr_err WINDOW w AS ( @@ -2375,9 +2304,8 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS val > 'string' ); --- Expected: ERROR: invalid input syntax for type integer: "string" --- Aggregate function in DEFINE (if not allowed) +-- ERROR: aggregate function in DEFINE is not supported SELECT COUNT(*) OVER w FROM rpr_err WINDOW w AS ( @@ -2386,9 +2314,8 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS COUNT(*) > 0 ); --- Expected: ERROR: aggregate functions are not allowed in DEFINE --- Subquery in DEFINE (NOT SUPPORTED) +-- Subquery in DEFINE is not supported SELECT COUNT(*) OVER w FROM rpr_err WINDOW w AS ( @@ -2397,9 +2324,6 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS val > (SELECT max(val) FROM rpr_err) ); --- Expected: ERROR: cannot use subquery in DEFINE expression - --- Edge cases -- Pattern variable not used (should work, extra vars ignored) SELECT id, val, COUNT(*) OVER w as cnt @@ -3414,7 +3338,6 @@ WINDOW w AS ( PATTERN ((A{2000000000,2147483647}){2}) DEFINE A AS val > 0 ); --- Expected: ERROR at parse time before optimization -- Test: nested unbounded with large min causes overflow fallback EXPLAIN (COSTS OFF) @@ -3563,7 +3486,6 @@ WINDOW w AS ( ) GROUP BY category ORDER BY category; --- Expected: ERROR: syntax error at or near "GROUP" -- (GROUP BY after WINDOW clause is not valid SQL syntax) -- ============================================================ @@ -3937,7 +3859,6 @@ INSERT INTO rpr_sort VALUES (4, 'B', 40), (5, 'A', 50), (6, 'B', 60); -- RPR with GROUP BY (aggregate in DEFINE -> ERROR before GROUP BY interaction) --- Expected: ERROR: aggregate functions are not allowed in DEFINE SELECT category, COUNT(*) as group_cnt, @@ -3954,7 +3875,6 @@ WINDOW w AS ( ORDER BY category; -- RPR with HAVING (same aggregate-in-DEFINE error) --- Expected: ERROR: aggregate functions are not allowed in DEFINE SELECT category, COUNT(*) as group_cnt, @@ -4189,7 +4109,7 @@ WINDOW w AS ( ); -- Expected: Success - exactly at RPR_VARID_MAX boundary --- Test: 241 variables in PATTERN, 240 in DEFINE (exceeds limit with implicit TRUE) +-- ERROR: 241 variables in PATTERN, 240 in DEFINE (exceeds limit with implicit TRUE) SELECT COUNT(*) OVER w FROM rpr_errors WINDOW w AS ( ORDER BY id @@ -4221,7 +4141,6 @@ WINDOW w AS ( V221 AS val > 0, V222 AS val > 0, V223 AS val > 0, V224 AS val > 0, V225 AS val > 0, V226 AS val > 0, V227 AS val > 0, V228 AS val > 0, V229 AS val > 0, V230 AS val > 0, V231 AS val > 0, V232 AS val > 0, V233 AS val > 0, V234 AS val > 0, V235 AS val > 0, V236 AS val > 0, V237 AS val > 0, V238 AS val > 0, V239 AS val > 0, V240 AS val > 0 ); --- Expected: ERROR - too many pattern variables (Maximum is 240) -- Test: Pattern nesting at maximum depth (depth 253) -- Note: 253 nested GROUP{3,7}? quantifiers; reluctant quantifiers are not @@ -4247,14 +4166,12 @@ WINDOW w AS ( PATTERN (((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((A{3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?){3,7}?) DEFINE A AS val > 0 ); --- Expected: ERROR - pattern nesting too deep DROP TABLE rpr_errors; -- ============================================================ --- Jacob's Patterns +-- Basic Pattern Matching -- ============================================================ --- Basic pattern matching tests from jacob branch -- Test: A? (optional, greedy) SELECT id, val, count(*) OVER w AS c @@ -4453,3 +4370,4 @@ FROM (SELECT id, val, ) s; DROP TABLE rpr_plan; +RESET client_min_messages; -- 2.50.1 (Apple Git-155)