v47-0001-v47-rpr-reformat-gram.y-and-improve-regression-tests.nocfbot
application/octet-stream
Filename: v47-0001-v47-rpr-reformat-gram.y-and-improve-regression-tests.nocfbot
Type: application/octet-stream
Part: 0
Message:
Re: Row pattern recognition
From 327d9cf3a37ffb3324427586b6e9e1e8cd144596 Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Thu, 4 Jun 2026 16:53:45 +0800
Subject: [PATCH v47 1/1] v47 rpr reformat gram.y and improve regression tests
Reformat the first three alternatives of row_pattern_quantifier_opt
in gram.y (empty, '*', '+') from inline single-line actions to
multi-line blocks, making them consistent with the surrounding Op
and '{...}' alternatives.
Update regression tests to follow the convention established in
commit ecb2508aaf [1]: do not repeat exact error messages as comments
in regress test files, since such comments silently go stale when
error wording changes. Replace the removed "Expected: ERROR: ..."
comments with short descriptive comments that state what the test
is checking rather than what output it expects.
Also remove unnecessary newline/comments in src/test/regress/sql/rpr_base.sql.
Add test coverage for:
1. invalid token combinations after a quantifier (A+ !, A+ ?+,
A* ?+, A? ??), exercising the split-token error paths in
row_pattern_quantifier_opt
2. set-returning function (generate_series) in a DEFINE clause
[1]: https://git.postgresql.org/cgit/postgresql.git/commit/?id=ecb2508aaf9b978871734ea2fdf701ab7d593d0a
---
src/backend/parser/gram.y | 21 ++-
src/test/regress/expected/rpr_base.out | 203 ++++++++++--------------
src/test/regress/sql/rpr_base.sql | 208 ++++++++-----------------
3 files changed, 164 insertions(+), 268 deletions(-)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index a2fafb717c..aa78319747 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -17723,11 +17723,22 @@ row_pattern_primary:
;
row_pattern_quantifier_opt:
- /* EMPTY - no quantifier means exactly once; @$ is unused since
- * min=max=1 never produces an error */
- { $$ = (Node *) makeRPRQuantifier(1, 1, -1, @$, yyscanner); }
- | '*' { $$ = (Node *) makeRPRQuantifier(0, INT_MAX, -1, @1, yyscanner); }
- | '+' { $$ = (Node *) makeRPRQuantifier(1, INT_MAX, -1, @1, yyscanner); }
+ {
+ /*
+ * EMPTY - no quantifier means exactly once
+ *
+ * @$ is unused since min=max=1, should never produces an error
+ */
+ $$ = (Node *) makeRPRQuantifier(1, 1, -1, @$, yyscanner);
+ }
+ | '*'
+ {
+ $$ = (Node *) makeRPRQuantifier(0, INT_MAX, -1, @1, yyscanner);
+ }
+ | '+'
+ {
+ $$ = (Node *) makeRPRQuantifier(1, INT_MAX, -1, @1, yyscanner);
+ }
| Op
{
/* Handle single Op: ? or reluctant quantifiers *?, +?, ?? */
diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out
index 1410ba7539..eee196f56c 100644
--- a/src/test/regress/expected/rpr_base.out
+++ b/src/test/regress/expected/rpr_base.out
@@ -223,7 +223,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 (
@@ -235,12 +235,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 (
@@ -252,7 +251,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
@@ -392,8 +390,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 (
@@ -407,7 +404,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
@@ -424,7 +420,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
@@ -440,7 +435,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
@@ -456,8 +450,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 (
@@ -470,8 +463,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 (
@@ -484,8 +476,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 (
@@ -499,8 +490,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 (
@@ -514,9 +504,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 (
@@ -528,8 +516,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 (
@@ -541,7 +528,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
@@ -558,7 +544,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
@@ -572,7 +557,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
@@ -597,9 +581,8 @@ EXECUTE rpr_end_offset(2);
6 | 30 | 1
(6 rows)
-EXECUTE rpr_end_offset(0);
+EXECUTE rpr_end_offset(0); -- ERROR: frame ending offset must be positive
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
@@ -643,7 +626,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 (
@@ -658,7 +641,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
@@ -674,7 +656,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
@@ -708,6 +689,7 @@ ORDER BY id;
-- Expected: Pattern matching should reset for each partition
-- PARTITION BY with RANGE frame
+-- range frame is not allowed with RPR
SELECT id, grp, val, COUNT(*) OVER w as cnt
FROM rpr_partition
WINDOW w AS (
@@ -723,7 +705,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
@@ -985,7 +966,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
@@ -999,7 +979,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
@@ -1129,6 +1108,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 (
@@ -1144,8 +1124,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 (
@@ -1161,8 +1141,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 (
@@ -1178,8 +1158,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 (
@@ -1195,8 +1175,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 (
@@ -1212,8 +1192,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 (
@@ -1229,8 +1209,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 (
@@ -1246,9 +1226,8 @@ WINDOW w AS (
0
(3 rows)
--- Reluctant quantifier: prefer shortest match
--- Invalid reluctant patterns (wrong token after quantifier)
-- {2}+ (should be {2}? not {2}+)
+-- quantifier on a quantifier is not allowed
SELECT COUNT(*) OVER w
FROM rpr_reluctant
WINDOW w AS (
@@ -1260,8 +1239,8 @@ 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,}*)
+-- quantifier on a quantifier is not allowed
SELECT COUNT(*) OVER w
FROM rpr_reluctant
WINDOW w AS (
@@ -1273,8 +1252,8 @@ 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}*)
+-- quantifier on a quantifier is not allowed
SELECT COUNT(*) OVER w
FROM rpr_reluctant
WINDOW w AS (
@@ -1286,8 +1265,8 @@ 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}+)
+-- quantifier on a quantifier is not allowed
SELECT COUNT(*) OVER w
FROM rpr_reluctant
WINDOW w AS (
@@ -1299,9 +1278,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 (
@@ -1313,8 +1291,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 (
@@ -1326,8 +1303,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 (
@@ -1339,8 +1315,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 (
@@ -1352,8 +1327,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 (
@@ -1365,8 +1339,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 (
@@ -1378,8 +1351,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 (
@@ -1391,8 +1363,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 (
@@ -1404,8 +1375,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 (
@@ -1417,10 +1387,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 (
@@ -1436,8 +1406,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 (
@@ -1453,8 +1423,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 (
@@ -1470,8 +1440,6 @@ WINDOW w AS (
0
(3 rows)
--- Reluctant quantifier: prefer shortest match
--- Invalid token combinations
-- * + (invalid combination)
SELECT COUNT(*) OVER w
FROM rpr_reluctant
@@ -1484,7 +1452,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
@@ -1497,8 +1464,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 (
@@ -1514,12 +1481,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 exceeds upper bound
SELECT COUNT(*) OVER w
FROM rpr_bounds
WINDOW w AS (
@@ -1531,7 +1497,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
@@ -1577,7 +1542,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 (
@@ -1589,9 +1554,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 (
@@ -1603,8 +1567,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 (
@@ -1616,9 +1579,9 @@ 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}
+-- zero upper bound is not allowed
SELECT COUNT(*) OVER w
FROM rpr_bounds
WINDOW w AS (
@@ -1630,8 +1593,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 (
@@ -1643,7 +1605,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)
@@ -1730,7 +1691,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
@@ -1746,7 +1706,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
@@ -1815,13 +1774,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
@@ -1994,6 +1951,7 @@ DROP TABLE rpr_init;
CREATE TABLE rpr_seek (id INT, val INT);
INSERT INTO rpr_seek VALUES (1, 10);
-- SEEK keyword
+-- ERROR: SEEK is not supported
SELECT COUNT(*) OVER w
FROM rpr_seek
WINDOW w AS (
@@ -2007,8 +1965,6 @@ 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
@@ -3014,7 +2970,6 @@ SELECT pg_get_viewdef('rpr_quant_n_plus_v'::regclass);
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
@@ -3028,7 +2983,27 @@ ERROR: unsupported quantifier "+!"
LINE 6: PATTERN (A+!)
^
HINT: Valid quantifiers are: *, +, ?, *?, +?, ??, {n}, {n,}, {,m}, {n,m} and their reluctant versions.
--- Expected: Syntax error
+-- none of the following 4 queries should be accepted
+SELECT FROM rpr_err WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING PATTERN (A+ !) DEFINE A AS TRUE);
+ERROR: invalid token after "+" quantifier
+LINE 1: ...S BETWEEN CURRENT ROW AND 1 FOLLOWING PATTERN (A+ !) DEFINE ...
+ ^
+HINT: Did you mean "+?" for reluctant quantifier?
+SELECT FROM rpr_err WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING PATTERN (A+ ?+) DEFINE A AS TRUE);
+ERROR: invalid token after "+" quantifier
+LINE 1: ...S BETWEEN CURRENT ROW AND 1 FOLLOWING PATTERN (A+ ?+) DEFINE...
+ ^
+HINT: Did you mean "+?" for reluctant quantifier?
+SELECT FROM rpr_err WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING PATTERN (A* ?+) DEFINE A AS TRUE);
+ERROR: invalid token after "*" quantifier
+LINE 1: ...S BETWEEN CURRENT ROW AND 1 FOLLOWING PATTERN (A* ?+) DEFINE...
+ ^
+HINT: Did you mean "*?" for reluctant quantifier?
+SELECT FROM rpr_err WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING PATTERN (A? ??) DEFINE A AS TRUE);
+ERROR: invalid quantifier combination
+LINE 1: ...OWS BETWEEN CURRENT ROW AND 1 FOLLOWING PATTERN (A? ??) DEFI...
+ ^
+HINT: Did you mean "??" for reluctant quantifier?
-- Unmatched parentheses
SET client_min_messages = NOTICE;
DO $$
@@ -3043,7 +3018,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 (
@@ -3055,8 +3030,7 @@ WINDOW w AS (
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 (
@@ -3068,8 +3042,7 @@ WINDOW w AS (
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 (
@@ -3080,7 +3053,6 @@ WINDOW w AS (
ERROR: syntax error at or near "DEFINE"
LINE 6: DEFINE A AS val > 0
^
--- Expected: Syntax error
-- Qualified column references (NOT SUPPORTED)
-- Pattern variable qualified name: not supported (valid per ISO/IEC 19075-5 6.15 / 4.16, not yet implemented)
SELECT COUNT(*) OVER w
@@ -3094,7 +3066,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
@@ -3107,7 +3078,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
@@ -3120,7 +3090,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
@@ -3133,7 +3102,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
@@ -3147,7 +3115,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.
@@ -3185,7 +3152,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 (
@@ -3197,11 +3163,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 (
@@ -3213,8 +3177,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 (
@@ -3226,8 +3189,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 (
@@ -3239,8 +3201,13 @@ 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)
+-- ERROR: set-returning function in DEFINE is not supported
+SELECT FROM rpr_err
+WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING PATTERN (A+) DEFINE A AS 1 > generate_series(1 ,2));
+ERROR: set-returning functions are not allowed in DEFINE
+LINE 2: ... ROW AND 1 FOLLOWING PATTERN (A+) DEFINE A AS 1 > generate_s...
+ ^
+-- Subquery in DEFINE is not supported
SELECT COUNT(*) OVER w
FROM rpr_err
WINDOW w AS (
@@ -3252,8 +3219,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
@@ -5400,7 +5365,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
@@ -5628,6 +5592,7 @@ ORDER BY id;
(9 rows)
-- Window with Aggregate Functions
+-- ERROR: (GROUP BY after WINDOW clause is not valid SQL syntax)
SELECT category,
COUNT(*) OVER w as window_cnt,
COUNT(*) as agg_cnt
@@ -5644,8 +5609,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
-- Files: planner.c, prepjointree.c
@@ -6124,7 +6087,7 @@ 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
+-- ERROR: aggregate function in DEFINE is not supported
SELECT category,
COUNT(*) as group_cnt,
MAX(val) as max_val,
@@ -6141,8 +6104,7 @@ ORDER BY category;
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
+-- ERROR: aggregate function in DEFINE is not supported
SELECT category,
COUNT(*) as group_cnt,
COUNT(*) OVER w as window_cnt
@@ -6455,7 +6417,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
@@ -6489,7 +6451,6 @@ WINDOW w AS (
);
ERROR: too many pattern variables
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
@@ -6521,7 +6482,6 @@ 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
@@ -6889,3 +6849,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 53bf090b90..41bc86599f 100644
--- a/src/test/regress/sql/rpr_base.sql
+++ b/src/test/regress/sql/rpr_base.sql
@@ -68,7 +68,6 @@ DROP TABLE rpr_keywords;
-- DEFINE Clause Tests
-- ============================================================
-
-- Simple column references
CREATE TABLE stock_price (
dt DATE,
@@ -181,7 +180,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 (
@@ -190,7 +189,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;
@@ -198,7 +196,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 (
@@ -207,7 +205,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
@@ -326,9 +323,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 (
@@ -337,7 +332,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
@@ -351,7 +345,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
@@ -363,7 +356,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
@@ -375,9 +367,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 (
@@ -386,9 +377,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 (
@@ -397,9 +387,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 (
@@ -408,9 +397,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 (
@@ -419,11 +407,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 (
@@ -432,9 +417,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 (
@@ -443,7 +427,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).
@@ -457,7 +440,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).
@@ -471,7 +453,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).
@@ -487,8 +468,7 @@ 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
+EXECUTE rpr_end_offset(0); -- ERROR: frame ending offset must be positive
DEALLOCATE rpr_end_offset;
-- Large offset: CURRENT ROW AND 1000 FOLLOWING
@@ -515,7 +495,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 (
@@ -526,7 +506,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
@@ -539,7 +518,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;
@@ -568,6 +546,7 @@ ORDER BY id;
-- Expected: Pattern matching should reset for each partition
-- PARTITION BY with RANGE frame
+-- range frame is not allowed with RPR
SELECT id, grp, val, COUNT(*) OVER w as cnt
FROM rpr_partition
WINDOW w AS (
@@ -579,7 +558,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;
@@ -682,8 +660,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),
@@ -725,7 +701,6 @@ WINDOW w AS (
ORDER BY id;
-- Edge case quantifiers
-
-- {0} is not allowed (min must be >= 1)
SELECT id, val, COUNT(*) OVER w as cnt
FROM rpr_quant
@@ -736,7 +711,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
@@ -748,7 +722,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
@@ -762,7 +735,6 @@ WINDOW w AS (
ORDER BY id;
-- Exact quantifiers {n}
-
-- {3} (representative exact quantifier)
SELECT id, val, COUNT(*) OVER w as cnt
FROM rpr_quant
@@ -775,7 +747,6 @@ WINDOW w AS (
ORDER BY id;
-- Range quantifiers {n,}
-
-- {2,} (representative n or more)
SELECT id, val, COUNT(*) OVER w as cnt
FROM rpr_quant
@@ -788,7 +759,6 @@ WINDOW w AS (
ORDER BY id;
-- Upper bound quantifiers {,m}
-
-- {,3} (representative up to m)
SELECT id, val, COUNT(*) OVER w as cnt
FROM rpr_quant
@@ -801,7 +771,6 @@ WINDOW w AS (
ORDER BY id;
-- Range quantifiers {n,m}
-
-- {3,7} (representative range)
SELECT id, val, COUNT(*) OVER w as cnt
FROM rpr_quant
@@ -820,6 +789,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 (
@@ -828,9 +798,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 (
@@ -839,9 +809,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 (
@@ -850,9 +820,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 (
@@ -861,9 +831,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 (
@@ -872,9 +842,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 (
@@ -883,9 +853,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 (
@@ -894,11 +864,9 @@ 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}+)
+-- quantifier on a quantifier is not allowed
SELECT COUNT(*) OVER w
FROM rpr_reluctant
WINDOW w AS (
@@ -907,9 +875,9 @@ WINDOW w AS (
PATTERN (A{2}+)
DEFINE A AS val > 0
);
--- Expected: ERROR: syntax error at or near "+"
-- {2,}* (should be {2,}? not {2,}*)
+-- quantifier on a quantifier is not allowed
SELECT COUNT(*) OVER w
FROM rpr_reluctant
WINDOW w AS (
@@ -918,9 +886,9 @@ WINDOW w AS (
PATTERN (A{2,}*)
DEFINE A AS val > 0
);
--- Expected: ERROR: syntax error at or near "*"
-- {,3}* (should be {,3}? not {,3}*)
+-- quantifier on a quantifier is not allowed
SELECT COUNT(*) OVER w
FROM rpr_reluctant
WINDOW w AS (
@@ -929,9 +897,9 @@ 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}+)
+-- quantifier on a quantifier is not allowed
SELECT COUNT(*) OVER w
FROM rpr_reluctant
WINDOW w AS (
@@ -940,11 +908,9 @@ 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 (
@@ -953,9 +919,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 (
@@ -964,9 +929,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 (
@@ -975,9 +939,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 (
@@ -986,9 +949,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 (
@@ -997,9 +959,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 (
@@ -1008,9 +969,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 (
@@ -1019,9 +979,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 (
@@ -1030,9 +989,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 (
@@ -1041,12 +999,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 (
@@ -1055,9 +1013,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 (
@@ -1066,9 +1024,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 (
@@ -1077,9 +1035,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
@@ -1090,7 +1045,6 @@ WINDOW w AS (
PATTERN (A* +)
DEFINE A AS val > 0
);
--- Expected: ERROR: syntax error at or near "+"
-- + * (invalid combination)
SELECT COUNT(*) OVER w
@@ -1101,9 +1055,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 (
@@ -1112,7 +1066,6 @@ WINDOW w AS (
PATTERN (A? ?)
DEFINE A AS val > 0
);
--- Reluctant quantifier: prefer shortest match
DROP TABLE rpr_reluctant;
@@ -1121,7 +1074,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 exceeds upper bound
SELECT COUNT(*) OVER w
FROM rpr_bounds
WINDOW w AS (
@@ -1130,7 +1083,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
@@ -1162,7 +1114,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 (
@@ -1171,11 +1123,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 (
@@ -1184,9 +1135,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 (
@@ -1195,11 +1145,11 @@ WINDOW w AS (
PATTERN (A{2147483647,})
DEFINE A AS id > 0
);
--- Expected: ERROR: quantifier bound must be between 0 and 2147483646
-- {,m} boundary errors
-- Zero upper bound in {,m}
+-- zero upper bound is not allowed
SELECT COUNT(*) OVER w
FROM rpr_bounds
WINDOW w AS (
@@ -1208,9 +1158,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 (
@@ -1219,7 +1168,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;
@@ -1284,7 +1232,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
@@ -1298,7 +1245,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
@@ -1341,11 +1287,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;
@@ -1466,6 +1410,7 @@ CREATE TABLE rpr_seek (id INT, val INT);
INSERT INTO rpr_seek VALUES (1, 10);
-- SEEK keyword
+-- ERROR: SEEK is not supported
SELECT COUNT(*) OVER w
FROM rpr_seek
WINDOW w AS (
@@ -1475,9 +1420,6 @@ WINDOW w AS (
PATTERN (A+)
DEFINE A AS val > 0
);
--- Expected: ERROR: SEEK is not supported
--- HINT: Use INITIAL instead.
-
DROP TABLE rpr_seek;
-- ============================================================
@@ -2038,14 +1980,10 @@ SELECT pg_get_viewdef('rpr_quant_n_plus_v'::regclass);
-- ============================================================
-- 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
@@ -2055,7 +1993,12 @@ WINDOW w AS (
PATTERN (A+!)
DEFINE A AS val > 0
);
--- Expected: Syntax error
+
+-- none of the following 4 queries should be accepted
+SELECT FROM rpr_err WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING PATTERN (A+ !) DEFINE A AS TRUE);
+SELECT FROM rpr_err WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING PATTERN (A+ ?+) DEFINE A AS TRUE);
+SELECT FROM rpr_err WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING PATTERN (A* ?+) DEFINE A AS TRUE);
+SELECT FROM rpr_err WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING PATTERN (A? ??) DEFINE A AS TRUE);
-- Unmatched parentheses
SET client_min_messages = NOTICE;
@@ -2071,7 +2014,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 (
@@ -2080,9 +2023,8 @@ WINDOW w AS (
PATTERN (A+)
DEFINE
);
--- Expected: Syntax error
--- Empty PATTERN
+-- ERROR: empty PATTERN not allowed
SELECT COUNT(*) OVER w
FROM rpr_err
WINDOW w AS (
@@ -2091,9 +2033,8 @@ WINDOW w AS (
PATTERN ()
DEFINE A AS val > 0
);
--- 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 (
@@ -2101,7 +2042,6 @@ WINDOW w AS (
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
DEFINE A AS val > 0
);
--- Expected: Syntax error
-- Qualified column references (NOT SUPPORTED)
@@ -2114,7 +2054,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
@@ -2125,7 +2064,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
@@ -2136,7 +2074,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
@@ -2147,7 +2084,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.
@@ -2159,7 +2095,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
@@ -2189,7 +2124,7 @@ 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 (
@@ -2198,13 +2133,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 (
@@ -2213,9 +2145,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 (
@@ -2224,9 +2155,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 (
@@ -2235,9 +2165,12 @@ WINDOW w AS (
PATTERN (A+)
DEFINE A AS COUNT(*) > 0
);
--- Expected: ERROR: aggregate functions are not allowed in DEFINE
--- Subquery in DEFINE (NOT SUPPORTED)
+-- ERROR: set-returning function in DEFINE is not supported
+SELECT FROM rpr_err
+WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING PATTERN (A+) DEFINE A AS 1 > generate_series(1 ,2));
+
+-- Subquery in DEFINE is not supported
SELECT COUNT(*) OVER w
FROM rpr_err
WINDOW w AS (
@@ -2246,9 +2179,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
@@ -3263,7 +3193,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)
@@ -3399,6 +3328,7 @@ FROM rpr_planner
ORDER BY id;
-- Window with Aggregate Functions
+-- ERROR: (GROUP BY after WINDOW clause is not valid SQL syntax)
SELECT category,
COUNT(*) OVER w as window_cnt,
COUNT(*) as agg_cnt
@@ -3412,8 +3342,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)
-- ============================================================
-- Subquery and CTE Tests
@@ -3786,8 +3714,7 @@ 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
-
+-- ERROR: aggregate function in DEFINE is not supported
SELECT category,
COUNT(*) as group_cnt,
MAX(val) as max_val,
@@ -3802,9 +3729,7 @@ WINDOW w AS (
)
ORDER BY category;
--- RPR with HAVING (same aggregate-in-DEFINE error)
--- Expected: ERROR: aggregate functions are not allowed in DEFINE
-
+-- ERROR: aggregate function in DEFINE is not supported
SELECT category,
COUNT(*) as group_cnt,
COUNT(*) OVER w as window_cnt
@@ -4038,7 +3963,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
@@ -4070,7 +3995,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
@@ -4096,7 +4020,6 @@ 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;
@@ -4302,3 +4225,4 @@ FROM (SELECT id, val,
) s;
DROP TABLE rpr_plan;
+RESET client_min_messages;
--
2.34.1