set DateStyle="ISO, MDY";
set gp_create_table_random_default_distribution=off;
set optimizer_print_missing_stats = off;
create schema qp_misc_jiras;
-- Test that "INSERT INTO SELECT * FROM <external table>" works.
create table qp_misc_jiras.tbl3301_foo (c1 int);
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 external web table qp_misc_jiras.tbl3301_bar(like qp_misc_jiras.tbl3301_foo) execute 'echo 1' on master format 'csv';
insert into qp_misc_jiras.tbl3301_foo select * from qp_misc_jiras.tbl3301_bar;
select * from qp_misc_jiras.tbl3301_foo;
 c1 
----
  1
(1 row)

-- Test that altering the datatype of an indexed column works
create table qp_misc_jiras.tbl1318(dummy integer, aa text not null);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'dummy' 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 tbl1318_daa on qp_misc_jiras.tbl1318(dummy,aa);
alter table qp_misc_jiras.tbl1318 alter column aa type integer using bit_length(aa);
ERROR:  cannot alter indexed column
HINT:  DROP the index first, and recreate it after the ALTER
drop index qp_misc_jiras.tbl1318_daa;
create index tbl1318_daa on qp_misc_jiras.tbl1318 using bitmap(dummy,aa);
alter table qp_misc_jiras.tbl1318 alter column aa type bigint using bit_length(aa::text);
ERROR:  cannot alter indexed column
HINT:  DROP the index first, and recreate it after the ALTER
drop index qp_misc_jiras.tbl1318_daa;
drop table qp_misc_jiras.tbl1318;
-- Test for the upstream bug with combocids:
-- https://www.postgresql.org/message-id/48B87164.4050802%40soe.ucsc.edu
CREATE TABLE qp_misc_jiras.tbl3403_tab(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.
begin;
INSERT INTO qp_misc_jiras.tbl3403_tab VALUES(1);
INSERT INTO qp_misc_jiras.tbl3403_tab VALUES(2);
INSERT INTO qp_misc_jiras.tbl3403_tab VALUES(3);
SAVEPOINT s; DELETE FROM qp_misc_jiras.tbl3403_tab; ROLLBACK TO SAVEPOINT s;
SELECT a FROM qp_misc_jiras.tbl3403_tab ORDER BY a;
 a 
---
 1
 2
 3
(3 rows)

SAVEPOINT s; DELETE FROM qp_misc_jiras.tbl3403_tab; ROLLBACK TO SAVEPOINT s;
SAVEPOINT s; DELETE FROM qp_misc_jiras.tbl3403_tab; ROLLBACK TO SAVEPOINT s;
SAVEPOINT s; DELETE FROM qp_misc_jiras.tbl3403_tab; ROLLBACK TO SAVEPOINT s;
SELECT a FROM qp_misc_jiras.tbl3403_tab ORDER BY a;
 a 
---
 1
 2
 3
(3 rows)

COMMIT;
SELECT a FROM qp_misc_jiras.tbl3403_tab ORDER BY a;
 a 
---
 1
 2
 3
(3 rows)

drop table qp_misc_jiras.tbl3403_tab;
-- Check that CTAS chooses a sensible distribution key.
create table qp_misc_jiras.tbl2788 as select * from generate_series(1, 1000);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'generate_series' 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.
\d qp_misc_jiras.tbl2788;
     Table "qp_misc_jiras.tbl2788"
     Column      |  Type   | Modifiers 
-----------------+---------+-----------
 generate_series | integer | 
Distributed by: (generate_series)

drop table qp_misc_jiras.tbl2788;
-- Test CTAS, with a subquery in the select list.
create table qp_misc_jiras.tbl2859 (domain integer, class integer, attr text, value integer) distributed by (domain);
insert into qp_misc_jiras.tbl2859 values(1, 1, 'A', 1);
insert into qp_misc_jiras.tbl2859 values(2, 1, 'A', 0);
insert into qp_misc_jiras.tbl2859 values(3, 0, 'B', 1);
SELECT attr, class, (select count(distinct class) from qp_misc_jiras.tbl2859) as dclass FROM qp_misc_jiras.tbl2859 GROUP BY attr, class ORDER BY attr;
 attr | class | dclass 
------+-------+--------
 A    |     1 |      2
 B    |     0 |      2
(2 rows)

create table qp_misc_jiras.tbl2859a as 
SELECT attr, class, (select count(distinct class) from qp_misc_jiras.tbl2859) as dclass FROM qp_misc_jiras.tbl2859 GROUP BY attr, class
distributed by (attr);
drop table qp_misc_jiras.tbl2859;
drop table qp_misc_jiras.tbl2859a;
-- Creating a view with an 'unknown' column produces a WARNING. Check that it's
-- emitted only once (there used to be a bug that it was emitted from every
-- segment.)
create view  qp_misc_jiras.tbl_2517_view  as select foo from (select 'bar' as foo) a;
WARNING:  column "foo" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
--  The following select is based on the query generated by psql's \dt command.
SELECT
  c.relname as "Name",
  CASE c.relkind WHEN 'a' THEN 'append-only' WHEN 'x' THEN 'external' WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type"
FROM pg_catalog.pg_class c
     JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('v','')
      AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
  AND C.relname =  'tbl_2517_view'
  AND c.relkind = 'v'
ORDER BY 1,2;
     Name      | Type 
---------------+------
 tbl_2517_view | view
(1 row)

drop view qp_misc_jiras.tbl_2517_view;
create table qp_misc_jiras.tbl3511 (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 qp_misc_jiras.tbl3511 values (1),(2),(3),(4),(5);
select * FROM qp_misc_jiras.tbl3511 where i = (select distinct max(i) from qp_misc_jiras.tbl3511);
 i 
---
 5
(1 row)

drop table qp_misc_jiras.tbl3511;
-- 
-- Create parent qp_misc_jiras.tbl1544 table
-- 
CREATE TABLE qp_misc_jiras.tbl1544
( pid INTEGER UNIQUE,
  pdate DATE NOT NULL,
  pval TEXT
)
;
-- 
-- Child tables
-- 
CREATE TABLE qp_misc_jiras.tbl1544_child_depth_1_y_2000_year (
CONSTRAINT tbl1544_child_depth_1_y_2000_year_pdate_check
CHECK (pdate >= '2000-01-01'::date AND pdate < '2001-01-01'::date))
INHERITS (qp_misc_jiras.tbl1544)
;
NOTICE:  table has parent, setting distribution columns to match parent table
CREATE TABLE qp_misc_jiras.tbl1544_child_depth_1_y_2001_year (
CONSTRAINT tbl1544_child_depth_1_y_2001_year_pdate_check
CHECK (pdate >= '2001-01-01'::date AND pdate < '2002-01-01'::date))
INHERITS (qp_misc_jiras.tbl1544)
;
NOTICE:  table has parent, setting distribution columns to match parent table
-- 
-- Inserting redirection triggers
-- 
CREATE RULE rule_tbl1544_child_depth_1_y_2000_year AS
ON INSERT TO qp_misc_jiras.tbl1544
WHERE new.pdate >= '2000-01-01'::date AND new.pdate < '2001-01-01'::date
DO INSTEAD
INSERT INTO qp_misc_jiras.tbl1544_child_depth_1_y_2000_year (pid, pdate, pval)
VALUES (new.pid, new.pdate, new.pval)
;
CREATE RULE rule_tbl1544_child_depth_1_y_2001_year AS
ON INSERT TO qp_misc_jiras.tbl1544
WHERE new.pdate >= '2001-01-01'::date AND new.pdate < '2002-01-01'::date
DO INSTEAD
INSERT INTO qp_misc_jiras.tbl1544_child_depth_1_y_2001_year (pid, pdate, pval)
VALUES (new.pid, new.pdate, new.pval)
;
-- 
-- Add Data
-- 
INSERT INTO qp_misc_jiras.tbl1544 VALUES(1,'2000-01-01','xxx');
INSERT INTO qp_misc_jiras.tbl1544 VALUES(2,'2001-01-01','xxx');
delete from qp_misc_jiras.tbl1544 where pdate='2000-01-01';
update qp_misc_jiras.tbl1544 set pval='yyy' where pdate='2000-01-01';
drop table qp_misc_jiras.tbl1544 cascade;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to table qp_misc_jiras.tbl1544_child_depth_1_y_2000_year
drop cascades to table qp_misc_jiras.tbl1544_child_depth_1_y_2001_year
CREATE TYPE qp_misc_jiras.tbl4009_mediageoitem AS ( loctn_type character varying,
loctn_nm character varying,
loctn_desc character varying,
tot_trk_impsn bigint,
all_impsn bigint, 
tot_trk_click bigint, 
all_click bigint,
tot_trk_conv bigint,
all_conv bigint,
rank integer);
create role tbl4009_gpadmin;
NOTICE:  resource queue required -- using default resource queue "pg_default"
alter type qp_misc_jiras.tbl4009_mediageoitem owner to tbl4009_gpadmin;
select typname, rolname as owner from pg_type,pg_authid where typname='tbl4009_mediageoitem' and pg_type.typowner=pg_authid.oid;
       typname        |      owner      
----------------------+-----------------
 tbl4009_mediageoitem | tbl4009_gpadmin
(1 row)

select relname, rolname as owner from pg_class,pg_authid where relname='tbl4009_mediageoitem' and pg_class.relowner=pg_authid.oid;
       relname        |      owner      
----------------------+-----------------
 tbl4009_mediageoitem | tbl4009_gpadmin
(1 row)

drop type qp_misc_jiras.tbl4009_mediageoitem;
drop role tbl4009_gpadmin;
-- create schemas
create schema tbl4105_sdc_stage;
create schema tbl4105_sandbox_gp;
-- create the tables
CREATE TABLE tbl4105_sdc_stage.curr_cust_cmunty_membr (
    cust_cmunty_user_id integer,
    user_friend_id integer,
    user_hash_key smallint,
    days_visited bigint,
    page_views bigint,
    is_friend boolean
) distributed by (user_hash_key);
CREATE TABLE tbl4105_sandbox_gp.curr_user_profl (
    myspace_id integer,
    user_hash_key smallint,
    gender smallint,
    drinker smallint,
    age_band integer,
    children smallint,
    education smallint,
    ethnicity smallint,
    maritalstatus smallint,
    smoker smallint,
    sexpref smallint,
    utype smallint,
    religion smallint,
    income smallint,
    bodytype smallint,
    dating smallint,
    networking smallint,
    culturepref smallint
) distributed by (user_hash_key);
CREATE TABLE tbl4105_sandbox_gp.curr_user_geo (
    user_id integer,
    user_hash_key smallint,
    ip_geo_sk integer,
    first_event timestamp without time zone,
    last_event timestamp without time zone,
    tot_trk_impsn bigint,
    tot_trk_click bigint,
    tot_trk_conv bigint,
    tot_trk_ut bigint,
    tot_hrs bigint,
    tot_days bigint,
    visits_last_30_days bigint,
    visits_last_60_days bigint,
    visits_last_90_days bigint
) distributed by (user_hash_key);
CREATE TABLE tbl4105_sandbox_gp.ip_geo (
    ip_geo_sk integer DEFAULT 1,
    srce_ip_geo_cntry character varying(65) NOT NULL,
    srce_ip_geo_rgn character varying(135) NOT NULL,
    srce_ip_geo_state character varying(65) NOT NULL,
    srce_ip_geo_dma integer DEFAULT 0 NOT NULL,
    srce_ip_geo_city character varying(100) NOT NULL,
    srce_ip_geo_zip character varying(30) NOT NULL,
    srce_ip_geo_time_zone character varying(5) NOT NULL,
    ip_geo_cntry character varying(65) NOT NULL,
    ip_geo_rgn character varying(135) NOT NULL,
    ip_geo_state character varying(65) NOT NULL,
    ip_geo_dma integer DEFAULT 0 NOT NULL,
    ip_geo_dma_nm character varying(65) NOT NULL,
    ip_geo_city character varying(100) NOT NULL,
    ip_geo_zip character varying(30) NOT NULL,
    ip_geo_time_zone character varying(5) NOT NULL,
    create_dttm timestamp without time zone NOT NULL,
    ip_geo_cntry_abbr character varying
) distributed randomly;
-- sql
   --     CREATE TABLE med.footest as 
        SELECT GROUPING( cust_cmunty_user_id, IS_FRIEND
                        ,ip_geo_cntry, ip_geo_cntry_abbr, ip_geo_state, ip_geo_city
                        ,GENDER,DRINKER,AGE_BAND,CHILDREN,EDUCATION,ETHNICITY,MARITALSTATUS,SMOKER,SEXPREF,UTYPE,RELIGION,INCOME,BODYTYPE,DATING,NETWORKING,CULTUREPREF)::INT as group_ind
              ,cust_cmunty_user_id 
              ,is_friend
              ,ip_geo_cntry
              ,ip_geo_cntry_abbr
              ,ip_geo_state
              ,ip_geo_city
              ,gender
              ,drinker
              ,age_band
              ,children
              ,education
              ,ethnicity
              ,maritalstatus
              ,smoker
              ,sexpref
              ,utype
              ,religion 
              ,income
              ,bodytype
              ,dating
              ,networking
              ,culturepref
              ,SUM( page_views )::BIGINT as page_views
              ,SUM( visit )::BIGINT as visitors 
              ,count(*)::BIGINT as uniques      
        FROM (
        SELECT f.cust_cmunty_user_id 
              ,f.is_friend
              ,g.ip_geo_cntry
              ,g.ip_geo_cntry_abbr
              ,g.ip_geo_state
              ,g.ip_geo_city
              ,coalesce(up.gender,-99) as gender
              ,coalesce(up.drinker,-99) as drinker
              ,coalesce(up.age_band,-99) as age_band
              ,coalesce(up.CHILDREN,-99) as children
              ,coalesce(up.EDUCATION,-99) as education
              ,coalesce(up.ETHNICITY,-99) as ethnicity
              ,coalesce(up.MARITALSTATUS,-99) as maritalstatus
              ,coalesce(up.SMOKER,-99) as smoker
              ,coalesce(up.SEXPREF,-99) as sexpref
              ,coalesce(up.UTYPE,-99) as utype
              ,coalesce(up.RELIGION,-99) as religion 
              ,coalesce(up.INCOME,-99) as income
              ,coalesce(up.BODYTYPE,-99) as bodytype
              ,coalesce(up.DATING,-99) as dating
              ,coalesce(up.NETWORKING,-99) as networking
              ,coalesce(up.CULTUREPREF,-99) as culturepref
              ,f.page_views
              ,CASE WHEN COALESCE( page_views,0 ) > 0 THEN 1 ELSE 0 END as visit 
        FROM tbl4105_sdc_stage.curr_cust_cmunty_membr f 
             LEFT OUTER JOIN tbl4105_sandbox_gp.curr_user_profl up
              ON ( f.user_hash_key = up.user_hash_key 
             AND   f.user_friend_id = up.myspace_id )
             LEFT OUTER JOIN ( SELECT user_hash_key
                                  , user_id
                                  , geo.ip_geo_cntry
                                  , geo.ip_geo_cntry_abbr
                                  , geo.ip_geo_state
                                  , geo.ip_geo_city
                              FROM tbl4105_sandbox_gp.curr_user_geo 
                                   LEFT OUTER JOIN tbl4105_sandbox_gp.ip_geo geo
                                   USING ( ip_geo_sk ) 
                              ) g
              ON ( f.user_hash_key = g.user_hash_key 
             AND   f.user_friend_id = g.user_id )
        ) foo
        GROUP BY cust_cmunty_user_id,
                 GROUPING SETS ( ( is_friend ,ip_geo_cntry, ip_geo_cntry_abbr, ip_geo_state, ip_geo_city )
                               , ( is_friend ,gender )
                               , ( is_friend ,drinker )
                               , ( is_friend ,age_band )
                               , ( is_friend ,children )
                               , ( is_friend ,education )
                               , ( is_friend ,ethnicity )
                               , ( is_friend ,maritalstatus )
                               , ( is_friend ,smoker )
                               , ( is_friend ,sexpref )
                               , ( is_friend ,utype )
                               , ( is_friend ,religion )
                               , ( is_friend ,income )
                               , ( is_friend ,bodytype )
                               , ( is_friend ,dating )
                               , ( is_friend ,networking )
                               , ( is_friend ,culturepref ) );
 group_ind | cust_cmunty_user_id | is_friend | ip_geo_cntry | ip_geo_cntry_abbr | ip_geo_state | ip_geo_city | gender | drinker | age_band | children | education | ethnicity | maritalstatus | smoker | sexpref | utype | religion | income | bodytype | dating | networking | culturepref | page_views | visitors | uniques 
-----------+---------------------+-----------+--------------+-------------------+--------------+-------------+--------+---------+----------+----------+-----------+-----------+---------------+--------+---------+-------+----------+--------+----------+--------+------------+-------------+------------+----------+---------
(0 rows)

   --     DISTRIBUTED RANDOMLY;
-- drop the tables
drop table tbl4105_sdc_stage.curr_cust_cmunty_membr;
drop table tbl4105_sandbox_gp.curr_user_profl;
drop table tbl4105_sandbox_gp.curr_user_geo;
drop table tbl4105_sandbox_gp.ip_geo;
-- drop the schemas
drop schema tbl4105_sdc_stage;
drop schema tbl4105_sandbox_gp;
set enable_nestloop=on;
CREATE TABLE qp_misc_jiras.satelliteupdatelog (
    id integer NOT NULL,
    "type" character varying NOT NULL,
    "action" character varying NOT NULL,
    scheduled timestamp without time zone NOT NULL,
    idrep integer,
    idaffiliate integer,
    idadvertiser integer,
    CONSTRAINT satelliteupdatelog_id_xor CHECK ((((((idrep IS NOT NULL) AND (idaffiliate IS NULL)) AND (idadvertiser IS NULL)) OR (((idrep IS NULL) AND (idaffiliate IS NOT NULL)) AND (idadvertiser IS NULL))) OR (((idrep IS NULL) AND (idaffiliate IS NULL)) AND (idadvertiser IS NOT NULL))))
);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' 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 SEQUENCE qp_misc_jiras.satelliteupdatelog_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;
ALTER SEQUENCE qp_misc_jiras.satelliteupdatelog_id_seq OWNED BY qp_misc_jiras.satelliteupdatelog.id;
ALTER TABLE qp_misc_jiras.satelliteupdatelog ALTER COLUMN id SET DEFAULT nextval('qp_misc_jiras.satelliteupdatelog_id_seq'::regclass);
ALTER TABLE ONLY qp_misc_jiras.satelliteupdatelog ADD CONSTRAINT satelliteupdatelog_pk PRIMARY KEY (id);
CREATE INDEX fki_satelliteupdatelog_idadvertiser_fk ON qp_misc_jiras.satelliteupdatelog USING btree (idadvertiser);
CREATE INDEX fki_satelliteupdatelog_idaffiliate_fk ON qp_misc_jiras.satelliteupdatelog USING btree (idaffiliate);
CREATE INDEX fki_satelliteupdatelog_idrep_fk ON qp_misc_jiras.satelliteupdatelog USING btree (idrep);
CREATE TABLE qp_misc_jiras.satelliteupdatelogkey (
    idsatelliteupdatelog integer NOT NULL,
    columnname character varying NOT NULL,
    value character varying
);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'idsatelliteupdatelog' 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.
ALTER TABLE ONLY qp_misc_jiras.satelliteupdatelogkey ADD CONSTRAINT satelliteupdatelogkey_pk PRIMARY KEY (idsatelliteupdatelog, columnname);
ALTER TABLE ONLY qp_misc_jiras.satelliteupdatelogkey ADD CONSTRAINT satelliteupdatelogkey_idsatelliteupdatelog_fk FOREIGN KEY (idsatelliteupdatelog) REFERENCES qp_misc_jiras.satelliteupdatelog(id);
WARNING:  referential integrity (FOREIGN KEY) constraints are not supported in Greenplum Database, will not be enforced
CREATE TABLE qp_misc_jiras.satellite (
    id integer NOT NULL,
    name character varying NOT NULL,
    "domain" character varying NOT NULL,
    "index" character varying NOT NULL,
    enabled boolean NOT NULL,
    isnewsatellite boolean NOT NULL
);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' 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 SEQUENCE qp_misc_jiras.satellite_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;
ALTER SEQUENCE qp_misc_jiras.satellite_id_seq OWNED BY qp_misc_jiras.satellite.id;
ALTER TABLE qp_misc_jiras.satellite ALTER COLUMN id SET DEFAULT nextval('qp_misc_jiras.satellite_id_seq'::regclass);
CREATE TABLE qp_misc_jiras.satelliteupdatelogserver (
    idsatelliteupdatelog integer NOT NULL,
    idsatellite integer NOT NULL,
    retrys integer NOT NULL,
    completed timestamp without time zone,
    failurereason character varying
);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'idsatelliteupdatelog' 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.
ALTER TABLE ONLY qp_misc_jiras.satelliteupdatelogserver ADD CONSTRAINT satelliteupdatelogserver_pk PRIMARY KEY (idsatelliteupdatelog, idsatellite); 
CREATE INDEX fki_satelliteupdatelogserver_idsatelliteupdatelog_fk ON qp_misc_jiras.satelliteupdatelogserver USING btree (idsatelliteupdatelog);
ALTER TABLE ONLY qp_misc_jiras.satelliteupdatelogserver ADD CONSTRAINT satelliteupdatelogserver_idsatellite_fk FOREIGN KEY (idsatellite) REFERENCES qp_misc_jiras.satellite(id);
ERROR:  there is no unique constraint matching given keys for referenced table "satellite"
ALTER TABLE ONLY qp_misc_jiras.satelliteupdatelogserver ADD CONSTRAINT satelliteupdatelogserver_idsatelliteupdatelog_fk FOREIGN KEY (idsatelliteupdatelog) REFERENCES qp_misc_jiras.satelliteupdatelog(id);
WARNING:  referential integrity (FOREIGN KEY) constraints are not supported in Greenplum Database, will not be enforced
SELECT /* gptest */ s.id, s.action, s.type, sk.columnName AS "columnName", sk.value
FROM qp_misc_jiras.satelliteUpdateLog AS s
LEFT JOIN qp_misc_jiras.satelliteUpdateLogKey AS sk ON s.id = sk.idSatelliteUpdateLog
JOIN qp_misc_jiras.satelliteUpdateLogServer AS ss ON s.id = ss.idSatelliteUpdateLog
JOIN qp_misc_jiras.satellite AS sat ON sat.id = ss.idSatellite
WHERE ss.completed IS NULL
AND ss.retrys > 0
AND sat.enabled
GROUP BY s.id, s.action, s.type, sk.columnName, sk.value, s.scheduled
ORDER BY s.scheduled; 
 id | action | type | columnName | value 
----+--------+------+------------+-------
(0 rows)

drop table qp_misc_jiras.satelliteupdatelog cascade;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to constraint satelliteupdatelogkey_idsatelliteupdatelog_fk on table qp_misc_jiras.satelliteupdatelogkey
drop cascades to constraint satelliteupdatelogserver_idsatelliteupdatelog_fk on table qp_misc_jiras.satelliteupdatelogserver
drop table qp_misc_jiras.satelliteupdatelogkey cascade;
drop table qp_misc_jiras.satellite cascade;
drop table qp_misc_jiras.satelliteupdatelogserver cascade;
create table qp_misc_jiras.tbl3183_t1 (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 qp_misc_jiras.tbl3183_t1 values (1), (1);
select * into qp_misc_jiras.tbl3183_t2 from qp_misc_jiras.tbl3183_t1;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) 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.
select * into qp_misc_jiras.tbl3183_t3 from qp_misc_jiras.tbl3183_t1;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) 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.
select i from (select i from qp_misc_jiras.tbl3183_t2 union all select i from qp_misc_jiras.tbl3183_t3) tmpt where i in (select i from qp_misc_jiras.tbl3183_t2 union all select i from qp_misc_jiras.tbl3183_t3);
 i 
---
 1
 1
 1
 1
(4 rows)

drop table qp_misc_jiras.tbl3183_t1;
drop table qp_misc_jiras.tbl3183_t2;
drop table qp_misc_jiras.tbl3183_t3;
create table qp_misc_jiras.tbl5028_part_test (a int, b date,c text) 
partition by range(b)
(
	partition p1 start('2007-01-01'),
        partition p2 start('2008-01-01'),
        partition p3 start('2009-01-01'),
	default partition def_part
);
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 "tbl5028_part_test_1_prt_def_part" for table "tbl5028_part_test"
NOTICE:  CREATE TABLE will create partition "tbl5028_part_test_1_prt_p1" for table "tbl5028_part_test"
NOTICE:  CREATE TABLE will create partition "tbl5028_part_test_1_prt_p2" for table "tbl5028_part_test"
NOTICE:  CREATE TABLE will create partition "tbl5028_part_test_1_prt_p3" for table "tbl5028_part_test"
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (1, '2006-1-01', 'Year 1');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (1, '2007-1-01', 'Year 2');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (1, '2008-1-01', 'Year 3');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (1, '2009-1-01', 'Year 4');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (2, '2006-2-01', 'Year 1');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (2, '2007-2-01', 'Year 2');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (2, '2008-2-01', 'Year 3');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (2, '2009-2-01', 'Year 4');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (3, '2006-3-01', 'Year 1');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (3, '2007-3-01', 'Year 2');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (3, '2008-3-01', 'Year 3');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (3, '2009-3-01', 'Year 4');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (4, '2006-4-01', 'Year 1');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (4, '2007-4-01', 'Year 2');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (4, '2008-4-01', 'Year 3');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (4, '2009-4-01', 'Year 4');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (5, '2006-5-01', 'Year 1');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (5, '2007-5-01', 'Year 2');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (5, '2008-5-01', 'Year 3');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (5, '2009-5-01', 'Year 4');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (6, '2006-6-01', 'Year 1');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (6, '2007-6-01', 'Year 2');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (6, '2008-6-01', 'Year 3');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (6, '2009-6-01', 'Year 4');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (7, '2006-7-01', 'Year 1');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (7, '2007-7-01', 'Year 2');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (7, '2008-7-01', 'Year 3');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (7, '2009-7-01', 'Year 4');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (8, '2006-8-01', 'Year 1');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (8, '2007-8-01', 'Year 2');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (8, '2008-8-01', 'Year 3');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (8, '2009-8-01', 'Year 4');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (9, '2006-9-01', 'Year 1');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (9, '2007-9-01', 'Year 2');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (9, '2008-9-01', 'Year 3');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (9, '2009-9-01', 'Year 4');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (10, '2006-10-01', 'Year 1');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (10, '2007-10-01', 'Year 2');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (10, '2008-10-01', 'Year 3');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (10, '2009-10-01', 'Year 4');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (11, '2006-11-01', 'Year 1');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (11, '2007-11-01', 'Year 2');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (11, '2008-11-01', 'Year 3');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (11, '2009-11-01', 'Year 4');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (12, '2006-12-01', 'Year 1');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (12, '2007-12-01', 'Year 2');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (12, '2008-12-01', 'Year 3');
INSERT INTO qp_misc_jiras.tbl5028_part_test VALUES (12, '2009-12-01', 'Year 4');
select count(*) from qp_misc_jiras.tbl5028_part_test_1_prt_def_part ;
 count 
-------
    12
(1 row)

select count(*) from qp_misc_jiras.tbl5028_part_test_1_prt_p1;
 count 
-------
    12
(1 row)

select count(*) from qp_misc_jiras.tbl5028_part_test_1_prt_p2;
 count 
-------
    12
(1 row)

select count(*) from qp_misc_jiras.tbl5028_part_test_1_prt_p3;
 count 
-------
    12
(1 row)

DELETE FROM qp_misc_jiras.tbl5028_part_test where b >= '2007-01-01' and b <= '2007-03-01';
select count(*) from qp_misc_jiras.tbl5028_part_test; -- should be 45
 count 
-------
    45
(1 row)

select count(*) from qp_misc_jiras.tbl5028_part_test_1_prt_def_part ; -- should be 12
 count 
-------
    12
(1 row)

select count(*) from qp_misc_jiras.tbl5028_part_test_1_prt_p1; -- should be 9
 count 
-------
     9
(1 row)

select count(*) from qp_misc_jiras.tbl5028_part_test_1_prt_p2; -- should be 12
 count 
-------
    12
(1 row)

select count(*) from qp_misc_jiras.tbl5028_part_test_1_prt_p3; -- should be 12
 count 
-------
    12
(1 row)

drop table qp_misc_jiras.tbl5028_part_test;
--   MPP-11125: partition p1 start('0') end('25') every 8 (12)
--   	- it did not work as expected but behaved same as "every (12)"
--   	- "every 8 (12)" is now an invalid syntax
--   Modified: "every 8 (12)" to "every (12)"
--             added an extra "every 8 (12)" to test invalid syntax
create table qp_misc_jiras.tbl5151_customer(C_CUSTKEY INTEGER,
                C_NAME VARCHAR(25),
                C_ADDRESS VARCHAR(40),
               C_NATIONKEY INTEGER,
                C_PHONE CHAR(15),
                C_ACCTBAL decimal,
                C_MKTSEGMENT CHAR(10),
                C_COMMENT VARCHAR(117)
                )
partition by range (c_nationkey) 
subpartition by range (c_acctbal) subpartition template (start('-999.99') end('10000.99') every(2200)
)
(
partition p1 start('0') end('25') every (12)
);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'c_custkey' 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 "tbl5151_customer_1_prt_p1_1" for table "tbl5151_customer"
NOTICE:  CREATE TABLE will create partition "tbl5151_customer_1_prt_p1_1_2_prt_1" for table "tbl5151_customer_1_prt_p1_1"
NOTICE:  CREATE TABLE will create partition "tbl5151_customer_1_prt_p1_1_2_prt_2" for table "tbl5151_customer_1_prt_p1_1"
NOTICE:  CREATE TABLE will create partition "tbl5151_customer_1_prt_p1_1_2_prt_3" for table "tbl5151_customer_1_prt_p1_1"
NOTICE:  CREATE TABLE will create partition "tbl5151_customer_1_prt_p1_1_2_prt_4" for table "tbl5151_customer_1_prt_p1_1"
NOTICE:  CREATE TABLE will create partition "tbl5151_customer_1_prt_p1_1_2_prt_5" for table "tbl5151_customer_1_prt_p1_1"
NOTICE:  CREATE TABLE will create partition "tbl5151_customer_1_prt_p1_1_2_prt_6" for table "tbl5151_customer_1_prt_p1_1"
NOTICE:  CREATE TABLE will create partition "tbl5151_customer_1_prt_p1_2" for table "tbl5151_customer"
NOTICE:  CREATE TABLE will create partition "tbl5151_customer_1_prt_p1_2_2_prt_1" for table "tbl5151_customer_1_prt_p1_2"
NOTICE:  CREATE TABLE will create partition "tbl5151_customer_1_prt_p1_2_2_prt_2" for table "tbl5151_customer_1_prt_p1_2"
NOTICE:  CREATE TABLE will create partition "tbl5151_customer_1_prt_p1_2_2_prt_3" for table "tbl5151_customer_1_prt_p1_2"
NOTICE:  CREATE TABLE will create partition "tbl5151_customer_1_prt_p1_2_2_prt_4" for table "tbl5151_customer_1_prt_p1_2"
NOTICE:  CREATE TABLE will create partition "tbl5151_customer_1_prt_p1_2_2_prt_5" for table "tbl5151_customer_1_prt_p1_2"
NOTICE:  CREATE TABLE will create partition "tbl5151_customer_1_prt_p1_2_2_prt_6" for table "tbl5151_customer_1_prt_p1_2"
NOTICE:  CREATE TABLE will create partition "tbl5151_customer_1_prt_p1_3" for table "tbl5151_customer"
NOTICE:  CREATE TABLE will create partition "tbl5151_customer_1_prt_p1_3_2_prt_1" for table "tbl5151_customer_1_prt_p1_3"
NOTICE:  CREATE TABLE will create partition "tbl5151_customer_1_prt_p1_3_2_prt_2" for table "tbl5151_customer_1_prt_p1_3"
NOTICE:  CREATE TABLE will create partition "tbl5151_customer_1_prt_p1_3_2_prt_3" for table "tbl5151_customer_1_prt_p1_3"
NOTICE:  CREATE TABLE will create partition "tbl5151_customer_1_prt_p1_3_2_prt_4" for table "tbl5151_customer_1_prt_p1_3"
NOTICE:  CREATE TABLE will create partition "tbl5151_customer_1_prt_p1_3_2_prt_5" for table "tbl5151_customer_1_prt_p1_3"
NOTICE:  CREATE TABLE will create partition "tbl5151_customer_1_prt_p1_3_2_prt_6" for table "tbl5151_customer_1_prt_p1_3"
create table qp_misc_jiras.tbl5151_customer(C_CUSTKEY INTEGER, primary key(c_custkey));
ERROR:  relation "tbl5151_customer" already exists
drop table qp_misc_jiras.tbl5151_customer;
--    invalid syntax
create table qp_misc_jiras.tbl5151_customer(C_CUSTKEY INTEGER) partition by range (c_nationkey) (start('0') end('25') every 8 (12));
ERROR:  syntax error at or near "8"
LINE 1: ... by range (c_nationkey) (start('0') end('25') every 8 (12));
                                                               ^
create table qp_misc_jiras.tbl2945_test (a numeric(19), b int) distributed by (a);
insert into qp_misc_jiras.tbl2945_test values(1,2), (3,4);
-- the following should error out
alter table qp_misc_jiras.tbl2945_test alter a type bigint;
ERROR:  cannot alter type of a column used in a distribution policy
drop table qp_misc_jiras.tbl2945_test;
create table qp_misc_jiras.tbl5219_test (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.
insert into qp_misc_jiras.tbl5219_test select i, i%10 from generate_series(0, 99) i;
select case when 1=2 then rank() over(partition by j order by i) end from qp_misc_jiras.tbl5219_test;
 case 
------
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
(100 rows)

drop table qp_misc_jiras.tbl5219_test;
select n
from ( select row_number() over (partition by x) from (values (0)) as t(x) ) as r(n)
group by n;
 n 
---
 1
(1 row)

select n
from ( select row_number() over () from (values (0)) as t(x) ) as r(n)
group by n;
 n 
---
 1
(1 row)

create external web table qp_misc_jiras.tbl4622_ext1 (i int, j int) execute 'echo " | 20" ' on master format 'text' (delimiter as E'|' null as ' ');
select * from qp_misc_jiras.tbl4622_ext1;
 i | j  
---+----
   | 20
(1 row)

create external web table qp_misc_jiras.tbl4622_ext2 (i int, j int) execute 'echo "  | 20" ' on master format 'text' (delimiter as E'|' null as '  ');
select * from qp_misc_jiras.tbl4622_ext2;
 i | j  
---+----
   | 20
(1 row)

drop external web table qp_misc_jiras.tbl4622_ext1;
drop external web table qp_misc_jiras.tbl4622_ext2;
create external web table qp_misc_jiras.tbl3286_ext_hostname (hostname text) execute 'hostname' format 'text'; 
-- start_ignore
select * from qp_misc_jiras.tbl3286_ext_hostname,gp_configuration;
 hostname | content | definedprimary | dbid | isprimary | valid | hostname | port | datadir 
----------+---------+----------------+------+-----------+-------+----------+------+---------
(0 rows)

-- end_ignore
drop external web table qp_misc_jiras.tbl3286_ext_hostname;
create table qp_misc_jiras.tbl5305_test(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 qp_misc_jiras.tbl5305_test select i,i+1 from generate_series(1,1000)i;
begin;
declare c scroll cursor for select * from qp_misc_jiras.tbl5305_test order by a;
fetch forward 100 from c; -- should give the first 100 rows
  a  |  b  
-----+-----
   1 |   2
   2 |   3
   3 |   4
   4 |   5
   5 |   6
   6 |   7
   7 |   8
   8 |   9
   9 |  10
  10 |  11
  11 |  12
  12 |  13
  13 |  14
  14 |  15
  15 |  16
  16 |  17
  17 |  18
  18 |  19
  19 |  20
  20 |  21
  21 |  22
  22 |  23
  23 |  24
  24 |  25
  25 |  26
  26 |  27
  27 |  28
  28 |  29
  29 |  30
  30 |  31
  31 |  32
  32 |  33
  33 |  34
  34 |  35
  35 |  36
  36 |  37
  37 |  38
  38 |  39
  39 |  40
  40 |  41
  41 |  42
  42 |  43
  43 |  44
  44 |  45
  45 |  46
  46 |  47
  47 |  48
  48 |  49
  49 |  50
  50 |  51
  51 |  52
  52 |  53
  53 |  54
  54 |  55
  55 |  56
  56 |  57
  57 |  58
  58 |  59
  59 |  60
  60 |  61
  61 |  62
  62 |  63
  63 |  64
  64 |  65
  65 |  66
  66 |  67
  67 |  68
  68 |  69
  69 |  70
  70 |  71
  71 |  72
  72 |  73
  73 |  74
  74 |  75
  75 |  76
  76 |  77
  77 |  78
  78 |  79
  79 |  80
  80 |  81
  81 |  82
  82 |  83
  83 |  84
  84 |  85
  85 |  86
  86 |  87
  87 |  88
  88 |  89
  89 |  90
  90 |  91
  91 |  92
  92 |  93
  93 |  94
  94 |  95
  95 |  96
  96 |  97
  97 |  98
  98 |  99
  99 | 100
 100 | 101
(100 rows)

fetch 100 from c; -- should give the next 100 rows
  a  |  b  
-----+-----
 101 | 102
 102 | 103
 103 | 104
 104 | 105
 105 | 106
 106 | 107
 107 | 108
 108 | 109
 109 | 110
 110 | 111
 111 | 112
 112 | 113
 113 | 114
 114 | 115
 115 | 116
 116 | 117
 117 | 118
 118 | 119
 119 | 120
 120 | 121
 121 | 122
 122 | 123
 123 | 124
 124 | 125
 125 | 126
 126 | 127
 127 | 128
 128 | 129
 129 | 130
 130 | 131
 131 | 132
 132 | 133
 133 | 134
 134 | 135
 135 | 136
 136 | 137
 137 | 138
 138 | 139
 139 | 140
 140 | 141
 141 | 142
 142 | 143
 143 | 144
 144 | 145
 145 | 146
 146 | 147
 147 | 148
 148 | 149
 149 | 150
 150 | 151
 151 | 152
 152 | 153
 153 | 154
 154 | 155
 155 | 156
 156 | 157
 157 | 158
 158 | 159
 159 | 160
 160 | 161
 161 | 162
 162 | 163
 163 | 164
 164 | 165
 165 | 166
 166 | 167
 167 | 168
 168 | 169
 169 | 170
 170 | 171
 171 | 172
 172 | 173
 173 | 174
 174 | 175
 175 | 176
 176 | 177
 177 | 178
 178 | 179
 179 | 180
 180 | 181
 181 | 182
 182 | 183
 183 | 184
 184 | 185
 185 | 186
 186 | 187
 187 | 188
 188 | 189
 189 | 190
 190 | 191
 191 | 192
 192 | 193
 193 | 194
 194 | 195
 195 | 196
 196 | 197
 197 | 198
 198 | 199
 199 | 200
 200 | 201
(100 rows)

fetch 1 from c; -- should give one row
  a  |  b  
-----+-----
 201 | 202
(1 row)

fetch backward 100 from c; -- should error out. we currently don't support this
ERROR:  backward scan is not supported in this version of Greenplum Database
rollback;
drop table qp_misc_jiras.tbl5305_test;
create external web table qp_misc_jiras.tbl_3739_foo (a text) execute E'' ON SEGMENT 0 FORMAT 'CSV';
ERROR:  invalid EXECUTE clause, command string is empty
show gp_interconnect_setup_timeout;
 gp_interconnect_setup_timeout 
-------------------------------
 2h
(1 row)

set gp_interconnect_setup_timeout=3600;
show gp_interconnect_setup_timeout;
 gp_interconnect_setup_timeout 
-------------------------------
 1h
(1 row)

set gp_interconnect_setup_timeout=10000;
ERROR:  10000 is outside the valid range for parameter "gp_interconnect_setup_timeout" (0 .. 7200)
set gp_interconnect_setup_timeout=4000;
create schema tbl5352_test;
select tablename from pg_tables where schemaname='tbl5352_test' and tablename not in( select partitiontablename from pg_partitions where partitionschemaname = 'tbl5352_test' );
 tablename 
-----------
(0 rows)

drop schema tbl5352_test;
CREATE TABLE qp_misc_jiras.tbl5223_sales_fact(
	   time     timestamp,
	   product  int, 
	   sales    numeric(12,2)
) distributed by (product);
copy qp_misc_jiras.tbl5223_sales_fact from stdin;
SELECT
  product, year, sales, 
  100*(1.0 - sales/(sum(sales) over (w))) as "% sales_growth"
FROM
  (SELECT
      product,
      extract(year from time) as year, 
	  extract(month from time) as month,
      sum(sales) as sales
   FROM qp_misc_jiras.tbl5223_sales_fact 
   GROUP BY product, year, month
  ) product_yearly_sales
window w as (partition by product order by year*12+month
             range between 12 preceding and 1 preceding)
order by year, product, sales; -- mvd 1,2->4
 product | year | sales |     % sales_growth      
---------+------+-------+-------------------------
       1 | 2007 |  2.42 |                        
       1 | 2007 |  2.49 | -2.89256198347107438000
       1 | 2007 |  2.49 | 66.75567423230974632800
       1 | 2007 |  2.54 | 74.54909819639278557100
       1 | 2007 |  2.58 | 47.45417515274949083500
       1 | 2007 |  3.19 | 74.52076677316293929700
       2 | 2007 | 25.50 | 56.77966101694915254200
       2 | 2007 | 27.50 |                        
       2 | 2007 | 27.50 | 81.22866894197952218400
       2 | 2007 | 30.50 | 73.70689655172413793100
       2 | 2007 | 31.50 |    -14.5454545454545500
       2 | 2007 | 31.50 | 62.72189349112426035500
       3 | 2007 | 16.62 | 66.70673076923076923100
       3 | 2007 | 16.64 |                        
       3 | 2007 | 16.64 | 50.00000000000000000000
       3 | 2007 | 16.64 |  0.00000000000000000000
       3 | 2007 | 16.64 | 74.99248572287345957300
       3 | 2007 | 49.92 | 39.98557345515748978100
       1 | 2008 |  2.06 | 89.54845256215119228800
       1 | 2008 |  2.07 | 89.21313183949973944800
       1 | 2008 |  2.09 | 88.86521044219499200900
       1 | 2008 |  4.42 | 71.86505410566518141300
       1 | 2008 |  4.49 | 74.64709203839638622200
       2 | 2008 |  2.29 | 98.82110682110682110700
       2 | 2008 | 20.25 | 90.53738317757009345800
       2 | 2008 | 22.25 | 89.34131736526946107800
       2 | 2008 | 23.25 | 88.17034700315457413200
       2 | 2008 | 25.25 | 87.34335839598997493700
       2 | 2008 | 47.50 | 72.70114942528735632200
       2 | 2008 | 51.50 | 73.45360824742268041200
       3 | 2008 |  9.30 | 91.80327868852459016400
       3 | 2008 |  9.32 | 92.72443403590944574600
       3 | 2008 |  9.32 | 92.28349064414638185100
       3 | 2008 |  9.32 | 91.21914452609760693400
       3 | 2008 | 11.64 | 91.25469571750563486100
       3 | 2008 | 28.96 | 70.69419145921878162300
(36 rows)

drop table qp_misc_jiras.tbl5223_sales_fact;
CREATE VIEW qp_misc_jiras.tbl4255_simple_v as SELECT 1 as value;
CREATE VIEW qp_misc_jiras.tbl4255_union_v as SELECT 1 as value UNION ALL SELECT 2;
SELECT * FROM qp_misc_jiras.tbl4255_simple_v;
 value 
-------
     1
(1 row)

SELECT generate_series(1,3), * from qp_misc_jiras.tbl4255_simple_v;
 generate_series | value 
-----------------+-------
               1 |     1
               2 |     1
               3 |     1
(3 rows)

SELECT * from qp_misc_jiras.tbl4255_union_v;
 value 
-------
     1
     2
(2 rows)

SELECT generate_series(1,3), * from qp_misc_jiras.tbl4255_union_v;
 generate_series | value 
-----------------+-------
               1 |     1
               2 |     1
               3 |     1
               1 |     2
               2 |     2
               3 |     2
(6 rows)

drop view qp_misc_jiras.tbl4255_simple_v;
drop view qp_misc_jiras.tbl4255_union_v;
create table qp_misc_jiras.tbl5246_sale
(       
        cn int not null,
        vn int not null,
        pn int not null,
        dt date not null,
        qty int not null,
        prc float not null,
        
        primary key (cn, vn, pn)
        
) distributed by (cn,vn,pn);
insert into qp_misc_jiras.tbl5246_sale values
  ( 2, 40, 100, '1401-1-1', 1100, 2400),
  ( 1, 10, 200, '1401-3-1', 1, 0),
  ( 3, 40, 200, '1401-4-1', 1, 0),
  ( 1, 20, 100, '1401-5-1', 1, 0),
  ( 1, 30, 300, '1401-5-2', 1, 0),
  ( 1, 50, 400, '1401-6-1', 1, 0),
  ( 2, 50, 400, '1401-6-1', 1, 0),
  ( 1, 30, 500, '1401-6-1', 12, 5),
  ( 3, 30, 500, '1401-6-1', 12, 5),
  ( 3, 30, 600, '1401-6-1', 12, 5),
  ( 4, 40, 700, '1401-6-1', 1, 1),
  ( 4, 40, 800, '1401-6-1', 1, 1);
explain select cn, count(*) over (order by dt range between '2 day'::interval preceding and 2 preceding) from qp_misc_jiras.tbl5246_sale;
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 WindowAgg  (cost=3.34..3.55 rows=12 width=8)
   Order By: dt
   ->  Gather Motion 3:1  (slice1; segments: 3)  (cost=3.34..3.37 rows=12 width=8)
         Merge Key: dt
         ->  Sort  (cost=3.34..3.37 rows=4 width=8)
               Sort Key: dt
               ->  Seq Scan on tbl5246_sale  (cost=0.00..3.12 rows=4 width=8)
 Settings:  enable_nestloop=on; optimizer=off
 Optimizer status: Postgres query optimizer
(9 rows)

drop table qp_misc_jiras.tbl5246_sale;
create table qp_misc_jiras.tbl4896_customer
(
        cn int not null,
        cname text not null,
        cloc text,
        primary key (cn)
) distributed by (cn);
create table qp_misc_jiras.tbl4896_sale
(
        cn int not null,
        vn int not null,
        pn int not null,
        dt date not null,
        qty int not null,
        prc float not null,
        
        primary key (cn, vn, pn)
        
) distributed by (cn,vn,pn);
insert into qp_misc_jiras.tbl4896_customer values
  ( 1, 'Macbeth', 'Inverness'),
  ( 2, 'Duncan', 'Forres'),
  ( 3, 'Lady Macbeth', 'Inverness'),
  ( 4, 'Witches, Inc', 'Lonely Heath');
insert into qp_misc_jiras.tbl4896_sale values
  ( 2, 40, 100, '1401-1-1', 1100, 2400),
  ( 1, 10, 200, '1401-3-1', 1, 0),
  ( 3, 40, 200, '1401-4-1', 1, 0),
  ( 1, 20, 100, '1401-5-1', 1, 0),
  ( 1, 30, 300, '1401-5-2', 1, 0),
  ( 1, 50, 400, '1401-6-1', 1, 0),
  ( 2, 50, 400, '1401-6-1', 1, 0),
  ( 1, 30, 500, '1401-6-1', 12, 5),
  ( 3, 30, 500, '1401-6-1', 12, 5),
  ( 3, 30, 600, '1401-6-1', 12, 5),
  ( 4, 40, 700, '1401-6-1', 1, 1),
  ( 4, 40, 800, '1401-6-1', 1, 1);
SELECT sale.cn*2 as newalias1,
CASE WHEN sale.vn < 10 THEN 1 ELSE 2 END as newalias2,
sale.cn + sale.cn as newalias3, 
TO_CHAR(COALESCE(MAX(DISTINCT floor(sale.vn/sale.cn)),0),'99999999.9999999'),
TO_CHAR(COALESCE(VARIANCE(DISTINCT floor(sale.vn/sale.cn)),0),'99999999.9999999'),
TO_CHAR(COALESCE(MAX(DISTINCT floor(sale.cn)),0),'99999999.9999999'),
TO_CHAR(COALESCE(AVG(DISTINCT floor(sale.vn+sale.pn)),0),'99999999.9999999'),
TO_CHAR(COALESCE(SUM(DISTINCT floor(sale.qty)),0),'99999999.9999999'),
TO_CHAR(COALESCE(COUNT(DISTINCT floor(sale.cn)),0),'99999999.9999999'),
TO_CHAR(COALESCE(MAX(DISTINCT floor(sale.cn)),0),'99999999.9999999'),
TO_CHAR(COALESCE(COUNT(DISTINCT floor(sale.qty)),0),'99999999.9999999'),
TO_CHAR(COALESCE(COUNT(DISTINCT floor(sale.cn)),0),'99999999.9999999')
FROM qp_misc_jiras.tbl4896_sale as sale ,qp_misc_jiras.tbl4896_customer as customer
WHERE sale.cn=customer.cn
GROUP BY CUBE((sale.dt),(newalias1,newalias2,newalias1),(sale.cn,sale.cn,sale.cn,newalias1),
(sale.qty),(sale.pn,newalias3,sale.vn),(sale.vn,sale.vn,sale.prc),(sale.cn,newalias2)),sale.cn,sale.vn;
ERROR:  GROUPING SETS / ROLLUP / CUBE columns ambiguous
drop table qp_misc_jiras.tbl4896_sale;
drop table qp_misc_jiras.tbl4896_customer;
create table qp_misc_jiras.tbl4703_test (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.
insert into qp_misc_jiras.tbl4703_test select i, i%10 from generate_series(0, 9999) i;
create index test_j on qp_misc_jiras.tbl4703_test(j);
set gp_select_invisible=true;
set enable_seqscan=off;
set enable_indexscan=off;
select count(*) from qp_misc_jiras.tbl4703_test where j = 5 or j = 10; --shouldn't crash
 count 
-------
  1000
(1 row)

drop table qp_misc_jiras.tbl4703_test;
create table qp_misc_jiras.tbl_694_1(c1 int, b1 box);
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.
select * from qp_misc_jiras.tbl_694_1;
 c1 | b1 
----+----
(0 rows)

create table qp_misc_jiras.tbl_694_2 (like qp_misc_jiras.tbl_694_1);
NOTICE:  table doesn't have 'DISTRIBUTED BY' clause, defaulting to distribution columns from LIKE table
select * from qp_misc_jiras.tbl_694_2;
 c1 | b1 
----+----
(0 rows)

insert into qp_misc_jiras.tbl_694_2 values(2,'(2,2),(2,2)');
insert into qp_misc_jiras.tbl_694_2 values(2,'(2,2),(3,2)');
insert into qp_misc_jiras.tbl_694_2 values(2,'(2,2),(3,3)');
insert into qp_misc_jiras.tbl_694_1 select * from qp_misc_jiras.tbl_694_2;
select * from qp_misc_jiras.tbl_694_2;
 c1 |     b1      
----+-------------
  2 | (2,2),(2,2)
  2 | (3,2),(2,2)
  2 | (3,3),(2,2)
(3 rows)

select * from qp_misc_jiras.tbl_694_2 join qp_misc_jiras.tbl_694_1 on qp_misc_jiras.tbl_694_2.b1=qp_misc_jiras.tbl_694_1.b1;
 c1 |     b1      | c1 |     b1      
----+-------------+----+-------------
  2 | (2,2),(2,2) |  2 | (2,2),(2,2)
  2 | (2,2),(2,2) |  2 | (3,2),(2,2)
  2 | (3,2),(2,2) |  2 | (2,2),(2,2)
  2 | (3,2),(2,2) |  2 | (3,2),(2,2)
  2 | (3,3),(2,2) |  2 | (3,3),(2,2)
(5 rows)

select * from qp_misc_jiras.tbl_694_1 join qp_misc_jiras.tbl_694_2 on qp_misc_jiras.tbl_694_1.b1=qp_misc_jiras.tbl_694_2.b1;
 c1 |     b1      | c1 |     b1      
----+-------------+----+-------------
  2 | (2,2),(2,2) |  2 | (2,2),(2,2)
  2 | (3,2),(2,2) |  2 | (2,2),(2,2)
  2 | (2,2),(2,2) |  2 | (3,2),(2,2)
  2 | (3,2),(2,2) |  2 | (3,2),(2,2)
  2 | (3,3),(2,2) |  2 | (3,3),(2,2)
(5 rows)

drop table qp_misc_jiras.tbl_694_1;
drop table qp_misc_jiras.tbl_694_2;
select * from ( select 'a' as a) x join (select 'a' as b) y on a=b;
 a | b 
---+---
 a | a
(1 row)

select * from ( ( select 'a' as a ) xx join (select 'a' as b) yy on a = b ) x join (select 'a' as c) y on a=c;
 a | b | c 
---+---+---
 a | a | a
(1 row)

select x.b from ( ( select 'a' as a ) xx join (select 'a' as b) yy on a = b ) x join (select 'a' as c) y on a=c;
 b 
---
 a
(1 row)

create table qp_misc_jiras.tbl6027_test (i int, j bigint, k int, l int, m 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 qp_misc_jiras.tbl6027_test select i, i%100, i%123, i%234, i%345 from generate_series(1, 500) i;
select j, sum(k), row_number() over (partition by j order by sum(k)) from qp_misc_jiras.tbl6027_test group by j order by j limit 10; -- order 1
 j | sum | row_number 
---+-----+------------
 0 | 270 |          1
 1 | 267 |          1
 2 | 272 |          1
 3 | 277 |          1
 4 | 282 |          1
 5 | 287 |          1
 6 | 292 |          1
 7 | 297 |          1
 8 | 302 |          1
 9 | 307 |          1
(10 rows)

CREATE TABLE qp_misc_jiras.tbl6419_test(
lstg_id numeric(38,0),
icedt date,
icehr smallint,
cre_time timestamp without time zone,
lstg_desc_txt text
)
WITH (appendonly=true, compresslevel=5) distributed by (lstg_id) PARTITION BY RANGE(cre_time)
(
START ('2009-05-26 00:00:00'::timestamp without time zone) 
END ('2009-06-05 00:00:00'::timestamp without time zone) EVERY ('1 day'::interval) WITH (appendonly=true, compresslevel=5)
);
NOTICE:  CREATE TABLE will create partition "tbl6419_test_1_prt_1" for table "tbl6419_test"
NOTICE:  CREATE TABLE will create partition "tbl6419_test_1_prt_2" for table "tbl6419_test"
NOTICE:  CREATE TABLE will create partition "tbl6419_test_1_prt_3" for table "tbl6419_test"
NOTICE:  CREATE TABLE will create partition "tbl6419_test_1_prt_4" for table "tbl6419_test"
NOTICE:  CREATE TABLE will create partition "tbl6419_test_1_prt_5" for table "tbl6419_test"
NOTICE:  CREATE TABLE will create partition "tbl6419_test_1_prt_6" for table "tbl6419_test"
NOTICE:  CREATE TABLE will create partition "tbl6419_test_1_prt_7" for table "tbl6419_test"
NOTICE:  CREATE TABLE will create partition "tbl6419_test_1_prt_8" for table "tbl6419_test"
NOTICE:  CREATE TABLE will create partition "tbl6419_test_1_prt_9" for table "tbl6419_test"
NOTICE:  CREATE TABLE will create partition "tbl6419_test_1_prt_10" for table "tbl6419_test"
insert into qp_misc_jiras.tbl6419_test values( 123, '2009-06-01', 12, '2009-06-01 01:01:01', 'aaaaaa');
select * from qp_misc_jiras.tbl6419_test where icedt::text = (select partitionrangestart FROM pg_partitions where tablename='test1' and schemaname='public' and partitionrank=1);
 lstg_id | icedt | icehr | cre_time | lstg_desc_txt 
---------+-------+-------+----------+---------------
(0 rows)

select * from qp_misc_jiras.tbl6419_test where '2009-12-12'::date::text = (select 'test'::text);
 lstg_id | icedt | icehr | cre_time | lstg_desc_txt 
---------+-------+-------+----------+---------------
(0 rows)

drop table qp_misc_jiras.tbl6419_test;
CREATE TABLE qp_misc_jiras.m_ccr_mthy_cr_nds_t00
(
cls_yymm date,
plyno character(16),
cr_yymm character(6),
cnrdt date,
ins_itm_smccd character(10),
ins_imcd character(10),
cr_udrtk_grdcd character(10),
nrdps_ctm_dscno character(20),
dh_stfno character(7),
ce_br_orgcd character(7),
ce_bzp_orgcd character(7),
ce_stfno character(7),
carno_hngl character(50),
nrdps_adr_sd character(30),
nrdps_adr_sgng character(30),
nrdps_adr_twmd character(30),
udrtk_gu_arecd character(10)
);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'cls_yymm' 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 qp_misc_jiras.m_ccr_cvr_nds_t99
(
cls_yymm date,
plyno character(16),
ndsno character(4),
nds_dt_seqno numeric(4),
nds_ap_dthms timestamp(0) without time zone,
nds_ap_dt date,
cr_yymm date,
cnrdt date,
ins_itm_smccd character(10),
ins_imcd character(10),
dh_stfno character(7),
ce_br_orgcd character(7),
ce_bzp_orgcd character(7),
ce_stfno character(7),
chbvl character varying(500),
chavl character varying(500),
nds_dt_itnm character varying(500),
nds_mgb character(3),
nds_lgb character(2)
)
WITH (OIDS=FALSE);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'cls_yymm' 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 qp_misc_jiras.ins_cr_nds_dt
(
plyno character varying(16) NOT NULL, 
ndsno character(4) NOT NULL, 
nds_dt_seqno numeric(5) NOT NULL, 
atrcd character(7) NOT NULL, 
chbvl character varying(500), 
chavl character varying(500), 
nds_dt_itnm character varying(500) NOT NULL, 
nds_note character varying(500), 
scr_idc_gp numeric(5), 
oj_or_relpc_flgcd character(10), 
ppr_seqno character(10), 
sbd_seqno character(10), 
dt_seqno character varying(20), 
chbf_cd character(10), 
chaf_cd character(10), 
bj_dscno character varying(13), 
bjnm character varying(100), 
dlflg_cd character(10), 
nds_tpcd character(10), 
ndscd character(10), 
da_ch_yn character(1), 
it_out_yn character(1), 
inp_usr_id character(7) NOT NULL, 
inp_dthms timestamp(0) without time zone NOT NULL, 
mdf_usr_id character(7) NOT NULL, 
mdf_dthms timestamp(0) without time zone NOT NULL, 
load_dthms timestamp(0) without time zone 
)
WITH (OIDS=FALSE);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'plyno' 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 qp_misc_jiras.ins_cr_nds_mstr
(
plyno character varying(16) NOT NULL, 
ndsno character(4) NOT NULL, 
ikd_grpcd character(10) NOT NULL, 
ch_rvsn_flgcd character(10), 
nds_cas_flgcd character(10), 
nds_wrdg character varying(2000), 
nds_prgcd character(10), 
prort_shtm_flgcd character(10), 
prort_cal_days numeric(5), 
prort_shtrt numeric(12,6) NOT NULL, 
nds_rqdt date, 
nds_ap_dthms timestamp(0) without time zone, 
otcm_mg_apdt date, 
nds_cr_flgcd character(10), 
nds_stdt date, 
nds_st_hms character(4), 
nds_nddt date, 
rqfno character(7), 
ap_stfno character(7), 
nds_acnmt_isdt date, 
nds_sno numeric(10), 
nds_ap_br_orgcd character(7), 
retr_nds_yn character(1), 
frc_nds_yn character(1), 
inp_trm_ip character varying(30), 
bsc_ndsno character(4), 
amt_cr_yn character(1), 
nds_plno character varying(16), 
nds_cgaf_ch_seqno numeric(5), 
retr_ndsof_ndsno character(4), 
whpy_mtdcd character(10), 
nds_rq_spc character varying(500), 
nds_rq_rs character varying(500), 
nds_ccmtd character varying(1000), 
inp_usr_id character(7) NOT NULL, 
inp_dthms timestamp(0) without time zone NOT NULL, 
mdf_usr_id character(7) NOT NULL, 
mdf_dthms timestamp(0) without time zone NOT NULL, 
frc_nds_cas_flgcd character(10), 
load_dthms timestamp(0) without time zone 
)
WITH (OIDS=FALSE);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'plyno' 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.
SELECT CLS_YYMM
,PLYNO
,NDSNO 
,NDS_DT_SEQNO 
,NDS_AP_DTHMS
,NDS_AP_DTHMS ::DATE AS NDS_AP_DT
,CR_YYMM 
,CNRDT 
,INS_ITM_SMCCD 
,INS_IMCD 
,DH_STFNO 
,CE_BR_ORGCD 
,CE_BZP_ORGCD 
,CE_STFNO 
,CHBVL 
,CHAVL 
,NDS_DT_ITNM 
,NDS_MGB
,NDS_LGB 
FROM 
( 
SELECT CLS_YYMM
,PLYNO
,NDSNO 
,NDS_DT_SEQNO 
,NDS_AP_DTHMS
,NDS_AP_DTHMS ::DATE AS NDS_AP_DT
,CR_YYMM 
,CNRDT 
,INS_ITM_SMCCD 
,INS_IMCD 
,DH_STFNO 
,CE_BR_ORGCD 
,CE_BZP_ORGCD 
,CE_STFNO 
,CHBVL 
,CHAVL 
,NDS_DT_ITNM 
,NDS_MGB
,NDS_LGB 
,RANK() OVER(PARTITION BY CLS_YYMM,PLYNO,NDSNO,NDS_MGB ORDER BY NDS_DT_SEQNO ASC) AS SQ
FROM
( 
SELECT S1.CLS_YYMM
,A1.PLYNO
,A1.NDSNO 
,A1.NDS_DT_SEQNO 
,A2.NDS_AP_DTHMS
,CAST(A2.NDS_AP_DTHMS AS DATE) NDS_AP_DT
,S1.CR_YYMM 
,S1.CNRDT 
,S1.INS_ITM_SMCCD 
,S1.INS_IMCD 
,S1.DH_STFNO 
,S1.CE_BR_ORGCD 
,S1.CE_BZP_ORGCD 
,S1.CE_STFNO 
,A1.CHBVL 
,A1.CHAVL 
,A1.NDS_DT_ITNM 
,CASE WHEN NDS_DT_ITNM IN ('긴급출동') THEN '201'
WHEN NDS_DT_ITNM IN ('대물2','대물배상가입금액','대물배상 가입금액') THEN '202'
WHEN NDS_DT_ITNM IN ('대인2','대인2 가입금액','대인배상가입금액') THEN '203'
WHEN NDS_DT_ITNM IN ('무보험(상해)','상해') THEN '204'
WHEN NDS_DT_ITNM IN ('자상','자상 가입금액','자손','자손가입금액','자기신체가입금액','자손확장특약가입금액') THEN '205'
WHEN NDS_DT_ITNM IN ('자차','자기차량가입금액','자기부담금액','자차 가입금액','자차 공제금액') THEN '206'
WHEN NDS_DT_ITNM IN ('기계 가입금액','기계장치가입금액') THEN '207'
ELSE '299' END NDS_MGB
,'2' NDS_LGB 
FROM qp_misc_jiras.M_CCR_MTHY_CR_NDS_T00 S1 /******공통사용예정********/
INNER JOIN qp_misc_jiras.INS_CR_NDS_DT A1
ON S1.PLYNO=A1.PLYNO
LEFT OUTER JOIN qp_misc_jiras.INS_CR_NDS_MSTR A2
ON A1.PLYNO = A2.PLYNO
AND A1.NDSNO = A2.NDSNO 
) T1
WHERE NDS_DT_ITNM LIKE ('%가입금액%')
AND (T1.PLYNO, T1.NDSNO, SUBSTR(T1.NDS_MGB,2,2)) NOT IN (
SELECT PLYNO
,NDSNO
,SUBSTR(NDS_MGB,2,2) AS NDS_MGB
FROM qp_misc_jiras.M_CCR_CVR_NDS_T99
) 
) T
WHERE T.SQ = 1 
;
 cls_yymm | plyno | ndsno | nds_dt_seqno | nds_ap_dthms | nds_ap_dt | cr_yymm | cnrdt | ins_itm_smccd | ins_imcd | dh_stfno | ce_br_orgcd | ce_bzp_orgcd | ce_stfno | chbvl | chavl | nds_dt_itnm | nds_mgb | nds_lgb 
----------+-------+-------+--------------+--------------+-----------+---------+-------+---------------+----------+----------+-------------+--------------+----------+-------+-------+-------------+---------+---------
(0 rows)

drop table qp_misc_jiras.ins_cr_nds_dt, qp_misc_jiras.ins_cr_nds_mstr, qp_misc_jiras.m_ccr_cvr_nds_t99, qp_misc_jiras.m_ccr_mthy_cr_nds_t00;
set gp_select_invisible=false;
create table qp_misc_jiras.tbl6448 (x "char");
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'x' 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 qp_misc_jiras.tbl6448 values ('a');
select x from qp_misc_jiras.tbl6448;
 x 
---
 a
(1 row)

drop table qp_misc_jiras.tbl6448;
create table qp_misc_jiras.tbl6833_anl(a int, b int, c int) distributed by (a) partition by range (b) (partition bb start (0) end (2) every (1));
NOTICE:  CREATE TABLE will create partition "tbl6833_anl_1_prt_bb_1" for table "tbl6833_anl"
NOTICE:  CREATE TABLE will create partition "tbl6833_anl_1_prt_bb_2" for table "tbl6833_anl"
analyze qp_misc_jiras.tbl6833_anl;
explain select * from qp_misc_jiras.tbl6833_anl; -- should not hit cdbRelSize();
                                              QUERY PLAN                                               
-------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=10000000000.00..20000000002.00 rows=2 width=12)
   ->  Append  (cost=10000000000.00..20000000002.00 rows=1 width=12)
         ->  Seq Scan on tbl6833_anl_1_prt_bb_1  (cost=10000000000.00..10000000001.00 rows=1 width=12)
         ->  Seq Scan on tbl6833_anl_1_prt_bb_2  (cost=10000000000.00..10000000001.00 rows=1 width=12)
 Optimizer: Postgres query optimizer
(5 rows)

select relname, reltuples, relpages from pg_class where relname like 'tbl6833_anl%'; -- should show relpages = 1.0
        relname         | reltuples | relpages 
------------------------+-----------+----------
 tbl6833_anl_1_prt_bb_1 |         0 |        1
 tbl6833_anl_1_prt_bb_2 |         0 |        1
 tbl6833_anl            |         0 |        0
(3 rows)

create table qp_misc_jiras.tbl6833_vac(a int, b int, c int) distributed by (a) partition by range (b) (partition bb start (0) end (2) every (1));
NOTICE:  CREATE TABLE will create partition "tbl6833_vac_1_prt_bb_1" for table "tbl6833_vac"
NOTICE:  CREATE TABLE will create partition "tbl6833_vac_1_prt_bb_2" for table "tbl6833_vac"
vacuum qp_misc_jiras.tbl6833_vac;
explain select * from qp_misc_jiras.tbl6833_vac; -- should not hit cdbRelSize();
                                              QUERY PLAN                                               
-------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=10000000000.00..20000000002.00 rows=2 width=12)
   ->  Append  (cost=10000000000.00..20000000002.00 rows=1 width=12)
         ->  Seq Scan on tbl6833_vac_1_prt_bb_1  (cost=10000000000.00..10000000001.00 rows=1 width=12)
         ->  Seq Scan on tbl6833_vac_1_prt_bb_2  (cost=10000000000.00..10000000001.00 rows=1 width=12)
 Optimizer: Postgres query optimizer
(5 rows)

select relname, reltuples, relpages from pg_class where relname like 'tbl6833_vac%'; -- should show relpages = 1.0
        relname         | reltuples | relpages 
------------------------+-----------+----------
 tbl6833_vac_1_prt_bb_1 |         0 |        1
 tbl6833_vac_1_prt_bb_2 |         0 |        1
 tbl6833_vac            |         0 |        1
(3 rows)

drop table qp_misc_jiras.tbl6833_anl;
drop table qp_misc_jiras.tbl6833_vac;
-- actual query
drop table if exists qp_misc_jiras_foo;
NOTICE:  table "qp_misc_jiras_foo" does not exist, skipping
create table qp_misc_jiras_foo(x int) distributed by (x);
SELECT t.distkey, a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod),
a.attnotnull, a.attnum, pg_catalog.col_description(a.attrelid, a.attnum)
FROM pg_catalog.pg_class c
LEFT outer JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
left outer join pg_catalog.gp_distribution_policy t on localoid = c.oid
left outer join pg_catalog.pg_attribute a on a.attrelid = c.oid and a.attnum in (select unnest(t.distkey))
WHERE c.relname ~ '^(qp_misc_jiras_foo)$';
 distkey | attname | format_type | attnotnull | attnum | col_description 
---------+---------+-------------+------------+--------+-----------------
 1       | x       | integer     | f          |      1 | 
(1 row)

-- previously supported query
create table qp_misc_jiras_bar (t int, d int, g int);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 't' 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 qp_misc_jiras_bar values(1,2,3);
insert into qp_misc_jiras_bar values(4,5,6);
select a.t from qp_misc_jiras_bar a where d in(select d from qp_misc_jiras_bar b where a.g=b.g) order by a.t;
 t 
---
 1
 4
(2 rows)

-- Various AO/CO util functions
-- ctas from one storage type to another
create table qp_misc_jiras.tbl7126_ao (a int, b text, c date) with (appendonly=true);
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 qp_misc_jiras.tbl7126_ao select i, 'abcd'||i, '1981-10-02' from generate_series(1,100000)i;
create table qp_misc_jiras.tbl7126_ao_zlib3 with (appendonly=true, compresstype = zlib, compresslevel=3 ) as select * from qp_misc_jiras.tbl7126_ao;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) 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 qp_misc_jiras.tbl7126_co with (appendonly=true,orientation=column) as select * from qp_misc_jiras.tbl7126_ao_zlib3;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) 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 qp_misc_jiras.tbl7126_co_zlib3  with (appendonly=true,orientation=column, compresstype=zlib, compresslevel=3) as select * from qp_misc_jiras.tbl7126_co;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) 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.
-- selects
select count(*) from qp_misc_jiras.tbl7126_ao;
 count  
