Mailing-List: contact commits-help@hawq.incubator.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@hawq.incubator.apache.org
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
From: huor@apache.org
To: commits@hawq.incubator.apache.org
Date: Wed, 15 Jun 2016 01:30:28 -0000
Message-Id: <dbd9e57dda7b4b92b3df8de6ec799e24@git.apache.org>
In-Reply-To: <1b8da508d6b94365a785eeebf31cc1bb@git.apache.org>
References: <1b8da508d6b94365a785eeebf31cc1bb@git.apache.org>
Subject: [2/4] incubator-hawq git commit: HAWQ-769. Remove function,
 function_extensions, set_functions and goh_partition test from installcheck,
 disable hcatalog and exttab1 test temporarily
archived-at: Wed, 15 Jun 2016 01:30:48 -0000

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/66f0bda1/src/test/regress/expected/set_functions.out
----------------------------------------------------------------------
diff --git a/src/test/regress/expected/set_functions.out b/src/test/regress/expected/set_functions.out
deleted file mode 100644
index 3ef716b..0000000
--- a/src/test/regress/expected/set_functions.out
+++ /dev/null
@@ -1,275 +0,0 @@
--- start_ignore
-drop schema set_functions cascade;
-ERROR:  schema "set_functions" does not exist
--- end_ignore
-create schema set_functions;
-set search_path=set_functions;
-DROP LANGUAGE IF EXISTS plpythonu CASCADE;
-CREATE LANGUAGE plpythonu;
-CREATE TABLE foo2(fooid int, f2 int);
-NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'fooid' 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 foo2 VALUES(1, 11);
-INSERT INTO foo2 VALUES(2, 22);
-INSERT INTO foo2 VALUES(1, 111);
-CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL;
-select foot.fooid, foot.f2 from foot(sin(pi()/2)::int) ORDER BY 1,2;
- fooid | f2  
--------+-----
-     1 |  11
-     1 | 111
-(2 rows)
-
-CREATE TABLE foo (fooid int, foosubid int, fooname text);
-NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'fooid' 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 foo VALUES(1,1,'Joe');
-INSERT INTO foo VALUES(1,2,'Ed');
-INSERT INTO foo VALUES(2,1,'Mary');
-CREATE FUNCTION getfoo(int) RETURNS setof int AS 'SELECT fooid FROM foo WHERE fooid = $1;' LANGUAGE SQL;
-SELECT * FROM getfoo(1) AS t1;
- t1 
-----
-  1
-  1
-(2 rows)
-
-CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
-SELECT * FROM vw_getfoo;
- getfoo 
---------
-      1
-      1
-(2 rows)
-
-DROP VIEW vw_getfoo;
-DROP FUNCTION getfoo(int);
-DROP FUNCTION foot(int);
-DROP TABLE foo2;
-DROP TABLE foo;
--- setof as a paramater --
-CREATE TYPE numtype as (i int, j int);
-CREATE FUNCTION g_numtype(x setof numtype) RETURNS setof numtype AS $$ select $1; $$ LANGUAGE SQL;
-DROP FUNCTION g_numtype(x setof numtype);
-DROP TYPE numtype;
---
--- Set functions samples from Madlib
---
-create function combination(s text) returns setof text[] as $$
-x = s.split(',')
-
-def subset(myset, N):
-   left = []
-   right = []
-   for i in range(0, len(myset)):
-      if ((1 << i) & N) > 0:
-         left.append(myset[i])
-      else:
-         right.append(myset[i])
-   return (', '.join(left), ', '.join(right))
-
-for i in range(1, (1 << len(x)) - 2):
-   yield subset(x, i)
-$$ language plpythonu strict;
-select x[1] || ' => ' || x[2] from combination('a,b,c,d') x;
-   ?column?   
---------------
- a => b, c, d
- b => a, c, d
- a, b => c, d
- c => a, b, d
- a, c => b, d
- b, c => a, d
- a, b, c => d
- d => a, b, c
- a, d => b, c
- b, d => a, c
- a, b, d => c
- c, d => a, b
- a, c, d => b
-(13 rows)
-
-CREATE TABLE rules(rule text) distributed by (rule);
-insert into rules values('a,b,c');
-insert into rules values('d,e');
-insert into rules values('f,g,h,i,j');
-insert into rules values('k,l,m');
-SELECT rule, combination(rule) from rules order by 1,2;
-   rule    |    combination     
------------+--------------------
- a,b,c     | {a,"b, c"}
- a,b,c     | {"a, b",c}
- a,b,c     | {"a, c",b}
- a,b,c     | {b,"a, c"}
- a,b,c     | {c,"a, b"}
- d,e       | {d,e}
- f,g,h,i,j | {f,"g, h, i, j"}
- f,g,h,i,j | {"f, g","h, i, j"}
- f,g,h,i,j | {"f, g, h","i, j"}
- f,g,h,i,j | {"f, g, h, i",j}
- f,g,h,i,j | {"f, g, h, j",i}
- f,g,h,i,j | {"f, g, i","h, j"}
- f,g,h,i,j | {"f, g, i, j",h}
- f,g,h,i,j | {"f, g, j","h, i"}
- f,g,h,i,j | {"f, h","g, i, j"}
- f,g,h,i,j | {"f, h, i","g, j"}
- f,g,h,i,j | {"f, h, i, j",g}
- f,g,h,i,j | {"f, h, j","g, i"}
- f,g,h,i,j | {"f, i","g, h, j"}
- f,g,h,i,j | {"f, i, j","g, h"}
- f,g,h,i,j | {"f, j","g, h, i"}
- f,g,h,i,j | {g,"f, h, i, j"}
- f,g,h,i,j | {"g, h","f, i, j"}
- f,g,h,i,j | {"g, h, i","f, j"}
- f,g,h,i,j | {"g, h, j","f, i"}
- f,g,h,i,j | {"g, i","f, h, j"}
- f,g,h,i,j | {"g, i, j","f, h"}
- f,g,h,i,j | {"g, j","f, h, i"}
- f,g,h,i,j | {h,"f, g, i, j"}
- f,g,h,i,j | {"h, i","f, g, j"}
- f,g,h,i,j | {"h, i, j","f, g"}
- f,g,h,i,j | {"h, j","f, g, i"}
- f,g,h,i,j | {i,"f, g, h, j"}
- f,g,h,i,j | {"i, j","f, g, h"}
- f,g,h,i,j | {j,"f, g, h, i"}
- k,l,m     | {k,"l, m"}
- k,l,m     | {"k, l",m}
- k,l,m     | {"k, m",l}
- k,l,m     | {l,"k, m"}
- k,l,m     | {m,"k, l"}
-(40 rows)
-
-DROP TABLE IF EXISTS foo;
-NOTICE:  table "foo" does not exist, skipping
-CREATE TABLE foo AS SELECT rule, combination(rule) from rules distributed by (rule);
--- UDT as argument/return type of set returning UDF
-CREATE TYPE r_type as (a int, b text);
-CREATE FUNCTION f1(x r_type) returns setof text as $$ SELECT $1.b from generate_series(1, $1.a) $$ language sql;
-CREATE FUNCTION f2(x int) returns setof r_type as $$ SELECT i, 'hello'::text from generate_series(1, $1) i $$ language sql;
-CREATE FUNCTION f3(x r_type) returns setof r_type as $$ SELECT $1 from generate_series(1, $1.a) $$ language sql;
-SELECT f1(row(2, 'hello'));
-  f1   
--------
- hello
- hello
-(2 rows)
-
-SELECT f2(2);
-    f2     
------------
- (1,hello)
- (2,hello)
-(2 rows)
-
-SELECT f3(row(2,'hello'));
-    f3     
------------
- (2,hello)
- (2,hello)
-(2 rows)
-
-SELECT * FROM f1(row(2,'hello'));
-  f1   
--------
- hello
- hello
-(2 rows)
-
-SELECT * FROM f2(2);
- a |   b   
----+-------
- 1 | hello
- 2 | hello
-(2 rows)
-
-SELECT * FROM f3(row(2,'hello'));
- a |   b   
----+-------
- 2 | hello
- 2 | hello
-(2 rows)
-
-CREATE TABLE t1 as SELECT i from generate_series(1,5) i distributed by (i);
-SELECT i, f1(row(i, 'hello')) from t1;
- i |  f1   
----+-------
- 2 | hello
- 2 | hello
- 4 | hello
- 4 | hello
- 4 | hello
- 4 | hello
- 1 | hello
- 3 | hello
- 3 | hello
- 3 | hello
- 5 | hello
- 5 | hello
- 5 | hello
- 5 | hello
- 5 | hello
-(15 rows)
-
-SELECT i, f2(i) from t1;
- i |    f2     
----+-----------
- 2 | (1,hello)
- 2 | (2,hello)
- 4 | (1,hello)
- 4 | (2,hello)
- 4 | (3,hello)
- 4 | (4,hello)
- 1 | (1,hello)
- 3 | (1,hello)
- 3 | (2,hello)
- 3 | (3,hello)
- 5 | (1,hello)
- 5 | (2,hello)
- 5 | (3,hello)
- 5 | (4,hello)
- 5 | (5,hello)
-(15 rows)
-
-SELECT i, f3(row(i,'hello')) from t1;
- i |    f3     
----+-----------
- 2 | (2,hello)
- 2 | (2,hello)
- 4 | (4,hello)
- 4 | (4,hello)
- 4 | (4,hello)
- 4 | (4,hello)
- 1 | (1,hello)
- 3 | (3,hello)
- 3 | (3,hello)
- 3 | (3,hello)
- 5 | (5,hello)
- 5 | (5,hello)
- 5 | (5,hello)
- 5 | (5,hello)
- 5 | (5,hello)
-(15 rows)
-
-CREATE TABLE o1 as SELECT f1(row(i, 'hello')) from t1;
-NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'f1' 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 o2 as SELECT f2(i) from t1;
-NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named '' 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 o3 as SELECT f3(row(i,'hello')) from t1;
-NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named '' 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.
--- start_ignore
-drop schema set_functions cascade;
-NOTICE:  drop cascades to append only table o3
-NOTICE:  drop cascades to append only table o2
-NOTICE:  drop cascades to append only table o1
-NOTICE:  drop cascades to append only table t1
-NOTICE:  drop cascades to function f3(r_type)
-NOTICE:  drop cascades to function f2(integer)
-NOTICE:  drop cascades to function f1(r_type)
-NOTICE:  drop cascades to type r_type
-NOTICE:  drop cascades to append only table foo
-NOTICE:  drop cascades to append only table rules
-NOTICE:  drop cascades to function combination(text)
--- end_ignore

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/66f0bda1/src/test/regress/known_good_schedule
----------------------------------------------------------------------
diff --git a/src/test/regress/known_good_schedule b/src/test/regress/known_good_schedule
index eb105d8..3043e55 100755
--- a/src/test/regress/known_good_schedule
+++ b/src/test/regress/known_good_schedule
@@ -9,7 +9,6 @@ ignore: goh_vacuum
 ignore: goh_subselect
 ignore: goh_array
 ignore: goh_gphdfs
