On 2018/02/28 1:05, Robert Haas wrote:
> On Mon, Feb 26, 2018 at 10:59 PM, Amit Langote
> <[email protected]> wrote:
>> You may say that partition bounds might have to be different too in this
>> case and hence partition-wise join won't occur anyway, but I'm wondering
>> if the mismatch of partcollation itself isn't enough to conclude that?
>
> Yeah, you're right. I think that this is just a bug in partition-wise
> join, and that the partition scheme should just be using partcollation
> instead of parttypcoll, as in the attached.
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
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.
Also attached updated version of your patch (fixed the typo).
Thanks,
Amit
diff --git a/src/backend/optimizer/util/plancat.c
b/src/backend/optimizer/util/plancat.c
index 60f21711f4..b799e249db 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -1891,7 +1891,7 @@ find_partition_scheme(PlannerInfo *root, Relation
relation)
sizeof(Oid) * partnatts) != 0 ||
memcmp(partkey->partopcintype,
part_scheme->partopcintype,
sizeof(Oid) * partnatts) != 0 ||
- memcmp(partkey->parttypcoll, part_scheme->parttypcoll,
+ memcmp(partkey->partcollation,
part_scheme->partcollation,
sizeof(Oid) * partnatts) != 0)
continue;
@@ -1926,8 +1926,8 @@ find_partition_scheme(PlannerInfo *root, Relation
relation)
memcpy(part_scheme->partopcintype, partkey->partopcintype,
sizeof(Oid) * partnatts);
- part_scheme->parttypcoll = (Oid *) palloc(sizeof(Oid) * partnatts);
- memcpy(part_scheme->parttypcoll, partkey->parttypcoll,
+ part_scheme->partcollation = (Oid *) palloc(sizeof(Oid) * partnatts);
+ memcpy(part_scheme->partcollation, partkey->partcollation,
sizeof(Oid) * partnatts);
part_scheme->parttyplen = (int16 *) palloc(sizeof(int16) * partnatts);
diff --git a/src/include/nodes/relation.h b/src/include/nodes/relation.h
index db8de2dfd0..d576aa7350 100644
--- a/src/include/nodes/relation.h
+++ b/src/include/nodes/relation.h
@@ -351,7 +351,7 @@ typedef struct PartitionSchemeData
int16 partnatts; /* number of partition
attributes */
Oid *partopfamily; /* OIDs of operator families */
Oid *partopcintype; /* OIDs of opclass declared
input data types */
- Oid *parttypcoll; /* OIDs of collations of
partition keys. */
+ Oid *partcollation; /* OIDs of partitioning
collations */
/* Cached information about partition key data types. */
int16 *parttyplen;
diff --git a/src/test/regress/expected/partition_join.out
b/src/test/regress/expected/partition_join.out
index 4fccd9ae54..6323a13777 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -1869,3 +1869,31 @@ 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
');
+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 ');
+EXPLAIN (COSTS OFF)
+SELECT * FROM posix_text p JOIN c_text c ON (p.a = c.a);
+ 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..0df7df487d 100644
--- a/src/test/regress/sql/partition_join.sql
+++ b/src/test/regress/sql/partition_join.sql
@@ -384,3 +384,19 @@ 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
');
+
+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 ');
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM posix_text p JOIN c_text c ON (p.a = c.a);
+
+DROP TABLE posix_text, c_text;
