From: Tom Lane <tgl@sss.pgh.pa.us>
To: tanner.sterling@outlook.com
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17868: Inconsistent collation in PL/pgSQL function
 parameters can lead to errors
In-reply-to: <17868-77406e24291c4548@postgresql.org>
References: <17868-77406e24291c4548@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Fri, 24 Mar 2023 16:54:42 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2346059.1679681804.1@sss.pgh.pa.us>
Date: Fri, 24 Mar 2023 14:16:44 -0400
Message-ID: <2346060.1679681804@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2346060.1679681804%40sss.pgh.pa.us>
Precedence: bulk

PG Bug reporting form <noreply@postgresql.org> writes:
> But when I tried to simplify this call method, the situation became
> unpredictable. The following use case looks equivalent to the one above,
> except that it passes two values with different collation directly into the
> function, but the execution results are different from the above use case.

> select less_than('abc' COLLATE "en_US", '123' COLLATE "C");

> Execution error reported:
> ERROR:  collation mismatch between explicit collations "en_US" and "C"
> LINE 1: select less_than('abc' COLLATE "en_US", '123' COLLATE "C")

> Why doesn't this example follow the principle mentioned above, which should
> have converted the collations to the default collation instead of raising an
> error? Is this a bug in the processing of PL/pgSQL function parameters?

No.  It has nothing whatever to do with plpgsql, as you'd get the same
result with

SELECT 'abc' COLLATE "en_US" < '123' COLLATE "C";
ERROR:  collation mismatch between explicit collations "en_US" and "C"
LINE 1: SELECT 'abc' COLLATE "en_US" < '123' COLLATE "C";
                                             ^

This is about the difference between explicit and implicit collation
specs.  The SQL standard says to throw an error when an expression
contains inconsistent explicit COLLATE markings, but not when the
collations are implicit (that is, inherited from some other place).
You already quoted the part of our docs saying exactly this:

https://www.postgresql.org/docs/current/collation.html

> What I want to say is that these different ways of handling collation
> conflicts seem to be inconsistent throughout PostgreSQL, and there are
> always surprising special cases even after understanding the basic
> principles of combination. Is it possible that this could be improved to
> make it more consistent and easier to use.

If you don't like this design, complain to the SQL standards committee.
I can't say that I care for their choices here either, but I doubt it's
going to change.

			regards, tom lane