On Tue, Jun 30, 2026 at 9:27 PM Amit Langote <[email protected]> wrote: > 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.
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
v2-0001-Enforce-RETURNING-typmod-on-SQL-JSON-DEFAULT-beha.patch
Description: Binary data
