Re: Row pattern recognition

On Wed, Sep 30, 2026 at 2:13 PM Henson Choi <assam258(at)gmail(dot)com> wrote:
>
> Finally, a question: of the three options above -- moving the two
> NFA-related files into a separate module, distributing the cross-feature
> tests into each feature's existing tests, and splitting rpr_base.sql
> into topical files -- which one, or which combination, do you think is
> realistic? If you see another direction, I'd like to hear that too.
> Either way, I'd want what the tests verify kept as it is.
>

-- ============================================================
-- Subquery and CTE Tests
-- ============================================================
-- Tests RPR with subqueries and CTEs

-- RPR in Subquery (FROM clause)

SELECT * FROM (
SELECT id, category, val,
COUNT(*) OVER w as cnt
FROM rpr_planner
WINDOW w AS (
ORDER BY id
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
PATTERN (A+)
DEFINE A AS val > 0
)
) sub
WHERE cnt > 5
ORDER BY id;
---------------------------------------------
The above tests from src/test/regress/sql/rpr_base.sql
The RPR window runs inside the subquery and produces rows; the outer
query then filters and sorts them, which is ordinary subquery behavior
that has nothing to do with RPR.
Whether the rows come from a subquery, a CTE, a JOIN, or a plain table
is irrelevant to RPR, so the above test adds no coverage beyond what
the plain-table cases already give.

Moving tests from one file to another does not solve the problem.
We should first try harder to remove unnecessary test queries.
Consolidating all the error cases into one place would also be a good idea.

In src/test/regress/sql/rpr_base.sql, I saw comments like
```
-- Complex Multi-Level Nesting
-- Pattern: (((A B) | C)+ D)+

```
I don't think the above comments are really any helpful.
The pattern is the same as the SELECT query below.
If the test query changes, the above comments will become stale.
It also occupied an unnecessary blank line.

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