On Thu, Jul 2, 2026 at 9:24 PM Amit Langote <[email protected]> wrote: > > 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 for picking it up and for the back-patch. LGTM. > > -- > Thanks, Amit Langote -- Regards, Ewan Young
