File: join_pushdown_3.out

package info (click to toggle)
postgresql-mysql-fdw 2.9.3-1
links: PTS, VCS
area: main
in suites: forky, sid
size: 1,280 kB
sloc: ansic: 6,010; sql: 1,994; sh: 84; makefile: 42
file content (1286 lines) | stat: -rw-r--r-- 64,828 bytes
\set MYSQL_HOST			`echo \'"$MYSQL_HOST"\'`
\set MYSQL_PORT			`echo \'"$MYSQL_PORT"\'`
\set MYSQL_USER_NAME	`echo \'"$MYSQL_USER_NAME"\'`
\set MYSQL_PASS			`echo \'"$MYSQL_PWD"\'`
-- Before running this file User must create database mysql_fdw_regress on
-- mysql with all permission for MYSQL_USER_NAME user with MYSQL_PWD password
-- and ran mysql_init.sh file to create tables.
\c contrib_regression
CREATE EXTENSION IF NOT EXISTS mysql_fdw;
-- FDW-139: Support for JOIN pushdown.
CREATE SERVER mysql_svr FOREIGN DATA WRAPPER mysql_fdw
  OPTIONS (host :MYSQL_HOST, port :MYSQL_PORT);
CREATE USER MAPPING FOR public SERVER mysql_svr
  OPTIONS (username :MYSQL_USER_NAME, password :MYSQL_PASS);
CREATE SERVER mysql_svr1 FOREIGN DATA WRAPPER mysql_fdw
  OPTIONS (host :MYSQL_HOST, port :MYSQL_PORT);
CREATE USER MAPPING FOR public SERVER mysql_svr1
  OPTIONS (username :MYSQL_USER_NAME, password :MYSQL_PASS);
CREATE TYPE user_enum AS ENUM ('foo', 'bar', 'buz');
CREATE FOREIGN TABLE fdw139_t1(c1 int, c2 int, c3 text COLLATE "C", c4 text COLLATE "C")
  SERVER mysql_svr OPTIONS(dbname 'mysql_fdw_regress', table_name 'test1');
CREATE FOREIGN TABLE fdw139_t2(c1 int, c2 int, c3 text COLLATE "C", c4 text COLLATE "C")
  SERVER mysql_svr OPTIONS(dbname 'mysql_fdw_regress', table_name 'test2');
CREATE FOREIGN TABLE fdw139_t3(c1 int, c2 int, c3 text COLLATE "C")
  SERVER mysql_svr OPTIONS(dbname 'mysql_fdw_regress', table_name 'test3');
CREATE FOREIGN TABLE fdw139_t4(c1 int, c2 int, c3 text COLLATE "C")
  SERVER mysql_svr1 OPTIONS(dbname 'mysql_fdw_regress', table_name 'test3');
INSERT INTO fdw139_t1 values(1, 100, 'AAA1', 'foo');
INSERT INTO fdw139_t1 values(2, 100, 'AAA2', 'bar');
INSERT INTO fdw139_t1 values(11, 100, 'AAA11', 'foo');
INSERT INTO fdw139_t2 values(1, 200, 'BBB1', 'foo');
INSERT INTO fdw139_t2 values(2, 200, 'BBB2', 'bar');
INSERT INTO fdw139_t2 values(12, 200, 'BBB12', 'foo');
INSERT INTO fdw139_t3 values(1, 300, 'CCC1');
INSERT INTO fdw139_t3 values(2, 300, 'CCC2');
INSERT INTO fdw139_t3 values(13, 300, 'CCC13');
SET enable_mergejoin TO off;
SET enable_hashjoin TO off;
SET enable_sort TO off;
ALTER FOREIGN TABLE fdw139_t1 ALTER COLUMN c4 type user_enum;
ALTER FOREIGN TABLE fdw139_t2 ALTER COLUMN c4 type user_enum;
-- Join two tables
-- target list order is different for v10.
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 JOIN fdw139_t2 t2 ON (t1.c1 = t2.c1)
  ORDER BY t1.c3, t1.c1;
                                                                                                           QUERY PLAN                                                                                                            
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan
   Output: t1.c1, t2.c1, t1.c3
   Relations: (mysql_fdw_regress.fdw139_t1 t1) INNER JOIN (mysql_fdw_regress.fdw139_t2 t2)
   Remote query: SELECT r1.`c1`, r2.`c1`, r1.`c3` FROM (`mysql_fdw_regress`.`test1` r1 INNER JOIN `mysql_fdw_regress`.`test2` r2 ON (((r1.`c1` = r2.`c1`)))) ORDER BY r1.`c3` IS NULL, r1.`c3` ASC, r1.`c1` IS NULL, r1.`c1` ASC
(4 rows)

SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 JOIN fdw139_t2 t2 ON (t1.c1 = t2.c1)
  ORDER BY t1.c3, t1.c1;
 c1 | c1 
----+----
  1 |  1
  2 |  2
(2 rows)

-- INNER JOIN with where condition.  Should execute where condition separately
-- on remote side.
-- target list order is different for v10.
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 JOIN fdw139_t2 t2 ON (t1.c1 = t2.c1) WHERE t1.c2 = 100
  ORDER BY t1.c3, t1.c1;
                                                                                                                       QUERY PLAN                                                                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan
   Output: t1.c1, t2.c1, t1.c3
   Relations: (mysql_fdw_regress.fdw139_t1 t1) INNER JOIN (mysql_fdw_regress.fdw139_t2 t2)
   Remote query: SELECT r1.`c1`, r2.`c1`, r1.`c3` FROM (`mysql_fdw_regress`.`test1` r1 INNER JOIN `mysql_fdw_regress`.`test2` r2 ON (((r1.`c1` = r2.`c1`)))) WHERE ((r1.`c2` = 100)) ORDER BY r1.`c3` IS NULL, r1.`c3` ASC, r1.`c1` IS NULL, r1.`c1` ASC
(4 rows)

SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 JOIN fdw139_t2 t2 ON (t1.c1 = t2.c1) WHERE t1.c2 = 100
  ORDER BY t1.c3, t1.c1;
 c1 | c1 
----+----
  1 |  1
  2 |  2
(2 rows)

-- INNER JOIN in which join clause is not pushable.
-- target list order is different for v10.
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 JOIN fdw139_t2 t2 ON (abs(t1.c1) = t2.c1) WHERE t1.c2 = 100
  ORDER BY t1.c3, t1.c1;
                                                                                                               QUERY PLAN                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan
   Output: t1.c1, t2.c1, t1.c3
   Filter: (abs(t1.c1) = t2.c1)
   Relations: (mysql_fdw_regress.fdw139_t1 t1) INNER JOIN (mysql_fdw_regress.fdw139_t2 t2)
   Remote query: SELECT r1.`c1`, r2.`c1`, r1.`c3` FROM (`mysql_fdw_regress`.`test1` r1 INNER JOIN `mysql_fdw_regress`.`test2` r2 ON (TRUE)) WHERE ((r1.`c2` = 100)) ORDER BY r1.`c3` IS NULL, r1.`c3` ASC, r1.`c1` IS NULL, r1.`c1` ASC
(5 rows)

SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 JOIN fdw139_t2 t2 ON (abs(t1.c1) = t2.c1) WHERE t1.c2 = 100
  ORDER BY t1.c3, t1.c1;
 c1 | c1 
----+----
  1 |  1
  2 |  2
(2 rows)

-- Join three tables
-- target list order is different for v10.
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c2, t3.c3
  FROM fdw139_t1 t1 JOIN fdw139_t2 t2 ON (t1.c1 = t2.c1) JOIN fdw139_t3 t3 ON (t3.c1 = t1.c1)
  ORDER BY t1.c3, t1.c1;
                                                                                                                                                   QUERY PLAN                                                                                                                                                    
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan
   Output: t1.c1, t2.c2, t3.c3, t1.c3
   Relations: ((mysql_fdw_regress.fdw139_t1 t1) INNER JOIN (mysql_fdw_regress.fdw139_t2 t2)) INNER JOIN (mysql_fdw_regress.fdw139_t3 t3)
   Remote query: SELECT r1.`c1`, r2.`c2`, r4.`c3`, r1.`c3` FROM ((`mysql_fdw_regress`.`test1` r1 INNER JOIN `mysql_fdw_regress`.`test2` r2 ON (((r1.`c1` = r2.`c1`)))) INNER JOIN `mysql_fdw_regress`.`test3` r4 ON (((r1.`c1` = r4.`c1`)))) ORDER BY r1.`c3` IS NULL, r1.`c3` ASC, r1.`c1` IS NULL, r1.`c1` ASC
(4 rows)

SELECT t1.c1, t2.c2, t3.c3
  FROM fdw139_t1 t1 JOIN fdw139_t2 t2 ON (t1.c1 = t2.c1) JOIN fdw139_t3 t3 ON (t3.c1 = t1.c1)
  ORDER BY t1.c3, t1.c1;
 c1 | c2  |  c3  
----+-----+------
  1 | 200 | CCC1
  2 | 200 | CCC2
(2 rows)

EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c1, t3.c1
  FROM fdw139_t1 t1, fdw139_t2 t2, fdw139_t3 t3 WHERE t1.c1 = 11 AND t2.c1 = 12 AND t3.c1 = 13
  ORDER BY t1.c1;
                                                                                                                            QUERY PLAN                                                                                                                             
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan
   Output: t1.c1, t2.c1, t3.c1
   Relations: ((mysql_fdw_regress.fdw139_t1 t1) INNER JOIN (mysql_fdw_regress.fdw139_t2 t2)) INNER JOIN (mysql_fdw_regress.fdw139_t3 t3)
   Remote query: SELECT r1.`c1`, r2.`c1`, r3.`c1` FROM ((`mysql_fdw_regress`.`test1` r1 INNER JOIN `mysql_fdw_regress`.`test2` r2 ON (TRUE)) INNER JOIN `mysql_fdw_regress`.`test3` r3 ON (TRUE)) WHERE ((r3.`c1` = 13)) AND ((r2.`c1` = 12)) AND ((r1.`c1` = 11))
(4 rows)

SELECT t1.c1, t2.c1, t3.c1
  FROM fdw139_t1 t1, fdw139_t2 t2, fdw139_t3 t3 WHERE t1.c1 = 11 AND t2.c1 = 12 AND t3.c1 = 13
  ORDER BY t1.c1;
 c1 | c1 | c1 
----+----+----
 11 | 12 | 13
(1 row)

-- LEFT OUTER JOIN
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 LEFT JOIN fdw139_t2 t2 ON (t1.c1 = t2.c1)
  ORDER BY t1.c1, t2.c1 NULLS LAST LIMIT 2 OFFSET 2;
                                                                                                               QUERY PLAN                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan
   Output: t1.c1, t2.c1
   Relations: (mysql_fdw_regress.fdw139_t1 t1) LEFT JOIN (mysql_fdw_regress.fdw139_t2 t2)
   Remote query: SELECT r1.`c1`, r2.`c1` FROM (`mysql_fdw_regress`.`test1` r1 LEFT JOIN `mysql_fdw_regress`.`test2` r2 ON (((r1.`c1` = r2.`c1`)))) ORDER BY r1.`c1` IS NULL, r1.`c1` ASC, r2.`c1` IS NULL, r2.`c1` ASC LIMIT 2 OFFSET 2
(4 rows)

SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 LEFT JOIN fdw139_t2 t2 ON (t1.c1 = t2.c1)
  ORDER BY t1.c1, t2.c1 NULLS LAST LIMIT 2 OFFSET 2;
 c1 | c1 
----+----
 11 |   
(1 row)

-- LEFT JOIN evaluating as INNER JOIN, having unsafe join clause.
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 LEFT JOIN fdw139_t2 t2 ON (abs(t1.c1) = t2.c1)
  WHERE t2.c1 > 1 ORDER BY t1.c1, t2.c1;
                                                                                                         QUERY PLAN                                                                                                          
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan
   Output: t1.c1, t2.c1
   Filter: (abs(t1.c1) = t2.c1)
   Relations: (mysql_fdw_regress.fdw139_t1 t1) INNER JOIN (mysql_fdw_regress.fdw139_t2 t2)
   Remote query: SELECT r1.`c1`, r2.`c1` FROM (`mysql_fdw_regress`.`test1` r1 INNER JOIN `mysql_fdw_regress`.`test2` r2 ON (TRUE)) WHERE ((r2.`c1` > 1)) ORDER BY r1.`c1` IS NULL, r1.`c1` ASC, r2.`c1` IS NULL, r2.`c1` ASC
(5 rows)

SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 LEFT JOIN fdw139_t2 t2 ON (abs(t1.c1) = t2.c1)
  WHERE t2.c1 > 1 ORDER BY t1.c1, t2.c1;
 c1 | c1 
----+----
  2 |  2
(1 row)

-- LEFT OUTER JOIN in which join clause is not pushable.
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 LEFT JOIN fdw139_t2 t2 ON (abs(t1.c1) = t2.c1)
  ORDER BY t1.c1, t2.c1;
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Sort
   Output: t1.c1, t2.c1
   Sort Key: t1.c1, t2.c1
   ->  Nested Loop Left Join
         Output: t1.c1, t2.c1
         Join Filter: (abs(t1.c1) = t2.c1)
         ->  Foreign Scan on public.fdw139_t1 t1
               Output: t1.c1, t1.c2, t1.c3, t1.c4
               Remote query: SELECT `c1` FROM `mysql_fdw_regress`.`test1`
         ->  Materialize
               Output: t2.c1
               ->  Foreign Scan on public.fdw139_t2 t2
                     Output: t2.c1
                     Remote query: SELECT `c1` FROM `mysql_fdw_regress`.`test2` ORDER BY `c1` IS NULL, `c1` ASC
(14 rows)

SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 LEFT JOIN fdw139_t2 t2 ON (abs(t1.c1) = t2.c1)
  ORDER BY t1.c1, t2.c1;
 c1 | c1 
----+----
  1 |  1
  2 |  2
 11 |   
(3 rows)

-- LEFT OUTER JOIN + placement of clauses.
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t1.c2, t2.c1, t2.c2
  FROM fdw139_t1 t1 LEFT JOIN (SELECT * FROM fdw139_t2 WHERE c1 < 10) t2 ON (t1.c1 = t2.c1)
  WHERE t1.c1 < 10;
                                                                                                   QUERY PLAN                                                                                                    
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan
   Output: t1.c1, t1.c2, fdw139_t2.c1, fdw139_t2.c2
   Relations: (mysql_fdw_regress.fdw139_t1 t1) LEFT JOIN (mysql_fdw_regress.fdw139_t2)
   Remote query: SELECT r1.`c1`, r1.`c2`, r4.`c1`, r4.`c2` FROM (`mysql_fdw_regress`.`test1` r1 LEFT JOIN `mysql_fdw_regress`.`test2` r4 ON (((r1.`c1` = r4.`c1`)) AND ((r4.`c1` < 10)))) WHERE ((r1.`c1` < 10))
(4 rows)

SELECT t1.c1, t1.c2, t2.c1, t2.c2
  FROM fdw139_t1 t1 LEFT JOIN (SELECT * FROM fdw139_t2 WHERE c1 < 10) t2 ON (t1.c1 = t2.c1)
  WHERE t1.c1 < 10;
 c1 | c2  | c1 | c2  
----+-----+----+-----
  1 | 100 |  1 | 200
  2 | 100 |  2 | 200
(2 rows)

-- Clauses within the nullable side are not pulled up, but the top level clause
-- on nullable side is not pushed down into nullable side
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t1.c2, t2.c1, t2.c2
  FROM fdw139_t1 t1 LEFT JOIN (SELECT * FROM fdw139_t2 WHERE c1 < 10) t2 ON (t1.c1 = t2.c1)
  WHERE (t2.c1 < 10 OR t2.c1 IS NULL) AND t1.c1 < 10;
                                                                                                                         QUERY PLAN                                                                                                                          
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan
   Output: t1.c1, t1.c2, fdw139_t2.c1, fdw139_t2.c2
   Relations: (mysql_fdw_regress.fdw139_t1 t1) LEFT JOIN (mysql_fdw_regress.fdw139_t2)
   Remote query: SELECT r1.`c1`, r1.`c2`, r4.`c1`, r4.`c2` FROM (`mysql_fdw_regress`.`test1` r1 LEFT JOIN `mysql_fdw_regress`.`test2` r4 ON (((r1.`c1` = r4.`c1`)) AND ((r4.`c1` < 10)))) WHERE (((r4.`c1` < 10) OR (r4.`c1` IS NULL))) AND ((r1.`c1` < 10))
(4 rows)

SELECT t1.c1, t1.c2, t2.c1, t2.c2
  FROM fdw139_t1 t1 LEFT JOIN (SELECT * FROM fdw139_t2 WHERE c1 < 10) t2 ON (t1.c1 = t2.c1)
  WHERE (t2.c1 < 10 OR t2.c1 IS NULL) AND t1.c1 < 10;
 c1 | c2  | c1 | c2  
----+-----+----+-----
  1 | 100 |  1 | 200
  2 | 100 |  2 | 200
(2 rows)

-- RIGHT OUTER JOIN
-- target list order is different for v10.
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 RIGHT JOIN fdw139_t2 t2 ON (t1.c1 = t2.c1)
  ORDER BY t2.c1, t1.c1 NULLS LAST;
                                                                                                      QUERY PLAN                                                                                                       
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan
   Output: t1.c1, t2.c1
   Relations: (mysql_fdw_regress.fdw139_t2 t2) LEFT JOIN (mysql_fdw_regress.fdw139_t1 t1)
   Remote query: SELECT r1.`c1`, r2.`c1` FROM (`mysql_fdw_regress`.`test2` r2 LEFT JOIN `mysql_fdw_regress`.`test1` r1 ON (((r1.`c1` = r2.`c1`)))) ORDER BY r2.`c1` IS NULL, r2.`c1` ASC, r1.`c1` IS NULL, r1.`c1` ASC
(4 rows)

SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 RIGHT JOIN fdw139_t2 t2 ON (t1.c1 = t2.c1)
  ORDER BY t2.c1, t1.c1 NULLS LAST;
 c1 | c1 
----+----
  1 |  1
  2 |  2
    | 12
(3 rows)

