Thread

1. JSON_VALUE/JSON_TABLE DEFAULT expression ignores RETURNING typmod

   Ewan Young <kdbase.hack@gmail.com> — 2026-06-30T07:01:31Z

   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).
   
   -- 
   Regards,
   Ewan Young
   

2. Re: JSON_VALUE/JSON_TABLE DEFAULT expression ignores RETURNING typmod

   Amit Langote <amitlangote09@gmail.com> — 2026-06-30T12:27:22Z

   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
   
   
   
   

3. Re: JSON_VALUE/JSON_TABLE DEFAULT expression ignores RETURNING typmod

   Amit Langote <amitlangote09@gmail.com> — 2026-07-02T13:24:10Z

   On Tue, Jun 30, 2026 at 9:27 PM Amit Langote <amitlangote09@gmail.com> wrote:
   > 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.
   
   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
   

4. Re: JSON_VALUE/JSON_TABLE DEFAULT expression ignores RETURNING typmod

   Ewan Young <kdbase.hack@gmail.com> — 2026-07-03T02:14:12Z

   On Thu, Jul 2, 2026 at 9:24 PM Amit Langote <amitlangote09@gmail.com> wrote:
   >
   > On Tue, Jun 30, 2026 at 9:27 PM Amit Langote <amitlangote09@gmail.com> wrote:
   > > 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.
   >
   > 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
   
   
   
   

5. Re: JSON_VALUE/JSON_TABLE DEFAULT expression ignores RETURNING typmod

   Amit Langote <amitlangote09@gmail.com> — 2026-07-06T23:52:03Z

   On Fri, Jul 3, 2026 at 11:14 AM Ewan Young <kdbase.hack@gmail.com> wrote:
   > On Thu, Jul 2, 2026 at 9:24 PM Amit Langote <amitlangote09@gmail.com> wrote:
   > >
   > > On Tue, Jun 30, 2026 at 9:27 PM Amit Langote <amitlangote09@gmail.com> wrote:
   > > > 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.
   > >
   > > 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.
   
   Pushed.
   
   -- 
   Thanks, Amit Langote