postgres.git / summary / log / commit / refs
commit af6fad879fbf9b542cfde05c52695c9dffa6bb47
Author: Alexander Korotkov <akorotkov@postgresql.org>
Date: Tue Jul 14 21:46:40 2026 +0000
Revert cascading of JSON_TABLE's ON ERROR
86ab7f4c721d commit made the table-level ON ERROR clause serve as the default
ON ERROR for columns lacking their own, so that a top-level ERROR ON ERROR
turned per-column evaluation errors into hard errors.
The SQL standard does mandate this cascade, but introducing it should be
a deliberate, separately-documented change, so restore the previous
behavior for now. This also reverts the paired ruleutils.c logic that
deparsed a column's behavior against an ERROR default: that dropped an
explicit ERROR ON EMPTY from a dumped view, and otherwise emitted a
redundant NULL ON EMPTY.
Reported-by: Thom Brown <thom@linux.com>
Discussion: https://postgr.es/m/CAA-aLv7aZGSExnbjJRw8eKkoXbu34TdoKLLA2gPye3aHjO5OSA@mail.gmail.com
src/backend/parser/parse_jsontable.c | 13 +++---------
src/backend/utils/adt/ruleutils.c | 4 ----
src/test/regress/expected/sqljson_jsontable.out | 27 ++++++++++++++++++++++++-
src/test/regress/sql/sqljson_jsontable.sql | 15 ++++++++++++++
4 files changed, 44 insertions(+), 15 deletions(-)
diff --git a/src/backend/parser/parse_jsontable.c b/src/backend/parser/parse_jsontable.c
index ef042830f02..674ab37c45a 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,
@@ -363,12 +362,8 @@ appendJsonTableColumns(JsonTableParseContext *cxt, List *columns, List *passingA
{
ListCell *col;
ParseState *pstate = cxt->pstate;
- JsonTable *jt = cxt->jt;
TableFunc *tf = cxt->tf;
bool ordinality_found = false;
- JsonBehavior *on_error = jt->on_error;
- bool errorOnError = on_error &&
- on_error->btype == JSON_BEHAVIOR_ERROR;
Oid contextItemTypid = exprType(tf->docexpr);
foreach(col, columns)
@@ -427,7 +422,7 @@ appendJsonTableColumns(JsonTableParseContext *cxt, List *columns, List *passingA
param->typeMod = -1;
jfe = transformJsonTableColumn(rawc, (Node *) param,
- passingArgs, errorOnError);
+ passingArgs);
colexpr = transformExpr(pstate, (Node *) jfe,
EXPR_KIND_FROM_FUNCTION);
@@ -482,7 +477,7 @@ isCompositeType(Oid typid)
*/
static JsonFuncExpr *
transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr,
- List *passingArgs, bool errorOnError)
+ List *passingArgs)
{
Node *pathspec;
JsonFuncExpr *jfexpr = makeNode(JsonFuncExpr);
@@ -524,8 +519,6 @@ transformJsonTableColumn(JsonTableColumn *jtc, Node *contextItemExpr,
jfexpr->output->returning->format = jtc->format;
jfexpr->on_empty = jtc->on_empty;
jfexpr->on_error = jtc->on_error;
- if (jfexpr->on_error == NULL && errorOnError)
- 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/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index b3aef797e7d..b83c8b9d7b5 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -12799,7 +12799,6 @@ get_json_table_columns(TableFunc *tf, JsonTablePathScan *scan,
bool showimplicit)
{
StringInfo buf = context->buf;
- JsonExpr *jexpr = castNode(JsonExpr, tf->docexpr);
ListCell *lc_colname;
ListCell *lc_coltype;
ListCell *lc_coltypmod;
@@ -12879,9 +12878,6 @@ get_json_table_columns(TableFunc *tf, JsonTablePathScan *scan,
default_behavior = JSON_BEHAVIOR_NULL;
}
- if (jexpr->on_error->btype == JSON_BEHAVIOR_ERROR)
- default_behavior = JSON_BEHAVIOR_ERROR;
-
appendStringInfoString(buf, " PATH ");
get_json_path_spec(colexpr->path_spec, context, showimplicit);
diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out
index 1530ef8afe0..6ff2b1ab28b 100644
--- a/src/test/regress/expected/sqljson_jsontable.out
+++ b/src/test/regress/expected/sqljson_jsontable.out
@@ -547,6 +547,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 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;
+ a
+---
+
+(1 row)
+
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH '$' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
a
---
@@ -1821,7 +1830,7 @@ CREATE OR REPLACE VIEW public.json_table_view9 AS
FROM JSON_TABLE(
'"a"'::text, '$' AS json_table_path_0
COLUMNS (
- a text PATH '$' NULL ON EMPTY
+ a text PATH '$'
) ERROR ON ERROR
)
DROP VIEW json_table_view8, json_table_view9;
@@ -1847,3 +1856,19 @@ CREATE OR REPLACE VIEW public.json_table_view9 AS
)
)
DROP VIEW json_table_view8, json_table_view9;
+-- Test JSON_TABLE() column deparsing -- a non-default ON EMPTY behavior of a
+-- column must be preserved
+CREATE VIEW json_table_view_on_empty AS
+SELECT * FROM JSON_TABLE(jsonb '{}', '$' AS p0
+ COLUMNS (a int PATH '$.nosuch' ERROR ON EMPTY)
+ ERROR ON ERROR);
+\sv json_table_view_on_empty;
+CREATE OR REPLACE VIEW public.json_table_view_on_empty AS
+ SELECT a
+ FROM JSON_TABLE(
+ '{}'::jsonb, '$' AS p0
+ COLUMNS (
+ 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 d71d57e99b2..5dab93973db 100644
--- a/src/test/regress/sql/sqljson_jsontable.sql
+++ b/src/test/regress/sql/sqljson_jsontable.sql
@@ -266,6 +266,11 @@ SELECT * FROM JSON_TABLE(jsonb '1', '$' COLUMNS (a int PATH '$.a' ERROR ON EMPTY
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 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;
+
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;
SELECT * FROM JSON_TABLE(jsonb '"a"', '$' COLUMNS (a int PATH 'lax $.a' DEFAULT 1 ON EMPTY DEFAULT 2 ON ERROR)) jt;
@@ -1004,3 +1009,13 @@ CREATE VIEW json_table_view9 AS SELECT * from JSON_TABLE('"a"', '$' COLUMNS (a t
\sv json_table_view9;
DROP VIEW json_table_view8, json_table_view9;
+
+-- Test JSON_TABLE() column deparsing -- a non-default ON EMPTY behavior of a
+-- column must be preserved
+CREATE VIEW json_table_view_on_empty AS
+SELECT * FROM JSON_TABLE(jsonb '{}', '$' AS p0
+ COLUMNS (a int PATH '$.nosuch' ERROR ON EMPTY)
+ ERROR ON ERROR);
+\sv json_table_view_on_empty;
+
+DROP VIEW json_table_view_on_empty;
[parent: d15a6bc2e16d]