CREATE SCHEMA cstore_alter_table_5;
SET SEARCH_PATH TO cstore_alter_table_5;
SET INTERVALsTYLE = POSTGRES;

CREATE TABLE alter_addcols_91 ( a int , c int, b varchar(50) not null);
INSERT INTO alter_addcols_91 VALUES (generate_series(1,10), 1, 'helloworldhelloworldhelloworldhelloworldhello');
INSERT INTO alter_addcols_91 SELECT * FROM alter_addcols_91;
INSERT INTO alter_addcols_91 SELECT * FROM alter_addcols_91;
INSERT INTO alter_addcols_91 SELECT * FROM alter_addcols_91;
INSERT INTO alter_addcols_91 SELECT * FROM alter_addcols_91;
INSERT INTO alter_addcols_91 SELECT * FROM alter_addcols_91;
INSERT INTO alter_addcols_91 SELECT * FROM alter_addcols_91;
INSERT INTO alter_addcols_91 SELECT * FROM alter_addcols_91;
CREATE TABLE alter_addcols_92 ( a int , c int, b varchar(50) not null);
INSERT INTO alter_addcols_92 VALUES (generate_series(1,10), 2, 'zhangxuefuzhangxuefuzhangxuefuzhangxuefuzhang');
INSERT INTO alter_addcols_92 SELECT * FROM alter_addcols_92;
INSERT INTO alter_addcols_92 SELECT * FROM alter_addcols_92;
INSERT INTO alter_addcols_92 SELECT * FROM alter_addcols_92;
INSERT INTO alter_addcols_92 SELECT * FROM alter_addcols_92;
INSERT INTO alter_addcols_92 SELECT * FROM alter_addcols_92;
INSERT INTO alter_addcols_92 SELECT * FROM alter_addcols_92;
INSERT INTO alter_addcols_92 SELECT * FROM alter_addcols_92;
CREATE TABLE alter_addcols_93 ( a int , c int, b varchar(50) not null ) WITH (ORIENTATION = column, COMPRESSION = no);
-- cu1 size > 1024
INSERT INTO alter_addcols_93 SELECT * FROM alter_addcols_91;
INSERT INTO alter_addcols_93 SELECT * FROM alter_addcols_92;
-- delete the whole cu1
DELETE FROM alter_addcols_93 WHERE c = 1;
-- expect that the new data will use the cu1's space.
ALTER TABLE alter_addcols_93 ALTER COLUMN b SET DATA TYPE varchar(49);
ALTER TABLE alter_addcols_93 ALTER COLUMN b SET DATA TYPE varchar(48);
-- expect the new data, but not the cu1's data.
SELECT DISTINCT * FROM alter_addcols_93 order by a,c;

-- test: estate var must be global
CREATE TABLE alter_addcols_94 (
c_tinyint  tinyint,
c_smallint smallint,
c_int integer,
c_bigint   bigint,
c_money    money,
c_numeric   numeric,
c_real      real,
c_double double precision,
c_decimal   decimal,
c_varchar   varchar,
c_char   char(30),
c_nvarchar2  nvarchar2,
c_text text,
c_timestamp   timestamp with time zone,
c_timestamptz timestamp without time zone,
c_date     date,
c_time     time without time zone,
c_timetz   time with time zone,
c_interval  interval,
c_tinterval   tinterval,
c_smalldatetime   smalldatetime,
c_bytea   bytea,
c_boolean  boolean,
c_inet inet,
c_cidr cidr,
c_bit bit(10),
c_varbit varbit(10),
c_oid oid) with (orientation=column);
INSERT INTO alter_addcols_94 (c_varchar) VALUES(1);
INSERT INTO alter_addcols_94 (c_varchar) VALUES(2);
ALTER TABLE  IF EXISTS alter_addcols_94 MODIFY (c_varchar interval);
SELECT c_varchar FROM alter_addcols_94;