-- Combinations of various joins
-- INNER JOIN + RIGHT JOIN
-- target list order is different for v10.
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c2, t3.c3
  FROM fdw139_t1 t1 JOIN fdw139_t2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN fdw139_t3 t3 ON (t1.c1 = t3.c1)
  ORDER BY t1.c1 NULLS LAST, t1.c3, t1.c1;
                                                                                                                                                   QUERY PLAN                                                                                                                                                   
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan
   Output: t1.c1, t2.c2, t3.c3, t1.c3
   Relations: (mysql_fdw_regress.fdw139_t3 t3) LEFT JOIN ((mysql_fdw_regress.fdw139_t1 t1) INNER JOIN (mysql_fdw_regress.fdw139_t2 t2))
   Remote query: SELECT r1.`c1`, r2.`c2`, r4.`c3`, r1.`c3` FROM (`mysql_fdw_regress`.`test3` r4 LEFT JOIN (`mysql_fdw_regress`.`test1` r1 INNER JOIN `mysql_fdw_regress`.`test2` r2 ON (((r1.`c1` = r2.`c1`)))) ON (((r1.`c1` = r4.`c1`)))) ORDER BY r1.`c1` IS NULL, r1.`c1` ASC, r1.`c3` IS NULL, r1.`c3` ASC
(4 rows)

SELECT t1.c1, t2.c2, t3.c3
  FROM fdw139_t1 t1 JOIN fdw139_t2 t2 ON (t1.c1 = t2.c1) RIGHT JOIN fdw139_t3 t3 ON (t1.c1 = t3.c1)
  ORDER BY t1.c1 NULLS LAST, t1.c3, t1.c1;
 c1 | c2  |  c3   
----+-----+-------
  1 | 200 | CCC1
  2 | 200 | CCC2
    |     | CCC13
(3 rows)

-- FULL OUTER JOIN, should not be pushdown as target database doesn't support
-- it.
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 FULL JOIN fdw139_t1 t2 ON (t1.c1 = t2.c1)
  ORDER BY t1.c1, t2.c1;
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Sort
   Output: t1.c1, t2.c1
   Sort Key: t1.c1, t2.c1
   ->  Merge Full Join
         Output: t1.c1, t2.c1
         Merge Cond: (t1.c1 = t2.c1)
         ->  Foreign Scan on public.fdw139_t1 t1
               Output: t1.c1, t1.c2, t1.c3, t1.c4
               Remote query: SELECT `c1` FROM `mysql_fdw_regress`.`test1` ORDER BY `c1` IS NULL, `c1` ASC
         ->  Materialize
               Output: t2.c1, t2.c2, t2.c3, t2.c4
               ->  Foreign Scan on public.fdw139_t1 t2
                     Output: t2.c1, t2.c2, t2.c3, t2.c4
                     Remote query: SELECT `c1` FROM `mysql_fdw_regress`.`test1` ORDER BY `c1` IS NULL, `c1` ASC
(14 rows)

SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 FULL JOIN fdw139_t1 t2 ON (t1.c1 = t2.c1)
  ORDER BY t1.c1, t2.c1;
 c1 | c1 
----+----
  1 |  1
  2 |  2
 11 | 11
(3 rows)

-- Join two tables with FOR UPDATE clause
-- tests whole-row reference for row marks
-- target list order is different for v10.
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 JOIN fdw139_t2 t2 ON (t1.c1 = t2.c1)
  ORDER BY t1.c3, t1.c1 FOR UPDATE OF t1;
                                                                                                                                  QUERY PLAN                                                                                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan
   Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
   Relations: (mysql_fdw_regress.fdw139_t1 t1) INNER JOIN (mysql_fdw_regress.fdw139_t2 t2)
   Remote query: SELECT r1.`c1`, r2.`c1`, r1.`c3`, r1.`c2`, r1.`c4`, r2.`c2`, r2.`c3`, r2.`c4` FROM (`mysql_fdw_regress`.`test1` r1 INNER JOIN `mysql_fdw_regress`.`test2` r2 ON (((r1.`c1` = r2.`c1`)))) ORDER BY r1.`c3` IS NULL, r1.`c3` ASC, r1.`c1` IS NULL, r1.`c1` ASC
(4 rows)

SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 JOIN fdw139_t2 t2 ON (t1.c1 = t2.c1)
  ORDER BY t1.c3, t1.c1 FOR UPDATE OF t1;
 c1 | c1 
----+----
  1 |  1
  2 |  2
(2 rows)

-- target list order is different for v10.
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 JOIN fdw139_t2 t2 ON (t1.c1 = t2.c1)
  ORDER BY t1.c3, t1.c1 FOR UPDATE;
                                                                                                                                  QUERY PLAN                                                                                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan
   Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
   Relations: (mysql_fdw_regress.fdw139_t1 t1) INNER JOIN (mysql_fdw_regress.fdw139_t2 t2)
   Remote query: SELECT r1.`c1`, r2.`c1`, r1.`c3`, r1.`c2`, r1.`c4`, r2.`c2`, r2.`c3`, r2.`c4` FROM (`mysql_fdw_regress`.`test1` r1 INNER JOIN `mysql_fdw_regress`.`test2` r2 ON (((r1.`c1` = r2.`c1`)))) ORDER BY r1.`c3` IS NULL, r1.`c3` ASC, r1.`c1` IS NULL, r1.`c1` ASC
(4 rows)

SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 JOIN fdw139_t2 t2 ON (t1.c1 = t2.c1)
  ORDER BY t1.c3, t1.c1 FOR UPDATE;
 c1 | c1 
----+----
  1 |  1
  2 |  2
(2 rows)

-- Join two tables with FOR SHARE clause
-- target list order is different for v10.
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 JOIN fdw139_t2 t2 ON (t1.c1 = t2.c1)
  ORDER BY t1.c3, t1.c1 FOR SHARE OF t1;
                                                                                                                                  QUERY PLAN                                                                                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan
   Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
   Relations: (mysql_fdw_regress.fdw139_t1 t1) INNER JOIN (mysql_fdw_regress.fdw139_t2 t2)
   Remote query: SELECT r1.`c1`, r2.`c1`, r1.`c3`, r1.`c2`, r1.`c4`, r2.`c2`, r2.`c3`, r2.`c4` FROM (`mysql_fdw_regress`.`test1` r1 INNER JOIN `mysql_fdw_regress`.`test2` r2 ON (((r1.`c1` = r2.`c1`)))) ORDER BY r1.`c3` IS NULL, r1.`c3` ASC, r1.`c1` IS NULL, r1.`c1` ASC
(4 rows)

SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 JOIN fdw139_t2 t2 ON (t1.c1 = t2.c1)
  ORDER BY t1.c3, t1.c1 FOR SHARE OF t1;
 c1 | c1 
----+----
  1 |  1
  2 |  2
(2 rows)

-- target list order is different for v10.
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 JOIN fdw139_t2 t2 ON (t1.c1 = t2.c1)
  ORDER BY t1.c3, t1.c1 FOR SHARE;
                                                                                                                                  QUERY PLAN                                                                                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan
   Output: t1.c1, t2.c1, t1.c3, t1.*, t2.*
   Relations: (mysql_fdw_regress.fdw139_t1 t1) INNER JOIN (mysql_fdw_regress.fdw139_t2 t2)
   Remote query: SELECT r1.`c1`, r2.`c1`, r1.`c3`, r1.`c2`, r1.`c4`, r2.`c2`, r2.`c3`, r2.`c4` FROM (`mysql_fdw_regress`.`test1` r1 INNER JOIN `mysql_fdw_regress`.`test2` r2 ON (((r1.`c1` = r2.`c1`)))) ORDER BY r1.`c3` IS NULL, r1.`c3` ASC, r1.`c1` IS NULL, r1.`c1` ASC
(4 rows)

SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 JOIN fdw139_t2 t2 ON (t1.c1 = t2.c1)
  ORDER BY t1.c3, t1.c1 FOR SHARE;
 c1 | c1 
----+----
  1 |  1
  2 |  2
(2 rows)

-- Join in CTE.
-- Explain plan difference between v11 (or pre) and later.
EXPLAIN (COSTS false, VERBOSE)
WITH t (c1_1, c1_3, c2_1) AS (
  SELECT t1.c1, t1.c3, t2.c1
    FROM fdw139_t1 t1 JOIN fdw139_t2 t2 ON (t1.c1 = t2.c1)
) SELECT c1_1, c2_1 FROM t ORDER BY c1_3, c1_1;
                                                                                                           QUERY PLAN                                                                                                            
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan
   Output: t1.c1, t2.c1, t1.c3
   Relations: (mysql_fdw_regress.fdw139_t1 t1) INNER JOIN (mysql_fdw_regress.fdw139_t2 t2)
   Remote query: SELECT r2.`c1`, r3.`c1`, r2.`c3` FROM (`mysql_fdw_regress`.`test1` r2 INNER JOIN `mysql_fdw_regress`.`test2` r3 ON (((r2.`c1` = r3.`c1`)))) ORDER BY r2.`c3` IS NULL, r2.`c3` ASC, r2.`c1` IS NULL, r2.`c1` ASC
(4 rows)

