Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitdd2f8eb

Browse files
committed
Disallow partitionwise grouping when collations don't match
If the collation of any grouping column doesn’t match the collation ofthe corresponding partition key, partitionwise grouping can yieldincorrect results. For example, rows that would be grouped under thegrouping collation may end up in different partitions under thepartitioning collation. In such cases, full partitionwise groupingwould produce results that differ from those without partitionwisegrouping, so disallowed that.Partial partitionwise aggregation is still allowed, as the Finalizestep reconciles partition-level aggregates with grouping requirementsacross all partitions, ensuring that the final output remainsconsistent.This commit also fixes group_by_has_partkey() by ensuring theRelabelType node is stripped from grouping expressions when matchingthem to partition key expressions to avoid false mismatches.Bug: #18568Reported-by: Webbo Han <1105066510@qq.com>Author: Webbo Han <1105066510@qq.com>Reviewed-by: Tender Wang <tndrwang@gmail.com>Reviewed-by: Aleksander Alekseev <aleksander@timescale.com>Reviewed-by: Jian He <jian.universality@gmail.com>Discussion:https://postgr.es/m/18568-2a9afb6b9f7e6ed3@postgresql.orgDiscussion:https://postgr.es/m/tencent_9D9103CDA420C07768349CC1DFF88465F90A@qq.comDiscussion:https://postgr.es/m/CAHewXNno_HKiQ6PqyLYfuqDtwp7KKHZiH1J7Pqyz0nr+PS2Dwg@mail.gmail.comBackpatch-through: 12
1 parent57c8b87 commitdd2f8eb

File tree

3 files changed

+163
-8
lines changed

3 files changed

+163
-8
lines changed

‎src/backend/optimizer/plan/planner.c

