On Sun, Jun 30, 2024 at 2:24 AM Alvaro Herrera <alvhe...@alvh.no-ip.org> wrote: > > TBH I'm not super clear on why we decide on explicit or implicit cast > based on presence of a typmod. Why isn't it better to always use an > implicit one? >
I am using an example to explain it.
SELECT JSON_SERIALIZE(JSON('{ "a" : 1 } '));
we cannot directly use implicit cast from json to text in
{coerceJsonFuncExpr, coerce_to_target_type}
because function calls:
coerceJsonFuncExpr->coerce_to_target_type->can_coerce_type
->find_coercion_pathway
will look up pg_cast entries.
but we don't have text & json implicit cast entries, we will fail at:
````
if (!res && report_error)
ereport(ERROR,
errcode(ERRCODE_CANNOT_COERCE),
errmsg("cannot cast type %s to %s",
format_type_be(exprtype),
format_type_be(returning->typid)),
parser_coercion_errposition(pstate, location, expr));
````
Most of the cast uses explicit cast, which is what we previously did,
then in this thread, we found out for the returning type typmod(
(varchar, or varchar's domain)
We need to first cast the expression to text then text to varchar via
implicit cast.
To trap the error:
for example: SELECT JSON_SERIALIZE('{ "a" : 1 } ' RETURNING varchar(2);
also see the comment:
https://git.postgresql.org/cgit/postgresql.git/commit/?id=c2d93c3802b205d135d1ae1d7ac167d74e08a274
+ /*
+ * 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.
+ */
In general, I think implicit cast here is an exception.
overall I come up with following logic:
-----------------
int32 baseTypmod = -1;
if (returning->typmod < 0)
(void) getBaseTypeAndTypmod(returning->typid, &baseTypmod);
else
baseTypmod = returning->typmod;
res = coerce_to_target_type(pstate, expr, exprtype,
returning->typid, baseTypmod,
baseTypmod > 0 ? COERCION_IMPLICIT :
COERCION_EXPLICIT,
baseTypmod > 0 ? COERCE_IMPLICIT_CAST :
COERCE_EXPLICIT_CAST,
location);
-----------------
By the same way we are dealing with varchar,
I came up with a verbose patch for transformJsonBehavior,
which can cope with all the corner cases of bit and varbit data type.
I also attached a test sql file (scratch169.sql) for it.
some examples:
--fail
SELECT JSON_VALUE(jsonb '"111a"', '$' RETURNING bit(3) default '1111'
on error);
--ok
SELECT JSON_VALUE(jsonb '"111a"', '$' RETURNING bit(3) default '111' on error);
--ok
SELECT JSON_VALUE(jsonb '"111a"', '$' RETURNING bit(3) default 32 on error);
makeJsonConstructorExpr we called (void)
getBaseTypeAndTypmod(returning->typid, &baseTypmod);
later in coerceJsonFuncExpr
we may also call (void) getBaseTypeAndTypmod(returning->typid, &baseTypmod);
maybe we can do some refactoring.
From 5f2d070d3cc47f7461d1474a5fe18e905243b31b Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Mon, 1 Jul 2024 14:26:09 +0800
Subject: [PATCH v1 1/1] hanlde types that have type modifier for json on
error, on empty
---
src/backend/parser/parse_expr.c | 83 ++++++++++++++++++++++++++-------
1 file changed, 65 insertions(+), 18 deletions(-)
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 560b3606..15cdea1c 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -3582,7 +3582,7 @@ coerceJsonFuncExpr(ParseState *pstate, Node *expr,
Node *res;
int location;
Oid exprtype = exprType(expr);
- int32 baseTypmod = returning->typmod;
+ int32 baseTypmod = -1;
/* if output type is not specified or equals to function type, return */
if (!OidIsValid(returning->typid) || returning->typid == exprtype)
@@ -3615,8 +3615,10 @@ coerceJsonFuncExpr(ParseState *pstate, Node *expr,
* 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)
+ if (returning->typmod < 0)
(void) getBaseTypeAndTypmod(returning->typid, &baseTypmod);
+ else
+ baseTypmod = returning->typmod;
/* try to coerce expression to the output type */
res = coerce_to_target_type(pstate, expr, exprtype,
@@ -3649,7 +3651,7 @@ makeJsonConstructorExpr(ParseState *pstate, JsonConstructorType type,
JsonConstructorExpr *jsctor = makeNode(JsonConstructorExpr);
Node *placeholder;
Node *coercion;
- int32 baseTypmod = returning->typmod;
+ int32 baseTypmod = -1;
jsctor->args = args;
jsctor->func = fexpr;
@@ -3693,8 +3695,10 @@ makeJsonConstructorExpr(ParseState *pstate, JsonConstructorType type,
* 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)
+ if (returning->typmod < 0)
(void) getBaseTypeAndTypmod(returning->typid, &baseTypmod);
+ else
+ baseTypmod = returning->typmod;
if (baseTypmod > 0)
placeholder = coerce_to_specific_type(pstate, placeholder, TEXTOID,
"JSON_CONSTRUCTOR()");
@@ -4718,22 +4722,65 @@ transformJsonBehavior(ParseState *pstate, JsonBehavior *behavior,
coerce_at_runtime = true;
else
{
- int32 baseTypmod = returning->typmod;
-
- if (get_typtype(returning->typid) == TYPTYPE_DOMAIN)
+ int32 baseTypmod = -1;
+ char typcategory;
+ bool typispreferred;
+ get_type_category_preferred(returning->typid, &typcategory, &typispreferred);
+ if (returning->typmod < 0)
(void) getBaseTypeAndTypmod(returning->typid, &baseTypmod);
+ else
+ baseTypmod = returning->typmod;
- if (baseTypmod > 0)
- expr = coerce_to_specific_type(pstate, expr, TEXTOID,
- "JSON_FUNCTION()");
- coerced_expr =
- coerce_to_target_type(pstate, expr, exprType(expr),
- returning->typid, baseTypmod,
- baseTypmod > 0 ? COERCION_IMPLICIT :
- COERCION_EXPLICIT,
- baseTypmod > 0 ? COERCE_IMPLICIT_CAST :
- COERCE_EXPLICIT_CAST,
- exprLocation((Node *) behavior));
+ if (typcategory == TYPCATEGORY_STRING)
+ {
+ if (baseTypmod > 0)
+ expr = coerce_to_specific_type(pstate, expr, TEXTOID,
+ "JSON_FUNCTION()");
+ coerced_expr =
+ coerce_to_target_type(pstate, expr, exprType(expr),
+ returning->typid, baseTypmod,
+ baseTypmod > 0 ? COERCION_IMPLICIT :
+ COERCION_EXPLICIT,
+ baseTypmod > 0 ? COERCE_IMPLICIT_CAST :
+ COERCE_EXPLICIT_CAST,
+ exprLocation((Node *) behavior));
+ }
+ else if (typcategory == TYPCATEGORY_BITSTRING)
+ {
+ CoercionContext ccontext;
+ CoercionForm cformat;
+
+ /*
+ * pg_cast don't have bit & text entry, so in function can_coerce_type
+ * cannot implilcit cast to text then cast to varbit.
+ * but we can unknown cast to bit via implilcit cast.
+ * select '21'::text::bit(2); --fail
+ * select 21::int4::bit(2); --ok
+ * so DEFAULT 21 ON ERROR, DEFAULT 11 ON ERROR should be fine;
+ */
+ if(baseTypmod > 0 && ((exprType(expr) != INT4OID && exprType(expr) != INT8OID)))
+ {
+ ccontext = COERCION_IMPLICIT;
+ cformat = COERCE_IMPLICIT_CAST;
+ }
+ else
+ {
+ ccontext = COERCION_EXPLICIT;
+ cformat = COERCE_EXPLICIT_CAST;
+ }
+ coerced_expr =
+ coerce_to_target_type(pstate, expr, exprType(expr),
+ returning->typid, returning->typmod,
+ ccontext,
+ cformat,
+ exprLocation((Node *) behavior));
+ }
+ else
+ coerced_expr =
+ coerce_to_target_type(pstate, expr, exprType(expr),
+ returning->typid, returning->typmod,
+ COERCION_EXPLICIT, COERCE_EXPLICIT_CAST,
+ exprLocation((Node *) behavior));
}
if (coerced_expr == NULL)
--
2.34.1
scratch169.sql
Description: application/sql
