SET client_min_messages=NOTICE;
\i test-common/disable_jit.include
DO $$
BEGIN
  IF current_setting('server_version_num')::bigint >= 110000 THEN
    SET jit = off;
  END IF;
END;
$$ LANGUAGE plpgsql;
CREATE EXTENSION multicorn;
CREATE server multicorn_srv foreign data wrapper multicorn options (
    wrapper 'multicorn.testfdw.TestForeignDataWrapper'
);
CREATE user mapping FOR current_user server multicorn_srv options (usermapping 'test');
-- Test for two thing: first, that when a low total row count, 
-- a full seq scan is used on a join.
CREATE foreign table testmulticorn (
    test1 character varying,
    test2 character varying
) server multicorn_srv options (
    option1 'option1'
);
explain select * from testmulticorn;
NOTICE:  [('option1', 'option1'), ('usermapping', 'test')]
NOTICE:  [('test1', 'character varying'), ('test2', 'character varying')]
                              QUERY PLAN                              
----------------------------------------------------------------------
 Foreign Scan on testmulticorn  (cost=10.00..400.00 rows=20 width=20)
(1 row)

explain select * from testmulticorn m1 inner join testmulticorn m2 on m1.test1 = m2.test1;
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Nested Loop  (cost=20.00..806.05 rows=2 width=128)
   Join Filter: ((m1.test1)::text = (m2.test1)::text)
   ->  Foreign Scan on testmulticorn m1  (cost=10.00..400.00 rows=20 width=20)
   ->  Materialize  (cost=10.00..400.10 rows=20 width=20)
         ->  Foreign Scan on testmulticorn m2  (cost=10.00..400.00 rows=20 width=20)
(5 rows)

explain select * from testmulticorn m1 left outer join testmulticorn m2 on m1.test1 = m2.test1;
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=20.00..806.05 rows=20 width=128)
   Join Filter: ((m1.test1)::text = (m2.test1)::text)
   ->  Foreign Scan on testmulticorn m1  (cost=10.00..400.00 rows=20 width=20)
   ->  Materialize  (cost=10.00..400.10 rows=20 width=20)
         ->  Foreign Scan on testmulticorn m2  (cost=10.00..400.00 rows=20 width=20)
(5 rows)

DROP foreign table testmulticorn;
-- Second, when a total row count is high 
-- a parameterized path is used on the test1 attribute.
CREATE foreign table testmulticorn (
    test1 character varying,
    test2 character varying
) server multicorn_srv options (
    option1 'option1',
    test_type 'planner'
);
explain select * from testmulticorn;
NOTICE:  [('option1', 'option1'), ('test_type', 'planner'), ('usermapping', 'test')]
NOTICE:  [('test1', 'character varying'), ('test2', 'character varying')]
                                    QUERY PLAN                                    
----------------------------------------------------------------------------------
 Foreign Scan on testmulticorn  (cost=10.00..200000000.00 rows=10000000 width=20)
(1 row)

explain select * from testmulticorn m1 inner join testmulticorn m2 on m1.test1 = m2.test1;
                                        QUERY PLAN                                         
-------------------------------------------------------------------------------------------
 Nested Loop  (cost=20.00..400100000.00 rows=500000000000 width=128)
   ->  Foreign Scan on testmulticorn m1  (cost=10.00..200000000.00 rows=10000000 width=20)
   ->  Foreign Scan on testmulticorn m2  (cost=10.00..20.00 rows=1 width=20)
         Filter: ((m1.test1)::text = (test1)::text)
(4 rows)

explain select * from testmulticorn m1 left outer join testmulticorn m2 on m1.test1 = m2.test1;
                                        QUERY PLAN                                         
-------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=20.00..400100000.00 rows=500000000000 width=128)
   ->  Foreign Scan on testmulticorn m1  (cost=10.00..200000000.00 rows=10000000 width=20)
   ->  Foreign Scan on testmulticorn m2  (cost=10.00..20.00 rows=1 width=20)
         Filter: ((m1.test1)::text = (test1)::text)
(4 rows)

DROP USER MAPPING FOR current_user SERVER multicorn_srv;
DROP EXTENSION multicorn cascade;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to server multicorn_srv
drop cascades to foreign table testmulticorn