--------
 100000
(1 row)

select count(*) from qp_misc_jiras.tbl7126_ao_zlib3;
 count  
--------
 100000
(1 row)

select count(*) from qp_misc_jiras.tbl7126_co;
 count  
--------
 100000
(1 row)

select count(*) from qp_misc_jiras.tbl7126_co_zlib3;
 count  
--------
 100000
(1 row)

-- util functions
-- start_ignore
select get_ao_compression_ratio('qp_misc_jiras.tbl7126_ao');
 get_ao_compression_ratio 
--------------------------
                        1
(1 row)

select get_ao_compression_ratio('qp_misc_jiras.tbl7126_ao_zlib3');
 get_ao_compression_ratio 
--------------------------
                     4.21
(1 row)

select get_ao_compression_ratio('qp_misc_jiras.tbl7126_co');
 get_ao_compression_ratio 
--------------------------
                        1
(1 row)

select get_ao_compression_ratio('qp_misc_jiras.tbl7126_co_zlib3');
 get_ao_compression_ratio 
--------------------------
                     4.38
(1 row)

select get_ao_distribution('qp_misc_jiras.tbl7126_ao');
 get_ao_distribution 
---------------------
 (2,33316)
 (1,33336)
 (0,33348)
(3 rows)

select get_ao_distribution('qp_misc_jiras.tbl7126_ao_zlib3');
 get_ao_distribution 
---------------------
 (2,33316)
 (1,33336)
 (0,33348)
(3 rows)

select get_ao_distribution('qp_misc_jiras.tbl7126_co');
 get_ao_distribution 
---------------------
 (2,33316)
 (0,33348)
 (1,33336)
(3 rows)

select get_ao_distribution('qp_misc_jiras.tbl7126_co_zlib3');
 get_ao_distribution 
---------------------
 (2,33316)
 (0,33348)
 (1,33336)
(3 rows)

-- end_ignore
select gp_update_ao_master_stats('qp_misc_jiras.tbl7126_ao');
 gp_update_ao_master_stats 
---------------------------
                    100000
(1 row)

select gp_update_ao_master_stats('qp_misc_jiras.tbl7126_ao_zlib3');
 gp_update_ao_master_stats 
---------------------------
                    100000
(1 row)

select gp_update_ao_master_stats('qp_misc_jiras.tbl7126_co');
 gp_update_ao_master_stats 
---------------------------
                    100000
(1 row)

select gp_update_ao_master_stats('qp_misc_jiras.tbl7126_co_zlib3');
 gp_update_ao_master_stats 
---------------------------
                    100000
(1 row)

-- drop the objects created
drop table qp_misc_jiras.tbl7126_ao;
drop table qp_misc_jiras.tbl7126_ao_zlib3;
drop table qp_misc_jiras.tbl7126_co;
drop table qp_misc_jiras.tbl7126_co_zlib3;
create table pre_visitor_ca_event_ao(
visitor_timestamp timestamp without time zone,
visitor_key varchar,
visitor_domain varchar,
visitor_firstvisited timestamp without time zone,
visitor_lastvisited timestamp without time zone,
visitor_useragent timestamp without time zone,
visitor_referer timestamp without time zone,
visitor_camifymemberid numeric,
visitor_language varchar,
visitor_labels varchar,
visitor_city varchar,
visitor_country varchar,
visitor_countrycode varchar,
visitor_ipaddress varchar,
visitor_latitude real,
visitor_longitude real,
visitor_organization varchar,
visitor_browser varchar,
visitor_browserversion varchar,
visitor_os varchar,
visitor_osversion varchar,
visitor_age numeric,
visitor_alias varchar,
visitor_avatar varchar,
visitor_profileurl varchar,
visitor_gender varchar,
contentaccess_accesseddate timestamp without time zone,
contentaccess_contentkey varchar,
contentaccess_domain varchar,
contentaccess_exiteddate timestamp without time zone,
contentaccess_status varchar,
contentaccess_url varchar,
contentaccess_contenttype varchar,
event_date timestamp without time zone,
event_name varchar,
event_duration numeric,
contentaccesslabels_name varchar,
contentaccesslabels_value varchar,
contentaccesslabels_date timestamp without time zone,
event_description varchar,
visitor_state varchar,
contentaccess_applicationid varchar) with (appendonly=true); 
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'visitor_timestamp' 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.
copy pre_visitor_ca_event_ao from STDIN with delimiter '~' null '';
create table pre_visitor_ca_event_cao with (appendonly=true,orientation=column) as select * from pre_visitor_ca_event_ao;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'visitor_timestamp' 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.
select count(*) from pre_visitor_ca_event_ao;
 count 
