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

     1  create database db7;
     2  use db7;
     3  
     4  drop table if exists t1;
     5  CREATE TABLE t1(
     6  col1 INT NOT NULL,
     7  col2 DATE NOT NULL,
     8  col3 VARCHAR(16) NOT NULL,
     9  col4 INT NOT NULL,
    10  PRIMARY KEY(col1)
    11  );
    12  
    13  insert into t1 values(1, '1980-12-17','Abby', 21);
    14  insert into t1 values(2, '1981-02-20','Bob', 22);
    15  insert into t1 values(3, '1981-02-22','Carol', 23);
    16  insert into t1 values(4, '1981-04-02','Dora', 24);
    17  insert into t1 values(5, '1981-09-28','bcvdf', 25);
    18  insert into t1 values(6, '1981-05-01','green', 26);
    19  
    20  begin;
    21  alter table t1 add column col5 int default 0;
    22  desc t1;
    23  select * from t1;
    24  
    25  insert into t1 values(7, '1989-09-28','bcvdfx', 25, 7);
    26  insert into t1 values(8, '1991-05-01','fgreen', 26, 8);
    27  select * from t1;
    28  
    29  alter table t1 modify col5 VARCHAR(20);
    30  desc t1;
    31  select * from t1;
    32  show tables;
    33  
    34  commit;
    35  desc t1;
    36  select * from t1;
    37  ----------------------------------------------------------------------------------------------------------------------------
    38  drop table if exists t1;
    39  CREATE TABLE t1(
    40  col1 INT NOT NULL,
    41  col2 DATE NOT NULL,
    42  col3 VARCHAR(16) NOT NULL,
    43  col4 INT NOT NULL,
    44  PRIMARY KEY(col1)
    45  );
    46  
    47  insert into t1 values(1, '1980-12-17','Abby', 21);
    48  insert into t1 values(2, '1981-02-20','Bob', 22);
    49  insert into t1 values(3, '1981-02-22','Carol', 23);
    50  insert into t1 values(4, '1981-04-02','Dora', 24);
    51  insert into t1 values(5, '1981-09-28','bcvdf', 25);
    52  insert into t1 values(6, '1981-05-01','green', 26);
    53  
    54  begin;
    55  alter table t1 add column col5 int default 0;
    56  desc t1;
    57  select * from t1;
    58  
    59  insert into t1 values(7, '1989-09-28','bcvdfx', 25, 7);
    60  insert into t1 values(8, '1991-05-01','fgreen', 26, 8);
    61  select * from t1;
    62  
    63  alter table t1 drop column col3;
    64  desc t1;
    65  select * from t1;
    66  show tables;
    67  
    68  commit;
    69  desc t1;
    70  select * from t1;
    71  ----------------------------------------------------------------------------------------------------------------------------
    72  drop table if exists t1;
    73  CREATE TABLE t1(
    74  col1 INT NOT NULL,
    75  col2 DATE NOT NULL,
    76  col3 VARCHAR(16) NOT NULL,
    77  col4 INT NOT NULL,
    78  PRIMARY KEY(col1)
    79  );
    80  
    81  insert into t1 values(1, '1980-12-17','Abby', 21);
    82  insert into t1 values(2, '1981-02-20','Bob', 22);
    83  insert into t1 values(3, '1982-02-22','Carol', 23);
    84  insert into t1 values(4, '1983-04-02','Dora', 24);
    85  insert into t1 values(5, '1984-09-28','bcvdf', 25);
    86  insert into t1 values(6, '1985-05-01','green', 26);
    87  
    88  begin;
    89  alter table t1 add column col5 int default 0;
    90  desc t1;
    91  select * from t1;
    92  
    93  insert into t1 values(7, '1989-09-28','bcvdfx', 25, 7);
    94  insert into t1 values(8, '1991-05-01','fgreen', 26, 8);
    95  
    96  alter table t1 modify col5 VARCHAR(20);
    97  desc t1;
    98  select * from t1;
    99  
   100  alter table t1 rename column col3 to colx;
   101  show index from t1;
   102  desc t1;
   103  select * from t1;
   104  
   105  commit;
   106  desc t1;
   107  select * from t1;
   108  ----------------------------------------------------------------------------------------------------------------------------
   109  drop table if exists t1;
   110  CREATE TABLE t1(
   111  col1 INT NOT NULL,
   112  col2 DATE NOT NULL,
   113  col3 VARCHAR(16) NOT NULL,
   114  col4 INT NOT NULL,
   115  PRIMARY KEY(col1)
   116  );
   117  
   118  insert into t1 values(1, '1980-12-17','Abby', 21);
   119  insert into t1 values(2, '1981-02-20','Bob', 22);
   120  insert into t1 values(3, '1982-02-22','Carol', 23);
   121  insert into t1 values(4, '1983-04-02','Dora', 24);
   122  insert into t1 values(5, '1984-09-28','bcvdf', 25);
   123  insert into t1 values(6, '1985-05-01','green', 26);
   124  
   125  begin;
   126  alter table t1 add column col5 int default 0;
   127  desc t1;
   128  
   129  insert into t1 values(7, '1989-09-28','bcvdfx', 25, 7);
   130  insert into t1 values(8, '1991-05-01','fgreen', 26, 8);
   131  select * from t1;
   132  
   133  alter table t1 modify col5 VARCHAR(20);
   134  desc t1;
   135  select * from t1;
   136  
   137  alter table t1 change col2 colx datetime;
   138  show index from t1;
   139  desc t1;
   140  select * from t1;
   141  
   142  commit;
   143  desc t1;
   144  select * from t1;
   145  drop database db7;