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

Amit Langote <amitlangote09@gmail.com> 于2024年11月1日周五 15:27写道：

> Hi,
>
> On Wed, Oct 23, 2024 at 10:48 PM Tender Wang <tndrwang@gmail.com> wrote:
> > 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.
>
> I think it would be better to keep RelOptInfo.partexprs unchanged in
> these fixes.  I haven't been able to come up with a way to "assign"
> the correct collation to partition keys that are not simple column
> references.  Checking PartitionScheme.partcollation separately is
> enough to fix these bugs and aligns with the style of existing code,
> such as match_clause_to_partition_key() in partprune.c.
>

Agree. I can't figure out another solution.


-- 
Thanks,
Tender Wang