github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/pessimistic_transaction/unique_secondary_index.sql (about) 1 -- create unique/secondary index all type 2 create table index_01 (col1 bigint primary key,col2 varchar(25),col3 float,col4 varchar(50),unique key num_phone(col2),key num_id(col4)); 3 insert into index_01 values (1,'13456789872',20.23,'5678'),(2,'13873458290',100.00,'23'),(3,NULL,100.00,'23'); 4 insert into index_01 values (67834,'13456789872',20.23,'4090'),(56473,'13456789872',100.00,'5678'); 5 insert into index_01 values (4,'13866666666',20.23,'5678'),(5,'13873458290',100.00,'23'),(6,'13777777777',100.00,'23'); 6 select col2,col4 from index_01; 7 create table index_02 (col1 bigint primary key,col2 char(25) unique key,col3 float,col4 char(50),key num_id(col4)); 8 insert into index_02 values (67834,'13456789872',20.23,'5678'),(56473,'',100.00,'5678'); 9 insert into index_02 values (1,'',20.23,'5678'),(2,'13873458290',100.00,'23'); 10 insert into index_02 values (67834,'13456799878',20.23,'4090'),(56473,NULL,100.00,''); 11 insert into index_02 values (3,'excel',0.1,'4090'),(4,'中文',0.2,''),(5,'MMEabc$%^123',0.2,''); 12 select col2,col4 from index_02; 13 select * from index_02 where col2="MMEabc$%^123"; 14 create table index_03 (col1 bigint auto_increment primary key,col2 int,col3 float,col4 int,unique key id1(col2),key id2(col4)); 15 insert into index_03(col2,col3,col4) values (10,20.23,4090),(10,100.00,5678); 16 insert into index_03(col2,col3,col4) values (10,20.23,4090),(11,100.00,4090); 17 insert into index_03(col2,col3,col4) values (67834,20.23,4090),(56473,100.00,5678),(NULL,0.01,NULL); 18 insert into index_03(col2,col3,col4) values (-2147483648,1.2,100),(2147483647,2.0,5); 19 select * from index_03; 20 select * from index_03 where col2=-2147483648; 21 create table index_04 (col1 bigint,col2 int primary key,col3 float,col4 bigint,unique key id1(col1),key id2(col4)); 22 insert into index_04 values (67834,2,20.23,4090),(67834,4,100.00,4091); 23 insert into index_04 values (1,2,20.23,4090),(2,4,100.00,4091),(NULL,3,0.01,NULL); 24 -- @pattern 25 insert into index_04 values (3,2,20.23,4090),(2,4,100.00,4091),(4,4,100.00,4090); 26 select * from index_04; 27 select * from index_04 where col1 between 10 and 1000000; 28 create table index_05 (col1 smallint unique key,col2 int primary key,col3 float,col4 smallint,key id2(col4)); 29 insert into index_05 values (1,2,20.23,4090),(1,4,100.00,4091); 30 insert into index_05 values (1,2,20.23,4090),(2,4,100.00,4091),(NULL,3,0.01,NULL); 31 select * from index_05; 32 create table index_06 (col1 tinyint,col2 int primary key,col3 float,col4 tinyint,unique key id1(col1),key id2(col4)); 33 insert into index_06 values (1,2,20.23,56),(1,4,100.00,90); 34 insert into index_06 values (1,2,20.23,56),(2,4,100.00,41),(NULL,3,0.01,NULL); 35 select * from index_06; 36 create table index_07 (col1 int unsigned,col2 int primary key,col3 float,col4 int unsigned,unique key id1(col1),key id2(col4)); 37 insert into index_07 values (1,2,20.23,56),(2,4,100.00,41),(NULL,3,0.01,NULL); 38 -- @pattern 39 insert into index_07 values (1,2,20.23,56),(1,4,100.00,90); 40 select * from index_07; 41 create table index_08 (col1 bigint unsigned,col2 int primary key,col3 float,col4 bigint unsigned,unique key id1(col1),key id2(col4)); 42 insert into index_08 values (1,2,20.23,56),(1,4,100.00,90); 43 insert into index_08 values (1,2,20.23,56),(2,4,100.00,41),(NULL,3,0.01,NULL); 44 select * from index_08; 45 create table index_09 (col1 bigint primary key,col2 decimal(4,2),col3 decimal(4,2),unique key d1(col2),key d2(col3)); 46 insert into index_09 values (1000,20.23,20.00),(1200,20.23,0.10); 47 insert into index_09 values (1000,20.23,20.00),(1200,0.23,20.10),(1100,NULL,NULL); 48 select * from index_09; 49 create table index_10 (col1 bigint primary key,col2 float,col3 float,unique key d1(col2),key d2(col3)); 50 insert into index_10 values (1000,20.23,20.00),(1200,20.23,0.10); 51 insert into index_10 values (1000,20.23,20.00),(1200,0.23,20.10),(1100,NULL,NULL); 52 select * from index_10; 53 create table index_11 (col1 bigint primary key,col2 double,col3 double,unique key d1(col2),key d2(col3)); 54 insert into index_11 values (1000,20.23,20.00),(1200,20.23,0.10); 55 insert into index_11 values (1000,20.23,20.00),(1200,0.23,20.10),(1100,NULL,NULL); 56 select * from index_11; 57 create table index_12 (col1 bigint auto_increment primary key,col2 date,col3 date,unique key d1(col2),key d2(col3)); 58 insert into index_12(col2,col3) values ('2013-01-01','2014-02-01'),('2013-01-01','2014-02-20'); 59 insert into index_12(col2,col3) values (NULL,'2014-02-01'),(NULL,NULL); 60 select col2 from index_12; 61 create table index_13 (col1 bigint auto_increment primary key,col2 datetime,col3 datetime,unique key d1(col2),key d2(col3)); 62 insert into index_13(col2,col3) values ('2013-01-01 12:00:00','2014-02-01 10:00:00'),('2013-01-01 12:00:00','2014-02-20 05:00:00'); 63 insert into index_13(col2,col3) values (NULL,'2014-02-01 12:00:0'),(NULL,NULL); 64 create table index_14 (col1 bigint auto_increment primary key,col2 timestamp,col3 timestamp,unique key d1(col2),key d2(col3)); 65 insert into index_14(col2,col3) values ('2013-01-01 12:00:00','2014-02-01 10:00:00'),('2013-01-01 12:00:00','2014-02-20 05:00:00'); 66 insert into index_14(col2,col3) values (NULL,'2014-02-01 12:00:0'),(NULL,NULL); 67 create table index_15 (col1 bigint primary key,col2 bool,unique key c2(col2)); 68 -- @pattern 69 insert into index_15 values (1,TRUE),(2,FALSE),(3,TRUE); 70 insert into index_15 values (1,TRUE),(2,FALSE),(3,NULL); 71 select * from index_15; 72 -- blob/json/text type not support unique index 73 create table index_16 (col1 bigint primary key,col2 blob,col3 blob,unique key d1(col2),key d2(col3)); 74 create table index_17 (col1 bigint primary key,col2 json,col3 json,unique key d1(col2),key d2(col3)); 75 create table index_18 (col1 bigint primary key,col2 text,col3 text,unique key d1(col2),key d2(col3)); 76 77 --unique index name test 78 create table index_name (col1 bigint primary key,col2 varchar(25),col3 float,col4 varchar(50),unique key 123(col2),key num_id(col4)); 79 create table index_name (col1 bigint primary key,col2 varchar(25),col3 float,col4 varchar(50),unique key abs@123.abc(col2),key num_id(col4)); 80 create table index_name (col1 bigint primary key,col2 varchar(25),col3 float,col4 varchar(50),unique key default(col2),key num_id(col4)); 81 create table index_name (col1 bigint primary key,col2 varchar(25),col3 float,col4 varchar(50),unique key `default`(col2),key num_id(col4)); 82 drop table index_name; 83 create table index_name (col1 bigint primary key,col2 varchar(25),col3 float,col4 varchar(50),unique key `123`(col2),key num_id(col4)); 84 drop table index_name; 85 create table index_name (col1 bigint primary key,col2 varchar(25),col3 float,col4 varchar(50),unique key `abs@123.abc`(col2),key num_id(col4)); 86 drop table index_name; 87 88 --unique index include null and not null,'' 89 create table index_table_01 (col1 bigint auto_increment primary key,col2 varchar(25),col3 float default 87.01,col4 int,unique key num_phone(col2),key num_id(col4)); 90 insert into index_table_01 values (67834,'13456789872',20.23,4090),(56473,'',100.00,5678); 91 insert into index_table_01 values (34,NULL,4090,1); 92 insert into index_table_01 values (56478,'',103.00,5670); 93 select * from index_table_01; 94 drop table index_table_01; 95 96 create table index_table_02 (col1 bigint primary key,col2 varchar(25) not null,col3 float default 87.01,col4 int,unique key num_phone(col2),key num_id(col4)); 97 insert into index_table_02 values (34,NULL,4090,1); 98 drop table index_table_02; 99 100 --unique index default 101 create table index_table_02 (col1 bigint primary key,col2 int default 0,unique key col2(col2)); 102 insert into index_table_02(col1) values (1); 103 insert into index_table_02(col1) values (2),(3); 104 drop table index_table_02; 105 106 --only unique key 107 create table index_table_03 (col1 bigint,col2 int,unique key col2(col2)); 108 insert into index_table_03 values (1,20),(2,NULL),(3,90); 109 update index_table_03 set col2=10 where col2 is NULL; 110 select * from index_table_03; 111 drop table index_table_03; 112 113 --load infile ,insert select 114 CREATE TABLE IF NOT EXISTS `t_code_rule` ( 115 `code_id` bigint(20) NOT NULL , 116 `code_no` varchar(50) NOT NULL, 117 `org_no` varchar(50) NOT NULL, 118 `org_name` varchar(50) NOT NULL, 119 `ancestors` varchar(255) NOT NULL, 120 `code_rule_no` varchar(50) NOT NULL, 121 `code_rule_name` varchar(50) NOT NULL, 122 `code_name` varchar(50) NOT NULL, 123 `code_type` int(11), 124 `split` varchar(50) DEFAULT NULL, 125 `remark` varchar(255), 126 `create_time` datetime NOT NULL, 127 `create_user` varchar(50) DEFAULT NULL, 128 `last_update_time` datetime DEFAULT NULL, 129 `last_update_user` varchar(50) DEFAULT NULL, 130 `is_system_code` varchar(20) NOT NULL DEFAULT 'N', 131 PRIMARY KEY (`code_id`), 132 UNIQUE KEY `code_type` (`code_type`), 133 KEY `code_no` (`code_no`), 134 KEY `code_rule_no` (`code_rule_no`), 135 KEY `org_no` (`org_no`) 136 ); 137 show create table t_code_rule; 138 load data infile '$resources/load_data/unique_index_file.csv' into table t_code_rule fields terminated by ','; 139 select code_id,code_type,code_no,code_rule_no,org_no from t_code_rule; 140 truncate table t_code_rule; 141 -- @bvt:issue#3433 142 load data infile '$resources/load_data/unique_index_duplicate.csv' into table t_code_rule fields terminated by ','; 143 select code_id,code_type,code_no,code_rule_no,org_no from t_code_rule; 144 create table index_temp( col1 bigint(20) NOT NULL ,col2 varchar(50) NOT NULL,col3 varchar(50) NOT NULL,col4 varchar(50) NOT NULL,col5 varchar(255) NOT NULL,col6 varchar(50) NOT NULL,col7 varchar(50) NOT NULL,col8 varchar(50) NOT NULL,col9 int(11) ,col10 varchar(50) DEFAULT NULL,col11 varchar(255),col12 datetime NOT NULL,col13 varchar(50) DEFAULT NULL,col14 datetime DEFAULT NULL,col15 varchar(50) DEFAULT NULL,col16 varchar(20) NOT NULL DEFAULT 'N'); 145 load data infile '$resources/load_data/unique_index_file.csv' into table index_temp fields terminated by ','; 146 insert into t_code_rule select * from index_temp; 147 select code_id,code_type,code_no,code_rule_no,org_no from t_code_rule; 148 truncate table index_temp; 149 load data infile '$resources/load_data/unique_index_duplicate.csv' into table index_temp fields terminated by ','; 150 -- @pattern 151 insert into t_code_rule select * from index_temp; 152 -- @bvt:issue 153 154 --unique index more column 155 create table index_table_04 (col1 bigint not null auto_increment,col2 varchar(25),col3 int,col4 varchar(50),primary key (col1),unique key m1(col2,col3),key num_id(col4)); 156 insert into index_table_04(col2,col3,col4) select 'apple',1,'10'; 157 insert into index_table_04(col2,col3,col4) select 'apple',2,'11'; 158 insert into index_table_04(col2,col3,col4) select 'apple',2,'12'; 159 insert into index_table_04(col2,col3,col4) select NULL,NULL,'13'; 160 select * from index_table_04; 161 drop table index_table_04; 162 create table index_table_04 (col1 bigint not null auto_increment,col2 varchar(25),col3 int,col4 varchar(50),primary key (col1),unique key m1(col2),unique key m2(col3),key num_id(col4)); 163 insert into index_table_04(col2,col3,col4) select 'apple',1,'10'; 164 insert into index_table_04(col2,col3,col4) select 'apple',2,'11'; 165 insert into index_table_04(col2,col3,col4) select 'apple',2,'12'; 166 insert into index_table_04(col2,col3,col4) select NULL,NULL,'13'; 167 select * from index_table_04; 168 169 --unique key update/delete/truncate and update duplicate data 170 create table index_table_05 (col1 bigint not null auto_increment,col2 varchar(25),col3 int,col4 varchar(50),primary key (col1),unique key col2(col2),key num_id(col4)); 171 insert into index_table_05(col2,col3,col4) values ('apple',1,'10'),('store',2,'11'),('bread',3,'12'); 172 select * from index_table_05; 173 update index_table_05 set col2='chart' where col1=2; 174 select col2 from index_table_05; 175 update index_table_05 set col2='bread' where col1=1; 176 select * from index_table_05; 177 delete from index_table_05 where col2='apple'; 178 select * from index_table_05; 179 truncate table index_table_05; 180 insert into index_table_05(col2,col3,col4) values ('apple',1,'10'),('store',2,'11'),('apple',3,'12'); 181 select * from index_table_05; 182 drop table index_table_05; 183 184 --rename table 185 create table index_table_05 (col1 bigint not null auto_increment,col2 varchar(25),col3 int,col4 varchar(50),primary key (col1),unique key col2(col2),key num_id(col4)); 186 insert into index_table_05(col2,col3,col4) values ('apple',1,'10'),('store',2,'11'),('bread',3,'12'); 187 --rename table index_table_05 to index_table_05_new; 188 --insert into index_table_05_new(col2,col3,col4) values ('apple',4,'13'); 189 --select * from index_table_05_new; 190 191 --only one/more key column 192 create table index_table_06 (col1 bigint not null auto_increment,col2 varchar(25),col3 int,col4 varchar(50),primary key (col1),key num_id(col4)); 193 insert into index_table_06(col2,col3,col4) values ('apple',1,'10'),('store',2,'11'),('bread',3,'12'); 194 select * from index_table_06; 195 drop table index_table_06; 196 create table index_table_06 (col1 bigint not null auto_increment,col2 varchar(25),col3 int default 10,col4 varchar(50),primary key (col1),key col2(col2),key col3(col3)); 197 insert into index_table_06(col2,col3,col4) values ('apple',1,'10'),('store',2,'11'),('bread',2,'12'); 198 insert into index_table_06(col2,col3,col4) values ('read',1,'10'),('write',2,'11'),('bread',3,'10'); 199 insert into index_table_06(col2,col3,col4) values ('read',NULL,'10'),('write',NULL,NULL); 200 select col2,col3 from index_table_06; 201 202 --abnormal test :create not exists unique/secondary index column 203 create table index_table_07 (col1 bigint not null auto_increment,col2 varchar(25),col3 int,col4 varchar(50),primary key (col1),unique key col10(col10),key num_id(col4)); 204 create table index_table_07 (col1 bigint not null auto_increment,col2 varchar(25),col3 int,col4 varchar(50),primary key (col1),unique key col2(col2),key num_id(col40)); 205 206 --create unique index 207 create table create_index_01 (col1 bigint primary key,col2 varchar(25),col3 float,col4 varchar(50)); 208 create unique index varchar_index on create_index_01(col2) comment 'create varchar index'; 209 insert into create_index_01 values (1,'13456789872',20.23,'5678'),(2,'13873458290',100.00,'23'),(3,NULL,100.00,'23'); 210 insert into create_index_01 values (67834,'13456789872',20.23,'4090'),(56473,'13456789872',100.00,'5678'); 211 select col2,col4 from create_index_01; 212 select * from create_index_01 where col2 not in ('13873458290'); 213 drop index varchar_index on create_index_01; 214 create table create_index_02 (col1 bigint,col2 char(25),col3 float,col4 char(50)); 215 create unique index char_index on create_index_02(col2); 216 insert into create_index_02 values (1,'',20.23,'5678'),(2,'13873458290',100.00,'23'); 217 insert into create_index_02 values (67834,'13456799878',20.23,'4090'),(56473,NULL,100.00,''); 218 insert into create_index_02 values (3,'excel',0.1,'4090'),(4,'中文',0.2,''),(5,'MMEabc$%^123',0.2,''); 219 select col2,col4 from create_index_02; 220 select * from create_index_02 where col2 like "MME%"; 221 drop index char_index on create_index_02; 222 create table create_index_03 (col1 bigint auto_increment,col2 int,col3 float,col4 int); 223 create unique index int_index on create_index_03(col2); 224 insert into create_index_03(col2,col3,col4) values (10,20.23,4090),(10,100.00,5678); 225 insert into create_index_03(col2,col3,col4) values (10,20.23,4090),(11,100.00,4090); 226 insert into create_index_03(col2,col3,col4) values (67834,20.23,4090),(56473,100.00,5678),(NULL,0.01,NULL); 227 select * from create_index_03; 228 drop index int_index on create_index_03; 229 create table create_index_04 (col1 bigint,col2 int primary key,col3 float,col4 bigint); 230 create unique index bigint_index on create_index_04(col1); 231 insert into create_index_04 values (67834,2,20.23,4090),(67834,4,100.00,4091); 232 insert into create_index_04 values (1,2,20.23,4090),(2,4,100.00,4091),(NULL,3,0.01,NULL); 233 insert into create_index_04 values (-9223372036854775808,5,20.23,4090),(9223372036854775807,6,100.00,4091); 234 select * from create_index_04; 235 select * from create_index_04 where col1 in (-9223372036854775808,9223372036854775807); 236 create table create_index_05 (col1 smallint,col2 int primary key,col3 float,col4 smallint); 237 create unique index smallint_index on create_index_05(col1); 238 insert into create_index_05 values (1,2,20.23,4090),(1,4,100.00,4091); 239 insert into create_index_05 values (1,2,20.23,4090),(2,4,100.00,4091),(NULL,3,0.01,NULL); 240 select * from create_index_05; 241 create table create_index_06 (col1 tinyint,col2 int primary key,col3 float,col4 tinyint); 242 create unique index tinyint_index on create_index_06(col1); 243 insert into create_index_06 values (1,2,20.23,56),(1,4,100.00,90); 244 insert into create_index_06 values (1,2,20.23,56),(2,4,100.00,41),(NULL,3,0.01,NULL); 245 select * from create_index_06; 246 create table create_index_07 (col1 int unsigned,col2 int primary key,col3 float,col4 int unsigned); 247 create unique index int_unsigned_index on create_index_07(col1); 248 insert into create_index_07 values (1,2,20.23,56),(2,4,100.00,41),(NULL,3,0.01,NULL); 249 -- @pattern 250 insert into create_index_07 values (1,2,20.23,56),(1,4,100.00,90); 251 select * from create_index_07; 252 create table create_index_08 (col1 bigint unsigned,col2 int primary key,col3 float,col4 bigint unsigned); 253 create unique index bigint_unsigned_index on create_index_08(col1); 254 insert into create_index_08 values (1,2,20.23,56),(1,4,100.00,90); 255 insert into create_index_08 values (1,2,20.23,56),(2,4,100.00,41),(NULL,3,0.01,NULL); 256 select * from create_index_08; 257 create table create_index_09 (col1 bigint primary key,col2 decimal(16,8),col3 decimal(16,8)); 258 create unique index decimal_index on create_index_09(col2); 259 insert into create_index_09 values (1000,20.23,20.00),(1200,20.23,0.10); 260 insert into create_index_09 values (1000,20.23,20.00),(1200,0.23,20.10),(1100,NULL,NULL); 261 select * from create_index_09; 262 create table create_index_10 (col1 bigint primary key,col2 float,col3 float); 263 create unique index float_index on create_index_10(col2); 264 insert into create_index_10 values (1000,20.23,20.00),(1200,20.23,0.10); 265 insert into create_index_10 values (1000,20.23,20.00),(1200,0.23,20.10),(1100,NULL,NULL); 266 select * from create_index_10; 267 create table create_index_11 (col1 bigint primary key,col2 double,col3 double); 268 create unique index double_index on create_index_11(col2); 269 insert into create_index_11 values (1000,20.23,20.00),(1200,20.23,0.10); 270 insert into create_index_11 values (1000,20.23,20.00),(1200,0.23,20.10),(1100,NULL,NULL); 271 select * from create_index_11; 272 create table create_index_12(col1 bigint auto_increment primary key,col2 date,col3 date); 273 create unique index date_index on create_index_12(col2); 274 insert into create_index_12(col2,col3) values ('2013-01-01','2014-02-01'),('2013-01-01','2014-02-20'); 275 insert into create_index_12(col2,col3) values (NULL,'2014-02-01'),(NULL,NULL); 276 create table create_index_13 (col1 bigint auto_increment primary key,col2 datetime,col3 datetime); 277 create unique index datetime_index on create_index_13(col2); 278 insert into create_index_13(col2,col3) values ('2013-01-01 12:00:00','2014-02-01 10:00:00'),('2013-01-01 12:00:00','2014-02-20 05:00:00'); 279 insert into create_index_13(col2,col3) values (NULL,'2014-02-01 12:00:0'),(NULL,NULL); 280 create table create_index_14 (col1 bigint auto_increment primary key,col2 timestamp,col3 timestamp); 281 create unique index timestamp_index on create_index_14(col2); 282 insert into create_index_14(col2,col3) values ('2013-01-01 12:00:00','2014-02-01 10:00:00'),('2013-01-01 12:00:00','2014-02-20 05:00:00'); 283 insert into create_index_14(col2,col3) values (NULL,'2014-02-01 12:00:0'),(NULL,NULL); 284 create table create_index_15 (col1 bigint primary key,col2 bool); 285 create unique index bool_index on create_index_15(col2); 286 -- @pattern 287 insert into create_index_15 values (1,TRUE),(2,FALSE),(3,TRUE); 288 insert into create_index_15 values (1,TRUE),(2,FALSE),(3,NULL); 289 select * from create_index_15; 290 -- blob/json/text type not support unique index 291 create table create_index_16 (col1 bigint primary key,col2 blob,col3 blob); 292 create unique index blob_index on create_index_16(col2); 293 drop table create_index_16; 294 create table create_index_17 (col1 bigint primary key,col2 json,col3 json); 295 create unique index json_index on create_index_17(col2); 296 drop table create_index_17; 297 create table create_index_18 (col1 bigint primary key,col2 text,col3 text); 298 create unique index text_index on create_index_18(col2); 299 drop table create_index_18; 300 301 --create unique index name test 302 create table create_index_name (col1 bigint not null auto_increment,col2 varchar(25),col3 int,col4 varchar(50),primary key (col1)); 303 create unique index 123 on create_index_name(col2); 304 create unique index INdex_123 on create_index_name(col3); 305 create unique index abs@123.abc on create_index_name(col4); 306 create unique index index_123 on create_index_name(col3); 307 create unique index default on create_index_name(col3); 308 create unique index `default` on create_index_name(col3); 309 310 --create unique index more column 311 create table create_index_18 (col1 bigint not null auto_increment,col2 varchar(25),col3 int,col4 varchar(50),primary key (col1)); 312 create unique index m1_index on create_index_18(col2,col3); 313 insert into create_index_18(col2,col3,col4) select 'apple',1,'10'; 314 insert into create_index_18(col2,col3,col4) select 'apple',2,'11'; 315 insert into create_index_18(col2,col3,col4) select 'apple',2,'12'; 316 insert into create_index_18(col2,col3,col4) select NULL,NULL,'13'; 317 select * from create_index_18; 318 drop index m1_index on create_index_18; 319 create unique index m2_index on create_index_18(col2,col3,col4); 320 truncate table create_index_18; 321 insert into create_index_18(col2,col3,col4) select 'apple',1,'10'; 322 insert into create_index_18(col2,col3,col4) select 'apple',2,'11'; 323 insert into create_index_18(col2,col3,col4) select 'apple',2,'12'; 324 insert into create_index_18(col2,col3,col4) select NULL,NULL,'13'; 325 insert into create_index_18(col2,col3,col4) select 'apple',2,'12'; 326 select * from create_index_18; 327 drop index m2_index on create_index_18; 328 create unique index m3_index on create_index_18(col2); 329 create unique index m4_index on create_index_18(col3); 330 create unique index m5_index on create_index_18(col4); 331 select * from create_index_18; 332 show create table create_index_18; 333 drop index m3_index on create_index_18; 334 drop index m4_index on create_index_18; 335 drop index m5_index on create_index_18; 336 drop table create_index_18; 337 create table create_index_18(col1 int,col2 char(15)); 338 insert into create_index_18 values(2,'20'); 339 insert into create_index_18 values(3,'20'); 340 create unique index m1_index on create_index_18(col1); 341 create unique index m2_index on create_index_18(col1); 342 create unique index m3_index on create_index_18(col1,col2); 343 drop table create_index_18; 344 drop index m3_index on create_index_18; 345 346 --create index update/delete/truncate 347 create table create_index_19 (col1 bigint not null auto_increment,col2 varchar(25),col3 int,col4 varchar(50),primary key (col1)); 348 create unique index col2 on create_index_19(col2); 349 insert into create_index_19(col2,col3,col4) values ('apple',1,'10'),('store',2,'11'),('bread',3,'12'); 350 select * from create_index_19; 351 update create_index_19 set col2='chart' where col1=2; 352 select col2 from create_index_19; 353 update create_index_19 set col2='bread' where col1=1; 354 select * from create_index_19; 355 delete from create_index_19 where col2='apple'; 356 select * from create_index_19; 357 truncate table create_index_19; 358 insert into create_index_19(col2,col3,col4) values ('apple',1,'10'),('store',2,'11'),('apple',3,'12'); 359 select * from create_index_19; 360 drop table create_index_19; 361 362 --abnormal test: create not exists unique column,drop not exists index 363 create table create_index_20(col1 int,col2 char(15)); 364 create unique index m3_index on create_index_20(col3); 365 drop index char_index on create_index_20; 366 367 --grant privilege 368 drop account if exists unique_test_account; 369 create account unique_test_account admin_name='admin' identified by '123456'; 370 -- @session:id=1&user=unique_test_account:admin&password=123456 371 create user if not exists user_1 identified by '123456'; 372 create role if not exists 'unique_priv_1'; 373 grant create database,drop database,connect on account * to unique_priv_1; 374 grant create table on database * to unique_priv_1; 375 grant all on table *.* to unique_priv_1; 376 grant unique_priv_1 to user_1; 377 -- @session 378 -- @session:id=1{ 379 create database testdb; 380 use testdb; 381 create table create_index_01 (col1 bigint primary key,col2 varchar(25),col3 float,col4 varchar(50)); 382 create unique index varchar_index on create_index_01(col2) comment 'create varchar index'; 383 insert into create_index_01 values (1,'13456789872',20.23,'5678'),(2,'13873458290',100.00,'23'),(3,NULL,100.00,'23'); 384 select col2,col4 from create_index_01; 385 drop index varchar_index on create_index_01; 386 drop database testdb; 387 -- @session} 388 drop account if exists unique_test_account; 389 390 --transaction conflict 391 use unique_secondary_index; 392 create table trans_index_01 (col1 bigint primary key,col2 varchar(25),col3 float,col4 varchar(50)); 393 start transaction; 394 create unique index varchar_index on trans_index_01(col2) comment 'create varchar index'; 395 insert into trans_index_01 values (1,'13456789872',20.23,'5678'),(2,'13873458290',100.00,'23'),(3,NULL,100.00,'23'); 396 -- @session:id=3{ 397 use unique_secondary_index; 398 -- @wait:0:commit 399 -- @pattern 400 insert into trans_index_01 values (1,'13456789872',20.23,'5678'),(2,'13873458290',100.00,'23'); 401 -- @session} 402 commit; 403 select * from trans_index_01; 404 drop table trans_index_01; 405 406 407 use unique_secondary_index; 408 create table trans_index_01 (col1 bigint primary key,col2 varchar(25),col3 float,col4 varchar(50)); 409 create unique index varchar_index on trans_index_01(col2) comment 'create varchar index'; 410 start transaction; 411 -- @session:id=3{ 412 use unique_secondary_index; 413 drop index varchar_index on trans_index_01; 414 insert into trans_index_01 values (1,'13456789872',20.23,'5678'),(2,'13456789872',100.00,'23'),(3,NULL,100.00,'23'); 415 -- @session} 416 insert into trans_index_01 values (1,'13456789872',20.23,'5678'),(2,'13456789872',100.00,'23'),(3,NULL,100.00,'23'); 417 select * from trans_index_01; 418 commit; 419 select * from trans_index_01; 420 drop table trans_index_01; 421 422 -- secondary ddl 423 create table create_secondary_01 (col1 bigint primary key,col2 varchar(25) unique key,col3 float,col4 varchar(50)); 424 insert into create_secondary_01 values (1,'13456789872',20.23,'5678'),(2,'13873458290',100.00,'23'),(3,NULL,100.00,'23'); 425 create index varchar_second_index on create_secondary_01(col4) comment 'create varchar index'; 426 show create table create_secondary_01; 427 insert into create_secondary_01 values (4,'13456789899',20.23,'5678'),(5,'13873458255',100.00,'23'); 428 select * from create_secondary_01; 429 drop index varchar_second_index on create_secondary_01; 430 show create table create_secondary_01; 431 create table create_secondary_02 (col1 bigint,col2 char(25),col3 float,col4 char(50)); 432 create index char_second_index on create_secondary_02(col2); 433 insert into create_secondary_02 values (1,'',20.23,'5678'),(2,'13873458290',100.00,'23'); 434 select col2,col4 from create_secondary_02; 435 drop index char_second_index on create_secondary_02; 436 create table create_secondary_03 (col1 bigint auto_increment,col2 int default 1000,col3 float,col4 int); 437 create index int_second_index on create_secondary_03(col2); 438 insert into create_secondary_03(col3,col4) values (20.23,4090),(100.00,5678); 439 select * from create_secondary_03; 440 drop index int_second_index on create_secondary_03; 441 create table create_secondary_04 (col1 bigint,col2 int primary key,col3 float,col4 bigint); 442 create index bigint_index on create_secondary_04(col1); 443 insert into create_secondary_04 values (1,2,20.23,4090),(2,4,100.00,4091),(NULL,3,0.01,NULL); 444 select * from create_secondary_04; 445 create table create_secondary_05 (col1 smallint,col2 int primary key,col3 float,col4 smallint); 446 create index smallint_second_index on create_secondary_05(col1); 447 insert into create_secondary_05 values (1,2,20.23,4090),(1,4,100.00,4091); 448 select * from create_secondary_05; 449 create table create_secondary_06 (col1 tinyint,col2 int primary key,col3 float,col4 tinyint); 450 create index tinyint_second_index on create_secondary_06(col1); 451 insert into create_secondary_06 values (1,2,20.23,56),(1,4,100.00,90); 452 select * from create_secondary_06; 453 create table create_secondary_07 (col1 int unsigned,col2 int primary key,col3 float,col4 int unsigned); 454 create index int_unsigned_index on create_secondary_07(col1); 455 insert into create_secondary_07 values (1,2,20.23,56),(1,4,100.00,90); 456 show create table create_secondary_07; 457 select * from create_secondary_07; 458 create table create_secondary_08 (col1 bigint unsigned,col2 int primary key,col3 float,col4 bigint unsigned); 459 create index bigint_unsigned_index on create_secondary_08(col1); 460 insert into create_secondary_08 values (1,2,20.23,56),(2,4,100.00,41),(NULL,3,0.01,NULL); 461 select * from create_secondary_08; 462 create table create_secondary_09 (col1 bigint primary key,col2 decimal(16,8),col3 decimal(16,8)); 463 create index decimal_index on create_secondary_09(col2); 464 insert into create_secondary_09 values (1000,20.23,20.00),(1200,0.23,20.10),(1100,NULL,NULL); 465 select * from create_secondary_09; 466 create table create_secondary_10 (col1 bigint primary key,col2 float,col3 float); 467 insert into create_secondary_10 values (1000,20.23,20.00),(1200,0.23,20.10),(1100,NULL,NULL); 468 create index float_index on create_secondary_10(col2); 469 select * from create_secondary_10; 470 create table create_secondary_11 (col1 bigint primary key,col2 double,col3 double); 471 create index double_index on create_secondary_11(col2); 472 insert into create_secondary_11 values (1000,20.23,20.00),(1200,20.23,0.10); 473 select * from create_secondary_11; 474 create table create_secondary_12(col1 bigint auto_increment primary key,col2 date,col3 date); 475 create index date_index on create_secondary_12(col2); 476 insert into create_secondary_12(col2,col3) values ('2013-01-01','2014-02-01'),('2013-01-01','2014-02-20'); 477 create table create_secondary_13 (col1 bigint auto_increment primary key,col2 datetime,col3 datetime); 478 create index datetime_index on create_secondary_13(col2); 479 insert into create_secondary_13(col2,col3) values (NULL,'2014-02-01 12:00:0'),(NULL,NULL); 480 create table create_secondary_14 (col1 bigint auto_increment primary key,col2 timestamp,col3 timestamp); 481 create index timestamp_index on create_secondary_14(col2); 482 insert into create_secondary_14(col2,col3) values ('2013-01-01 12:00:00','2014-02-01 10:00:00'),('2013-01-01 12:00:00','2014-02-20 05:00:00'); 483 create table create_secondary_15 (col1 bigint primary key,col2 bool); 484 create index bool_index on create_secondary_15(col2); 485 insert into create_secondary_15 values (1,TRUE),(2,FALSE),(3,TRUE); 486 select * from create_secondary_15; 487 -- blob/json/text type not support unique index 488 create table create_secondary_16 (col1 bigint primary key,col2 blob,col3 blob); 489 create index blob_index on create_secondary_16(col2); 490 drop table create_secondary_16; 491 create table create_secondary_17 (col1 bigint primary key,col2 json,col3 json); 492 create index json_index on create_secondary_17(col2); 493 drop table create_secondary_17; 494 create table create_secondary_18 (col1 bigint primary key,col2 text,col3 text); 495 create index text_index on create_secondary_18(col2); 496 drop table create_secondary_18; 497 498 --more column secondary key 499 drop table create_secondary_01; 500 create table create_secondary_01 (col1 bigint primary key,col2 varchar(25) unique key,col3 float,col4 varchar(50),col5 int); 501 create index secondary_key on create_secondary_01(col4,col5); 502 insert into create_secondary_01 values (1,'13456789872',20.23,'5678',99),(2,'13873458290',100.00,'23',9),(3,NULL,100.00,'23',99); 503 select * from create_secondary_01; 504 show create table create_secondary_01; 505 drop index secondary_key on create_secondary_01; 506 truncate table create_secondary_01; 507 show create table create_secondary_01; 508 drop table create_secondary_01; 509 510 --primary key create secondary key 511 create table create_secondary_01 (col1 bigint primary key,col2 varchar(25) unique key,col3 float,col4 varchar(50),col5 int); 512 create index secondary_key1 on create_secondary_01(col1); 513 create index secondary_key2 on create_secondary_01(col1,col2); 514 show create table create_secondary_01; 515 drop index secondary_key1 on create_secondary_01; 516 drop index secondary_key2 on create_secondary_01; 517 create index secondary_key1 on create_secondary_01(col4,col5); 518 create index secondary_key1 on create_secondary_01(col4,col5); 519 drop table create_secondary_01; 520 521 --anormal test 522 create table create_secondary_01 (col1 bigint primary key,col2 varchar(25) unique key,col3 float,col4 varchar(50),col5 int); 523 drop index secondary_key1 on create_secondary_01; 524 create unique index secondary_key1 on create_secondary_01(col1); 525 create index secondary_key1 on create_secondary_01(col1); 526 show create table create_secondary_01;