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

jian he <jian(dot)universality(at)gmail(dot)com> 于2024年10月29日周二 14:15写道：

> On Thu, Oct 24, 2024 at 3:01 PM Tender Wang <tndrwang(at)gmail(dot)com> wrote:
> >
> > 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.
> >
>
> in RelOptInfo->partexprs, maybe we should mention that the partition
> key collation is stored
> in RelOptInfo->part_scheme, not here.
>
> > Maybe we should add another collation match checks in
> match_clause_to_partition_key(), like
> > partition pruning logic does.
> >
> in match_clause_to_partition_key
> we already have
>
> else if (IsA(clause, OpExpr) &&
> list_length(((OpExpr *) clause)->args) == 2)
> {
> /*
> * Partition key match also requires collation match. There may be
> * multiple partkeys with the same expression but different
> * collations, so failure is NOMATCH.
> */
> if (!PartCollMatchesExprColl(partcoll, opclause->inputcollid))
> return PARTCLAUSE_NOMATCH;
> }
> else if (IsA(clause, ScalarArrayOpExpr))
> {
> if (!equal(leftop, partkey) ||
> !PartCollMatchesExprColl(partcoll, saop->inputcollid))
> return PARTCLAUSE_NOMATCH;
> }
> So I think match_clause_to_partition_key handling collation is fine.
>
> I think the problem is match_expr_to_partition_keys
> don't have a collation related check.
>

Sorry, it's a typo. It should be match_expr_to_partition_keys().

> CREATE TABLE pagg_join1 (c text collate case_insensitive) PARTITION BY
> LIST(c collate "C");
> CREATE TABLE pagg_join2 (c text collate "C") PARTITION BY LIST(c
> collate case_insensitive);
> CREATE TABLE pagg_join3 (c text collate "POSIX") PARTITION BY LIST(c
> collate "C");
> CREATE TABLE pagg_join4 (c text collate case_insensitive) PARTITION BY
> LIST(c collate ignore_accents);
>
> Our partition-wise join is based on Equi-join [1].
> In some cases,column and partitionkey collation are different,
> but if these two collations are deterministic, then texteq should work
> as expected.
> So I think, pagg_join3 can do partition-wise join,
> I think pagg_join2 can do partition-wise join also.
>
> we can let all (pagg_join1, pagg_join2, pagg_join3, pagg_join4) cannot
> do partition-wise join (join with themself),
> or we can let pagg_join2, pagg_join3 do partition-wise join (join with
> themself).
>
>
> POC attached, will let pagg_join2, pagg_join3 do partition-wise join.
>

Hmm, I'm not sure

>
> [1] https://en.wikipedia.org/wiki/Join_%28SQL%29#Equi-join
>

--
Thanks,
Tender Wang