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

     1  drop database if exists db1;
     2  create database db1;
     3  use db1;
     4  
     5  drop table if exists t1;
     6  create table t1(a int, b int, c int, unique key(a));
     7  insert into t1 values(1, 1, 1);
     8  insert into t1 values(2, 2, 2);
     9  insert into t1 values(3, 3, 3);
    10  insert into t1 values(4, 4, 4);
    11  insert into t1 values(NULL, 5, 5);
    12  select * from t1;
    13  delete from t1 where a = 1;
    14  select * from t1;
    15  insert into t1 values(1, 1, 1);
    16  delete from t1 where a is null;
    17  select * from t1;
    18  delete from t1;
    19  select * from t1;
    20  
    21  drop table if exists t1;
    22  create table t1(a int, b int, c int, unique key(a, b));
    23  insert into t1 values(1, 1, 1);
    24  insert into t1 values(2, 2, 2);
    25  insert into t1 values(3, 3, 3);
    26  insert into t1 values(4, 4, 4);
    27  insert into t1 values(NULL, 5, 5);
    28  select * from t1;
    29  delete from t1 where a = 1;
    30  select * from t1;
    31  insert into t1 values(1, 1, 1);
    32  delete from t1 where a is null;
    33  select * from t1;
    34  delete from t1;
    35  select * from t1;
    36  
    37  drop table if exists t1;
    38  create table t1(a int, b int, c int, unique key(a), unique key(b));
    39  insert into t1 values(1, 1, 1);
    40  insert into t1 values(2, 2, 2);
    41  insert into t1 values(3, 3, 3);
    42  insert into t1 values(4, 4, 4);
    43  insert into t1 values(NULL, 5, 5);
    44  select * from t1;
    45  delete from t1 where a = 1;
    46  select * from t1;
    47  insert into t1 values(1, 1, 1);
    48  delete from t1 where a is null;
    49  select * from t1;
    50  delete from t1;
    51  select * from t1;
    52  
    53  drop table if exists t1;
    54  create table t1(a int, b int, c int, unique key(a, b), unique key(b, c));
    55  insert into t1 values(1, 1, 1);
    56  insert into t1 values(2, 2, 2);
    57  insert into t1 values(3, 3, 3);
    58  insert into t1 values(4, 4, 4);
    59  insert into t1 values(NULL, 5, 5);
    60  select * from t1;
    61  delete from t1 where a = 1;
    62  select * from t1;
    63  insert into t1 values(1, 1, 1);
    64  delete from t1 where a is null;
    65  select * from t1;
    66  delete from t1;
    67  select * from t1;
    68  
    69  drop table if exists t1;
    70  create table t1(a int, b int, c int, unique key(a, b), unique 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(NULL, 5, 5);
    76  select * from t1;
    77  delete from t1 where a = 1;
    78  select * from t1;
    79  insert into t1 values(1, 1, 1);
    80  delete from t1 where a is null;
    81  select * from t1;
    82  delete from t1;
    83  select * from t1;
    84  
    85  drop table if exists t1;
    86  drop table if exists t2;
    87  create table t1(a int, b int, c int, unique key(a));
    88  insert into t1 values(1, 1, 1);
    89  insert into t1 values(2, 2, 2);
    90  insert into t1 values(3, 3, 3);
    91  insert into t1 values(4, 4, 4);
    92  insert into t1 values(NULL, 5, 5);
    93  create table t2(a int, b int, c int);
    94  insert into t2 values(1, 1, 1);
    95  insert into t2 values(2, 2, 2);
    96  insert into t2 values(3, 3, 3);
    97  insert into t2 values(4, 4, 4);
    98  insert into t2 values(NULL, 5, 5);
    99  select * from t1;
   100  select * from t2;
   101  delete t1, t2 from t1,t2 where t1.a = t2.a;
   102  select * from t1;
   103  select * from t2;
   104  insert into t1 values(1, 1, 1);
   105  insert into t1 values(2, 2, 2);
   106  insert into t1 values(3, 3, 3);
   107  insert into t1 values(4, 4, 4);
   108  insert into t1 values(NULL, 5, 5);
   109  
   110  drop table if exists t1;
   111  drop table if exists t2;
   112  create table t1(a int, b int, c int, unique key(a), unique key(b));
   113  insert into t1 values(1, 1, 1);
   114  insert into t1 values(2, 2, 2);
   115  insert into t1 values(3, 3, 3);
   116  insert into t1 values(4, 4, 4);
   117  insert into t1 values(NULL, 5, 5);
   118  create table t2(a int, b int, c int);
   119  insert into t2 values(1, 1, 1);
   120  insert into t2 values(2, 2, 2);
   121  insert into t2 values(3, 3, 3);
   122  insert into t2 values(4, 4, 4);
   123  insert into t2 values(NULL, 5, 5);
   124  select * from t1;
   125  select * from t2;
   126  delete t1, t2 from t1,t2 where t1.a = t2.a;
   127  select * from t1;
   128  select * from t2;
   129  insert into t1 values(1, 1, 1);
   130  insert into t1 values(2, 2, 2);
   131  insert into t1 values(3, 3, 3);
   132  insert into t1 values(4, 4, 4);
   133  insert into t1 values(NULL, 5, 5);
   134  
   135  drop table if exists t1;
   136  drop table if exists t2;
   137  create table t1(a int, b int, c int, unique key(a, b));
   138  insert into t1 values(1, 1, 1);
   139  insert into t1 values(2, 2, 2);
   140  insert into t1 values(3, 3, 3);
   141  insert into t1 values(4, 4, 4);
   142  insert into t1 values(NULL, 5, 5);
   143  create table t2(a int, b int, c int);
   144  insert into t2 values(1, 1, 1);
   145  insert into t2 values(2, 2, 2);
   146  insert into t2 values(3, 3, 3);
   147  insert into t2 values(4, 4, 4);
   148  insert into t2 values(NULL, 5, 5);
   149  select * from t1;
   150  select * from t2;
   151  delete t1, t2 from t1,t2 where t1.a = t2.a;
   152  select * from t1;
   153  select * from t2;
   154  insert into t1 values(1, 1, 1);
   155  insert into t1 values(2, 2, 2);
   156  insert into t1 values(3, 3, 3);
   157  insert into t1 values(4, 4, 4);
   158  insert into t1 values(NULL, 5, 5);
   159  
   160  drop table if exists t1;
   161  drop table if exists t2;
   162  create table t1(a int, b int, c int, unique key(a, b), unique key(b, c));
   163  insert into t1 values(1, 1, 1);
   164  insert into t1 values(2, 2, 2);
   165  insert into t1 values(3, 3, 3);
   166  insert into t1 values(4, 4, 4);
   167  insert into t1 values(NULL, 5, 5);
   168  create table t2(a int, b int, c int);
   169  insert into t2 values(1, 1, 1);
   170  insert into t2 values(2, 2, 2);
   171  insert into t2 values(3, 3, 3);
   172  insert into t2 values(4, 4, 4);
   173  insert into t2 values(NULL, 5, 5);
   174  select * from t1;
   175  select * from t2;
   176  delete t1, t2 from t1,t2 where t1.a = t2.a;
   177  select * from t1;
   178  select * from t2;
   179  insert into t1 values(1, 1, 1);
   180  insert into t1 values(2, 2, 2);
   181  insert into t1 values(3, 3, 3);
   182  insert into t1 values(4, 4, 4);
   183  insert into t1 values(NULL, 5, 5);
   184  
   185  drop table if exists t1;
   186  drop table if exists t2;
   187  create table t1(a int, b int, c int, unique key(a));
   188  insert into t1 values(1, 1, 1);
   189  insert into t1 values(2, 2, 2);
   190  insert into t1 values(3, 3, 3);
   191  insert into t1 values(4, 4, 4);
   192  insert into t1 values(NULL, 5, 5);
   193  create table t2(a int, b int, c int, unique key(b));
   194  insert into t2 values(1, 1, 1);
   195  insert into t2 values(2, 2, 2);
   196  insert into t2 values(3, 3, 3);
   197  insert into t2 values(4, 4, 4);
   198  insert into t2 values(NULL, 5, 5);
   199  select * from t1;
   200  select * from t2;
   201  delete t1, t2 from t1,t2 where t1.a = t2.a;
   202  select * from t1;
   203  select * from t2;
   204  insert into t1 values(1, 1, 1);
   205  insert into t1 values(2, 2, 2);
   206  insert into t1 values(3, 3, 3);
   207  insert into t1 values(4, 4, 4);
   208  insert into t1 values(NULL, 5, 5);
   209  
   210  drop table if exists t1;
   211  drop table if exists t2;
   212  create table t1(a int, b int, c int, unique key(a), unique key(b));
   213  insert into t1 values(1, 1, 1);
   214  insert into t1 values(2, 2, 2);
   215  insert into t1 values(3, 3, 3);
   216  insert into t1 values(4, 4, 4);
   217  insert into t1 values(NULL, 5, 5);
   218  create table t2(a int, b int, c int, unique key(b), unique key(c));
   219  insert into t2 values(1, 1, 1);
   220  insert into t2 values(2, 2, 2);
   221  insert into t2 values(3, 3, 3);
   222  insert into t2 values(4, 4, 4);
   223  insert into t2 values(NULL, 5, 5);
   224  select * from t1;
   225  select * from t2;
   226  delete t1, t2 from t1,t2 where t1.a = t2.a;
   227  select * from t1;
   228  select * from t2;
   229  insert into t1 values(1, 1, 1);
   230  insert into t1 values(2, 2, 2);
   231  insert into t1 values(3, 3, 3);
   232  insert into t1 values(4, 4, 4);
   233  insert into t1 values(NULL, 5, 5);
   234  
   235  drop table if exists t1;
   236  drop table if exists t2;
   237  create table t1(a int, b int, c int, unique key(a, b));
   238  insert into t1 values(1, 1, 1);
   239  insert into t1 values(2, 2, 2);
   240  insert into t1 values(3, 3, 3);
   241  insert into t1 values(4, 4, 4);
   242  insert into t1 values(NULL, 5, 5);
   243  create table t2(a int, b int, c int, unique key(b, c));
   244  insert into t2 values(1, 1, 1);
   245  insert into t2 values(2, 2, 2);
   246  insert into t2 values(3, 3, 3);
   247  insert into t2 values(4, 4, 4);
   248  insert into t2 values(NULL, 5, 5);
   249  select * from t1;
   250  select * from t2;
   251  delete t1, t2 from t1,t2 where t1.a = t2.a;
   252  select * from t1;
   253  select * from t2;
   254  insert into t1 values(1, 1, 1);
   255  insert into t1 values(2, 2, 2);
   256  insert into t1 values(3, 3, 3);
   257  insert into t1 values(4, 4, 4);
   258  insert into t1 values(NULL, 5, 5);
   259  
   260  drop table if exists t1;
   261  drop table if exists t2;
   262  create table t1(a int, b int, c int, unique key(a, b), unique key(b, c));
   263  insert into t1 values(1, 1, 1);
   264  insert into t1 values(2, 2, 2);
   265  insert into t1 values(3, 3, 3);
   266  insert into t1 values(4, 4, 4);
   267  insert into t1 values(NULL, 5, 5);
   268  create table t2(a int, b int, c int, unique key(a, c), unique key(b, c));
   269  insert into t2 values(1, 1, 1);
   270  insert into t2 values(2, 2, 2);
   271  insert into t2 values(3, 3, 3);
   272  insert into t2 values(4, 4, 4);
   273  insert into t2 values(NULL, 5, 5);
   274  select * from t1;
   275  select * from t2;
   276  delete t1, t2 from t1,t2 where t1.a = t2.a;
   277  select * from t1;
   278  select * from t2;
   279  insert into t1 values(1, 1, 1);
   280  insert into t1 values(2, 2, 2);
   281  insert into t1 values(3, 3, 3);
   282  insert into t1 values(4, 4, 4);
   283  insert into t1 values(NULL, 5, 5);
   284  
   285  drop table if exists t1;
   286  drop table if exists t2;
   287  create table t1(a int, b int, c int, unique key(a, b), unique key(c));
   288  insert into t1 values(1, 1, 1);
   289  insert into t1 values(2, 2, 2);
   290  insert into t1 values(3, 3, 3);
   291  insert into t1 values(4, 4, 4);
   292  insert into t1 values(NULL, 5, 5);
   293  create table t2(a int, b int, c int, unique key(a), unique key(b, c));
   294  insert into t2 values(1, 1, 1);
   295  insert into t2 values(2, 2, 2);
   296  insert into t2 values(3, 3, 3);
   297  insert into t2 values(4, 4, 4);
   298  insert into t2 values(NULL, 5, 5);
   299  select * from t1;
   300  select * from t2;
   301  delete t1, t2 from t1,t2 where t1.a = t2.a;
   302  select * from t1;
   303  select * from t2;
   304  insert into t1 values(1, 1, 1);
   305  insert into t1 values(2, 2, 2);
   306  insert into t1 values(3, 3, 3);
   307  insert into t1 values(4, 4, 4);
   308  insert into t1 values(NULL, 5, 5);
   309  
   310  drop table if exists t1;
   311  create table t1(a int, b int, c int, primary key(c), unique key(a));
   312  insert into t1 values(1, 1, 1);
   313  insert into t1 values(2, 2, 2);
   314  insert into t1 values(3, 3, 3);
   315  insert into t1 values(4, 4, 4);
   316  insert into t1 values(NULL, 5, 5);
   317  select * from t1;
   318  delete from t1 where a = 1;
   319  select * from t1;
   320  insert into t1 values(1, 1, 1);
   321  delete from t1 where a is null;
   322  select * from t1;
   323  delete from t1;
   324  select * from t1;
   325  
   326  drop table if exists t1;
   327  create table t1(a int, b int, c int, primary key(c), unique key(a, b));
   328  insert into t1 values(1, 1, 1);
   329  insert into t1 values(2, 2, 2);
   330  insert into t1 values(3, 3, 3);
   331  insert into t1 values(4, 4, 4);
   332  insert into t1 values(NULL, 5, 5);
   333  select * from t1;
   334  delete from t1 where a = 1;
   335  select * from t1;
   336  insert into t1 values(1, 1, 1);
   337  delete from t1 where a is null;
   338  select * from t1;
   339  delete from t1;
   340  select * from t1;
   341  
   342  drop table if exists t1;
   343  drop table if exists t2;
   344  create table t1(a int, b int, c int, unique key(a), primary key(b));
   345  insert into t1 values(1, 1, 1);
   346  insert into t1 values(2, 2, 2);
   347  insert into t1 values(3, 3, 3);
   348  insert into t1 values(4, 4, 4);
   349  insert into t1 values(NULL, 5, 5);
   350  create table t2(a int, b int, c int, unique key(b), primary key(b, c));
   351  insert into t2 values(1, 1, 1);
   352  insert into t2 values(2, 2, 2);
   353  insert into t2 values(3, 3, 3);
   354  insert into t2 values(4, 4, 4);
   355  insert into t2 values(NULL, 5, 5);
   356  select * from t1;
   357  select * from t2;
   358  delete t1, t2 from t1,t2 where t1.a = t2.a;
   359  select * from t1;
   360  select * from t2;
   361  insert into t1 values(1, 1, 1);
   362  insert into t1 values(2, 2, 2);
   363  insert into t1 values(3, 3, 3);
   364  insert into t1 values(4, 4, 4);