github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/prepare/prepare_transaction.result (about)

     1  drop table if exists user;
     2  CREATE TABLE user
     3  (
     4  id BIGINT(20) NOT NULL COMMENT '主键ID',
     5  age INT(11) NULL DEFAULT NULL COMMENT '年龄',
     6  PRIMARY KEY (id)
     7  );
     8  INSERT INTO user (id, age) VALUES
     9  (1, 18),
    10  (2, 20),
    11  (3, 28),
    12  (4, 21),
    13  (5, 24);
    14  set autocommit=0;
    15  set @id=6;
    16  set @age=6;
    17  prepare s1 from 'insert into user values (?,?)';
    18  execute s1 using @id,@age;
    19  deallocate prepare s1;
    20  commit;
    21  set autocommit=1;
    22  select * from user;
    23  id    age
    24  1    18
    25  2    20
    26  3    28
    27  4    21
    28  5    24
    29  6    6
    30  set autocommit=0;
    31  set @id=6;
    32  prepare s2 from 'delete from user where id=?';
    33  execute s2 using @id;
    34  deallocate prepare s2;
    35  commit;
    36  set autocommit=1;
    37  select * from user;
    38  id    age
    39  1    18
    40  2    20
    41  3    28
    42  4    21
    43  5    24
    44  set autocommit=0;
    45  set @id=5;
    46  set @age=100;
    47  prepare s3 from 'update user set age=? where id=?';
    48  execute s3 using @age, @id;
    49  deallocate prepare s3;
    50  commit;
    51  set autocommit=1;
    52  select * from user;
    53  id    age
    54  1    18
    55  2    20
    56  3    28
    57  4    21
    58  5    100
    59  set autocommit=0;
    60  set @id=3;
    61  prepare s4 from 'select * from user where id>?';
    62  execute s4 using @id;
    63  id    age
    64  4    21
    65  5    100
    66  deallocate prepare s4;
    67  prepare s5 from 'select * from user where id<?';
    68  execute s5 using @id;
    69  id    age
    70  1    18
    71  2    20
    72  deallocate prepare s5;
    73  commit;
    74  set autocommit=1;
    75  set autocommit=0;
    76  prepare s6 from 'create table test_user(a int)';
    77  execute s6;
    78  deallocate prepare s6;
    79  commit;
    80  set autocommit=1;
    81  show tables like 'test_user';
    82  tables_in_prepare_transaction
    83  test_user
    84  set autocommit=0;
    85  prepare s7 from 'drop table test_user';
    86  execute s7;
    87  internal error: unclassified statement appears in uncommitted transaction
    88  deallocate prepare s7;
    89  commit;
    90  set autocommit=1;
    91  show tables like 'test_user';
    92  tables_in_prepare_transaction
    93  test_user
    94  drop table user;
    95  drop table if exists user;
    96  CREATE TABLE user
    97  (
    98  id BIGINT(20) NOT NULL COMMENT '主键ID',
    99  age INT(11) NULL DEFAULT NULL COMMENT '年龄',
   100  PRIMARY KEY (id)
   101  );
   102  INSERT INTO user (id, age) VALUES
   103  (1, 18),
   104  (2, 20),
   105  (3, 28),
   106  (4, 21),
   107  (5, 24);
   108  set @id=6;
   109  set @age=6;
   110  begin;
   111  prepare s1 from 'insert into user values (?,?)';
   112  execute s1 using @id,@age;
   113  deallocate prepare s1;
   114  commit;
   115  select * from user;
   116  id    age
   117  1    18
   118  2    20
   119  3    28
   120  4    21
   121  5    24
   122  6    6
   123  set @id=6;
   124  begin;
   125  prepare s2 from 'delete from user where id=?';
   126  execute s2 using @id;
   127  deallocate prepare s2;
   128  commit;
   129  select * from user;
   130  id    age
   131  1    18
   132  2    20
   133  3    28
   134  4    21
   135  5    24
   136  set @id=5;
   137  set @age=100;
   138  begin;
   139  prepare s3 from 'update user set age=? where id=?';
   140  execute s3 using @age, @id;
   141  deallocate prepare s3;
   142  commit;
   143  select * from user;
   144  id    age
   145  1    18
   146  2    20
   147  3    28
   148  4    21
   149  5    100
   150  set @id=3;
   151  begin;
   152  prepare s4 from 'select * from user where id>?';
   153  execute s4 using @id;
   154  id    age
   155  4    21
   156  5    100
   157  deallocate prepare s4;
   158  prepare s5 from 'select * from user where id<?';
   159  execute s5 using @id;
   160  id    age
   161  1    18
   162  2    20
   163  deallocate prepare s5;
   164  commit;
   165  begin;
   166  prepare s6 from 'create table test_user(a int)';
   167  execute s6;
   168  table test_user already exists
   169  Previous DML conflicts with existing constraints or data format. This transaction has to be aborted
   170  deallocate prepare s6;
   171  commit;
   172  show tables like 'test_user';
   173  tables_in_prepare_transaction
   174  test_user
   175  begin;
   176  prepare s7 from 'drop table test_user';
   177  internal error: unclassified statement appears in uncommitted transaction
   178  execute s7;
   179  invalid state prepared statement 's7' does not exist
   180  deallocate prepare s7;
   181  commit;
   182  show tables like 'test_user';
   183  tables_in_prepare_transaction
   184  test_user