[DEPARSE] Column alias lists of XMLTABLE and JSON_TABLE are not printed

From: Henson Choi <assam258(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: [DEPARSE] Column alias lists of XMLTABLE and JSON_TABLE are not printed
Date: 2026-11-17 03:03:24
Message-ID: CAAAe_zBqehPbVDfF4ojOENiqutXmk06cmMRP3ChOar-TohybKw@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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=KmKg@mail.gmail.com
[2]
https://postgr.es/m/CAAAe_zA7eFMiRRJAgHoaQ1S6YZWmMz7P=83kXnavg1jVq%2BNTyQ@mail.gmail.com

Best regards,
Henson

Attachment Content-Type Size
REL_16-v1-0001-Add-tests-for-XMLTABLE-alias-lists.txt text/plain 3.4 KB
v1-0001-Add-tests-for-JSON_TABLE-and-XMLTABLE-alias-lists.patch application/octet-stream 7.7 KB
v1-0002-Fix-JSON_TABLE-and-XMLTABLE-alias-lists-in-depars.patch application/octet-stream 8.6 KB
REL_16-v1-0002-Fix-JSON_TABLE-and-XMLTABLE-alias-lists-in-depars.txt text/plain 5.6 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Chee Wooson 2026-11-17 03:20:52 Re: [PATCH v2] Avoid carrying self lock-only xmax to updated tuple
Previous Message Henson Choi 2026-11-17 03:02:27 [DEPARSE] Renamed columns of relations not in FROM break rules and functions