Re: Row pattern recognition

>> Attached is the v3 patch. In this patch following changes are made.
>
> Excellent. Thanks!

You are welcome!

> A few quick comments:
>
> - PERMUTE is still misspelled as PREMUTE

Oops. Will fix.

> - 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 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?

>> 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.

> 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.

>> I am going to add this thread to CommitFest and plan to add both of
>> you as reviewers. Thanks in advance.
>
> My pleasure. Thank you for working on this difficult feature.

Thank you for accepting being registered as a reviewer. Your comments
are really helpful.
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp