agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Jacob Champion <jchampion@timescale.com>
To: Vik Fearing <vik@postgresfriends.org>
To: Tatsuo Ishii <ishii@sraoss.co.jp>
Cc: pgsql-hackers@postgresql.org
Subject: Re: Row pattern recognition
Date: Fri, 21 Jul 2023 16:14:12 -0700
Message-ID: <862cff51-eaf6-3fa1-07a5-9666c7e4e897@timescale.com> (raw)
In-Reply-To: <69272b9d-1985-9902-0632-e29180c158ae@postgresfriends.org>
References: <90bff6fa-01b7-cf60-f4f4-3412514a7cd2@postgresfriends.org>
<20230628.095819.1123345378945479712.t-ishii@sranhm.sra.co.jp>
<07214b3a-5893-f6ce-ef55-0e5d24c94925@timescale.com>
<20230720.141513.1986681389874528348.t-ishii@sranhm.sra.co.jp>
<022a392f-2a9e-3df1-a509-a5bd51160fa5@timescale.com>
<69272b9d-1985-9902-0632-e29180c158ae@postgresfriends.org>
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
--
Attachments:
[text/plain] tests.diff.txt (3.5K, ../862cff51-eaf6-3fa1-07a5-9666c7e4e897@timescale.com/2-tests.diff.txt)
download | inline diff:
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
--
view thread (522+ messages) latest in thread
Message-ID: <862cff51-eaf6-3fa1-07a5-9666c7e4e897@timescale.com>
Permalink: ../862cff51-eaf6-3fa1-07a5-9666c7e4e897@timescale.com/
Also on: postgresql.org/message-id/862cff51-eaf6-3fa1-07a5-9666c7e4e897@timescale.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: jchampion@timescale.com, vik@postgresfriends.org, ishii@sraoss.co.jp
Subject: Re: Row pattern recognition
In-Reply-To: <862cff51-eaf6-3fa1-07a5-9666c7e4e897@timescale.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox