Re: Row pattern recognition - Mailing list pgsql-hackers
| From | Henson Choi |
|---|---|
| Subject | Re: Row pattern recognition |
| Date | |
| Msg-id | CAAAe_zBtYoqDtbb1Zb0eCQ_C3WExbcaXCTdjOP=5huhXZNoyzQ@mail.gmail.com Whole thread |
| In response to | Re: Row pattern recognition (Tatsuo Ishii <ishii@postgresql.org>) |
| List | pgsql-hackers |
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
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".
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.
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.
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.
Best regards,
Henson
> 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
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".
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.
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.
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.
Best regards,
Henson
pgsql-hackers by date: