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

     1  drop database if exists rollbacktest;
     2  create database rollbacktest;
     3  use rollbacktest;
     4  --// workspace内部有与表中数据重复时，commit会报错
     5  create table if not exists t2( id int primary key );
     6  insert into t2 values(1);
     7  select * from t2;
     8  begin;
     9  insert into t2 values(2);
    10  select * from t2;
    11  insert into t2 values(1);
    12  select * from t2;
    13  -- ERROR 1062 (HY000): Duplicate entry '1' for key 'id'
    14  commit;
    15  --1
    16  select * from t2;
    17  
    18  --// workspace内部数据重复时，commit不会报错
    19  
    20  begin;
    21  insert into t2 values(2);
    22  insert into t2 values(2);
    23  select * from t2;
    24  --no error
    25  commit;
    26  --1
    27  --2
    28  --2
    29  select * from t2;
    30  
    31  begin;
    32  insert into t2 values(2);
    33  insert into t2 values(2);
    34  select * from t2;
    35  --Duplicate entry '2' for key 'id'
    36  commit;
    37  --1
    38  --2
    39  --2
    40  select * from t2;
    41  
    42  --// workspace内部有与表中数据重复时，commit不会报错？
    43  
    44  create table t1(a int primary key );
    45  --no error
    46  insert into t1 values(1);
    47  --error. duplicate key
    48  insert into t1 values(1);
    49  --1
    50  select * from t1;
    51  
    52  begin;
    53  delete from t1 where a = 1;
    54  --no error
    55  insert into t1 values(1);
    56  --no error? why? workspace does check duplicate key?
    57  insert into t1 values(1);
    58  --1
    59  --1
    60  select * from t1;
    61  
    62  --no error
    63  insert into t1 values(2);
    64  --no error? why? workspace does check duplicate key?
    65  insert into t1 values(2);
    66  
    67  --1
    68  --1
    69  --2
    70  --2
    71  select * from t1;
    72  --no error
    73  insert into t1 values(3);
    74  --no error
    75  delete from t1 where a = 3;
    76  --error. no column b in t1
    77  delete from t1 where b = 3;
    78  --no error
    79  insert into t1 values(3);
    80  --error. duplicate key？
    81  update t1 set a = 2;
    82  ----------------
    83  --why not error?
    84  ----------------
    85  commit ;
    86  --1
    87  --1
    88  --2
    89  --2
    90  --3
    91  select * from t1;
    92  
    93  drop database if exists rollbacktest;