github.com/matrixorigin/matrixone@v1.2.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  -- @pattern
   184  insert into t1 values(NULL, 5, 5);
   185  
   186  drop table if exists t1;
   187  drop table if exists t2;
   188  create table t1(a int, b int, c int, unique key(a));
   189  insert into t1 values(1, 1, 1);
   190  insert into t1 values(2, 2, 2);
   191  insert into t1 values(3, 3, 3);
   192  insert into t1 values(4, 4, 4);
   193  insert into t1 values(NULL, 5, 5);
   194  create table t2(a int, b int, c int, unique key(b));
   195  insert into t2 values(1, 1, 1);
   196  insert into t2 values(2, 2, 2);
   197  insert into t2 values(3, 3, 3);
   198  insert into t2 values(4, 4, 4);
   199  insert into t2 values(NULL, 5, 5);
   200  select * from t1;
   201  select * from t2;
   202  delete t1, t2 from t1,t2 where t1.a = t2.a;
   203  select * from t1;
   204  select * from t2;
   205  insert into t1 values(1, 1, 1);
   206  insert into t1 values(2, 2, 2);
   207  insert into t1 values(3, 3, 3);
   208  insert into t1 values(4, 4, 4);
   209  insert into t1 values(NULL, 5, 5);
   210  
   211  drop table if exists t1;
   212  drop table if exists t2;
   213  create table t1(a int, b int, c int, unique key(a), unique key(b));
   214  insert into t1 values(1, 1, 1);
   215  insert into t1 values(2, 2, 2);
   216  insert into t1 values(3, 3, 3);
   217  insert into t1 values(4, 4, 4);
   218  insert into t1 values(NULL, 5, 5);
   219  create table t2(a int, b int, c int, unique key(b), unique key(c));
   220  insert into t2 values(1, 1, 1);
   221  insert into t2 values(2, 2, 2);
   222  insert into t2 values(3, 3, 3);
   223  insert into t2 values(4, 4, 4);
   224  insert into t2 values(NULL, 5, 5);
   225  select * from t1;
   226  select * from t2;
   227  delete t1, t2 from t1,t2 where t1.a = t2.a;
   228  select * from t1;
   229  select * from t2;
   230  insert into t1 values(1, 1, 1);
   231  insert into t1 values(2, 2, 2);
   232  insert into t1 values(3, 3, 3);
   233  insert into t1 values(4, 4, 4);
   234  insert into t1 values(NULL, 5, 5);
   235  
   236  drop table if exists t1;
   237  drop table if exists t2;
   238  create table t1(a int, b int, c int, unique key(a, b));
   239  insert into t1 values(1, 1, 1);
   240  insert into t1 values(2, 2, 2);
   241  insert into t1 values(3, 3, 3);
   242  insert into t1 values(4, 4, 4);
   243  insert into t1 values(NULL, 5, 5);
   244  create table t2(a int, b int, c int, unique key(b, c));
   245  insert into t2 values(1, 1, 1);
   246  insert into t2 values(2, 2, 2);
   247  insert into t2 values(3, 3, 3);
   248  insert into t2 values(4, 4, 4);
   249  insert into t2 values(NULL, 5, 5);
   250  select * from t1;
   251  select * from t2;
   252  delete t1, t2 from t1,t2 where t1.a = t2.a;
   253  select * from t1;
   254  select * from t2;
   255  insert into t1 values(1, 1, 1);
   256  insert into t1 values(2, 2, 2);
   257  insert into t1 values(3, 3, 3);
   258  insert into t1 values(4, 4, 4);
   259  insert into t1 values(NULL, 5, 5);
   260  
   261  drop table if exists t1;
   262  drop table if exists t2;
   263  create table t1(a int, b int, c int, unique key(a, b), unique key(b, c));
   264  insert into t1 values(1, 1, 1);
   265  insert into t1 values(2, 2, 2);
   266  insert into t1 values(3, 3, 3);
   267  insert into t1 values(4, 4, 4);
   268  insert into t1 values(NULL, 5, 5);
   269  create table t2(a int, b int, c int, unique key(a, c), unique key(b, c));
   270  insert into t2 values(1, 1, 1);
   271  insert into t2 values(2, 2, 2);
   272  insert into t2 values(3, 3, 3);
   273  insert into t2 values(4, 4, 4);
   274  insert into t2 values(NULL, 5, 5);
   275  select * from t1;
   276  select * from t2;
   277  delete t1, t2 from t1,t2 where t1.a = t2.a;
   278  select * from t1;
   279  select * from t2;
   280  insert into t1 values(1, 1, 1);
   281  insert into t1 values(2, 2, 2);
   282  insert into t1 values(3, 3, 3);
   283  insert into t1 values(4, 4, 4);
   284  -- @pattern
   285  insert into t1 values(NULL, 5, 5);
   286  
   287  drop table if exists t1;
   288  drop table if exists t2;
   289  create table t1(a int, b int, c int, unique key(a, b), unique key(c));
   290  insert into t1 values(1, 1, 1);
   291  insert into t1 values(2, 2, 2);
   292  insert into t1 values(3, 3, 3);
   293  insert into t1 values(4, 4, 4);
   294  insert into t1 values(NULL, 5, 5);
   295  create table t2(a int, b int, c int, unique key(a), unique key(b, c));
   296  insert into t2 values(1, 1, 1);
   297  insert into t2 values(2, 2, 2);
   298  insert into t2 values(3, 3, 3);
   299  insert into t2 values(4, 4, 4);
   300  insert into t2 values(NULL, 5, 5);
   301  select * from t1;
   302  select * from t2;
   303  delete t1, t2 from t1,t2 where t1.a = t2.a;
   304  select * from t1;
   305  select * from t2;
   306  insert into t1 values(1, 1, 1);
   307  insert into t1 values(2, 2, 2);
   308  insert into t1 values(3, 3, 3);
   309  insert into t1 values(4, 4, 4);
   310  insert into t1 values(NULL, 5, 5);
   311  
   312  drop table if exists t1;
   313  create table t1(a int, b int, c int, primary key(c), unique key(a));
   314  insert into t1 values(1, 1, 1);
   315  insert into t1 values(2, 2, 2);
   316  insert into t1 values(3, 3, 3);
   317  insert into t1 values(4, 4, 4);
   318  insert into t1 values(NULL, 5, 5);
   319  select * from t1;
   320  delete from t1 where a = 1;
   321  select * from t1;
   322  insert into t1 values(1, 1, 1);
   323  delete from t1 where a is null;
   324  select * from t1;
   325  delete from t1;
   326  select * from t1;
   327  
   328  drop table if exists t1;
   329  create table t1(a int, b int, c int, primary key(c), unique key(a, b));
   330  insert into t1 values(1, 1, 1);
   331  insert into t1 values(2, 2, 2);
   332  insert into t1 values(3, 3, 3);
   333  insert into t1 values(4, 4, 4);
   334  insert into t1 values(NULL, 5, 5);
   335  select * from t1;
   336  delete from t1 where a = 1;
   337  select * from t1;
   338  insert into t1 values(1, 1, 1);
   339  delete from t1 where a is null;
   340  select * from t1;
   341  delete from t1;
   342  select * from t1;
   343  
   344  drop table if exists t1;
   345  drop table if exists t2;
   346  create table t1(a int, b int, c int, unique key(a), primary key(b));
   347  insert into t1 values(1, 1, 1);
   348  insert into t1 values(2, 2, 2);
   349  insert into t1 values(3, 3, 3);
   350  insert into t1 values(4, 4, 4);
   351  insert into t1 values(NULL, 5, 5);
   352  create table t2(a int, b int, c int, unique key(b), primary key(b, c));
   353  insert into t2 values(1, 1, 1);
   354  insert into t2 values(2, 2, 2);
   355  insert into t2 values(3, 3, 3);
   356  insert into t2 values(4, 4, 4);
   357  insert into t2 values(NULL, 5, 5);
   358  select * from t1;
   359  select * from t2;
   360  delete t1, t2 from t1,t2 where t1.a = t2.a;
   361  select * from t1;
   362  select * from t2;
   363  insert into t1 values(1, 1, 1);
   364  insert into t1 values(2, 2, 2);
   365  insert into t1 values(3, 3, 3);
   366  insert into t1 values(4, 4, 4);
   367  drop database if exists db1;