-------
    50
(1 row)

select count(*) from pre_visitor_ca_event_cao;
 count 
-------
    50
(1 row)

drop table pre_visitor_ca_event_ao;
drop table pre_visitor_ca_event_cao;
create table qp_misc_jiras.tbl6701_test(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 qp_misc_jiras.tbl6701_test values('{1,2,3}');
select i from qp_misc_jiras.tbl6701_test;
    i    
---------
 {1,2,3}
(1 row)

select i from qp_misc_jiras.tbl6701_test group by i;
    i    
---------
 {1,2,3}
(1 row)

drop table qp_misc_jiras.tbl6701_test;
select name,category from pg_settings where name like 'gp_enable_predicate_propagation';
              name               |               category               
---------------------------------+--------------------------------------
 gp_enable_predicate_propagation | Query Tuning / Other Planner Options
(1 row)

create table qp_misc_jiras.tbl7161_co(a int, b varchar);
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 qp_misc_jiras.tbl7161_co (a, b) select i, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || i from generate_series(0, 0) i;
select count(*) from qp_misc_jiras.tbl7161_co;
 count 
-------
     1
(1 row)

insert into qp_misc_jiras.tbl7161_co (a, b) select i, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || i from generate_series(0, 15) i;
select count(*) from qp_misc_jiras.tbl7161_co;
 count 
-------
    17
(1 row)

insert into qp_misc_jiras.tbl7161_co (a, b) select i, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || i from generate_series(0, 11) i;
select count(*) from qp_misc_jiras.tbl7161_co;
 count 
-------
    29
(1 row)

insert into qp_misc_jiras.tbl7161_co (a, b) select i, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || i from generate_series(0, 16) i;
select count(*) from qp_misc_jiras.tbl7161_co;
 count 
-------
    46
(1 row)

insert into qp_misc_jiras.tbl7161_co (a, b) select i, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || i from generate_series(0, 3) i;
select count(*) from qp_misc_jiras.tbl7161_co;
 count 
-------
    50
(1 row)

insert into qp_misc_jiras.tbl7161_co (a, b) select i, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || i from generate_series(0, 7) i;
select count(*) from qp_misc_jiras.tbl7161_co;
 count 
-------
    58
(1 row)

insert into qp_misc_jiras.tbl7161_co (a, b) select i, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || i from generate_series(0, 5) i;
select count(*) from qp_misc_jiras.tbl7161_co;
 count 
-------
    64
(1 row)

insert into qp_misc_jiras.tbl7161_co (a, b) select i, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || i from generate_series(0, 9) i;
select count(*) from qp_misc_jiras.tbl7161_co;
 count 
-------
    74
(1 row)

insert into qp_misc_jiras.tbl7161_co (a, b) select i, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || i from generate_series(0, 1) i;
select count(*) from qp_misc_jiras.tbl7161_co;
 count 
-------
    76
(1 row)

insert into qp_misc_jiras.tbl7161_co (a, b) select i, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || i from generate_series(0, 17) i;
select count(*) from qp_misc_jiras.tbl7161_co;
 count 
-------
    94
(1 row)

insert into qp_misc_jiras.tbl7161_co (a, b) select i, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || i from generate_series(0, 9) i;
select count(*) from qp_misc_jiras.tbl7161_co;
 count 
-------
   104
(1 row)

insert into qp_misc_jiras.tbl7161_co (a, b) select i, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || i from generate_series(0, 13) i;
select count(*) from qp_misc_jiras.tbl7161_co;
 count 
-------
   118
(1 row)

insert into qp_misc_jiras.tbl7161_co (a, b) select i, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || i from generate_series(0, 12) i;
select count(*) from qp_misc_jiras.tbl7161_co;
 count 
-------
   131
(1 row)

insert into qp_misc_jiras.tbl7161_co (a, b) select i, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || i from generate_series(0, 6) i;
select count(*) from qp_misc_jiras.tbl7161_co;
 count 
-------
   138
(1 row)

insert into qp_misc_jiras.tbl7161_co (a, b) select i, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || i from generate_series(0, 11) i;
select count(*) from qp_misc_jiras.tbl7161_co;
 count 
-------
   150
(1 row)

insert into qp_misc_jiras.tbl7161_co (a, b) select i, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || i from generate_series(0, 17) i;
select count(*) from qp_misc_jiras.tbl7161_co;
 count 
-------
   168
(1 row)

insert into qp_misc_jiras.tbl7161_co (a, b) select i, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || i from generate_series(0, 3) i;
select count(*) from qp_misc_jiras.tbl7161_co;
 count 
-------
   172
(1 row)

insert into qp_misc_jiras.tbl7161_co (a, b) select i, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || i from generate_series(0, 6) i;
select count(*) from qp_misc_jiras.tbl7161_co;
 count 
-------
   179
(1 row)

insert into qp_misc_jiras.tbl7161_co (a, b) select i, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || i from generate_series(0, 16) i;
select count(*) from qp_misc_jiras.tbl7161_co;
 count 
-------
   196
(1 row)

insert into qp_misc_jiras.tbl7161_co (a, b) select i, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || i from generate_series(0, 18) i;
select count(*) from qp_misc_jiras.tbl7161_co;
 count 
-------
   215
(1 row)

insert into qp_misc_jiras.tbl7161_co (a, b) select i, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || i from generate_series(0, 10) i;
select count(*) from qp_misc_jiras.tbl7161_co;
 count 
-------
   226
(1 row)

insert into qp_misc_jiras.tbl7161_co (a, b) select i, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' || i from generate_series(0, 4) i;
select count(*) from qp_misc_jiras.tbl7161_co;
 count 
-------
   231
(1 row)

drop table qp_misc_jiras.tbl7161_co;
select i, j into qp_misc_jiras.tbl6535_table from generate_series(1, 200) i, generate_series(1, 201) j;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) 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 qp_misc_jiras.tbl6535_table select * from qp_misc_jiras.tbl6535_table ;
insert into qp_misc_jiras.tbl6535_table select * from qp_misc_jiras.tbl6535_table ;
insert into qp_misc_jiras.tbl6535_table select * from qp_misc_jiras.tbl6535_table ;
insert into qp_misc_jiras.tbl6535_table select * from qp_misc_jiras.tbl6535_table ;
insert into qp_misc_jiras.tbl6535_table select * from qp_misc_jiras.tbl6535_table ;
set gp_enable_agg_distinct_pruning  = off;
select count(distinct i), count(distinct j) from qp_misc_jiras.tbl6535_table;
 count | count 
-------+-------
   200 |   201
(1 row)

set gp_enable_agg_distinct = off;
select count(distinct i), count(distinct j) from qp_misc_jiras.tbl6535_table;
 count | count 
-------+-------
   200 |   201
(1 row)

drop table qp_misc_jiras.tbl6535_table;
create table statement_timeout_test(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 statement_timeout_test select i,i+1 from generate_series(1,10000)i;
prepare prestmt as select * from statement_timeout_test s1, statement_timeout_test s2, statement_timeout_test s3, statement_timeout_test s4, statement_timeout_test s5;
set statement_timeout = 1000; -- 1 sec
execute prestmt; -- should get cancelled automatically
ERROR:  canceling statement due to statement timeout
drop table statement_timeout_test;
reset statement_timeout;
set gp_autostats_mode=none;
-- Test for an old bug with ANALYZE (analyze code has been rewritten since).
create table qp_misc_jiras.tbl_6934(x inet);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'x' 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 qp_misc_jiras.tbl_6934 select (i%200 || '.' || i%11 || '.' || i%11 || '.' || i%100)::inet from generate_series(1,40000) i;
analyze qp_misc_jiras.tbl_6934; 
drop table qp_misc_jiras.tbl_6934;
create table qp_misc_jiras.tbl7286_test (i int, d date);
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 qp_misc_jiras.tbl7286_test select i%10, '2009/01/01'::date + (i || ' days')::interval from generate_series(0, 99999) i;
set gp_enable_agg_distinct=off;
set gp_enable_agg_distinct_pruning=off;
set statement_mem='1000kB';
set optimizer_force_three_stage_scalar_dqa=off;
select count(distinct d) from qp_misc_jiras.tbl7286_test;
 count  
--------
 100000
(1 row)

drop table qp_misc_jiras.tbl7286_test;
create table qp_misc_jiras.tbl7381_test (i int, t text, d date, ti time with time zone);
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.
alter table qp_misc_jiras.tbl7381_test alter column t set storage external;
insert into qp_misc_jiras.tbl7381_test select i, '0123456789' || i, current_date, current_time from generate_series(0, 999) i;
insert into qp_misc_jiras.tbl7381_test select i, t||t||t||t||t||t||t||t||t||t, d, ti from qp_misc_jiras.tbl7381_test;
insert into qp_misc_jiras.tbl7381_test select i, t||t||t||t||t||t||t||t||t||t, d, ti from qp_misc_jiras.tbl7381_test;
insert into qp_misc_jiras.tbl7381_test select i, t||t||t||t||t||t||t||t||t||t, d, ti from qp_misc_jiras.tbl7381_test;
select max(length(t)) from qp_misc_jiras.tbl7381_test;
  max  
-------
 13000
(1 row)

create table qp_misc_jiras.tbl7381_test1 (i int, t text, d date, ti time with time zone);
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.
alter table qp_misc_jiras.tbl7381_test1 alter column t set storage extended;
insert into qp_misc_jiras.tbl7381_test1 select i,t,current_date, current_time from qp_misc_jiras.tbl7381_test;
select count(*) from qp_misc_jiras.tbl7381_test1;
 count 
-------
  8000
(1 row)

drop table qp_misc_jiras.tbl7381_test;
drop table qp_misc_jiras.tbl7381_test1;
set gp_autostats_mode=none;
create table qp_misc_jiras.tbl7404_t1(x text);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'x' 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 qp_misc_jiras.tbl7404_t2(x text);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'x' 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 qp_misc_jiras.tbl7404_t1 select ('foo'|| i::text) from generate_series(1,2) i;
insert into qp_misc_jiras.tbl7404_t2 select x from qp_misc_jiras.tbl7404_t1;
--Setting to modify system catalog
set allow_system_table_mods=true;
--
-- Case 1: make t1 and t2 appear really big
--
update pg_class set reltuples=100000, relpages=1000 where relname='qp_misc_jiras.tbl7404_t1';
update pg_class set reltuples=100000, relpages=1000 where relname='qp_misc_jiras.tbl7404_t2';
--explain select count(*) from qp_misc_jiras.tbl7404_t1 where substr(x,0,2) in (select substr(x,0,2) from qp_misc_jiras.tbl7404_t2);
select count(*) from qp_misc_jiras.tbl7404_t1 where substr(x,0,2) in (select substr(x,0,2) from qp_misc_jiras.tbl7404_t2);
 count 
-------
     2
(1 row)

--
-- Case 2: make t1, t2 appear small
--
update pg_class set reltuples=1, relpages=1 where relname='qp_misc_jiras.tbl7404_t1';
update pg_class set reltuples=1, relpages=1 where relname='qp_misc_jiras.tbl7404_t2';
--explain select * from qp_misc_jiras.tbl7404_t1 where substr(x,0,2) in (select substr(x,0,2) from qp_misc_jiras.tbl7404_t2);
select count(*) from qp_misc_jiras.tbl7404_t1 where substr(x,0,2) in (select substr(x,0,2) from qp_misc_jiras.tbl7404_t2);
 count 
-------
     2
(1 row)

select to_char(10, '999E99');
 to_char 
---------
    E 10
(1 row)

create table qp_misc_jiras.tbl7616_test (a int, b text) with (appendonly=true, orientation=column) distributed by (a);
insert into qp_misc_jiras.tbl7616_test select generate_series(1,1000), generate_series(1,1000);
select count(a.*) from qp_misc_jiras.tbl7616_test a inner join qp_misc_jiras.tbl7616_test b using (a);
 count 
-------
  1000
(1 row)

select count(a.b) from qp_misc_jiras.tbl7616_test a inner join qp_misc_jiras.tbl7616_test b using (a);
 count 
-------
  1000
(1 row)

drop table qp_misc_jiras.tbl7616_test;
create table qp_misc_jiras.tbl6874 (a int, b text) distributed by (a);
\d+ qp_misc_jiras.tbl6874
                    Table "qp_misc_jiras.tbl6874"
 Column |  Type   | Modifiers | Storage  | Stats target | Description 
--------+---------+-----------+----------+--------------+-------------
 a      | integer |           | plain    |              | 
 b      | text    |           | extended |              | 
Distributed by: (a)

insert into qp_misc_jiras.tbl6874 values (generate_series(1,10),'test_1');
create index tbl6874_a on qp_misc_jiras.tbl6874 using bitmap(a);
\d+ qp_misc_jiras.tbl6874
                    Table "qp_misc_jiras.tbl6874"
 Column |  Type   | Modifiers | Storage  | Stats target | Description 
--------+---------+-----------+----------+--------------+-------------
 a      | integer |           | plain    |              | 
 b      | text    |           | extended |              | 
Indexes:
    "tbl6874_a" bitmap (a)
Distributed by: (a)

drop index qp_misc_jiras.tbl6874_a;
\d+ qp_misc_jiras.tbl6874
                    Table "qp_misc_jiras.tbl6874"
 Column |  Type   | Modifiers | Storage  | Stats target | Description 
--------+---------+-----------+----------+--------------+-------------
 a      | integer |           | plain    |              | 
 b      | text    |           | extended |              | 
Distributed by: (a)

drop table qp_misc_jiras.tbl6874;
CREATE TABLE qp_misc_jiras.tbl7740_rank (id int, gender char(1), count char(1) )
            DISTRIBUTED BY (id)
            PARTITION BY LIST (gender,count)
            ( PARTITION girls VALUES (('F','1')),
              PARTITION boys VALUES (('M','1')),
              DEFAULT PARTITION other );
NOTICE:  CREATE TABLE will create partition "tbl7740_rank_1_prt_girls" for table "tbl7740_rank"
NOTICE:  CREATE TABLE will create partition "tbl7740_rank_1_prt_boys" for table "tbl7740_rank"
NOTICE:  CREATE TABLE will create partition "tbl7740_rank_1_prt_other" for table "tbl7740_rank"
insert into qp_misc_jiras.tbl7740_rank values(1,'F','1');
insert into qp_misc_jiras.tbl7740_rank values(1,'F','0');
insert into qp_misc_jiras.tbl7740_rank values(1,'M','1');
insert into qp_misc_jiras.tbl7740_rank values(1,'M','0');
-- start_ignore 
alter table qp_misc_jiras.tbl7740_rank split default partition at (values(('F', '0'))) into (partition mother, partition other);
NOTICE:  exchanged partition "other" of relation "tbl7740_rank" with relation "pg_temp_33751"
NOTICE:  dropped partition "other" for relation "tbl7740_rank"
NOTICE:  CREATE TABLE will create partition "tbl7740_rank_1_prt_mother" for table "tbl7740_rank"
NOTICE:  CREATE TABLE will create partition "tbl7740_rank_1_prt_other" for table "tbl7740_rank"
-- end_ignore
select * from qp_misc_jiras.tbl7740_rank_1_prt_girls;
 id | gender | count 
----+--------+-------
  1 | F      | 1
(1 row)

select * from qp_misc_jiras.tbl7740_rank_1_prt_boys;
 id | gender | count 
----+--------+-------
  1 | M      | 1
(1 row)

select * from qp_misc_jiras.tbl7740_rank_1_prt_mother;
 id | gender | count 
----+--------+-------
  1 | F      | 0
(1 row)

select * from qp_misc_jiras.tbl7740_rank_1_prt_other;
 id | gender | count 
----+--------+-------
  1 | M      | 0
(1 row)

drop table qp_misc_jiras.tbl7740_rank;
-- This is the reproduction material for bug 7027.
-- Most of this is taken from an existing test, so this might be 
-- redundant, but it's easy to add it here and then we don't have to 
-- worry about the original test being modified in a way that no longer 
-- tests this scenario.
-- Create a column-oriented table.
CREATE TABLE qp_misc_jiras.one_of_every_data_type (
    id BIGINT,
    lseg_col LSEG,
    serial_col SERIAL
    ) 
 DISTRIBUTED BY (id)
 ;
CREATE FUNCTION TO_LSEG(TEXT) RETURNS LSEG AS
  $$
    SELECT lseg_in(textout($1))
  $$ LANGUAGE SQL;
INSERT INTO qp_misc_jiras.one_of_every_data_type ( lseg_col)
 VALUES (
  TO_LSEG('((' || 1 || ', ' || 1 || '), (' || 1 + 1 || ', ' || 1 + 1 || '))')
  ) ;
INSERT INTO qp_misc_jiras.one_of_every_data_type ( lseg_col)
 VALUES (
  TO_LSEG('((' || 1 || ', ' || 1 || '), (' || 1 + 1 || ', ' || 1 + 1 || '))')
  ) ;
-- Retrieve data from the table.
SELECT * FROM qp_misc_jiras.one_of_every_data_type ORDER BY id,serial_col;
 id |   lseg_col    | serial_col 
----+---------------+------------
    | [(1,1),(2,2)] |          1
    | [(1,1),(2,2)] |          2
(2 rows)

-- Clean up.
DROP TABLE IF EXISTS qp_misc_jiras.one_of_every_data_type; 
create table qp_misc_jiras.tbl7553_test (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.
insert into qp_misc_jiras.tbl7553_test values(1,2);
explain select i as a, i as b from qp_misc_jiras.tbl7553_test group by grouping sets( (a, b), (a));
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice2; segments: 3)  (cost=10000008731.75..10000008759.25 rows=2750 width=20)
   ->  HashAggregate  (cost=10000008731.75..10000008759.25 rows=917 width=20)
         Group Key: "rollup".a, "rollup".a_1, "rollup"."grouping", "rollup"."group_id"
         ->  Subquery Scan on "rollup"  (cost=10000008018.50..10000008724.25 rows=250 width=20)
               ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=10000008018.50..10000008716.75 rows=250 width=20)
                     Hash Key: rollup_1.a, rollup_1.a_1, (Grouping), group_id()
                     ->  GroupAggregate  (cost=10000008018.50..10000008701.75 rows=250 width=20)
                           Group Key: rollup_1.a, rollup_1."grouping", rollup_1."group_id"
                           ->  Subquery Scan on rollup_1  (cost=10000008018.50..10000008684.25 rows=334 width=20)
                                 ->  GroupAggregate  (cost=10000008018.50..10000008674.25 rows=334 width=20)
                                       Group Key: tbl7553_test.i, tbl7553_test.i
                                       ->  Sort  (cost=10000008018.50..10000008233.75 rows=28700 width=4)
                                             Sort Key: tbl7553_test.i, tbl7553_test.i
                                             ->  Seq Scan on tbl7553_test  (cost=10000000000.00..10000000961.00 rows=28700 width=4)
 Optimizer: Postgres query optimizer
(15 rows)

select i as a, i as b from qp_misc_jiras.tbl7553_test group by grouping sets( (a, b), (a)); 
 a | b 
---+---
 1 | 1
 1 | 1
(2 rows)

explain select j as a, j as b from qp_misc_jiras.tbl7553_test group by grouping sets( (a, b), (a)); 
                                                                       QUERY PLAN                                                                       
--------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice2; segments: 3)  (cost=116.12..139.79 rows=2367 width=8)
   ->  HashAggregate  (cost=116.12..139.79 rows=789 width=8)
         Group By: partial_aggregation.a, partial_aggregation.a, partial_aggregation."grouping", partial_aggregation."group_id"
         ->  Subquery Scan on partial_aggregation  (cost=51.58..109.67 rows=215 width=20)
               ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=51.58..103.22 rows=215 width=20)
                     Hash Key: "rollup".a, "rollup".a
                     ->  GroupAggregate  (cost=51.58..90.32 rows=215 width=20)
                           Group By: "rollup".a, "rollup"."grouping", "rollup"."group_id"
                           ->  Subquery Scan on "rollup"  (cost=51.58..75.26 rows=287 width=20)
                                 ->  GroupAggregate  (cost=51.58..66.65 rows=287 width=20)
                                       Group By: tbl7553_test.j, tbl7553_test.j
                                       ->  Sort  (cost=51.58..53.74 rows=287 width=4)
                                             Sort Key: tbl7553_test.j, tbl7553_test.j
                                             ->  Seq Scan on tbl7553_test  (cost=0.00..9.61 rows=287 width=4)
 Settings:  enable_indexscan=off; enable_nestloop=on; enable_seqscan=off; gp_enable_agg_distinct=off; gp_enable_agg_distinct_pruning=off; optimizer=off
 Optimizer status: Postgres query optimizer
