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

Commitf734b6b

Browse files
committed
Disallow partitionwise join when collations don't match
If the collation of any join key column doesn’t match the collation ofthe corresponding partition key, partitionwise joins can yield incorrectresults. For example, rows that would match under the join key collationmight be located in different partitions due to the partitioningcollation. In such cases, a partitionwise join would yield differentresults from a non-partitionwise join, so disallow it in such cases.Reported-by: Tender Wang <tndrwang@gmail.com>Author: Jian He <jian.universality@gmail.com>Reviewed-by: Tender Wang <tndrwang@gmail.com>Reviewed-by: Junwang Zhao <zhjwpku@gmail.com>Discussion:https://postgr.es/m/CAHewXNno_HKiQ6PqyLYfuqDtwp7KKHZiH1J7Pqyz0nr+PS2Dwg@mail.gmail.comBackpatch-through: 12
1 parentdd2f8eb commitf734b6b

File tree

3 files changed

+144
-0
lines changed

3 files changed

+144
-0
lines changed

‎src/backend/optimizer/util/relnode.c

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2163,6 +2163,10 @@ have_partkey_equi_join(PlannerInfo *root, RelOptInfo *joinrel,
21632163
if (ipk1!=ipk2)
21642164
continue;
21652165

2166+
/* Reject if the partition key collation differs from the clause's. */
2167+
if (rel1->part_scheme->partcollation[ipk1]!=opexpr->inputcollid)
2168+
return false;
2169+
21662170
/*
21672171
* The clause allows partitionwise join only if it uses the same
21682172
* operator family as that specified by the partition key.

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

Lines changed: 118 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2136,6 +2136,124 @@ SELECT c collate "C", count(c) FROM pagg_tab3 GROUP BY c collate "C" ORDER BY 1;
21362136
b | 5
21372137
(4 rows)
21382138

2139+
-- Partitionwise join should not be allowed too when the collation used by the
2140+
-- join keys doesn't match the partition key collation.
2141+
SET enable_partitionwise_join TO false;
2142+
EXPLAIN (COSTS OFF)
2143+
SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c GROUP BY 1 ORDER BY t1.c COLLATE "C";
2144+
QUERY PLAN
2145+
-------------------------------------------------------------
2146+
Sort
2147+
Sort Key: t1.c COLLATE "C"
2148+
-> HashAggregate
2149+
Group Key: t1.c
2150+
-> Hash Join
2151+
Hash Cond: (t1.c = t2.c)
2152+
-> Append
2153+
-> Seq Scan on pagg_tab3_p2 t1_1
2154+
-> Seq Scan on pagg_tab3_p1 t1_2
2155+
-> Hash
2156+
-> Append
2157+
-> Seq Scan on pagg_tab3_p2 t2_1
2158+
-> Seq Scan on pagg_tab3_p1 t2_2
2159+
(13 rows)
2160+
2161+
SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c GROUP BY 1 ORDER BY t1.c COLLATE "C";
2162+
c | count
2163+
---+-------
2164+
A | 100
2165+
B | 100
2166+
(2 rows)
2167+
2168+
SET enable_partitionwise_join TO true;
2169+
EXPLAIN (COSTS OFF)
2170+
SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c GROUP BY 1 ORDER BY t1.c COLLATE "C";
2171+
QUERY PLAN
2172+
-------------------------------------------------------------
2173+
Sort
2174+
Sort Key: t1.c COLLATE "C"
2175+
-> HashAggregate
2176+
Group Key: t1.c
2177+
-> Hash Join
2178+
Hash Cond: (t1.c = t2.c)
2179+
-> Append
2180+
-> Seq Scan on pagg_tab3_p2 t1_1
2181+
-> Seq Scan on pagg_tab3_p1 t1_2
2182+
-> Hash
2183+
-> Append
2184+
-> Seq Scan on pagg_tab3_p2 t2_1
2185+
-> Seq Scan on pagg_tab3_p1 t2_2
2186+
(13 rows)
2187+
2188+
SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c GROUP BY 1 ORDER BY t1.c COLLATE "C";
2189+
c | count
2190+
---+-------
2191+
A | 100
2192+
B | 100
2193+
(2 rows)
2194+
2195+
-- OK when the join clause uses the same collation as the partition key.
2196+
EXPLAIN (COSTS OFF)
2197+
SELECT t1.c COLLATE "C", count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c COLLATE "C" GROUP BY t1.c COLLATE "C" ORDER BY t1.c COLLATE "C";
2198+
QUERY PLAN
2199+
------------------------------------------------------------------
2200+
Sort
2201+
Sort Key: ((t1.c)::text) COLLATE "C"
2202+
-> Append
2203+
-> HashAggregate
2204+
Group Key: (t1.c)::text
2205+
-> Hash Join
2206+
Hash Cond: ((t1.c)::text = (t2.c)::text)
2207+
-> Seq Scan on pagg_tab3_p2 t1
2208+
-> Hash
2209+
-> Seq Scan on pagg_tab3_p2 t2
2210+
-> HashAggregate
2211+
Group Key: (t1_1.c)::text
2212+
-> Hash Join
2213+
Hash Cond: ((t1_1.c)::text = (t2_1.c)::text)
2214+
-> Seq Scan on pagg_tab3_p1 t1_1
2215+
-> Hash
2216+
-> Seq Scan on pagg_tab3_p1 t2_1
2217+
(17 rows)
2218+
2219+
SELECT t1.c COLLATE "C", count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c COLLATE "C" GROUP BY t1.c COLLATE "C" ORDER BY t1.c COLLATE "C";
2220+
c | count
2221+
---+-------
2222+
A | 25
2223+
B | 25
2224+
a | 25
2225+
b | 25
2226+
(4 rows)
2227+
2228+
SET enable_partitionwise_join TO false;
2229+
EXPLAIN (COSTS OFF)
2230+
SELECT t1.c COLLATE "C", count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c COLLATE "C" GROUP BY t1.c COLLATE "C" ORDER BY t1.c COLLATE "C";
2231+
QUERY PLAN
2232+
-------------------------------------------------------------
2233+
Sort
2234+
Sort Key: ((t1.c)::text) COLLATE "C"
2235+
-> HashAggregate
2236+
Group Key: (t1.c)::text
2237+
-> Hash Join
2238+
Hash Cond: ((t1.c)::text = (t2.c)::text)
2239+
-> Append
2240+
-> Seq Scan on pagg_tab3_p2 t1_1
2241+
-> Seq Scan on pagg_tab3_p1 t1_2
2242+
-> Hash
2243+
-> Append
2244+
-> Seq Scan on pagg_tab3_p2 t2_1
2245+
-> Seq Scan on pagg_tab3_p1 t2_2
2246+
(13 rows)
2247+
2248+
SELECT t1.c COLLATE "C", count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c COLLATE "C" GROUP BY t1.c COLLATE "C" ORDER BY t1.c COLLATE "C";
2249+
c | count
2250+
---+-------
2251+
A | 25
2252+
B | 25
2253+
a | 25
2254+
b | 25
2255+
(4 rows)
2256+
21392257
DROP TABLE pagg_tab3;
21402258
RESET enable_partitionwise_aggregate;
21412259
RESET max_parallel_workers_per_gather;

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

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -826,6 +826,28 @@ EXPLAIN (COSTS OFF)
826826
SELECT c collate"C",count(c)FROM pagg_tab3GROUP BY c collate"C"ORDER BY1;
827827
SELECT c collate"C",count(c)FROM pagg_tab3GROUP BY c collate"C"ORDER BY1;
828828

829+
-- Partitionwise join should not be allowed too when the collation used by the
830+
-- join keys doesn't match the partition key collation.
831+
SET enable_partitionwise_join TO false;
832+
EXPLAIN (COSTS OFF)
833+
SELECTt1.c,count(t2.c)FROM pagg_tab3 t1JOIN pagg_tab3 t2ONt1.c=t2.cGROUP BY1ORDER BYt1.c COLLATE"C";
834+
SELECTt1.c,count(t2.c)FROM pagg_tab3 t1JOIN pagg_tab3 t2ONt1.c=t2.cGROUP BY1ORDER BYt1.c COLLATE"C";
835+
836+
SET enable_partitionwise_join TO true;
837+
EXPLAIN (COSTS OFF)
838+
SELECTt1.c,count(t2.c)FROM pagg_tab3 t1JOIN pagg_tab3 t2ONt1.c=t2.cGROUP BY1ORDER BYt1.c COLLATE"C";
839+
SELECTt1.c,count(t2.c)FROM pagg_tab3 t1JOIN pagg_tab3 t2ONt1.c=t2.cGROUP BY1ORDER BYt1.c COLLATE"C";
840+
841+
-- OK when the join clause uses the same collation as the partition key.
842+
EXPLAIN (COSTS OFF)
843+
SELECTt1.c COLLATE"C",count(t2.c)FROM pagg_tab3 t1JOIN pagg_tab3 t2ONt1.c=t2.c COLLATE"C"GROUP BYt1.c COLLATE"C"ORDER BYt1.c COLLATE"C";
844+
SELECTt1.c COLLATE"C",count(t2.c)FROM pagg_tab3 t1JOIN pagg_tab3 t2ONt1.c=t2.c COLLATE"C"GROUP BYt1.c COLLATE"C"ORDER BYt1.c COLLATE"C";
845+
846+
SET enable_partitionwise_join TO false;
847+
EXPLAIN (COSTS OFF)
848+
SELECTt1.c COLLATE"C",count(t2.c)FROM pagg_tab3 t1JOIN pagg_tab3 t2ONt1.c=t2.c COLLATE"C"GROUP BYt1.c COLLATE"C"ORDER BYt1.c COLLATE"C";
849+
SELECTt1.c COLLATE"C",count(t2.c)FROM pagg_tab3 t1JOIN pagg_tab3 t2ONt1.c=t2.c COLLATE"C"GROUP BYt1.c COLLATE"C"ORDER BYt1.c COLLATE"C";
850+
829851
DROPTABLE pagg_tab3;
830852

831853
RESET enable_partitionwise_aggregate;

0 commit comments

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