CREATE SCHEMA cstore_alter_table;
SET SEARCH_PATH TO cstore_alter_table;
SET INTERVALsTYLE = POSTGRES;
-- test empty normal table
DROP TABLE IF EXISTS alter_addcols_00;
CREATE TABLE alter_addcols_00( a int , b int ) with ( orientation = column ) ;
ALTER TABLE alter_addcols_00 ADD COLUMN b int;
ALTER TABLE alter_addcols_00 ADD COLUMN c int NULL;
SELECT c FROM alter_addcols_00 WHERE c =  1;
ALTER TABLE alter_addcols_00 ADD COLUMN d int NOT NULL;
SELECT d FROM alter_addcols_00 WHERE d >=  1;
ALTER TABLE alter_addcols_00 ADD COLUMN e int default 5;
SELECT e FROM alter_addcols_00 WHERE e =  5;
ALTER TABLE alter_addcols_00 ADD COLUMN f int NULL default 10;
SELECT f FROM alter_addcols_00 WHERE f =  10;
ALTER TABLE alter_addcols_00 ADD COLUMN g int check(g > 2);
ALTER TABLE alter_addcols_00 ADD COLUMN g int default f + 11;

ALTER TABLE alter_addcols_00 ADD COLUMN g varchar(100) default 'chars less than 31 size';
INSERT INTO alter_addcols_00(a) VALUES(1);
SELECT COUNT(*) FROM alter_addcols_00;
SELECT * FROM alter_addcols_00;
INSERT INTO alter_addcols_00(a, d) VALUES(1, 4);
SELECT * FROM alter_addcols_00;

ALTER TABLE alter_addcols_00 ADD COLUMN h varchar(100) default 'i think this is a string whose length is greater than 31.';
SELECT * FROM alter_addcols_00;

ALTER TABLE alter_addcols_00 ADD COLUMN i varchar(100) NOT NULL DEFAULT 'i think this is a string whose length is greater than 31.';
SELECT * FROM alter_addcols_00;

INSERT INTO alter_addcols_00(a, d) VALUES(1, 7);
INSERT INTO alter_addcols_00(a, d) VALUES(1, 11);
INSERT INTO alter_addcols_00(a, d) VALUES(1, 13);
ALTER TABLE alter_addcols_00 ADD COLUMN j varchar(100) NOT NULL DEFAULT 'i think this is a string whose length is greater than 31.';
SELECT * FROM alter_addcols_00;

INSERT INTO alter_addcols_00(a, d) VALUES(1, 17);
ALTER TABLE alter_addcols_00 ADD COLUMN k time with time zone DEFAULT '2015-04-02 20:26:53 PST';
SELECT * FROM alter_addcols_00;
INSERT INTO alter_addcols_00(a, d) VALUES(1, 19);
SELECT * FROM alter_addcols_00;

ALTER TABLE alter_addcols_00 ADD COLUMN p interval DEFAULT 1100;
INSERT INTO alter_addcols_00(a, d) VALUES(1, 23);
SELECT * FROM alter_addcols_00;

ALTER TABLE alter_addcols_00 ADD COLUMN q interval DEFAULT '1 12:59:10';
INSERT INTO alter_addcols_00(a, d) VALUES(1, 29);
SELECT * FROM alter_addcols_00;

ALTER TABLE alter_addcols_00 ADD COLUMN l name DEFAULT 'Gauss MPPDB';
-- TODO: forbid SMALLSERIAL && SERIAL && BIGSERIA
ALTER TABLE alter_addcols_00 ADD COLUMN l smallserial;
ALTER TABLE alter_addcols_00 ADD COLUMN l serial;
ALTER TABLE alter_addcols_00 ADD COLUMN l bigserial;
ALTER TABLE alter_addcols_00 ADD COLUMN m clob;
ALTER TABLE alter_addcols_00 ADD COLUMN n blob;
ALTER TABLE alter_addcols_00 ADD COLUMN o macaddr;
-- test noempty normal table holding 1 tuple
DROP TABLE IF EXISTS alter_addcols_01;
CREATE TABLE alter_addcols_01(a int) with ( orientation = column ) ;
INSERT INTO alter_addcols_01 VALUES (1);

ALTER TABLE alter_addcols_01 ADD COLUMN b int;
SELECT * FROM alter_addcols_01;
INSERT INTO alter_addcols_01 VALUES (1, 2);
SELECT * FROM alter_addcols_01;

ALTER TABLE alter_addcols_01 ADD COLUMN c int NULL;
SELECT * FROM alter_addcols_01;
INSERT INTO alter_addcols_01 VALUES (1, 3);
SELECT * FROM alter_addcols_01;
INSERT INTO alter_addcols_01 VALUES (1, 4, 1);
SELECT * FROM alter_addcols_01;

ALTER TABLE alter_addcols_01 ADD COLUMN d int NOT NULL;
ALTER TABLE alter_addcols_01 ADD COLUMN d int NOT NULL DEFAULT 55;
SELECT * FROM alter_addcols_01;
INSERT INTO alter_addcols_01 VALUES (1, 5, 2);
SELECT * FROM alter_addcols_01;
INSERT INTO alter_addcols_01 VALUES (1, 6, 3, 0);
SELECT * FROM alter_addcols_01;

ALTER TABLE alter_addcols_01 ADD COLUMN e int NOT NULL DEFAULT 5+5;
SELECT * FROM alter_addcols_01;

-- some cu whose tuples has been deleted must be skipped.
DROP TABLE IF EXISTS alter_addcols_02;
CREATE TABLE alter_addcols_02(a int, b int) with ( orientation = column ) ;
INSERT INTO alter_addcols_02 VALUES (1, 2);
INSERT INTO alter_addcols_02 VALUES (1, 3);
INSERT INTO alter_addcols_02 VALUES (1, 4);
DELETE FROM alter_addcols_02 WHERE b = 2;
ALTER TABLE alter_addcols_02 ADD COLUMN c int NOT NULL DEFAULT 9*9;
SELECT * FROM alter_addcols_02;

