From: Tom Lane <tgl@sss.pgh.pa.us>
To: "Daniel Westermann (DWE)" <daniel.westermann@dbi-services.com>
cc: "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Subject: Re: Wrong results with postgres_fdw and merge anti join from RHEL 7.9
 to RHEL 8.7
In-reply-to: 
 <GV0P278MB0419C826AF173592DA648C88D2909@GV0P278MB0419.CHEP278.PROD.OUTLOOK.COM>
References: 
 <GV0P278MB0419C826AF173592DA648C88D2909@GV0P278MB0419.CHEP278.PROD.OUTLOOK.COM>
Comments: In-reply-to "Daniel Westermann (DWE)"
 <daniel.westermann@dbi-services.com>
	message dated "Wed, 05 Apr 2023 18:33:08 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2017351.1680720065.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 05 Apr 2023 14:41:05 -0400
Message-ID: <2017352.1680720065@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2017352.1680720065%40sss.pgh.pa.us>
Precedence: bulk

"Daniel Westermann (DWE)" <daniel.westermann@dbi-services.com> writes:
> I am not sure if this qualifies as  bug, but anyway:

> Source instance: PostgreSQL 13.7 on RHEL 7.9
> Target instance PostgreSQL 13.7 on RHEL 8.7

> This is the statement:
> SELECT * FROM "rsu_adm"."data_2d_clb_global_product" f2
> LEFT OUTER JOIN "rsu_adm"."clb_global_product" f1 on f1.cprd=3Df2.cprd
> WHERE f1.cprd is null;

> Per default we see a merge anti join, and this gives results, which is w=
rong:

You didn't provide anything useful like the table schemas, but
correctness of a merge join depends on the servers having the same
ideas about sort ordering, and if "cprd" is a text-type column then
inconsistent collations could break that.

The given plan is at hazard for that because it intends to do
one sort locally and the other remotely:

>    Merge Cond: ((f2.cprd)::text =3D (clb_global_product.cprd)::text)
>    ->  Index Scan using data_2d_clb_global_product_pkey on rsu_adm.data_=
2d_clb_global_product f2  (cost=3D0.42..2898.56 rows=3D101426 width=3D34)
>          Output: f2.cprd, f2.xtc_id, f2.rprd, f2.prdgalsts_id, f2.dlz_la=
st_transaction_ts
>    ->  Foreign Scan on ro_dlz.clb_global_product  (cost=3D100.42..52506.=
16 rows=3D923613 width=3D34)
>          Output: clb_global_product.cprd, clb_global_product.xtc_id, clb=
_global_product.rprd, clb_global_product.prdgalsts_id, clb_global_product.=
dlz_last_transaction_ts
>          Remote SQL: SELECT cprd, xtc_id, rprd, prdgalsts_id, dlz_last_t=
ransaction_ts FROM ro_rsu.clb_global_product ORDER BY cprd ASC NULLS LAST

> I am aware that the version of glibc is not the same between those red h=
ats. Is this expected?

That's certainly a hazard, but do the servers even have the same
collation settings for these columns?

			regards, tom lane