MIME-Version: 1.0
References: <CAHewXNno_HKiQ6PqyLYfuqDtwp7KKHZiH1J7Pqyz0nr+PS2Dwg@mail.gmail.com>
 <CAHewXN=sGCF7cp3r+8PyZXP0TT+Q1imoEe-7Z-0Sxxb76eWAXg@mail.gmail.com>
 <CA+HiwqFmtOjVX0Gy2HCMGDRJg3zKNmhdt=o=K=pTvE2VhEe-1A@mail.gmail.com>
 <CAHewXNk1K2G56C7kXvuYzQexeQs2EuHA3x-7v68ihn843svEZQ@mail.gmail.com>
 <CACJufxFefXhnD+Z3swU9A0SCK3AfmEbTSx7hj=TCu-0hr2OOgA@mail.gmail.com>
 <CAHewXNnoTXxsAuJKxEjs-TASjG_-soH9Y_+tup8a5teggS4caw@mail.gmail.com> <CACJufxF1bmHSc+L7Hg7QxnH9UDbyP+qMsK8Z2ZBU8kux6C=t3A@mail.gmail.com>
In-Reply-To: <CACJufxF1bmHSc+L7Hg7QxnH9UDbyP+qMsK8Z2ZBU8kux6C=t3A@mail.gmail.com>
From: Junwang Zhao <zhjwpku@gmail.com>
Date: Wed, 30 Oct 2024 20:36:25 +0800
Message-ID: <CAEG8a3Ks7_5b+0bnqbSceAbofj=o1RmP4qedv1Jr43Nz1wY-vA@mail.gmail.com>
Subject: Re: Wrong result when enable_partitionwise_join is on if collation of
 PartitionKey and Column is different.
To: jian he <jian.universality@gmail.com>
Cc: Tender Wang <tndrwang@gmail.com>, Amit Langote <amitlangote09@gmail.com>, 
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAEG8a3Ks7_5b%2B0bnqbSceAbofj%3Do1RmP4qedv1Jr43Nz1wY-vA%40mail.gmail.com>
Precedence: bulk

On Wed, Oct 30, 2024 at 11:58=E2=80=AFAM jian he <jian.universality@gmail.c=
om> wrote:
>
> I missed a case when column collation and partition key collation are
> the same and indeterministic.
> that should be fine for partition-wise join.
> so v2 attached.
>
> have_partkey_equi_join, match_expr_to_partition_keys didn't do any
> collation related check.
> propose v2 change disallow partitionwise join for  case when
> column collation is indeterministic *and* is differ from partition
> key's collation.
>
> the attached partition_wise_join_collation.sql is the test script.
> you may use it to compare with the master behavior.

What if the partkey collation and column collation are both deterministic,
but with different sort order?

I'm not familiar with this part of the code base, but it seems to me the
partition wise join should use partkey collation instead of column collatio=
n,
because it's the partkey collation that decides which partition a row to
be dispatched.

What Jian proposed is also reasonable but seems another aspect of $subject?

Just some random thought, might be wrong ;(

--=20
Regards
Junwang Zhao