Skip to content

Commit 231b7d6

Browse files
amitlanamitlan
committed
SQL/JSON: Improve error-handling of JsonBehavior expressions
Instead of returning a NULL when the JsonBehavior expression value could not be coerced to the RETURNING type, throw the error message informing the user that it is the JsonBehavior expression that caused the error with the actual coercion error message shown in its DETAIL line. Discussion: https://postgr.es/m/CACJufxEo4sUjKCYtda0_qt9tazqqKPmF1cqhW9KBOUeJFqQd2g@mail.gmail.com Backpatch-through: 17
1 parent 63e6c5f commit 231b7d6

File tree

3 files changed

+78
-28
lines changed

3 files changed

+78
-28
lines changed

‎src/backend/executor/execExprInterp.c

Lines changed: 70 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -4284,13 +4284,12 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
42844284
memset(&jsestate->error, 0, sizeof(NullableDatum));
42854285
memset(&jsestate->empty, 0, sizeof(NullableDatum));
42864286

4287-
/*
4288-
* Also reset ErrorSaveContext contents for the next row. Since we don't
4289-
* set details_wanted, we don't need to also reset error_data, which would
4290-
* be NULL anyway.
4291-
*/
4292-
Assert(!jsestate->escontext.details_wanted &&
4293-
jsestate->escontext.error_data == NULL);
4287+
/* Also reset ErrorSaveContext contents for the next row. */
4288+
if (jsestate->escontext.details_wanted)
4289+
{
4290+
jsestate->escontext.error_data = NULL;
4291+
jsestate->escontext.details_wanted = false;
4292+
}
42944293
jsestate->escontext.error_occurred = false;
42954294

42964295
switch (jsexpr->op)
@@ -4400,6 +4399,14 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
44004399
error = true;
44014400
}
44024401

4402+
/*
4403+
* When setting up the ErrorSaveContext (if needed) for capturing the
4404+
* errors that occur when coercing the JsonBehavior expression, set
4405+
* details_wanted to be able to show the actual error message as the
4406+
* DETAIL of the error message that tells that it is the JsonBehavior
4407+
* expression that caused the error; see ExecEvalJsonCoercionFinish().
4408+
*/
4409+
44034410
/* Handle ON EMPTY. */
44044411
if (empty)
44054412
{
@@ -4410,13 +4417,19 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
44104417
if (jsexpr->on_empty->btype != JSON_BEHAVIOR_ERROR)
44114418
{
44124419
jsestate->empty.value = BoolGetDatum(true);
4420+
/* Set up to catch coercion errors of the ON EMPTY value. */
4421+
jsestate->escontext.error_occurred = false;
4422+
jsestate->escontext.details_wanted = true;
44134423
Assert(jsestate->jump_empty >= 0);
44144424
return jsestate->jump_empty;
44154425
}
44164426
}
44174427
else if (jsexpr->on_error->btype != JSON_BEHAVIOR_ERROR)
44184428
{
44194429
jsestate->error.value = BoolGetDatum(true);
4430+
/* Set up to catch coercion errors of the ON ERROR value. */
4431+
jsestate->escontext.error_occurred = false;
4432+
jsestate->escontext.details_wanted = true;
44204433
Assert(!throw_error && jsestate->jump_error >= 0);
44214434
return jsestate->jump_error;
44224435
}
@@ -4442,6 +4455,9 @@ ExecEvalJsonExprPath(ExprState *state, ExprEvalStep *op,
44424455
*op->resvalue = (Datum) 0;
44434456
*op->resnull = true;
44444457
jsestate->error.value = BoolGetDatum(true);
4458+
/* Set up to catch coercion errors of the ON ERROR value. */
4459+
jsestate->escontext.error_occurred = false;
4460+
jsestate->escontext.details_wanted = true;
44454461
return jsestate->jump_error;
44464462
}
44474463

@@ -4544,9 +4560,33 @@ ExecEvalJsonCoercion(ExprState *state, ExprEvalStep *op,
45444560
(Node *) escontext);
45454561
}
45464562

4563+
static char *
4564+
GetJsonBehaviorValueString(JsonBehavior *behavior)
4565+
{
4566+
/*
4567+
* The order of array elements must correspond to the order of
4568+
* JsonBehaviorType members.
4569+
*/
4570+
const char *behavior_names[] =
4571+
{
4572+
"NULL",
4573+
"ERROR",
4574+
"EMPTY",
4575+
"TRUE",
4576+
"FALSE",
4577+
"UNKNOWN",
4578+
"EMPTY ARRAY",
4579+
"EMPTY OBJECT",
4580+
"DEFAULT"
4581+
};
4582+
4583+
return pstrdup(behavior_names[behavior->btype]);
4584+
}
4585+
45474586
/*
45484587
* Checks if an error occurred in ExecEvalJsonCoercion(). If so, this sets
4549-
* JsonExprState.error to trigger the ON ERROR handling steps.
4588+
* JsonExprState.error to trigger the ON ERROR handling steps, unless the
4589+
* error is thrown when coercing a JsonBehavior value.
45504590
*/
45514591
void
45524592
ExecEvalJsonCoercionFinish(ExprState *state, ExprEvalStep *op)
@@ -4555,15 +4595,37 @@ ExecEvalJsonCoercionFinish(ExprState *state, ExprEvalStep *op)
45554595

