text: 
  -- test numeric hash join
  
  set enable_hashjoin to on;
  set enable_mergejoin to off;
  set enable_nestloop to off;
  create table nhtest (i numeric(10, 2)) distributed by (i);
  insert into nhtest values(100000.22);
  insert into nhtest values(300000.19);
  explain select * from nhtest a join nhtest b using (i);
  select * from nhtest a join nhtest b using (i);
  
  create temp table l(a int);
  insert into l values (1), (1), (2);
  select * from l l1 join l l2 on l1.a = l2.a left join l l3 on l1.a = l3.a and l1.a = 2 order by 1,2,3;
  
  --
  -- test hash join
  --
  
  create table hjtest (i int, j int) distributed by (i,j);
  insert into hjtest values(3, 4);
  
  select count(*) from hjtest a1, hjtest a2 where a2.i = least (a1.i,4) and a2.j = 4;
  
  --
  -- Test for correct behavior when there is a Merge Join on top of Materialize
  -- on top of a Motion :
  -- 1. Use FULL OUTER JOIN to induce a Merge Join
  -- 2. Use a large tuple size to induce a Materialize
  -- 3. Use gp_dist_random() to induce a Redistribute
  --
  
  set enable_hashjoin to off;
  set enable_mergejoin to on;
  set enable_nestloop to off;
  
  DROP TABLE IF EXISTS alpha;
  DROP TABLE IF EXISTS theta;
  
  CREATE TABLE alpha (i int, j int) distributed by (i);
  CREATE TABLE theta (i int, j char(10000000)) distributed by (i);
  
  INSERT INTO alpha values (1, 1), (2, 2);
  INSERT INTO theta values (1, 'f'), (2, 'g');
  
  SELECT *
  FROM gp_dist_random('alpha') FULL OUTER JOIN gp_dist_random('theta')
    ON (alpha.i = theta.i)
  WHERE (alpha.j IS NULL or theta.j IS NULL);
  
  reset enable_hashjoin;
  reset enable_mergejoin;
  reset enable_nestloop;
  
  --
  -- Predicate propagation over equality conditions
  --
  
  drop schema if exists pred;
  create schema pred;
  set search_path=pred;
  
  create table t1 (x int, y int, z int) distributed by (y);
  create table t2 (x int, y int, z int) distributed by (x);
  insert into t1 select i, i, i from generate_series(1,100) i;
  insert into t2 select * from t1;
  
  analyze t1;
  analyze t2;
  
  --
  -- infer over equalities
  --
  explain select count(*) from t1,t2 where t1.x = 100 and t1.x = t2.x;
  select count(*) from t1,t2 where t1.x = 100 and t1.x = t2.x;
  
  --
  -- infer over >=
  --
  explain select * from t1,t2 where t1.x = 100 and t2.x >= t1.x;
  select * from t1,t2 where t1.x = 100 and t2.x >= t1.x;
  
  --
  -- multiple inferences
  --
  
  set optimizer_segments=2;
  explain select * from t1,t2 where t1.x = 100 and t1.x = t2.y and t1.x <= t2.x;
  reset optimizer_segments;
  select * from t1,t2 where t1.x = 100 and t1.x = t2.y and t1.x <= t2.x;
  
  --
  -- MPP-18537: hash clause references a constant in outer child target list
  --
  
  create table hjn_test (i int, j int) distributed by (i,j);
  insert into hjn_test values(3, 4);
  create table int4_tbl (f1 int) distributed by (f1);
  insert into int4_tbl values(123456), (-2147483647), (0), (-123456), (2147483647);
  select count(*) from hjn_test, (select 3 as bar) foo where hjn_test.i = least (foo.bar,4) and hjn_test.j = 4;
  select count(*) from hjn_test, (select 3 as bar) foo where hjn_test.i = least (foo.bar,(array[4])[1]) and hjn_test.j = (array[4])[1];
  select count(*) from hjn_test, (select 3 as bar) foo where least (foo.bar,(array[4])[1]) = hjn_test.i and hjn_test.j = (array[4])[1];
  select count(*) from hjn_test, (select 3 as bar) foo where hjn_test.i = least (foo.bar, least(4,10)) and hjn_test.j = least(4,10);
  select * from int4_tbl a join int4_tbl b on (a.f1 = (select f1 from int4_tbl c where c.f1=b.f1));
  
  -- Same as the last query, but with a partitioned table (which requires a
  -- Result node to do projection of the hash expression, as Append is not
  -- projection-capable)
  create table part4_tbl (f1 int4) partition by range (f1) (start(-1000000) end (1000000) every (1000000));
  insert into part4_tbl values
         (-123457), (-123456), (-123455),
         (-1), (0), (1),
         (123455), (123456), (123457);
  select * from part4_tbl a join part4_tbl b on (a.f1 = (select f1 from int4_tbl c where c.f1=b.f1));
  
  --
  -- Test case where a Motion hash key is only needed for the redistribution,
  -- and not returned in the final result set. There was a bug at one point where
  -- tjoin.c1 was used as the hash key in a Motion node, but it was not added
  -- to the sub-plans target list, causing a "variable not found in subplan
  -- target list" error.
  --
  create table tjoin1(dk integer, id integer) distributed by (dk);
  create table tjoin2(dk integer, id integer, t text) distributed by (dk);
  create table tjoin3(dk integer, id integer, t text) distributed by (dk);
  
  insert into tjoin1 values (1, 1), (1, 2), (1, 3), (2, 1), (2, 2), (2, 3);
  insert into tjoin2 values (1, 1, '1-1'), (1, 2, '1-2'), (2, 1, '2-1'), (2, 2, '2-2');
  insert into tjoin3 values (1, 1, '1-1'), (2, 1, '2-1');
  
  select tjoin1.id, tjoin2.t, tjoin3.t
  from tjoin1
  left outer join (tjoin2 left outer join tjoin3 on tjoin2.id=tjoin3.id) on tjoin1.id=tjoin3.id;
  
  
  set enable_hashjoin to off;
  set optimizer_enable_hashjoin = off;
  
  select count(*) from hjn_test, (select 3 as bar) foo where hjn_test.i = least (foo.bar,4) and hjn_test.j = 4;
  select count(*) from hjn_test, (select 3 as bar) foo where hjn_test.i = least (foo.bar,(array[4])[1]) and hjn_test.j = (array[4])[1];
  select count(*) from hjn_test, (select 3 as bar) foo where least (foo.bar,(array[4])[1]) = hjn_test.i and hjn_test.j = (array[4])[1];
  select count(*) from hjn_test, (select 3 as bar) foo where hjn_test.i = least (foo.bar, least(4,10)) and hjn_test.j = least(4,10);
  select * from int4_tbl a join int4_tbl b on (a.f1 = (select f1 from int4_tbl c where c.f1=b.f1));
  
  reset enable_hashjoin;
  reset optimizer_enable_hashjoin;
  
  -- In case of Left Anti Semi Join, if the left rel is empty a dummy join
  -- should be created
  drop table if exists foo;
  drop table if exists bar;
  create table foo (a int, b int) distributed randomly;
  create table bar (c int, d int) distributed randomly;
  insert into foo select generate_series(1,10);
  insert into bar select generate_series(1,10);
  
  explain select a from foo where a<1 and a>1 and not exists (select c from bar where c=a);
  select a from foo where a<1 and a>1 and not exists (select c from bar where c=a);
  
  -- The merge join executor code doesn't support LASJ_NOTIN joins. Make sure
  -- the planner doesn't create an invalid plan with them.
  create index index_foo on foo (a);
  create index index_bar on bar (c);
  
  set enable_nestloop to off;
  set enable_hashjoin to off;
  set enable_mergejoin to on;
  
  select * from foo where a not in (select c from bar where c <= 5);
  
  set enable_nestloop to off;
  set enable_hashjoin to on;
  set enable_mergejoin to off;
  
  create table dept
  (
  	id int,
  	pid int,
  	name char(40)
  );
  
  insert into dept values(3, 0, 'root');
  insert into dept values(4, 3, '2<-1');
  insert into dept values(5, 4, '3<-2<-1');
  insert into dept values(6, 4, '4<-2<-1');
  insert into dept values(7, 3, '5<-1');
  insert into dept values(8, 7, '5<-1');
  insert into dept select i, i % 6 + 3 from generate_series(9,50) as i;
  insert into dept select i, 99 from generate_series(100,15000) as i;
  
  ANALYZE dept;
  
  -- Test rescannable hashjoin with spilling hashtable for buffile
  set statement_mem='1000kB';
  set gp_workfile_type_hashjoin=buffile;
  WITH RECURSIVE subdept(id, parent_department, name) AS
  (
  	-- non recursive term
  	SELECT * FROM dept WHERE name = 'root'
  	UNION ALL
  	-- recursive term
  	SELECT d.* FROM dept AS d, subdept AS sd
  		WHERE d.pid = sd.id
  )
  SELECT count(*) FROM subdept;
  
  -- Test rescannable hashjoin with spilling hashtable for bfz
  set gp_workfile_type_hashjoin=bfz;
  WITH RECURSIVE subdept(id, parent_department, name) AS
  (
  	-- non recursive term
  	SELECT * FROM dept WHERE name = 'root'
  	UNION ALL
  	-- recursive term
  	SELECT d.* FROM dept AS d, subdept AS sd
  		WHERE d.pid = sd.id
  )
  SELECT count(*) FROM subdept;
  
  -- Test rescannable hashjoin with in-memory hashtable
  reset statement_mem;
  WITH RECURSIVE subdept(id, parent_department, name) AS
  (
  	-- non recursive term
  	SELECT * FROM dept WHERE name = 'root'
  	UNION ALL
  	-- recursive term
  	SELECT d.* FROM dept AS d, subdept AS sd
  		WHERE d.pid = sd.id
  )
  SELECT count(*) FROM subdept;
  
  
  -- MPP-29458
  -- When we join on a clause with two different types. If one table distribute by one type, the query plan
  -- will redistribute data on another type. But the has values of two types would not be equal. The data will
  -- redistribute to wrong segments.
  create table test_timestamp_t1 (id  numeric(10,0) ,field_dt date) distributed by (id);
  create table test_timestamp_t2 (id numeric(10,0),field_tms timestamp without time zone) distributed by (id,field_tms);
  
  insert into test_timestamp_t1 values(10 ,'2018-1-10');
  insert into test_timestamp_t1 values(11 ,'2018-1-11');
  insert into test_timestamp_t2 values(10 ,'2018-1-10'::timestamp);
  insert into test_timestamp_t2 values(11 ,'2018-1-11'::timestamp);
  
  -- Test nest loop redistribute keys
  set enable_nestloop to on;
  set enable_hashjoin to on;
  set enable_mergejoin to on;
  select count(*) from test_timestamp_t1 t1 ,test_timestamp_t2 t2 where T1.id = T2.id and T1.field_dt = t2.field_tms;
  
  -- Test hash join redistribute keys
  set enable_nestloop to off;
  set enable_hashjoin to on;
  set enable_mergejoin to on;
  select count(*) from test_timestamp_t1 t1 ,test_timestamp_t2 t2 where T1.id = T2.id and T1.field_dt = t2.field_tms;
  
  drop table test_timestamp_t1;
  drop table test_timestamp_t2;
  
  -- Test merge join redistribute keys
  create table test_timestamp_t1 (id  numeric(10,0) ,field_dt date) distributed randomly;
  
  create table test_timestamp_t2 (id numeric(10,0),field_tms timestamp without time zone) distributed by (field_tms);
  
  insert into test_timestamp_t1 values(10 ,'2018-1-10');
  insert into test_timestamp_t1 values(11 ,'2018-1-11');
  insert into test_timestamp_t2 values(10 ,'2018-1-10'::timestamp);
  insert into test_timestamp_t2 values(11 ,'2018-1-11'::timestamp);
  
  select * from test_timestamp_t1 t1 full outer join test_timestamp_t2 t2 on T1.id = T2.id and T1.field_dt = t2.field_tms;
  
  -- test float type
  set enable_nestloop to off;
  set enable_hashjoin to on;
  set enable_mergejoin to on;
  create table test_float1(id int, data float4)  DISTRIBUTED BY (data);
  create table test_float2(id int, data float8)  DISTRIBUTED BY (data);
  insert into test_float1 values(1, 10), (2, 20);
  insert into test_float2 values(3, 10), (4, 20);
  select t1.id, t1.data, t2.id, t2.data from test_float1 t1, test_float2 t2 where t1.data = t2.data;
  
  -- test int type
  create table test_int1(id int, data int4)  DISTRIBUTED BY (data);
  create table test_int2(id int, data int8)  DISTRIBUTED BY (data);
  insert into test_int1 values(1, 10), (2, 20);
  insert into test_int2 values(3, 10), (4, 20);
  select t1.id, t1.data, t2.id, t2.data from test_int1 t1, test_int2 t2 where t1.data = t2.data;
  
  -- Test to ensure that for full outer join on varchar columns, planner is successful in finding a sort operator in the catalog
  create table input_table(a varchar(30), b varchar(30)) distributed by (a);
  set enable_hashjoin = off;
  explain (costs off) select X.a from input_table X full join (select a from input_table) Y ON X.a = Y.a;
  
  -- Cleanup
  set client_min_messages='warning'; -- silence drop-cascade NOTICEs
  drop schema pred cascade;
  