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

> On 28.05.2020, at 23:43, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> 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?

I think it is 6.6 Syntax Rule 17:

• 17) If the declared type of a <basic identifier chain> BIC is character string, then the collation derivation of the declared type of BIC is

Case:

• a) If the declared type has a declared type collation DTC, then implicit.

• b) Otherwise, none.

That gives derivation “none” to the column.

When this is concatenated, 9.5 ("Result of data type combinations”) SR 3 a ii 3 applies:

• ii) The collation derivation and declared type collation of the result are determined as follows. Case:

• 1) If some data type in DTS has an explicit collation derivation [… doesn’t apply]
• 2) If every data type in DTS has an implicit collation derivation, then [… doesn’t apply beause of “every"]
• 3) Otherwise, the collation derivation is none. [applies]

Also, the standard doesn’t have a forth derivation (strength). It also says that
not having a declared type collation implies the derivation “none”. See 4.2.2:

Every declared type that is a character string type has a collation
derivation, this being either none, implicit, or explicit. The
collation derivation of a declared type with a declared type collation
that is explicitly or implicitly specified by a <data type> is implicit.
If the collation derivation of a declared type that has a declared type
collation is not implicit, then it is explicit. The collation derivation
of an expression of character string type that has no declared type
collation is none.

-markus