postgres.git / summary / log / commit / refs
commit 2c6f2c5e1778e243f5300ec45d0f4b0be15e6325
Author: Alexander Korotkov <akorotkov@postgresql.org>
Date: Thu Sep 24 15:32:19 2026 +0000
Revert "JSON_TABLE: propagate table-level ON ERROR to columns per SQL standard"
This reverts commit e73841ffbceea314cf9fa3f64a5eae9f87a46449. Due to
pg_dump/pg_restore problem and related pg_upgrade problem.
Reported-by: Jeff Davis <pgsql@j-davis.com>
Reported-by: Robert Haas <robertmhaas@gmail.com>
Discussion: https://postgr.es/m/d048d3c10964ed984f949f649f194061501e2317.camel%40j-davis.com
Discussion: https://postgr.es/m/CA%2BTgmoaT9LYxg2%2B4fYh8in0zguHcwyUhN_cFOS-6TCH1-2_Cfw%40mail.gmail.com
doc/src/sgml/func/func-json.sgml | 16 ++++----------
src/backend/parser/parse_jsontable.c | 29 +++++--------------------
src/test/regress/expected/sqljson_jsontable.out | 28 ++++++------------------
src/test/regress/sql/sqljson_jsontable.sql | 17 +++++----------
4 files changed, 22 insertions(+), 68 deletions(-)
diff --git a/doc/src/sgml/func/func-json.sgml b/doc/src/sgml/func/func-json.sgml
index f8ff4355a31..0763ec81ed8 100644
--- a/doc/src/sgml/func/func-json.sgml
+++ b/doc/src/sgml/func/func-json.sgml
@@ -4021,18 +4021,10 @@ where <replaceable class="parameter">json_table_column</replaceable> is:
handle errors when evaluating the top-level
<replaceable>path_expression</replaceable>. Use <literal>ERROR</literal>
if you want the errors to be thrown and <literal>EMPTY</literal> to
- return an empty table, that is, a table containing 0 rows.
- </para>
- <para>
- This clause also provides the default error behavior for the individual
- columns: a regular or formatted column that does not specify its own
- <literal>ON ERROR</literal> clause uses <literal>ERROR ON ERROR</literal>
- when <literal>ERROR</literal> is specified here, and
- <literal>NULL ON ERROR</literal> otherwise (including when this clause is
- omitted, in which case <literal>EMPTY</literal> is the default). A column
- that specifies its own <literal>ON ERROR</literal> clause is unaffected.
- <literal>EXISTS</literal> columns are an exception: they always default to
- <literal>FALSE ON ERROR</literal> regardless of this clause.
+ return an empty table, that is, a table containing 0 rows. Note that
+ this clause does not affect the errors that occur when evaluating
+ columns, for which the behavior depends on whether the
+ <literal>ON ERROR</literal> clause is specified against a given column.
</para>
</listitem>
</varlistentry>
diff --git a/src/backend/parser/parse_jsontable.c b/src/backend/parser/parse_jsontable.c
index 0c6c0a6dbf9..d86c6946ee9 100644
--- a/src/backend/parser/parse_jsontable.c
+++ b/src/backend/parser/parse_jsontable.c
@@ -49,8 +49,7 @@ static JsonTablePlan *transformJsonTableNestedColumns(JsonTableParseContext *cxt
List *columns);
static JsonFuncExpr *transformJsonTableColumn(JsonTableColumn *jtc,
Node *contextItemExpr,
- List *passingArgs,
- bool errorOnError);
+ List *passingArgs);
static bool isCompositeType(Oid typid);
static JsonTablePlan *makeJsonTablePathScan(JsonTableParseContext *cxt,
JsonTablePathSpec *pathspec,
@@ -69,8 +68,7 @@ static JsonTablePlan *makeJsonTableSiblingJoin(bool cross,
JsonTablePlan *lplan,
JsonTablePlan *rplan);
static void
- appendJsonTableColumns(JsonTableParseContext *cxt, List *columns,
- List *passingArgs, bool errorOnError);
+ appendJsonTableColumns(JsonTableParseContext *cxt, List *columns, List *passingArgs);
/*
* transformJsonTable -
@@ -346,7 +344,7 @@ transformJsonTableColumns(JsonTableParseContext *cxt,
validateJsonTableChildPlan(cxt, childPlanSpec, columns);
}
- appendJsonTableColumns(cxt, columns, passingArgs, errorOnError);
+ appendJsonTableColumns(cxt, columns, passingArgs);
/* End of column range. */
if (list_length(tf->colvalexprs) == colMin)
@@ -377,8 +375,7 @@ transformJsonTableColumns(JsonTableParseContext *cxt,
/* Append transformed non-nested JSON_TABLE columns to the TableFunc node */
static void
-appendJsonTableColumns(JsonTableParseContext *cxt, List *columns,
- List *passingArgs, bool errorOnError)
+appendJsonTableColumns(JsonTableParseContext *cxt, List *columns, List *passingArgs)
{
ListCell *col;
ParseState *pstate = cxt->pstate;
@@ -442,7 +439,7 @@ appendJsonTableColumns(JsonTableParseContext *cxt, List *columns,
param->typeMod = -1;
jfe = transformJsonTableColumn(rawc, (Node *) param,
- passingArgs, errorOnError);
+ passingArgs);
colexpr = transformExpr(pstate, (Node *) jfe,
EXPR_KIND_FROM_FUNCTION);
@@ -497,7 +494,7 @@ isCompositeType(Oid typid)
*/
static JsonFuncExpr *
transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr,
- List *passingArgs, bool errorOnError)
+ List *passingArgs)
{
Node *pathspec;
JsonFuncExpr *jfexpr = makeNode(JsonFuncExpr);
@@ -539,20 +536,6 @@ transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr,
jfexpr->output->returning->format = jtc->format;
jfexpr->on_empty = jtc->on_empty;
jfexpr->on_error = jtc->on_error;
-
- /*
- * Per the SQL/JSON standard, a regular or formatted column that does not
- * specify its own ON ERROR clause inherits ERROR ON ERROR from a
- * table-level ERROR ON ERROR clause; otherwise it defaults to NULL ON
- * ERROR (applied downstream in transformJsonExprCommon()). See ISO/IEC
- * 9075-2:2023, 7.11 <JSON table>, Syntax Rules 1)e)iv) (regular columns)
- * and 1)f)xi) (formatted columns). EXISTS columns are not covered by the
- * standard, so they keep their own default (FALSE ON ERROR).
- */
- if (jfexpr->on_error == NULL && errorOnError &&
- jfexpr->op != JSON_EXISTS_OP)
- jfexpr->on_error = makeJsonBehavior(JSON_BEHAVIOR_ERROR, NULL, -1);
-
jfexpr->quotes = jtc->quotes;
jfexpr->wrapper = jtc->wrapper;
jfexpr->location = jtc->location;
diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out
index 4129a731faa..ae64dbed303 100644
--- a/src/test/regress/expected/sqljson_jsontable.out
+++ b/src/test/regress/expected/sqljson_jsontable.out
@@ -562,13 +562,12 @@ FROM
JSON_TABLE(vals.js::jsonb, '$' COLUMNS (a int PATH '$' ERROR ON ERROR)) jt
ON true;
ERROR: invalid input syntax for type integer: "err"
--- TABLE-level ERROR ON ERROR is propagated to a column without its own
--- ON ERROR clause (per SQL standard), so "err" raises instead of yielding NULL.
+-- TABLE-level ERROR ON ERROR is not propagated to columns
SELECT *
FROM
(VALUES ('1'), ('"err"')) vals(js)
LEFT OUTER JOIN
- JSON_TABLE(vals.js::jsonb, '$' COLUMNS (a int PATH '$') ERROR ON ERROR) jt
+ JSON_TABLE(vals.js::jsonb, '$' COLUMNS (a int PATH '$' ERROR ON ERROR)) jt
ON true;
ERROR: invalid input syntax for type integer: "err"
SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH '$.a' ERROR ON EMPTY)) jt;
@@ -577,28 +576,15 @@ SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'strict $.a' ERROR O
ERROR: jsonpath member accessor can only be applied to an object
SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'lax $.a' ERROR ON EMPTY) ERROR ON ERROR) jt;
ERROR: no SQL/JSON item found for specified path of column "a"
--- Table-level ERROR ON ERROR is propagated to a column lacking its own ON ERROR
--- clause, so a conversion failure raises an error rather than yielding NULL
--- (ISO/IEC 9075-2:2023, 7.11 <JSON table>, SR 1)e)iv)).
+-- Table-level ERROR ON ERROR is not propagated to a column lacking its own
+-- ON ERROR clause: the column keeps the default NULL ON ERROR behavior, so a
+-- conversion failure yields NULL rather than raising an error.
SELECT * FROM JSON_TABLE(jsonb '"err"', '$' COLUMNS (a int PATH '$') ERROR ON ERROR) jt;
-ERROR: invalid input syntax for type integer: "err"
--- ... but an explicit column-level ON ERROR still wins over the table-level one.
-SELECT * FROM JSON_TABLE(jsonb '"err"', '$' COLUMNS (a int PATH '$' NULL ON ERROR) ERROR ON ERROR) jt;
a
---
(1 row)
--- Propagation likewise applies to formatted columns (SR 1)f)xi)), but not to
--- EXISTS columns, which are not part of the standard and keep FALSE ON ERROR.
-SELECT * FROM JSON_TABLE(jsonb '{"a":1}', '$' COLUMNS (a int[] PATH '$.a') ERROR ON ERROR) jt;
-ERROR: expected JSON array
-SELECT * FROM JSON_TABLE(jsonb '{}', 'strict $' COLUMNS (a int EXISTS PATH 'strict $.x') ERROR ON ERROR) jt;
- a
----
- 0
-(1 row)
-
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH '$' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
a
---
@@ -1898,7 +1884,7 @@ CREATE OR REPLACE VIEW public.json_table_view9 AS
FROM JSON_TABLE(
'"a"'::text, '$' AS json_table_path_0
COLUMNS (
- a text PATH '$' ERROR ON ERROR
+ a text PATH '$'
) ERROR ON ERROR
)
DROP VIEW json_table_view8, json_table_view9;
@@ -1936,7 +1922,7 @@ CREATE OR REPLACE VIEW public.json_table_view_on_empty AS
FROM JSON_TABLE(
'{}'::jsonb, '$' AS p0
COLUMNS (
- a integer PATH '$."nosuch"' ERROR ON EMPTY ERROR ON ERROR
+ a integer PATH '$."nosuch"' ERROR ON EMPTY
) ERROR ON ERROR
)
DROP VIEW json_table_view_on_empty;
diff --git a/src/test/regress/sql/sqljson_jsontable.sql b/src/test/regress/sql/sqljson_jsontable.sql
index b93b43899dd..2a33aaec57f 100644
--- a/src/test/regress/sql/sqljson_jsontable.sql
+++ b/src/test/regress/sql/sqljson_jsontable.sql
@@ -269,29 +269,22 @@ FROM
JSON_TABLE(vals.js::jsonb, '$' COLUMNS (a int PATH '$' ERROR ON ERROR)) jt
ON true;
--- TABLE-level ERROR ON ERROR is propagated to a column without its own
--- ON ERROR clause (per SQL standard), so "err" raises instead of yielding NULL.
+-- TABLE-level ERROR ON ERROR is not propagated to columns
SELECT *
FROM
(VALUES ('1'), ('"err"')) vals(js)
LEFT OUTER JOIN
- JSON_TABLE(vals.js::jsonb, '$' COLUMNS (a int PATH '$') ERROR ON ERROR) jt
+ JSON_TABLE(vals.js::jsonb, '$' COLUMNS (a int PATH '$' ERROR ON ERROR)) jt
ON true;
SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH '$.a' ERROR ON EMPTY)) jt;
SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'strict $.a' ERROR ON ERROR) ERROR ON ERROR) jt;
SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH 'lax $.a' ERROR ON EMPTY) ERROR ON ERROR) jt;
--- Table-level ERROR ON ERROR is propagated to a column lacking its own ON ERROR
--- clause, so a conversion failure raises an error rather than yielding NULL
--- (ISO/IEC 9075-2:2023, 7.11 <JSON table>, SR 1)e)iv)).
+-- Table-level ERROR ON ERROR is not propagated to a column lacking its own
+-- ON ERROR clause: the column keeps the default NULL ON ERROR behavior, so a
+-- conversion failure yields NULL rather than raising an error.
SELECT * FROM JSON_TABLE(jsonb '"err"', '$' COLUMNS (a int PATH '$') ERROR ON ERROR) jt;
--- ... but an explicit column-level ON ERROR still wins over the table-level one.
-SELECT * FROM JSON_TABLE(jsonb '"err"', '$' COLUMNS (a int PATH '$' NULL ON ERROR) ERROR ON ERROR) jt;
--- Propagation likewise applies to formatted columns (SR 1)f)xi)), but not to
--- EXISTS columns, which are not part of the standard and keep FALSE ON ERROR.
-SELECT * FROM JSON_TABLE(jsonb '{"a":1}', '$' COLUMNS (a int[] PATH '$.a') ERROR ON ERROR) jt;
-SELECT * FROM JSON_TABLE(jsonb '{}', 'strict $' COLUMNS (a int EXISTS PATH 'strict $.x') ERROR ON ERROR) jt;
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH '$' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'strict $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
[parent: 4e277b424a53]