From 56ebb1ad70a152cb22d4adb60069b4625659421e Mon Sep 17 00:00:00 2001 From: Henson Choi Date: Thu, 7 May 2026 14:29:14 +0900 Subject: [PATCH 03/11] Cover RPR empty-match path with EXPLAIN tests; fix stale XXX comments The test_728_* cases in rpr_nfa.sql claimed (via XXX) that the visited bitmap blocks empty iterations. In fact the NFA finds the empty matches; window aggregates just return 0 / NULL over the length-0 frame, indistinguishable from "no match" without MATCH_NUMBER(). Replace the XXX comments with the actual behavior, and add paired EXPLAIN ANALYZE tests in rpr_explain.sql that lock in "NFA: 3 matched (len 0/0/0.0)" as observable regression coverage. --- src/test/regress/expected/rpr_explain.out | 200 ++++++++++++++++++++++ src/test/regress/expected/rpr_nfa.out | 29 ++-- src/test/regress/sql/rpr_explain.sql | 116 +++++++++++++ src/test/regress/sql/rpr_nfa.sql | 29 ++-- 4 files changed, 340 insertions(+), 34 deletions(-) diff --git a/src/test/regress/expected/rpr_explain.out b/src/test/regress/expected/rpr_explain.out index 0a049d1beba..c4516d3c756 100644 --- a/src/test/regress/expected/rpr_explain.out +++ b/src/test/regress/expected/rpr_explain.out @@ -2102,6 +2102,206 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=0.00 loops=1) (5 rows) +-- Empty matches (length 0): mirror the test_728_* cases in rpr_nfa.sql. +-- Window aggregates over a length-0 frame return 0 / NULL, so the SELECT +-- result alone cannot distinguish "no match" from "empty match"; the +-- "NFA: N matched (len 0/0/0.0)" line in EXPLAIN is the only observable +-- proof that the empty matches were found. +-- (A?){0,3}: min=0, A never matches -> 3 length-0 matches +CREATE VIEW rpr_ev_edge_empty_match_min0 AS +SELECT count(*) OVER w +FROM generate_series(1, 3) AS s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + PATTERN ((A?){0,3}) + DEFINE A AS FALSE +); +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_empty_match_min0'), E'\n')) AS line WHERE line ~ 'PATTERN'; + line +------------------------ + PATTERN ((a?){0,3}) +(1 row) + +SELECT rpr_explain_filter(' +EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) +SELECT count(*) OVER w +FROM generate_series(1, 3) AS s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + PATTERN ((A?){0,3}) + DEFINE A AS FALSE +);'); + rpr_explain_filter +--------------------------------------------------------------------- + WindowAgg (actual rows=3.00 loops=1) + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: (a?){0,3} + Nav Mark Lookback: 0 + Storage: Memory Maximum Storage: NkB + NFA States: 6 peak, 20 total, 4 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) +(9 rows) + +-- (A?){1,3}: min=1, one empty iteration satisfies min -> 3 length-0 matches +CREATE VIEW rpr_ev_edge_empty_match_min1 AS +SELECT count(*) OVER w +FROM generate_series(1, 3) AS s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + PATTERN ((A?){1,3}) + DEFINE A AS FALSE +); +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_empty_match_min1'), E'\n')) AS line WHERE line ~ 'PATTERN'; + line +------------------------ + PATTERN ((a?){1,3}) +(1 row) + +SELECT rpr_explain_filter(' +EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) +SELECT count(*) OVER w +FROM generate_series(1, 3) AS s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + PATTERN ((A?){1,3}) + DEFINE A AS FALSE +);'); + rpr_explain_filter +--------------------------------------------------------------------- + WindowAgg (actual rows=3.00 loops=1) + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: (a?){1,3} + Nav Mark Lookback: 0 + Storage: Memory Maximum Storage: NkB + NFA States: 5 peak, 16 total, 4 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) +(9 rows) + +-- (A?){2,3}: min=2 (SQL:2016 STR06 = STRE STRE) -> 3 length-0 matches +CREATE VIEW rpr_ev_edge_empty_match_min2 AS +SELECT count(*) OVER w +FROM generate_series(1, 3) AS s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + PATTERN ((A?){2,3}) + DEFINE A AS FALSE +); +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_empty_match_min2'), E'\n')) AS line WHERE line ~ 'PATTERN'; + line +------------------------ + PATTERN ((a?){2,3}) +(1 row) + +SELECT rpr_explain_filter(' +EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) +SELECT count(*) OVER w +FROM generate_series(1, 3) AS s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + PATTERN ((A?){2,3}) + DEFINE A AS FALSE +);'); + rpr_explain_filter +--------------------------------------------------------------------- + WindowAgg (actual rows=3.00 loops=1) + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: (a?){2,3} + Nav Mark Lookback: 0 + Storage: Memory Maximum Storage: NkB + NFA States: 5 peak, 16 total, 4 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) +(9 rows) + +-- (A?){2,3} mixed: rows 1-2 match A (real), rows 3-4 fall back to empty +CREATE VIEW rpr_ev_edge_empty_match_mixed AS +SELECT count(*) OVER w +FROM generate_series(1, 4) AS s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + PATTERN ((A?){2,3}) + DEFINE A AS v <= 2 +); +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_empty_match_mixed'), E'\n')) AS line WHERE line ~ 'PATTERN'; + line +------------------------ + PATTERN ((a?){2,3}) +(1 row) + +SELECT rpr_explain_filter(' +EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) +SELECT count(*) OVER w +FROM generate_series(1, 4) AS s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + PATTERN ((A?){2,3}) + DEFINE A AS v <= 2 +);'); + rpr_explain_filter +--------------------------------------------------------------------- + WindowAgg (actual rows=4.00 loops=1) + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: (a?){2,3} + Nav Mark Lookback: 0 + Storage: Memory Maximum Storage: NkB + NFA States: 8 peak, 26 total, 5 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) +(9 rows) + +-- (A? B?){2,3}: pure empty multi-element body -> 3 length-0 matches +CREATE VIEW rpr_ev_edge_empty_match_multi AS +SELECT count(*) OVER w +FROM generate_series(1, 3) AS s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + PATTERN ((A? B?){2,3}) + DEFINE A AS FALSE, B AS FALSE +); +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_empty_match_multi'), E'\n')) AS line WHERE line ~ 'PATTERN'; + line +--------------------------- + PATTERN ((a? b?){2,3}) +(1 row) + +SELECT rpr_explain_filter(' +EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) +SELECT count(*) OVER w +FROM generate_series(1, 3) AS s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + PATTERN ((A? B?){2,3}) + DEFINE A AS FALSE, B AS FALSE +);'); + rpr_explain_filter +--------------------------------------------------------------------- + WindowAgg (actual rows=3.00 loops=1) + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: (a? b?){2,3} + Nav Mark Lookback: 0 + Storage: Memory Maximum Storage: NkB + NFA States: 6 peak, 20 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) +(9 rows) + -- Single row CREATE VIEW rpr_ev_edge_single_row AS SELECT count(*) OVER w diff --git a/src/test/regress/expected/rpr_nfa.out b/src/test/regress/expected/rpr_nfa.out index a19b26c3b94..4cff7cfbbd7 100644 --- a/src/test/regress/expected/rpr_nfa.out +++ b/src/test/regress/expected/rpr_nfa.out @@ -4395,9 +4395,9 @@ WINDOW w AS ( (3 rows) -- (A?){0,3}: min=0, nullable inner. --- A never matches. A? matches empty, min=0 satisfied immediately. --- Per standard: empty match expected for every row. --- XXX: visited bitmap blocks empty iteration -> no match (same as {2,3}) +-- A never matches but A? matches empty, satisfying min=0 immediately. +-- NFA reports 3 length-0 matches (one per row); first_value / last_value +-- are NULL because the window frame for an empty match has no rows. WITH test_728_min0 AS ( SELECT * FROM (VALUES (1, ARRAY['B']), @@ -4425,9 +4425,8 @@ WINDOW w AS ( (3 rows) -- (A?){1,3}: min=1, nullable inner. --- A never matches. Need 1 empty iteration to satisfy min=1. --- Per standard: empty match expected for every row. --- XXX: visited bitmap blocks empty iteration -> no match (same as {2,3}) +-- A never matches; one empty iteration satisfies min=1. +-- NFA reports 3 length-0 matches; first/last_value NULL over empty frame. WITH test_728_min1 AS ( SELECT * FROM (VALUES (1, ARRAY['B']), @@ -4454,11 +4453,9 @@ WINDOW w AS ( 3 | {B} | | (3 rows) --- (A?){2,3}: min=2, nullable inner. --- 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 --- XXX: visited bitmap blocks second empty iteration -> match failure +-- (A?){2,3}: min=2, nullable inner. Per SQL:2016 STR06 = (STRE STRE) +-- is valid: two empty iterations satisfy min=2. +-- NFA reports 3 length-0 matches; first/last_value NULL over empty frame. WITH test_728_min2 AS ( SELECT * FROM (VALUES (1, ARRAY['B']), @@ -4486,9 +4483,8 @@ WINDOW w AS ( (3 rows) -- (A?){2,3} mixed: some rows match A, some don't --- Rows 1-2: A matches, greedy takes 2 -> min satisfied --- Row 3: A doesn't match, needs 2 empty iterations for min=2 --- XXX: Row 3 fails due to visited bitmap (same as pure empty {2,3}) +-- Rows 1-2: A matches, greedy takes 2 -> min satisfied (real match) +-- Row 3: A doesn't match, two empty iterations satisfy min=2 (length-0 match) -- Row 4: A matches 1 real iter + 1 ff empty exit -> match 4-4 WITH test_728_min2_mixed AS ( SELECT * FROM (VALUES @@ -4559,9 +4555,8 @@ WINDOW w AS ( 6 | {B} | 6 | 6 (6 rows) --- (A? B?){2,3}: pure empty body (nothing matches) --- XXX: All NULL: same issue as test_728_min2 (empty match at context --- start yields UNMATCHED via startPos-1 initial advance) +-- (A? B?){2,3}: pure empty body (nothing matches A or B). +-- NFA reports 3 length-0 matches; first/last_value NULL over empty frame. WITH test_728_multi_empty AS ( SELECT * FROM (VALUES (1, ARRAY['C']), diff --git a/src/test/regress/sql/rpr_explain.sql b/src/test/regress/sql/rpr_explain.sql index e123be60aea..d339a80a673 100644 --- a/src/test/regress/sql/rpr_explain.sql +++ b/src/test/regress/sql/rpr_explain.sql @@ -1178,6 +1178,122 @@ WINDOW w AS ( DEFINE A AS v = 1, B AS v = 2 );'); +-- Empty matches (length 0): mirror the test_728_* cases in rpr_nfa.sql. +-- Window aggregates over a length-0 frame return 0 / NULL, so the SELECT +-- result alone cannot distinguish "no match" from "empty match"; the +-- "NFA: N matched (len 0/0/0.0)" line in EXPLAIN is the only observable +-- proof that the empty matches were found. + +-- (A?){0,3}: min=0, A never matches -> 3 length-0 matches +CREATE VIEW rpr_ev_edge_empty_match_min0 AS +SELECT count(*) OVER w +FROM generate_series(1, 3) AS s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + PATTERN ((A?){0,3}) + DEFINE A AS FALSE +); +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_empty_match_min0'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT rpr_explain_filter(' +EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) +SELECT count(*) OVER w +FROM generate_series(1, 3) AS s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + PATTERN ((A?){0,3}) + DEFINE A AS FALSE +);'); + +-- (A?){1,3}: min=1, one empty iteration satisfies min -> 3 length-0 matches +CREATE VIEW rpr_ev_edge_empty_match_min1 AS +SELECT count(*) OVER w +FROM generate_series(1, 3) AS s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + PATTERN ((A?){1,3}) + DEFINE A AS FALSE +); +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_empty_match_min1'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT rpr_explain_filter(' +EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) +SELECT count(*) OVER w +FROM generate_series(1, 3) AS s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + PATTERN ((A?){1,3}) + DEFINE A AS FALSE +);'); + +-- (A?){2,3}: min=2 (SQL:2016 STR06 = STRE STRE) -> 3 length-0 matches +CREATE VIEW rpr_ev_edge_empty_match_min2 AS +SELECT count(*) OVER w +FROM generate_series(1, 3) AS s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + PATTERN ((A?){2,3}) + DEFINE A AS FALSE +); +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_empty_match_min2'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT rpr_explain_filter(' +EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) +SELECT count(*) OVER w +FROM generate_series(1, 3) AS s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + PATTERN ((A?){2,3}) + DEFINE A AS FALSE +);'); + +-- (A?){2,3} mixed: rows 1-2 match A (real), rows 3-4 fall back to empty +CREATE VIEW rpr_ev_edge_empty_match_mixed AS +SELECT count(*) OVER w +FROM generate_series(1, 4) AS s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + PATTERN ((A?){2,3}) + DEFINE A AS v <= 2 +); +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_empty_match_mixed'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT rpr_explain_filter(' +EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) +SELECT count(*) OVER w +FROM generate_series(1, 4) AS s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + PATTERN ((A?){2,3}) + DEFINE A AS v <= 2 +);'); + +-- (A? B?){2,3}: pure empty multi-element body -> 3 length-0 matches +CREATE VIEW rpr_ev_edge_empty_match_multi AS +SELECT count(*) OVER w +FROM generate_series(1, 3) AS s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + PATTERN ((A? B?){2,3}) + DEFINE A AS FALSE, B AS FALSE +); +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_edge_empty_match_multi'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT rpr_explain_filter(' +EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) +SELECT count(*) OVER w +FROM generate_series(1, 3) AS s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + PATTERN ((A? B?){2,3}) + DEFINE A AS FALSE, B AS FALSE +);'); + -- Single row CREATE VIEW rpr_ev_edge_single_row AS SELECT count(*) OVER w diff --git a/src/test/regress/sql/rpr_nfa.sql b/src/test/regress/sql/rpr_nfa.sql index 1d27e0dc09e..29ec4a9dacb 100644 --- a/src/test/regress/sql/rpr_nfa.sql +++ b/src/test/regress/sql/rpr_nfa.sql @@ -3234,9 +3234,9 @@ WINDOW w AS ( ); -- (A?){0,3}: min=0, nullable inner. --- A never matches. A? matches empty, min=0 satisfied immediately. --- Per standard: empty match expected for every row. --- XXX: visited bitmap blocks empty iteration -> no match (same as {2,3}) +-- A never matches but A? matches empty, satisfying min=0 immediately. +-- NFA reports 3 length-0 matches (one per row); first_value / last_value +-- are NULL because the window frame for an empty match has no rows. WITH test_728_min0 AS ( SELECT * FROM (VALUES (1, ARRAY['B']), @@ -3258,9 +3258,8 @@ WINDOW w AS ( ); -- (A?){1,3}: min=1, nullable inner. --- A never matches. Need 1 empty iteration to satisfy min=1. --- Per standard: empty match expected for every row. --- XXX: visited bitmap blocks empty iteration -> no match (same as {2,3}) +-- A never matches; one empty iteration satisfies min=1. +-- NFA reports 3 length-0 matches; first/last_value NULL over empty frame. WITH test_728_min1 AS ( SELECT * FROM (VALUES (1, ARRAY['B']), @@ -3281,11 +3280,9 @@ WINDOW w AS ( A AS 'A' = ANY(flags) ); --- (A?){2,3}: min=2, nullable inner. --- 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 --- XXX: visited bitmap blocks second empty iteration -> match failure +-- (A?){2,3}: min=2, nullable inner. Per SQL:2016 STR06 = (STRE STRE) +-- is valid: two empty iterations satisfy min=2. +-- NFA reports 3 length-0 matches; first/last_value NULL over empty frame. WITH test_728_min2 AS ( SELECT * FROM (VALUES (1, ARRAY['B']), @@ -3307,9 +3304,8 @@ WINDOW w AS ( ); -- (A?){2,3} mixed: some rows match A, some don't --- Rows 1-2: A matches, greedy takes 2 -> min satisfied --- Row 3: A doesn't match, needs 2 empty iterations for min=2 --- XXX: Row 3 fails due to visited bitmap (same as pure empty {2,3}) +-- Rows 1-2: A matches, greedy takes 2 -> min satisfied (real match) +-- Row 3: A doesn't match, two empty iterations satisfy min=2 (length-0 match) -- Row 4: A matches 1 real iter + 1 ff empty exit -> match 4-4 WITH test_728_min2_mixed AS ( SELECT * FROM (VALUES @@ -3364,9 +3360,8 @@ WINDOW w AS ( B AS 'B' = ANY(flags) ); --- (A? B?){2,3}: pure empty body (nothing matches) --- XXX: All NULL: same issue as test_728_min2 (empty match at context --- start yields UNMATCHED via startPos-1 initial advance) +-- (A? B?){2,3}: pure empty body (nothing matches A or B). +-- NFA reports 3 length-0 matches; first/last_value NULL over empty frame. WITH test_728_multi_empty AS ( SELECT * FROM (VALUES (1, ARRAY['C']), -- 2.50.1 (Apple Git-155)