create schema bfv_partition_plans;
set search_path=bfv_partition_plans;
--
-- Initial setup for all the partitioning test for this suite
--
-- start_ignore
create language plpythonu;
ERROR:  language "plpythonu" already exists
-- end_ignore
create or replace function count_operator(query text, operator text) returns int as
$$
rv = plpy.execute('EXPLAIN ' + query)
search_text = operator
result = 0
for i in range(len(rv)):
    cur_line = rv[i]['QUERY PLAN']
    if search_text.lower() in cur_line.lower():
        result = result+1
return result
$$
language plpythonu;
create or replace function find_operator(query text, operator_name text) returns text as
$$
rv = plpy.execute('EXPLAIN ' + query)
search_text = operator_name
result = ['false']
for i in range(len(rv)):
    cur_line = rv[i]['QUERY PLAN']
    if search_text.lower() in cur_line.lower():
        result = ['true']
        break
return result
$$
language plpythonu;
-- Test UPDATE that moves row from one partition to another. The partitioning
-- key is also the distribution key in this case.
create table mpp3061 (i int) partition by range(i) (start(1) end(5) every(1));
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
NOTICE:  CREATE TABLE will create partition "mpp3061_1_prt_1" for table "mpp3061"
NOTICE:  CREATE TABLE will create partition "mpp3061_1_prt_2" for table "mpp3061"
NOTICE:  CREATE TABLE will create partition "mpp3061_1_prt_3" for table "mpp3061"
NOTICE:  CREATE TABLE will create partition "mpp3061_1_prt_4" for table "mpp3061"
insert into mpp3061 values(1);
update mpp3061 set i = 2 where i = 1;
ERROR:  moving tuple from partition "mpp3061_1_prt_1" to partition "mpp3061_1_prt_2" not supported
drop table mpp3061;
--
-- Tests if it produces SIGSEGV from "select from partition_table group by rollup or cube function"
--
-- SETUP
create table mpp7980
(
 month_id date,
 bill_stmt_id  character varying(30),
 cust_type     character varying(10),
 subscription_status      character varying(30),
 voice_call_min           numeric(15,2),
 minute_per_call          numeric(15,2),
 subscription_id          character varying(15)
)
distributed by (subscription_id, bill_stmt_id)
  PARTITION BY RANGE(month_id)
    (
    start ('2009-02-01'::date) end ('2009-08-01'::date)  exclusive EVERY (INTERVAL '1 month')
    );
NOTICE:  CREATE TABLE will create partition "mpp7980_1_prt_1" for table "mpp7980"
NOTICE:  CREATE TABLE will create partition "mpp7980_1_prt_2" for table "mpp7980"
NOTICE:  CREATE TABLE will create partition "mpp7980_1_prt_3" for table "mpp7980"
NOTICE:  CREATE TABLE will create partition "mpp7980_1_prt_4" for table "mpp7980"
NOTICE:  CREATE TABLE will create partition "mpp7980_1_prt_5" for table "mpp7980"
NOTICE:  CREATE TABLE will create partition "mpp7980_1_prt_6" for table "mpp7980"
    
-- TEST
select count_operator('select cust_type, subscription_status,count(distinct subscription_id),sum(voice_call_min),sum(minute_per_call) from mpp7980 where month_id =E''2009-04-01'' group by rollup(1,2);','SIGSEGV');
 count_operator 
----------------
              0
(1 row)

insert into mpp7980 values('2009-04-01','xyz','zyz','1',1,1,'1');
insert into mpp7980 values('2009-04-01','zxyz','zyz','2',2,1,'1');
insert into mpp7980 values('2009-03-03','xyz','zyz','4',1,3,'1');
select cust_type, subscription_status,count(distinct subscription_id),sum(voice_call_min),sum(minute_per_call) from mpp7980 where month_id ='2009-04-01' group by rollup(1,2);
 cust_type | subscription_status | count | sum  | sum  
-----------+---------------------+-------+------+------
           |                     |     1 | 3.00 | 2.00
 zyz       |                     |     1 | 3.00 | 2.00
 zyz       | 1                   |     1 | 1.00 | 1.00
 zyz       | 2                   |     1 | 2.00 | 1.00
(4 rows)

-- CLEANUP
drop table mpp7980;
-- ************ORCA ENABLED**********
--
-- MPP-23195
--
-- SETUP
-- start_ignore
set optimizer_enable_bitmapscan=on;
set optimizer_enable_indexjoin=on;
drop table if exists mpp23195_t1;
NOTICE:  table "mpp23195_t1" does not exist, skipping
drop table if exists mpp23195_t2;
NOTICE:  table "mpp23195_t2" does not exist, skipping
-- end_ignore
create table mpp23195_t1 (i int) partition by range(i) (partition pt1 start(1) end(10), partition pt2 start(10) end(20));
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
NOTICE:  CREATE TABLE will create partition "mpp23195_t1_1_prt_pt1" for table "mpp23195_t1"
NOTICE:  CREATE TABLE will create partition "mpp23195_t1_1_prt_pt2" for table "mpp23195_t1"
create index index_mpp23195_t1_i on mpp23195_t1(i);
NOTICE:  building index for child partition "mpp23195_t1_1_prt_pt1"
NOTICE:  building index for child partition "mpp23195_t1_1_prt_pt2"
create table mpp23195_t2(i int);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
insert into mpp23195_t1 values (generate_series(1,19));
insert into mpp23195_t2 values (1);
-- TEST
select find_operator('select * from mpp23195_t1,mpp23195_t2 where mpp23195_t1.i < mpp23195_t2.i;', 'Dynamic Index Scan');
 find_operator 
---------------
 ['false']
(1 row)

select * from mpp23195_t1,mpp23195_t2 where mpp23195_t1.i < mpp23195_t2.i;
 i | i 
---+---
(0 rows)

-- CLEANUP
-- start_ignore
drop table if exists mpp23195_t1;
drop table if exists mpp23195_t2;
set optimizer_enable_bitmapscan=off;
set optimizer_enable_indexjoin=off;
-- end_ignore
--
-- Check we have Dynamic Index Scan operator and check we have Nest loop operator
--
-- SETUP
-- start_ignore
drop table if exists mpp21834_t1;
NOTICE:  table "mpp21834_t1" does not exist, skipping
drop table if exists mpp21834_t2;
NOTICE:  table "mpp21834_t2" does not exist, skipping
-- end_ignore
create table mpp21834_t1 (i int, j int) partition by range(i) (partition pp1 start(1) end(10), partition pp2 start(10) end(20));
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
NOTICE:  CREATE TABLE will create partition "mpp21834_t1_1_prt_pp1" for table "mpp21834_t1"
NOTICE:  CREATE TABLE will create partition "mpp21834_t1_1_prt_pp2" for table "mpp21834_t1"
create index index_1 on mpp21834_t1(i);
NOTICE:  building index for child partition "mpp21834_t1_1_prt_pp1"
NOTICE:  building index for child partition "mpp21834_t1_1_prt_pp2"
create index index_2 on mpp21834_t1(j);
NOTICE:  building index for child partition "mpp21834_t1_1_prt_pp1"
NOTICE:  building index for child partition "mpp21834_t1_1_prt_pp2"
create table mpp21834_t2(i int, j int);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
-- TEST
set optimizer_enable_hashjoin = off;
select find_operator('analyze select * from mpp21834_t2,mpp21834_t1 where mpp21834_t2.i < mpp21834_t1.i;','Dynamic Index Scan');
 find_operator 
---------------
 ['false']
(1 row)

select find_operator('analyze select * from mpp21834_t2,mpp21834_t1 where mpp21834_t2.i < mpp21834_t1.i;','Nested Loop');
 find_operator 
---------------
 ['true']
(1 row)

-- CLEANUP
-- start_ignore
drop index index_2;
WARNING:  Only dropped the index "index_2"
HINT:  To drop other indexes on child partitions, drop each one explicitly.
drop index index_1;
WARNING:  Only dropped the index "index_1"
HINT:  To drop other indexes on child partitions, drop each one explicitly.
drop table if exists mpp21834_t2;
drop table if exists mpp21834_t1;
reset optimizer_enable_hashjoin;
-- end_ignore
--
-- A rescanning of DTS with its own partition selector (under sequence node)
--
-- SETUP
-- start_ignore
set optimizer_enable_broadcast_nestloop_outer_child=on;
drop table if exists mpp23288;
NOTICE:  table "mpp23288" does not exist, skipping
-- end_ignore
create table mpp23288(a int, b int) 
  partition by range (a)
  (
      PARTITION pfirst  END(5) INCLUSIVE,
      PARTITION pinter  START(5) EXCLUSIVE END (10) INCLUSIVE,
      PARTITION plast   START (10) EXCLUSIVE
  );
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
NOTICE:  CREATE TABLE will create partition "mpp23288_1_prt_pfirst" for table "mpp23288"
NOTICE:  CREATE TABLE will create partition "mpp23288_1_prt_pinter" for table "mpp23288"
NOTICE:  CREATE TABLE will create partition "mpp23288_1_prt_plast" for table "mpp23288"
insert into mpp23288(a) select generate_series(1,20);
analyze mpp23288;
-- TEST
select count_operator('select t2.a, t1.a from mpp23288 as t1 join mpp23288 as t2 on (t1.a < t2.a and t2.a =10) order by t2.a, t1.a;','Dynamic Table Scan');
 count_operator 
----------------
              0
(1 row)

select t2.a, t1.a from mpp23288 as t1 join mpp23288 as t2 on (t1.a < t2.a and t2.a =10) order by t2.a, t1.a;
 a  | a 
----+---
 10 | 1
 10 | 2
 10 | 3
 10 | 4
 10 | 5
 10 | 6
 10 | 7
 10 | 8
 10 | 9
(9 rows)

select count_operator('select t2.a, t1.a from mpp23288 as t1 join mpp23288 as t2 on (t1.a < t2.a and (t2.a = 10 or t2.a = 5 or t2.a = 12)) order by t2.a, t1.a;','Dynamic Table Scan');
 count_operator 
----------------
              0
(1 row)

select t2.a, t1.a from mpp23288 as t1 join mpp23288 as t2 on (t1.a < t2.a and (t2.a = 10 or t2.a = 5 or t2.a = 12)) order by t2.a, t1.a;
 a  | a  
----+----
  5 |  1
  5 |  2
  5 |  3
  5 |  4
 10 |  1
 10 |  2
 10 |  3
 10 |  4
 10 |  5
 10 |  6
 10 |  7
 10 |  8
 10 |  9
 12 |  1
 12 |  2
 12 |  3
 12 |  4
 12 |  5
 12 |  6
 12 |  7
 12 |  8
 12 |  9
 12 | 10
 12 | 11
(24 rows)

select count_operator('select t2.a, t1.a from mpp23288 as t1 join mpp23288 as t2 on t1.a < t2.a and t2.a = 1 or t2.a < 10 order by t2.a, t1.a;','Dynamic Table Scan');
 count_operator 
----------------
              0
(1 row)

select t2.a, t1.a from mpp23288 as t1 join mpp23288 as t2 on t1.a < t2.a and t2.a = 1 or t2.a < 10 order by t2.a, t1.a;
 a | a  
---+----
 1 |  1
 1 |  2
 1 |  3
 1 |  4
 1 |  5
 1 |  6
 1 |  7
 1 |  8
 1 |  9
 1 | 10
 1 | 11
 1 | 12
 1 | 13
 1 | 14
 1 | 15
 1 | 16
 1 | 17
 1 | 18
 1 | 19
 1 | 20
 2 |  1
 2 |  2
 2 |  3
 2 |  4
 2 |  5
 2 |  6
 2 |  7
 2 |  8
 2 |  9
 2 | 10
 2 | 11
 2 | 12
 2 | 13
 2 | 14
 2 | 15
 2 | 16
 2 | 17
 2 | 18
 2 | 19
 2 | 20
 3 |  1
 3 |  2
 3 |  3
 3 |  4
 3 |  5
 3 |  6
 3 |  7
 3 |  8
 3 |  9
 3 | 10
 3 | 11
 3 | 12
 3 | 13
 3 | 14
 3 | 15
 3 | 16
 3 | 17
 3 | 18
 3 | 19
 3 | 20
 4 |  1
 4 |  2
 4 |  3
 4 |  4
 4 |  5
 4 |  6
 4 |  7
 4 |  8
 4 |  9
 4 | 10
 4 | 11
 4 | 12
 4 | 13
 4 | 14
 4 | 15
 4 | 16
 4 | 17
 4 | 18
 4 | 19
 4 | 20
 5 |  1
 5 |  2
 5 |  3
 5 |  4
 5 |  5
 5 |  6
 5 |  7
 5 |  8
 5 |  9
 5 | 10
 5 | 11
 5 | 12
 5 | 13
 5 | 14
 5 | 15
 5 | 16
 5 | 17
 5 | 18
 5 | 19
 5 | 20
 6 |  1
 6 |  2
 6 |  3
 6 |  4
 6 |  5
 6 |  6
 6 |  7
 6 |  8
 6 |  9
 6 | 10
 6 | 11
 6 | 12
 6 | 13
 6 | 14
 6 | 15
 6 | 16
 6 | 17
 6 | 18
 6 | 19
 6 | 20
 7 |  1
 7 |  2
 7 |  3
 7 |  4
 7 |  5
 7 |  6
 7 |  7
 7 |  8
 7 |  9
 7 | 10
 7 | 11
 7 | 12
 7 | 13
 7 | 14
 7 | 15
 7 | 16
 7 | 17
 7 | 18
 7 | 19
 7 | 20
 8 |  1
 8 |  2
 8 |  3
 8 |  4
 8 |  5
 8 |  6
 8 |  7
 8 |  8
 8 |  9
 8 | 10
 8 | 11
 8 | 12
 8 | 13
 8 | 14
 8 | 15
 8 | 16
 8 | 17
 8 | 18
 8 | 19
 8 | 20
 9 |  1
 9 |  2
 9 |  3
 9 |  4
 9 |  5
 9 |  6
 9 |  7
 9 |  8
 9 |  9
 9 | 10
 9 | 11
 9 | 12
 9 | 13
 9 | 14
 9 | 15
 9 | 16
 9 | 17
 9 | 18
 9 | 19
 9 | 20
(180 rows)

-- CLEANUP
-- start_ignore
drop table if exists mpp23288;
set optimizer_enable_broadcast_nestloop_outer_child=off;
-- end_ignore
--
-- Tests if DynamicIndexScan sets tuple descriptor of the planstate->ps_ResultTupleSlot
--
-- SETUP
-- start_ignore
drop table if exists mpp24151_t;
NOTICE:  table "mpp24151_t" does not exist, skipping
drop table if exists mpp24151_pt;
NOTICE:  table "mpp24151_pt" does not exist, skipping
-- end_ignore
create table mpp24151_t(dist int, tid int, t1 text, t2 text);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'dist' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
create table mpp24151_pt(dist int, pt1 text, pt2 text, pt3 text, ptid int) 
DISTRIBUTED BY (dist)
PARTITION BY RANGE(ptid) 
          (
          START (0) END (5) EVERY (1),
          DEFAULT PARTITION junk_data
          )
;
NOTICE:  CREATE TABLE will create partition "mpp24151_pt_1_prt_junk_data" for table "mpp24151_pt"
NOTICE:  CREATE TABLE will create partition "mpp24151_pt_1_prt_2" for table "mpp24151_pt"
NOTICE:  CREATE TABLE will create partition "mpp24151_pt_1_prt_3" for table "mpp24151_pt"
NOTICE:  CREATE TABLE will create partition "mpp24151_pt_1_prt_4" for table "mpp24151_pt"
NOTICE:  CREATE TABLE will create partition "mpp24151_pt_1_prt_5" for table "mpp24151_pt"
NOTICE:  CREATE TABLE will create partition "mpp24151_pt_1_prt_6" for table "mpp24151_pt"
create index pt1_idx on mpp24151_pt using btree (pt1);
NOTICE:  building index for child partition "mpp24151_pt_1_prt_junk_data"
NOTICE:  building index for child partition "mpp24151_pt_1_prt_2"
NOTICE:  building index for child partition "mpp24151_pt_1_prt_3"
NOTICE:  building index for child partition "mpp24151_pt_1_prt_4"
NOTICE:  building index for child partition "mpp24151_pt_1_prt_5"
NOTICE:  building index for child partition "mpp24151_pt_1_prt_6"
create index ptid_idx on mpp24151_pt using btree (ptid);
NOTICE:  building index for child partition "mpp24151_pt_1_prt_junk_data"
NOTICE:  building index for child partition "mpp24151_pt_1_prt_2"
NOTICE:  building index for child partition "mpp24151_pt_1_prt_3"
NOTICE:  building index for child partition "mpp24151_pt_1_prt_4"
NOTICE:  building index for child partition "mpp24151_pt_1_prt_5"
NOTICE:  building index for child partition "mpp24151_pt_1_prt_6"
insert into mpp24151_pt select i, 'hello' || 0, 'world', 'drop this', i % 6 from generate_series(0,100)i;
insert into mpp24151_pt select i, 'hello' || i, 'world', 'drop this', i % 6 from generate_series(0,200000)i;
insert into mpp24151_t select i, i % 6, 'hello' || i, 'bar' from generate_series(0,10)i;
analyze mpp24151_pt;
analyze mpp24151_t;
-- TEST
set optimizer_enable_dynamictablescan = off;
select count_operator('select * from mpp24151_t, mpp24151_pt where tid = ptid and pt1 = E''hello0'';','Result');
 count_operator 
----------------
              6
(1 row)

select * from mpp24151_t, mpp24151_pt where tid = ptid and pt1 = 'hello0';
 dist | tid |   t1    | t2  | dist |  pt1   |  pt2  |    pt3    | ptid 
