github.com/matrixorigin/matrixone@v1.2.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; 160 161 # test cn block delete for single table, one CN 162 use `delete`; 163 create table temp(a int); 164 insert into temp select * from generate_series(1,8192) g; 165 create table t(a int); 166 insert into t select * from temp; 167 insert into t select * from t; 168 insert into t select * from t; 169 insert into t select * from t; 170 insert into t select * from t; 171 insert into t select * from t; 172 insert into t select * from t; 173 insert into t select * from t; 174 insert into t select * from t; 175 176 -- @bvt:issue#9447 177 insert into t select * from t; 178 begin; 179 insert into t select * from t; 180 delete from t where a = 1; 181 select count(*) from t; 182 rollback; 183 begin; 184 insert into t select * from t; 185 delete from t where a = 1; 186 select count(*) from t; 187 commit; 188 select count(*) from t; 189 -- @bvt:issue 190 191 # test cn block delete for single table, multi CN 192 drop table if exists temp; 193 drop table if exists t; 194 create table temp(a int); 195 insert into temp select * from generate_series(1,8192) g; 196 create table t(a int); 197 insert into t select * from temp; 198 insert into t select * from t; 199 begin; 200 insert into t select * from t; 201 delete from t where a > 1; 202 select count(*) from t; 203 rollback; 204 select count(*) from t; 205 begin; 206 insert into t select * from t; 207 delete from t where a > 1; 208 delete from t where a = 1; 209 select count(*) from t; 210 commit; 211 select count(*) from t; 212 213 drop table if exists t2; 214 create table t2 (a int, b int unique key, c int, d int, primary key(c,d)); 215 insert into t2 values (1,2,1,2); 216 delete from t2 where b in (c in (select 1) and d in (select 1)); 217 select * from t2; 218 219 drop table if exists t7; 220 create table t7(a int primary key, b int unique key, c varchar(20) unique key); 221 insert into t7 select result, result, "a"||result from generate_series(1,20000) g; 222 select count(*) from t7; 223 delete from t7; 224 select count(*) from t7; 225 226 drop database if exists `delete`; 227 228 drop database if exists db1; 229 drop database if exists db2; 230 create database db1; 231 create database db2; 232 use db2; 233 drop table if exists t1; 234 create table t1 (a int); 235 insert into t1 values (1),(2),(4); 236 use db1; 237 drop table if exists t2; 238 create table t2 (b int); 239 insert into t2 values(1),(2),(3); 240 delete from db1.t2, db2.t1 using db1.t2 join db2.t1 on db1.t2.b = db2.t1.a; 241 select * from db1.t2; 242 select * from db2.t1; 243 244 drop table if exists t3; 245 create table t3 (a int primary key, b int unique key, c int, key(c)); 246 insert into t3 (a, b, c) values (1, 100, 1000); 247 insert into t3 (a, b, c) values (2, 200, 2000); 248 insert into t3 (a, b, c) values (3, 300, 3000); 249 insert into t3 (a, b, c) values (4, 400, 4000); 250 delete from t3 where a > 3; 251 select * from t3; 252 delete from t3 limit 1; 253 select * from t3; 254 delete from t3; 255 select * from t3; 256 257 drop table if exists t4; 258 create table t4 (a int, b int, c int); 259 insert into t4 (a, b, c) values (1, 100, 1000); 260 insert into t4 (a, b, c) values (2, 200, 2000); 261 insert into t4 (a, b, c) values (3, 300, 3000); 262 insert into t4 (a, b, c) values (4, 400, 4000); 263 delete from t4 where a > 3; 264 select * from t4; 265 delete from t4 limit 1; 266 select * from t4; 267 delete from t4; 268 select * from t4; 269 270 create table t5 (id int primary key, c int); 271 create table t6 (id int primary key, t5_id int, foreign KEY (t5_id) references t5(id) on delete cascade); 272 insert into t5 (id, c) values (1, 100); 273 insert into t5 (id, c) values (2, 200); 274 insert into t5 (id, c) values (3, 300); 275 insert into t5 (id, c) values (4, 400); 276 insert into t6 (id, t5_id) values (4, 1); 277 insert into t6 (id, t5_id) values (5, 2); 278 insert into t6 (id, t5_id) values (6, 3); 279 insert into t6 (id, t5_id) values (7, 4); 280 delete from t5 where id > 3; 281 select * from t6; 282 delete from t5 limit 1; 283 select * from t6; 284 delete from t5; 285 select * from t6; 286 287 drop table if exists t6; 288 drop table if exists t5; 289 create table t5 (id int primary key, c int); 290 create table t6 (id int primary key, t5_id int, foreign KEY (t5_id) references t5(id) on delete set null); 291 insert into t5 (id, c) values (1, 100); 292 insert into t5 (id, c) values (2, 200); 293 insert into t5 (id, c) values (3, 300); 294 insert into t5 (id, c) values (4, 400); 295 insert into t6 (id, t5_id) values (4, 1); 296 insert into t6 (id, t5_id) values (5, 2); 297 insert into t6 (id, t5_id) values (6, 3); 298 insert into t6 (id, t5_id) values (7, 4); 299 delete from t5 where id > 3; 300 select * from t6; 301 delete from t5 limit 1; 302 select * from t6; 303 delete from t5; 304 select * from t6; 305 306 drop table if exists t1; 307 drop table if exists t2; 308 drop table if exists t3; 309 drop table if exists t4; 310 drop table if exists t6; 311 drop table if exists t5; 312 drop database if exists db1; 313 drop database if exists db2;