Re: Wrong result when enable_partitionwise_join is on if collation of PartitionKey and Column is different.

Amit Langote <amitlangote09(at)gmail(dot)com> 于2024年10月24日周四 14:33写道：

> Hi,
>
> On Thu, Oct 24, 2024 at 1:46 PM Tender Wang <tndrwang(at)gmail(dot)com> wrote:
> > Tender Wang <tndrwang(at)gmail(dot)com> 于2024年10月23日周三 21:48写道：
> >>
> >> Hi all,
> >>
> >> I find another issue as $SUBJECT when I work on [1].
> >
> > When I continue to work on this, I find below issue. But I'm not sure
> whether it is a bug.
> >
> > postgres=# create table part_index(a text primary key) partition by list
> ( a collate "POSIX");
> > ERROR: unique constraint on partitioned table must include all
> partitioning columns
> > DETAIL: PRIMARY KEY constraint on table "part_index" lacks column "a"
> which is part of the partition key.
> > postgres=# create table part_index(a text) partition by list ( a collate
> "POSIX");
> > CREATE TABLE
> > postgres=# alter table part_index add primary key (a);
> > ERROR: unique constraint on partitioned table must include all
> partitioning columns
> > DETAIL: PRIMARY KEY constraint on table "part_index" lacks column "a"
> which is part of the partition key.
> >
> > It seems we can't create a primary key if the collation is different
> between columnDef and PartitionKey.
>
> Yeah, you don't want to have the PK index and the partitioning logic
> to not be in sync about the collation rules applied to the individual
> rows.
>
> > By the way, I think the error message is misleading to users.
> > ostgres=# alter table part_index add primary key (a);
> > ERROR: unique constraint on partitioned table must include all
> partitioning columns
> > DETAIL: PRIMARY KEY constraint on table "part_index" lacks column "a"
> which is part of the partition key.
>
> I think it's kind of similar to the message you get when a GROUP BY
> column's collation doesn't match the column appearing in the SELECT
> list:
>
> explain SELECT c collate case_insensitive, count(c) FROM
> pagg_tab_case_s GROUP BY c collate "C";
> ERROR: column "pagg_tab_case_s.c" must appear in the GROUP BY clause
> or be used in an aggregate function
> LINE 1: explain SELECT c collate case_insensitive, count(c) FROM pag...
>
> Perhaps it would be more helpful for the error message or hint or
> detail to mention the actual discrepancy (collation mismatch) that's
> causing the error.
>
> There might be other instances of such an error and I am not sure it
> would be worthwhile to find and fix them all.
>
>
Thanks for the explanation. We had better focus on the wrong result issue.

I feel that it's hard only to use one struct(for example, X), which just
calls equal(X, expr)
can check both the expression match and the collation match.

Maybe we should add another collation match checks in
match_clause_to_partition_key(), like
partition pruning logic does.

Any thoughts?

--
Thanks,
Tender Wang