WITH t (c1_1, c1_3, c2_1) AS (
  SELECT t1.c1, t1.c3, t2.c1
    FROM fdw139_t1 t1 JOIN fdw139_t2 t2 ON (t1.c1 = t2.c1)
) SELECT c1_1, c2_1 FROM t ORDER BY c1_3, c1_1;
 c1_1 | c2_1 
------+------
    1 |    1
    2 |    2
(2 rows)

-- Whole-row reference
EXPLAIN (COSTS false, VERBOSE)
SELECT t1, t2, t1.c1
  FROM fdw139_t1 t1 JOIN fdw139_t2 t2 ON (t1.c1 = t2.c1)
  ORDER BY t1.c3, t1.c1;
                                                                                                                                  QUERY PLAN                                                                                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan
   Output: t1.*, t2.*, t1.c1, t1.c3
   Relations: (mysql_fdw_regress.fdw139_t1 t1) INNER JOIN (mysql_fdw_regress.fdw139_t2 t2)
   Remote query: SELECT r1.`c1`, r1.`c2`, r1.`c3`, r1.`c4`, r2.`c1`, r2.`c2`, r2.`c3`, r2.`c4` FROM (`mysql_fdw_regress`.`test1` r1 INNER JOIN `mysql_fdw_regress`.`test2` r2 ON (((r1.`c1` = r2.`c1`)))) ORDER BY r1.`c3` IS NULL, r1.`c3` ASC, r1.`c1` IS NULL, r1.`c1` ASC
(4 rows)

SELECT t1, t2, t1.c1
  FROM fdw139_t1 t1 JOIN fdw139_t2 t2 ON (t1.c1 = t2.c1)
  ORDER BY t1.c3, t1.c1;
        t1        |        t2        | c1 
------------------+------------------+----
 (1,100,AAA1,foo) | (1,200,BBB1,foo) |  1
 (2,100,AAA2,bar) | (2,200,BBB2,bar) |  2
(2 rows)

-- SEMI JOIN, not pushed down
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1
  FROM fdw139_t1 t1 WHERE EXISTS (SELECT 1 FROM fdw139_t2 t2 WHERE t1.c1 = t2.c1)
  ORDER BY t1.c1;
                                                QUERY PLAN                                                
----------------------------------------------------------------------------------------------------------
 Nested Loop Semi Join
   Output: t1.c1
   Join Filter: (t1.c1 = t2.c1)
   ->  Foreign Scan on public.fdw139_t1 t1
         Output: t1.c1, t1.c2, t1.c3, t1.c4
         Remote query: SELECT `c1` FROM `mysql_fdw_regress`.`test1` ORDER BY `c1` IS NULL, `c1` ASC
   ->  Materialize
         Output: t2.c1
         ->  Foreign Scan on public.fdw139_t2 t2
               Output: t2.c1
               Remote query: SELECT `c1` FROM `mysql_fdw_regress`.`test2` ORDER BY `c1` IS NULL, `c1` ASC
(11 rows)

SELECT t1.c1
  FROM fdw139_t1 t1 WHERE EXISTS (SELECT 1 FROM fdw139_t2 t2 WHERE t1.c1 = t2.c1)
  ORDER BY t1.c1;
 c1 
----
  1
  2
(2 rows)

-- ANTI JOIN, not pushed down
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1
  FROM fdw139_t1 t1 WHERE NOT EXISTS (SELECT 1 FROM fdw139_t2 t2 WHERE t1.c1 = t2.c2)
  ORDER BY t1.c1 LIMIT 2;
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Limit
   Output: t1.c1
   ->  Nested Loop Anti Join
         Output: t1.c1
         Join Filter: (t1.c1 = t2.c2)
         ->  Foreign Scan on public.fdw139_t1 t1
               Output: t1.c1, t1.c2, t1.c3, t1.c4
               Remote query: SELECT `c1` FROM `mysql_fdw_regress`.`test1` ORDER BY `c1` IS NULL, `c1` ASC
         ->  Materialize
               Output: t2.c2
               ->  Foreign Scan on public.fdw139_t2 t2
                     Output: t2.c2
                     Remote query: SELECT `c2` FROM `mysql_fdw_regress`.`test2` ORDER BY `c2` IS NULL, `c2` ASC
(13 rows)

SELECT t1.c1
  FROM fdw139_t1 t1 WHERE NOT EXISTS (SELECT 1 FROM fdw139_t2 t2 WHERE t1.c1 = t2.c2)
  ORDER BY t1.c1 LIMIT 2;
 c1 
----
  1
  2
(2 rows)

-- CROSS JOIN can be pushed down
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 CROSS JOIN fdw139_t2 t2
  ORDER BY t1.c1, t2.c1 LIMIT round(5.4) OFFSET 2;
                                                                                                       QUERY PLAN                                                                                                       
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan
   Output: t1.c1, t2.c1
   Relations: (mysql_fdw_regress.fdw139_t1 t1) INNER JOIN (mysql_fdw_regress.fdw139_t2 t2)
   Remote query: SELECT r1.`c1`, r2.`c1` FROM (`mysql_fdw_regress`.`test1` r1 INNER JOIN `mysql_fdw_regress`.`test2` r2 ON (TRUE)) ORDER BY r1.`c1` IS NULL, r1.`c1` ASC, r2.`c1` IS NULL, r2.`c1` ASC LIMIT 5 OFFSET 2
(4 rows)

SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 CROSS JOIN fdw139_t2 t2
  ORDER BY t1.c1, t2.c1 LIMIT round(5.4) OFFSET 2;
 c1 | c1 
----+----
  1 | 12
  2 |  1
  2 |  2
  2 | 12
 11 |  1
(5 rows)

-- CROSS JOIN combined with local table.
CREATE TABLE local_t1(c1 int);
INSERT INTO local_t1 VALUES (1), (2);
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c1, l1.c1
  FROM fdw139_t1 t1 CROSS JOIN fdw139_t2 t2 CROSS JOIN local_t1 l1
  ORDER BY t1.c1, t2.c1, l1.c1 LIMIT 8 OFFSET round(2.2);
                                                                     QUERY PLAN                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit
   Output: t1.c1, t2.c1, l1.c1
   ->  Sort
         Output: t1.c1, t2.c1, l1.c1
         Sort Key: t1.c1, t2.c1, l1.c1
         ->  Nested Loop
               Output: t1.c1, t2.c1, l1.c1
               ->  Seq Scan on public.local_t1 l1
                     Output: l1.c1
               ->  Foreign Scan
                     Output: t1.c1, t2.c1
                     Relations: (mysql_fdw_regress.fdw139_t1 t1) INNER JOIN (mysql_fdw_regress.fdw139_t2 t2)
                     Remote query: SELECT r1.`c1`, r2.`c1` FROM (`mysql_fdw_regress`.`test1` r1 INNER JOIN `mysql_fdw_regress`.`test2` r2 ON (TRUE))
(13 rows)

SELECT t1.c1, t2.c1, l1.c1
  FROM fdw139_t1 t1 CROSS JOIN fdw139_t2 t2 CROSS JOIN local_t1 l1
  ORDER BY t1.c1, t2.c1, l1.c1 LIMIT 8 OFFSET round(2.2);
 c1 | c1 | c1 
----+----+----
  1 |  2 |  1
  1 |  2 |  2
  1 | 12 |  1
  1 | 12 |  2
  2 |  1 |  1
  2 |  1 |  2
  2 |  2 |  1
  2 |  2 |  2
(8 rows)

SELECT count(t1.c1)
  FROM fdw139_t1 t1 CROSS JOIN fdw139_t2 t2 CROSS JOIN local_t1 l1;
 count 
-------
    18
(1 row)

-- Join two tables from two different foreign table
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 JOIN fdw139_t4 t2 ON (t1.c1 = t2.c1)
  ORDER BY t1.c3, t1.c1;
                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Nested Loop
   Output: t1.c1, t2.c1, t1.c3
   Join Filter: (t1.c1 = t2.c1)
   ->  Foreign Scan on public.fdw139_t1 t1
         Output: t1.c1, t1.c2, t1.c3, t1.c4
         Remote query: SELECT `c1`, `c3` FROM `mysql_fdw_regress`.`test1` ORDER BY `c3` IS NULL, `c3` ASC, `c1` IS NULL, `c1` ASC
   ->  Materialize
         Output: t2.c1
         ->  Foreign Scan on public.fdw139_t4 t2
               Output: t2.c1
               Remote query: SELECT `c1` FROM `mysql_fdw_regress`.`test3` ORDER BY `c1` IS NULL, `c1` ASC
(11 rows)

SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 JOIN fdw139_t4 t2 ON (t1.c1 = t2.c1)
  ORDER BY t1.c3, t1.c1;
 c1 | c1 
----+----
  1 |  1
  2 |  2
(2 rows)

-- Unsafe join conditions (c4 has a UDT), not pushed down.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 LEFT JOIN fdw139_t2 t2 ON (t1.c4 = t2.c4)
  ORDER BY t1.c1, t2.c1;
                                      QUERY PLAN                                      
