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

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, Amit Langote