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

     1  drop table if exists t1;
     2  create table t1(id int PRIMARY KEY,name VARCHAR(255),age int);
     3  create index idx1 on t1(name);
     4  insert into t1 values(1,"Abby", 24);
     5  insert into t1 values(2,"Deb", 26);
     6  select * from t1;
     7  id    name    age
     8  1    Abby    24
     9  2    Deb    26
    10  update t1 set name = "Dora" where id = 2;
    11  select * from t1;
    12  id    name    age
    13  1    Abby    24
    14  2    Dora    26
    15  update t1 set id=3 where id=2;
    16  update t1 set name = "Abby" where id = 3;
    17  select * from t1;
    18  id    name    age
    19  1    Abby    24
    20  3    Abby    26
    21  show index from t1;
    22  Table    Non_unique    Key_name    Seq_in_index    Column_name    Collation    Cardinality    Sub_part    Packed    Null    Index_type    Comment    Index_comment    Index_params    Visible    Expression
    23  t1    0    PRIMARY    1    id    A    0    NULL    NULL                        YES    NULL
    24  t1    1    idx1    1    name    A    0    NULL    NULL    YES                    YES    NULL
    25  show create table t1;
    26  Table    Create Table
    27  t1    CREATE TABLE `t1` (\n`id` INT NOT NULL,\n`name` VARCHAR(255) DEFAULT NULL,\n`age` INT DEFAULT NULL,\nPRIMARY KEY (`id`),\nKEY `idx1` (`name`)\n)
    28  select name, type, column_name from mo_catalog.mo_indexes mi where name="idx1";
    29  name    type    column_name
    30  idx1    MULTIPLE    name
    31  idx1    MULTIPLE    __mo_alias_id
    32  drop table if exists t1;
    33  create table t1(id int PRIMARY KEY,name VARCHAR(255),age int);
    34  create index idx2 on t1(name);
    35  insert into t1 values(1,"Abby", 24);
    36  insert into t1 values(2,"Abby", 26);
    37  select * from t1;
    38  id    name    age
    39  1    Abby    24
    40  2    Abby    26
    41  drop table if exists t1;
    42  create table t1(id int PRIMARY KEY,name VARCHAR(255),age int);
    43  create index idx3 on t1(name);
    44  insert into t1 values(1,"Abby", 24);
    45  insert into t1 values(2,"Dora", 25);
    46  update t1 set name = "Abby" where id = 2;
    47  select * from t1;
    48  id    name    age
    49  1    Abby    24
    50  2    Abby    25
    51  drop table if exists t1;
    52  create table t1(id int PRIMARY KEY,name VARCHAR(255),age int);
    53  create index idx4 on t1(name);
    54  insert into t1 values(1,"Abby", 24);
    55  insert into t1 values(2,"Dora", 25);
    56  update t1 set name = null where id = 2;
    57  select * from t1;
    58  id    name    age
    59  1    Abby    24
    60  2    null    25
    61  drop table if exists t1;
    62  create table t1(id int PRIMARY KEY,name VARCHAR(255),age int);
    63  create index idx5 on t1(name);
    64  insert into t1 values(1,"Abby", 24);
    65  insert into t1 (id, age) values(2, 25);
    66  select * from t1;
    67  id    name    age
    68  1    Abby    24
    69  2    null    25
    70  drop table if exists t1;
    71  create table t1(id VARCHAR(255) PRIMARY KEY,name VARCHAR(255),age VARCHAR(255));
    72  create  index idx6 on t1(name);
    73  insert into t1 values("a","Abby", "twenty four");
    74  insert into t1 values("b","Debby", "twenty six");
    75  select * from t1;
    76  id    name    age
    77  a    Abby    twenty four
    78  b    Debby    twenty six
    79  update t1 set name = null where id = "b";
    80  select * from t1;
    81  id    name    age
    82  a    Abby    twenty four
    83  b    null    twenty six
    84  update t1 set name = "Cia" where id = "b";
    85  select * from t1;
    86  id    name    age
    87  a    Abby    twenty four
    88  b    Cia    twenty six
    89  update t1 set name = null where id = "b";
    90  select * from t1;
    91  id    name    age
    92  a    Abby    twenty four
    93  b    null    twenty six
    94  delete from t1 where id = "b";
    95  select * from t1;
    96  id    name    age
    97  a    Abby    twenty four