(16 rows)

select j as a, j as b from qp_misc_jiras.tbl7553_test group by grouping sets( (a, b), (a)); 
 a | b 
---+---
 2 | 2
 2 | 2
(2 rows)

drop table qp_misc_jiras.tbl7553_test;
-- Check that a table created with CTAS "inherits" the distribution key from
-- the source table.
create table qp_misc_jiras.tbl7268_foo (a varchar(15), b varchar(15)) distributed by (b);
\d qp_misc_jiras.tbl7268_foo;
     Table "qp_misc_jiras.tbl7268_foo"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 a      | character varying(15) | 
 b      | character varying(15) | 
Distributed by: (b)

create table qp_misc_jiras.tbl7268_bar as select * from qp_misc_jiras.tbl7268_foo;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) 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.
\d qp_misc_jiras.tbl7268_bar; 
     Table "qp_misc_jiras.tbl7268_bar"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 a      | character varying(15) | 
 b      | character varying(15) | 
Distributed by: (b)

drop table if exists qp_misc_jiras.tbl7268_foo;
drop table if exists qp_misc_jiras.tbl7268_bar;
create table qp_misc_jiras.tbl7268_foo (a int, b int) distributed by (b);
\d qp_misc_jiras.tbl7268_foo;
Table "qp_misc_jiras.tbl7268_foo"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer | 
 b      | integer | 
Distributed by: (b)

create table qp_misc_jiras.tbl7268_bar as select * from qp_misc_jiras.tbl7268_foo;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) 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.
\d qp_misc_jiras.tbl7268_bar; 
Table "qp_misc_jiras.tbl7268_bar"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer | 
 b      | integer | 
Distributed by: (b)

drop table if exists qp_misc_jiras.tbl7268_foo;
drop table if exists qp_misc_jiras.tbl7268_bar;
create table qp_misc_jiras.tbl7268_foo (a varchar(15), b int) distributed by (b);
\d qp_misc_jiras.tbl7268_foo;
     Table "qp_misc_jiras.tbl7268_foo"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 a      | character varying(15) | 
 b      | integer               | 
Distributed by: (b)

create table qp_misc_jiras.tbl7268_bar as select * from qp_misc_jiras.tbl7268_foo;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) 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.
\d qp_misc_jiras.tbl7268_bar; 
     Table "qp_misc_jiras.tbl7268_bar"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 a      | character varying(15) | 
 b      | integer               | 
Distributed by: (b)

drop table if exists qp_misc_jiras.tbl7268_foo;
drop table if exists qp_misc_jiras.tbl7268_bar;
create table qp_misc_jiras.tbl7268_foo (a int, b varchar(15)) distributed by (b);
\d qp_misc_jiras.tbl7268_foo;
     Table "qp_misc_jiras.tbl7268_foo"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 a      | integer               | 
 b      | character varying(15) | 
Distributed by: (b)

create table qp_misc_jiras.tbl7268_bar as select * from qp_misc_jiras.tbl7268_foo;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) 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.
\d qp_misc_jiras.tbl7268_bar; 
     Table "qp_misc_jiras.tbl7268_bar"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 a      | integer               | 
 b      | character varying(15) | 
Distributed by: (b)

drop table if exists qp_misc_jiras.tbl7268_foo;
drop table if exists qp_misc_jiras.tbl7268_bar;
create table qp_misc_jiras.tbl6775_bar(x int) distributed randomly;        
create table qp_misc_jiras.tbl6775_foo(like qp_misc_jiras.tbl6775_bar) distributed randomly;
alter table qp_misc_jiras.tbl6775_foo add column y int;
alter table qp_misc_jiras.tbl6775_foo drop column y;
insert into qp_misc_jiras.tbl6775_foo(x) select  t1.x from qp_misc_jiras.tbl6775_bar t1 join qp_misc_jiras.tbl6775_bar t2 on t1.x=t2.x;
insert into qp_misc_jiras.tbl6775_foo(x) select  t1.x from qp_misc_jiras.tbl6775_bar t1;
insert into qp_misc_jiras.tbl6775_foo(x) select  t1.x from qp_misc_jiras.tbl6775_bar t1 group by t1.x;
drop table if exists qp_misc_jiras.tbl6775_foo;
drop table if exists qp_misc_jiras.tbl6775_bar;
--
-- Test Set 1
--
set optimizer_segments=3;
create table qp_misc_jiras.sample as select generate_series(1,1000);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'generate_series' 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 qp_misc_jiras.fim1 (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.
insert into qp_misc_jiras.fim1 values(100);
insert into qp_misc_jiras.fim1 values(200);
insert into qp_misc_jiras.fim1 values(300);
create table qp_misc_jiras.pg_foo(x int);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'x' 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 qp_misc_jiras.pg_foo values(111);
insert into qp_misc_jiras.pg_foo values(222);
insert into qp_misc_jiras.pg_foo values(333);
create or replace function func1(int) returns int as $$
declare
	t1 int;
begin
	execute 'select count(*) from pg_class' into t1;
	return t1;
end;
$$
language plpgsql;
drop table if exists qp_misc_jiras.bar;
create table qp_misc_jiras.bar(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 or replace function func2(int) returns int as $$
declare
	t1 int;
begin
	execute 'select count(*) from qp_misc_jiras.pg_foo' into t1;
	return t1;
end;
$$
language plpgsql;
select gp_segment_id, a, func2(a) from qp_misc_jiras.fim1; -- we should disallow this
ERROR:  function cannot execute on a QE slice because it accesses relation "qp_misc_jiras.pg_foo"  (seg2 slice1 nikos-mac:25434 pid=94008)
CONTEXT:  SQL statement "select count(*) from qp_misc_jiras.pg_foo"
PL/pgSQL function func2(integer) line 5 at EXECUTE statement
create or replace function func3(int) returns int as $$
declare
	t1 int;
begin
	execute 'select count(*) from (select count(distinct relname) from pg_class) a' into t1;
	return t1;
end;
$$
language plpgsql;
select gp_segment_id, a, func3(a) from qp_misc_jiras.fim1; -- we allow this
 gp_segment_id |  a  | func3 
---------------+-----+-------
             1 | 300 |     1
             2 | 100 |     1
             2 | 200 |     1
(3 rows)

--
-- Test Set 2
--
drop table if exists qp_misc_jiras.fim1;
create table qp_misc_jiras.fim1 (a int, chldrn_id varchar(50)) ;
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 qp_misc_jiras.fim1 values (1,'1~9');
insert into qp_misc_jiras.fim1 values (2,'2~9');
CREATE OR REPLACE FUNCTION public.array_intersect1(anyarray, anyarray) RETURNS anyarray as $$
SELECT ARRAY(SELECT x FROM unnest($1) x INTERSECT SELECT y from unnest($2) y)
$$ language sql;
CREATE OR REPLACE FUNCTION public.array_intersect2(anyarray, anyarray) RETURNS anyarray as $$
SELECT ARRAY(SELECT $1[i]
    FROM generate_series( array_lower($1, 1), array_upper($1, 1) ) i
      WHERE ARRAY[$1[i]] && $2)
$$ language sql;
CREATE OR REPLACE FUNCTION public.array_intersect3(anyarray, anyarray) RETURNS anyarray as $$
SELECT ARRAY(SELECT x FROM unnest($1) x INTERSECT SELECT y from unnest($2) y)
$$ language sql immutable;
CREATE OR REPLACE FUNCTION public.array_intersect4(anyarray, anyarray) RETURNS anyarray as $$
SELECT ARRAY(SELECT $1[i]
    FROM generate_series( array_lower($1, 1), array_upper($1, 1) ) i
      WHERE ARRAY[$1[i]] && $2)
$$ language sql immutable;
select array_intersect1(array[1,9]::int4[], array[-1,5,4,3,2,1,0]::int4[]) from qp_misc_jiras.fim1; -- we allow it even though the function is volatile.
 array_intersect1 
------------------
 {1}
 {1}
(2 rows)

select array_intersect2(array[1,9]::int4[], array[-1,5,4,3,2,1,0]::int4[]) from qp_misc_jiras.fim1; -- we allow it even though the function is volatile.
 array_intersect2 
------------------
 {1}
 {1}
(2 rows)

select array_intersect3(array[1,9]::int4[], array[-1,5,4,3,2,1,0]::int4[]) from qp_misc_jiras.fim1; -- should work.
 array_intersect3 
------------------
 {1}
 {1}
(2 rows)

select array_intersect4(array[1,9]::int4[], array[-1,5,4,3,2,1,0]::int4[]) from qp_misc_jiras.fim1; -- should work.
 array_intersect4 
------------------
 {1}
 {1}
(2 rows)

select array_intersect3(i::int4[], array[-1,5,4,3,2,1,0]::int4[]) from (select string_to_array(chldrn_id,'~') as i from qp_misc_jiras.fim1) b  ; -- should produce {1},{2}
 array_intersect3 
------------------
 {1}
 {2}
(2 rows)

select array_intersect4(i::int4[], array[-1,5,4,3,2,1,0]::int4[]) from (select string_to_array(chldrn_id,'~') as i from qp_misc_jiras.fim1) b  ; -- should produce {1},{2}
 array_intersect4 
------------------
 {1}
 {2}
(2 rows)

drop function func1(int);
drop function func2(int);
drop function func3(int);
drop table qp_misc_jiras.sample;
drop table qp_misc_jiras.fim1;
drop table qp_misc_jiras.pg_foo;
drop table qp_misc_jiras.bar;
-- Create 2 tables from which we can inherit.  
CREATE TABLE qp_misc_jiras.tableA (ssn INT, lastName VARCHAR, junk INT) DISTRIBUTED BY (ssn);
CREATE TABLE qp_misc_jiras.tableB (id INT, lastName VARCHAR, moreJunk INT) DISTRIBUTED BY (id);
-- Create a table that inherits from the previous two.
CREATE TABLE qp_misc_jiras.tableC (uid INT) INHERITS (qp_misc_jiras.tableA, qp_misc_jiras.tableB) DISTRIBUTED BY (uid);
NOTICE:  merging multiple inherited definitions of column "lastname"
-- Expose the bug.
SELECT * FROM qp_misc_jiras.tableC;
 ssn | lastname | junk | id | morejunk | uid 
-----+----------+------+----+----------+-----
(0 rows)

-- start_ignore
DROP TABLE qp_misc_jiras.tableC;
DROP TABLE qp_misc_jiras.tableB;
DROP TABLE qp_misc_jiras.tableA;
-- end_ignore
CREATE TABLE qp_misc_jiras.tableA (ssn INT, lastName VARCHAR, junk INT) DISTRIBUTED BY (ssn);
CREATE TABLE qp_misc_jiras.tableB (id INT, lastName VARCHAR, moreJunk INT) DISTRIBUTED BY (id);
INSERT INTO qp_misc_jiras.tablea VALUES (1, 'foo',2);
INSERT INTO qp_misc_jiras.tableb VALUES (2, 'bar',4);
CREATE TABLE qp_misc_jiras.tableC (uid1 INT, uid2 INT, uid3 INT) INHERITS (qp_misc_jiras.tableA, qp_misc_jiras.tableB) DISTRIBUTED BY (uid1, uid2, uid3);
NOTICE:  merging multiple inherited definitions of column "lastname"
INSERT INTO qp_misc_jiras.tableC VALUES (3, 'foobar', 5, 6, 7, 8, 9, 10);
-- Should display records from tableA and tableC.
SELECT * FROM qp_misc_jiras.tableA ORDER BY ssn;
 ssn | lastname | junk 
-----+----------+------
   1 | foo      |    2
   3 | foobar   |    5
(2 rows)

-- Should display records from tableB and tableC.
SELECT * FROM qp_misc_jiras.tableB ORDER BY id;
 id | lastname | morejunk 
----+----------+----------
  2 | bar      |        4
  6 | foobar   |        7
(2 rows)

-- Should display only records from tableC.
SELECT * FROM qp_misc_jiras.tableC;
 ssn | lastname | junk | id | morejunk | uid1 | uid2 | uid3 
-----+----------+------+----+----------+------+------+------
   3 | foobar   |    5 |  6 |        7 |    8 |    9 |   10
(1 row)

CREATE TABLE qp_misc_jiras.tableD (a INT, b INT, c INT) DISTRIBUTED BY (a);
INSERT INTO qp_misc_jiras.tableD VALUES (11, 12, 13);
CREATE TABLE qp_misc_jiras.tableE (x INT) INHERITS (qp_misc_jiras.tableA, qp_misc_jiras.tableD) DISTRIBUTED BY (x);
INSERT INTO qp_misc_jiras.tableE VALUES (1,'foo', -2, 11, 12, -13, 14);
-- Should display records from tableD and tableE.
SELECT * FROM qp_misc_jiras.tableD ORDER BY a, c;
 a  | b  |  c  
----+----+-----
 11 | 12 | -13
 11 | 12 |  13
(2 rows)

-- Should display records from only table E.
SELECT * FROM qp_misc_jiras.tableE;
 ssn | lastname | junk | a  | b  |  c  | x  
-----+----------+------+----+----+-----+----
   1 | foo      |   -2 | 11 | 12 | -13 | 14
(1 row)

-- Should display records from tableA, tableC, and tableE.
SELECT * FROM qp_misc_jiras.tableA ORDER BY ssn, junk;
 ssn | lastname | junk 
-----+----------+------
   1 | foo      |   -2
   1 | foo      |    2
   3 | foobar   |    5
(3 rows)

CREATE TABLE qp_misc_jiras.tableAA (ssn INT, lastName VARCHAR, junk INT) DISTRIBUTED randomly;
CREATE TABLE qp_misc_jiras.tableBB (id INT, lastName VARCHAR, moreJunk INT) DISTRIBUTED randomly;
INSERT INTO qp_misc_jiras.tableAA VALUES (1, 'foo',2);
INSERT INTO qp_misc_jiras.tableBB VALUES (2, 'bar',4);
CREATE TABLE qp_misc_jiras.tableCC (uid1 INT, uid2 INT, uid3 INT) INHERITS (qp_misc_jiras.tableAA, qp_misc_jiras.tableBB) DISTRIBUTED randomly;
NOTICE:  merging multiple inherited definitions of column "lastname"
INSERT INTO qp_misc_jiras.tableCC VALUES (3, 'foobar', 5, 6, 7, 8, 9, 10);
-- Should display records from tableAA and tableCC.
SELECT * FROM qp_misc_jiras.tableAA ORDER BY ssn, junk;
 ssn | lastname | junk 
-----+----------+------
   1 | foo      |    2
   3 | foobar   |    5
(2 rows)

-- Should display records from tableBB and tableCC.
SELECT * FROM qp_misc_jiras.tableBB ORDER BY id, moreJunk;
 id | lastname | morejunk 
----+----------+----------
  2 | bar      |        4
  6 | foobar   |        7
(2 rows)

-- Should display records from only tableCC.
SELECT * FROM qp_misc_jiras.tableCC;
 ssn | lastname | junk | id | morejunk | uid1 | uid2 | uid3 
-----+----------+------+----+----------+------+------+------
   3 | foobar   |    5 |  6 |        7 |    8 |    9 |   10
(1 row)

-- start_ignore
set statement_timeout=0;
set gp_interconnect_setup_timeout=7200;
-- end_ignore
CREATE TABLE qp_misc_jiras.inet_ip_pairs (saddr inet, daddr inet, instance_count int8);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'saddr' 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.
SELECT * FROM pg_indexes WHERE tablename='inet_ip_pairs';
 schemaname | tablename | indexname | tablespace | indexdef 
------------+-----------+-----------+------------+----------
(0 rows)

INSERT INTO qp_misc_jiras.inet_ip_pairs (saddr) SELECT (i%201 || '.' || i%11 || '.' || i%11 || '.' || i%100)::inet FROM generate_series(1,1000000) i;
--
-- MPP-6870: CREATE INDEX caused Out-Of-Memory problem
--
CREATE INDEX  inet_ip_pairs_idx1 ON  qp_misc_jiras.inet_ip_pairs (saddr);
SELECT * FROM pg_indexes WHERE tablename='inet_ip_pairs';
  schemaname   |   tablename   |     indexname      | tablespace |                                      indexdef                                      
---------------+---------------+--------------------+------------+------------------------------------------------------------------------------------
 qp_misc_jiras | inet_ip_pairs | inet_ip_pairs_idx1 |            | CREATE INDEX inet_ip_pairs_idx1 ON qp_misc_jiras.inet_ip_pairs USING btree (saddr)
(1 row)

--
-- Data should be evenly distributed to multiple segments?
--
SELECT gp_segment_id,count(*) FROM qp_misc_jiras.inet_ip_pairs GROUP BY 1;
 gp_segment_id | count  
---------------+--------
             2 | 331726
             1 | 333028
             0 | 335246
(3 rows)

-- start_ignore
DROP TABLE IF EXISTS qp_misc_jiras.inet_ip_pairs CASCADE;
-- end_ignore
set gp_autostats_mode=none;
create table qp_misc_jiras.tbl_7498_t1(x int, y text) distributed by (x);
insert into qp_misc_jiras.tbl_7498_t1 select x, 'foo' from generate_series(1,70000) x;
create table qp_misc_jiras.tbl_7498_t2 as select * from qp_misc_jiras.tbl_7498_t1;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'x' 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 qp_misc_jiras.tbl_7498_t3 as select * from qp_misc_jiras.tbl_7498_t1;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'x' 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.
drop table qp_misc_jiras.tbl_7498_t2;
drop table if exists qp_misc_jiras.tbl_7498_t1, qp_misc_jiras.tbl_7498_t2, qp_misc_jiras.tbl_7498_t3 cascade;
NOTICE:  table "tbl_7498_t2" does not exist, skipping
create table qp_misc_jiras.tbl_7498_t1(x int, y text) distributed by (x);
insert into qp_misc_jiras.tbl_7498_t1 select x, 'foo' from generate_series(1,70000) x;
create table qp_misc_jiras.tbl_7498_t2 as select * from qp_misc_jiras.tbl_7498_t1;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'x' 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 qp_misc_jiras.tbl_7498_t3 as select * from qp_misc_jiras.tbl_7498_t1;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'x' 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.
-- clean up all the test tables
-- start_ignore
drop table if exists qp_misc_jiras.tbl_7498_t1, qp_misc_jiras.tbl_7498_t2, qp_misc_jiras.tbl_7498_t3 cascade;
-- end_ignore
-- currtid2() is not supported in GPDB.
select currtid2('tbl7886'::text, '(0,1)'::tid);
ERROR:  function currtid2 is not supported by GPDB
create table qp_misc_jiras.tbl8017(x int, y int) distributed by (x);
create index tbl8017_x on qp_misc_jiras.tbl8017(x);
insert into qp_misc_jiras.tbl8017 select generate_series(1,100), generate_series(1,100);
analyze qp_misc_jiras.tbl8017;
select relname, reltuples, relpages from pg_class where relname in ('tbl8017', 'tbl8017_x');
  relname  | reltuples | relpages 
-----------+-----------+----------
 tbl8017   |       100 |        3
 tbl8017_x |       100 |        6
(2 rows)

drop table qp_misc_jiras.tbl8017;
create table qp_misc_jiras.tbl7957_foo(x int, y int, z int) distributed by (x);
insert into qp_misc_jiras.tbl7957_foo values(1,2,3);
insert into qp_misc_jiras.tbl7957_foo values(1,2,3);
insert into qp_misc_jiras.tbl7957_foo values(1,2,3);
select count(*) as c from qp_misc_jiras.tbl7957_foo group by cube(x) order by c;
 c 
---
 3
 3
(2 rows)

select count(*) as c from qp_misc_jiras.tbl7957_foo group by cube(y) order by c;
 c 
---
 3
 3
(2 rows)

select count(*) as c from qp_misc_jiras.tbl7957_foo group by cube(z) order by c;
 c 
---
 3
 3
(2 rows)

select sum(z) as c from qp_misc_jiras.tbl7957_foo group by cube(x) order by c;
 c 
---
 9
 9
(2 rows)

select sum(z) as c from qp_misc_jiras.tbl7957_foo group by cube(y) order by c;
 c 
---
 9
 9
(2 rows)

select sum(z) as c from qp_misc_jiras.tbl7957_foo group by cube(z) order by c;
 c 
---
 9
 9
(2 rows)

drop table qp_misc_jiras.tbl7957_foo;
-- start_ignore
CREATE SCHEMA mustan;
CREATE TABLE mustan.test(
	id int,
	d date
);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' 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.
-- end_ignore
	
INSERT INTO mustan.test( id, d ) VALUES( 1, '20080101' ), (2, '20080102');
CREATE OR REPLACE FUNCTION mustan.f1() RETURNS DATE AS $$
BEGIN
	return '20080101';
END;
$$LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION mustan.f2() RETURNS DATE AS $$
BEGIN
	return '20080101';
END;
$$LANGUAGE plpgsql immutable;
CREATE OR REPLACE FUNCTION mustan.f3() RETURNS DATE AS $$
DECLARE
	out_dt date;
BEGIN
	SELECT '20080101' INTO out_dt;
	return '20080101';
END;
$$LANGUAGE plpgsql immutable;
CREATE OR REPLACE FUNCTION mustan.f4() RETURNS DATE AS $$
DECLARE
	out_dt date;
BEGIN
	SELECT '20080101' INTO out_dt FROM mustan.test WHERE id=2;
	return out_dt;
END;
$$LANGUAGE plpgsql immutable;
CREATE OR REPLACE FUNCTION mustan.f5() RETURNS DATE AS $$
DECLARE
	out_dt date;
BEGIN
	SELECT d INTO out_dt FROM mustan.test WHERE id=2;
	return out_dt;
END;
$$LANGUAGE plpgsql immutable;
CREATE OR REPLACE FUNCTION mustan.f6( n int ) RETURNS DATE AS $$
DECLARE
	out_dt date;
BEGIN
	SELECT d INTO out_dt FROM mustan.test WHERE id=n;
	return out_dt;
END;
$$LANGUAGE plpgsql immutable;
CREATE OR REPLACE FUNCTION mustan.f7( in_d date ) RETURNS DATE AS $$
DECLARE
	out_dt date;
BEGIN
	SELECT d INTO out_dt FROM mustan.test WHERE d=in_d;
	return out_dt;
END;
$$LANGUAGE plpgsql immutable;
CREATE OR REPLACE FUNCTION mustan.f7( in_d text ) RETURNS DATE AS $$
DECLARE
	out_dt date;
BEGIN
	-- SELECT d INTO out_dt FROM mustan.test WHERE d=cast( in_d as date );
	SELECT d INTO out_dt FROM mustan.test WHERE d=in_d::date;
	return out_dt;
END;
$$LANGUAGE plpgsql immutable;
select * from mustan.f1();
     f1     
------------
 2008-01-01
(1 row)

select * from mustan.f2();
     f2     
------------
 2008-01-01
(1 row)

select * from mustan.f3();
     f3     
------------
 2008-01-01
(1 row)

select * from mustan.f4();
     f4     
------------
 2008-01-01
(1 row)

select * from mustan.f5();
     f5     
------------
 2008-01-02
(1 row)

select * from mustan.f6( 2 );
     f6     
------------
 2008-01-02
(1 row)

select * from mustan.f7( '20080102'::date );
     f7     
------------
 2008-01-02
(1 row)

select * from mustan.f7( '20080102' );-- 
     f7     
------------
 2008-01-02
(1 row)

-- start_ignore
create table qp_misc_jiras.tbl5994_test (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.
-- end_ignore
insert into qp_misc_jiras.tbl5994_test select i, i%1000 from generate_series(1, 100000) i;
reset enable_indexscan; 
reset enable_nestloop; 
reset enable_seqscan; 
reset gp_enable_agg_distinct; 
reset gp_enable_agg_distinct_pruning;
set enable_groupagg=off;
-- both queries should use hashagg
explain select count(distinct j) from (select t1.* from qp_misc_jiras.tbl5994_test t1, qp_misc_jiras.tbl5994_test t2 where t1.j = t2.j) tmp group by j;
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice3; segments: 3)  (cost=1063082.43..1063092.43 rows=1000 width=12)
   ->  HashAggregate  (cost=1063082.43..1063092.43 rows=334 width=12)
         Group Key: t1.j
         ->  HashAggregate  (cost=1063057.43..1063067.43 rows=334 width=8)
               Group Key: t1.j, t1.j
               ->  HashAggregate  (cost=1063039.93..1063039.93 rows=334 width=8)
                     Group Key: t1.j, t1.j
                     ->  Hash Join  (cost=3759.25..1007440.85 rows=2471070 width=4)
                           Hash Cond: t1.j = t2.j
                           ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..2683.00 rows=28700 width=4)
                                 Hash Key: t1.j
                                 ->  Seq Scan on tbl5994_test t1  (cost=0.00..961.00 rows=28700 width=4)
                           ->  Hash  (cost=2683.00..2683.00 rows=28700 width=4)
                                 ->  Redistribute Motion 3:3  (slice2; segments: 3)  (cost=0.00..2683.00 rows=28700 width=4)
                                       Hash Key: t2.j
                                       ->  Seq Scan on tbl5994_test t2  (cost=0.00..961.00 rows=28700 width=4)
 Optimizer: Postgres query optimizer
(17 rows)

explain select count(distinct j) from (select t1.* from qp_misc_jiras.tbl5994_test t1, qp_misc_jiras.tbl5994_test t2 where t1.i = t2.i) tmp group by j;
                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice2; segments: 3)  (cost=1059658.43..1059668.43 rows=1000 width=12)
   ->  HashAggregate  (cost=1059658.43..1059668.43 rows=334 width=12)
         Group Key: t1.j
         ->  HashAggregate  (cost=1059633.43..1059643.43 rows=334 width=8)
               Group Key: t1.j, t1.j
               ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=1059595.93..1059615.93 rows=334 width=8)
                     Hash Key: t1.j
                     ->  HashAggregate  (cost=1059595.93..1059595.93 rows=334 width=8)
                           Group Key: t1.j, t1.j
                           ->  Hash Join  (cost=2037.25..1003996.85 rows=2471070 width=4)
                                 Hash Cond: t1.i = t2.i
                                 ->  Seq Scan on tbl5994_test t1  (cost=0.00..961.00 rows=28700 width=8)
                                 ->  Hash  (cost=961.00..961.00 rows=28700 width=4)
                                       ->  Seq Scan on tbl5994_test t2  (cost=0.00..961.00 rows=28700 width=4)
 Optimizer: Postgres query optimizer
