Re: JSON_VALUE/JSON_TABLE DEFAULT expression ignores RETURNING typmod

On Tue, Jun 30, 2026 at 9:27 PM Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> On Tue, Jun 30, 2026 at 4:01 PM Ewan Young <kdbase(dot)hack(at)gmail(dot)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.

Confirmed, and your diagnosis is right. The matching-OID short-circuit
skips the coercion that would enforce the typmod. The fix looks good,
including the const-NULL handling.

Attached is v2, which adds a couple more regression cases on top of
yours (bit(n) and a non-Const DEFAULT). I also slightly edited the
code comment. I plan to commit (down to 17) on Monday barring
objections.

--
Thanks, Amit Langote