Hash join not finding which collation to use for string hashing

While reviewing the partition-wise join patch, I ran into an issue that exists in master, so rather than responding to that patch, I’m starting this new thread.

I noticed that this seems similar to the problem that was supposed to have been fixed in the "Re: COLLATE: Hash partition vs UPDATE” thread. As such, I’ve included Tom and Amit in the CC list.

Notice the "ERROR: could not determine which collation to use for string hashing”

The following is extracted from the output from the test:

> CREATE TABLE raw_data (a text);
> INSERT INTO raw_data (a) VALUES ('Türkiye'),
> ('TÜRKIYE'),
> ('bıt'),
> ('BIT'),
> ('äbç'),
> ('ÄBÇ'),
> ('aaá'),
> ('coté'),
> ('Götz'),
> ('ὀδυσσεύς'),
> ('ὈΔΥΣΣΕΎΣ'),
> ('を読み取り用'),
> ('にオープンできませんでした');
> -- Create unpartitioned tables for test
> CREATE TABLE alpha (a TEXT COLLATE "ja_JP", b TEXT COLLATE "sv_SE");
> CREATE TABLE beta (a TEXT COLLATE "tr_TR", b TEXT COLLATE "en_US");
> INSERT INTO alpha (SELECT a, a FROM raw_data);
> INSERT INTO beta (SELECT a, a FROM raw_data);
> ANALYZE alpha;
> ANALYZE beta;
> EXPLAIN (COSTS OFF)
> SELECT t1.a, t2.a FROM alpha t1 INNER JOIN beta t2 ON (t1.a = t2.a) WHERE t1.a IN ('äbç', 'ὀδυσσεύς');
> QUERY PLAN
> ------------------------------------------------------------
> Hash Join
> Hash Cond: ((t2.a)::text = (t1.a)::text)
> -> Seq Scan on beta t2
> -> Hash
> -> Seq Scan on alpha t1
> Filter: (a = ANY ('{äbç,ὀδυσσεύς}'::text[]))
> (6 rows)
>
> SELECT t1.a, t2.a FROM alpha t1 INNER JOIN beta t2 ON (t1.a = t2.a) WHERE t1.a IN ('äbç', 'ὀδυσσεύς');
> ERROR: could not determine which collation to use for string hashing
> HINT: Use the COLLATE clause to set the collation explicitly.
>

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company