github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/exec/execbuilder/testdata/cascade_opt (about) 1 # LogicTest: local 2 3 # The tests in this file target the new optimizer-driven FK paths (with 4 # fall back on the legacy paths for unsupported cases). 5 statement ok 6 SET optimizer_foreign_keys = true 7 8 statement ok 9 SET experimental_optimizer_foreign_key_cascades = true 10 11 subtest DeleteCascade_Basic 12 ### Basic Delete Cascade 13 # a 14 # / \ 15 # b1 b2 16 # / \ \ 17 # c1 c2 c3 18 19 statement ok 20 CREATE TABLE a ( 21 id STRING PRIMARY KEY 22 ) 23 24 statement ok 25 CREATE TABLE b1 ( 26 id STRING PRIMARY KEY, 27 delete_cascade STRING NOT NULL REFERENCES a ON DELETE CASCADE, 28 FAMILY (id, delete_cascade) 29 ) 30 31 statement ok 32 CREATE TABLE b2 ( 33 id STRING PRIMARY KEY, 34 delete_cascade STRING NOT NULL REFERENCES a ON DELETE CASCADE, 35 FAMILY (id, delete_cascade) 36 ) 37 38 statement ok 39 CREATE TABLE c1 ( 40 id STRING PRIMARY KEY, 41 delete_cascade STRING NOT NULL REFERENCES b1 ON DELETE CASCADE, 42 FAMILY (id, delete_cascade) 43 ) 44 45 statement ok 46 CREATE TABLE c2 ( 47 id STRING PRIMARY KEY, 48 delete_cascade STRING NOT NULL REFERENCES b1 ON DELETE CASCADE, 49 FAMILY (id, delete_cascade) 50 ) 51 52 statement ok 53 CREATE TABLE c3 ( 54 id STRING PRIMARY KEY REFERENCES b2 ON DELETE CASCADE 55 ) 56 57 statement ok 58 INSERT INTO a VALUES ('a-pk1'); 59 INSERT INTO b1 VALUES ('b1-pk1', 'a-pk1'), ('b1-pk2', 'a-pk1'); 60 INSERT INTO b2 VALUES ('b2-pk1', 'a-pk1'), ('b2-pk2', 'a-pk1'); 61 INSERT INTO c1 VALUES 62 ('c1-pk1-b1-pk1', 'b1-pk1') 63 ,('c1-pk2-b1-pk1', 'b1-pk1') 64 ,('c1-pk3-b1-pk2', 'b1-pk2') 65 ,('c1-pk4-b1-pk2', 'b1-pk2') 66 ; 67 INSERT INTO c2 VALUES 68 ('c2-pk1-b1-pk1', 'b1-pk1') 69 ,('c2-pk2-b1-pk1', 'b1-pk1') 70 ,('c2-pk3-b1-pk2', 'b1-pk2') 71 ,('c2-pk4-b1-pk2', 'b1-pk2') 72 ; 73 INSERT INTO c3 VALUES ('b2-pk1'), ('b2-pk2'); 74 75 statement ok 76 SET tracing = on,kv,results; DELETE FROM a WHERE id = 'a-pk1'; SET tracing = off 77 78 query T 79 SELECT message FROM [SHOW TRACE FOR SESSION] 80 WHERE message LIKE '%executing cascade %' OR message LIKE 'Del%' 81 ---- 82 Del /Table/53/1/"a-pk1"/0 83 executing cascade for constraint fk_delete_cascade_ref_a 84 Del /Table/54/2/"a-pk1"/"b1-pk1"/0 85 Del /Table/54/1/"b1-pk1"/0 86 Del /Table/54/2/"a-pk1"/"b1-pk2"/0 87 Del /Table/54/1/"b1-pk2"/0 88 executing cascade for constraint fk_delete_cascade_ref_a 89 Del /Table/55/2/"a-pk1"/"b2-pk1"/0 90 Del /Table/55/1/"b2-pk1"/0 91 Del /Table/55/2/"a-pk1"/"b2-pk2"/0 92 Del /Table/55/1/"b2-pk2"/0 93 executing cascade for constraint fk_delete_cascade_ref_b1 94 Del /Table/56/2/"b1-pk1"/"c1-pk1-b1-pk1"/0 95 Del /Table/56/1/"c1-pk1-b1-pk1"/0 96 Del /Table/56/2/"b1-pk1"/"c1-pk2-b1-pk1"/0 97 Del /Table/56/1/"c1-pk2-b1-pk1"/0 98 Del /Table/56/2/"b1-pk2"/"c1-pk3-b1-pk2"/0 99 Del /Table/56/1/"c1-pk3-b1-pk2"/0 100 Del /Table/56/2/"b1-pk2"/"c1-pk4-b1-pk2"/0 101 Del /Table/56/1/"c1-pk4-b1-pk2"/0 102 executing cascade for constraint fk_delete_cascade_ref_b1 103 Del /Table/57/2/"b1-pk1"/"c2-pk1-b1-pk1"/0 104 Del /Table/57/1/"c2-pk1-b1-pk1"/0 105 Del /Table/57/2/"b1-pk1"/"c2-pk2-b1-pk1"/0 106 Del /Table/57/1/"c2-pk2-b1-pk1"/0 107 Del /Table/57/2/"b1-pk2"/"c2-pk3-b1-pk2"/0 108 Del /Table/57/1/"c2-pk3-b1-pk2"/0 109 Del /Table/57/2/"b1-pk2"/"c2-pk4-b1-pk2"/0 110 Del /Table/57/1/"c2-pk4-b1-pk2"/0 111 executing cascade for constraint fk_id_ref_b2 112 Del /Table/58/1/"b2-pk1"/0 113 Del /Table/58/1/"b2-pk2"/0 114 115 # Clean up after the test. 116 statement ok 117 DROP TABLE c3, c2, c1, b2, b1, a; 118 119 subtest UpdateCascade_Basic 120 ### Basic Update Cascade 121 # a 122 # / \ 123 # b1 b2 124 # / \ \ 125 # c1 c2 c3 126 127 statement ok 128 CREATE TABLE a ( 129 id STRING PRIMARY KEY 130 ); 131 132 statement ok 133 CREATE TABLE b1 ( 134 id STRING PRIMARY KEY, 135 update_cascade STRING NOT NULL UNIQUE REFERENCES a ON UPDATE CASCADE, 136 FAMILY (id, update_cascade) 137 ); 138 139 statement ok 140 CREATE TABLE b2 ( 141 id STRING PRIMARY KEY, 142 update_cascade STRING NOT NULL UNIQUE REFERENCES a ON UPDATE CASCADE, 143 FAMILY (id, update_cascade) 144 ); 145 146 statement ok 147 CREATE TABLE c1 ( 148 id STRING PRIMARY KEY, 149 update_cascade STRING NOT NULL REFERENCES b1 (update_cascade) ON UPDATE CASCADE, 150 FAMILY (id, update_cascade) 151 ); 152 153 statement ok 154 CREATE TABLE c2 ( 155 id STRING PRIMARY KEY, 156 update_cascade STRING NOT NULL REFERENCES b1 (update_cascade) ON UPDATE CASCADE, 157 FAMILY (id, update_cascade) 158 ); 159 160 statement ok 161 CREATE TABLE c3 ( 162 id STRING PRIMARY KEY REFERENCES b2(update_cascade) ON UPDATE CASCADE 163 ); 164 165 statement ok 166 INSERT INTO a VALUES ('original'); 167 INSERT INTO b1 VALUES ('b1-pk1', 'original'); 168 INSERT INTO b2 VALUES ('b2-pk1', 'original'); 169 INSERT INTO c1 VALUES 170 ('c1-pk1', 'original') 171 ,('c1-pk2', 'original') 172 ,('c1-pk3', 'original') 173 ,('c1-pk4', 'original') 174 ; 175 INSERT INTO c2 VALUES 176 ('c2-pk1', 'original') 177 ,('c2-pk2', 'original') 178 ,('c2-pk3', 'original') 179 ,('c2-pk4', 'original') 180 ; 181 INSERT INTO c3 VALUES ('original'); 182 183 # ON UPDATE CASCADE 184 statement ok 185 UPDATE a SET id = 'updated' WHERE id = 'original'; 186 187 statement ok 188 SET tracing = on,kv,results; UPDATE a SET id = 'updated2' WHERE id = 'updated'; SET tracing = off 189 190 query T 191 SELECT message FROM [SHOW TRACE FOR SESSION] 192 WHERE message LIKE '%executing cascade %' OR message LIKE 'Del%' OR message LIKE 'CPut%' 193 ---- 194 Del /Table/59/1/"updated"/0 195 CPut /Table/59/1/"updated2"/0 -> /TUPLE/ 196 executing cascade for constraint fk_update_cascade_ref_a 197 Del /Table/60/2/"updated"/0 198 CPut /Table/60/2/"updated2"/0 -> /BYTES/0x1262312d706b310001 (expecting does not exist) 199 executing cascade for constraint fk_update_cascade_ref_a 200 Del /Table/61/2/"updated"/0 201 CPut /Table/61/2/"updated2"/0 -> /BYTES/0x1262322d706b310001 (expecting does not exist) 202 executing cascade for constraint fk_update_cascade_ref_b1 203 Del /Table/62/2/"updated"/"c1-pk1"/0 204 CPut /Table/62/2/"updated2"/"c1-pk1"/0 -> /BYTES/ (expecting does not exist) 205 Del /Table/62/2/"updated"/"c1-pk2"/0 206 CPut /Table/62/2/"updated2"/"c1-pk2"/0 -> /BYTES/ (expecting does not exist) 207 Del /Table/62/2/"updated"/"c1-pk3"/0 208 CPut /Table/62/2/"updated2"/"c1-pk3"/0 -> /BYTES/ (expecting does not exist) 209 Del /Table/62/2/"updated"/"c1-pk4"/0 210 CPut /Table/62/2/"updated2"/"c1-pk4"/0 -> /BYTES/ (expecting does not exist) 211 executing cascade for constraint fk_update_cascade_ref_b1 212 Del /Table/63/2/"updated"/"c2-pk1"/0 213 CPut /Table/63/2/"updated2"/"c2-pk1"/0 -> /BYTES/ (expecting does not exist) 214 Del /Table/63/2/"updated"/"c2-pk2"/0 215 CPut /Table/63/2/"updated2"/"c2-pk2"/0 -> /BYTES/ (expecting does not exist) 216 Del /Table/63/2/"updated"/"c2-pk3"/0 217 CPut /Table/63/2/"updated2"/"c2-pk3"/0 -> /BYTES/ (expecting does not exist) 218 Del /Table/63/2/"updated"/"c2-pk4"/0 219 CPut /Table/63/2/"updated2"/"c2-pk4"/0 -> /BYTES/ (expecting does not exist) 220 executing cascade for constraint fk_id_ref_b2 221 Del /Table/64/1/"updated"/0 222 CPut /Table/64/1/"updated2"/0 -> /TUPLE/ 223 224 # Clean up after the test. 225 statement ok 226 DROP TABLE c3, c2, c1, b2, b1, a; 227 228 subtest DeleteSetNull_Basic1 229 ### Basic Delete Set Null 230 # a 231 # // \\ 232 # / | | \ 233 # b1 b2 b3 b4 234 235 statement ok 236 CREATE TABLE a ( 237 id STRING PRIMARY KEY 238 ); 239 CREATE TABLE b1 ( 240 id STRING PRIMARY KEY, 241 delete_set_null STRING REFERENCES a ON DELETE SET NULL, 242 FAMILY (id, delete_set_null) 243 ); 244 CREATE TABLE b2 ( 245 id STRING PRIMARY KEY, 246 delete_set_null STRING REFERENCES a ON DELETE SET NULL, 247 FAMILY (id, delete_set_null) 248 ); 249 CREATE TABLE b3 ( 250 id STRING PRIMARY KEY, 251 delete_set_null STRING REFERENCES a ON DELETE SET NULL, 252 FAMILY (id, delete_set_null) 253 ); 254 CREATE TABLE b4 ( 255 id STRING PRIMARY KEY, 256 delete_set_null STRING REFERENCES a ON DELETE SET NULL, 257 FAMILY (id, delete_set_null) 258 ); 259 260 statement ok 261 INSERT INTO a VALUES ('delete_me'), ('untouched'); 262 INSERT INTO b1 VALUES ('b1-pk1', 'untouched'), ('b1-pk2', 'untouched'); 263 INSERT INTO b2 VALUES ('b2-pk1', 'untouched'), ('b2-pk2', 'delete_me'); 264 INSERT INTO b3 VALUES ('b3-pk1', 'delete_me'), ('b3-pk2', 'untouched'); 265 INSERT INTO b4 VALUES ('b4-pk1', 'delete_me'), ('b4-pk2', 'delete_me'); 266 267 # Ensure that show trace adds a cascade message for each of the tables that is 268 # cascaded into. 269 statement ok 270 SET tracing = on,kv,results; DELETE FROM a WHERE id = 'delete_me'; SET tracing = off 271 272 query T 273 SELECT message FROM [SHOW TRACE FOR SESSION] 274 WHERE message LIKE '%executing cascade %' OR message LIKE 'Del%' 275 ---- 276 Del /Table/65/1/"delete_me"/0 277 executing cascade for constraint fk_delete_set_null_ref_a 278 executing cascade for constraint fk_delete_set_null_ref_a 279 Del /Table/67/2/"delete_me"/"b2-pk2"/0 280 executing cascade for constraint fk_delete_set_null_ref_a 281 Del /Table/68/2/"delete_me"/"b3-pk1"/0 282 executing cascade for constraint fk_delete_set_null_ref_a 283 Del /Table/69/2/"delete_me"/"b4-pk1"/0 284 Del /Table/69/2/"delete_me"/"b4-pk2"/0 285 286 # Clean up after the test. 287 statement ok 288 DROP TABLE b4, b3, b2, b1, a; 289 290 subtest DeleteSetNull_Basic2 291 ### Basic Delete Set Null 292 # a 293 # / \ 294 # b1 b2 295 # / \ \ 296 # c1 c2 c3 297 298 statement ok 299 CREATE TABLE a ( 300 id STRING PRIMARY KEY 301 ); 302 CREATE TABLE b1 ( 303 id STRING PRIMARY KEY, 304 delete_cascade STRING NOT NULL REFERENCES a ON DELETE CASCADE, 305 FAMILY (id, delete_cascade) 306 ); 307 CREATE TABLE b2 ( 308 id STRING PRIMARY KEY, 309 delete_cascade STRING NOT NULL REFERENCES a ON DELETE CASCADE, 310 FAMILY (id, delete_cascade) 311 ); 312 CREATE TABLE c1 ( 313 id STRING PRIMARY KEY, 314 delete_set_null STRING REFERENCES b1 ON DELETE SET NULL, 315 FAMILY (id, delete_set_null) 316 ); 317 CREATE TABLE c2 ( 318 id STRING PRIMARY KEY, 319 delete_set_null STRING REFERENCES b1 ON DELETE SET NULL, 320 FAMILY (id, delete_set_null) 321 ); 322 CREATE TABLE c3 ( 323 id STRING PRIMARY KEY, 324 delete_set_null STRING REFERENCES b2 ON DELETE SET NULL, 325 FAMILY (id, delete_set_null) 326 ); 327 328 statement ok 329 INSERT INTO a VALUES ('a-pk1'); 330 INSERT INTO b1 VALUES ('b1-pk1', 'a-pk1'), ('b1-pk2', 'a-pk1'); 331 INSERT INTO b2 VALUES ('b2-pk1', 'a-pk1'), ('b2-pk2', 'a-pk1'); 332 INSERT INTO c1 VALUES 333 ('c1-pk1-b1-pk1', 'b1-pk1') 334 ,('c1-pk2-b1-pk1', 'b1-pk1') 335 ,('c1-pk3-b1-pk2', 'b1-pk2') 336 ,('c1-pk4-b1-pk2', 'b1-pk2') 337 ; 338 INSERT INTO c2 VALUES 339 ('c2-pk1-b1-pk1', 'b1-pk1') 340 ,('c2-pk2-b1-pk1', 'b1-pk1') 341 ,('c2-pk3-b1-pk2', 'b1-pk2') 342 ,('c2-pk4-b1-pk2', 'b1-pk2') 343 ; 344 INSERT INTO c3 VALUES 345 ('c3-pk1-b2-pk1', 'b2-pk1') 346 ,('c3-pk2-b2-pk1', 'b2-pk1') 347 ,('c3-pk3-b2-pk2', 'b2-pk2') 348 ,('c3-pk4-b2-pk2', 'b2-pk2') 349 ; 350 351 statement ok 352 SET tracing = on,kv,results; DELETE FROM a WHERE id = 'a-pk1'; SET tracing = off 353 354 query T 355 SELECT message FROM [SHOW TRACE FOR SESSION] 356 WHERE message LIKE '%executing cascade %' OR message LIKE 'Del%' 357 ---- 358 Del /Table/70/1/"a-pk1"/0 359 executing cascade for constraint fk_delete_cascade_ref_a 360 Del /Table/71/2/"a-pk1"/"b1-pk1"/0 361 Del /Table/71/1/"b1-pk1"/0 362 Del /Table/71/2/"a-pk1"/"b1-pk2"/0 363 Del /Table/71/1/"b1-pk2"/0 364 executing cascade for constraint fk_delete_cascade_ref_a 365 Del /Table/72/2/"a-pk1"/"b2-pk1"/0 366 Del /Table/72/1/"b2-pk1"/0 367 Del /Table/72/2/"a-pk1"/"b2-pk2"/0 368 Del /Table/72/1/"b2-pk2"/0 369 executing cascade for constraint fk_delete_set_null_ref_b1 370 Del /Table/73/2/"b1-pk1"/"c1-pk1-b1-pk1"/0 371 Del /Table/73/2/"b1-pk1"/"c1-pk2-b1-pk1"/0 372 Del /Table/73/2/"b1-pk2"/"c1-pk3-b1-pk2"/0 373 Del /Table/73/2/"b1-pk2"/"c1-pk4-b1-pk2"/0 374 executing cascade for constraint fk_delete_set_null_ref_b1 375 Del /Table/74/2/"b1-pk1"/"c2-pk1-b1-pk1"/0 376 Del /Table/74/2/"b1-pk1"/"c2-pk2-b1-pk1"/0 377 Del /Table/74/2/"b1-pk2"/"c2-pk3-b1-pk2"/0 378 Del /Table/74/2/"b1-pk2"/"c2-pk4-b1-pk2"/0 379 executing cascade for constraint fk_delete_set_null_ref_b2 380 Del /Table/75/2/"b2-pk1"/"c3-pk1-b2-pk1"/0 381 Del /Table/75/2/"b2-pk1"/"c3-pk2-b2-pk1"/0 382 Del /Table/75/2/"b2-pk2"/"c3-pk3-b2-pk2"/0 383 Del /Table/75/2/"b2-pk2"/"c3-pk4-b2-pk2"/0 384 385 # Clean up after the test. 386 statement ok 387 DROP TABLE c3, c2, c1, b2, b1, a; 388 389 subtest UpdateSetNull_Basic1 390 ### Basic Update Set Null 391 # a 392 # // \\ 393 # / | | \ 394 # b1 b2 b3 b4 395 396 statement ok 397 CREATE TABLE a ( 398 id STRING PRIMARY KEY 399 ); 400 CREATE TABLE b1 ( 401 id STRING PRIMARY KEY, 402 update_set_null STRING REFERENCES a ON UPDATE SET NULL, 403 FAMILY (id, update_set_null) 404 ); 405 CREATE TABLE b2 ( 406 id STRING PRIMARY KEY, 407 update_set_null STRING REFERENCES a ON UPDATE SET NULL, 408 FAMILY (id, update_set_null) 409 ); 410 CREATE TABLE b3 ( 411 id STRING PRIMARY KEY, 412 update_set_null STRING REFERENCES a ON UPDATE SET NULL, 413 FAMILY (id, update_set_null) 414 ); 415 CREATE TABLE b4 ( 416 id STRING PRIMARY KEY, 417 update_set_null STRING REFERENCES a ON UPDATE SET NULL, 418 FAMILY (id, update_set_null) 419 ); 420 421 statement ok 422 INSERT INTO a VALUES ('original'), ('untouched'); 423 INSERT INTO b1 VALUES ('b1-pk1', 'untouched'), ('b1-pk2', 'untouched'); 424 INSERT INTO b2 VALUES ('b2-pk1', 'untouched'), ('b2-pk2', 'original'); 425 INSERT INTO b3 VALUES ('b3-pk1', 'original'), ('b3-pk2', 'untouched'); 426 INSERT INTO b3 VALUES ('b4-pk1', 'original'), ('b4-pk2', 'original'); 427 428 # Ensure that show trace adds a cascade message for each of the tables that is 429 # cascaded into. 430 statement ok 431 SET tracing = on,kv,results; UPDATE a SET id = 'updated' WHERE id = 'original'; SET tracing = off 432 433 query T 434 SELECT message FROM [SHOW TRACE FOR SESSION] 435 WHERE message LIKE '%executing cascade %' OR message LIKE 'Del%' OR message LIKE 'CPut%' 436 ---- 437 Del /Table/76/1/"original"/0 438 CPut /Table/76/1/"updated"/0 -> /TUPLE/ 439 executing cascade for constraint fk_update_set_null_ref_a 440 executing cascade for constraint fk_update_set_null_ref_a 441 Del /Table/78/2/"original"/"b2-pk2"/0 442 CPut /Table/78/2/NULL/"b2-pk2"/0 -> /BYTES/ (expecting does not exist) 443 executing cascade for constraint fk_update_set_null_ref_a 444 Del /Table/79/2/"original"/"b3-pk1"/0 445 CPut /Table/79/2/NULL/"b3-pk1"/0 -> /BYTES/ (expecting does not exist) 446 Del /Table/79/2/"original"/"b4-pk1"/0 447 CPut /Table/79/2/NULL/"b4-pk1"/0 -> /BYTES/ (expecting does not exist) 448 Del /Table/79/2/"original"/"b4-pk2"/0 449 CPut /Table/79/2/NULL/"b4-pk2"/0 -> /BYTES/ (expecting does not exist) 450 executing cascade for constraint fk_update_set_null_ref_a 451 452 # Clean up after the test. 453 statement ok 454 DROP TABLE b4, b3, b2, b1, a; 455 456 subtest UpdateSetNull_Basic2 457 ### Basic Update Set Null 458 # a 459 # / \ 460 # b1 b2 461 # / \ \ 462 # c1 c2 c3 463 464 statement ok 465 CREATE TABLE a ( 466 id STRING PRIMARY KEY 467 ); 468 CREATE TABLE b1 ( 469 id STRING PRIMARY KEY, 470 update_cascade STRING UNIQUE NOT NULL REFERENCES a ON UPDATE CASCADE, 471 FAMILY (id, update_cascade) 472 ); 473 CREATE TABLE b2 ( 474 id STRING PRIMARY KEY, 475 update_cascade STRING UNIQUE NOT NULL REFERENCES a ON UPDATE CASCADE, 476 FAMILY (id, update_cascade) 477 ); 478 CREATE TABLE c1 ( 479 id STRING PRIMARY KEY, 480 update_set_null STRING REFERENCES b1(update_cascade) ON UPDATE SET NULL, 481 FAMILY (id, update_set_null) 482 ); 483 CREATE TABLE c2 ( 484 id STRING PRIMARY KEY, 485 update_set_null STRING REFERENCES b1(update_cascade) ON UPDATE SET NULL, 486 FAMILY (id, update_set_null) 487 ); 488 CREATE TABLE c3 ( 489 id STRING PRIMARY KEY, 490 update_set_null STRING REFERENCES b2(update_cascade) ON UPDATE SET NULL, 491 FAMILY (id, update_set_null) 492 ); 493 494 statement ok 495 INSERT INTO a VALUES ('original'), ('untouched'); 496 INSERT INTO b1 VALUES ('b1-pk1', 'original'), ('b1-pk2', 'untouched'); 497 INSERT INTO b2 VALUES ('b2-pk1', 'original'), ('b2-pk2', 'untouched'); 498 INSERT INTO c1 VALUES 499 ('c1-pk1-b1-pk1', 'original') 500 ,('c1-pk2-b1-pk1', 'original') 501 ,('c1-pk3-b1-pk2', 'untouched') 502 ,('c1-pk4-b1-pk2', 'untouched') 503 ; 504 INSERT INTO c2 VALUES 505 ('c2-pk1-b1-pk1', 'original') 506 ,('c2-pk2-b1-pk1', 'original') 507 ,('c2-pk3-b1-pk2', 'untouched') 508 ,('c2-pk4-b1-pk2', 'untouched') 509 ; 510 INSERT INTO c3 VALUES 511 ('c3-pk1-b2-pk1', 'original') 512 ,('c3-pk2-b2-pk1', 'original') 513 ,('c3-pk3-b2-pk2', 'untouched') 514 ,('c3-pk4-b2-pk2', 'untouched') 515 ; 516 517 # Ensure that show trace adds a cascade message for each of the tables that is 518 # cascaded into. 519 statement ok 520 SET tracing = on,kv,results; UPDATE a SET id = 'updated' WHERE id = 'original'; SET tracing = off 521 522 query T 523 SELECT message FROM [SHOW TRACE FOR SESSION] 524 WHERE message LIKE '%executing cascade %' OR message LIKE 'Del%' OR message LIKE 'CPut%' 525 ---- 526 Del /Table/81/1/"original"/0 527 CPut /Table/81/1/"updated"/0 -> /TUPLE/ 528 executing cascade for constraint fk_update_cascade_ref_a 529 Del /Table/82/2/"original"/0 530 CPut /Table/82/2/"updated"/0 -> /BYTES/0x1262312d706b310001 (expecting does not exist) 531 executing cascade for constraint fk_update_cascade_ref_a 532 Del /Table/83/2/"original"/0 533 CPut /Table/83/2/"updated"/0 -> /BYTES/0x1262322d706b310001 (expecting does not exist) 534 executing cascade for constraint fk_update_set_null_ref_b1 535 Del /Table/84/2/"original"/"c1-pk1-b1-pk1"/0 536 CPut /Table/84/2/NULL/"c1-pk1-b1-pk1"/0 -> /BYTES/ (expecting does not exist) 537 Del /Table/84/2/"original"/"c1-pk2-b1-pk1"/0 538 CPut /Table/84/2/NULL/"c1-pk2-b1-pk1"/0 -> /BYTES/ (expecting does not exist) 539 executing cascade for constraint fk_update_set_null_ref_b1 540 Del /Table/85/2/"original"/"c2-pk1-b1-pk1"/0 541 CPut /Table/85/2/NULL/"c2-pk1-b1-pk1"/0 -> /BYTES/ (expecting does not exist) 542 Del /Table/85/2/"original"/"c2-pk2-b1-pk1"/0 543 CPut /Table/85/2/NULL/"c2-pk2-b1-pk1"/0 -> /BYTES/ (expecting does not exist) 544 executing cascade for constraint fk_update_set_null_ref_b2 545 Del /Table/86/2/"original"/"c3-pk1-b2-pk1"/0 546 CPut /Table/86/2/NULL/"c3-pk1-b2-pk1"/0 -> /BYTES/ (expecting does not exist) 547 Del /Table/86/2/"original"/"c3-pk2-b2-pk1"/0 548 CPut /Table/86/2/NULL/"c3-pk2-b2-pk1"/0 -> /BYTES/ (expecting does not exist) 549 550 # Clean up after the test. 551 statement ok 552 DROP TABLE c3, c2, c1, b2, b1, a; 553 554 ############## 555 556 subtest DeleteSetDefault_Basic1 557 ### Basic Delete Set Default 558 # a 559 # // \\ 560 # / | | \ 561 # b1 b2 b3 b4 562 563 statement ok 564 CREATE TABLE a ( 565 id STRING PRIMARY KEY 566 ); 567 CREATE TABLE b1 ( 568 id STRING PRIMARY KEY, 569 delete_set_default STRING DEFAULT 'b1-default' REFERENCES a ON DELETE SET DEFAULT, 570 FAMILY (id, delete_set_default) 571 ); 572 CREATE TABLE b2 ( 573 id STRING PRIMARY KEY, 574 delete_set_default STRING DEFAULT 'b2-default' REFERENCES a ON DELETE SET DEFAULT, 575 FAMILY (id, delete_set_default) 576 ); 577 CREATE TABLE b3 ( 578 id STRING PRIMARY KEY, 579 delete_set_default STRING DEFAULT 'b3-default' REFERENCES a ON DELETE SET DEFAULT, 580 FAMILY (id, delete_set_default) 581 ); 582 CREATE TABLE b4 ( 583 id STRING PRIMARY KEY, 584 delete_set_default STRING DEFAULT 'b4-default' REFERENCES a ON DELETE SET DEFAULT, 585 FAMILY (id, delete_set_default) 586 ); 587 588 statement ok 589 INSERT INTO a VALUES ('delete_me'), ('untouched'), ('b2-default'), ('b3-default'), ('b4-default'); 590 INSERT INTO b1 VALUES ('b1-pk1', 'untouched'), ('b1-pk2', 'untouched'); 591 INSERT INTO b2 VALUES ('b2-pk1', 'untouched'), ('b2-pk2', 'delete_me'); 592 INSERT INTO b3 VALUES ('b3-pk1', 'delete_me'), ('b3-pk2', 'untouched'); 593 INSERT INTO b4 VALUES ('b4-pk1', 'delete_me'), ('b4-pk2', 'delete_me'); 594 595 # Ensure that show trace adds a cascade message for each of the tables that is 596 # cascaded into. 597 statement ok 598 SET tracing = on,kv,results; DELETE FROM a WHERE id = 'delete_me'; SET tracing = off 599 600 query T 601 SELECT message FROM [SHOW TRACE FOR SESSION] 602 WHERE message LIKE '%executing cascade %' OR message LIKE 'Del%' 603 ---- 604 Del /Table/87/1/"delete_me"/0 605 executing cascade for constraint fk_delete_set_default_ref_a 606 executing cascade for constraint fk_delete_set_default_ref_a 607 Del /Table/89/2/"delete_me"/"b2-pk2"/0 608 executing cascade for constraint fk_delete_set_default_ref_a 609 Del /Table/90/2/"delete_me"/"b3-pk1"/0 610 executing cascade for constraint fk_delete_set_default_ref_a 611 Del /Table/91/2/"delete_me"/"b4-pk1"/0 612 Del /Table/91/2/"delete_me"/"b4-pk2"/0 613 614 # Clean up after the test. 615 statement ok 616 DROP TABLE b4, b3, b2, b1, a; 617 618 subtest DeleteSetDefault_Basic2 619 ### Basic Delete Set Null via an ON DELETE CASCADE 620 # a 621 # / \ 622 # b1 b2 623 # / \ \ 624 # c1 c2 c3 625 626 statement ok 627 CREATE TABLE a ( 628 id STRING PRIMARY KEY 629 ); 630 CREATE TABLE b1 ( 631 id STRING PRIMARY KEY, 632 delete_cascade STRING NOT NULL REFERENCES a ON DELETE CASCADE, 633 FAMILY (id, delete_cascade) 634 ); 635 CREATE TABLE b2 ( 636 id STRING PRIMARY KEY, 637 delete_cascade STRING NOT NULL REFERENCES a ON DELETE CASCADE, 638 FAMILY (id, delete_cascade) 639 ); 640 CREATE TABLE c1 ( 641 id STRING PRIMARY KEY, 642 delete_set_default STRING DEFAULT 'b1-default' REFERENCES b1 ON DELETE SET DEFAULT, 643 FAMILY (id, delete_set_default) 644 ); 645 CREATE TABLE c2 ( 646 id STRING PRIMARY KEY, 647 delete_set_default STRING DEFAULT 'b1-default' REFERENCES b1 ON DELETE SET DEFAULT, 648 FAMILY (id, delete_set_default) 649 ); 650 CREATE TABLE c3 ( 651 id STRING PRIMARY KEY, 652 delete_set_default STRING DEFAULT 'b2-default' REFERENCES b2 ON DELETE SET DEFAULT, 653 FAMILY (id, delete_set_default) 654 ); 655 656 statement ok 657 INSERT INTO a VALUES ('a-pk1'), ('a-default'); 658 INSERT INTO b1 VALUES ('b1-pk1', 'a-pk1'), ('b1-pk2', 'a-pk1'), ('b1-default', 'a-default'); 659 INSERT INTO b2 VALUES ('b2-pk1', 'a-pk1'), ('b2-pk2', 'a-pk1'), ('b2-default', 'a-default'); 660 INSERT INTO c1 VALUES 661 ('c1-pk1-b1-pk1', 'b1-pk1') 662 ,('c1-pk2-b1-pk1', 'b1-pk1') 663 ,('c1-pk3-b1-pk2', 'b1-pk2') 664 ,('c1-pk4-b1-pk2', 'b1-pk2') 665 ; 666 INSERT INTO c2 VALUES 667 ('c2-pk1-b1-pk1', 'b1-pk1') 668 ,('c2-pk2-b1-pk1', 'b1-pk1') 669 ,('c2-pk3-b1-pk2', 'b1-pk2') 670 ,('c2-pk4-b1-pk2', 'b1-pk2') 671 ; 672 INSERT INTO c3 VALUES 673 ('c3-pk1-b2-pk1', 'b2-pk1') 674 ,('c3-pk2-b2-pk1', 'b2-pk1') 675 ,('c3-pk3-b2-pk2', 'b2-pk2') 676 ,('c3-pk4-b2-pk2', 'b2-pk2') 677 ; 678 679 statement ok 680 SET tracing = on,kv,results; DELETE FROM a WHERE id = 'a-pk1'; SET tracing = off 681 682 query T 683 SELECT message FROM [SHOW TRACE FOR SESSION] 684 WHERE message LIKE '%executing cascade %' OR message LIKE 'Del%' 685 ---- 686 Del /Table/92/1/"a-pk1"/0 687 executing cascade for constraint fk_delete_cascade_ref_a 688 Del /Table/93/2/"a-pk1"/"b1-pk1"/0 689 Del /Table/93/1/"b1-pk1"/0 690 Del /Table/93/2/"a-pk1"/"b1-pk2"/0 691 Del /Table/93/1/"b1-pk2"/0 692 executing cascade for constraint fk_delete_cascade_ref_a 693 Del /Table/94/2/"a-pk1"/"b2-pk1"/0 694 Del /Table/94/1/"b2-pk1"/0 695 Del /Table/94/2/"a-pk1"/"b2-pk2"/0 696 Del /Table/94/1/"b2-pk2"/0 697 executing cascade for constraint fk_delete_set_default_ref_b1 698 Del /Table/95/2/"b1-pk1"/"c1-pk1-b1-pk1"/0 699 Del /Table/95/2/"b1-pk1"/"c1-pk2-b1-pk1"/0 700 Del /Table/95/2/"b1-pk2"/"c1-pk3-b1-pk2"/0 701 Del /Table/95/2/"b1-pk2"/"c1-pk4-b1-pk2"/0 702 executing cascade for constraint fk_delete_set_default_ref_b1 703 Del /Table/96/2/"b1-pk1"/"c2-pk1-b1-pk1"/0 704 Del /Table/96/2/"b1-pk1"/"c2-pk2-b1-pk1"/0 705 Del /Table/96/2/"b1-pk2"/"c2-pk3-b1-pk2"/0 706 Del /Table/96/2/"b1-pk2"/"c2-pk4-b1-pk2"/0 707 executing cascade for constraint fk_delete_set_default_ref_b2 708 Del /Table/97/2/"b2-pk1"/"c3-pk1-b2-pk1"/0 709 Del /Table/97/2/"b2-pk1"/"c3-pk2-b2-pk1"/0 710 Del /Table/97/2/"b2-pk2"/"c3-pk3-b2-pk2"/0 711 Del /Table/97/2/"b2-pk2"/"c3-pk4-b2-pk2"/0 712 713 # Clean up after the test. 714 statement ok 715 DROP TABLE c3, c2, c1, b2, b1, a; 716 717 subtest UpdateSetDefault_Basic1 718 ### Basic Update Set Default 719 # a 720 # // \\ 721 # / | | \ 722 # b1 b2 b3 b4 723 724 statement ok 725 CREATE TABLE a ( 726 id STRING PRIMARY KEY 727 ); 728 CREATE TABLE b1 ( 729 id STRING PRIMARY KEY, 730 update_set_null STRING DEFAULT 'b1-default' REFERENCES a ON UPDATE SET DEFAULT, 731 FAMILY (id, update_set_null) 732 ); 733 CREATE TABLE b2 ( 734 id STRING PRIMARY KEY, 735 update_set_null STRING DEFAULT 'b2-default' REFERENCES a ON UPDATE SET DEFAULT, 736 FAMILY (id, update_set_null) 737 ); 738 CREATE TABLE b3 ( 739 id STRING PRIMARY KEY, 740 update_set_null STRING DEFAULT 'b3-default' REFERENCES a ON UPDATE SET DEFAULT, 741 FAMILY (id, update_set_null) 742 ); 743 CREATE TABLE b4 ( 744 id STRING PRIMARY KEY, 745 update_set_null STRING DEFAULT 'b4-default' REFERENCES a ON UPDATE SET DEFAULT, 746 FAMILY (id, update_set_null) 747 ); 748 749 statement ok 750 INSERT INTO a VALUES ('original'), ('untouched'), ('b1-default'), ('b2-default'), ('b3-default'), ('b4-default'); 751 INSERT INTO b1 VALUES ('b1-pk1', 'untouched'), ('b1-pk2', 'untouched'); 752 INSERT INTO b2 VALUES ('b2-pk1', 'untouched'), ('b2-pk2', 'original'); 753 INSERT INTO b3 VALUES ('b3-pk1', 'original'), ('b3-pk2', 'untouched'); 754 INSERT INTO b3 VALUES ('b4-pk1', 'original'), ('b4-pk2', 'original'); 755 756 # Ensure that show trace adds a cascade message for each of the tables that is 757 # cascaded into. 758 statement ok 759 SET tracing = on,kv,results; UPDATE a SET id = 'updated' WHERE id = 'original'; SET tracing = off 760 761 query T 762 SELECT message FROM [SHOW TRACE FOR SESSION] 763 WHERE message LIKE '%executing cascade %' OR message LIKE 'Del%' OR message LIKE 'CPut%' 764 ---- 765 Del /Table/98/1/"original"/0 766 CPut /Table/98/1/"updated"/0 -> /TUPLE/ 767 executing cascade for constraint fk_update_set_null_ref_a 768 executing cascade for constraint fk_update_set_null_ref_a 769 Del /Table/100/2/"original"/"b2-pk2"/0 770 CPut /Table/100/2/"b2-default"/"b2-pk2"/0 -> /BYTES/ (expecting does not exist) 771 executing cascade for constraint fk_update_set_null_ref_a 772 Del /Table/101/2/"original"/"b3-pk1"/0 773 CPut /Table/101/2/"b3-default"/"b3-pk1"/0 -> /BYTES/ (expecting does not exist) 774 Del /Table/101/2/"original"/"b4-pk1"/0 775 CPut /Table/101/2/"b3-default"/"b4-pk1"/0 -> /BYTES/ (expecting does not exist) 776 Del /Table/101/2/"original"/"b4-pk2"/0 777 CPut /Table/101/2/"b3-default"/"b4-pk2"/0 -> /BYTES/ (expecting does not exist) 778 executing cascade for constraint fk_update_set_null_ref_a 779 780 # Clean up after the test. 781 statement ok 782 DROP TABLE b4, b3, b2, b1, a; 783 784 subtest UpdateSetDefault_Basic2 785 ### Basic UPDATE SET DEFAULT via an UPDATE CASCADE 786 # a 787 # / \ 788 # b1 b2 789 # / \ \ 790 # c1 c2 c3 791 792 statement ok 793 CREATE TABLE a ( 794 id STRING PRIMARY KEY 795 ); 796 CREATE TABLE b1 ( 797 id STRING PRIMARY KEY, 798 update_cascade STRING UNIQUE NOT NULL REFERENCES a ON UPDATE CASCADE, 799 FAMILY (id, update_cascade) 800 ); 801 CREATE TABLE b2 ( 802 id STRING PRIMARY KEY, 803 update_cascade STRING UNIQUE NOT NULL REFERENCES a ON UPDATE CASCADE, 804 FAMILY (id, update_cascade) 805 ); 806 CREATE TABLE c1 ( 807 id STRING PRIMARY KEY, 808 update_set_null STRING DEFAULT 'b1-default' REFERENCES b1(update_cascade) ON UPDATE SET DEFAULT, 809 FAMILY (id, update_set_null) 810 ); 811 CREATE TABLE c2 ( 812 id STRING PRIMARY KEY, 813 update_set_null STRING DEFAULT 'b1-default' REFERENCES b1(update_cascade) ON UPDATE SET DEFAULT, 814 FAMILY (id, update_set_null) 815 ); 816 CREATE TABLE c3 ( 817 id STRING PRIMARY KEY, 818 update_set_null STRING DEFAULT 'b2-default' REFERENCES b2(update_cascade) ON UPDATE SET DEFAULT, 819 FAMILY (id, update_set_null) 820 ); 821 822 statement ok 823 INSERT INTO a VALUES ('original'), ('untouched'), ('b1-default'), ('b2-default'); 824 INSERT INTO b1 VALUES ('b1-pk1', 'original'), ('b1-pk2', 'untouched'), ('b1-default', 'b1-default'); 825 INSERT INTO b2 VALUES ('b2-pk1', 'original'), ('b2-pk2', 'untouched'), ('b2-default', 'b2-default'); 826 INSERT INTO c1 VALUES 827 ('c1-pk1-b1-pk1', 'original') 828 ,('c1-pk2-b1-pk1', 'original') 829 ,('c1-pk3-b1-pk2', 'untouched') 830 ,('c1-pk4-b1-pk2', 'untouched') 831 ; 832 INSERT INTO c2 VALUES 833 ('c2-pk1-b1-pk1', 'original') 834 ,('c2-pk2-b1-pk1', 'original') 835 ,('c2-pk3-b1-pk2', 'untouched') 836 ,('c2-pk4-b1-pk2', 'untouched') 837 ; 838 INSERT INTO c3 VALUES 839 ('c3-pk1-b2-pk1', 'original') 840 ,('c3-pk2-b2-pk1', 'original') 841 ,('c3-pk3-b2-pk2', 'untouched') 842 ,('c3-pk4-b2-pk2', 'untouched') 843 ; 844 845 # Ensure that show trace adds a cascade message for each of the tables that is 846 # cascaded into. 847 statement ok 848 SET tracing = on,kv,results; UPDATE a SET id = 'updated' WHERE id = 'original'; SET tracing = off 849 850 query T 851 SELECT message FROM [SHOW TRACE FOR SESSION] 852 WHERE message LIKE '%executing cascade %' OR message LIKE 'Del%' OR message LIKE 'CPut%' 853 ---- 854 Del /Table/103/1/"original"/0 855 CPut /Table/103/1/"updated"/0 -> /TUPLE/ 856 executing cascade for constraint fk_update_cascade_ref_a 857 Del /Table/104/2/"original"/0 858 CPut /Table/104/2/"updated"/0 -> /BYTES/0x1262312d706b310001 (expecting does not exist) 859 executing cascade for constraint fk_update_cascade_ref_a 860 Del /Table/105/2/"original"/0 861 CPut /Table/105/2/"updated"/0 -> /BYTES/0x1262322d706b310001 (expecting does not exist) 862 executing cascade for constraint fk_update_set_null_ref_b1 863 Del /Table/106/2/"original"/"c1-pk1-b1-pk1"/0 864 CPut /Table/106/2/"b1-default"/"c1-pk1-b1-pk1"/0 -> /BYTES/ (expecting does not exist) 865 Del /Table/106/2/"original"/"c1-pk2-b1-pk1"/0 866 CPut /Table/106/2/"b1-default"/"c1-pk2-b1-pk1"/0 -> /BYTES/ (expecting does not exist) 867 executing cascade for constraint fk_update_set_null_ref_b1 868 Del /Table/107/2/"original"/"c2-pk1-b1-pk1"/0 869 CPut /Table/107/2/"b1-default"/"c2-pk1-b1-pk1"/0 -> /BYTES/ (expecting does not exist) 870 Del /Table/107/2/"original"/"c2-pk2-b1-pk1"/0 871 CPut /Table/107/2/"b1-default"/"c2-pk2-b1-pk1"/0 -> /BYTES/ (expecting does not exist) 872 executing cascade for constraint fk_update_set_null_ref_b2 873 Del /Table/108/2/"original"/"c3-pk1-b2-pk1"/0 874 CPut /Table/108/2/"b2-default"/"c3-pk1-b2-pk1"/0 -> /BYTES/ (expecting does not exist) 875 Del /Table/108/2/"original"/"c3-pk2-b2-pk1"/0 876 CPut /Table/108/2/"b2-default"/"c3-pk2-b2-pk1"/0 -> /BYTES/ (expecting does not exist) 877 878 # Clean up after the test. 879 statement ok 880 DROP TABLE c3, c2, c1, b2, b1, a; 881 882 # Regression for #46094. 883 884 statement ok 885 CREATE TABLE parent (x INT PRIMARY KEY); 886 CREATE TABLE child1 ( 887 id INT PRIMARY KEY, 888 x INT REFERENCES parent (x) ON DELETE CASCADE, 889 FAMILY (id, x) 890 ); 891 CREATE TABLE child2 ( 892 id INT PRIMARY KEY, 893 x INT REFERENCES parent (x) ON DELETE SET NULL, 894 FAMILY (id, x) 895 ); 896 INSERT INTO parent VALUES (1), (2); 897 INSERT INTO child1 VALUES (1, 1), (2, 1); 898 INSERT INTO child2 VALUES (1, 1), (2, 1) 899 900 # Here we ensure that after the cascaded deletes we don't need 901 # to perform additional and unneeded FKScan operations after 902 # cascade deleting or setting null to referencing rows. 903 query T kvtrace(Del,FKScan) 904 DELETE FROM parent WHERE x = 1 905 ---- 906 Del /Table/109/1/1/0 907 Del /Table/110/2/1/1/0 908 Del /Table/110/1/1/0 909 Del /Table/110/2/1/2/0 910 Del /Table/110/1/2/0 911 Del /Table/111/2/1/1/0 912 Del /Table/111/2/1/2/0