------+-----+---------+-----+------+--------+-------+-----------+------
    5 |   5 | hello5  | bar |    5 | hello0 | world | drop this |    5
    5 |   5 | hello5  | bar |   41 | hello0 | world | drop this |    5
    5 |   5 | hello5  | bar |   53 | hello0 | world | drop this |    5
    5 |   5 | hello5  | bar |   77 | hello0 | world | drop this |    5
    5 |   5 | hello5  | bar |   89 | hello0 | world | drop this |    5
    6 |   0 | hello6  | bar |   96 | hello0 | world | drop this |    0
    0 |   0 | hello0  | bar |   96 | hello0 | world | drop this |    0
    6 |   0 | hello6  | bar |   90 | hello0 | world | drop this |    0
    0 |   0 | hello0  | bar |   90 | hello0 | world | drop this |    0
    6 |   0 | hello6  | bar |   54 | hello0 | world | drop this |    0
    0 |   0 | hello0  | bar |   54 | hello0 | world | drop this |    0
    6 |   0 | hello6  | bar |   42 | hello0 | world | drop this |    0
    0 |   0 | hello0  | bar |   42 | hello0 | world | drop this |    0
    6 |   0 | hello6  | bar |   18 | hello0 | world | drop this |    0
    0 |   0 | hello0  | bar |   18 | hello0 | world | drop this |    0
    6 |   0 | hello6  | bar |    6 | hello0 | world | drop this |    0
    0 |   0 | hello0  | bar |    6 | hello0 | world | drop this |    0
    7 |   1 | hello7  | bar |    7 | hello0 | world | drop this |    1
    1 |   1 | hello1  | bar |    7 | hello0 | world | drop this |    1
    7 |   1 | hello7  | bar |   19 | hello0 | world | drop this |    1
    1 |   1 | hello1  | bar |   19 | hello0 | world | drop this |    1
    7 |   1 | hello7  | bar |   55 | hello0 | world | drop this |    1
    1 |   1 | hello1  | bar |   55 | hello0 | world | drop this |    1
    7 |   1 | hello7  | bar |   73 | hello0 | world | drop this |    1
    1 |   1 | hello1  | bar |   73 | hello0 | world | drop this |    1
    7 |   1 | hello7  | bar |   91 | hello0 | world | drop this |    1
    1 |   1 | hello1  | bar |   91 | hello0 | world | drop this |    1
    7 |   1 | hello7  | bar |   97 | hello0 | world | drop this |    1
    1 |   1 | hello1  | bar |   97 | hello0 | world | drop this |    1
    2 |   2 | hello2  | bar |   20 | hello0 | world | drop this |    2
    8 |   2 | hello8  | bar |   20 | hello0 | world | drop this |    2
    2 |   2 | hello2  | bar |   38 | hello0 | world | drop this |    2
    8 |   2 | hello8  | bar |   38 | hello0 | world | drop this |    2
    2 |   2 | hello2  | bar |   56 | hello0 | world | drop this |    2
    8 |   2 | hello8  | bar |   56 | hello0 | world | drop this |    2
    2 |   2 | hello2  | bar |   74 | hello0 | world | drop this |    2
    8 |   2 | hello8  | bar |   74 | hello0 | world | drop this |    2
    3 |   3 | hello3  | bar |    3 | hello0 | world | drop this |    3
    9 |   3 | hello9  | bar |    3 | hello0 | world | drop this |    3
    3 |   3 | hello3  | bar |   21 | hello0 | world | drop this |    3
    9 |   3 | hello9  | bar |   21 | hello0 | world | drop this |    3
    3 |   3 | hello3  | bar |   39 | hello0 | world | drop this |    3
    9 |   3 | hello9  | bar |   39 | hello0 | world | drop this |    3
    3 |   3 | hello3  | bar |   57 | hello0 | world | drop this |    3
    9 |   3 | hello9  | bar |   57 | hello0 | world | drop this |    3
    3 |   3 | hello3  | bar |   75 | hello0 | world | drop this |    3
    9 |   3 | hello9  | bar |   75 | hello0 | world | drop this |    3
    3 |   3 | hello3  | bar |   87 | hello0 | world | drop this |    3
    9 |   3 | hello9  | bar |   87 | hello0 | world | drop this |    3
    4 |   4 | hello4  | bar |    4 | hello0 | world | drop this |    4
   10 |   4 | hello10 | bar |    4 | hello0 | world | drop this |    4
    4 |   4 | hello4  | bar |   22 | hello0 | world | drop this |    4
   10 |   4 | hello10 | bar |   22 | hello0 | world | drop this |    4
    4 |   4 | hello4  | bar |   40 | hello0 | world | drop this |    4
   10 |   4 | hello10 | bar |   40 | hello0 | world | drop this |    4
    4 |   4 | hello4  | bar |   76 | hello0 | world | drop this |    4
   10 |   4 | hello10 | bar |   76 | hello0 | world | drop this |    4
    4 |   4 | hello4  | bar |   88 | hello0 | world | drop this |    4
   10 |   4 | hello10 | bar |   88 | hello0 | world | drop this |    4
    5 |   5 | hello5  | bar |   17 | hello0 | world | drop this |    5
    5 |   5 | hello5  | bar |   29 | hello0 | world | drop this |    5
    5 |   5 | hello5  | bar |   35 | hello0 | world | drop this |    5
    5 |   5 | hello5  | bar |   71 | hello0 | world | drop this |    5
    5 |   5 | hello5  | bar |   83 | hello0 | world | drop this |    5
    6 |   0 | hello6  | bar |    0 | hello0 | world | drop this |    0
    0 |   0 | hello0  | bar |    0 | hello0 | world | drop this |    0
    6 |   0 | hello6  | bar |   84 | hello0 | world | drop this |    0
    0 |   0 | hello0  | bar |   84 | hello0 | world | drop this |    0
    6 |   0 | hello6  | bar |   72 | hello0 | world | drop this |    0
    0 |   0 | hello0  | bar |   72 | hello0 | world | drop this |    0
    6 |   0 | hello6  | bar |   48 | hello0 | world | drop this |    0
    0 |   0 | hello0  | bar |   48 | hello0 | world | drop this |    0
    6 |   0 | hello6  | bar |   36 | hello0 | world | drop this |    0
    0 |   0 | hello0  | bar |   36 | hello0 | world | drop this |    0
    6 |   0 | hello6  | bar |   30 | hello0 | world | drop this |    0
    0 |   0 | hello0  | bar |   30 | hello0 | world | drop this |    0
    6 |   0 | hello6  | bar |    0 | hello0 | world | drop this |    0
    0 |   0 | hello0  | bar |    0 | hello0 | world | drop this |    0
    7 |   1 | hello7  | bar |    1 | hello0 | world | drop this |    1
    1 |   1 | hello1  | bar |    1 | hello0 | world | drop this |    1
    7 |   1 | hello7  | bar |   13 | hello0 | world | drop this |    1
    1 |   1 | hello1  | bar |   13 | hello0 | world | drop this |    1
    7 |   1 | hello7  | bar |   31 | hello0 | world | drop this |    1
    1 |   1 | hello1  | bar |   31 | hello0 | world | drop this |    1
    7 |   1 | hello7  | bar |   37 | hello0 | world | drop this |    1
    1 |   1 | hello1  | bar |   37 | hello0 | world | drop this |    1
    7 |   1 | hello7  | bar |   49 | hello0 | world | drop this |    1
    1 |   1 | hello1  | bar |   49 | hello0 | world | drop this |    1
    7 |   1 | hello7  | bar |   85 | hello0 | world | drop this |    1
    1 |   1 | hello1  | bar |   85 | hello0 | world | drop this |    1
    2 |   2 | hello2  | bar |    2 | hello0 | world | drop this |    2
    8 |   2 | hello8  | bar |    2 | hello0 | world | drop this |    2
    2 |   2 | hello2  | bar |   14 | hello0 | world | drop this |    2
    8 |   2 | hello8  | bar |   14 | hello0 | world | drop this |    2
    2 |   2 | hello2  | bar |   50 | hello0 | world | drop this |    2
    8 |   2 | hello8  | bar |   50 | hello0 | world | drop this |    2
    2 |   2 | hello2  | bar |   68 | hello0 | world | drop this |    2
    8 |   2 | hello8  | bar |   68 | hello0 | world | drop this |    2
    2 |   2 | hello2  | bar |   86 | hello0 | world | drop this |    2
    8 |   2 | hello8  | bar |   86 | hello0 | world | drop this |    2
    3 |   3 | hello3  | bar |   15 | hello0 | world | drop this |    3
    9 |   3 | hello9  | bar |   15 | hello0 | world | drop this |    3
    3 |   3 | hello3  | bar |   33 | hello0 | world | drop this |    3
    9 |   3 | hello9  | bar |   33 | hello0 | world | drop this |    3
    3 |   3 | hello3  | bar |   51 | hello0 | world | drop this |    3
    9 |   3 | hello9  | bar |   51 | hello0 | world | drop this |    3
    3 |   3 | hello3  | bar |   63 | hello0 | world | drop this |    3
    9 |   3 | hello9  | bar |   63 | hello0 | world | drop this |    3
    3 |   3 | hello3  | bar |   69 | hello0 | world | drop this |    3
    9 |   3 | hello9  | bar |   69 | hello0 | world | drop this |    3
    4 |   4 | hello4  | bar |   16 | hello0 | world | drop this |    4
   10 |   4 | hello10 | bar |   16 | hello0 | world | drop this |    4
    4 |   4 | hello4  | bar |   28 | hello0 | world | drop this |    4
   10 |   4 | hello10 | bar |   28 | hello0 | world | drop this |    4
    4 |   4 | hello4  | bar |   34 | hello0 | world | drop this |    4
   10 |   4 | hello10 | bar |   34 | hello0 | world | drop this |    4
    4 |   4 | hello4  | bar |   52 | hello0 | world | drop this |    4
   10 |   4 | hello10 | bar |   52 | hello0 | world | drop this |    4
    4 |   4 | hello4  | bar |   70 | hello0 | world | drop this |    4
   10 |   4 | hello10 | bar |   70 | hello0 | world | drop this |    4
    5 |   5 | hello5  | bar |   11 | hello0 | world | drop this |    5
    5 |   5 | hello5  | bar |   23 | hello0 | world | drop this |    5
    5 |   5 | hello5  | bar |   47 | hello0 | world | drop this |    5
    5 |   5 | hello5  | bar |   59 | hello0 | world | drop this |    5
    5 |   5 | hello5  | bar |   65 | hello0 | world | drop this |    5
    5 |   5 | hello5  | bar |   95 | hello0 | world | drop this |    5
    6 |   0 | hello6  | bar |   78 | hello0 | world | drop this |    0
    0 |   0 | hello0  | bar |   78 | hello0 | world | drop this |    0
    6 |   0 | hello6  | bar |   66 | hello0 | world | drop this |    0
    0 |   0 | hello0  | bar |   66 | hello0 | world | drop this |    0
    6 |   0 | hello6  | bar |   60 | hello0 | world | drop this |    0
    0 |   0 | hello0  | bar |   60 | hello0 | world | drop this |    0
    6 |   0 | hello6  | bar |   24 | hello0 | world | drop this |    0
    0 |   0 | hello0  | bar |   24 | hello0 | world | drop this |    0
    6 |   0 | hello6  | bar |   12 | hello0 | world | drop this |    0
    0 |   0 | hello0  | bar |   12 | hello0 | world | drop this |    0
    7 |   1 | hello7  | bar |   25 | hello0 | world | drop this |    1
    1 |   1 | hello1  | bar |   25 | hello0 | world | drop this |    1
    7 |   1 | hello7  | bar |   43 | hello0 | world | drop this |    1
    1 |   1 | hello1  | bar |   43 | hello0 | world | drop this |    1
    7 |   1 | hello7  | bar |   61 | hello0 | world | drop this |    1
    1 |   1 | hello1  | bar |   61 | hello0 | world | drop this |    1
    7 |   1 | hello7  | bar |   67 | hello0 | world | drop this |    1
    1 |   1 | hello1  | bar |   67 | hello0 | world | drop this |    1
    7 |   1 | hello7  | bar |   79 | hello0 | world | drop this |    1
    1 |   1 | hello1  | bar |   79 | hello0 | world | drop this |    1
    2 |   2 | hello2  | bar |    8 | hello0 | world | drop this |    2
    8 |   2 | hello8  | bar |    8 | hello0 | world | drop this |    2
    2 |   2 | hello2  | bar |   26 | hello0 | world | drop this |    2
    8 |   2 | hello8  | bar |   26 | hello0 | world | drop this |    2
    2 |   2 | hello2  | bar |   32 | hello0 | world | drop this |    2
    8 |   2 | hello8  | bar |   32 | hello0 | world | drop this |    2
    2 |   2 | hello2  | bar |   44 | hello0 | world | drop this |    2
    8 |   2 | hello8  | bar |   44 | hello0 | world | drop this |    2
    2 |   2 | hello2  | bar |   62 | hello0 | world | drop this |    2
    8 |   2 | hello8  | bar |   62 | hello0 | world | drop this |    2
    2 |   2 | hello2  | bar |   80 | hello0 | world | drop this |    2
    8 |   2 | hello8  | bar |   80 | hello0 | world | drop this |    2
    2 |   2 | hello2  | bar |   92 | hello0 | world | drop this |    2
    8 |   2 | hello8  | bar |   92 | hello0 | world | drop this |    2
    2 |   2 | hello2  | bar |   98 | hello0 | world | drop this |    2
    8 |   2 | hello8  | bar |   98 | hello0 | world | drop this |    2
    3 |   3 | hello3  | bar |    9 | hello0 | world | drop this |    3
    9 |   3 | hello9  | bar |    9 | hello0 | world | drop this |    3
    3 |   3 | hello3  | bar |   27 | hello0 | world | drop this |    3
    9 |   3 | hello9  | bar |   27 | hello0 | world | drop this |    3
    3 |   3 | hello3  | bar |   45 | hello0 | world | drop this |    3
    9 |   3 | hello9  | bar |   45 | hello0 | world | drop this |    3
    3 |   3 | hello3  | bar |   81 | hello0 | world | drop this |    3
    9 |   3 | hello9  | bar |   81 | hello0 | world | drop this |    3
    3 |   3 | hello3  | bar |   93 | hello0 | world | drop this |    3
    9 |   3 | hello9  | bar |   93 | hello0 | world | drop this |    3
    3 |   3 | hello3  | bar |   99 | hello0 | world | drop this |    3
    9 |   3 | hello9  | bar |   99 | hello0 | world | drop this |    3
    4 |   4 | hello4  | bar |   10 | hello0 | world | drop this |    4
   10 |   4 | hello10 | bar |   10 | hello0 | world | drop this |    4
    4 |   4 | hello4  | bar |   46 | hello0 | world | drop this |    4
   10 |   4 | hello10 | bar |   46 | hello0 | world | drop this |    4
    4 |   4 | hello4  | bar |   58 | hello0 | world | drop this |    4
   10 |   4 | hello10 | bar |   58 | hello0 | world | drop this |    4
    4 |   4 | hello4  | bar |   64 | hello0 | world | drop this |    4
   10 |   4 | hello10 | bar |   64 | hello0 | world | drop this |    4
    4 |   4 | hello4  | bar |   82 | hello0 | world | drop this |    4
   10 |   4 | hello10 | bar |   82 | hello0 | world | drop this |    4
    4 |   4 | hello4  | bar |   94 | hello0 | world | drop this |    4
   10 |   4 | hello10 | bar |   94 | hello0 | world | drop this |    4
    4 |   4 | hello4  | bar |  100 | hello0 | world | drop this |    4
   10 |   4 | hello10 | bar |  100 | hello0 | world | drop this |    4
(188 rows)

-- CLEANUP
-- start_ignore
drop index ptid_idx;
WARNING:  Only dropped the index "ptid_idx"
HINT:  To drop other indexes on child partitions, drop each one explicitly.
drop index pt1_idx;
WARNING:  Only dropped the index "pt1_idx"
HINT:  To drop other indexes on child partitions, drop each one explicitly.
drop table if exists mpp24151_t;
drop table if exists mpp24151_pt;
reset optimizer_enable_dynamictablescan;
-- end_ignore
--
-- No DPE (Dynamic Partition Elimination) on second child of a union under a join
--
-- SETUP
-- start_ignore
drop table if exists t;
NOTICE:  table "t" does not exist, skipping
drop table if exists p1;
NOTICE:  table "p1" does not exist, skipping
drop table if exists p2;
NOTICE:  table "p2" does not exist, skipping
drop table if exists p3;
NOTICE:  table "p3" does not exist, skipping
drop table if exists p;
NOTICE:  table "p" does not exist, skipping
-- end_ignore
create table p1 (a int, b int) partition by range(b) (start (1) end(100) every (20));
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
NOTICE:  CREATE TABLE will create partition "p1_1_prt_1" for table "p1"
NOTICE:  CREATE TABLE will create partition "p1_1_prt_2" for table "p1"
NOTICE:  CREATE TABLE will create partition "p1_1_prt_3" for table "p1"
NOTICE:  CREATE TABLE will create partition "p1_1_prt_4" for table "p1"
NOTICE:  CREATE TABLE will create partition "p1_1_prt_5" for table "p1"
create table p2 (a int, b int) partition by range(b) (start (1) end(100) every (20));
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
NOTICE:  CREATE TABLE will create partition "p2_1_prt_1" for table "p2"
NOTICE:  CREATE TABLE will create partition "p2_1_prt_2" for table "p2"
NOTICE:  CREATE TABLE will create partition "p2_1_prt_3" for table "p2"
NOTICE:  CREATE TABLE will create partition "p2_1_prt_4" for table "p2"
NOTICE:  CREATE TABLE will create partition "p2_1_prt_5" for table "p2"
create table p3 (a int, b int) partition by range(b) (start (1) end(100) every (20));
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
NOTICE:  CREATE TABLE will create partition "p3_1_prt_1" for table "p3"
NOTICE:  CREATE TABLE will create partition "p3_1_prt_2" for table "p3"
NOTICE:  CREATE TABLE will create partition "p3_1_prt_3" for table "p3"
NOTICE:  CREATE TABLE will create partition "p3_1_prt_4" for table "p3"
NOTICE:  CREATE TABLE will create partition "p3_1_prt_5" for table "p3"
create table p (a int, b int);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
create table t(a int, b int);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
insert into t select g, g*10 from generate_series(1,100) g;
insert into p1 select g, g%99 +1 from generate_series(1,10000) g;
insert into p2 select g, g%99 +1 from generate_series(1,10000) g;
insert into p3 select g, g%99 +1 from generate_series(1,10000) g;
insert into p select g, g%99 +1 from generate_series(1,10000) g;
analyze t;
analyze p1;
analyze p2;
analyze p3;
analyze p;
-- TEST
select count_operator('select * from (select * from p1 union all select * from p2) as p_all, t where p_all.b=t.b;','Partition Selector');
 count_operator 
----------------
              0
(1 row)

select count_operator('select * from (select * from p1 union select * from p2) as p_all, t where p_all.b=t.b;','Partition Selector');
 count_operator 
----------------
              0
(1 row)

select count_operator('select * from (select * from p1 except all select * from p2) as p_all, t where p_all.b=t.b;','Partition Selector');
 count_operator 
----------------
              0
(1 row)

select count_operator('select * from (select * from p1 except select * from p2) as p_all, t where p_all.b=t.b;','Partition Selector');
 count_operator 
----------------
              0
(1 row)

select count_operator('select * from (select * from p1 intersect all select * from p2) as p_all, t where p_all.b=t.b;','Partition Selector');
 count_operator 
----------------
              0
(1 row)

select count_operator('select * from (select * from p1 union select * from p2 union all select * from p3) as p_all, t where p_all.b=t.b;','Partition Selector');
 count_operator 
----------------
              0
(1 row)

select count_operator('select * from (select * from p1 union select * from p2 union all select * from p) as p_all, t where p_all.b=t.b;','Partition Selector');
 count_operator 
----------------
              0
(1 row)

select count_operator('select * from (select * from p1 union select * from p union all select * from p2) as p_all, t where p_all.b=t.b;','Partition Selector');
 count_operator 
----------------
              0
(1 row)

select count_operator('select * from (select * from p1 union select * from p2 intersect all select * from p3) as p_all, t where p_all.b=t.b;','Partition Selector');
 count_operator 
----------------
              0
(1 row)

select count_operator('select * from (select * from p1 union select * from p intersect all select * from p2) as p_all, t where p_all.b=t.b;','Partition Selector');
 count_operator 
----------------
              0
(1 row)

-- CLEANUP
-- start_ignore
drop table t;
drop table p1;
drop table p2;
drop table p3;
drop table p;
-- end_ignore
--
-- Gracefully handle NULL partition set from BitmapTableScan, DynamicTableScan and DynamicIndexScan
--
-- SETUP
-- start_ignore
drop table if exists dts;
NOTICE:  table "dts" does not exist, skipping
drop table if exists dis;
NOTICE:  table "dis" does not exist, skipping
drop table if exists dbs;
NOTICE:  table "dbs" does not exist, skipping
-- end_ignore
create table dts(c1 int, c2 int) partition by range(c2) (start(1) end(11) every(1));
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'c1' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
NOTICE:  CREATE TABLE will create partition "dts_1_prt_1" for table "dts"
NOTICE:  CREATE TABLE will create partition "dts_1_prt_2" for table "dts"
NOTICE:  CREATE TABLE will create partition "dts_1_prt_3" for table "dts"
NOTICE:  CREATE TABLE will create partition "dts_1_prt_4" for table "dts"
NOTICE:  CREATE TABLE will create partition "dts_1_prt_5" for table "dts"
NOTICE:  CREATE TABLE will create partition "dts_1_prt_6" for table "dts"
NOTICE:  CREATE TABLE will create partition "dts_1_prt_7" for table "dts"
NOTICE:  CREATE TABLE will create partition "dts_1_prt_8" for table "dts"
NOTICE:  CREATE TABLE will create partition "dts_1_prt_9" for table "dts"
NOTICE:  CREATE TABLE will create partition "dts_1_prt_10" for table "dts"
create table dis(c1 int, c2 int, c3 int) partition by range(c2) (start(1) end(11) every(1));
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'c1' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
NOTICE:  CREATE TABLE will create partition "dis_1_prt_1" for table "dis"
NOTICE:  CREATE TABLE will create partition "dis_1_prt_2" for table "dis"
NOTICE:  CREATE TABLE will create partition "dis_1_prt_3" for table "dis"
NOTICE:  CREATE TABLE will create partition "dis_1_prt_4" for table "dis"
NOTICE:  CREATE TABLE will create partition "dis_1_prt_5" for table "dis"
NOTICE:  CREATE TABLE will create partition "dis_1_prt_6" for table "dis"
NOTICE:  CREATE TABLE will create partition "dis_1_prt_7" for table "dis"
NOTICE:  CREATE TABLE will create partition "dis_1_prt_8" for table "dis"
NOTICE:  CREATE TABLE will create partition "dis_1_prt_9" for table "dis"
NOTICE:  CREATE TABLE will create partition "dis_1_prt_10" for table "dis"
create index dis_index on dis(c3);
NOTICE:  building index for child partition "dis_1_prt_1"
NOTICE:  building index for child partition "dis_1_prt_2"
NOTICE:  building index for child partition "dis_1_prt_3"
NOTICE:  building index for child partition "dis_1_prt_4"
NOTICE:  building index for child partition "dis_1_prt_5"
NOTICE:  building index for child partition "dis_1_prt_6"
NOTICE:  building index for child partition "dis_1_prt_7"
NOTICE:  building index for child partition "dis_1_prt_8"
NOTICE:  building index for child partition "dis_1_prt_9"
NOTICE:  building index for child partition "dis_1_prt_10"
CREATE TABLE dbs(c1 int, c2 int, c3 int) partition by range(c2) (start(1) end(11) every(1));
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'c1' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
NOTICE:  CREATE TABLE will create partition "dbs_1_prt_1" for table "dbs"
NOTICE:  CREATE TABLE will create partition "dbs_1_prt_2" for table "dbs"
NOTICE:  CREATE TABLE will create partition "dbs_1_prt_3" for table "dbs"
NOTICE:  CREATE TABLE will create partition "dbs_1_prt_4" for table "dbs"
NOTICE:  CREATE TABLE will create partition "dbs_1_prt_5" for table "dbs"
NOTICE:  CREATE TABLE will create partition "dbs_1_prt_6" for table "dbs"
NOTICE:  CREATE TABLE will create partition "dbs_1_prt_7" for table "dbs"
NOTICE:  CREATE TABLE will create partition "dbs_1_prt_8" for table "dbs"
NOTICE:  CREATE TABLE will create partition "dbs_1_prt_9" for table "dbs"
NOTICE:  CREATE TABLE will create partition "dbs_1_prt_10" for table "dbs"
create index dbs_index on dbs using bitmap(c3);
NOTICE:  building index for child partition "dbs_1_prt_1"
NOTICE:  building index for child partition "dbs_1_prt_2"
NOTICE:  building index for child partition "dbs_1_prt_3"
NOTICE:  building index for child partition "dbs_1_prt_4"
NOTICE:  building index for child partition "dbs_1_prt_5"
NOTICE:  building index for child partition "dbs_1_prt_6"
NOTICE:  building index for child partition "dbs_1_prt_7"
NOTICE:  building index for child partition "dbs_1_prt_8"
NOTICE:  building index for child partition "dbs_1_prt_9"
NOTICE:  building index for child partition "dbs_1_prt_10"
-- TEST
select find_operator('(select * from dts where c2 = 1) union (select * from dts where c2 = 2) union (select * from dts where c2 = 3) union (select * from dts where c2 = 4) union (select * from dts where c2 = 5) union (select * from dts where c2 = 6) union (select * from dts where c2 = 7) union (select * from dts where c2 = 8) union (select * from dts where c2 = 9) union (select * from dts where c2 = 10);', 'Dynamic Table Scan');
 find_operator 
---------------
 ['false']
(1 row)

(select * from dts where c2 = 1) union
(select * from dts where c2 = 2) union
(select * from dts where c2 = 3) union
(select * from dts where c2 = 4) union
(select * from dts where c2 = 5) union
(select * from dts where c2 = 6) union
(select * from dts where c2 = 7) union
(select * from dts where c2 = 8) union
(select * from dts where c2 = 9) union
(select * from dts where c2 = 10);
 c1 | c2 
----+----
(0 rows)

set optimizer_enable_dynamictablescan = off;
select find_operator('(select * from dis where c3 = 1) union (select * from dis where c3 = 2) union (select * from dis where c3 = 3) union (select * from dis where c3 = 4) union (select * from dis where c3 = 5) union (select * from dis where c3 = 6) union (select * from dis where c3 = 7) union (select * from dis where c3 = 8) union (select * from dis where c3 = 9) union (select * from dis where c3 = 10);', 'Dynamic Index Scan');
 find_operator 
---------------
 ['false']
(1 row)

(select * from dis where c3 = 1) union
(select * from dis where c3 = 2) union
(select * from dis where c3 = 3) union
(select * from dis where c3 = 4) union
(select * from dis where c3 = 5) union
(select * from dis where c3 = 6) union
(select * from dis where c3 = 7) union
(select * from dis where c3 = 8) union
(select * from dis where c3 = 9) union
(select * from dis where c3 = 10);
 c1 | c2 | c3 
----+----+----
(0 rows)

select find_operator('select * from dbs where c2= 15 and c3 = 5;', 'Bitmap Table Scan');
 find_operator 
---------------
 ['false']
(1 row)

select * from dbs where c2= 15 and c3 = 5;
 c1 | c2 | c3 
----+----+----
(0 rows)

