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

Amit Langote <amitlangote09@gmail.com> 于2024年10月22日周二 15:33写道：

> Hi,
>
> On Tue, Aug 6, 2024 at 10:24 PM Tender Wang <tndrwang@gmail.com> wrote:
> > PG Bug reporting form <noreply@postgresql.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@qq.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");
>
>
Thanks for your explanation.

> 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.
>

Agree.


-- 
Thanks,
Tender Wang