|From:||Markus Winand <markus(dot)winand(at)winand(dot)at>|
|To:||Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>|
|Cc:||PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>|
|Subject:||Re: Conflict of implicit collations doesn't propagate out of subqueries|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
> 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
• 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.
|Next Message||Daniel Gustafsson||2020-05-28 22:16:47||OpenSSL 3.0.0 compatibility|
|Previous Message||Andres Freund||2020-05-28 22:07:50||Re: Fix compilation failure against LLVM 11|