Incorrect query results in PostgreSQL connector when comparing varchar values are collation sensitive

[findepi]

Using io.prestosql.plugin.postgresql.PostgreSqlQueryRunner#main PostgreSQL connector:

trino:public> SELECT count(*) FROM orders WHERE orderpriority BETWEEN '5-' AND '5.' OR rand() = 42;
 _col0
-------
  2950

trino:public> SELECT count(*) FROM orders WHERE orderpriority BETWEEN '5-' AND '5.';
 _col0
-------
     0

And:

trino:public> select count(*) from test_collation where a > 'Ala' OR rand() = 42;
 _col0
-------
     1

trino:public> select count(*) from test_collation where a > 'Ala';;
 _col0
-------
     0

This is because default collation in Postgres:

• puts 5. after 5- (like Trino), but before 5-LOW (the actual value in the column).
• puts a before A, unlike Trino

[findepi]

in Postgres the recommended way is to use COLLATE "C" like here:

SELECT count(*) FROM orders WHERE orderpriority COLLATE "C" BETWEEN '5-' AND '5.';

[findepi]

There seems to be no way to enforce "C" locale connection-wide.

• Allow setting default collation for a connection pgjdbc/pgjdbc#1772
• https://postgresteam.slack.com/archives/C0FS3UTAP/p1588764357190100

[kokosing]

I think this issue is more generic. It not only about punctuation symbol. PostgreSQL has different collation by default than Trino. For example for PostgreSQL 'a' < 'A' while in Trino 'A' < 'a'

[findepi]

Thanks for broadening the issue scope. Can you please add relevant examples to the description as well?

[kokosing]

Sure, done.

[findepi]

#7145