Horton

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

Amit Langote <amitlangote09@gmail.com>

View thread View on postgr.es
From: Amit Langote <amitlangote09@gmail.com>
To: Tender Wang <tndrwang@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2024-11-01T07:26:45Z
Lists: pgsql-hackers 
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.


--
Thanks, Amit Langote

Commits

  1. Disallow partitionwise join when collations don't match

  2. Disallow partitionwise grouping when collations don't match