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

     1  drop table if exists test_11;
     2  create table test_11 (c int primary key,d int);
     3  
     4  begin;
     5  Insert into test_11 values(1,1);
     6  Insert into test_11 values(2,2);
     7  Rollback;
     8  select * from test_11 ;
     9  
    10  begin;
    11  Insert into test_11 values(1,1);
    12  Insert into test_11 values(2,2);
    13  commit;
    14  select * from test_11 ;
    15  
    16  drop table if exists test_11;
    17  create table test_11 (c int primary key,d int);
    18  Insert into test_11 values(1,1);
    19  Insert into test_11 values(2,2);
    20  Insert into test_11 values(3,1);
    21  Insert into test_11 values(4,2);
    22  begin;
    23  delete from test_11 where c < 3;
    24  update test_11 set d = c + 1 where c >= 3;
    25  rollback;
    26  select * from test_11 ;
    27  
    28  begin;
    29  delete from test_11 where c <3;
    30  update test_11 set d = c + 1 where c >= 3;
    31  commit;
    32  select * from test_11 ;
    33  
    34  drop table if exists test_11;
    35  begin;
    36  create table test_11 (c int primary key,d int);
    37  Insert into test_11 values(1,1);
    38  Insert into test_11 values(2,2);
    39  Insert into test_11 values(3,1);
    40  Insert into test_11 values(4,2);
    41  rollback;
    42  select * from test_11 ;
    43  
    44  begin;
    45  create table test_11 (c int primary key,d int);
    46  Insert into test_11 values(1,1);
    47  Insert into test_11 values(2,2);
    48  Insert into test_11 values(3,1);
    49  Insert into test_11 values(4,2);
    50  delete from test_11 where c <3;
    51  update test_11 set d = c + 1 where c >= 3;
    52  commit;
    53  select * from test_11;
    54  
    55  drop table if exists test_11;
    56  create table test_11 (c int primary key,d int);
    57  Insert into test_11 values(1,1);
    58  Insert into test_11 values(2,2);
    59  begin;
    60  Insert into test_11 values(3,1);
    61  Insert into test_11 values(4,2);
    62  rollback;
    63  select * from test_11;
    64  
    65  drop table if exists test_11;
    66  create table test_11 (c int primary key,d int);
    67  Insert into test_11 values(1,1);
    68  Insert into test_11 values(2,2);
    69  begin;
    70  Insert into test_11 values(3,1);
    71  Insert into test_11 values(4,2);
    72  commit;
    73  drop table if exists test_11;
    74  select * from test_11 ;
    75  
    76  begin;
    77  create table test_12(col1 int primary key,col2 varchar(25));
    78  create unique index id_01 on test_12(col2);
    79  select * from test_12;
    80  show create table test_12;
    81  -- @session:id=2&user=sys:dump&password=111
    82  show create table test_12;
    83  -- @session
    84  rollback ;
    85  show create table test_12;
    86  select * from test_12;
    87  
    88  start transaction;
    89  create table test_12(col1 int primary key,col2 varchar(25));
    90  insert into test_12 values(1,'a'),(2,'b');
    91  -- @session:id=2&user=sys:dump&password=111
    92  select * from test_12;
    93  use atomicity;
    94  create table test_12(col1 int,col2 varchar(25));
    95  insert into test_12 values (90,'tt');
    96  -- @session
    97  select * from test_12;
    98  show create table test_12;
    99  commit;
   100  show create table test_12;
   101  select * from test_12;
   102  drop table test_12;
   103  
   104  start transaction;
   105  create table test_12(col1 int primary key auto_increment,col2 varchar(25));
   106  insert into test_12(col2) values('c'),('d'),('e');
   107  create index id_01 on test_12(col2);
   108  select * from test_12;
   109  show create table test_12;
   110  commit;
   111  show create table test_12;
   112  select * from test_12;
   113  
   114  create database s_db_1;
   115  begin;
   116  use s_db_1;
   117  create table test_13(col1 int primary key,col2 varchar(25));
   118  rollback;
   119  drop database s_db_1;
   120  use s_db_1;
   121  select * from test_13;
   122  
   123  create database s_db_1;
   124  start transaction ;
   125  use s_db_1;
   126  create table test_13(col1 int primary key,col2 varchar(25));
   127  -- @session:id=2&user=sys:dump&password=111
   128  create database s_db_1;
   129  -- @session
   130  commit;
   131  drop database s_db_1;
   132  
   133  begin;
   134  use atomicity;
   135  create table test_14(col1 int primary key,col2 varchar(25), unique key col2(col2));
   136  insert into test_14 values(1,'a'),(2,'b');
   137  create view test_view_1 as select * from test_14;
   138  -- @session:id=2&user=sys:dump&password=111
   139  select * from test_view_1;
   140  -- @session
   141  show create table test_14;
   142  select  * from test_view_1;
   143  rollback ;
   144  select * from test_14;
   145  select  * from test_view_1;
   146  show create table test_14;
   147  
   148  start transaction ;
   149  use atomicity;
   150  create temporary table test_15(col1 int,col2 float);
   151  insert into test_15 values(1,20.98),(2,30.34);
   152  -- @session:id=2&user=sys:dump&password=111
   153  select * from test_15;
   154  -- @session
   155  select * from test_15;
   156  rollback ;
   157  select * from test_15;
   158  
   159  start transaction ;
   160  use atomicity;
   161  create external table test_ex_table_1(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';
   162  select num_col1 ,num_col2 from test_ex_table_1;
   163  create table test_16(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));
   164  insert into test_16 select * from test_ex_table_1;
   165  rollback ;
   166  select num_col1 ,num_col2  from test_ex_table_1;
   167  select num_col1 ,num_col2  from test_16;
   168  
   169  begin;
   170  use atomicity;
   171  create external table test_ex_table_1(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';
   172  select num_col1 ,num_col2 from test_ex_table_1;
   173  create table test_16(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));
   174  insert into test_16 select * from test_ex_table_1;
   175  -- @session:id=2&user=sys:dump&password=111
   176  select num_col1 ,num_col2 from test_ex_table_1;
   177  -- @session
   178  commit;
   179  select num_col1 ,num_col2 from test_ex_table_1;
   180  select num_col1 ,num_col2 from test_16;
   181  
   182  
   183  drop table if exists s3t;
   184  create table s3t (a int, b int, c int, primary key(a, b));
   185  insert into s3t select result, 2, 12 from generate_series(1, 30000, 1) g;
   186  select count(*) from s3t;
   187  
   188  begin;
   189  CREATE TABLE `s3t_copy` (
   190                              `a` INT NOT NULL,
   191                              `b` INT NOT NULL,
   192                              `d` INT DEFAULT null,
   193                              `c` INT DEFAULT null,
   194                              PRIMARY KEY (`a`,`b`)
   195  );
   196  insert into s3t_copy(a, b, c) select a, b, c from s3t;
   197  select count(*) from s3t_copy;
   198  commit;
   199