Re: Row pattern recognition

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

Yeah, migration is good point. I agree we should have the feature.

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

Thank you for the detailed explanation. I think I understand now.

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp