bfv_partition_plans.out

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 plpython3u;
ERROR:  language "plpython3u" 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 plpython3u;
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 plpython3u;
-- 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.
insert into mpp3061 values(1);
update mpp3061 set i = 2 where i = 1;
select tableoid::regclass, * from mpp3061 where i = 2;
    tableoid     | i 
-----------------+---
 mpp3061_1_prt_2 | 2
(1 row)

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')
    );
    
-- 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.
create index index_mpp23195_t1_i on mpp23195_t1(i);
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.
create index index_1 on mpp21834_t1(i);
create index index_2 on mpp21834_t1(j);
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
drop index index_2;
drop index index_1;
drop table if exists mpp21834_t2;
drop table if exists mpp21834_t1;
reset optimizer_enable_hashjoin;
--
-- 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(6) END (10) INCLUSIVE,
      PARTITION plast   START (11)
  );
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 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 Seq 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 Seq 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 Seq 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
          )
;
create index pt1_idx on mpp24151_pt using btree (pt1);
create index ptid_idx on mpp24151_pt using btree (ptid);
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;
-- GPDB_12_MERGE_FIXME: With the big refactoring t how Partition Selectors are
-- implemented during the v12 merge, I'm not sure if this test is testing anything
-- useful anymore. And/or it redundant with the tests in 'dpe'?
select count_operator('select * from mpp24151_t, mpp24151_pt where tid = ptid and pt1 = E''hello0'';','->  Partition Selector');
 count_operator 
----------------
              1
(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
drop index ptid_idx;
drop index pt1_idx;
drop table if exists mpp24151_t;
drop table if exists mpp24151_pt;
reset optimizer_enable_dynamictablescan;
--
-- 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.
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.
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.
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 
----------------
              2
(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.
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.
create index dis_index on dis(c3);
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.
create index dbs_index on dbs using bitmap(c3);
-- 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 Seq 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 Heap Scan');
 find_operator 
---------------
 ['false']
(1 row)

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

-- CLEANUP
drop index dbs_index;
drop table if exists dbs;
drop index dis_index;
drop table if exists dis;
drop table if exists dts;
reset optimizer_enable_dynamictablescan;
--
-- 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.
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')
);
-- 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 
----------------
              0
(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.
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 Key: bar_1_prt_1.b
         ->  Append  (cost=0.00..16.00 rows=334 width=4)
               ->  Seq Scan on bar_1_prt_1  (cost=0.00..8.00 rows=167 width=4)
               ->  Seq Scan on bar_1_prt_2  (cost=0.00..8.00 rows=167 width=4)
 Optimizer: Postgres 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.
explain analyze select a.* from mpp8031 a, mpp8031 b where a.oid = b.oid;
                                                               QUERY PLAN                                                               
----------------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=8221.00..12550518.80 rows=80883360 width=16) (actual time=0.756..0.756 rows=0 loops=1)
   ->  Hash Join  (cost=8221.00..10932851.60 rows=26961120 width=16) (never executed)
         Hash Cond: (a.oid = b.oid)
         ->  Append  (cost=0.00..4666.00 rows=94800 width=16) (never executed)
               ->  Seq Scan on mpp8031_1_prt_foo_1 a  (cost=0.00..811.00 rows=23700 width=16) (never executed)
               ->  Seq Scan on mpp8031_1_prt_2 a_1  (cost=0.00..811.00 rows=23700 width=16) (never executed)
               ->  Seq Scan on mpp8031_1_prt_3 a_2  (cost=0.00..811.00 rows=23700 width=16) (never executed)
               ->  Seq Scan on mpp8031_1_prt_4 a_3  (cost=0.00..811.00 rows=23700 width=16) (never executed)
         ->  Hash  (cost=4666.00..4666.00 rows=94800 width=4) (never executed)
               ->  Append  (cost=0.00..4666.00 rows=94800 width=4) (never executed)
                     ->  Seq Scan on mpp8031_1_prt_foo_1 b  (cost=0.00..811.00 rows=23700 width=4) (never executed)
                     ->  Seq Scan on mpp8031_1_prt_2 b_1  (cost=0.00..811.00 rows=23700 width=4) (never executed)
                     ->  Seq Scan on mpp8031_1_prt_3 b_2  (cost=0.00..811.00 rows=23700 width=4) (never executed)
                     ->  Seq Scan on mpp8031_1_prt_4 b_3  (cost=0.00..811.00 rows=23700 width=4) (never executed)
 Planning Time: 2.439 ms
   (slice0)    Executor memory: 51K bytes.
   (slice1)    Executor memory: 57K bytes avg x 3 workers, 57K bytes max (seg0).
 Memory used:  128000kB
 Optimizer: Postgres query optimizer
 Execution Time: 1.481 ms
(20 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)
);
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);
EXPLAIN SELECT * FROM part_tbl WHERE profile_key = 99999999;
                                                QUERY PLAN                                                
----------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..2.02 rows=2 width=25)
   ->  Append  (cost=0.00..2.02 rows=1 width=25)
         ->  Seq Scan on part_tbl_1_prt_p20151110_2_prt_package5  (cost=0.00..1.01 rows=1 width=25)
               Filter: profile_key = 99999999::numeric
         ->  Seq Scan on part_tbl_1_prt_p20151110_2_prt_other_services  (cost=0.00..1.01 rows=1 width=25)
               Filter: profile_key = 99999999::numeric
 Optimizer: Postgres query optimizer
