Re: sql/json query function JsonBehavior default expression's collation may differ from returning type's collation - Mailing list pgsql-hackers

On Mon, Jul 14, 2025 at 7:39 PM jian he <jian.universality@gmail.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.