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.
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.
