Skip to content

Commit bbd4c05

Browse files
amitlanamitlan
committed
SQL/JSON: Fix default ON ERROR behavior for JSON_TABLE
Use EMPTY ARRAY instead of EMPTY. This change does not affect the runtime behavior of JSON_TABLE(), which continues to return an empty relation ON ERROR. It only alters whether the default ON ERROR behavior is shown in the deparsed output. Reported-by: Jian He <jian.universality@gmail.com> Discussion: https://postgr.es/m/CACJufxEo4sUjKCYtda0_qt9tazqqKPmF1cqhW9KBOUeJFqQd2g@mail.gmail.com Backpatch-through: 17
1 parent ee75a03 commit bbd4c05

File tree

4 files changed

+34
-3
lines changed

4 files changed

+34
-3
lines changed

‎src/backend/parser/parse_expr.c

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -4603,13 +4603,13 @@ transformJsonFuncExpr(ParseState *pstate, JsonFuncExpr *func)
46034603
}
46044604

46054605
/*
4606-
* Assume EMPTY ON ERROR when ON ERROR is not specified.
4606+
* Assume EMPTY ARRAY ON ERROR when ON ERROR is not specified.
46074607
*
46084608
* ON EMPTY cannot be specified at the top level but it can be for
46094609
* the individual columns.
46104610
*/
46114611
jsexpr->on_error = transformJsonBehavior(pstate, func->on_error,
4612-
JSON_BEHAVIOR_EMPTY,
4612+
JSON_BEHAVIOR_EMPTY_ARRAY,
46134613
jsexpr->returning);
46144614
break;
46154615

‎src/backend/utils/adt/ruleutils.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -11875,7 +11875,7 @@ get_json_table(TableFunc *tf, deparse_context *context, bool showimplicit)
1187511875
get_json_table_columns(tf, castNode(JsonTablePathScan, tf->plan), context,
1187611876
showimplicit);
1187711877

11878-
if (jexpr->on_error->btype != JSON_BEHAVIOR_EMPTY)
11878+
if (jexpr->on_error->btype != JSON_BEHAVIOR_EMPTY_ARRAY)
1187911879
get_json_behavior(jexpr->on_error, context, "ERROR");
1188011880

1188111881
if (PRETTY_INDENT(context))

‎src/test/regress/expected/sqljson_jsontable.out

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1155,3 +1155,25 @@ CREATE OR REPLACE VIEW public.json_table_view9 AS
11551155
) ERROR ON ERROR
11561156
)
11571157
DROP VIEW json_table_view8, json_table_view9;
1158+
-- Test JSON_TABLE() deparsing -- don't emit default ON ERROR behavior
1159+
CREATE VIEW json_table_view8 AS SELECT * from JSON_TABLE('"a"', '$' COLUMNS (a text PATH '$') EMPTY ON ERROR);
1160+
\sv json_table_view8;
1161+
CREATE OR REPLACE VIEW public.json_table_view8 AS
1162+
SELECT a
1163+
FROM JSON_TABLE(
1164+
'"a"'::text, '$' AS json_table_path_0
1165+
COLUMNS (
1166+
a text PATH '$'
1167+
)
1168+
)
1169+
CREATE VIEW json_table_view9 AS SELECT * from JSON_TABLE('"a"', '$' COLUMNS (a text PATH '$') EMPTY ARRAY ON ERROR);
1170+
\sv json_table_view9;
1171+
CREATE OR REPLACE VIEW public.json_table_view9 AS
1172+
SELECT a
1173+
FROM JSON_TABLE(
1174+
'"a"'::text, '$' AS json_table_path_0
1175+
COLUMNS (
1176+
a text PATH '$'
1177+
)
1178+
)
1179+
DROP VIEW json_table_view8, json_table_view9;

‎src/test/regress/sql/sqljson_jsontable.sql

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -552,3 +552,12 @@ CREATE VIEW json_table_view9 AS SELECT * from JSON_TABLE('"a"', '$' COLUMNS (a t
552552
\sv json_table_view9;
553553

554554
DROP VIEW json_table_view8, json_table_view9;
555+
556+
-- Test JSON_TABLE() deparsing -- don't emit default ON ERROR behavior
557+
CREATE VIEW json_table_view8 AS SELECT * from JSON_TABLE('"a"', '$' COLUMNS (a text PATH '$') EMPTY ON ERROR);
558+
\sv json_table_view8;
559+
560+
CREATE VIEW json_table_view9 AS SELECT * from JSON_TABLE('"a"', '$' COLUMNS (a text PATH '$') EMPTY ARRAY ON ERROR);
561+
\sv json_table_view9;
562+
563+
DROP VIEW json_table_view8, json_table_view9;

0 commit comments

Comments
 (0)