CREATE SCHEMA cstore_alter_table_6;
SET SEARCH_PATH TO cstore_alter_table_6;
SET INTERVALsTYLE = POSTGRES;
CREATE TABLE alter_addcols_101 (id int , info varchar(1100) not null, info1 int) WITH (ORIENTATION=COLUMN);
CREATE INDEX idx101_1 ON alter_addcols_101 (info);
CREATE INDEX idx101_2 ON alter_addcols_101 (info1);
INSERT INTO alter_addcols_101 VALUES (1,'aa',1),(2,'aa',2),(3,'aa',3),(4,4,4),(5,'aa',5);
SELECT * FROM alter_addcols_101 WHERE info='aa' ORDER BY 1, 3;
SELECT * FROM alter_addcols_101 WHERE info1>3 ORDER BY 1, 3;
ALTER TABLE alter_addcols_101 ALTER info TYPE varchar(1100);
SELECT * FROM alter_addcols_101 WHERE info='aa' ORDER BY 1, 3;
SELECT * FROM alter_addcols_101 WHERE info1>3 ORDER BY 1, 3;
-- rebuild all the indexes when rewrite the heap data.
CREATE TABLE alter_addcols_98 (id int , info varchar(1100) not null, info1 int) WITH (ORIENTATION=COLUMN);
CREATE INDEX idx98_1 ON alter_addcols_98 (info1);
INSERT INTO alter_addcols_98 VALUES (1,'aa',1),(2,'aa',2),(3,'aa',3),(4,4,4),(5,'aa',5);
SELECT * FROM alter_addcols_98 WHERE info='aa' ORDER BY 1, 3;
SELECT * FROM alter_addcols_98 WHERE info1>3 ORDER BY 1, 3;
ALTER TABLE alter_addcols_98 ALTER info1 TYPE varchar(15);
SELECT * FROM alter_addcols_98 WHERE info='aa' ORDER BY 1, 3;
SELECT * FROM alter_addcols_98 WHERE info1='3' ORDER BY 1, 3;
ALTER TABLE alter_addcols_98 ALTER info1 TYPE int;
SELECT * FROM alter_addcols_98 WHERE info='aa' ORDER BY 1, 3;
SELECT * FROM alter_addcols_98 WHERE info1='3' ORDER BY 1, 3;
CREATE TABLE alter_addcols_102 (id int , info varchar(1100) not null, info1 int) WITH (ORIENTATION=COLUMN)
partition by range(id)
(
partition p1 values less than (5),
partition p2 values less than (10)
);
CREATE INDEX idx102_1 ON alter_addcols_102 (info1) LOCAL;
INSERT INTO alter_addcols_102 VALUES (1,'aa',1),(2,'aa',2),(3,'aa',3),(4,4,4),(5,'aa',5);
SELECT * FROM alter_addcols_102 WHERE info='aa' ORDER BY 1, 3;
SELECT * FROM alter_addcols_102 WHERE info1>3 ORDER BY 1, 3;
ALTER TABLE alter_addcols_102 ALTER info1 TYPE varchar(15);
SELECT * FROM alter_addcols_102 WHERE info='aa' ORDER BY 1, 3;
SELECT * FROM alter_addcols_102 WHERE info1='3' ORDER BY 1, 3;
ALTER TABLE alter_addcols_102 ALTER info1 TYPE int;
SELECT * FROM alter_addcols_102 WHERE info='aa' ORDER BY 1, 3;
SELECT * FROM alter_addcols_102 WHERE info1='3' ORDER BY 1, 3;
DROP TABLE IF EXISTS base_copy_table ;
create table base_copy_table (c_hashid int, 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);
copy base_copy_table from '@abs_srcdir@/data/cstore_alter_change_plus_update.dat';
DROP TABLE IF EXISTS alter_addcols_103 ;
DROP TABLE IF EXISTS alter_addcols_104 ;
create table alter_addcols_103 (c_hashid int, 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)
partition by range(c_numeric)(
partition create_columnar_transaction_table_010_partition_s1 values less than(1),
partition create_columnar_transaction_table_010_partition_s2 values less than(3),
partition create_columnar_transaction_table_010_partition_s3 values less than(27),
partition create_columnar_transaction_table_010_partition_s4 values less than(1221),
partition create_columnar_transaction_table_010_partition_s5 values less than(861121),
partition create_columnar_transaction_table_010_partition_s6 values less than(maxvalue))
;
create table alter_addcols_104 (c_hashid int, 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)
partition by range(c_numeric)(
partition create_columnar_transaction_table_010_partition_s1 values less than(1),
partition create_columnar_transaction_table_010_partition_s2 values less than(3),
partition create_columnar_transaction_table_010_partition_s3 values less than(27),
partition create_columnar_transaction_table_010_partition_s4 values less than(1221),
partition create_columnar_transaction_table_010_partition_s5 values less than(861121),
partition create_columnar_transaction_table_010_partition_s6 values less than(maxvalue))
;
insert into alter_addcols_103 select * from base_copy_table;
insert into alter_addcols_104 select * from base_copy_table;
start transaction isolation level read committed;
alter table alter_addcols_103 alter column c_tinyint set data type int;
update alter_addcols_103 set c_tinyint=c_tinyint+10 where c_tinyint<10;
rollback;
(select * from alter_addcols_103 ) minus (select * from alter_addcols_104);
(select * from alter_addcols_104 ) minus (select * from alter_addcols_103);
start transaction isolation level read committed;
alter table alter_addcols_103 alter column c_tinyint set data type int;
select count(*) from alter_addcols_103 ;
rollback;
start transaction isolation level read committed;
alter table alter_addcols_103 alter column c_tinyint set data type int;
update alter_addcols_103 set c_tinyint=c_tinyint+10 where c_tinyint<10;
alter table alter_addcols_104 alter column c_tinyint set data type int;
alter table alter_addcols_104 alter column c_smallint set data type int;
rollback;
DROP TABLE IF EXISTS alter_addcols_105 ;
DROP TABLE IF EXISTS alter_addcols_106 ;
create table alter_addcols_105 (c_hashid int, 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) ;
create table alter_addcols_106 (c_hashid int, 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_105 select * from base_copy_table;
insert into alter_addcols_106 select * from base_copy_table;
start transaction isolation level read committed;
alter table alter_addcols_106 alter column c_tinyint set data type int;
update alter_addcols_106 set c_tinyint=c_tinyint+10 where c_tinyint<10;
rollback;
(select * from alter_addcols_106 ) minus (select * from alter_addcols_105);
(select * from alter_addcols_105 ) minus (select * from alter_addcols_106);
start transaction isolation level read committed;
alter table alter_addcols_106 alter column c_tinyint set data type int;
select count(*) from alter_addcols_106 ;
rollback;
start transaction isolation level read committed;
alter table alter_addcols_106 alter column c_tinyint set data type int;
update alter_addcols_106 set c_tinyint=c_tinyint+10 where c_tinyint<10;
alter table alter_addcols_105 alter column c_tinyint set data type int;
alter table alter_addcols_105 alter column c_smallint set data type int;
rollback;
-- support SET STATISTICS feature
CREATE TABLE alter_set_stat_00 (id int, info int) with (orientation = column);
ALTER TABLE alter_set_stat_00 ALTER COLUMN info SET STATISTICS 1000;
select attstattarget from pg_attribute where attrelid = ( select oid from pg_class where relname = 'alter_set_stat_00' ) and attnum = 2;
ALTER TABLE alter_set_stat_00 ALTER COLUMN info SET STATISTICS PERCENT 80;
select attstattarget from pg_attribute where attrelid = ( select oid from pg_class where relname = 'alter_set_stat_00' ) and attnum = 2;
DROP TABLE alter_set_stat_00;
drop schema cstore_alter_table_6 cascade;