Semi-join related bug in postgres_fdw

Hello hackers,

while working on a patch on postgres_fdw, I came across this bug,

postgres_fdw is shipping a semi-join to the remote server even when one of
the quals is not shippable, and in doing so it is creating a wrong sql
which eventually errors out.

CREATE TABLE t1 (a int, b int);
CREATE TABLE t2 (a int, b int);
INSERT INTO t1 SELECT g, g % 10 FROM generate_series(1, 100) g;
INSERT INTO t2 SELECT g, g % 10 FROM generate_series(1, 100) g;
ANALYZE t1, t2;

CREATE FOREIGN TABLE ft1 (a int, b int) SERVER loopback OPTIONS (table_name
't1');
CREATE FOREIGN TABLE ft2 (a int, b int) SERVER loopback OPTIONS (table_name
't2');

CREATE FUNCTION no_ship(x int) RETURNS int LANGUAGE plpgsql IMMUTABLE
AS $$BEGIN RETURN x; END$$;

SELECT count(*) FROM t1
WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = no_ship(t1.b));
count
-------
100
(1 row)

On firing the same query on foreign server,

SELECT count(*) FROM ft1 WHERE EXISTS (SELECT 1 FROM ft2 WHERE ft2.b =
no_ship(ft1.b));
ERROR: missing FROM-clause entry for table "r2"
CONTEXT: remote SQL command: EXPLAIN SELECT r1.b, r2.b FROM public.t1 r1
WHERE EXISTS (SELECT NULL FROM public.t2 r2)

Even the explain statement fails.
In my understanding what is happening here is that in semjoin_target_ok it
is only checking for remote_conds and local_conds were left out.

The issue has existed since v17.

I am also attaching a patch to fix this issue.In the patch
semojoin_target_ok also gets local_conds now. Later it checks if the table
is at the inner side of the semi-join if so then stops the pushdown if
there is any column reference from the inner side, by returning false. So
now both the remote and local conditions are covered. In order to make the
check for semi_join in foreign_join_ok work, it is now called later after
the loop which classifies the columns in local_conds and remote_conds. I
have also added the found test case to the regression_suite.

I have attached the patches from versions v17 onwards.
I am also attaching a reproducible script (by Claude) for the issue.
--
Regards,
Rafia Sabih
CYBERTEC PostgreSQL International GmbH