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

     1  ----------------
     2  --single value--
     3  ----------------
     4  -- bool value
     5  set @b_var1 = 1 = 0,@b_var2 = 0 = 0;
     6  select @b_var1,@b_var2;
     7  -- int8
     8  set @i8_var1 = 127,@i8_var2 = -128;
     9  select @i8_var1,@i8_var2;
    10  -- int16
    11  set @i16_var1 = 32767,@i16_var2 = -32768;
    12  select @i16_var1,@i16_var2;
    13  --int32
    14  set @i32_var1 = 2147483647,@i32_var2 = -2147483648;
    15  select @i32_var1,@i32_var2;
    16  --int64
    17  set @i64_var1 = 9223372036854775807,@i64_var2 = -9223372036854775808;
    18  select @i64_var1,@i64_var2;
    19  -- uint8
    20  set @u8_var1 = 0,@u8_var2 = 255;
    21  select @u8_var1,@u8_var2;
    22  -- uint16
    23  set @u16_var1 = 0,@u16_var2 = 65535;
    24  select @u16_var1,@u16_var2;
    25  --uint32
    26  set @u32_var1 = 0,@u32_var2 = 4294967295;
    27  select @u32_var1,@u32_var2;
    28  --uint64
    29  set @u64_var1 = 0,@u64_var2 = 18446744073709551615;
    30  select @u64_var1,@u64_var2;
    31  --float32
    32  set @f32_var1 = 1.1754943508222875e-38,@f32_var2 = 99999999999999999999999999999999999999;
    33  select @f32_var1,@f32_var2;
    34  --float64
    35  set @f64_var1 = 2.2250738585072014e-308,@f64_var2 = 99999999999999999999999999999999999999;
    36  select @f64_var1,@f64_var2;
    37  --char
    38  set @ch_var='abc',@varch_var=cast('def' as varchar),@bin_var=cast('1001' as binary),@varbin_var=cast('1001' as varbinary(6)),@text_var=cast('ghi' as text),@blob_var=cast('1010' as blob);
    39  select @ch_var,@varch_var,@bin_var,@varbin_var,@text_var,@blob_var;
    40  --decimal64
    41  set @d64_var1 = cast(9223372036854775807 as decimal),@d64_var2 = cast(-9223372036854775808 as decimal);
    42  select @d64_var1,@d64_var2;
    43  --decimal128
    44  set @d128_var1 = cast(99999999999999999999999999999999999999 as decimal),@d128_var2 = cast(-99999999999999999999999999999999999999 as decimal);
    45  select @d128_var1,@d128_var2;
    46  --json
    47  set @json_var1 = cast('{"a":1,"b":2}' as json),@json_var2 = cast('[1,2,3]' as json);
    48  select @json_var1,@json_var2;
    49  --uuid
    50  set @uuid_var1 = cast('00000000-0000-0000-0000-000000000000' as uuid),@uuid_var2 = cast('ffffffff-ffff-ffff-ffff-ffffffffffff' as uuid);
    51  select @uuid_var1,@uuid_var2;
    52  --time
    53  set @time_var1 = cast('00:00:00' as time),@time_var2 = cast('23:59:59' as time);
    54  select @time_var1,@time_var2;
    55  --datetime
    56  set @dt_var1 = cast('1000-01-01 00:00:00' as datetime),@dt_var2 = cast('9999-12-31 23:59:59' as datetime);
    57  select @dt_var1,@dt_var2;
    58  --timestamp
    59  set @ts_var1 = cast('1970-01-01 00:00:00' as timestamp),@ts_var2 = cast('2038-01-19 03:14:07' as timestamp);
    60  select @ts_var1,@ts_var2;
    61  ---------------------
    62  ---compound value----
    63  ---------------------
    64  drop table if exists seq_t1;
    65  create table seq_t1(a int, b int);
    66  insert into seq_t1 values (1,2);
    67  insert into seq_t1 values (1,2);
    68  
    69  set @single_var1 = (select a from seq_t1 limit 1),@single_var2 = (select b from seq_t1 limit 1);
    70  select @single_var1,@single_var2;
    71  
    72  set @single_var2 = (select a from seq_t1 limit 2);
    73  set @single_var3 = (select a,b from seq_t1 limit 1);
    74  select @single_var3;
    75  set @single_var3 = (select b,a from seq_t1 limit 1);
    76  select @single_var3;
    77  
    78  set @single_var4 = (select count(a) from seq_t1 limit 1),@single_var5 = (select min(b) from seq_t1 limit 1),@single_var6 = (select max(a) from seq_t1 limit 1);
    79  select @single_var4,@single_var5,@single_var6;
    80  
    81  set @single_var7 = (select a,b from seq_t1);
    82  set @single_var8 = (select b,a from seq_t1);
    83  
    84  drop table if exists seq_t1;
    85  
    86  ----sequence function----
    87  create sequence seq1;
    88  set @seq_var1 = nextval('seq1'),@seq_var2 = nextval('seq1'),@seq_var3 = nextval('seq1');
    89  select @seq_var1,@seq_var2,@seq_var3;
    90  select currval('seq1'),nextval('seq1'),lastval(),currval('seq1'),lastval();
    91  select setval('seq1', 50);
    92  select currval('seq1'),nextval('seq1'),lastval(),currval('seq1'),lastval();
    93  drop sequence seq1;
    94  
    95  -- @bvt:issue#9847
    96  create table seq_table_01(col1 int);
    97  create sequence seq_14  increment 50 start with 126 no cycle;
    98  --126[176]
    99  select nextval('seq_14');
   100  --176[226]
   101  select nextval('seq_14'),currval('seq_14');
   102  --226[276]
   103  insert into seq_table_01 select nextval('seq_14');
   104  select currval('seq_14');
   105  --276[326]
   106  insert into seq_table_01 values(nextval('seq_14'));
   107  --326[376]
   108  insert into seq_table_01 values(nextval('seq_14'));
   109  --376[426]
   110  insert into seq_table_01 values(nextval('seq_14'));
   111  --426[476]
   112  insert into seq_table_01 values(nextval('seq_14'));
   113  --426
   114  
   115  select currval('seq_14');
   116  select * from seq_table_01;
   117  
   118  drop sequence seq_14;
   119  drop table seq_table_01;
   120  -- @bvt:issue