bfv_partition.out

create schema bfv_partition;
set search_path=bfv_partition;
--
-- 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;
--
-- Tests if it produces SIGSEGV from "select from partition_table group by rollup or cube function"
--
-- SETUP
-- start_ignore
drop table if exists mpp7980;
NOTICE:  table "mpp7980" does not exist, skipping
-- end_ignore
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
-- start_ignore
drop table mpp7980;
-- end_ignore
--
-- Tests if it is using casting comparator for partition selector with compatible types
--
-- SETUP
CREATE TABLE TIMESTAMP_MONTH_rangep_STARTINCL (i1 int, f2 timestamp)
partition by range (f2)
(
  start ('2000-01-01'::timestamp) INCLUSIVE
  end (date '2001-01-01'::timestamp) EXCLUSIVE
  every ('1 month'::interval)
);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i1' 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 "timestamp_month_rangep_startincl_1_prt_1" for table "timestamp_month_rangep_startincl"
NOTICE:  CREATE TABLE will create partition "timestamp_month_rangep_startincl_1_prt_2" for table "timestamp_month_rangep_startincl"
NOTICE:  CREATE TABLE will create partition "timestamp_month_rangep_startincl_1_prt_3" for table "timestamp_month_rangep_startincl"
NOTICE:  CREATE TABLE will create partition "timestamp_month_rangep_startincl_1_prt_4" for table "timestamp_month_rangep_startincl"
NOTICE:  CREATE TABLE will create partition "timestamp_month_rangep_startincl_1_prt_5" for table "timestamp_month_rangep_startincl"
NOTICE:  CREATE TABLE will create partition "timestamp_month_rangep_startincl_1_prt_6" for table "timestamp_month_rangep_startincl"
NOTICE:  CREATE TABLE will create partition "timestamp_month_rangep_startincl_1_prt_7" for table "timestamp_month_rangep_startincl"
NOTICE:  CREATE TABLE will create partition "timestamp_month_rangep_startincl_1_prt_8" for table "timestamp_month_rangep_startincl"
NOTICE:  CREATE TABLE will create partition "timestamp_month_rangep_startincl_1_prt_9" for table "timestamp_month_rangep_startincl"
NOTICE:  CREATE TABLE will create partition "timestamp_month_rangep_startincl_1_prt_10" for table "timestamp_month_rangep_startincl"
NOTICE:  CREATE TABLE will create partition "timestamp_month_rangep_startincl_1_prt_11" for table "timestamp_month_rangep_startincl"
NOTICE:  CREATE TABLE will create partition "timestamp_month_rangep_startincl_1_prt_12" for table "timestamp_month_rangep_startincl"
CREATE TABLE TIMESTAMP_MONTH_rangep_STARTEXCL (i1 int, f2 timestamp)
partition by range (f2)
(
  start ('2000-01-01'::timestamp) EXCLUSIVE
  end (date '2001-01-01'::timestamp) INCLUSIVE
  every ('1 month'::interval)
);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i1' 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 "timestamp_month_rangep_startexcl_1_prt_1" for table "timestamp_month_rangep_startexcl"
NOTICE:  CREATE TABLE will create partition "timestamp_month_rangep_startexcl_1_prt_2" for table "timestamp_month_rangep_startexcl"
NOTICE:  CREATE TABLE will create partition "timestamp_month_rangep_startexcl_1_prt_3" for table "timestamp_month_rangep_startexcl"
NOTICE:  CREATE TABLE will create partition "timestamp_month_rangep_startexcl_1_prt_4" for table "timestamp_month_rangep_startexcl"
NOTICE:  CREATE TABLE will create partition "timestamp_month_rangep_startexcl_1_prt_5" for table "timestamp_month_rangep_startexcl"
NOTICE:  CREATE TABLE will create partition "timestamp_month_rangep_startexcl_1_prt_6" for table "timestamp_month_rangep_startexcl"
NOTICE:  CREATE TABLE will create partition "timestamp_month_rangep_startexcl_1_prt_7" for table "timestamp_month_rangep_startexcl"
NOTICE:  CREATE TABLE will create partition "timestamp_month_rangep_startexcl_1_prt_8" for table "timestamp_month_rangep_startexcl"
NOTICE:  CREATE TABLE will create partition "timestamp_month_rangep_startexcl_1_prt_9" for table "timestamp_month_rangep_startexcl"
NOTICE:  CREATE TABLE will create partition "timestamp_month_rangep_startexcl_1_prt_10" for table "timestamp_month_rangep_startexcl"
NOTICE:  CREATE TABLE will create partition "timestamp_month_rangep_startexcl_1_prt_11" for table "timestamp_month_rangep_startexcl"
NOTICE:  CREATE TABLE will create partition "timestamp_month_rangep_startexcl_1_prt_12" for table "timestamp_month_rangep_startexcl"
CREATE TABLE TIMESTAMP_MONTH_listp (i1 int, f2 timestamp)
partition by list (f2)
(
  partition jan1 values ('2000-01-01'::timestamp),
  partition jan2 values ('2000-01-02'::timestamp),
  partition jan3 values ('2000-01-03'::timestamp),
  partition jan4 values ('2000-01-04'::timestamp),
  partition jan5 values ('2000-01-05'::timestamp)
);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i1' 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 "timestamp_month_listp_1_prt_jan1" for table "timestamp_month_listp"
NOTICE:  CREATE TABLE will create partition "timestamp_month_listp_1_prt_jan2" for table "timestamp_month_listp"
NOTICE:  CREATE TABLE will create partition "timestamp_month_listp_1_prt_jan3" for table "timestamp_month_listp"
NOTICE:  CREATE TABLE will create partition "timestamp_month_listp_1_prt_jan4" for table "timestamp_month_listp"
NOTICE:  CREATE TABLE will create partition "timestamp_month_listp_1_prt_jan5" for table "timestamp_month_listp"
-- TEST
-- Middle of a middle range
INSERT INTO TIMESTAMP_MONTH_rangep_STARTINCL values (1, '2000-07-16');
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = '2000-07-16';
 i1 |            f2            
