Hi hackers, ruleutils.c never prints a column alias list for XMLTABLE or JSON_TABLE, and does not print the alias of one that it renamed to avoid another one in an outer query level. So when their columns are renamed, by an alias list written by the user or by the deparser, a view or rule does not read back, or reads back with other column names.
The problem
-----------
create view v1 as
select t.p from json_table(jsonb '[{"a": 1}]', '$[*]'
columns (a int path '$.a')) as t(p);
before: SELECT p FROM JSON_TABLE(...) t;
restore: ERROR: column "p" does not exist
after: SELECT p FROM JSON_TABLE(...) t(p);
When the alias list only reorders the names, the text reads back
without an error, and the result changes:
create view v2 as
select row_to_json(jt) as r
from json_table(jsonb '[{"a": 1, "b": 2}]', '$[*]'
columns (a int path '$.a', b int path '$.b')) as jt(b, a);
before: {"b":1,"a":2}, and {"a":1,"b":2} after restore
after: {"b":1,"a":2} both times
The same happens when the deparser renames the columns itself, as
under an unnamed join with USING. And the alias of the RTE is lost
when it is renamed:
create view v3 as
select a, (select max(b) from json_table(jsonb '[{"b": 2}]',
'$[*]' columns (b int path '$.b'))) as m
from json_table(jsonb '[{"a": 1}]', '$[*]'
columns (a int path '$.a'));
before: SELECT a, (SELECT max(json_table_1.b) AS max
FROM JSON_TABLE(...)) AS m
FROM JSON_TABLE(...);
restore: ERROR: missing FROM-clause entry for table
"json_table_1"
after: ... FROM JSON_TABLE(...) json_table_1) AS m ...
XMLTABLE behaves the same way, from 10 on. A fixed server cannot
repair a dump made by an old one, as the text is already wrong.
The fix
-------
The fix removes the exception that keeps table functions from printing
a column alias list, so they follow the rule of the other non-relation
RTEs: the list is printed when the user wrote one or the deparser
changed a name. It also prints the alias when set_rtable_names()
changed it from the default name. No existing expected output
changes.
Tests
-----
The tests add views to xml and sqljson_jsontable with a user-written
alias list and with a renamed table function, and in sqljson_jsontable
one with NATURAL JOIN, which joins on the names in the alias lists.
They come first with the output before the fix, so the fix patch shows
how the output changes. The views are kept for the dump and restore
comparison in the pg_upgrade test. xml_1.out needs no change, as the
test stops early without libxml.
Back branches
-------------
I think this should be back-patched to 15.
- The master patches apply to 19, 18 and 17 as they are.
- The REL_16 .txt files are for 15 and 16, which have no
JSON_TABLE: they drop its tests and its mention in a comment,
and place the XMLTABLE tests at the end of a different xml.sql.
With that, make check and the pg_upgrade test pass on all six
branches, with libxml.
About the exception
-------------------
I do not know why the exception came in with XMLTABLE. One thing that
may be behind it is that the names in COLUMNS are not only the column
names of the table but also the names of XML elements and JSON keys.
Those names cannot change, and printing them again in a list, as for
functions, would only repeat them.
Without an alias list, this makes a round trip: when XML or JSON is
built again from the columns, as with row_to_json() or xmlforest(),
the column names come back as the names of the elements or keys:
select row_to_json(xt) as r
from xmltable('/r' passing ('<r><a>1</a><b>2</b></r>'::xml)
columns a int, b int) as xt;
{"a":1,"b":2}
But an alias list changes the names that come back, and it cannot be
done without. To keep the text of a view valid, also across DDL, the
deparser gives columns new names such as x_1 in alias lists to avoid
conflicts, for a table function too. Even if one tried to keep the
names in COLUMNS as they are, it could not be done, as those of
different table functions can conflict. So not printing the list
cannot keep the round trip; the text reads back with the names of the
clause, and the result can change without an error:
create view xr as
select row_to_json(xt) as r
from xmltable('/r' passing ('<r><a>1</a><b>2</b></r>'::xml)
columns a int, b int) as xt(b, a);
before: {"b":1,"a":2}, and {"a":1,"b":2} after restore
after: {"b":1,"a":2} both times
JSON_TABLE reuses the same kind of RTE, and so the exception. All
this is only a guess; if anyone knows the reason, I would be glad to
hear it.
This is the last of the three ruleutils.c fixes I said in the row
pattern recognition thread [1] I would send on their own, and the
patch that takes it out of the series is posted there [2].
[1]
https://postgr.es/m/CAAAe_zCb9847vcoaH72wvk61O-xOH_OSzYHt40TH37xjz=k...@mail.gmail.com
[2]
https://postgr.es/m/CAAAe_zA7eFMiRRJAgHoaQ1S6YZWmMz7P=83kxnavg1jvq%[email protected]
Best regards,
Henson
From 917972001c47014c57f7310451d4a7c7c4239fad Mon Sep 17 00:00:00 2001 From: Henson Choi <[email protected]> Date: Thu, 22 Oct 2026 12:45:47 +0900 Subject: [PATCH v1 1/2] Add tests for XMLTABLE alias lists The column alias list of an XMLTABLE is not printed, and neither is the alias of one that is renamed to avoid another one in an outer query level, so the text refers to names that are not defined: SELECT t.p FROM xmldata, LATERAL XMLTABLE(...) t SELECT "xmltable".a, (SELECT max(xmltable_1.b) AS max FROM XMLTABLE(...)) AS m FROM xmldata, LATERAL XMLTABLE(...) The expected output shows the behavior before the fix. The next commit fixes it and updates the output. --- src/test/regress/expected/xml.out | 24 ++++++++++++++++++++++++ src/test/regress/sql/xml.sql | 15 +++++++++++++++ 2 files changed, 39 insertions(+) diff --git a/src/test/regress/expected/xml.out b/src/test/regress/expected/xml.out index ac90e3eccc8..384ea02dda8 100644 --- a/src/test/regress/expected/xml.out +++ b/src/test/regress/expected/xml.out @@ -1813,3 +1813,27 @@ SELECT * FROM XMLTABLE('.' PASSING XMLELEMENT(NAME a) columns a varchar(20) PATH <foo/> | <foo/> (1 row) +-- 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 +CREATE VIEW xmltableview_alias AS +SELECT t.p +FROM xmldata, XMLTABLE('/ROWS/ROW' PASSING data + COLUMNS a int PATH '@id') AS t(p); +\sv xmltableview_alias +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 +CREATE VIEW xmltableview_conflict AS +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 +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 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 81628227e0d..8a506b5e693 100644 --- a/src/test/regress/sql/xml.sql +++ b/src/test/regress/sql/xml.sql @@ -677,3 +677,18 @@ SELECT * FROM XMLTABLE('*' PASSING '<e>pre<!--c1--><?pi arg?><![CDATA[&ent1]]><n \x SELECT * FROM XMLTABLE('.' PASSING XMLELEMENT(NAME a) columns a varchar(20) PATH '"<foo/>"', b xml PATH '"<foo/>"'); + +-- 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 +CREATE VIEW xmltableview_alias AS +SELECT t.p +FROM xmldata, XMLTABLE('/ROWS/ROW' PASSING data + COLUMNS a int PATH '@id') AS t(p); +\sv xmltableview_alias +CREATE VIEW xmltableview_conflict AS +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)
v1-0001-Add-tests-for-JSON_TABLE-and-XMLTABLE-alias-lists.patch
Description: Binary data
v1-0002-Fix-JSON_TABLE-and-XMLTABLE-alias-lists-in-depars.patch
Description: Binary data
From 4b88dc55e44b73ed81aeeeb1fa6af74561ed144c Mon Sep 17 00:00:00 2001 From: Henson Choi <[email protected]> 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)