-- CLEANUP
-- start_ignore
drop index dbs_index;
WARNING:  Only dropped the index "dbs_index"
HINT:  To drop other indexes on child partitions, drop each one explicitly.
drop table if exists dbs;
drop index dis_index;
WARNING:  Only dropped the index "dis_index"
HINT:  To drop other indexes on child partitions, drop each one explicitly.
drop table if exists dis;
drop table if exists dts;
reset optimizer_enable_dynamictablescan;
-- end_ignore
--
-- Partition elimination for heterogenous DynamicIndexScans
--
-- SETUP
-- start_ignore
drop table if exists pp;
NOTICE:  table "pp" does not exist, skipping
drop index if exists pp_1_prt_1_idx;
NOTICE:  index "pp_1_prt_1_idx" does not exist, skipping
drop index if exists pp_rest_1_idx;
NOTICE:  index "pp_rest_1_idx" does not exist, skipping
drop index if exists pp_rest_2_idx;
NOTICE:  index "pp_rest_2_idx" does not exist, skipping
set optimizer_segments=2;
set optimizer_partition_selection_log=on;
-- end_ignore
create table pp(a int, b int, c int) partition by range(b) (start(1) end(15) every(5));
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
NOTICE:  CREATE TABLE will create partition "pp_1_prt_1" for table "pp"
NOTICE:  CREATE TABLE will create partition "pp_1_prt_2" for table "pp"
NOTICE:  CREATE TABLE will create partition "pp_1_prt_3" for table "pp"
insert into pp values (1,1,2),(2,6,2), (3,11,2);
-- Heterogeneous Index on the partition table
create index pp_1_prt_1_idx on pp_1_prt_1(c);
-- Create other indexes so that we can automate the repro for MPP-21069 by disabling tablescan
create index pp_rest_1_idx on pp_1_prt_2(c,a);
create index pp_rest_2_idx on pp_1_prt_3(c,a);
-- TEST
set optimizer_enable_dynamictablescan = off;
select * from pp where b=2 and c=2;
 a | b | c 
---+---+---
(0 rows)

select count_operator('select * from pp where b=2 and c=2;','Partition Selector');
 count_operator 
----------------
              0
(1 row)

-- CLEANUP
-- start_ignore
drop index if exists pp_rest_2_idx;
drop index if exists pp_rest_1_idx;
drop index if exists pp_1_prt_1_idx;
drop table if exists pp;
reset optimizer_enable_dynamictablescan;
reset optimizer_segments;
set optimizer_partition_selection_log=off;
-- end_ignore
--
-- Partition elimination with implicit CAST on the partitioning key
--
-- SETUP
-- start_ignore
set optimizer_segments=2;
set optimizer_partition_selection_log=on;
DROP TABLE IF EXISTS ds_4;
NOTICE:  table "ds_4" does not exist, skipping
-- end_ignore
CREATE TABLE ds_4
(
  month_id character varying(6),
  cust_group_acc numeric(10),
  mobile_no character varying(10)
)
DISTRIBUTED BY (cust_group_acc, mobile_no)
PARTITION BY LIST(month_id)
          (
          PARTITION p200800 VALUES('200800'),
          PARTITION p200801 VALUES('200801'),
          PARTITION p200802 VALUES('200802'),
          PARTITION p200803 VALUES('200803')
);
NOTICE:  CREATE TABLE will create partition "ds_4_1_prt_p200800" for table "ds_4"
NOTICE:  CREATE TABLE will create partition "ds_4_1_prt_p200801" for table "ds_4"
NOTICE:  CREATE TABLE will create partition "ds_4_1_prt_p200802" for table "ds_4"
NOTICE:  CREATE TABLE will create partition "ds_4_1_prt_p200803" for table "ds_4"
-- TEST
select * from ds_4 where month_id = '200800';
 month_id | cust_group_acc | mobile_no 
----------+----------------+-----------
(0 rows)

select count_operator('select * from ds_4 where month_id = E''200800'';','Partition Selector');
 count_operator 
----------------
              0
(1 row)

select * from ds_4 where month_id > '200800';
 month_id | cust_group_acc | mobile_no 
----------+----------------+-----------
(0 rows)

select count_operator('select * from ds_4 where month_id > E''200800'';','Partition Selector');
 count_operator 
----------------
              0
(1 row)

select * from ds_4 where month_id <= '200800';
 month_id | cust_group_acc | mobile_no 
----------+----------------+-----------
(0 rows)

select count_operator('select * from ds_4 where month_id <= E''200800'';','Partition Selector');
 count_operator 
----------------
              0
(1 row)

select * from ds_4 a1,ds_4 a2 where a1.month_id = a2.month_id and a1.month_id > '200800';
 month_id | cust_group_acc | mobile_no | month_id | cust_group_acc | mobile_no 
----------+----------------+-----------+----------+----------------+-----------
(0 rows)

select count_operator('select * from ds_4 a1,ds_4 a2 where a1.month_id = a2.month_id and a1.month_id > E''200800'';','Partition Selector');
 count_operator 
----------------
              1
(1 row)

-- CLEANUP
-- start_ignore
DROP TABLE IF EXISTS ds_4;
set optimizer_partition_selection_log=off;
reset optimizer_segments;
-- end_ignore
--
-- Test a hash agg that has a Sequence + Partition Selector below it.
--
-- SETUP
-- start_ignore
DROP TABLE IF EXISTS bar;
-- end_ignore
CREATE TABLE bar (b int, c int)
PARTITION BY RANGE (b)
(
  START (0) END (10),
  START (10) END (20)
);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'b' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
NOTICE:  CREATE TABLE will create partition "bar_1_prt_1" for table "bar"
NOTICE:  CREATE TABLE will create partition "bar_1_prt_2" for table "bar"
INSERT INTO bar SELECT g % 20, g % 20 from generate_series(1, 1000) g;
ANALYZE bar;
SELECT b FROM bar GROUP BY b;
 b  
----
  7
  4
 19
  3
  5
 18
  6
 11
  9
  8
 12
 10
 17
  1
  0
  2
 16
 15
 14
 13
(20 rows)

EXPLAIN SELECT b FROM bar GROUP BY b;
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=18.50..18.60 rows=10 width=4)
   ->  HashAggregate  (cost=18.50..18.60 rows=4 width=4)
         Group By: bfv_partition.bar.b
         ->  Append  (cost=0.00..16.00 rows=334 width=4)
               ->  Seq Scan on bar_1_prt_1 bar  (cost=0.00..8.00 rows=167 width=4)
               ->  Seq Scan on bar_1_prt_2 bar  (cost=0.00..8.00 rows=167 width=4)
 Optimizer status: legacy query optimizer
(7 rows)

-- CLEANUP
DROP TABLE IF EXISTS foo;
DROP TABLE IF EXISTS bar;
-- Test EXPLAIN ANALYZE on a partitioned table. There used to be a bug, where
-- you got an internal error with this, because the EXPLAIN ANALYZE sends the
-- stats from QEs to the QD at the end of query, but because the subnodes are
-- terminated earlier, their stats were already gone.
create table mpp8031 (oid integer,
odate timestamp without time zone,
cid integer)
PARTITION BY RANGE(odate)
(
PARTITION foo START ('2005-05-01 00:00:00'::timestamp
without time zone) END ('2005-07-01 00:00:00'::timestamp
without time zone) EVERY ('2 mons'::interval),
START ('2005-07-01 00:00:00'::timestamp without time zone)
END ('2006-01-01 00:00:00'::timestamp without time zone)
EVERY ('2 mons'::interval)
);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'oid' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
NOTICE:  CREATE TABLE will create partition "mpp8031_1_prt_foo" for table "mpp8031"
NOTICE:  CREATE TABLE will create partition "mpp8031_1_prt_2" for table "mpp8031"
NOTICE:  CREATE TABLE will create partition "mpp8031_1_prt_3" for table "mpp8031"
NOTICE:  CREATE TABLE will create partition "mpp8031_1_prt_4" for table "mpp8031"
explain analyze select a.* from mpp8031 a, mpp8031 b where a.oid = b.oid;
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=6799.00..1021796.00 rows=80883360 width=16)
   Rows out:  0 rows at destination with 1.011 ms to end, start offset by 0.365 ms.
   ->  Hash Join  (cost=6799.00..1021796.00 rows=26961120 width=16)
         Hash Cond: a.oid = b.oid
         ->  Append  (cost=0.00..3244.00 rows=94800 width=16) (never executed)
               ->  Seq Scan on mpp8031_1_prt_foo a  (cost=0.00..811.00 rows=23700 width=16) (never executed)
               ->  Seq Scan on mpp8031_1_prt_2 a  (cost=0.00..811.00 rows=23700 width=16) (never executed)
               ->  Seq Scan on mpp8031_1_prt_3 a  (cost=0.00..811.00 rows=23700 width=16) (never executed)
               ->  Seq Scan on mpp8031_1_prt_4 a  (cost=0.00..811.00 rows=23700 width=16) (never executed)
         ->  Hash  (cost=3244.00..3244.00 rows=94800 width=4) (never executed)
               ->  Append  (cost=0.00..3244.00 rows=94800 width=4) (never executed)
                     ->  Seq Scan on mpp8031_1_prt_foo b  (cost=0.00..811.00 rows=23700 width=4) (never executed)
                     ->  Seq Scan on mpp8031_1_prt_2 b  (cost=0.00..811.00 rows=23700 width=4) (never executed)
                     ->  Seq Scan on mpp8031_1_prt_3 b  (cost=0.00..811.00 rows=23700 width=4) (never executed)
                     ->  Seq Scan on mpp8031_1_prt_4 b  (cost=0.00..811.00 rows=23700 width=4) (never executed)
   (slice0)    Executor memory: 514K bytes.
   (slice1)    Executor memory: 142K bytes avg x 3 workers, 142K bytes max (seg0).
 Memory used:  128000kB
 Optimizer: legacy query optimizer
 Total runtime: 2.034 ms