----+--------------------------
  1 | Sun Jul 16 00:00:00 2000
(1 row)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_TIMESTAMP('2000-07-16', 'YYYY-MM-DD');
 i1 |            f2            
----+--------------------------
  1 | Sun Jul 16 00:00:00 2000
(1 row)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_DATE('2000-07-16', 'YYYY-MM-DD');
 i1 |            f2            
----+--------------------------
  1 | Sun Jul 16 00:00:00 2000
(1 row)

-- Beginning of the first range
INSERT INTO TIMESTAMP_MONTH_rangep_STARTINCL values (2, '2000-01-01');
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = '2000-01-01';
 i1 |            f2            
----+--------------------------
  2 | Sat Jan 01 00:00:00 2000
(1 row)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_TIMESTAMP('2000-01-01', 'YYYY-MM-DD');
 i1 |            f2            
----+--------------------------
  2 | Sat Jan 01 00:00:00 2000
(1 row)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_DATE('2000-01-01', 'YYYY-MM-DD');
 i1 |            f2            
----+--------------------------
  2 | Sat Jan 01 00:00:00 2000
(1 row)

INSERT INTO TIMESTAMP_MONTH_rangep_STARTINCL values (3, '2000-01-02');
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = '2000-01-02';
 i1 |            f2            
----+--------------------------
  3 | Sun Jan 02 00:00:00 2000
(1 row)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_TIMESTAMP('2000-01-02', 'YYYY-MM-DD');
 i1 |            f2            
----+--------------------------
  3 | Sun Jan 02 00:00:00 2000
(1 row)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_DATE('2000-01-02', 'YYYY-MM-DD');
 i1 |            f2            
----+--------------------------
  3 | Sun Jan 02 00:00:00 2000
(1 row)

-- End of the last range
INSERT INTO TIMESTAMP_MONTH_rangep_STARTINCL values (4, '2000-12-31');
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = '2000-12-31';
 i1 |            f2            
----+--------------------------
  4 | Sun Dec 31 00:00:00 2000
(1 row)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_TIMESTAMP('2000-12-31', 'YYYY-MM-DD');
 i1 |            f2            
----+--------------------------
  4 | Sun Dec 31 00:00:00 2000
