Re: BUG #18568: BUG: Result wrong when do group by on partition table!

Hi,

On Tue, Aug 6, 2024 at 10:24 PM Tender Wang <tndrwang(at)gmail(dot)com> wrote:
> PG Bug reporting form <noreply(at)postgresql(dot)org> 于2024年8月6日周二 15:01写道：
>>
>> The following bug has been logged on the website:
>>
>> Bug reference: 18568
>> Logged by: Webbo Han
>> Email address: 1105066510(at)qq(dot)com
>> PostgreSQL version: 16.3
>> Operating system: centos 7.6
>> Description:
>>
>> First, we create one case-insensitive collation use ICU:
>> ```sql
>> CREATE COLLATION case_insensitive (
>> provider = icu,
>> locale = 'und-u-ks-level2',
>> deterministic = false
>> );
>> ```
>>
>> Then, we create the partition table, meanwhile we set the collation of
>> column c to `case_insensitive`,
>> and set partkey's collation to 'C'.
>> ```sql
>> SET enable_partitionwise_aggregate TO true;
>> SET enable_partitionwise_join TO true;
>> SET max_parallel_workers_per_gather TO 0;
>> SET enable_incremental_sort TO off;
>> CREATE TABLE pagg_tab (c text collate case_insensitive) PARTITION BY LIST(c
>> collate "C");
>
>
> I think above create table again. Should we allow this CREATE TABLE? The partition key
> definition are not same with column definiton. Is it better to report error for users?

Not really. As the documentation says, collation can be specified per
column or per operation:

https://www.postgresql.org/docs/current/collation.html

In this case, the operation is partitioning. When you specify the
COLLATE clause for a partition key, it means that the partitioning
logic, such as partition tuple routing, will use that collation
instead of the column-specified or the column type's collation.

That's similar to how you can create an index on a column using a
different collation than the column's own:

create table foo (a text collate case_insensitive);
create index on foo (a collate "C");

I do notice that the CREATE TABLE documentation does not describe what
the COLLATE clause does when mentioned in a PARTITION BY clause.
We'll need to fix that.

--
Thanks, Amit Langote