Hi Jian, On Mon, Oct 26, 2026 at 9:12 PM jian he <[email protected]> wrote: > > On Wed, Sep 16, 2026 at 11:29 AM Amit Langote <[email protected]> wrote: > > > > Thanks for providing the example. While playing around with that, I > > noticed that you do get error when parentheses are moved: > > > > postgres=# SELECT JSON_VALUE('"a"', '$' RETURNING int DEFAULT ('a' || > > ((random()* 0)::int)::int + 0) ON ERROR); > > ERROR: could not coerce ON ERROR expression (DEFAULT) to the RETURNING type > > DETAIL: invalid input syntax for type integer: "a0" > > > > I haven't checked but I suppose your patch ensures the query you > > shared produces the error like this. > > > > Yes. > > I think > SELECT JSON_VALUE('"a"', '$' RETURNING int DEFAULT (('a' || (random() > * 0)::int)::int + 0) ON ERROR); > > SELECT JSON_VALUE('"a"', '$' RETURNING int DEFAULT (('a' || (random() > * 0)::int)::int ) ON ERROR); > > They should behave the same; the resulting values might differ > (assuming no overflow). > > While working on the CAST DEFAULT patch > (https://commitfest.postgresql.org/patch/5941/), I found another bug related > to > this thread: after a soft error has been reported, we didn't return earlier. > and let the code continues. Since it mostly affects SQL/JSON, I'm posting it > here too. > > CREATE DOMAIN dchk AS int CHECK ((1 / coalesce(VALUE, 0)) IS NOT NULL); > SELECT JSON_VALUE(jsonb '"x"', '$' RETURNING dchk DEFAULT 11 ON ERROR); > SELECT JSON_VALUE(jsonb '"x"', '$' RETURNING int DEFAULT 11 ON ERROR); > > I belive the above should return the same result 11. Currentlty the > first one failed with > ERROR: division by zero > > SELECT JSON_QUERY(jsonb '["x"]', '$' RETURNING dchk[] DEFAULT '{11}' ON > ERROR); > It should return {11} instead of throwing an error. > > So in many places, once SOFT_ERROR_OCCURRED is true, we should return > early or handle the error specially, rather than carry on. > Please review the attached patch commit message, which I believe > explains it clearly (i hope).
Thanks for reminding me of this thread. I agree about the second bug and your proposed fix. I’ve looked at the patch and it seems to be fixing the following two bugs: 1. Lack of a COERCION_FINISH step after ON ERROR/EMPTY expression evaluation in some cases meant the soft error saved during that evaluation was never rethrown and the query silently returned NULL. 2. Once a soft error has occurred, the dummy NULL it leaves behind should not be processed any further, but in a few places (CoerceToDomain, populate_domain(), populate_record(), ExecEvalJsonCoercion() called on the DEFAULT result) it currently is. I decided to break it down into attached two patches along those lines. -- Thanks, Amit Langote
From 8a8bf296f167873fd20aa3610ed091861b66733e Mon Sep 17 00:00:00 2001 From: Amit Langote <[email protected]> Date: Tue, 27 Oct 2026 21:50:59 +0900 Subject: [PATCH v3 2/2] SQL/JSON: fix error handling when coercing to a domain fails softly SQL/JSON query functions could throw an error, or report the wrong one, in cases where the ON ERROR or ON EMPTY clause said what to do instead. This happened when the RETURNING type, or a DEFAULT expression, involved a domain: if the value failed to convert to the domain's base type, that failure was saved to be handled by the ON ERROR behavior, but the domain's constraints were then checked anyway, against the dummy NULL left behind by the failed conversion. A CHECK expression can throw a hard error on that NULL, which ON ERROR cannot catch, and a NOT NULL constraint reports a violation that overwrites the saved error, so the error message ends up describing the wrong problem. For example, this failed with "division by zero" instead of returning 11: CREATE DOMAIN d AS int CHECK (1 / coalesce(VALUE, 0) = 0); SELECT JSON_VALUE(jsonb '"x"', '$' RETURNING d DEFAULT 11 ON ERROR); Fix by stopping as soon as a soft error has been saved, in each of the places that kept going: * The domain constraint checks of a CoerceToDomain in an ON ERROR or ON EMPTY DEFAULT expression, which is the only case where they are evaluated softly. ExecInitCoerceToDomain now emits a jump before each constraint check that skips the remaining checks if an error has already been saved. * populate_domain, called from json_populate_type when coercing to a domain. Return as soon as the conversion to the base type fails, instead of going on to check the domain's constraints. * populate_record, likewise. Stop populating the remaining columns as soon as one of them fails. * ExecEvalJsonCoercion. Return at once if the DEFAULT expression whose result it would coerce has already failed, and leave throwing the error to the COERCION_FINISH step that follows. Reported-by: jian he <[email protected]> Author: jian he <[email protected]> Discussion: https://postgr.es/m/CACJufxHDK39zF+1zZF6prH_wtn1SDt74_mrqYk1Oo82cCu44ng@mail.gmail.com Backpatch-through: 17 --- src/backend/executor/execExpr.c | 23 ++++++++ src/backend/executor/execExprInterp.c | 15 ++++++ src/backend/utils/adt/jsonfuncs.c | 19 +++++++ .../regress/expected/sqljson_queryfuncs.out | 54 +++++++++++++++++++ src/test/regress/sql/sqljson_queryfuncs.sql | 18 +++++++ 5 files changed, 129 insertions(+) diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c index 5b8aac1d397..dfb6a3d2e5c 100644 --- a/src/backend/executor/execExpr.c +++ b/src/backend/executor/execExpr.c @@ -3520,6 +3520,7 @@ ExecInitCoerceToDomain(ExprEvalStep *scratch, CoerceToDomain *ctest, Datum *domainval = NULL; bool *domainnull = NULL; ListCell *l; + List *skipjumps = NIL; scratch->d.domaincheck.resulttype = ctest->resulttype; /* we'll allocate workspace only if needed */ @@ -3570,6 +3571,24 @@ ExecInitCoerceToDomain(ExprEvalStep *scratch, CoerceToDomain *ctest, scratch->d.domaincheck.constraintname = con->name; + /* + * If this CoerceToDomain is being evaluated softly and its argument, + * or a constraint checked before this one, has already failed, the + * result is a dummy NULL that the caller will discard, so don't check + * the remaining constraints against it; doing so might throw a hard + * error or overwrite the saved error. + */ + if (state->escontext != NULL) + { + ExprEvalStep jump = {0}; + + jump.opcode = EEOP_JUMP_IF_NULL; + jump.resnull = &state->escontext->error_occurred; + jump.d.jump.jumpdone = -1; /* set below */ + skipjumps = lappend_int(skipjumps, state->steps_len); + ExprEvalPushStep(state, &jump); + } + switch (con->constrainttype) { case DOM_CONSTRAINT_NOTNULL: @@ -3650,6 +3669,10 @@ ExecInitCoerceToDomain(ExprEvalStep *scratch, CoerceToDomain *ctest, break; } } + + /* Now that we know where the constraint steps end, fix up the jumps. */ + foreach_int(jumpno, skipjumps) + state->steps[jumpno].d.jump.jumpdone = state->steps_len; } /* diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c index 397219f7a3a..f9fcfca46bd 100644 --- a/src/backend/executor/execExprInterp.c +++ b/src/backend/executor/execExprInterp.c @@ -5188,6 +5188,21 @@ ExecEvalJsonCoercion(ExprState *state, ExprEvalStep *op, { ErrorSaveContext *escontext = op->d.jsonexpr_coercion.escontext; + /* + * An ON ERROR or ON EMPTY DEFAULT expression is evaluated with escontext + * in effect, so if it failed, the error has been saved in escontext and + * the value we were given to coerce is a dummy NULL. Return it as is; + * the COERCION_FINISH step that follows will throw the saved error, and + * running the coercion on the NULL might instead throw a hard error or + * overwrite that saved error. + */ + if (SOFT_ERROR_OCCURRED(escontext)) + { + *op->resnull = true; + *op->resvalue = (Datum) 0; + return; + } + /* * Prepare to call json_populate_type() to coerce the boolean result of * JSON_EXISTS_OP to the target type. If the target type is integer or a diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c index 274ed9d3184..1d3c8c0865a 100644 --- a/src/backend/utils/adt/jsonfuncs.c +++ b/src/backend/utils/adt/jsonfuncs.c @@ -3233,6 +3233,17 @@ populate_domain(DomainIOData *io, colname, mcxt, PointerGetDatum(NULL), jsv, isnull, escontext, omit_quotes); Assert(!*isnull || SOFT_ERROR_OCCURRED(escontext)); + + /* + * If converting to the base type failed softly, the caller is going + * to discard the result, so don't check the domain's constraints on + * it; doing so might throw a hard error or overwrite the saved error. + */ + if (SOFT_ERROR_OCCURRED(escontext)) + { + *isnull = true; + return (Datum) 0; + } } if (!domain_check_safe(res, *isnull, typid, &io->domain_info, mcxt, @@ -3616,6 +3627,14 @@ populate_record(TupleDesc tupdesc, &nulls[i], escontext, false); + + /* + * Once a column has failed softly, the caller is going to discard the + * whole tuple, so don't populate the remaining columns; doing so + * might throw a hard error or overwrite the saved error. + */ + if (SOFT_ERROR_OCCURRED(escontext)) + break; } res = heap_form_tuple(tupdesc, values, nulls); diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out index a99429a08e6..209fb3382c3 100644 --- a/src/test/regress/expected/sqljson_queryfuncs.out +++ b/src/test/regress/expected/sqljson_queryfuncs.out @@ -1532,6 +1532,60 @@ DETAIL: invalid input syntax for type integer: "a0" SELECT JSON_QUERY('"a"', '$' RETURNING int DEFAULT (('a' || (random() * 0)::int)::int + 0) ON ERROR); ERROR: could not coerce ON ERROR expression (DEFAULT) to the RETURNING type DETAIL: invalid input syntax for type integer: "a0" +-- error. should report that the DEFAULT expression cannot be coerced to the +-- RETURNING type, not a NOT NULL violation +SELECT JSON_VALUE(jsonb '{"a": 1}', '$' RETURNING sqljsonb_int_not_null DEFAULT concat('y') ON ERROR); +ERROR: could not coerce ON ERROR expression (DEFAULT) to the RETURNING type +DETAIL: invalid input syntax for type integer: "y" +-- domain constraints must not be checked once a soft error has already occurred +CREATE DOMAIN queryfuncs_int AS int CHECK (1 / coalesce(VALUE, 0) = 0); +SELECT JSON_VALUE(jsonb '"x"', '$' RETURNING int DEFAULT 11 ON ERROR); + json_value +------------ + 11 +(1 row) + +SELECT JSON_QUERY(jsonb '["x"]', '$' RETURNING int[] DEFAULT '{11}' ON ERROR); + json_query +------------ + {11} +(1 row) + +SELECT JSON_VALUE(jsonb '"x"', '$' RETURNING queryfuncs_int DEFAULT 11 ON ERROR); + json_value +------------ + 11 +(1 row) + +SELECT JSON_QUERY(jsonb '["x"]', '$' RETURNING queryfuncs_int[] DEFAULT '{11}' ON ERROR); + json_query +------------ + {11} +(1 row) + +CREATE TYPE compcoalesce AS (a int, b queryfuncs_int); +SELECT JSON_QUERY(jsonb '{"a": "x"}', '$' RETURNING compcoalesce); + json_query +------------ + +(1 row) + +SELECT JSON_QUERY(jsonb '{"a": 1, "b": 11}', '$' RETURNING compcoalesce); + json_query +------------ + (1,11) +(1 row) + +SELECT JSON_QUERY(jsonb '{"a": 1, "b": "B"}', '$' RETURNING compcoalesce); + json_query +------------ + +(1 row) + +SELECT JSON_QUERY(jsonb '{"a": 1, "b": 0}', '$' RETURNING compcoalesce); -- error +ERROR: division by zero +DROP TYPE compcoalesce; +DROP DOMAIN queryfuncs_int; -- Test implicit coercion to a domain over fixed-length type specified in -- RETURNING CREATE DOMAIN queryfuncs_char2 AS char(2); diff --git a/src/test/regress/sql/sqljson_queryfuncs.sql b/src/test/regress/sql/sqljson_queryfuncs.sql index 9b81429b4bc..d4ba4c710db 100644 --- a/src/test/regress/sql/sqljson_queryfuncs.sql +++ b/src/test/regress/sql/sqljson_queryfuncs.sql @@ -516,6 +516,24 @@ SELECT JSON_VALUE('"a"', '$' RETURNING int DEFAULT (NULL::sqljsonb_int_not_null SELECT JSON_QUERY('"a"', '$' RETURNING int DEFAULT (NULL::sqljsonb_int_not_null + 0) ON ERROR); SELECT JSON_VALUE('"a"', '$' RETURNING int DEFAULT (('a' || (random() * 0)::int)::int + 0) ON ERROR); SELECT JSON_QUERY('"a"', '$' RETURNING int DEFAULT (('a' || (random() * 0)::int)::int + 0) ON ERROR); +-- error. should report that the DEFAULT expression cannot be coerced to the +-- RETURNING type, not a NOT NULL violation +SELECT JSON_VALUE(jsonb '{"a": 1}', '$' RETURNING sqljsonb_int_not_null DEFAULT concat('y') ON ERROR); + +-- domain constraints must not be checked once a soft error has already occurred +CREATE DOMAIN queryfuncs_int AS int CHECK (1 / coalesce(VALUE, 0) = 0); +SELECT JSON_VALUE(jsonb '"x"', '$' RETURNING int DEFAULT 11 ON ERROR); +SELECT JSON_QUERY(jsonb '["x"]', '$' RETURNING int[] DEFAULT '{11}' ON ERROR); +SELECT JSON_VALUE(jsonb '"x"', '$' RETURNING queryfuncs_int DEFAULT 11 ON ERROR); +SELECT JSON_QUERY(jsonb '["x"]', '$' RETURNING queryfuncs_int[] DEFAULT '{11}' ON ERROR); + +CREATE TYPE compcoalesce AS (a int, b queryfuncs_int); +SELECT JSON_QUERY(jsonb '{"a": "x"}', '$' RETURNING compcoalesce); +SELECT JSON_QUERY(jsonb '{"a": 1, "b": 11}', '$' RETURNING compcoalesce); +SELECT JSON_QUERY(jsonb '{"a": 1, "b": "B"}', '$' RETURNING compcoalesce); +SELECT JSON_QUERY(jsonb '{"a": 1, "b": 0}', '$' RETURNING compcoalesce); -- error +DROP TYPE compcoalesce; +DROP DOMAIN queryfuncs_int; -- Test implicit coercion to a domain over fixed-length type specified in -- RETURNING -- 2.47.3
From 8455f4d2c022f0b311b9a13cb1acd08002273a21 Mon Sep 17 00:00:00 2001 From: Amit Langote <[email protected]> Date: Tue, 27 Oct 2026 22:02:03 +0900 Subject: [PATCH v3 1/2] SQL/JSON: always rethrow soft errors from DEFAULT expressions The DEFAULT expression of an ON ERROR or ON EMPTY clause is compiled with the JsonExpr's ErrorSaveContext in effect, so that any error raised while evaluating it is saved rather than thrown and can be reported later as "could not coerce ON ERROR expression (DEFAULT) to the RETURNING type". That reporting is done by an EEOP_JSONEXPR_COERCION_FINISH step, but ExecInitJsonExpr only added that step when the DEFAULT expression needed a runtime coercion, or when its top-level node was a CoerceViaIO or a CoerceToDomain. Those are not the only nodes that can save an error: the ErrorSaveContext applies to the whole expression tree, so a cast nested inside, say, an OpExpr saves its error just the same. In that case there was nothing to rethrow it and the query silently returned NULL instead of failing, for example: SELECT JSON_VALUE('"a"', '$' RETURNING int DEFAULT (('a' || (random() * 0)::int)::int + 0) ON ERROR); Fix by adding the COERCION_FINISH step after every DEFAULT expression that is evaluated softly, that is, whenever the ON ERROR behavior is not ERROR. Reported-by: jian he <[email protected]> Author: jian he <[email protected]> Reviewed-by: Amit Langote <[email protected]> Discussion: https://postgr.es/m/CACJufxHDK39zF+1zZF6prH_wtn1SDt74_mrqYk1Oo82cCu44ng@mail.gmail.com Backpatch-through: 17 --- src/backend/executor/execExpr.c | 19 ++++++++----------- .../regress/expected/sqljson_jsontable.out | 13 +++++++++++++ .../regress/expected/sqljson_queryfuncs.out | 17 +++++++++++++++++ src/test/regress/sql/sqljson_jsontable.sql | 5 +++++ src/test/regress/sql/sqljson_queryfuncs.sql | 8 ++++++++ 5 files changed, 51 insertions(+), 11 deletions(-) diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c index 3f063b79f4d..5b8aac1d397 100644 --- a/src/backend/executor/execExpr.c +++ b/src/backend/executor/execExpr.c @@ -4948,12 +4948,11 @@ ExecInitJsonExpr(JsonExpr *jsexpr, ExprState *state, resv, resnull); /* - * Add a COERCION_FINISH step to check for errors that may occur when - * coercing and rethrow them. + * If the ON ERROR expression was evaluated softly, add a + * COERCION_FINISH step to throw any error saved while evaluating it + * or coercing its result. */ - if (jsexpr->on_error->coerce || - IsA(jsexpr->on_error->expr, CoerceViaIO) || - IsA(jsexpr->on_error->expr, CoerceToDomain)) + if (escontext) { scratch->opcode = EEOP_JSONEXPR_COERCION_FINISH; scratch->resvalue = resv; @@ -5011,14 +5010,12 @@ ExecInitJsonExpr(JsonExpr *jsexpr, ExprState *state, resv, resnull); /* - * Add a COERCION_FINISH step to check for errors that may occur when - * coercing and rethrow them. + * If the ON EMPTY expression was evaluated softly, add a + * COERCION_FINISH step to throw any error saved while evaluating it + * or coercing its result. */ - if (jsexpr->on_empty->coerce || - IsA(jsexpr->on_empty->expr, CoerceViaIO) || - IsA(jsexpr->on_empty->expr, CoerceToDomain)) + if (escontext) { - scratch->opcode = EEOP_JSONEXPR_COERCION_FINISH; scratch->resvalue = resv; scratch->resnull = resnull; diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out index ae64dbed303..d6a22ba8205 100644 --- a/src/test/regress/expected/sqljson_jsontable.out +++ b/src/test/regress/expected/sqljson_jsontable.out @@ -672,6 +672,19 @@ SELECT a, a::bool FROM JSON_TABLE(jsonb '{"a":1}', '$' COLUMNS (a dint4_0 EXISTS 1 | t (1 row) +-- soft errors in the ON EMPTY expression must be rethrown +SELECT * FROM JSON_TABLE('{}', '$' COLUMNS (a int PATH '$.a' DEFAULT (0::dint4_0 + 0) ON EMPTY)); +ERROR: could not coerce ON EMPTY expression (DEFAULT) to the RETURNING type +DETAIL: value for domain dint4_0 violates check constraint "dint4_0_check" +SELECT * FROM JSON_TABLE('{}', '$' COLUMNS (a int PATH '$.a' KEEP QUOTES DEFAULT (0::dint4_0 + 0) ON EMPTY)); +ERROR: could not coerce ON EMPTY expression (DEFAULT) to the RETURNING type +DETAIL: value for domain dint4_0 violates check constraint "dint4_0_check" +SELECT * FROM JSON_TABLE('{}', '$' COLUMNS (a int PATH '$.a' DEFAULT (('a' || (random() * 0)::int)::int + 0) ON EMPTY)); +ERROR: could not coerce ON EMPTY expression (DEFAULT) to the RETURNING type +DETAIL: invalid input syntax for type integer: "a0" +SELECT * FROM JSON_TABLE('{}', '$' COLUMNS (a int PATH '$.a' KEEP QUOTES DEFAULT (('a' || (random() * 0)::int)::int + 0) ON EMPTY)); +ERROR: could not coerce ON EMPTY expression (DEFAULT) to the RETURNING type +DETAIL: invalid input syntax for type integer: "a0" DROP DOMAIN dint4, dint4_0; -- JSON_TABLE: WRAPPER/QUOTES clauses on scalar columns SELECT * FROM JSON_TABLE(jsonb '"world"', '$' COLUMNS (item text PATH '$' KEEP QUOTES ON SCALAR STRING)); diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out index fe8aa927b5e..a99429a08e6 100644 --- a/src/test/regress/expected/sqljson_queryfuncs.out +++ b/src/test/regress/expected/sqljson_queryfuncs.out @@ -1515,6 +1515,23 @@ ERROR: invalid ON ERROR behavior LINE 1: SELECT JSON_QUERY(jsonb '1', '$' TRUE ON ERROR); ^ DETAIL: Only ERROR, NULL, EMPTY ARRAY, EMPTY OBJECT, or DEFAULT expression is allowed in ON ERROR for JSON_QUERY(). +-- soft errors raised by the ON ERROR or ON EMPTY DEFAULT expression must always +-- be rethrown +SELECT JSON_VALUE('"a"', '$' RETURNING sqljsonb_int_not_null DEFAULT NULL ON ERROR); +ERROR: could not coerce ON ERROR expression (DEFAULT) to the RETURNING type +DETAIL: domain sqljsonb_int_not_null does not allow null values +SELECT JSON_VALUE('"a"', '$' RETURNING int DEFAULT (NULL::sqljsonb_int_not_null + 0) ON ERROR); +ERROR: could not coerce ON ERROR expression (DEFAULT) to the RETURNING type +DETAIL: domain sqljsonb_int_not_null does not allow null values +SELECT JSON_QUERY('"a"', '$' RETURNING int DEFAULT (NULL::sqljsonb_int_not_null + 0) ON ERROR); +ERROR: could not coerce ON ERROR expression (DEFAULT) to the RETURNING type +DETAIL: domain sqljsonb_int_not_null does not allow null values +SELECT JSON_VALUE('"a"', '$' RETURNING int DEFAULT (('a' || (random() * 0)::int)::int + 0) ON ERROR); +ERROR: could not coerce ON ERROR expression (DEFAULT) to the RETURNING type +DETAIL: invalid input syntax for type integer: "a0" +SELECT JSON_QUERY('"a"', '$' RETURNING int DEFAULT (('a' || (random() * 0)::int)::int + 0) ON ERROR); +ERROR: could not coerce ON ERROR expression (DEFAULT) to the RETURNING type +DETAIL: invalid input syntax for type integer: "a0" -- Test implicit coercion to a domain over fixed-length type specified in -- RETURNING CREATE DOMAIN queryfuncs_char2 AS char(2); diff --git a/src/test/regress/sql/sqljson_jsontable.sql b/src/test/regress/sql/sqljson_jsontable.sql index 2a33aaec57f..7f0b54b1121 100644 --- a/src/test/regress/sql/sqljson_jsontable.sql +++ b/src/test/regress/sql/sqljson_jsontable.sql @@ -311,6 +311,11 @@ SELECT a, a::bool FROM JSON_TABLE(jsonb '{"a":1}', '$' COLUMNS (a dint4_0 EXISTS SELECT a, a::bool FROM JSON_TABLE(jsonb '{"a":1}', '$' COLUMNS (a dint4_0 EXISTS PATH '$.b' ERROR ON ERROR)); SELECT a, a::bool FROM JSON_TABLE(jsonb '{"a":1}', '$' COLUMNS (a dint4_0 EXISTS PATH '$.b' FALSE ON ERROR)); SELECT a, a::bool FROM JSON_TABLE(jsonb '{"a":1}', '$' COLUMNS (a dint4_0 EXISTS PATH '$.b' TRUE ON ERROR)); +-- soft errors in the ON EMPTY expression must be rethrown +SELECT * FROM JSON_TABLE('{}', '$' COLUMNS (a int PATH '$.a' DEFAULT (0::dint4_0 + 0) ON EMPTY)); +SELECT * FROM JSON_TABLE('{}', '$' COLUMNS (a int PATH '$.a' KEEP QUOTES DEFAULT (0::dint4_0 + 0) ON EMPTY)); +SELECT * FROM JSON_TABLE('{}', '$' COLUMNS (a int PATH '$.a' DEFAULT (('a' || (random() * 0)::int)::int + 0) ON EMPTY)); +SELECT * FROM JSON_TABLE('{}', '$' COLUMNS (a int PATH '$.a' KEEP QUOTES DEFAULT (('a' || (random() * 0)::int)::int + 0) ON EMPTY)); DROP DOMAIN dint4, dint4_0; -- JSON_TABLE: WRAPPER/QUOTES clauses on scalar columns diff --git a/src/test/regress/sql/sqljson_queryfuncs.sql b/src/test/regress/sql/sqljson_queryfuncs.sql index 0767db9cfff..9b81429b4bc 100644 --- a/src/test/regress/sql/sqljson_queryfuncs.sql +++ b/src/test/regress/sql/sqljson_queryfuncs.sql @@ -509,6 +509,14 @@ SELECT JSON_EXISTS(jsonb '1', '$' DEFAULT 1 ON ERROR); SELECT JSON_VALUE(jsonb '1', '$' EMPTY ON ERROR); SELECT JSON_QUERY(jsonb '1', '$' TRUE ON ERROR); +-- soft errors raised by the ON ERROR or ON EMPTY DEFAULT expression must always +-- be rethrown +SELECT JSON_VALUE('"a"', '$' RETURNING sqljsonb_int_not_null DEFAULT NULL ON ERROR); +SELECT JSON_VALUE('"a"', '$' RETURNING int DEFAULT (NULL::sqljsonb_int_not_null + 0) ON ERROR); +SELECT JSON_QUERY('"a"', '$' RETURNING int DEFAULT (NULL::sqljsonb_int_not_null + 0) ON ERROR); +SELECT JSON_VALUE('"a"', '$' RETURNING int DEFAULT (('a' || (random() * 0)::int)::int + 0) ON ERROR); +SELECT JSON_QUERY('"a"', '$' RETURNING int DEFAULT (('a' || (random() * 0)::int)::int + 0) ON ERROR); + -- Test implicit coercion to a domain over fixed-length type specified in -- RETURNING CREATE DOMAIN queryfuncs_char2 AS char(2); -- 2.47.3
