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>
 <CAEG8a3Ks7_5b+0bnqbSceAbofj=o1RmP4qedv1Jr43Nz1wY-vA@mail.gmail.com> <CA+HiwqEHx++qTjCGmWyYZDiPR=_fLGPjR7RcFEVnSNZVc-Qj2g@mail.gmail.com>
In-Reply-To: <CA+HiwqEHx++qTjCGmWyYZDiPR=_fLGPjR7RcFEVnSNZVc-Qj2g@mail.gmail.com>
From: jian he <jian.universality@gmail.com>
Date: Fri, 1 Nov 2024 13:39:11 +0800
Message-ID: <CACJufxG1pKrjf+_ugjYJFmww_kGd9Wn8OpU=XoH5w2hUqSXFbg@mail.gmail.com>
Subject: Re: Wrong result when enable_partitionwise_join is on if collation of
 PartitionKey and Column is different.
To: Amit Langote <amitlangote09@gmail.com>
Cc: Junwang Zhao <zhjwpku@gmail.com>, Tender Wang <tndrwang@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/CACJufxG1pKrjf%2B_ugjYJFmww_kGd9Wn8OpU%3DXoH5w2hUqSXFbg%40mail.gmail.com>
Precedence: bulk

On Thu, Oct 31, 2024 at 9:09=E2=80=AFPM Amit Langote <amitlangote09@gmail.c=
om> wrote:
>
>
> I think we should insist that the join key collation and the partition
> collation are exactly the same and refuse to match them if they are
> not.
>
> +           {
> +               Oid     colloid =3D  exprCollation((Node *) expr);
> +
> +               if ((partcoll !=3D colloid) &&
> +                    OidIsValid(colloid) &&
> +                    !get_collation_isdeterministic(colloid))
> +                   *coll_incompatiable =3D true;
>
> I am not quite sure what is the point of checking whether or not the
> expression collation is deterministic after confirming that it's not
> the same as partcoll.
>
> Attached 0002 is what I came up with.  One thing that's different from
> what Jian proposed is that match_expr_to_partition_keys() returns -1
> (expr not matched to any key) when the collation is also not matched
> instead of using a separate output parameter for that.
>
i was thinking that
CREATE TABLE part_tab (c text collate "POSIX") PARTITION BY LIST(c collate =
"C");
maybe can do partitionwise join.
join key collation and the partition key collation same sure would
make things easy.


about 0002.
Similar to PartCollMatchesExprColl in match_clause_to_partition_key
I think we can simply do the following:
no need to hack match_expr_to_partition_keys.

@@ -2181,6 +2181,9 @@ have_partkey_equi_join(PlannerInfo *root,
RelOptInfo *joinrel,
                if (ipk1 !=3D ipk2)
                        continue;

+               if (rel1->part_scheme->partcollation[ipk1] !=3D
opexpr->inputcollid)
+                       return false;