Обсуждение: Bug in either collation docs or code

On 05/06/18 07:10, Melanie Plageman wrote:
> Hi,
> I noticed what seems like a bug in collation.
> 
> This query errors out:
> SELECT (SELECT 'c' COLLATE "de_DE") > (SELECT 'ç' COLLATE "es_ES");  --
> error
> 
> While this query does not:
> SELECT 'c' COLLATE "de_DE" > (SELECT 'ç' COLLATE "es_ES");  -- no error
> 
> It seems like this is in conflict with what the documentation
> <https://www.postgresql.org/docs/devel/static/collation.html> says:
> "If any input expression has an explicit collation derivation, then all
> explicitly derived collations among the input expressions must be the same,
> otherwise an error is raised."
> 
> After digging into it a bit, I believe the reason for this is that when we
> are assigning collations in assign_collations_walker, we always set
> collation strength to IMPLICIT for the subquery and always set the
> collation strength to EXPLICIT for the collate node on the other side of
> the OpExpr. So, we don't hit an error later like the one in
> merge_collation_state when the collation of one expression is conflicting
> with that of its parent and the strength of both is EXPLICIT.
> I think this still applies to our case because one of the two arguments to
> OpExpr would have set their parent's collation strength to either IMPLICIT
> or EXPLICIT and then we will process the other argument which would then
> have a different collation strength than the one we just set its parent to.
> So, we end up setting the inputcollid for the OpExpr to that of the
> explicit collation in the collate node.
> 
> Basically, it seems like our subquery will always have its collation
> strength set to IMPLICIT, so, if we have explicit collation in the first
> target entry of the subquery's target list, it looks like we will never
> truly treat that as explicit collation.

Right, a subquery's result is considered IMPLICIT, even if there is an 
explicit COLLATE inside the subquery.

You could mark the subquery's result with a collation like this:

postgres=# SELECT 'c' COLLATE "de_DE" > (SELECT 'ç') COLLATE "es_ES";
ERROR:  collation mismatch between explicit collations "de_DE" and "es_ES"

> I'm not sure if this behavior is considered a bug, but I also can't imagine
> how it would be expected given the current documentation. It seems to me
> one or the other should be updated.

It seems correct to me. It does say "An explicit collation derivation 
occurs when a COLLATE clause is used; all other collation derivations 
are implicit". A subquery falls under the "all other collation 
derivations" category. Perhaps we could make it more clear what the 
COLLATE clause binds to, especially with subqueries, but I'm not sure 
how exactly to phrase it. Perhaps an additional example with a subquery 
would help?

- Heikki

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thu, Jun 7, 2018 at 4:37 PM, Melanie Plageman <melanieplageman@gmail.com>
> wrote:
>> I thought this would error out because the subquery's result is considered
>> implicit and, in this case, it seems you now have conflicting implicit
>> collations. However, this does not produce an error. What am I missing?

> Data, apparently...I got the same non-error result before inserting a
> record into test1 then I got the expected error.
> Its the function/operator the fails when faced with invalid input, not the
> planner, so the error requires data to provoke.

IIRC this was an intentional decision, made on the grounds that we
can't tell whether the function/operator actually cares about having
a determinate collation or not, so we have to leave it to execution of
that function/operator to complain or not.

If collation had been part of the system design to start with, we'd
probably have insisted on being able to determine this sooner.  But
it wasn't, and when we added it, the ability to throw an error sooner
did not seem worth breaking a lot of third-party code for.

            regards, tom lane

Melanie Plageman <melanieplageman@gmail.com> writes:
> In this case, why treat implicit and explicit collation conflicts
> differently?

Um ... because the SQL standard says so?

            regards, tom lane

Melanie Plageman <melanieplageman@gmail.com> writes:
> On postgres built off of master on my mac (sierra), the following is the
> output:

[ scratches head ... ]  I get the same results on either Mac or Linux:

regression=# create database u8 encoding utf8 template template0;
CREATE DATABASE
regression=# \c u8
You are now connected to database "u8" as user "tgl".
u8=# CREATE TABLE test_col_mac (
u8(#     a text COLLATE "de_DE",
u8(#     b text COLLATE "es_ES"
u8(# );
CREATE TABLE
u8=# SELECT a < (SELECT 'foo'::TEXT COLLATE "fr_FR") FROM test_col_mac;
 ?column? 
----------
(0 rows)

u8=# INSERT INTO test_col_mac VALUES('A','A');
INSERT 0 1
u8=# SELECT a < (SELECT 'foo'::TEXT COLLATE "fr_FR") FROM test_col_mac;
ERROR:  could not determine which collation to use for string comparison
HINT:  Use the COLLATE clause to set the collation explicitly.

Now, without the sub-select, it works:

u8=# SELECT a < ('foo'::TEXT COLLATE "fr_FR") FROM test_col_mac;
 ?column? 
----------
 t
(1 row)

because the explicit COLLATE is considered to determine the
collation of the comparison operator.

I wonder whether you're using stock Postgres, or something that
flattens scalar sub-selects?

            regards, tom lane