(7 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.
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.
insert into r_part values (1,1), (2,2), (3,3), (4,4), (5,5), (6,6), (7,7), (8,8);
-- following tests rely on the data distribution, verify them
select gp_segment_id, * from r_part order by a,b;
 gp_segment_id | a | b 
---------------+---+---
             1 | 1 | 1
             0 | 2 | 2
             0 | 3 | 3
             0 | 4 | 4
             2 | 5 | 5
             2 | 6 | 6
             0 | 7 | 7
             0 | 8 | 8
(8 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  (slice1; segments: 3)  (cost=10000000000.00..10000000010.38 rows=9 width=16)
   ->  Nested Loop  (cost=10000000000.00..10000000010.26 rows=3 width=16)
         ->  Broadcast Motion 1:3  (slice2; segments: 1)  (cost=0.00..1.03 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)
         ->  Append  (cost=0.00..9.13 rows=9 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_1  (cost=0.00..1.01 rows=1 width=8)
               ->  Seq Scan on r_part_1_prt_3 r2_2  (cost=0.00..1.01 rows=1 width=8)
               ->  Seq Scan on r_part_1_prt_4 r2_3  (cost=0.00..1.01 rows=1 width=8)
               ->  Seq Scan on r_part_1_prt_5 r2_4  (cost=0.00..1.01 rows=1 width=8)
               ->  Seq Scan on r_part_1_prt_6 r2_5  (cost=0.00..1.01 rows=1 width=8)
               ->  Seq Scan on r_part_1_prt_7 r2_6  (cost=0.00..1.01 rows=1 width=8)
               ->  Seq Scan on r_part_1_prt_8 r2_7  (cost=0.00..1.01 rows=1 width=8)
               ->  Seq Scan on r_part_1_prt_9 r2_8  (cost=0.00..1.01 rows=1 width=8)
 Optimizer: Postgres query optimizer
(16 rows)

-- the numbers in the filter should be both on segment 0
explain select * from r_part where a in (7,8); -- 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_7  (cost=0.00..1.01 rows=1 width=8)
               Filter: a = ANY ('{7,8}'::integer[])
         ->  Seq Scan on r_part_1_prt_8  (cost=0.00..1.01 rows=1 width=8)
               Filter: a = ANY ('{7,8}'::integer[])
 Optimizer: Postgres query optimizer
(7 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.05 rows=1 width=8)
   Merge Key: r_part_1_prt_1.b
   ->  Sort  (cost=1.02..1.03 rows=1 width=8)
         Sort Key: r_part_1_prt_1.b
         ->  Seq Scan on r_part_1_prt_1  (cost=0.00..1.01 rows=1 width=8)
               Filter: (a = 1)
 Optimizer: Postgres query optimizer
(7 rows)

--force_explain
explain execute f1(1); -- should eliminate partitions 
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Gather Motion 1:1  (slice1; segments: 1)  (cost=1.02..1.05 rows=1 width=8)
   Merge Key: r_part_1_prt_1.b
   ->  Sort  (cost=1.02..1.03 rows=1 width=8)
         Sort Key: r_part_1_prt_1.b
         ->  Seq Scan on r_part_1_prt_1  (cost=0.00..1.01 rows=1 width=8)
               Filter: (a = 1)
 Optimizer: Postgres query optimizer
(7 rows)

--force_explain
explain execute f2(2); -- should eliminate partitions
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Gather Motion 1:1  (slice1; segments: 1)  (cost=1.02..1.05 rows=1 width=8)
   Merge Key: r_part_1_prt_2.b
   ->  Sort  (cost=1.02..1.03 rows=1 width=8)
         Sort Key: r_part_1_prt_2.b
         ->  Seq Scan on r_part_1_prt_2  (cost=0.00..1.01 rows=1 width=8)
               Filter: (a = 2)
 Optimizer: Postgres query optimizer
(7 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.03 rows=1 width=8)
   ->  Seq Scan on r_co_1_prt_2  (cost=0.00..1.01 rows=1 width=8)
         Filter: (a = 2)
 Optimizer: Postgres query optimizer
(4 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.05 rows=1 width=8)
   Merge Key: r_co_1_prt_2.b
   ->  Sort  (cost=1.02..1.03 rows=1 width=8)
         Sort Key: r_co_1_prt_2.b
         ->  Seq Scan on r_co_1_prt_2  (cost=0.00..1.01 rows=1 width=8)
               Filter: (a = 2)
 Optimizer: Postgres query optimizer
(7 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'));
-- 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.02 rows=1 width=40)
   ->  Seq Scan on fact_1_prt_1  (cost=0.00..1.00 rows=1 width=40)
         Filter: (dd < '01-02-2009'::date)
 Optimizer: Postgres query optimizer
(4 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.02 rows=1 width=40)
   ->  Seq Scan on fact_1_prt_1  (cost=0.00..1.00 rows=1 width=40)
         Filter: (dd < '01-02-2009'::date)
 Optimizer: Postgres query optimizer
(4 rows)

explain select * from fact where dd < current_date; --partitions eliminated
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..4.10 rows=4 width=40)
   ->  Append  (cost=0.00..4.02 rows=2 width=40)
         Subplans Removed: 3
         ->  Seq Scan on fact_1_prt_1  (cost=0.00..1.00 rows=1 width=40)
               Filter: (dd < CURRENT_DATE)
 Optimizer: Postgres 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.02 rows=1 width=40)
   ->  Seq Scan on fact_1_prt_1  (cost=0.00..1.00 rows=1 width=40)
         Filter: (dd < '01-02-2009'::date)
 Optimizer: Postgres query optimizer
(4 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.02 rows=1 width=40)
   ->  Seq Scan on fact_1_prt_1  (cost=0.00..1.00 rows=1 width=40)
         Filter: (dd < '01-02-2009'::date)
 Optimizer: Postgres query optimizer
(4 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' ) );
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