"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=f2.cprd
> WHERE f1.cprd is null;
> Per default we see a merge anti join, and this gives results, which is wrong:
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 = (clb_global_product.cprd)::text)
> -> Index Scan using data_2d_clb_global_product_pkey on rsu_adm.data_2d_clb_global_product f2 (cost=0.42..2898.56
rows=101426width=34)
> Output: f2.cprd, f2.xtc_id, f2.rprd, f2.prdgalsts_id, f2.dlz_last_transaction_ts
> -> Foreign Scan on ro_dlz.clb_global_product (cost=100.42..52506.16 rows=923613 width=34)
> 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_transaction_ts FROM ro_rsu.clb_global_product
ORDERBY cprd ASC NULLS LAST
> I am aware that the version of glibc is not the same between those red hats. Is this expected?
That's certainly a hazard, but do the servers even have the same
collation settings for these columns?
regards, tom lane