Lateral join filter no longer pushed down at 2.9.3

[rose-rey]

Summary

After upgrading from mysql_fdw 2.9.2 to 2.9.3, WHERE clause pushdown stopped working for foreign tables used inside a LEFT JOIN LATERAL where the join key is a correlated reference to an outer row. The entire foreign table is now pulled into PostgreSQL and filtered locally, instead of the filter being sent to MySQL. For us, the lateral joins are constructed from Hasura's GraphQL engine, so we can't rewrite the query to use regular left joins.

Reproduction

Two foreign tables on the same MySQL server:

• users (foreign table over a MySQL view)
• profiles (foreign table over a MySQL view)

Case 1 — explicit JOIN (works correctly):

EXPLAIN ANALYZE VERBOSE
SELECT u.id, email, first_name, last_name, p.id, p.license_type
FROM users u
JOIN profiles p ON u.id = p.user_id
WHERE full_name_email LIKE '%example@example.com%';

Result: single remote query, join pushed down to MySQL:

Foreign Scan  (cost=15.00..35.00 rows=5000 width=1276) (actual time=2655.616..2655.618 rows=1 loops=1)
  Relations: (prod.users u) INNER JOIN (prod.profiles p)
  Remote query: SELECT r1.`id`, r1.`email`, r1.`first_name`, r1.`last_name`, r2.`id`, r2.`license_type`
    FROM (`prod`.`users` r1
    INNER JOIN `prod`.`profiles` r2 ON (((r1.`id` = r2.`user_id`))))
    WHERE ((r1.`full_name_email` LIKE BINARY '%example@example.com%'))
Execution Time: 2657.111 ms

Case 2 — LATERAL join with correlated filter (broken — no pushdown):

EXPLAIN ANALYZE VERBOSE
SELECT u.id, u.email, u.first_name, u.last_name, sub.*
FROM users u
LEFT JOIN LATERAL (
    SELECT * FROM profiles p
    WHERE p.user_id = u.id
    LIMIT 1
) sub ON true
WHERE u.full_name_email LIKE '%example@example.com%';

Result: the outer table's WHERE clause is still pushed down correctly, but the inner (lateral) foreign scan pulls the entire remote table with no filter applied remotely, then filters locally:

Nested Loop Left Join (actual time=11714.906..11714.910 rows=1 loops=1)
  ->  Foreign Scan on users (actual time=2633.364..2633.366 rows=1 loops=1)
        Remote query: SELECT `id`, `first_name`, `last_name`, `email`
          FROM `prod`.`users`
          WHERE ((`full_name_email` LIKE BINARY '%example@example.com%'))
  ->  Subquery Scan on "sub" (actual time=9081.534..9081.536 rows=1 loops=1)
        ->  Limit (actual time=9081.517..9081.517 rows=1 loops=1)
              ->  Foreign Scan on profiles (actual time=9081.515..9081.515 rows=1 loops=1)
                    Filter: (users.id = profiles.user_id)
                    Rows Removed by Filter: 248906
                    Remote query: SELECT `id`, `user_id`, `license_type`
                      FROM `prod`.`profiles`
Execution Time: 11716.928 ms

Note the second Remote query: has no WHERE clause — profiles (248,907 rows) is pulled in full and filtered in PostgreSQL (Rows Removed by Filter: 248906), once per outer row.

Environment

PostgreSQL 14.22 (AWS RDS), MySQL 8