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_mrqyk1oo82ccu4...@mail.gmail.com Backpatch-through: 17 Branch ------ REL_17_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/46798ab1adf34bf57b7585019033db89fb0641e2 Modified Files -------------- src/backend/executor/execExpr.c | 23 ++++++++++ src/backend/executor/execExprInterp.c | 14 ++++++ src/backend/utils/adt/jsonfuncs.c | 19 +++++++++ src/test/regress/expected/sqljson_queryfuncs.out | 54 ++++++++++++++++++++++++ src/test/regress/sql/sqljson_queryfuncs.sql | 18 ++++++++ 5 files changed, 128 insertions(+)
