nocfbot-0009-use-after-free.txt
text/plain
Filename: nocfbot-0009-use-after-free.txt
Type: text/plain
Part: 8
Message:
Re: Row pattern recognition
From 518d23ab243023ab94c5e4f4bca4d8a00c177ba1 Mon Sep 17 00:00:00 2001
From: Henson Choi <assam258@gmail.com>
Date: Thu, 26 Feb 2026 01:29:17 +0900
Subject: [PATCH 09/10] Fix use-after-free in NFA alternation and optional-VAR
routing
---
src/backend/executor/nodeWindowAgg.c | 42 +++++------
src/test/regress/expected/rpr_explain.out | 52 ++++++-------
src/test/regress/expected/rpr_nfa.out | 90 ++++++++++++++++++++++-
src/test/regress/sql/rpr_nfa.sql | 73 +++++++++++++++++-
4 files changed, 202 insertions(+), 55 deletions(-)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index 0aa5df3e153..cd41eff394e 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -6070,15 +6070,17 @@ nfa_route_to_elem(WindowAggState *winstate, RPRNFAContext *ctx,
{
if (RPRElemIsVar(nextElem))
{
- nfa_add_state_unique(winstate, ctx, state);
- if (RPRElemCanSkip(nextElem))
- {
- RPRNFAState *skipState;
+ RPRNFAState *skipState = NULL;
+ /* Create skip state before add_unique, which may free state */
+ if (RPRElemCanSkip(nextElem))
skipState = nfa_state_create(winstate, nextElem->next,
state->counts, state->isAbsorbable);
+
+ nfa_add_state_unique(winstate, ctx, state);
+
+ if (skipState != NULL)
nfa_advance_state(winstate, ctx, skipState, currentPos, initialAdvance);
- }
}
else
{
@@ -6099,7 +6101,6 @@ nfa_advance_alt(WindowAggState *winstate, RPRNFAContext *ctx,
RPRPattern *pattern = winstate->rpPattern;
RPRPatternElement *elements = pattern->elements;
RPRElemIdx altIdx = elem->next;
- bool first = true;
while (altIdx >= 0 && altIdx < pattern->numElements)
{
@@ -6110,25 +6111,16 @@ nfa_advance_alt(WindowAggState *winstate, RPRNFAContext *ctx,
if (altElem->depth <= elem->depth)
break;
- if (first)
- {
- state->elemIdx = altIdx;
- newState = state;
- first = false;
- }
- else
- {
- newState = nfa_state_create(winstate, altIdx,
- state->counts, state->isAbsorbable);
- }
+ /* Create independent state for each branch */
+ newState = nfa_state_create(winstate, altIdx,
+ state->counts, state->isAbsorbable);
/* Recursively process this branch before next */
nfa_advance_state(winstate, ctx, newState, currentPos, initialAdvance);
altIdx = altElem->jump;
}
- /* ALT must have at least one branch */
- Assert(!first);
+ nfa_state_free(winstate, state);
}
/*
@@ -6229,12 +6221,12 @@ nfa_advance_end(WindowAggState *winstate, RPRNFAContext *ctx,
currentPos, initialAdvance);
/*
- * Fast-forward fallback for nullable bodies. E.g. (A?){2,3} when
- * A doesn't match: the loop-back produces empty iterations that
- * cycle detection would kill. Instead, exit directly treating all
- * remaining required iterations as empty. Route to elem->next
- * (not nfa_advance_end) to avoid creating competing greedy/reluctant
- * loop states.
+ * Fast-forward fallback for nullable bodies. E.g. (A?){2,3} when A
+ * doesn't match: the loop-back produces empty iterations that cycle
+ * detection would kill. Instead, exit directly treating all
+ * remaining required iterations as empty. Route to elem->next (not
+ * nfa_advance_end) to avoid creating competing greedy/reluctant loop
+ * states.
*/
if (ffState != NULL)
{
diff --git a/src/test/regress/expected/rpr_explain.out b/src/test/regress/expected/rpr_explain.out
index dacb8d6907f..340408cc454 100644
--- a/src/test/regress/expected/rpr_explain.out
+++ b/src/test/regress/expected/rpr_explain.out
@@ -255,7 +255,7 @@ WINDOW w AS (
Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Pattern: a (b | c)
Storage: Memory Maximum Storage: NkB
- NFA States: 3 peak, 28 total, 0 merged
+ NFA States: 3 peak, 35 total, 0 merged
NFA Contexts: 2 peak, 21 total, 6 pruned
NFA: 7 matched (len 2/2/2.0), 0 mismatched
NFA: 0 absorbed, 7 skipped (len 1/1/1.0)
@@ -295,7 +295,7 @@ WINDOW w AS (
Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Pattern: a ((b | c) (d | e))*
Storage: Memory Maximum Storage: NkB
- NFA States: 4 peak, 49 total, 0 merged
+ NFA States: 4 peak, 61 total, 0 merged
NFA Contexts: 3 peak, 31 total, 24 pruned
NFA: 6 matched (len 1/1/1.0), 0 mismatched
-> Function Scan on generate_series s (actual rows=30.00 loops=1)
@@ -380,7 +380,7 @@ WINDOW w AS (
Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Pattern: (a | b | c) (d | e)
Storage: Memory Maximum Storage: NkB
- NFA States: 5 peak, 363 total, 0 merged
+ NFA States: 6 peak, 524 total, 0 merged
NFA Contexts: 3 peak, 101 total, 20 pruned
NFA: 20 matched (len 2/2/2.0), 40 mismatched (len 2/2/2.0)
NFA: 0 absorbed, 20 skipped (len 1/1/1.0)
@@ -507,7 +507,7 @@ WINDOW w AS (
Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Pattern: (a | b){8}
Storage: Memory Maximum Storage: NkB
- NFA States: 16 peak, 548 total, 0 merged
+ NFA States: 17 peak, 995 total, 0 merged
NFA Contexts: 8 peak, 101 total, 1 pruned
NFA: 12 matched (len 8/8/8.0), 3 mismatched (len 2/4/3.0)
NFA: 0 absorbed, 84 skipped (len 1/7/4.0)
@@ -548,7 +548,7 @@ WINDOW w AS (
Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Pattern: (a | b){2} (c | d)
Storage: Memory Maximum Storage: NkB
- NFA States: 6 peak, 111 total, 0 merged
+ NFA States: 7 peak, 181 total, 0 merged
NFA Contexts: 3 peak, 41 total, 12 pruned
NFA: 9 matched (len 3/3/3.0), 1 mismatched (len 2/2/2.0)
NFA: 0 absorbed, 18 skipped (len 1/2/1.5)
@@ -589,7 +589,7 @@ WINDOW w AS (
Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Pattern: (a | b){2} c
Storage: Memory Maximum Storage: NkB
- NFA States: 5 peak, 109 total, 0 merged
+ NFA States: 6 peak, 177 total, 0 merged
NFA Contexts: 3 peak, 41 total, 2 pruned
NFA: 12 matched (len 3/3/3.0), 2 mismatched (len 2/2/2.0)
NFA: 0 absorbed, 24 skipped (len 1/2/1.5)
@@ -629,7 +629,7 @@ WINDOW w AS (
Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Pattern: (a | b){3,}
Storage: Memory Maximum Storage: NkB
- NFA States: 6 peak, 161 total, 0 merged
+ NFA States: 7 peak, 243 total, 0 merged
NFA Contexts: 3 peak, 41 total, 0 pruned
NFA: 1 matched (len 40/40/40.0), 0 mismatched
NFA: 0 absorbed, 39 skipped (len 1/2/1.0)
@@ -669,7 +669,7 @@ WINDOW w AS (
Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Pattern: (a | b | c)+ d
Storage: Memory Maximum Storage: NkB
- NFA States: 15 peak, 753 total, 0 merged
+ NFA States: 16 peak, 1004 total, 0 merged
NFA Contexts: 4 peak, 101 total, 0 pruned
NFA: 25 matched (len 4/4/4.0), 0 mismatched
NFA: 0 absorbed, 75 skipped (len 1/3/2.0)
@@ -710,7 +710,7 @@ WINDOW w AS (
Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Pattern: (a | a b)+
Storage: Memory Maximum Storage: NkB
- NFA States: 5 peak, 204 total, 0 merged
+ NFA States: 6 peak, 306 total, 0 merged
NFA Contexts: 3 peak, 101 total, 99 pruned
NFA: 1 matched (len 1/1/1.0), 0 mismatched
-> Function Scan on generate_series s (actual rows=100.00 loops=1)
@@ -749,7 +749,7 @@ WINDOW w AS (
Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Pattern: (a | b)+
Storage: Memory Maximum Storage: NkB
- NFA States: 5 peak, 3336 total, 0 merged
+ NFA States: 6 peak, 5004 total, 0 merged
NFA Contexts: 3 peak, 1001 total, 333 pruned
NFA: 334 matched (len 1/2/2.0), 0 mismatched
NFA: 0 absorbed, 333 skipped (len 1/1/1.0)
@@ -1579,8 +1579,8 @@ WINDOW w AS (
"Pattern": "(a | b){8}", +
"Storage": "Memory", +
"Maximum Storage": 0, +
- "NFA States Peak": 16, +
- "NFA States Total": 548, +
+ "NFA States Peak": 17, +
+ "NFA States Total": 995, +
"NFA States Merged": 0, +
"NFA Contexts Peak": 8, +
"NFA Contexts Total": 101, +
@@ -1971,7 +1971,7 @@ WINDOW w AS (
Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Pattern: (a | b) (c | d | e)
Storage: Memory Maximum Storage: NkB
- NFA States: 5 peak, 282 total, 0 merged
+ NFA States: 6 peak, 423 total, 0 merged
NFA Contexts: 3 peak, 101 total, 40 pruned
NFA: 20 matched (len 2/2/2.0), 20 mismatched (len 2/2/2.0)
NFA: 0 absorbed, 20 skipped (len 1/1/1.0)
@@ -2337,7 +2337,7 @@ WINDOW w AS (
Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Pattern: (a | b)+ c
Storage: Memory Maximum Storage: NkB
- NFA States: 8 peak, 2004 total, 0 merged
+ NFA States: 9 peak, 3006 total, 0 merged
NFA Contexts: 3 peak, 501 total, 1 pruned
NFA: 166 matched (len 3/3/3.0), 1 mismatched (len 2/2/2.0)
NFA: 0 absorbed, 332 skipped (len 1/2/1.5)
@@ -2832,7 +2832,7 @@ WINDOW w AS (
Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Pattern: (a | b) c
Storage: Memory Maximum Storage: NkB
- NFA States: 3 peak, 202 total, 0 merged
+ NFA States: 4 peak, 303 total, 0 merged
NFA Contexts: 3 peak, 101 total, 40 pruned
NFA: 20 matched (len 2/2/2.0), 20 mismatched (len 2/2/2.0)
NFA: 0 absorbed, 20 skipped (len 1/1/1.0)
@@ -2876,7 +2876,7 @@ WINDOW w AS (
Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Pattern: (a | b | c | d) e
Storage: Memory Maximum Storage: NkB
- NFA States: 5 peak, 404 total, 0 merged
+ NFA States: 6 peak, 505 total, 0 merged
NFA Contexts: 3 peak, 101 total, 0 pruned
NFA: 20 matched (len 2/2/2.0), 60 mismatched (len 2/2/2.0)
NFA: 0 absorbed, 20 skipped (len 1/1/1.0)
@@ -2916,7 +2916,7 @@ WINDOW w AS (
Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Pattern: (a | b)+ c
Storage: Memory Maximum Storage: NkB
- NFA States: 8 peak, 204 total, 0 merged
+ NFA States: 9 peak, 306 total, 0 merged
NFA Contexts: 3 peak, 51 total, 1 pruned
NFA: 16 matched (len 3/3/3.0), 1 mismatched (len 2/2/2.0)
NFA: 0 absorbed, 32 skipped (len 1/2/1.5)
@@ -2954,7 +2954,7 @@ WINDOW w AS (
Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Pattern: (a | b | c | d | e)
Storage: Memory Maximum Storage: NkB
- NFA States: 6 peak, 505 total, 0 merged
+ NFA States: 7 peak, 606 total, 0 merged
NFA Contexts: 2 peak, 101 total, 0 pruned
NFA: 100 matched (len 1/1/1.0), 0 mismatched
-> Function Scan on generate_series s (actual rows=100.00 loops=1)
@@ -2991,7 +2991,7 @@ WINDOW w AS (
Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Pattern: (a | b) c d
Storage: Memory Maximum Storage: NkB
- NFA States: 3 peak, 122 total, 0 merged
+ NFA States: 4 peak, 183 total, 0 merged
NFA Contexts: 3 peak, 61 total, 16 pruned
NFA: 15 matched (len 3/3/3.0), 14 mismatched (len 2/2/2.0)
NFA: 0 absorbed, 15 skipped (len 1/1/1.0)
@@ -3029,7 +3029,7 @@ WINDOW w AS (
Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Pattern: (a | b) c (d | e) f
Storage: Memory Maximum Storage: NkB
- NFA States: 4 peak, 219 total, 0 merged
+ NFA States: 5 peak, 337 total, 0 merged
NFA Contexts: 3 peak, 101 total, 67 pruned
NFA: 0 matched, 33 mismatched (len 2/4/3.0)
-> Function Scan on generate_series s (actual rows=100.00 loops=1)
@@ -3066,7 +3066,7 @@ WINDOW w AS (
Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Pattern: (a+" | b+") c
Storage: Memory Maximum Storage: NkB
- NFA States: 4 peak, 162 total, 0 merged
+ NFA States: 5 peak, 223 total, 0 merged
NFA Contexts: 3 peak, 61 total, 1 pruned
NFA: 20 matched (len 2/2/2.0), 19 mismatched (len 2/2/2.0)
NFA: 0 absorbed, 20 skipped (len 1/1/1.0)
@@ -3104,7 +3104,7 @@ WINDOW w AS (
Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Pattern: a b (c | d)
Storage: Memory Maximum Storage: NkB
- NFA States: 3 peak, 75 total, 0 merged
+ NFA States: 3 peak, 89 total, 0 merged
NFA Contexts: 3 peak, 61 total, 32 pruned
NFA: 14 matched (len 3/3/3.0), 0 mismatched
NFA: 0 absorbed, 14 skipped (len 1/1/1.0)
@@ -3143,7 +3143,7 @@ WINDOW w AS (
Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Pattern: a ((b | c) d | e)
Storage: Memory Maximum Storage: NkB
- NFA States: 4 peak, 29 total, 0 merged
+ NFA States: 4 peak, 37 total, 0 merged
NFA Contexts: 3 peak, 21 total, 17 pruned
NFA: 0 matched, 3 mismatched (len 3/3/3.0)
-> Function Scan on generate_series s (actual rows=20.00 loops=1)
@@ -3181,7 +3181,7 @@ WINDOW w AS (
Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Pattern: (c (a | b) | d)
Storage: Memory Maximum Storage: NkB
- NFA States: 4 peak, 47 total, 0 merged
+ NFA States: 5 peak, 73 total, 0 merged
NFA Contexts: 3 peak, 21 total, 10 pruned
NFA: 5 matched (len 1/1/1.0), 5 mismatched (len 2/2/2.0)
-> Function Scan on generate_series s (actual rows=20.00 loops=1)
@@ -3341,7 +3341,7 @@ WINDOW w AS (
Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Pattern: (a | b)+
Storage: Memory Maximum Storage: NkB
- NFA States: 5 peak, 162 total, 0 merged
+ NFA States: 6 peak, 243 total, 0 merged
NFA Contexts: 2 peak, 41 total, 0 pruned
NFA: 1 matched (len 40/40/40.0), 0 mismatched
NFA: 0 absorbed, 39 skipped (len 1/1/1.0)
@@ -3379,7 +3379,7 @@ WINDOW w AS (
Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Pattern: (a | b){2,3} c
Storage: Memory Maximum Storage: NkB
- NFA States: 7 peak, 200 total, 0 merged
+ NFA States: 8 peak, 320 total, 0 merged
NFA Contexts: 3 peak, 61 total, 2 pruned
NFA: 19 matched (len 3/3/3.0), 1 mismatched (len 2/2/2.0)
NFA: 0 absorbed, 38 skipped (len 1/2/1.5)
diff --git a/src/test/regress/expected/rpr_nfa.out b/src/test/regress/expected/rpr_nfa.out
index 2ace2df13ca..03ee174d359 100644
--- a/src/test/regress/expected/rpr_nfa.out
+++ b/src/test/regress/expected/rpr_nfa.out
@@ -1944,6 +1944,92 @@ WINDOW w AS (
3 | {B,_} | |
(3 rows)
+-- Optional first branch in ALT with quantifier: (A? | B){1,2}
+-- First branch A? exit path may loop back to ALT and trigger cycle
+-- detection during DFS. All branches must receive correct counts.
+WITH test_alt_opt_first AS (
+ SELECT * FROM (VALUES
+ (1, ARRAY['B']),
+ (2, ARRAY['B']),
+ (3, ARRAY['B'])
+ ) AS t(id, flags)
+)
+SELECT id, flags,
+ first_value(id) OVER w AS match_start,
+ last_value(id) OVER w AS match_end
+FROM test_alt_opt_first
+WINDOW w AS (
+ ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN (((A? | B){1,2}))
+ DEFINE
+ A AS 'A' = ANY(flags),
+ B AS 'B' = ANY(flags)
+);
+ id | flags | match_start | match_end
+----+-------+-------------+-----------
+ 1 | {B} | 1 | 2
+ 2 | {B} | 2 | 3
+ 3 | {B} | 3 | 3
+(3 rows)
+
+-- Mixed A/B rows across iterations of (A? | B){1,2}
+WITH test_alt_opt_mixed AS (
+ SELECT * FROM (VALUES
+ (1, ARRAY['A']),
+ (2, ARRAY['B']),
+ (3, ARRAY['A','B'])
+ ) AS t(id, flags)
+)
+SELECT id, flags,
+ first_value(id) OVER w AS match_start,
+ last_value(id) OVER w AS match_end
+FROM test_alt_opt_mixed
+WINDOW w AS (
+ ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN (((A? | B){1,2}))
+ DEFINE
+ A AS 'A' = ANY(flags),
+ B AS 'B' = ANY(flags)
+);
+ id | flags | match_start | match_end
+----+-------+-------------+-----------
+ 1 | {A} | 1 | 2
+ 2 | {B} | 2 | 3
+ 3 | {A,B} | 3 | 3
+(3 rows)
+
+-- Reluctant variant: (A?? | B){1,2}
+WITH test_alt_opt_reluctant AS (
+ SELECT * FROM (VALUES
+ (1, ARRAY['B']),
+ (2, ARRAY['B']),
+ (3, ARRAY['B'])
+ ) AS t(id, flags)
+)
+SELECT id, flags,
+ first_value(id) OVER w AS match_start,
+ last_value(id) OVER w AS match_end
+FROM test_alt_opt_reluctant
+WINDOW w AS (
+ ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN (((A?? | B){1,2}))
+ DEFINE
+ A AS 'A' = ANY(flags),
+ B AS 'B' = ANY(flags)
+);
+ id | flags | match_start | match_end
+----+-------+-------------+-----------
+ 1 | {B} | 1 | 2
+ 2 | {B} | 2 | 3
+ 3 | {B} | 3 | 3
+(3 rows)
+
-- ============================================================
-- Deep Nested Groups
-- ============================================================
@@ -3381,7 +3467,7 @@ WINDOW w AS (
-- A never matches. Need 2 empty iterations to satisfy min=2.
-- Per standard: STR06=(STRE STRE) is valid for min=2.
-- Expected: empty match for every row
--- BUG: visited bitmap blocks second empty iteration → match failure
+-- XXX: visited bitmap blocks second empty iteration → match failure
WITH test_728_min2 AS (
SELECT * FROM (VALUES
(1, ARRAY['B']),
@@ -3482,7 +3568,7 @@ WINDOW w AS (
(6 rows)
-- (A? B?){2,3}: pure empty body (nothing matches)
--- All NULL: same known bug as test_728_min2 (initialAdvance blocks FIN
+-- XXX: All NULL: same issue as test_728_min2 (initialAdvance blocks FIN
-- for zero-length matches at context start)
WITH test_728_multi_empty AS (
SELECT * FROM (VALUES
diff --git a/src/test/regress/sql/rpr_nfa.sql b/src/test/regress/sql/rpr_nfa.sql
index 27426b1ac4f..a3f01b60bc4 100644
--- a/src/test/regress/sql/rpr_nfa.sql
+++ b/src/test/regress/sql/rpr_nfa.sql
@@ -1413,6 +1413,74 @@ WINDOW w AS (
B AS 'B' = ANY(flags)
);
+-- Optional first branch in ALT with quantifier: (A? | B){1,2}
+-- First branch A? exit path may loop back to ALT and trigger cycle
+-- detection during DFS. All branches must receive correct counts.
+WITH test_alt_opt_first AS (
+ SELECT * FROM (VALUES
+ (1, ARRAY['B']),
+ (2, ARRAY['B']),
+ (3, ARRAY['B'])
+ ) AS t(id, flags)
+)
+SELECT id, flags,
+ first_value(id) OVER w AS match_start,
+ last_value(id) OVER w AS match_end
+FROM test_alt_opt_first
+WINDOW w AS (
+ ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN (((A? | B){1,2}))
+ DEFINE
+ A AS 'A' = ANY(flags),
+ B AS 'B' = ANY(flags)
+);
+
+-- Mixed A/B rows across iterations of (A? | B){1,2}
+WITH test_alt_opt_mixed AS (
+ SELECT * FROM (VALUES
+ (1, ARRAY['A']),
+ (2, ARRAY['B']),
+ (3, ARRAY['A','B'])
+ ) AS t(id, flags)
+)
+SELECT id, flags,
+ first_value(id) OVER w AS match_start,
+ last_value(id) OVER w AS match_end
+FROM test_alt_opt_mixed
+WINDOW w AS (
+ ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN (((A? | B){1,2}))
+ DEFINE
+ A AS 'A' = ANY(flags),
+ B AS 'B' = ANY(flags)
+);
+
+-- Reluctant variant: (A?? | B){1,2}
+WITH test_alt_opt_reluctant AS (
+ SELECT * FROM (VALUES
+ (1, ARRAY['B']),
+ (2, ARRAY['B']),
+ (3, ARRAY['B'])
+ ) AS t(id, flags)
+)
+SELECT id, flags,
+ first_value(id) OVER w AS match_start,
+ last_value(id) OVER w AS match_end
+FROM test_alt_opt_reluctant
+WINDOW w AS (
+ ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ PATTERN (((A?? | B){1,2}))
+ DEFINE
+ A AS 'A' = ANY(flags),
+ B AS 'B' = ANY(flags)
+);
+
-- ============================================================
-- Deep Nested Groups
-- ============================================================
@@ -2547,7 +2615,7 @@ WINDOW w AS (
-- A never matches. Need 2 empty iterations to satisfy min=2.
-- Per standard: STR06=(STRE STRE) is valid for min=2.
-- Expected: empty match for every row
--- BUG: visited bitmap blocks second empty iteration → match failure
+-- XXX: visited bitmap blocks second empty iteration → match failure
WITH test_728_min2 AS (
SELECT * FROM (VALUES
(1, ARRAY['B']),
@@ -2626,7 +2694,7 @@ WINDOW w AS (
);
-- (A? B?){2,3}: pure empty body (nothing matches)
--- All NULL: same known bug as test_728_min2 (initialAdvance blocks FIN
+-- XXX: All NULL: same issue as test_728_min2 (initialAdvance blocks FIN
-- for zero-length matches at context start)
WITH test_728_multi_empty AS (
SELECT * FROM (VALUES
@@ -2705,3 +2773,4 @@ WINDOW w AS (
A AS price > 100,
B AS TRUE
);
+
--
2.50.1 (Apple Git-155)