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

     1  -- 4.a Update PK and SK column (with non nulls)
     2  drop table if exists t1;
     3  create table t1(id int PRIMARY KEY,name VARCHAR(255),age int);
     4  create index idx1 on t1(name);
     5  insert into t1 values(1,"Abby", 24);
     6  insert into t1 values(2,"Deb", 26);
     7  select * from t1;
     8  update t1 set name = "Dora" where id = 2;
     9  select * from t1;
    10  update t1 set id=3 where id=2;
    11  update t1 set name = "Abby" where id = 3;
    12  select * from t1;
    13  show index from t1;
    14  show create table t1;
    15  select name, type, column_name from mo_catalog.mo_indexes mi where name="idx1";
    16  
    17  -- 4.b Insert duplicate
    18  drop table if exists t1;
    19  create table t1(id int PRIMARY KEY,name VARCHAR(255),age int);
    20  create index idx2 on t1(name);
    21  insert into t1 values(1,"Abby", 24);
    22  insert into t1 values(2,"Abby", 26);
    23  select * from t1;
    24  
    25  -- 4.c Update to Duplicate
    26  drop table if exists t1;
    27  create table t1(id int PRIMARY KEY,name VARCHAR(255),age int);
    28  create index idx3 on t1(name);
    29  insert into t1 values(1,"Abby", 24);
    30  insert into t1 values(2,"Dora", 25);
    31  update t1 set name = "Abby" where id = 2;
    32  select * from t1;
    33  
    34  -- 4.d Update to Null
    35  drop table if exists t1;
    36  create table t1(id int PRIMARY KEY,name VARCHAR(255),age int);
    37  create index idx4 on t1(name);
    38  insert into t1 values(1,"Abby", 24);
    39  insert into t1 values(2,"Dora", 25);
    40  update t1 set name = null where id = 2;
    41  select * from t1;
    42  
    43  -- 4.e Insert Null
    44  drop table if exists t1;
    45  create table t1(id int PRIMARY KEY,name VARCHAR(255),age int);
    46  create index idx5 on t1(name);
    47  insert into t1 values(1,"Abby", 24);
    48  insert into t1 (id, age) values(2, 25);
    49  select * from t1;
    50  
    51  -- 4.f Update to Null and Then Update to Non Null and Then Delete
    52  drop table if exists t1;
    53  create table t1(id VARCHAR(255) PRIMARY KEY,name VARCHAR(255),age VARCHAR(255));
    54  create  index idx6 on t1(name);
    55  insert into t1 values("a","Abby", "twenty four");
    56  insert into t1 values("b","Debby", "twenty six");
    57  select * from t1;
    58  update t1 set name = null where id = "b";
    59  select * from t1;
    60  update t1 set name = "Cia" where id = "b";
    61  select * from t1;
    62  update t1 set name = null where id = "b";
    63  select * from t1;
    64  delete from t1 where id = "b";
    65  select * from t1;