rpr_explain.sql

--
-- Test: EXPLAIN ANALYZE output for Row Pattern Recognition NFA statistics
--
-- This file tests the NFA statistics shown in EXPLAIN ANALYZE output:
-- - NFA States: peak, total, merged
-- - NFA Contexts: peak, total, absorbed, skipped
-- - NFA: matched (len min/max/avg), mismatched (len min/max/avg)
--

-- Setup: Create test tables
CREATE TEMP TABLE nfa_test (
    id serial,
    v int,
    cat char(1)
);

-- Insert test data: 100 rows with predictable pattern
INSERT INTO nfa_test (v, cat)
SELECT i,
       CASE
           WHEN i % 5 = 1 THEN 'A'
           WHEN i % 5 = 2 THEN 'B'
           WHEN i % 5 = 3 THEN 'C'
           WHEN i % 5 = 4 THEN 'D'
           ELSE 'E'
       END
FROM generate_series(1, 100) i;

-- Additional test table with more complex patterns
CREATE TEMP TABLE nfa_complex (
    id serial,
    price int,
    trend char(1)  -- U=up, D=down, S=stable
);

INSERT INTO nfa_complex (price, trend)
VALUES
    (100, 'S'), (105, 'U'), (110, 'U'), (108, 'D'), (112, 'U'),
    (115, 'U'), (113, 'D'), (111, 'D'), (109, 'D'), (110, 'U'),
    (120, 'U'), (125, 'U'), (130, 'U'), (128, 'D'), (126, 'D'),
    (124, 'D'), (122, 'D'), (120, 'D'), (118, 'D'), (119, 'U'),
    (121, 'U'), (123, 'U'), (125, 'U'), (127, 'U'), (129, 'U'),
    (131, 'U'), (133, 'U'), (130, 'D'), (127, 'D'), (124, 'D');

--
-- Section 1: Basic NFA Statistics Tests
--

-- Test 1.1: Simple pattern - should show basic statistics
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM nfa_test
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A B)
    DEFINE A AS cat = 'A', B AS cat = 'B'
);

-- Test 1.2: Pattern with no matches - 0 matched
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM nfa_test
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (X Y Z)
    DEFINE X AS cat = 'X', Y AS cat = 'Y', Z AS cat = 'Z'
);

-- Test 1.3: Pattern matching every row - high match count
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM nfa_test
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (R)
    DEFINE R AS TRUE
);

--
-- Section 2: State Statistics Tests (peak, total, merged)
--

-- Test 2.1: Simple quantifier pattern - A+ with short matches (no merging)
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 50) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A+)
    DEFINE A AS v % 2 = 1
);

-- Test 2.2: Alternation pattern - multiple state branches
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM nfa_test
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN ((A | B | C) (D | E))
    DEFINE
        A AS cat = 'A', B AS cat = 'B', C AS cat = 'C',
        D AS cat = 'D', E AS cat = 'E'
);

-- Test 2.3: Complex pattern with high state count
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 100) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A+ B* C+)
    DEFINE
        A AS v % 3 = 1,
        B AS v % 3 = 2,
        C AS v % 3 = 0
);

-- Test 2.4: Grouped pattern with quantifier - state merging
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 60) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN ((A B)+)
    DEFINE A AS v % 2 = 1, B AS v % 2 = 0
);

-- Test 2.5: State explosion pattern - many alternations
-- Pattern (A|B)(A|B)(A|B)(A|B) can create many parallel states
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 100) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN ((A | B) (A | B) (A | B) (A | B) (A | B) (A | B) (A | B) (A | B))
    DEFINE A AS v % 2 = 1, B AS v % 2 = 0
);

-- Test 2.6: High state merging - alternation with plus quantifier
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 100) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN ((A | B | C)+ D)
    DEFINE A AS v % 4 = 1, B AS v % 4 = 2, C AS v % 4 = 3, D AS v % 4 = 0
);

-- Test 2.7: Nested quantifiers causing state growth
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 1000) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (((A | B)+)+)
    DEFINE A AS v % 3 = 1, B AS v % 3 = 2
);

--
-- Section 3: Context Statistics Tests (peak, total, absorbed, skipped)
--

-- Test 3.1: Context absorption with unbounded quantifier at start
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 50) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A+ B)
    DEFINE A AS v % 5 <> 0, B AS v % 5 = 0
);

