agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Vik Fearing <vik@postgresfriends.org>
To: Tatsuo Ishii <ishii@sraoss.co.jp>
Cc: jchampion@timescale.com
Cc: pgsql-hackers@postgresql.org
Subject: Re: Row pattern recognition
Date: Fri, 28 Jul 2023 14:36:58 +0200
Message-ID: <9e135bd6-252a-e39a-bf7d-5a9726427769@postgresfriends.org> (raw)
In-Reply-To: <20230728.200230.1800661389729335298.t-ishii@sranhm.sra.co.jp>
References: <022a392f-2a9e-3df1-a509-a5bd51160fa5@timescale.com>
<20230726.212134.193373300820157886.t-ishii@sranhm.sra.co.jp>
<c9ebc3d0-c3d1-e8eb-4a57-0ec099cbda17@postgresfriends.org>
<20230728.200230.1800661389729335298.t-ishii@sranhm.sra.co.jp>
On 7/28/23 13:02, Tatsuo Ishii wrote:
>>> Attached is the v3 patch. In this patch following changes are made.
>>
>> - PATTERN variables do not have to exist in the DEFINE clause. They are
>> - considered TRUE if not present.
>
> Do you think we really need this? I found a criticism regarding this.
>
> https://link.springer.com/article/10.1007/s13222-022-00404-3
> "3.2 Explicit Definition of All Row Pattern Variables"
>
> What do you think?
I think that a large part of obeying the standard is to allow queries
from other engines to run the same on ours. The standard does not
require the pattern variables to be defined and so there are certainly
queries out there without them, and that hurts migrating to PostgreSQL.
>>> - I am working on making window aggregates RPR aware now. The
>>> implementation is in progress and far from completeness. An example
>>> is below. I think row 2, 3, 4 of "count" column should be NULL
>>> instead of 3, 2, 0, 0. Same thing can be said to other
>>> rows. Probably this is an effect of moving aggregate but I still
>>> studying the window aggregation code.
>>
>> This tells me again that RPR is not being run in the right place. All
>> windowed aggregates and frame-level window functions should Just Work
>> with no modification.
>
> I am not touching each aggregate function. I am modifying
> eval_windowaggregates() in nodeWindowAgg.c, which calls each aggregate
> function. Do you think it's not the right place to make window
> aggregates RPR aware?
Oh, okay.
>>> SELECT company, tdate, 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
>>> INITIAL
>>> PATTERN (START UP+ DOWN+)
>>> DEFINE
>>> START AS TRUE,
>>> UP AS price > PREV(price),
>>> DOWN AS price < PREV(price)
>>> );
>>> company | tdate | first_value | count
>>> ----------+------------+-------------+-------
>>> company1 | 2023-07-01 | 100 | 4
>>> company1 | 2023-07-02 | | 3
>>> company1 | 2023-07-03 | | 2
>>> company1 | 2023-07-04 | | 0
>>> company1 | 2023-07-05 | | 0
>>> company1 | 2023-07-06 | 90 | 4
>>> company1 | 2023-07-07 | | 3
>>> company1 | 2023-07-08 | | 2
>>> company1 | 2023-07-09 | | 0
>>> company1 | 2023-07-10 | | 0
>>> company2 | 2023-07-01 | 50 | 4
>>> company2 | 2023-07-02 | | 3
>>> company2 | 2023-07-03 | | 2
>>> company2 | 2023-07-04 | | 0
>>> company2 | 2023-07-05 | | 0
>>> company2 | 2023-07-06 | 60 | 4
>>> company2 | 2023-07-07 | | 3
>>> company2 | 2023-07-08 | | 2
>>> company2 | 2023-07-09 | | 0
>>> company2 | 2023-07-10 | | 0
>>
>> In this scenario, row 1's frame is the first 5 rows and specified SKIP
>> PAST LAST ROW, so rows 2-5 don't have *any* frame (because they are
>> skipped) and the result of the outer count should be 0 for all of them
>> because there are no rows in the frame.
>
> Ok. Just I want to make sure. If it's other aggregates like sum or
> avg, the result of the outer aggregates should be NULL.
They all behave the same way as in a normal query when they receive no
rows as input.
>> When we get to adding count in the MEASURES clause, there will be a
>> difference between no match and empty match, but that does not apply
>> here.
>
> Can you elaborate more? I understand that "no match" and "empty match"
> are different things. But I do not understand how the difference
> affects the result of count.
This query:
SELECT v.a, wcnt OVER w, count(*) OVER w
FROM (VALUES ('A')) AS v (a)
WINDOW w AS (
ORDER BY v.a
MEASURES count(*) AS wcnt
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
PATTERN (B)
DEFINE B AS B.a = 'B'
)
produces this result:
a | wcnt | count
---+------+-------
A | | 0
(1 row)
Inside the window specification, *no match* was found and so all of the
MEASURES are null. The count(*) in the target list however, still
exists and operates over zero rows.
This very similar query:
SELECT v.a, wcnt OVER w, count(*) OVER w
FROM (VALUES ('A')) AS v (a)
WINDOW w AS (
ORDER BY v.a
MEASURES count(*) AS wcnt
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
PATTERN (B?)
DEFINE B AS B.a = 'B'
)
produces this result:
a | wcnt | count
---+------+-------
A | 0 | 0
(1 row)
In this case, the pattern is B? instead of just B, which produces an
*empty match* for the MEASURES to be applied over.
--
Vik Fearing
view thread (523+ messages) latest in thread
Message-ID: <9e135bd6-252a-e39a-bf7d-5a9726427769@postgresfriends.org>
Permalink: ../9e135bd6-252a-e39a-bf7d-5a9726427769@postgresfriends.org/
Also on: postgresql.org/message-id/9e135bd6-252a-e39a-bf7d-5a9726427769@postgresfriends.org
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: vik@postgresfriends.org, ishii@sraoss.co.jp, jchampion@timescale.com
Subject: Re: Row pattern recognition
In-Reply-To: <9e135bd6-252a-e39a-bf7d-5a9726427769@postgresfriends.org>
* 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