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;