-- Test 3.2: No absorption - bounded quantifier
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 50) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A{2,4} B)
    DEFINE A AS v % 5 <> 0, B AS v % 5 = 0
);

-- Test 3.3: Contexts skipped by SKIP PAST LAST ROW
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 100) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A B C)
    DEFINE A AS v % 10 = 1, B AS v % 10 = 2, C AS v % 10 = 3
);

-- Test 3.4: High context absorption - unbounded group
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 100) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN ((A B)+ C)
    DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0
);

--
-- Section 4: Match Length Statistics Tests
--

-- Test 4.1: Fixed length matches - all same length
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM nfa_test
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A B C D E)
    DEFINE
        A AS cat = 'A', B AS cat = 'B', C AS cat = 'C',
        D AS cat = 'D', E AS cat = 'E'
);

-- Test 4.2: Variable length matches - min/max/avg differ
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 100) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A+ B)
    DEFINE A AS v % 10 <> 0, B AS v % 10 = 0
);

-- Test 4.3: Very long matches
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 200) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A+ B)
    DEFINE A AS v <= 195, B AS v > 195
);

-- Test 4.4: Mix of short and long matches
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 100) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A+ B)
    DEFINE
        A AS (v % 20 <> 0) AND (v % 20 <= 10 OR v % 20 > 15),
        B AS v % 20 = 0
);

--
-- Section 5: Mismatch Length Statistics Tests
--

-- Test 5.1: Pattern that causes mismatches with length > 1
-- Mismatch happens when partial match fails after processing multiple rows
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM (
    SELECT v,
           CASE WHEN v % 10 IN (1,2,3) THEN 'A'
                WHEN v % 10 IN (4,5) THEN 'B'
                WHEN v % 10 = 6 THEN 'C'
                ELSE 'X' END AS cat
    FROM generate_series(1, 100) AS s(v)
) t
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A+ B+ C)
    DEFINE A AS cat = 'A', B AS cat = 'B', C AS cat = 'C'
);

-- Test 5.2: Long partial matches that fail
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM (
    SELECT i AS v,
           CASE
               WHEN i <= 20 THEN 'A'
               WHEN i <= 25 THEN 'B'
               WHEN i = 26 THEN 'X'  -- breaks the pattern
               WHEN i <= 50 THEN 'A'
               WHEN i <= 55 THEN 'B'
               WHEN i = 56 THEN 'C'  -- completes pattern
               ELSE 'Y'
           END AS cat
    FROM generate_series(1, 60) i
) t
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A+ B+ C)
    DEFINE A AS cat = 'A', B AS cat = 'B', C AS cat = 'C'
);

--
-- Section 6: JSON Format Tests
--

-- Test 6.1: JSON format output with all statistics
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF, FORMAT JSON)
SELECT count(*) OVER w
FROM generate_series(1, 50) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A+ B+)
    DEFINE A AS v % 3 = 1, B AS v % 3 = 2
);

-- Test 6.2: JSON format with match length statistics
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF, FORMAT JSON)
SELECT count(*) OVER w
FROM generate_series(1, 100) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A+ B)
    DEFINE A AS v % 10 <> 0, B AS v % 10 = 0
);

--
-- Section 7: XML Format Tests
--

-- Test 7.1: XML format output
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF, FORMAT XML)
SELECT count(*) OVER w
FROM generate_series(1, 30) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A B)
    DEFINE A AS v % 2 = 1, B AS v % 2 = 0
);

--
-- Section 8: Multiple Partitions Tests
--

-- Test 8.1: Statistics across multiple partitions
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM (
    SELECT p, v
    FROM generate_series(1, 3) p,
         generate_series(1, 30) v
) t
WINDOW w AS (
    PARTITION BY p
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A+ B)
    DEFINE A AS v % 5 <> 0, B AS v % 5 = 0
);

-- Test 8.2: Different pattern behavior per partition
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM (
    SELECT
        CASE WHEN v <= 25 THEN 1 ELSE 2 END AS p,
        v % 10 AS val
    FROM generate_series(1, 50) v
) t
WINDOW w AS (
    PARTITION BY p
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A+ B)
    DEFINE A AS val < 5, B AS val >= 5
);

--
-- Section 9: Edge Cases
--

-- Test 9.1: Empty result set
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 0) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A B)
    DEFINE A AS v = 1, B AS v = 2
);

-- Test 9.2: Single row
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 1) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A)
    DEFINE A AS TRUE
);

