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

Markus Winand <markus(dot)winand(at)winand(dot)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