> 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