--------------------------------------------------------------------------------------
 Sort
   Output: t1.c1, t2.c1
   Sort Key: t1.c1, t2.c1
   ->  Nested Loop Left Join
         Output: t1.c1, t2.c1
         Join Filter: (t1.c4 = t2.c4)
         ->  Foreign Scan on public.fdw139_t1 t1
               Output: t1.c1, t1.c2, t1.c3, t1.c4
               Remote query: SELECT `c1`, `c4` FROM `mysql_fdw_regress`.`test1`
         ->  Materialize
               Output: t2.c1, t2.c4
               ->  Foreign Scan on public.fdw139_t2 t2
                     Output: t2.c1, t2.c4
                     Remote query: SELECT `c1`, `c4` FROM `mysql_fdw_regress`.`test2`
(14 rows)

SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 LEFT JOIN fdw139_t2 t2 ON (t1.c4 = t2.c4)
  ORDER BY t1.c1, t2.c1;
 c1 | c1 
----+----
  1 |  1
  1 | 12
  2 |  2
 11 |  1
 11 | 12
(5 rows)

-- Unsafe conditions on one side (c4 has a UDT), not pushed down.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 LEFT JOIN fdw139_t2 t2 ON (t1.c1 = t2.c1) WHERE t1.c4 = 'foo'
  ORDER BY t1.c1, t2.c1 NULLS LAST;
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Incremental Sort
   Output: t1.c1, t2.c1
   Sort Key: t1.c1, t2.c1
   Presorted Key: t1.c1
   ->  Nested Loop Left Join
         Output: t1.c1, t2.c1
         Join Filter: (t1.c1 = t2.c1)
         ->  Foreign Scan on public.fdw139_t1 t1
               Output: t1.c1, t1.c2, t1.c3, t1.c4
               Filter: (t1.c4 = 'foo'::user_enum)
               Remote query: SELECT `c1`, `c4` FROM `mysql_fdw_regress`.`test1` ORDER BY `c1` IS NULL, `c1` ASC
         ->  Materialize
               Output: t2.c1
               ->  Foreign Scan on public.fdw139_t2 t2
                     Output: t2.c1
                     Remote query: SELECT `c1` FROM `mysql_fdw_regress`.`test2` ORDER BY `c1` IS NULL, `c1` ASC
(16 rows)

SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 LEFT JOIN fdw139_t2 t2 ON (t1.c1 = t2.c1) WHERE t1.c4 = 'foo'
  ORDER BY t1.c1, t2.c1 NULLS LAST;
 c1 | c1 
----+----
  1 |  1
 11 |   
(2 rows)

-- Join where unsafe to pushdown condition in WHERE clause has a column not
-- in the SELECT clause.  In this test unsafe clause needs to have column
-- references from both joining sides so that the clause is not pushed down
-- into one of the joining sides.
-- target list order is different for v10.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 JOIN fdw139_t2 t2 ON (t1.c1 = t2.c1) WHERE t1.c4 = t2.c4
  ORDER BY t1.c3, t1.c1;
                                                                                                                    QUERY PLAN                                                                                                                     
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan
   Output: t1.c1, t2.c1, t1.c3
   Filter: (t1.c4 = t2.c4)
   Relations: (mysql_fdw_regress.fdw139_t1 t1) INNER JOIN (mysql_fdw_regress.fdw139_t2 t2)
   Remote query: SELECT r1.`c1`, r2.`c1`, r1.`c3`, r1.`c4`, r2.`c4` FROM (`mysql_fdw_regress`.`test1` r1 INNER JOIN `mysql_fdw_regress`.`test2` r2 ON (((r1.`c1` = r2.`c1`)))) ORDER BY r1.`c3` IS NULL, r1.`c3` ASC, r1.`c1` IS NULL, r1.`c1` ASC
(5 rows)

SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 JOIN fdw139_t2 t2 ON (t1.c1 = t2.c1) WHERE t1.c4 = t2.c4
  ORDER BY t1.c3, t1.c1;
 c1 | c1 
----+----
  1 |  1
  2 |  2
(2 rows)

-- Check join pushdown in situations where multiple userids are involved
CREATE ROLE regress_view_owner SUPERUSER;
CREATE USER MAPPING FOR regress_view_owner
  SERVER mysql_svr OPTIONS (username :MYSQL_USER_NAME, password :MYSQL_PASS);
GRANT SELECT ON fdw139_t1 TO regress_view_owner;
GRANT SELECT ON fdw139_t2 TO regress_view_owner;
CREATE VIEW v1 AS SELECT * FROM fdw139_t1;
CREATE VIEW v2 AS SELECT * FROM fdw139_t2;
ALTER VIEW v2 OWNER TO regress_view_owner;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c2
  FROM v1 t1 LEFT JOIN v2 t2 ON (t1.c1 = t2.c1)
  ORDER BY t1.c1, t2.c1, t2.c2 NULLS LAST;  -- not pushed down, different view owners
                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Incremental Sort
   Output: fdw139_t1.c1, fdw139_t2.c2, fdw139_t2.c1
   Sort Key: fdw139_t1.c1, fdw139_t2.c1, fdw139_t2.c2
   Presorted Key: fdw139_t1.c1
   ->  Nested Loop Left Join
         Output: fdw139_t1.c1, fdw139_t2.c2, fdw139_t2.c1
         Join Filter: (fdw139_t1.c1 = fdw139_t2.c1)
         ->  Foreign Scan on public.fdw139_t1
               Output: fdw139_t1.c1, fdw139_t1.c2, fdw139_t1.c3, fdw139_t1.c4
               Remote query: SELECT `c1` FROM `mysql_fdw_regress`.`test1` ORDER BY `c1` IS NULL, `c1` ASC
         ->  Materialize
               Output: fdw139_t2.c2, fdw139_t2.c1
               ->  Foreign Scan on public.fdw139_t2
                     Output: fdw139_t2.c2, fdw139_t2.c1
                     Remote query: SELECT `c1`, `c2` FROM `mysql_fdw_regress`.`test2` ORDER BY `c1` IS NULL, `c1` ASC
(15 rows)

SELECT t1.c1, t2.c2
  FROM v1 t1 LEFT JOIN v2 t2 ON (t1.c1 = t2.c1)
  ORDER BY t1.c1, t2.c1, t2.c2 NULLS LAST;
 c1 | c2  
----+-----
  1 | 200
  2 | 200
 11 |    
(3 rows)

ALTER VIEW v1 OWNER TO regress_view_owner;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c2
  FROM v1 t1 LEFT JOIN v2 t2 ON (t1.c1 = t2.c1)
  ORDER BY t1.c1, t2.c1, t2.c2 NULLS LAST;  -- pushed down
                                                                                                                          QUERY PLAN                                                                                                                          
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan
   Output: fdw139_t1.c1, fdw139_t2.c2, fdw139_t2.c1
   Relations: (mysql_fdw_regress.fdw139_t1) LEFT JOIN (mysql_fdw_regress.fdw139_t2)
   Remote query: SELECT r4.`c1`, r5.`c2`, r5.`c1` FROM (`mysql_fdw_regress`.`test1` r4 LEFT JOIN `mysql_fdw_regress`.`test2` r5 ON (((r4.`c1` = r5.`c1`)))) ORDER BY r4.`c1` IS NULL, r4.`c1` ASC, r5.`c1` IS NULL, r5.`c1` ASC, r5.`c2` IS NULL, r5.`c2` ASC
(4 rows)

SELECT t1.c1, t2.c2
  FROM v1 t1 LEFT JOIN v2 t2 ON (t1.c1 = t2.c1)
  ORDER BY t1.c1, t2.c1, t2.c2 NULLS LAST;
 c1 | c2  
----+-----
  1 | 200
  2 | 200
 11 |    
