postgres.git / summary / log / commit / refs
commit d30bfcbddca3b08f66d1207265d9ab7d8a7b95c1
Author: Amit Langote <amitlan@postgresql.org>
Date: Mon Jul 06 23:13:59 2026 +0000
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.
Reported-by: Ewan Young <kdbase.hack@gmail.com>
Author: Ewan Young <kdbase.hack@gmail.com>
Discussion: https://postgr.es/m/CAON2xHPO9f4cAmyGn1mQ=VqoS7wN5rz4yOiqudxX78zninZpCw@mail.gmail.com
Backpatch-through: 17
src/backend/parser/parse_expr.c | 11 ++++++++++-
src/test/regress/expected/sqljson_jsontable.out | 16 ++++++++++++++++
src/test/regress/expected/sqljson_queryfuncs.out | 21 +++++++++++++++++++++
src/test/regress/sql/sqljson_jsontable.sql | 9 +++++++++
src/test/regress/sql/sqljson_queryfuncs.sql | 8 ++++++++
5 files changed, 64 insertions(+), 1 deletion(-)
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 9adc9d4c0f6..e6ea34a7809 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -4931,8 +4931,17 @@ transformJsonBehavior(ParseState *pstate, JsonExpr *jsexpr,
*
* For other non-NULL expressions, try to find a cast and error out if one
* is not found.
+ *
+ * The DEFAULT expression's base type may already match the RETURNING type
+ * yet still need coercion: when the RETURNING type carries a type
+ * modifier (e.g. numeric(4,1)), the cast below is what enforces it, so
+ * skipping it here would let the DEFAULT yield a value that violates its
+ * declared RETURNING type. A NULL constant needs no such 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..4d500e7de2d 100644
--- a/src/test/regress/expected/sqljson_jsontable.out
+++ b/src/test/regress/expected/sqljson_jsontable.out
@@ -250,6 +250,22 @@ 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.
+SELECT * FROM JSON_TABLE(jsonb '{}', '$'
+ COLUMNS (c bit(3) PATH '$.x' DEFAULT b'10101' ON EMPTY));
+ERROR: bit string length 5 does not match type bit(3)
+SELECT * FROM JSON_TABLE(jsonb '{}', '$'
+ COLUMNS (c numeric(4,1) PATH '$.x' DEFAULT abs(NULL::numeric) ON EMPTY));
+ c
+---
+
+(1 row)
+
-- 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..ff64dce0c59 100644
--- a/src/test/regress/expected/sqljson_queryfuncs.out
+++ b/src/test/regress/expected/sqljson_queryfuncs.out
@@ -433,6 +433,27 @@ 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)
+
+SELECT JSON_VALUE(jsonb '{}', '$.a' RETURNING bit(3) DEFAULT b'10101' ON EMPTY);
+ERROR: bit string length 5 does not match type bit(3)
+SELECT JSON_VALUE(jsonb '{}', '$.a' RETURNING numeric(4,1) DEFAULT abs(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..41824094b96 100644
--- a/src/test/regress/sql/sqljson_jsontable.sql
+++ b/src/test/regress/sql/sqljson_jsontable.sql
@@ -132,6 +132,15 @@ 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));
+SELECT * FROM JSON_TABLE(jsonb '{}', '$'
+ COLUMNS (c bit(3) PATH '$.x' DEFAULT b'10101' ON EMPTY));
+SELECT * FROM JSON_TABLE(jsonb '{}', '$'
+ COLUMNS (c numeric(4,1) PATH '$.x' DEFAULT abs(NULL::numeric) 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..a69ef253f66 100644
--- a/src/test/regress/sql/sqljson_queryfuncs.sql
+++ b/src/test/regress/sql/sqljson_queryfuncs.sql
@@ -105,6 +105,14 @@ 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);
+SELECT JSON_VALUE(jsonb '{}', '$.a' RETURNING bit(3) DEFAULT b'10101' ON EMPTY);
+SELECT JSON_VALUE(jsonb '{}', '$.a' RETURNING numeric(4,1) DEFAULT abs(NULL::numeric) ON EMPTY);
+
-- RETUGNING pseudo-types not allowed
SELECT JSON_VALUE(jsonb '["1"]', '$[*]' RETURNING record);
[parent: da8889ccd7ea]