postgres_fdw: semi-join pushdown generates invalid remote SQL ("missing FROM-clause entry") with a nested correlated subquery

Hi,

postgres_fdw's semi-join pushdown (new in 17) can generate remote SQL that
references a relation outside the EXISTS it belongs to, so the remote
server rejects it with "missing FROM-clause entry for table".

PostgreSQL versions: 17.10 and 18.6 (official Docker images, Debian). We
also saw it on 17.11 and 19beta1.
Operating system: Linux (Debian, aarch64 and x86_64).

Reproduction (fresh database, superuser, loopback server):

CREATE EXTENSION postgres_fdw;
DO $d$ BEGIN
EXECUTE format('CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname %L, port %L)',
current_database(), current_setting('port'));
END $d$;
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;
CREATE TABLE a (id int PRIMARY KEY, ts timestamptz);
CREATE TABLE b (id int PRIMARY KEY, a_id int, n int);
INSERT INTO a SELECT g, now() FROM generate_series(1,1000) g;
INSERT INTO b SELECT g, g, 1 FROM generate_series(1,1000) g;
ANALYZE a; ANALYZE b;
CREATE FOREIGN TABLE fa (id int, ts timestamptz) SERVER loopback OPTIONS
(table_name 'a');
CREATE FOREIGN TABLE fb (id int, a_id int, n int) SERVER loopback OPTIONS
(table_name 'b');
ALTER SERVER loopback OPTIONS (ADD use_remote_estimate 'true');

SELECT count(*) FROM fa
WHERE EXISTS (SELECT 1 FROM fb
WHERE fb.a_id =
https://www.google.com/url?q=http://fa.id&source=gmail&ust=1791909307574000&sa=E
AND EXISTS (SELECT 1 FROM fa x WHERE
https://www.google.com/url?q=http://x.id&source=gmail&ust=1791909307574000&sa=E
= fb.n AND x.ts = fa.ts));

Actual result:

ERROR: missing FROM-clause entry for table "r2"
CONTEXT: remote SQL command: EXPLAIN SELECT r2.n, r1.ts FROM public.a r1
WHERE EXISTS (SELECT NULL FROM public.b r2 WHERE ((
https://www.google.com/url?q=http://r1.id&source=gmail&ust=1791909307574000&sa=E
= r2.a_id)))

Expected result: 1000 (the same query against the local tables a and b
returns 1000, and so does the foreign query with use_remote_estimate off).

The inner EXISTS refers to the outermost relation (fa.ts), so it stays a
SubPlan in the semi-join's join clause. It looks like postgres_fdw keeps
that clause as a local condition and then adds the columns it needs (r2.n)
to the remote target list, outside the EXISTS where r2 is not in scope.
With use_remote_estimate on, the invalid SQL is sent while costing the
path, so the query fails even if that path would not be chosen.

We also hit the same error, without any nested subquery, when a condition
inside a single-level EXISTS compares an outer column with an inner column
through an expression postgres_fdw does not ship, for example:

EXISTS (SELECT 1 FROM fb p WHERE
https://www.google.com/url?q=http://p.id&source=gmail&ust=1791909307574000&sa=E
=
https://www.google.com/url?q=http://t.id&source=gmail&ust=1791909307574000&sa=E
AND EXTRACT(EPOCH FROM t.d) < EXTRACT(EPOCH FROM p.d) * 0.8)
EXISTS (SELECT 1 FROM fb p WHERE
https://www.google.com/url?q=http://p.id&source=gmail&ust=1791909307574000&sa=E
=
https://www.google.com/url?q=http://t.id&source=gmail&ust=1791909307574000&sa=E
AND p.ts > t.ts + interval '3 days')

Some of these fail at execution too, not only during EXPLAIN with
use_remote_estimate.

Thanks,
Kelly Morrison