Re: Conflict of implicit collations doesn't propagate out of subqueries

Markus Winand <markus.winand@winand.at> writes:
> However, if the conflict happens in a subquery, it doesn’t anymore:

>         WITH data (c, posix) AS (
>                 values ('a' COLLATE "C", 'b' COLLATE "POSIX")
>         )
>         SELECT *
>           FROM (SELECT *, c || posix AS none FROM data) data
>          ORDER BY none || posix;

>          c | posix | none
>         ---+-------+------
>          a | b     | ab
>         (1 row)

I'm not exactly convinced this is a bug.  Can you cite chapter and verse
in the spec to justify throwing an error?

AIUI, collation conflicts can only occur within a single expression, and
this is not that.  Moreover, even if data.none arguably has no collation,
treating it from outside the sub-query as having collation strength "none"
seems to me to be similar to our policy of promoting unknown-type subquery
outputs to type "text" rather than leaving them to cause trouble later.
It's not pedantically correct, but nobody liked the old behavior.

            regards, tom lane