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.ratundalov@postgrespro.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)