v2-0001-Fix-collation-different-between-columan-collation.patch

From 802f231501cdf11361a0d1e10adbb84fd9261ff1 Mon Sep 17 00:00:00 2001
From: Tender Wang <tndrwang@gmail.com>
Date: Tue, 6 Aug 2024 16:38:03 +0800
Subject: [PATCH v2] Fix collation different between columan collation and
 partkey collation

---
 src/backend/optimizer/util/plancat.c         |  2 +-
 src/test/regress/expected/partition_info.out | 52 ++++++++++++++++++++
 src/test/regress/sql/partition_info.sql      | 22 +++++++++
 3 files changed, 75 insertions(+), 1 deletion(-)

diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 78a3cfafde..29e33150f1 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -2531,7 +2531,7 @@ set_baserel_partition_key_exprs(Relation relation,
 			partexpr = (Expr *) makeVar(varno, attno,
 										partkey->parttypid[cnt],
 										partkey->parttypmod[cnt],
-										partkey->parttypcoll[cnt], 0);
+										partkey->partcollation[cnt], 0);
 		}
 		else
 		{
diff --git a/src/test/regress/expected/partition_info.out b/src/test/regress/expected/partition_info.out
index 42b6bc77ca..e68ff1fbe8 100644
--- a/src/test/regress/expected/partition_info.out
+++ b/src/test/regress/expected/partition_info.out
@@ -349,3 +349,55 @@ SELECT pg_partition_root('ptif_li_child');
 DROP VIEW ptif_test_view;
 DROP MATERIALIZED VIEW ptif_test_matview;
 DROP TABLE ptif_li_parent, ptif_li_child;
+-- Test collation
+SET enable_partitionwise_aggregate TO true;
+CREATE COLLATION case_insensitive (
+       provider = icu,
+       locale = 'und-u-ks-level2',
+       deterministic = false);
+CREATE TABLE pagg_tab_col (c text collate case_insensitive) PARTITION BY LIST(c collate "C");
+CREATE TABLE pagg_tab_col_p1 PARTITION OF pagg_tab_col FOR VALUES IN ('a', 'b', 'c', 'd');
+CREATE TABLE pagg_tab_col_p2 PARTITION OF pagg_tab_col FOR VALUES IN ('e', 'f','A');
+CREATE TABLE pagg_tab_col_p3 PARTITION OF pagg_tab_col FOR VALUES IN ('B', 'C', 'D', 'E');
+INSERT INTO pagg_tab_col SELECT substr('abcdeABCDE', (i % 10) +1 , 1) FROM generate_series(0, 2999) i;
+ANALYZE pagg_tab_col;
+EXPLAIN(VERBOSE, COSTS OFF) SELECT c collate case_insensitive, count(c) FROM
+pagg_tab_col GROUP BY c collate case_insensitive;
+                                  QUERY PLAN                                   
+-------------------------------------------------------------------------------
+ Finalize GroupAggregate
+   Output: pagg_tab_col.c, count(pagg_tab_col.c)
+   Group Key: pagg_tab_col.c
+   ->  Sort
+         Output: pagg_tab_col.c, (PARTIAL count(pagg_tab_col.c))
+         Sort Key: pagg_tab_col.c COLLATE case_insensitive
+         ->  Append
+               ->  Partial HashAggregate
+                     Output: pagg_tab_col.c, PARTIAL count(pagg_tab_col.c)
+                     Group Key: pagg_tab_col.c
+                     ->  Seq Scan on public.pagg_tab_col_p2 pagg_tab_col
+                           Output: pagg_tab_col.c
+               ->  Partial HashAggregate
+                     Output: pagg_tab_col_1.c, PARTIAL count(pagg_tab_col_1.c)
+                     Group Key: pagg_tab_col_1.c
+                     ->  Seq Scan on public.pagg_tab_col_p3 pagg_tab_col_1
+                           Output: pagg_tab_col_1.c
+               ->  Partial HashAggregate
+                     Output: pagg_tab_col_2.c, PARTIAL count(pagg_tab_col_2.c)
+                     Group Key: pagg_tab_col_2.c
+                     ->  Seq Scan on public.pagg_tab_col_p1 pagg_tab_col_2
+                           Output: pagg_tab_col_2.c
+(22 rows)
+
+SELECT c collate case_insensitive, count(c) FROM
+pagg_tab_col GROUP BY c collate case_insensitive;
+ c | count 
+---+-------
+ a |   600
+ b |   600
+ C |   600
+ d |   600
+ E |   600
+(5 rows)
+
+RESET enable_partitionwise_aggregate;
diff --git a/src/test/regress/sql/partition_info.sql b/src/test/regress/sql/partition_info.sql
index b5060bec7f..5c1a7bab5d 100644
--- a/src/test/regress/sql/partition_info.sql
+++ b/src/test/regress/sql/partition_info.sql
@@ -127,3 +127,25 @@ SELECT pg_partition_root('ptif_li_child');
 DROP VIEW ptif_test_view;
 DROP MATERIALIZED VIEW ptif_test_matview;
 DROP TABLE ptif_li_parent, ptif_li_child;
+
+-- Test collation
+SET enable_partitionwise_aggregate TO true;
+CREATE COLLATION case_insensitive (
+       provider = icu,
+       locale = 'und-u-ks-level2',
+       deterministic = false);
+
+CREATE TABLE pagg_tab_col (c text collate case_insensitive) PARTITION BY LIST(c collate "C");
+CREATE TABLE pagg_tab_col_p1 PARTITION OF pagg_tab_col FOR VALUES IN ('a', 'b', 'c', 'd');
+CREATE TABLE pagg_tab_col_p2 PARTITION OF pagg_tab_col FOR VALUES IN ('e', 'f','A');
+CREATE TABLE pagg_tab_col_p3 PARTITION OF pagg_tab_col FOR VALUES IN ('B', 'C', 'D', 'E');
+INSERT INTO pagg_tab_col SELECT substr('abcdeABCDE', (i % 10) +1 , 1) FROM generate_series(0, 2999) i;
+ANALYZE pagg_tab_col;
+
+EXPLAIN(VERBOSE, COSTS OFF) SELECT c collate case_insensitive, count(c) FROM
+pagg_tab_col GROUP BY c collate case_insensitive;
+
+SELECT c collate case_insensitive, count(c) FROM
+pagg_tab_col GROUP BY c collate case_insensitive;
+
+RESET enable_partitionwise_aggregate;
-- 
2.34.1