From e9e41cc3c9134ad926e81c640601358e3d5eb9dc Mon Sep 17 00:00:00 2001 From: Henson Choi Date: Thu, 22 Oct 2026 14:16:13 +0900 Subject: [PATCH 2/6] Remove the column alias list fix for table functions This series carried its own fix for the deparse of XMLTABLE and JSON_TABLE column alias lists, which is not specific to row pattern recognition. It goes upstream as a patch of its own. Remove the fix here, the tests for it in create_view, and its mention in README.rpr. Remove also the rpr_base tests that need the fix, where a DEFINE clause makes a JSON_TABLE column be renamed. What they check of row pattern recognition, the names a DEFINE clause reserves, is checked by the rpr_base tests on relations and joins, and the renaming of a JSON_TABLE column by the tests of that patch. --- src/backend/executor/README.rpr | 6 +- src/backend/utils/adt/ruleutils.c | 12 +- src/test/regress/expected/create_view.out | 354 ------------ src/test/regress/expected/rpr_base.out | 675 ---------------------- src/test/regress/sql/create_view.sql | 134 ----- src/test/regress/sql/rpr_base.sql | 230 -------- 6 files changed, 8 insertions(+), 1403 deletions(-) diff --git a/src/backend/executor/README.rpr b/src/backend/executor/README.rpr index 16e158fdcf3..afe366fa40d 100644 --- a/src/backend/executor/README.rpr +++ b/src/backend/executor/README.rpr @@ -2553,10 +2553,8 @@ every query, with a DEFINE clause or not. For a function RTE it appends the columns the composite result type has gained since parse time (function_rte_late_colnames(); only for a single function without WITH ORDINALITY or a column definition list), so a reserved name is kept off -them and the positional column alias list counts them. A table function -RTE (XMLTABLE, JSON_TABLE) prints a column alias list when it has -user-written column aliases or a column was renamed, as other non-relation -RTEs do. The columns colname_is_fixed() reports are never renamed there. +them and the positional column alias list counts them. The columns +colname_is_fixed() reports are never renamed there. XIII-6. Navigation Names Shadow User Functions diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index e9560bfea94..d411d79c6ae 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -5015,17 +5015,17 @@ set_relation_column_names(deparse_namespace *dpns, RangeTblEntry *rte, * are different from the underlying "real" names. For a function RTE, * always emit a complete column alias list; this is to protect against * possible instability of the default column names (eg, from altering - * parameter names). For other RTE types, print if we changed anything OR - * if there were user-written column aliases (since the latter would be - * part of the underlying "reality"). A tablefunc RTE is among those: its - * clause names the columns it produces, but it accepts a column alias - * list like any other, and one is needed once a column has had to be - * renamed. + * parameter names). For tablefunc RTEs, we never print aliases, because + * the column names are part of the clause itself. For other RTE types, + * print if we changed anything OR if there were user-written column + * aliases (since the latter would be part of the underlying "reality"). */ if (rte->rtekind == RTE_RELATION) colinfo->printaliases = changed_any; else if (rte->rtekind == RTE_FUNCTION) colinfo->printaliases = true; + else if (rte->rtekind == RTE_TABLEFUNC) + colinfo->printaliases = false; else if (rte->alias && rte->alias->colnames != NIL) colinfo->printaliases = true; else diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out index 9551039968e..68734694ee4 100644 --- a/src/test/regress/expected/create_view.out +++ b/src/test/regress/expected/create_view.out @@ -971,360 +971,6 @@ select pg_get_viewdef('view_of_joins_2d', true); JOIN tbl1a USING (a) AS x) y; (1 row) --- A TABLEFUNC RTE names its columns in the clause that produces them, but it --- accepts a column alias list like any other RTE, and that list is where a --- rename of one of its columns has to be printed: the ON clause below refers --- to the column by the name the query sees, and without the list the two --- would not agree. The anonymous FULL JOIN is what forces USING names to be --- unique query-wide, which is what pushes the JSON_TABLE column aside. -create table tblnr (m int); -create table tblnu (x int, m int); -create table tblnl (x int); -create table tblnm (x int); -create table tblnw (a int, c int, d int); -create table tblnv (c int); -create view view_of_unrenamable as -select j.m -from (tblnl full join tblnm using (x)), - (json_table(jsonb '[1,2]', '$[*]' columns (x int path '$')) as jt - join tblnr on jt.x > 0) j; -select pg_get_viewdef('view_of_unrenamable', true); - pg_get_viewdef ----------------------------------------------------------- - SELECT j.m + - FROM tblnl + - FULL JOIN tblnm USING (x), + - (JSON_TABLE( + - '[1, 2]'::jsonb, '$[*]' AS json_table_path_0+ - COLUMNS ( + - x integer PATH '$' + - ) + - ) jt(x_1) + - JOIN tblnr ON jt.x_1 > 0) j; -(1 row) - --- and that text is what has to reparse -select 'create view view_of_unrenamable_2 as ' - || pg_get_viewdef('view_of_unrenamable', true) \gexec -create view view_of_unrenamable_2 as SELECT j.m - FROM tblnl - FULL JOIN tblnm USING (x), - (JSON_TABLE( - '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 - COLUMNS ( - x integer PATH '$' - ) - ) jt(x_1) - JOIN tblnr ON jt.x_1 > 0) j; -select pg_get_viewdef('view_of_unrenamable', true) - = pg_get_viewdef('view_of_unrenamable_2', true) as round_trips; - round_trips -------------- - t -(1 row) - --- The name of a merged column is the other place a rename lands, and it lands --- on both sides at once: whatever is picked, each input has to answer to it, --- the TABLEFUNC through its alias list like the table through its own. -create view view_of_unrenamable_using as -select j.m -from (tblnl full join tblnm using (x)), - (json_table(jsonb '[1,2]', '$[*]' columns (x int path '$')) as jt - join tblnu using (x)) j; -select pg_get_viewdef('view_of_unrenamable_using', true); - pg_get_viewdef ----------------------------------------------------------- - SELECT j.m + - FROM tblnl + - FULL JOIN tblnm USING (x), + - (JSON_TABLE( + - '[1, 2]'::jsonb, '$[*]' AS json_table_path_0+ - COLUMNS ( + - x integer PATH '$' + - ) + - ) jt(x_1) + - JOIN tblnu tblnu(x_1, m) USING (x_1)) j; -(1 row) - -select 'create view view_of_unrenamable_using_2 as ' - || pg_get_viewdef('view_of_unrenamable_using', true) \gexec -create view view_of_unrenamable_using_2 as SELECT j.m - FROM tblnl - FULL JOIN tblnm USING (x), - (JSON_TABLE( - '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 - COLUMNS ( - x integer PATH '$' - ) - ) jt(x_1) - JOIN tblnu tblnu(x_1, m) USING (x_1)) j; -select pg_get_viewdef('view_of_unrenamable_using', true) - = pg_get_viewdef('view_of_unrenamable_using_2', true) as round_trips; - round_trips -------------- - t -(1 row) - --- The far side of an INNER or LEFT JOIN is as much a side as the near one: --- the merged column's expression names only the near input, but the rename --- reaches both. -create view view_of_unrenamable_right as -select j.m -from (tblnl full join tblnm using (x)), - ((tblnu join json_table(jsonb '[1,2]', '$[*]' columns (x int path '$')) as jt - using (x)) full join tblnm t2 using (x)) j; -select pg_get_viewdef('view_of_unrenamable_right', true); - pg_get_viewdef ----------------------------------------------------------- - SELECT j.m + - FROM tblnl + - FULL JOIN tblnm USING (x), + - (tblnu tblnu(x_1, m) + - JOIN JSON_TABLE( + - '[1, 2]'::jsonb, '$[*]' AS json_table_path_0+ - COLUMNS ( + - x integer PATH '$' + - ) + - ) jt(x_1) USING (x_1) + - FULL JOIN tblnm t2(x_1) USING (x_1)) j; -(1 row) - -select 'create view view_of_unrenamable_right_2 as ' - || pg_get_viewdef('view_of_unrenamable_right', true) \gexec -create view view_of_unrenamable_right_2 as SELECT j.m - FROM tblnl - FULL JOIN tblnm USING (x), - (tblnu tblnu(x_1, m) - JOIN JSON_TABLE( - '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 - COLUMNS ( - x integer PATH '$' - ) - ) jt(x_1) USING (x_1) - FULL JOIN tblnm t2(x_1) USING (x_1)) j; -select pg_get_viewdef('view_of_unrenamable_right', true) - = pg_get_viewdef('view_of_unrenamable_right_2', true) as round_trips; - round_trips -------------- - t -(1 row) - -create view view_of_unrenamable_left as -select j.m -from (tblnl full join tblnm using (x)), - ((tblnu left join json_table(jsonb '[1,2]', '$[*]' columns (x int path '$')) as jt - using (x)) full join tblnm t2 using (x)) j; -select pg_get_viewdef('view_of_unrenamable_left', true); - pg_get_viewdef ----------------------------------------------------------- - SELECT j.m + - FROM tblnl + - FULL JOIN tblnm USING (x), + - (tblnu tblnu(x_1, m) + - LEFT JOIN JSON_TABLE( + - '[1, 2]'::jsonb, '$[*]' AS json_table_path_0+ - COLUMNS ( + - x integer PATH '$' + - ) + - ) jt(x_1) USING (x_1) + - FULL JOIN tblnm t2(x_1) USING (x_1)) j; -(1 row) - -select 'create view view_of_unrenamable_left_2 as ' - || pg_get_viewdef('view_of_unrenamable_left', true) \gexec -create view view_of_unrenamable_left_2 as SELECT j.m - FROM tblnl - FULL JOIN tblnm USING (x), - (tblnu tblnu(x_1, m) - LEFT JOIN JSON_TABLE( - '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 - COLUMNS ( - x integer PATH '$' - ) - ) jt(x_1) USING (x_1) - FULL JOIN tblnm t2(x_1) USING (x_1)) j; -select pg_get_viewdef('view_of_unrenamable_left', true) - = pg_get_viewdef('view_of_unrenamable_left_2', true) as round_trips; - round_trips -------------- - t -(1 row) - --- An aliased join hides its inputs, and a USING name above it is taken by --- the join's own column: the join renames it on its alias list, and the --- TABLEFUNC underneath, being held to the query-wide USING names as well, --- renames its column on its own list. -create view view_of_unrenamable_hidden as -select j.m -from (tblnl full join tblnm using (x)), - ((json_table(jsonb '[1,2]', '$[*]' columns (x int path '$')) as jt - join tblnr on true) j full join tblnm t2 using (x)); -select pg_get_viewdef('view_of_unrenamable_hidden', true); - pg_get_viewdef ----------------------------------------------------------- - SELECT j.m + - FROM tblnl + - FULL JOIN tblnm USING (x), + - (JSON_TABLE( + - '[1, 2]'::jsonb, '$[*]' AS json_table_path_0+ - COLUMNS ( + - x integer PATH '$' + - ) + - ) jt(x_2) + - JOIN tblnr ON true) j(x_1, m) + - FULL JOIN tblnm t2(x_1) USING (x_1); -(1 row) - -select 'create view view_of_unrenamable_hidden_2 as ' - || pg_get_viewdef('view_of_unrenamable_hidden', true) \gexec -create view view_of_unrenamable_hidden_2 as SELECT j.m - FROM tblnl - FULL JOIN tblnm USING (x), - (JSON_TABLE( - '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 - COLUMNS ( - x integer PATH '$' - ) - ) jt(x_2) - JOIN tblnr ON true) j(x_1, m) - FULL JOIN tblnm t2(x_1) USING (x_1); -select pg_get_viewdef('view_of_unrenamable_hidden', true) - = pg_get_viewdef('view_of_unrenamable_hidden_2', true) as round_trips; - round_trips -------------- - t -(1 row) - --- A name a parent join pushes down onto a merged column has to be unique in --- the input it lands in, whatever the USING clause underneath spells. -create view view_of_unrenamable_pushed as -select j2.d -from (tblnv join (tblnw join json_table(jsonb '[1]', '$[*]' columns (a int path '$')) x - using (a)) using (c)) as j2(a); -select pg_get_viewdef('view_of_unrenamable_pushed', true); - pg_get_viewdef -------------------------------------------------------- - SELECT j2.d + - FROM (tblnv tblnv(a) + - JOIN (tblnw tblnw(a_1, a, d) + - JOIN JSON_TABLE( + - '[1]'::jsonb, '$[*]' AS json_table_path_0+ - COLUMNS ( + - a integer PATH '$' + - ) + - ) x(a_1) USING (a_1)) USING (a)) j2; -(1 row) - -select 'create view view_of_unrenamable_pushed_2 as ' - || pg_get_viewdef('view_of_unrenamable_pushed', true) \gexec -create view view_of_unrenamable_pushed_2 as SELECT j2.d - FROM (tblnv tblnv(a) - JOIN (tblnw tblnw(a_1, a, d) - JOIN JSON_TABLE( - '[1]'::jsonb, '$[*]' AS json_table_path_0 - COLUMNS ( - a integer PATH '$' - ) - ) x(a_1) USING (a_1)) USING (a)) j2; -select pg_get_viewdef('view_of_unrenamable_pushed', true) - = pg_get_viewdef('view_of_unrenamable_pushed_2', true) as round_trips; - round_trips -------------- - t -(1 row) - --- Two anonymous FULL JOINs merging the same name through a TABLEFUNC each --- still get distinct names. -create view view_of_unrenamable_twice as -select count(*) as n -from (tblnl full join json_table(jsonb '[1]', '$[*]' columns (x int path '$')) jt1 - using (x)), - (tblnm full join json_table(jsonb '[1]', '$[*]' columns (x int path '$')) jt2 - using (x)); -select pg_get_viewdef('view_of_unrenamable_twice', true); - pg_get_viewdef -------------------------------------------------------- - SELECT count(*) AS n + - FROM tblnl + - FULL JOIN JSON_TABLE( + - '[1]'::jsonb, '$[*]' AS json_table_path_0+ - COLUMNS ( + - x integer PATH '$' + - ) + - ) jt1 USING (x), + - tblnm tblnm(x_1) + - FULL JOIN JSON_TABLE( + - '[1]'::jsonb, '$[*]' AS json_table_path_0+ - COLUMNS ( + - x integer PATH '$' + - ) + - ) jt2(x_1) USING (x_1); -(1 row) - -select 'create view view_of_unrenamable_twice_2 as ' - || pg_get_viewdef('view_of_unrenamable_twice', true) \gexec -create view view_of_unrenamable_twice_2 as SELECT count(*) AS n - FROM tblnl - FULL JOIN JSON_TABLE( - '[1]'::jsonb, '$[*]' AS json_table_path_0 - COLUMNS ( - x integer PATH '$' - ) - ) jt1 USING (x), - tblnm tblnm(x_1) - FULL JOIN JSON_TABLE( - '[1]'::jsonb, '$[*]' AS json_table_path_0 - COLUMNS ( - x integer PATH '$' - ) - ) jt2(x_1) USING (x_1); -select pg_get_viewdef('view_of_unrenamable_twice', true) - = pg_get_viewdef('view_of_unrenamable_twice_2', true) as round_trips; - round_trips -------------- - t -(1 row) - --- and a column alias list the user wrote on a TABLEFUNC is part of the query -create view view_of_tablefunc_alias as -select t.a from json_table(jsonb '[1]', '$[*]' columns (c int path '$')) as t(a); -select pg_get_viewdef('view_of_tablefunc_alias', true); - pg_get_viewdef -------------------------------------------------------- - SELECT a + - FROM JSON_TABLE( + - '[1]'::jsonb, '$[*]' AS json_table_path_0+ - COLUMNS ( + - c integer PATH '$' + - ) + - ) t(a); -(1 row) - -select 'create view view_of_tablefunc_alias_2 as ' - || pg_get_viewdef('view_of_tablefunc_alias', true) \gexec -create view view_of_tablefunc_alias_2 as SELECT a - FROM JSON_TABLE( - '[1]'::jsonb, '$[*]' AS json_table_path_0 - COLUMNS ( - c integer PATH '$' - ) - ) t(a); -select pg_get_viewdef('view_of_tablefunc_alias', true) - = pg_get_viewdef('view_of_tablefunc_alias_2', true) as round_trips; - round_trips -------------- - t -(1 row) - -drop view view_of_tablefunc_alias_2, view_of_tablefunc_alias; -drop view view_of_unrenamable_twice_2, view_of_unrenamable_twice; -drop view view_of_unrenamable_pushed_2, view_of_unrenamable_pushed; -drop view view_of_unrenamable_hidden_2, view_of_unrenamable_hidden; -drop view view_of_unrenamable_left_2, view_of_unrenamable_left; -drop view view_of_unrenamable_right_2, view_of_unrenamable_right; -drop view view_of_unrenamable_using_2, view_of_unrenamable_using; -drop view view_of_unrenamable_2, view_of_unrenamable; -drop table tblnr, tblnu, tblnl, tblnm, tblnw, tblnv; -- The columns a function's result type grows after the view is made are -- columns the RTE has now, and the alias list being positional they are -- printed in full: an aliased join above lays its own list over its inputs' diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out index 560e89ac611..65090e6b37b 100644 --- a/src/test/regress/expected/rpr_base.out +++ b/src/test/regress/expected/rpr_base.out @@ -5127,173 +5127,6 @@ SELECT * FROM rpr_cov_merge_rt ORDER BY id; DROP VIEW rpr_cov_merge_rt, rpr_cov_merge_v; DROP TABLE rpr_cov_l, rpr_cov_r; --- A TABLEFUNC RTE writes its column names into the clause that produces them, --- but it accepts a column alias list like any other RTE, and a rename of one --- of its columns is printed there. So a TABLEFUNC column that comes to --- answer to the name a DEFINE clause reads is renamed like any other column --- would be, and the DEFINE clause keeps its spelling. -CREATE TABLE rpr_res_tf (id INT, s INT); -INSERT INTO rpr_res_tf VALUES (1, 1), (2, 2); -CREATE VIEW rpr_res_tf_v AS -SELECT count(*) OVER w AS cnt -FROM rpr_res_tf, - JSON_TABLE(jsonb '[1,2]', '$[*]' COLUMNS (c1 int PATH '$')) AS jx -WINDOW w AS (ORDER BY rpr_res_tf.id - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - PATTERN (A+) - DEFINE A AS s > 0); --- the collision arrives only now -ALTER TABLE rpr_res_tf RENAME COLUMN s TO c1; -SELECT pg_get_viewdef('rpr_res_tf_v'::regclass, true); - pg_get_viewdef ----------------------------------------------------------------------------------------- - SELECT count(*) OVER w AS cnt + - FROM rpr_res_tf, + - JSON_TABLE( + - '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 + - COLUMNS ( + - c1 integer PATH '$' + - ) + - ) jx(c1_1) + - WINDOW w AS (ORDER BY rpr_res_tf.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN (a+) + - DEFINE + - a AS c1 > 0); -(1 row) - -SELECT 'CREATE VIEW rpr_res_tf_rt AS ' - || pg_get_viewdef('rpr_res_tf_v'::regclass, true) \gexec -CREATE VIEW rpr_res_tf_rt AS SELECT count(*) OVER w AS cnt - FROM rpr_res_tf, - JSON_TABLE( - '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 - COLUMNS ( - c1 integer PATH '$' - ) - ) jx(c1_1) - WINDOW w AS (ORDER BY rpr_res_tf.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - INITIAL - PATTERN (a+) - DEFINE - a AS c1 > 0); -SELECT pg_get_viewdef('rpr_res_tf_v'::regclass, true) - = pg_get_viewdef('rpr_res_tf_rt'::regclass, true) AS round_trips; - round_trips -------------- - t -(1 row) - -SELECT * FROM rpr_res_tf_v; - cnt ------ - 4 - 0 - 0 - 0 -(4 rows) - -SELECT * FROM rpr_res_tf_rt; - cnt ------ - 4 - 0 - 0 - 0 -(4 rows) - -DROP VIEW rpr_res_tf_rt, rpr_res_tf_v; -DROP TABLE rpr_res_tf; --- An aliased join answers for its inputs and hides them, so the DEFINE --- clause here reads the join's own column x. That name is reserved across --- the query level, which reaches the TABLEFUNC column underneath as well: --- it is renamed on its own alias list, and the join prints x on its list to --- keep the name the query sees. Out of reach of an unqualified reference, --- the TABLEFUNC column would not have collided, but a reserved name is kept --- off every RTE of the level, as a globally unique USING name is. -CREATE TABLE rpr_res_hid (id INT, v INT); -CREATE TABLE rpr_res_hu (m INT); -INSERT INTO rpr_res_hid VALUES (1, 1), (2, 2), (3, 3); -INSERT INTO rpr_res_hu VALUES (9); -CREATE VIEW rpr_res_hid_v AS -SELECT count(*) OVER w AS cnt -FROM rpr_res_hid, - (JSON_TABLE(jsonb '[1,2]', '$[*]' COLUMNS (x int PATH '$')) AS jt - JOIN rpr_res_hu ON jt.x > 0) j -WINDOW w AS (ORDER BY rpr_res_hid.id - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - PATTERN (A+) - DEFINE A AS x > 0); -SELECT pg_get_viewdef('rpr_res_hid_v'::regclass, true); - pg_get_viewdef ------------------------------------------------------------------------------------------ - SELECT count(*) OVER w AS cnt + - FROM rpr_res_hid, + - (JSON_TABLE( + - '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 + - COLUMNS ( + - x integer PATH '$' + - ) + - ) jt(x_1) + - JOIN rpr_res_hu ON jt.x_1 > 0) j(x, m) + - WINDOW w AS (ORDER BY rpr_res_hid.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN (a+) + - DEFINE + - a AS x > 0); -(1 row) - -SELECT 'CREATE VIEW rpr_res_hid_rt AS ' - || pg_get_viewdef('rpr_res_hid_v'::regclass, true) \gexec -CREATE VIEW rpr_res_hid_rt AS SELECT count(*) OVER w AS cnt - FROM rpr_res_hid, - (JSON_TABLE( - '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 - COLUMNS ( - x integer PATH '$' - ) - ) jt(x_1) - JOIN rpr_res_hu ON jt.x_1 > 0) j(x, m) - WINDOW w AS (ORDER BY rpr_res_hid.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - INITIAL - PATTERN (a+) - DEFINE - a AS x > 0); -SELECT pg_get_viewdef('rpr_res_hid_v'::regclass, true) - = pg_get_viewdef('rpr_res_hid_rt'::regclass, true) AS round_trips; - round_trips -------------- - t -(1 row) - -SELECT * FROM rpr_res_hid_v; - cnt ------ - 6 - 0 - 0 - 0 - 0 - 0 -(6 rows) - -SELECT * FROM rpr_res_hid_rt; - cnt ------ - 6 - 0 - 0 - 0 - 0 - 0 -(6 rows) - -DROP VIEW rpr_res_hid_rt, rpr_res_hid_v; -DROP TABLE rpr_res_hid, rpr_res_hu; -- A column can come to carry the name a DEFINE clause reads only after the -- view is made, by being renamed, and from then on the two have to be told -- apart in the printed text. The DEFINE column keeps its spelling, being @@ -5619,284 +5452,6 @@ DROP VIEW rpr_res_ren_rt, rpr_res_ren_v; DROP TABLE rpr_res_ren, rpr_res_ren2, rpr_res_renl, rpr_res_renr; DROP TABLE rpr_res_rs, rpr_res_rr; DROP TABLE rpr_res_rena, rpr_res_renb; --- A merged column an aliased join carries can spell the DEFINE name from the --- start, and a TABLEFUNC among the join's inputs changes nothing about that: --- the join's own alias list names the merged column, the inputs answer to --- it, and the DEFINE column is left alone. Nor does the order of the FROM --- list matter. -CREATE TABLE rpr_res_tj (id INT, c1 INT); -INSERT INTO rpr_res_tj VALUES (1, 1), (2, 2); -CREATE TABLE rpr_res_tb (c1 INT, y INT); -INSERT INTO rpr_res_tb VALUES (1, 10), (2, 20); -CREATE TABLE rpr_res_ti (id INT); -INSERT INTO rpr_res_ti VALUES (1), (2); -CREATE VIEW rpr_res_tj_v AS -SELECT count(*) OVER w AS cnt -FROM rpr_res_tj, - (JSON_TABLE(jsonb '[1,2]', '$[*]' COLUMNS (c1 int PATH '$')) AS jt - JOIN rpr_res_tb USING (c1)) AS j(k, m) -WINDOW w AS (ORDER BY rpr_res_tj.id - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - PATTERN (A+) - DEFINE A AS c1 > 0); -CREATE VIEW rpr_res_tjr_v AS -SELECT count(*) OVER w AS cnt -FROM (JSON_TABLE(jsonb '[1,2]', '$[*]' COLUMNS (c1 int PATH '$')) AS jt - JOIN rpr_res_tb USING (c1)) AS j(k, m), - rpr_res_tj -WINDOW w AS (ORDER BY rpr_res_tj.id - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - PATTERN (A+) - DEFINE A AS c1 > 0); --- the join's alias list puts the USING spelling on a column it does not --- merge, and that column is the one the DEFINE clause reads -CREATE VIEW rpr_res_tja_v AS -SELECT count(*) OVER w AS cnt -FROM rpr_res_ti, - (JSON_TABLE(jsonb '[1,2]', '$[*]' COLUMNS (c1 int PATH '$')) AS jt - JOIN rpr_res_tb USING (c1)) AS j(k, c1) -WINDOW w AS (ORDER BY rpr_res_ti.id - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - PATTERN (A+) - DEFINE A AS c1 > 0); --- the same with the TABLEFUNC on the right -CREATE VIEW rpr_res_tjb_v AS -SELECT count(*) OVER w AS cnt -FROM (rpr_res_tb JOIN JSON_TABLE(jsonb '[1,2]', '$[*]' COLUMNS (c1 int PATH '$')) AS jt - USING (c1)) AS j(a, c1) -WINDOW w AS (ORDER BY j.a - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - PATTERN (A+) - DEFINE A AS c1 > 0); -SELECT pg_get_viewdef('rpr_res_tj_v'::regclass, true); - pg_get_viewdef ----------------------------------------------------------------------------------------- - SELECT count(*) OVER w AS cnt + - FROM rpr_res_tj, + - (JSON_TABLE( + - '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 + - COLUMNS ( + - c1 integer PATH '$' + - ) + - ) jt(k) + - JOIN rpr_res_tb rpr_res_tb(k, y) USING (k)) j(k, m) + - WINDOW w AS (ORDER BY rpr_res_tj.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN (a+) + - DEFINE + - a AS c1 > 0); -(1 row) - -SELECT 'CREATE VIEW rpr_res_tj_rt AS ' - || pg_get_viewdef('rpr_res_tj_v'::regclass, true) \gexec -CREATE VIEW rpr_res_tj_rt AS SELECT count(*) OVER w AS cnt - FROM rpr_res_tj, - (JSON_TABLE( - '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 - COLUMNS ( - c1 integer PATH '$' - ) - ) jt(k) - JOIN rpr_res_tb rpr_res_tb(k, y) USING (k)) j(k, m) - WINDOW w AS (ORDER BY rpr_res_tj.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - INITIAL - PATTERN (a+) - DEFINE - a AS c1 > 0); -SELECT pg_get_viewdef('rpr_res_tj_v'::regclass, true) - = pg_get_viewdef('rpr_res_tj_rt'::regclass, true) AS round_trips; - round_trips -------------- - t -(1 row) - -SELECT * FROM rpr_res_tj_v; - cnt ------ - 4 - 0 - 0 - 0 -(4 rows) - -SELECT * FROM rpr_res_tj_rt; - cnt ------ - 4 - 0 - 0 - 0 -(4 rows) - -SELECT pg_get_viewdef('rpr_res_tjr_v'::regclass, true); - pg_get_viewdef ----------------------------------------------------------------------------------------- - SELECT count(*) OVER w AS cnt + - FROM (JSON_TABLE( + - '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 + - COLUMNS ( + - c1 integer PATH '$' + - ) + - ) jt(k) + - JOIN rpr_res_tb rpr_res_tb(k, y) USING (k)) j(k, m), + - rpr_res_tj + - WINDOW w AS (ORDER BY rpr_res_tj.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN (a+) + - DEFINE + - a AS c1 > 0); -(1 row) - -SELECT 'CREATE VIEW rpr_res_tjr_rt AS ' - || pg_get_viewdef('rpr_res_tjr_v'::regclass, true) \gexec -CREATE VIEW rpr_res_tjr_rt AS SELECT count(*) OVER w AS cnt - FROM (JSON_TABLE( - '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 - COLUMNS ( - c1 integer PATH '$' - ) - ) jt(k) - JOIN rpr_res_tb rpr_res_tb(k, y) USING (k)) j(k, m), - rpr_res_tj - WINDOW w AS (ORDER BY rpr_res_tj.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - INITIAL - PATTERN (a+) - DEFINE - a AS c1 > 0); -SELECT pg_get_viewdef('rpr_res_tjr_v'::regclass, true) - = pg_get_viewdef('rpr_res_tjr_rt'::regclass, true) AS round_trips; - round_trips -------------- - t -(1 row) - -SELECT pg_get_viewdef('rpr_res_tja_v'::regclass, true); - pg_get_viewdef ----------------------------------------------------------------------------------------- - SELECT count(*) OVER w AS cnt + - FROM rpr_res_ti, + - (JSON_TABLE( + - '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 + - COLUMNS ( + - c1 integer PATH '$' + - ) + - ) jt(k) + - JOIN rpr_res_tb rpr_res_tb(k, y) USING (k)) j(k, c1) + - WINDOW w AS (ORDER BY rpr_res_ti.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN (a+) + - DEFINE + - a AS c1 > 0); -(1 row) - -SELECT 'CREATE VIEW rpr_res_tja_rt AS ' - || pg_get_viewdef('rpr_res_tja_v'::regclass, true) \gexec -CREATE VIEW rpr_res_tja_rt AS SELECT count(*) OVER w AS cnt - FROM rpr_res_ti, - (JSON_TABLE( - '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 - COLUMNS ( - c1 integer PATH '$' - ) - ) jt(k) - JOIN rpr_res_tb rpr_res_tb(k, y) USING (k)) j(k, c1) - WINDOW w AS (ORDER BY rpr_res_ti.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - INITIAL - PATTERN (a+) - DEFINE - a AS c1 > 0); -SELECT pg_get_viewdef('rpr_res_tja_v'::regclass, true) - = pg_get_viewdef('rpr_res_tja_rt'::regclass, true) AS round_trips; - round_trips -------------- - t -(1 row) - -SELECT * FROM rpr_res_tja_v; - cnt ------ - 4 - 0 - 0 - 0 -(4 rows) - -SELECT * FROM rpr_res_tja_rt; - cnt ------ - 4 - 0 - 0 - 0 -(4 rows) - -SELECT pg_get_viewdef('rpr_res_tjb_v'::regclass, true); - pg_get_viewdef ------------------------------------------------------------------------------- - SELECT count(*) OVER w AS cnt + - FROM (rpr_res_tb rpr_res_tb(a, y) + - JOIN JSON_TABLE( + - '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 + - COLUMNS ( + - c1 integer PATH '$' + - ) + - ) jt(a) USING (a)) j(a, c1) + - WINDOW w AS (ORDER BY j.a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN (a+) + - DEFINE + - a AS c1 > 0); -(1 row) - -SELECT 'CREATE VIEW rpr_res_tjb_rt AS ' - || pg_get_viewdef('rpr_res_tjb_v'::regclass, true) \gexec -CREATE VIEW rpr_res_tjb_rt AS SELECT count(*) OVER w AS cnt - FROM (rpr_res_tb rpr_res_tb(a, y) - JOIN JSON_TABLE( - '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 - COLUMNS ( - c1 integer PATH '$' - ) - ) jt(a) USING (a)) j(a, c1) - WINDOW w AS (ORDER BY j.a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - INITIAL - PATTERN (a+) - DEFINE - a AS c1 > 0); -SELECT pg_get_viewdef('rpr_res_tjb_v'::regclass, true) - = pg_get_viewdef('rpr_res_tjb_rt'::regclass, true) AS round_trips; - round_trips -------------- - t -(1 row) - -SELECT * FROM rpr_res_tjb_v; - cnt ------ - 2 - 0 -(2 rows) - -SELECT * FROM rpr_res_tjb_rt; - cnt ------ - 2 - 0 -(2 rows) - -DROP VIEW rpr_res_tjb_rt, rpr_res_tjb_v; -DROP VIEW rpr_res_tja_rt, rpr_res_tja_v; -DROP VIEW rpr_res_tjr_rt, rpr_res_tjr_v; -DROP VIEW rpr_res_tj_rt, rpr_res_tj_v; -DROP TABLE rpr_res_tj, rpr_res_tb, rpr_res_ti; -- The name a USING clause is given for an aliased join has to stay clear of -- the names the join's alias list already carries, and of what a DEFINE -- clause reads among them. The anonymous FULL JOIN takes the plain spelling @@ -5968,236 +5523,6 @@ SELECT * FROM rpr_res_fa_rt; DROP VIEW rpr_res_fa_rt, rpr_res_fa_v; DROP TABLE rpr_res_fa, rpr_res_fb, rpr_res_fc; --- A TABLEFUNC that merges through an aliased join, or that carries a column --- alias list of its own, is no different: when a relation column is renamed --- onto the TABLEFUNC's name, it is the TABLEFUNC column that moves, and a --- third RTE that already spells the name it moves to is left alone, that --- name being read nowhere unqualified. -CREATE TABLE rpr_res_tk (id INT, y INT); -INSERT INTO rpr_res_tk VALUES (1, 1), (2, 2); -CREATE TABLE rpr_res_th (x INT, z INT); -INSERT INTO rpr_res_th VALUES (1, 1), (2, 2); -CREATE TABLE rpr_res_ta (id INT, c1 INT); -INSERT INTO rpr_res_ta VALUES (1, 1), (2, 2); -CREATE TABLE rpr_res_ts (id INT, s INT); -INSERT INTO rpr_res_ts VALUES (1, 1), (2, 2); -CREATE TABLE rpr_res_to (c1_1 INT); -INSERT INTO rpr_res_to VALUES (9); -CREATE VIEW rpr_res_tk_v AS -SELECT count(*) OVER w AS cnt -FROM rpr_res_tk, - (JSON_TABLE(jsonb '[1,2]', '$[*]' COLUMNS (x int PATH '$')) AS jt - JOIN rpr_res_th USING (x)) j -WINDOW w AS (ORDER BY rpr_res_tk.id - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - PATTERN (A+) - DEFINE A AS y > 0); -CREATE VIEW rpr_res_ta_v AS -SELECT count(*) OVER w AS cnt -FROM rpr_res_ta, - JSON_TABLE(jsonb '[1,2]', '$[*]' COLUMNS (c1 int PATH '$')) AS jx(a) -WINDOW w AS (ORDER BY rpr_res_ta.id - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - PATTERN (A+) - DEFINE A AS c1 > 0); -CREATE VIEW rpr_res_ts_v AS -SELECT count(*) OVER w AS cnt -FROM rpr_res_ts, rpr_res_to, - JSON_TABLE(jsonb '[1,2]', '$[*]' COLUMNS (c1 int PATH '$')) AS jx -WINDOW w AS (ORDER BY rpr_res_ts.id - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - PATTERN (A+) - DEFINE A AS s > 0); -ALTER TABLE rpr_res_tk RENAME y TO x; -ALTER TABLE rpr_res_ts RENAME s TO c1; -SELECT pg_get_viewdef('rpr_res_tk_v'::regclass, true); - pg_get_viewdef ----------------------------------------------------------------------------------------- - SELECT count(*) OVER w AS cnt + - FROM rpr_res_tk, + - (JSON_TABLE( + - '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 + - COLUMNS ( + - x integer PATH '$' + - ) + - ) jt(x_1) + - JOIN rpr_res_th rpr_res_th(x_1, z) USING (x_1)) j + - WINDOW w AS (ORDER BY rpr_res_tk.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN (a+) + - DEFINE + - a AS x > 0); -(1 row) - -SELECT 'CREATE VIEW rpr_res_tk_rt AS ' - || pg_get_viewdef('rpr_res_tk_v'::regclass, true) \gexec -CREATE VIEW rpr_res_tk_rt AS SELECT count(*) OVER w AS cnt - FROM rpr_res_tk, - (JSON_TABLE( - '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 - COLUMNS ( - x integer PATH '$' - ) - ) jt(x_1) - JOIN rpr_res_th rpr_res_th(x_1, z) USING (x_1)) j - WINDOW w AS (ORDER BY rpr_res_tk.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - INITIAL - PATTERN (a+) - DEFINE - a AS x > 0); -SELECT pg_get_viewdef('rpr_res_tk_v'::regclass, true) - = pg_get_viewdef('rpr_res_tk_rt'::regclass, true) AS round_trips; - round_trips -------------- - t -(1 row) - -SELECT * FROM rpr_res_tk_v; - cnt ------ - 4 - 0 - 0 - 0 -(4 rows) - -SELECT * FROM rpr_res_tk_rt; - cnt ------ - 4 - 0 - 0 - 0 -(4 rows) - -SELECT pg_get_viewdef('rpr_res_ta_v'::regclass, true); - pg_get_viewdef ----------------------------------------------------------------------------------------- - SELECT count(*) OVER w AS cnt + - FROM rpr_res_ta, + - JSON_TABLE( + - '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 + - COLUMNS ( + - c1 integer PATH '$' + - ) + - ) jx(a) + - WINDOW w AS (ORDER BY rpr_res_ta.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN (a+) + - DEFINE + - a AS c1 > 0); -(1 row) - -SELECT 'CREATE VIEW rpr_res_ta_rt AS ' - || pg_get_viewdef('rpr_res_ta_v'::regclass, true) \gexec -CREATE VIEW rpr_res_ta_rt AS SELECT count(*) OVER w AS cnt - FROM rpr_res_ta, - JSON_TABLE( - '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 - COLUMNS ( - c1 integer PATH '$' - ) - ) jx(a) - WINDOW w AS (ORDER BY rpr_res_ta.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - INITIAL - PATTERN (a+) - DEFINE - a AS c1 > 0); -SELECT pg_get_viewdef('rpr_res_ta_v'::regclass, true) - = pg_get_viewdef('rpr_res_ta_rt'::regclass, true) AS round_trips; - round_trips -------------- - t -(1 row) - -SELECT * FROM rpr_res_ta_v; - cnt ------ - 4 - 0 - 0 - 0 -(4 rows) - -SELECT * FROM rpr_res_ta_rt; - cnt ------ - 4 - 0 - 0 - 0 -(4 rows) - -SELECT pg_get_viewdef('rpr_res_ts_v'::regclass, true); - pg_get_viewdef ----------------------------------------------------------------------------------------- - SELECT count(*) OVER w AS cnt + - FROM rpr_res_ts, + - rpr_res_to, + - JSON_TABLE( + - '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 + - COLUMNS ( + - c1 integer PATH '$' + - ) + - ) jx(c1_1) + - WINDOW w AS (ORDER BY rpr_res_ts.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+ - AFTER MATCH SKIP PAST LAST ROW + - INITIAL + - PATTERN (a+) + - DEFINE + - a AS c1 > 0); -(1 row) - -SELECT 'CREATE VIEW rpr_res_ts_rt AS ' - || pg_get_viewdef('rpr_res_ts_v'::regclass, true) \gexec -CREATE VIEW rpr_res_ts_rt AS SELECT count(*) OVER w AS cnt - FROM rpr_res_ts, - rpr_res_to, - JSON_TABLE( - '[1, 2]'::jsonb, '$[*]' AS json_table_path_0 - COLUMNS ( - c1 integer PATH '$' - ) - ) jx(c1_1) - WINDOW w AS (ORDER BY rpr_res_ts.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - AFTER MATCH SKIP PAST LAST ROW - INITIAL - PATTERN (a+) - DEFINE - a AS c1 > 0); -SELECT pg_get_viewdef('rpr_res_ts_v'::regclass, true) - = pg_get_viewdef('rpr_res_ts_rt'::regclass, true) AS round_trips; - round_trips -------------- - t -(1 row) - -SELECT * FROM rpr_res_ts_v; - cnt ------ - 4 - 0 - 0 - 0 -(4 rows) - -SELECT * FROM rpr_res_ts_rt; - cnt ------ - 4 - 0 - 0 - 0 -(4 rows) - -DROP VIEW rpr_res_ts_rt, rpr_res_ts_v; -DROP VIEW rpr_res_ta_rt, rpr_res_ta_v; -DROP VIEW rpr_res_tk_rt, rpr_res_tk_v; -DROP TABLE rpr_res_tk, rpr_res_th, rpr_res_ta, rpr_res_ts, rpr_res_to; -- A merged column the DEFINE clause reads is named by the DEFINE clause, not -- by the USING clause: the name settled for it is the one the merge adopts. -- So a join whose alias list renames the merged column prints the same text diff --git a/src/test/regress/sql/create_view.sql b/src/test/regress/sql/create_view.sql index 3c3e93a32c7..72579c0b645 100644 --- a/src/test/regress/sql/create_view.sql +++ b/src/test/regress/sql/create_view.sql @@ -409,140 +409,6 @@ select pg_get_viewdef('view_of_joins_2b', true); select pg_get_viewdef('view_of_joins_2c', true); select pg_get_viewdef('view_of_joins_2d', true); --- A TABLEFUNC RTE names its columns in the clause that produces them, but it --- accepts a column alias list like any other RTE, and that list is where a --- rename of one of its columns has to be printed: the ON clause below refers --- to the column by the name the query sees, and without the list the two --- would not agree. The anonymous FULL JOIN is what forces USING names to be --- unique query-wide, which is what pushes the JSON_TABLE column aside. -create table tblnr (m int); -create table tblnu (x int, m int); -create table tblnl (x int); -create table tblnm (x int); -create table tblnw (a int, c int, d int); -create table tblnv (c int); - -create view view_of_unrenamable as -select j.m -from (tblnl full join tblnm using (x)), - (json_table(jsonb '[1,2]', '$[*]' columns (x int path '$')) as jt - join tblnr on jt.x > 0) j; - -select pg_get_viewdef('view_of_unrenamable', true); - --- and that text is what has to reparse -select 'create view view_of_unrenamable_2 as ' - || pg_get_viewdef('view_of_unrenamable', true) \gexec -select pg_get_viewdef('view_of_unrenamable', true) - = pg_get_viewdef('view_of_unrenamable_2', true) as round_trips; - --- The name of a merged column is the other place a rename lands, and it lands --- on both sides at once: whatever is picked, each input has to answer to it, --- the TABLEFUNC through its alias list like the table through its own. -create view view_of_unrenamable_using as -select j.m -from (tblnl full join tblnm using (x)), - (json_table(jsonb '[1,2]', '$[*]' columns (x int path '$')) as jt - join tblnu using (x)) j; - -select pg_get_viewdef('view_of_unrenamable_using', true); - -select 'create view view_of_unrenamable_using_2 as ' - || pg_get_viewdef('view_of_unrenamable_using', true) \gexec -select pg_get_viewdef('view_of_unrenamable_using', true) - = pg_get_viewdef('view_of_unrenamable_using_2', true) as round_trips; - --- The far side of an INNER or LEFT JOIN is as much a side as the near one: --- the merged column's expression names only the near input, but the rename --- reaches both. -create view view_of_unrenamable_right as -select j.m -from (tblnl full join tblnm using (x)), - ((tblnu join json_table(jsonb '[1,2]', '$[*]' columns (x int path '$')) as jt - using (x)) full join tblnm t2 using (x)) j; - -select pg_get_viewdef('view_of_unrenamable_right', true); -select 'create view view_of_unrenamable_right_2 as ' - || pg_get_viewdef('view_of_unrenamable_right', true) \gexec -select pg_get_viewdef('view_of_unrenamable_right', true) - = pg_get_viewdef('view_of_unrenamable_right_2', true) as round_trips; - -create view view_of_unrenamable_left as -select j.m -from (tblnl full join tblnm using (x)), - ((tblnu left join json_table(jsonb '[1,2]', '$[*]' columns (x int path '$')) as jt - using (x)) full join tblnm t2 using (x)) j; - -select pg_get_viewdef('view_of_unrenamable_left', true); -select 'create view view_of_unrenamable_left_2 as ' - || pg_get_viewdef('view_of_unrenamable_left', true) \gexec -select pg_get_viewdef('view_of_unrenamable_left', true) - = pg_get_viewdef('view_of_unrenamable_left_2', true) as round_trips; - --- An aliased join hides its inputs, and a USING name above it is taken by --- the join's own column: the join renames it on its alias list, and the --- TABLEFUNC underneath, being held to the query-wide USING names as well, --- renames its column on its own list. -create view view_of_unrenamable_hidden as -select j.m -from (tblnl full join tblnm using (x)), - ((json_table(jsonb '[1,2]', '$[*]' columns (x int path '$')) as jt - join tblnr on true) j full join tblnm t2 using (x)); - -select pg_get_viewdef('view_of_unrenamable_hidden', true); -select 'create view view_of_unrenamable_hidden_2 as ' - || pg_get_viewdef('view_of_unrenamable_hidden', true) \gexec -select pg_get_viewdef('view_of_unrenamable_hidden', true) - = pg_get_viewdef('view_of_unrenamable_hidden_2', true) as round_trips; - --- A name a parent join pushes down onto a merged column has to be unique in --- the input it lands in, whatever the USING clause underneath spells. -create view view_of_unrenamable_pushed as -select j2.d -from (tblnv join (tblnw join json_table(jsonb '[1]', '$[*]' columns (a int path '$')) x - using (a)) using (c)) as j2(a); - -select pg_get_viewdef('view_of_unrenamable_pushed', true); -select 'create view view_of_unrenamable_pushed_2 as ' - || pg_get_viewdef('view_of_unrenamable_pushed', true) \gexec -select pg_get_viewdef('view_of_unrenamable_pushed', true) - = pg_get_viewdef('view_of_unrenamable_pushed_2', true) as round_trips; - --- Two anonymous FULL JOINs merging the same name through a TABLEFUNC each --- still get distinct names. -create view view_of_unrenamable_twice as -select count(*) as n -from (tblnl full join json_table(jsonb '[1]', '$[*]' columns (x int path '$')) jt1 - using (x)), - (tblnm full join json_table(jsonb '[1]', '$[*]' columns (x int path '$')) jt2 - using (x)); - -select pg_get_viewdef('view_of_unrenamable_twice', true); -select 'create view view_of_unrenamable_twice_2 as ' - || pg_get_viewdef('view_of_unrenamable_twice', true) \gexec -select pg_get_viewdef('view_of_unrenamable_twice', true) - = pg_get_viewdef('view_of_unrenamable_twice_2', true) as round_trips; - --- and a column alias list the user wrote on a TABLEFUNC is part of the query -create view view_of_tablefunc_alias as -select t.a from json_table(jsonb '[1]', '$[*]' columns (c int path '$')) as t(a); - -select pg_get_viewdef('view_of_tablefunc_alias', true); -select 'create view view_of_tablefunc_alias_2 as ' - || pg_get_viewdef('view_of_tablefunc_alias', true) \gexec -select pg_get_viewdef('view_of_tablefunc_alias', true) - = pg_get_viewdef('view_of_tablefunc_alias_2', true) as round_trips; - -drop view view_of_tablefunc_alias_2, view_of_tablefunc_alias; -drop view view_of_unrenamable_twice_2, view_of_unrenamable_twice; -drop view view_of_unrenamable_pushed_2, view_of_unrenamable_pushed; -drop view view_of_unrenamable_hidden_2, view_of_unrenamable_hidden; -drop view view_of_unrenamable_left_2, view_of_unrenamable_left; -drop view view_of_unrenamable_right_2, view_of_unrenamable_right; -drop view view_of_unrenamable_using_2, view_of_unrenamable_using; -drop view view_of_unrenamable_2, view_of_unrenamable; -drop table tblnr, tblnu, tblnl, tblnm, tblnw, tblnv; - -- The columns a function's result type grows after the view is made are -- columns the RTE has now, and the alias list being positional they are -- printed in full: an aliased join above lays its own list over its inputs' diff --git a/src/test/regress/sql/rpr_base.sql b/src/test/regress/sql/rpr_base.sql index e60c2c46d28..c23e4cf571d 100644 --- a/src/test/regress/sql/rpr_base.sql +++ b/src/test/regress/sql/rpr_base.sql @@ -3108,70 +3108,6 @@ SELECT * FROM rpr_cov_merge_rt ORDER BY id; DROP VIEW rpr_cov_merge_rt, rpr_cov_merge_v; DROP TABLE rpr_cov_l, rpr_cov_r; --- A TABLEFUNC RTE writes its column names into the clause that produces them, --- but it accepts a column alias list like any other RTE, and a rename of one --- of its columns is printed there. So a TABLEFUNC column that comes to --- answer to the name a DEFINE clause reads is renamed like any other column --- would be, and the DEFINE clause keeps its spelling. -CREATE TABLE rpr_res_tf (id INT, s INT); -INSERT INTO rpr_res_tf VALUES (1, 1), (2, 2); - -CREATE VIEW rpr_res_tf_v AS -SELECT count(*) OVER w AS cnt -FROM rpr_res_tf, - JSON_TABLE(jsonb '[1,2]', '$[*]' COLUMNS (c1 int PATH '$')) AS jx -WINDOW w AS (ORDER BY rpr_res_tf.id - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - PATTERN (A+) - DEFINE A AS s > 0); - --- the collision arrives only now -ALTER TABLE rpr_res_tf RENAME COLUMN s TO c1; - -SELECT pg_get_viewdef('rpr_res_tf_v'::regclass, true); -SELECT 'CREATE VIEW rpr_res_tf_rt AS ' - || pg_get_viewdef('rpr_res_tf_v'::regclass, true) \gexec -SELECT pg_get_viewdef('rpr_res_tf_v'::regclass, true) - = pg_get_viewdef('rpr_res_tf_rt'::regclass, true) AS round_trips; -SELECT * FROM rpr_res_tf_v; -SELECT * FROM rpr_res_tf_rt; - -DROP VIEW rpr_res_tf_rt, rpr_res_tf_v; -DROP TABLE rpr_res_tf; - --- An aliased join answers for its inputs and hides them, so the DEFINE --- clause here reads the join's own column x. That name is reserved across --- the query level, which reaches the TABLEFUNC column underneath as well: --- it is renamed on its own alias list, and the join prints x on its list to --- keep the name the query sees. Out of reach of an unqualified reference, --- the TABLEFUNC column would not have collided, but a reserved name is kept --- off every RTE of the level, as a globally unique USING name is. -CREATE TABLE rpr_res_hid (id INT, v INT); -CREATE TABLE rpr_res_hu (m INT); -INSERT INTO rpr_res_hid VALUES (1, 1), (2, 2), (3, 3); -INSERT INTO rpr_res_hu VALUES (9); - -CREATE VIEW rpr_res_hid_v AS -SELECT count(*) OVER w AS cnt -FROM rpr_res_hid, - (JSON_TABLE(jsonb '[1,2]', '$[*]' COLUMNS (x int PATH '$')) AS jt - JOIN rpr_res_hu ON jt.x > 0) j -WINDOW w AS (ORDER BY rpr_res_hid.id - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - PATTERN (A+) - DEFINE A AS x > 0); - -SELECT pg_get_viewdef('rpr_res_hid_v'::regclass, true); -SELECT 'CREATE VIEW rpr_res_hid_rt AS ' - || pg_get_viewdef('rpr_res_hid_v'::regclass, true) \gexec -SELECT pg_get_viewdef('rpr_res_hid_v'::regclass, true) - = pg_get_viewdef('rpr_res_hid_rt'::regclass, true) AS round_trips; -SELECT * FROM rpr_res_hid_v; -SELECT * FROM rpr_res_hid_rt; - -DROP VIEW rpr_res_hid_rt, rpr_res_hid_v; -DROP TABLE rpr_res_hid, rpr_res_hu; - -- A column can come to carry the name a DEFINE clause reads only after the -- view is made, by being renamed, and from then on the two have to be told -- apart in the printed text. The DEFINE column keeps its spelling, being @@ -3299,96 +3235,6 @@ DROP TABLE rpr_res_ren, rpr_res_ren2, rpr_res_renl, rpr_res_renr; DROP TABLE rpr_res_rs, rpr_res_rr; DROP TABLE rpr_res_rena, rpr_res_renb; --- A merged column an aliased join carries can spell the DEFINE name from the --- start, and a TABLEFUNC among the join's inputs changes nothing about that: --- the join's own alias list names the merged column, the inputs answer to --- it, and the DEFINE column is left alone. Nor does the order of the FROM --- list matter. -CREATE TABLE rpr_res_tj (id INT, c1 INT); -INSERT INTO rpr_res_tj VALUES (1, 1), (2, 2); -CREATE TABLE rpr_res_tb (c1 INT, y INT); -INSERT INTO rpr_res_tb VALUES (1, 10), (2, 20); -CREATE TABLE rpr_res_ti (id INT); -INSERT INTO rpr_res_ti VALUES (1), (2); - -CREATE VIEW rpr_res_tj_v AS -SELECT count(*) OVER w AS cnt -FROM rpr_res_tj, - (JSON_TABLE(jsonb '[1,2]', '$[*]' COLUMNS (c1 int PATH '$')) AS jt - JOIN rpr_res_tb USING (c1)) AS j(k, m) -WINDOW w AS (ORDER BY rpr_res_tj.id - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - PATTERN (A+) - DEFINE A AS c1 > 0); - -CREATE VIEW rpr_res_tjr_v AS -SELECT count(*) OVER w AS cnt -FROM (JSON_TABLE(jsonb '[1,2]', '$[*]' COLUMNS (c1 int PATH '$')) AS jt - JOIN rpr_res_tb USING (c1)) AS j(k, m), - rpr_res_tj -WINDOW w AS (ORDER BY rpr_res_tj.id - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - PATTERN (A+) - DEFINE A AS c1 > 0); - --- the join's alias list puts the USING spelling on a column it does not --- merge, and that column is the one the DEFINE clause reads -CREATE VIEW rpr_res_tja_v AS -SELECT count(*) OVER w AS cnt -FROM rpr_res_ti, - (JSON_TABLE(jsonb '[1,2]', '$[*]' COLUMNS (c1 int PATH '$')) AS jt - JOIN rpr_res_tb USING (c1)) AS j(k, c1) -WINDOW w AS (ORDER BY rpr_res_ti.id - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - PATTERN (A+) - DEFINE A AS c1 > 0); - --- the same with the TABLEFUNC on the right -CREATE VIEW rpr_res_tjb_v AS -SELECT count(*) OVER w AS cnt -FROM (rpr_res_tb JOIN JSON_TABLE(jsonb '[1,2]', '$[*]' COLUMNS (c1 int PATH '$')) AS jt - USING (c1)) AS j(a, c1) -WINDOW w AS (ORDER BY j.a - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - PATTERN (A+) - DEFINE A AS c1 > 0); - -SELECT pg_get_viewdef('rpr_res_tj_v'::regclass, true); -SELECT 'CREATE VIEW rpr_res_tj_rt AS ' - || pg_get_viewdef('rpr_res_tj_v'::regclass, true) \gexec -SELECT pg_get_viewdef('rpr_res_tj_v'::regclass, true) - = pg_get_viewdef('rpr_res_tj_rt'::regclass, true) AS round_trips; -SELECT * FROM rpr_res_tj_v; -SELECT * FROM rpr_res_tj_rt; - -SELECT pg_get_viewdef('rpr_res_tjr_v'::regclass, true); -SELECT 'CREATE VIEW rpr_res_tjr_rt AS ' - || pg_get_viewdef('rpr_res_tjr_v'::regclass, true) \gexec -SELECT pg_get_viewdef('rpr_res_tjr_v'::regclass, true) - = pg_get_viewdef('rpr_res_tjr_rt'::regclass, true) AS round_trips; - -SELECT pg_get_viewdef('rpr_res_tja_v'::regclass, true); -SELECT 'CREATE VIEW rpr_res_tja_rt AS ' - || pg_get_viewdef('rpr_res_tja_v'::regclass, true) \gexec -SELECT pg_get_viewdef('rpr_res_tja_v'::regclass, true) - = pg_get_viewdef('rpr_res_tja_rt'::regclass, true) AS round_trips; -SELECT * FROM rpr_res_tja_v; -SELECT * FROM rpr_res_tja_rt; - -SELECT pg_get_viewdef('rpr_res_tjb_v'::regclass, true); -SELECT 'CREATE VIEW rpr_res_tjb_rt AS ' - || pg_get_viewdef('rpr_res_tjb_v'::regclass, true) \gexec -SELECT pg_get_viewdef('rpr_res_tjb_v'::regclass, true) - = pg_get_viewdef('rpr_res_tjb_rt'::regclass, true) AS round_trips; -SELECT * FROM rpr_res_tjb_v; -SELECT * FROM rpr_res_tjb_rt; - -DROP VIEW rpr_res_tjb_rt, rpr_res_tjb_v; -DROP VIEW rpr_res_tja_rt, rpr_res_tja_v; -DROP VIEW rpr_res_tjr_rt, rpr_res_tjr_v; -DROP VIEW rpr_res_tj_rt, rpr_res_tj_v; -DROP TABLE rpr_res_tj, rpr_res_tb, rpr_res_ti; - -- The name a USING clause is given for an aliased join has to stay clear of -- the names the join's alias list already carries, and of what a DEFINE -- clause reads among them. The anonymous FULL JOIN takes the plain spelling @@ -3421,82 +3267,6 @@ SELECT * FROM rpr_res_fa_rt; DROP VIEW rpr_res_fa_rt, rpr_res_fa_v; DROP TABLE rpr_res_fa, rpr_res_fb, rpr_res_fc; --- A TABLEFUNC that merges through an aliased join, or that carries a column --- alias list of its own, is no different: when a relation column is renamed --- onto the TABLEFUNC's name, it is the TABLEFUNC column that moves, and a --- third RTE that already spells the name it moves to is left alone, that --- name being read nowhere unqualified. -CREATE TABLE rpr_res_tk (id INT, y INT); -INSERT INTO rpr_res_tk VALUES (1, 1), (2, 2); -CREATE TABLE rpr_res_th (x INT, z INT); -INSERT INTO rpr_res_th VALUES (1, 1), (2, 2); -CREATE TABLE rpr_res_ta (id INT, c1 INT); -INSERT INTO rpr_res_ta VALUES (1, 1), (2, 2); -CREATE TABLE rpr_res_ts (id INT, s INT); -INSERT INTO rpr_res_ts VALUES (1, 1), (2, 2); -CREATE TABLE rpr_res_to (c1_1 INT); -INSERT INTO rpr_res_to VALUES (9); - -CREATE VIEW rpr_res_tk_v AS -SELECT count(*) OVER w AS cnt -FROM rpr_res_tk, - (JSON_TABLE(jsonb '[1,2]', '$[*]' COLUMNS (x int PATH '$')) AS jt - JOIN rpr_res_th USING (x)) j -WINDOW w AS (ORDER BY rpr_res_tk.id - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - PATTERN (A+) - DEFINE A AS y > 0); - -CREATE VIEW rpr_res_ta_v AS -SELECT count(*) OVER w AS cnt -FROM rpr_res_ta, - JSON_TABLE(jsonb '[1,2]', '$[*]' COLUMNS (c1 int PATH '$')) AS jx(a) -WINDOW w AS (ORDER BY rpr_res_ta.id - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - PATTERN (A+) - DEFINE A AS c1 > 0); - -CREATE VIEW rpr_res_ts_v AS -SELECT count(*) OVER w AS cnt -FROM rpr_res_ts, rpr_res_to, - JSON_TABLE(jsonb '[1,2]', '$[*]' COLUMNS (c1 int PATH '$')) AS jx -WINDOW w AS (ORDER BY rpr_res_ts.id - ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING - PATTERN (A+) - DEFINE A AS s > 0); - -ALTER TABLE rpr_res_tk RENAME y TO x; -ALTER TABLE rpr_res_ts RENAME s TO c1; - -SELECT pg_get_viewdef('rpr_res_tk_v'::regclass, true); -SELECT 'CREATE VIEW rpr_res_tk_rt AS ' - || pg_get_viewdef('rpr_res_tk_v'::regclass, true) \gexec -SELECT pg_get_viewdef('rpr_res_tk_v'::regclass, true) - = pg_get_viewdef('rpr_res_tk_rt'::regclass, true) AS round_trips; -SELECT * FROM rpr_res_tk_v; -SELECT * FROM rpr_res_tk_rt; - -SELECT pg_get_viewdef('rpr_res_ta_v'::regclass, true); -SELECT 'CREATE VIEW rpr_res_ta_rt AS ' - || pg_get_viewdef('rpr_res_ta_v'::regclass, true) \gexec -SELECT pg_get_viewdef('rpr_res_ta_v'::regclass, true) - = pg_get_viewdef('rpr_res_ta_rt'::regclass, true) AS round_trips; -SELECT * FROM rpr_res_ta_v; -SELECT * FROM rpr_res_ta_rt; - -SELECT pg_get_viewdef('rpr_res_ts_v'::regclass, true); -SELECT 'CREATE VIEW rpr_res_ts_rt AS ' - || pg_get_viewdef('rpr_res_ts_v'::regclass, true) \gexec -SELECT pg_get_viewdef('rpr_res_ts_v'::regclass, true) - = pg_get_viewdef('rpr_res_ts_rt'::regclass, true) AS round_trips; -SELECT * FROM rpr_res_ts_v; -SELECT * FROM rpr_res_ts_rt; - -DROP VIEW rpr_res_ts_rt, rpr_res_ts_v; -DROP VIEW rpr_res_ta_rt, rpr_res_ta_v; -DROP VIEW rpr_res_tk_rt, rpr_res_tk_v; -DROP TABLE rpr_res_tk, rpr_res_th, rpr_res_ta, rpr_res_ts, rpr_res_to; - -- A merged column the DEFINE clause reads is named by the DEFINE clause, not -- by the USING clause: the name settled for it is the one the merge adopts. -- So a join whose alias list renames the merged column prints the same text -- 2.54.0 (Apple Git-157)