(19 rows)

drop table mpp8031;
-- Partitioned tables with default partitions and indexes on all parts,
-- queries on them with a predicate on index column must not consider the scan
-- as partial and should not fallback.
CREATE TABLE part_tbl
(
	time_client_key numeric(16,0) NOT NULL,
	ngin_service_key numeric NOT NULL,
	profile_key numeric NOT NULL
)
DISTRIBUTED BY (time_client_key)
PARTITION BY RANGE(time_client_key)
SUBPARTITION BY LIST (ngin_service_key)
SUBPARTITION TEMPLATE
(
	SUBPARTITION Package5 VALUES (479534741),
	DEFAULT SUBPARTITION other_services
)
(
	PARTITION p20151110 START (2015111000::numeric) 
END (2015111100::numeric) WITH (appendonly=false)
);
NOTICE:  CREATE TABLE will create partition "part_tbl_1_prt_p20151110" for table "part_tbl"
NOTICE:  CREATE TABLE will create partition "part_tbl_1_prt_p20151110_2_prt_package5" for table "part_tbl_1_prt_p20151110"
NOTICE:  CREATE TABLE will create partition "part_tbl_1_prt_p20151110_2_prt_other_services" for table "part_tbl_1_prt_p20151110"
INSERT INTO part_tbl VALUES (2015111000, 479534741, 99999999);
INSERT INTO part_tbl VALUES (2015111000, 479534742, 99999999);
CREATE INDEX part_tbl_idx 
ON part_tbl(profile_key);
NOTICE:  building index for child partition "part_tbl_1_prt_p20151110"
NOTICE:  building index for child partition "part_tbl_1_prt_p20151110_2_prt_package5"
NOTICE:  building index for child partition "part_tbl_1_prt_p20151110_2_prt_other_services"
EXPLAIN SELECT * FROM part_tbl WHERE profile_key = 99999999;
                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..942.50 rows=60 width=80)
   ->  Append  (cost=0.00..942.50 rows=20 width=80)
         ->  Seq Scan on part_tbl_1_prt_p20151110_2_prt_package5 part_tbl  (cost=0.00..471.25 rows=10 width=80)
               Filter: profile_key = 99999999::numeric
         ->  Seq Scan on part_tbl_1_prt_p20151110_2_prt_other_services part_tbl  (cost=0.00..471.25 rows=10 width=80)
               Filter: profile_key = 99999999::numeric
 Settings:  optimizer=off
 Optimizer status: legacy query optimizer
(8 rows)

SELECT * FROM part_tbl WHERE profile_key = 99999999;
 time_client_key | ngin_service_key | profile_key 
-----------------+------------------+-------------
      2015111000 |        479534741 |    99999999
      2015111000 |        479534742 |    99999999
(2 rows)

DROP TABLE part_tbl;
--
-- Test partition elimination, MPP-7891
--
-- cleanup
-- start_ignore
drop table if exists r_part;
NOTICE:  table "r_part" does not exist, skipping
drop table if exists r_co;
NOTICE:  table "r_co" does not exist, skipping
deallocate f1;
ERROR:  prepared statement "f1" does not exist
deallocate f2;
ERROR:  prepared statement "f2" does not exist
deallocate f3;
ERROR:  prepared statement "f3" does not exist
-- end_ignore
create table r_part(a int, b int) partition by range(a) (start (1) end(10) every(1));
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
NOTICE:  CREATE TABLE will create partition "r_part_1_prt_1" for table "r_part"
NOTICE:  CREATE TABLE will create partition "r_part_1_prt_2" for table "r_part"
NOTICE:  CREATE TABLE will create partition "r_part_1_prt_3" for table "r_part"
NOTICE:  CREATE TABLE will create partition "r_part_1_prt_4" for table "r_part"
NOTICE:  CREATE TABLE will create partition "r_part_1_prt_5" for table "r_part"
NOTICE:  CREATE TABLE will create partition "r_part_1_prt_6" for table "r_part"
NOTICE:  CREATE TABLE will create partition "r_part_1_prt_7" for table "r_part"
NOTICE:  CREATE TABLE will create partition "r_part_1_prt_8" for table "r_part"
NOTICE:  CREATE TABLE will create partition "r_part_1_prt_9" for table "r_part"
create table r_co(a int, b int) with (orientation=column, appendonly=true) partition by range(a) (start (1) end(10) every(1)) ;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
NOTICE:  CREATE TABLE will create partition "r_co_1_prt_1" for table "r_co"
NOTICE:  CREATE TABLE will create partition "r_co_1_prt_2" for table "r_co"
NOTICE:  CREATE TABLE will create partition "r_co_1_prt_3" for table "r_co"
NOTICE:  CREATE TABLE will create partition "r_co_1_prt_4" for table "r_co"
NOTICE:  CREATE TABLE will create partition "r_co_1_prt_5" for table "r_co"
NOTICE:  CREATE TABLE will create partition "r_co_1_prt_6" for table "r_co"
NOTICE:  CREATE TABLE will create partition "r_co_1_prt_7" for table "r_co"
NOTICE:  CREATE TABLE will create partition "r_co_1_prt_8" for table "r_co"
NOTICE:  CREATE TABLE will create partition "r_co_1_prt_9" for table "r_co"
insert into r_part values (1,1), (2,2), (3,3);
select * from r_part order by a,b;
 a | b 
---+---
 1 | 1
 2 | 2
 3 | 3
(3 rows)

analyze r_part;
explain select * from r_part r1, r_part r2 where r1.a=1; -- should eliminate partitions in the r1 copy of r_part
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice2; segments: 3)  (cost=10000000000.00..10000000010.44 rows=9 width=16)
   ->  Nested Loop  (cost=10000000000.00..10000000010.44 rows=3 width=16)
         ->  Broadcast Motion 1:3  (slice1; segments: 1)  (cost=0.00..1.05 rows=3 width=8)
               ->  Append  (cost=0.00..1.01 rows=1 width=8)
                     ->  Seq Scan on r_part_1_prt_1 r1  (cost=0.00..1.01 rows=1 width=8)
                           Filter: a = 1
         ->  Materialize  (cost=0.00..9.08 rows=3 width=8)
               ->  Append  (cost=0.00..9.03 rows=3 width=8)
                     ->  Seq Scan on r_part_1_prt_1 r2  (cost=0.00..1.01 rows=1 width=8)
                     ->  Seq Scan on r_part_1_prt_2 r2  (cost=0.00..1.01 rows=1 width=8)
                     ->  Seq Scan on r_part_1_prt_3 r2  (cost=0.00..1.01 rows=1 width=8)
                     ->  Seq Scan on r_part_1_prt_4 r2  (cost=0.00..1.00 rows=1 width=8)
                     ->  Seq Scan on r_part_1_prt_5 r2  (cost=0.00..1.00 rows=1 width=8)
                     ->  Seq Scan on r_part_1_prt_6 r2  (cost=0.00..1.00 rows=1 width=8)
                     ->  Seq Scan on r_part_1_prt_7 r2  (cost=0.00..1.00 rows=1 width=8)
                     ->  Seq Scan on r_part_1_prt_8 r2  (cost=0.00..1.00 rows=1 width=8)
                     ->  Seq Scan on r_part_1_prt_9 r2  (cost=0.00..1.00 rows=1 width=8)
 Optimizer: legacy query optimizer
(19 rows)

explain select * from r_part where a in (1,2); -- should eliminate partitions
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Gather Motion 1:1  (slice1; segments: 1)  (cost=0.00..2.02 rows=2 width=8)
   ->  Append  (cost=0.00..2.02 rows=1 width=8)
         ->  Seq Scan on r_part_1_prt_1 r_part  (cost=0.00..1.01 rows=1 width=8)
               Filter: a = ANY ('{1,2}'::integer[])
         ->  Seq Scan on r_part_1_prt_2 r_part  (cost=0.00..1.01 rows=1 width=8)
               Filter: a = ANY ('{1,2}'::integer[])
 Settings:  optimizer=off
 Optimizer status: legacy query optimizer
(8 rows)

-- Test partition elimination in prepared statements
prepare f1(int) as select * from r_part where a = 1 order by a,b; 
prepare f2(int) as select * from r_part where a = $1 order by a,b;
execute f1(1); 
 a | b 
---+---
 1 | 1
(1 row)

execute f2(1); 
 a | b 
---+---
 1 | 1
(1 row)

execute f2(2); 
 a | b 
---+---
 2 | 2
(1 row)

explain select * from r_part where a = 1 order by a,b; -- should eliminate partitions
                                      QUERY PLAN                                       
