From d36648d007c6a6ad2e2248de338685810f0f4265 Mon Sep 17 00:00:00 2001 From: Henson Choi Date: Sat, 15 Aug 2026 09:38:55 +0900 Subject: [PATCH] Pin the column names a DEFINE clause references when deparsing A DEFINE clause names a column without a qualifier: the qualifier slot belongs to the pattern variable, and get_rule_define() deparses with varprefix off for that reason. The bare name therefore has to resolve exactly as printed. When another relation of the same query acquires a column of that name, the deparsed view no longer re-parses and pg_dump output fails to restore. Reserve those names the way set_using_names() reserves a merged USING name: register each one in dpns->using_names so no other RTE can be assigned it, and store it into the owning RTE's colnames entry, which exempts the column itself from being renamed. set_relation_column_names() then uniquifies the intruding column to name_1 and prints a column alias list for its RTE, and the definition round-trips again. A name already chosen by set_using_names(), for a join's merged column or one of its inputs, is reserved as it stands. No alias list appears unless a collision actually occurs, and nothing happens for a query without a DEFINE clause. --- src/backend/utils/adt/ruleutils.c | 125 ++++++++++++++ src/test/regress/expected/rpr_base.out | 219 +++++++++++++++++-------- src/test/regress/sql/rpr_base.sql | 139 +++++++++++----- 3 files changed, 376 insertions(+), 107 deletions(-) diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 180ad5627e7..a247b4175ec 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -390,6 +390,9 @@ static void set_simple_column_names(deparse_namespace *dpns); static bool has_dangerous_join_using(deparse_namespace *dpns, Node *jtnode); static void set_using_names(deparse_namespace *dpns, Node *jtnode, List *parentUsing); +static void set_define_names(deparse_namespace *dpns, Query *query); +static bool set_define_names_walker(Node *node, deparse_namespace *dpns); +static void pin_define_colname(deparse_namespace *dpns, Var *var); static void set_relation_column_names(deparse_namespace *dpns, RangeTblEntry *rte, deparse_columns *colinfo); @@ -4434,6 +4437,12 @@ set_deparse_for_query(deparse_namespace *dpns, Query *query, * the query jointree. */ set_using_names(dpns, (Node *) query->jointree, NIL); + + /* + * Pin the column names that DEFINE clauses reference, so that they + * still resolve as written when the query is re-parsed. + */ + set_define_names(dpns, query); } /* @@ -4736,6 +4745,122 @@ set_using_names(deparse_namespace *dpns, Node *jtnode, List *parentUsing) (int) nodeTag(jtnode)); } +/* + * set_define_names: pin the column names that DEFINE clauses reference + * + * Within a DEFINE clause a column can only be named without a qualifier, + * since the qualifier slot names a pattern variable; get_rule_define() + * deparses with varprefix off for that reason. So an unqualified reference + * there has to resolve exactly as printed, much like a column merged by + * USING. If another relation of the same query later acquires a column of + * that name, re-parsing the deparsed query would find the name ambiguous. + * + * We therefore treat such a name the way set_using_names() treats a merged + * USING name: register it in dpns->using_names, so that no other RTE can be + * given that name, and store it into the owning RTE's colnames entry, which + * exempts the column itself from being renamed. set_relation_column_names() + * then does the rest, uniquifying the intruding column to name_1 and printing + * a column alias list for its RTE. + */ +static void +set_define_names(deparse_namespace *dpns, Query *query) +{ + ListCell *lc; + + foreach(lc, query->windowClause) + { + WindowClause *wc = lfirst_node(WindowClause, lc); + + if (wc->defineClause != NIL) + (void) set_define_names_walker((Node *) wc->defineClause, dpns); + } +} + +/* + * Walk a DEFINE clause, pinning the name of every column it references. + */ +static bool +set_define_names_walker(Node *node, deparse_namespace *dpns) +{ + if (node == NULL) + return false; + if (IsA(node, Var)) + { + pin_define_colname(dpns, (Var *) node); + return false; + } + /* Sub-selects are not allowed here, but be safe: they have own namespace */ + if (IsA(node, Query)) + return false; + return expression_tree_walker(node, set_define_names_walker, dpns); +} + +/* + * pin_define_colname: reserve the printed name of one DEFINE-referenced column + */ +static void +pin_define_colname(deparse_namespace *dpns, Var *var) +{ + RangeTblEntry *rte; + deparse_columns *colinfo; + AttrNumber attno = var->varattno; + char *colname; + ListCell *lc; + + /* Only ordinary columns of this query level have a name to pin */ + if (var->varlevelsup != 0 || attno <= 0) + return; + if (var->varno < 1 || var->varno > list_length(dpns->rtable)) + return; + + rte = rt_fetch(var->varno, dpns->rtable); + colinfo = deparse_columns_fetch(var->varno, dpns); + + /* + * Find the name this column is going to be printed with. If a name was + * assigned already, that is the one to protect; set_using_names() does + * that both for a join's merged column and for the input columns it was + * merged from. + */ + if (attno <= colinfo->num_cols && colinfo->colnames[attno - 1] != NULL) + colname = colinfo->colnames[attno - 1]; + else if (rte->rtekind == RTE_RELATION) + { + /* Consult the catalogs, as set_relation_column_names() will */ + colname = get_attname(rte->relid, attno, true); + if (colname == NULL) + return; /* dropped column */ + expand_colnames_array_to(colinfo, attno); + colinfo->colnames[attno - 1] = colname; + } + else if (rte->rtekind != RTE_JOIN && + attno <= list_length(rte->eref->colnames)) + { + colname = strVal(list_nth(rte->eref->colnames, attno - 1)); + if (colname[0] == '\0') + return; /* dropped column */ + expand_colnames_array_to(colinfo, attno); + colinfo->colnames[attno - 1] = colname; + } + else + { + /* + * A join column that set_using_names() left alone. Its name is taken + * from the child column later on, and the child is pinned in its own + * right if the DEFINE clause reaches it, so there is nothing to do. + */ + return; + } + + /* Reserve the name query-wide, unless it is reserved already */ + foreach(lc, dpns->using_names) + { + if (strcmp((char *) lfirst(lc), colname) == 0) + return; + } + dpns->using_names = lappend(dpns->using_names, colname); +} + /* * set_relation_column_names: select column aliases for a non-join RTE * diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out index bd6904ecc66..28f05d25f1c 100644 --- a/src/test/regress/expected/rpr_base.out +++ b/src/test/regress/expected/rpr_base.out @@ -3693,83 +3693,170 @@ SELECT pg_get_viewdef('rpr_serial_join'::regclass); up AS (val > 0)); (1 row) --- Ambiguity introduced after the view was created: ALTER TABLE adds a column --- whose name already appears in the other side of the join, so the deparser --- must qualify or alias it. sv3 shows the same text is rejected on a fresh --- CREATE VIEW; sv4 shows the alias form that survives. These stay temporary --- and are dropped at the end: sv is deliberately unrestorable, so leaving it --- in place would hand pg_dump a view that cannot be restored. -CREATE TEMP TABLE sa (id int, price int); -CREATE TEMP TABLE sb (id int, qty int); -INSERT INTO sa VALUES (1,10),(2,20); -INSERT INTO sb VALUES (1,5),(2,7); -CREATE TEMP VIEW sv AS -SELECT a.id, count(*) OVER w AS cnt -FROM sa a JOIN sb b ON a.id = b.id -WINDOW w AS (ORDER BY a.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - PATTERN (UP+) DEFINE UP AS price > 0); -ALTER TABLE sb ADD COLUMN price int; -SELECT pg_get_viewdef('sv'::regclass, true); - pg_get_viewdef -------------------------------------------------------------------------------- - SELECT a.id, + - count(*) OVER w AS cnt + - FROM sa a + - JOIN sb b ON a.id = b.id + - WINDOW w AS (ORDER BY a.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN (up+) + - DEFINE + - up AS price > 0); +-- A DEFINE clause can only name a column without a qualifier, so the name has +-- to resolve exactly as printed. When another relation of the query acquires +-- a column of that name, the deparser pushes the newcomer aside with a column +-- alias list, the same way it protects a column merged by USING. +CREATE TABLE rpr_pin (id INT, val INT); +CREATE TABLE rpr_pin_other (id INT); +INSERT INTO rpr_pin VALUES (1, 10), (2, 20), (3, 15); +INSERT INTO rpr_pin_other VALUES (1), (2), (3); +CREATE VIEW rpr_pin_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_pin, rpr_pin_other +WHERE rpr_pin.id = rpr_pin_other.id +WINDOW w AS (ORDER BY rpr_pin.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS val > 0); +-- names reached through a navigation operation are pinned too +CREATE VIEW rpr_pin_nav_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_pin, rpr_pin_other +WHERE rpr_pin.id = rpr_pin_other.id +WINDOW w AS (ORDER BY rpr_pin.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(val) < val); +-- no collision yet, so no column alias list +SELECT pg_get_viewdef('rpr_pin_v'::regclass, true); + pg_get_viewdef +------------------------------------------------------------------------------------- + SELECT count(*) OVER w AS cnt + + FROM rpr_pin, + + rpr_pin_other + + WHERE rpr_pin.id = rpr_pin_other.id + + WINDOW w AS (ORDER BY rpr_pin.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS val > 0); (1 row) --- ERROR: the deparsed text above no longer re-parses -CREATE TEMP VIEW sv3 AS -SELECT a.id, count(*) OVER w AS cnt -FROM sa a JOIN sb b ON a.id = b.id -WINDOW w AS (ORDER BY a.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - PATTERN (UP+) DEFINE UP AS price > 0); -ERROR: column reference "price" is ambiguous -LINE 5: PATTERN (UP+) DEFINE UP AS price > 0); - ^ -CREATE TEMP VIEW sv4 AS -SELECT a.id, count(*) OVER w AS cnt -FROM sa a (id, price) JOIN sb b (id, qty, price_1) ON a.id = b.id -WINDOW w AS (ORDER BY a.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - PATTERN (UP+) DEFINE UP AS price > 0); -SELECT pg_get_viewdef('sv4'::regclass, true); - pg_get_viewdef -------------------------------------------------------------------------------- - SELECT a.id, + - count(*) OVER w AS cnt + - FROM sa a + - JOIN sb b(id, qty, price_1) ON a.id = b.id + - WINDOW w AS (ORDER BY a.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN (up+) + - DEFINE + - up AS price > 0); +ALTER TABLE rpr_pin_other ADD COLUMN val INT; +SELECT pg_get_viewdef('rpr_pin_v'::regclass, true); + pg_get_viewdef +------------------------------------------------------------------------------------- + SELECT count(*) OVER w AS cnt + + FROM rpr_pin, + + rpr_pin_other rpr_pin_other(id, val_1) + + WHERE rpr_pin.id = rpr_pin_other.id + + WINDOW w AS (ORDER BY rpr_pin.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS val > 0); (1 row) -SELECT * FROM sv4; - id | cnt -----+----- - 1 | 2 - 2 | 0 -(2 rows) +SELECT pg_get_viewdef('rpr_pin_nav_v'::regclass, true); + pg_get_viewdef +------------------------------------------------------------------------------------- + SELECT count(*) OVER w AS cnt + + FROM rpr_pin, + + rpr_pin_other rpr_pin_other(id, val_1) + + WHERE rpr_pin.id = rpr_pin_other.id + + WINDOW w AS (ORDER BY rpr_pin.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS PREV(val) < val); +(1 row) + +-- and the deparsed text builds an identical view +CREATE VIEW rpr_pin_v2 AS + SELECT count(*) OVER w AS cnt + FROM rpr_pin, + rpr_pin_other rpr_pin_other(id, val_1) + WHERE rpr_pin.id = rpr_pin_other.id + WINDOW w AS (ORDER BY rpr_pin.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (a+) + DEFINE + a AS val > 0); +SELECT pg_get_viewdef('rpr_pin_v'::regclass, true) + = pg_get_viewdef('rpr_pin_v2'::regclass, true) AS identical; + identical +----------- + t +(1 row) + +-- a column merged by USING is pinned the same way +CREATE TABLE rpr_pin_l (x INT, y INT); +CREATE TABLE rpr_pin_r (x INT, z INT); +CREATE TABLE rpr_pin_x (id INT); +CREATE VIEW rpr_pin_using_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_pin_l JOIN rpr_pin_r USING (x), rpr_pin_x +WINDOW w AS (ORDER BY rpr_pin_l.y + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS x > 0); +ALTER TABLE rpr_pin_x ADD COLUMN x INT; +SELECT pg_get_viewdef('rpr_pin_using_v'::regclass, true); + pg_get_viewdef +-------------------------------------------------------------------------------------- + SELECT count(*) OVER w AS cnt + + FROM rpr_pin_l + + JOIN rpr_pin_r USING (x), + + rpr_pin_x rpr_pin_x(id, x_1) + + WINDOW w AS (ORDER BY rpr_pin_l.y ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS x > 0); +(1 row) -SELECT * FROM sv; +-- a JOIN ... ON behaves the same, and the view keeps returning its rows +CREATE TABLE rpr_pin_j1 (id INT, price INT); +CREATE TABLE rpr_pin_j2 (id INT, qty INT); +INSERT INTO rpr_pin_j1 VALUES (1, 10), (2, 20); +INSERT INTO rpr_pin_j2 VALUES (1, 5), (2, 7); +CREATE VIEW rpr_pin_on_v AS +SELECT j1.id, count(*) OVER w AS cnt +FROM rpr_pin_j1 j1 JOIN rpr_pin_j2 j2 ON j1.id = j2.id +WINDOW w AS (ORDER BY j1.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS price > 0); +ALTER TABLE rpr_pin_j2 ADD COLUMN price INT; +SELECT pg_get_viewdef('rpr_pin_on_v'::regclass, true); + pg_get_viewdef +-------------------------------------------------------------------------------- + SELECT j1.id, + + count(*) OVER w AS cnt + + FROM rpr_pin_j1 j1 + + JOIN rpr_pin_j2 j2(id, qty, price_1) ON j1.id = j2.id + + WINDOW w AS (ORDER BY j1.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a+) + + DEFINE + + a AS price > 0); +(1 row) + +SELECT * FROM rpr_pin_on_v ORDER BY id; id | cnt ----+----- 1 | 2 2 | 0 (2 rows) -DROP VIEW sv4; -DROP VIEW sv; -DROP TABLE sa, sb; +-- The same query written fresh is rejected, since nothing pins the name for +-- it. Pinning is what lets the stored definition above still reparse. +SELECT j1.id, count(*) OVER w AS cnt +FROM rpr_pin_j1 j1 JOIN rpr_pin_j2 j2 ON j1.id = j2.id +WINDOW w AS (ORDER BY j1.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS price > 0); +ERROR: column reference "price" is ambiguous +LINE 6: DEFINE A AS price > 0); + ^ -- Materialized view (if supported) CREATE TABLE rpr_mview (id INT, val INT); INSERT INTO rpr_mview VALUES (1, 10), (2, 20), (3, 30); diff --git a/src/test/regress/sql/rpr_base.sql b/src/test/regress/sql/rpr_base.sql index 361d0923c2a..ed197909f95 100644 --- a/src/test/regress/sql/rpr_base.sql +++ b/src/test/regress/sql/rpr_base.sql @@ -2377,47 +2377,104 @@ WINDOW w AS (ORDER BY s.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (UP+) DEFINE UP AS val > 0); SELECT pg_get_viewdef('rpr_serial_join'::regclass); --- Ambiguity introduced after the view was created: ALTER TABLE adds a column --- whose name already appears in the other side of the join, so the deparser --- must qualify or alias it. sv3 shows the same text is rejected on a fresh --- CREATE VIEW; sv4 shows the alias form that survives. These stay temporary --- and are dropped at the end: sv is deliberately unrestorable, so leaving it --- in place would hand pg_dump a view that cannot be restored. -CREATE TEMP TABLE sa (id int, price int); -CREATE TEMP TABLE sb (id int, qty int); -INSERT INTO sa VALUES (1,10),(2,20); -INSERT INTO sb VALUES (1,5),(2,7); - -CREATE TEMP VIEW sv AS -SELECT a.id, count(*) OVER w AS cnt -FROM sa a JOIN sb b ON a.id = b.id -WINDOW w AS (ORDER BY a.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - PATTERN (UP+) DEFINE UP AS price > 0); - -ALTER TABLE sb ADD COLUMN price int; - -SELECT pg_get_viewdef('sv'::regclass, true); - --- ERROR: the deparsed text above no longer re-parses -CREATE TEMP VIEW sv3 AS -SELECT a.id, count(*) OVER w AS cnt -FROM sa a JOIN sb b ON a.id = b.id -WINDOW w AS (ORDER BY a.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - PATTERN (UP+) DEFINE UP AS price > 0); - -CREATE TEMP VIEW sv4 AS -SELECT a.id, count(*) OVER w AS cnt -FROM sa a (id, price) JOIN sb b (id, qty, price_1) ON a.id = b.id -WINDOW w AS (ORDER BY a.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - PATTERN (UP+) DEFINE UP AS price > 0); - -SELECT pg_get_viewdef('sv4'::regclass, true); -SELECT * FROM sv4; -SELECT * FROM sv; - -DROP VIEW sv4; -DROP VIEW sv; -DROP TABLE sa, sb; +-- A DEFINE clause can only name a column without a qualifier, so the name has +-- to resolve exactly as printed. When another relation of the query acquires +-- a column of that name, the deparser pushes the newcomer aside with a column +-- alias list, the same way it protects a column merged by USING. + +CREATE TABLE rpr_pin (id INT, val INT); +CREATE TABLE rpr_pin_other (id INT); +INSERT INTO rpr_pin VALUES (1, 10), (2, 20), (3, 15); +INSERT INTO rpr_pin_other VALUES (1), (2), (3); + +CREATE VIEW rpr_pin_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_pin, rpr_pin_other +WHERE rpr_pin.id = rpr_pin_other.id +WINDOW w AS (ORDER BY rpr_pin.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS val > 0); + +-- names reached through a navigation operation are pinned too +CREATE VIEW rpr_pin_nav_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_pin, rpr_pin_other +WHERE rpr_pin.id = rpr_pin_other.id +WINDOW w AS (ORDER BY rpr_pin.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(val) < val); + +-- no collision yet, so no column alias list +SELECT pg_get_viewdef('rpr_pin_v'::regclass, true); + +ALTER TABLE rpr_pin_other ADD COLUMN val INT; + +SELECT pg_get_viewdef('rpr_pin_v'::regclass, true); +SELECT pg_get_viewdef('rpr_pin_nav_v'::regclass, true); + +-- and the deparsed text builds an identical view +CREATE VIEW rpr_pin_v2 AS + SELECT count(*) OVER w AS cnt + FROM rpr_pin, + rpr_pin_other rpr_pin_other(id, val_1) + WHERE rpr_pin.id = rpr_pin_other.id + WINDOW w AS (ORDER BY rpr_pin.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (a+) + DEFINE + a AS val > 0); + +SELECT pg_get_viewdef('rpr_pin_v'::regclass, true) + = pg_get_viewdef('rpr_pin_v2'::regclass, true) AS identical; + +-- a column merged by USING is pinned the same way +CREATE TABLE rpr_pin_l (x INT, y INT); +CREATE TABLE rpr_pin_r (x INT, z INT); +CREATE TABLE rpr_pin_x (id INT); + +CREATE VIEW rpr_pin_using_v AS +SELECT count(*) OVER w AS cnt +FROM rpr_pin_l JOIN rpr_pin_r USING (x), rpr_pin_x +WINDOW w AS (ORDER BY rpr_pin_l.y + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS x > 0); + +ALTER TABLE rpr_pin_x ADD COLUMN x INT; + +SELECT pg_get_viewdef('rpr_pin_using_v'::regclass, true); + +-- a JOIN ... ON behaves the same, and the view keeps returning its rows +CREATE TABLE rpr_pin_j1 (id INT, price INT); +CREATE TABLE rpr_pin_j2 (id INT, qty INT); +INSERT INTO rpr_pin_j1 VALUES (1, 10), (2, 20); +INSERT INTO rpr_pin_j2 VALUES (1, 5), (2, 7); + +CREATE VIEW rpr_pin_on_v AS +SELECT j1.id, count(*) OVER w AS cnt +FROM rpr_pin_j1 j1 JOIN rpr_pin_j2 j2 ON j1.id = j2.id +WINDOW w AS (ORDER BY j1.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS price > 0); + +ALTER TABLE rpr_pin_j2 ADD COLUMN price INT; + +SELECT pg_get_viewdef('rpr_pin_on_v'::regclass, true); +SELECT * FROM rpr_pin_on_v ORDER BY id; + +-- The same query written fresh is rejected, since nothing pins the name for +-- it. Pinning is what lets the stored definition above still reparse. +SELECT j1.id, count(*) OVER w AS cnt +FROM rpr_pin_j1 j1 JOIN rpr_pin_j2 j2 ON j1.id = j2.id +WINDOW w AS (ORDER BY j1.id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS price > 0); + -- Materialized view (if supported)