From 033b6d0bd42a41b2501d34fb413cb889caffa366 Mon Sep 17 00:00:00 2001 From: Henson Choi 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)