Re: sql/json query function JsonBehavior default expression's collation may differ from returning type's collation

On Mon, Jul 14, 2025 at 7:39 PM jian he <jian(dot)universality(at)gmail(dot)com> wrote:
>
> overall, raising an error if the collation of the
> JsonBehavior DEFAULT clause differs from that of the RETURNING clause
> is the best option.
>
> what do you think?

in exprSetCollation, the node can be T_CollateExpr.
In that case, CollateExpr->collOid should be the same as the collation
of the caller.

--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -1131,6 +1131,10 @@ exprSetCollation(Node *expr, Oid collation)
case T_Const:
((Const *) expr)->constcollid = collation;
break;
+ case T_CollateExpr:
+ if (((CollateExpr *) expr)->collOid != collation)
+ elog(ERROR, "COLLATE clause collation should be %u",
collation);
+ break;
case T_Param:
((Param *) expr)->paramcollid = collation;
break;
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index d66276801c6..9cbffff52c3 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -4825,6 +4825,15 @@ transformJsonBehavior(ParseState *pstate,
JsonBehavior *behavior,
parser_errposition(pstate, exprLocation(expr)));
}

+ if (typcategory == TYPCATEGORY_STRING &&
+ exprCollation(coerced_expr) !=
get_typcollation(returning->typid))
+ {
+ ereport(ERROR,
+ errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("DEFAULT expression collation does not
match with RETURNING type's collation"),
+ parser_errposition(pstate,
exprLocation(coerced_expr)));
+ }
+

create table t(a jsonb);
select json_value(a, '$.c' returning text default 'A' collate "C" on
empty) from t;
ERROR: DEFAULT expression collation does not match with RETURNING
type's collation

as you can see, this query returns a set of rows. If the collation of the
DEFAULT node differs from the default text collation, the resulting set may have
inconscient collations.
a set of rows all the collation should be the same.
overall I think it should error out.