From 48826523d0578527af2a10bea8bb0020b9dbc80f 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. Which name to reserve is the whole of it, and a Var can carry two. When it reads a column of an aliased join, varno holds the child relation the reference was flattened to and varnosyn holds the join, and get_variable() prints from varnosyn whenever it is set and a parse tree is being deparsed. Resolve the same way here. Reserving the child's name instead protects a name that never reaches the output and leaves the printed one free, so the collision this commit exists to prevent still happens: CREATE VIEW v AS SELECT count(*) OVER w AS c FROM (ka JOIN kb ON ka.i = kb.k) j(p, q, r, s), kd WINDOW w AS (ORDER BY kd.m ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING INITIAL PATTERN (a) DEFINE a AS q > 0); ALTER TABLE kd ADD COLUMN q int; -- feeding pg_get_viewdef() back: ERROR: column reference "q" is ambiguous A join RTE is therefore no longer skipped when the name is taken from eref->colnames. The branch that skipped it reasoned that a join column takes its name from the child later on and the child is pinned in its own right, which is true and is exactly why the printed name was left unguarded. One consequence is deliberate. dpns->using_names is consulted by colname_is_unique() for every RTE of the query, so reserving a join's column name also pushes aside a column of that name in one of the join's own inputs -- even though an aliased join hides its inputs and that name could not have been referenced. Narrowing the reservation means changing what colname_is_unique() does with using_names, which serves USING columns as much as this, so the wider reservation stands and only the deparsed text carries the extra alias list. No alias list appears unless a collision actually occurs, and nothing happens for a query without a DEFINE clause. The restriction this rests on -- that a DEFINE clause names a column without a qualifier, so the name must be unique across the FROM clause -- is now stated on the SELECT reference page. --- doc/src/sgml/ref/select.sgml | 12 + src/backend/utils/adt/ruleutils.c | 135 +++++++++++ src/test/regress/expected/rpr_base.out | 310 +++++++++++++++++++------ src/test/regress/sql/rpr_base.sql | 193 +++++++++++---- 4 files changed, 543 insertions(+), 107 deletions(-) diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml index 0a03957fd23..fbdc89f8b7e 100644 --- a/doc/src/sgml/ref/select.sgml +++ b/doc/src/sgml/ref/select.sgml @@ -1228,6 +1228,18 @@ DEFINE definition_variable_name AS to WITH RECURSIVE. + + A column reference in the DEFINE clause must be + written without a qualifier. The SQL standard reserves the qualifier + slot for a pattern variable, so a table name or alias is rejected + there, and a pattern variable qualifier is rejected as not supported. + The column name therefore has to resolve on its own across the + whole FROM clause, and an ambiguous name is + rejected. Where the name is not unique, rename the column in + the FROM clause, for example with a column alias on + a subquery. + + The purpose of a WINDOW clause is to specify the behavior of window functions appearing in the query's diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 756606f272f..ef03a3fdd70 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); @@ -4433,6 +4436,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); } /* @@ -4735,6 +4744,132 @@ 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; + int varno; + AttrNumber attno; + char *colname; + ListCell *lc; + + /* + * Resolve the reference the way get_variable() will when it prints this + * Var, or the name reserved here is not the name that reaches the output. + * A Var that reads a join column carries the child relation in varno and + * the join RTE in varnosyn, and it is the latter that gets printed. + */ + if (var->varnosyn > 0 && dpns->plan == NULL) + { + varno = var->varnosyn; + attno = var->varattnosyn; + } + else + { + varno = var->varno; + attno = var->varattno; + } + + /* Only ordinary columns of this query level have a name to pin */ + if (var->varlevelsup != 0 || attno <= 0) + return; + if (varno < 1 || varno > list_length(dpns->rtable)) + return; + + rte = rt_fetch(varno, dpns->rtable); + colinfo = deparse_columns_fetch(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 (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 + 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..8d173221511 100644 --- a/src/test/regress/expected/rpr_base.out +++ b/src/test/regress/expected/rpr_base.out @@ -3693,83 +3693,261 @@ 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) -SELECT * FROM sv; +-- 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) + +-- 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; +-- An aliased join hides its inputs, so the name that gets printed is the join's +-- own, taken from varnosyn, not the child column the Var carries in varno. +-- Pinning the child instead would reserve a name that never reaches the output +-- and leave the printed one free for a later column to collide with. +CREATE TABLE rpr_pin_a (i INT, x INT); +CREATE TABLE rpr_pin_b (k INT, y INT); +CREATE TABLE rpr_pin_c (m INT); +INSERT INTO rpr_pin_a VALUES (1, 10), (2, 20); +INSERT INTO rpr_pin_b VALUES (1, 5), (2, 7); +INSERT INTO rpr_pin_c VALUES (100), (200); +CREATE VIEW rpr_pin_alias_v AS +SELECT count(*) OVER w AS cnt +FROM (rpr_pin_a JOIN rpr_pin_b ON rpr_pin_a.i = rpr_pin_b.k) j(p, q, r, s), + rpr_pin_c +WINDOW w AS (ORDER BY rpr_pin_c.m + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS q > 0); +ALTER TABLE rpr_pin_c ADD COLUMN q INT; +SELECT pg_get_viewdef('rpr_pin_alias_v'::regclass, true); + pg_get_viewdef +-------------------------------------------------------------------------------------- + SELECT count(*) OVER w AS cnt + + FROM (rpr_pin_a + + JOIN rpr_pin_b ON rpr_pin_a.i = rpr_pin_b.k) j(p, q, r, s), + + rpr_pin_c rpr_pin_c(m, q_1) + + WINDOW w AS (ORDER BY rpr_pin_c.m ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a) + + DEFINE + + a AS q > 0); +(1 row) + +SELECT * FROM rpr_pin_alias_v; + cnt +----- + 1 + 1 + 1 + 1 +(4 rows) + +-- and the deparsed text builds a view that returns the same rows +CREATE VIEW rpr_pin_alias_v2 AS +SELECT count(*) OVER w AS cnt +FROM (rpr_pin_a JOIN rpr_pin_b ON rpr_pin_a.i = rpr_pin_b.k) j(p, q, r, s), + rpr_pin_c rpr_pin_c(m, q_1) +WINDOW w AS (ORDER BY rpr_pin_c.m + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (a) + DEFINE a AS q > 0); +SELECT * FROM rpr_pin_alias_v2; + cnt +----- + 1 + 1 + 1 + 1 +(4 rows) + +DROP VIEW rpr_pin_alias_v2; +-- Without a user column alias list the join still keeps the printed name, and +-- the input relation is the one that moves aside. +CREATE VIEW rpr_pin_alias_v3 AS +SELECT count(*) OVER w AS cnt +FROM (rpr_pin_a JOIN rpr_pin_b ON rpr_pin_a.i = rpr_pin_b.k) j, rpr_pin_c +WINDOW w AS (ORDER BY rpr_pin_c.m + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS x > 0); +SELECT pg_get_viewdef('rpr_pin_alias_v3'::regclass, true); + pg_get_viewdef +-------------------------------------------------------------------------------------- + SELECT count(*) OVER w AS cnt + + FROM (rpr_pin_a rpr_pin_a(i, x_1) + + JOIN rpr_pin_b ON rpr_pin_a.i = rpr_pin_b.k) j(i, x, k, y), + + rpr_pin_c + + WINDOW w AS (ORDER BY rpr_pin_c.m ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ + AFTER MATCH SKIP PAST LAST ROW + + INITIAL + + PATTERN (a) + + DEFINE + + a AS x > 0); +(1 row) + +DROP VIEW rpr_pin_alias_v3; +DROP VIEW rpr_pin_alias_v; +DROP TABLE rpr_pin_a, rpr_pin_b, rpr_pin_c; +-- 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..06e2fda3bc0 100644 --- a/src/test/regress/sql/rpr_base.sql +++ b/src/test/regress/sql/rpr_base.sql @@ -2377,47 +2377,158 @@ 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; + +-- An aliased join hides its inputs, so the name that gets printed is the join's +-- own, taken from varnosyn, not the child column the Var carries in varno. +-- Pinning the child instead would reserve a name that never reaches the output +-- and leave the printed one free for a later column to collide with. +CREATE TABLE rpr_pin_a (i INT, x INT); +CREATE TABLE rpr_pin_b (k INT, y INT); +CREATE TABLE rpr_pin_c (m INT); +INSERT INTO rpr_pin_a VALUES (1, 10), (2, 20); +INSERT INTO rpr_pin_b VALUES (1, 5), (2, 7); +INSERT INTO rpr_pin_c VALUES (100), (200); + +CREATE VIEW rpr_pin_alias_v AS +SELECT count(*) OVER w AS cnt +FROM (rpr_pin_a JOIN rpr_pin_b ON rpr_pin_a.i = rpr_pin_b.k) j(p, q, r, s), + rpr_pin_c +WINDOW w AS (ORDER BY rpr_pin_c.m + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS q > 0); + +ALTER TABLE rpr_pin_c ADD COLUMN q INT; + +SELECT pg_get_viewdef('rpr_pin_alias_v'::regclass, true); +SELECT * FROM rpr_pin_alias_v; + +-- and the deparsed text builds a view that returns the same rows +CREATE VIEW rpr_pin_alias_v2 AS +SELECT count(*) OVER w AS cnt +FROM (rpr_pin_a JOIN rpr_pin_b ON rpr_pin_a.i = rpr_pin_b.k) j(p, q, r, s), + rpr_pin_c rpr_pin_c(m, q_1) +WINDOW w AS (ORDER BY rpr_pin_c.m + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + INITIAL + PATTERN (a) + DEFINE a AS q > 0); +SELECT * FROM rpr_pin_alias_v2; +DROP VIEW rpr_pin_alias_v2; + +-- Without a user column alias list the join still keeps the printed name, and +-- the input relation is the one that moves aside. +CREATE VIEW rpr_pin_alias_v3 AS +SELECT count(*) OVER w AS cnt +FROM (rpr_pin_a JOIN rpr_pin_b ON rpr_pin_a.i = rpr_pin_b.k) j, rpr_pin_c +WINDOW w AS (ORDER BY rpr_pin_c.m + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS x > 0); +SELECT pg_get_viewdef('rpr_pin_alias_v3'::regclass, true); + +DROP VIEW rpr_pin_alias_v3; +DROP VIEW rpr_pin_alias_v; +DROP TABLE rpr_pin_a, rpr_pin_b, rpr_pin_c; + +-- 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)