Repository: incubator-hawq Updated Branches: refs/heads/master aaafcab84 -> 9833d128c
HAWQ-776. Refactor alter table checkinstall cases using new test framework Project: http://git-wip-us.apache.org/repos/asf/incubator-hawq/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-hawq/commit/9833d128 Tree: http://git-wip-us.apache.org/repos/asf/incubator-hawq/tree/9833d128 Diff: http://git-wip-us.apache.org/repos/asf/incubator-hawq/diff/9833d128 Branch: refs/heads/master Commit: 9833d128c9b8e38bbafe6a2476c57300110f76a9 Parents: aaafcab Author: YI JIN <[email protected]> Authored: Thu Jun 16 12:45:35 2016 +1000 Committer: YI JIN <[email protected]> Committed: Thu Jun 16 12:45:35 2016 +1000 ---------------------------------------------------------------------- .../ans/alter-table-addcol-insert-alltypes.ans | 34 +++ .../sql/alter-table-addcol-insert-alltypes.sql | 29 +++ src/test/feature/catalog/test_alter_table.cpp | 254 +++++++++++++++++++ src/test/regress/expected/gpsql_alter_table.out | 197 -------------- .../expected/gpsql_alter_table_optimizer.out | 202 --------------- src/test/regress/known_good_schedule | 1 - src/test/regress/sql/gpsql_alter_table.sql | 153 ----------- 7 files changed, 317 insertions(+), 553 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/9833d128/src/test/feature/catalog/ans/alter-table-addcol-insert-alltypes.ans ---------------------------------------------------------------------- diff --git a/src/test/feature/catalog/ans/alter-table-addcol-insert-alltypes.ans b/src/test/feature/catalog/ans/alter-table-addcol-insert-alltypes.ans new file mode 100644 index 0000000..e28d8f6 --- /dev/null +++ b/src/test/feature/catalog/ans/alter-table-addcol-insert-alltypes.ans @@ -0,0 +1,34 @@ +INSERT INTO tmp + (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u, v, w, x, y, z) + VALUES (4, + 'name', + 'text', + 4.1, + 4.1, + 2, + '(4.1,4.1,3.1,3.1)', + 'Mon May 1 00:30:30 1995', + 'c', + '{Mon May 1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}', + 314159, + '(1,1)', + '512', + '1 2 3 4 5 6 7 8', + 'magnetic disk', + '(1.1,1.1)', + '(4.1,4.1,3.1,3.1)', + '(0,2,4.1,4.1,3.1,3.1)', + '(4.1,4.1,3.1,3.1)', + '["epoch" "infinity"]', + 'epoch', + '01:00:10', + '{1.0,2.0,3.0,4.0}', + '{1.0,2.0,3.0,4.0}', + '{1,2,3,4}'); +INSERT 0 1 +SELECT * FROM tmp; + initial | a | b | c | d | e | f | g | h | i | j | k | l | m | n | p | q | r | s | t | u | v | w | x | y | z +---------+---+------+------+-----+-----+---+-----------------------+------------------------+---+------------------------------------------------------------------------------+--------+-------+-----+-----------------+---------------+-----------+-----------------------+-----------------------------+---------------------+---------------------------------------+---------------------+----------+-----------+-----------+----------- + | 4 | name | text | 4.1 | 4.1 | 2 | ((4.1,4.1),(3.1,3.1)) | 1995-05-01 00:30:30+10 | c | {"1995-05-01 00:30:30+10","1992-08-24 14:43:07+10","1970-01-01 10:00:00+10"} | 314159 | (1,1) | 512 | 1 2 3 4 5 6 7 8 | magnetic disk | (1.1,1.1) | [(4.1,4.1),(3.1,3.1)] | ((0,2),(4.1,4.1),(3.1,3.1)) | (4.1,4.1),(3.1,3.1) | ["1970-01-01 10:00:00+10" "infinity"] | 1970-01-01 00:00:00 | 01:00:10 | {1,2,3,4} | {1,2,3,4} | {1,2,3,4} +(1 row) + http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/9833d128/src/test/feature/catalog/sql/alter-table-addcol-insert-alltypes.sql ---------------------------------------------------------------------- diff --git a/src/test/feature/catalog/sql/alter-table-addcol-insert-alltypes.sql b/src/test/feature/catalog/sql/alter-table-addcol-insert-alltypes.sql new file mode 100644 index 0000000..be3280a --- /dev/null +++ b/src/test/feature/catalog/sql/alter-table-addcol-insert-alltypes.sql @@ -0,0 +1,29 @@ +INSERT INTO tmp + (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u, v, w, x, y, z) + VALUES (4, + 'name', + 'text', + 4.1, + 4.1, + 2, + '(4.1,4.1,3.1,3.1)', + 'Mon May 1 00:30:30 1995', + 'c', + '{Mon May 1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}', + 314159, + '(1,1)', + '512', + '1 2 3 4 5 6 7 8', + 'magnetic disk', + '(1.1,1.1)', + '(4.1,4.1,3.1,3.1)', + '(0,2,4.1,4.1,3.1,3.1)', + '(4.1,4.1,3.1,3.1)', + '["epoch" "infinity"]', + 'epoch', + '01:00:10', + '{1.0,2.0,3.0,4.0}', + '{1.0,2.0,3.0,4.0}', + '{1,2,3,4}'); + +SELECT * FROM tmp; http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/9833d128/src/test/feature/catalog/test_alter_table.cpp ---------------------------------------------------------------------- diff --git a/src/test/feature/catalog/test_alter_table.cpp b/src/test/feature/catalog/test_alter_table.cpp new file mode 100644 index 0000000..d88cc2a --- /dev/null +++ b/src/test/feature/catalog/test_alter_table.cpp @@ -0,0 +1,254 @@ +#include <pwd.h> +#include <sys/types.h> +#include <unistd.h> +#include <vector> +#include <stdio.h> +#include <stdlib.h> +#include <errno.h> +#include <iostream> +#include <string> + +#include "lib/sql_util.h" + +#include "gtest/gtest.h" + +class TestAlterTable : public ::testing::Test { + public: + TestAlterTable() {} + ~TestAlterTable() {} +}; + + +TEST_F(TestAlterTable, TestAlterTableAOColumnDefaultValue) { + hawq::test::SQLUtility util; + // prepare + util.execute("drop table if exists altable"); + util.execute("create table altable (a int, b text, c int)"); + util.execute("insert into altable " + "select i, i::text, i from generate_series(1,10) i"); + + // test add new column into an ao table without default value setting + util.execute("alter table altable add column y int", false); + std::string errstr = "ERROR: ADD COLUMN with no default value in " + "append-only tables is not yet supported."; + EXPECT_STREQ(errstr.c_str(), + util.getPSQL()->getLastResult().substr(0, errstr.size()).c_str()); + + // test add new column into an ao table having default value setting + util.execute("alter table altable add column x int default 1"); + util.query("select a,b,c,x from altable where a=1", + "1|1|1|1|\n"); + + // test alter column having default value setting + util.execute("alter table altable alter column c set default 10 - 1"); + util.execute("insert into altable(a,b) values(11,'11')"); + util.query("select a,b,c from altable where a=11", + "11|11|9|\n"); + + // test alter column dropping default value setting + util.execute("alter table altable alter column c drop default"); + util.execute("insert into altable(a,b) values(12,'12')"); + util.query("select a,b,c from altable where a=12", + "12|12||\n"); + + // cleanup + util.execute("drop table altable"); +} + +TEST_F(TestAlterTable, TestAlterTableAOColumnNOTNULL) { + hawq::test::SQLUtility util; + // prepare + util.execute("drop table if exists altable"); + util.execute("create table altable (a int, b text, c int)"); + util.execute("insert into altable " + "select i, i::text, i from generate_series(1,10) i"); + + // test set not null + util.execute("alter table altable alter column c set not null"); + + util.execute("insert into altable(a,b) values(13,'13')", false); + std::string errstr = "ERROR: null value in column \"c\" violates " + "not-null constraint"; + EXPECT_STREQ(errstr.c_str(), + util.getPSQL()->getLastResult().substr(0, errstr.size()).c_str()); + + // test drop not null + util.execute("alter table altable alter column c drop not null"); + util.execute("insert into altable(a,b) values(13,'13')"); + util.query("select a,b,c from altable where a=13", + "13|13||\n"); + + // cleanup + util.execute("drop table altable"); +} + +TEST_F(TestAlterTable, TestAlterTableAOColumnConstraint) { + hawq::test::SQLUtility util; + bool orcaon = false; + if (util.getGUCValue("optimizer") == "on") { + std::cout << "NOTE: TestAlterTable.TestAlterTableAOColumnConstraint " + "uses answer for optimizer on" << std::endl; + orcaon = true; + } + // prepare + util.execute("drop table if exists altable"); + util.execute("create table altable (a int, b text, c int)"); + util.execute("insert into altable " + "select i, i::text, i from generate_series(1,10) i"); + + // test , constaint is broken by existing rows + util.execute("alter table altable " + "add constraint c_check check (c<10)", false); + std::string errstr = "ERROR: check constraint \"c_check\" " + "is violated by some row"; + EXPECT_STREQ(errstr.c_str(), + util.getPSQL()->getLastResult().substr(0, errstr.size()).c_str()); + + // test, new row breaks existing contraint + util.execute("alter table altable add constraint c_check check (c>0)"); + util.execute("insert into altable(a,b,c) values(11,'11',-11)", false); + if (orcaon) { + errstr = "ERROR: One or more assertions failed"; + } + else { + errstr = "ERROR: new row for relation \"altable\" " + "violates check constraint \"c_check\""; + } + EXPECT_STREQ(errstr.c_str(), + util.getPSQL()->getLastResult().substr(0, errstr.size()).c_str()); + if (orcaon) { + std::string errdetail = "DETAIL: Check constraint c_check for table " + "altable was violated"; + std::string::size_type find = util.getPSQL()->getLastResult().find(errdetail); + EXPECT_NE(find, std::string::npos); + } + + // test, drop constraint + util.execute("alter table altable drop constraint c_check"); + util.execute("insert into altable(a,b,c) values(11,'11',-11)"); + util.query("select a,b,c from altable where a=11", + "11|11|-11|\n"); + // cleanup + util.execute("drop table altable"); +} + +TEST_F(TestAlterTable, TestAlterTableAOColumnMisc) { + hawq::test::SQLUtility util; + // prepare + util.execute("drop table if exists altable"); + util.execute("create table altable (a int, b text, c int)"); + util.execute("insert into altable " + "select i, i::text, i from generate_series(1,10) i"); + + util.execute("alter table altable alter column c set statistics 100"); + util.execute("alter table altable alter column b set storage plain"); + util.execute("insert into altable(a,b,c) values(11,'11',11)"); + util.query("select a,b,c from altable where a=11", + "11|11|11|\n"); + + // drop column + util.execute("alter table altable drop column b"); + util.query("select a,c from altable where a=1", "1|1|\n"); + + // change column type from int to bigint + util.execute("alter table altable alter column c type bigint"); + + // miscs ( should add more to verify the changes after successfully changed + // the target table + util.execute("alter table altable set without oids"); + + util.execute("alter table altable set (fillfactor=90)", false); + std::string errstr = "ERROR: altering reloptions for append only tables " + "is not permitted"; + EXPECT_STREQ(errstr.c_str(), + util.getPSQL()->getLastResult().substr(0, errstr.size()).c_str()); + + // cleanup + util.execute("drop table altable"); +} + +TEST_F(TestAlterTable, TestAlterTableAODropColumn) { + hawq::test::SQLUtility util; + // prepare + util.execute("drop table if exists altable"); + util.execute("create table altable (a int, b text, c int)"); + util.execute("insert into altable " + "select i, i::text, i from generate_series(1,10) i"); + + // test set not null + util.execute("alter table altable drop column b"); + + util.query("select a,c from altable where a=10","10|10|\n"); + + // cleanup + util.execute("drop table altable"); +} + +TEST_F(TestAlterTable, TestAlterTableOwner) { + hawq::test::SQLUtility util; + // prepare + util.execute("drop table if exists altable"); + util.execute("drop user if exists altuser"); + + // test + util.execute("create user altuser"); + util.execute("create table altable (a,b) as values(1,10),(2,20)"); + util.execute("alter table altable owner to altuser"); + util.execute("set role altuser"); + util.execute("insert into altable(a,b) values(3,30)"); + util.execute("reset role"); + + // cleanup + util.execute("drop table altable"); + util.execute("drop user altuser"); +} + +TEST_F(TestAlterTable, TestAlterTableAddColumn) { + hawq::test::SQLUtility util; + // prepare + util.execute("drop table if exists tmp"); + + // test + util.execute("create table tmp (initial int4)"); + util.execute("ALTER TABLE tmp ADD COLUMN a int4 default 3"); + util.execute("ALTER TABLE tmp ADD COLUMN b name default 'Alan Turing'"); + util.execute("ALTER TABLE tmp ADD COLUMN c text default 'Pivotal'"); + util.execute("ALTER TABLE tmp ADD COLUMN d float8 default 0"); + util.execute("ALTER TABLE tmp ADD COLUMN e float4 default 0"); + util.execute("ALTER TABLE tmp ADD COLUMN f int2 default 0"); + util.execute("ALTER TABLE tmp ADD COLUMN g polygon default " + "'(1,1),(1,2),(2,2)'::polygon"); + util.execute("ALTER TABLE tmp ADD COLUMN h abstime default null"); + util.execute("ALTER TABLE tmp ADD COLUMN i char default 'P'"); + util.execute("set datestyle=ISO,DMY;" + "ALTER TABLE tmp ADD COLUMN j abstime[] " + "default ARRAY['2/2/2013 4:05:06'::abstime, " + "'2/2/2013 5:05:06'::abstime]"); + util.execute("ALTER TABLE tmp ADD COLUMN k int4 default 0"); + util.execute("ALTER TABLE tmp ADD COLUMN l tid default '(0,1)'::tid"); + util.execute("ALTER TABLE tmp ADD COLUMN m xid default '0'::xid"); + util.execute("ALTER TABLE tmp ADD COLUMN n oidvector " + "default '0 0 0 0'::oidvector"); + util.execute("ALTER TABLE tmp ADD COLUMN p smgr " + "default 'magnetic disk'::smgr"); + util.execute("ALTER TABLE tmp ADD COLUMN q point default '(0,0)'::point"); + util.execute("ALTER TABLE tmp ADD COLUMN r lseg default '(0,0),(1,1)'::lseg"); + util.execute("ALTER TABLE tmp ADD COLUMN s path default '(1,1),(1,2),(2,2)'::path"); + util.execute("ALTER TABLE tmp ADD COLUMN t box default box(circle '((0,0), 2.0)')"); + + util.execute("set datestyle=ISO,DMY;" + "ALTER TABLE tmp ADD COLUMN u tinterval " + "default tinterval('2/2/2013 4:05:06', '2/2/2013 5:05:06')"); + util.execute("set datestyle=ISO,DMY;" + "ALTER TABLE tmp ADD COLUMN v timestamp " + "default '2/2/2013 4:05:06'::timestamp"); + util.execute("ALTER TABLE tmp ADD COLUMN w interval default '3 4:05:06'::interval"); + util.execute("ALTER TABLE tmp ADD COLUMN x float8[] default ARRAY[0, 0, 0]"); + util.execute("ALTER TABLE tmp ADD COLUMN y float4[] default ARRAY[0, 0, 0]"); + util.execute("ALTER TABLE tmp ADD COLUMN z int2[] default ARRAY[0, 0, 0]"); + + util.execSQLFile("catalog/sql/alter-table-addcol-insert-alltypes.sql", + "catalog/ans/alter-table-addcol-insert-alltypes.ans"); + // cleanup + util.execute("drop table tmp"); +} http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/9833d128/src/test/regress/expected/gpsql_alter_table.out ---------------------------------------------------------------------- diff --git a/src/test/regress/expected/gpsql_alter_table.out b/src/test/regress/expected/gpsql_alter_table.out deleted file mode 100644 index 275ec6f..0000000 --- a/src/test/regress/expected/gpsql_alter_table.out +++ /dev/null @@ -1,197 +0,0 @@ --- AO -CREATE TABLE altable(a int, b text, c 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 altable SELECT i, i::text, i FROM generate_series(1, 10)i; -ALTER TABLE altable ADD COLUMN x int; -ERROR: ADD COLUMN with no default value in append-only tables is not yet supported. -ALTER TABLE altable ADD COLUMN x int DEFAULT 1; -SELECT a, b, c, x FROM altable; - a | b | c | x -----+----+----+--- - 8 | 8 | 8 | 1 - 9 | 9 | 9 | 1 - 10 | 10 | 10 | 1 - 1 | 1 | 1 | 1 - 2 | 2 | 2 | 1 - 3 | 3 | 3 | 1 - 4 | 4 | 4 | 1 - 5 | 5 | 5 | 1 - 6 | 6 | 6 | 1 - 7 | 7 | 7 | 1 -(10 rows) - -ALTER TABLE altable ALTER COLUMN c SET DEFAULT 10 - 1; -INSERT INTO altable(a, b) VALUES(11, '11'); -SELECT a, b, c FROM altable WHERE a = 11; - a | b | c -----+----+--- - 11 | 11 | 9 -(1 row) - -ALTER TABLE altable ALTER COLUMN c DROP DEFAULT; -BEGIN; -INSERT INTO altable(a, b) VALUES(12, '12'); -SELECT a, b, c FROM altable WHERE a = 12; - a | b | c -----+----+--- - 12 | 12 | -(1 row) - -ROLLBACK; -ALTER TABLE altable ALTER COLUMN c SET NOT NULL; -INSERT INTO altable(a, b) VALUES(13, '13'); -ERROR: null value in column "c" violates not-null constraint (seg7 localhost:40000 pid=87759) -ALTER TABLE altable ALTER COLUMN c DROP NOT NULL; -INSERT INTO altable(a, b) VALUES(13, '13'); -SELECT a, b, c FROM altable WHERE a = 13; - a | b | c -----+----+--- - 13 | 13 | -(1 row) - -ALTER TABLE altable ALTER COLUMN c SET STATISTICS 100; -ALTER TABLE altable ALTER COLUMN b SET STORAGE PLAIN; -ALTER TABLE altable DROP COLUMN b; -SELECT a, c FROM altable; - a | c -----+---- - 1 | 1 - 9 | 9 - 8 | 8 - 3 | 3 - 2 | 2 - 10 | 10 - 5 | 5 - 4 | 4 - 7 | 7 - 6 | 6 - 11 | 9 - 13 | -(12 rows) - -ALTER TABLE altable ADD CONSTRAINT c_check CHECK (c < 10); -ERROR: check constraint "c_check" is violated by some row (seg0 localhost:40000 pid=87752) -ALTER TABLE altable ADD CONSTRAINT c_check CHECK (c > 0); -INSERT INTO altable(a, c) VALUES(0, -10); -ERROR: new row for relation "altable" violates check constraint "c_check" (seg5 localhost:40000 pid=87757) -ALTER TABLE altable DROP CONSTRAINT c_check; -INSERT INTO altable(a, c) VALUES(0, -10); -ALTER TABLE altable ALTER COLUMN c TYPE bigint; -CREATE USER alt_user; -NOTICE: resource queue required -- using default resource queue "pg_default" -CREATE TABLE altable_owner(a, b) AS VALUES(1, 10),(2,20); -ALTER TABLE altable_owner OWNER to alt_user; -SET ROLE alt_user; -SELECT a, b FROM altable_owner; - a | b ----+---- - 1 | 10 - 2 | 20 -(2 rows) - -RESET ROLE; -DROP TABLE altable_owner; -DROP USER alt_user; -ALTER TABLE altable CLUSTER ON some_index; -ERROR: ALTER TABLE ... CLUSTER is not supported -ALTER TABLE altable SET WITHOUT OIDS; -ALTER TABLE altable SET TABLESPACE pg_default; -ERROR: ALTER TABLE ... SET TABLESPACE is not supported -ALTER TABLE altable SET (fillfactor = 90); -ERROR: altering reloptions for append only tables is not permitted -ALTER TABLE altable ENABLE TRIGGER ALL; -ERROR: ALTER TABLE ... ENABLE TRIGGER is not supported -ALTER TABLE altable DISABLE TRIGGER ALL; -ERROR: ALTER TABLE ... DISABLE TRIGGER is not supported --- CO -CREATE TABLE altablec(a int, b text, c int) WITH (appendonly=true, orientation=column); -- should error: deprecated -ERROR: Column oriented tables are deprecated. Not support it any more. --- --- cover more column/attribute types --- -DROP TABLE IF EXISTS tmp; -NOTICE: table "tmp" does not exist, skipping -CREATE TABLE tmp (initial int4); -COMMENT ON TABLE tmp_wrong IS 'table comment'; -ERROR: relation "tmp_wrong" does not exist -COMMENT ON TABLE tmp IS 'table comment'; -COMMENT ON TABLE tmp IS NULL; -ALTER TABLE tmp ADD COLUMN a int4 default 3; -ALTER TABLE tmp ADD COLUMN b name default 'Alan Turing'; -ALTER TABLE tmp ADD COLUMN c text default 'Pivotal'; -ALTER TABLE tmp ADD COLUMN d float8 default 0; -ALTER TABLE tmp ADD COLUMN e float4 default 0; -ALTER TABLE tmp ADD COLUMN f int2 default 0; -ALTER TABLE tmp ADD COLUMN g polygon default '(1,1),(1,2),(2,2)'::polygon; -ALTER TABLE tmp ADD COLUMN h abstime default null; -ALTER TABLE tmp ADD COLUMN i char default 'P'; -ALTER TABLE tmp ADD COLUMN j abstime[] default ARRAY['2/2/2013 4:05:06'::abstime, '2/2/2013 5:05:06'::abstime]; -ALTER TABLE tmp ADD COLUMN k int4 default 0; -ALTER TABLE tmp ADD COLUMN l tid default '(0,1)'::tid; -ALTER TABLE tmp ADD COLUMN m xid default '0'::xid; -ALTER TABLE tmp ADD COLUMN n oidvector default '0 0 0 0'::oidvector; ---ALTER TABLE tmp ADD COLUMN o lock; -ALTER TABLE tmp ADD COLUMN p smgr default 'magnetic disk'::smgr; -ALTER TABLE tmp ADD COLUMN q point default '(0,0)'::point; -ALTER TABLE tmp ADD COLUMN r lseg default '(0,0),(1,1)'::lseg; -ALTER TABLE tmp ADD COLUMN s path default '(1,1),(1,2),(2,2)'::path; -ALTER TABLE tmp ADD COLUMN t box default box(circle '((0,0), 2.0)'); -ALTER TABLE tmp ADD COLUMN u tinterval default tinterval('2/2/2013 4:05:06', '2/2/2013 5:05:06'); -ALTER TABLE tmp ADD COLUMN v timestamp default '2/2/2013 4:05:06'::timestamp; -ALTER TABLE tmp ADD COLUMN w interval default '3 4:05:06'::interval; -ALTER TABLE tmp ADD COLUMN x float8[] default ARRAY[0, 0, 0]; -ALTER TABLE tmp ADD COLUMN y float4[] default ARRAY[0, 0, 0]; -ALTER TABLE tmp ADD COLUMN z int2[] default ARRAY[0, 0, 0]; -INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u, - v, w, x, y, z) - VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)', - 'Mon May 1 00:30:30 1995', 'c', '{Mon May 1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}', - 314159, '(1,1)', '512', - '1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)', - '(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["epoch" "infinity"]', - 'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}'); -SELECT * FROM tmp; - initial | a | b | c | d | e | f | g | h | i | j | k | l | m | n | p | q | r | s | t | u | v | w | x | y | z ----------+---+------+------+-----+-----+---+-----------------------+------------------------------+---+------------------------------------------------------------------------------------------------+--------+-------+-----+-----------------+---------------+-----------+-----------------------+-----------------------------+---------------------+---------------------------------------------+--------------------------+------------------+-----------+-----------+----------- - | 4 | name | text | 4.1 | 4.1 | 2 | ((4.1,4.1),(3.1,3.1)) | Mon May 01 00:30:30 1995 PDT | c | {"Mon May 01 00:30:30 1995 PDT","Mon Aug 24 14:43:07 1992 PDT","Wed Dec 31 16:00:00 1969 PST"} | 314159 | (1,1) | 512 | 1 2 3 4 5 6 7 8 | magnetic disk | (1.1,1.1) | [(4.1,4.1),(3.1,3.1)] | ((0,2),(4.1,4.1),(3.1,3.1)) | (4.1,4.1),(3.1,3.1) | ["Wed Dec 31 16:00:00 1969 PST" "infinity"] | Thu Jan 01 00:00:00 1970 | @ 1 hour 10 secs | {1,2,3,4} | {1,2,3,4} | {1,2,3,4} -(1 row) - -DROP TABLE tmp; --- add a column of user defined type -create type udt1 - AS(base integer, incbase integer, ctime timestamptz); -create table ao1 (a integer, b integer) distributed by(a); -insert into ao1 select i, 10*i from generate_series(1,10)i; -alter table ao1 add column c udt1 default null; -insert into ao1 - select i, -i, - (-i*2, 10*i, '12/1/14 22:22:01')::udt1 - from generate_series(1,10)i; -select * from ao1 order by a,b; - a | b | c -----+-----+------------------------------------------ - 1 | -1 | (-2,10,"Mon Dec 01 22:22:01 2014 PST") - 1 | 10 | - 2 | -2 | (-4,20,"Mon Dec 01 22:22:01 2014 PST") - 2 | 20 | - 3 | -3 | (-6,30,"Mon Dec 01 22:22:01 2014 PST") - 3 | 30 | - 4 | -4 | (-8,40,"Mon Dec 01 22:22:01 2014 PST") - 4 | 40 | - 5 | -5 | (-10,50,"Mon Dec 01 22:22:01 2014 PST") - 5 | 50 | - 6 | -6 | (-12,60,"Mon Dec 01 22:22:01 2014 PST") - 6 | 60 | - 7 | -7 | (-14,70,"Mon Dec 01 22:22:01 2014 PST") - 7 | 70 | - 8 | -8 | (-16,80,"Mon Dec 01 22:22:01 2014 PST") - 8 | 80 | - 9 | -9 | (-18,90,"Mon Dec 01 22:22:01 2014 PST") - 9 | 90 | - 10 | -10 | (-20,100,"Mon Dec 01 22:22:01 2014 PST") - 10 | 100 | -(20 rows) - -drop table ao1; -drop type udt1; http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/9833d128/src/test/regress/expected/gpsql_alter_table_optimizer.out ---------------------------------------------------------------------- diff --git a/src/test/regress/expected/gpsql_alter_table_optimizer.out b/src/test/regress/expected/gpsql_alter_table_optimizer.out deleted file mode 100644 index cc17057..0000000 --- a/src/test/regress/expected/gpsql_alter_table_optimizer.out +++ /dev/null @@ -1,202 +0,0 @@ --- AO -CREATE TABLE altable(a int, b text, c 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 altable SELECT i, i::text, i FROM generate_series(1, 10)i; -ALTER TABLE altable ADD COLUMN x int; -ERROR: ADD COLUMN with no default value in append-only tables is not yet supported. -ALTER TABLE altable ADD COLUMN x int DEFAULT 1; -SELECT a, b, c, x FROM altable; - a | b | c | x -----+----+----+--- - 8 | 8 | 8 | 1 - 9 | 9 | 9 | 1 - 10 | 10 | 10 | 1 - 1 | 1 | 1 | 1 - 2 | 2 | 2 | 1 - 3 | 3 | 3 | 1 - 4 | 4 | 4 | 1 - 5 | 5 | 5 | 1 - 6 | 6 | 6 | 1 - 7 | 7 | 7 | 1 -(10 rows) - -ALTER TABLE altable ALTER COLUMN c SET DEFAULT 10 - 1; -INSERT INTO altable(a, b) VALUES(11, '11'); -SELECT a, b, c FROM altable WHERE a = 11; - a | b | c -----+----+--- - 11 | 11 | 9 -(1 row) - -ALTER TABLE altable ALTER COLUMN c DROP DEFAULT; -BEGIN; -INSERT INTO altable(a, b) VALUES(12, '12'); -SELECT a, b, c FROM altable WHERE a = 12; - a | b | c -----+----+--- - 12 | 12 | -(1 row) - -ROLLBACK; -ALTER TABLE altable ALTER COLUMN c SET NOT NULL; -INSERT INTO altable(a, b) VALUES(13, '13'); -ERROR: null value in column "c" violates not-null constraint (COptTasks.cpp:1756) -ALTER TABLE altable ALTER COLUMN c DROP NOT NULL; -INSERT INTO altable(a, b) VALUES(13, '13'); -SELECT a, b, c FROM altable WHERE a = 13; - a | b | c -----+----+--- - 13 | 13 | -(1 row) - -ALTER TABLE altable ALTER COLUMN c SET STATISTICS 100; -ALTER TABLE altable ALTER COLUMN b SET STORAGE PLAIN; -ALTER TABLE altable DROP COLUMN b; -SELECT a, c FROM altable; - a | c -----+---- - 3 | 3 - 4 | 4 - 5 | 5 - 6 | 6 - 7 | 7 - 8 | 8 - 9 | 9 - 10 | 10 - 11 | 9 - 1 | 1 - 2 | 2 - 13 | -(12 rows) - -ALTER TABLE altable ADD CONSTRAINT c_check CHECK (c < 10); -ERROR: check constraint "c_check" is violated by some row (seg2 smdw:40000 pid=12117) -ALTER TABLE altable ADD CONSTRAINT c_check CHECK (c > 0); -INSERT INTO altable(a, c) VALUES(0, -10); -ERROR: One or more assertions failed -DETAIL: Check constraint c_check for table altable was violated -ALTER TABLE altable DROP CONSTRAINT c_check; -INSERT INTO altable(a, c) VALUES(0, -10); -ALTER TABLE altable ALTER COLUMN c TYPE bigint; -CREATE USER alt_user; -NOTICE: resource queue required -- using default resource queue "pg_default" -CREATE TABLE altable_owner(a, b) AS VALUES(1, 10),(2,20); -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. -ALTER TABLE altable_owner OWNER to alt_user; -SET ROLE alt_user; -SELECT a, b FROM altable_owner; - a | b ----+---- - 1 | 10 - 2 | 20 -(2 rows) - -RESET ROLE; -DROP TABLE altable_owner; -DROP USER alt_user; -ALTER TABLE altable CLUSTER ON some_index; -ERROR: ALTER TABLE ... CLUSTER is not supported -ALTER TABLE altable SET WITHOUT OIDS; -ALTER TABLE altable SET TABLESPACE pg_default; -ERROR: ALTER TABLE ... SET TABLESPACE is not supported -ALTER TABLE altable SET (fillfactor = 90); -ERROR: altering reloptions for append only tables is not permitted -ALTER TABLE altable ENABLE TRIGGER ALL; -ERROR: ALTER TABLE ... ENABLE TRIGGER is not supported -ALTER TABLE altable DISABLE TRIGGER ALL; -ERROR: ALTER TABLE ... DISABLE TRIGGER is not supported --- CO -CREATE TABLE altablec(a int, b text, c int) WITH (appendonly=true, orientation=column); -- should error: deprecated -ERROR: Column oriented tables are deprecated. Not support it any more. --- --- cover more column/attribute types --- -DROP TABLE IF EXISTS tmp; -NOTICE: table "tmp" does not exist, skipping -CREATE TABLE tmp (initial int4); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'initial' 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. -COMMENT ON TABLE tmp_wrong IS 'table comment'; -ERROR: relation "tmp_wrong" does not exist -COMMENT ON TABLE tmp IS 'table comment'; -COMMENT ON TABLE tmp IS NULL; -ALTER TABLE tmp ADD COLUMN a int4 default 3; -ALTER TABLE tmp ADD COLUMN b name default 'Alan Turing'; -ALTER TABLE tmp ADD COLUMN c text default 'Pivotal'; -ALTER TABLE tmp ADD COLUMN d float8 default 0; -ALTER TABLE tmp ADD COLUMN e float4 default 0; -ALTER TABLE tmp ADD COLUMN f int2 default 0; -ALTER TABLE tmp ADD COLUMN g polygon default '(1,1),(1,2),(2,2)'::polygon; -ALTER TABLE tmp ADD COLUMN h abstime default null; -ALTER TABLE tmp ADD COLUMN i char default 'P'; -ALTER TABLE tmp ADD COLUMN j abstime[] default ARRAY['2/2/2013 4:05:06'::abstime, '2/2/2013 5:05:06'::abstime]; -ALTER TABLE tmp ADD COLUMN k int4 default 0; -ALTER TABLE tmp ADD COLUMN l tid default '(0,1)'::tid; -ALTER TABLE tmp ADD COLUMN m xid default '0'::xid; -ALTER TABLE tmp ADD COLUMN n oidvector default '0 0 0 0'::oidvector; ---ALTER TABLE tmp ADD COLUMN o lock; -ALTER TABLE tmp ADD COLUMN p smgr default 'magnetic disk'::smgr; -ALTER TABLE tmp ADD COLUMN q point default '(0,0)'::point; -ALTER TABLE tmp ADD COLUMN r lseg default '(0,0),(1,1)'::lseg; -ALTER TABLE tmp ADD COLUMN s path default '(1,1),(1,2),(2,2)'::path; -ALTER TABLE tmp ADD COLUMN t box default box(circle '((0,0), 2.0)'); -ALTER TABLE tmp ADD COLUMN u tinterval default tinterval('2/2/2013 4:05:06', '2/2/2013 5:05:06'); -ALTER TABLE tmp ADD COLUMN v timestamp default '2/2/2013 4:05:06'::timestamp; -ALTER TABLE tmp ADD COLUMN w interval default '3 4:05:06'::interval; -ALTER TABLE tmp ADD COLUMN x float8[] default ARRAY[0, 0, 0]; -ALTER TABLE tmp ADD COLUMN y float4[] default ARRAY[0, 0, 0]; -ALTER TABLE tmp ADD COLUMN z int2[] default ARRAY[0, 0, 0]; -INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u, - v, w, x, y, z) - VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)', - 'Mon May 1 00:30:30 1995', 'c', '{Mon May 1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}', - 314159, '(1,1)', '512', - '1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)', - '(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["epoch" "infinity"]', - 'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}'); -SELECT * FROM tmp; - initial | a | b | c | d | e | f | g | h | i | j | k | l | m | n | p | q | r | s | t | u | v | w | x | y | z ----------+---+------+------+-----+-----+---+-----------------------+------------------------------+---+------------------------------------------------------------------------------------------------+--------+-------+-----+-----------------+---------------+-----------+-----------------------+-----------------------------+---------------------+---------------------------------------------+--------------------------+------------------+-----------+-----------+----------- - | 4 | name | text | 4.1 | 4.1 | 2 | ((4.1,4.1),(3.1,3.1)) | Mon May 01 00:30:30 1995 PDT | c | {"Mon May 01 00:30:30 1995 PDT","Mon Aug 24 14:43:07 1992 PDT","Wed Dec 31 16:00:00 1969 PST"} | 314159 | (1,1) | 512 | 1 2 3 4 5 6 7 8 | magnetic disk | (1.1,1.1) | [(4.1,4.1),(3.1,3.1)] | ((0,2),(4.1,4.1),(3.1,3.1)) | (4.1,4.1),(3.1,3.1) | ["Wed Dec 31 16:00:00 1969 PST" "infinity"] | Thu Jan 01 00:00:00 1970 | @ 1 hour 10 secs | {1,2,3,4} | {1,2,3,4} | {1,2,3,4} -(1 row) - -DROP TABLE tmp; --- add a column of user defined type -create type udt1 - AS(base integer, incbase integer, ctime timestamptz); -create table ao1 (a integer, b integer) distributed by(a); -insert into ao1 select i, 10*i from generate_series(1,10)i; -alter table ao1 add column c udt1 default null; -insert into ao1 - select i, -i, - (-i*2, 10*i, '12/1/14 22:22:01')::udt1 - from generate_series(1,10)i; -select * from ao1 order by a,b; - a | b | c -----+-----+------------------------------------------ - 1 | -1 | (-2,10,"Mon Dec 01 22:22:01 2014 PST") - 1 | 10 | - 2 | -2 | (-4,20,"Mon Dec 01 22:22:01 2014 PST") - 2 | 20 | - 3 | -3 | (-6,30,"Mon Dec 01 22:22:01 2014 PST") - 3 | 30 | - 4 | -4 | (-8,40,"Mon Dec 01 22:22:01 2014 PST") - 4 | 40 | - 5 | -5 | (-10,50,"Mon Dec 01 22:22:01 2014 PST") - 5 | 50 | - 6 | -6 | (-12,60,"Mon Dec 01 22:22:01 2014 PST") - 6 | 60 | - 7 | -7 | (-14,70,"Mon Dec 01 22:22:01 2014 PST") - 7 | 70 | - 8 | -8 | (-16,80,"Mon Dec 01 22:22:01 2014 PST") - 8 | 80 | - 9 | -9 | (-18,90,"Mon Dec 01 22:22:01 2014 PST") - 9 | 90 | - 10 | -10 | (-20,100,"Mon Dec 01 22:22:01 2014 PST") - 10 | 100 | -(20 rows) - -drop table ao1; -drop type udt1; http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/9833d128/src/test/regress/known_good_schedule ---------------------------------------------------------------------- diff --git a/src/test/regress/known_good_schedule b/src/test/regress/known_good_schedule index 3043e55..b84e00f 100755 --- a/src/test/regress/known_good_schedule +++ b/src/test/regress/known_good_schedule @@ -18,7 +18,6 @@ ignore: goh_column_compression test: goh_database test: goh_gp_dist_random ignore: gpsql_fault_tolerance -test: gpsql_alter_table test: goh_portals test: goh_prepare ignore: goh_set_schema http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/9833d128/src/test/regress/sql/gpsql_alter_table.sql ---------------------------------------------------------------------- diff --git a/src/test/regress/sql/gpsql_alter_table.sql b/src/test/regress/sql/gpsql_alter_table.sql deleted file mode 100644 index 0c2a737..0000000 --- a/src/test/regress/sql/gpsql_alter_table.sql +++ /dev/null @@ -1,153 +0,0 @@ --- AO -CREATE TABLE altable(a int, b text, c int); -INSERT INTO altable SELECT i, i::text, i FROM generate_series(1, 10)i; - -ALTER TABLE altable ADD COLUMN x int; -ALTER TABLE altable ADD COLUMN x int DEFAULT 1; -SELECT a, b, c, x FROM altable; - -ALTER TABLE altable ALTER COLUMN c SET DEFAULT 10 - 1; -INSERT INTO altable(a, b) VALUES(11, '11'); -SELECT a, b, c FROM altable WHERE a = 11; - -ALTER TABLE altable ALTER COLUMN c DROP DEFAULT; -BEGIN; -INSERT INTO altable(a, b) VALUES(12, '12'); -SELECT a, b, c FROM altable WHERE a = 12; -ROLLBACK; - -ALTER TABLE altable ALTER COLUMN c SET NOT NULL; -INSERT INTO altable(a, b) VALUES(13, '13'); - -ALTER TABLE altable ALTER COLUMN c DROP NOT NULL; -INSERT INTO altable(a, b) VALUES(13, '13'); -SELECT a, b, c FROM altable WHERE a = 13; - -ALTER TABLE altable ALTER COLUMN c SET STATISTICS 100; - -ALTER TABLE altable ALTER COLUMN b SET STORAGE PLAIN; - -ALTER TABLE altable DROP COLUMN b; -SELECT a, c FROM altable; - -ALTER TABLE altable ADD CONSTRAINT c_check CHECK (c < 10); - -ALTER TABLE altable ADD CONSTRAINT c_check CHECK (c > 0); -INSERT INTO altable(a, c) VALUES(0, -10); - -ALTER TABLE altable DROP CONSTRAINT c_check; -INSERT INTO altable(a, c) VALUES(0, -10); - -ALTER TABLE altable ALTER COLUMN c TYPE bigint; - -CREATE USER alt_user; -CREATE TABLE altable_owner(a, b) AS VALUES(1, 10),(2,20); -ALTER TABLE altable_owner OWNER to alt_user; -SET ROLE alt_user; -SELECT a, b FROM altable_owner; -RESET ROLE; -DROP TABLE altable_owner; -DROP USER alt_user; - -ALTER TABLE altable CLUSTER ON some_index; - -ALTER TABLE altable SET WITHOUT OIDS; - -ALTER TABLE altable SET TABLESPACE pg_default; - -ALTER TABLE altable SET (fillfactor = 90); - -ALTER TABLE altable ENABLE TRIGGER ALL; - -ALTER TABLE altable DISABLE TRIGGER ALL; - --- CO -CREATE TABLE altablec(a int, b text, c int) WITH (appendonly=true, orientation=column); -- should error: deprecated - --- --- cover more column/attribute types --- -DROP TABLE IF EXISTS tmp; -CREATE TABLE tmp (initial int4); - -COMMENT ON TABLE tmp_wrong IS 'table comment'; -COMMENT ON TABLE tmp IS 'table comment'; -COMMENT ON TABLE tmp IS NULL; - -ALTER TABLE tmp ADD COLUMN a int4 default 3; - -ALTER TABLE tmp ADD COLUMN b name default 'Alan Turing'; - -ALTER TABLE tmp ADD COLUMN c text default 'Pivotal'; - -ALTER TABLE tmp ADD COLUMN d float8 default 0; - -ALTER TABLE tmp ADD COLUMN e float4 default 0; - -ALTER TABLE tmp ADD COLUMN f int2 default 0; - -ALTER TABLE tmp ADD COLUMN g polygon default '(1,1),(1,2),(2,2)'::polygon; - -ALTER TABLE tmp ADD COLUMN h abstime default null; - -ALTER TABLE tmp ADD COLUMN i char default 'P'; - -ALTER TABLE tmp ADD COLUMN j abstime[] default ARRAY['2/2/2013 4:05:06'::abstime, '2/2/2013 5:05:06'::abstime]; - -ALTER TABLE tmp ADD COLUMN k int4 default 0; - -ALTER TABLE tmp ADD COLUMN l tid default '(0,1)'::tid; - -ALTER TABLE tmp ADD COLUMN m xid default '0'::xid; - -ALTER TABLE tmp ADD COLUMN n oidvector default '0 0 0 0'::oidvector; - ---ALTER TABLE tmp ADD COLUMN o lock; -ALTER TABLE tmp ADD COLUMN p smgr default 'magnetic disk'::smgr; - -ALTER TABLE tmp ADD COLUMN q point default '(0,0)'::point; - -ALTER TABLE tmp ADD COLUMN r lseg default '(0,0),(1,1)'::lseg; - -ALTER TABLE tmp ADD COLUMN s path default '(1,1),(1,2),(2,2)'::path; - -ALTER TABLE tmp ADD COLUMN t box default box(circle '((0,0), 2.0)'); - -ALTER TABLE tmp ADD COLUMN u tinterval default tinterval('2/2/2013 4:05:06', '2/2/2013 5:05:06'); - -ALTER TABLE tmp ADD COLUMN v timestamp default '2/2/2013 4:05:06'::timestamp; - -ALTER TABLE tmp ADD COLUMN w interval default '3 4:05:06'::interval; - -ALTER TABLE tmp ADD COLUMN x float8[] default ARRAY[0, 0, 0]; - -ALTER TABLE tmp ADD COLUMN y float4[] default ARRAY[0, 0, 0]; - -ALTER TABLE tmp ADD COLUMN z int2[] default ARRAY[0, 0, 0]; - -INSERT INTO tmp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u, - v, w, x, y, z) - VALUES (4, 'name', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)', - 'Mon May 1 00:30:30 1995', 'c', '{Mon May 1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}', - 314159, '(1,1)', '512', - '1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)', - '(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["epoch" "infinity"]', - 'epoch', '01:00:10', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}'); - -SELECT * FROM tmp; - -DROP TABLE tmp; - --- add a column of user defined type -create type udt1 - AS(base integer, incbase integer, ctime timestamptz); -create table ao1 (a integer, b integer) distributed by(a); -insert into ao1 select i, 10*i from generate_series(1,10)i; -alter table ao1 add column c udt1 default null; -insert into ao1 - select i, -i, - (-i*2, 10*i, '12/1/14 22:22:01')::udt1 - from generate_series(1,10)i; -select * from ao1 order by a,b; -drop table ao1; -drop type udt1;
