From 1c02e15f99bb9981035760ccaf65f91975e6a088 Mon Sep 17 00:00:00 2001 From: jian he Date: Sat, 2 Nov 2024 15:40:55 +0800 Subject: [PATCH v5 1/1] make partition-wise, partitoin-aggreagte related test deterministic --- src/test/regress/expected/collate.icu.utf8.out | 18 +++++++++--------- src/test/regress/sql/collate.icu.utf8.sql | 12 ++++++------ 2 files changed, 15 insertions(+), 15 deletions(-) diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out index e6af9b405f..b2950a0634 100644 --- a/src/test/regress/expected/collate.icu.utf8.out +++ b/src/test/regress/expected/collate.icu.utf8.out @@ -2195,11 +2195,11 @@ SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c GROU SET enable_partitionwise_join TO true; EXPLAIN (COSTS OFF) -SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c GROUP BY 1 ORDER BY 1; +SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c GROUP BY t1.c ORDER BY t1.c collate "C"; QUERY PLAN ------------------------------------------------------------- Sort - Sort Key: t1.c COLLATE case_insensitive + Sort Key: t1.c COLLATE "C" -> HashAggregate Group Key: t1.c -> Hash Join @@ -2213,7 +2213,7 @@ SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c GROU -> Seq Scan on pagg_tab3_p1 t2_2 (13 rows) -SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c GROUP BY 1 ORDER BY 1; +SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c GROUP BY t1.c ORDER BY t1.c collate "C"; c | count ---+------- A | 36 @@ -2261,11 +2261,11 @@ CREATE TABLE pagg_tab4_p2 PARTITION OF pagg_tab4 FOR VALUES IN ('B', 'A'); INSERT INTO pagg_tab4 (b, c) SELECT substr('abAB', (i % 4) + 1 , 1), substr('abAB', (i % 2) + 1 , 1) FROM generate_series(0, 11) i; ANALYZE pagg_tab4; EXPLAIN (COSTS OFF) -SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab4 t2 ON t1.c = t2.c AND t1.c = t2.b GROUP BY 1 ORDER BY 1; +SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab4 t2 ON t1.c = t2.c AND t1.c = t2.b GROUP BY t1.c ORDER BY t1.c collate "C"; QUERY PLAN ------------------------------------------------------------- Sort - Sort Key: t1.c COLLATE case_insensitive + Sort Key: t1.c COLLATE "C" -> HashAggregate Group Key: t1.c -> Hash Join @@ -2281,7 +2281,7 @@ SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab4 t2 ON t1.c = t2.c AND Filter: (c = b) (15 rows) -SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab4 t2 ON t1.c = t2.c AND t1.c = t2.b GROUP BY 1 ORDER BY 1; +SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab4 t2 ON t1.c = t2.c AND t1.c = t2.b GROUP BY t1.c ORDER BY t1.c collate "C"; c | count ---+------- A | 36 @@ -2290,11 +2290,11 @@ SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab4 t2 ON t1.c = t2.c AND -- OK when the join clause uses the same collation as the partition key EXPLAIN (COSTS OFF) -SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab4 t2 ON t1.c = t2.c AND t1.c = t2.b COLLATE "C" GROUP BY 1 ORDER BY 1; +SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab4 t2 ON t1.c = t2.c AND t1.c = t2.b COLLATE "C" GROUP BY t1.c ORDER BY t1.c collate "C"; QUERY PLAN ------------------------------------------------------------------------------------------ Sort - Sort Key: t1.c COLLATE case_insensitive + Sort Key: t1.c COLLATE "C" -> HashAggregate Group Key: t1.c -> Append @@ -2310,7 +2310,7 @@ SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab4 t2 ON t1.c = t2.c AND -> Seq Scan on pagg_tab4_p1 t2_2 (15 rows) -SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab4 t2 ON t1.c = t2.c AND t1.c = t2.b COLLATE "C" GROUP BY 1 ORDER BY 1; +SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab4 t2 ON t1.c = t2.c AND t1.c = t2.b COLLATE "C" GROUP BY t1.c ORDER BY t1.c collate "C"; c | count ---+------- A | 18 diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sql index 407e5e5b21..beae6603b1 100644 --- a/src/test/regress/sql/collate.icu.utf8.sql +++ b/src/test/regress/sql/collate.icu.utf8.sql @@ -843,8 +843,8 @@ SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c GROU SET enable_partitionwise_join TO true; EXPLAIN (COSTS OFF) -SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c GROUP BY 1 ORDER BY 1; -SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c GROUP BY 1 ORDER BY 1; +SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c GROUP BY t1.c ORDER BY t1.c collate "C"; +SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab3 t2 ON t1.c = t2.c GROUP BY t1.c ORDER BY t1.c collate "C"; -- OK when the join clause uses the same collation as the partition key. EXPLAIN (COSTS OFF) @@ -860,13 +860,13 @@ INSERT INTO pagg_tab4 (b, c) SELECT substr('abAB', (i % 4) + 1 , 1), substr('abA ANALYZE pagg_tab4; EXPLAIN (COSTS OFF) -SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab4 t2 ON t1.c = t2.c AND t1.c = t2.b GROUP BY 1 ORDER BY 1; -SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab4 t2 ON t1.c = t2.c AND t1.c = t2.b GROUP BY 1 ORDER BY 1; +SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab4 t2 ON t1.c = t2.c AND t1.c = t2.b GROUP BY t1.c ORDER BY t1.c collate "C"; +SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab4 t2 ON t1.c = t2.c AND t1.c = t2.b GROUP BY t1.c ORDER BY t1.c collate "C"; -- OK when the join clause uses the same collation as the partition key EXPLAIN (COSTS OFF) -SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab4 t2 ON t1.c = t2.c AND t1.c = t2.b COLLATE "C" GROUP BY 1 ORDER BY 1; -SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab4 t2 ON t1.c = t2.c AND t1.c = t2.b COLLATE "C" GROUP BY 1 ORDER BY 1; +SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab4 t2 ON t1.c = t2.c AND t1.c = t2.b COLLATE "C" GROUP BY t1.c ORDER BY t1.c collate "C"; +SELECT t1.c, count(t2.c) FROM pagg_tab3 t1 JOIN pagg_tab4 t2 ON t1.c = t2.c AND t1.c = t2.b COLLATE "C" GROUP BY t1.c ORDER BY t1.c collate "C"; DROP TABLE pagg_tab3; DROP TABLE pagg_tab4; -- 2.34.1