---------------------------------------------------------------------------------------
 Gather Motion 1:1  (slice1; segments: 1)  (cost=1.02..1.03 rows=1 width=8)
   Merge Key: bfv_partition.r_part.b
   ->  Sort  (cost=1.02..1.03 rows=1 width=8)
         Sort Key: bfv_partition.r_part.b
         ->  Append  (cost=0.00..1.01 rows=1 width=8)
               ->  Seq Scan on r_part_1_prt_1 r_part  (cost=0.00..1.01 rows=1 width=8)
                     Filter: a = 1
 Settings:  optimizer=off
 Optimizer status: legacy query optimizer
(9 rows)

--force_explain
explain execute f1(1); -- should eliminate partitions 
                                      QUERY PLAN                                       
---------------------------------------------------------------------------------------
 Gather Motion 1:1  (slice1; segments: 1)  (cost=1.02..1.03 rows=1 width=8)
   Merge Key: bfv_partition.r_part.b
   ->  Sort  (cost=1.02..1.03 rows=1 width=8)
         Sort Key: bfv_partition.r_part.b
         ->  Append  (cost=0.00..1.01 rows=1 width=8)
               ->  Seq Scan on r_part_1_prt_1 r_part  (cost=0.00..1.01 rows=1 width=8)
                     Filter: a = 1
 Settings:  optimizer=off
 Optimizer status: legacy query optimizer
(9 rows)

--force_explain
explain execute f2(2); -- should eliminate partitions
                                      QUERY PLAN                                       
---------------------------------------------------------------------------------------
 Gather Motion 1:1  (slice1; segments: 1)  (cost=1.02..1.03 rows=1 width=8)
   Merge Key: bfv_partition.r_part.b
   ->  Sort  (cost=1.02..1.03 rows=1 width=8)
         Sort Key: bfv_partition.r_part.b
         ->  Append  (cost=0.00..1.01 rows=1 width=8)
               ->  Seq Scan on r_part_1_prt_2 r_part  (cost=0.00..1.01 rows=1 width=8)
                     Filter: a = 2
 Settings:  optimizer=off
 Optimizer status: legacy query optimizer
(9 rows)

-- Test partition elimination on CO tables
insert into r_co values (1,1), (2,2), (3,3);
analyze r_co; 
explain select * from r_co where a=2; -- should eliminate partitions
                                          QUERY PLAN                                          
----------------------------------------------------------------------------------------------
 Gather Motion 1:1  (slice1; segments: 1)  (cost=0.00..1.01 rows=1 width=8)
   ->  Append  (cost=0.00..1.01 rows=1 width=8)
         ->  Append-only Columnar Scan on r_co_1_prt_2 r_co  (cost=0.00..1.01 rows=1 width=8)
               Filter: a = 2
 Settings:  optimizer=off
 Optimizer status: legacy query optimizer
(6 rows)

-- test partition elimination in prepared statements on CO tables
prepare f3(int) as select * from r_co where a = $1 order by a,b; 
--force_explain
explain execute f3(2); -- should eliminate partitions
                                             QUERY PLAN                                             
----------------------------------------------------------------------------------------------------
 Gather Motion 1:1  (slice1; segments: 1)  (cost=1.02..1.03 rows=1 width=8)
   Merge Key: bfv_partition.r_co.b
   ->  Sort  (cost=1.02..1.03 rows=1 width=8)
         Sort Key: bfv_partition.r_co.b
         ->  Append  (cost=0.00..1.01 rows=1 width=8)
               ->  Append-only Columnar Scan on r_co_1_prt_2 r_co  (cost=0.00..1.01 rows=1 width=8)
                     Filter: a = 2
 Settings:  optimizer=off
 Optimizer status: legacy query optimizer
(9 rows)

-- start_ignore
drop table r_part;
drop table r_co;
deallocate f1;
deallocate f2;
deallocate f3;
-- end_ignore
--
-- Test partition elimination, MPP-7891
--
-- start_ignore
drop table if exists fact;
NOTICE:  table "fact" does not exist, skipping
deallocate f1;
ERROR:  prepared statement "f1" does not exist
create table fact(x int, dd date, dt text) distributed by (x) partition by range (dd) ( start('2008-01-01') end ('2320-01-01') every(interval '100 years'));
NOTICE:  CREATE TABLE will create partition "fact_1_prt_1" for table "fact"
NOTICE:  CREATE TABLE will create partition "fact_1_prt_2" for table "fact"
NOTICE:  CREATE TABLE will create partition "fact_1_prt_3" for table "fact"
NOTICE:  CREATE TABLE will create partition "fact_1_prt_4" for table "fact"
-- end_ignore
analyze fact;
select '2009-01-02'::date = to_date('2009-01-02','YYYY-MM-DD'); -- ensure that both are in fact equal
 ?column? 
----------
 t
(1 row)

explain select * from fact where dd < '2009-01-02'::date; -- partitions eliminated
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..1.00 rows=1 width=40)
   ->  Append  (cost=0.00..1.00 rows=1 width=40)
         ->  Seq Scan on fact_1_prt_1 fact  (cost=0.00..1.00 rows=1 width=40)
               Filter: dd < '01-02-2009'::date
 Settings:  optimizer=off
 Optimizer status: legacy query optimizer
(6 rows)

explain select * from fact where dd < to_date('2009-01-02','YYYY-MM-DD'); -- partitions eliminated
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..1.00 rows=1 width=40)
   ->  Append  (cost=0.00..1.00 rows=1 width=40)
         ->  Seq Scan on fact_1_prt_1 fact  (cost=0.00..1.00 rows=1 width=40)
               Filter: dd < '01-02-2009'::date
 Settings:  optimizer=off
 Optimizer status: legacy query optimizer
(6 rows)

explain select * from fact where dd < current_date; --partitions eliminated
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..1.00 rows=1 width=40)
   ->  Append  (cost=0.00..1.00 rows=1 width=40)
         ->  Seq Scan on fact_1_prt_1 fact  (cost=0.00..1.00 rows=1 width=40)
               Filter: dd < '10-22-2017'::date
 Settings:  optimizer=off
 Optimizer status: legacy query optimizer
(6 rows)

-- Test partition elimination in prepared statements
prepare f1(date) as select * from fact where dd < $1;
-- force_explain
explain execute f1('2009-01-02'::date); -- should eliminate partitions
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..1.00 rows=1 width=40)
   ->  Append  (cost=0.00..1.00 rows=1 width=40)
         ->  Seq Scan on fact_1_prt_1 fact  (cost=0.00..1.00 rows=1 width=40)
               Filter: dd < '01-02-2009'::date
 Settings:  optimizer=off
 Optimizer status: legacy query optimizer
(6 rows)

-- force_explain
explain execute f1(to_date('2009-01-02', 'YYYY-MM-DD')); -- should eliminate partitions
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..1.00 rows=1 width=40)
   ->  Append  (cost=0.00..1.00 rows=1 width=40)
         ->  Seq Scan on fact_1_prt_1 fact  (cost=0.00..1.00 rows=1 width=40)
               Filter: dd < '01-02-2009'::date
 Settings:  optimizer=off
 Optimizer status: legacy query optimizer
(6 rows)

-- start_ignore
drop table fact;
deallocate f1;
-- end_ignore
-- MPP-6247
-- Delete Using on partitioned table causes repetitive scans on using table	
create table mpp6247_foo ( c1 int, dt date ) distributed by ( c1 ) partition by range (dt) ( start ( date '2009-05-01' ) end ( date '2009-05-11' ) every ( interval '1 day' ) );
NOTICE:  CREATE TABLE will create partition "mpp6247_foo_1_prt_1" for table "mpp6247_foo"
NOTICE:  CREATE TABLE will create partition "mpp6247_foo_1_prt_2" for table "mpp6247_foo"
NOTICE:  CREATE TABLE will create partition "mpp6247_foo_1_prt_3" for table "mpp6247_foo"
NOTICE:  CREATE TABLE will create partition "mpp6247_foo_1_prt_4" for table "mpp6247_foo"
NOTICE:  CREATE TABLE will create partition "mpp6247_foo_1_prt_5" for table "mpp6247_foo"
NOTICE:  CREATE TABLE will create partition "mpp6247_foo_1_prt_6" for table "mpp6247_foo"
NOTICE:  CREATE TABLE will create partition "mpp6247_foo_1_prt_7" for table "mpp6247_foo"
NOTICE:  CREATE TABLE will create partition "mpp6247_foo_1_prt_8" for table "mpp6247_foo"
NOTICE:  CREATE TABLE will create partition "mpp6247_foo_1_prt_9" for table "mpp6247_foo"
NOTICE:  CREATE TABLE will create partition "mpp6247_foo_1_prt_10" for table "mpp6247_foo"
create table mpp6247_bar (like mpp6247_foo);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause, defaulting to distribution columns from LIKE table
-- EXPECT: Single HJ after partition elimination instead of sequence of HJ under Append
select count_operator('delete from mpp6247_foo using mpp6247_bar where mpp6247_foo.c1 = mpp6247_bar.c1 and mpp6247_foo.dt = ''2009-05-03''', 'Hash Join');
 count_operator 
----------------
              1
(1 row)

drop table mpp6247_bar;
drop table mpp6247_foo;
-- CLEANUP
-- start_ignore
drop schema if exists bfv_partition_plans cascade;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to function count_operator(text,text)
drop cascades to function find_operator(text,text)
-- end_ignore