(1 row)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_DATE('2000-12-31', 'YYYY-MM-DD');
 i1 |            f2            
----+--------------------------
  4 | Sun Dec 31 00:00:00 2000
(1 row)

INSERT INTO TIMESTAMP_MONTH_rangep_STARTINCL values (5, '2001-01-01'); -- should fail, no such partition
ERROR:  no partition for partitioning key  (seg1 office-4-160.pa.pivotal.io:25433 pid=9457)
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = '2001-01-01';
 i1 | f2 
----+----
(0 rows)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_TIMESTAMP('2001-01-01', 'YYYY-MM-DD');
 i1 | f2 
----+----
(0 rows)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_DATE('2001-01-01', 'YYYY-MM-DD');
 i1 | f2 
----+----
(0 rows)

-- Range partitioning: START EXCLUSIVE, END INCLUSIVE
-- Middle of a middle range
INSERT INTO TIMESTAMP_MONTH_rangep_STARTEXCL values (1, '2000-07-16');
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = '2000-07-16';
 i1 |            f2            
----+--------------------------
  1 | Sun Jul 16 00:00:00 2000
(1 row)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_TIMESTAMP('2000-07-16', 'YYYY-MM-DD');
 i1 |            f2            
----+--------------------------
  1 | Sun Jul 16 00:00:00 2000
(1 row)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_DATE('2000-07-16', 'YYYY-MM-DD');
 i1 |            f2            
----+--------------------------
  1 | Sun Jul 16 00:00:00 2000
(1 row)

-- Beginning of the first range
INSERT INTO TIMESTAMP_MONTH_rangep_STARTEXCL values (2, '2000-01-01'); -- should fail, no such partition
ERROR:  no partition for partitioning key  (seg0 office-4-160.pa.pivotal.io:25432 pid=9456)
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = '2000-01-01';
 i1 | f2 
----+----
(0 rows)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_TIMESTAMP('2000-01-01', 'YYYY-MM-DD');
 i1 | f2 
----+----
(0 rows)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_DATE('2000-01-01', 'YYYY-MM-DD');
 i1 | f2 
----+----
(0 rows)

INSERT INTO TIMESTAMP_MONTH_rangep_STARTEXCL values (3, '2000-01-02');
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = '2000-01-02';
 i1 |            f2            
----+--------------------------
  3 | Sun Jan 02 00:00:00 2000
(1 row)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_TIMESTAMP('2000-01-02', 'YYYY-MM-DD');
 i1 |            f2            
----+--------------------------
  3 | Sun Jan 02 00:00:00 2000
(1 row)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_DATE('2000-01-02', 'YYYY-MM-DD');
 i1 |            f2            
----+--------------------------
  3 | Sun Jan 02 00:00:00 2000
(1 row)

-- End of the last range
INSERT INTO TIMESTAMP_MONTH_rangep_STARTEXCL values (4, '2000-12-31');
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = '2000-12-31';
 i1 |            f2            
----+--------------------------
  4 | Sun Dec 31 00:00:00 2000
(1 row)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_TIMESTAMP('2000-12-31', 'YYYY-MM-DD');
 i1 |            f2            
----+--------------------------
  4 | Sun Dec 31 00:00:00 2000
(1 row)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_DATE('2000-12-31', 'YYYY-MM-DD');
 i1 |            f2            
----+--------------------------
  4 | Sun Dec 31 00:00:00 2000
(1 row)

INSERT INTO TIMESTAMP_MONTH_rangep_STARTEXCL values (5, '2001-01-01');
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = '2001-01-01';
 i1 |            f2            
----+--------------------------
  5 | Mon Jan 01 00:00:00 2001
(1 row)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_TIMESTAMP('2001-01-01', 'YYYY-MM-DD');
 i1 |            f2            
----+--------------------------
  5 | Mon Jan 01 00:00:00 2001
(1 row)

SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_DATE('2001-01-01', 'YYYY-MM-DD');
 i1 |            f2            
----+--------------------------
  5 | Mon Jan 01 00:00:00 2001
(1 row)

-- List partitioning
INSERT INTO TIMESTAMP_MONTH_listp values (1, '2000-01-03');
SELECT * FROM TIMESTAMP_MONTH_listp WHERE f2 = '2000-01-03';
 i1 |            f2            
----+--------------------------
  1 | Mon Jan 03 00:00:00 2000
(1 row)

SELECT * FROM TIMESTAMP_MONTH_listp WHERE f2 = TO_TIMESTAMP('2000-01-03', 'YYYY-MM-DD');
 i1 |            f2            
----+--------------------------
  1 | Mon Jan 03 00:00:00 2000
(1 row)

SELECT * FROM TIMESTAMP_MONTH_listp WHERE f2 = TO_DATE('2000-01-03', 'YYYY-MM-DD');
 i1 |            f2            
----+--------------------------
  1 | Mon Jan 03 00:00:00 2000
(1 row)

-- CLEANUP
-- start_ignore
DROP TABLE TIMESTAMP_MONTH_listp;
DROP TABLE TIMESTAMP_MONTH_rangep_STARTEXCL;
DROP TABLE TIMESTAMP_MONTH_rangep_STARTINCL;
-- end_ignore
--
-- Data Engineer can see partition key in psql
--
-- SETUP
-- start_ignore
DROP TABLE IF EXISTS T26002_T1;
NOTICE:  table "t26002_t1" does not exist, skipping
DROP TABLE IF EXISTS T26002_T2;
NOTICE:  table "t26002_t2" does not exist, skipping
-- end_ignore
CREATE TABLE T26002_T1 (empid int, departmentid int, year int, region varchar(20))
DISTRIBUTED BY (empid)
  PARTITION BY RANGE (year)
  SUBPARTITION BY LIST (region, departmentid)
    SUBPARTITION TEMPLATE (
       SUBPARTITION usa VALUES (('usa', 1)),
       SUBPARTITION europe VALUES (('europe', 2)),
       SUBPARTITION asia VALUES (('asia', 3)),
       DEFAULT SUBPARTITION other_regions)
( START (2012) END (2015) EVERY (3),
  DEFAULT PARTITION outlying_years);
NOTICE:  CREATE TABLE will create partition "t26002_t1_1_prt_outlying_years" for table "t26002_t1"
NOTICE:  CREATE TABLE will create partition "t26002_t1_1_prt_2" for table "t26002_t1"
NOTICE:  CREATE TABLE will create partition "t26002_t1_1_prt_outlying_years_2_prt_usa" for table "t26002_t1_1_prt_outlying_years"
NOTICE:  CREATE TABLE will create partition "t26002_t1_1_prt_outlying_years_2_prt_europe" for table "t26002_t1_1_prt_outlying_years"
NOTICE:  CREATE TABLE will create partition "t26002_t1_1_prt_outlying_years_2_prt_asia" for table "t26002_t1_1_prt_outlying_years"
NOTICE:  CREATE TABLE will create partition "t26002_t1_1_prt_outlying_years_2_prt_other_regions" for table "t26002_t1_1_prt_outlying_years"
NOTICE:  CREATE TABLE will create partition "t26002_t1_1_prt_2_2_prt_usa" for table "t26002_t1_1_prt_2"
NOTICE:  CREATE TABLE will create partition "t26002_t1_1_prt_2_2_prt_europe" for table "t26002_t1_1_prt_2"
NOTICE:  CREATE TABLE will create partition "t26002_t1_1_prt_2_2_prt_asia" for table "t26002_t1_1_prt_2"
NOTICE:  CREATE TABLE will create partition "t26002_t1_1_prt_2_2_prt_other_regions" for table "t26002_t1_1_prt_2"
  
-- TEST
-- expected to see the partition key
\d T26002_T1;
         Table "bfv_partition.t26002_t1"
    Column    |         Type          | Modifiers 
--------------+-----------------------+-----------
 empid        | integer               | 
 departmentid | integer               | 
 year         | integer               | 
 region       | character varying(20) | 
Number of child tables: 2 (Use \d+ to list them.)
Distributed by: (empid)
Partition by: (year)

\d t26002_t1_1_prt_2;
     Table "bfv_partition.t26002_t1_1_prt_2"
    Column    |         Type          | Modifiers 
