diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/collate.icu.utf8.out | 90 | ||||
| -rw-r--r-- | src/test/regress/sql/collate.icu.utf8.sql | 37 |
2 files changed, 127 insertions, 0 deletions
diff --git a/src/test/regress/expected/collate.icu.utf8.out b/src/test/regress/expected/collate.icu.utf8.out index faa376e060c..e7e123550d2 100644 --- a/src/test/regress/expected/collate.icu.utf8.out +++ b/src/test/regress/expected/collate.icu.utf8.out @@ -2054,6 +2054,96 @@ SELECT (SELECT count(*) FROM test33_0) <> (SELECT count(*) FROM test33_1); t (1 row) +-- +-- Bug #18568 +-- +-- Partitionwise aggregate (full or partial) should not be used when a +-- partition key's collation doesn't match that of the GROUP BY column it is +-- matched with. +SET max_parallel_workers_per_gather TO 0; +SET enable_incremental_sort TO off; +CREATE TABLE pagg_tab3 (a text, c text collate case_insensitive) PARTITION BY LIST(c collate "C"); +CREATE TABLE pagg_tab3_p1 PARTITION OF pagg_tab3 FOR VALUES IN ('a', 'b'); +CREATE TABLE pagg_tab3_p2 PARTITION OF pagg_tab3 FOR VALUES IN ('B', 'A'); +INSERT INTO pagg_tab3 SELECT i % 4 + 1, substr('abAB', (i % 4) + 1 , 1) FROM generate_series(0, 19) i; +ANALYZE pagg_tab3; +SET enable_partitionwise_aggregate TO false; +EXPLAIN (COSTS OFF) +SELECT upper(c collate case_insensitive), count(c) FROM pagg_tab3 GROUP BY c collate case_insensitive ORDER BY 1; + QUERY PLAN +----------------------------------------------------------- + Sort + Sort Key: (upper(pagg_tab3.c)) COLLATE case_insensitive + -> HashAggregate + Group Key: pagg_tab3.c + -> Append + -> Seq Scan on pagg_tab3_p2 pagg_tab3_1 + -> Seq Scan on pagg_tab3_p1 pagg_tab3_2 +(7 rows) + +SELECT upper(c collate case_insensitive), count(c) FROM pagg_tab3 GROUP BY c collate case_insensitive ORDER BY 1; + upper | count +-------+------- + A | 10 + B | 10 +(2 rows) + +-- No "full" partitionwise aggregation allowed, though "partial" is allowed. +SET enable_partitionwise_aggregate TO true; +EXPLAIN (COSTS OFF) +SELECT upper(c collate case_insensitive), count(c) FROM pagg_tab3 GROUP BY c collate case_insensitive ORDER BY 1; + QUERY PLAN +-------------------------------------------------------------- + Sort + Sort Key: (upper(pagg_tab3.c)) COLLATE case_insensitive + -> Finalize HashAggregate + Group Key: pagg_tab3.c + -> Append + -> Partial HashAggregate + Group Key: pagg_tab3.c + -> Seq Scan on pagg_tab3_p2 pagg_tab3 + -> Partial HashAggregate + Group Key: pagg_tab3_1.c + -> Seq Scan on pagg_tab3_p1 pagg_tab3_1 +(11 rows) + +SELECT upper(c collate case_insensitive), count(c) FROM pagg_tab3 GROUP BY c collate case_insensitive ORDER BY 1; + upper | count +-------+------- + A | 10 + B | 10 +(2 rows) + +-- OK to use full partitionwise aggregate after changing the GROUP BY column's +-- collation to be the same as that of the partition key. +EXPLAIN (COSTS OFF) +SELECT c collate "C", count(c) FROM pagg_tab3 GROUP BY c collate "C" ORDER BY 1; + QUERY PLAN +-------------------------------------------------------- + Sort + Sort Key: ((pagg_tab3.c)::text) COLLATE "C" + -> Append + -> HashAggregate + Group Key: (pagg_tab3.c)::text + -> Seq Scan on pagg_tab3_p2 pagg_tab3 + -> HashAggregate + Group Key: (pagg_tab3_1.c)::text + -> Seq Scan on pagg_tab3_p1 pagg_tab3_1 +(9 rows) + +SELECT c collate "C", count(c) FROM pagg_tab3 GROUP BY c collate "C" ORDER BY 1; + c | count +---+------- + A | 5 + B | 5 + a | 5 + b | 5 +(4 rows) + +DROP TABLE pagg_tab3; +RESET enable_partitionwise_aggregate; +RESET max_parallel_workers_per_gather; +RESET enable_incremental_sort; -- cleanup RESET search_path; SET client_min_messages TO warning; diff --git a/src/test/regress/sql/collate.icu.utf8.sql b/src/test/regress/sql/collate.icu.utf8.sql index 80f28a97d78..9f7c06aa38a 100644 --- a/src/test/regress/sql/collate.icu.utf8.sql +++ b/src/test/regress/sql/collate.icu.utf8.sql @@ -796,6 +796,43 @@ INSERT INTO test33 VALUES (2, 'DEF'); -- they end up in the same partition (but it's platform-dependent which one) SELECT (SELECT count(*) FROM test33_0) <> (SELECT count(*) FROM test33_1); +-- +-- Bug #18568 +-- +-- Partitionwise aggregate (full or partial) should not be used when a +-- partition key's collation doesn't match that of the GROUP BY column it is +-- matched with. +SET max_parallel_workers_per_gather TO 0; +SET enable_incremental_sort TO off; + +CREATE TABLE pagg_tab3 (a text, c text collate case_insensitive) PARTITION BY LIST(c collate "C"); +CREATE TABLE pagg_tab3_p1 PARTITION OF pagg_tab3 FOR VALUES IN ('a', 'b'); +CREATE TABLE pagg_tab3_p2 PARTITION OF pagg_tab3 FOR VALUES IN ('B', 'A'); +INSERT INTO pagg_tab3 SELECT i % 4 + 1, substr('abAB', (i % 4) + 1 , 1) FROM generate_series(0, 19) i; +ANALYZE pagg_tab3; + +SET enable_partitionwise_aggregate TO false; +EXPLAIN (COSTS OFF) +SELECT upper(c collate case_insensitive), count(c) FROM pagg_tab3 GROUP BY c collate case_insensitive ORDER BY 1; +SELECT upper(c collate case_insensitive), count(c) FROM pagg_tab3 GROUP BY c collate case_insensitive ORDER BY 1; + +-- No "full" partitionwise aggregation allowed, though "partial" is allowed. +SET enable_partitionwise_aggregate TO true; +EXPLAIN (COSTS OFF) +SELECT upper(c collate case_insensitive), count(c) FROM pagg_tab3 GROUP BY c collate case_insensitive ORDER BY 1; +SELECT upper(c collate case_insensitive), count(c) FROM pagg_tab3 GROUP BY c collate case_insensitive ORDER BY 1; + +-- OK to use full partitionwise aggregate after changing the GROUP BY column's +-- collation to be the same as that of the partition key. +EXPLAIN (COSTS OFF) +SELECT c collate "C", count(c) FROM pagg_tab3 GROUP BY c collate "C" ORDER BY 1; +SELECT c collate "C", count(c) FROM pagg_tab3 GROUP BY c collate "C" ORDER BY 1; + +DROP TABLE pagg_tab3; + +RESET enable_partitionwise_aggregate; +RESET max_parallel_workers_per_gather; +RESET enable_incremental_sort; -- cleanup RESET search_path; |