-- Test 9.3: Pattern longer than data
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 5) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A B C D E F G H I J)
    DEFINE
        A AS v = 1, B AS v = 2, C AS v = 3, D AS v = 4, E AS v = 5,
        F AS v = 6, G AS v = 7, H AS v = 8, I AS v = 9, J AS v = 10
);

-- Test 9.4: All rows match as single match
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 50) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A+)
    DEFINE A AS TRUE
);

--
-- Section 10: Complex Pattern Tests
--

-- Test 10.1: Nested groups
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 60) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (((A B) C)+)
    DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0
);

-- Test 10.2: Multiple alternations
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM nfa_test
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN ((A | B) (C | D | E))
    DEFINE
        A AS cat = 'A', B AS cat = 'B', C AS cat = 'C',
        D AS cat = 'D', E AS cat = 'E'
);

-- Test 10.3: Optional elements
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 50) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A B? C)
    DEFINE A AS v % 4 = 1, B AS v % 4 = 2, C AS v % 4 = 3
);

-- Test 10.4: Bounded quantifiers
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 100) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A{2,5} B)
    DEFINE A AS v % 10 <> 0, B AS v % 10 = 0
);

-- Test 10.5: Star quantifier
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 50) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A B* C)
    DEFINE A AS v % 10 = 1, B AS v % 10 IN (2,3,4,5,6,7,8), C AS v % 10 = 9
);

--
-- Section 11: Real-world Pattern Examples
--

-- Test 11.1: Stock price pattern - V-shape (down then up)
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM nfa_complex
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (D+ U+)
    DEFINE D AS trend = 'D', U AS trend = 'U'
);

-- Test 11.2: Stock price pattern - peak (up, stable, down)
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM nfa_complex
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (U+ S* D+)
    DEFINE U AS trend = 'U', S AS trend = 'S', D AS trend = 'D'
);

-- Test 11.3: Consecutive increasing values (using PREV)
-- FIXME: The original pattern was:
--   DEFINE A AS v > PREV(v) OR PREV(v) IS NULL
-- This causes "ERROR: unrecognized node type: 15" (T_FuncExpr) because
-- NullTest(FuncExpr(PREV)) is not properly handled somewhere in the planner.
-- The expression v > PREV(v) works fine, but PREV(v) IS NULL fails.
-- Using COALESCE(PREV(v), 0) as a workaround until the bug is fixed.
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 50) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A{3,})
    DEFINE A AS v > COALESCE(PREV(v), 0)
);

--
-- Section 12: Performance-oriented Tests
--

-- Test 12.1: Large dataset with simple pattern
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 1000) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A B)
    DEFINE A AS v % 2 = 1, B AS v % 2 = 0
);

-- Test 12.2: Large dataset with absorption
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 1000) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A+ B)
    DEFINE A AS v % 100 <> 0, B AS v % 100 = 0
);

-- Test 12.3: High state merge ratio
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 500) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN ((A | B)+ C)
    DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0
);

--
-- Section 13: INITIAL vs no INITIAL comparison
--

-- Test 13.1: With INITIAL keyword
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 50) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    INITIAL
    PATTERN (A+ B)
    DEFINE A AS v % 5 <> 0, B AS v % 5 = 0
);

-- Test 13.2: Without INITIAL keyword (same behavior currently)
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 50) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A+ B)
    DEFINE A AS v % 5 <> 0, B AS v % 5 = 0
);

--
-- Section 14: Quantifier Variations
--

-- Test 14.1: Plus quantifier
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 40) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A+)
    DEFINE A AS v % 4 <> 0
);

-- Test 14.2: Star quantifier (zero or more)
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 40) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A* B)
    DEFINE A AS v % 4 IN (1, 2), B AS v % 4 = 3
);

-- Test 14.3: Question mark (zero or one)
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 40) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A? B C)
    DEFINE A AS v % 4 = 1, B AS v % 4 = 2, C AS v % 4 = 3
);

-- Test 14.4: Exact count {n}
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 50) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A{3} B)
    DEFINE A AS v % 5 <> 0, B AS v % 5 = 0
);

-- Test 14.5: Range {n,m}
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 50) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A{2,4} B)
    DEFINE A AS v % 5 <> 0, B AS v % 5 = 0
);

-- Test 14.6: At least {n,}
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 50) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A{3,} B)
    DEFINE A AS v % 10 <> 0, B AS v % 10 = 0
);

