From c0aa0a1ca48f6c520658a50560b86d708118b9a0 Mon Sep 17 00:00:00 2001 From: Henson Choi Date: Wed, 3 Jun 2026 23:10:49 +0900 Subject: [PATCH 43/68] Generalize quantifier multiplication in row pattern recognition The planner flattens a nested pattern quantifier (child{p,q}){m,n} into child{p*m, q*n}. This is valid only when the repetition counts the nested quantifiers can produce form exactly one contiguous interval. Replace the previous handful of special cases with that general test: fold when the outer count is exact (m == n), or the child is skippable (p == 0), or the per-iteration intervals touch (p <= Max(m,1)*(q-p) + 1) and, for a skippable outer, the zero case connects to the child range (m >= 1 or p <= 1). This additionally folds contiguous cases that were previously left unflattened, such as (A{2,3}){2,3} -> a{4,9}, (A+){3} -> a{3,}, and (A+){2,4} -> a{2,}, while still leaving gap-producing patterns like (A{2}){2,3} and (A{2,})* untouched. The int64 overflow guards on the multiplied bounds are retained. Expand the quatifier-multiplication regression cases to exercise each branch with both folding and gap (no-fold) patterns. The pattern-nesting depth tests now use reluctant quantifiers, which are not subject to multiplication, so they still build the intended nesting depth. --- src/backend/optimizer/plan/rpr.c | 130 +++++++++--------- src/test/regress/expected/rpr_base.out | 154 +++++++++++++++++++++- src/test/regress/expected/rpr_explain.out | 16 +-- src/test/regress/sql/rpr_base.sql | 72 +++++++++- 4 files changed, 284 insertions(+), 88 deletions(-) diff --git a/src/backend/optimizer/plan/rpr.c b/src/backend/optimizer/plan/rpr.c index 4652ca6ebeb..617a3869948 100644 --- a/src/backend/optimizer/plan/rpr.c +++ b/src/backend/optimizer/plan/rpr.c @@ -784,21 +784,25 @@ optimizeAltPattern(RPRPatternNode *pattern) /* * tryMultiplyQuantifiers - * Try to multiply quantifiers. - * - * Multiplication is SAFE when: - * 1. Both unbounded, with skipless outer or child->min <= 1: - * (A*)* -> A*, (A+)+ -> A+, (A+)* -> A*, (A{2,})+ -> A{2,} - * 2. Outer exact: (A{m,n}){k} -> A{m*k, n*k} - * 3. Outer range + child {1,1}: (A){2,} -> A{2,} - * - * Multiplication is NOT safe when: - * - Only child unbounded: (A+){3} has different semantics - * - Outer range + child not {1,1}: gaps possible - * e.g., (A{2}){2,3} yields 4,6 only (not 4,5,6) - * - Skippable outer (min 0) + child->min >= 2: (A{2,})* reaches - * {0} UNION [child->min, INF), so 1..child->min-1 are unreachable - * and A* would wrongly admit them + * Try to flatten (child{p,q}){m,n} into child{p*m, q*n}. + * + * Below, p,q are the child's {min,max} and m,n the outer {min,max}. + * + * Flattening is valid only when the repetition counts the nested quantifiers + * can produce form exactly the contiguous interval [p*m, q*n]. For an outer + * iteration count t (m <= t <= n) the child contributes any count in + * [t*p, t*q], and t = 0 contributes {0}. The union of those intervals is + * contiguous, hence flattenable, when: + * + * - m == n: a single outer count, so the result is just [m*p, m*q]; or + * - p == 0: every interval starts at 0, so they all overlap; or + * - consecutive intervals touch and the zero case (if any) connects: + * p <= Max(m,1)*(q-p) + 1 (touch; trivially true if q is unbounded) + * and (m >= 1 or p <= 1) (when m == 0, {0} must reach [p,q]) + * + * Otherwise gaps appear and the pattern is left unflattened: (A{2}){2,3} + * yields {4,6} (not 4..6), and (A{2,})* yields {0} UNION [2,INF) (not + * [0,INF), so A* would wrongly admit a single A). * * Returns the child node with multiplied quantifiers if successful, * otherwise returns the original pattern unchanged. @@ -807,6 +811,7 @@ static RPRPatternNode * tryMultiplyQuantifiers(RPRPatternNode *pattern) { RPRPatternNode *child; + bool safe; int64 new_min_64; int64 new_max_64; @@ -823,69 +828,60 @@ tryMultiplyQuantifiers(RPRPatternNode *pattern) child->reluctant) return pattern; - /* Case 1: Both unbounded - (A*)* -> A*, (A+)+ -> A+ */ - if (child->max == RPR_QUANTITY_INF && pattern->max == RPR_QUANTITY_INF) + /* + * Decide whether the achievable counts form one contiguous interval. The + * child quantifier is {child->min, child->max} and the outer one is + * {pattern->min, pattern->max}; either max may be RPR_QUANTITY_INF. + */ + if (pattern->min == pattern->max || child->min == 0) + safe = true; + else { + bool touch; + bool zero_ok; + /* - * A skippable outer (min 0) over a child with min >= 2 reaches - * repetition counts {0} UNION [child->min, INF): the counts - * 1..child->min-1 are unreachable, and no single quantifier can - * express that gap. Flattening to A{0,INF} = A* would wrongly admit - * them, e.g. (A{2,})* would match a single A. Multiplication is safe - * here only when child->min <= 1 (the reachable set is then - * contiguous from 0); otherwise leave the pattern unflattened. + * Consecutive intervals [t*min, t*max] and [(t+1)*min, (t+1)*max] + * touch when (t+1)*min <= t*max + 1, i.e. min <= t*(max-min) + 1. + * This is tightest at the smallest t in play, Max(pattern->min, 1). + * An unbounded child->max makes every interval reach INF, so they + * always touch. */ - if (pattern->min == 0 && child->min >= 2) - return pattern; + if (child->max == RPR_QUANTITY_INF) + touch = true; + else + touch = ((int64) child->min <= + (int64) Max(pattern->min, 1) * (child->max - child->min) + 1); - new_min_64 = (int64) child->min * pattern->min; - if (new_min_64 >= RPR_QUANTITY_INF) - return pattern; /* overflow, skip optimization */ + /* + * A skippable outer (min 0) also needs {0} adjacent to the child + * range. + */ + zero_ok = (pattern->min >= 1 || child->min <= 1); - child->min = (int) new_min_64; - child->max = RPR_QUANTITY_INF; - return child; + safe = touch && zero_ok; } - /*---------- - * Case 2: Outer exact (min == max): (A{2,3}){4} -> A{8,12}. - * Safe because every iteration produces the same range. - * - * Case 3: Child {1,1}: (A){2,5} -> A{2,5}. - * Safe because the child contributes exactly one per - * iteration, so the outer range maps directly. - * - * Unsafe example: (A{2}){2,3} produces counts 4 or 6 only, not the full - * range 4..6, so we cannot flatten when child has a non-trivial range AND - * outer is also a range. - *---------- - */ - if (child->max != RPR_QUANTITY_INF && - (pattern->min == pattern->max || - (child->min == 1 && child->max == 1))) - { - new_min_64 = (int64) pattern->min * child->min; - if (new_min_64 >= RPR_QUANTITY_INF) - return pattern; - - /* Outer unbounded: result is unbounded regardless of child */ - if (pattern->max == RPR_QUANTITY_INF) - new_max_64 = RPR_QUANTITY_INF; - else - { - new_max_64 = (int64) pattern->max * child->max; + if (!safe) + return pattern; - if (new_max_64 >= RPR_QUANTITY_INF) - return pattern; - } + /* Flatten the child quantifier, guarding against overflow. */ + new_min_64 = (int64) pattern->min * child->min; + if (new_min_64 >= RPR_QUANTITY_INF) + return pattern; /* overflow, skip optimization */ - child->min = (int) new_min_64; - child->max = (int) new_max_64; - return child; + if (pattern->max == RPR_QUANTITY_INF || child->max == RPR_QUANTITY_INF) + new_max_64 = RPR_QUANTITY_INF; + else + { + new_max_64 = (int64) pattern->max * child->max; + if (new_max_64 >= RPR_QUANTITY_INF) + return pattern; } - /* Not safe to multiply */ - return pattern; + child->min = (int) new_min_64; + child->max = (int) new_max_64; + return child; } /* diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out index e9303af6384..1410ba75395 100644 --- a/src/test/regress/expected/rpr_base.out +++ b/src/test/regress/expected/rpr_base.out @@ -3785,8 +3785,8 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING -> Seq Scan on rpr_plan (7 rows) --- Quantifier NO multiply: (A{2,3}){2,3} stays as (a{2,3}){2,3} --- outer range, child range - gaps possible (e.g., (A{4,5}){2,3} misses 11) +-- Quantifier multiply: (A{2,3}){2,3} -> a{4,9} +-- outer range, child range: counts [4,6] U [6,9] = [4,9] are contiguous, so it folds EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_plan WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING @@ -3795,7 +3795,24 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ------------------------------------------------------------------------------- WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a{2,3}){2,3} + Pattern: a{4,9} + Nav Mark Lookback: 0 + -> Sort + Sort Key: id + -> Seq Scan on rpr_plan +(7 rows) + +-- Quantifier NO multiply: (A{4,5}){2,3} stays as (a{4,5}){2,3} +-- outer range, child range with a gap: [8,10] U [12,15] misses 11 +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{4,5}){2,3}) DEFINE A AS val > 0); + QUERY PLAN +------------------------------------------------------------------------------- + WindowAgg + Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: (a{4,5}){2,3} Nav Mark Lookback: 0 -> Sort Sort Key: id @@ -3850,6 +3867,125 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING -> Seq Scan on rpr_plan (7 rows) +-- Quantifier multiply with an unbounded child: an exact outer count (m == n) +-- always folds regardless of the child's max - (A+){3} -> a{3,} +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+){3}) DEFINE A AS val > 0); + QUERY PLAN +------------------------------------------------------------------------------- + WindowAgg + Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a{3,}" + Nav Mark Lookback: 0 + -> Sort + Sort Key: id + -> Seq Scan on rpr_plan +(7 rows) + +-- (A{2,}){3} -> a{6,} (m == n, unbounded child with min 2) +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{2,}){3}) DEFINE A AS val > 0); + QUERY PLAN +------------------------------------------------------------------------------- + WindowAgg + Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a{6,}" + Nav Mark Lookback: 0 + -> Sort + Sort Key: id + -> Seq Scan on rpr_plan +(7 rows) + +-- (A+){2,4} -> a{2,} (outer range, unbounded child: every interval reaches INF, +-- so they always touch) +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+){2,4}) DEFINE A AS val > 0); + QUERY PLAN +------------------------------------------------------------------------------- + WindowAgg + Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a{2,}" + Nav Mark Lookback: 0 + -> Sort + Sort Key: id + -> Seq Scan on rpr_plan +(7 rows) + +-- (A{2,3}){2,4} -> a{4,12} (outer range x child range, contiguous: +-- [4,6] U [6,9] U [8,12] = [4,12]) +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{2,3}){2,4}) DEFINE A AS val > 0); + QUERY PLAN +------------------------------------------------------------------------------- + WindowAgg + Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a{4,12} + Nav Mark Lookback: 0 + -> Sort + Sort Key: id + -> Seq Scan on rpr_plan +(7 rows) + +-- Skippable outer (min 0) folds only when the zero case connects to the child +-- range: (A{1,3})? -> a{0,3} (child min <= 1, so {0} U [1,3] = [0,3] is contiguous) +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{1,3})?) DEFINE A AS val > 0); + QUERY PLAN +------------------------------------------------------------------------------- + WindowAgg + Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a{0,3} + Nav Mark Lookback: 0 + -> Sort + Sort Key: id + -> Seq Scan on rpr_plan +(7 rows) + +-- Quantifier NO multiply: (A{2,3})? stays as (a{2,3})? +-- min 0 with child min >= 2: {0} U [2,3] leaves 1 unreachable (intervals touch but +-- the zero case does not connect) +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{2,3})?) DEFINE A AS val > 0); + QUERY PLAN +------------------------------------------------------------------------------- + WindowAgg + Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: (a{2,3})? + Nav Mark Lookback: 0 + -> Sort + Sort Key: id + -> Seq Scan on rpr_plan +(7 rows) + +-- Quantifier NO multiply: (A{3,4})? stays as (a{3,4})? +-- min 0 with child min >= 2: {0} U [3,4] leaves 1,2 unreachable +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{3,4})?) DEFINE A AS val > 0); + QUERY PLAN +------------------------------------------------------------------------------- + WindowAgg + Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: (a{3,4})? + Nav Mark Lookback: 0 + -> Sort + Sort Key: id + -> Seq Scan on rpr_plan +(7 rows) + -- Unwrap GROUP{1,1}: (A) -> a EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_plan @@ -6355,12 +6491,14 @@ 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 produce depth 253 after optimization +-- Note: 253 nested GROUP{3,7}? quantifiers; reluctant quantifiers are not +-- subject to quantifier multiplication, so the nesting (and depth 253) is +-- preserved after optimization. SELECT id, val, COUNT(*) OVER w FROM rpr_errors WINDOW w AS ( ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - 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}) + 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}?) DEFINE A AS val > 0 ); id | val | count @@ -6371,12 +6509,14 @@ WINDOW w AS ( -- Expected: Should succeed -- Test: Pattern nesting depth exceeds maximum (depth 254) --- Note: 254 nested GROUP{3,7} quantifiers produce depth 254 after optimization +-- Note: 254 nested GROUP{3,7}? quantifiers; reluctant quantifiers are not +-- subject to quantifier multiplication, so the nesting reaches depth 254 and +-- exceeds the limit. SELECT id, val, COUNT(*) OVER w FROM rpr_errors WINDOW w AS ( ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - 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}) + 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 ); ERROR: pattern nesting too deep diff --git a/src/test/regress/expected/rpr_explain.out b/src/test/regress/expected/rpr_explain.out index 9ba302b11ae..5cddd1a56df 100644 --- a/src/test/regress/expected/rpr_explain.out +++ b/src/test/regress/expected/rpr_explain.out @@ -2178,10 +2178,10 @@ WINDOW w AS ( --------------------------------------------------------------------- WindowAgg (actual rows=3.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a?){0,3} + Pattern: a{0,3} Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB - NFA States: 6 peak, 20 total, 4 merged + NFA States: 3 peak, 8 total, 0 merged NFA Contexts: 2 peak, 4 total, 0 pruned NFA: 3 matched (len 0/0/0.0), 0 mismatched -> Function Scan on generate_series s (actual rows=3.00 loops=1) @@ -2217,10 +2217,10 @@ WINDOW w AS ( --------------------------------------------------------------------- WindowAgg (actual rows=3.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a?){1,3} + Pattern: a{0,3} Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB - NFA States: 5 peak, 16 total, 4 merged + NFA States: 3 peak, 8 total, 0 merged NFA Contexts: 2 peak, 4 total, 0 pruned NFA: 3 matched (len 0/0/0.0), 0 mismatched -> Function Scan on generate_series s (actual rows=3.00 loops=1) @@ -2256,10 +2256,10 @@ WINDOW w AS ( --------------------------------------------------------------------- WindowAgg (actual rows=3.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a?){2,3} + Pattern: a{0,3} Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB - NFA States: 5 peak, 16 total, 4 merged + NFA States: 3 peak, 8 total, 0 merged NFA Contexts: 2 peak, 4 total, 0 pruned NFA: 3 matched (len 0/0/0.0), 0 mismatched -> Function Scan on generate_series s (actual rows=3.00 loops=1) @@ -2295,10 +2295,10 @@ WINDOW w AS ( --------------------------------------------------------------------- WindowAgg (actual rows=4.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: (a?){2,3} + Pattern: a{0,3} Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB - NFA States: 8 peak, 26 total, 5 merged + NFA States: 6 peak, 13 total, 0 merged NFA Contexts: 4 peak, 5 total, 1 pruned NFA: 3 matched (len 0/2/1.0), 0 mismatched -> Function Scan on generate_series s (actual rows=4.00 loops=1) diff --git a/src/test/regress/sql/rpr_base.sql b/src/test/regress/sql/rpr_base.sql index 475541d4550..53bf090b903 100644 --- a/src/test/regress/sql/rpr_base.sql +++ b/src/test/regress/sql/rpr_base.sql @@ -2516,13 +2516,20 @@ SELECT COUNT(*) OVER w FROM rpr_plan WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN ((A{2}){3,5}) DEFINE A AS val > 0); --- Quantifier NO multiply: (A{2,3}){2,3} stays as (a{2,3}){2,3} --- outer range, child range - gaps possible (e.g., (A{4,5}){2,3} misses 11) +-- Quantifier multiply: (A{2,3}){2,3} -> a{4,9} +-- outer range, child range: counts [4,6] U [6,9] = [4,9] are contiguous, so it folds 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{2,3}){2,3}) DEFINE A AS val > 0); +-- Quantifier NO multiply: (A{4,5}){2,3} stays as (a{4,5}){2,3} +-- outer range, child range with a gap: [8,10] U [12,15] misses 11 +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{4,5}){2,3}) DEFINE A AS val > 0); + -- Nested unbounded: (A*)* -> a* EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_plan @@ -2541,6 +2548,55 @@ SELECT COUNT(*) OVER w FROM rpr_plan WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN ((A+)+) DEFINE A AS val > 0); +-- Quantifier multiply with an unbounded child: an exact outer count (m == n) +-- always folds regardless of the child's max - (A+){3} -> a{3,} +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+){3}) DEFINE A AS val > 0); + +-- (A{2,}){3} -> a{6,} (m == n, unbounded child with min 2) +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{2,}){3}) DEFINE A AS val > 0); + +-- (A+){2,4} -> a{2,} (outer range, unbounded child: every interval reaches INF, +-- so they always touch) +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+){2,4}) DEFINE A AS val > 0); + +-- (A{2,3}){2,4} -> a{4,12} (outer range x child range, contiguous: +-- [4,6] U [6,9] U [8,12] = [4,12]) +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{2,3}){2,4}) DEFINE A AS val > 0); + +-- Skippable outer (min 0) folds only when the zero case connects to the child +-- range: (A{1,3})? -> a{0,3} (child min <= 1, so {0} U [1,3] = [0,3] is contiguous) +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{1,3})?) DEFINE A AS val > 0); + +-- Quantifier NO multiply: (A{2,3})? stays as (a{2,3})? +-- min 0 with child min >= 2: {0} U [2,3] leaves 1 unreachable (intervals touch but +-- the zero case does not connect) +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{2,3})?) DEFINE A AS val > 0); + +-- Quantifier NO multiply: (A{3,4})? stays as (a{3,4})? +-- min 0 with child min >= 2: {0} U [3,4] leaves 1,2 unreachable +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{3,4})?) DEFINE A AS val > 0); + -- Unwrap GROUP{1,1}: (A) -> a EXPLAIN (COSTS OFF) SELECT COUNT(*) OVER w FROM rpr_plan @@ -4017,23 +4073,27 @@ WINDOW w AS ( -- Expected: ERROR - too many pattern variables (Maximum is 240) -- Test: Pattern nesting at maximum depth (depth 253) --- Note: 253 nested GROUP{3,7} quantifiers produce depth 253 after optimization +-- Note: 253 nested GROUP{3,7}? quantifiers; reluctant quantifiers are not +-- subject to quantifier multiplication, so the nesting (and depth 253) is +-- preserved after optimization. SELECT id, val, COUNT(*) OVER w FROM rpr_errors WINDOW w AS ( ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - 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}) + 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}?) DEFINE A AS val > 0 ); -- Expected: Should succeed -- Test: Pattern nesting depth exceeds maximum (depth 254) --- Note: 254 nested GROUP{3,7} quantifiers produce depth 254 after optimization +-- Note: 254 nested GROUP{3,7}? quantifiers; reluctant quantifiers are not +-- subject to quantifier multiplication, so the nesting reaches depth 254 and +-- exceeds the limit. SELECT id, val, COUNT(*) OVER w FROM rpr_errors WINDOW w AS ( ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - 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}) + 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 -- 2.50.1 (Apple Git-155)