(15 rows)

set enable_groupagg=on;
-- first query should use groupagg, and second one - hashagg
explain select count(distinct j) from (select t1.* from qp_misc_jiras.tbl5994_test t1, qp_misc_jiras.tbl5994_test t2 where t1.j = t2.j) tmp group by j;
                                                      QUERY PLAN                                                       
-----------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice3; segments: 3)  (cost=2226209.90..2281818.97 rows=1000 width=12)
   ->  GroupAggregate  (cost=2226209.90..2281818.97 rows=334 width=12)
         Group Key: t1.j
         ->  Sort  (cost=2226209.90..2244742.92 rows=2471070 width=4)
               Sort Key: t1.j
               ->  Hash Join  (cost=3759.25..1007440.85 rows=2471070 width=4)
                     Hash Cond: t1.j = t2.j
                     ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..2683.00 rows=28700 width=4)
                           Hash Key: t1.j
                           ->  Seq Scan on tbl5994_test t1  (cost=0.00..961.00 rows=28700 width=4)
                     ->  Hash  (cost=2683.00..2683.00 rows=28700 width=4)
                           ->  Redistribute Motion 3:3  (slice2; segments: 3)  (cost=0.00..2683.00 rows=28700 width=4)
                                 Hash Key: t2.j
                                 ->  Seq Scan on tbl5994_test t2  (cost=0.00..961.00 rows=28700 width=4)
 Optimizer: Postgres query optimizer
(15 rows)

explain select count(distinct j) from (select t1.* from qp_misc_jiras.tbl5994_test t1, qp_misc_jiras.tbl5994_test t2 where t1.i = t2.i) tmp group by j;
                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice2; segments: 3)  (cost=1059658.43..1059668.43 rows=1000 width=12)
   ->  HashAggregate  (cost=1059658.43..1059668.43 rows=334 width=12)
         Group Key: t1.j
         ->  HashAggregate  (cost=1059633.43..1059643.43 rows=334 width=8)
               Group Key: t1.j, t1.j
               ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=1059595.93..1059615.93 rows=334 width=8)
                     Hash Key: t1.j
                     ->  HashAggregate  (cost=1059595.93..1059595.93 rows=334 width=8)
                           Group Key: t1.j, t1.j
                           ->  Hash Join  (cost=2037.25..1003996.85 rows=2471070 width=4)
                                 Hash Cond: t1.i = t2.i
                                 ->  Seq Scan on tbl5994_test t1  (cost=0.00..961.00 rows=28700 width=8)
                                 ->  Hash  (cost=961.00..961.00 rows=28700 width=4)
                                       ->  Seq Scan on tbl5994_test t2  (cost=0.00..961.00 rows=28700 width=4)
 Optimizer: Postgres query optimizer
(15 rows)

drop table qp_misc_jiras.tbl5994_test;
CREATE TABLE qp_misc_jiras.tbl_8205 (
text_col text,
bigint_col bigint,
char_vary_col character varying(30),
numeric_col numeric)
DISTRIBUTED RANDOMLY;
set enable_seqscan = off;
set enable_bitmapscan = off;
set enable_indexscan = on;
show enable_indexscan;
 enable_indexscan 
------------------
 on
(1 row)

explain analyze select reltablespace  from pg_class where oid = (select reltoastrelid from pg_class where relname='tbl_8205'); -- force_explain
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using pg_class_oid_index on pg_class  (cost=200.27..400.54 rows=1 width=4)
   Index Cond: oid = $0
   InitPlan 1 (returns $0)
     ->  Index Scan using pg_class_relname_nsp_index on pg_class pg_class_1  (cost=0.00..200.27 rows=1 width=4)
           Index Cond: relname = 'tbl_8205'::name
 Slice statistics:
   (slice0)    Executor memory: 113K bytes.
   (slice1)    Executor memory: 113K bytes.
 Statement statistics:
   Memory used: 1000K bytes
 Settings:  enable_bitmapscan=off; enable_groupagg=on; enable_indexscan=on; enable_seqscan=off; optimizer=off; optimizer_segments=3
 Optimizer status: Postgres query optimizer
 Total runtime: 0.068 ms
(15 rows)

select reltablespace from pg_class where oid = (select reltoastrelid from pg_class where relname='tbl_8205');
 reltablespace 
---------------
             0
(1 row)

drop table qp_misc_jiras.tbl_8205;
-- create sample table
create table qp_misc_jiras.tbl2976(x int);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'x' 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 qp_misc_jiras.tbl2976 select generate_series(1,1000);
-- test cases
-- start_ignore
select pg_relation_size(tablename) from pg_tables where schemaname ='qp_misc_jiras' and tablename = 'tbl2976'; -- This should work
 pg_relation_size 
------------------
                 
(1 row)

select pg_relation_size(oid) from pg_class where relname = 'tbl2976'; -- This should work
 pg_relation_size 
------------------
            98304
(1 row)

-- end_ignore
create table qp_misc_jiras.tbl2976_3(x int) distributed by (x);
drop table qp_misc_jiras.tbl2976;
drop table qp_misc_jiras.tbl2976_3;
create table qp_misc_jiras.tbl8258 (a int, b double precision)
PARTITION BY RANGE(b)
(START (1::double precision) END (100::double precision)
EVERY ((20)::double precision),
PARTITION p1 START (100::double precision) END (150::double precision));
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 "tbl8258_1_prt_1" for table "tbl8258"
NOTICE:  CREATE TABLE will create partition "tbl8258_1_prt_2" for table "tbl8258"
NOTICE:  CREATE TABLE will create partition "tbl8258_1_prt_3" for table "tbl8258"
NOTICE:  CREATE TABLE will create partition "tbl8258_1_prt_4" for table "tbl8258"
NOTICE:  CREATE TABLE will create partition "tbl8258_1_prt_5" for table "tbl8258"
NOTICE:  CREATE TABLE will create partition "tbl8258_1_prt_p1" for table "tbl8258"
select pg_get_partition_def('qp_misc_jiras.tbl8258'::regclass, true);
                                       pg_get_partition_def                                       
--------------------------------------------------------------------------------------------------
 PARTITION BY RANGE(b)                                                                           +
           (                                                                                     +
           START (1::double precision) END (100::double precision) EVERY (20::double precision), +
           PARTITION p1 START (100::double precision) END (150::double precision)                +
           )
(1 row)

drop table qp_misc_jiras.tbl8258;
reset statement_timeout;
set statement_mem = '512MB';
create table qp_misc_jiras.utable (
    tstart timestamp,
    tfinish timestamp,
    utxt text,
    unum numeric
    )
    distributed by (tstart);
create view qp_misc_jiras.uview(period, ctxt, wsum) as
select
    period, 
    utxt,
    sum(usum) over (partition by period)
from
	(   select 
		    to_char( tstart, 'YYYY-MM-DD HH24:00'), 
		    utxt, 
		    sum(unum)
		from
		(
			select 
			    tstart, 
			    utxt,
			    case -- this case seems critical
			        when tfinish >tstart
			        then unum 
			        else 0 
			        end
			from qp_misc_jiras.utable
		) x(tstart, utxt, unum)
		
		group by 1, 2
	) y(period, utxt, usum);
insert into qp_misc_jiras.utable values
    (timestamp '2009-05-01 01:01:10', timestamp '2009-05-01 02:01:10', 'a', 1.0),
    (timestamp '2009-05-01 02:01:10', timestamp '2009-05-01 01:01:10', 'a', 1.0),
    (timestamp '2009-05-01 02:01:10', timestamp '2009-05-01 03:01:10', 'a', 1.0),
    (timestamp '2009-05-01 03:01:10', timestamp '2009-05-01 03:01:10', 'b', 1.0),
    (timestamp '2009-05-01 01:01:09', timestamp '2009-05-01 02:01:10', 'b', 1.0),
    (timestamp '2009-05-03 01:01:10', timestamp '2009-05-01 02:01:10', 'b', 1.0);
-- This works.
reset statement_mem;
select 
    period::date,
--    avg( case ctxt when 'a' then wsum end) as "a"
    avg( case ctxt when 'b' then wsum end) as "b"
from
    qp_misc_jiras.uview 
group by 1;
   period   |           b            
------------+------------------------
 2009-05-03 | 0.00000000000000000000
 2009-05-01 | 1.00000000000000000000
(2 rows)

-- This used to fail on an assert on a segment (palloc)
select 
    period::date, -- the cast is significant 
    avg( case ctxt when 'a' then wsum end) as "a", 
    avg( case ctxt when 'b' then wsum end) as "b"
from
    qp_misc_jiras.uview 
group by 1;
   period   |         a          |           b            
------------+--------------------+------------------------
 2009-05-01 | 1.5000000000000000 | 1.00000000000000000000
 2009-05-03 |                    | 0.00000000000000000000
(2 rows)

-- start_ignore
drop view qp_misc_jiras.uview;
drop table qp_misc_jiras.utable;
-- end_ignore
CREATE TABLE qp_misc_jiras.foo_6325 (
  foo_6325_attr text
)
DISTRIBUTED BY (foo_6325_attr);
CREATE TABLE qp_misc_jiras.bar_6325 (
  bar_6325_attr text
)
DISTRIBUTED RANDOMLY;
-- force_explain
explain
select  t1.bar_6325_attr::int, t1.foo_6325_attr 
from (
	select c.bar_6325_attr::int, a.foo_6325_attr
	from qp_misc_jiras.foo_6325 a, qp_misc_jiras.bar_6325 c
	group by 1,2
     ) as t1 
group by 1, 2;
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice2; segments: 3)  (cost=0.16..0.17 rows=1 width=36)
   ->  HashAggregate  (cost=0.16..0.17 rows=1 width=36)
         Group By: "?column3?", a.foo_6325_attr
         ->  HashAggregate  (cost=0.13..0.14 rows=1 width=36)
               Group By: c.bar_6325_attr::integer, a.foo_6325_attr
               ->  Nested Loop  (cost=0.04..0.11 rows=2 width=64)
                     ->  Seq Scan on foo_6325 a  (cost=0.00..0.00 rows=1 width=32)
                     ->  Materialize  (cost=0.04..0.07 rows=1 width=32)
                           ->  Broadcast Motion 3:3  (slice1; segments: 3)  (cost=0.00..0.04 rows=1 width=32)
                                 ->  Seq Scan on bar_6325 c  (cost=0.00..0.00 rows=1 width=32)
 Settings:  enable_bitmapscan=off; enable_groupagg=on; enable_indexscan=on; enable_seqscan=off; optimizer=off; optimizer_segments=3
 Optimizer status: Postgres query optimizer
(12 rows)

-- start_ignore
drop table qp_misc_jiras.foo_6325;
drop table qp_misc_jiras.bar_6325;
-- end_ignore
-- Test for an old bug in extending a heap relation beyond RELSEG_SIZE (1 GB).
-- The insert failed with an error:
--   could not open segment 1 of relation ...: No such file or directory
create table qp_misc_jiras.heap_tbl8621 (a int) with (fillfactor=10) distributed by (a);
insert into qp_misc_jiras.heap_tbl8621 select 1 from generate_series(1, 3100000);
-- Verify that the table really was larger than 1 GB. Otherwise we wouldn't
-- test the segment boundary.
select pg_relation_size('qp_misc_jiras.heap_tbl8621') > 1100000000 as over_1gb;
 over_1gb 
----------
 t
(1 row)

DROP TABLE qp_misc_jiras.heap_tbl8621;
CREATE TABLE qp_misc_jiras.tbl8860_1 (
     id INTEGER NOT NULL,
     key TEXT NOT NULL
) distributed by (id);
CREATE TABLE qp_misc_jiras.tbl8860_2 (
     id INTEGER NOT NULL,
     key CHARACTER VARYING(50) NOT NULL
) distributed by (id);
INSERT INTO qp_misc_jiras.tbl8860_1 SELECT 1, key FROM qp_misc_jiras.tbl8860_2;
-- start_ignore
drop table if exists qp_misc_jiras.tbl8860_1;
drop table if exists qp_misc_jiras.tbl8860_2;
-- end_ignore
-- MPP-9739: Unicode not supported - chr() ascii() functions do not return unicode codepoints for multibyte characters
Create Table qp_misc_jiras.tbl9739_unicode_test
(
id int Not Null,
str Varchar(10) Not Null
)
Distributed By (id);
Insert Into qp_misc_jiras.tbl9739_unicode_test (id, str) Values (1, Chr(1000) || Chr(2001) || Chr(3010));
Insert Into qp_misc_jiras.tbl9739_unicode_test (id, str) Values (2, Chr(105) || Chr(110) || Chr(35));
Select id,
Ascii (Substr(str, 1, 1)) As first_char_code,
Ascii (Substr(str, 2, 1)) As second_char_code,
Ascii (Substr(str, 3, 1)) As third_char_code,
Ascii (Substr(str, 4, 1)) As fourth_char_code
From qp_misc_jiras.tbl9739_unicode_test
Order
By id;
 id | first_char_code | second_char_code | third_char_code | fourth_char_code 
----+-----------------+------------------+-----------------+------------------
  1 |            1000 |             2001 |            3010 |                0
  2 |             105 |              110 |              35 |                0
(2 rows)

drop table qp_misc_jiras.tbl9739_unicode_test;
--
-- Create table qp_misc_jiras.tbl9613
--
create table qp_misc_jiras.tbl9613(x int, y numeric) distributed by (x);
insert into qp_misc_jiras.tbl9613 values(1, 1);
insert into qp_misc_jiras.tbl9613 values(1, 2);
insert into qp_misc_jiras.tbl9613 values(1, 3);
insert into qp_misc_jiras.tbl9613 values(1, 4);
insert into qp_misc_jiras.tbl9613 values(1, 5);
insert into qp_misc_jiras.tbl9613 values(1, 6);
insert into qp_misc_jiras.tbl9613 values(1, 7);
insert into qp_misc_jiras.tbl9613 values(1, 8);
--
-- First set of functions. These do not access external relations.
--
CREATE OR REPLACE FUNCTION _final_median(numeric[])
   RETURNS numeric AS
$$
   SELECT AVG(val)
   FROM (
     SELECT val
     FROM unnest($1) val
     ORDER BY 1
     LIMIT  2 - MOD(array_upper($1, 1), 2)
     OFFSET CEIL(array_upper($1, 1) / 2.0) - 1
   ) sub;
$$
LANGUAGE 'sql' IMMUTABLE;
-- start_ignore
DROP AGGREGATE "median"(numeric);
ERROR:  aggregate median(numeric) does not exist
-- end_ignore
 
CREATE AGGREGATE "median"(numeric) (
  SFUNC=array_append,
  STYPE=numeric[],
  FINALFUNC=_final_median,
  INITCOND='{}'
);
--
-- Second set of functions. _final_median2 accesses relation in a subquery. This should be disallowed.
--
CREATE OR REPLACE FUNCTION _final_median2(numeric[])
   RETURNS numeric AS
$$
   SELECT AVG(val.x)
   FROM (
   	select count(*) as x from qp_misc_jiras.tbl9613
   	) val;
$$
LANGUAGE 'sql' IMMUTABLE;
-- start_ignore
DROP AGGREGATE median2(numeric);
ERROR:  aggregate median2(numeric) does not exist
-- end_ignore
 
CREATE AGGREGATE median2(numeric) (
  SFUNC=array_append,
  STYPE=numeric[],
  FINALFUNC=_final_median2,
  INITCOND='{}'
);
--
-- Queries.
--
explain select x, "median"(y) from qp_misc_jiras.tbl9613 group by x; -- this should be allowed
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=28.17..38.09 rows=496 width=36)
   ->  GroupAggregate  (cost=28.17..38.09 rows=166 width=36)
         Group By: x
         ->  Sort  (cost=28.17..29.41 rows=166 width=36)
               Sort Key: x
               ->  Seq Scan on tbl9613  (cost=0.00..5.96 rows=166 width=36)
 Settings:  enable_bitmapscan=off; enable_groupagg=on; enable_indexscan=on; enable_seqscan=off; optimizer=off; optimizer_segments=3
 Optimizer status: Postgres query optimizer
(8 rows)

select x, "median"(y) from qp_misc_jiras.tbl9613 group by x; -- this should be allowed
 x |       median       
---+--------------------
 1 | 4.5000000000000000
(1 row)

explain select x, median2(y) from qp_misc_jiras.tbl9613 group by x; -- this should be disallowed
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=28.17..38.09 rows=496 width=36)
   ->  GroupAggregate  (cost=28.17..38.09 rows=166 width=36)
         Group By: x
         ->  Sort  (cost=28.17..29.41 rows=166 width=36)
               Sort Key: x
               ->  Seq Scan on tbl9613  (cost=0.00..5.96 rows=166 width=36)
 Settings:  enable_bitmapscan=off; enable_groupagg=on; enable_indexscan=on; enable_seqscan=off; optimizer=off; optimizer_segments=3
 Optimizer status: Postgres query optimizer
(8 rows)

select x, median2(y) from qp_misc_jiras.tbl9613 group by x; -- this should be disallowed
ERROR:  function cannot execute on a QE slice because it accesses relation "qp_misc_jiras.tbl9613"  (seg0 slice1 nikos-mac:25432 pid=94006)
CONTEXT:  SQL function "_final_median2" during startup
-- start_ignore
drop table qp_misc_jiras.tbl9613;
DROP AGGREGATE "median"(numeric);
DROP AGGREGATE median2(numeric);
-- end_ignore
create table qp_misc_jiras.tbl7285_axg (id int, time timestamp) partition by range (time) (start ('2009-01-01 00:00:00'::timestamp without time zone) end ('2009-01-03 00:00:00'::timestamp without time zone) every ('1 day'::interval));
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' 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 "tbl7285_axg_1_prt_1" for table "tbl7285_axg"
NOTICE:  CREATE TABLE will create partition "tbl7285_axg_1_prt_2" for table "tbl7285_axg"
alter table qp_misc_jiras.tbl7285_axg add partition p20090103 start ('2009-01-03 00:00:00'::timestamp without time zone) end ('2009-01-04 00:00:00'::timestamp without time zone);
NOTICE:  CREATE TABLE will create partition "tbl7285_axg_1_prt_p20090103" for table "tbl7285_axg"
select count(*) from qp_misc_jiras.tbl7285_axg;
 count 
-------
     0
(1 row)

drop table qp_misc_jiras.tbl7285_axg;
show autovacuum;
 autovacuum 
------------
 off
(1 row)

set autovacuum=on;
ERROR:  parameter "autovacuum" cannot be changed now
create table qp_misc_jiras.tbl9706ao ( a int ) with (appendonly=true, compresslevel=5) distributed by (a);
create table qp_misc_jiras.tbl9706aoc ( a int ) with (appendonly=true, compresslevel=5, orientation=column) distributed by (a);
insert into qp_misc_jiras.tbl9706ao select i from generate_series(1, 100) i;
insert into qp_misc_jiras.tbl9706aoc select i from generate_series(1, 100) i;
select * from pg_catalog.get_ao_distribution('qp_misc_jiras.tbl9706ao'::regclass) order by 1 limit 2;
 segmentid | tupcount 
-----------+----------
         0 |       38
         1 |       37
(2 rows)

select * from pg_catalog.get_ao_distribution('qp_misc_jiras.tbl9706aoc'::regclass) order by 1 limit 2;
 segmentid | tupcount 
-----------+----------
         0 |       38
         1 |       37
(2 rows)

create table qp_misc_jiras.bmap2 (a varchar, b varchar);
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 qp_misc_jiras.bmap2 values ('1', NULL);
create index bmap2_index on qp_misc_jiras.bmap2 using bitmap (a, b);
create table qp_misc_jiras.badbitmapindex (x int, y int, z int);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'x' 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 qp_misc_jiras.badbitmapindex values (0,0,0);
insert into qp_misc_jiras.badbitmapindex values (0,0,NULL);
create index badbitmapindex1 on qp_misc_jiras.badbitmapindex using bitmap(y,z);
analyze qp_misc_jiras.badbitmapindex;
--explain select * from badbitmapindex where y = 0 and z = 0;
insert into qp_misc_jiras.badbitmapindex select generate_series(1,100*500);
analyze qp_misc_jiras.badbitmapindex;
--explain select * from badbitmapindex where y = 0 and z = 0;
select * from qp_misc_jiras.badbitmapindex where y = 0 and z = 0;
 x | y | z 
---+---+---
 0 | 0 | 0
(1 row)

