Enforce RETURNING typmod on SQL/JSON DEFAULT behavior expressions
transformJsonBehavior() coerced an ON EMPTY / ON ERROR DEFAULT
expression only when its type differed from the RETURNING type's OID.
When the base type matched but the RETURNING type carried a type
modifier (e.g. numeric(4,1) or varchar(3)), the coercion that enforces
the typmod was skipped, so the DEFAULT value could violate the
declared type:
SELECT JSON_VALUE(jsonb '{}', '$.a'
RETURNING numeric(4,1) DEFAULT 99999.999 ON EMPTY);
returned 99999.999, which 99999.999::numeric(4,1) would reject; the
value could even be stored into a numeric(4,1) column, as later
coercions trust its already-correct type label.
Fix by also coercing when the RETURNING type has a typmod, except for
a NULL constant. coerce_to_target_type() is a no-op when the typmod
already matches. The matching-OID short-circuit dates to 74c96699be3.
Reported-by: Ewan Young <[email protected]>
Author: Ewan Young <[email protected]>
Discussion:
https://postgr.es/m/CAON2xHPO9f4cAmyGn1mQ=vqos7wn5rz4yoiqudxx78zninz...@mail.gmail.com
Backpatch-through: 17
Branch
------
REL_19_STABLE
Details
-------
https://git.postgresql.org/pg/commitdiff/d30bfcbddca3b08f66d1207265d9ab7d8a7b95c1
Modified Files
--------------
src/backend/parser/parse_expr.c | 11 ++++++++++-
src/test/regress/expected/sqljson_jsontable.out | 16 ++++++++++++++++
src/test/regress/expected/sqljson_queryfuncs.out | 21 +++++++++++++++++++++
src/test/regress/sql/sqljson_jsontable.sql | 9 +++++++++
src/test/regress/sql/sqljson_queryfuncs.sql | 8 ++++++++
5 files changed, 64 insertions(+), 1 deletion(-)
