nocfbot-0022-Add-2-arg-PREV-NEXT-host-variable-test.txt
text/plain
Filename: nocfbot-0022-Add-2-arg-PREV-NEXT-host-variable-test.txt
Type: text/plain
Part: 21
Message:
Re: Row pattern recognition
From 081f70847766b2aa312b667e5b7c8e2a41088378 Mon Sep 17 00:00:00 2001 From: Henson Choi <assam258@gmail.com> Date: Mon, 6 Apr 2026 09:53:14 +0900 Subject: [PATCH 22/40] Add 2-arg PREV/NEXT test for row pattern navigation with host variable --- src/test/regress/expected/rpr.out | 63 +++++++++++++++++++++++++++++++ src/test/regress/sql/rpr.sql | 16 ++++++++ 2 files changed, 79 insertions(+) diff --git a/src/test/regress/expected/rpr.out b/src/test/regress/expected/rpr.out index 5a460e9bd52..c02dbd4c08d 100644 --- a/src/test/regress/expected/rpr.out +++ b/src/test/regress/expected/rpr.out @@ -1492,6 +1492,69 @@ EXECUTE test_prev_offset(-1); ERROR: PREV/NEXT offset must not be negative EXECUTE test_prev_offset(NULL); ERROR: PREV/NEXT offset must not be null +DEALLOCATE test_prev_offset; +-- 2-arg PREV/NEXT: host variable with positive value +-- Exercises RPR_NAV_OFFSET_NEEDS_EVAL -> eval_nav_max_offset() path +PREPARE test_prev_offset(int8) AS +SELECT company, tdate, price, first_value(price) OVER w, count(*) OVER w +FROM stock +WINDOW w AS ( + PARTITION BY company ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A B+) + DEFINE A AS TRUE, B AS price > PREV(price, $1) +); +EXECUTE test_prev_offset(1); + company | tdate | price | first_value | count +----------+------------+-------+-------------+------- + company1 | 07-01-2023 | 100 | 100 | 2 + company1 | 07-02-2023 | 200 | | 0 + company1 | 07-03-2023 | 150 | | 0 + company1 | 07-04-2023 | 140 | 140 | 2 + company1 | 07-05-2023 | 150 | | 0 + company1 | 07-06-2023 | 90 | 90 | 3 + company1 | 07-07-2023 | 110 | | 0 + company1 | 07-08-2023 | 130 | | 0 + company1 | 07-09-2023 | 120 | 120 | 2 + company1 | 07-10-2023 | 130 | | 0 + company2 | 07-01-2023 | 50 | 50 | 2 + company2 | 07-02-2023 | 2000 | | 0 + company2 | 07-03-2023 | 1500 | | 0 + company2 | 07-04-2023 | 1400 | 1400 | 2 + company2 | 07-05-2023 | 1500 | | 0 + company2 | 07-06-2023 | 60 | 60 | 3 + company2 | 07-07-2023 | 1100 | | 0 + company2 | 07-08-2023 | 1300 | | 0 + company2 | 07-09-2023 | 1200 | 1200 | 2 + company2 | 07-10-2023 | 1300 | | 0 +(20 rows) + +EXECUTE test_prev_offset(2); + company | tdate | price | first_value | count +----------+------------+-------+-------------+------- + company1 | 07-01-2023 | 100 | | 0 + company1 | 07-02-2023 | 200 | 200 | 2 + company1 | 07-03-2023 | 150 | | 0 + company1 | 07-04-2023 | 140 | | 0 + company1 | 07-05-2023 | 150 | | 0 + company1 | 07-06-2023 | 90 | | 0 + company1 | 07-07-2023 | 110 | 110 | 3 + company1 | 07-08-2023 | 130 | | 0 + company1 | 07-09-2023 | 120 | | 0 + company1 | 07-10-2023 | 130 | | 0 + company2 | 07-01-2023 | 50 | | 0 + company2 | 07-02-2023 | 2000 | 2000 | 2 + company2 | 07-03-2023 | 1500 | | 0 + company2 | 07-04-2023 | 1400 | | 0 + company2 | 07-05-2023 | 1500 | | 0 + company2 | 07-06-2023 | 60 | | 0 + company2 | 07-07-2023 | 1100 | 1100 | 3 + company2 | 07-08-2023 | 1300 | | 0 + company2 | 07-09-2023 | 1200 | | 0 + company2 | 07-10-2023 | 1300 | | 0 +(20 rows) + DEALLOCATE test_prev_offset; -- 2-arg: two PREV with different offsets in same DEFINE clause -- B: price exceeds both 1-back and 2-back values diff --git a/src/test/regress/sql/rpr.sql b/src/test/regress/sql/rpr.sql index e417789eb2b..47f33904690 100644 --- a/src/test/regress/sql/rpr.sql +++ b/src/test/regress/sql/rpr.sql @@ -732,6 +732,22 @@ EXECUTE test_prev_offset(-1); EXECUTE test_prev_offset(NULL); DEALLOCATE test_prev_offset; +-- 2-arg PREV/NEXT: host variable with positive value +-- Exercises RPR_NAV_OFFSET_NEEDS_EVAL -> eval_nav_max_offset() path +PREPARE test_prev_offset(int8) AS +SELECT company, tdate, price, first_value(price) OVER w, count(*) OVER w +FROM stock +WINDOW w AS ( + PARTITION BY company ORDER BY tdate + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN (A B+) + DEFINE A AS TRUE, B AS price > PREV(price, $1) +); +EXECUTE test_prev_offset(1); +EXECUTE test_prev_offset(2); +DEALLOCATE test_prev_offset; + -- 2-arg: two PREV with different offsets in same DEFINE clause -- B: price exceeds both 1-back and 2-back values SELECT company, tdate, price, -- 2.50.1 (Apple Git-155)