github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/auto_increment/auto_increment.sql (about)

     1  -- @suit
     2  -- @case
     3  -- @desc:auto_increment
     4  -- @label:bvt
     5  
     6  -- auto_increment = 0
     7  drop table if exists auto_increment01;
     8  create table auto_increment01(col1 int auto_increment primary key)auto_increment = 0;
     9  select * from auto_increment01;
    10  Insert into auto_increment01 values();
    11  select last_insert_id();
    12  Select * from auto_increment01;
    13  Insert into auto_increment01 values(1);
    14  Select * from auto_increment01;
    15  drop table auto_increment01;
    16  
    17  
    18  -- auto_increment > 0
    19  -- @bvt:issue#10836
    20  Drop table if exists auto_increment02;
    21  Create table auto_increment02(col1 int auto_increment unique key)auto_increment = 10;
    22  Insert into auto_increment02 values();
    23  Select * from auto_increment02;
    24  Insert into auto_increment02 values(10);
    25  insert into auto_increment02 values(100);
    26  select last_insert_id();
    27  Select * from auto_increment02;
    28  Drop table auto_increment02;
    29  -- @bvt:issue
    30  
    31  
    32  -- auto_increment > 0 and have duplicate value
    33  Drop table if exists auto_increment03;
    34  create table auto_increment03(col1 int auto_increment primary key) auto_increment = 10000;
    35  Insert into auto_increment03 values();
    36  select last_insert_id();
    37  Insert into auto_increment03 values(10000);
    38  Insert into auto_increment03 values(10000);
    39  Select * from auto_increment03;
    40  Drop table auto_increment03;
    41  
    42  
    43  -- auto_increment > 0 and col is primary key: check for duplicate primary keys
    44  Drop table if exists auto_increment04;
    45  Create table auto_increment04(col1 int primary key auto_increment) auto_increment = 10;
    46  insert into auto_increment04 values();
    47  Select * from auto_increment04;
    48  Insert into auto_increment04 values();
    49  select last_insert_id();
    50  Insert into auto_increment04 values(100);
    51  Insert into auto_increment04 values(200);
    52  Insert into auto_increment04 values(10);
    53  Insert into auto_increment04 values(11);
    54  Select * from auto_increment04;
    55  Drop table auto_increment04;
    56  
    57  
    58  -- auto_increment > 0 and column constraint unique index
    59  Drop table if exists auto_increment05;
    60  Create table auto_increment05(col1 int unique key auto_increment) auto_increment = 10000;
    61  Insert into auto_increment05 values();
    62  Insert into auto_increment05 values();
    63  Insert into auto_increment05 values();
    64  select last_insert_id();
    65  Select * from auto_increment05;
    66  Insert into auto_increment05 values(10001);
    67  Insert into auto_increment05 values(10002);
    68  Select * from auto_increment05;
    69  Drop table auto_increment05;
    70  
    71  
    72  -- auto_increment > 0 and test the threshold value of int
    73  Drop table if exists auto_increment06;
    74  Create table auto_increment06(col1 int auto_increment primary key) auto_increment = 2147483646;
    75  Insert into auto_increment06 values();
    76  Insert into auto_increment06 values();
    77  Insert into auto_increment06 values();
    78  select last_insert_id();
    79  Select * from auto_increment06;
    80  Insert into auto_increment06 values(10001);
    81  Insert into auto_increment06 values(10002);
    82  Select * from auto_increment06;
    83  Drop table auto_increment06;
    84  
    85  
    86  -- auto_increment > 0 and test the threshold value of smallint
    87  Drop table if exists auto_increment07;
    88  Create table auto_increment07(col1 smallint auto_increment primary key) auto_increment = 32766;
    89  Insert into auto_increment07 values();
    90  Insert into auto_increment07 values();
    91  Insert into auto_increment07 values();
    92  select last_insert_id();
    93  Select * from auto_increment07;
    94  Drop table auto_increment07;
    95  
    96  
    97  -- auto_increment > 0 and test the threshold value of bigint
    98  Drop table if exists auto_increment08;
    99  Create table auto_increment08(col1 bigint auto_increment primary key) auto_increment = 9223372036854775806;
   100  Insert into auto_increment08 values();
   101  Insert into auto_increment08 values();
   102  Insert into auto_increment08 values();
   103  select last_insert_id();
   104  Select * from auto_increment08;
   105  Drop table auto_increment08;
   106  
   107  
   108  -- auto_increment > 0 and test the threshold value of tinyint unsigned
   109  Drop table if exists auto_increment09;
   110  Create table auto_increment09(col1 tinyint unsigned auto_increment primary key) auto_increment = 254;
   111  Insert into auto_increment09 values();
   112  Insert into auto_increment09 values();
   113  Insert into auto_increment09 values();
   114  select last_insert_id();
   115  Select * from auto_increment09;
   116  Drop table auto_increment09;
   117  
   118  
   119  -- auto_increment > 0 and the column constraint unique index
   120  -- @bvt:issue#10834
   121  Drop table if exists auto_increment10;
   122  Create table auto_increment10(col1 int auto_increment, col2 int, unique index(col1)) auto_increment = 254;
   123  Insert into auto_increment10(col2) values(100);
   124  Insert into auto_increment10(col2) values(200);
   125  insert into auto_increment10(col2) values(100);
   126  select last_insert_id();
   127  Select * from auto_increment10;
   128  Drop table auto_increment10;
   129  -- @bvt:issue
   130  
   131  
   132  -- auto_increment > 0 and update/delete
   133  Drop table if exists auto_increment11;
   134  Create table auto_increment11(col1 int auto_increment primary key) auto_increment = 100;
   135  insert into auto_increment11 values();
   136  Insert into auto_increment11 values();
   137  Insert into auto_increment11 values();
   138  select last_insert_id();
   139  Select * from auto_increment11;
   140  Delete from auto_increment11 where col1 = 100;
   141  Update auto_increment11 set col1 = 200 where col1 = 101;
   142  Select * from auto_increment11;
   143  Drop table auto_increment11;
   144  
   145  
   146  -- auto_increment > 0 and insert into table non-int type
   147  -- @bvt:issue#10842
   148  Drop table if exists auto_increment12;
   149  create table auto_increment12(col1 int auto_increment primary key)auto_increment = 10;
   150  Insert into auto_increment12 values();
   151  Insert into auto_increment12 values();
   152  Select * from auto_increment12;
   153  Insert into auto_increment12 values(16.898291);
   154  insert into auto_increment12 values(124312.4321424324);
   155  insert into auto_increment12 values();
   156  select last_insert_id();
   157  Select * from auto_increment12;
   158  Drop table auto_increment12;
   159  -- @bvt:issue
   160  
   161  
   162  -- auto_increment > 0 and truncate table
   163  Drop table if exists auto_increment10;
   164  Create table auto_increment13(col1 int auto_increment primary key)auto_increment = 30000;
   165  Insert into auto_increment13 values();
   166  Insert into auto_increment13 values();
   167  select * from auto_increment13;
   168  Truncate table auto_increment13;
   169  Insert into auto_increment13 values();
   170  select last_insert_id();
   171  Insert into auto_increment13 values(10000);
   172  Select * from auto_increment13;
   173  Drop table auto_increment13;
   174  
   175  
   176  -- auto_increment > 0, order by
   177  Drop table if exists auto_increment14;
   178  Create table auto_increment14(col1 int primary key auto_increment, col2 varchar(10))auto_increment = 100;
   179  insert into auto_increment14 values (-2147483648, 'aaa');
   180  select * from auto_increment14 order by c;
   181  insert into auto_increment14 values (-2147483649, 'aaa');
   182  Insert into auto_increment14 values();
   183  Insert into auto_increment14 values();
   184  Select last_insert_id();
   185  insert into auto_increment14(col2) values ('22222');
   186  select * from auto_increment14 order by col1;
   187  select * from auto_increment14 order by col1 desc;
   188  Drop table auto_increment14;
   189  
   190  
   191  -- test one table more auto_increment columns
   192  drop table if exists auto_increment15;
   193  create table auto_increment15(
   194                                   a int primary key auto_increment,
   195                                   b bigint auto_increment,
   196                                   c int auto_increment,
   197                                   d int auto_increment,
   198                                   e bigint auto_increment
   199  );
   200  show create table auto_increment15;
   201  insert into auto_increment15 values (),(),(),();
   202  select * from auto_increment15 order by a;
   203  insert into auto_increment15 values (NULL, NULL, NULL, NULL, NULL);
   204  select * from auto_increment15 order by a;
   205  insert into auto_increment15(b,c,d) values (NULL,NULL,NULL);
   206  select * from auto_increment15 order by a;
   207  insert into auto_increment15(a,b) values (100, 400);
   208  select * from auto_increment15 order by a;
   209  insert into auto_increment15(c,d,e) values (200, 200, 200);
   210  select * from auto_increment15;
   211  insert into auto_increment15(c,d,e) values (200, 400, 600);
   212  select * from auto_increment15;
   213  Drop table auto_increment15;
   214  
   215  
   216  -- abnormal test:auto_increment < 0
   217  Drop table if exists auto_increment16;
   218  Create table auto_increment16(col1 int auto_increment)auto_increment < 0;
   219  Drop table auto_increment16;
   220  
   221  
   222  -- temporary table: auto_incerment = 0
   223  -- @bvt:issue#10903
   224  drop table if exists auto_increment01;
   225  create temporary table auto_increment01(col1 int auto_increment primary key)auto_increment = 0;
   226  select * from auto_increment01;
   227  Insert into auto_increment01 values();
   228  select last_insert_id();
   229  Select * from auto_increment01;
   230  Insert into auto_increment01 values(1);
   231  Select * from auto_increment01;
   232  drop table auto_increment01;
   233  -- @bvt:issue
   234  
   235  
   236  -- temporary table:auto_increment > 0
   237  -- @bvt:issue#10836
   238  Drop table if exists auto_increment02;
   239  Create temporary table auto_increment02(col1 int auto_increment unique key)auto_increment = 10;
   240  Insert into auto_increment02 values();
   241  select last_insert_id();
   242  Select * from auto_increment02;
   243  Insert into auto_increment02 values(10);
   244  insert into auto_increment02 values(100);
   245  Select * from auto_increment02;
   246  Drop table auto_increment02;
   247  -- @bvt:issue
   248  
   249  
   250  -- temporary table:auto_increment > 0 and have duplicate value
   251  -- @bvt:issue#10903
   252  Drop table if exists auto_increment03;
   253  create temporary table auto_increment03(col1 int auto_increment primary key) auto_increment = 10000;
   254  Insert into auto_increment03 values();
   255  Insert into auto_increment03 values(10000);
   256  Insert into auto_increment03 values(10000);
   257  Insert into auto_increment03 values();
   258  select last_insert_id();
   259  Select * from auto_increment03;
   260  Drop table auto_increment03;
   261  -- @bvt:issue
   262  
   263  
   264  -- temporary table:auto_increment > 0 and col is primary key: check for duplicate primary keys
   265  -- @bvt:issue#10903
   266  Drop table if exists auto_increment04;
   267  Create temporary table auto_increment04(col1 int primary key auto_increment) auto_increment = 10;
   268  insert into auto_increment04 values();
   269  Select * from auto_increment04;
   270  Insert into auto_increment04 values();
   271  select last_insert_id();
   272  Insert into auto_increment04 values(100);
   273  Insert into auto_increment04 values(200);
   274  Insert into auto_increment04 values(10);
   275  Insert into auto_increment04 values(11);
   276  Select * from auto_increment04;
   277  Drop table auto_increment04;
   278  -- @bvt:issue
   279  
   280  
   281  -- temporary table:auto_increment > 0 and column constraint unique index
   282  -- @bvt:issue#10834
   283  Drop table if exists auto_increment05;
   284  Create temporary table auto_increment05(col1 int unique key auto_increment) auto_increment = 10000;
   285  Insert into auto_increment05 values();
   286  Insert into auto_increment05 values();
   287  Insert into auto_increment05 values();
   288  select last_insert_id();
   289  Select * from auto_increment05;
   290  Insert into auto_increment05 values(10001);
   291  Insert into auto_increment05 values(10002);
   292  Select * from auto_increment05;
   293  Drop table auto_increment05;
   294  -- @bvt:issue#10834
   295  
   296  
   297  -- temporary table:auto_increment > 0 and test the threshold value of int unsigned
   298  Drop table if exists auto_increment06;
   299  Create temporary table auto_increment06(col1 int unsigned auto_increment primary key) auto_increment = 2147483646;
   300  Insert into auto_increment06 values();
   301  Insert into auto_increment06 values();
   302  Insert into auto_increment06 values();
   303  select last_insert_id();
   304  Select * from auto_increment06;
   305  Insert into auto_increment06 values(10001);
   306  Insert into auto_increment06 values(10002);
   307  Select * from auto_increment06;
   308  Drop table auto_increment06;
   309  
   310  
   311  -- auto_increment > 0 and test the threshold value of smallint unsigned
   312  Drop table if exists auto_increment07;
   313  Create table auto_increment07(col1 smallint unsigned auto_increment primary key) auto_increment = 65534;
   314  Insert into auto_increment07 values();
   315  Insert into auto_increment07 values();
   316  Insert into auto_increment07 values();
   317  Insert into auto_increment07 values();
   318  select last_insert_id();
   319  Select * from auto_increment07;
   320  Drop table auto_increment07;
   321  
   322  
   323  -- auto_increment > 0 and test the threshold value of bigint unsigned
   324  Drop table if exists auto_increment08;
   325  Create table auto_increment08(col1 bigint unsigned auto_increment primary key) auto_increment = 9223372036854775806;
   326  Insert into auto_increment08 values();
   327  Insert into auto_increment08 values();
   328  Insert into auto_increment08 values();
   329  select last_insert_id();
   330  Select * from auto_increment08;
   331  Drop table auto_increment08;
   332  
   333  
   334  -- auto_increment > 0 and test the threshold value of tinyint
   335  Drop table if exists auto_increment09;
   336  Create table auto_increment09(col1 tinyint auto_increment primary key) auto_increment = 254;
   337  Insert into auto_increment09 values();
   338  Insert into auto_increment09 values();
   339  Insert into auto_increment09 values();
   340  select last_insert_id();
   341  Select * from auto_increment09;
   342  Drop table auto_increment09;
   343  
   344  
   345  -- temporary table:auto_increment > 0 and column constraint unique index
   346  -- @bvt:issue#10834
   347  Drop table if exists auto_increment10;
   348  Create temporary table auto_increment10(col1 int auto_increment, col2 int, unique index(col1)) auto_increment = 3267183;
   349  Insert into auto_increment10(col2) values(100);
   350  Insert into auto_increment10(col2) values(200);
   351  insert into auto_increment10(col2) values(100);
   352  select last_insert_id();
   353  Select * from auto_increment10;
   354  Drop table auto_increment10;
   355  -- @bvt:issue
   356  
   357  
   358  -- temporary table:auto_increment > 0 and update/delete
   359  -- @bvt:issue#10903
   360  Drop table if exists auto_increment11;
   361  Create temporary table auto_increment11(col1 int auto_increment primary key) auto_increment = 100;
   362  insert into auto_increment11 values();
   363  Insert into auto_increment11 values();
   364  Insert into auto_increment11 values();
   365  select last_insert_id();
   366  Select * from auto_increment11;
   367  Delete from auto_increment11 where col1 = 100;
   368  -- @bvt:issue#10903
   369  -- @bvt:issue#10834
   370  Update auto_increment11 set col1 = 200 where col1 = 101;
   371  Select * from auto_increment11;
   372  Drop table auto_increment11;
   373  -- @bvt:issue
   374  
   375  
   376  -- temporary table:auto_increment > 0 and insert into table non-int type
   377  -- @bvt:issue#10842
   378  Drop table if exists auto_increment12;
   379  create temporary table auto_increment12(col1 int auto_increment primary key)auto_increment = 10;
   380  Insert into auto_increment12 values();
   381  Insert into auto_increment12 values();
   382  Select * from auto_increment12;
   383  Insert into auto_increment12 values(16.898291);
   384  insert into auto_increment12 values();
   385  select last_insert_id();
   386  Select * from auto_increment12;
   387  Drop table auto_increment12;
   388  -- @bvt:issue
   389  
   390  
   391  -- temporary:auto_increment > 0 and truncate table, auto_increment columns whether it will be cleared.
   392  Drop table if exists auto_increment13;
   393  Create table auto_increment13(col1 int auto_increment primary key)auto_increment = 30000;
   394  Insert into auto_increment13 values();
   395  Insert into auto_increment13 values();
   396  select * from auto_increment13;
   397  Truncate table auto_increment13;
   398  Insert into auto_increment13 values();
   399  select last_insert_id();
   400  Insert into auto_increment13 values(10000);
   401  Select * from auto_increment13;
   402  Drop table auto_increment13;
   403  
   404  
   405  -- temporary: auto_increment > 0, order by
   406  Drop table if exists auto_increment14;
   407  Create table auto_increment14(col1 int primary key auto_increment, col2 varchar(10))auto_increment = 100;
   408  insert into auto_increment14 values (-2147483648, 'aaa');
   409  select * from auto_increment14 order by c;
   410  insert into auto_increment14 values (-2147483649, 'aaa');
   411  Insert into auto_increment14 values();
   412  Insert into auto_increment14 values();
   413  Select last_insert_id();
   414  insert into auto_increment14(col2) values ('22222');
   415  select * from auto_increment14 order by col1;
   416  select * from auto_increment14 order by col1 desc;
   417  Drop table auto_increment14;
   418  
   419  
   420  -- temporary: test one table more auto_increment columns
   421  -- @bvt:issue#10903
   422  drop table if exists auto_increment15;
   423  create temporary table auto_increment15(
   424  a int primary key auto_increment,
   425  b bigint auto_increment,
   426  c int auto_increment,
   427  d int auto_increment,
   428  e bigint auto_increment
   429  )auto_increment = 100;
   430  show create table auto_increment15;
   431  insert into auto_increment15 values (),(),(),();
   432  select * from auto_increment15 order by a;
   433  insert into auto_increment15 values (NULL, NULL, NULL, NULL, NULL);
   434  select * from auto_increment15 order by a;
   435  insert into auto_increment15(b,c,d) values (NULL,NULL,NULL);
   436  select * from auto_increment15 order by a;
   437  insert into auto_increment15(a,b) values (100, 400);
   438  select * from auto_increment15 order by a;
   439  insert into auto_increment15(c,d,e) values (200, 200, 200);
   440  select * from auto_increment15;
   441  insert into auto_increment15(c,d,e) values (200, 400, 600);
   442  select * from auto_increment15;
   443  Drop table auto_increment15;
   444  -- @bvt:issue
   445  
   446  
   447  -- temporary table:abnormal test:auto_increment < 0
   448  Drop table if exists auto_increment16;
   449  Create temporary table auto_increment16(col1 int auto_increment)auto_increment < 0;
   450  Drop table auto_increment16;
   451  
   452  -- global variable: auto_increment_increment
   453  drop table if exists auto_increment17;
   454  set global auto_increment_offset= 10;
   455  create table auto_increment17(col1 int auto_increment);
   456  insert into auto_increment17 values();
   457  select * from auto_increment17;
   458  drop table auto_increment17;
   459  set auto_increment_offset = 100;
   460  create table auto_increment17(col1 int auto_increment);
   461  insert into auto_increment17 values();
   462  select * from auto_increment17;
   463  drop table auto_increment17;
   464  set auto_increment_offset = 1;
   465  set global auto_increment_offset= 1;