github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/optimistic/unique_secondary_index.result (about) 1 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)); 2 insert into index_01 values (1,'13456789872',20.23,'5678'),(2,'13873458290',100.00,'23'),(3,NULL,100.00,'23'); 3 insert into index_01 values (67834,'13456789872',20.23,'4090'),(56473,'13456789872',100.00,'5678'); 4 Duplicate entry '13456789872' for key '__mo_index_idx_col' 5 insert into index_01 values (4,'13866666666',20.23,'5678'),(5,'13873458290',100.00,'23'),(6,'13777777777',100.00,'23'); 6 Duplicate entry '13873458290' for key '__mo_index_idx_col' 7 select col2,col4 from index_01; 8 col2 col4 9 13456789872 5678 10 13873458290 23 11 null 23 12 create table index_02 (col1 bigint primary key,col2 char(25) unique key,col3 float,col4 char(50),key num_id(col4)); 13 insert into index_02 values (67834,'13456789872',20.23,'5678'),(56473,'',100.00,'5678'); 14 insert into index_02 values (1,'',20.23,'5678'),(2,'13873458290',100.00,'23'); 15 Duplicate entry '' for key '__mo_index_idx_col' 16 insert into index_02 values (67834,'13456799878',20.23,'4090'),(56473,NULL,100.00,''); 17 Duplicate entry '67834' for key 'col1' 18 insert into index_02 values (3,'excel',0.1,'4090'),(4,'中文',0.2,''),(5,'MMEabc$%^123',0.2,''); 19 select col2,col4 from index_02; 20 col2 col4 21 13456789872 5678 22 5678 23 excel 4090 24 中文 25 MMEabc$%^123 26 select * from index_02 where col2="MMEabc$%^123"; 27 col1 col2 col3 col4 28 5 MMEabc$%^123 0.2 29 create table index_03 (col1 bigint auto_increment primary key,col2 int,col3 float,col4 int,unique key id1(col2),key id2(col4)); 30 insert into index_03(col2,col3,col4) values (10,20.23,4090),(10,100.00,5678); 31 Duplicate entry '10' for key '__mo_index_idx_col' 32 insert into index_03(col2,col3,col4) values (10,20.23,4090),(11,100.00,4090); 33 insert into index_03(col2,col3,col4) values (67834,20.23,4090),(56473,100.00,5678),(NULL,0.01,NULL); 34 insert into index_03(col2,col3,col4) values (-2147483648,1.2,100),(2147483647,2.0,5); 35 select * from index_03; 36 col1 col2 col3 col4 37 3 10 20.23 4090 38 4 11 100.0 4090 39 5 67834 20.23 4090 40 6 56473 100.0 5678 41 7 null 0.01 null 42 8 -2147483648 1.2 100 43 9 2147483647 2.0 5 44 select * from index_03 where col2=-2147483648; 45 col1 col2 col3 col4 46 8 -2147483648 1.2 100 47 create table index_04 (col1 bigint,col2 int primary key,col3 float,col4 bigint,unique key id1(col1),key id2(col4)); 48 insert into index_04 values (67834,2,20.23,4090),(67834,4,100.00,4091); 49 Duplicate entry '67834' for key '__mo_index_idx_col' 50 insert into index_04 values (1,2,20.23,4090),(2,4,100.00,4091),(NULL,3,0.01,NULL); 51 insert into index_04 values (3,2,20.23,4090),(2,4,100.00,4091),(4,4,100.00,4090); 52 (Duplicate entry ')([\d\D]*)(' for key 'col2') 53 select * from index_04; 54 col1 col2 col3 col4 55 1 2 20.23 4090 56 2 4 100.0 4091 57 null 3 0.01 null 58 select * from index_04 where col1 between 10 and 1000000; 59 col1 col2 col3 col4 60 create table index_05 (col1 smallint unique key,col2 int primary key,col3 float,col4 smallint,key id2(col4)); 61 insert into index_05 values (1,2,20.23,4090),(1,4,100.00,4091); 62 Duplicate entry '1' for key '__mo_index_idx_col' 63 insert into index_05 values (1,2,20.23,4090),(2,4,100.00,4091),(NULL,3,0.01,NULL); 64 select * from index_05; 65 col1 col2 col3 col4 66 1 2 20.23 4090 67 2 4 100.0 4091 68 null 3 0.01 null 69 create table index_06 (col1 tinyint,col2 int primary key,col3 float,col4 tinyint,unique key id1(col1),key id2(col4)); 70 insert into index_06 values (1,2,20.23,56),(1,4,100.00,90); 71 Duplicate entry '1' for key '__mo_index_idx_col' 72 insert into index_06 values (1,2,20.23,56),(2,4,100.00,41),(NULL,3,0.01,NULL); 73 select * from index_06; 74 col1 col2 col3 col4 75 1 2 20.23 56 76 2 4 100.0 41 77 null 3 0.01 null 78 create table index_07 (col1 int unsigned,col2 int primary key,col3 float,col4 int unsigned,unique key id1(col1),key id2(col4)); 79 insert into index_07 values (1,2,20.23,56),(2,4,100.00,41),(NULL,3,0.01,NULL); 80 insert into index_07 values (1,2,20.23,56),(1,4,100.00,90); 81 Duplicate entry (.*) for key (.*) 82 select * from index_07; 83 col1 col2 col3 col4 84 1 2 20.23 56 85 2 4 100.0 41 86 null 3 0.01 null 87 create table index_08 (col1 bigint unsigned,col2 int primary key,col3 float,col4 bigint unsigned,unique key id1(col1),key id2(col4)); 88 insert into index_08 values (1,2,20.23,56),(1,4,100.00,90); 89 Duplicate entry '1' for key '__mo_index_idx_col' 90 insert into index_08 values (1,2,20.23,56),(2,4,100.00,41),(NULL,3,0.01,NULL); 91 select * from index_08; 92 col1 col2 col3 col4 93 1 2 20.23 56 94 2 4 100.0 41 95 null 3 0.01 null 96 create table index_09 (col1 bigint primary key,col2 decimal(4,2),col3 decimal(4,2),unique key d1(col2),key d2(col3)); 97 insert into index_09 values (1000,20.23,20.00),(1200,20.23,0.10); 98 Duplicate entry '20.23' for key '__mo_index_idx_col' 99 insert into index_09 values (1000,20.23,20.00),(1200,0.23,20.10),(1100,NULL,NULL); 100 select * from index_09; 101 col1 col2 col3 102 1000 20.23 20.00 103 1200 0.23 20.10 104 1100 null null 105 create table index_10 (col1 bigint primary key,col2 float,col3 float,unique key d1(col2),key d2(col3)); 106 insert into index_10 values (1000,20.23,20.00),(1200,20.23,0.10); 107 Duplicate entry '20.23' for key '__mo_index_idx_col' 108 insert into index_10 values (1000,20.23,20.00),(1200,0.23,20.10),(1100,NULL,NULL); 109 select * from index_10; 110 col1 col2 col3 111 1000 20.23 20.0 112 1200 0.23 20.1 113 1100 null null 114 create table index_11 (col1 bigint primary key,col2 double,col3 double,unique key d1(col2),key d2(col3)); 115 insert into index_11 values (1000,20.23,20.00),(1200,20.23,0.10); 116 Duplicate entry '20.23' for key '__mo_index_idx_col' 117 insert into index_11 values (1000,20.23,20.00),(1200,0.23,20.10),(1100,NULL,NULL); 118 select * from index_11; 119 col1 col2 col3 120 1000 20.23 20.0 121 1200 0.23 20.1 122 1100 null null 123 create table index_12 (col1 bigint auto_increment primary key,col2 date,col3 date,unique key d1(col2),key d2(col3)); 124 insert into index_12(col2,col3) values ('2013-01-01','2014-02-01'),('2013-01-01','2014-02-20'); 125 Duplicate entry '2013-01-01' for key '__mo_index_idx_col' 126 insert into index_12(col2,col3) values (NULL,'2014-02-01'),(NULL,NULL); 127 select col2 from index_12; 128 col2 129 null 130 null 131 create table index_13 (col1 bigint auto_increment primary key,col2 datetime,col3 datetime,unique key d1(col2),key d2(col3)); 132 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'); 133 Duplicate entry '2013-01-01 12:00:00' for key '__mo_index_idx_col' 134 insert into index_13(col2,col3) values (NULL,'2014-02-01 12:00:0'),(NULL,NULL); 135 create table index_14 (col1 bigint auto_increment primary key,col2 timestamp,col3 timestamp,unique key d1(col2),key d2(col3)); 136 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'); 137 Duplicate entry '2013-01-01 12:00:00' for key '__mo_index_idx_col' 138 insert into index_14(col2,col3) values (NULL,'2014-02-01 12:00:0'),(NULL,NULL); 139 create table index_15 (col1 bigint primary key,col2 bool,unique key c2(col2)); 140 insert into index_15 values (1,TRUE),(2,FALSE),(3,TRUE); 141 (Duplicate entry ')([\d\D][true|false]*)(' for key '__mo_index_idx_col') 142 insert into index_15 values (1,TRUE),(2,FALSE),(3,NULL); 143 select * from index_15; 144 col1 col2 145 1 true 146 2 false 147 3 null 148 create table index_16 (col1 bigint primary key,col2 blob,col3 blob,unique key d1(col2),key d2(col3)); 149 not supported: BLOB column 'col2' cannot be in index 150 create table index_17 (col1 bigint primary key,col2 json,col3 json,unique key d1(col2),key d2(col3)); 151 not supported: JSON column 'col2' cannot be in index 152 create table index_18 (col1 bigint primary key,col2 text,col3 text,unique key d1(col2),key d2(col3)); 153 not supported: TEXT column 'col2' cannot be in index 154 create table index_name (col1 bigint primary key,col2 varchar(25),col3 float,col4 varchar(50),unique key 123(col2),key num_id(col4)); 155 SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 1 column 108 near " 123(col2),key num_id(col4));"; 156 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)); 157 SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 1 column 116 near "@123.abc(col2),key num_id(col4));"; 158 create table index_name (col1 bigint primary key,col2 varchar(25),col3 float,col4 varchar(50),unique key default(col2),key num_id(col4)); 159 SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 1 column 112 near " default(col2),key num_id(col4));"; 160 create table index_name (col1 bigint primary key,col2 varchar(25),col3 float,col4 varchar(50),unique key `default`(col2),key num_id(col4)); 161 drop table index_name; 162 create table index_name (col1 bigint primary key,col2 varchar(25),col3 float,col4 varchar(50),unique key `123`(col2),key num_id(col4)); 163 drop table index_name; 164 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)); 165 drop table index_name; 166 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)); 167 insert into index_table_01 values (67834,'13456789872',20.23,4090),(56473,'',100.00,5678); 168 insert into index_table_01 values (34,NULL,4090,1); 169 insert into index_table_01 values (56478,'',103.00,5670); 170 Duplicate entry '' for key '__mo_index_idx_col' 171 select * from index_table_01; 172 col1 col2 col3 col4 173 67834 13456789872 20.23 4090 174 56473 100.0 5678 175 34 null 4090.0 1 176 drop table index_table_01; 177 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)); 178 insert into index_table_02 values (34,NULL,4090,1); 179 constraint violation: Column 'col2' cannot be null 180 drop table index_table_02; 181 create table index_table_02 (col1 bigint primary key,col2 int default 0,unique key col2(col2)); 182 insert into index_table_02(col1) values (1); 183 insert into index_table_02(col1) values (2),(3); 184 Duplicate entry '0' for key '__mo_index_idx_col' 185 drop table index_table_02; 186 create table index_table_03 (col1 bigint,col2 int,unique key col2(col2)); 187 insert into index_table_03 values (1,20),(2,NULL),(3,90); 188 update index_table_03 set col2=10 where col2 is NULL; 189 select * from index_table_03; 190 col1 col2 191 1 20 192 3 90 193 2 10 194 drop table index_table_03; 195 CREATE TABLE IF NOT EXISTS `t_code_rule` ( 196 `code_id` bigint(20) NOT NULL , 197 `code_no` varchar(50) NOT NULL, 198 `org_no` varchar(50) NOT NULL, 199 `org_name` varchar(50) NOT NULL, 200 `ancestors` varchar(255) NOT NULL, 201 `code_rule_no` varchar(50) NOT NULL, 202 `code_rule_name` varchar(50) NOT NULL, 203 `code_name` varchar(50) NOT NULL, 204 `code_type` int(11), 205 `split` varchar(50) DEFAULT NULL, 206 `remark` varchar(255), 207 `create_time` datetime NOT NULL, 208 `create_user` varchar(50) DEFAULT NULL, 209 `last_update_time` datetime DEFAULT NULL, 210 `last_update_user` varchar(50) DEFAULT NULL, 211 `is_system_code` varchar(20) NOT NULL DEFAULT 'N', 212 PRIMARY KEY (`code_id`), 213 UNIQUE KEY `code_type` (`code_type`), 214 KEY `code_no` (`code_no`), 215 KEY `code_rule_no` (`code_rule_no`), 216 KEY `org_no` (`org_no`) 217 ); 218 show create table t_code_rule; 219 Table Create Table 220 t_code_rule CREATE TABLE `t_code_rule` (\n`code_id` BIGINT NOT NULL,\n`code_no` VARCHAR(50) NOT NULL,\n`org_no` VARCHAR(50) NOT NULL,\n`org_name` VARCHAR(50) NOT NULL,\n`ancestors` VARCHAR(255) NOT NULL,\n`code_rule_no` VARCHAR(50) NOT NULL,\n`code_rule_name` VARCHAR(50) NOT NULL,\n`code_name` VARCHAR(50) NOT NULL,\n`code_type` INT DEFAULT NULL,\n`split` VARCHAR(50) DEFAULT null,\n`remark` VARCHAR(255) DEFAULT NULL,\n`create_time` DATETIME NOT NULL,\n`create_user` VARCHAR(50) DEFAULT null,\n`last_update_time` DATETIME DEFAULT null,\n`last_update_user` VARCHAR(50) DEFAULT null,\n`is_system_code` VARCHAR(20) DEFAULT 'N',\nPRIMARY KEY (`code_id`),\nUNIQUE KEY `code_type` (`code_type`),\nKEY `code_no` (`code_no`),\nKEY `code_rule_no` (`code_rule_no`),\nKEY `org_no` (`org_no`)\n) 221 load data infile '$resources/load_data/unique_index_file.csv' into table t_code_rule fields terminated by ','; 222 select code_id,code_type,code_no,code_rule_no,org_no from t_code_rule; 223 code_id code_type code_no code_rule_no org_no 224 1000 30 18723 0000234 yy01 225 1001 31 9999 0000235 yy02 226 1002 32 8783 0000236 yy03 227 1003 33 6783 0000237 yy04 228 1004 34 8394 0000238 yy05 229 1005 21 1111 0000239 yy06 230 1006 36 5983 0000240 yy07 231 1007 37 9844 0000241 yy08 232 1008 null 2378 0000242 yy09 233 1009 39 8744 0000243 yy10 234 truncate table t_code_rule; 235 load data infile '$resources/load_data/unique_index_duplicate.csv' into table t_code_rule fields terminated by ','; 236 Duplicate entry '30' for key '__mo_index_idx_col' 237 select code_id,code_type,code_no,code_rule_no,org_no from t_code_rule; 238 code_id code_type code_no code_rule_no org_no 239 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'); 240 load data infile '$resources/load_data/unique_index_file.csv' into table index_temp fields terminated by ','; 241 insert into t_code_rule select * from index_temp; 242 select code_id,code_type,code_no,code_rule_no,org_no from t_code_rule; 243 code_id code_type code_no code_rule_no org_no 244 1000 30 18723 0000234 yy01 245 1001 31 9999 0000235 yy02 246 1002 32 8783 0000236 yy03 247 1003 33 6783 0000237 yy04 248 1004 34 8394 0000238 yy05 249 1005 21 1111 0000239 yy06 250 1006 36 5983 0000240 yy07 251 1007 37 9844 0000241 yy08 252 1008 null 2378 0000242 yy09 253 1009 39 8744 0000243 yy10 254 truncate table index_temp; 255 load data infile '$resources/load_data/unique_index_duplicate.csv' into table index_temp fields terminated by ','; 256 insert into t_code_rule select * from index_temp; 257 (Duplicate entry ')([\d\D]*)(' for key '__mo_index_idx_col') 258 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)); 259 insert into index_table_04(col2,col3,col4) select 'apple',1,'10'; 260 insert into index_table_04(col2,col3,col4) select 'apple',2,'11'; 261 insert into index_table_04(col2,col3,col4) select 'apple',2,'12'; 262 Duplicate entry '46016170706c65003a1502' for key '__mo_index_idx_col' 263 insert into index_table_04(col2,col3,col4) select NULL,NULL,'13'; 264 select * from index_table_04; 265 col1 col2 col3 col4 266 1 apple 1 10 267 2 apple 2 11 268 4 null null 13 269 drop table index_table_04; 270 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)); 271 insert into index_table_04(col2,col3,col4) select 'apple',1,'10'; 272 insert into index_table_04(col2,col3,col4) select 'apple',2,'11'; 273 Duplicate entry 'apple' for key '__mo_index_idx_col' 274 insert into index_table_04(col2,col3,col4) select 'apple',2,'12'; 275 Duplicate entry 'apple' for key '__mo_index_idx_col' 276 insert into index_table_04(col2,col3,col4) select NULL,NULL,'13'; 277 select * from index_table_04; 278 col1 col2 col3 col4 279 1 apple 1 10 280 4 null null 13 281 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)); 282 insert into index_table_05(col2,col3,col4) values ('apple',1,'10'),('store',2,'11'),('bread',3,'12'); 283 select * from index_table_05; 284 col1 col2 col3 col4 285 1 apple 1 10 286 2 store 2 11 287 3 bread 3 12 288 update index_table_05 set col2='chart' where col1=2; 289 select col2 from index_table_05; 290 col2 291 apple 292 bread 293 chart 294 update index_table_05 set col2='bread' where col1=1; 295 Duplicate entry 'bread' for key '__mo_index_idx_col' 296 select * from index_table_05; 297 col1 col2 col3 col4 298 1 apple 1 10 299 3 bread 3 12 300 2 chart 2 11 301 delete from index_table_05 where col2='apple'; 302 select * from index_table_05; 303 col1 col2 col3 col4 304 3 bread 3 12 305 2 chart 2 11 306 truncate table index_table_05; 307 insert into index_table_05(col2,col3,col4) values ('apple',1,'10'),('store',2,'11'),('apple',3,'12'); 308 Duplicate entry 'apple' for key '__mo_index_idx_col' 309 select * from index_table_05; 310 col1 col2 col3 col4 311 drop table index_table_05; 312 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)); 313 insert into index_table_05(col2,col3,col4) values ('apple',1,'10'),('store',2,'11'),('bread',3,'12'); 314 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)); 315 insert into index_table_06(col2,col3,col4) values ('apple',1,'10'),('store',2,'11'),('bread',3,'12'); 316 select * from index_table_06; 317 col1 col2 col3 col4 318 1 apple 1 10 319 2 store 2 11 320 3 bread 3 12 321 drop table index_table_06; 322 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)); 323 insert into index_table_06(col2,col3,col4) values ('apple',1,'10'),('store',2,'11'),('bread',2,'12'); 324 insert into index_table_06(col2,col3,col4) values ('read',1,'10'),('write',2,'11'),('bread',3,'10'); 325 insert into index_table_06(col2,col3,col4) values ('read',NULL,'10'),('write',NULL,NULL); 326 select col2,col3 from index_table_06; 327 col2 col3 328 apple 1 329 store 2 330 bread 2 331 read 1 332 write 2 333 bread 3 334 read null 335 write null 336 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)); 337 invalid input: column 'col10' is not exist 338 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)); 339 invalid input: column 'col40' is not exist 340 create table create_index_01 (col1 bigint primary key,col2 varchar(25),col3 float,col4 varchar(50)); 341 create unique index varchar_index on create_index_01(col2) comment 'create varchar index'; 342 insert into create_index_01 values (1,'13456789872',20.23,'5678'),(2,'13873458290',100.00,'23'),(3,NULL,100.00,'23'); 343 insert into create_index_01 values (67834,'13456789872',20.23,'4090'),(56473,'13456789872',100.00,'5678'); 344 Duplicate entry '13456789872' for key '__mo_index_idx_col' 345 select col2,col4 from create_index_01; 346 col2 col4 347 13456789872 5678 348 13873458290 23 349 null 23 350 select * from create_index_01 where col2 not in ('13873458290'); 351 col1 col2 col3 col4 352 1 13456789872 20.23 5678 353 drop index varchar_index on create_index_01; 354 create table create_index_02 (col1 bigint,col2 char(25),col3 float,col4 char(50)); 355 create unique index char_index on create_index_02(col2); 356 insert into create_index_02 values (1,'',20.23,'5678'),(2,'13873458290',100.00,'23'); 357 insert into create_index_02 values (67834,'13456799878',20.23,'4090'),(56473,NULL,100.00,''); 358 insert into create_index_02 values (3,'excel',0.1,'4090'),(4,'中文',0.2,''),(5,'MMEabc$%^123',0.2,''); 359 select col2,col4 from create_index_02; 360 col2 col4 361 5678 362 13873458290 23 363 13456799878 4090 364 null 365 excel 4090 366 中文 367 MMEabc$%^123 368 select * from create_index_02 where col2 like "MME%"; 369 col1 col2 col3 col4 370 5 MMEabc$%^123 0.2 371 drop index char_index on create_index_02; 372 create table create_index_03 (col1 bigint auto_increment,col2 int,col3 float,col4 int); 373 create unique index int_index on create_index_03(col2); 374 insert into create_index_03(col2,col3,col4) values (10,20.23,4090),(10,100.00,5678); 375 Duplicate entry '10' for key '__mo_index_idx_col' 376 insert into create_index_03(col2,col3,col4) values (10,20.23,4090),(11,100.00,4090); 377 insert into create_index_03(col2,col3,col4) values (67834,20.23,4090),(56473,100.00,5678),(NULL,0.01,NULL); 378 select * from create_index_03; 379 col1 col2 col3 col4 380 3 10 20.23 4090 381 4 11 100.0 4090 382 5 67834 20.23 4090 383 6 56473 100.0 5678 384 7 null 0.01 null 385 drop index int_index on create_index_03; 386 create table create_index_04 (col1 bigint,col2 int primary key,col3 float,col4 bigint); 387 create unique index bigint_index on create_index_04(col1); 388 insert into create_index_04 values (67834,2,20.23,4090),(67834,4,100.00,4091); 389 Duplicate entry '67834' for key '__mo_index_idx_col' 390 insert into create_index_04 values (1,2,20.23,4090),(2,4,100.00,4091),(NULL,3,0.01,NULL); 391 insert into create_index_04 values (-9223372036854775808,5,20.23,4090),(9223372036854775807,6,100.00,4091); 392 select * from create_index_04; 393 col1 col2 col3 col4 394 1 2 20.23 4090 395 2 4 100.0 4091 396 null 3 0.01 null 397 -9223372036854775808 5 20.23 4090 398 9223372036854775807 6 100.0 4091 399 select * from create_index_04 where col1 in (-9223372036854775808,9223372036854775807); 400 col1 col2 col3 col4 401 -9223372036854775808 5 20.23 4090 402 9223372036854775807 6 100.0 4091 403 create table create_index_05 (col1 smallint,col2 int primary key,col3 float,col4 smallint); 404 create unique index smallint_index on create_index_05(col1); 405 insert into create_index_05 values (1,2,20.23,4090),(1,4,100.00,4091); 406 Duplicate entry '1' for key '__mo_index_idx_col' 407 insert into create_index_05 values (1,2,20.23,4090),(2,4,100.00,4091),(NULL,3,0.01,NULL); 408 select * from create_index_05; 409 col1 col2 col3 col4 410 1 2 20.23 4090 411 2 4 100.0 4091 412 null 3 0.01 null 413 create table create_index_06 (col1 tinyint,col2 int primary key,col3 float,col4 tinyint); 414 create unique index tinyint_index on create_index_06(col1); 415 insert into create_index_06 values (1,2,20.23,56),(1,4,100.00,90); 416 Duplicate entry '1' for key '__mo_index_idx_col' 417 insert into create_index_06 values (1,2,20.23,56),(2,4,100.00,41),(NULL,3,0.01,NULL); 418 select * from create_index_06; 419 col1 col2 col3 col4 420 1 2 20.23 56 421 2 4 100.0 41 422 null 3 0.01 null 423 create table create_index_07 (col1 int unsigned,col2 int primary key,col3 float,col4 int unsigned); 424 create unique index int_unsigned_index on create_index_07(col1); 425 insert into create_index_07 values (1,2,20.23,56),(2,4,100.00,41),(NULL,3,0.01,NULL); 426 insert into create_index_07 values (1,2,20.23,56),(1,4,100.00,90); 427 (Duplicate entry ')([\d\D]*)(' for key ')([col2|__mo_index_idx_col]*)(') 428 select * from create_index_07; 429 col1 col2 col3 col4 430 1 2 20.23 56 431 2 4 100.0 41 432 null 3 0.01 null 433 create table create_index_08 (col1 bigint unsigned,col2 int primary key,col3 float,col4 bigint unsigned); 434 create unique index bigint_unsigned_index on create_index_08(col1); 435 insert into create_index_08 values (1,2,20.23,56),(1,4,100.00,90); 436 Duplicate entry '1' for key '__mo_index_idx_col' 437 insert into create_index_08 values (1,2,20.23,56),(2,4,100.00,41),(NULL,3,0.01,NULL); 438 select * from create_index_08; 439 col1 col2 col3 col4 440 1 2 20.23 56 441 2 4 100.0 41 442 null 3 0.01 null 443 create table create_index_09 (col1 bigint primary key,col2 decimal(16,8),col3 decimal(16,8)); 444 create unique index decimal_index on create_index_09(col2); 445 insert into create_index_09 values (1000,20.23,20.00),(1200,20.23,0.10); 446 Duplicate entry '20.23000000' for key '__mo_index_idx_col' 447 insert into create_index_09 values (1000,20.23,20.00),(1200,0.23,20.10),(1100,NULL,NULL); 448 select * from create_index_09; 449 col1 col2 col3 450 1000 20.23000000 20.00000000 451 1200 0.23000000 20.10000000 452 1100 null null 453 create table create_index_10 (col1 bigint primary key,col2 float,col3 float); 454 create unique index float_index on create_index_10(col2); 455 insert into create_index_10 values (1000,20.23,20.00),(1200,20.23,0.10); 456 Duplicate entry '20.23' for key '__mo_index_idx_col' 457 insert into create_index_10 values (1000,20.23,20.00),(1200,0.23,20.10),(1100,NULL,NULL); 458 select * from create_index_10; 459 col1 col2 col3 460 1000 20.23 20.0 461 1200 0.23 20.1 462 1100 null null 463 create table create_index_11 (col1 bigint primary key,col2 double,col3 double); 464 create unique index double_index on create_index_11(col2); 465 insert into create_index_11 values (1000,20.23,20.00),(1200,20.23,0.10); 466 Duplicate entry '20.23' for key '__mo_index_idx_col' 467 insert into create_index_11 values (1000,20.23,20.00),(1200,0.23,20.10),(1100,NULL,NULL); 468 select * from create_index_11; 469 col1 col2 col3 470 1000 20.23 20.0 471 1200 0.23 20.1 472 1100 null null 473 create table create_index_12(col1 bigint auto_increment primary key,col2 date,col3 date); 474 create unique index date_index on create_index_12(col2); 475 insert into create_index_12(col2,col3) values ('2013-01-01','2014-02-01'),('2013-01-01','2014-02-20'); 476 Duplicate entry '2013-01-01' for key '__mo_index_idx_col' 477 insert into create_index_12(col2,col3) values (NULL,'2014-02-01'),(NULL,NULL); 478 create table create_index_13 (col1 bigint auto_increment primary key,col2 datetime,col3 datetime); 479 create unique index datetime_index on create_index_13(col2); 480 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'); 481 Duplicate entry '2013-01-01 12:00:00' for key '__mo_index_idx_col' 482 insert into create_index_13(col2,col3) values (NULL,'2014-02-01 12:00:0'),(NULL,NULL); 483 create table create_index_14 (col1 bigint auto_increment primary key,col2 timestamp,col3 timestamp); 484 create unique index timestamp_index on create_index_14(col2); 485 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'); 486 Duplicate entry '2013-01-01 12:00:00' for key '__mo_index_idx_col' 487 insert into create_index_14(col2,col3) values (NULL,'2014-02-01 12:00:0'),(NULL,NULL); 488 create table create_index_15 (col1 bigint primary key,col2 bool); 489 create unique index bool_index on create_index_15(col2); 490 insert into create_index_15 values (1,TRUE),(2,FALSE),(3,TRUE); 491 (Duplicate entry ')([\d\D][true|false]*)(' for key '__mo_index_idx_col') 492 insert into create_index_15 values (1,TRUE),(2,FALSE),(3,NULL); 493 select * from create_index_15; 494 col1 col2 495 1 true 496 2 false 497 3 null 498 create table create_index_16 (col1 bigint primary key,col2 blob,col3 blob); 499 create unique index blob_index on create_index_16(col2); 500 not supported: BLOB column 'col2' cannot be in index 501 drop table create_index_16; 502 create table create_index_17 (col1 bigint primary key,col2 json,col3 json); 503 create unique index json_index on create_index_17(col2); 504 not supported: JSON column 'col2' cannot be in index 505 drop table create_index_17; 506 create table create_index_18 (col1 bigint primary key,col2 text,col3 text); 507 create unique index text_index on create_index_18(col2); 508 not supported: TEXT column 'col2' cannot be in index 509 drop table create_index_18; 510 create table create_index_name (col1 bigint not null auto_increment,col2 varchar(25),col3 int,col4 varchar(50),primary key (col1)); 511 create unique index 123 on create_index_name(col2); 512 SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 1 column 23 near " 123 on create_index_name(col2);"; 513 create unique index INdex_123 on create_index_name(col3); 514 create unique index abs@123.abc on create_index_name(col4); 515 SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 1 column 31 near "@123.abc on create_index_name(col4);"; 516 create unique index index_123 on create_index_name(col3); 517 duplicate key name 'index_123' 518 create unique index default on create_index_name(col3); 519 SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 1 column 27 near " default on create_index_name(col3);"; 520 create unique index `default` on create_index_name(col3); 521 create table create_index_18 (col1 bigint not null auto_increment,col2 varchar(25),col3 int,col4 varchar(50),primary key (col1)); 522 create unique index m1_index on create_index_18(col2,col3); 523 insert into create_index_18(col2,col3,col4) select 'apple',1,'10'; 524 insert into create_index_18(col2,col3,col4) select 'apple',2,'11'; 525 insert into create_index_18(col2,col3,col4) select 'apple',2,'12'; 526 Duplicate entry '46016170706c65003a1502' for key '__mo_index_idx_col' 527 insert into create_index_18(col2,col3,col4) select NULL,NULL,'13'; 528 select * from create_index_18; 529 col1 col2 col3 col4 530 1 apple 1 10 531 2 apple 2 11 532 4 null null 13 533 drop index m1_index on create_index_18; 534 create unique index m2_index on create_index_18(col2,col3,col4); 535 truncate table create_index_18; 536 insert into create_index_18(col2,col3,col4) select 'apple',1,'10'; 537 insert into create_index_18(col2,col3,col4) select 'apple',2,'11'; 538 insert into create_index_18(col2,col3,col4) select 'apple',2,'12'; 539 insert into create_index_18(col2,col3,col4) select NULL,NULL,'13'; 540 insert into create_index_18(col2,col3,col4) select 'apple',2,'12'; 541 Duplicate entry '46016170706c65003a15024601313200' for key '__mo_index_idx_col' 542 select * from create_index_18; 543 col1 col2 col3 col4 544 1 apple 1 10 545 2 apple 2 11 546 3 apple 2 12 547 4 null null 13 548 drop index m2_index on create_index_18; 549 create unique index m3_index on create_index_18(col2); 550 Duplicate entry 'apple' for key '__mo_index_idx_col' 551 create unique index m4_index on create_index_18(col3); 552 Duplicate entry '2' for key '__mo_index_idx_col' 553 create unique index m5_index on create_index_18(col4); 554 select * from create_index_18; 555 col1 col2 col3 col4 556 1 apple 1 10 557 2 apple 2 11 558 3 apple 2 12 559 4 null null 13 560 show create table create_index_18; 561 Table Create Table 562 create_index_18 CREATE TABLE `create_index_18` (\n`col1` BIGINT NOT NULL AUTO_INCREMENT,\n`col2` VARCHAR(25) DEFAULT NULL,\n`col3` INT DEFAULT NULL,\n`col4` VARCHAR(50) DEFAULT NULL,\nPRIMARY KEY (`col1`),\nUNIQUE KEY `m5_index` (`col4`)\n) 563 drop index m3_index on create_index_18; 564 internal error: not found index: m3_index 565 drop index m4_index on create_index_18; 566 internal error: not found index: m4_index 567 drop index m5_index on create_index_18; 568 drop table create_index_18; 569 create table create_index_18(col1 int,col2 char(15)); 570 insert into create_index_18 values(2,'20'); 571 insert into create_index_18 values(3,'20'); 572 create unique index m1_index on create_index_18(col1); 573 create unique index m2_index on create_index_18(col1); 574 create unique index m3_index on create_index_18(col1,col2); 575 drop table create_index_18; 576 drop index m3_index on create_index_18; 577 no such table unique_secondary_index.create_index_18 578 create table create_index_19 (col1 bigint not null auto_increment,col2 varchar(25),col3 int,col4 varchar(50),primary key (col1)); 579 create unique index col2 on create_index_19(col2); 580 insert into create_index_19(col2,col3,col4) values ('apple',1,'10'),('store',2,'11'),('bread',3,'12'); 581 select * from create_index_19; 582 col1 col2 col3 col4 583 1 apple 1 10 584 2 store 2 11 585 3 bread 3 12 586 update create_index_19 set col2='chart' where col1=2; 587 select col2 from create_index_19; 588 col2 589 apple 590 bread 591 chart 592 update create_index_19 set col2='bread' where col1=1; 593 Duplicate entry 'bread' for key '__mo_index_idx_col' 594 select * from create_index_19; 595 col1 col2 col3 col4 596 1 apple 1 10 597 3 bread 3 12 598 2 chart 2 11 599 delete from create_index_19 where col2='apple'; 600 select * from create_index_19; 601 col1 col2 col3 col4 602 3 bread 3 12 603 2 chart 2 11 604 truncate table create_index_19; 605 insert into create_index_19(col2,col3,col4) values ('apple',1,'10'),('store',2,'11'),('apple',3,'12'); 606 Duplicate entry 'apple' for key '__mo_index_idx_col' 607 select * from create_index_19; 608 col1 col2 col3 col4 609 drop table create_index_19; 610 create table create_index_20(col1 int,col2 char(15)); 611 create unique index m3_index on create_index_20(col3); 612 invalid input: column 'col3' is not exist 613 drop index char_index on create_index_20; 614 internal error: not found index: char_index 615 drop account if exists unique_test_account; 616 create account unique_test_account admin_name='admin' identified by '123456'; 617 create user if not exists user_1 identified by '123456'; 618 create role if not exists 'unique_priv_1'; 619 grant create database,drop database,connect on account * to unique_priv_1; 620 grant create table on database * to unique_priv_1; 621 grant all on table *.* to unique_priv_1; 622 grant unique_priv_1 to user_1; 623 create database testdb; 624 use testdb; 625 create table create_index_01 (col1 bigint primary key,col2 varchar(25),col3 float,col4 varchar(50)); 626 create unique index varchar_index on create_index_01(col2) comment 'create varchar index'; 627 insert into create_index_01 values (1,'13456789872',20.23,'5678'),(2,'13873458290',100.00,'23'),(3,NULL,100.00,'23'); 628 select col2,col4 from create_index_01; 629 col2 col4 630 13456789872 5678 631 13873458290 23 632 null 23 633 drop index varchar_index on create_index_01; 634 drop database testdb; 635 drop account if exists unique_test_account; 636 use unique_secondary_index; 637 create table trans_index_01 (col1 bigint primary key,col2 varchar(25),col3 float,col4 varchar(50)); 638 start transaction; 639 create unique index varchar_index on trans_index_01(col2) comment 'create varchar index'; 640 insert into trans_index_01 values (1,'13456789872',20.23,'5678'),(2,'13873458290',100.00,'23'),(3,NULL,100.00,'23'); 641 use unique_secondary_index; 642 insert into trans_index_01 values (1,'13456789872',20.23,'5678'),(2,'13873458290',100.00,'23'); 643 select * from trans_index_01; 644 col1 col2 col3 col4 645 1 13456789872 20.23 5678 646 2 13873458290 100.0 23 647 select * from trans_index_01; 648 col1 col2 col3 col4 649 1 13456789872 20.23 5678 650 2 13873458290 100.0 23 651 3 null 100.0 23 652 commit; 653 w-w conflict 654 select * from trans_index_01; 655 col1 col2 col3 col4 656 1 13456789872 20.23 5678 657 2 13873458290 100.0 23 658 drop table trans_index_01; 659 use unique_secondary_index; 660 create table trans_index_01 (col1 bigint primary key,col2 varchar(25),col3 float,col4 varchar(50)); 661 create unique index varchar_index on trans_index_01(col2) comment 'create varchar index'; 662 start transaction; 663 use unique_secondary_index; 664 drop index varchar_index on trans_index_01; 665 insert into trans_index_01 values (1,'13456789872',20.23,'5678'),(2,'13456789872',100.00,'23'),(3,NULL,100.00,'23'); 666 insert into trans_index_01 values (1,'13456789872',20.23,'5678'),(2,'13456789872',100.00,'23'),(3,NULL,100.00,'23'); 667 Duplicate entry '13456789872' for key '__mo_index_idx_col' 668 select * from trans_index_01; 669 col1 col2 col3 col4 670 commit; 671 select * from trans_index_01; 672 col1 col2 col3 col4 673 1 13456789872 20.23 5678 674 2 13456789872 100.0 23 675 3 null 100.0 23 676 drop table trans_index_01; 677 create table create_secondary_01 (col1 bigint primary key,col2 varchar(25) unique key,col3 float,col4 varchar(50)); 678 insert into create_secondary_01 values (1,'13456789872',20.23,'5678'),(2,'13873458290',100.00,'23'),(3,NULL,100.00,'23'); 679 create index varchar_second_index on create_secondary_01(col4) comment 'create varchar index'; 680 show create table create_secondary_01; 681 Table Create Table 682 create_secondary_01 CREATE TABLE `create_secondary_01` (\n`col1` BIGINT NOT NULL,\n`col2` VARCHAR(25) DEFAULT NULL,\n`col3` FLOAT DEFAULT NULL,\n`col4` VARCHAR(50) DEFAULT NULL,\nPRIMARY KEY (`col1`),\nUNIQUE KEY `col2` (`col2`),\nKEY `varchar_second_index` (`col4`) COMMENT 'create varchar index'\n) 683 insert into create_secondary_01 values (4,'13456789899',20.23,'5678'),(5,'13873458255',100.00,'23'); 684 select * from create_secondary_01; 685 col1 col2 col3 col4 686 1 13456789872 20.23 5678 687 2 13873458290 100.0 23 688 3 null 100.0 23 689 4 13456789899 20.23 5678 690 5 13873458255 100.0 23 691 drop index varchar_second_index on create_secondary_01; 692 show create table create_secondary_01; 693 Table Create Table 694 create_secondary_01 CREATE TABLE `create_secondary_01` (\n`col1` BIGINT NOT NULL,\n`col2` VARCHAR(25) DEFAULT NULL,\n`col3` FLOAT DEFAULT NULL,\n`col4` VARCHAR(50) DEFAULT NULL,\nPRIMARY KEY (`col1`),\nUNIQUE KEY `col2` (`col2`)\n) 695 create table create_secondary_02 (col1 bigint,col2 char(25),col3 float,col4 char(50)); 696 create index char_second_index on create_secondary_02(col2); 697 insert into create_secondary_02 values (1,'',20.23,'5678'),(2,'13873458290',100.00,'23'); 698 select col2,col4 from create_secondary_02; 699 col2 col4 700 5678 701 13873458290 23 702 drop index char_second_index on create_secondary_02; 703 create table create_secondary_03 (col1 bigint auto_increment,col2 int default 1000,col3 float,col4 int); 704 create index int_second_index on create_secondary_03(col2); 705 insert into create_secondary_03(col3,col4) values (20.23,4090),(100.00,5678); 706 select * from create_secondary_03; 707 col1 col2 col3 col4 708 1 1000 20.23 4090 709 2 1000 100.0 5678 710 drop index int_second_index on create_secondary_03; 711 create table create_secondary_04 (col1 bigint,col2 int primary key,col3 float,col4 bigint); 712 create index bigint_index on create_secondary_04(col1); 713 insert into create_secondary_04 values (1,2,20.23,4090),(2,4,100.00,4091),(NULL,3,0.01,NULL); 714 select * from create_secondary_04; 715 col1 col2 col3 col4 716 1 2 20.23 4090 717 2 4 100.0 4091 718 null 3 0.01 null 719 create table create_secondary_05 (col1 smallint,col2 int primary key,col3 float,col4 smallint); 720 create index smallint_second_index on create_secondary_05(col1); 721 insert into create_secondary_05 values (1,2,20.23,4090),(1,4,100.00,4091); 722 select * from create_secondary_05; 723 col1 col2 col3 col4 724 1 2 20.23 4090 725 1 4 100.0 4091 726 create table create_secondary_06 (col1 tinyint,col2 int primary key,col3 float,col4 tinyint); 727 create index tinyint_second_index on create_secondary_06(col1); 728 insert into create_secondary_06 values (1,2,20.23,56),(1,4,100.00,90); 729 select * from create_secondary_06; 730 col1 col2 col3 col4 731 1 2 20.23 56 732 1 4 100.0 90 733 create table create_secondary_07 (col1 int unsigned,col2 int primary key,col3 float,col4 int unsigned); 734 create index int_unsigned_index on create_secondary_07(col1); 735 insert into create_secondary_07 values (1,2,20.23,56),(1,4,100.00,90); 736 show create table create_secondary_07; 737 Table Create Table 738 create_secondary_07 CREATE TABLE `create_secondary_07` (\n`col1` INT UNSIGNED DEFAULT NULL,\n`col2` INT NOT NULL,\n`col3` FLOAT DEFAULT NULL,\n`col4` INT UNSIGNED DEFAULT NULL,\nPRIMARY KEY (`col2`),\nKEY `int_unsigned_index` (`col1`)\n) 739 select * from create_secondary_07; 740 col1 col2 col3 col4 741 1 2 20.23 56 742 1 4 100.0 90 743 create table create_secondary_08 (col1 bigint unsigned,col2 int primary key,col3 float,col4 bigint unsigned); 744 create index bigint_unsigned_index on create_secondary_08(col1); 745 insert into create_secondary_08 values (1,2,20.23,56),(2,4,100.00,41),(NULL,3,0.01,NULL); 746 select * from create_secondary_08; 747 col1 col2 col3 col4 748 1 2 20.23 56 749 2 4 100.0 41 750 null 3 0.01 null 751 create table create_secondary_09 (col1 bigint primary key,col2 decimal(16,8),col3 decimal(16,8)); 752 create index decimal_index on create_secondary_09(col2); 753 insert into create_secondary_09 values (1000,20.23,20.00),(1200,0.23,20.10),(1100,NULL,NULL); 754 select * from create_secondary_09; 755 col1 col2 col3 756 1000 20.23000000 20.00000000 757 1200 0.23000000 20.10000000 758 1100 null null 759 create table create_secondary_10 (col1 bigint primary key,col2 float,col3 float); 760 insert into create_secondary_10 values (1000,20.23,20.00),(1200,0.23,20.10),(1100,NULL,NULL); 761 create index float_index on create_secondary_10(col2); 762 select * from create_secondary_10; 763 col1 col2 col3 764 1000 20.23 20.0 765 1200 0.23 20.1 766 1100 null null 767 create table create_secondary_11 (col1 bigint primary key,col2 double,col3 double); 768 create index double_index on create_secondary_11(col2); 769 insert into create_secondary_11 values (1000,20.23,20.00),(1200,20.23,0.10); 770 select * from create_secondary_11; 771 col1 col2 col3 772 1000 20.23 20.0 773 1200 20.23 0.1 774 create table create_secondary_12(col1 bigint auto_increment primary key,col2 date,col3 date); 775 create index date_index on create_secondary_12(col2); 776 insert into create_secondary_12(col2,col3) values ('2013-01-01','2014-02-01'),('2013-01-01','2014-02-20'); 777 create table create_secondary_13 (col1 bigint auto_increment primary key,col2 datetime,col3 datetime); 778 create index datetime_index on create_secondary_13(col2); 779 insert into create_secondary_13(col2,col3) values (NULL,'2014-02-01 12:00:0'),(NULL,NULL); 780 create table create_secondary_14 (col1 bigint auto_increment primary key,col2 timestamp,col3 timestamp); 781 create index timestamp_index on create_secondary_14(col2); 782 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'); 783 create table create_secondary_15 (col1 bigint primary key,col2 bool); 784 create index bool_index on create_secondary_15(col2); 785 insert into create_secondary_15 values (1,TRUE),(2,FALSE),(3,TRUE); 786 select * from create_secondary_15; 787 col1 col2 788 1 true 789 2 false 790 3 true 791 create table create_secondary_16 (col1 bigint primary key,col2 blob,col3 blob); 792 create index blob_index on create_secondary_16(col2); 793 not supported: BLOB column 'col2' cannot be in index 794 drop table create_secondary_16; 795 create table create_secondary_17 (col1 bigint primary key,col2 json,col3 json); 796 create index json_index on create_secondary_17(col2); 797 not supported: JSON column 'col2' cannot be in index 798 drop table create_secondary_17; 799 create table create_secondary_18 (col1 bigint primary key,col2 text,col3 text); 800 create index text_index on create_secondary_18(col2); 801 not supported: TEXT column 'col2' cannot be in index 802 drop table create_secondary_18; 803 drop table create_secondary_01; 804 create table create_secondary_01 (col1 bigint primary key,col2 varchar(25) unique key,col3 float,col4 varchar(50),col5 int); 805 create index secondary_key on create_secondary_01(col4,col5); 806 insert into create_secondary_01 values (1,'13456789872',20.23,'5678',99),(2,'13873458290',100.00,'23',9),(3,NULL,100.00,'23',99); 807 select * from create_secondary_01; 808 col1 col2 col3 col4 col5 809 1 13456789872 20.23 5678 99 810 2 13873458290 100.0 23 9 811 3 null 100.0 23 99 812 show create table create_secondary_01; 813 Table Create Table 814 create_secondary_01 CREATE TABLE `create_secondary_01` (\n`col1` BIGINT NOT NULL,\n`col2` VARCHAR(25) DEFAULT NULL,\n`col3` FLOAT DEFAULT NULL,\n`col4` VARCHAR(50) DEFAULT NULL,\n`col5` INT DEFAULT NULL,\nPRIMARY KEY (`col1`),\nUNIQUE KEY `col2` (`col2`),\nKEY `secondary_key` (`col4`,`col5`)\n) 815 drop index secondary_key on create_secondary_01; 816 truncate table create_secondary_01; 817 show create table create_secondary_01; 818 Table Create Table 819 create_secondary_01 CREATE TABLE `create_secondary_01` (\n`col1` BIGINT NOT NULL,\n`col2` VARCHAR(25) DEFAULT NULL,\n`col3` FLOAT DEFAULT NULL,\n`col4` VARCHAR(50) DEFAULT NULL,\n`col5` INT DEFAULT NULL,\nPRIMARY KEY (`col1`),\nUNIQUE KEY `col2` (`col2`)\n) 820 drop table create_secondary_01; 821 create table create_secondary_01 (col1 bigint primary key,col2 varchar(25) unique key,col3 float,col4 varchar(50),col5 int); 822 create index secondary_key1 on create_secondary_01(col1); 823 create index secondary_key2 on create_secondary_01(col1,col2); 824 show create table create_secondary_01; 825 Table Create Table 826 create_secondary_01 CREATE TABLE `create_secondary_01` (\n`col1` BIGINT NOT NULL,\n`col2` VARCHAR(25) DEFAULT NULL,\n`col3` FLOAT DEFAULT NULL,\n`col4` VARCHAR(50) DEFAULT NULL,\n`col5` INT DEFAULT NULL,\nPRIMARY KEY (`col1`),\nUNIQUE KEY `col2` (`col2`),\nKEY `secondary_key1` (`col1`),\nKEY `secondary_key2` (`col1`,`col2`)\n) 827 drop index secondary_key1 on create_secondary_01; 828 drop index secondary_key2 on create_secondary_01; 829 create index secondary_key1 on create_secondary_01(col4,col5); 830 create index secondary_key1 on create_secondary_01(col4,col5); 831 duplicate key name 'secondary_key1' 832 drop table create_secondary_01; 833 create table create_secondary_01 (col1 bigint primary key,col2 varchar(25) unique key,col3 float,col4 varchar(50),col5 int); 834 drop index secondary_key1 on create_secondary_01; 835 internal error: not found index: secondary_key1 836 create unique index secondary_key1 on create_secondary_01(col1); 837 create index secondary_key1 on create_secondary_01(col1); 838 duplicate key name 'secondary_key1' 839 show create table create_secondary_01; 840 Table Create Table 841 create_secondary_01 CREATE TABLE `create_secondary_01` (\n`col1` BIGINT NOT NULL,\n`col2` VARCHAR(25) DEFAULT NULL,\n`col3` FLOAT DEFAULT NULL,\n`col4` VARCHAR(50) DEFAULT NULL,\n`col5` INT DEFAULT NULL,\nPRIMARY KEY (`col1`),\nUNIQUE KEY `col2` (`col2`),\nUNIQUE KEY `secondary_key1` (`col1`)\n)