v1-0001-Enforce-RETURNING-typmod-on-SQL-JSON-DEFAULT-beha.patch

From b59e8cd0c2c50c79ef74c320d0d2a61b3d34f724 Mon Sep 17 00:00:00 2001
From: Ewan Young <kdbase.hack@gmail.com>
Date: Tue, 30 Jun 2026 22:40:27 +0800
Subject: [PATCH v1] Enforce RETURNING typmod on SQL/JSON DEFAULT behavior
 expressions

transformJsonBehavior() coerced an ON EMPTY / ON ERROR DEFAULT
expression only when its type differed from the RETURNING type's OID.
When the base type matched but the RETURNING type carried a type
modifier (e.g. numeric(4,1) or varchar(3)), the coercion that enforces
the typmod was skipped, so the DEFAULT value could violate the declared
type:

    SELECT JSON_VALUE(jsonb '{}', '$.a'
                      RETURNING numeric(4,1) DEFAULT 99999.999 ON EMPTY);

returned 99999.999, which 99999.999::numeric(4,1) would reject; the value
could even be stored into a numeric(4,1) column, as later coercions trust
its already-correct type label.

Fix by also coercing when the RETURNING type has a typmod, except for a
NULL constant.  coerce_to_target_type() is a no-op when the typmod
already matches.  The matching-OID short-circuit dates to 74c96699be3.
---
 src/backend/parser/parse_expr.c                  | 12 +++++++++++-
 src/test/regress/expected/sqljson_jsontable.out  |  6 ++++++
 src/test/regress/expected/sqljson_queryfuncs.out | 13 +++++++++++++
 src/test/regress/sql/sqljson_jsontable.sql       |  5 +++++
 src/test/regress/sql/sqljson_queryfuncs.sql      |  6 ++++++
 5 files changed, 41 insertions(+), 1 deletion(-)

diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 9adc9d4c0f6..c0717520de4 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -4931,8 +4931,18 @@ transformJsonBehavior(ParseState *pstate, JsonExpr *jsexpr,
 	 *
 	 * For other non-NULL expressions, try to find a cast and error out if one
 	 * is not found.
+	 *
+	 * We must coerce even when the expression's type already matches the
+	 * RETURNING type's base type, as long as the RETURNING type carries a
+	 * type modifier (e.g. numeric(4,1) or varchar(3)).  The cast below is
+	 * what enforces the typmod, so skipping it for the matching-type case
+	 * would let a DEFAULT expression yield a value that violates the declared
+	 * RETURNING type.  (A NULL constant needs no typmod enforcement.)
 	 */
-	if (expr && exprType(expr) != returning->typid)
+	if (expr &&
+		(exprType(expr) != returning->typid ||
+		 (returning->typmod >= 0 &&
+		  !(IsA(expr, Const) && ((Const *) expr)->constisnull))))
 	{
 		bool		isnull = (IsA(expr, Const) && ((Const *) expr)->constisnull);
 
diff --git a/src/test/regress/expected/sqljson_jsontable.out b/src/test/regress/expected/sqljson_jsontable.out
index 458c5aaa5b0..8be1bc58e2b 100644
--- a/src/test/regress/expected/sqljson_jsontable.out
+++ b/src/test/regress/expected/sqljson_jsontable.out
@@ -250,6 +250,12 @@ SELECT * FROM JSON_TABLE(jsonb '{"d1": "foo"}', '$'
  {1}
 (1 row)
 
+-- A DEFAULT expression whose base type matches the column type must still be
+-- coerced to the column's typmod.
+SELECT * FROM JSON_TABLE(jsonb '{}', '$'
+    COLUMNS (c numeric(4,1) PATH '$.x' DEFAULT 99999.999 ON EMPTY));
+ERROR:  numeric field overflow
+DETAIL:  A field with precision 4, scale 1 must round to an absolute value less than 10^3.
 -- JSON_TABLE: Test backward parsing
 CREATE VIEW jsonb_table_view2 AS
 SELECT * FROM
diff --git a/src/test/regress/expected/sqljson_queryfuncs.out b/src/test/regress/expected/sqljson_queryfuncs.out
index 57e52e963f6..ca8f8b3972a 100644
--- a/src/test/regress/expected/sqljson_queryfuncs.out
+++ b/src/test/regress/expected/sqljson_queryfuncs.out
@@ -433,6 +433,19 @@ SELECT JSON_VALUE(jsonb '["1"]', '$[*]' RETURNING int FORMAT JSON); -- RETURNING
 ERROR:  cannot specify FORMAT JSON in RETURNING clause of JSON_VALUE()
 LINE 1: ...CT JSON_VALUE(jsonb '["1"]', '$[*]' RETURNING int FORMAT JSO...
                                                              ^
+-- A DEFAULT expression must be coerced to the RETURNING type's typmod even
+-- when its base type already matches, but a matching NULL needs no coercion.
+SELECT JSON_VALUE(jsonb '{}', '$.a' RETURNING numeric(4,1) DEFAULT 99999.999 ON EMPTY);
+ERROR:  numeric field overflow
+DETAIL:  A field with precision 4, scale 1 must round to an absolute value less than 10^3.
+SELECT JSON_VALUE(jsonb '{}', '$.a' RETURNING varchar(3) DEFAULT 'toolong'::varchar(10) ON EMPTY);
+ERROR:  value too long for type character varying(3)
+SELECT JSON_VALUE(jsonb '{}', '$.a' RETURNING numeric(4,1) DEFAULT NULL::numeric ON EMPTY);
+ json_value 
+------------
+           
+(1 row)
+
 -- RETUGNING pseudo-types not allowed
 SELECT JSON_VALUE(jsonb '["1"]', '$[*]' RETURNING record);
 ERROR:  returning pseudo-types is not supported in SQL/JSON functions
diff --git a/src/test/regress/sql/sqljson_jsontable.sql b/src/test/regress/sql/sqljson_jsontable.sql
index 154eea79c76..5bd041b9658 100644
--- a/src/test/regress/sql/sqljson_jsontable.sql
+++ b/src/test/regress/sql/sqljson_jsontable.sql
@@ -132,6 +132,11 @@ SELECT * FROM JSON_TABLE(jsonb '{"d1": "foo"}', '$'
 SELECT * FROM JSON_TABLE(jsonb '{"d1": "foo"}', '$'
     COLUMNS (js1 oid[] PATH '$.d2' DEFAULT '{1}'::int[]::oid[] ON EMPTY));
 
+-- A DEFAULT expression whose base type matches the column type must still be
+-- coerced to the column's typmod.
+SELECT * FROM JSON_TABLE(jsonb '{}', '$'
+    COLUMNS (c numeric(4,1) PATH '$.x' DEFAULT 99999.999 ON EMPTY));
+
 -- JSON_TABLE: Test backward parsing
 
 CREATE VIEW jsonb_table_view2 AS
diff --git a/src/test/regress/sql/sqljson_queryfuncs.sql b/src/test/regress/sql/sqljson_queryfuncs.sql
index d218b44ea47..94850c2bc47 100644
--- a/src/test/regress/sql/sqljson_queryfuncs.sql
+++ b/src/test/regress/sql/sqljson_queryfuncs.sql
@@ -105,6 +105,12 @@ SELECT JSON_VALUE(jsonb '[" "]', '$[*]' RETURNING int DEFAULT 2 + 3 ON ERROR);
 SELECT JSON_VALUE(jsonb '["1"]', '$[*]' RETURNING int DEFAULT 2 + 3 ON ERROR);
 SELECT JSON_VALUE(jsonb '["1"]', '$[*]' RETURNING int FORMAT JSON); -- RETURNING FORMAT not allowed
 
+-- A DEFAULT expression must be coerced to the RETURNING type's typmod even
+-- when its base type already matches, but a matching NULL needs no coercion.
+SELECT JSON_VALUE(jsonb '{}', '$.a' RETURNING numeric(4,1) DEFAULT 99999.999 ON EMPTY);
+SELECT JSON_VALUE(jsonb '{}', '$.a' RETURNING varchar(3) DEFAULT 'toolong'::varchar(10) ON EMPTY);
+SELECT JSON_VALUE(jsonb '{}', '$.a' RETURNING numeric(4,1) DEFAULT NULL::numeric ON EMPTY);
+
 -- RETUGNING pseudo-types not allowed
 SELECT JSON_VALUE(jsonb '["1"]', '$[*]' RETURNING record);
 
-- 
2.47.3