github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/dml/update/update_index.test (about)

     1  drop database if exists db1;
     2  create database db1;
     3  use db1;
     4  drop table if exists t1;
     5  create table t1(a int, b int, c int, unique key(a));
     6  insert into t1 values(1, 1, 1);
     7  insert into t1 values(2, 2, 2);
     8  insert into t1 values(3, 3, 3);
     9  insert into t1 values(4, 4, 4);
    10  insert into t1 values(5, 5, 5);
    11  insert into t1 values(null, 7, 7);
    12  update t1 set a = 6 where a = 1;
    13  insert into t1 values(1, 1, 1);
    14  update t1 set a = 7 where a is null;
    15  insert into t1 values(7, 7, 7);
    16  update t1 set a = null where a = 7;
    17  insert into t1 values(7, 7, 7);
    18  select * from t1;
    19  drop table if exists t1;
    20  create table t1(a int, b int, c int, unique key(a, b));
    21  insert into t1 values(1, 1, 1);
    22  insert into t1 values(2, 2, 2);
    23  insert into t1 values(3, 3, 3);
    24  insert into t1 values(4, 4, 4);
    25  insert into t1 values(5, 5, 5);
    26  insert into t1 values(null, 7, 7);
    27  update t1 set a = 6 where a = 1;
    28  insert into t1 values(1, 1, 1);
    29  update t1 set a = 7 where a is null;
    30  insert into t1 values(7, 7, 7);
    31  update t1 set a = null where a = 7;
    32  insert into t1 values(7, 7, 7);
    33  insert into t1 values(null, 8, 8);
    34  update t1 set a = 8, b = null where b = 8;
    35  insert into t1 values(8, 8, 8);
    36  select * from t1;
    37  drop table if exists t1;
    38  drop table if exists t2;
    39  create table t1(a int, b int, c int, unique key(a, b));
    40  create table t2(a int, b int, c int, unique key(a));
    41  insert into t1 values(1, 1, 1);
    42  insert into t1 values(2, 2, 2);
    43  insert into t1 values(3, 3, 3);
    44  insert into t1 values(4, 4, 4);
    45  insert into t1 values(5, 5, 5);
    46  insert into t1 values(null, 7, 7);
    47  insert into t2 values(1, 1, 1);
    48  insert into t2 values(2, 2, 2);
    49  insert into t2 values(3, 3, 3);
    50  insert into t2 values(4, 4, 4);
    51  insert into t2 values(5, 5, 5);
    52  insert into t2 values(null, 7, 7);
    53  update t1, t2 set t1.a = 6, t2.a = 6 where t1.a = 1 and t1.a = t2.a;
    54  insert into t1 values(1, 1, 1);
    55  insert into t2 values(1, 1, 1);
    56  update t1, t2 set t1.a = 7, t2.a = 7 where t1.a is null and t2.a is null;
    57  insert into t1 values(7, 7, 7);
    58  insert into t2 values(7, 7, 7);
    59  update t1, t2 set t1.a = null, t2.a = null where t1.a = 7 and t1.a = t2.a;
    60  insert into t1 values(7, 7, 7);
    61  insert into t1 values(null, 8, 8);
    62  insert into t2 values(7, 7, 7);
    63  insert into t2 values(null, 8, 8);
    64  update t1, t2 set t1.a = 8, t1.b = null, t2.a = 8, t2.b = null where t1.b = 8 and t1.b = t2.b;
    65  insert into t1 values(8, 8, 8);
    66  insert into t2 values(8, 8, 8);
    67  select * from t1;
    68  select * from t2;
    69  drop table if exists t1;
    70  create table t1(a int, b int, c int, unique key(a), primary key(c));
    71  insert into t1 values(1, 1, 1);
    72  insert into t1 values(2, 2, 2);
    73  insert into t1 values(3, 3, 3);
    74  insert into t1 values(4, 4, 4);
    75  insert into t1 values(5, 5, 5);
    76  insert into t1 values(null, 7, 7);
    77  update t1 set a = 6 where a = 1;
    78  update t1 set a = 7 where a is null;
    79  insert into t1 values(7, 7, 8);
    80  update t1 set a = null where a = 7;
    81  insert into t1 values(7, 7, 9);
    82  select * from t1;
    83  drop table if exists t1;
    84  create table t1(a int, b int, c int, unique key(a, b), primary key(b, c));
    85  insert into t1 values(1, 1, 1);
    86  insert into t1 values(2, 2, 2);
    87  insert into t1 values(3, 3, 3);
    88  insert into t1 values(4, 4, 4);
    89  insert into t1 values(5, 5, 5);
    90  insert into t1 values(null, 7, 7);
    91  update t1 set a = 6 where a = 1;
    92  update t1 set a = 7 where a is null;
    93  insert into t1 values(7, 7, 8);
    94  update t1 set a = null where a = 7;
    95  insert into t1 values(7, 7, 9);
    96  insert into t1 values(null, 8, 10);
    97  update t1 set a = 8, b = null where b = 8;
    98  insert into t1 values(8, 8, 11);
    99  select * from t1;
   100  drop table if exists t1;
   101  create table t1(a int unique, b int, c int);
   102  insert into t1 values(1, 1, 1);
   103  insert into t1 values(2, 2, 2);
   104  insert into t1 values(3, 3, 3);
   105  insert into t1 values(4, 4, 4);
   106  insert into t1 values(5, 5, 5);
   107  insert into t1 values(null, 7, 7);
   108  update t1 set a = 6 where a = 1;
   109  insert into t1 values(1, 1, 1);
   110  update t1 set a = 7 where a is null;
   111  insert into t1 values(7, 7, 7);
   112  update t1 set a = null where a = 7;
   113  insert into t1 values(7, 7, 7);
   114  select * from t1;
   115  drop table if exists t1;
   116  create table t1(a int unique key, b int, c int);
   117  insert into t1 values(1, 1, 1);
   118  insert into t1 values(2, 2, 2);
   119  insert into t1 values(3, 3, 3);
   120  insert into t1 values(4, 4, 4);
   121  insert into t1 values(5, 5, 5);
   122  insert into t1 values(null, 7, 7);
   123  update t1 set a = 6 where a = 1;
   124  insert into t1 values(1, 1, 1);
   125  update t1 set a = 7 where a is null;
   126  insert into t1 values(7, 7, 7);
   127  update t1 set a = null where a = 7;
   128  insert into t1 values(7, 7, 7);
   129  select * from t1;
   130  drop table t1;
   131  drop table if exists t2;
   132  create table t2(a int primary key, b int unique, c int);
   133  insert into t2 values(1, 1, 1);
   134  insert into t2 values(2, 2, 2);
   135  insert into t2 values(3, 3, 3);
   136  insert into t2 values(4, 4, 4);
   137  insert into t2 values(5, 5, 5);
   138  update t2 set a = 6 where b = 5;
   139  select * from t2;
   140  drop table t2;