From 802f231501cdf11361a0d1e10adbb84fd9261ff1 Mon Sep 17 00:00:00 2001 From: Tender Wang 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