(3 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c2
  FROM v1 t1 LEFT JOIN fdw139_t2 t2 ON (t1.c1 = t2.c1)
  ORDER BY t1.c1, t2.c1, t2.c2 NULLS LAST;  -- not pushed down, view owner not current user
                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Incremental Sort
   Output: fdw139_t1.c1, t2.c2, t2.c1
   Sort Key: fdw139_t1.c1, t2.c1, t2.c2
   Presorted Key: fdw139_t1.c1
   ->  Nested Loop Left Join
         Output: fdw139_t1.c1, t2.c2, t2.c1
         Join Filter: (fdw139_t1.c1 = t2.c1)
         ->  Foreign Scan on public.fdw139_t1
               Output: fdw139_t1.c1, fdw139_t1.c2, fdw139_t1.c3, fdw139_t1.c4
               Remote query: SELECT `c1` FROM `mysql_fdw_regress`.`test1` ORDER BY `c1` IS NULL, `c1` ASC
         ->  Materialize
               Output: t2.c2, t2.c1
               ->  Foreign Scan on public.fdw139_t2 t2
                     Output: t2.c2, t2.c1
                     Remote query: SELECT `c1`, `c2` FROM `mysql_fdw_regress`.`test2` ORDER BY `c1` IS NULL, `c1` ASC
(15 rows)

SELECT t1.c1, t2.c2
  FROM v1 t1 LEFT JOIN fdw139_t2 t2 ON (t1.c1 = t2.c1)
  ORDER BY t1.c1, t2.c1, t2.c2 NULLS LAST;
 c1 | c2  
----+-----
  1 | 200
  2 | 200
 11 |    
(3 rows)

ALTER VIEW v1 OWNER TO CURRENT_USER;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t2.c2
  FROM v1 t1 LEFT JOIN fdw139_t2 t2 ON (t1.c1 = t2.c1)
  ORDER BY t1.c1, t2.c1, t2.c2 NULLS LAST;  -- pushed down
                                                                                                                          QUERY PLAN                                                                                                                          
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan
   Output: fdw139_t1.c1, t2.c2, t2.c1
   Relations: (mysql_fdw_regress.fdw139_t1) LEFT JOIN (mysql_fdw_regress.fdw139_t2 t2)
   Remote query: SELECT r4.`c1`, r2.`c2`, r2.`c1` FROM (`mysql_fdw_regress`.`test1` r4 LEFT JOIN `mysql_fdw_regress`.`test2` r2 ON (((r4.`c1` = r2.`c1`)))) ORDER BY r4.`c1` IS NULL, r4.`c1` ASC, r2.`c1` IS NULL, r2.`c1` ASC, r2.`c2` IS NULL, r2.`c2` ASC
(4 rows)

SELECT t1.c1, t2.c2
  FROM v1 t1 LEFT JOIN fdw139_t2 t2 ON (t1.c1 = t2.c1)
  ORDER BY t1.c1, t2.c1, t2.c2 NULLS LAST;
 c1 | c2  
----+-----
  1 | 200
  2 | 200
 11 |    
(3 rows)

ALTER VIEW v1 OWNER TO regress_view_owner;
-- Non-Var items in targetlist of the nullable rel of a join preventing
-- push-down in some cases
-- Unable to push {fdw139_t1, fdw139_t2}
EXPLAIN (VERBOSE, COSTS OFF)
SELECT q.a, fdw139_t2.c1
  FROM (SELECT 13 FROM fdw139_t1 WHERE c1 = 13) q(a) RIGHT JOIN fdw139_t2 ON (q.a = fdw139_t2.c1)
  WHERE fdw139_t2.c1 BETWEEN 10 AND 15;
                                                                 QUERY PLAN                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join
   Output: (13), fdw139_t2.c1
   Join Filter: (13 = fdw139_t2.c1)
   ->  Foreign Scan on public.fdw139_t2
         Output: fdw139_t2.c1, fdw139_t2.c2, fdw139_t2.c3, fdw139_t2.c4
         Remote query: SELECT `c1` FROM `mysql_fdw_regress`.`test2` WHERE ((`c1` >= 10)) AND ((`c1` <= 15)) ORDER BY `c1` IS NULL, `c1` ASC
   ->  Materialize
         Output: (13)
         ->  Foreign Scan on public.fdw139_t1
               Output: 13
               Remote query: SELECT NULL FROM `mysql_fdw_regress`.`test1` WHERE ((`c1` = 13))
(11 rows)

SELECT q.a, fdw139_t2.c1
  FROM (SELECT 13 FROM fdw139_t1 WHERE c1 = 13) q(a) RIGHT JOIN fdw139_t2 ON (q.a = fdw139_t2.c1)
  WHERE fdw139_t2.c1 BETWEEN 10 AND 15;
 a | c1 
---+----
   | 12
(1 row)

-- Ok to push {fdw139_t1, fdw139_t2 but not {fdw139_t1, fdw139_t2, fdw139_t3}
EXPLAIN (VERBOSE, COSTS OFF)
SELECT fdw139_t3.c1, q.*
  FROM fdw139_t3 LEFT JOIN (
    SELECT 13, fdw139_t1.c1, fdw139_t2.c1
    FROM fdw139_t1 RIGHT JOIN fdw139_t2 ON (fdw139_t1.c1 = fdw139_t2.c1)
    WHERE fdw139_t1.c1 = 11
  ) q(a, b, c) ON (fdw139_t3.c1 = q.b)
  WHERE fdw139_t3.c1 BETWEEN 10 AND 15;
                                                                                                        QUERY PLAN                                                                                                         
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join
   Output: fdw139_t3.c1, (13), fdw139_t1.c1, fdw139_t2.c1
   Join Filter: (fdw139_t3.c1 = fdw139_t1.c1)
   ->  Foreign Scan on public.fdw139_t3
         Output: fdw139_t3.c1, fdw139_t3.c2, fdw139_t3.c3
         Remote query: SELECT `c1` FROM `mysql_fdw_regress`.`test3` WHERE ((`c1` >= 10)) AND ((`c1` <= 15)) ORDER BY `c1` IS NULL, `c1` ASC
   ->  Foreign Scan
         Output: fdw139_t1.c1, fdw139_t2.c1, 13
         Relations: (mysql_fdw_regress.fdw139_t1) INNER JOIN (mysql_fdw_regress.fdw139_t2)
         Remote query: SELECT r4.`c1`, r5.`c1` FROM (`mysql_fdw_regress`.`test1` r4 INNER JOIN `mysql_fdw_regress`.`test2` r5 ON (TRUE)) WHERE ((r5.`c1` = 11)) AND ((r4.`c1` = 11)) ORDER BY r4.`c1` IS NULL, r4.`c1` ASC
(10 rows)

SELECT fdw139_t3.c1, q.*
  FROM fdw139_t3 LEFT JOIN (
    SELECT 13, fdw139_t1.c1, fdw139_t2.c1
    FROM fdw139_t1 RIGHT JOIN fdw139_t2 ON (fdw139_t1.c1 = fdw139_t2.c1)
    WHERE fdw139_t1.c1 = 11
  ) q(a, b, c) ON (fdw139_t3.c1 = q.b)
  WHERE fdw139_t3.c1 BETWEEN 10 AND 15;
 c1 | a | b | c 
----+---+---+---
 13 |   |   |  
(1 row)

-- FDW-129: Limit and offset pushdown with join pushdown.
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 JOIN fdw139_t2 t2 ON (TRUE)
  ORDER BY t1.c1, t2.c1 LIMIT round(2.2) OFFSET 2;
                                                                                                       QUERY PLAN                                                                                                       
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan
   Output: t1.c1, t2.c1
   Relations: (mysql_fdw_regress.fdw139_t1 t1) INNER JOIN (mysql_fdw_regress.fdw139_t2 t2)
   Remote query: SELECT r1.`c1`, r2.`c1` FROM (`mysql_fdw_regress`.`test1` r1 INNER JOIN `mysql_fdw_regress`.`test2` r2 ON (TRUE)) ORDER BY r1.`c1` IS NULL, r1.`c1` ASC, r2.`c1` IS NULL, r2.`c1` ASC LIMIT 2 OFFSET 2
(4 rows)

SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 JOIN fdw139_t2 t2 ON (TRUE)
  ORDER BY t1.c1, t2.c1 LIMIT round(2.2) OFFSET 2;
 c1 | c1 
----+----
  1 | 12
  2 |  1
(2 rows)

-- Limit as NULL, no LIMIT/OFFSET pushdown.
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 JOIN fdw139_t2 t2 ON (TRUE)
  ORDER BY t1.c1, t2.c1 LIMIT NULL OFFSET 1;
                                                                                                 QUERY PLAN                                                                                                  
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit
   Output: t1.c1, t2.c1
   ->  Foreign Scan
         Output: t1.c1, t2.c1
         Relations: (mysql_fdw_regress.fdw139_t1 t1) INNER JOIN (mysql_fdw_regress.fdw139_t2 t2)
         Remote query: SELECT r1.`c1`, r2.`c1` FROM (`mysql_fdw_regress`.`test1` r1 INNER JOIN `mysql_fdw_regress`.`test2` r2 ON (TRUE)) ORDER BY r1.`c1` IS NULL, r1.`c1` ASC, r2.`c1` IS NULL, r2.`c1` ASC
(6 rows)

SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 JOIN fdw139_t2 t2 ON (TRUE)
  ORDER BY t1.c1, t2.c1 LIMIT NULL OFFSET 1;
 c1 | c1 
----+----
  1 |  2
  1 | 12
  2 |  1
  2 |  2
  2 | 12
 11 |  1
 11 |  2
 11 | 12
(8 rows)

-- Limit as ALL, no LIMIT/OFFSET pushdown.
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 JOIN fdw139_t2 t2 ON (TRUE)
  ORDER BY t1.c1, t2.c1 LIMIT ALL OFFSET 1;
                                                                                                 QUERY PLAN                                                                                                  
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit
   Output: t1.c1, t2.c1
   ->  Foreign Scan
         Output: t1.c1, t2.c1
         Relations: (mysql_fdw_regress.fdw139_t1 t1) INNER JOIN (mysql_fdw_regress.fdw139_t2 t2)
         Remote query: SELECT r1.`c1`, r2.`c1` FROM (`mysql_fdw_regress`.`test1` r1 INNER JOIN `mysql_fdw_regress`.`test2` r2 ON (TRUE)) ORDER BY r1.`c1` IS NULL, r1.`c1` ASC, r2.`c1` IS NULL, r2.`c1` ASC
(6 rows)

SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 JOIN fdw139_t2 t2 ON (TRUE)
  ORDER BY t1.c1, t2.c1 LIMIT ALL OFFSET 1;
 c1 | c1 
----+----
  1 |  2
  1 | 12
  2 |  1
  2 |  2
  2 | 12
 11 |  1
 11 |  2
 11 | 12
(8 rows)

-- Offset as NULL, no LIMIT/OFFSET pushdown.
EXPLAIN (COSTS false, VERBOSE)
SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 JOIN fdw139_t2 t2 ON (TRUE)
  ORDER BY t1.c1, t2.c1 LIMIT 3 OFFSET NULL;
                                                                                                  QUERY PLAN                                                                                                   
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan
   Output: t1.c1, t2.c1
   Relations: (mysql_fdw_regress.fdw139_t1 t1) INNER JOIN (mysql_fdw_regress.fdw139_t2 t2)
   Remote query: SELECT r1.`c1`, r2.`c1` FROM (`mysql_fdw_regress`.`test1` r1 INNER JOIN `mysql_fdw_regress`.`test2` r2 ON (TRUE)) ORDER BY r1.`c1` IS NULL, r1.`c1` ASC, r2.`c1` IS NULL, r2.`c1` ASC LIMIT 3
(4 rows)

SELECT t1.c1, t2.c1
  FROM fdw139_t1 t1 JOIN fdw139_t2 t2 ON (TRUE)
  ORDER BY t1.c1, t2.c1 LIMIT 3 OFFSET NULL;
 c1 | c1 
----+----
  1 |  1
  1 |  2
  1 | 12
(3 rows)

-- Delete existing data and load new data for partition-wise join test cases.
DROP OWNED BY regress_view_owner;
DROP ROLE regress_view_owner;
DELETE FROM fdw139_t1;
DELETE FROM fdw139_t2;
DELETE FROM fdw139_t3;
INSERT INTO fdw139_t1 values(1, 1, 'AAA1', 'foo');
INSERT INTO fdw139_t1 values(2, 2, 'AAA2', 'bar');
INSERT INTO fdw139_t1 values(3, 3, 'AAA11', 'foo');
INSERT INTO fdw139_t1 values(4, 4, 'AAA12', 'foo');
INSERT INTO fdw139_t2 values(5, 5, 'BBB1', 'foo');
INSERT INTO fdw139_t2 values(6, 6, 'BBB2', 'bar');
INSERT INTO fdw139_t2 values(7, 7, 'BBB11', 'foo');
INSERT INTO fdw139_t2 values(8, 8, 'BBB12', 'foo');
INSERT INTO fdw139_t3 values(1, 1, 'CCC1');
INSERT INTO fdw139_t3 values(2, 2, 'CCC2');
INSERT INTO fdw139_t3 values(3, 3, 'CCC13');
INSERT INTO fdw139_t3 values(4, 4, 'CCC14');
DROP FOREIGN TABLE fdw139_t4;
CREATE FOREIGN TABLE tmp_t4(c1 int, c2 int, c3 text)
  SERVER mysql_svr1 OPTIONS(dbname 'mysql_fdw_regress', table_name 'test4');
INSERT INTO tmp_t4 values(5, 5, 'CCC1');
INSERT INTO tmp_t4 values(6, 6, 'CCC2');
INSERT INTO tmp_t4 values(7, 7, 'CCC13');
INSERT INTO tmp_t4 values(8, 8, 'CCC13');
-- Test partition-wise join
SET enable_partitionwise_join TO on;
-- Create the partition table.
CREATE TABLE fprt1 (c1 int, c2 int, c3 varchar, c4 varchar) PARTITION BY RANGE(c1);
CREATE FOREIGN TABLE ftprt1_p1 PARTITION OF fprt1 FOR VALUES FROM (1) TO (4)
  SERVER mysql_svr OPTIONS (dbname 'mysql_fdw_regress', table_name 'test1');
CREATE FOREIGN TABLE ftprt1_p2 PARTITION OF fprt1 FOR VALUES FROM (5) TO (8)
  SERVER mysql_svr OPTIONS (dbname 'mysql_fdw_regress', TABLE_NAME 'test2');
CREATE TABLE fprt2 (c1 int, c2 int, c3 varchar) PARTITION BY RANGE(c2);
CREATE FOREIGN TABLE ftprt2_p1 PARTITION OF fprt2 FOR VALUES FROM (1) TO (4)
  SERVER mysql_svr OPTIONS (dbname 'mysql_fdw_regress', table_name 'test3');
CREATE FOREIGN TABLE ftprt2_p2 PARTITION OF fprt2 FOR VALUES FROM (5) TO (8)
  SERVER mysql_svr OPTIONS (dbname 'mysql_fdw_regress', TABLE_NAME 'test4');
-- Inner join three tables
-- Different explain plan on v10 as partition-wise join is not supported there.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1,t2.c2,t3.c3
  FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.c1 = t2.c2) INNER JOIN fprt1 t3 ON (t2.c2 = t3.c1)
  WHERE t1.c1 % 2 =0 ORDER BY 1,2,3;
                                                                                                                                                                  QUERY PLAN                                                                                                                                                                   
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Append
   Sort Key: t1.c1, t3.c3
   ->  Foreign Scan
         Output: t1_1.c1, t2_1.c2, t3_1.c3
         Relations: ((mysql_fdw_regress.ftprt1_p1 t1) INNER JOIN (mysql_fdw_regress.ftprt2_p1 t2)) INNER JOIN (mysql_fdw_regress.ftprt1_p1 t3)
         Remote query: SELECT r6.`c1`, r8.`c2`, r10.`c3` FROM ((`mysql_fdw_regress`.`test1` r6 INNER JOIN `mysql_fdw_regress`.`test3` r8 ON (((r6.`c1` = r8.`c2`)))) INNER JOIN `mysql_fdw_regress`.`test1` r10 ON (((r6.`c1` = r10.`c1`)))) WHERE (((r6.`c1` % 2) = 0)) ORDER BY r6.`c1` IS NULL, r6.`c1` ASC, r10.`c3` IS NULL, r10.`c3` ASC
   ->  Foreign Scan
         Output: t1_2.c1, t2_2.c2, t3_2.c3
         Relations: ((mysql_fdw_regress.ftprt1_p2 t1) INNER JOIN (mysql_fdw_regress.ftprt2_p2 t2)) INNER JOIN (mysql_fdw_regress.ftprt1_p2 t3)
         Remote query: SELECT r7.`c1`, r9.`c2`, r11.`c3` FROM ((`mysql_fdw_regress`.`test2` r7 INNER JOIN `mysql_fdw_regress`.`test4` r9 ON (((r7.`c1` = r9.`c2`)))) INNER JOIN `mysql_fdw_regress`.`test2` r11 ON (((r7.`c1` = r11.`c1`)))) WHERE (((r7.`c1` % 2) = 0)) ORDER BY r7.`c1` IS NULL, r7.`c1` ASC, r11.`c3` IS NULL, r11.`c3` ASC
(10 rows)

SELECT t1.c1,t2.c2,t3.c3
  FROM fprt1 t1 INNER JOIN fprt2 t2 ON (t1.c1 = t2.c2) INNER JOIN fprt1 t3 ON (t2.c2 = t3.c1)
  WHERE t1.c1 % 2 =0 ORDER BY 1,2,3;
 c1 | c2 |  c3   
----+----+-------
  2 |  2 | AAA2
  4 |  4 | AAA12
  6 |  6 | BBB2
  8 |  8 | BBB12
(4 rows)

-- With whole-row reference; partitionwise join does not apply
EXPLAIN (VERBOSE, COSTS false)
SELECT t1, t2, t1.c1
  FROM fprt1 t1 JOIN fprt2 t2 ON (t1.c1 = t2.c2)
  ORDER BY t1.c3, t1.c1;
                                                                     QUERY PLAN                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop
   Output: ((t1.*)::fprt1), ((t2.*)::fprt2), t1.c1, t1.c3
   Join Filter: (t1.c1 = t2.c2)
   ->  Merge Append
         Sort Key: t1.c3, t1.c1
         ->  Foreign Scan on public.ftprt1_p1 t1_1
               Output: t1_1.*, t1_1.c1, t1_1.c3
               Remote query: SELECT `c1`, `c2`, `c3`, `c4` FROM `mysql_fdw_regress`.`test1` ORDER BY `c3` IS NULL, `c3` ASC, `c1` IS NULL, `c1` ASC
         ->  Foreign Scan on public.ftprt1_p2 t1_2
               Output: t1_2.*, t1_2.c1, t1_2.c3
               Remote query: SELECT `c1`, `c2`, `c3`, `c4` FROM `mysql_fdw_regress`.`test2` ORDER BY `c3` IS NULL, `c3` ASC, `c1` IS NULL, `c1` ASC
   ->  Materialize
         Output: ((t2.*)::fprt2), t2.c2
         ->  Append
               ->  Foreign Scan on public.ftprt2_p1 t2_1
                     Output: t2_1.*, t2_1.c2
                     Remote query: SELECT `c1`, `c2`, `c3` FROM `mysql_fdw_regress`.`test3` ORDER BY `c2` IS NULL, `c2` ASC
               ->  Foreign Scan on public.ftprt2_p2 t2_2
                     Output: t2_2.*, t2_2.c2
                     Remote query: SELECT `c1`, `c2`, `c3` FROM `mysql_fdw_regress`.`test4` ORDER BY `c2` IS NULL, `c2` ASC
(20 rows)

SELECT t1, t2, t1.c1
  FROM fprt1 t1 JOIN fprt2 t2 ON (t1.c1 = t2.c2)
  ORDER BY t1.c3, t1.c1;
       t1        |     t2      | c1 
-----------------+-------------+----
 (1,1,AAA1,foo)  | (1,1,CCC1)  |  1
 (3,3,AAA11,foo) | (3,3,CCC13) |  3
 (4,4,AAA12,foo) | (4,4,CCC14) |  4
 (2,2,AAA2,bar)  | (2,2,CCC2)  |  2
 (5,5,BBB1,foo)  | (5,5,CCC1)  |  5
 (7,7,BBB11,foo) | (7,7,CCC13) |  7
 (8,8,BBB12,foo) | (8,8,CCC13) |  8
 (6,6,BBB2,bar)  | (6,6,CCC2)  |  6
(8 rows)

-- Join with lateral reference
-- Different explain plan on v10 as partition-wise join is not supported there.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1,t1.c2
  FROM fprt1 t1, LATERAL (SELECT t2.c1, t2.c2 FROM fprt2 t2
  WHERE t1.c1 = t2.c2 AND t1.c2 = t2.c1) q WHERE t1.c1 % 2 = 0 ORDER BY 1,2;
                                                                                                                                     QUERY PLAN                                                                                                                                     
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Append
   Sort Key: t1.c1, t1.c2
   ->  Foreign Scan
         Output: t1_1.c1, t1_1.c2
         Relations: (mysql_fdw_regress.ftprt1_p1 t1) INNER JOIN (mysql_fdw_regress.ftprt2_p1 t2)
         Remote query: SELECT r4.`c1`, r4.`c2` FROM (`mysql_fdw_regress`.`test1` r4 INNER JOIN `mysql_fdw_regress`.`test3` r6 ON (((r4.`c1` = r6.`c2`)) AND ((r4.`c2` = r6.`c1`)))) WHERE (((r4.`c1` % 2) = 0)) ORDER BY r4.`c1` IS NULL, r4.`c1` ASC, r4.`c2` IS NULL, r4.`c2` ASC
   ->  Foreign Scan
         Output: t1_2.c1, t1_2.c2
         Relations: (mysql_fdw_regress.ftprt1_p2 t1) INNER JOIN (mysql_fdw_regress.ftprt2_p2 t2)
         Remote query: SELECT r5.`c1`, r5.`c2` FROM (`mysql_fdw_regress`.`test2` r5 INNER JOIN `mysql_fdw_regress`.`test4` r7 ON (((r5.`c1` = r7.`c2`)) AND ((r5.`c2` = r7.`c1`)))) WHERE (((r5.`c1` % 2) = 0)) ORDER BY r5.`c1` IS NULL, r5.`c1` ASC, r5.`c2` IS NULL, r5.`c2` ASC
(10 rows)

SELECT t1.c1,t1.c2
  FROM fprt1 t1, LATERAL (SELECT t2.c1, t2.c2 FROM fprt2 t2
  WHERE t1.c1 = t2.c2 AND t1.c2 = t2.c1) q WHERE t1.c1 % 2 = 0 ORDER BY 1,2;
 c1 | c2 
----+----
  2 |  2
  4 |  4
  6 |  6
  8 |  8
(4 rows)

-- With PHVs, partitionwise join selected but no join pushdown
EXPLAIN (VERBOSE, COSTS OFF)
SELECT t1.c1, t1.phv, t2.c2, t2.phv
  FROM (SELECT 't1_phv' phv, * FROM fprt1 WHERE c1 % 2 = 0) t1 LEFT JOIN
    (SELECT 't2_phv' phv, * FROM fprt2 WHERE c2 % 2 = 0) t2 ON (t1.c1 = t2.c2)
  ORDER BY t1.c1, t2.c2;
                                                                  QUERY PLAN                                                                   
-----------------------------------------------------------------------------------------------------------------------------------------------
 Incremental Sort
   Output: fprt1.c1, 't1_phv'::text, fprt2.c2, ('t2_phv'::text)
   Sort Key: fprt1.c1, fprt2.c2
   Presorted Key: fprt1.c1
   ->  Merge Append
         Sort Key: fprt1.c1
         ->  Nested Loop Left Join
               Output: fprt1_1.c1, 't1_phv'::text, fprt2_1.c2, ('t2_phv'::text)
               Join Filter: (fprt1_1.c1 = fprt2_1.c2)
               ->  Foreign Scan on public.ftprt1_p1 fprt1_1
                     Output: fprt1_1.c1
                     Remote query: SELECT `c1` FROM `mysql_fdw_regress`.`test1` WHERE (((`c1` % 2) = 0)) ORDER BY `c1` IS NULL, `c1` ASC
               ->  Materialize
                     Output: fprt2_1.c2, ('t2_phv'::text)
                     ->  Foreign Scan on public.ftprt2_p1 fprt2_1
                           Output: fprt2_1.c2, 't2_phv'::text
                           Remote query: SELECT `c2` FROM `mysql_fdw_regress`.`test3` WHERE (((`c2` % 2) = 0)) ORDER BY `c2` IS NULL, `c2` ASC
         ->  Nested Loop Left Join
               Output: fprt1_2.c1, 't1_phv'::text, fprt2_2.c2, ('t2_phv'::text)
               Join Filter: (fprt1_2.c1 = fprt2_2.c2)
               ->  Foreign Scan on public.ftprt1_p2 fprt1_2
                     Output: fprt1_2.c1
                     Remote query: SELECT `c1` FROM `mysql_fdw_regress`.`test2` WHERE (((`c1` % 2) = 0)) ORDER BY `c1` IS NULL, `c1` ASC
               ->  Materialize
                     Output: fprt2_2.c2, ('t2_phv'::text)
                     ->  Foreign Scan on public.ftprt2_p2 fprt2_2
                           Output: fprt2_2.c2, 't2_phv'::text
                           Remote query: SELECT `c2` FROM `mysql_fdw_regress`.`test4` WHERE (((`c2` % 2) = 0)) ORDER BY `c2` IS NULL, `c2` ASC
(28 rows)

SELECT t1.c1, t1.phv, t2.c2, t2.phv
  FROM (SELECT 't1_phv' phv, * FROM fprt1 WHERE c1 % 2 = 0) t1 LEFT JOIN
    (SELECT 't2_phv' phv, * FROM fprt2 WHERE c2 % 2 = 0) t2 ON (t1.c1 = t2.c2)
  ORDER BY t1.c1, t2.c2;
 c1 |  phv   | c2 |  phv   
----+--------+----+--------
  2 | t1_phv |  2 | t2_phv
  4 | t1_phv |  4 | t2_phv
  6 | t1_phv |  6 | t2_phv
  8 | t1_phv |  8 | t2_phv
(4 rows)

SET enable_partitionwise_join TO off;
-- FDW-679: Whole-row reference in left join should work correctly.
EXPLAIN (COSTS false, VERBOSE)
SELECT t3 FROM fdw139_t1 t1
    LEFT JOIN fdw139_t1 t2 ON (t1.c1 = t2.c1)
    LEFT JOIN fdw139_t1 t3 ON (t3.c1 = t2.c1)
  ORDER BY 1;
                                                                                                                   QUERY PLAN                                                                                                                    
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort
   Output: t3.*
   Sort Key: t3.*
   ->  Foreign Scan
         Output: t3.*
         Relations: ((mysql_fdw_regress.fdw139_t1 t1) LEFT JOIN (mysql_fdw_regress.fdw139_t1 t2)) LEFT JOIN (mysql_fdw_regress.fdw139_t1 t3)
         Remote query: SELECT r4.`c1`, r4.`c2`, r4.`c3`, r4.`c4` FROM ((`mysql_fdw_regress`.`test1` r1 LEFT JOIN `mysql_fdw_regress`.`test1` r2 ON (((r1.`c1` = r2.`c1`)))) LEFT JOIN `mysql_fdw_regress`.`test1` r4 ON (((r4.`c1` = r2.`c1`))))
(7 rows)

SELECT t3 FROM fdw139_t1 t1
    LEFT JOIN fdw139_t1 t2 ON (t1.c1 = t2.c1)
    LEFT JOIN fdw139_t1 t3 ON (t3.c1 = t2.c1)
  ORDER BY 1;
       t3        
-----------------
 (1,1,AAA1,foo)
 (2,2,AAA2,bar)
 (3,3,AAA11,foo)
 (4,4,AAA12,foo)
(4 rows)

-- Cleanup
DELETE FROM fdw139_t1;
DELETE FROM fdw139_t2;
DELETE FROM fdw139_t3;
DELETE FROM tmp_t4;
DROP FOREIGN TABLE fdw139_t1;
DROP FOREIGN TABLE fdw139_t2;
DROP FOREIGN TABLE fdw139_t3;
DROP FOREIGN TABLE tmp_t4;
DROP TABLE IF EXISTS fprt1;
DROP TABLE IF EXISTS fprt2;
DROP TYPE user_enum;
DROP USER MAPPING FOR public SERVER mysql_svr;
DROP USER MAPPING FOR public SERVER mysql_svr1;
DROP SERVER mysql_svr;
DROP SERVER mysql_svr1;
DROP EXTENSION mysql_fdw;