On 7/20/23 17:07, Vik Fearing wrote:
> On 7/21/23 01:36, Jacob Champion wrote:
>> (I've attached two failing tests against v2, to hopefully better
>> illustrate, along with what I_think_ should be the correct results.)
>
> Almost. You are matching 07-01-2023 but the condition is "price > 100".
D'oh. Correction attached. I think :)
Thanks,
--Jacob
diff --git a/src/test/regress/expected/rpr.out
b/src/test/regress/expected/rpr.out
index 6bf8818911..f3fd22de2a 100644
--- a/src/test/regress/expected/rpr.out
+++ b/src/test/regress/expected/rpr.out
@@ -230,6 +230,79 @@ SELECT company, tdate, price, rpr(price) OVER w FROM stock
company2 | 07-10-2023 | 1300 |
(20 rows)
+-- match everything
+SELECT company, tdate, price, rpr(price) OVER w FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ INITIAL
+ PATTERN (A+)
+ DEFINE
+ A AS TRUE
+);
+ company | tdate | price | rpr
+----------+------------+-------+-----
+ company1 | 07-01-2023 | 100 | 100
+ company1 | 07-02-2023 | 200 |
+ company1 | 07-03-2023 | 150 |
+ company1 | 07-04-2023 | 140 |
+ company1 | 07-05-2023 | 150 |
+ company1 | 07-06-2023 | 90 |
+ company1 | 07-07-2023 | 110 |
+ company1 | 07-08-2023 | 130 |
+ company1 | 07-09-2023 | 120 |
+ company1 | 07-10-2023 | 130 |
+ company2 | 07-01-2023 | 50 | 50
+ company2 | 07-02-2023 | 2000 |
+ company2 | 07-03-2023 | 1500 |
+ company2 | 07-04-2023 | 1400 |
+ company2 | 07-05-2023 | 1500 |
+ company2 | 07-06-2023 | 60 |
+ company2 | 07-07-2023 | 1100 |
+ company2 | 07-08-2023 | 1300 |
+ company2 | 07-09-2023 | 1200 |
+ company2 | 07-10-2023 | 1300 |
+(20 rows)
+
+-- backtracking with reclassification of rows
+SELECT company, tdate, price, rpr(price) OVER w FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ INITIAL
+ PATTERN (A+ B+)
+ DEFINE
+ A AS price > 100,
+ B AS price > 100
+);
+ company | tdate | price | rpr
+----------+------------+-------+------
+ company1 | 07-01-2023 | 100 |
+ company1 | 07-02-2023 | 200 | 200
+ company1 | 07-03-2023 | 150 |
+ company1 | 07-04-2023 | 140 |
+ company1 | 07-05-2023 | 150 |
+ company1 | 07-06-2023 | 90 |
+ company1 | 07-07-2023 | 110 | 110
+ company1 | 07-08-2023 | 130 |
+ company1 | 07-09-2023 | 120 |
+ company1 | 07-10-2023 | 130 |
+ company2 | 07-01-2023 | 50 |
+ company2 | 07-02-2023 | 2000 | 2000
+ company2 | 07-03-2023 | 1500 |
+ company2 | 07-04-2023 | 1400 |
+ company2 | 07-05-2023 | 1500 |
+ company2 | 07-06-2023 | 60 |
+ company2 | 07-07-2023 | 1100 | 1100
+ company2 | 07-08-2023 | 1300 |
+ company2 | 07-09-2023 | 1200 |
+ company2 | 07-10-2023 | 1300 |
+(20 rows)
+
--
-- Error cases
--
diff --git a/src/test/regress/sql/rpr.sql b/src/test/regress/sql/rpr.sql
index 951c9abfe9..f1cd0369f4 100644
--- a/src/test/regress/sql/rpr.sql
+++ b/src/test/regress/sql/rpr.sql
@@ -94,6 +94,33 @@ SELECT company, tdate, price, rpr(price) OVER w FROM stock
UPDOWN AS price > PREV(price) AND price > NEXT(price)
);
+-- match everything
+SELECT company, tdate, price, rpr(price) OVER w FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ INITIAL
+ PATTERN (A+)
+ DEFINE
+ A AS TRUE
+);
+
+-- backtracking with reclassification of rows
+SELECT company, tdate, price, rpr(price) OVER w FROM stock
+ WINDOW w AS (
+ PARTITION BY company
+ ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ INITIAL
+ PATTERN (A+ B+)
+ DEFINE
+ A AS price > 100,
+ B AS price > 100
+);
+
--
-- Error cases
--
