From 4b88dc55e44b73ed81aeeeb1fa6af74561ed144c Mon Sep 17 00:00:00 2001 From: Henson Choi Date: Thu, 22 Oct 2026 12:46:00 +0900 Subject: [PATCH v1 2/2] Fix JSON_TABLE and XMLTABLE alias lists in deparsed SQL XMLTABLE and JSON_TABLE never printed a column alias list, because the clause names its columns itself. But the columns can still be renamed, by a user-written alias list or by the deparser, and the text then referred to columns that do not exist ("column p does not exist"). The alias of the RTE was not printed either, even when it was renamed to avoid another one in an outer query level ("missing FROM-clause entry for table json_table_1"). Print both when needed, as for other RTEs. The XMLTABLE part goes back to 15; JSON_TABLE exists only from 17. Backpatch-through: 15 --- src/backend/utils/adt/ruleutils.c | 21 +++++++++++++++------ src/test/regress/expected/xml.out | 7 +++---- src/test/regress/sql/xml.sql | 3 +-- 3 files changed, 19 insertions(+), 12 deletions(-) diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 035bdcf3c03..65b129b4782 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -4483,17 +4483,15 @@ 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 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"). + * 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"). That includes tablefunc RTEs, since + * XMLTABLE accepts a column alias list too. */ 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 @@ -11855,6 +11853,17 @@ get_rte_alias(RangeTblEntry *rte, int varno, bool use_as, if (strcmp(refname, rte->ctename) != 0) printalias = true; } + else if (rte->rtekind == RTE_TABLEFUNC) + { + /* + * No need to print alias if it's the default name the parser assigned + * (this would normally be the case, but not if set_rtable_names had + * to resolve a conflict). There is no user-written alias here, so + * eref->aliasname is that default name. + */ + if (strcmp(refname, rte->eref->aliasname) != 0) + printalias = true; + } if (printalias) appendStringInfo(context->buf, "%s%s", diff --git a/src/test/regress/expected/xml.out b/src/test/regress/expected/xml.out index 384ea02dda8..d4fb48c5be9 100644 --- a/src/test/regress/expected/xml.out +++ b/src/test/regress/expected/xml.out @@ -1815,7 +1815,7 @@ SELECT * FROM XMLTABLE('.' PASSING XMLELEMENT(NAME a) columns a varchar(20) PATH -- Test XMLTABLE() column alias list deparsing, and the alias of an -- XMLTABLE() that is renamed to avoid a conflict with another one in an --- outer query level +-- outer query level; leave the views around for the pg_upgrade test CREATE VIEW xmltableview_alias AS SELECT t.p FROM xmldata, XMLTABLE('/ROWS/ROW' PASSING data @@ -1824,7 +1824,7 @@ FROM xmldata, XMLTABLE('/ROWS/ROW' PASSING data CREATE OR REPLACE VIEW public.xmltableview_alias AS SELECT t.p FROM xmldata, - LATERAL XMLTABLE(('/ROWS/ROW'::text) PASSING (xmldata.data) COLUMNS a integer PATH ('@id'::text)) t + LATERAL XMLTABLE(('/ROWS/ROW'::text) PASSING (xmldata.data) COLUMNS a integer PATH ('@id'::text)) t(p) CREATE VIEW xmltableview_conflict AS SELECT a, (SELECT max(b) FROM XMLTABLE('/ROWS/ROW' PASSING data COLUMNS b int PATH '@id')) AS m @@ -1833,7 +1833,6 @@ FROM xmldata, XMLTABLE('/ROWS/ROW' PASSING data COLUMNS a int PATH '@id'); CREATE OR REPLACE VIEW public.xmltableview_conflict AS SELECT "xmltable".a, ( SELECT max(xmltable_1.b) AS max - FROM XMLTABLE(('/ROWS/ROW'::text) PASSING (xmldata.data) COLUMNS b integer PATH ('@id'::text))) AS m + FROM XMLTABLE(('/ROWS/ROW'::text) PASSING (xmldata.data) COLUMNS b integer PATH ('@id'::text)) xmltable_1) AS m FROM xmldata, LATERAL XMLTABLE(('/ROWS/ROW'::text) PASSING (xmldata.data) COLUMNS a integer PATH ('@id'::text)) -DROP VIEW xmltableview_alias, xmltableview_conflict; diff --git a/src/test/regress/sql/xml.sql b/src/test/regress/sql/xml.sql index 8a506b5e693..aa572b8a767 100644 --- a/src/test/regress/sql/xml.sql +++ b/src/test/regress/sql/xml.sql @@ -680,7 +680,7 @@ SELECT * FROM XMLTABLE('.' PASSING XMLELEMENT(NAME a) columns a varchar(20) PATH -- Test XMLTABLE() column alias list deparsing, and the alias of an -- XMLTABLE() that is renamed to avoid a conflict with another one in an --- outer query level +-- outer query level; leave the views around for the pg_upgrade test CREATE VIEW xmltableview_alias AS SELECT t.p FROM xmldata, XMLTABLE('/ROWS/ROW' PASSING data @@ -691,4 +691,3 @@ SELECT a, (SELECT max(b) FROM XMLTABLE('/ROWS/ROW' PASSING data COLUMNS b int PATH '@id')) AS m FROM xmldata, XMLTABLE('/ROWS/ROW' PASSING data COLUMNS a int PATH '@id'); \sv xmltableview_conflict -DROP VIEW xmltableview_alias, xmltableview_conflict; -- 2.54.0 (Apple Git-157)