From 0ef3fa76fa1aa340a56eb5841183d61bc8129f3e Mon Sep 17 00:00:00 2001 From: Henson Choi Date: Thu, 15 Jan 2026 22:19:07 +0900 Subject: [PATCH] Row pattern recognition: Enable context absorption only for SKIP PAST LAST ROW --- src/backend/executor/nodeWindowAgg.c | 10 +++-- src/test/regress/expected/rpr.out | 63 +++++++++++++++++++--------- src/test/regress/sql/rpr.sql | 18 ++++++++ 3 files changed, 68 insertions(+), 23 deletions(-) diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c index 3fc73db4e59..4af9998aacf 100644 --- a/src/backend/executor/nodeWindowAgg.c +++ b/src/backend/executor/nodeWindowAgg.c @@ -4796,8 +4796,9 @@ update_reduced_frame(WindowObject winobj, int64 pos) if (ctx->states != NULL) nfa_step(winstate, ctx, NULL, currentPos - 1); } - /* Absorb completed contexts at partition boundary */ - nfa_absorb_contexts(winstate, NULL, currentPos - 1); + /* Absorb completed contexts at partition boundary (SKIP PAST LAST ROW only) */ + if (winstate->rpSkipTo == ST_PAST_LAST_ROW) + nfa_absorb_contexts(winstate, NULL, currentPos - 1); break; } @@ -4815,12 +4816,13 @@ update_reduced_frame(WindowObject winobj, int64 pos) nfa_start_context(winstate, currentPos + 1); /* - * Absorb redundant contexts. + * Absorb redundant contexts (SKIP PAST LAST ROW only). * At the same elementIndex, if newer context's count <= older context's count, * the newer context can be absorbed (for unbounded quantifiers). * Note: Never absorb targetCtx - it's the context we're trying to complete. */ - nfa_absorb_contexts(winstate, targetCtx, currentPos); + if (winstate->rpSkipTo == ST_PAST_LAST_ROW) + nfa_absorb_contexts(winstate, targetCtx, currentPos); /* Check if target context is now complete */ if (targetCtx->states == NULL) diff --git a/src/test/regress/expected/rpr.out b/src/test/regress/expected/rpr.out index 652b1eb811d..f22bba23bff 100644 --- a/src/test/regress/expected/rpr.out +++ b/src/test/regress/expected/rpr.out @@ -884,26 +884,51 @@ SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER ----------+------------+-------+-------------+------------ company1 | 07-01-2023 | 100 | | company1 | 07-02-2023 | 200 | 07-02-2023 | 07-05-2023 - company1 | 07-03-2023 | 150 | | - company1 | 07-04-2023 | 140 | | + company1 | 07-03-2023 | 150 | 07-03-2023 | 07-05-2023 + company1 | 07-04-2023 | 140 | 07-04-2023 | 07-05-2023 company1 | 07-05-2023 | 150 | | company1 | 07-06-2023 | 90 | | company1 | 07-07-2023 | 110 | 07-07-2023 | 07-10-2023 - company1 | 07-08-2023 | 130 | | - company1 | 07-09-2023 | 120 | | + company1 | 07-08-2023 | 130 | 07-08-2023 | 07-10-2023 + company1 | 07-09-2023 | 120 | 07-09-2023 | 07-10-2023 company1 | 07-10-2023 | 130 | | company2 | 07-01-2023 | 50 | | company2 | 07-02-2023 | 2000 | 07-02-2023 | 07-05-2023 - company2 | 07-03-2023 | 1500 | | - company2 | 07-04-2023 | 1400 | | + company2 | 07-03-2023 | 1500 | 07-03-2023 | 07-05-2023 + company2 | 07-04-2023 | 1400 | 07-04-2023 | 07-05-2023 company2 | 07-05-2023 | 1500 | | company2 | 07-06-2023 | 60 | | company2 | 07-07-2023 | 1100 | 07-07-2023 | 07-10-2023 - company2 | 07-08-2023 | 1300 | | - company2 | 07-09-2023 | 1200 | | + company2 | 07-08-2023 | 1300 | 07-08-2023 | 07-10-2023 + company2 | 07-09-2023 | 1200 | 07-09-2023 | 07-10-2023 company2 | 07-10-2023 | 1300 | | (20 rows) +-- SKIP TO NEXT ROW with limited frame (Ishii-san's test case) +-- Each row should produce its own match within its frame +WITH data AS ( + SELECT * FROM (VALUES + ('A', 1), ('A', 2), + ('B', 3), ('B', 4) + ) AS t(gid, id) +) +SELECT gid, id, array_agg(id) OVER w +FROM data +WINDOW w AS ( + PARTITION BY gid + ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + PATTERN (A+) + DEFINE A AS id < 10 +); + gid | id | array_agg +-----+----+----------- + A | 1 | {1,2} + A | 2 | {2} + B | 3 | {3,4} + B | 4 | {4} +(4 rows) + -- ROWS BETWEEN CURRENT ROW AND offset FOLLOWING SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER w, count(*) OVER w @@ -2340,8 +2365,8 @@ WINDOW w AS ( 5 | {B} | | 6 | {C} | | 7 | {B} | 7 | 10 - 8 | {B} | | - 9 | {B} | | + 8 | {B} | 8 | 10 + 9 | {B} | 9 | 10 10 | {D} | | (10 rows) @@ -2556,9 +2581,9 @@ WINDOW w AS ( id | flags | match_start | match_end ----+-------+-------------+----------- 1 | {A} | 1 | 4 - 2 | {A} | | - 3 | {A} | | - 4 | {A} | | + 2 | {A} | 2 | 4 + 3 | {A} | 3 | 4 + 4 | {A} | 4 | 4 5 | {B} | | (5 rows) @@ -2589,8 +2614,8 @@ WINDOW w AS ( id | flags | match_start | match_end ----+-------+-------------+----------- 1 | {A} | 1 | 4 - 2 | {A} | | - 3 | {A} | | + 2 | {A} | 2 | 4 + 3 | {A} | 3 | 4 4 | {B} | | 5 | {X} | | (5 rows) @@ -2623,8 +2648,8 @@ WINDOW w AS ( id | flags | match_start | match_end ----+-------+-------------+----------- 1 | {B} | 1 | 4 - 2 | {B} | | - 3 | {B} | | + 2 | {B} | 2 | 4 + 3 | {B} | 3 | 4 4 | {D} | | 5 | {X} | | (5 rows) @@ -2691,7 +2716,7 @@ WINDOW w AS ( ----+-------+-------------+----------- 1 | {A} | 1 | 6 2 | {B} | | - 3 | {A} | | + 3 | {A} | 3 | 6 4 | {B} | | 5 | {A} | | 6 | {B} | | @@ -2725,7 +2750,7 @@ WINDOW w AS ( id | flags | match_start | match_end ----+-------+-------------+----------- 1 | {A} | 1 | 4 - 2 | {A} | | + 2 | {A} | 2 | 4 3 | {B} | | 4 | {B} | | 5 | {X} | | diff --git a/src/test/regress/sql/rpr.sql b/src/test/regress/sql/rpr.sql index 19146f7f03d..93bf1698fd9 100644 --- a/src/test/regress/sql/rpr.sql +++ b/src/test/regress/sql/rpr.sql @@ -355,6 +355,24 @@ SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER B AS price > 100 ); +-- SKIP TO NEXT ROW with limited frame (Ishii-san's test case) +-- Each row should produce its own match within its frame +WITH data AS ( + SELECT * FROM (VALUES + ('A', 1), ('A', 2), + ('B', 3), ('B', 4) + ) AS t(gid, id) +) +SELECT gid, id, array_agg(id) OVER w +FROM data +WINDOW w AS ( + PARTITION BY gid + ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING + AFTER MATCH SKIP TO NEXT ROW + PATTERN (A+) + DEFINE A AS id < 10 +); + -- ROWS BETWEEN CURRENT ROW AND offset FOLLOWING SELECT company, tdate, price, first_value(tdate) OVER w, last_value(tdate) OVER w, count(*) OVER w -- 2.50.1 (Apple Git-155)