Hi Ewan On Tue, Jun 30, 2026 at 4:01 PM Ewan Young <[email protected]> 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
