On Sat, 2026-09-19 at 17:37 +0300, Alexander Korotkov wrote:
> Pushed.
AI discovered a problem with the ruleutils output after e73841ffbce.
Report attached.
Regards,
Jeff Davis
JSON_TABLE deparse drops a column-level NULL ON ERROR that overrides a
table-level ERROR ON ERROR, so the view changes behavior on restore
Problem
-------
Since commit e73841ffbce ("JSON_TABLE: propagate table-level ON ERROR
to columns per SQL standard", 2026-09-19), a regular or formatted
JSON_TABLE column without its own ON ERROR clause inherits ERROR ON
ERROR from a table-level ERROR ON ERROR clause. A column that
explicitly says NULL ON ERROR still overrides that, as the standard
requires and as the commit's own tests check.
However, ruleutils.c still treats NULL ON ERROR as "the default" for
such a column and omits it from the deparsed text. Under a
table-level ERROR ON ERROR the omitted clause is no longer implied:
re-parsing the deparsed text makes the column inherit ERROR ON ERROR.
So pg_get_viewdef() (and hence pg_dump, pg_restore, pg_upgrade, and
CREATE OR REPLACE VIEW from \d+ output) turns a column that returned
NULL on a conversion failure into one that raises an error. The
stored view itself keeps working; only the textual definition is
wrong, which is exactly the form that survives a dump.
Affected branches and commits
-----------------------------
master only (20devel). Introduced by e73841ffbce, which changed the
parser default without a matching change in get_json_table_columns()
/ get_json_expr_options() in src/backend/utils/adt/ruleutils.c.
The deparse code itself is unchanged since JSON_TABLE was added in
17 (de3600452b6), where omitting NULL ON ERROR was harmless because
the table-level clause never affected columns. Released branches
are therefore not wrong on their own, but a view created on 17-19 as
COLUMNS (a int PATH '$' NULL ON ERROR) ERROR ON ERROR
is dumped by those servers as
COLUMNS (a integer PATH '$') ERROR ON ERROR
and will change behavior when restored on a master with e73841ffbce,
including via pg_upgrade. Fixing master alone does not cover that
path; only teaching the back branches to print the column clause in
this situation (or a release note) would.
Reproduction
------------
On master (tested at 09a579abaca, which includes e73841ffbce):
-- explicit column NULL ON ERROR wins over table ERROR ON ERROR
SELECT * FROM JSON_TABLE(jsonb '"err"', '$'
COLUMNS (a int PATH '$' NULL ON ERROR) ERROR ON ERROR) jt;
a
---
(1 row)
CREATE VIEW v AS
SELECT * FROM JSON_TABLE(jsonb '"err"', '$'
COLUMNS (a int PATH '$' NULL ON ERROR) ERROR ON ERROR) jt;
SELECT * FROM v; -- NULL row, as above
SELECT pg_get_viewdef('v', true);
SELECT a
FROM JSON_TABLE(
'"err"'::jsonb, '$' AS json_table_path_0
COLUMNS (
a integer PATH '$'
) ERROR ON ERROR
) jt;
-- Feed the deparsed definition back (what pg_dump/restore does)
CREATE OR REPLACE VIEW v AS
SELECT a FROM JSON_TABLE('"err"'::jsonb, '$' AS json_table_path_0
COLUMNS (a integer PATH '$') ERROR ON ERROR) jt;
SELECT * FROM v;
ERROR: invalid input syntax for type integer: "err"
Expected: pg_get_viewdef() prints "a integer PATH '$' NULL ON ERROR"
and the re-created view (or a pg_dump/pg_restore of it) still returns
a NULL row.
Actual: the column clause is omitted and the restored view raises an
error. The same happens for formatted columns, e.g.
"a int[] PATH '$.a' NULL ON ERROR" under ERROR ON ERROR deparses as
"a integer[] PATH '$."a"' WITHOUT WRAPPER KEEP QUOTES" with no ON
ERROR clause.
A plain "pg_dump src | psql dst" of a database containing the view
above produces a view in dst that raises where the one in src
returned NULL.
Diagnosis
---------
transformJsonTableColumn() (parse_jsontable.c) now synthesizes
ERROR ON ERROR for a non-EXISTS column that has no on_error when the
table-level on_error is JSON_BEHAVIOR_ERROR. The column-level
default is therefore a function of the table-level clause.
get_json_table_columns() (ruleutils.c) still passes a fixed
default_behavior of JSON_BEHAVIOR_NULL (JSON_BEHAVIOR_FALSE for
EXISTS) to get_json_expr_options(), which suppresses ON EMPTY and ON
ERROR clauses whose btype equals that single default. So under a
table-level ERROR ON ERROR:
- a column with on_error = ERROR (whether written or inherited)
is printed explicitly, which is redundant but harmless; and
- a column with on_error = NULL (written explicitly) is dropped,
and re-parses as ERROR ON ERROR.
The fix is to make the ON ERROR default used for suppression follow
the table-level clause: for non-EXISTS columns use JSON_BEHAVIOR_ERROR
when castNode(JsonExpr, tf->docexpr)->on_error->btype is
JSON_BEHAVIOR_ERROR, else JSON_BEHAVIOR_NULL, while leaving the ON
EMPTY default alone (it does not cascade). That needs
get_json_expr_options() to take separate ON EMPTY and ON ERROR
defaults. Verified: with that change the view above deparses with
"NULL ON ERROR", pg_dump/restore round-trips to a NULL row, and the
only regression change is that the two views in sqljson_jsontable
whose ERROR ON ERROR was inherited are now printed without the
redundant clause. Alternatively, always print the column's ON ERROR
when the table-level clause is ERROR ON ERROR; that keeps the current
expected output and is more robust against future default changes.
The commit's tests exercise the explicit NULL ON ERROR override only
at execution time; a pg_get_viewdef() test for it would have caught
this.