drop index qp_misc_jiras.badbitmapindex1;
drop index qp_misc_jiras.bmap2_index;
drop table qp_misc_jiras.badbitmapindex;
drop table qp_misc_jiras.bmap2;
CREATE TABLE qp_misc_jiras.ir_voice_sms_and_data (
    imsi_number character varying(35),
    ir_call_country_name character varying(35),
    partner_operator_id character varying(15),
    tap_event_identifier character varying(35),
    ir_call_originating_number character varying(30),
    ir_call_terminating_number character varying(30),
    ir_call_type_group_code character varying(10),
    ir_call_supplementary_svc_code character(3),
    ir_call_charged_item_code character(1)
) distributed randomly;
explain select
case when ir_call_type_group_code in ('H', 'VH', 'PCB') then 'Thailland'
else 'Unidentify' end || 'a'
from qp_misc_jiras.ir_voice_sms_and_data
group by 
case when ir_call_supplementary_svc_code in ('S20','S21')
then 'MO' else 'foo' end
, 
case when ir_call_type_group_code in ('H', 'VH', 'PCB') then 'Thailland'
else 'Unidentify' end
;
                                                                                                                                                   QUERY PLAN                                                                                                                                                   
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice2; segments: 3)  (cost=0.05..0.07 rows=1 width=96)
   ->  HashAggregate  (cost=0.05..0.07 rows=1 width=96)
         Group By: "?column1?", "?column2?"
         ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.01..0.04 rows=1 width=64)
               Hash Key: unnamed_attr_1, unnamed_attr_2
               ->  HashAggregate  (cost=0.01..0.02 rows=1 width=64)
                     Group By: CASE WHEN ir_voice_sms_and_data.ir_call_supplementary_svc_code = ANY ('{S20,S21}'::bpchar[]) THEN 'MO'::text ELSE 'foo'::text END, CASE WHEN ir_voice_sms_and_data.ir_call_type_group_code::text = ANY ('{H,VH,PCB}'::text[]) THEN 'Thailland'::text ELSE 'Unidentify'::text END
                     ->  Seq Scan on ir_voice_sms_and_data  (cost=0.00..0.01 rows=1 width=54)
 Settings:  enable_bitmapscan=off; enable_groupagg=on; enable_indexscan=on; enable_seqscan=off; optimizer=off; optimizer_segments=3
 Optimizer status: Postgres query optimizer
(10 rows)

DROP TABLE qp_misc_jiras.ir_voice_sms_and_data;
create table qp_misc_jiras.r
    (a int, b int, c int)
    with (appendonly = true)
    distributed by (a);
create index rb
    on qp_misc_jiras.r(b);
insert into qp_misc_jiras.r
    values
        (1,2,3),
        (4,5,6),
        (7,8,9);
--alter table r
--    set with (reorganize = true)
--    distributed by (c);
alter table qp_misc_jiras.r
    set distributed by (c);
drop table if exists qp_misc_jiras.r cascade;
-- Triggers are applied AFTER the row has been routed per the distribution
-- policy. However, a trigger can change the value of the distribution column.
-- This means, we have an unexpected row in a segment, which can lead to wrong
-- results. Including gp_segment_id in below queries allows us to ensure that
-- no redistribution has occurred.
CREATE TABLE qp_misc_jiras.test_trig(id int, aaa text) DISTRIBUTED BY (id);
INSERT INTO qp_misc_jiras.test_trig VALUES (1, 'before creating trigger');
SELECT gp_segment_id, * FROM qp_misc_jiras.test_trig;
 gp_segment_id | id |           aaa           
---------------+----+-------------------------
             1 |  1 | before creating trigger
(1 row)

CREATE OR REPLACE FUNCTION fn_trig() RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
   NEW.aaa = NEW.aaa || '1';
   NEW.id = NEW.id + 1;
   RAISE NOTICE '%', NEW.id;
RETURN NEW;
END
$$;
CREATE TRIGGER test_trig_1
BEFORE INSERT OR UPDATE ON qp_misc_jiras.test_trig
FOR EACH ROW
    EXECUTE PROCEDURE fn_trig();
INSERT INTO qp_misc_jiras.test_trig VALUES (0, 'after creating trigger');
NOTICE:  1  (seg1 127.0.0.1:40001 pid=16301)
INSERT INTO qp_misc_jiras.test_trig VALUES (1, 'aaa');
NOTICE:  2  (seg1 127.0.0.1:40001 pid=16301)
SELECT gp_segment_id, * FROM qp_misc_jiras.test_trig;
 gp_segment_id | id |           aaa           
---------------+----+-------------------------
             1 |  1 | before creating trigger
             1 |  1 | after creating trigger1
             1 |  2 | aaa1
(3 rows)

UPDATE qp_misc_jiras.test_trig SET id = id;
NOTICE:  2  (seg1 127.0.0.1:40001 pid=16301)
NOTICE:  2  (seg1 127.0.0.1:40001 pid=16301)
NOTICE:  3  (seg1 127.0.0.1:40001 pid=16301)
SELECT gp_segment_id, * FROM qp_misc_jiras.test_trig;
 gp_segment_id | id |           aaa            
---------------+----+--------------------------
             1 |  2 | before creating trigger1
             1 |  2 | after creating trigger11
             1 |  3 | aaa11
(3 rows)

DROP TABLE qp_misc_jiras.test_trig;
DROP FUNCTION fn_trig();
set gp_select_invisible=false;
create table qp_misc_jiras.tbl1_tbl_11257 (
 id            bigint,
partition_key timestamp,
distribution_key bigint not null,
constraint pk_tbl1_tbl_11257 primary key (distribution_key, partition_key, id)
)
distributed by (distribution_key)
partition by range (partition_key)
(
default partition default_partition,
partition d_2010_09_28 start (date '2010-09-28') end (date '2010-09-29')
);
NOTICE:  CREATE TABLE will create partition "tbl1_tbl_11257_1_prt_default_partition" for table "tbl1_tbl_11257"
NOTICE:  CREATE TABLE will create partition "tbl1_tbl_11257_1_prt_d_2010_09_28" for table "tbl1_tbl_11257"
select relname, distkey as distribution_attributes from gp_distribution_policy p, pg_class c where p.localoid = c.oid and relname like 'tbl1_tbl_11257%';
                relname                 | distribution_attributes 
----------------------------------------+-------------------------
 tbl1_tbl_11257                         | 3
 tbl1_tbl_11257_1_prt_default_partition | 3
 tbl1_tbl_11257_1_prt_d_2010_09_28      | 3
(3 rows)

-- start_ignore
alter table qp_misc_jiras.tbl1_tbl_11257 split default partition
start (date '2010-09-27' )
end (date '2010-09-28')
into (partition d_2010_09_27, partition default_partition);
NOTICE:  exchanged partition "default_partition" of relation "tbl1_tbl_11257" with relation "pg_temp_37266"
NOTICE:  dropped partition "default_partition" for relation "tbl1_tbl_11257"
NOTICE:  CREATE TABLE will create partition "tbl1_tbl_11257_1_prt_d_2010_09_27" for table "tbl1_tbl_11257"
NOTICE:  CREATE TABLE will create partition "tbl1_tbl_11257_1_prt_default_partition" for table "tbl1_tbl_11257"
-- end_ignore
select relname, distkey as distribution_attributes from gp_distribution_policy p, pg_class c where p.localoid = c.oid and relname like 'tbl1_tbl_11257%';
                relname                 | distribution_attributes 
----------------------------------------+-------------------------
 tbl1_tbl_11257                         | 3
 tbl1_tbl_11257_1_prt_d_2010_09_28      | 3
 tbl1_tbl_11257_1_prt_d_2010_09_27      | 3
 tbl1_tbl_11257_1_prt_default_partition | 3
(4 rows)

create table qp_misc_jiras.tbl2_tbl_11257 (
a int,
b int,
c int,
constraint pk_tbl2_tbl_11257 primary key (a,b,c)
)
distributed by (a)
partition by range (b)
(
 default partition default_partition,
partition p1 start (1) end (2)
 );
NOTICE:  CREATE TABLE will create partition "tbl2_tbl_11257_1_prt_default_partition" for table "tbl2_tbl_11257"
NOTICE:  CREATE TABLE will create partition "tbl2_tbl_11257_1_prt_p1" for table "tbl2_tbl_11257"
insert into qp_misc_jiras.tbl2_tbl_11257 values(1,2,3);
select * from qp_misc_jiras.tbl2_tbl_11257;
 a | b | c 
---+---+---
 1 | 2 | 3
(1 row)

delete from qp_misc_jiras.tbl2_tbl_11257 where a=1 and b=2 and c=3;
select * from qp_misc_jiras.tbl2_tbl_11257;
 a | b | c 
---+---+---
(0 rows)

insert into qp_misc_jiras.tbl2_tbl_11257 values(1,2,3); 
select relname, distkey as distribution_attributes from gp_distribution_policy p, pg_class c where p.localoid = c.oid and relname like 'tbl2_tbl_11257%';
                relname                 | distribution_attributes 
----------------------------------------+-------------------------
 tbl2_tbl_11257                         | 1
 tbl2_tbl_11257_1_prt_default_partition | 1
 tbl2_tbl_11257_1_prt_p1                | 1
(3 rows)

-- start_ignore
alter table qp_misc_jiras.tbl2_tbl_11257 split default partition
start (3)
end (4)
into (partition p2, partition default_partition);
NOTICE:  exchanged partition "default_partition" of relation "tbl2_tbl_11257" with relation "pg_temp_37629"
NOTICE:  dropped partition "default_partition" for relation "tbl2_tbl_11257"
NOTICE:  CREATE TABLE will create partition "tbl2_tbl_11257_1_prt_p2" for table "tbl2_tbl_11257"
NOTICE:  CREATE TABLE will create partition "tbl2_tbl_11257_1_prt_default_partition" for table "tbl2_tbl_11257"
-- end_ignore
select relname, distkey as distribution_attributes from gp_distribution_policy p, pg_class c where p.localoid = c.oid and relname like 'tbl2_tbl_11257%';
                relname                 | distribution_attributes 
----------------------------------------+-------------------------
 tbl2_tbl_11257                         | 1
 tbl2_tbl_11257_1_prt_p1                | 1
 tbl2_tbl_11257_1_prt_p2                | 1
 tbl2_tbl_11257_1_prt_default_partition | 1
(4 rows)

delete from qp_misc_jiras.tbl2_tbl_11257 where a=1 and b=2 and c=3;
select * from qp_misc_jiras.tbl2_tbl_11257;
 a | b | c 
---+---+---
(0 rows)

drop table qp_misc_jiras.tbl1_tbl_11257;
drop table qp_misc_jiras.tbl2_tbl_11257;
set enable_seqscan=off;
--
-- Heap table. TidScan should be used.
--
create table qp_misc_jiras.test_heap (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.
insert into qp_misc_jiras.test_heap values (0, 0);
explain select  * from qp_misc_jiras.test_heap where ctid='(0,1)' and gp_segment_id >= 0;
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..100.02 rows=1 width=8)
   ->  Tid Scan on test_heap  (cost=0.00..100.02 rows=1 width=8)
         TID Cond: ctid = '(0,1)'::tid
         Filter: gp_segment_id >= 0
 Settings:  enable_bitmapscan=off; enable_groupagg=on; enable_indexscan=on; enable_seqscan=off; optimizer=off; optimizer_segments=3
 Optimizer status: Postgres query optimizer
(6 rows)

select  * from qp_misc_jiras.test_heap where ctid='(0,1)' and gp_segment_id >= 0;
 i | j 
---+---
 0 | 0
(1 row)

--
-- AO table. TidScan should not be used.
--
create table qp_misc_jiras.test_ao (i int, j int) with (appendonly=true);
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 qp_misc_jiras.test_ao values (0, 0);
explain select  * from qp_misc_jiras.test_ao where ctid='(33554432,1)' and gp_segment_id >= 0;
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..13.91 rows=1 width=8)
   ->  Seq Scan on test_ao  (cost=0.00..13.91 rows=1 width=8)
         Filter: gp_segment_id >= 0 AND ctid = '(33554432,1)'::tid
 Settings:  enable_bitmapscan=off; enable_groupagg=on; enable_indexscan=on; enable_seqscan=off; optimizer=off; optimizer_segments=3
 Optimizer status: Postgres query optimizer
(5 rows)

select  * from qp_misc_jiras.test_ao where ctid='(33554432,1)' and gp_segment_id >= 0;
 i | j 
---+---
 0 | 0
(1 row)

--
-- CO table. TidScan should not be used.
--
create table qp_misc_jiras.test_co (i int, j int) with (appendonly=true, orientation=column);
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 qp_misc_jiras.test_co values (0, 0);
explain select  * from qp_misc_jiras.test_co where ctid='(33554432,1)' and gp_segment_id >= 0;
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..13.91 rows=1 width=8)
   ->  Seq Scan on test_co  (cost=0.00..13.91 rows=1 width=8)
         Filter: gp_segment_id >= 0 AND ctid = '(33554432,1)'::tid
 Settings:  enable_bitmapscan=off; enable_groupagg=on; enable_indexscan=on; enable_seqscan=off; optimizer=off; optimizer_segments=3
 Optimizer status: Postgres query optimizer
(5 rows)

select  * from qp_misc_jiras.test_co where ctid='(33554432,1)' and gp_segment_id >= 0;
 i | j 
---+---
 0 | 0
(1 row)

-- This is to verify MPP-10856: test gp_enable_explain_allstat
set gp_enable_explain_allstat=on;
insert into qp_misc_jiras.test_heap select i, i from generate_series(0, 99999) i;
explain analyze select count(*) from qp_misc_jiras.test_heap;
                                                                         QUERY PLAN                                                                         
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1176.32..1176.33 rows=1 width=8)
   ->  Gather Motion 3:1  (slice1; segments: 3)  (cost=1176.25..1176.30 rows=1 width=8)
         ->  Aggregate  (cost=1176.25..1176.26 rows=1 width=8)
               allstat: seg_firststart_total_ntuples/seg0_0.621 ms_15 ms_1/seg1_0.621 ms_15 ms_1/seg2_0.634 ms_15 ms_1//end
               ->  Seq Scan on test_heap  (cost=0.00..961.00 rows=28700 width=0)
                     allstat: seg_firststart_total_ntuples/seg0_0.622 ms_7.878 ms_33350/seg1_0.622 ms_7.850 ms_33336/seg2_0.634 ms_7.866 ms_33315//end
 Slice statistics:
   (slice0)    Executor memory: 382K bytes.
   (slice1)    Executor memory: 163K bytes avg x 3 workers, 163K bytes max (seg0).
 Statement statistics:
   Memory used: 128000K bytes
 Settings:  enable_bitmapscan=off; enable_groupagg=on; enable_indexscan=on; enable_seqscan=off; optimizer_segments=3
 Total runtime: 15.974 ms
(17 rows)

-- start_ignore
-- This is to verify MPP-8946
-- ramans2 : Modifying queries to add filter on schema name to remove diffs in multi-node cdbfast runs
-- end_ignore
create schema schema1;
create schema schema2;
create schema schema3;
create table schema1.foo1 (i int) partition by range(i)  (start(1) end(2) every(1));
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
NOTICE:  CREATE TABLE will create partition "foo1_1_prt_1" for table "foo1"
create table schema2.foo2 (i int) partition by range(i)  (start(1) end(2) every(1));
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
NOTICE:  CREATE TABLE will create partition "foo2_1_prt_1" for table "foo2"
create table schema2.foo3 (i int) partition by range(i)  (start(1) end(2) every(1));
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
NOTICE:  CREATE TABLE will create partition "foo3_1_prt_1" for table "foo3"
create table schema3.foo4 (i int) partition by range(i)  (start(1) end(2) every(1));
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
NOTICE:  CREATE TABLE will create partition "foo4_1_prt_1" for table "foo4"
set statement_mem='1900MB';
select * from pg_partitions where schemaname in ('schema1', 'schema2', 'schema3');
 schemaname | tablename | partitionschemaname | partitiontablename | partitionname | parentpartitiontablename | parentpartitionname | partitiontype | partitionlevel | partitionrank | partitionposition | partitionlistvalues | partitionrangestart | partitionstartinclusive | partitionrangeend | partitionendinclusive | partitioneveryclause | partitionisdefault |      partitionboundary      | parenttablespace | partitiontablespace 
------------+-----------+---------------------+--------------------+---------------+--------------------------+---------------------+---------------+----------------+---------------+-------------------+---------------------+---------------------+-------------------------+-------------------+-----------------------+----------------------+--------------------+-----------------------------+------------------+---------------------
 schema1    | foo1      | schema1             | foo1_1_prt_1       |               |                          |                     | range         |              0 |             1 |                 1 |                     | 1                   | t                       | 2                 | f                     | 1                    | f                  | START (1) END (2) EVERY (1) | pg_default       | pg_default
 schema2    | foo2      | schema2             | foo2_1_prt_1       |               |                          |                     | range         |              0 |             1 |                 1 |                     | 1                   | t                       | 2                 | f                     | 1                    | f                  | START (1) END (2) EVERY (1) | pg_default       | pg_default
 schema2    | foo3      | schema2             | foo3_1_prt_1       |               |                          |                     | range         |              0 |             1 |                 1 |                     | 1                   | t                       | 2                 | f                     | 1                    | f                  | START (1) END (2) EVERY (1) | pg_default       | pg_default
 schema3    | foo4      | schema3             | foo4_1_prt_1       |               |                          |                     | range         |              0 |             1 |                 1 |                     | 1                   | t                       | 2                 | f                     | 1                    | f                  | START (1) END (2) EVERY (1) | pg_default       | pg_default
(4 rows)

--
-- Case 1: query from MPP-7073.
-- expected 0 rows
--
select * from pg_partitions p 
   where not exists (select 1 from pg_partitions p2 where p2.schemaname = p.schemaname) and p.schemaname in ('schema1', 'schema2', 'schema3'); 
 schemaname | tablename | partitionschemaname | partitiontablename | partitionname | parentpartitiontablename | parentpartitionname | partitiontype | partitionlevel | partitionrank | partitionposition | partitionlistvalues | partitionrangestart | partitionstartinclusive | partitionrangeend | partitionendinclusive | partitioneveryclause | partitionisdefault | partitionboundary | parenttablespace | partitiontablespace 
------------+-----------+---------------------+--------------------+---------------+--------------------------+---------------------+---------------+----------------+---------------+-------------------+---------------------+---------------------+-------------------------+-------------------+-----------------------+----------------------+--------------------+-------------------+------------------+---------------------
(0 rows)

--
-- Case 2: modified form of Case 1 which returns non-zero results
-- expected 2 rows: (foo1 and foo4)
--
select * from pg_partitions p 
   where not exists (select 1 from pg_partitions p2 where p2.schemaname = p.schemaname and p2.tablename != p.tablename ) and p.schemaname in ('schema1', 'schema2', 'schema3'); 
 schemaname | tablename | partitionschemaname | partitiontablename | partitionname | parentpartitiontablename | parentpartitionname | partitiontype | partitionlevel | partitionrank | partitionposition | partitionlistvalues | partitionrangestart | partitionstartinclusive | partitionrangeend | partitionendinclusive | partitioneveryclause | partitionisdefault |      partitionboundary      | parenttablespace | partitiontablespace 
------------+-----------+---------------------+--------------------+---------------+--------------------------+---------------------+---------------+----------------+---------------+-------------------+---------------------+---------------------+-------------------------+-------------------+-----------------------+----------------------+--------------------+-----------------------------+------------------+---------------------
 schema1    | foo1      | schema1             | foo1_1_prt_1       |               |                          |                     | range         |              0 |             1 |                 1 |                     | 1                   | t                       | 2                 | f                     | 1                    | f                  | START (1) END (2) EVERY (1) | pg_default       | pg_default
 schema3    | foo4      | schema3             | foo4_1_prt_1       |               |                          |                     | range         |              0 |             1 |                 1 |                     | 1                   | t                       | 2                 | f                     | 1                    | f                  | START (1) END (2) EVERY (1) | pg_default       | pg_default
(2 rows)

--
-- Case 3: query from MPP-8946.
--
select schemaname, tablename, partitionschemaname,
       partitiontablename, partitionlevel 
  from pg_partitions p1 
  where partitionlevel > 0 
    and partitionlevel < (select max(partitionlevel)
                          from pg_partitions p2 where p2.tablename=p1.tablename)  and schemaname in ('schema1', 'schema2', 'schema3'); 
 schemaname | tablename | partitionschemaname | partitiontablename | partitionlevel 
------------+-----------+---------------------+--------------------+----------------
(0 rows)

drop schema schema1 cascade;
NOTICE:  drop cascades to table schema1.foo1
drop schema schema2 cascade;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to table schema2.foo2
drop cascades to table schema2.foo3
drop schema schema3 cascade;
NOTICE:  drop cascades to table schema3.foo4
-- This is the to verify MPP-9167:
--	Gather Motion shows wrong row estimate compared to 3.3
-- 	from Lyublena: 
	/*
 	I believe the correct number of total rows output by explain should be on  a per segment basis.

	For example, a sequential scan on 2 segments with 500 rows each should
	show 500. For Motion nodes, the number is based on the number of segments
	that send data in, and you might see a difference in the output of explain
	analyze that shows the number of rows sent out.
	
	For example in the last plan below, for the redistribute motion node you
	see "rows=500" and "Rows out:  Avg 500.0 rows x 2 workers". For the gather
	motion you get "rows=500" and "Rows out:  1000 rows at destination". That
	output should be fine.
	*/
create table qp_misc_jiras.r(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 qp_misc_jiras.r select generate_series(1,1000), generate_series(1,1000);
create table qp_misc_jiras.s(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 qp_misc_jiras.s select generate_series(1,1000), generate_series(1,1000);
analyze qp_misc_jiras.r;
analyze qp_misc_jiras.s;
explain select * from qp_misc_jiras.r;
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice1; segments: 3)  (cost=0.00..13.00 rows=1000 width=8)
   ->  Seq Scan on r  (cost=0.00..13.00 rows=334 width=8)
 Settings:  enable_bitmapscan=off; enable_groupagg=on; enable_indexscan=on; enable_seqscan=off; optimizer=off; optimizer_segments=3
 Optimizer status: Postgres query optimizer
(4 rows)

explain analyze select * from qp_misc_jiras.r,qp_misc_jiras.s where r.a=s.b;
                                                                          QUERY PLAN                                                                          
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather Motion 3:1  (slice2; segments: 3)  (cost=25.50..73.50 rows=1000 width=16)
   ->  Hash Join  (cost=25.50..73.50 rows=334 width=16)
         Hash Cond: s.b = r.a
         Executor memory:  11K bytes avg, 11K bytes max (seg1).
         Work_mem used:  11K bytes avg, 11K bytes max (seg1). Workfile: (0 spilling, 0 reused)
         (seg1)   Hash chain length 1.0 avg, 1 max, using 342 of 8388619 buckets.
         allstat: seg_firststart_total_ntuples/seg0_0.408 ms_116 ms_321/seg1_0.434 ms_126 ms_342/seg2_0.446 ms_119 ms_337//end
         ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..33.00 rows=334 width=8)
               Hash Key: s.b
               allstat: seg_firststart_total_ntuples/seg0_78 ms_0.142 ms_321/seg1_86 ms_0.177 ms_342/seg2_80 ms_0.135 ms_337//end
               ->  Seq Scan on s  (cost=0.00..13.00 rows=334 width=8)
                     allstat: seg_firststart_total_ntuples/seg0_0.822 ms_2.413 ms_321/seg1_2.147 ms_2.023 ms_342/seg2_2.115 ms_1.947 ms_337//end
         ->  Hash  (cost=13.00..13.00 rows=334 width=8)
               Rows in:  Avg 333.3 rows x 3 workers.  Max 342 rows (seg1) with 0.247 ms to end, start offset by 86 ms.
               allstat: seg_firststart_total_ntuples/seg0_78 ms_0.215 ms_321/seg1_86 ms_0.247 ms_342/seg2_80 ms_0.173 ms_337//end
               ->  Seq Scan on r  (cost=0.00..13.00 rows=334 width=8)
                     allstat: seg_firststart_total_ntuples/seg0_78 ms_0.073 ms_321/seg1_86 ms_0.099 ms_342/seg2_80 ms_0.065 ms_337//end
 Slice statistics:
   (slice0)    Executor memory: 267K bytes.
   (slice1)    Executor memory: 193K bytes avg x 3 workers, 193K bytes max (seg0).
   (slice2)    Executor memory: 131366K bytes avg x 3 workers, 131387K bytes max (seg1).  Work_mem: 11K bytes max.
 Statement statistics:
   Memory used: 1945600K bytes
 Settings:  enable_bitmapscan=off; enable_groupagg=on; enable_indexscan=on; enable_seqscan=off; optimizer=off; optimizer_segments=3
 Optimizer status: Postgres query optimizer
 Total runtime: 127.268 ms
