mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-04-20 09:07:44 +03:00
189 lines
9.6 KiB
SQL
Executable File
189 lines
9.6 KiB
SQL
Executable File
-- test column data types
|
|
ALTER TABLE calpont.tbl_name ADD COLUMN col_name char(1);
|
|
ALTER TABLE tbl_name ADD COLUMN col_name char(2) engine=infinidb;
|
|
ALTER TABLE tbl_name ADD COLUMN col_name char(4) engine = infinidb;
|
|
ALTER TABLE tbl_name ADD COLUMN col_name char(8);
|
|
ALTER TABLE tbl_name ADD COLUMN col_name varchar(50);
|
|
ALTER TABLE tbl_name ADD COLUMN col_name bit;
|
|
ALTER TABLE tbl_name ADD COLUMN col_name bit(8);
|
|
ALTER TABLE tbl_name ADD COLUMN col_name bit(63);
|
|
ALTER TABLE tbl_name ADD COLUMN col_name real(5);
|
|
ALTER TABLE tbl_name ADD COLUMN col_name real(10,2);
|
|
ALTER TABLE tbl_name ADD COLUMN col_name decimal(1);
|
|
ALTER TABLE tbl_name ADD COLUMN col_name decimal(2,2);
|
|
ALTER TABLE tbl_name ADD COLUMN col_name decimal(5,4);
|
|
ALTER TABLE tbl_name ADD COLUMN col_name decimal(10,8);
|
|
ALTER TABLE tbl_name ADD COLUMN col_name float(25);
|
|
ALTER TABLE tbl_name ADD COLUMN col_name float(25,10);
|
|
ALTER TABLE tbl_name ADD COLUMN col_name double;
|
|
ALTER TABLE tbl_name ADD COLUMN col_name int;
|
|
ALTER TABLE tbl_name ADD COLUMN col_name bigint;
|
|
ALTER TABLE tbl_name ADD COLUMN col_name medint;
|
|
ALTER TABLE tbl_name ADD COLUMN col_name smallint;
|
|
ALTER TABLE tbl_name ADD COLUMN col_name tinyint;
|
|
ALTER TABLE tbl_name ADD COLUMN col_name date;
|
|
ALTER TABLE tbl_name ADD COLUMN col_name datetime;
|
|
ALTER TABLE tbl_name ADD COLUMN col_name clob;
|
|
ALTER TABLE tbl_name ADD COLUMN col_name blob;
|
|
ALTER TABLE tbl_name ADD COLUMN col_name numeric(7);
|
|
ALTER TABLE tbl_name ADD COLUMN col_name numeric(1,1);
|
|
ALTER TABLE tbl_name ADD COLUMN col_name numeric(8,1);
|
|
ALTER TABLE tbl_name ADD COLUMN col_name numeric(20,10);
|
|
ALTER TABLE tbl_name ADD COLUMN col_name number;
|
|
ALTER TABLE tbl_name ADD COLUMN col_name integer;
|
|
ALTER TABLE tbl_name ADD col_name int;
|
|
ALTER TABLE tbl_name ADD col_name CHAR (4);
|
|
|
|
|
|
-- test column constraints
|
|
ALTER TABLE calpont.tbl_name ADD COLUMN col_name datetime not null;
|
|
ALTER TABLE tbl_name ADD COLUMN col_name float(25,10) null engine=infinidb;
|
|
|
|
ALTER TABLE tbl_name ADD COLUMN col_name int auto_increment;
|
|
|
|
ALTER TABLE tbl_name ADD COLUMN col_name decimal(10,2) default 1;
|
|
ALTER TABLE tbl_name ADD COLUMN col_name char(1) default 'unknown';
|
|
ALTER TABLE tbl_name ADD COLUMN col_name char(1) default USER;
|
|
ALTER TABLE tbl_name ADD COLUMN col_name char(1) default CURRENT_USER;
|
|
ALTER TABLE tbl_name ADD COLUMN col_name char(1) default SESSION_USER;
|
|
ALTER TABLE tbl_name ADD COLUMN col_name char(1) default SYSTEM_USER;
|
|
ALTER TABLE tbl_name ADD COLUMN col_name char(1) default NULL;
|
|
|
|
ALTER TABLE tbl_name ADD COLUMN col_name clob check (col_name < 0),
|
|
alter table tbl_name add column col_name int check (col_name > 1) initially deferred deferrable;
|
|
alter table tbl_name add column col_name integer check (col_name = 1) initially immediate not deferrable;
|
|
|
|
ALTER TABLE tbl_name ADD COLUMN col_name numeric(5,2) primary key;
|
|
ALTER TABLE tbl_name ADD COLUMN col_name numeric(25,2) primary key disabled;
|
|
ALTER TABLE tbl_name ADD COLUMN col_name numeric(25) primary key DEFERRED;
|
|
|
|
ALTER TABLE tbl_name ADD COLUMN col_name int references tbl_name1(col_name);
|
|
ALTER TABLE tbl_name ADD COLUMN col_name int references tbl_name1(col_name, col_name1);
|
|
|
|
ALTER TABLE tbl_name ADD COLUMN col_name int unique;
|
|
ALTER TABLE tbl_name ADD COLUMN col_name decimal(8,2) unique DISABLE;
|
|
ALTER TABLE tbl_name ADD COLUMN col_name decimal(8) unique DEFERRED;
|
|
|
|
|
|
-- test out-of-line constraints
|
|
ALTER TABLE calpont.tbl_name ADD CONSTRAINT const_name check (col_name) (col_name > 0);
|
|
alter table tbl_name add check (col_name) (col_name > 0) DEFERRED engine=infinidb;
|
|
alter table tbl_name add const_name check (col_name) (col_name > 0) DISABLE;
|
|
|
|
alter table tbl_name add CONSTRAINT const_name primary key (col_name);
|
|
alter table tbl_name add const_name primary key(col_name);
|
|
alter table tbl_name add primary key (col_name);
|
|
alter table tbl_name add const_name primary key (col_nam,col_name1);
|
|
|
|
alter table tbl_name add CONSTRAINT const_name unique(col_name);
|
|
alter table tbl_name add unique(col_name);
|
|
alter table tbl_name add const_name unique col_name DISABLE;
|
|
alter table tbl_name add const_name unique col_name DEFERRED;
|
|
alter table tbl_name add const_name unique (col_name, col_name1);
|
|
|
|
ALTER TABLE tbl_name add CONSTRAINT const_name foreign key(col_name) references Customers( p_a );
|
|
ALTER TABLE tbl_name ADD FOREIGN KEY(col_name) references Customers( p_a );
|
|
alter table tbl_name add foreign key (col_name,col_name1) references Customers( p_a );
|
|
alter table tbl_name add foreign key (col_name,col_name1) references Customers( p_a,p_b ) MATCH FULL;
|
|
alter table tbl_name add foreign key (col_name,col_name1) references Customers( p_a ) MATCH PARTIAL;
|
|
alter table tbl_name add foreign key (col_name,col_name1) references Customers( p_a ) ON UPDATE CASCADE;
|
|
alter table tbl_name add foreign key (col_name,col_name1) references Customers( p_a ) ON UPDATE SET NULL;
|
|
alter table tbl_name add foreign key (col_name,col_name1) references Customers( p_a ) ON UPDATE SET DEFAULT;
|
|
alter table tbl_name add foreign key (col_name,col_name1) references Customers( p_a ) ON UPDATE NO ACTION;
|
|
alter table tbl_name add foreign key (col_name,col_name1) references Customers( p_a ) ON DELETE CASCADE;
|
|
alter table tbl_name add foreign key (col_name,col_name1) references Customers( p_a ) ON DELETE SET NULL;
|
|
alter table tbl_name add foreign key (col_name,col_name1) references Customers( p_a ) ON DELETE SET DEFAULT;
|
|
alter table tbl_name add foreign key (col_name,col_name1) references Customers( p_a ) ON DELETE NO ACTION;
|
|
alter table tbl_name add foreign key (col_name,col_name1) references Customers( p_a );
|
|
|
|
ALTER TABLE tbl_name storage(initial 100000 next 200000);
|
|
|
|
|
|
-- test contsraint state
|
|
alter table calpont.tbl_name DISABLE constraint const_name;
|
|
alter table tbl_name ENABLE constraint const_name engine=infinidb;
|
|
|
|
|
|
-- test alter column
|
|
alter table calpont.tbl_name alter col_name drop not null;
|
|
alter table tbl_name alter col_name drop null engine=infinidb;
|
|
alter table tbl_name alter col_name drop default;
|
|
alter table tbl_name alter col_name drop auto_increment;
|
|
ALTER TABLE tbl_name ALTER COLUMN col_name DROP DEFAULT;
|
|
|
|
alter table tbl_name alter col_name set not null;
|
|
alter table tbl_name alter col_name set null;
|
|
alter table tbl_name alter col_name set default 3;
|
|
alter table tbl_name alter col_name set default 'unknown';
|
|
alter table tbl_name alter col_name set default USER;
|
|
alter table tbl_name alter col_name set default CURRENT_USER;
|
|
alter table tbl_name alter col_name set default SESSION_USER;
|
|
alter table tbl_name alter col_name set default SYSTEM_USER;
|
|
alter table tbl_name alter col_name set default NULL;
|
|
alter table tbl_name alter col_name set auto_increment;
|
|
ALTER TABLE tbl_name ALTER COLUMN col_name SET DEFAULT;
|
|
|
|
|
|
-- test drop column
|
|
alter table calpont.tbl_name drop col_name engine=infinidb;
|
|
ALTER TABLE tbl_name DROP col_name CASCADE;
|
|
ALTER TABLE tbl_name DROP col_name RESTRICT;
|
|
ALTER TABLE tbl_name DROP col_name INVALIDATE;
|
|
ALTER TABLE tbl_name DROP col_name CASCADE CONSTRAINTS;
|
|
ALTER TABLE tbl_name DROP ( col_name , col_name1) CASCADE CONSTRAINTS;
|
|
alter table tbl_name drop column col_name;
|
|
|
|
|
|
-- test rename column and table
|
|
alter table calpont.tbl_name rename col_name to col_name1;
|
|
alter table tbl_name rename column col_name to col_name1 engine=infinidb;
|
|
ALTER TABLE tbl_name RENAME COLUMN col_name to col_name1;
|
|
ALTER TABLE tbl_name RENAME to new_tbl_name ;
|
|
|
|
|
|
-- test set column
|
|
alter table tbl_name set unused col_name;
|
|
alter table tbl_name set unused (col_name,col_name1);
|
|
|
|
|
|
-- test MAX name sizes
|
|
ALTER TABLE tbl_name0123456789012345678901234567890 ADD COLUMN col_name char(1);
|
|
ALTER TABLE tbl_name ADD COLUMN col_name90123456789012345678901234567890 char(1);
|
|
ALTER TABLE schema7890123456789012345678901234567890.tbl_name ADD COLUMN col_name char(1);
|
|
ALTER TABLE tbl_name ADD CONSTRAINT const_name123456789012345678901234567890 CHECK(col_name > 1);
|
|
ALTER TABLE schema7890123456789012345678901234567890.tbl_name0123456789012345678901234567890 ADD CONSTRAINT const_name123456789012345678901234567890 CHECK(col_name90123456789012345678901234567890 > 1);
|
|
|
|
|
|
-- test combination request
|
|
ALTER TABLE tbl_name ADD (col_name int, col_name1 timestamp, col_name2 NUMERIC(7));
|
|
ALTER TABLE tbl_name ADD COLUMN col_name int null references Customers(col_name);
|
|
ALTER TABLE tbl_name ADD COLUMN col_name int NOT null auto_increment default 1000 references tbl_name1(col_name);
|
|
ALTER TABLE tbl_name ADD COLUMN col_name clob not null check (col_name < 0),
|
|
ALTER TABLE tbl_name ADD COLUMN col_name numeric(25,2) primary key unique check (col_name = 50);
|
|
ALTER TABLE tbl_name ADD COLUMN col_name numeric(25,2) primary key unique check (col_name = 50) disable references tbl_name2(col_name1) DEFERRED;
|
|
ALTER TABLE tbl_name ADD COLUMN col_name real(10,2) ADD CONSTRAINT const_name (p_partkey) (p_partkey > 0);
|
|
ALTER TABLE tbl_name ADD COLUMN col_name float(10,2) NOT null auto_increment default 1000 primary key DEFERRED unique DISABLED check (col_name = 50) references tbl_name1(col_name) engine=infinidb;
|
|
|
|
|
|
-- test big do all test
|
|
ALTER TABLE calpont.tbl_name
|
|
ADD (
|
|
col_name float(10,2) NOT null auto_increment default 1000 primary key DEFERRED unique DISABLED check (col_name = 50) references tbl_name1(col_name),
|
|
col_name1 timestamp ,
|
|
col_name2 NUMERIC(7)
|
|
)
|
|
ADD CONSTRAINT const_name check (col_name2) (col_name2 > 0)
|
|
add CONSTRAINT const_name1 primary key (col_name2)
|
|
add CONSTRAINT const_name2 unique(col_name1)
|
|
add CONSTRAINT const_name3 foreign key(col_name) references Customers( p_a )
|
|
storage(initial 100000 next 200000)
|
|
DISABLE constraint const_name
|
|
alter col_name drop not null
|
|
alter table tbl_name alter col_name2 set default 'unknown'
|
|
DROP ( col_name , col_name1) CASCADE CONSTRAINTS
|
|
RENAME COLUMN col_name to col_name1;
|
|
RENAME to new_tbl_name
|
|
alter table tbl_name set unused (col_name,col_name1)
|
|
engine=infinidb;
|
|
|