Re: Bug in either collation docs or code

On 05/06/18 07:10, Melanie Plageman wrote:
> Hi,
> I noticed what seems like a bug in collation.
>
> This query errors out:
> SELECT (SELECT 'c' COLLATE "de_DE") > (SELECT 'ç' COLLATE "es_ES"); --
> error
>
> While this query does not:
> SELECT 'c' COLLATE "de_DE" > (SELECT 'ç' COLLATE "es_ES"); -- no error
>
> It seems like this is in conflict with what the documentation
> <https://www.postgresql.org/docs/devel/static/collation.html> says:
> "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."
>
> After digging into it a bit, I believe the reason for this is that when we
> are assigning collations in assign_collations_walker, we always set
> collation strength to IMPLICIT for the subquery and always set the
> collation strength to EXPLICIT for the collate node on the other side of
> the OpExpr. So, we don't hit an error later like the one in
> merge_collation_state when the collation of one expression is conflicting
> with that of its parent and the strength of both is EXPLICIT.
> I think this still applies to our case because one of the two arguments to
> OpExpr would have set their parent's collation strength to either IMPLICIT
> or EXPLICIT and then we will process the other argument which would then
> have a different collation strength than the one we just set its parent to.
> So, we end up setting the inputcollid for the OpExpr to that of the
> explicit collation in the collate node.
>
> Basically, it seems like our subquery will always have its collation
> strength set to IMPLICIT, so, if we have explicit collation in the first
> target entry of the subquery's target list, it looks like we will never
> truly treat that as explicit collation.

Right, a subquery's result is considered IMPLICIT, even if there is an
explicit COLLATE inside the subquery.

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?

- Heikki