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

Hi Jian,

(Sorry for the long delay -- I meant to get back to this a while ago.)

On Fri, Oct 3, 2025 at 9:57 PM jian he <jian(dot)universality(at)gmail(dot)com> wrote:
> 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.

Thanks for posting v2 of the patch. I’ve made a few follow-up changes
(v3 attached):

* Moved the regression tests from sqljson_queryfuncs.sql to
collation.icu.utf8.sql to avoid failures on buildfarm machines without
ICU support.

* Adjusted the collation-mismatch check in transformJsonBehavior() so
that it runs last within the DEFAULT-handling block. That keeps the
control flow cleaner and avoids affecting existing tests that already
fail earlier checks, preventing unnecessary regression output churn.

* Did a few cosmetic edits and fixed the error code and message text.

Otherwise, behavior and coverage remain the same.

--
Thanks, Amit Langote