--------------+-----------------------+-----------
 empid        | integer               | 
 departmentid | integer               | 
 year         | integer               | 
 region       | character varying(20) | 
Check constraints:
    "t26002_t1_1_prt_2_check" CHECK (year >= 2012 AND year < 2015)
Inherits: t26002_t1
Number of child tables: 4 (Use \d+ to list them.)
Distributed by: (empid)
Partition by: (region, departmentid)

\d t26002_t1_1_prt_2_2_prt_asia;
Table "bfv_partition.t26002_t1_1_prt_2_2_prt_asia"
    Column    |         Type          | Modifiers 
--------------+-----------------------+-----------
 empid        | integer               | 
 departmentid | integer               | 
 year         | integer               | 
 region       | character varying(20) | 
Check constraints:
    "t26002_t1_1_prt_2_2_prt_asia_check" CHECK (region::text = 'asia'::text AND departmentid = 3)
    "t26002_t1_1_prt_2_check" CHECK (year >= 2012 AND year < 2015)
Inherits: t26002_t1_1_prt_2
Distributed by: (empid)

\d+ T26002_T1;
                      Table "bfv_partition.t26002_t1"
    Column    |         Type          | Modifiers | Storage  | Description 
--------------+-----------------------+-----------+----------+-------------
 empid        | integer               |           | plain    | 
 departmentid | integer               |           | plain    | 
 year         | integer               |           | plain    | 
 region       | character varying(20) |           | extended | 
Child tables: t26002_t1_1_prt_2,
              t26002_t1_1_prt_outlying_years
Has OIDs: no
Distributed by: (empid)
Partition by: (year)

\d+ t26002_t1_1_prt_2;
                  Table "bfv_partition.t26002_t1_1_prt_2"
    Column    |         Type          | Modifiers | Storage  | Description 
--------------+-----------------------+-----------+----------+-------------
 empid        | integer               |           | plain    | 
 departmentid | integer               |           | plain    | 
 year         | integer               |           | plain    | 
 region       | character varying(20) |           | extended | 
Check constraints:
    "t26002_t1_1_prt_2_check" CHECK (year >= 2012 AND year < 2015)
Inherits: t26002_t1
Child tables: t26002_t1_1_prt_2_2_prt_asia,
              t26002_t1_1_prt_2_2_prt_europe,
              t26002_t1_1_prt_2_2_prt_other_regions,
              t26002_t1_1_prt_2_2_prt_usa
Has OIDs: no
Distributed by: (empid)
Partition by: (region, departmentid)

\d+ t26002_t1_1_prt_2_2_prt_asia;
            Table "bfv_partition.t26002_t1_1_prt_2_2_prt_asia"
    Column    |         Type          | Modifiers | Storage  | Description 
--------------+-----------------------+-----------+----------+-------------
 empid        | integer               |           | plain    | 
 departmentid | integer               |           | plain    | 
 year         | integer               |           | plain    | 
 region       | character varying(20) |           | extended | 
Check constraints:
    "t26002_t1_1_prt_2_2_prt_asia_check" CHECK (region::text = 'asia'::text AND departmentid = 3)
    "t26002_t1_1_prt_2_check" CHECK (year >= 2012 AND year < 2015)
Inherits: t26002_t1_1_prt_2
Has OIDs: no
Distributed by: (empid)

/*
-- test 2: Data Engineer won't see partition key for non-partitioned table
GIVEN I am a Data Engineer
WHEN I run \d on a non-partitioned table
THEN I should NOT see the partition key in the output
*/
CREATE TABLE T26002_T2 (empid int, departmentid int, year int, region varchar(20))
DISTRIBUTED BY (empid);
\d T26002_T2;
         Table "bfv_partition.t26002_t2"
    Column    |         Type          | Modifiers 
--------------+-----------------------+-----------
 empid        | integer               | 
 departmentid | integer               | 
 year         | integer               | 
 region       | character varying(20) | 
Distributed by: (empid)

\d+ T26002_T2;
                      Table "bfv_partition.t26002_t2"
    Column    |         Type          | Modifiers | Storage  | Description 
--------------+-----------------------+-----------+----------+-------------
 empid        | integer               |           | plain    | 
 departmentid | integer               |           | plain    | 
 year         | integer               |           | plain    | 
 region       | character varying(20) |           | extended | 
