github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/exec/execbuilder/testdata/delete (about) 1 # LogicTest: local 2 3 statement ok 4 CREATE TABLE kv ( 5 k INT PRIMARY KEY, 6 v INT, 7 UNIQUE INDEX foo (v), 8 INDEX bar (k, v) 9 ) 10 11 statement ok 12 CREATE TABLE unindexed ( 13 k INT PRIMARY KEY, 14 v INT 15 ) 16 17 statement ok 18 CREATE TABLE indexed (id int primary key, value int, other int, index (value)) 19 20 statement count 4 21 INSERT INTO kv VALUES (1, 2), (3, 4), (5, 6), (7, 8) 22 23 statement count 2 24 DELETE FROM kv WHERE k=3 OR v=6 25 26 query II 27 DELETE FROM kv RETURNING k, v 28 ---- 29 1 2 30 7 8 31 32 statement ok 33 SET tracing = on,kv,results; SELECT * FROM kv; SET tracing = off 34 35 query T 36 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 37 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 38 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 39 ---- 40 41 statement ok 42 SET tracing = on,kv,results; SELECT * FROM kv@foo; SET tracing = off 43 44 query T 45 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 46 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 47 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 48 ---- 49 50 statement ok 51 SET tracing = on,kv,results; SELECT * FROM kv@bar; SET tracing = off 52 53 query T 54 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 55 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 56 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 57 ---- 58 59 # Check that EXPLAIN does not destroy data (#6613) 60 query TTT colnames 61 EXPLAIN DELETE FROM unindexed 62 ---- 63 tree field description 64 · distributed false 65 · vectorized false 66 delete range · · 67 · from unindexed 68 · spans FULL SCAN 69 70 query TTT 71 EXPLAIN DELETE FROM unindexed WHERE v = 7 ORDER BY v LIMIT 10 72 ---- 73 · distributed false 74 · vectorized false 75 count · · 76 └── delete · · 77 │ from unindexed 78 │ strategy deleter 79 │ auto commit · 80 └── render · · 81 └── limit · · 82 │ count 10 83 └── scan · · 84 · table unindexed@primary 85 · spans FULL SCAN 86 · filter v = 7 87 88 # Check DELETE with LIMIT clause (MySQL extension) 89 query TTT 90 EXPLAIN DELETE FROM unindexed WHERE v = 5 LIMIT 10 91 ---- 92 · distributed false 93 · vectorized false 94 count · · 95 └── delete · · 96 │ from unindexed 97 │ strategy deleter 98 │ auto commit · 99 └── render · · 100 └── limit · · 101 │ count 10 102 └── scan · · 103 · table unindexed@primary 104 · spans FULL SCAN 105 · filter v = 5 106 107 # Check fast DELETE. 108 query TTT 109 EXPLAIN DELETE FROM unindexed WHERE k > 0 110 ---- 111 · distributed false 112 · vectorized false 113 delete range · · 114 · from unindexed 115 · spans /1- 116 117 # Check fast DELETE with reverse scans (not supported by optimizer). 118 query error DELETE statement requires LIMIT when ORDER BY is used 119 EXPLAIN DELETE FROM unindexed WHERE true ORDER BY k DESC 120 121 # Check that limits don't permit fast deletes. 122 query TTT 123 EXPLAIN DELETE FROM unindexed WHERE k > 0 LIMIT 1 124 ---- 125 · distributed false 126 · vectorized false 127 count · · 128 └── delete · · 129 │ from unindexed 130 │ strategy deleter 131 │ auto commit · 132 └── scan · · 133 · table unindexed@primary 134 · spans /1- 135 · limit 1 136 137 query TTT 138 EXPLAIN DELETE FROM indexed WHERE value = 5 LIMIT 10 139 ---- 140 · distributed false 141 · vectorized false 142 count · · 143 └── delete · · 144 │ from indexed 145 │ strategy deleter 146 │ auto commit · 147 └── scan · · 148 · table indexed@indexed_value_idx 149 · spans /5-/6 150 · limit 10 151 152 query TTT 153 EXPLAIN DELETE FROM indexed LIMIT 10 154 ---- 155 · distributed false 156 · vectorized false 157 count · · 158 └── delete · · 159 │ from indexed 160 │ strategy deleter 161 │ auto commit · 162 └── scan · · 163 · table indexed@indexed_value_idx 164 · spans LIMITED SCAN 165 · limit 10 166 167 # TODO(andyk): Prune columns so that index-join is not necessary. 168 query TTT 169 EXPLAIN DELETE FROM indexed WHERE value = 5 LIMIT 10 RETURNING id 170 ---- 171 · distributed false 172 · vectorized false 173 run · · 174 └── delete · · 175 │ from indexed 176 │ strategy deleter 177 │ auto commit · 178 └── scan · · 179 · table indexed@indexed_value_idx 180 · spans /5-/6 181 · limit 10 182 183 # Ensure that index hints in DELETE statements force the choice of a specific index 184 # as described in #38799. 185 statement ok 186 CREATE TABLE t38799 (a INT PRIMARY KEY, b INT, c INT, INDEX foo(b)) 187 188 query TTTTT 189 EXPLAIN (VERBOSE) DELETE FROM t38799@foo 190 ---- 191 · distributed false · · 192 · vectorized false · · 193 count · · () · 194 └── delete · · () · 195 │ from t38799 · · 196 │ strategy deleter · · 197 │ auto commit · · · 198 └── scan · · (a, b) · 199 · table t38799@foo · · 200 · spans FULL SCAN · · 201 202 # Tracing tests for fast delete. 203 statement ok 204 CREATE TABLE a (a INT PRIMARY KEY) 205 206 # Delete range operates in chunks of 600 (defined by sql.TableTruncateChunkSize). 207 statement ok 208 INSERT INTO a SELECT * FROM generate_series(1,1000) 209 210 statement ok 211 SET tracing = on,kv; DELETE FROM a; SET tracing = off 212 213 # Ensure that DelRange requests are chunked for DELETE FROM... 214 query TT 215 SELECT operation, message FROM [SHOW KV TRACE FOR SESSION] 216 WHERE message LIKE '%DelRange%' OR message LIKE '%DelRng%' 217 ---- 218 flow DelRange /Table/57/1 - /Table/57/2 219 dist sender send r32: sending batch 1 DelRng to (n1,s1):1 220 flow DelRange /Table/57/1/601/0 - /Table/57/2 221 dist sender send r32: sending batch 1 DelRng to (n1,s1):1 222 223 # Ensure that DelRange requests are autocommitted when DELETE FROM happens on a 224 # chunk of fewer than 600 keys. 225 226 statement ok 227 INSERT INTO a VALUES(5) 228 229 statement ok 230 SET tracing = on,kv; DELETE FROM a WHERE a = 5; SET tracing = off 231 232 query TT 233 SELECT operation, message FROM [SHOW KV TRACE FOR SESSION] 234 WHERE message LIKE '%DelRange%' OR message LIKE '%sending batch%' 235 ---- 236 flow DelRange /Table/57/1/5 - /Table/57/1/5/# 237 dist sender send r32: sending batch 1 DelRng, 1 EndTxn to (n1,s1):1 238 239 # Test use of fast path when there are interleaved tables. 240 241 statement ok 242 CREATE TABLE parent (id INT PRIMARY KEY) 243 244 # Delete range should be used. 245 query TTT 246 EXPLAIN DELETE FROM parent WHERE id > 10 247 ---- 248 · distributed false 249 · vectorized false 250 delete range · · 251 · from parent 252 · spans /11- 253 254 statement ok 255 CREATE TABLE child ( 256 pid INT, 257 id INT, 258 PRIMARY KEY (pid, id), 259 FOREIGN KEY (pid) REFERENCES parent(id) ON DELETE CASCADE 260 ) INTERLEAVE IN PARENT parent(pid) 261 262 # Delete range should be used. 263 query TTT 264 EXPLAIN DELETE FROM parent WHERE id > 10 265 ---- 266 · distributed false 267 · vectorized false 268 delete range · · 269 · from parent 270 · spans /11- 271 272 # Delete range should not be used when deleting from the child. 273 query TTT 274 EXPLAIN DELETE FROM child WHERE id > 10 275 ---- 276 · distributed false 277 · vectorized false 278 count · · 279 └── delete · · 280 │ from child 281 │ strategy deleter 282 │ auto commit · 283 └── scan · · 284 · table child@primary 285 · spans FULL SCAN 286 · filter id > 10 287 288 statement ok 289 CREATE TABLE sibling ( 290 pid INT, 291 id INT, 292 PRIMARY KEY (pid, id), 293 FOREIGN KEY (pid) REFERENCES parent(id) ON DELETE CASCADE 294 ) INTERLEAVE IN PARENT parent(pid) 295 296 # Delete range should be used. 297 query TTT 298 EXPLAIN DELETE FROM parent WHERE id > 10 299 ---- 300 · distributed false 301 · vectorized false 302 delete range · · 303 · from parent 304 · spans /11- 305 306 statement ok 307 CREATE TABLE grandchild ( 308 gid INT, 309 pid INT, 310 id INT, 311 FOREIGN KEY (gid, pid) REFERENCES child(pid, id) ON DELETE CASCADE, 312 PRIMARY KEY(gid, pid, id) 313 ) INTERLEAVE IN PARENT child(gid, pid) 314 315 # Delete range should be used. 316 query TTT 317 EXPLAIN DELETE FROM parent WHERE id > 10 318 ---- 319 · distributed false 320 · vectorized false 321 delete range · · 322 · from parent 323 · spans /11- 324 325 statement ok 326 CREATE TABLE external_ref ( 327 id INT, 328 parent_id INT, 329 child_id INT, 330 FOREIGN KEY (parent_id, child_id) REFERENCES child(pid, id) ON DELETE CASCADE 331 ) 332 333 # Delete range should not be used (external ref). 334 query TTT 335 EXPLAIN DELETE FROM parent WHERE id > 10 336 ---- 337 · distributed false 338 · vectorized false 339 root · · 340 ├── count · · 341 │ └── delete · · 342 │ │ from parent 343 │ │ strategy deleter 344 │ └── buffer node · · 345 │ │ label buffer 1 346 │ └── scan · · 347 │ table parent@primary 348 │ spans /11- 349 ├── fk-cascade · · 350 │ fk fk_pid_ref_parent 351 │ input buffer 1 352 └── fk-cascade · · 353 · fk fk_pid_ref_parent 354 · input buffer 1 355 356 statement ok 357 DROP TABLE external_ref 358 359 statement ok 360 CREATE TABLE child_with_index ( 361 pid INT, 362 child_id INT, 363 other_field STRING, 364 PRIMARY KEY (pid, child_id), 365 FOREIGN KEY (pid) REFERENCES parent(id), 366 UNIQUE (other_field) 367 ) INTERLEAVE IN PARENT parent(pid) 368 369 # Delete range should not be used (child with secondary index). 370 query TTT 371 EXPLAIN DELETE FROM parent WHERE id > 10 372 ---- 373 · distributed false 374 · vectorized false 375 root · · 376 ├── count · · 377 │ └── delete · · 378 │ │ from parent 379 │ │ strategy deleter 380 │ └── buffer node · · 381 │ │ label buffer 1 382 │ └── scan · · 383 │ table parent@primary 384 │ spans /11- 385 ├── fk-cascade · · 386 │ fk fk_pid_ref_parent 387 │ input buffer 1 388 ├── fk-cascade · · 389 │ fk fk_pid_ref_parent 390 │ input buffer 1 391 └── fk-check · · 392 └── error if rows · · 393 └── render · · 394 └── hash-join · · 395 │ type inner 396 │ equality (id) = (pid) 397 │ left cols are key · 398 │ right cols are key · 399 ├── scan buffer node · · 400 │ label buffer 1 401 └── distinct · · 402 │ distinct on pid 403 │ order key pid 404 └── scan · · 405 · table child_with_index@primary 406 · spans FULL SCAN 407 408 statement ok 409 DROP TABLE child_with_index 410 411 statement ok 412 CREATE TABLE child_without_cascade ( 413 pid INT, 414 id INT, 415 PRIMARY KEY (pid, id), 416 FOREIGN KEY (pid) REFERENCES parent(id) 417 ) INTERLEAVE IN PARENT parent(pid) 418 419 # Delete range should not be used (child without cascading FK). 420 query TTT 421 EXPLAIN DELETE FROM parent WHERE id > 10 422 ---- 423 · distributed false 424 · vectorized false 425 root · · 426 ├── count · · 427 │ └── delete · · 428 │ │ from parent 429 │ │ strategy deleter 430 │ └── buffer node · · 431 │ │ label buffer 1 432 │ └── scan · · 433 │ table parent@primary 434 │ spans /11- 435 ├── fk-cascade · · 436 │ fk fk_pid_ref_parent 437 │ input buffer 1 438 ├── fk-cascade · · 439 │ fk fk_pid_ref_parent 440 │ input buffer 1 441 └── fk-check · · 442 └── error if rows · · 443 └── render · · 444 └── hash-join · · 445 │ type inner 446 │ equality (id) = (pid) 447 │ left cols are key · 448 │ right cols are key · 449 ├── scan buffer node · · 450 │ label buffer 1 451 └── distinct · · 452 │ distinct on pid 453 │ order key pid 454 └── scan · · 455 · table child_without_cascade@primary 456 · spans FULL SCAN 457 458 statement ok 459 DROP TABLE child_without_cascade 460 461 statement ok 462 CREATE TABLE child_without_fk ( 463 pid INT, 464 id INT, 465 PRIMARY KEY (pid, id) 466 ) INTERLEAVE IN PARENT parent(pid) 467 468 # Delete range should not be used (child without cascading FK). 469 query TTT 470 EXPLAIN DELETE FROM parent WHERE id > 10 471 ---- 472 · distributed false 473 · vectorized false 474 root · · 475 ├── count · · 476 │ └── delete · · 477 │ │ from parent 478 │ │ strategy deleter 479 │ └── buffer node · · 480 │ │ label buffer 1 481 │ └── scan · · 482 │ table parent@primary 483 │ spans /11- 484 ├── fk-cascade · · 485 │ fk fk_pid_ref_parent 486 │ input buffer 1 487 └── fk-cascade · · 488 · fk fk_pid_ref_parent 489 · input buffer 1 490 491 statement ok 492 DROP TABLE child_without_fk 493 494 # Test case where we have a cascading FK but the columns are not in the right 495 # order. 496 statement ok 497 CREATE TABLE ab (a INT, b INT, PRIMARY KEY (a, b)) 498 499 statement ok 500 CREATE TABLE abc ( 501 a INT, b INT, c INT, 502 FOREIGN KEY (b, a) REFERENCES ab(a, b) ON DELETE CASCADE, 503 PRIMARY KEY (a, b, c) 504 ) INTERLEAVE IN PARENT ab(a, b) 505 506 507 # Delete range should not be used (FK columns are not in the right order). 508 query TTT 509 EXPLAIN DELETE FROM ab WHERE a = 1 510 ---- 511 · distributed false 512 · vectorized false 513 root · · 514 ├── count · · 515 │ └── delete · · 516 │ │ from ab 517 │ │ strategy deleter 518 │ └── buffer node · · 519 │ │ label buffer 1 520 │ └── scan · · 521 │ table ab@primary 522 │ spans /1-/2 523 └── fk-cascade · · 524 · fk fk_b_ref_ab 525 · input buffer 1