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

     1  drop database if exists db1;
     2  drop database if exists db2;
     3  create database db1;
     4  create database db2;
     5  use db2;
     6  drop table if exists t1;
     7  create table t1 (a int);
     8  insert into t1 values (1),(2),(4);
     9  use db1;
    10  drop table if exists t2;
    11  create table t2 (b int);
    12  insert into t2 values(1),(2),(3);
    13  delete from db1.t2, db2.t1 using db1.t2 join db2.t1 on db1.t2.b = db2.t1.a where 2 > 1;
    14  select * from db1.t2;
    15  select * from db2.t1;
    16  drop table if exists t1;
    17  drop table if exists t2;
    18  drop database if exists db1;
    19  drop database if exists db2;
    20  create database db1;
    21  create database db2;
    22  use db1;
    23  drop table if exists t1;
    24  create table t1 (a char(20));
    25  insert into t1 values ('a'), ('b'), ('c');
    26  use db2;
    27  drop table if exists t2;
    28  create table t2 (b char(20));
    29  insert into t2 values('a'),('b'),('d');
    30  delete from db1.t1, db2.t2 using db1.t1 join db2.t2 on db1.t1.a = db2.t2.b where db1.t1.a = 'a';
    31  select * from db1.t1;
    32  select * from db2.t2;
    33  drop table if exists t1;
    34  drop table if exists t2;
    35  drop database if exists db1;
    36  drop database if exists db2;
    37  
    38  create database db1;
    39  use db1;
    40  drop table if exists t1;
    41  drop table if exists t2;
    42  create table t1 (a int);
    43  insert into t1 values(1), (2), (3);
    44  create table t2 (b int);
    45  insert into t2 values(1), (2), (3);
    46  with t11 as ( select * from t1) delete t2 from t11 join t2 on t11.a = t2.b where t2.b = 3;
    47  select * from t2;
    48  
    49  drop table if exists t1;
    50  drop table if exists t2;
    51  create table t1 (a int);
    52  insert into t1 values(1), (2), (4);
    53  create table t2 (b int);
    54  insert into t2 values(1), (2), (5);
    55  delete t1 from t1 join t2 where t1.a = 2;
    56  select * from t1;
    57  
    58  drop table if exists t1;
    59  create table t1 (a int);
    60  insert into t1 values(1), (2), (3);
    61  delete from t1 as a1 where a1.a = 1;
    62  select * from t1;
    63  
    64  drop table if exists t1;
    65  drop table if exists t2;
    66  create table t1 (a int);
    67  insert into t1 values(1), (2), (4);
    68  create table t2 (b int);
    69  insert into t2 values(1), (2), (5);
    70  DELETE a1, a2 FROM t1 AS a1 INNER JOIN t2 AS a2 WHERE a1.a = a2.b;
    71  select * from t1;
    72  
    73  drop table if exists t1;
    74  create table t1 (a char(20));
    75  insert into t1 values (null), (null), ('hello');
    76  delete from t1 where a is null;
    77  select * from t1;
    78  
    79  drop table if exists t1;
    80  create table t1 (a int, b int);
    81  insert into t1 values (1, 2), (3, 4), (5, 6);
    82  delete from t1 where a > 1;
    83  select * from t1;
    84  
    85  drop table if exists t2;
    86  create table t2 (a int primary key, b int);
    87  insert into t2 values (1, 2), (3, 4), (5, 6);
    88  delete from t2 where a > 1 order by a limit 1;
    89  select * from t2;
    90  
    91  drop table if exists t1;
    92  create table t1(a int primary key);
    93  delete from t1;
    94  select * from t1;
    95  
    96  drop table if exists t1;
    97  create table t1 (a char(20));
    98  insert into t1 values ('heelo'), ('sub'), ('none'), (null);
    99  delete from t1 where a is not null;
   100  select * from t1;
   101  
   102  drop table if exists t1;
   103  drop table if exists t2;
   104  create table t1 (a int);
   105  insert into t1 values(1), (2), (3);
   106  create table t2 (b int primary key);
   107  insert into t2 values(1), (2), (3);
   108  delete t1, t2 from t1 join t2 on t1.a = t2.b where t1.a = 1;
   109  select * from t2;
   110  
   111  
   112  drop table if exists t1;
   113  create table t1(a int auto_increment, b bigint auto_increment);
   114  insert into t1 values(null, 2), (3, null), (null, null);
   115  select * from t1;
   116  insert into t1 values(100, 2), (null, null), (null, null);
   117  select * from t1;
   118  delete from t1 where a >= 100;
   119  select * from t1;
   120  insert into t1 values(null, null), (null, null), (null, null);
   121  select * from t1;
   122  
   123  drop table if exists t1;
   124  create table t1(a int, b int, primary key(a, b));
   125  insert into t1 values(1, 2);
   126  insert into t1 values(1, 3);
   127  insert into t1 values(2, 2);
   128  insert into t1 values(2, 3);
   129  select * from t1;
   130  delete from t1 where a = 1;
   131  select * from t1;
   132  
   133  -- @bvt:issue#5790
   134  drop table if exists t1;
   135  create table t1(a int, b int, unique key(a));
   136  insert into t1 values(1, 1);
   137  insert into t1 values(2, 2);
   138  insert into t1 values(3, 3);
   139  insert into t1 values(4, 4);
   140  select * from t1;
   141  delete from t1 where a = 1;
   142  select * from t1;
   143  insert into t1 values(1, 2);
   144  
   145  drop table if exists t1;
   146  create table t1(a int, b int, unique key(a, b));
   147  insert into t1 values(1, 2);
   148  insert into t1 values(1, 3);
   149  insert into t1 values(2, 2);
   150  insert into t1 values(2, 3);
   151  select * from t1;
   152  delete from t1 where a = 1;
   153  select * from t1;
   154  insert into t1 values(1, 2);
   155  insert into t1 values(1, null);
   156  delete from t1 where a = 1;
   157  -- @bvt:issue
   158  
   159  drop database if exists db1;