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

On Thu, Oct 24, 2024 at 3:01 PM Tender Wang <tndrwang@gmail.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.

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.


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