On Mon, Jul 14, 2025 at 7:39 PM jian he <jian.universal...@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.
From 1a9935377abe897c7afbf2419b71f5209034f209 Mon Sep 17 00:00:00 2001
From: jian he <jian.universal...@gmail.com>
Date: Mon, 21 Jul 2025 16:49:46 +0800
Subject: [PATCH v1 1/1] fix SQL/JSON default expression with CollateExpr node
in exprSetCollation
the node can be CollateExpr, in that case,
((CollateExpr *) expr)->collOid should equal to collation.
create table t(a jsonb);
select json_value(a, '$.c' returning text default 'A' collate "C" on empty) from t;
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
inconsistent collations.
As a result, the query's collation becomes unreliable. For instance, is it valid
to create the following index in this case?
create index xx on t (( json_value(a, '$.c' returning text default 'A' on empty) ));
#TODO: regress test add later
---
src/backend/nodes/nodeFuncs.c | 4 ++++
src/backend/parser/parse_expr.c | 9 +++++++++
2 files changed, 13 insertions(+)
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 7bc823507f1..d904668987e 100644
--- 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)));
+ }
+
expr = coerced_expr;
}
}
--
2.34.1
