Wrong results with postgres_fdw and merge anti join from RHEL 7.9 to RHEL 8.7

From: "Daniel Westermann (DWE)" <daniel(dot)westermann(at)dbi-services(dot)com>
To: "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: Wrong results with postgres_fdw and merge anti join from RHEL 7.9 to RHEL 8.7
Date: 2023-04-05 18:33:08
Message-ID: GV0P278MB0419C826AF173592DA648C88D2909@GV0P278MB0419.CHEP278.PROD.OUTLOOK.COM
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hi,

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:

rsup1=# 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;
cprd | xtc_id | rprd | prdgalsts_id | dlz_last_transaction_ts | cprd | xtc_id | rprd | prdgalsts_id | dlz_last_tran
---------------+--------+---------+--------------+------------------------------+------+--------+------+--------------+--------------
0027033 | | 179722 | 1 | 2023-04-03 06:15:09.45135+02 | | | | |
0112113 | | 3199208 | 1 | 2023-04-03 06:15:09.45135+02 | | | | |
0116713 | | 2071012 | 1 | 2023-04-03 06:15:09.45135+02 | | | | |
0116953 | | 2070136 | 1 | 2023-04-03 06:15:09.45135+02 | | | | |
...

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Anti Join (cost=100.84..67203.45 rows=50713 width=122)
Output: f2.cprd, f2.xtc_id, f2.rprd, f2.prdgalsts_id, f2.dlz_last_transaction_ts, 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
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=101426 width=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 ORDER BY cprd ASC NULLS LAST
(8 rows)

Disabling merge join gives the correct result:
rsup1=# set enable_mergejoin = off;
SET
rsup1=# explain verbose 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;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Anti Join (cost=60274.55..681118.72 rows=50713 width=122)
Output: f2.cprd, f2.xtc_id, f2.rprd, f2.prdgalsts_id, f2.dlz_last_transaction_ts, 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
Hash Cond: ((f2.cprd)::text = (clb_global_product.cprd)::text)
-> Seq Scan on rsu_adm.data_2d_clb_global_product f2 (cost=0.00..1768.26 rows=101426 width=34)
Output: f2.cprd, f2.xtc_id, f2.rprd, f2.prdgalsts_id, f2.dlz_last_transaction_ts
-> Hash (cost=41513.39..41513.39 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
-> Foreign Scan on ro_dlz.clb_global_product (cost=100.00..41513.39 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
(10 rows)

rsup1=# 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;
cprd | xtc_id | rprd | prdgalsts_id | dlz_last_transaction_ts | cprd | xtc_id | rprd | prdgalsts_id | dlz_last_transaction_ts
------+--------+------+--------------+-------------------------+------+--------+------+--------------+-------------------------
(0 rows)

This is the server definition:
rsup1=# \des+
List of foreign servers
Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW options | Description
---------+----------+----------------------+-------------------+------+---------+------------------------------------------------------------------------------------------------------+-------------
tgt_srv | postgres | postgres_fdw | | | | (host '192.168.100.245', dbname 'dlzp1', port '5432', use_remote_estimate 'true', fetch_size '5000') |
(1 row)

I am aware that the version of glibc is not the same between those red hats. Is this expected?

Thanks in advance
Daniel

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2023-04-05 18:41:05 Re: Wrong results with postgres_fdw and merge anti join from RHEL 7.9 to RHEL 8.7
Previous Message PG Bug reporting form 2023-04-05 17:32:26 BUG #17887: EDB Community Installer for windows, locale selection not working properly