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

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

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
derivationof 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