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

     1  
     2  -- test auto_increment as primary key
     3  drop table if exists t1;
     4  
     5  create table t1(
     6  a bigint primary key auto_increment,
     7  b varchar(10)
     8  );
     9  
    10  show create table t1;
    11  
    12  insert into t1(b) values ('bbb');
    13  
    14  -- echo error msg: tae data: duplicate
    15  insert into t1 values (1, 'ccc');
    16  
    17  insert into t1 values (3, 'ccc');
    18  insert into t1(b) values ('bbb1111');
    19  
    20  
    21  select * from t1 order by a;
    22  
    23  insert into t1 values (2, 'aaaa1111');
    24  select * from t1 order by a;
    25  
    26  insert into t1(b) values ('aaaa1111');
    27  
    28  select * from t1 order by a;
    29  
    30  insert into t1 values (100, 'xxxx');
    31  insert into t1(b) values ('xxxx');
    32  
    33  select * from t1 order by a;
    34  
    35  insert into t1 values (0, 'xxxx');
    36  insert into t1(b) values ('xxxx');
    37  
    38  insert into t1 values (-1000, 'yyy');
    39  
    40  select * from t1 order by a;
    41  
    42  insert into t1 values ('-2000', 'yyy');
    43  insert into t1 values ('200', 'yyy');
    44  
    45  -- echo error msg;
    46  insert into t1 values ('0', 'yyy');
    47  
    48  select * from t1;
    49  
    50  insert into t1 values (NULL, 'yyy');
    51  select * from t1 order by a;
    52  
    53  -- echo error
    54  update t1 set a=0 where b='ccc';
    55  
    56  update t1 set a='200' where b='ccc';
    57  
    58  insert into t1(b) values ('xefrsdfgds');
    59  
    60  select * from t1 order by a;
    61  
    62  -- test bigint min value
    63  insert into t1 values (-9223372036854775808,'xefrsdfgds');
    64  -- echo error msg
    65  insert into t1 values (-9223372036854775809,'xefrsdfgds');
    66  
    67  -- test bigint max value
    68  insert into t1 values (9223372036854775807,'xefrsdfgds');
    69  -- echo error msg
    70  insert into t1 values (9223372036854775808,'xefrsdfgds');
    71  insert into t1(b) values ('eeeee');
    72  
    73  drop table t1;
    74  
    75  
    76  -- tet int max value and min value
    77  drop table if exists t2;
    78  create table t2 (
    79  c int primary key auto_increment,
    80  d varchar(10)
    81  );
    82  
    83  insert into t2 values (-2147483648, 'aaa');
    84  select * from t2 order by c;
    85  -- echo error
    86  insert into t2 values (-2147483649, 'aaa');
    87  
    88  insert into t2(d) values ('1111');
    89  select * from t2 order by c;
    90  
    91  insert into t2 values(2147483647, 'bbb');
    92  -- echo error
    93  insert into t2 values(2147483648, 'bbb');
    94  
    95  insert into t2(d) values ('22222');
    96  select * from t2 order by c;
    97  
    98  drop table t2;
    99  
   100  
   101  drop table if exists t3;
   102  create table t3(
   103  a int primary key auto_increment,
   104  b varchar(10)
   105  );
   106  
   107  insert into t3 values (-19, 'aaa');
   108  insert into t3(b) values ('bbb');
   109  select * from t3 order by a;
   110  
   111  delete from t3 where b='bbb';
   112  insert into t3(b) values ('bbb');
   113  select * from t3 order by a;
   114  
   115  insert into t3 values (1, 'aaa');
   116  -- error msg
   117  update t3 set a=10 where b='aaa';
   118  update t3 set a=10 where b='bbb';
   119  select * from t3 order by a;
   120  
   121  insert into t3 values (2,'ccc');
   122  select * from t3 order by a;
   123  
   124  delete from t3;
   125  insert into t3(b) values ('bbb');
   126  select * from t3 order by a;
   127  
   128  drop table t3;
   129  
   130  -- test auto_increment as not primary key
   131  drop table if exists t4;
   132  
   133  create table t4(
   134  a bigint  auto_increment,
   135  b varchar(10)
   136  );
   137  
   138  insert into t4(b) values ('bbb');
   139  
   140  insert into t4 values (1, 'ccc');
   141  
   142  insert into t4 values (3, 'ccc');
   143  insert into t4(b) values ('bbb1111');
   144  
   145  select * from t4 order by a;
   146  
   147  insert into t4 values (2, 'aaaa1111');
   148  select * from t4 order by a;
   149  
   150  insert into t4(b) values ('aaaa1111');
   151  
   152  select * from t4 order by a;
   153  
   154  insert into t4 values (100, 'xxxx');
   155  insert into t4(b) values ('xxxx');
   156  
   157  select * from t4 order by a;
   158  
   159  insert into t4 values (0, 'xxxx');
   160  insert into t4(b) values ('xxxx');
   161  
   162  insert into t4 values (-1000, 'yyy');
   163  
   164  select * from t4 order by a;
   165  
   166  insert into t4 values ('-2000', 'yyy');
   167  insert into t4 values ('200', 'yyy');
   168  
   169  -- echo error msg;
   170  insert into t4 values ('0', 'yyy');
   171  
   172  select * from t4 order by a;
   173  
   174  insert into t4 values (NULL, 'yyy');
   175  select * from t4 order by a;
   176  
   177  -- echo error
   178  update t4 set a=0 where b='ccc';
   179  
   180  update t4 set a='200' where b='ccc';
   181  
   182  insert into t4(b) values ('xefrsdfgds');
   183  
   184  select * from t4 order by a;
   185  
   186  -- test bigint min value
   187  insert into t4 values (-9223372036854775808,'xefrsdfgds');
   188  -- echo error msg
   189  insert into t4 values (-9223372036854775809,'xefrsdfgds');
   190  
   191  -- test bigint max value
   192  insert into t4 values (9223372036854775807,'xefrsdfgds');
   193  -- echo error msg
   194  insert into t4 values (9223372036854775808,'xefrsdfgds');
   195  insert into t4(b) values ('eeeee');
   196  
   197  drop table t4;
   198  
   199  -- test no primary key auto_increment columns
   200  
   201  drop table if exists t5;
   202  create table t5 (
   203  c int auto_increment,
   204  d varchar(10)
   205  );
   206  
   207  insert into t5 values (-2147483648, 'aaa');
   208  select * from t5 order by c;
   209  -- echo error
   210  insert into t5 values (-2147483649, 'aaa');
   211  
   212  insert into t5(d) values ('1111');
   213  select * from t5 order by c;
   214  
   215  insert into t5 values(2147483647, 'bbb');
   216  -- echo error
   217  insert into t5 values(2147483648, 'bbb');
   218  select * from t5 order by c;
   219  
   220  insert into t5(d) values ('22222');
   221  select * from t5 order by c;
   222  
   223  drop table t5;
   224  
   225  
   226  -- test one table more auto_increment columns.
   227  
   228  drop table if exists t6;
   229  create table t6(
   230  a int primary key auto_increment,
   231  b bigint auto_increment,
   232  c int auto_increment,
   233  d int auto_increment,
   234  e bigint auto_increment
   235  );
   236  
   237  show create table t6;
   238  
   239  insert into t6 values (),(),(),();
   240  select * from t6 order by a;
   241  
   242  insert into t6 values (NULL, NULL, NULL, NULL, NULL);
   243  select * from t6 order by a;
   244  
   245  insert into t6(b,c,d) values (NULL,NULL,NULL);
   246  select * from t6 order by a;
   247  
   248  insert into t6(a,b) values (100, 400);
   249  select * from t6 order by a;
   250  
   251  insert into t6(c,d,e) values (200, 200, 200);
   252  select * from t6;
   253  
   254  insert into t6(c,d,e) values (200, 400, 600);
   255  select * from t6;
   256  
   257  -- echo error: duplicate
   258  insert into t6(a,b) values (100, 400);
   259  select * from t6 order by a;
   260  
   261  insert into t6 values ('0','0','0','0','0');
   262  select * from t6 order by a;
   263  
   264  -- echo error
   265  insert into t6 values ('a','a','a','a','a');
   266  select * from t6 order by a;
   267  
   268  insert into t6 values ('-1',0,0,0,0);
   269  select * from t6 order by a;
   270  
   271  drop table t6;
   272  
   273  -- Test for the presence of autoincrement columns in multiple tables
   274  drop table if exists t8;
   275  create table t8(
   276  a int auto_increment primary key,
   277  b int auto_increment
   278  );
   279  
   280  drop table if exists t9;
   281  create table t9(
   282  c int auto_increment primary key,
   283  d int auto_increment
   284  );
   285  
   286  insert into t8 values (),();
   287  select * from t8 order by a;
   288  
   289  insert into t9 values (),();
   290  select * from t9 order by c;
   291  
   292  insert into t8(a) values (19);
   293  select * from t8 order by a;
   294  
   295  insert into t9 (c) values (19);
   296  select * from t9 order by c;
   297  
   298  insert into t8 values (),();
   299  select * from t8 order by a;
   300  
   301  insert into t9 values (),();
   302  select * from t9 order by c;
   303  
   304  insert into t8(b) values (1);
   305  select * from t8 order by a;
   306  
   307  insert into t9 (d) values (1);
   308  select * from t9 order by c;
   309  
   310  -- echo error
   311  insert into t8(a) values (1);
   312  select * from t8 order by a;
   313  
   314  insert into t9 (c) values (1);
   315  select * from t9 order by c;
   316  
   317  drop table t8;
   318  drop table t9;
   319  
   320  -- test truncate table,auto_increment columns whether it will be cleared.
   321  drop table if exists t10;
   322  create table t10(
   323  a int auto_increment primary key,
   324  b int auto_increment
   325  );
   326  
   327  insert into t10 values (10, 10);
   328  insert into t10 values (),(),();
   329  select * from t10 order by a;
   330  truncate table t10;
   331  insert into t10 values ();
   332  select * from t10 order by a;
   333  
   334  drop table t10;
   335  
   336  
   337  -- test load data
   338  drop table if exists t11;
   339  create table t11(
   340  a int primary key auto_increment,
   341  b bigint auto_increment,
   342  c varchar(25)
   343  );
   344  
   345  load data infile '$resources/auto_increment_columns/auto_increment_1.csv' into table t11 fields terminated by ',';
   346  select * from t11 order by a;
   347  drop table t11;
   348  
   349  
   350  drop table if exists t12;
   351  create table t12(
   352  a int primary key auto_increment,
   353  b bigint auto_increment,
   354  c varchar(25)
   355  );
   356  load data infile '$resources/auto_increment_columns/auto_increment_2.csv' into table t12 fields terminated by ',';
   357  select * from t12 order by a;
   358  
   359  drop table t12;
   360  
   361  drop table if exists t13;
   362  create table t13(
   363  a int primary key auto_increment,
   364  b bigint auto_increment,
   365  c varchar(25)
   366  );
   367  
   368  load data infile '$resources/auto_increment_columns/auto_increment_3.csv' into table t13 fields terminated by ',';
   369  select * from t13 order by a;
   370  
   371  drop table t13;
   372  
   373  create table t1(a int default(-1) auto_increment);
   374  create table t1(a int primary key default(-1) auto_increment);
   375  create table t1(a bigint default(-1) auto_increment);
   376  create table t1(a bigint primary key default(-1) auto_increment);
   377  create table t1(a int, b int default(10), c int auto_increment);
   378  show create table t1;
   379  drop table t1;
   380  
   381  create table t1(a tinyint auto_increment);
   382  insert into t1 values(null), (3), (null), (6), (null), (127);
   383  select * from t1;
   384  insert into t1 values();
   385  
   386  drop table t1;
   387  create table t1(a smallint auto_increment);
   388  insert into t1 values(null), (3), (null), (6), (null), (32767);
   389  select * from t1;
   390  insert into t1 values();
   391  
   392  drop table t1;
   393  create table t1(a int auto_increment);
   394  insert into t1 values(null), (3), (null), (6), (null), (2147483647);
   395  select * from t1;
   396  insert into t1 values();
   397  
   398  drop table t1;
   399  create table t1(a bigint auto_increment);
   400  insert into t1 values(null), (3), (null), (6), (null), (9223372036854775807);
   401  select * from t1;
   402  insert into t1 values();
   403  
   404  drop table t1;
   405  create table t1(a tinyint unsigned auto_increment);
   406  insert into t1 values(null), (3), (null), (6), (null), (255);
   407  select * from t1;
   408  insert into t1 values();
   409  
   410  drop table t1;
   411  create table t1(a smallint unsigned auto_increment);
   412  insert into t1 values(null), (3), (null), (6), (null), (65535);
   413  select * from t1;
   414  insert into t1 values();
   415  
   416  drop table t1;
   417  create table t1(a int unsigned auto_increment);
   418  insert into t1 values(null), (3), (null), (6), (null), (4294967295);
   419  select * from t1;
   420  insert into t1 values();
   421  
   422  drop table t1;
   423  create table t1(a bigint unsigned auto_increment);
   424  insert into t1 values(null), (3), (null), (6), (null), (18446744073709551615);
   425  select * from t1;
   426  insert into t1 values();
   427  drop table t1;
   428  drop table if exists t1;
   429  create table t1 (a int not null auto_increment, b int);
   430  insert into t1(b) values (1);
   431  select * from t1;
   432  
   433  drop table t1;
   434  create table t1(a int auto_increment primary key);
   435  insert into t1 values();
   436  select last_insert_id();
   437  insert into t1 values(11);
   438  insert into t1 values();
   439  select last_insert_id();
   440  create table t2(a int auto_increment primary key);
   441  insert into t2 values();
   442  select last_insert_id();
   443  insert into t2 values(100);
   444  insert into t2 values();
   445  select last_insert_id();
   446  insert into t1 values();
   447  select last_insert_id();
   448  insert into t2 values();
   449  select last_insert_id();
   450  
   451  -- test for auto_increment column with default value
   452  drop table if exists t1;
   453  create table t1(a int auto_increment primary key) auto_increment = 0;
   454  insert into t1 values();
   455  select last_insert_id();
   456  insert into t1 values(11);
   457  insert into t1 values();
   458  select last_insert_id();
   459  
   460  drop table if exists t1;
   461  create table t1(a int auto_increment primary key) auto_increment = 10;
   462  insert into t1 values();
   463  select last_insert_id();
   464  insert into t1 values(101);
   465  insert into t1 values();
   466  select last_insert_id();
   467  
   468  drop table if exists t1;
   469  create table t1(a int auto_increment primary key) auto_increment = 100;
   470  insert into t1 values();
   471  select last_insert_id();
   472  insert into t1 values(7);
   473  insert into t1 values();
   474  select last_insert_id();