| From: | Tender Wang <tndrwang(at)gmail(dot)com> |
|---|---|
| To: | Amit Langote <amitlangote09(at)gmail(dot)com> |
| Cc: | 1105066510(at)qq(dot)com, pgsql-bugs(at)lists(dot)postgresql(dot)org |
| Subject: | Re: BUG #18568: BUG: Result wrong when do group by on partition table! |
| Date: | 2024-10-22 07:46:03 |
| Message-ID: | CAHewXNnGkTHXAY3XvUrv4=7kzWQKHso6LkQR8kqf+PVx-c+vVA@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
Amit Langote <amitlangote09(at)gmail(dot)com> 于2024年10月22日周二 15:33写道:
> 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://stream.boxwatch.ru/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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tender Wang | 2024-10-22 08:30:04 | Re: BUG #18568: BUG: Result wrong when do group by on partition table! |
| Previous Message | Amit Langote | 2024-10-22 07:33:21 | Re: BUG #18568: BUG: Result wrong when do group by on partition table! |