BUG #19071: commit b448f1c8d broke LEFT JOIN pushdown

The following bug has been logged on the website:

Bug reference: 19071
Logged by: Anton Ratundalov
Email address: a(dot)ratundalov(at)postgrespro(dot)ru
PostgreSQL version: 18.0
Operating system: Debian 6.1.148-1 (2025-08-26) x86_64 GNU/Linux
Description:

DDL :

\c postgres anton 127.0.0.2 65432

DROP TABLE IF exists a;
DROP TABLE IF exists b;

CREATE TABLE IF NOT exists a (c1 integer NOT NULL, c2 text);
CREATE TABLE IF NOT exists b (c1 integer NOT NULL, c2 text);

\c postgres anton 127.0.0.1 65432

CREATE extension IF NOT exists postgres_fdw;

CREATE server IF NOT exists s2 FOREIGN data wrapper postgres_fdw
options (host '127.0.0.2', port '65432', dbname 'postgres');
CREATE USER mapping IF NOT exists FOR anton server s2 options (USER
'anton');

DROP TABLE IF exists a cascade;
DROP TABLE IF exists b cascade;

CREATE TABLE IF NOT exists a (c1 integer NOT NULL, c2 text)
partition by hash(c1);
CREATE TABLE IF NOT exists a_p1 partition of a FOR values WITH
(modulus 2, remainder 0);
CREATE FOREIGN TABLE IF NOT exists a_p2 partition of a FOR values
WITH (modulus 2, remainder 1) server s2 options (table_name 'a');

CREATE TABLE IF NOT exists b (c1 integer NOT NULL, c2 text)
partition by hash(c1);
CREATE TABLE IF NOT exists b_p1 partition of b FOR values WITH
(modulus 2, remainder 0);
CREATE FOREIGN TABLE IF NOT exists b_p2 partition of b FOR values
WITH (modulus 2, remainder 1) server s2 options (table_name 'b');

INSERT INTO a ( c1, c2 ) SELECT i, 'text_'||(i)::text FROM
generate_series(1,6) i;
INSERT INTO b ( c1, c2 ) SELECT i, 'text_'||(i)::text FROM
generate_series(1,4) i;

set enable_partitionwise_join TO ON;

QUERY :

EXPLAIN( verbose, costs off )
SELECT * FROM a LEFT JOIN b ON a.c1=b.c1 WHERE a.c1=6;

PLAN :

QUERY PLAN
------------------------------------------------------------------------
Nested Loop Left Join
Output: a.c1, a.c2, b.c1, b.c2
-> Foreign Scan on public.a_p2 a
Output: a.c1, a.c2
Remote SQL: SELECT c1, c2 FROM public.a WHERE ((c1 = 6))
-> Materialize
Output: b.c1, b.c2
-> Foreign Scan on public.b_p2 b
Output: b.c1, b.c2
Remote SQL: SELECT c1, c2 FROM public.b WHERE ((c1 = 6))
(10 rows)

PLAN BEFORE THE CHANGES :

QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
Foreign Scan
Output: a.c1, a.c2, b.c1, b.c2
Relations: (public.a_p2 a) LEFT JOIN (public.b_p2 b)
Remote SQL: SELECT r4.c1, r4.c2, r5.c1, r5.c2 FROM (public.a r4 LEFT JOIN
public.b r5 ON (((r4.c1 = r5.c1)) AND ((r5.c1 = 6)))) WHERE ((r4.c1 = 6))
(4 rows)