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

On Tue, Aug 12, 2025 at 7:09 PM Amit Langote <amitlangote09@gmail.com> wrote:
>
> Hi Jian,
>
> Thanks for the patch and also for the offlist heads-up.
>
> I agree with rejecting cases where the DEFAULT clause’s collation does not match the RETURNING collation. The result collation for json_value should come from the RETURNING clause if it has an explicit COLLATE, otherwise from the RETURNING type’s collation, and both the extracted value source (the value obtained from the JSON path when it matches) and the DEFAULT source should match it.
>

hi.

based on my understand of
https://www.postgresql.org/docs/current/collation.html#COLLATION-CONCEPTS
<<<<<<<
1. If any input expression has an explicit collation derivation, then all
explicitly derived collations among the input expressions must be the same,
otherwise an error is raised. If any explicitly derived collation is present,
that is the result of the collation combination.

2. Otherwise, all input expressions must have the same implicit collation
derivation or the default collation. If any non-default collation is present,
that is the result of the collation combination. Otherwise, the result is the
default collation.
<<<<<<<

CREATE COLLATION case_insensitive (provider = icu, locale =
'und-u-ks-level2', deterministic = false);
create domain d1 as text collate case_insensitive;
create domain d2 as text collate "C";

the below two queries should error out:
select json_value('{"a": "A"}', '$.a' returning d1 default 'C'::d2 on
empty) = 'a'; --error
select json_value('{"a": "A"}', '$.a' returning d1 default 'C' collate
"C" on empty) = 'a'; --error

please check attached patch.