On 2018/03/01 2:23, Robert Haas wrote:
> On Tue, Feb 27, 2018 at 8:12 PM, Amit Langote
> <[email protected]> wrote:
>> Ah, OK. I was missing that there is no need to have both parttypcoll and
>> partcollation in PartitionSchemeData, as the Vars in rel->partexprs are
>> built from a bare PartitionKey (not PartitionSchemeData), and after that
>> point, parttypcoll no longer needs to kept around.
>>
>> I noticed that there is a typo in the patch.
>>
>> + memcpy(part_scheme->partcollation, partkey->parttypcoll,
>>
>> s/parttypcoll/partcollation/g
>
> Committed your version.
Thank you.
>> BTW, should there be a relevant test in partition_join.sql? If yes,
>> attached a patch (partitionwise-join-collation-test-1.patch) to add one.
>
> I don't feel strongly about it, but I'm not going to try to prevent
> you from adding one, either.
OK. Attached is a revised version of that patch in case you consider
committing it, addressing Ashutosh's comment that the tables used in the
test should contain some data.
Thanks,
Amit
diff --git a/src/test/regress/expected/partition_join.out
b/src/test/regress/expected/partition_join.out
index 4fccd9ae54..f076d15ced 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -1869,3 +1869,35 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 FULL JOIN
prt1 t2 ON (t1.c = t2.c);
-> Seq Scan on prt1_n_p2 t1_1
(10 rows)
+--
+-- No partition-wise join if partitioning collation doesn't match
+--
+CREATE TABLE posix_text (a text) PARTITION BY RANGE (a COLLATE "POSIX");
+CREATE TABLE posix_text1 PARTITION OF posix_text FOR VALUES FROM ('a') TO
('m');
+CREATE TABLE posix_text2 PARTITION OF posix_text FOR VALUES FROM ('m') TO ('z
');
+INSERT INTO posix_text SELECT chr(97 + (i-1)%26::int) FROM generate_series(1,
599) i;
+ANALYZE posix_text;
+CREATE TABLE c_text (a text) PARTITION BY RANGE (a COLLATE "C");
+CREATE TABLE c_text1 PARTITION OF c_text FOR VALUES FROM ('a') TO ('m');
+CREATE TABLE c_text2 PARTITION OF c_text FOR VALUES FROM ('m') TO ('z ');
+INSERT INTO c_text SELECT chr(97 + (i-1)%26::int) FROM generate_series(1, 599)
i;
+ANALYZE c_text;
+EXPLAIN (COSTS OFF)
+SELECT p.a, c.a FROM posix_text p JOIN c_text c ON (p.a = c.a) ORDER BY 1;
+ QUERY PLAN
+-----------------------------------------------
+ Merge Join
+ Merge Cond: (p.a = c.a)
+ -> Sort
+ Sort Key: p.a
+ -> Append
+ -> Seq Scan on posix_text1 p
+ -> Seq Scan on posix_text2 p_1
+ -> Sort
+ Sort Key: c.a
+ -> Append
+ -> Seq Scan on c_text1 c
+ -> Seq Scan on c_text2 c_1
+(12 rows)
+
+DROP TABLE posix_text, c_text;
diff --git a/src/test/regress/sql/partition_join.sql
b/src/test/regress/sql/partition_join.sql
index a2d8b1be55..9dd15c1059 100644
--- a/src/test/regress/sql/partition_join.sql
+++ b/src/test/regress/sql/partition_join.sql
@@ -384,3 +384,23 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 JOIN prt2_n
t2 ON (t1.c = t2.c) JOI
-- partitioned table
EXPLAIN (COSTS OFF)
SELECT t1.a, t1.c, t2.b, t2.c FROM prt1_n t1 FULL JOIN prt1 t2 ON (t1.c =
t2.c);
+
+--
+-- No partition-wise join if partitioning collation doesn't match
+--
+CREATE TABLE posix_text (a text) PARTITION BY RANGE (a COLLATE "POSIX");
+CREATE TABLE posix_text1 PARTITION OF posix_text FOR VALUES FROM ('a') TO
('m');
+CREATE TABLE posix_text2 PARTITION OF posix_text FOR VALUES FROM ('m') TO ('z
');
+INSERT INTO posix_text SELECT chr(97 + (i-1)%26::int) FROM generate_series(1,
599) i;
+ANALYZE posix_text;
+
+CREATE TABLE c_text (a text) PARTITION BY RANGE (a COLLATE "C");
+CREATE TABLE c_text1 PARTITION OF c_text FOR VALUES FROM ('a') TO ('m');
+CREATE TABLE c_text2 PARTITION OF c_text FOR VALUES FROM ('m') TO ('z ');
+INSERT INTO c_text SELECT chr(97 + (i-1)%26::int) FROM generate_series(1, 599)
i;
+ANALYZE c_text;
+
+EXPLAIN (COSTS OFF)
+SELECT p.a, c.a FROM posix_text p JOIN c_text c ON (p.a = c.a) ORDER BY 1;
+
+DROP TABLE posix_text, c_text;