Lines changed: 36 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -3951,9 +3951,10 @@ create_ordinary_grouping_paths(PlannerInfo *root, RelOptInfo *input_rel,
39513951
* If this is the topmost relation or if the parent relation is doing
39523952
* full partitionwise aggregation, then we can do full partitionwise
39533953
* aggregation provided that the GROUP BY clause contains all of the
3954-
* partitioning columns at this level. Otherwise, we can do at most
3955-
* partial partitionwise aggregation. But if partial aggregation is
3956-
* not supported in general then we can't use it for partitionwise
3954+
* partitioning columns at this level and the collation used by GROUP
3955+
* BY matches the partitioning collation. Otherwise, we can do at
3956+
* most partial partitionwise aggregation. But if partial aggregation
3957+
* is not supported in general then we can't use it for partitionwise
39573958
* aggregation either.
39583959
*
39593960
* Check parse->groupClause not processed_groupClause, because it's
@@ -7867,8 +7868,8 @@ create_partitionwise_grouping_paths(PlannerInfo *root,
78677868
/*
78687869
* group_by_has_partkey
78697870
*
7870-
* Returns true, if all the partition keys of the given relation are part of
7871-
* the GROUP BY clauses, false otherwise.
7871+
* Returns true if all the partition keys of the given relation are part of
7872+
* the GROUP BY clauses,including having matching collation,false otherwise.
78727873
*/
78737874
staticbool
78747875
group_by_has_partkey(RelOptInfo*input_rel,
@@ -7896,13 +7897,40 @@ group_by_has_partkey(RelOptInfo *input_rel,
78967897

78977898
foreach(lc,partexprs)
78987899
{
7900+
ListCell*lg;
78997901
Expr*partexpr=lfirst(lc);
7902+
Oidpartcoll=input_rel->part_scheme->partcollation[cnt];
79007903

7901-
if (list_member(groupexprs,partexpr))
7904+
foreach(lg,groupexprs)
79027905
{
7903-
found= true;
7904-
break;
7906+
Expr*groupexpr=lfirst(lg);
7907+
Oidgroupcoll=exprCollation((Node*)groupexpr);
7908+
7909+
/*
7910+
* Note: we can assume there is at most one RelabelType node;
7911+
* eval_const_expressions() will have simplified if more than
7912+
* one.
7913+
*/
7914+
if (IsA(groupexpr,RelabelType))
7915+
groupexpr= ((RelabelType*)groupexpr)->arg;
7916+
7917+
if (equal(groupexpr,partexpr))
7918+
{
7919+
/*
7920+
* Reject a match if the grouping collation does not match
7921+
* the partitioning collation.
7922+
*/
7923+
if (OidIsValid(partcoll)&&OidIsValid(groupcoll)&&
7924+
partcoll!=groupcoll)
7925+
return false;
7926+
7927+
found= true;
7928+
break;
7929+
}
79057930
}
7931+
7932+
if (found)
7933+
break;
79067934
}
79077935

79087936
/*

‎src/test/regress/expected/collate.icu.utf8.out

Lines changed: 90 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2050,6 +2050,96 @@ SELECT (SELECT count(*) FROM test33_0) <> (SELECT count(*) FROM test33_1);
20502050
t
20512051
(1 row)
20522052

2053+
--
2054+
-- Bug #18568
2055+
--
2056+
-- Partitionwise aggregate (full or partial) should not be used when a
2057+
-- partition key's collation doesn't match that of the GROUP BY column it is
2058+
-- matched with.
2059+
SET max_parallel_workers_per_gather TO 0;
2060+
SET enable_incremental_sort TO off;
2061+
CREATE TABLE pagg_tab3 (a text, c text collate case_insensitive) PARTITION BY LIST(c collate "C");
2062+
CREATE TABLE pagg_tab3_p1 PARTITION OF pagg_tab3 FOR VALUES IN ('a', 'b');
2063+
CREATE TABLE pagg_tab3_p2 PARTITION OF pagg_tab3 FOR VALUES IN ('B', 'A');
2064+
INSERT INTO pagg_tab3 SELECT i % 4 + 1, substr('abAB', (i % 4) + 1 , 1) FROM generate_series(0, 19) i;
2065+
ANALYZE pagg_tab3;
2066+
SET enable_partitionwise_aggregate TO false;
2067+
EXPLAIN (COSTS OFF)
2068+
SELECT upper(c collate case_insensitive), count(c) FROM pagg_tab3 GROUP BY c collate case_insensitive ORDER BY 1;
2069+
QUERY PLAN
2070+
-----------------------------------------------------------
2071+
Sort
2072+
Sort Key: (upper(pagg_tab3.c)) COLLATE case_insensitive
2073+
-> HashAggregate
2074+
Group Key: pagg_tab3.c
2075+
-> Append
2076+
-> Seq Scan on pagg_tab3_p2 pagg_tab3_1
2077+
-> Seq Scan on pagg_tab3_p1 pagg_tab3_2
2078+
(7 rows)
2079+
2080+
SELECT upper(c collate case_insensitive), count(c) FROM pagg_tab3 GROUP BY c collate case_insensitive ORDER BY 1;
2081+
upper | count
2082+
-------+-------
2083+
A | 10
2084+
B | 10
2085+
(2 rows)
2086+
2087+
-- No "full" partitionwise aggregation allowed, though "partial" is allowed.
2088+
SET enable_partitionwise_aggregate TO true;
2089+
EXPLAIN (COSTS OFF)
2090+
SELECT upper(c collate case_insensitive), count(c) FROM pagg_tab3 GROUP BY c collate case_insensitive ORDER BY 1;
2091+
QUERY PLAN
2092+
--------------------------------------------------------------
2093+
Sort
2094+
Sort Key: (upper(pagg_tab3.c)) COLLATE case_insensitive
2095+
-> Finalize HashAggregate
2096+
Group Key: pagg_tab3.c
2097+
-> Append
2098+
-> Partial HashAggregate
2099+
Group Key: pagg_tab3.c
2100+
-> Seq Scan on pagg_tab3_p2 pagg_tab3
2101+
-> Partial HashAggregate
2102+
Group Key: pagg_tab3_1.c
2103+
-> Seq Scan on pagg_tab3_p1 pagg_tab3_1
2104+
(11 rows)
2105+
2106+
SELECT upper(c collate case_insensitive), count(c) FROM pagg_tab3 GROUP BY c collate case_insensitive ORDER BY 1;
2107+
upper | count
2108+
-------+-------
2109+
A | 10
2110+
B | 10
2111+
(2 rows)
2112+
2113+
-- OK to use full partitionwise aggregate after changing the GROUP BY column's
2114+
-- collation to be the same as that of the partition key.
2115+
EXPLAIN (COSTS OFF)
2116+
SELECT c collate "C", count(c) FROM pagg_tab3 GROUP BY c collate "C" ORDER BY 1;
2117+
QUERY PLAN
2118+
--------------------------------------------------------
2119+
Sort
2120+
Sort Key: ((pagg_tab3.c)::text) COLLATE "C"
2121+
-> Append
2122+
-> HashAggregate
2123+
Group Key: (pagg_tab3.c)::text
2124+
-> Seq Scan on pagg_tab3_p2 pagg_tab3
2125+
-> HashAggregate
2126+
Group Key: (pagg_tab3_1.c)::text
2127+
-> Seq Scan on pagg_tab3_p1 pagg_tab3_1
2128+
(9 rows)
2129+
2130+
SELECT c collate "C", count(c) FROM pagg_tab3 GROUP BY c collate "C" ORDER BY 1;
2131+
c | count
2132+
---+-------
2133+
A | 5
2134+
B | 5
2135+
a | 5
2136+
b | 5
2137+
(4 rows)
2138+
2139+
DROP TABLE pagg_tab3;
2140+
RESET enable_partitionwise_aggregate;
2141+
RESET max_parallel_workers_per_gather;
2142+
RESET enable_incremental_sort;
20532143
-- cleanup
20542144
RESET search_path;
20552145
SET client_min_messages TO warning;

‎src/test/regress/sql/collate.icu.utf8.sql

Lines changed: 37 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -794,6 +794,43 @@ INSERT INTO test33 VALUES (2, 'DEF');
794794
-- they end up in the same partition (but it's platform-dependent which one)
795795
SELECT (SELECTcount(*)FROM test33_0)<> (SELECTcount(*)FROM test33_1);
796796

797+
--
798+
-- Bug #18568
799+
--
800+
-- Partitionwise aggregate (full or partial) should not be used when a
801+
-- partition key's collation doesn't match that of the GROUP BY column it is
802+
-- matched with.
803+
SET max_parallel_workers_per_gather TO0;
804+
SET enable_incremental_sort TO off;
805+
806+
CREATETABLEpagg_tab3 (atext, ctext collate case_insensitive) PARTITION BY LIST(c collate"C");
807+
CREATETABLEpagg_tab3_p1 PARTITION OF pagg_tab3 FORVALUESIN ('a','b');
808+
CREATETABLEpagg_tab3_p2 PARTITION OF pagg_tab3 FORVALUESIN ('B','A');
809+
INSERT INTO pagg_tab3SELECT i %4+1, substr('abAB', (i %4)+1 ,1)FROM generate_series(0,19) i;
810+
ANALYZE pagg_tab3;
811+
812+
SET enable_partitionwise_aggregate TO false;
813+
EXPLAIN (COSTS OFF)
814+
SELECTupper(c collate case_insensitive),count(c)FROM pagg_tab3GROUP BY c collate case_insensitiveORDER BY1;
815+
SELECTupper(c collate case_insensitive),count(c)FROM pagg_tab3GROUP BY c collate case_insensitiveORDER BY1;
816+
817+
-- No "full" partitionwise aggregation allowed, though "partial" is allowed.
818+
SET enable_partitionwise_aggregate TO true;
819+
EXPLAIN (COSTS OFF)
820+
SELECTupper(c collate case_insensitive),count(c)FROM pagg_tab3GROUP BY c collate case_insensitiveORDER BY1;
821+
SELECTupper(c collate case_insensitive),count(c)FROM pagg_tab3GROUP BY c collate case_insensitiveORDER BY1;
822+
823+
-- OK to use full partitionwise aggregate after changing the GROUP BY column's
824+
-- collation to be the same as that of the partition key.
825+
EXPLAIN (COSTS OFF)
826+
SELECT c collate"C",count(c)FROM pagg_tab3GROUP BY c collate"C"ORDER BY1;
827+
SELECT c collate"C",count(c)FROM pagg_tab3GROUP BY c collate"C"ORDER BY1;
828+
829+
DROPTABLE pagg_tab3;
830+
831+
RESET enable_partitionwise_aggregate;
832+
RESET max_parallel_workers_per_gather;
833+
RESET enable_incremental_sort;
797834

798835
-- cleanup
799836
RESET search_path;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp