Re: Row pattern recognition

Hi.

CREATE VIEW v3 AS
SELECT
FROM (VALUES (1,'A'),(2,'B'),(3,'B')) t(id, v)
WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
AFTER MATCH SKIP TO NEXT ROW
PATTERN (A B C | B)
DEFINE A AS true, b as true, c as true);

\sv v3

PATTERN (A B C | B)
can be interpreted as
PATTERN (A B (C | B))
or
PATTERN ((A B C) | B)

We need extra parentheses to disambiguate it.
(I searched for "parentheses"; it seems this issue has never been discussed.)

Another question:

PATTERN (A B ?c)
is interpreted as
PATTERN (A B? c)

Maybe one para in select.sgml is enough to explain this, no need to
use parentheses to wrap it as
PATTERN (A (B?) c)

While looking at it, i think the below paragraph is not necessary.
The manual is generally not the place to explain implementation
shortcomings, and the RPR documentation is already quite long.

<para>
Two planner optimizations are not applied to a window that uses row
pattern recognition. A filter on the result of a monotonic window
function, such as <literal>rn &lt;= 3</literal> on
a <function>row_number()</function> computed in a subquery, is not
pushed down into the window as a run condition, so the window is
evaluated to the end of the partition instead of stopping early. And
the frame clause is not replaced with a cheaper one, even when the
window functions using it do not depend on the frame. Query results
are not affected, only the cost of evaluating the window.
</para>

--
jian
https://www.enterprisedb.com/