START TRANSACTION;
INSERT INTO alter_addcols_02 VALUES (1, 5);
\! @gsqldir@/gsql -d regression -p @portstring@ -c "INSERT INTO cstore_alter_table.alter_addcols_02 VALUES (1, 6);"
\! @gsqldir@/gsql -d regression -p @portstring@ -c "SELECT * FROM cstore_alter_table.alter_addcols_02"
ROLLBACK;
ALTER TABLE alter_addcols_02 ADD COLUMN d int NOT NULL DEFAULT 9*99;
SELECT * FROM alter_addcols_02;

ALTER TABLE alter_addcols_02 ADD COLUMN e int DEFAULT 100*100, ADD COLUMN f char(31) DEFAULT 'hello world, i am from China!!', ADD COLUMN g char(32) DEFAULT 'hello world, i am from China!!!', ADD COLUMN h char(33) DEFAULT 'hello world, i am from China!!!!', ADD COLUMN i char(130) DEFAULT 'hello world, i am from China!!!!hello world, i am from China!!!!';
INSERT INTO alter_addcols_02 VALUES (1, 5);
SELECT * FROM alter_addcols_02;

UPDATE alter_addcols_02 SET b = 7 WHERE b > 4;
SELECT * FROM alter_addcols_02;
ALTER TABLE alter_addcols_02 ADD COLUMN j varchar(100) NOT NULL DEFAULT 'DEFAULT hello world, i am from China!!!!hello world, i am from DEFAULT hello world, i am !!!!';
SELECT * FROM alter_addcols_02;

-----------  partition table -----------
-- test empty partition table
DROP TABLE IF EXISTS alter_addcols_50;
CREATE TABLE alter_addcols_50( a int , b int ) with ( orientation = column )  partition by range(b)
(
    partition p1 values less than (3),
	partition p2 values less than (6),
	partition p3 values less than (maxvalue)
);
ALTER TABLE alter_addcols_50 ADD COLUMN b int;
ALTER TABLE alter_addcols_50 ADD COLUMN c int NULL;
SELECT c FROM alter_addcols_50 WHERE c =  1;
ALTER TABLE alter_addcols_50 ADD COLUMN d int NOT NULL;
SELECT d FROM alter_addcols_50 WHERE d >=  1;
ALTER TABLE alter_addcols_50 ADD COLUMN e int default 5;
SELECT e FROM alter_addcols_50 WHERE e =  5;
ALTER TABLE alter_addcols_50 ADD COLUMN f int NULL default 10;
SELECT f FROM alter_addcols_50 WHERE f =  10;

ALTER TABLE alter_addcols_50 ADD COLUMN g int check(g > 2);
ALTER TABLE alter_addcols_50 ADD COLUMN g int default f + 11;

ALTER TABLE alter_addcols_50 ADD COLUMN g varchar(100) default 'chars less than 31 size';
INSERT INTO alter_addcols_50(a) VALUES(1);
SELECT COUNT(*) FROM alter_addcols_50;
SELECT * FROM alter_addcols_50;

INSERT INTO alter_addcols_50(a, d) VALUES(1, 4);
SELECT * FROM alter_addcols_50;

ALTER TABLE alter_addcols_50 ADD COLUMN h varchar(100) default 'i think this is a string whose length is greater than 31.';
SELECT * FROM alter_addcols_50;

ALTER TABLE alter_addcols_50 ADD COLUMN i varchar(100) NOT NULL DEFAULT 'i think this is a string whose length is greater than 31.';
SELECT * FROM alter_addcols_50;

INSERT INTO alter_addcols_50(a, d) VALUES(1, 7);
INSERT INTO alter_addcols_50(a, d) VALUES(1, 11);
INSERT INTO alter_addcols_50(a, d) VALUES(1, 13);
ALTER TABLE alter_addcols_50 ADD COLUMN j varchar(100) NOT NULL DEFAULT 'i think this is a string whose length is greater than 31.';
SELECT * FROM alter_addcols_50;

INSERT INTO alter_addcols_50(a, d) VALUES(1, 17);
ALTER TABLE alter_addcols_50 ADD COLUMN k time with time zone DEFAULT '2015-04-02 20:26:53 PST';
SELECT * FROM alter_addcols_50;

INSERT INTO alter_addcols_50(a, d) VALUES(1, 19);
SELECT * FROM alter_addcols_50;

ALTER TABLE alter_addcols_50 ADD COLUMN p interval DEFAULT 1100;
INSERT INTO alter_addcols_50(a, d) VALUES(1, 23);
SELECT * FROM alter_addcols_50;

ALTER TABLE alter_addcols_50 ADD COLUMN q interval DEFAULT '1 12:59:10';
INSERT INTO alter_addcols_50(a, d) VALUES(1, 29);
SELECT * FROM alter_addcols_50;

ALTER TABLE alter_addcols_50 ADD COLUMN l name DEFAULT 'Gauss MPPDB';
-- TODO: forbid SMALLSERIAL && SERIAL && BIGSERIA
ALTER TABLE alter_addcols_50 ADD COLUMN l smallserial;
ALTER TABLE alter_addcols_50 ADD COLUMN l serial;
ALTER TABLE alter_addcols_50 ADD COLUMN l bigserial;
ALTER TABLE alter_addcols_50 ADD COLUMN m clob;
ALTER TABLE alter_addcols_50 ADD COLUMN n blob;
ALTER TABLE alter_addcols_50 ADD COLUMN o macaddr;

drop schema cstore_alter_table cascade;