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
thatpatch, 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
partitionvs 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