Has OIDs: no
Distributed by: (empid)

-- CLEANUP
-- start_ignore
DROP TABLE IF EXISTS T26002_T1;
DROP TABLE IF EXISTS T26002_T2;
-- end_ignore
-- ************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
-- start_ignore
select disable_xform('CXformInnerJoin2HashJoin');
            disable_xform             
--------------------------------------
 CXformInnerJoin2HashJoin is disabled
(1 row)

-- end_ignore
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;
select enable_xform('CXformInnerJoin2HashJoin');
            enable_xform             
-------------------------------------
 CXformInnerJoin2HashJoin is enabled
(1 row)

-- 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
--
-- Testing whether test gives wrong results with partition tables when sub-partitions are distributed differently than the parent partition.
--
-- SETUP
-- start_ignore
drop table if exists pt;
NOTICE:  table "pt" does not exist, skipping
drop table if exists t;
NOTICE:  table "t" does not exist, skipping
-- end_ignore
create table pt(a int, b int, c int) distributed by (a) partition by range(b) (start(0) end(10) every (2));
NOTICE:  CREATE TABLE will create partition "pt_1_prt_1" for table "pt"
NOTICE:  CREATE TABLE will create partition "pt_1_prt_2" for table "pt"
NOTICE:  CREATE TABLE will create partition "pt_1_prt_3" for table "pt"
NOTICE:  CREATE TABLE will create partition "pt_1_prt_4" for table "pt"
NOTICE:  CREATE TABLE will create partition "pt_1_prt_5" for table "pt"
alter table pt_1_prt_1 set distributed randomly;
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 pt select g%10, g%2 + 1, g*2 from generate_series(1,20) g;
insert into pt values(1,1,3);
insert into t select g%10, g%2 + 1 from generate_series(1,20) g;
create index pt_c on pt(c);
NOTICE:  building index for child partition "pt_1_prt_1"
NOTICE:  building index for child partition "pt_1_prt_2"
NOTICE:  building index for child partition "pt_1_prt_3"
NOTICE:  building index for child partition "pt_1_prt_4"
NOTICE:  building index for child partition "pt_1_prt_5"
analyze t;
analyze pt;
-- TEST
SELECT COUNT(*) FROM pt, t WHERE pt.a = t.a;
 count 
-------
    42
(1 row)

SELECT COUNT(*) FROM pt, t WHERE pt.a = t.a and pt.c=4;
 count 
-------
     2
(1 row)

select a, count(*) from pt group by a; 
 a | count 
---+-------
 9 |     2
 8 |     2
 2 |     2
 1 |     3
 0 |     2
 3 |     2
 6 |     2
 7 |     2
 5 |     2
 4 |     2
(10 rows)

select b, count(*) from pt group by b;
 b | count 
---+-------
 2 |    10
 1 |    11
(2 rows)

select a, count(*) from pt where a<2 group by a;
 a | count 
---+-------
 1 |     3
 0 |     2
(2 rows)

-- CLEANUP
-- start_ignore
drop index pt_c;
WARNING:  Only dropped the index "pt_c"
HINT:  To drop other indexes on child partitions, drop each one explicitly.
drop table if exists pt;
drop table if exists t;
-- 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
-- start_ignore
select disable_xform('CXformDynamicGet2DynamicTableScan');
                 disable_xform                 
-----------------------------------------------
 CXformDynamicGet2DynamicTableScan is disabled
(1 row)

-- end_ignore
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;
select enable_xform('CXformDynamicGet2DynamicTableScan');
                 enable_xform                 
----------------------------------------------
 CXformDynamicGet2DynamicTableScan is enabled
(1 row)

-- 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)

-- start_ignore
select disable_xform('CXformDynamicGet2DynamicTableScan');
                 disable_xform                 
-----------------------------------------------
 CXformDynamicGet2DynamicTableScan is disabled
(1 row)

-- end_ignore
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;
select enable_xform('CXformDynamicGet2DynamicTableScan');
                 enable_xform                 
----------------------------------------------
 CXformDynamicGet2DynamicTableScan is enabled
(1 row)