-- test: index is empty when table data will not be rewrited.
CREATE TABLE alter_addcols_95 (id int , info varchar(1100) not null, info1 int) WITH (ORIENTATION=COLUMN);
CREATE INDEX idx95_1 ON alter_addcols_95 (info);
INSERT INTO alter_addcols_95 VALUES (1,'aa',1),(2,'aa',2),(3,'aa',3),(4,4,4),(5,'aa',5);
SELECT * FROM  alter_addcols_95 WHERE info='aa' ORDER BY 1, 3;
ALTER TABLE alter_addcols_95 ALTER info TYPE varchar(1100);
SELECT * FROM  alter_addcols_95 WHERE info='aa' ORDER BY 1, 3;

CREATE TABLE alter_addcols_99 (id int , info varchar(1100) not null, info1 int) WITH (ORIENTATION=COLUMN)
partition by range(info1)
(
partition p1 values less than (5),
partition p2 values less than (10)
);
CREATE INDEX idx99_1 ON alter_addcols_99 (info) LOCAL;
INSERT INTO alter_addcols_99 VALUES (1,'aa',1),(2,'aa',2),(3,'aa',3),(4,4,4),(5,'aa',5);
SELECT * FROM  alter_addcols_99 WHERE info='aa' ORDER BY 1, 3;
ALTER TABLE alter_addcols_99 ALTER info TYPE varchar(1100);
SELECT * FROM  alter_addcols_99 WHERE info='aa' ORDER BY 1, 3;

-- successfull to handle the continuous two ALTER TABLE same action.
CREATE TABLE alter_addcols_96 (id int , info varchar(1100) not null, info1 int) WITH (ORIENTATION=COLUMN);
CREATE INDEX idx96_1 ON alter_addcols_96 (info);
INSERT INTO alter_addcols_96 VALUES (1,'aa',1),(2,'aa',2),(3,'aa',3),(4,4,4),(5,'aa',5);
SELECT * FROM  alter_addcols_96 WHERE info='aa' ORDER BY 1, 3;
ALTER TABLE alter_addcols_96 ALTER info TYPE varchar(1100);
SELECT * FROM  alter_addcols_96 WHERE info='aa' ORDER BY 1, 3;
ALTER TABLE alter_addcols_96 ALTER info TYPE varchar(1100);
SELECT * FROM  alter_addcols_96 WHERE info='aa' ORDER BY 1, 3;
DROP TABLE alter_addcols_96;

CREATE TABLE alter_addcols_100 (id int , info varchar(1100) not null, info1 int) WITH (ORIENTATION=COLUMN)
partition by range(info1)
(
partition p1 values less than (5),
partition p2 values less than (10)
);
CREATE INDEX idx100_1 ON alter_addcols_100 (info) LOCAL;
INSERT INTO alter_addcols_100 VALUES (1,'aa',1),(2,'aa',2),(3,'aa',3),(4,4,4),(5,'aa',5);
SELECT * FROM  alter_addcols_100 WHERE info='aa' ORDER BY 1, 3;
ALTER TABLE alter_addcols_100 ALTER info TYPE varchar(1100);
SELECT * FROM  alter_addcols_100 WHERE info='aa' ORDER BY 1, 3;
ALTER TABLE alter_addcols_100 ALTER info TYPE varchar(1100);
SELECT * FROM  alter_addcols_100 WHERE info='aa' ORDER BY 1, 3;

-- successfull to handle more than one index
CREATE TABLE alter_addcols_97 (id int , info varchar(1100) not null, info1 int) WITH (ORIENTATION=COLUMN)
partition by range(info1)
(
partition p1 values less than (5),
partition p2 values less than (10)
);
CREATE INDEX idx97_1 ON alter_addcols_97 (info) LOCAL;
CREATE INDEX idx97_2 ON alter_addcols_97 (info1) LOCAL;
INSERT INTO alter_addcols_97 VALUES (1,'aa',1),(2,'aa',2),(3,'aa',3),(4,4,4),(5,'aa',5);
SELECT * FROM  alter_addcols_97 WHERE info='aa' ORDER BY 1, 3;
SELECT * FROM  alter_addcols_97 WHERE info1>3 ORDER BY 1, 3;
ALTER TABLE alter_addcols_97 ALTER info TYPE varchar(1100);
SELECT * FROM  alter_addcols_97 WHERE info='aa' ORDER BY 1, 3;
SELECT * FROM  alter_addcols_97 WHERE info1>3 ORDER BY 1, 3;

drop schema cstore_alter_table_5 cascade;