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

Tender Wang <tndrwang(at)gmail(dot)com> 于2024年10月23日周三 21:48写道：

> Hi all,
>
>
> I think the root cause of this thread and [1] are same. We don't use the
> Partition Key collation but column's
> collation to fill the RelOptInfo partexprs field in
> set_baserel_partition_key_exprs().
> If the Partition Key definition is same as column definition, which most
> times is,
> that will be ok. But if it's not, this thread issue will arise.
>
> As far as I know, only partition pruning logic considers not only call
> equal(), but also check collation match.
> Other codes only call equal() to check if the exprs match the partition
> key.
> For example, in this thread case, match_expr_to_partition_keys() think the
> expr match the partition key:
> if (equal(lfirst(lc), expr))
> return cnt;
>
> Although We can fix this issue like [1], I think why not directly use the
> partkey->partcollation[cnt], which its value is
> same with pg_partitioned_table's partcollation. I tried this to fix [1],
> but at that time, I was unsure if it was the correct fix.
>
> Until I find this thread issue, I think we should do it this way.
> In the attached patch, I include this thread test and [1] test case.
>
>
Hi
In the last email, I proposed to use partcollation directly. But I ignore
the case that partkey is not a simple column reference.
for expample:
create table coll_pruning_multi (a text) partition by range (substr(a,
1) collate "POSIX", substr(a, 1) collate "C");

The partexprs in RelOptInfo store substr(a,1), a FuncExpr, and its
collation is same with column a not collate "posix".

I'm now thinking how we can use a uniform solution to fix this thread issue
and issue in [1]

[1]
https://www.postgresql.org/message-id/18568-2a9afb6b9f7e6ed3%40postgresql.org
--
Thanks,
Tender Wang