--
-- Section 15: Regression Tests for Statistics Accuracy
--

-- Test 15.1: Verify state count accuracy
-- Pattern A+ B with 20 rows should show predictable state behavior
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 20) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A+ B)
    DEFINE A AS v % 5 <> 0, B AS v % 5 = 0
);

-- Test 15.2: Verify context count with known absorption
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 30) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A+ B C)
    DEFINE A AS v % 10 IN (1,2,3,4,5,6,7), B AS v % 10 = 8, C AS v % 10 = 9
);

-- Test 15.3: Verify match length with fixed-length pattern
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 30) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A B C)
    DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0
);

--
-- Section 16: Alternation Pattern Tests
--

-- Test 16.1: Simple alternation
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM nfa_test
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN ((A | B) C)
    DEFINE A AS cat = 'A', B AS cat = 'B', C AS cat = 'C'
);

-- Test 16.2: Multiple items in alternation
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM nfa_test
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN ((A | B | C | D) E)
    DEFINE
        A AS cat = 'A', B AS cat = 'B', C AS cat = 'C',
        D AS cat = 'D', E AS cat = 'E'
);

-- Test 16.3: Alternation with quantifiers
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 50) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN ((A | B)+ C)
    DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0
);

--
-- Section 17: Group Pattern Tests
--

-- Test 17.1: Simple group
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 40) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN ((A B)+)
    DEFINE A AS v % 2 = 1, B AS v % 2 = 0
);

-- Test 17.2: Group with bounded quantifier
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 40) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN ((A B){2,4})
    DEFINE A AS v % 2 = 1, B AS v % 2 = 0
);

-- Test 17.3: Nested groups
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 60) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (((A B){2})+)
    DEFINE A AS v % 2 = 1, B AS v % 2 = 0
);

--
-- Section 18: Window Function Combinations
--

-- Test 18.1: count(*) with pattern
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 30) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A+ B)
    DEFINE A AS v % 5 <> 0, B AS v % 5 = 0
);

-- Test 18.2: first_value with pattern
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT first_value(v) OVER w
FROM generate_series(1, 30) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A+ B)
    DEFINE A AS v % 5 <> 0, B AS v % 5 = 0
);

-- Test 18.3: last_value with pattern
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT last_value(v) OVER w
FROM generate_series(1, 30) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A+ B)
    DEFINE A AS v % 5 <> 0, B AS v % 5 = 0
);

-- Test 18.4: Multiple window functions
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT
    count(*) OVER w,
    first_value(v) OVER w,
    last_value(v) OVER w
FROM generate_series(1, 30) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A+ B)
    DEFINE A AS v % 5 <> 0, B AS v % 5 = 0
);

--
-- Section 19: DEFINE Expression Variations
--

-- Test 19.1: Complex boolean expressions
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 50) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A+ B)
    DEFINE
        A AS (v % 5 <> 0) AND (v % 3 <> 0),
        B AS (v % 5 = 0) OR (v % 3 = 0)
);

-- Test 19.2: Using PREV function
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 30) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (S U+ D+)
    DEFINE
        S AS TRUE,
        U AS v > PREV(v),
        D AS v < PREV(v)
);

-- Test 19.3: Using NULL comparisons
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM (
    SELECT CASE WHEN v % 5 = 0 THEN NULL ELSE v END AS v
    FROM generate_series(1, 30) v
) t
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A+ B)
    DEFINE A AS v IS NOT NULL, B AS v IS NULL
);

--
-- Section 20: Large Scale Statistics Verification
--

-- Test 20.1: 500 rows - verify statistics scale correctly
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 500) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A+ B C)
    DEFINE A AS v % 10 < 7, B AS v % 10 = 7, C AS v % 10 = 8
);

-- Test 20.2: High match count scenario
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 500) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A B)
    DEFINE A AS v % 2 = 1, B AS v % 2 = 0
);

-- Test 20.3: High skip count scenario
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT count(*) OVER w
FROM generate_series(1, 500) AS s(v)
WINDOW w AS (
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    AFTER MATCH SKIP PAST LAST ROW
    PATTERN (A B C D E)
    DEFINE
        A AS v % 100 = 1,
        B AS v % 100 = 2,
        C AS v % 100 = 3,
        D AS v % 100 = 4,
        E AS v % 100 = 5
);

-- Cleanup
DROP TABLE nfa_test;
DROP TABLE nfa_complex;