45564596
if (SOFT_ERROR_OCCURRED(&jsestate->escontext))
45574597
{
4598+
/*
4599+
* jsestate->error or jsetate->empty being set means that the error
4600+
* occurred when coercing the JsonBehavior value. Throw the error in
4601+
* that case with the actual coercion error message shown in the
4602+
* DETAIL part.
4603+
*/
4604+
if (DatumGetBool(jsestate->error.value))
4605+
ereport(ERROR,
4606+
(errcode(ERRCODE_DATATYPE_MISMATCH),
4607+
errmsg("could not coerce ON ERROR expression (%s) to the RETURNING type",
4608+
GetJsonBehaviorValueString(jsestate->jsexpr->on_error)),
4609+
errdetail("%s", jsestate->escontext.error_data->message)));
4610+
else if (DatumGetBool(jsestate->empty.value))
4611+
ereport(ERROR,
4612+
(errcode(ERRCODE_DATATYPE_MISMATCH),
4613+
errmsg("could not coerce ON EMPTY expression (%s) to the RETURNING type",
4614+
GetJsonBehaviorValueString(jsestate->jsexpr->on_empty)),
4615+
errdetail("%s", jsestate->escontext.error_data->message)));
4616+
45584617
*op->resvalue = (Datum) 0;
45594618
*op->resnull = true;
4619+
45604620
jsestate->error.value = BoolGetDatum(true);
45614621

45624622
/*
45634623
* Reset for next use such as for catching errors when coercing a
45644624
* JsonBehavior expression.
45654625
*/
45664626
jsestate->escontext.error_occurred = false;
4627+
jsestate->escontext.error_occurred = false;
4628+
jsestate->escontext.details_wanted = true;
45674629
}
45684630
}
45694631

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

Lines changed: 2 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -227,11 +227,8 @@ SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$'
227227

228228
SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$'
229229
COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT 'foo'::jsonb_test_domain ON EMPTY));
230-
js1
231-
-----
232-
233-
(1 row)
234-
230+
ERROR: could not coerce ON EMPTY expression (DEFAULT) to the RETURNING type
231+
DETAIL: value for domain jsonb_test_domain violates check constraint "jsonb_test_domain_check"
235232
SELECT * FROM JSON_TABLE(jsonb '{"d1": "H"}', '$'
236233
COLUMNS (js1 jsonb_test_domain PATH '$.a2' DEFAULT 'foo1'::jsonb_test_domain ON EMPTY));
237234
js1

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

Lines changed: 6 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -313,11 +313,8 @@ SELECT JSON_VALUE(jsonb '"2017-02-20"', '$' RETURNING date) + 9;
313313
-- Test NULL checks execution in domain types
314314
CREATE DOMAIN sqljsonb_int_not_null AS int NOT NULL;
315315
SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null);
316-
json_value
317-
------------
318-
319-
(1 row)
320-
316+
ERROR: could not coerce ON ERROR expression (NULL) to the RETURNING type
317+
DETAIL: domain sqljsonb_int_not_null does not allow null values
321318
SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null ERROR ON ERROR);
322319
ERROR: domain sqljsonb_int_not_null does not allow null values
323320
SELECT JSON_VALUE(jsonb 'null', '$' RETURNING sqljsonb_int_not_null DEFAULT 2 ON EMPTY ERROR ON ERROR);
@@ -1035,11 +1032,8 @@ SELECT JSON_QUERY(jsonb '{"a": 1}', '$.a' RETURNING sqljsonb_int_not_null);
10351032
(1 row)
10361033

10371034
SELECT JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING sqljsonb_int_not_null);
1038-
json_query
1039-
------------
1040-
1041-
(1 row)
1042-
1035+
ERROR: could not coerce ON EMPTY expression (NULL) to the RETURNING type
1036+
DETAIL: domain sqljsonb_int_not_null does not allow null values
10431037
SELECT JSON_QUERY(jsonb '{"a": 1}', '$.b' RETURNING sqljsonb_int_not_null ERROR ON EMPTY ERROR ON ERROR);
10441038
ERROR: no SQL/JSON item found for specified path
10451039
-- Test timestamptz passing and output
@@ -1232,11 +1226,8 @@ DROP TABLE test_jsonb_mutability;
12321226
DROP FUNCTION ret_setint;
12331227
CREATE DOMAIN queryfuncs_test_domain AS text CHECK (value <> 'foo');
12341228
SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT 'foo'::queryfuncs_test_domain ON EMPTY);
1235-
json_value
1236-
------------
1237-
1238-
(1 row)
1239-
1229+
ERROR: could not coerce ON EMPTY expression (DEFAULT) to the RETURNING type
1230+
DETAIL: value for domain queryfuncs_test_domain violates check constraint "queryfuncs_test_domain_check"
12401231
SELECT JSON_VALUE(jsonb '{"d1": "H"}', '$.a2' RETURNING queryfuncs_test_domain DEFAULT 'foo1'::queryfuncs_test_domain ON EMPTY);
12411232
json_value
12421233
------------

0 commit comments

Comments
 (0)