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

     1  -- 2.a Alter Add new column to PK list [Add a new PK column and then remove it later]
     2  drop table if exists t1;
     3  create table t1(id VARCHAR(20) PRIMARY KEY,name VARCHAR(255),age int);
     4  create index idx1 on t1(name);
     5  insert into t1 values("a","Abby", 24);
     6  insert into t1 values("b","Deb", 26);
     7  select * from t1;
     8  show index from t1;
     9  show create table t1;
    10  select name, type, column_name from mo_catalog.mo_indexes mi where name="idx1";
    11  alter table t1 add column id2 VARCHAR(20);
    12  update t1 set id2 = id;
    13  ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY (id, id2);
    14  show index from t1;
    15  show create table t1;
    16  select name, type, column_name from mo_catalog.mo_indexes mi where name="idx1";
    17  insert into t1 values("d","Abby", 24,"d2");
    18  alter table t1 drop column id2;
    19  desc t1;
    20  show index from t1;
    21  show create table t1;
    22  select name, type, column_name from mo_catalog.mo_indexes mi where name="idx1";
    23  
    24  -- 2.b Alter Drop SK [Drop the last standing SK]
    25  drop table if exists t1;
    26  create table t1(id int PRIMARY KEY,name VARCHAR(255),age int);
    27  create index idx2 on t1(name);
    28  insert into t1 values(1,"Abby", 24);
    29  insert into t1 values(2,"Dora", 25);
    30  show index from t1;
    31  select name, type, column_name from mo_catalog.mo_indexes mi where name="idx2";
    32  alter table t1 drop column name;
    33  show index from t1;
    34  select name, type, column_name from mo_catalog.mo_indexes mi where name="idx2";
    35  
    36  
    37  -- 2.c Alter Drop SK [Drop 1 of 2 SKs]
    38  drop table if exists t1;
    39  create table t1(id int PRIMARY KEY,name VARCHAR(255),age int);
    40  create index idx3 on t1(name,age);
    41  insert into t1 values(1,"Abby", 24);
    42  insert into t1 values(2,"Dora", 25);
    43  show index from t1;
    44  select name, type, column_name from mo_catalog.mo_indexes mi where name="idx3";
    45  alter table t1 drop column name;
    46  show index from t1;
    47  select name, type, column_name from mo_catalog.mo_indexes mi where name="idx3";
    48  
    49  
    50  
    51  -- 2.d Alter Drop user defined PK column, resulting in falling back to __mo_fake_pk
    52  drop table if exists t1;
    53  create table t1(id VARCHAR(255) PRIMARY KEY,name VARCHAR(255),age int);
    54  create index idx4 on t1(id,name);
    55  insert into t1 values("a","Abby", 24);
    56  insert into t1 values("b","Bob", 25);
    57  insert into t1 values("c","Carol", 23);
    58  select * from t1;
    59  show index from t1;
    60  select name, type, column_name from mo_catalog.mo_indexes mi where name="idx4";
    61  alter table t1 drop column id;
    62  show index from t1;
    63  select name, type, column_name from mo_catalog.mo_indexes mi where name="idx4";
    64  insert into t1 values("Dora", 29);
    65  alter table t1 drop column name;
    66  show index from t1;
    67  select name, type, column_name from mo_catalog.mo_indexes mi where name="idx4";
    68  insert into t1 values(29);