From 58746e21b076d8697fd1a2ce9b9056ce1f5a797b Mon Sep 17 00:00:00 2001 From: Amit Langote Date: Wed, 5 Jun 2024 09:13:15 +0900 Subject: [PATCH v3 2/2] SQL/JSON: Use implicit casts for RETURNING type with typmod This fixes things so that the various SQL/JSON functions that support specifying the type of the returned value using the RETURNING clause perform an implicit cast to the target type at least in the cases where the target type also specifies a length limit. That is to ensure that the output values (including those specified in the DEFAULT .. ON ERROR) that exceed the length produce an error instead of being silently truncated to the specified length, which is a behavior that agrees with what the SQL standard. Reported-by: Alvaro Herrera Discussion: https://postgr.es/m/202405271326.5a5rprki64aw%40alvherre.pgsql --- src/backend/parser/parse_expr.c | 24 +++++++++++++++++++++++- src/test/regress/expected/sqljson.out | 17 +++++++++++++++++ src/test/regress/sql/sqljson.sql | 12 ++++++++++++ 3 files changed, 52 insertions(+), 1 deletion(-) diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 97a92f2b0d..f91eb5b658 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -3583,6 +3583,7 @@ coerceJsonFuncExpr(ParseState *pstate, Node *expr, Node *res; int location; Oid exprtype = exprType(expr); + int32 baseTypmod = returning->typmod; /* if output type is not specified or equals to function type, return */ if (!OidIsValid(returning->typid) || returning->typid == exprtype) @@ -3611,10 +3612,19 @@ coerceJsonFuncExpr(ParseState *pstate, Node *expr, return (Node *) fexpr; } + /* + * For domains, consider the base type's typmod to decide whether to setup + * an implicit or explicit cast. + */ + if (get_typtype(returning->typid) == TYPTYPE_DOMAIN) + (void) getBaseTypeAndTypmod(returning->typid, &baseTypmod); + /* try to coerce expression to the output type */ res = coerce_to_target_type(pstate, expr, exprtype, - returning->typid, returning->typmod, + returning->typid, baseTypmod, + baseTypmod > 0 ? COERCION_IMPLICIT : COERCION_EXPLICIT, + baseTypmod > 0 ? COERCE_IMPLICIT_CAST : COERCE_EXPLICIT_CAST, location); @@ -3640,6 +3650,7 @@ makeJsonConstructorExpr(ParseState *pstate, JsonConstructorType type, JsonConstructorExpr *jsctor = makeNode(JsonConstructorExpr); Node *placeholder; Node *coercion; + int32 baseTypmod = returning->typmod; jsctor->args = args; jsctor->func = fexpr; @@ -3677,6 +3688,17 @@ makeJsonConstructorExpr(ParseState *pstate, JsonConstructorType type, placeholder = (Node *) cte; } + /* + * Convert the source expression to text, because coerceJsonFuncExpr() will + * create an implicit cast to the RETURNING types with typmod and there are + * no implicit casts from json(b) to such types. For domains, the base + * type's typmod will be considered, so do so here too. + */ + if (get_typtype(returning->typid) == TYPTYPE_DOMAIN) + (void) getBaseTypeAndTypmod(returning->typid, &baseTypmod); + if (baseTypmod > 0) + placeholder = coerce_to_specific_type(pstate, placeholder, TEXTOID, + "JSON_CONSTRUCTOR()"); coercion = coerceJsonFuncExpr(pstate, placeholder, returning, true); if (coercion != placeholder) diff --git a/src/test/regress/expected/sqljson.out b/src/test/regress/expected/sqljson.out index cbf8542d8d..4f91e2117e 100644 --- a/src/test/regress/expected/sqljson.out +++ b/src/test/regress/expected/sqljson.out @@ -1278,3 +1278,20 @@ CREATE OR REPLACE VIEW public.is_json_view AS '{}'::text IS JSON OBJECT WITH UNIQUE KEYS AS object FROM generate_series(1, 3) i(i) DROP VIEW is_json_view; +-- Test implicit coercion to a fixed-length type specified in RETURNING +SELECT JSON_SERIALIZE('{ "a" : 1 } ' RETURNING varchar(2)); +ERROR: value too long for type character varying(2) +SELECT JSON_OBJECT('a': JSON_OBJECT('b': 1 RETURNING varchar(2))); +ERROR: value too long for type character varying(2) +SELECT JSON_ARRAY(JSON_ARRAY('{ "a" : 123 }' RETURNING varchar(2))); +ERROR: value too long for type character varying(2) +SELECT JSON_ARRAYAGG(('111' || i)::bytea FORMAT JSON NULL ON NULL RETURNING varchar(2)) FROM generate_series(1,1) i; +ERROR: value too long for type character varying(2) +SELECT JSON_OBJECTAGG(i: ('111' || i)::bytea FORMAT JSON WITH UNIQUE RETURNING varchar(2)) FROM generate_series(1, 1) i; +ERROR: value too long for type character varying(2) +-- Now try domain over fixed-length type +CREATE DOMAIN sqljson_char2 AS char(2) CHECK (VALUE NOT IN ('12')); +SELECT JSON_SERIALIZE('123' RETURNING sqljson_char2); +ERROR: value too long for type character(2) +SELECT JSON_SERIALIZE('12' RETURNING sqljson_char2); +ERROR: value for domain sqljson_char2 violates check constraint "sqljson_char2_check" diff --git a/src/test/regress/sql/sqljson.sql b/src/test/regress/sql/sqljson.sql index e6e20175b0..bb2487e864 100644 --- a/src/test/regress/sql/sqljson.sql +++ b/src/test/regress/sql/sqljson.sql @@ -463,3 +463,15 @@ SELECT '1' IS JSON AS "any", ('1' || i) IS JSON SCALAR AS "scalar", '[]' IS NOT \sv is_json_view DROP VIEW is_json_view; + +-- Test implicit coercion to a fixed-length type specified in RETURNING +SELECT JSON_SERIALIZE('{ "a" : 1 } ' RETURNING varchar(2)); +SELECT JSON_OBJECT('a': JSON_OBJECT('b': 1 RETURNING varchar(2))); +SELECT JSON_ARRAY(JSON_ARRAY('{ "a" : 123 }' RETURNING varchar(2))); +SELECT JSON_ARRAYAGG(('111' || i)::bytea FORMAT JSON NULL ON NULL RETURNING varchar(2)) FROM generate_series(1,1) i; +SELECT JSON_OBJECTAGG(i: ('111' || i)::bytea FORMAT JSON WITH UNIQUE RETURNING varchar(2)) FROM generate_series(1, 1) i; + +-- Now try domain over fixed-length type +CREATE DOMAIN sqljson_char2 AS char(2) CHECK (VALUE NOT IN ('12')); +SELECT JSON_SERIALIZE('123' RETURNING sqljson_char2); +SELECT JSON_SERIALIZE('12' RETURNING sqljson_char2); -- 2.43.0