On Fri Aug 28, 2026, Jan Nidzwetzki wrote:
> From my understanding, outer-join clauses don't form an equivalence
> class. So, have_partkey_equi_join() can only prove the keys equal by
> matching the join clause through match_expr_to_partition_keys(), which
> strips the clause operand but compares it against rel->partexprs
> unstripped.
I think that's right, for a non-degenerate outer-join qual,
distribute_qual_to_rels() sets maybe_equivalence = false , so
process_equivalence() is never called, the comment there gives the
reason, that the two sides may be unequal above the join once one of
them has gone to NULL. So loop 2 has nothing to work with and loop 1 is
the only path, which is exactly where the unstripped partexprs
comparison bites.
Your 0003 patch looks correct to me, I've just made two additions to its
test:
1. The fix strips both rel->partexprs and rel->nullable_partexprs, but a
two-way LEFT JOIN only ever consults the non-nullable list, so half the
change had no coverage. I added a three-way case:
SELECT count(*) FROM (pht5 t1 LEFT JOIN pht6 t2 ON t1.c::text = t2.c::text)
LEFT JOIN pht7 t3 ON t2.c::text = t3.c::text;
Here the upper clause references t2.c, which can only be in the (pht5,
pht6) joinrel's nullable_partexprs, t1.c is the non-nullable entry and
has a different varno. This was fully non-partitionwise before 0003 and
is fully partitionwise after it, so it does cover the nullable half.
2. Every c value was present in both tables, so the LEFT JOIN produced
no null-extended rows at all. I added one key with no match in pht6 and
changed the query to report count(*) and count(t2.a), so the difference
makes the null-extended row visible and the test checks outer join
semantics rather than just the plan shape.
I also set max_parallel_workers_per_gather = 0 for these, since the
three-way query otherwise picked up a Gather and a Parallel Append whose
child order varied.
> Patch 3 is the new one, which could potentially be squashed into patch 2
> if you find the modification useful.
I've left it separate for now although I think that make sense to
squash it into 0002. Let's see if a committer have any thoughts on this.
While checking whether match_expr_to_partition_keys(), I found one more
case, and I've fixed on 0004. group_by_has_partkey() strips RelabelType
from the grouping expressions but not from the partition key expression.
The effect is that full partitionwise aggregation is never chosen for a
partition key involving a binary-compatible cast, and the plan falls
back to partial aggregation with a finalize step. Both spellings fail,
for slightly different reasons: GROUP BY c compares a bare Var against a
RelabelType, and GROUP BY c::text strips the grouping side to a bare Var
while the partition key side stays wrapped.
v7 attached. 0001 and 0002 remains the same. 0003 is your patch with new
test cases added and 0004 is the new one.
--
Matheus Alcantara
EDB: https://www.enterprisedb.com
From f9c747e2d5fa304822292be62e01803de03113d8 Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <[email protected]>
Date: Wed, 26 Aug 2026 15:45:56 -0300
Subject: [PATCH v7 1/4] Fix partition pruning for partition keys wrapped by
RelabelType
match_clause_to_partition_key() strips RelabelType decorations from the
operands of the clause being matched, but not from the partition key
expression itself. When the partition key is an expression involving a
binary-compatible cast, for example
PARTITION BY LIST ((col::text))
on a varchar column, the stored partition key expression is itself a
RelabelType. The equal() comparisons against the stripped clause
operands therefore never match, so no pruning steps are generated at all
and every partition is scanned regardless of the clause.
Fix by stripping the partition key expression the same way, once, up
front. This covers all of the clause shapes handled by the function
(OpExpr, ScalarArrayOpExpr, NullTest, and the Boolean clause forms
recognized by match_boolean_partition_clause()), since they all receive
the same partkey.
Collation correctness does not depend on the partition key expression's
exposed collation: for the operator clause forms it is established by
checking the clause's input collation against the partition collation via
PartCollMatchesExprColl(), and nullness and Boolean tests do not depend
on collation at all.
Reviewed-by: Jan Nidzwetzki <[email protected]>
Discussion: https://postgr.es/m/[email protected]
---
src/backend/partitioning/partprune.c | 10 ++++
src/test/regress/expected/partition_prune.out | 51 +++++++++++++++++++
src/test/regress/sql/partition_prune.sql | 17 +++++++
3 files changed, 78 insertions(+)
diff --git a/src/backend/partitioning/partprune.c
b/src/backend/partitioning/partprune.c
index 5e98f503244..dc87b9b9329 100644
--- a/src/backend/partitioning/partprune.c
+++ b/src/backend/partitioning/partprune.c
@@ -1836,6 +1836,16 @@
match_clause_to_partition_key(GeneratePruningStepsContext *context,
Expr *expr;
bool notclause;
+ /*
+ * Strip any RelabelType from the partition key expression itself, to
+ * match the stripping already done below on the clause operands.
+ * Partition key expressions can be wrapped in a RelabelType. Collation
+ * correctness doesn't depend on keeping the RelabelType here, since
it's
+ * separately verified below via PartCollMatchesExprColl().
+ */
+ while (IsA(partkey, RelabelType))
+ partkey = ((const RelabelType *) partkey)->arg;
+
/*
* Recognize specially shaped clauses that match a Boolean partition
key.
*/
diff --git a/src/test/regress/expected/partition_prune.out
b/src/test/regress/expected/partition_prune.out
index 8f1119a026d..899df49fbc1 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -186,6 +186,57 @@ explain (costs off) select * from coll_pruning where a
collate "POSIX" = 'a' col
Filter: ((a)::text = 'a'::text COLLATE "POSIX")
(7 rows)
+-- the partition key expression involves a binary-compatible cast, so it is
+-- stored wrapped in a RelabelType; that has to be ignored when matching
+-- clauses to the partition key, else no pruning occurs
+create table cast_pruning (a int, c varchar(40)) partition by list ((c::text));
+create table cast_pruning_a partition of cast_pruning for values in ('a');
+create table cast_pruning_b partition of cast_pruning for values in ('b');
+create table cast_pruning_null partition of cast_pruning for values in (null);
+insert into cast_pruning values (1, 'a'), (2, 'b'), (3, null);
+explain (costs off) select * from cast_pruning where c = 'a';
+ QUERY PLAN
+-----------------------------------------
+ Seq Scan on cast_pruning_a cast_pruning
+ Filter: ((c)::text = 'a'::text)
+(2 rows)
+
+explain (costs off) select * from cast_pruning where c in ('a', 'b');
+ QUERY PLAN
+-----------------------------------------------------
+ Append
+ -> Seq Scan on cast_pruning_a cast_pruning_1
+ Filter: ((c)::text = ANY ('{a,b}'::text[]))
+ -> Seq Scan on cast_pruning_b cast_pruning_2
+ Filter: ((c)::text = ANY ('{a,b}'::text[]))
+(5 rows)
+
+explain (costs off) select * from cast_pruning where c is null;
+ QUERY PLAN
+--------------------------------------------
+ Seq Scan on cast_pruning_null cast_pruning
+ Filter: (c IS NULL)
+(2 rows)
+
+select * from cast_pruning where c = 'a';
+ a | c
+---+---
+ 1 | a
+(1 row)
+
+select * from cast_pruning where c in ('a', 'b') order by a;
+ a | c
+---+---
+ 1 | a
+ 2 | b
+(2 rows)
+
+select * from cast_pruning where c is null;
+ a | c
+---+---
+ 3 |
+(1 row)
+
create table rlp (a int, b varchar) partition by range (a);
create table rlp_default partition of rlp default partition by list (a);
create table rlp_default_default partition of rlp_default default;
diff --git a/src/test/regress/sql/partition_prune.sql
b/src/test/regress/sql/partition_prune.sql
index f967658d4b5..918d24a5b11 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -53,6 +53,23 @@ explain (costs off) select * from coll_pruning where a
collate "C" = 'a' collate
-- collation doesn't match the partitioning collation, no pruning occurs
explain (costs off) select * from coll_pruning where a collate "POSIX" = 'a'
collate "POSIX";
+-- the partition key expression involves a binary-compatible cast, so it is
+-- stored wrapped in a RelabelType; that has to be ignored when matching
+-- clauses to the partition key, else no pruning occurs
+create table cast_pruning (a int, c varchar(40)) partition by list ((c::text));
+create table cast_pruning_a partition of cast_pruning for values in ('a');
+create table cast_pruning_b partition of cast_pruning for values in ('b');
+create table cast_pruning_null partition of cast_pruning for values in (null);
+insert into cast_pruning values (1, 'a'), (2, 'b'), (3, null);
+
+explain (costs off) select * from cast_pruning where c = 'a';
+explain (costs off) select * from cast_pruning where c in ('a', 'b');
+explain (costs off) select * from cast_pruning where c is null;
+
+select * from cast_pruning where c = 'a';
+select * from cast_pruning where c in ('a', 'b') order by a;
+select * from cast_pruning where c is null;
+
create table rlp (a int, b varchar) partition by range (a);
create table rlp_default partition of rlp default partition by list (a);
create table rlp_default_default partition of rlp_default default;
--
2.50.1 (Apple Git-155)
From 4b24d1a6918ae51b53dbd0958a78f86cd8875474 Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <[email protected]>
Date: Wed, 26 Aug 2026 15:49:42 -0300
Subject: [PATCH v7 2/4] Enable partitionwise join for partition keys wrapped
by RelabelType
The function exprs_known_equal() is used by the planner to determine if
two expressions are semantically equivalent, often by checking if they
belong to the same Equivalence Class (EC).
Either side of that comparison may be decorated with a binary-compatible
RelabelType, and which side carries it varies. When a partitioned table
uses a varchar(N) type as a partition key, the expression stored in the
equivalence class member (em->em_expr) is wrapped in a RelabelType while
the input expression (item1 or item2) is a plain Var; when the partition
key is itself an expression involving a binary-compatible cast, such as
PARTITION BY HASH ((col::text)), it is the input expression that is
wrapped. Comparing a wrapped expression against an unwrapped one leads
to an incorrect equal() comparison and fails to detect a known
equivalence. This prevents the planner from recognizing that a join
condition matches the partition keys, thereby disabling optimizations
like partitionwise joins.
This commit modifies exprs_known_equal() to strip RelabelType
decorations from both sides of the comparison: the EC member's
expression as well as the input expressions (item1 and item2).
Stripping only proceeds through layers that don't change the
expression's exposed collation. canonicalize_ec_expression()
deliberately wraps equivalence class members in a collation-changing
RelabelType so that every member of a class exposes that class's
collation, and discarding one is not safe: under a non-deterministic
collation, equality in the EC's collation differs from equality in the
partition collation, so a table's partitions are not join-closed and
reporting such keys as equal would let have_partkey_equi_join() choose a
partitionwise join that silently omits matching rows.
Co-authored-by: Jian He
Reviewed-by: Jan Nidzwetzki <[email protected]>
Discussion: https://postgr.es/m/[email protected]
---
.../postgres_fdw/expected/postgres_fdw.out | 22 +++++++
contrib/postgres_fdw/sql/postgres_fdw.sql | 17 ++++++
src/backend/optimizer/path/equivclass.c | 52 ++++++++++++++++-
.../regress/expected/collate.icu.utf8.out | 46 +++++++++++++++
src/test/regress/expected/partition_join.out | 58 +++++++++++++++++++
src/test/regress/sql/collate.icu.utf8.sql | 26 +++++++++
src/test/regress/sql/partition_join.sql | 37 ++++++++++++
7 files changed, 256 insertions(+), 2 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out
b/contrib/postgres_fdw/expected/postgres_fdw.out
index 517d15cf1fa..24c3cf31588 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -11192,6 +11192,16 @@ CREATE FOREIGN TABLE ftprt2_p2 PARTITION OF fprt2 FOR
VALUES FROM (250) TO (500)
ANALYZE fprt2;
ANALYZE fprt2_p1;
ANALYZE fprt2_p2;
+CREATE TABLE fprt3 (a int, b int, c varchar(40)) PARTITION BY HASH(c);
+CREATE TABLE fprt3_ft (a int, b int, c varchar(40));
+CREATE TABLE fprt3_p1 PARTITION OF fprt3 FOR VALUES WITH (MODULUS 2, REMAINDER
0);
+CREATE FOREIGN TABLE fprt3_p2 PARTITION OF fprt3 FOR VALUES WITH (MODULUS 2,
REMAINDER 1)
+ SERVER loopback OPTIONS (table_name 'fprt3_ft');
+CREATE TABLE fprt4 (a int, b int, c varchar(40)) PARTITION BY HASH(c);
+CREATE TABLE fprt4_ft (a int, b int, c varchar(40));
+CREATE TABLE fprt4_p1 PARTITION OF fprt4 FOR VALUES WITH (MODULUS 2, REMAINDER
0);
+CREATE FOREIGN TABLE fprt4_p2 PARTITION OF fprt4 FOR VALUES WITH (MODULUS 2,
REMAINDER 1)
+ SERVER loopback OPTIONS (table_name 'fprt4_ft');
-- inner join three tables
EXPLAIN (COSTS OFF)
SELECT t1.a,t2.b,t3.c FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) INNER
JOIN fprt1 t3 ON (t2.b = t3.a) WHERE t1.a % 25 =0 ORDER BY 1,2,3;
@@ -11361,6 +11371,18 @@ SELECT t1.a, t2.b FROM fprt1 t1 INNER JOIN fprt2 t2 ON
(t1.a = t2.b) WHERE t1.a
400 | 400
(4 rows)
+-- with a varchar partition key, the partitionwise join lets the whole join be
+-- pushed down to the remote server
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT fprt3.a, fprt4.a FROM fprt3 JOIN fprt4 ON fprt3.c = fprt4.c WHERE
fprt3.c = '0002';
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------
+ Foreign Scan
+ Output: fprt3.a, fprt4.a
+ Relations: (public.fprt3_p2 fprt3) INNER JOIN (public.fprt4_p2 fprt4)
+ Remote SQL: SELECT r4.a, r5.a FROM (public.fprt3_ft r4 INNER JOIN
public.fprt4_ft r5 ON (((r5.c = '0002')) AND ((r4.c = '0002'))))
+(4 rows)
+
RESET enable_partitionwise_join;
-- ===================================================================
-- test partitionwise aggregates
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql
b/contrib/postgres_fdw/sql/postgres_fdw.sql
index ec766e2b28a..041b81d6885 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -3693,6 +3693,18 @@ ANALYZE fprt2;
ANALYZE fprt2_p1;
ANALYZE fprt2_p2;
+CREATE TABLE fprt3 (a int, b int, c varchar(40)) PARTITION BY HASH(c);
+CREATE TABLE fprt3_ft (a int, b int, c varchar(40));
+CREATE TABLE fprt3_p1 PARTITION OF fprt3 FOR VALUES WITH (MODULUS 2, REMAINDER
0);
+CREATE FOREIGN TABLE fprt3_p2 PARTITION OF fprt3 FOR VALUES WITH (MODULUS 2,
REMAINDER 1)
+ SERVER loopback OPTIONS (table_name 'fprt3_ft');
+
+CREATE TABLE fprt4 (a int, b int, c varchar(40)) PARTITION BY HASH(c);
+CREATE TABLE fprt4_ft (a int, b int, c varchar(40));
+CREATE TABLE fprt4_p1 PARTITION OF fprt4 FOR VALUES WITH (MODULUS 2, REMAINDER
0);
+CREATE FOREIGN TABLE fprt4_p2 PARTITION OF fprt4 FOR VALUES WITH (MODULUS 2,
REMAINDER 1)
+ SERVER loopback OPTIONS (table_name 'fprt4_ft');
+
-- inner join three tables
EXPLAIN (COSTS OFF)
SELECT t1.a,t2.b,t3.c FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) INNER
JOIN fprt1 t3 ON (t2.b = t3.a) WHERE t1.a % 25 =0 ORDER BY 1,2,3;
@@ -3723,6 +3735,11 @@ EXPLAIN (COSTS OFF)
SELECT t1.a, t2.b FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) WHERE
t1.a % 25 = 0 ORDER BY 1,2 FOR UPDATE OF t1;
SELECT t1.a, t2.b FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.a = t2.b) WHERE
t1.a % 25 = 0 ORDER BY 1,2 FOR UPDATE OF t1;
+-- with a varchar partition key, the partitionwise join lets the whole join be
+-- pushed down to the remote server
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT fprt3.a, fprt4.a FROM fprt3 JOIN fprt4 ON fprt3.c = fprt4.c WHERE
fprt3.c = '0002';
+
RESET enable_partitionwise_join;
diff --git a/src/backend/optimizer/path/equivclass.c
b/src/backend/optimizer/path/equivclass.c
index 393a7a69742..df2717d9584 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -2583,6 +2583,40 @@ find_join_domain(PlannerInfo *root, Relids relids)
}
+/*
+ * strip_collation_preserving_relabel
+ * Remove any leading RelabelType decorations from "node", but only the
+ * layers that don't change the expression's exposed collation.
+ *
+ * This lets us match expressions that differ only by a binary-compatible
+ * cast, such as varchar to text.
+ *
+ * Layers that do change the exposed collation must be kept. Note that
+ * canonicalize_ec_expression() deliberately wraps equivalence class members
+ * in such a RelabelType, so that every member of a class exposes that
+ * class's collation; that wrapper is not mere decoration. Under a
+ * non-deterministic collation, equality in the equivalence class's collation
+ * differs from equality in the partition collation, so a table's partitions
+ * are not join-closed and reporting such keys as equal would let
+ * have_partkey_equi_join() choose a partitionwise join that silently omits
+ * matching rows. We can't leave that check to the caller, since
+ * exprs_known_equal() returns only a boolean and its callers never learn the
+ * collation of the equivalence class that proved the equality.
+ */
+static Node *
+strip_collation_preserving_relabel(Node *node)
+{
+ while (node && IsA(node, RelabelType))
+ {
+ RelabelType *re = (RelabelType *) node;
+
+ if (re->resultcollid != exprCollation((Node *) re->arg))
+ break;
+ node = (Node *) re->arg;
+ }
+ return node;
+}
+
/*
* exprs_known_equal
* Detect whether two expressions are known equal due to equivalence
@@ -2602,6 +2636,17 @@ exprs_known_equal(PlannerInfo *root, Node *item1, Node
*item2, Oid opfamily)
{
ListCell *lc1;
+ /*
+ * Strip any collation-preserving RelabelType decorations from the input
+ * expressions, so that they can still be matched below against EC
members
+ * that get the same treatment. This is needed because, e.g., partition
+ * key expressions stored in an EC member may be wrapped in a
RelabelType
+ * (binary-compatible cast) that the caller-supplied item may or may not
+ * also carry.
+ */
+ item1 = strip_collation_preserving_relabel(item1);
+ item2 = strip_collation_preserving_relabel(item2);
+
foreach(lc1, root->eq_classes)
{
EquivalenceClass *ec = (EquivalenceClass *) lfirst(lc1);
@@ -2628,12 +2673,15 @@ exprs_known_equal(PlannerInfo *root, Node *item1, Node
*item2, Oid opfamily)
foreach(lc2, ec->ec_members)
{
EquivalenceMember *em = (EquivalenceMember *)
lfirst(lc2);
+ Node *expr;
/* Child members should not exist in ec_members */
Assert(!em->em_is_child);
- if (equal(item1, em->em_expr))
+
+ expr = strip_collation_preserving_relabel((Node *)
em->em_expr);
+ if (equal(item1, expr))
item1member = true;
- else if (equal(item2, em->em_expr))
+ else if (equal(item2, expr))
item2member = true;
/* Exit as soon as equality is proven */
if (item1member && item2member)
diff --git a/src/test/regress/expected/collate.icu.utf8.out
b/src/test/regress/expected/collate.icu.utf8.out
index fcfcc658bea..130997f9928 100644
--- a/src/test/regress/expected/collate.icu.utf8.out
+++ b/src/test/regress/expected/collate.icu.utf8.out
@@ -3574,11 +3574,57 @@ SELECT t1.c, count(t2.c) FROM pagg_tab5 t1 JOIN
pagg_tab6 t2 ON t1.c = t2.c AND
d | 9
(4 rows)
+-- Here the partition key collation is the same as the collation of the
+-- partitioned column, so a partitionwise join can't be rejected by comparing
+-- those two. The equality of the partition keys is proven only by an
+-- equivalence class, whose collation (case_insensitive) is not the partition
+-- key collation. Under a non-deterministic collation the partitions are not
+-- join-closed, so a partitionwise join would give a wrong answer here (it
+-- would return 5 rather than 9).
+SET enable_partitionwise_join TO true;
+CREATE TABLE pwj_tab1 (a int, b text) PARTITION BY LIST (b);
+CREATE TABLE pwj_tab1_p1 PARTITION OF pwj_tab1 FOR VALUES IN ('AbC');
+CREATE TABLE pwj_tab1_p2 PARTITION OF pwj_tab1 FOR VALUES IN ('abc', 'ABC');
+CREATE TABLE pwj_tab2 (a int, b text) PARTITION BY LIST (b);
+CREATE TABLE pwj_tab2_p1 PARTITION OF pwj_tab2 FOR VALUES IN ('AbC');
+CREATE TABLE pwj_tab2_p2 PARTITION OF pwj_tab2 FOR VALUES IN ('abc', 'ABC');
+INSERT INTO pwj_tab1 VALUES (1, 'abc'), (2, 'ABC'), (3, 'AbC');
+INSERT INTO pwj_tab2 VALUES (1, 'abc'), (2, 'ABC'), (3, 'AbC');
+ANALYZE pwj_tab1;
+ANALYZE pwj_tab2;
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM pwj_tab1 t1, pwj_tab2 t2 WHERE t1.b = 'abc' COLLATE
case_insensitive AND t2.b = 'abc' COLLATE case_insensitive;
+ QUERY PLAN
+--------------------------------------------------------------------------------------
+ Aggregate
+ -> Nested Loop
+ -> Append
+ -> Seq Scan on pwj_tab1_p2 t1_1
+ Filter: ((b)::text = 'abc'::text COLLATE case_insensitive)
+ -> Seq Scan on pwj_tab1_p1 t1_2
+ Filter: ((b)::text = 'abc'::text COLLATE case_insensitive)
+ -> Materialize
+ -> Append
+ -> Seq Scan on pwj_tab2_p2 t2_1
+ Filter: ((b)::text = 'abc'::text COLLATE
case_insensitive)
+ -> Seq Scan on pwj_tab2_p1 t2_2
+ Filter: ((b)::text = 'abc'::text COLLATE
case_insensitive)
+(13 rows)
+
+SELECT count(*) FROM pwj_tab1 t1, pwj_tab2 t2 WHERE t1.b = 'abc' COLLATE
case_insensitive AND t2.b = 'abc' COLLATE case_insensitive;
+ count
+-------
+ 9
+(1 row)
+
DROP TABLE pagg_tab3;
DROP TABLE pagg_tab4;
DROP TABLE pagg_tab5;
DROP TABLE pagg_tab6;
+DROP TABLE pwj_tab1;
+DROP TABLE pwj_tab2;
RESET enable_partitionwise_aggregate;
+RESET enable_partitionwise_join;
RESET max_parallel_workers_per_gather;
RESET enable_incremental_sort;
--
diff --git a/src/test/regress/expected/partition_join.out
b/src/test/regress/expected/partition_join.out
index 38643d41fd7..8a122a6cc24 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -1750,6 +1750,64 @@ SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c,
t2.c, t3.c FROM pht1 t1, ph
273.0000000000000000 | 273.0000000000000000 | 548.0000000000000000 | 0005 |
0005 | A0005
(6 rows)
+--
+-- hash partitioned by a varchar column and an int column
+--
+-- The partition key for c is a plain Var, but an equivalence class member
+-- derived from comparing it against a text value is wrapped in a RelabelType.
+-- Proving the partition keys equal therefore requires ignoring
+-- binary-compatible relabeling on both sides of the comparison.
+--
+-- Below, c's equality is proven by the equivalence class rather than by a join
+-- clause, which is why both sides are compared to a constant instead of to
+-- each other; d's equality comes from the join clause. Hash pruning needs
+-- every partition key column to be constrained, so constraining only c prunes
+-- nothing and all partitions remain on both sides, which is what makes the
+-- partitionwise join visible here.
+--
+CREATE TABLE pht3 (a int, c varchar(40), d int) PARTITION BY HASH (c, d);
+CREATE TABLE pht3_p1 PARTITION OF pht3 FOR VALUES WITH (MODULUS 2, REMAINDER
0);
+CREATE TABLE pht3_p2 PARTITION OF pht3 FOR VALUES WITH (MODULUS 2, REMAINDER
1);
+INSERT INTO pht3 SELECT i, to_char(i/50, 'FM0000'), i % 10 FROM
generate_series(0, 599, 2) i;
+ANALYZE pht3;
+CREATE TABLE pht4 (a int, c varchar(40), d int) PARTITION BY HASH (c, d);
+CREATE TABLE pht4_p1 PARTITION OF pht4 FOR VALUES WITH (MODULUS 2, REMAINDER
0);
+CREATE TABLE pht4_p2 PARTITION OF pht4 FOR VALUES WITH (MODULUS 2, REMAINDER
1);
+INSERT INTO pht4 SELECT i, to_char(i/50, 'FM0000'), i % 10 FROM
generate_series(0, 599, 3) i;
+ANALYZE pht4;
+-- avoid hash and merge joins, whose costs here are close enough to the
+-- partitionwise nested loop's to make the test output unstable
+SET enable_hashjoin = off;
+SET enable_mergejoin = off;
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t2.a FROM pht3 t1 JOIN pht4 t2 ON t1.d = t2.d WHERE t1.c = '0002'
AND t2.c = '0002';
+ QUERY PLAN
+--------------------------------------------------------
+ Append
+ -> Nested Loop
+ Join Filter: (t1_1.d = t2_1.d)
+ -> Seq Scan on pht3_p1 t1_1
+ Filter: ((c)::text = '0002'::text)
+ -> Materialize
+ -> Seq Scan on pht4_p1 t2_1
+ Filter: ((c)::text = '0002'::text)
+ -> Nested Loop
+ Join Filter: (t1_2.d = t2_2.d)
+ -> Seq Scan on pht3_p2 t1_2
+ Filter: ((c)::text = '0002'::text)
+ -> Materialize
+ -> Seq Scan on pht4_p2 t2_2
+ Filter: ((c)::text = '0002'::text)
+(15 rows)
+
+SELECT count(*) FROM pht3 t1 JOIN pht4 t2 ON t1.d = t2.d WHERE t1.c = '0002'
AND t2.c = '0002';
+ count
+-------
+ 40
+(1 row)
+
+RESET enable_hashjoin;
+RESET enable_mergejoin;
-- test default partition behavior for range
ALTER TABLE prt1 DETACH PARTITION prt1_p3;
ALTER TABLE prt1 ATTACH PARTITION prt1_p3 DEFAULT;
diff --git a/src/test/regress/sql/collate.icu.utf8.sql
b/src/test/regress/sql/collate.icu.utf8.sql
index ce4e2bb3ffd..ea7321f22d7 100644
--- a/src/test/regress/sql/collate.icu.utf8.sql
+++ b/src/test/regress/sql/collate.icu.utf8.sql
@@ -1301,12 +1301,38 @@ EXPLAIN (COSTS OFF)
SELECT t1.c, count(t2.c) FROM pagg_tab5 t1 JOIN pagg_tab6 t2 ON t1.c = t2.c
AND t1.c = t2.b GROUP BY 1 ORDER BY t1.c COLLATE "C";
SELECT t1.c, count(t2.c) FROM pagg_tab5 t1 JOIN pagg_tab6 t2 ON t1.c = t2.c
AND t1.c = t2.b GROUP BY 1 ORDER BY t1.c COLLATE "C";
+-- Here the partition key collation is the same as the collation of the
+-- partitioned column, so a partitionwise join can't be rejected by comparing
+-- those two. The equality of the partition keys is proven only by an
+-- equivalence class, whose collation (case_insensitive) is not the partition
+-- key collation. Under a non-deterministic collation the partitions are not
+-- join-closed, so a partitionwise join would give a wrong answer here (it
+-- would return 5 rather than 9).
+SET enable_partitionwise_join TO true;
+CREATE TABLE pwj_tab1 (a int, b text) PARTITION BY LIST (b);
+CREATE TABLE pwj_tab1_p1 PARTITION OF pwj_tab1 FOR VALUES IN ('AbC');
+CREATE TABLE pwj_tab1_p2 PARTITION OF pwj_tab1 FOR VALUES IN ('abc', 'ABC');
+CREATE TABLE pwj_tab2 (a int, b text) PARTITION BY LIST (b);
+CREATE TABLE pwj_tab2_p1 PARTITION OF pwj_tab2 FOR VALUES IN ('AbC');
+CREATE TABLE pwj_tab2_p2 PARTITION OF pwj_tab2 FOR VALUES IN ('abc', 'ABC');
+INSERT INTO pwj_tab1 VALUES (1, 'abc'), (2, 'ABC'), (3, 'AbC');
+INSERT INTO pwj_tab2 VALUES (1, 'abc'), (2, 'ABC'), (3, 'AbC');
+ANALYZE pwj_tab1;
+ANALYZE pwj_tab2;
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM pwj_tab1 t1, pwj_tab2 t2 WHERE t1.b = 'abc' COLLATE
case_insensitive AND t2.b = 'abc' COLLATE case_insensitive;
+SELECT count(*) FROM pwj_tab1 t1, pwj_tab2 t2 WHERE t1.b = 'abc' COLLATE
case_insensitive AND t2.b = 'abc' COLLATE case_insensitive;
+
DROP TABLE pagg_tab3;
DROP TABLE pagg_tab4;
DROP TABLE pagg_tab5;
DROP TABLE pagg_tab6;
+DROP TABLE pwj_tab1;
+DROP TABLE pwj_tab2;
RESET enable_partitionwise_aggregate;
+RESET enable_partitionwise_join;
RESET max_parallel_workers_per_gather;
RESET enable_incremental_sort;
diff --git a/src/test/regress/sql/partition_join.sql
b/src/test/regress/sql/partition_join.sql
index c4549fc1ad8..2a423f14190 100644
--- a/src/test/regress/sql/partition_join.sql
+++ b/src/test/regress/sql/partition_join.sql
@@ -350,6 +350,43 @@ EXPLAIN (COSTS OFF)
SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM pht1 t1,
pht2 t2, pht1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') =
t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
SELECT avg(t1.a), avg(t2.b), avg(t3.a + t3.b), t1.c, t2.c, t3.c FROM pht1 t1,
pht2 t2, pht1_e t3 WHERE t1.b = t2.b AND t1.c = t2.c AND ltrim(t3.c, 'A') =
t1.c GROUP BY t1.c, t2.c, t3.c ORDER BY t1.c, t2.c, t3.c;
+--
+-- hash partitioned by a varchar column and an int column
+--
+-- The partition key for c is a plain Var, but an equivalence class member
+-- derived from comparing it against a text value is wrapped in a RelabelType.
+-- Proving the partition keys equal therefore requires ignoring
+-- binary-compatible relabeling on both sides of the comparison.
+--
+-- Below, c's equality is proven by the equivalence class rather than by a join
+-- clause, which is why both sides are compared to a constant instead of to
+-- each other; d's equality comes from the join clause. Hash pruning needs
+-- every partition key column to be constrained, so constraining only c prunes
+-- nothing and all partitions remain on both sides, which is what makes the
+-- partitionwise join visible here.
+--
+CREATE TABLE pht3 (a int, c varchar(40), d int) PARTITION BY HASH (c, d);
+CREATE TABLE pht3_p1 PARTITION OF pht3 FOR VALUES WITH (MODULUS 2, REMAINDER
0);
+CREATE TABLE pht3_p2 PARTITION OF pht3 FOR VALUES WITH (MODULUS 2, REMAINDER
1);
+INSERT INTO pht3 SELECT i, to_char(i/50, 'FM0000'), i % 10 FROM
generate_series(0, 599, 2) i;
+ANALYZE pht3;
+
+CREATE TABLE pht4 (a int, c varchar(40), d int) PARTITION BY HASH (c, d);
+CREATE TABLE pht4_p1 PARTITION OF pht4 FOR VALUES WITH (MODULUS 2, REMAINDER
0);
+CREATE TABLE pht4_p2 PARTITION OF pht4 FOR VALUES WITH (MODULUS 2, REMAINDER
1);
+INSERT INTO pht4 SELECT i, to_char(i/50, 'FM0000'), i % 10 FROM
generate_series(0, 599, 3) i;
+ANALYZE pht4;
+
+-- avoid hash and merge joins, whose costs here are close enough to the
+-- partitionwise nested loop's to make the test output unstable
+SET enable_hashjoin = off;
+SET enable_mergejoin = off;
+EXPLAIN (COSTS OFF)
+SELECT t1.a, t2.a FROM pht3 t1 JOIN pht4 t2 ON t1.d = t2.d WHERE t1.c = '0002'
AND t2.c = '0002';
+SELECT count(*) FROM pht3 t1 JOIN pht4 t2 ON t1.d = t2.d WHERE t1.c = '0002'
AND t2.c = '0002';
+RESET enable_hashjoin;
+RESET enable_mergejoin;
+
-- test default partition behavior for range
ALTER TABLE prt1 DETACH PARTITION prt1_p3;
ALTER TABLE prt1 ATTACH PARTITION prt1_p3 DEFAULT;
--
2.50.1 (Apple Git-155)
From 13fd0d2ec87d28d5dc8f02b1f56b4310bdf5354e Mon Sep 17 00:00:00 2001
From: Jan Nidzwetzki <[email protected]>
Date: Fri, 28 Aug 2026 15:03:59 +0200
Subject: [PATCH v7 3/4] Enable partitionwise join for outer joins on
RelabelType-wrapped keys
Strip RelabelType decorations in match_expr_to_partition_keys() to
support partitionwise join for outer joins on RelabelType-wrapped keys.
---
src/backend/optimizer/util/relnode.c | 27 ++++--
src/test/regress/expected/partition_join.out | 88 ++++++++++++++++++++
src/test/regress/sql/partition_join.sql | 43 ++++++++++
3 files changed, 153 insertions(+), 5 deletions(-)
diff --git a/src/backend/optimizer/util/relnode.c
b/src/backend/optimizer/util/relnode.c
index ee69f81945f..1d45a013acf 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -2480,6 +2480,18 @@ have_partkey_equi_join(PlannerInfo *root, RelOptInfo
*joinrel,
return false;
}
+/*
+ * strip_relabel_decorations
+ * Remove any RelabelType decorations from "expr".
+ */
+static Expr *
+strip_relabel_decorations(Expr *expr)
+{
+ while (expr && IsA(expr, RelabelType))
+ expr = ((RelabelType *) expr)->arg;
+ return expr;
+}
+
/*
* match_expr_to_partition_keys
*
@@ -2501,9 +2513,14 @@ match_expr_to_partition_keys(Expr *expr, RelOptInfo
*rel, bool strict_op)
Assert(rel->partexprs);
Assert(rel->nullable_partexprs);
- /* Remove any relabel decorations. */
- while (IsA(expr, RelabelType))
- expr = (Expr *) (castNode(RelabelType, expr))->arg;
+ /*
+ * Remove any relabel decorations, from the clause expression here and
+ * from each partition key expression below. A key involving a
+ * binary-compatible cast is itself stored wrapped in a RelabelType. The
+ * collation is not lost, as the caller compares the clause's
inputcollid
+ * against the partition collation.
+ */
+ expr = strip_relabel_decorations(expr);
for (cnt = 0; cnt < rel->part_scheme->partnatts; cnt++)
{
@@ -2512,7 +2529,7 @@ match_expr_to_partition_keys(Expr *expr, RelOptInfo *rel,
bool strict_op)
/* We can always match to the non-nullable partition keys. */
foreach(lc, rel->partexprs[cnt])
{
- if (equal(lfirst(lc), expr))
+ if (equal(strip_relabel_decorations(lfirst(lc)), expr))
return cnt;
}
@@ -2528,7 +2545,7 @@ match_expr_to_partition_keys(Expr *expr, RelOptInfo *rel,
bool strict_op)
*/
foreach(lc, rel->nullable_partexprs[cnt])
{
- if (equal(lfirst(lc), expr))
+ if (equal(strip_relabel_decorations(lfirst(lc)), expr))
return cnt;
}
}
diff --git a/src/test/regress/expected/partition_join.out
b/src/test/regress/expected/partition_join.out
index 8a122a6cc24..dd25536f85f 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -1806,6 +1806,94 @@ SELECT count(*) FROM pht3 t1 JOIN pht4 t2 ON t1.d = t2.d
WHERE t1.c = '0002' AND
40
(1 row)
+RESET enable_hashjoin;
+RESET enable_mergejoin;
+-- outer join on a partition key that is an expression wrapped in a RelabelType
+CREATE TABLE pht5 (a int, c varchar(40)) PARTITION BY HASH ((c::text));
+CREATE TABLE pht5_p1 PARTITION OF pht5 FOR VALUES WITH (MODULUS 2, REMAINDER
0);
+CREATE TABLE pht5_p2 PARTITION OF pht5 FOR VALUES WITH (MODULUS 2, REMAINDER
1);
+INSERT INTO pht5 SELECT i, to_char(i/50, 'FM0000') FROM generate_series(0,
599, 2) i;
+-- a key with no match in pht6, so that the join produces a null-extended row
+INSERT INTO pht5 VALUES (600, '9999');
+ANALYZE pht5;
+CREATE TABLE pht6 (a int, c varchar(40)) PARTITION BY HASH ((c::text));
+CREATE TABLE pht6_p1 PARTITION OF pht6 FOR VALUES WITH (MODULUS 2, REMAINDER
0);
+CREATE TABLE pht6_p2 PARTITION OF pht6 FOR VALUES WITH (MODULUS 2, REMAINDER
1);
+INSERT INTO pht6 SELECT i, to_char(i/50, 'FM0000') FROM generate_series(0,
599, 3) i;
+ANALYZE pht6;
+CREATE TABLE pht7 (a int, c varchar(40)) PARTITION BY HASH ((c::text));
+CREATE TABLE pht7_p1 PARTITION OF pht7 FOR VALUES WITH (MODULUS 2, REMAINDER
0);
+CREATE TABLE pht7_p2 PARTITION OF pht7 FOR VALUES WITH (MODULUS 2, REMAINDER
1);
+INSERT INTO pht7 SELECT i, to_char(i/50, 'FM0000') FROM generate_series(0,
599, 5) i;
+ANALYZE pht7;
+-- avoid hash and merge joins, whose costs here are close enough to the
+-- partitionwise nested loop's to make the test output unstable
+SET enable_hashjoin = off;
+SET enable_mergejoin = off;
+SET max_parallel_workers_per_gather = 0;
+-- the second count() shows that a null-extended row is produced
+EXPLAIN (COSTS OFF)
+SELECT count(*), count(t2.a) FROM pht5 t1 LEFT JOIN pht6 t2 ON t1.c::text =
t2.c::text;
+ QUERY PLAN
+--------------------------------------------------------------
+ Aggregate
+ -> Append
+ -> Nested Loop Left Join
+ Join Filter: ((t1_1.c)::text = (t2_1.c)::text)
+ -> Seq Scan on pht5_p1 t1_1
+ -> Materialize
+ -> Seq Scan on pht6_p1 t2_1
+ -> Nested Loop Left Join
+ Join Filter: ((t1_2.c)::text = (t2_2.c)::text)
+ -> Seq Scan on pht5_p2 t1_2
+ -> Materialize
+ -> Seq Scan on pht6_p2 t2_2
+(12 rows)
+
+SELECT count(*), count(t2.a) FROM pht5 t1 LEFT JOIN pht6 t2 ON t1.c::text =
t2.c::text;
+ count | count
+-------+-------
+ 5001 | 5000
+(1 row)
+
+-- Here the upper join clause references t2.c, which is a nullable partition
+-- key expression of the (pht5, pht6) join relation, so matching it requires
+-- ignoring relabeling on nullable_partexprs as well.
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM (pht5 t1 LEFT JOIN pht6 t2 ON t1.c::text = t2.c::text)
+ LEFT JOIN pht7 t3 ON t2.c::text = t3.c::text;
+ QUERY PLAN
+--------------------------------------------------------------------------
+ Aggregate
+ -> Append
+ -> Nested Loop Left Join
+ Join Filter: ((t1_1.c)::text = (t2_1.c)::text)
+ -> Seq Scan on pht5_p1 t1_1
+ -> Materialize
+ -> Nested Loop Left Join
+ Join Filter: ((t2_1.c)::text = (t3_1.c)::text)
+ -> Seq Scan on pht6_p1 t2_1
+ -> Materialize
+ -> Seq Scan on pht7_p1 t3_1
+ -> Nested Loop Left Join
+ Join Filter: ((t1_2.c)::text = (t2_2.c)::text)
+ -> Seq Scan on pht5_p2 t1_2
+ -> Materialize
+ -> Nested Loop Left Join
+ Join Filter: ((t2_2.c)::text = (t3_2.c)::text)
+ -> Seq Scan on pht6_p2 t2_2
+ -> Materialize
+ -> Seq Scan on pht7_p2 t3_2
+(20 rows)
+
+SELECT count(*) FROM (pht5 t1 LEFT JOIN pht6 t2 ON t1.c::text = t2.c::text)
+ LEFT JOIN pht7 t3 ON t2.c::text = t3.c::text;
+ count
+-------
+ 50001
+(1 row)
+
+RESET max_parallel_workers_per_gather;
RESET enable_hashjoin;
RESET enable_mergejoin;
-- test default partition behavior for range
diff --git a/src/test/regress/sql/partition_join.sql
b/src/test/regress/sql/partition_join.sql
index 2a423f14190..34e7e1fdaca 100644
--- a/src/test/regress/sql/partition_join.sql
+++ b/src/test/regress/sql/partition_join.sql
@@ -387,6 +387,49 @@ SELECT count(*) FROM pht3 t1 JOIN pht4 t2 ON t1.d = t2.d
WHERE t1.c = '0002' AND
RESET enable_hashjoin;
RESET enable_mergejoin;
+-- outer join on a partition key that is an expression wrapped in a RelabelType
+CREATE TABLE pht5 (a int, c varchar(40)) PARTITION BY HASH ((c::text));
+CREATE TABLE pht5_p1 PARTITION OF pht5 FOR VALUES WITH (MODULUS 2, REMAINDER
0);
+CREATE TABLE pht5_p2 PARTITION OF pht5 FOR VALUES WITH (MODULUS 2, REMAINDER
1);
+INSERT INTO pht5 SELECT i, to_char(i/50, 'FM0000') FROM generate_series(0,
599, 2) i;
+-- a key with no match in pht6, so that the join produces a null-extended row
+INSERT INTO pht5 VALUES (600, '9999');
+ANALYZE pht5;
+
+CREATE TABLE pht6 (a int, c varchar(40)) PARTITION BY HASH ((c::text));
+CREATE TABLE pht6_p1 PARTITION OF pht6 FOR VALUES WITH (MODULUS 2, REMAINDER
0);
+CREATE TABLE pht6_p2 PARTITION OF pht6 FOR VALUES WITH (MODULUS 2, REMAINDER
1);
+INSERT INTO pht6 SELECT i, to_char(i/50, 'FM0000') FROM generate_series(0,
599, 3) i;
+ANALYZE pht6;
+
+CREATE TABLE pht7 (a int, c varchar(40)) PARTITION BY HASH ((c::text));
+CREATE TABLE pht7_p1 PARTITION OF pht7 FOR VALUES WITH (MODULUS 2, REMAINDER
0);
+CREATE TABLE pht7_p2 PARTITION OF pht7 FOR VALUES WITH (MODULUS 2, REMAINDER
1);
+INSERT INTO pht7 SELECT i, to_char(i/50, 'FM0000') FROM generate_series(0,
599, 5) i;
+ANALYZE pht7;
+
+-- avoid hash and merge joins, whose costs here are close enough to the
+-- partitionwise nested loop's to make the test output unstable
+SET enable_hashjoin = off;
+SET enable_mergejoin = off;
+SET max_parallel_workers_per_gather = 0;
+-- the second count() shows that a null-extended row is produced
+EXPLAIN (COSTS OFF)
+SELECT count(*), count(t2.a) FROM pht5 t1 LEFT JOIN pht6 t2 ON t1.c::text =
t2.c::text;
+SELECT count(*), count(t2.a) FROM pht5 t1 LEFT JOIN pht6 t2 ON t1.c::text =
t2.c::text;
+
+-- Here the upper join clause references t2.c, which is a nullable partition
+-- key expression of the (pht5, pht6) join relation, so matching it requires
+-- ignoring relabeling on nullable_partexprs as well.
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM (pht5 t1 LEFT JOIN pht6 t2 ON t1.c::text = t2.c::text)
+ LEFT JOIN pht7 t3 ON t2.c::text = t3.c::text;
+SELECT count(*) FROM (pht5 t1 LEFT JOIN pht6 t2 ON t1.c::text = t2.c::text)
+ LEFT JOIN pht7 t3 ON t2.c::text = t3.c::text;
+RESET max_parallel_workers_per_gather;
+RESET enable_hashjoin;
+RESET enable_mergejoin;
+
-- test default partition behavior for range
ALTER TABLE prt1 DETACH PARTITION prt1_p3;
ALTER TABLE prt1 ATTACH PARTITION prt1_p3 DEFAULT;
--
2.50.1 (Apple Git-155)
From 5c4eab98178b7ae3ad71b27c5d0d0b1db2bf207c Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <[email protected]>
Date: Mon, 31 Aug 2026 10:49:50 -0300
Subject: [PATCH v7 4/4] Enable full partitionwise aggregate for partition keys
wrapped by RelabelType
group_by_has_partkey() decides whether a partitioned aggregate can be
computed independently per partition (PARTITIONWISE_AGGREGATE_FULL) or
whether partial aggregates have to be combined afterwards
(PARTITIONWISE_AGGREGATE_PARTIAL). It does so by matching each partition
key expression against the GROUP BY expressions with equal().
The grouping expressions have their RelabelType decorations stripped
before that comparison, but the partition key expressions do not. When
the partition key is an expression involving a binary-compatible cast,
for example
PARTITION BY LIST ((col::text))
on a varchar column, the stored partition key expression is itself a
RelabelType, so no GROUP BY expression can ever match it: grouping by the
column compares a bare Var against a RelabelType, and spelling out the
cast strips the grouping side down to a bare Var while the partition key
side stays wrapped. Full partitionwise aggregation is therefore never
chosen for such a key, and the plan falls back to partial aggregation
with a finalize step on top.
Fix by stripping RelabelType from the partition key expression as well.
Collation correctness is unaffected: the partition collation comes from
the PartitionScheme rather than from the expression, and the grouping
expression's collation is captured before it is stripped, so the existing
comparison of the two is unchanged.
Discussion: https://postgr.es/m/[email protected]
---
src/backend/optimizer/plan/planner.c | 11 ++++
.../regress/expected/partition_aggregate.out | 55 +++++++++++++++++++
src/test/regress/sql/partition_aggregate.sql | 20 +++++++
3 files changed, 86 insertions(+)
diff --git a/src/backend/optimizer/plan/planner.c
b/src/backend/optimizer/plan/planner.c
index c3c158a253d..d997e877a5e 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -8534,6 +8534,17 @@ group_by_has_partkey(RelOptInfo *input_rel,
Expr *partexpr = lfirst(lc);
Oid partcoll =
input_rel->part_scheme->partcollation[cnt];
+ /*
+ * Strip any RelabelType decorations, to match the
stripping done
+ * on the grouping expressions below. A partition key
involving a
+ * binary-compatible cast, such as ((col::text)) on a
varchar
+ * column, is itself stored wrapped in a RelabelType.
The
+ * collation is not lost, since partcoll and groupcoll
are
+ * compared separately below.
+ */
+ while (partexpr && IsA(partexpr, RelabelType))
+ partexpr = ((RelabelType *) partexpr)->arg;
+
foreach(lg, groupexprs)
{
Expr *groupexpr = lfirst(lg);
diff --git a/src/test/regress/expected/partition_aggregate.out
b/src/test/regress/expected/partition_aggregate.out
index c30304b99c7..23d657c30a8 100644
--- a/src/test/regress/expected/partition_aggregate.out
+++ b/src/test/regress/expected/partition_aggregate.out
@@ -961,6 +961,61 @@ SELECT a, c, sum(b), avg(c), count(*) FROM pagg_tab_m
GROUP BY (a+b)/2, 2, 1 HAV
20 | 40 | 50 | 40.0000000000000000 | 5
(6 rows)
+-- Partition by an expression that is a binary-compatible cast, so that the
+-- stored partition key expression is itself wrapped in a RelabelType
+CREATE TABLE pagg_tab_v (a int, c varchar(40)) PARTITION BY LIST ((c::text));
+CREATE TABLE pagg_tab_v_p1 PARTITION OF pagg_tab_v FOR VALUES IN ('0000',
'0001');
+CREATE TABLE pagg_tab_v_p2 PARTITION OF pagg_tab_v FOR VALUES IN ('0002');
+INSERT INTO pagg_tab_v SELECT i, to_char(i % 3, 'FM0000') FROM
generate_series(0, 2999) i;
+ANALYZE pagg_tab_v;
+-- Full aggregation as GROUP BY clause matches with PARTITION KEY
+EXPLAIN (COSTS OFF)
+SELECT c, sum(a), count(*) FROM pagg_tab_v GROUP BY c ORDER BY 1;
+ QUERY PLAN
+----------------------------------------------------------
+ Sort
+ Sort Key: pagg_tab_v.c
+ -> Append
+ -> HashAggregate
+ Group Key: pagg_tab_v.c
+ -> Seq Scan on pagg_tab_v_p1 pagg_tab_v
+ -> HashAggregate
+ Group Key: pagg_tab_v_1.c
+ -> Seq Scan on pagg_tab_v_p2 pagg_tab_v_1
+(9 rows)
+
+SELECT c, sum(a), count(*) FROM pagg_tab_v GROUP BY c ORDER BY 1;
+ c | sum | count
+------+---------+-------
+ 0000 | 1498500 | 1000
+ 0001 | 1499500 | 1000
+ 0002 | 1500500 | 1000
+(3 rows)
+
+-- Full aggregation also when the GROUP BY clause spells out the cast
+EXPLAIN (COSTS OFF)
+SELECT c::text, sum(a), count(*) FROM pagg_tab_v GROUP BY c::text ORDER BY 1;
+ QUERY PLAN
+----------------------------------------------------------
+ Sort
+ Sort Key: ((pagg_tab_v.c)::text)
+ -> Append
+ -> HashAggregate
+ Group Key: (pagg_tab_v.c)::text
+ -> Seq Scan on pagg_tab_v_p1 pagg_tab_v
+ -> HashAggregate
+ Group Key: (pagg_tab_v_1.c)::text
+ -> Seq Scan on pagg_tab_v_p2 pagg_tab_v_1
+(9 rows)
+
+SELECT c::text, sum(a), count(*) FROM pagg_tab_v GROUP BY c::text ORDER BY 1;
+ c | sum | count
+------+---------+-------
+ 0000 | 1498500 | 1000
+ 0001 | 1499500 | 1000
+ 0002 | 1500500 | 1000
+(3 rows)
+
-- Test with multi-level partitioning scheme
CREATE TABLE pagg_tab_ml (a int, b int, c text) PARTITION BY RANGE(a);
CREATE TABLE pagg_tab_ml_p1 PARTITION OF pagg_tab_ml FOR VALUES FROM (0) TO
(12);
diff --git a/src/test/regress/sql/partition_aggregate.sql
b/src/test/regress/sql/partition_aggregate.sql
index 7c725e2663a..77566fbf146 100644
--- a/src/test/regress/sql/partition_aggregate.sql
+++ b/src/test/regress/sql/partition_aggregate.sql
@@ -208,6 +208,26 @@ SELECT a, c, sum(b), avg(c), count(*) FROM pagg_tab_m
GROUP BY (a+b)/2, 2, 1 HAV
SELECT a, c, sum(b), avg(c), count(*) FROM pagg_tab_m GROUP BY (a+b)/2, 2, 1
HAVING sum(b) = 50 AND avg(c) > 25 ORDER BY 1, 2, 3;
+-- Partition by an expression that is a binary-compatible cast, so that the
+-- stored partition key expression is itself wrapped in a RelabelType
+
+CREATE TABLE pagg_tab_v (a int, c varchar(40)) PARTITION BY LIST ((c::text));
+CREATE TABLE pagg_tab_v_p1 PARTITION OF pagg_tab_v FOR VALUES IN ('0000',
'0001');
+CREATE TABLE pagg_tab_v_p2 PARTITION OF pagg_tab_v FOR VALUES IN ('0002');
+INSERT INTO pagg_tab_v SELECT i, to_char(i % 3, 'FM0000') FROM
generate_series(0, 2999) i;
+ANALYZE pagg_tab_v;
+
+-- Full aggregation as GROUP BY clause matches with PARTITION KEY
+EXPLAIN (COSTS OFF)
+SELECT c, sum(a), count(*) FROM pagg_tab_v GROUP BY c ORDER BY 1;
+SELECT c, sum(a), count(*) FROM pagg_tab_v GROUP BY c ORDER BY 1;
+
+-- Full aggregation also when the GROUP BY clause spells out the cast
+EXPLAIN (COSTS OFF)
+SELECT c::text, sum(a), count(*) FROM pagg_tab_v GROUP BY c::text ORDER BY 1;
+SELECT c::text, sum(a), count(*) FROM pagg_tab_v GROUP BY c::text ORDER BY 1;
+
+
-- Test with multi-level partitioning scheme
CREATE TABLE pagg_tab_ml (a int, b int, c text) PARTITION BY RANGE(a);
--
2.50.1 (Apple Git-155)
