github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/dml/delete/delete_index.result (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(NULL, 5, 5); 11 select * from t1; 12 a b c 13 1 1 1 14 2 2 2 15 3 3 3 16 4 4 4 17 null 5 5 18 delete from t1 where a = 1; 19 select * from t1; 20 a b c 21 2 2 2 22 3 3 3 23 4 4 4 24 null 5 5 25 insert into t1 values(1, 1, 1); 26 delete from t1 where a is null; 27 select * from t1; 28 a b c 29 2 2 2 30 3 3 3 31 4 4 4 32 1 1 1 33 delete from t1; 34 select * from t1; 35 a b c 36 drop table if exists t1; 37 create table t1(a int, b int, c int, unique key(a, b)); 38 insert into t1 values(1, 1, 1); 39 insert into t1 values(2, 2, 2); 40 insert into t1 values(3, 3, 3); 41 insert into t1 values(4, 4, 4); 42 insert into t1 values(NULL, 5, 5); 43 select * from t1; 44 a b c 45 1 1 1 46 2 2 2 47 3 3 3 48 4 4 4 49 null 5 5 50 delete from t1 where a = 1; 51 select * from t1; 52 a b c 53 2 2 2 54 3 3 3 55 4 4 4 56 null 5 5 57 insert into t1 values(1, 1, 1); 58 delete from t1 where a is null; 59 select * from t1; 60 a b c 61 2 2 2 62 3 3 3 63 4 4 4 64 1 1 1 65 delete from t1; 66 select * from t1; 67 a b c 68 drop table if exists t1; 69 create table t1(a int, b int, c int, unique key(a), unique key(b)); 70 insert into t1 values(1, 1, 1); 71 insert into t1 values(2, 2, 2); 72 insert into t1 values(3, 3, 3); 73 insert into t1 values(4, 4, 4); 74 insert into t1 values(NULL, 5, 5); 75 select * from t1; 76 a b c 77 1 1 1 78 2 2 2 79 3 3 3 80 4 4 4 81 null 5 5 82 delete from t1 where a = 1; 83 select * from t1; 84 a b c 85 2 2 2 86 3 3 3 87 4 4 4 88 null 5 5 89 insert into t1 values(1, 1, 1); 90 delete from t1 where a is null; 91 select * from t1; 92 a b c 93 2 2 2 94 3 3 3 95 4 4 4 96 1 1 1 97 delete from t1; 98 select * from t1; 99 a b c 100 drop table if exists t1; 101 create table t1(a int, b int, c int, unique key(a, b), unique key(b, c)); 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(NULL, 5, 5); 107 select * from t1; 108 a b c 109 1 1 1 110 2 2 2 111 3 3 3 112 4 4 4 113 null 5 5 114 delete from t1 where a = 1; 115 select * from t1; 116 a b c 117 2 2 2 118 3 3 3 119 4 4 4 120 null 5 5 121 insert into t1 values(1, 1, 1); 122 delete from t1 where a is null; 123 select * from t1; 124 a b c 125 2 2 2 126 3 3 3 127 4 4 4 128 1 1 1 129 delete from t1; 130 select * from t1; 131 a b c 132 drop table if exists t1; 133 create table t1(a int, b int, c int, unique key(a, b), unique key(c)); 134 insert into t1 values(1, 1, 1); 135 insert into t1 values(2, 2, 2); 136 insert into t1 values(3, 3, 3); 137 insert into t1 values(4, 4, 4); 138 insert into t1 values(NULL, 5, 5); 139 select * from t1; 140 a b c 141 1 1 1 142 2 2 2 143 3 3 3 144 4 4 4 145 null 5 5 146 delete from t1 where a = 1; 147 select * from t1; 148 a b c 149 2 2 2 150 3 3 3 151 4 4 4 152 null 5 5 153 insert into t1 values(1, 1, 1); 154 delete from t1 where a is null; 155 select * from t1; 156 a b c 157 2 2 2 158 3 3 3 159 4 4 4 160 1 1 1 161 delete from t1; 162 select * from t1; 163 a b c 164 drop table if exists t1; 165 drop table if exists t2; 166 create table t1(a int, b int, c int, unique key(a)); 167 insert into t1 values(1, 1, 1); 168 insert into t1 values(2, 2, 2); 169 insert into t1 values(3, 3, 3); 170 insert into t1 values(4, 4, 4); 171 insert into t1 values(NULL, 5, 5); 172 create table t2(a int, b int, c int); 173 insert into t2 values(1, 1, 1); 174 insert into t2 values(2, 2, 2); 175 insert into t2 values(3, 3, 3); 176 insert into t2 values(4, 4, 4); 177 insert into t2 values(NULL, 5, 5); 178 select * from t1; 179 a b c 180 1 1 1 181 2 2 2 182 3 3 3 183 4 4 4 184 null 5 5 185 select * from t2; 186 a b c 187 1 1 1 188 2 2 2 189 3 3 3 190 4 4 4 191 null 5 5 192 delete t1, t2 from t1,t2 where t1.a = t2.a; 193 select * from t1; 194 a b c 195 null 5 5 196 select * from t2; 197 a b c 198 null 5 5 199 insert into t1 values(1, 1, 1); 200 insert into t1 values(2, 2, 2); 201 insert into t1 values(3, 3, 3); 202 insert into t1 values(4, 4, 4); 203 insert into t1 values(NULL, 5, 5); 204 drop table if exists t1; 205 drop table if exists t2; 206 create table t1(a int, b int, c int, unique key(a), unique key(b)); 207 insert into t1 values(1, 1, 1); 208 insert into t1 values(2, 2, 2); 209 insert into t1 values(3, 3, 3); 210 insert into t1 values(4, 4, 4); 211 insert into t1 values(NULL, 5, 5); 212 create table t2(a int, b int, c int); 213 insert into t2 values(1, 1, 1); 214 insert into t2 values(2, 2, 2); 215 insert into t2 values(3, 3, 3); 216 insert into t2 values(4, 4, 4); 217 insert into t2 values(NULL, 5, 5); 218 select * from t1; 219 a b c 220 1 1 1 221 2 2 2 222 3 3 3 223 4 4 4 224 null 5 5 225 select * from t2; 226 a b c 227 1 1 1 228 2 2 2 229 3 3 3 230 4 4 4 231 null 5 5 232 delete t1, t2 from t1,t2 where t1.a = t2.a; 233 select * from t1; 234 a b c 235 null 5 5 236 select * from t2; 237 a b c 238 null 5 5 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 Duplicate entry '5' for key '__mo_index_idx_col' 245 drop table if exists t1; 246 drop table if exists t2; 247 create table t1(a int, b int, c int, unique key(a, b)); 248 insert into t1 values(1, 1, 1); 249 insert into t1 values(2, 2, 2); 250 insert into t1 values(3, 3, 3); 251 insert into t1 values(4, 4, 4); 252 insert into t1 values(NULL, 5, 5); 253 create table t2(a int, b int, c int); 254 insert into t2 values(1, 1, 1); 255 insert into t2 values(2, 2, 2); 256 insert into t2 values(3, 3, 3); 257 insert into t2 values(4, 4, 4); 258 insert into t2 values(NULL, 5, 5); 259 select * from t1; 260 a b c 261 1 1 1 262 2 2 2 263 3 3 3 264 4 4 4 265 null 5 5 266 select * from t2; 267 a b c 268 1 1 1 269 2 2 2 270 3 3 3 271 4 4 4 272 null 5 5 273 delete t1, t2 from t1,t2 where t1.a = t2.a; 274 select * from t1; 275 a b c 276 null 5 5 277 select * from t2; 278 a b c 279 null 5 5 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 insert into t1 values(NULL, 5, 5); 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(b, 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); 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 a b c 301 1 1 1 302 2 2 2 303 3 3 3 304 4 4 4 305 null 5 5 306 select * from t2; 307 a b c 308 1 1 1 309 2 2 2 310 3 3 3 311 4 4 4 312 null 5 5 313 delete t1, t2 from t1,t2 where t1.a = t2.a; 314 select * from t1; 315 a b c 316 null 5 5 317 select * from t2; 318 a b c 319 null 5 5 320 insert into t1 values(1, 1, 1); 321 insert into t1 values(2, 2, 2); 322 insert into t1 values(3, 3, 3); 323 insert into t1 values(4, 4, 4); 324 insert into t1 values(NULL, 5, 5); 325 Duplicate entry ('\(\d\,\d\)'|'\d\w\d{5}\w\d{4}') for key '__mo_index_idx_col' 326 drop table if exists t1; 327 drop table if exists t2; 328 create table t1(a int, b int, c int, unique key(a)); 329 insert into t1 values(1, 1, 1); 330 insert into t1 values(2, 2, 2); 331 insert into t1 values(3, 3, 3); 332 insert into t1 values(4, 4, 4); 333 insert into t1 values(NULL, 5, 5); 334 create table t2(a int, b int, c int, unique key(b)); 335 insert into t2 values(1, 1, 1); 336 insert into t2 values(2, 2, 2); 337 insert into t2 values(3, 3, 3); 338 insert into t2 values(4, 4, 4); 339 insert into t2 values(NULL, 5, 5); 340 select * from t1; 341 a b c 342 1 1 1 343 2 2 2 344 3 3 3 345 4 4 4 346 null 5 5 347 select * from t2; 348 a b c 349 1 1 1 350 2 2 2 351 3 3 3 352 4 4 4 353 null 5 5 354 delete t1, t2 from t1,t2 where t1.a = t2.a; 355 select * from t1; 356 a b c 357 null 5 5 358 select * from t2; 359 a b c 360 null 5 5 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); 365 insert into t1 values(NULL, 5, 5); 366 drop table if exists t1; 367 drop table if exists t2; 368 create table t1(a int, b int, c int, unique key(a), unique key(b)); 369 insert into t1 values(1, 1, 1); 370 insert into t1 values(2, 2, 2); 371 insert into t1 values(3, 3, 3); 372 insert into t1 values(4, 4, 4); 373 insert into t1 values(NULL, 5, 5); 374 create table t2(a int, b int, c int, unique key(b), unique key(c)); 375 insert into t2 values(1, 1, 1); 376 insert into t2 values(2, 2, 2); 377 insert into t2 values(3, 3, 3); 378 insert into t2 values(4, 4, 4); 379 insert into t2 values(NULL, 5, 5); 380 select * from t1; 381 a b c 382 1 1 1 383 2 2 2 384 3 3 3 385 4 4 4 386 null 5 5 387 select * from t2; 388 a b c 389 1 1 1 390 2 2 2 391 3 3 3 392 4 4 4 393 null 5 5 394 delete t1, t2 from t1,t2 where t1.a = t2.a; 395 select * from t1; 396 a b c 397 null 5 5 398 select * from t2; 399 a b c 400 null 5 5 401 insert into t1 values(1, 1, 1); 402 insert into t1 values(2, 2, 2); 403 insert into t1 values(3, 3, 3); 404 insert into t1 values(4, 4, 4); 405 insert into t1 values(NULL, 5, 5); 406 Duplicate entry '5' for key '__mo_index_idx_col' 407 drop table if exists t1; 408 drop table if exists t2; 409 create table t1(a int, b int, c int, unique key(a, b)); 410 insert into t1 values(1, 1, 1); 411 insert into t1 values(2, 2, 2); 412 insert into t1 values(3, 3, 3); 413 insert into t1 values(4, 4, 4); 414 insert into t1 values(NULL, 5, 5); 415 create table t2(a int, b int, c int, unique key(b, c)); 416 insert into t2 values(1, 1, 1); 417 insert into t2 values(2, 2, 2); 418 insert into t2 values(3, 3, 3); 419 insert into t2 values(4, 4, 4); 420 insert into t2 values(NULL, 5, 5); 421 select * from t1; 422 a b c 423 1 1 1 424 2 2 2 425 3 3 3 426 4 4 4 427 null 5 5 428 select * from t2; 429 a b c 430 1 1 1 431 2 2 2 432 3 3 3 433 4 4 4 434 null 5 5 435 delete t1, t2 from t1,t2 where t1.a = t2.a; 436 select * from t1; 437 a b c 438 null 5 5 439 select * from t2; 440 a b c 441 null 5 5 442 insert into t1 values(1, 1, 1); 443 insert into t1 values(2, 2, 2); 444 insert into t1 values(3, 3, 3); 445 insert into t1 values(4, 4, 4); 446 insert into t1 values(NULL, 5, 5); 447 drop table if exists t1; 448 drop table if exists t2; 449 create table t1(a int, b int, c int, unique key(a, b), unique key(b, c)); 450 insert into t1 values(1, 1, 1); 451 insert into t1 values(2, 2, 2); 452 insert into t1 values(3, 3, 3); 453 insert into t1 values(4, 4, 4); 454 insert into t1 values(NULL, 5, 5); 455 create table t2(a int, b int, c int, unique key(a, c), unique key(b, c)); 456 insert into t2 values(1, 1, 1); 457 insert into t2 values(2, 2, 2); 458 insert into t2 values(3, 3, 3); 459 insert into t2 values(4, 4, 4); 460 insert into t2 values(NULL, 5, 5); 461 select * from t1; 462 a b c 463 1 1 1 464 2 2 2 465 3 3 3 466 4 4 4 467 null 5 5 468 select * from t2; 469 a b c 470 1 1 1 471 2 2 2 472 3 3 3 473 4 4 4 474 null 5 5 475 delete t1, t2 from t1,t2 where t1.a = t2.a; 476 select * from t1; 477 a b c 478 null 5 5 479 select * from t2; 480 a b c 481 null 5 5 482 insert into t1 values(1, 1, 1); 483 insert into t1 values(2, 2, 2); 484 insert into t1 values(3, 3, 3); 485 insert into t1 values(4, 4, 4); 486 insert into t1 values(NULL, 5, 5); 487 Duplicate entry ('\(\d\,\d\)'|'\d\w\d{5}\w\d{4}') for key '__mo_index_idx_col' 488 drop table if exists t1; 489 drop table if exists t2; 490 create table t1(a int, b int, c int, unique key(a, b), unique key(c)); 491 insert into t1 values(1, 1, 1); 492 insert into t1 values(2, 2, 2); 493 insert into t1 values(3, 3, 3); 494 insert into t1 values(4, 4, 4); 495 insert into t1 values(NULL, 5, 5); 496 create table t2(a int, b int, c int, unique key(a), unique key(b, c)); 497 insert into t2 values(1, 1, 1); 498 insert into t2 values(2, 2, 2); 499 insert into t2 values(3, 3, 3); 500 insert into t2 values(4, 4, 4); 501 insert into t2 values(NULL, 5, 5); 502 select * from t1; 503 a b c 504 1 1 1 505 2 2 2 506 3 3 3 507 4 4 4 508 null 5 5 509 select * from t2; 510 a b c 511 1 1 1 512 2 2 2 513 3 3 3 514 4 4 4 515 null 5 5 516 delete t1, t2 from t1,t2 where t1.a = t2.a; 517 select * from t1; 518 a b c 519 null 5 5 520 select * from t2; 521 a b c 522 null 5 5 523 insert into t1 values(1, 1, 1); 524 insert into t1 values(2, 2, 2); 525 insert into t1 values(3, 3, 3); 526 insert into t1 values(4, 4, 4); 527 insert into t1 values(NULL, 5, 5); 528 Duplicate entry '5' for key '__mo_index_idx_col' 529 drop table if exists t1; 530 create table t1(a int, b int, c int, primary key(c), unique key(a)); 531 insert into t1 values(1, 1, 1); 532 insert into t1 values(2, 2, 2); 533 insert into t1 values(3, 3, 3); 534 insert into t1 values(4, 4, 4); 535 insert into t1 values(NULL, 5, 5); 536 select * from t1; 537 a b c 538 1 1 1 539 2 2 2 540 3 3 3 541 4 4 4 542 null 5 5 543 delete from t1 where a = 1; 544 select * from t1; 545 a b c 546 2 2 2 547 3 3 3 548 4 4 4 549 null 5 5 550 insert into t1 values(1, 1, 1); 551 delete from t1 where a is null; 552 select * from t1; 553 a b c 554 2 2 2 555 3 3 3 556 4 4 4 557 1 1 1 558 delete from t1; 559 select * from t1; 560 a b c 561 drop table if exists t1; 562 create table t1(a int, b int, c int, primary key(c), unique key(a, b)); 563 insert into t1 values(1, 1, 1); 564 insert into t1 values(2, 2, 2); 565 insert into t1 values(3, 3, 3); 566 insert into t1 values(4, 4, 4); 567 insert into t1 values(NULL, 5, 5); 568 select * from t1; 569 a b c 570 1 1 1 571 2 2 2 572 3 3 3 573 4 4 4 574 null 5 5 575 delete from t1 where a = 1; 576 select * from t1; 577 a b c 578 2 2 2 579 3 3 3 580 4 4 4 581 null 5 5 582 insert into t1 values(1, 1, 1); 583 delete from t1 where a is null; 584 select * from t1; 585 a b c 586 2 2 2 587 3 3 3 588 4 4 4 589 1 1 1 590 delete from t1; 591 select * from t1; 592 a b c 593 drop table if exists t1; 594 drop table if exists t2; 595 create table t1(a int, b int, c int, unique key(a), primary key(b)); 596 insert into t1 values(1, 1, 1); 597 insert into t1 values(2, 2, 2); 598 insert into t1 values(3, 3, 3); 599 insert into t1 values(4, 4, 4); 600 insert into t1 values(NULL, 5, 5); 601 create table t2(a int, b int, c int, unique key(b), primary key(b, c)); 602 insert into t2 values(1, 1, 1); 603 insert into t2 values(2, 2, 2); 604 insert into t2 values(3, 3, 3); 605 insert into t2 values(4, 4, 4); 606 insert into t2 values(NULL, 5, 5); 607 select * from t1; 608 a b c 609 1 1 1 610 2 2 2 611 3 3 3 612 4 4 4 613 null 5 5 614 select * from t2; 615 a b c 616 1 1 1 617 2 2 2 618 3 3 3 619 4 4 4 620 null 5 5 621 delete t1, t2 from t1,t2 where t1.a = t2.a; 622 select * from t1; 623 a b c 624 null 5 5 625 select * from t2; 626 a b c 627 null 5 5 628 insert into t1 values(1, 1, 1); 629 insert into t1 values(2, 2, 2); 630 insert into t1 values(3, 3, 3); 631 insert into t1 values(4, 4, 4); 632 drop database if exists db1;