Re: JSON_VALUE/JSON_TABLE DEFAULT expression ignores RETURNING typmod

Hi Ewan

On Tue, Jun 30, 2026 at 4:01 PM Ewan Young <kdbase.hack@gmail.com> wrote:
>
> Hi,
>
> While testing SQL/JSON on master I noticed that a DEFAULT expression in
> JSON_VALUE / JSON_TABLE (ON EMPTY / ON ERROR) is not coerced to the
> RETURNING type's type modifier when the expression's base type already
> matches the RETURNING base type. The declared typmod is silently
> dropped, so the result can violate its own declared type:
>
> SELECT JSON_VALUE(jsonb '{}', '$.a'
>                   RETURNING numeric(4,1) DEFAULT 99999.999 ON EMPTY);
>  json_value
> ------------
>   99999.999
>
> even though the equivalent cast is rejected:
>
> SELECT 99999.999::numeric(4,1);
> ERROR:  numeric field overflow
>
> The same happens for varchar(n), bit(n), timestamp(p), and for
> JSON_TABLE column DEFAULTs.
>
> It is not limited to a query-time wrong result: because the returned
> Datum is labeled numeric(4,1) but holds an out-of-range value, a later
> assignment cast that sees the matching type trusts the label and skips
> re-checking, so the value can be stored into a column whose typmod it
> violates:
>
> CREATE TABLE sink (c numeric(4,1));
> INSERT INTO sink VALUES (99999.999);              -- ERROR: numeric
> field overflow
> INSERT INTO sink
>   SELECT JSON_VALUE(jsonb '{}', '$.a'
>                     RETURNING numeric(4,1) DEFAULT 99999.999 ON
> EMPTY);  -- succeeds
> SELECT * FROM sink;                                -- 99999.999
>
> Root cause is in transformJsonBehavior() (parse_expr.c), which gates the
> DEFAULT coercion on a type-OID mismatch only:
>
> if (expr && exprType(expr) != returning->typid)
>
> The coerce_to_target_type() call inside that branch is what enforces the
> typmod, so when the base type matches but the typmod differ
> coercion is skipped entirely. (A DEFAULT whose type differs, e.g.
> DEFAULT 99999 :: int, is coerced and correctly errors; a DO
> numeric(4,1) also errors, since its OID differs.) The matching-OID
> short-circuit dates back to 74c96699be3.
>
> The attached patch coerces when the RETURNING type carries
> well, excluding a NULL constant (which needs no enforcement).
> coerce_to_target_type() is a no-op when the typmod already
> conforming expressions are unaffected, and the jsonb-valued / NULL /
> boolean runtime-coercion path (json_populate_type()) alread
> typmod -- this only closes the gap in the parse-time cast path.
>
> Note this is distinct from c0fc0751862, which fixed which expression
> kinds are accepted in DEFAULT, not typmod enforcement.
>
> make check passes with the added regression cases. Reproduc
> fix verified on master (c776550e466).

Thanks for the report and the patch.  And also for adding me, though I
am not sure why Peter was also added. AFAIK, this one is on me.

I'll try to take a look this week.


--
Thanks, Amit Langote