-- 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
-- start_ignore
select disable_xform('CXformDynamicGet2DynamicTableScan') ;
                 disable_xform                 
-----------------------------------------------
 CXformDynamicGet2DynamicTableScan is disabled
(1 row)

-- end_ignore
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;
select enable_xform('CXformDynamicGet2DynamicTableScan') ;
                 enable_xform                 
----------------------------------------------
 CXformDynamicGet2DynamicTableScan is enabled
(1 row)

reset optimizer_segments;
set optimizer_partition_selection_log=off;
select enable_xform('CXformDynamicGet2DynamicTableScan') ;
                 enable_xform                 
----------------------------------------------
 CXformDynamicGet2DynamicTableScan is enabled
(1 row)

-- 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;
drop function if exists find_operator(query text, operator_name text);
drop function if exists count_operator(query text, operator_name text);
-- end_ignore
--
-- Partition table with appendonly leaf, full join
--
-- SETUP
-- start_ignore
DROP TABLE IF EXISTS foo;
NOTICE:  table "foo" does not exist, skipping
DROP TABLE IF EXISTS bar;
NOTICE:  table "bar" does not exist, skipping
CREATE TABLE foo (a 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 bar (b int, c int)
PARTITION BY RANGE (b)
  SUBPARTITION BY RANGE (c) SUBPARTITION TEMPLATE 
  (
    START (1) END (10) WITH (appendonly=true),
    START (10) END (20)
  ) 
( 
  START (1) 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_1_2_prt_1" for table "bar_1_prt_1"
NOTICE:  CREATE TABLE will create partition "bar_1_prt_1_2_prt_2" for table "bar_1_prt_1"
NOTICE:  CREATE TABLE will create partition "bar_1_prt_2" for table "bar"
NOTICE:  CREATE TABLE will create partition "bar_1_prt_2_2_prt_1" for table "bar_1_prt_2"
NOTICE:  CREATE TABLE will create partition "bar_1_prt_2_2_prt_2" for table "bar_1_prt_2"
-- end_ignore
INSERT INTO foo VALUES (1);
INSERT INTO bar VALUES (2,3);
SELECT * FROM foo FULL JOIN bar ON foo.a = bar.b;
 a | b | c 
---+---+---
 1 |   |  
   | 2 | 3
(2 rows)

-- CLEANUP
-- start_ignore
DROP TABLE IF EXISTS foo;
DROP TABLE IF EXISTS bar;
-- end_ignore
--
-- Partition table with appendonly set at middlevel partition, full join
--
-- SETUP
-- start_ignore
DROP TABLE IF EXISTS foo;
NOTICE:  table "foo" does not exist, skipping
DROP TABLE IF EXISTS bar;
NOTICE:  table "bar" does not exist, skipping
CREATE TABLE foo (a 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 bar (b int, c int)
PARTITION BY RANGE (b)
  SUBPARTITION BY RANGE (c) SUBPARTITION TEMPLATE 
  (
    START (1) END (10),
    START (10) END (20)
  ) 
( 
  START (1) END (10) WITH (appendonly=true),
  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_1_2_prt_1" for table "bar_1_prt_1"
NOTICE:  CREATE TABLE will create partition "bar_1_prt_1_2_prt_2" for table "bar_1_prt_1"
NOTICE:  CREATE TABLE will create partition "bar_1_prt_2" for table "bar"
NOTICE:  CREATE TABLE will create partition "bar_1_prt_2_2_prt_1" for table "bar_1_prt_2"
NOTICE:  CREATE TABLE will create partition "bar_1_prt_2_2_prt_2" for table "bar_1_prt_2"
-- end_ignore
INSERT INTO foo VALUES (1);
INSERT INTO bar VALUES (2,3);
SELECT * FROM foo FULL JOIN bar ON foo.a = bar.b;
 a | b | c 
---+---+---
 1 |   |  
   | 2 | 3
(2 rows)

-- CLEANUP
-- start_ignore
DROP TABLE IF EXISTS foo;
DROP TABLE IF EXISTS bar;
-- end_ignore
--
-- Partition table with appendonly set at root partition, full join
--
-- SETUP
-- start_ignore
DROP TABLE IF EXISTS foo;
NOTICE:  table "foo" does not exist, skipping
DROP TABLE IF EXISTS bar;
NOTICE:  table "bar" does not exist, skipping
CREATE TABLE foo (a 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 bar (b int, c int) WITH (appendonly=true)
PARTITION BY RANGE (b)
  SUBPARTITION BY RANGE (c) SUBPARTITION TEMPLATE 
  (
    START (1) END (10),
    START (10) END (20)
  ) 
( 
  START (1) 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_1_2_prt_1" for table "bar_1_prt_1"
NOTICE:  CREATE TABLE will create partition "bar_1_prt_1_2_prt_2" for table "bar_1_prt_1"
NOTICE:  CREATE TABLE will create partition "bar_1_prt_2" for table "bar"
NOTICE:  CREATE TABLE will create partition "bar_1_prt_2_2_prt_1" for table "bar_1_prt_2"
NOTICE:  CREATE TABLE will create partition "bar_1_prt_2_2_prt_2" for table "bar_1_prt_2"
-- end_ignore
INSERT INTO foo VALUES (1);
INSERT INTO bar VALUES (2,3);
SELECT * FROM foo FULL JOIN bar ON foo.a = bar.b;
 a | b | c 
---+---+---
 1 |   |  
   | 2 | 3
(2 rows)

--
-- 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
         Rows out:  0 rows (seg0) with 0.051 ms to end, start offset by 1.246 ms.
         ->  Append  (cost=0.00..3244.00 rows=94800 width=16)
               Rows out:  0 rows (seg0) with 0.051 ms to end, start offset by 1.246 ms.
               ->  Seq Scan on mpp8031_1_prt_foo a  (cost=0.00..811.00 rows=23700 width=16)
                     Rows out:  0 rows (seg0) with 0.022 ms to end, start offset by 1.247 ms.
               ->  Seq Scan on mpp8031_1_prt_2 a  (cost=0.00..811.00 rows=23700 width=16)
                     Rows out:  0 rows (seg0) with 0.009 ms to end, start offset by 1.270 ms.
               ->  Seq Scan on mpp8031_1_prt_3 a  (cost=0.00..811.00 rows=23700 width=16)
                     Rows out:  0 rows (seg0) with 0.009 ms to end, start offset by 1.279 ms.
               ->  Seq Scan on mpp8031_1_prt_4 a  (cost=0.00..811.00 rows=23700 width=16)
                     Rows out:  0 rows (seg0) with 0.008 ms to end, start offset by 1.288 ms.
         ->  Hash  (cost=3244.00..3244.00 rows=94800 width=4)
               Rows in:  (No row requested) 0 rows (seg0) with 0 ms to end.
               ->  Append  (cost=0.00..3244.00 rows=94800 width=4)
                     Rows out:  (No row requested) 0 rows (seg0) with 0 ms to end.
                     ->  Seq Scan on mpp8031_1_prt_foo b  (cost=0.00..811.00 rows=23700 width=4)
                           Rows out:  (No row requested) 0 rows (seg0) with 0 ms to end.
                     ->  Seq Scan on mpp8031_1_prt_2 b  (cost=0.00..811.00 rows=23700 width=4)
                           Rows out:  (No row requested) 0 rows (seg0) with 0 ms to end.
                     ->  Seq Scan on mpp8031_1_prt_3 b  (cost=0.00..811.00 rows=23700 width=4)
                           Rows out:  (No row requested) 0 rows (seg0) with 0 ms to end.
                     ->  Seq Scan on mpp8031_1_prt_4 b  (cost=0.00..811.00 rows=23700 width=4)
                           Rows out:  (No row requested) 0 rows (seg0) with 0 ms to end.
 Slice statistics:
   (slice0)    Executor memory: 382K bytes.
   (slice1)    Executor memory: 146K bytes avg x 3 workers, 146K bytes max (seg0).
 Statement statistics:
   Memory used: 128000K bytes
 Optimizer status: legacy query optimizer
 Total runtime: 2.045 ms
(34 rows)

drop table mpp8031;
-- CLEANUP
-- start_ignore
drop schema if exists bfv_partition;
-- end_ignore