Re: Bug in either collation docs or code

You could mark the subquery's result with a collation like this:
>
> postgres=# SELECT 'c' COLLATE "de_DE" > (SELECT 'ç') COLLATE "es_ES";
> ERROR: collation mismatch between explicit collations "de_DE" and "es_ES"
>
> I'm not sure if this behavior is considered a bug, but I also can't imagine
>> how it would be expected given the current documentation. It seems to me
>> one or the other should be updated.
>>
>
> It seems correct to me. It does say "An explicit collation derivation
> occurs when a COLLATE clause is used; all other collation derivations are
> implicit". A subquery falls under the "all other collation derivations"
> category. Perhaps we could make it more clear what the COLLATE clause binds
> to, especially with subqueries, but I'm not sure how exactly to phrase it.
> Perhaps an additional example with a subquery would help?
>

So, I tried coming up with an example with a subquery that explains what
the COLLATE clause binds to, and I realized I'm still not clear. I came up
with an example using the DDL that is in the docs
<https://www.postgresql.org/docs/devel/static/collation.html>:

CREATE TABLE test1 (
a text COLLATE "de_DE",
b text COLLATE "es_ES",
...
);

My thought was to add the following example:

SELECT a < (select 'foo' COLLATE "fr_FR") FROM test1;

I thought this would error out because the subquery's result is considered
implicit and, in this case, it seems you now have conflicting implicit
collations. However, this does not produce an error. What am I missing? The
result of the subquery has collation "fr_FR" and, if it's implicit, then I
shouldn't be able to compare it with test1.a, which has an implicit
collation of "de_DE".