-test: goh_partition
 ignore: goh_dpe
 ignore: goh_gpic
 ignore: goh_eagerfree
@@ -63,8 +62,6 @@ ignore: geometry
 ignore: horology
 test: insert
 test: create_function_1
-test: function
-test: function_extensions
 test: subplan
 ignore: create_type
 test: create_table_test
@@ -129,7 +126,6 @@ ignore: plpgsql
 ignore: copy2
 test: temp
 ignore: domain
-test: set_functions
 ignore: rangefuncs
 ignore: table_functions
 ignore: rangefuncs_cdb
@@ -145,7 +141,7 @@ ignore: returning
 ignore: stats
 ignore: tablespace
 ignore: gpdiffcheck
-test: exttab1
+ignore: exttab1
 ignore: custom_format
 ignore: resource_queue
 ignore: gptokencheck
@@ -213,7 +209,7 @@ ignore: hdfs_filesystem
 ignore: co_disabled
 # HCatalog tests
 test: caqlinmem
-test: hcatalog_lookup
+ignore: hcatalog_lookup
 test: json_load
 test: external_oid
 test: validator_function

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/66f0bda1/src/test/regress/sql/function.sql
----------------------------------------------------------------------
diff --git a/src/test/regress/sql/function.sql b/src/test/regress/sql/function.sql
deleted file mode 100644
index a050b22..0000000
--- a/src/test/regress/sql/function.sql
+++ /dev/null
@@ -1,439 +0,0 @@
--- SETUP
-DROP TABLE IF EXISTS foo;
-CREATE TABLE foo AS SELECT * FROM generate_series(1, 10) x;
-CREATE FUNCTION f(x INT) RETURNS INT AS $$
-BEGIN
-RETURN x;
-END
-$$ LANGUAGE PLPGSQL;
-
-
-
--- DDL, CREATE FUNCTION
-CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT;
-SELECT proname FROM pg_proc WHERE proname = 'g';
-SELECT proname FROM gp_dist_random('pg_proc') WHERE proname = 'g';
-DROP FUNCTION g(int);
-
-
-
--- DDL, CREATE OR REPLACE FUNCTION
-CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT;
-SELECT proname FROM pg_proc WHERE proname = 'g';
-SELECT proname FROM gp_dist_random('pg_proc') WHERE proname = 'g';
-CREATE OR REPLACE FUNCTION g(x INT) RETURNS INT AS $$
-BEGIN
-RETURN (-1) * x;
-END
-$$ LANGUAGE PLPGSQL;
-SELECT proname, prosrc FROM pg_proc WHERE proname = 'g';
-SELECT proname, prosrc FROM gp_dist_random('pg_proc') WHERE proname = 'g';
-DROP FUNCTION g(int);
-
-
-
--- DDL, DROP FUNCTION
-CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT;
-DROP FUNCTION g(int);
-SELECT oid, proname FROM pg_proc WHERE proname = 'g';
-SELECT oid, proname FROM gp_dist_random('pg_proc') WHERE proname = 'g';
-
-
-
--- DDL, DROP FUNCTION, NEGATIVE
-DROP FUNCTION g(int);
-
-
-
--- DDL, CREATE FUNCTION, RECORD
-CREATE FUNCTION foo(int) RETURNS record AS 'SELECT * FROM foo WHERE x=$1' LANGUAGE SQL;
-SELECT foo(5);
-DROP FUNCTION foo(int);
-CREATE FUNCTION foo(int) RETURNS foo AS 'SELECT * FROM foo WHERE x=$1' LANGUAGE SQL;
-SELECT foo(5);
-DROP FUNCTION foo(int);
-
-
-
--- DDL, CREATE FUNCTION, SRF
-CREATE FUNCTION g(x setof int) RETURNS INT
-    AS $$ SELECT 1 $$ LANGUAGE SQL;
-DROP FUNCTION g(setof int);
-CREATE FUNCTION g() RETURNS setof INT
-    AS $$ SELECT 1 $$ LANGUAGE SQL;
-DROP FUNCTION g();
-
-
-
--- DDL, CREATE FUNCTION, TABLE, NEGATIVE
-CREATE FUNCTION g() RETURNS TABLE(x int)
-    AS $$ SELECT * FROM foo $$ LANGUAGE SQL;
-DROP FUNCTION g();
-CREATE FUNCTION g(anytable) RETURNS int
-    AS 'does_not_exist', 'does_not_exist' LANGUAGE C;
-
-
-
--- DDL, CREATE FUNCTION, SECURITY DEFINER
-CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE SECURITY DEFINER;
-DROP FUNCTION g(int);
-
-
--- DDL, ALTER FUNCTION
--- DDL, STRICT
-CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE;
-SELECT g(NULL);
-ALTER FUNCTION g(int) STRICT;
-SELECT g(NULL);
-DROP FUNCTION g(int);
-
-
-
--- DDL, ALTER FUNCTION, OWNER
-CREATE ROLE superuser SUPERUSER;
-CREATE ROLE u1;
-SET ROLE superuser;
-CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE;
-SELECT a.rolname FROM pg_proc p, pg_authid a where p.proowner = a.oid and proname = 'g';
-ALTER FUNCTION g(int) OWNER TO u1;
-SELECT a.rolname FROM pg_proc p, pg_authid a where p.proowner = a.oid and proname = 'g';
-DROP FUNCTION g(int);
-RESET ROLE;
-DROP ROLE u1;
-DROP ROLE superuser;
-
-
-
--- DDL, ALTER FUNCTION, RENAME
-CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE;
-SELECT g(0);
-ALTER FUNCTION g(int) RENAME TO h;
-SELECT h(0);
-DROP FUNCTION h(int);
-
-
-
--- DDL, ALTER FUNCTION, SET SCHEMA
-CREATE SCHEMA bar;
-CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE;
-SELECT g(0);
-ALTER FUNCTION g(int) SET SCHEMA bar;
-SELECT bar.g(0);
-DROP SCHEMA bar CASCADE;
-
-
-
--- DDL, ALTER FUNCTION, SECURITY DEFINER
-CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE;
-ALTER FUNCTION g(int) SECURITY DEFINER;
-DROP FUNCTION g(int); 
-
-
-
--- DCL, GRANT/REVOKE
--- GRANT { EXECUTE | ALL [ PRIVILEGES ] }
---    ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
---    TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]
--- REVOKE [ GRANT OPTION FOR ]
---    { EXECUTE | ALL [ PRIVILEGES ] }
---    ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...]
---    FROM { username | GROUP groupname | PUBLIC } [, ...]
---    [ CASCADE | RESTRICT ]
-
--- DCL, GRANT/REVOKE, EXECUTE
-CREATE ROLE superuser SUPERUSER;
-SET ROLE superuser;
-CREATE ROLE u1;
-GRANT SELECT ON TABLE foo TO u1;
-CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT;
-SELECT proacl FROM pg_proc where proname = 'g';
-REVOKE ALL ON FUNCTION g(int) FROM PUBLIC;
-SELECT proacl FROM pg_proc where proname = 'g';
-SELECT g(1);
-SELECT count(g(x)) FROM foo;
-SET ROLE u1;
-SELECT g(1);
-SELECT count(g(x)) FROM foo;
-SET ROLE superuser;
-GRANT EXECUTE ON FUNCTION g(int) TO u1;
-SELECT proacl FROM pg_proc where proname = 'g';
-SET ROLE u1;
-SELECT g(1);
-SELECT count(g(x)) FROM foo;
-SET ROLE superuser;
-REVOKE EXECUTE ON FUNCTION g(int) FROM u1;
-SELECT proacl FROM pg_proc where proname = 'g';
-SET ROLE u1;
-SELECT g(1);
-SELECT count(g(x)) FROM foo;
-RESET ROLE;
-DROP FUNCTION g(int);
-REVOKE SELECT ON TABLE foo FROM u1;
-DROP ROLE u1;
-DROP ROLE superuser;
-
-
-
--- DCL, GRANT/REVOKE, PUBLIC
-CREATE ROLE superuser SUPERUSER;
-SET ROLE superuser;
-CREATE ROLE u1;
-GRANT SELECT ON TABLE foo TO u1;
-CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT;
-SELECT proacl FROM pg_proc where proname = 'g';
-REVOKE ALL ON FUNCTION g(int) FROM PUBLIC;
-SELECT proacl FROM pg_proc where proname = 'g';
-SELECT g(1);
-SELECT count(g(x)) FROM foo;
-SET ROLE u1;
-SELECT g(1);
-SELECT count(g(x)) FROM foo;
-SET ROLE superuser;
-GRANT EXECUTE ON FUNCTION g(int) TO PUBLIC;
-SELECT proacl FROM pg_proc where proname = 'g';
-SET ROLE u1;
-SELECT g(1);
-SELECT count(g(x)) FROM foo;
-SET ROLE superuser;
-REVOKE EXECUTE ON FUNCTION g(int) FROM PUBLIC;
-SELECT proacl FROM pg_proc where proname = 'g';
-SET ROLE u1;
-SELECT g(1);
-SELECT count(g(x)) FROM foo;
-RESET ROLE;
-DROP FUNCTION g(int);
-REVOKE SELECT ON TABLE foo FROM u1;
-DROP ROLE u1;
-DROP ROLE superuser;
-
-
-
--- DCL, GRANT/REVOKE, Groups
-CREATE ROLE superuser SUPERUSER;
-SET ROLE superuser;
-CREATE ROLE u1;
-CREATE ROLE u2 IN GROUP u1;
-GRANT SELECT ON TABLE foo TO u1;
-CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT;
-SELECT proacl FROM pg_proc where proname = 'g';
-REVOKE ALL ON FUNCTION g(int) FROM PUBLIC;
-SELECT proacl FROM pg_proc where proname = 'g';
-SELECT g(1);
-SELECT count(g(x)) FROM foo;
-SET ROLE u2;
-SELECT g(1);
-SELECT count(g(x)) FROM foo;
-SET ROLE superuser;
-GRANT EXECUTE ON FUNCTION g(int) TO u1;
-SELECT proacl FROM pg_proc where proname = 'g';
-SET ROLE u2;
-SELECT g(1);
-SELECT count(g(x)) FROM foo;
-SET ROLE superuser;
-REVOKE EXECUTE ON FUNCTION g(int) FROM u1;
-SELECT proacl FROM pg_proc where proname = 'g';
-SET ROLE u2;
-SELECT g(1);
-SELECT count(g(x)) FROM foo;
-RESET ROLE;
-DROP FUNCTION g(int);
-REVOKE SELECT ON TABLE foo FROM u1;
-DROP ROLE u1;
-DROP ROLE u2;
-DROP ROLE superuser;
-
-
-
--- DCL, GRANT/REVOKE, WITH GRANT OPTION
-CREATE ROLE superuser SUPERUSER;
-SET ROLE superuser;
-CREATE ROLE u1;
-CREATE ROLE u2;
-GRANT SELECT ON TABLE foo TO PUBLIC;
-CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT;
-SELECT proacl FROM pg_proc where proname = 'g';
-REVOKE ALL ON FUNCTION g(int) FROM PUBLIC;
-SELECT proacl FROM pg_proc where proname = 'g';
-SELECT g(1);
-SELECT count(g(x)) FROM foo;
-SET ROLE u2;
-SELECT g(1);
-SELECT count(g(x)) FROM foo;
-SET ROLE superuser;
-GRANT ALL ON FUNCTION g(int) TO u1 WITH GRANT OPTION;
-SET ROLE u1;
-GRANT ALL ON FUNCTION g(int) TO u2;
-SELECT proacl FROM pg_proc where proname = 'g';
-SET ROLE u1;
-SELECT g(1);
-SELECT count(g(x)) FROM foo;
-SET ROLE u2;
-SELECT g(1);
-SELECT count(g(x)) FROM foo;
-SET ROLE superuser;
-REVOKE ALL ON FUNCTION g(int) FROM u1 CASCADE;
-SELECT proacl FROM pg_proc where proname = 'g';
-SET ROLE u1;
-SELECT g(1);
-SELECT count(g(x)) FROM foo;
-SET ROLE u2;
-SELECT g(1);
-SELECT count(g(x)) FROM foo;
-RESET ROLE;
-DROP FUNCTION g(int);
-REVOKE SELECT ON TABLE foo FROM PUBLIC;
-DROP ROLE u1;
-DROP ROLE u2;
-DROP ROLE superuser;
-
-
-
--- DML, CaseExpr
-SELECT CASE WHEN x % 2 = 0 THEN f(x) ELSE 0 END FROM foo ORDER BY x;
-
-
-
--- DML, OpExpr
-SELECT f(x) + f(x) FROM foo ORDER BY x;
-SELECT f(x) + f(x) + f(x) FROM foo ORDER BY x;
-SELECT f(x) + f(x) - f(x) FROM foo ORDER BY x;
-
-
-
--- DML, FuncExpr
-CREATE FUNCTION g(x INT) RETURNS INT AS $$
-BEGIN
-RETURN x;
-END
-$$ LANGUAGE PLPGSQL;
-SELECT g(f(x)) FROM foo ORDER BY x;
-DROP FUNCTION g(int);
-
--- DML, BoolExpr
-SELECT x % 2 = 0 AND f(x) % 2 = 1 FROM foo ORDER BY x;
-
-
-
--- DML, DistinctExpr
-SELECT x IS DISTINCT FROM f(x) from foo ORDER BY x;
-
-
-
--- DML, PercentileExpr
-SELECT MEDIAN(f(x)) FROM foo;
-
-
-
--- DML, Complex Expression
-CREATE FUNCTION g(x INT) RETURNS INT AS $$
-BEGIN
-RETURN x;
-END
-$$ LANGUAGE PLPGSQL;
-SELECT CASE
-	WHEN x % 2 = 0 THEN g(g(x)) + g(g(x))
-	WHEN f(x) % 2 = 1 THEN g(g(x)) - g(g(x))
-	END FROM foo ORDER BY x;
-DROP FUNCTION g(int);
-
-
-
--- DML, Qual
-SELECT x FROM foo WHERE f(x) % 2 = 0 ORDER BY x;
-
-
-
--- DML, FROM
-SELECT * FROM f(5);
-
-
-
--- DML, Grouping
-SELECT DISTINCT f(x) FROM foo ORDER BY f(x);
-SELECT f(x) FROM foo GROUP BY f(x) ORDER BY f(x);
-
-
-
--- DML, Join
-SELECT a.x FROM foo a, foo b WHERE f(a.x) = f(b.x) ORDER BY x;
-SELECT a.x FROM foo a JOIN foo b ON f(a.x) = f(b.x) ORDER BY x;
-
-
-
--- DML, Windowing
-SELECT avg(x) OVER (PARTITION BY f(x)) FROM foo ORDER BY x;
-
-
-
--- DML, CTE
-WITH t AS (SELECT x from foo)
-	SELECT f(x) from t ORDER BY x;
-
-
-
--- DML, InitPlan
-SELECT UNNEST(ARRAY(SELECT x FROM foo)) ORDER BY 1;
-SELECT UNNEST(ARRAY(SELECT f(1)));
-
-
-
--- PROPERTIES, VOLATILITY, IMMUTABLE
-CREATE FUNCTION g() RETURNS float AS 'SELECT random();' LANGUAGE SQL IMMUTABLE;
-SELECT COUNT(DISTINCT(g())) > 1 FROM foo;
-DROP FUNCTION g();
-
-
-
--- PROPERTIES, VOLATILITY, STABLE
-CREATE FUNCTION g() RETURNS float AS 'SELECT random();' LANGUAGE SQL STABLE;
-SELECT COUNT(DISTINCT(g())) > 1 FROM foo;
-DROP FUNCTION g();
-
-
-
--- PROPERTIES, VOLATILITY, VOLATILE
-CREATE FUNCTION g() RETURNS float AS 'SELECT random();' LANGUAGE SQL VOLATILE;
-SELECT COUNT(DISTINCT(g())) > 1 FROM foo;
-DROP FUNCTION g();
-
------------------
--- NEGATIVE TESTS
------------------
-SELECT h(1);
--- DML, InitPlan
-SELECT UNNEST(ARRAY(SELECT f(x) from foo));
-
--- LANGUAGES not yet supported
--- CREATE LANGUAGE plr;
--- CREATE LANGUAGE plpython;
--- CREATE LANGUAGE pljava;
--- CREATE LANGUAGE plperl;
-
--- NESTED FUNCTION
-CREATE FUNCTION inner(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE;
-CREATE FUNCTION outer(x INT) RETURNS INT AS $$
-BEGIN
-RETURN inner(x);
-END
-$$ LANGUAGE PLPGSQL;
-SELECT outer(0);
-SELECT outer(0) FROM foo;
-DROP FUNCTION outer(int);
-DROP FUNCTION inner(int);
-
-
-
--- TEARDOWN
-DROP TABLE foo;
-
-
-
--- HAWQ-510
-drop table if exists testEntryDB;
-create table testEntryDB(key int, value int) distributed randomly;
-insert into testEntryDB values(1, 0);
-select t2.key, t2.value
-from   (select key, value from testEntryDB where value = 0) as t1,
-       (select generate_series(1,2)::int as key, 0::int as value) as t2
-where  t1.value=t2.value;
-drop table testEntryDB;

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/66f0bda1/src/test/regress/sql/function_extensions.sql
----------------------------------------------------------------------
diff --git a/src/test/regress/sql/function_extensions.sql b/src/test/regress/sql/function_extensions.sql
deleted file mode 100644
index d3d2abc..0000000
--- a/src/test/regress/sql/function_extensions.sql
+++ /dev/null
@@ -1,123 +0,0 @@
--- -----------------------------------------------------------------
--- Test extensions to functions (MPP-16060)
--- 	1. data access indicators
--- -----------------------------------------------------------------
-
--- test prodataaccess
-create function func1(int, int) returns int as
-$$
-  select $1 + $2;
-$$ language sql immutable contains sql;
-
--- check prodataaccess column in pg_proc
-select proname, prodataaccess from pg_proc where proname = 'func1';
-
--- check prodataaccess in pg_attribute
-select relname, attname, attlen from pg_class c, pg_attribute
-where attname = 'prodataaccess' and attrelid = c.oid and c.relname = 'pg_proc';
-
-create function func2(a anyelement, b anyelement, flag bool)
-returns anyelement as
-$$
-  select $1 + $2;
-$$ language sql reads sql data;
-
--- check prodataaccess column in pg_proc
-select proname, prodataaccess from pg_proc where proname = 'func2';
-
-create function func3() returns oid as
-$$
-  select oid from pg_class where relname = 'pg_type';
-$$ language sql modifies sql data volatile;
-
--- check prodataaccess column in pg_proc
-select proname, prodataaccess from pg_proc where proname = 'func3';
-
--- check default value of prodataaccess
-drop function func1(int, int);
-create function func1(int, int) returns varchar as $$
-declare
-	v_name varchar(20) DEFAULT 'zzzzz';
-begin
-	select relname from pg_class into v_name where oid=$1;
-	return v_name;
-end;
-$$ language plpgsql;
-
-select proname, proargnames, prodataaccess from pg_proc where proname = 'func1';
-
-create function func4(int, int) returns int as
-$$
-  select $1 + $2;
-$$ language sql;
-
--- check prodataaccess column
-select proname, proargnames, prodataaccess from pg_proc where proname = 'func4';
-
--- change prodataaccess option
-create or replace function func4(int, int) returns int as
-$$
-  select $1 + $2;
-$$ language sql modifies sql data;
-
-select proname, proargnames, prodataaccess from pg_proc where proname = 'func4';
-
--- upper case language name
-create or replace function func5(int) returns int as
-$$
-  select $1;
-$$ language "SQL";
-
--- check prodataaccess column
-select proname, proargnames, prodataaccess from pg_proc where proname = 'func5';
-
--- alter function with data access
-alter function func5(int) reads sql data;
-
--- check prodataaccess column
-select proname, proargnames, prodataaccess from pg_proc where proname = 'func5';
-
--- alter function with data access
-alter function func5(int) modifies sql data;
-
--- check prodataaccess column
-select proname, proargnames, prodataaccess from pg_proc where proname = 'func5';
-
--- alter function with data access
-alter function func5(int) no sql;
-
--- alter function with data access
-alter function func5(int) volatile contains sql;
-
-alter function func5(int) immutable reads sql data;
-alter function func5(int) immutable modifies sql data;
-
--- data_access indicators for plpgsql
-drop function func1(int, int);
-create or replace function func1(int, int) returns varchar as $$
-declare
-	v_name varchar(20) DEFAULT 'zzzzz';
-begin
-	select relname from pg_class into v_name where oid=$1;
-	return v_name;
-end;
-$$ language plpgsql reads sql data;
-
-select proname, proargnames, prodataaccess from pg_proc where proname = 'func1';
-
--- check conflicts
-drop function func1(int, int);
-create function func1(int, int) returns int as
-$$
-  select $1 + $2;
-$$ language sql immutable no sql;
-
-create function func1(int, int) returns int as
-$$
-  select $1 + $2;
-$$ language sql immutable reads sql data;
-
-drop function func2(anyelement, anyelement, bool);
-drop function func3();
-drop function func4(int, int);
-drop function func5(int);

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/66f0bda1/src/test/regress/sql/goh_partition.sql
----------------------------------------------------------------------
diff --git a/src/test/regress/sql/goh_partition.sql b/src/test/regress/sql/goh_partition.sql
deleted file mode 100644
index 4c04bd8..0000000
--- a/src/test/regress/sql/goh_partition.sql
+++ /dev/null
@@ -1,1297 +0,0 @@
-set enable_partition_rules = false;
-set gp_enable_hash_partitioned_tables = true;
-
--- missing subpartition by
-create table ggg (a char(1), b char(2), d char(3))
-distributed by (a)
-partition by hash (b)
-(
-partition aa (subpartition cc, subpartition dd),
-partition bb (subpartition cc, subpartition dd)
-);
-
--- missing subpartition spec
-create table ggg (a char(1), b char(2), d char(3))
-distributed by (a)
-partition by hash (b)
-subpartition by hash (d) 
-(
-partition aa ,
-partition bb 
-);
-
--- subpart spec conflict
-create table ggg (a char(1), b char(2), d char(3))
-distributed by (a)
-partition by hash (b) 
-subpartition by hash (d) subpartition template (subpartition jjj)
-(
-partition aa (subpartition cc, subpartition dd),
-partition bb (subpartition cc, subpartition dd)
-);
-
--- missing subpartition by
-create table ggg (a char(1), b char(2), d char(3))
-distributed by (a)
-partition by hash (b)
-subpartition by hash (d)
-(
-partition aa (subpartition cc, subpartition dd (subpartition iii)),
-partition bb (subpartition cc, subpartition dd)
-);
-
--- Test column lookup works
-create table ggg (a char(1), b char(2), d char(3))
-distributed by (a)
-partition by hash(doesnotexist)
-partitions 3;
-
-create table ggg (a char(1), b char(2), d char(3))
-distributed by (a)
-partition by hash(b)
-partitions 3
-subpartition by list(alsodoesntexist)
-subpartition template (
-subpartition aa values(1)
-);
-
--- will not work since sql has no subpartition templates
-create table ggg (a char(1), b char(2), d char(3))
-distributed by (a)
-partition by hash (b)
-subpartition by hash (d) 
-(
-partition aa (subpartition cc, subpartition dd),
-partition bb (subpartition cc, subpartition dd)
-);
-
-drop table if exists ggg cascade;
-
--- disable hash partitions
-set gp_enable_hash_partitioned_tables = false;
-
-create table ggg (a char(1), b char(2), d char(3))
-distributed by (a)
-partition by hash (b)
-subpartition by hash (d) 
-(
-partition aa (subpartition cc, subpartition dd),
-partition bb (subpartition cc, subpartition dd)
-);
-
-drop table ggg cascade;
-
-set gp_enable_hash_partitioned_tables = true;
-
--- should work
-create table ggg (a char(1), b char(2), d char(3), e int)
-distributed by (a)
-partition by hash (b)
-subpartition by hash (d) 
-subpartition template ( 
-subpartition cc,
-subpartition dd
-), 
-subpartition by hash (e) 
-subpartition template ( 
-subpartition ee,
-subpartition ff
-) 
-(
-partition aa,
-partition bb
-);
-
-drop table ggg cascade;
-
--- should not work since the first-level subpartitioning has no template
-create table ggg (a char(1), b char(2), d char(3), e int)
-distributed by (a)
-partition by hash (b)
-subpartition by hash (d),
-subpartition by hash (e)
-subpartition template ( 
-subpartition ee,
-subpartition ff
-) 
-(
-partition aa (subpartition cc, subpartition dd),
-partition bb (subpartition cc, subpartition dd)
-);
-
-drop table if exists ggg cascade;
-
--- doesn't work because cannot have nested declaration in template
-create table ggg (a char(1), b char(2), d char(3), e int)
-distributed by (a)
-partition by hash (b)
-subpartition by hash (d) 
-subpartition template ( 
-subpartition cc (subpartition ee, subpartition ff),
-subpartition dd (subpartition ee, subpartition ff)
-), 
-subpartition by hash (e) 
-(
-partition aa,
-partition bb
-);
-
-drop table ggg cascade;
-
---ERROR: Missing boundary specification in partition 'aa' of type LIST 
-create table fff (a char(1), b char(2), d char(3)) distributed by
-(a) partition by list (b) (partition aa ); 
-
-
--- ERROR: Invalid use of RANGE boundary specification in partition
---   number 1 of type LIST
-create table fff (a char(1), b char(2), d char(3)) distributed by (a)
-partition by list (b) (start ('a') );
-
-
--- should work
-create table fff (a char(1), b char(2), d char(3)) distributed by (a)
-partition by list (b) (partition aa values ('2'));
-
-drop table fff cascade;
-
--- Invalid use of RANGE boundary specification in partition "cc" of 
--- type HASH (at depth 2) & subpartition at one level has no template
-create table ggg (a char(1), b char(2), d char(3), e int) distributed by (a)
-partition by hash (b) subpartition by hash (d),
-subpartition by hash (e) 
-subpartition template ( subpartition ee, subpartition ff ) (
-partition aa (subpartition cc, subpartition dd), partition bb
-(subpartition cc start ('a') , subpartition dd) );
-
--- this is subtly wrong -- it defines 4 partitions
--- the problem is the comma before "end", which causes us to
--- generate 2 anonymous partitions.
--- This is an error: 
--- ERROR:  invalid use of mixed named and unnamed RANGE boundary specifications
-create table ggg (a char(1), b int, d char(3))
-distributed by (a)
-partition by range (b)
-(
-partition aa start ('2007'), end ('2008'),
-partition bb start ('2008'), end ('2009')
-);
-
-create table ggg (a char(1), b int)
-distributed by (a)
-partition by range(b)
-(
-partition aa start ('2007'), end ('2008')
-);
-
-drop table ggg cascade;
-
-create table ggg (a char(1), b date, d char(3))
-distributed by (a)
-partition by range (b)
-(
-partition aa start (date '2007-01-01') end (date '2008-01-01'),
-partition bb start (date '2008-01-01') end (date '2009-01-01')
-);
-
-
-drop table ggg cascade;
-
--- don't allow nonconstant expressions, even simple ones...
-create table ggg (a char(1), b numeric, d numeric)
-distributed by (a)
-partition by range (b,d)
-(
-partition aa start (2007,1) end (2008,2+2),
-partition bb start (2008,2) end (2009,3)
-);
-
--- composite key
-create table ggg (a char(1), b numeric, d numeric)
-distributed by (a)
-partition by range (b,d)
-(
-partition aa start (2007,1) end (2008,2),
-partition bb start (2008,2) end (2009,3)
-);
-
--- demo starts here
-
--- nested subpartitions
-create table ggg
- (a char(1),   b date,
-  d char(3),  e numeric,
-  f numeric,  g numeric,
-  h numeric)
-distributed by (a)
-partition by hash(b)
-partitions 2
-subpartition by hash(d)
-subpartitions 2,
-subpartition by hash(e) subpartitions 2,
-subpartition by hash(f) subpartitions 2,
-subpartition by hash(g) subpartitions 2,
-subpartition by hash(h) subpartitions 2;
-
-drop table ggg cascade;
-
-
--- named, inline subpartitions
-create table ggg (a char(1), b char(2), d char(3))
-distributed by (a)
-partition by hash (b)
-subpartition by hash (d) 
-(
-partition aa (subpartition cc, subpartition dd),
-partition bb (subpartition cc, subpartition dd)
-);
-
-drop table ggg cascade;
-
-
--- subpartitions with templates
-create table ggg (a char(1), b char(2), d char(3), e numeric)
-distributed by (a)
-partition by hash (b)
-subpartition by hash (d) 
-subpartition template ( 
-subpartition cc,
-subpartition dd
-), 
-subpartition by hash (e) 
-subpartition template ( 
-subpartition ee,
-subpartition ff
-) 
-(
-partition aa,
-partition bb
-);
-
-drop table ggg cascade;
-
-
--- mixed inline subpartition declarations with templates
-create table ggg (a char(1), b char(2), d char(3), e numeric)
-distributed by (a)
-partition by hash (b)
-subpartition by hash (d) , 
-subpartition by hash (e) 
-subpartition template ( 
-subpartition ee,
-subpartition ff
-) 
-(
-partition aa (subpartition cc, subpartition dd),
-partition bb (subpartition cc, subpartition dd)
-);
-
-drop table ggg cascade;
-
-
--- basic list partition
-create table ggg (a char(1), b char(2), d char(3))
-distributed by (a)
-partition by LIST (b)
-(
-partition aa values ('a', 'b', 'c', 'd'),
-partition bb values ('e', 'f', 'g')
-);
-
-insert into ggg values ('x', 'a');
-insert into ggg values ('x', 'b');
-insert into ggg values ('x', 'c');
-insert into ggg values ('x', 'd');
-insert into ggg values ('x', 'e');
-insert into ggg values ('x', 'f');
-insert into ggg values ('x', 'g');
-insert into ggg values ('x', 'a');
-insert into ggg values ('x', 'b');
-insert into ggg values ('x', 'c');
-insert into ggg values ('x', 'd');
-insert into ggg values ('x', 'e');
-insert into ggg values ('x', 'f');
-insert into ggg values ('x', 'g');
-
-select * from ggg order by 1, 2;
-
--- ok
-select * from ggg_1_prt_aa order by 1, 2;
-select * from ggg_1_prt_bb order by 1, 2;
-
-drop table ggg cascade;
-
--- documentation example - partition by list and range
-CREATE TABLE rank (id int, rank int, year date, gender 
-char(1)) DISTRIBUTED BY (id, gender, year)
-partition by list (gender)
-subpartition by range (year)
-subpartition template (
-start (date '2001-01-01'),
-start (date '2002-01-01'),
-start (date '2003-01-01'),
-start (date '2004-01-01'),
-start (date '2005-01-01')
-)
-(
-  partition boys values ('M'),
-  partition girls values ('F')
-);
-
-insert into rank values (1, 1, date '2001-01-15', 'M');
-insert into rank values (2, 1, date '2002-02-15', 'M');
-insert into rank values (3, 1, date '2003-03-15', 'M');
-insert into rank values (4, 1, date '2004-04-15', 'M');
-insert into rank values (5, 1, date '2005-05-15', 'M');
-insert into rank values (6, 1, date '2001-01-15', 'F');
-insert into rank values (7, 1, date '2002-02-15', 'F');
-insert into rank values (8, 1, date '2003-03-15', 'F');
-insert into rank values (9, 1, date '2004-04-15', 'F');
-insert into rank values (10, 1, date '2005-05-15', 'F');
-
-select * from rank order by 1, 2, 3, 4;
-select * from rank_1_prt_boys order by 1, 2, 3, 4;
-select * from rank_1_prt_girls order by 1, 2, 3, 4;
-select * from rank_1_prt_girls_2_prt_1 order by 1, 2, 3, 4;
-select * from rank_1_prt_girls_2_prt_2 order by 1, 2, 3, 4;
-
-
-drop table rank cascade;
-
-
-
--- range list hash combo, but subpartitions has no templates
-create table ggg (a char(1), b date, d char(3), e numeric)
-distributed by (a)
-partition by range (b)
-subpartition by list(d),
-subpartition by hash(e) subpartitions 3
-(
-partition aa 
-start  (date '2007-01-01') 
-end (date '2008-01-01') 
-       (subpartition dd values ('1', '2', '3'), 
-	    subpartition ee values ('4', '5', '6')),
-partition bb
-start  (date '2008-01-01') 
-end (date '2009-01-01') 
-       (subpartition dd values ('1', '2', '3'),
-	    subpartition ee values ('4', '5', '6'))
-);
-
-drop table ggg cascade;
-
-
--- demo ends here
-
-
--- LIST validation
-
--- duplicate partition name
-CREATE TABLE rank (id int, rank int, year date, gender
-char(1)) DISTRIBUTED BY (id, gender, year)
-partition by list (gender)
-(
-  partition boys values ('M'),
-  partition girls values ('a'),
-  partition girls values ('b'),
-  partition girls values ('c'),
-  partition girls values ('d'),
-  partition girls values ('e'),
-  partition bob values ('M')
-);
-
--- RANGE validation
-
--- legal if end of aa not inclusive
-create table ggg (a char(1), b date, d char(3)) 
-distributed by (a)
-partition by range (b)
-(
-partition aa start (date '2007-01-01') end (date '2008-01-01'),
-partition bb start (date '2008-01-01') end (date '2009-01-01') 
-every (interval '10 days'));
-
-drop table ggg cascade;
-
-
--- bad - legal if end of aa not inclusive
-create table ggg (a char(1), b date, d char(3)) 
-distributed by (a)
-partition by range (b)
-(
-partition aa start (date '2007-01-01') end (date '2008-01-01') inclusive,
-partition bb start (date '2008-01-01') end (date '2009-01-01') 
-every (interval '10 days'));
-
-drop table ggg cascade;
-
--- legal because start of bb not inclusive
-create table ggg (a char(1), b date, d char(3)) 
-distributed by (a)
-partition by range (b)
-(
-partition aa start (date '2007-01-01') end (date '2008-01-01') inclusive,
-partition bb start (date '2008-01-01') exclusive end (date '2009-01-01') 
-every (interval '10 days'));
-
-drop table ggg cascade;
-
--- legal if end of aa not inclusive
-create table ggg (a char(1), b date, d char(3)) 
-distributed by (a)
-partition by range (b)
-(
-partition bb start (date '2008-01-01') end (date '2009-01-01'),
-partition aa start (date '2007-01-01') end (date '2008-01-01')
-);
-
-drop table ggg cascade;
-
--- bad - legal if end of aa not inclusive
-create table ggg (a char(1), b date, d char(3)) 
-distributed by (a)
-partition by range (b)
-(
-partition bb start (date '2008-01-01') end (date '2009-01-01'),
-partition aa start (date '2007-01-01') end (date '2008-01-01') inclusive
-);
-
-drop table ggg cascade;
-
--- legal because start of bb not inclusive
-create table ggg (a char(1), b date, d char(3)) 
-distributed by (a)
-partition by range (b)
-(
-partition bb start (date '2008-01-01') exclusive end (date '2009-01-01'),
-partition aa start (date '2007-01-01') end (date '2008-01-01') inclusive
-);
-
-drop table ggg cascade;
-
--- validate aa - start greater than end
-create table ggg (a char(1), b date, d char(3)) 
-distributed by (a)
-partition by range (b)
-(
-partition bb start (date '2008-01-01') end (date '2009-01-01'),
-partition aa start (date '2007-01-01') end (date '2006-01-01')
-);
-
-drop table ggg cascade;
-
--- formerly we could not set end of first partition because next is before
--- but we can sort them now so this is legal.
-create table ggg (a char(1), b date, d char(3)) 
-distributed by (a)
-partition by range (b)
-(
-partition bb start (date '2008-01-01') ,
-partition aa start (date '2007-01-01') 
-);
-
-drop table ggg cascade;
-
--- test cross type coercion
--- int -> char(N)
-create table ggg (i int, a char(1))
-distributed by (i)
-partition by list(a)
-(partition aa values(1, 2));
-
-drop table ggg cascade;
-
--- int -> numeric
-create table ggg (i int, n numeric(20, 2))
-distributed by (i)
-partition by list(n)
-(partition aa values(1.22, 4.1));
-drop table ggg cascade;
-
--- EVERY
-
---  the documentation example, rewritten with EVERY in a template
-CREATE TABLE rank (id int,
-rank int, year date, gender char(1))
-DISTRIBUTED BY (id, gender, year)
-partition by list (gender)
-subpartition by range (year)
-subpartition template (
-start (date '2001-01-01')
-end (date '2006-01-01') every (interval '1 year')) (
-partition boys values ('M'),
-partition girls values ('F')
-);
-
-
-insert into rank values (1, 1, date '2001-01-15', 'M');
-insert into rank values (2, 1, date '2002-02-15', 'M');
-insert into rank values (3, 1, date '2003-03-15', 'M');
-insert into rank values (4, 1, date '2004-04-15', 'M');
-insert into rank values (5, 1, date '2005-05-15', 'M');
-insert into rank values (6, 1, date '2001-01-15', 'F');
-insert into rank values (7, 1, date '2002-02-15', 'F');
-insert into rank values (8, 1, date '2003-03-15', 'F');
-insert into rank values (9, 1, date '2004-04-15', 'F');
-insert into rank values (10, 1, date '2005-05-15', 'F');
-
-
-select * from rank order by 1, 2, 3, 4;
-select * from rank_1_prt_boys order by 1, 2, 3, 4;
-select * from rank_1_prt_girls order by 1, 2, 3, 4;
-select * from rank_1_prt_girls_2_prt_1 order by 1, 2, 3, 4;
-select * from rank_1_prt_girls_2_prt_2 order by 1, 2, 3, 4;
-
-drop table rank cascade;
-
--- integer ranges work too
-create table ggg (id integer, a integer)
-distributed by (id)
-partition by range (a)
-(start (1) end (10) every (1));
-
-insert into ggg values (1, 1);
-insert into ggg values (2, 2);
-insert into ggg values (3, 3);
-insert into ggg values (4, 4);
-insert into ggg values (5, 5);
-insert into ggg values (6, 6);
-insert into ggg values (7, 7);
-insert into ggg values (8, 8);
-insert into ggg values (9, 9);
-insert into ggg values (10, 10);
-
-select * from ggg order by 1, 2;
-
-select * from ggg_1_prt_1 order by 1, 2;
-select * from ggg_1_prt_2 order by 1, 2;
-select * from ggg_1_prt_3 order by 1, 2;
-select * from ggg_1_prt_4 order by 1, 2;
-
-drop table ggg cascade;
-
--- hash tests
-
-create table ggg (a char(1), b varchar(2), d varchar(2))
-distributed by (a)
-partition by hash(b)
-partitions 3
-(partition a, partition b, partition c);
-
-insert into ggg values (1,1,1);
-insert into ggg values (2,2,1);
-insert into ggg values (1,3,1);
-insert into ggg values (2,2,3);
-insert into ggg values (1,4,5);
-insert into ggg values (2,2,4);
-insert into ggg values (1,5,6);
-insert into ggg values (2,7,3);
-insert into ggg values (1,'a','b');
-insert into ggg values (2,'c','c');
-
-select * from ggg order by 1, 2, 3;
-
---select * from ggg_1_prt_a order by 1, 2, 3;
---select * from ggg_1_prt_b order by 1, 2, 3;
---select * from ggg_1_prt_c order by 1, 2, 3;
-
-drop table ggg cascade;
-
--- use multiple cols
-create table ggg (a char(1), b varchar(2), d varchar(2))
-distributed by (a)
-partition by hash(b,d)
-partitions 3
-(partition a, partition b, partition c);
-
--- append only tests
-create table foz (i int, d date) with (appendonly = true) distributed by (i)
-partition by range (d) (start (date '2001-01-01') end (date '2005-01-01')
-every(interval '1 year'));
-insert into foz select i, '2001-01-01'::date + ('1 day'::interval * i) from
-generate_series(1, 1000) i;
-select count(*) from foz;
-select count(*) from foz_1_prt_1;
-
-select min(d), max(d) from foz;
-select min(d), max(d) from foz_1_prt_1;
-select min(d), max(d) from foz_1_prt_2;
-select min(d), max(d) from foz_1_prt_3;
-select min(d), max(d) from foz_1_prt_4;
-
-
-drop table foz cascade;
-
-
--- complain if create table as select (CTAS)
-
-CREATE TABLE rank1 (id int,
-rank int, year date, gender char(1));
-
-create table rank2 as select * from rank1
-DISTRIBUTED BY (id, gender, year)
-partition by list (gender)
-subpartition by range (year)
-subpartition template (
-start (date '2001-01-01')
-end (date '2006-01-01') every (interval '1 year')) (
-partition boys values ('M'),
-partition girls values ('F')
-);
-
--- like is ok
-
-create table rank2 (like rank1)
-DISTRIBUTED BY (id, gender, year)
-partition by list (gender)
-subpartition by range (year)
-subpartition template (
-start (date '2001-01-01')
-end (date '2006-01-01') every (interval '1 year')) (
-partition boys values ('M'),
-partition girls values ('F')
-);
-
-drop table rank1 cascade;
-drop table rank2 cascade;
-
-
--- alter table testing
-
-create table hhh (a char(1), b date, d char(3))
-distributed by (a)
-partition by range (b)
-(
-partition aa start (date '2007-01-01') end (date '2008-01-01') 
-    with (appendonly=true),
-partition bb start (date '2008-01-01') end (date '2009-01-01')
-    with (appendonly=true)
-);
-
--- already exists
-alter table hhh add partition aa;
-
--- no partition spec
-alter table hhh add partition cc;
-
--- overlaps
-alter table hhh add partition cc start ('2008-01-01') end ('2010-01-01');
-alter table hhh add partition cc end ('2008-01-01');
-
--- reversed (start > end)
-alter table hhh add partition cc start ('2010-01-01') end ('2009-01-01');
-
--- works
---alter table hhh add partition cc start ('2009-01-01') end ('2010-01-01');
-alter table hhh add partition cc end ('2010-01-01');
-
--- works - anonymous partition MPP-3350
-alter table hhh add partition end ('2010-02-01');
-
--- MPP-3607 - ADD PARTITION with open intervals
-create table no_end1 (aa int, bb int) partition by range (bb)
-(partition foo start(3));
-
--- fail overlap
-alter table no_end1 add partition baz end (4);
-
--- fail overlap (because prior partition has no end)
-alter table no_end1 add partition baz start (5);
-
--- ok (terminates on foo start)
-alter table no_end1 add partition baz start (2);
-
--- ok (because ends before baz start)
-alter table no_end1 add partition baz2 end (1);
-
-create table no_start1 (aa int, bb int) partition by range (bb)
-(partition foo end(3));
-
--- fail overlap (because next partition has no start)
-alter table no_start1 add partition baz start (2);
-
--- fail overlap (because next partition has no start)
-alter table no_start1 add partition baz end (1);
-
--- ok (starts on foo end)
-alter table no_start1 add partition baz end (4);
-
--- ok (because starts after baz end)
-alter table no_start1 add partition baz2 start (5);
-
-select tablename, partitionlevel, parentpartitiontablename,
-partitionname, partitionrank, partitionboundary from pg_partitions
-where tablename = 'no_start1' or tablename = 'no_end1' 
-order by tablename, partitionrank;
-
-drop table no_end1;
-drop table no_start1;
-
--- hash partitions cannot have default partitions
-create table jjj (aa int, bb int) 
-partition by hash(bb) 
-(partition j1, partition j2);
-
-alter table jjj add default partition;
-
-drop table jjj cascade;
-
--- default partitions cannot have boundary specifications
-create table jjj (aa int, bb date) 
-partition by range(bb) 
-(partition j1 end (date '2008-01-01'), 
-partition j2 end (date '2009-01-01'));
-
--- must have a name
-alter table jjj add default partition;
-alter table jjj add default partition for (rank(1));
--- cannot have boundary spec
-alter table jjj add default partition j3 end (date '2010-01-01');
-
-drop table jjj cascade;
-
--- only one default partition
-create table jjj (aa int, bb date) 
-partition by range(bb) 
-(partition j1 end (date '2008-01-01'), 
-partition j2 end (date '2009-01-01'),
-default partition j3);
-
-alter table jjj add default partition j3 ;
-alter table jjj add default partition j4 ;
-
--- cannot add if have default, must split
-alter table jjj add partition j5 end (date '2010-01-01');
-
-drop table jjj cascade;
-
-alter table hhh alter partition cc set tablespace foo_p;
-
-alter table hhh alter partition aa set tablespace foo_p;
-
-alter table hhh coalesce partition cc;
-
-alter table hhh coalesce partition aa;
-
-alter table hhh drop partition cc;
-
-alter table hhh drop partition cc cascade;
-
-alter table hhh drop partition cc restrict;
-
-alter table hhh drop partition if exists cc;
-
--- fail (mpp-3265)
-alter table hhh drop partition for (rank(0));
-alter table hhh drop partition for (rank(-55));
-alter table hhh drop partition for ('2001-01-01');
-
-
-create table hhh_r1 (a char(1), b date, d char(3))
-distributed by (a)
-partition by range (b)
-(
-partition aa start (date '2007-01-01') end (date '2008-01-01') 
-             every (interval '1 month')
-);
-
-create table hhh_l1 (a char(1), b date, d char(3))
-distributed by (a)
-partition by list (b)
-(
-partition aa values ('2007-01-01'),
-partition bb values ('2008-01-01'),
-partition cc values ('2009-01-01') 
-);
-
--- must have name or value for list partition
-alter table hhh_l1 drop partition;
-alter table hhh_l1 drop partition aa;
-alter table hhh_l1 drop partition for ('2008-01-01');
-
--- if not specified, drop first range partition...
-alter table hhh_r1 drop partition for ('2007-04-01');
-alter table hhh_r1 drop partition;
-alter table hhh_r1 drop partition;
-alter table hhh_r1 drop partition;
-alter table hhh_r1 drop partition;
-alter table hhh_r1 drop partition;
-
--- more add partition tests
-
--- start before first partition (fail because start equal end)
-alter table hhh_r1 add partition zaa start ('2007-07-01');
--- start before first partition (ok)
-alter table hhh_r1 add partition zaa start ('2007-06-01');
--- start > last (fail because start equal end)
-alter table hhh_r1 add partition bb start ('2008-01-01') end ('2008-01-01') ;
--- start > last (ok)
-alter table hhh_r1 add partition bb start ('2008-01-01') 
-end ('2008-02-01') inclusive;
--- start > last (fail because start == last end inclusive)
-alter table hhh_r1 add partition cc start ('2008-02-01') end ('2008-03-01') ;
--- start > last (ok [and leave a gap])
-alter table hhh_r1 add partition cc start ('2008-04-01') end ('2008-05-01') ;
--- overlap (fail)
-alter table hhh_r1 add partition dd start ('2008-01-01') end ('2008-05-01') ;
--- new partition in "gap" (ok)
-alter table hhh_r1 add partition dd start ('2008-03-01') end ('2008-04-01') ;
--- overlap all partitions (fail)
-alter table hhh_r1 add partition ee start ('2006-01-01') end ('2009-01-01') ;
--- start before first partition (fail because end in "gap" [and overlaps])
-alter table hhh_r1 add partition yaa start ('2007-05-01') end ('2007-07-01');
--- start before first partition (fail )
-alter table hhh_r1 add partition yaa start ('2007-05-01') 
-end ('2007-10-01') inclusive;
--- start before first partition (fail because end overlaps)
-alter table hhh_r1 add partition yaa start ('2007-05-01') 
-end ('2007-10-01') exclusive;
-
-drop table hhh_r1 cascade;
-drop table hhh_l1 cascade;
-
--- SPLIT tests
--- basic sanity tests. All should pass.
-create table k (i int) partition by range(i) (start(1) end(10) every(2), 
-default partition mydef);
-insert into k select i from generate_series(1, 100) i;
-alter table k split partition mydef at (20) into (partition mydef, 
-partition foo);
-drop table k;
-
-create table j (i int) partition by list(i) (partition a values(1, 2, 3, 4),
-partition b values(5, 6, 7, 8));
-insert into j select i from generate_series(1, 8) i;
-alter table j split partition for(1) at (2, 3) into (partition fa, partition
-fb);
-select * from j_1_prt_fa;
-select * from j_1_prt_fb;
-alter table j split partition for(5) at (6);
-select * from j;
--- should fail
-alter table j split partition for (1) at (100);
-drop table j;
-create table k (i int) partition by range(i) (start(1) end(10) every(2), 
-default partition mydef);
--- should fail
-alter table k split default partition start(30) end (300) into (partition mydef, partition mydef);
-alter table k split partition for(3) at (20);
-drop table k;
--- should work
-create table k (i int) partition by range(i) (start(1) end(10) every(2), 
-default partition mydef);
-insert into k select i from generate_series(1, 30) i;
-alter table k split default partition start(15) end(20) into
-(partition mydef, partition foo);
-select * from k_1_prt_foo;
-alter table k split default partition start(22) exclusive end(25) inclusive
-into (partition bar, partition mydef);
-select * from k_1_prt_bar;
-alter table k split partition bar at (23) into (partition baz, partition foz);
-select partitiontablename,partitionposition,partitionrangestart,
-       partitionrangeend from pg_partitions where tablename = 'k'
-	   order by partitionposition;
-drop table k;
--- Test errors for default handling
-create table k (i int) partition by range(i) (start(1) end(2), 
-default partition mydef);
-alter table k split partition mydef at (25) into (partition foo, partition
-mydef);
-drop table k;
--- check that when we split a default, the INTO clause must named the default
-create table k (i date) partition by range(i) (start('2008-01-01')
-end('2009-01-01') every(interval '1 month'), default partition default_part);
-alter table k split default partition start ('2009-01-01') end ('2009-02-01')
-into (partition aa, partition nodate);
-alter table k split default partition start ('2009-01-01') end ('2009-02-01')
-into (partition aa, partition default_part);
--- check that it works without INTO
-alter table k split default partition start ('2009-02-01') end ('2009-03-01');
-drop table k;
--- List too
-create table k (i int) partition by list(i) (partition a values(1, 2),
-partition b values(3, 4), default partition mydef);
-alter table k split partition mydef at (5) into (partition foo, partition bar);
-alter table k split partition mydef at (5) into (partition foo, partition mydef);
-alter table k split partition mydef at (10);
-drop table k;
-
--- For LIST, make sure that we reject AT() clauses which match all parameters
-create table j (i int) partition by list(i) (partition a values(1, 2, 3, 4),
- partition b values(5, 6, 7, 8));
-alter table j split partition for(1) at (1,2) into (partition fa, partition fb);
-alter table j split partition for(1) at (1,2) 
-into (partition f1a, partition f1b); -- This has partition rules that overlaps
-drop table j;
-
--- Check that we can split LIST partitions that have a default partition
-create table j (i int) partition by list(i) (partition a values(1, 2, 3, 4),
-partition b values(5, 6, 7, 8), default partition default_part);
-alter table j split partition for(1) at (1,2) into (partition f1a, partition
-f1b);
-drop table j;
--- Make sure range can too
-create table j (i int) partition by range(i) (partition a start(1) end(10),
-default partition default_part);
-alter table j split partition for(1) at (5) into (partition f1a, partition f1b);
-drop table j;
-
--- GPSQL-285 -- GPSQL-277
-create table pt_table (a int, b int, c int, d int) distributed by (a) partition by range(b) (default partition others, start(1) end(5) every(1));
-select partitionname, partitiontablename from pg_partitions where tablename='pt_table' order by partitionname;
-insert into pt_table values(1,1,1,1);
-insert into pt_table values(1,2,1,1);
-select * from pt_table_1_prt_2;
-select * from pt_table_1_prt_3;
-insert into pt_table_1_prt_2 values(1,1,2,2);
-select * from pt_table_1_prt_2 order by d;
-insert into pt_table_1_prt_others values(1,1,1,1);
-select * from pt_table order by b,d;
-select * from pt_table_1_prt_2 order by b,d;
-select * from pt_table_1_prt_others order by b,d;
-
-drop table pt_table;
-
--- GPSQL-278
--- GPSQL-278 - sanity
-drop table if exists pt_check;
-create table pt_check
-(
-distcol int,
-ptcol date,
-col1 text,
-CONSTRAINT distcol_chk CHECK (distcol > 0)
-)
-distributed by (distcol)
-partition by range (ptcol)
-(
-default partition defpt,
-start (date '2010-01-01') inclusive
-end (date '2010-12-31') inclusive
-every (interval '1 month')
-);
---Insert 2 records to partitioned table.
-INSERT INTO pt_check values (1, '2010-01-10'::date, 'part 1');
-INSERT INTO pt_check values (2, '2010-01-21'::date, 'part 2');
-select * from pt_check order by col1;
---Split partition '2010-01-10' into 2 parts (Jan 1-15 and Jan 16-31).
-ALTER TABLE pt_check SPLIT PARTITION FOR ('2010-01-01')
-AT ('2010-01-16')
-INTO (PARTITION jan1thru15, PARTITION jan16thru31);
--- Verify split result.
-Select * from pt_check_1_prt_jan1thru15 order by col1;
-Select * from pt_check_1_prt_jan16thru31 order by col1;
-
--- GPSQL-278 - default partitions
-drop table if exists pt_check;
-create table pt_check
-(
-distcol int,
-ptcol date,
-col1 text,
-CONSTRAINT distcol_chk CHECK (distcol > 0)
-)
-distributed by (distcol)
-partition by range (ptcol)
-(
-default partition defpt,
-start (date '2010-01-01') inclusive
-end (date '2010-12-31') inclusive
-every (interval '1 month')
-);
---Insert 2 records to partitioned table.
-INSERT INTO pt_check values (1, '2011-01-10'::date, 'part 1');
-INSERT INTO pt_check values (2, '2011-02-21'::date, 'part 2');
-select * from pt_check order by col1;
---Split default partition into 2 parts (Jan 2011 and default).
-ALTER TABLE pt_check SPLIT DEFAULT PARTITION
-	START ('2011-01-01') INCLUSIVE END ('2011-02-01') EXCLUSIVE
-	INTO (PARTITION jan2011, DEFAULT PARTITION);
--- Verify split result.
-select * from pt_check_1_prt_jan2011 order by col1;
-select * from pt_check_1_prt_defpt order by col1;
-
--- GPSQL-278 - default partitions
-drop table if exists pt_check;
-create table pt_check
-(
-distcol int,
-ptcol date,
-col1 text,
-CONSTRAINT distcol_chk CHECK (distcol > 0)
-)
-distributed by (distcol)
-partition by range (ptcol)
-(
-default partition defpt,
-start (date '2010-01-01') inclusive
-end (date '2010-12-31') inclusive
-every (interval '1 month')
-);
---Insert 2 records to partitioned table.
-INSERT INTO pt_check values (1, '2011-01-10'::date, 'part 1');
-INSERT INTO pt_check values (2, '2011-02-21'::date, 'part 2');
-select * from pt_check order by col1;
---Split default partition into 2 parts (Jan 2011 and default).
-ALTER TABLE pt_check SPLIT DEFAULT PARTITION
-	START ('2011-01-01') INCLUSIVE END ('2011-02-01') EXCLUSIVE
-	INTO (DEFAULT PARTITION, PARTITION jan2011);
--- Verify split result.
-select * from pt_check_1_prt_jan2011 order by col1;
-select * from pt_check_1_prt_defpt order by col1;
---- allow the creation of multi-level partition tables with templates
-CREATE TABLE sales (id int, date date, amt decimal(10,2), region text)
-DISTRIBUTED BY (id)
-PARTITION BY RANGE (date)
-              SUBPARTITION BY LIST (region)
-                SUBPARTITION TEMPLATE (
-                  SUBPARTITION usa VALUES ('usa'),
-                  SUBPARTITION europe VALUES ('europe'),
-                  SUBPARTITION asia VALUES ('asia'))
-( PARTITION Jan08 START (date '2008-01-01') INCLUSIVE , 
-  PARTITION Feb08 START (date '2008-02-01') INCLUSIVE ,
-  PARTITION Mar08 START (date '2008-03-01') INCLUSIVE ,
-  PARTITION Apr08 START (date '2008-04-01') INCLUSIVE ,
-  PARTITION May08 START (date '2008-05-01') INCLUSIVE ,
-  PARTITION Jun08 START (date '2008-06-01') INCLUSIVE ,
-  PARTITION Jul08 START (date '2008-07-01') INCLUSIVE ,
-  PARTITION Aug08 START (date '2008-08-01') INCLUSIVE ,
-  PARTITION Sep08 START (date '2008-09-01') INCLUSIVE ,
-  PARTITION Oct08 START (date '2008-10-01') INCLUSIVE ,
-  PARTITION Nov08 START (date '2008-11-01') INCLUSIVE ,
-  PARTITION Dec08 START (date '2008-12-01') INCLUSIVE 
-                  END (date '2009-01-01') EXCLUSIVE )
-;
-drop table sales;
---- allow the creation of multi-level partition tables with templates
-CREATE TABLE MPP10223pk
-(
-rnc VARCHAR(100),
-wbts VARCHAR(100),
-axc VARCHAR(100),
-vptt VARCHAR(100),
-vcct VARCHAR(100),
-agg_level CHAR(5),
-period_start_time TIMESTAMP WITH TIME ZONE,
-load_time TIMESTAMP WITH TIME ZONE DEFAULT now(),
-interval INTEGER,
-totcellsegress double precision,
-totcellsingress double precision
-)
- 
-DISTRIBUTED BY (rnc,wbts,axc,vptt,vcct)
- 
-PARTITION BY LIST (AGG_LEVEL)
-  SUBPARTITION BY RANGE (PERIOD_START_TIME)
-  SUBPARTITION TEMPLATE
-    (
-       SUBPARTITION P_FUTURE  START (date '2001-01-01') INCLUSIVE,
-       SUBPARTITION P_ENDPART START (date '2999-12-30') INCLUSIVE
-                              END (date '2999-12-31') EXCLUSIVE
-    )
-(
-  PARTITION min15part  VALUES ('15min'),
-  PARTITION hourpart   VALUES ('hour'),
-  PARTITION daypart    VALUES ('day')
-);
-
-drop table MPP10223pk;
-
---- disallow the creation of multi-level partition tables without templates
-CREATE TABLE MPP10223pk
-(
-rnc VARCHAR(100),
-wbts VARCHAR(100),
-axc VARCHAR(100),
-vptt VARCHAR(100),
-vcct VARCHAR(100),
-agg_level CHAR(5),
-period_start_time TIMESTAMP WITH TIME ZONE,
-load_time TIMESTAMP WITH TIME ZONE DEFAULT now(),
-interval INTEGER,
-totcellsegress double precision,
-totcellsingress double precision
-)
- 
-DISTRIBUTED BY (rnc,wbts,axc,vptt,vcct)
- 
-PARTITION BY LIST (AGG_LEVEL)
-  SUBPARTITION BY RANGE (PERIOD_START_TIME)
-(
-  PARTITION min15part  VALUES ('15min')
-    (
-       SUBPARTITION P_FUTURE  START (date '2001-01-01') INCLUSIVE,
-       SUBPARTITION P_ENDPART START (date '2999-12-30') INCLUSIVE
-                              END (date '2999-12-31') EXCLUSIVE
-    ),
-  PARTITION hourpart   VALUES ('hour')
-    (
-               SUBPARTITION P20100622 START (date '2010-06-22') INCLUSIVE,
-               SUBPARTITION P20100623 START (date '2010-06-23') INCLUSIVE,
-               SUBPARTITION P20100624 START (date '2010-06-24') INCLUSIVE,
-               SUBPARTITION P20100625 START (date '2010-06-25') INCLUSIVE,
-               SUBPARTITION P20100626 START (date '2010-06-26') INCLUSIVE,
-               SUBPARTITION P_FUTURE  START (date '2001-01-01') INCLUSIVE,
-               SUBPARTITION P_ENDPART START (date '2999-12-30') INCLUSIVE
-                                      END (date '2999-12-31') EXCLUSIVE
-    ),
-  PARTITION daypart    VALUES ('day')
-    (
-               SUBPARTITION P20100622 START (date '2010-06-22') INCLUSIVE,
-               SUBPARTITION P20100623 START (date '2010-06-23') INCLUSIVE,
-               SUBPARTITION P20100624 START (date '2010-06-24') INCLUSIVE,
-               SUBPARTITION P20100625 START (date '2010-06-25') INCLUSIVE,
-               SUBPARTITION P20100626 START (date '2010-06-26') INCLUSIVE,
-               SUBPARTITION P_FUTURE  START (date '2001-01-01') INCLUSIVE,
-               SUBPARTITION P_ENDPART START (date '2999-12-30') INCLUSIVE
-                                      END (date '2999-12-31') EXCLUSIVE
-    )
-);
-
-
---- disallow the creation of multi-level partition tables without templates
-CREATE TABLE rank3 (id int, rank int,
-year date, gender char(1),
-usstate char(2))
-DISTRIBUTED BY (id, gender, year, usstate)
-partition by list (gender)
-subpartition by range (year),
-subpartition by list (usstate)
-(
-  partition boys values ('M') 
-(
-subpartition jan01 start (date '2001-01-01') 
-(
-subpartition mass values ('MA'),
-subpartition cali values ('CA'),
-subpartition ohio values ('OH')
-),
-subpartition jan02 start (date '2002-01-01')
-(
-subpartition mass values ('MA'),
-subpartition cali values ('CA'),
-subpartition ohio values ('OH')
-),
-subpartition jan03 start (date '2003-01-01')
-(
-subpartition mass values ('MA'),
-subpartition cali values ('CA'),
-subpartition ohio values ('OH')
-),
-subpartition jan04 start (date '2004-01-01')
-(
-subpartition mass values ('MA'),
-subpartition cali values ('CA'),
-subpartition ohio values ('OH')
-),
-subpartition jan05 start (date '2005-01-01')
-(
-subpartition mass values ('MA'),
-subpartition cali values ('CA'),
-subpartition ohio values ('OH')
-)
-)
-,
-  partition girls values ('F')
-(
-subpartition jan01 start (date '2001-01-01')
-(
-subpartition mass values ('MA'),
-subpartition cali values ('CA'),
-subpartition ohio values ('OH')
-),
-subpartition jan02 start (date '2002-01-01')
-(
-subpartition mass values ('MA'),
-subpartition cali values ('CA'),
-subpartition ohio values ('OH')
-),
-subpartition jan03 start (date '2003-01-01')
-(
-subpartition mass values ('MA'),
-subpartition cali values ('CA'),
-subpartition ohio values ('OH')
-),
-subpartition jan04 start (date '2004-01-01')
-(
-subpartition mass values ('MA'),
-subpartition cali values ('CA'),
-subpartition ohio values ('OH')
-),
-subpartition jan05 start (date '2005-01-01')
-(
-subpartition mass values ('MA'),
-subpartition cali values ('CA'),
-subpartition ohio values ('OH')
-)
-)
-);
-
--- Tests for sort operator before insert with AO and PARQUET tables (HAWQ-404)
--- A GUC's value is set to less than the number of partitions in the example table, so that sort is activated.
-
-DROP TABLE IF EXISTS ch_sort_src, ch_sort_aodest, ch_sort_pqdest, ch_sort_aopqdest, ch_sort__pq_table;
-
-SET optimizer_parts_to_force_sort_on_insert = 5;
-
-CREATE TABLE ch_sort_src (id int, year int, month int, day int, region text)
-DISTRIBUTED BY (month); 
-INSERT INTO ch_sort_src select i, 2000 + i, i % 12, (2*i) % 30, i::text from generate_series(0, 99) i; 
-
--- AO partitioned table
-CREATE TABLE ch_sort_aodest (id int, year int, month int, day int, region text)
-WITH (APPENDONLY=TRUE)
-DISTRIBUTED BY (id)
-PARTITION BY RANGE (year)
-( 
-    START (2002) END (2010) EVERY (1),
-    DEFAULT PARTITION outlying_years
-);
-
--- PARQUET partitioned table
-CREATE TABLE ch_sort_pqdest (id int, year int, month int, day int, region text)
-WITH (APPENDONLY=TRUE, ORIENTATION = PARQUET)
-DISTRIBUTED BY (id)
-PARTITION BY RANGE (year)
-( 
-    START (2002) END (2010) EVERY (1),
-    DEFAULT PARTITION outlying_years
-);
-
--- AO/PARQUET mixed table
-CREATE TABLE ch_sort_aopqdest (id int, year int, month int, day int, region text)
-WITH (APPENDONLY=TRUE)
-DISTRIBUTED BY (id)
-PARTITION BY RANGE (year)
-( 
-    START (2002) END (2010) EVERY (1),
-    DEFAULT PARTITION outlying_years
-);
-
-CREATE TABLE ch_sort__pq_table (id int, year int, month int, day int, region text)
-WITH (APPENDONLY=TRUE, ORIENTATION = PARQUET)
-DISTRIBUTED BY (id);
-
-ALTER TABLE ch_sort_aopqdest
-EXCHANGE PARTITION FOR(2006)
-WITH TABLE ch_sort__pq_table;
-
-
--- Test that inserts work
-INSERT INTO ch_sort_aodest SELECT * FROM ch_sort_src;
-SELECT COUNT(*) FROM ch_sort_aodest;
-SELECT COUNT(*) FROM ch_sort_aodest_1_prt_6;
-SELECT COUNT(*) FROM ch_sort_aodest_1_prt_outlying_years;
-
-INSERT INTO ch_sort_pqdest SELECT * FROM ch_sort_src;
-SELECT COUNT(*) FROM ch_sort_pqdest;
-SELECT COUNT(*) FROM ch_sort_pqdest_1_prt_6;
-SELECT COUNT(*) FROM ch_sort_pqdest_1_prt_outlying_years;
-
-INSERT INTO ch_sort_aopqdest SELECT * FROM ch_sort_src;
-SELECT COUNT(*) FROM ch_sort_aopqdest;
-SELECT COUNT(*) FROM ch_sort_aopqdest_1_prt_6;
-SELECT COUNT(*) FROM ch_sort_aopqdest_1_prt_outlying_years;
-
-RESET optimizer_parts_to_force_sort_on_insert;

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/66f0bda1/src/test/regress/sql/set_functions.sql
----------------------------------------------------------------------
diff --git a/src/test/regress/sql/set_functions.sql b/src/test/regress/sql/set_functions.sql
deleted file mode 100644
index 95235b2..0000000
--- a/src/test/regress/sql/set_functions.sql
+++ /dev/null
@@ -1,104 +0,0 @@
--- start_ignore
-drop schema set_functions cascade;
--- end_ignore
-
-create schema set_functions;
-set search_path=set_functions;
-
-DROP LANGUAGE IF EXISTS plpythonu CASCADE;
-CREATE LANGUAGE plpythonu;
-
-CREATE TABLE foo2(fooid int, f2 int);
-INSERT INTO foo2 VALUES(1, 11);
-INSERT INTO foo2 VALUES(2, 22);
-INSERT INTO foo2 VALUES(1, 111);
-
-CREATE FUNCTION foot(int) returns setof foo2 as 'SELECT * FROM foo2 WHERE fooid = $1;' LANGUAGE SQL;
-select foot.fooid, foot.f2 from foot(sin(pi()/2)::int) ORDER BY 1,2;
-
-CREATE TABLE foo (fooid int, foosubid int, fooname text);
-INSERT INTO foo VALUES(1,1,'Joe');
-INSERT INTO foo VALUES(1,2,'Ed');
-INSERT INTO foo VALUES(2,1,'Mary');
-
-CREATE FUNCTION getfoo(int) RETURNS setof int AS 'SELECT fooid FROM foo WHERE fooid = $1;' LANGUAGE SQL;
-SELECT * FROM getfoo(1) AS t1;
-CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
-SELECT * FROM vw_getfoo;
-
-DROP VIEW vw_getfoo;
-DROP FUNCTION getfoo(int);
-DROP FUNCTION foot(int);
-DROP TABLE foo2;
-DROP TABLE foo;
-
--- setof as a paramater --
-CREATE TYPE numtype as (i int, j int);
-
-CREATE FUNCTION g_numtype(x setof numtype) RETURNS setof numtype AS $$ select $1; $$ LANGUAGE SQL;
-
-DROP FUNCTION g_numtype(x setof numtype);
-DROP TYPE numtype;
-
---
--- Set functions samples from Madlib
---
-create function combination(s text) returns setof text[] as $$
-x = s.split(',')
-
-def subset(myset, N):
-   left = []
-   right = []
-   for i in range(0, len(myset)):
-      if ((1 << i) & N) > 0:
-         left.append(myset[i])
-      else:
-         right.append(myset[i])
-   return (', '.join(left), ', '.join(right))
-
-for i in range(1, (1 << len(x)) - 2):
-   yield subset(x, i)
-$$ language plpythonu strict;
-
-select x[1] || ' => ' || x[2] from combination('a,b,c,d') x;
-
-CREATE TABLE rules(rule text) distributed by (rule);
-insert into rules values('a,b,c');
-insert into rules values('d,e');
-insert into rules values('f,g,h,i,j');
-insert into rules values('k,l,m');
-
-SELECT rule, combination(rule) from rules order by 1,2;
-
-DROP TABLE IF EXISTS foo;
-CREATE TABLE foo AS SELECT rule, combination(rule) from rules distributed by (rule);
-
-
--- UDT as argument/return type of set returning UDF
-CREATE TYPE r_type as (a int, b text);
-
-CREATE FUNCTION f1(x r_type) returns setof text as $$ SELECT $1.b from generate_series(1, $1.a) $$ language sql;
-CREATE FUNCTION f2(x int) returns setof r_type as $$ SELECT i, 'hello'::text from generate_series(1, $1) i $$ language sql;
-CREATE FUNCTION f3(x r_type) returns setof r_type as $$ SELECT $1 from generate_series(1, $1.a) $$ language sql;
-
-SELECT f1(row(2, 'hello'));
-SELECT f2(2);
-SELECT f3(row(2,'hello'));
-
-SELECT * FROM f1(row(2,'hello'));
-SELECT * FROM f2(2);
-SELECT * FROM f3(row(2,'hello'));
-
-CREATE TABLE t1 as SELECT i from generate_series(1,5) i distributed by (i);
-
-SELECT i, f1(row(i, 'hello')) from t1;
-SELECT i, f2(i) from t1;
-SELECT i, f3(row(i,'hello')) from t1;
-
-CREATE TABLE o1 as SELECT f1(row(i, 'hello')) from t1;
-CREATE TABLE o2 as SELECT f2(i) from t1;
-CREATE TABLE o3 as SELECT f3(row(i,'hello')) from t1;
-
--- start_ignore
-drop schema set_functions cascade;
--- end_ignore