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

     1  --sequence name normal case
     2  create sequence seq_01 as int start 30;
     3  create sequence `123` start 30;
     4  select nextval('123');
     5  create sequence SEQ increment 100 start 30;
     6  create sequence sEq increment 100 start 30;
     7  create sequence `中文` maxvalue 6899 cycle;
     8  select nextval('中文');
     9  select nextval('中文'),currval('中文');
    10  create sequence `test@123456`;
    11  select nextval('test@123456');
    12  select nextval('test@123456'),currval('test@123456');
    13  create sequence _acc;
    14  select nextval('_acc');
    15  select nextval('_acc'),currval('_acc');
    16  create sequence `ab.cd` start with 1;
    17  select nextval('ab.cd');
    18  select nextval('ab.cd'),currval('ab.cd');
    19  create sequence `abcddddddfsfafaffsefsfsefljofiseosfjosisssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss24444444444444444444444444222222222@fffffffffffffffffffffffffffffffffffffffffffffffffffff` start 30;
    20  select nextval('abcddddddfsfafaffsefsfsefljofiseosfjosisssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss24444444444444444444444444222222222@fffffffffffffffffffffffffffffffffffffffffffffffffffff') as s1;
    21  
    22  --abnormal sequence name include same as table,view,external table,index name
    23  create table if not exists seq_temp(col1 int);
    24  create sequence seq_temp start 10;
    25  drop table seq_temp;
    26  create table if not exists table_temp(col1 int);
    27  create view seq_temp as select * from table_temp;
    28  create sequence seq_temp start 10;
    29  drop view seq_temp;
    30  drop table table_temp;
    31  create external table seq_temp(num_col1 tinyint,num_col2 smallint,num_col3 int,num_col4 bigint,num_col5 tinyint unsigned,num_col6 smallint unsigned,num_col7 int unsigned,num_col8 bigint unsigned ,num_col9 float(5,3),num_col10 double,num_col11 decimal(38,19)) infile{"filepath"='$resources/external_table_file/ex_table_number.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n';
    32  create sequence seq_temp start 10;
    33  drop table seq_temp;
    34  create sequence seq_temp start 10;
    35  create sequence seq_temp start 10;
    36  create sequence Seq_temp start 10;
    37  create sequence sequence;
    38  create sequence table as bigint;
    39  
    40  --if not exists
    41  create sequence seq_01 as tinyint unsigned MINVALUE 26  NO CYCLE;
    42  create sequence if not exists seq_01 as tinyint unsigned MINVALUE 26  NO CYCLE;
    43  create sequence if not exists seq_02 as tinyint CYCLE;
    44  
    45  --sequence grammar cover
    46  create sequence seq_03 increment 3;
    47  select nextval('seq_03');
    48  select nextval('seq_03'),currval('seq_03');
    49  select nextval('seq_03');
    50  select nextval('seq_03');
    51  select * from seq_03;
    52  create sequence seq_04 increment by -10;
    53  select nextval('seq_04');
    54  select nextval('seq_04'),currval('seq_04');
    55  select nextval('seq_04');
    56  select nextval('seq_04'),currval('seq_04');
    57  create sequence seq_05 start 10000;
    58  select nextval('seq_05');
    59  select nextval('seq_05'),currval('seq_05');
    60  create table seq_table_01(col1 int);
    61  insert into seq_table_01 values(nextval('seq_05')),(nextval('seq_04')),(nextval('seq_05')),(nextval('seq_05'));
    62  select col1 from seq_table_01;
    63  create sequence seq_06 start with 10000;
    64  select nextval('seq_06');
    65  select nextval('seq_06'),currval('seq_06');
    66  select nextval('seq_06'),currval('seq_06');
    67  select nextval('seq_06');
    68  select nextval('seq_06');
    69  select * from seq_06;
    70  truncate table seq_table_01;
    71  insert into seq_table_01 values(nextval('seq_06')),(nextval('seq_06'));
    72  insert into seq_table_01 values(nextval('seq_06'));
    73  select col1 from seq_table_01;
    74  create sequence seq_07 minvalue 999 maxvalue 1999;
    75  select nextval('seq_07');
    76  select nextval('seq_07'),currval('seq_07');
    77  select nextval('seq_07'),currval('seq_07');
    78  select nextval('seq_07');
    79  select setval('seq_07',1050,false);
    80  select lastval();
    81  truncate table seq_table_01;
    82  insert into seq_table_01 values(nextval('seq_07'));
    83  insert into seq_table_01 select nextval('seq_07');
    84  select * from seq_table_01;
    85  create sequence seq_08;
    86  select nextval('seq_08');
    87  select nextval('seq_08'),currval('seq_08');
    88  select nextval('seq_08'),currval('seq_08');
    89  create sequence seq_09 minvalue 10 maxvalue 12 no cycle;
    90  select nextval('seq_09');
    91  select nextval('seq_09');
    92  select nextval('seq_09');
    93  select nextval('seq_09');
    94  drop sequence seq_09;
    95  create sequence seq_09 increment 2 minvalue 10 maxvalue 12 cycle;
    96  select nextval('seq_09');
    97  select nextval('seq_09');
    98  select nextval('seq_09');
    99  select nextval('seq_09');
   100  select * from seq_09;
   101  create sequence seq_10 minvalue 1000;
   102  select nextval('seq_10');
   103  select nextval('seq_10'),currval('seq_10');
   104  select nextval('seq_10');
   105  select nextval('seq_10');
   106  select nextval('seq_08'),currval('seq_08');
   107  create sequence seq_11 as smallint start 126;
   108  select nextval('seq_11');
   109  select nextval('seq_11'),currval('seq_11');
   110  select nextval('seq_11');
   111  select nextval('seq_11');
   112  create sequence if not exists seq_12 as bigint increment by 10000 minvalue 500  start with 500 cycle;
   113  select nextval('seq_12');
   114  select nextval('seq_12'),currval('seq_12');
   115  select nextval('seq_12'),currval('seq_12');
   116  select nextval('seq_12');
   117  select * from seq_12;
   118  truncate table seq_table_01;
   119  insert into seq_table_01 select nextval('seq_12');
   120  insert into seq_table_01 values(nextval('seq_12'));
   121  select * from seq_table_01;
   122  create sequence seq_13 as int increment -10000  no cycle;
   123  select nextval('seq_13');
   124  select nextval('seq_13'),currval('seq_13');
   125  truncate table seq_table_01;
   126  insert into seq_table_01 select nextval('seq_13');
   127  insert into seq_table_01 values(nextval('seq_13'));
   128  select * from seq_table_01;
   129  create sequence seq_14  increment 50 start with 126 no cycle;
   130  select nextval('seq_14');
   131  select nextval('seq_14'),currval('seq_14');
   132  truncate table seq_table_01;
   133  insert into seq_table_01 select nextval('seq_14');
   134  insert into seq_table_01 values(nextval('seq_14'));
   135  insert into seq_table_01 values(nextval('seq_14'));
   136  insert into seq_table_01 values(nextval('seq_14'));
   137  insert into seq_table_01 values(nextval('seq_14'));
   138  select * from seq_table_01;
   139  
   140  --abnormal test: max/min/start value out of range
   141  create sequence seq_an_01 as smallint start -1000;
   142  create sequence if not exists seq_an_01 as bigint increment by 10000 minvalue 500  start with 100;
   143  create sequence seq_an_01 as smallint maxvalue 9999999 start with -10;
   144  create sequence seq_an_01 start with 0;
   145  create sequence seq_an_02 as tinyint unsigned;
   146  create sequence seq_an_02 as tinyint;
   147  create sequence seq_an_03  increment -50 start with 126 no cycle;
   148  
   149  
   150  --show
   151  show sequences;
   152  show sequences where names in('seq_05','seq_06');
   153  --select * from mo_catalog.mo_sequences;
   154  
   155  --drop if exists
   156  drop sequence seq_15;
   157  drop sequence if exists seq_15;
   158  drop sequence seq_15;
   159  drop sequence seq_non;
   160  
   161  --prepare
   162  create sequence seq_15;
   163  create sequence seq_16 increment 10 start with 20 no cycle;
   164  truncate table seq_table_01;
   165  prepare stmt1 from 'insert into seq_table_01 values(?)';
   166  -- first value of seq_15 is 1
   167  set @a_var = nextval('seq_15');
   168  execute stmt1 using @a_var;
   169  select * from seq_table_01;
   170  execute stmt1 using @a_var;
   171  select * from seq_table_01;
   172  execute stmt1 using @a_var;
   173  select * from seq_table_01;
   174  drop sequence seq_16;
   175  drop sequence seq_15;
   176  --lastval and setval
   177  create sequence seq_17 increment 10 start with 20 no cycle;
   178  select lastval();
   179  select nextval('seq_17');
   180  select lastval();
   181  select setval('seq_17',5);
   182  select nextval('seq_17'),currval('seq_17');
   183  select setval('seq_17',8,false);
   184  select nextval('seq_17'),currval('seq_17');
   185  select nextval('seq_17'),currval('seq_17');
   186  select lastval();
   187  
   188  --transaction
   189  -- @bvt:issue#8890
   190  begin;
   191  create sequence seq_18 minvalue 1000;
   192  select nextval('seq_18');
   193  -- @session:id=2&user=sys:dump&password=111
   194  select nextval('seq_18');
   195  create sequence seq_18;
   196  -- @session
   197  select nextval('seq_18');
   198  commit;
   199  select nextval('seq_18'),currval('seq_18');
   200  drop sequence seq_18;
   201  
   202  begin;
   203  create sequence seq_19 minvalue 1000;
   204  select nextval('seq_19');
   205  -- @session:id=2&user=sys:dump&password=111
   206  select nextval('seq_19');
   207  create sequence seq_19;
   208  -- @session
   209  rollback;
   210  select nextval('seq_19');
   211  drop sequence seq_19;
   212  
   213  start transaction ;
   214  create sequence seq_20 increment by -10;
   215  select nextval('seq_20');
   216  rollback;
   217  select nextval('seq_20');
   218  drop sequence seq_20;
   219  -- @bvt:issue