From: Henson Choi <[email protected]>
Subject: Re: Row pattern recognition
Date: Sun, 27 Sep 2026 07:31:45 +0900
Message-ID: <CAAAe_zBtYoqDtbb1Zb0eCQ_C3WExbcaXCTdjOP=5huhxzno...@mail.gmail.com>
> Hi Tatsuo,
>
>> So question is whether we should support RUNNING keyword.
>> ...
>> In my opinion, the effort to add support for the RUNNING keyword
>> is not worth the trouble. Thoughts?
>
> I agree, for two reasons.
>
> First, in R020 the two keywords never change the result. In the
> standard, FINAL is not permitted in DEFINE and RUNNING is the
> default there; in a window's MEASURES there is no real difference
> between running and final semantics. They only mark how much of the
You are right. In R020, RUNNING and FINAL for MEASURES are
semantically same.
In addition to ISO/IEC 19075, ISO/IEC 9075-2 stats:
"4.21.1 Running vs. final semantics
:
:
As for measures, if row pattern matching is performed in a window, or
if ONE ROW PER MATCH is specified, then expression evaluation is
performed in the last row of the row pattern match, so that running
and final semantics are the same."
BTW, I plan to add a note to "9.23. Window Functions" in the
PostgreSQL documentation, something like below to explicitly stat
that the patch does not support the RUNNING keyword. Comments?
-----------------------------------------------------------------
Note
The SQL standard allows to put a keyword RUNNING in front of row
pattern navigation functions FIRST and LAST. This is not implemented
in PostgreSQL. However, the expected behavior is the same with or
without the RUNNING keyword according to the standard.
-----------------------------------------------------------------
> set of rows mapped to a pattern variable is seen, and the places
> they are written -- aggregates, FIRST/LAST over a pattern variable,
> MEASURES -- are the three features the patch does not have. What
> remains, RUNNING FIRST(x) and RUNNING LAST(x), is the same as the
> expression without the keyword.
>
> Second, it looks simple -- accept RUNNING/FINAL as unreserved
> keywords in front of a function call and sort them out in parse
> analysis -- but it actually carries a complication: the grammar of
> DEFINE expressions would have to be built by copying the whole
> existing expression grammar. The standard, too, uses the general
> scalar expression syntax in DEFINE and writes the keyword before
> the operator, as in RUNNING COUNT (A.*), so the prefix belongs in
> the general expression grammar. But a RUNNING prefix in the general
> a_expr, which DEFINE uses today, conflicts with an alias written
> without AS: "SELECT running total FROM t" and
> "SELECT running total(x) ..." cannot be told apart until the "("
> arrives. Nothing follows a DEFINE expression as an alias, so a
> grammar of DEFINE's own would not have this conflict, but it means
> copying a_expr and everything below it. Even supporting only
> RUNNING before FIRST/LAST, the forms usable today, through a lexer
> lookahead as NULLS_LA does, would stop some existing queries from
> working, such as "SELECT running first FROM t".
Ouch.
> Pattern variables and the range variables of the FROM clause have
> mutually exclusive scopes, so a qualifier in DEFINE has to be
> resolved against a namespace that the ordinary column reference
> resolution knows nothing about. The problems that came up while
> handling the errors for those qualifiers made me wonder whether
> DEFINE expressions should get a grammar of their own, copied from
> the existing one. Because of the maintenance burden, I tried to
> keep to a single grammar as far as possible. Unless the scope of
> RPR grows, I think this part is best frozen as it is.
Agreed.
> The migration benefit you mention would be small in practice, too.
> Queries that use RUNNING in other DBMSs mostly use it because they
> need those three features:
>
> MEASURES RUNNING LAST(price) AS last_price -- MEASURES
> DEFINE B AS price > RUNNING LAST(A.price) -- qualifier
> DEFINE B AS price > RUNNING AVG(price) -- aggregate
>
> So even if we supported RUNNING now, most of those queries would
> still fail on the missing features.
Right.
> In the end, both in meaning and in grammar, RUNNING/FINAL belong
> with those three features. Every way to take the prefix today
> either breaks existing queries or copies the expression grammar,
> and neither is a choice to make for a keyword that changes nothing.
> So when MEASURES, qualifiers, and aggregates in DEFINE are
> developed, I think they need to be dealt with in depth, together
> with the grammar of DEFINE expressions, as one of the architectural
> considerations.
>
> One direction worth weighing then is a grammar of DEFINE's own with
> its own keyword categories, where RUNNING and FINAL are reserved
> inside DEFINE but stay unreserved everywhere else. That removes the
> conflicts by construction and breaks no existing query, though it
> still costs the copy of the expression grammar, a split of the
> keyword categories, and context-aware quoting in deparse, since a
> column named "running" read in DEFINE would have to be printed
> quoted.
>
> That is too heavy to take on now. It changes the core grammar, the
> keyword categories, and deparse, all of which are shared far beyond
> RPR, so it would need agreement from a broad part of the community,
> not just from this thread.
Yes, if someone want to continue the discussion, please create a new
thread.
Regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
