nocfbot-0067-Add-row-pattern-recognition-negative-and-coverage.txt
text/plain
Filename: nocfbot-0067-Add-row-pattern-recognition-negative-and-coverage.txt
Type: text/plain
Part: 65
Message:
Re: Row pattern recognition
From 033b6d0bd42a41b2501d34fb413cb889caffa366 Mon Sep 17 00:00:00 2001
From: Henson Choi <assam258@gmail.com>
Date: Mon, 8 Jun 2026 16:34:09 +0900
Subject: [PATCH 67/68] Add row pattern recognition negative and coverage tests
Cover the split-token error paths after a quantifier (* / + / Op and
the {n}/{n,}/{,m}/{n,m} range forms), rejection of set-returning and
window functions in a DEFINE clause, and the reluctant-GROUP case where
quantifier multiplication is correctly suppressed. Tests only.
---
src/test/regress/expected/rpr_base.out | 91 ++++++++++++++++++++++++--
src/test/regress/sql/rpr_base.sql | 37 +++++++++--
2 files changed, 120 insertions(+), 8 deletions(-)
diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out
index f1767305d06..2407c455164 100644
--- a/src/test/regress/expected/rpr_base.out
+++ b/src/test/regress/expected/rpr_base.out
@@ -3283,7 +3283,48 @@ 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?
+-- none of the following 4 range-quantifier queries should be accepted
+SELECT FROM rpr_err WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING PATTERN (A{2} !) DEFINE A AS TRUE);
+ERROR: invalid token after range quantifier
+LINE 1: ...BETWEEN CURRENT ROW AND 1 FOLLOWING PATTERN (A{2} !) DEFINE ...
+ ^
+HINT: Only "?" is allowed after {n} to make it reluctant.
+SELECT FROM rpr_err WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING PATTERN (A{2,} !) DEFINE A AS TRUE);
+ERROR: invalid token after range quantifier
+LINE 1: ...ETWEEN CURRENT ROW AND 1 FOLLOWING PATTERN (A{2,} !) DEFINE ...
+ ^
+HINT: Only "?" is allowed after {n,} or {,m} to make it reluctant.
+SELECT FROM rpr_err WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING PATTERN (A{,3} !) DEFINE A AS TRUE);
+ERROR: invalid token after range quantifier
+LINE 1: ...ETWEEN CURRENT ROW AND 1 FOLLOWING PATTERN (A{,3} !) DEFINE ...
+ ^
+HINT: Only "?" is allowed after {n,} or {,m} to make it reluctant.
+SELECT FROM rpr_err WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING PATTERN (A{2,3} !) DEFINE A AS TRUE);
+ERROR: invalid token after range quantifier
+LINE 1: ...TWEEN CURRENT ROW AND 1 FOLLOWING PATTERN (A{2,3} !) DEFINE ...
+ ^
+HINT: Only "?" is allowed after {n,m} to make it reluctant.
-- Unmatched parentheses
SET client_min_messages = NOTICE;
DO $$
@@ -3310,7 +3351,6 @@ WINDOW w AS (
ERROR: syntax error at or near ")"
LINE 8: );
^
--- Expected: Syntax error
-- ERROR: empty PATTERN not allowed
SELECT COUNT(*) OVER w
FROM rpr_err
@@ -3323,7 +3363,6 @@ WINDOW w AS (
ERROR: syntax error at or near ")"
LINE 6: PATTERN ()
^
--- Expected: Syntax error
-- ERROR: DEFINE without PATTERN (PATTERN and DEFINE must be used together)
SELECT COUNT(*) OVER w
FROM rpr_err
@@ -3335,7 +3374,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
@@ -3484,6 +3522,18 @@ WINDOW w AS (
ERROR: aggregate functions are not allowed in DEFINE
LINE 7: DEFINE A AS COUNT(*) > 0
^
+-- 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...
+ ^
+-- ERROR: window 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 > row_number() OVER ());
+ERROR: window functions are not allowed in DEFINE
+LINE 2: ... ROW AND 1 FOLLOWING PATTERN (A+) DEFINE A AS 1 > row_number...
+ ^
-- Subquery in DEFINE is not supported
SELECT COUNT(*) OVER w
FROM rpr_err
@@ -3925,6 +3975,39 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
-> Seq Scan on rpr_plan
(7 rows)
+-- Quantifier NO multiply: reluctant GROUP child (((A B){2}?){3}) stays nested
+-- a reluctant quantifier on a GROUP is not subject to multiplication
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) OVER w FROM rpr_plan
+WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (((A B){2}?){3}) DEFINE A AS val > 0, B AS val > 0);
+ QUERY PLAN
+-------------------------------------------------------------------------------
+ WindowAgg
+ Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+ Pattern: ((a b){2}?){3}
+ Nav Mark Lookback: 0
+ -> Sort
+ Sort Key: id
+ -> Seq Scan on rpr_plan
+(7 rows)
+
+-- Quantifier multiply control: greedy GROUP (((A B){2}){3}) -> (a b){6}
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) OVER w FROM rpr_plan
+WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (((A B){2}){3}) DEFINE A AS val > 0, B AS val > 0);
+ QUERY PLAN
+-------------------------------------------------------------------------------
+ WindowAgg
+ Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
+ Pattern: (a b){6}
+ Nav Mark Lookback: 0
+ -> Sort
+ Sort Key: id
+ -> Seq Scan on rpr_plan
+(7 rows)
+
-- Quantifier multiply with child range: (A{2,3}){3} -> a{6,9}
-- outer exact, child range - optimization applies
EXPLAIN (COSTS OFF)
diff --git a/src/test/regress/sql/rpr_base.sql b/src/test/regress/sql/rpr_base.sql
index 7dc13232d89..cec7ea1e8db 100644
--- a/src/test/regress/sql/rpr_base.sql
+++ b/src/test/regress/sql/rpr_base.sql
@@ -2145,7 +2145,18 @@ 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);
+
+-- none of the following 4 range-quantifier queries should be accepted
+SELECT FROM rpr_err WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING PATTERN (A{2} !) DEFINE A AS TRUE);
+SELECT FROM rpr_err WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING PATTERN (A{2,} !) DEFINE A AS TRUE);
+SELECT FROM rpr_err WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING PATTERN (A{,3} !) DEFINE A AS TRUE);
+SELECT FROM rpr_err WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING PATTERN (A{2,3} !) DEFINE A AS TRUE);
-- Unmatched parentheses
SET client_min_messages = NOTICE;
@@ -2170,7 +2181,6 @@ WINDOW w AS (
PATTERN (A+)
DEFINE
);
--- Expected: Syntax error
-- ERROR: empty PATTERN not allowed
SELECT COUNT(*) OVER w
@@ -2181,7 +2191,6 @@ WINDOW w AS (
PATTERN ()
DEFINE A AS val > 0
);
--- Expected: Syntax error
-- ERROR: DEFINE without PATTERN (PATTERN and DEFINE must be used together)
SELECT COUNT(*) OVER w
@@ -2191,7 +2200,6 @@ WINDOW w AS (
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
DEFINE A AS val > 0
);
--- Expected: Syntax error
-- Qualified column references (NOT SUPPORTED)
@@ -2315,6 +2323,14 @@ WINDOW w AS (
DEFINE A AS COUNT(*) > 0
);
+-- 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: window 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 > row_number() OVER ());
+
-- Subquery in DEFINE is not supported
SELECT COUNT(*) OVER w
FROM rpr_err
@@ -2549,6 +2565,19 @@ SELECT COUNT(*) OVER w FROM rpr_plan
WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
PATTERN ((A{2}){3}) DEFINE A AS val > 0);
+-- Quantifier NO multiply: reluctant GROUP child (((A B){2}?){3}) stays nested
+-- a reluctant quantifier on a GROUP is not subject to multiplication
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) OVER w FROM rpr_plan
+WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (((A B){2}?){3}) DEFINE A AS val > 0, B AS val > 0);
+
+-- Quantifier multiply control: greedy GROUP (((A B){2}){3}) -> (a b){6}
+EXPLAIN (COSTS OFF)
+SELECT COUNT(*) OVER w FROM rpr_plan
+WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (((A B){2}){3}) DEFINE A AS val > 0, B AS val > 0);
+
-- Quantifier multiply with child range: (A{2,3}){3} -> a{6,9}
-- outer exact, child range - optimization applies
EXPLAIN (COSTS OFF)
--
2.50.1 (Apple Git-155)