(31 rows)

drop table qp_misc_jiras.r;
drop table qp_misc_jiras.s;
--to_date had issues when there were padding(space) in front of the string
select to_date ('20010101','YYYYMMDD');
  to_date   
------------
 2001-01-01
(1 row)

select to_date (' 20010101','YYYYMMDD');
  to_date   
------------
 2001-01-01
(1 row)

select to_date ('    20010101','YYYYMMDD');
  to_date   
------------
 2001-01-01
(1 row)

select to_date(to_char( 20010101, '99999999'), 'YYYYMMDD');
  to_date   
------------
 2001-01-01
(1 row)

select to_date('20000-1130', 'YYYY-MMDD');
   to_date   
-------------
 20000-11-30
(1 row)

select to_date('  20000-1130', 'YYYY-MMDD');
   to_date   
-------------
 20000-11-30
(1 row)

select to_date(' 2011-01-20','YYYY-MM-DD HH24:MI:SS');
  to_date   
------------
 2011-01-20
(1 row)

SELECT to_date('20110521', 'YYYYMMDD'), to_date(' 20110521', 'YYYYMMDD'), to_date(' 20110521', ' YYYYMMDD');
  to_date   |  to_date   |  to_date   
------------+------------+------------
 2011-05-21 | 2011-05-21 | 2011-05-21
(1 row)

select to_timestamp('2011-01-20','YYYY-MM-DD HH24:MI:SS');
      to_timestamp      
------------------------
 2011-01-20 00:00:00-08
(1 row)

select to_timestamp(' 2011-01-20','YYYY-MM-DD HH24:MI:SS');
      to_timestamp      
------------------------
 2011-01-20 00:00:00-08
(1 row)

select to_timestamp('    2011-01-20','YYYY-MM-DD HH24:MI:SS');
      to_timestamp      
------------------------
 2011-01-20 00:00:00-08
(1 row)

set TIMEZONE TO 'America/Los_Angeles';
select to_timestamp('15:12:02.020.001230', 'HH:MI:SS.MS.US');
WARNING:  hour "15" is invalid for the 12-hour clock
HINT:  Use the 24-hour clock, or give an hour between 1 and 12.
             to_timestamp              
---------------------------------------
 0001-01-01 15:12:02.02123-07:52:58 BC
(1 row)

--The above warning is due to change in postgres after 3.2
-- To avoid the warning use HH24 format instead of HH
select to_timestamp('15:12:02.020.001230', 'HH24:MI:SS.MS.US');
             to_timestamp              
---------------------------------------
 0001-01-01 15:12:02.02123-07:52:58 BC
(1 row)

reset TIMEZONE;
create table qp_misc_jiras.tbl13409_test (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.
--Hangs when given an invalid value for reorganize
alter table qp_misc_jiras.tbl13409_test set with (reorganize=foo) distributed by (j);
ERROR:  invalid REORGANIZE option
HINT:  Valid REORGANIZE option is "true" or "false".
--Invalid integer value. def->arg
alter table qp_misc_jiras.tbl13409_test set with (reorganize=123) distributed by (j);
ERROR:  invalid REORGANIZE option
HINT:  Valid REORGANIZE option is "true" or "false".
alter table qp_misc_jiras.tbl13409_test set with (reorganize="true");
ERROR:  invalid REORGANIZE option
HINT:  Valid REORGANIZE option is "true" or "false".
alter table qp_misc_jiras.tbl13409_test set with (reorganize="TRUE");
ERROR:  invalid REORGANIZE option
HINT:  Valid REORGANIZE option is "true" or "false".
alter table qp_misc_jiras.tbl13409_test set with (reorganize="FALSE");
ERROR:  invalid REORGANIZE option
HINT:  Valid REORGANIZE option is "true" or "false".
alter table qp_misc_jiras.tbl13409_test set with (reorganize="false");
ERROR:  invalid REORGANIZE option
HINT:  Valid REORGANIZE option is "true" or "false".
--Valid strings
alter table qp_misc_jiras.tbl13409_test set with (reorganize=true);
alter table qp_misc_jiras.tbl13409_test set with (reorganize=TRUE);
alter table qp_misc_jiras.tbl13409_test set with (reorganize=FALSE);
alter table qp_misc_jiras.tbl13409_test set with (reorganize=false);
alter table qp_misc_jiras.tbl13409_test set with (reorganize='false');
alter table qp_misc_jiras.tbl13409_test set with (reorganize='FALSE');
alter table qp_misc_jiras.tbl13409_test set with (reorganize='TRUE');
alter table qp_misc_jiras.tbl13409_test set with (reorganize='true');
create table qp_misc_jiras.tbl13879_1 (a int) distributed by (a);
insert into qp_misc_jiras.tbl13879_1 select generate_series(1,10);
select * from qp_misc_jiras.tbl13879_1;
 a  
----
  3
  4
  5
  6
  7
  8
  9
 10
  1
  2
(10 rows)

select a, max(a) over (order by a range between current row and 2 following) as max from qp_misc_jiras.tbl13879_1;
 a  | max 
----+-----
  1 |   3
  2 |   4
  3 |   5
  4 |   6
  5 |   7
  6 |   8
  7 |   9
  8 |  10
  9 |  10
 10 |  10
(10 rows)

create table qp_misc_jiras.tbl13879_2 (a numeric) distributed by (a);
create table qp_misc_jiras.tbl13879_2 (a numeric) distributed by (a);
ERROR:  relation "tbl13879_2" already exists
insert into qp_misc_jiras.tbl13879_2 select generate_series(1,10);
select * from qp_misc_jiras.tbl13879_2;
 a  
----
  1
  4
  7
 10
  2
  5
  8
  3
  6
  9
(10 rows)

select a, max(a) over (order by a range between current row and 2 following) as max from qp_misc_jiras.tbl13879_2;
 a  | max 
----+-----
  1 |   3
  2 |   4
  3 |   5
  4 |   6
  5 |   7
  6 |   8
  7 |   9
  8 |  10
  9 |  10
 10 |  10
(10 rows)

drop table qp_misc_jiras.tbl13879_1;
drop table qp_misc_jiras.tbl13879_2;
create table qp_misc_jiras.esc176_1 (id integer, seq integer, val double precision, clickdate timestamp without time zone) distributed by (id);
insert into qp_misc_jiras.esc176_1 values (1,1,0.2,CURRENT_TIMESTAMP);
insert into qp_misc_jiras.esc176_1 values (1,2,0.1,CURRENT_TIMESTAMP);
insert into qp_misc_jiras.esc176_1 values (1,3,0.5,CURRENT_TIMESTAMP);
insert into qp_misc_jiras.esc176_1 values (1,4,0.7,CURRENT_TIMESTAMP);
select id, seq, sum (val) over (partition by id order by clickdate range between interval '0 seconds' following and interval '1000 seconds' following) from qp_misc_jiras.esc176_1;
 id | seq | sum 
----+-----+-----
  1 |   1 | 1.5
  1 |   2 | 1.3
  1 |   3 | 1.2
  1 |   4 | 0.7
(4 rows)

select id, seq, sum (val) over (partition by id order by clickdate range between interval '0 seconds' preceding and interval '1000 seconds' following) from qp_misc_jiras.esc176_1;
 id | seq | sum 
----+-----+-----
  1 |   1 | 1.5
  1 |   2 | 1.3
  1 |   3 | 1.2
  1 |   4 | 0.7
(4 rows)

select id, seq, sum(val) over (partition by id order by seq::numeric range between 0 following and 10 following), val from qp_misc_jiras.esc176_1;
 id | seq | sum | val 
----+-----+-----+-----
  1 |   1 | 1.5 | 0.2
  1 |   2 | 1.3 | 0.1
  1 |   3 | 1.2 | 0.5
  1 |   4 | 0.7 | 0.7
(4 rows)

select id, seq, sum(val) over (partition by id order by seq::numeric range between 10 preceding and 0 preceding), val from qp_misc_jiras.esc176_1;
 id | seq | sum | val 
----+-----+-----+-----
  1 |   1 | 0.2 | 0.2
  1 |   2 | 0.3 | 0.1
  1 |   3 | 0.8 | 0.5
  1 |   4 | 1.5 | 0.7
(4 rows)

insert into qp_misc_jiras.esc176_1 values (1,9,0.3,CURRENT_TIMESTAMP);
insert into qp_misc_jiras.esc176_1 values (1,10,0.4,CURRENT_TIMESTAMP);
insert into qp_misc_jiras.esc176_1 values (1,11,0.6, CURRENT_TIMESTAMP);
insert into qp_misc_jiras.esc176_1 select * from qp_misc_jiras.esc176_1;
insert into qp_misc_jiras.esc176_1 select * from qp_misc_jiras.esc176_1;
insert into qp_misc_jiras.esc176_1 select * from qp_misc_jiras.esc176_1;
insert into qp_misc_jiras.esc176_1 values (1,9,0.3, CURRENT_TIMESTAMP);
insert into qp_misc_jiras.esc176_1 values (1,10,0.4,CURRENT_TIMESTAMP);
insert into qp_misc_jiras.esc176_1 values (1,11,0.6,CURRENT_TIMESTAMP);
select id, seq, sum (val) over (partition by id order by clickdate range between interval '0 seconds' following and interval '1000 seconds' following) from qp_misc_jiras.esc176_1;
 id | seq | sum  
----+-----+------
  1 |   1 | 23.7
  1 |   1 | 23.7
  1 |   1 | 23.7
  1 |   1 | 23.7
  1 |   1 | 23.7
  1 |   1 | 23.7
  1 |   1 | 23.7
  1 |   1 | 23.7
  1 |   2 | 22.1
  1 |   2 | 22.1
  1 |   2 | 22.1
  1 |   2 | 22.1
  1 |   2 | 22.1
  1 |   2 | 22.1
  1 |   2 | 22.1
  1 |   2 | 22.1
  1 |   3 | 21.3
  1 |   3 | 21.3
  1 |   3 | 21.3
  1 |   3 | 21.3
  1 |   3 | 21.3
  1 |   3 | 21.3
  1 |   3 | 21.3
  1 |   3 | 21.3
  1 |   4 | 17.3
  1 |   4 | 17.3
  1 |   4 | 17.3
  1 |   4 | 17.3
  1 |   4 | 17.3
  1 |   4 | 17.3
  1 |   4 | 17.3
  1 |   4 | 17.3
  1 |   9 | 11.7
  1 |   9 | 11.7
  1 |   9 | 11.7
  1 |   9 | 11.7
  1 |   9 | 11.7
  1 |   9 | 11.7
  1 |   9 | 11.7
  1 |   9 | 11.7
  1 |  10 |  9.3
  1 |  10 |  9.3
  1 |  10 |  9.3
  1 |  10 |  9.3
  1 |  10 |  9.3
  1 |  10 |  9.3
  1 |  10 |  9.3
  1 |  10 |  9.3
  1 |  11 |  6.1
  1 |  11 |  6.1
  1 |  11 |  6.1
  1 |  11 |  6.1
  1 |  11 |  6.1
  1 |  11 |  6.1
  1 |  11 |  6.1
  1 |  11 |  6.1
  1 |   9 |  1.3
  1 |  10 |    1
  1 |  11 |  0.6
(59 rows)

select id, seq, sum (val) over (partition by id order by clickdate range between interval '0 seconds' preceding and interval '1000 seconds' following) from qp_misc_jiras.esc176_1;
 id | seq | sum  
----+-----+------
  1 |   1 | 23.7
  1 |   1 | 23.7
  1 |   1 | 23.7
  1 |   1 | 23.7
  1 |   1 | 23.7
  1 |   1 | 23.7
  1 |   1 | 23.7
  1 |   1 | 23.7
  1 |   2 | 22.1
  1 |   2 | 22.1
  1 |   2 | 22.1
  1 |   2 | 22.1
  1 |   2 | 22.1
  1 |   2 | 22.1
  1 |   2 | 22.1
  1 |   2 | 22.1
  1 |   3 | 21.3
  1 |   3 | 21.3
  1 |   3 | 21.3
  1 |   3 | 21.3
  1 |   3 | 21.3
  1 |   3 | 21.3
  1 |   3 | 21.3
  1 |   3 | 21.3
  1 |   4 | 17.3
  1 |   4 | 17.3
  1 |   4 | 17.3
  1 |   4 | 17.3
  1 |   4 | 17.3
  1 |   4 | 17.3
  1 |   4 | 17.3
  1 |   4 | 17.3
  1 |   9 | 11.7
  1 |   9 | 11.7
  1 |   9 | 11.7
  1 |   9 | 11.7
  1 |   9 | 11.7
  1 |   9 | 11.7
  1 |   9 | 11.7
  1 |   9 | 11.7
  1 |  10 |  9.3
  1 |  10 |  9.3
  1 |  10 |  9.3
  1 |  10 |  9.3
  1 |  10 |  9.3
  1 |  10 |  9.3
  1 |  10 |  9.3
  1 |  10 |  9.3
  1 |  11 |  6.1
  1 |  11 |  6.1
  1 |  11 |  6.1
  1 |  11 |  6.1
  1 |  11 |  6.1
  1 |  11 |  6.1
  1 |  11 |  6.1
  1 |  11 |  6.1
  1 |   9 |  1.3
  1 |  10 |    1
  1 |  11 |  0.6
(59 rows)

select id, seq, sum(val) over (partition by id order by seq::numeric range between 0 following and 10 following), val from qp_misc_jiras.esc176_1;
 id | seq | sum  | val 
----+-----+------+-----
  1 |   1 | 23.7 | 0.2
  1 |   1 | 23.7 | 0.2
  1 |   1 | 23.7 | 0.2
  1 |   1 | 23.7 | 0.2
  1 |   1 | 23.7 | 0.2
  1 |   1 | 23.7 | 0.2
  1 |   1 | 23.7 | 0.2
  1 |   1 | 23.7 | 0.2
  1 |   2 | 22.1 | 0.1
  1 |   2 | 22.1 | 0.1
  1 |   2 | 22.1 | 0.1
  1 |   2 | 22.1 | 0.1
  1 |   2 | 22.1 | 0.1
  1 |   2 | 22.1 | 0.1
  1 |   2 | 22.1 | 0.1
  1 |   2 | 22.1 | 0.1
  1 |   3 | 21.3 | 0.5
  1 |   3 | 21.3 | 0.5
  1 |   3 | 21.3 | 0.5
  1 |   3 | 21.3 | 0.5
  1 |   3 | 21.3 | 0.5
  1 |   3 | 21.3 | 0.5
  1 |   3 | 21.3 | 0.5
  1 |   3 | 21.3 | 0.5
  1 |   4 | 17.3 | 0.7
  1 |   4 | 17.3 | 0.7
  1 |   4 | 17.3 | 0.7
  1 |   4 | 17.3 | 0.7
  1 |   4 | 17.3 | 0.7
  1 |   4 | 17.3 | 0.7
  1 |   4 | 17.3 | 0.7
  1 |   4 | 17.3 | 0.7
  1 |   9 | 11.7 | 0.3
  1 |   9 | 11.7 | 0.3
  1 |   9 | 11.7 | 0.3
  1 |   9 | 11.7 | 0.3
  1 |   9 | 11.7 | 0.3
  1 |   9 | 11.7 | 0.3
  1 |   9 | 11.7 | 0.3
  1 |   9 | 11.7 | 0.3
  1 |   9 | 11.7 | 0.3
  1 |  10 |    9 | 0.4
  1 |  10 |    9 | 0.4
  1 |  10 |    9 | 0.4
  1 |  10 |    9 | 0.4
  1 |  10 |    9 | 0.4
  1 |  10 |    9 | 0.4
  1 |  10 |    9 | 0.4
  1 |  10 |    9 | 0.4
  1 |  10 |    9 | 0.4
  1 |  11 |  5.4 | 0.6
  1 |  11 |  5.4 | 0.6
  1 |  11 |  5.4 | 0.6
  1 |  11 |  5.4 | 0.6
  1 |  11 |  5.4 | 0.6
  1 |  11 |  5.4 | 0.6
  1 |  11 |  5.4 | 0.6
  1 |  11 |  5.4 | 0.6
  1 |  11 |  5.4 | 0.6
(59 rows)

select id, seq, sum(val) over (partition by id order by seq::numeric range between 10 preceding and 0 preceding), val from qp_misc_jiras.esc176_1;
 id | seq | sum  | val 
----+-----+------+-----
  1 |   1 |  1.6 | 0.2
  1 |   1 |  1.6 | 0.2
  1 |   1 |  1.6 | 0.2
  1 |   1 |  1.6 | 0.2
  1 |   1 |  1.6 | 0.2
  1 |   1 |  1.6 | 0.2
  1 |   1 |  1.6 | 0.2
  1 |   1 |  1.6 | 0.2
  1 |   2 |  2.4 | 0.1
  1 |   2 |  2.4 | 0.1
  1 |   2 |  2.4 | 0.1
  1 |   2 |  2.4 | 0.1
  1 |   2 |  2.4 | 0.1
  1 |   2 |  2.4 | 0.1
  1 |   2 |  2.4 | 0.1
  1 |   2 |  2.4 | 0.1
  1 |   3 |  6.4 | 0.5
  1 |   3 |  6.4 | 0.5
  1 |   3 |  6.4 | 0.5
  1 |   3 |  6.4 | 0.5
  1 |   3 |  6.4 | 0.5
  1 |   3 |  6.4 | 0.5
  1 |   3 |  6.4 | 0.5
  1 |   3 |  6.4 | 0.5
  1 |   4 |   12 | 0.7
  1 |   4 |   12 | 0.7
  1 |   4 |   12 | 0.7
  1 |   4 |   12 | 0.7
  1 |   4 |   12 | 0.7
  1 |   4 |   12 | 0.7
  1 |   4 |   12 | 0.7
  1 |   4 |   12 | 0.7
  1 |   9 | 14.7 | 0.3
  1 |   9 | 14.7 | 0.3
  1 |   9 | 14.7 | 0.3
  1 |   9 | 14.7 | 0.3
  1 |   9 | 14.7 | 0.3
  1 |   9 | 14.7 | 0.3
  1 |   9 | 14.7 | 0.3
  1 |   9 | 14.7 | 0.3
  1 |   9 | 14.7 | 0.3
  1 |  10 | 18.3 | 0.4
  1 |  10 | 18.3 | 0.4
  1 |  10 | 18.3 | 0.4
  1 |  10 | 18.3 | 0.4
  1 |  10 | 18.3 | 0.4
  1 |  10 | 18.3 | 0.4
  1 |  10 | 18.3 | 0.4
  1 |  10 | 18.3 | 0.4
  1 |  10 | 18.3 | 0.4
  1 |  11 | 23.7 | 0.6
  1 |  11 | 23.7 | 0.6
  1 |  11 | 23.7 | 0.6
  1 |  11 | 23.7 | 0.6
  1 |  11 | 23.7 | 0.6
  1 |  11 | 23.7 | 0.6
  1 |  11 | 23.7 | 0.6
  1 |  11 | 23.7 | 0.6
  1 |  11 | 23.7 | 0.6
(59 rows)

drop table qp_misc_jiras.esc176_1;
create table qp_misc_jiras.tbl13491_h(a int,str varchar)distributed by (a);
alter table qp_misc_jiras.tbl13491_h alter column str set storage external;
insert into qp_misc_jiras.tbl13491_h values (1, lpad('a', 100000, 'b'));
create table qp_misc_jiras.tbl13491_aocol with (appendonly=true, orientation=column) as select * from qp_misc_jiras.tbl13491_h distributed by (a);
truncate table qp_misc_jiras.tbl13491_h;
\t off
select str = lpad('a', 100000, 'b') from qp_misc_jiras.tbl13491_aocol;
 ?column? 
----------
 t
(1 row)

drop table qp_misc_jiras.tbl13491_h;
select str = lpad('a', 100000, 'b') from qp_misc_jiras.tbl13491_aocol;
 ?column? 
----------
 t
(1 row)

drop table qp_misc_jiras.tbl13491_aocol;
-- start_ignore
drop function if exists test();
NOTICE:  function test() does not exist, skipping
create table qp_misc_jiras._tbl10050_test (id int) distributed randomly;
-- end_ignore
create function test()
returns void
as
$$
begin
drop table if exists qp_misc_jiras._tbl10050_test;
create table qp_misc_jiras._tbl10050_test (id int) distributed randomly;
insert into qp_misc_jiras._tbl10050_test values (1);
end;
$$
language plpgsql
;
select test();
 test 
------
 
(1 row)

select test();
 test 
------
 
(1 row)

select test();
 test 
------
 
(1 row)

select test();
 test 
------
 
(1 row)

--
-- Test gp_enable_relsize_collection's effect on ORCA plan generation
--
create table tbl_z(x int) distributed by (x);
set optimizer_metadata_caching to off;
insert into tbl_z select i from generate_series(1,100) i;
-- plan with no relsize collection
explain select 1 as t1 where 1 <= ALL (select x from tbl_z);
                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Result  (cost=10000000651.88..10000000651.89 rows=1 width=0)
   One-Time Filter: (SubPlan 1)
   SubPlan 1  (slice0)
     ->  Materialize  (cost=10000000000.00..10000001544.50 rows=32100 width=4)
           ->  Gather Motion 3:1  (slice1; segments: 3)  (cost=10000000000.00..10000001063.00 rows=96300 width=4)
                 ->  Seq Scan on tbl_z  (cost=10000000000.00..10000001063.00 rows=32100 width=4)
 Optimizer: Postgres query optimizer
(7 rows)

set gp_enable_relsize_collection = on;
-- plan with relsize collection
explain select 1 as t1 where 1 <= ALL (select x from tbl_z);
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Result  (cost=10000000006.52..10000000006.53 rows=1 width=0)
   One-Time Filter: (SubPlan 1)
   SubPlan 1  (slice0)
     ->  Materialize  (cost=10000000000.00..10000000015.44 rows=321 width=4)
           ->  Gather Motion 3:1  (slice1; segments: 3)  (cost=10000000000.00..10000000010.63 rows=963 width=4)
                 ->  Seq Scan on tbl_z  (cost=10000000000.00..10000000010.63 rows=321 width=4)
 Optimizer: Postgres query optimizer
(7 rows)

drop table if exists tbl_z;
reset optimizer_metadata_caching;
reset gp_enable_relsize_collection;
-- start_ignore
drop schema qp_misc_jiras cascade;
NOTICE:  drop cascades to table qp_misc_jiras._tbl10050_test
NOTICE:  drop cascades to table qp_misc_jiras.tbl13409_test
NOTICE:  drop cascades to append only columnar table qp_misc_jiras.test_co
NOTICE:  drop cascades to append only table qp_misc_jiras.test_ao
NOTICE:  drop cascades to table qp_misc_jiras.test_heap
NOTICE:  drop cascades to append only columnar table qp_misc_jiras.tbl9706aoc
NOTICE:  drop cascades to append only table qp_misc_jiras.tbl9706ao
NOTICE:  drop cascades to table qp_misc_jiras.tablecc
NOTICE:  drop cascades to table qp_misc_jiras.tablebb
NOTICE:  drop cascades to table qp_misc_jiras.tableaa
NOTICE:  drop cascades to table qp_misc_jiras.tablee
NOTICE:  drop cascades to table qp_misc_jiras.tabled
NOTICE:  drop cascades to table qp_misc_jiras.tablec
NOTICE:  drop cascades to table qp_misc_jiras.tableb
NOTICE:  drop cascades to table qp_misc_jiras.tablea
NOTICE:  drop cascades to table qp_misc_jiras.tbl7404_t2
NOTICE:  drop cascades to table qp_misc_jiras.tbl7404_t1
NOTICE:  drop cascades to table qp_misc_jiras.foo
NOTICE:  drop cascades to table qp_misc_jiras.tbl6027_test
NOTICE:  drop cascades to external table qp_misc_jiras.tbl3301_bar
NOTICE:  drop cascades to table qp_misc_jiras.tbl3301_foo
-- end_ignore