github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/optbuilder/testdata/fk-checks-update (about) 1 exec-ddl 2 CREATE TABLE parent (x INT, p INT PRIMARY KEY, other INT UNIQUE) 3 ---- 4 5 exec-ddl 6 CREATE TABLE child (c INT PRIMARY KEY, p INT NOT NULL REFERENCES parent(p)) 7 ---- 8 9 build 10 UPDATE child SET p = 4 11 ---- 12 update child 13 ├── columns: <none> 14 ├── fetch columns: c:3 child.p:4 15 ├── update-mapping: 16 │ └── p_new:5 => child.p:2 17 ├── input binding: &1 18 ├── project 19 │ ├── columns: p_new:5!null c:3!null child.p:4!null 20 │ ├── scan child 21 │ │ └── columns: c:3!null child.p:4!null 22 │ └── projections 23 │ └── 4 [as=p_new:5] 24 └── f-k-checks 25 └── f-k-checks-item: child(p) -> parent(p) 26 └── anti-join (hash) 27 ├── columns: p_new:6!null 28 ├── with-scan &1 29 │ ├── columns: p_new:6!null 30 │ └── mapping: 31 │ └── p_new:5 => p_new:6 32 ├── scan parent 33 │ └── columns: parent.p:8!null 34 └── filters 35 └── p_new:6 = parent.p:8 36 37 build 38 UPDATE parent SET p = p+1 39 ---- 40 update parent 41 ├── columns: <none> 42 ├── fetch columns: x:4 parent.p:5 other:6 43 ├── update-mapping: 44 │ └── p_new:7 => parent.p:2 45 ├── input binding: &1 46 ├── project 47 │ ├── columns: p_new:7!null x:4 parent.p:5!null other:6 48 │ ├── scan parent 49 │ │ └── columns: x:4 parent.p:5!null other:6 50 │ └── projections 51 │ └── parent.p:5 + 1 [as=p_new:7] 52 └── f-k-checks 53 └── f-k-checks-item: child(p) -> parent(p) 54 └── semi-join (hash) 55 ├── columns: p:8!null 56 ├── except 57 │ ├── columns: p:8!null 58 │ ├── left columns: p:8!null 59 │ ├── right columns: p_new:9 60 │ ├── with-scan &1 61 │ │ ├── columns: p:8!null 62 │ │ └── mapping: 63 │ │ └── parent.p:5 => p:8 64 │ └── with-scan &1 65 │ ├── columns: p_new:9!null 66 │ └── mapping: 67 │ └── p_new:7 => p_new:9 68 ├── scan child 69 │ └── columns: child.p:11!null 70 └── filters 71 └── p:8 = child.p:11 72 73 exec-ddl 74 CREATE TABLE grandchild (g INT PRIMARY KEY, c INT NOT NULL REFERENCES child(c)) 75 ---- 76 77 build 78 UPDATE child SET c = 4 79 ---- 80 update child 81 ├── columns: <none> 82 ├── fetch columns: child.c:3 p:4 83 ├── update-mapping: 84 │ └── c_new:5 => child.c:1 85 ├── input binding: &1 86 ├── project 87 │ ├── columns: c_new:5!null child.c:3!null p:4!null 88 │ ├── scan child 89 │ │ └── columns: child.c:3!null p:4!null 90 │ └── projections 91 │ └── 4 [as=c_new:5] 92 └── f-k-checks 93 └── f-k-checks-item: grandchild(c) -> child(c) 94 └── semi-join (hash) 95 ├── columns: c:6!null 96 ├── except 97 │ ├── columns: c:6!null 98 │ ├── left columns: c:6!null 99 │ ├── right columns: c_new:7 100 │ ├── with-scan &1 101 │ │ ├── columns: c:6!null 102 │ │ └── mapping: 103 │ │ └── child.c:3 => c:6 104 │ └── with-scan &1 105 │ ├── columns: c_new:7!null 106 │ └── mapping: 107 │ └── c_new:5 => c_new:7 108 ├── scan grandchild 109 │ └── columns: grandchild.c:9!null 110 └── filters 111 └── c:6 = grandchild.c:9 112 113 # This update shouldn't emit checks for c, since it's unchanged. 114 build 115 UPDATE child SET p = 4 116 ---- 117 update child 118 ├── columns: <none> 119 ├── fetch columns: c:3 child.p:4 120 ├── update-mapping: 121 │ └── p_new:5 => child.p:2 122 ├── input binding: &1 123 ├── project 124 │ ├── columns: p_new:5!null c:3!null child.p:4!null 125 │ ├── scan child 126 │ │ └── columns: c:3!null child.p:4!null 127 │ └── projections 128 │ └── 4 [as=p_new:5] 129 └── f-k-checks 130 └── f-k-checks-item: child(p) -> parent(p) 131 └── anti-join (hash) 132 ├── columns: p_new:6!null 133 ├── with-scan &1 134 │ ├── columns: p_new:6!null 135 │ └── mapping: 136 │ └── p_new:5 => p_new:6 137 ├── scan parent 138 │ └── columns: parent.p:8!null 139 └── filters 140 └── p_new:6 = parent.p:8 141 142 build 143 UPDATE child SET p = p 144 ---- 145 update child 146 ├── columns: <none> 147 ├── fetch columns: c:3 child.p:4 148 ├── update-mapping: 149 │ └── child.p:4 => child.p:2 150 ├── input binding: &1 151 ├── scan child 152 │ └── columns: c:3!null child.p:4!null 153 └── f-k-checks 154 └── f-k-checks-item: child(p) -> parent(p) 155 └── anti-join (hash) 156 ├── columns: p:5!null 157 ├── with-scan &1 158 │ ├── columns: p:5!null 159 │ └── mapping: 160 │ └── child.p:4 => p:5 161 ├── scan parent 162 │ └── columns: parent.p:7!null 163 └── filters 164 └── p:5 = parent.p:7 165 166 build 167 UPDATE child SET p = p+1, c = c+1 168 ---- 169 update child 170 ├── columns: <none> 171 ├── fetch columns: child.c:3 child.p:4 172 ├── update-mapping: 173 │ ├── c_new:6 => child.c:1 174 │ └── p_new:5 => child.p:2 175 ├── input binding: &1 176 ├── project 177 │ ├── columns: p_new:5!null c_new:6!null child.c:3!null child.p:4!null 178 │ ├── scan child 179 │ │ └── columns: child.c:3!null child.p:4!null 180 │ └── projections 181 │ ├── child.p:4 + 1 [as=p_new:5] 182 │ └── child.c:3 + 1 [as=c_new:6] 183 └── f-k-checks 184 ├── f-k-checks-item: child(p) -> parent(p) 185 │ └── anti-join (hash) 186 │ ├── columns: p_new:7!null 187 │ ├── with-scan &1 188 │ │ ├── columns: p_new:7!null 189 │ │ └── mapping: 190 │ │ └── p_new:5 => p_new:7 191 │ ├── scan parent 192 │ │ └── columns: parent.p:9!null 193 │ └── filters 194 │ └── p_new:7 = parent.p:9 195 └── f-k-checks-item: grandchild(c) -> child(c) 196 └── semi-join (hash) 197 ├── columns: c:11!null 198 ├── except 199 │ ├── columns: c:11!null 200 │ ├── left columns: c:11!null 201 │ ├── right columns: c_new:12 202 │ ├── with-scan &1 203 │ │ ├── columns: c:11!null 204 │ │ └── mapping: 205 │ │ └── child.c:3 => c:11 206 │ └── with-scan &1 207 │ ├── columns: c_new:12!null 208 │ └── mapping: 209 │ └── c_new:6 => c_new:12 210 ├── scan grandchild 211 │ └── columns: grandchild.c:14!null 212 └── filters 213 └── c:11 = grandchild.c:14 214 215 exec-ddl 216 CREATE TABLE child_nullable (c INT PRIMARY KEY, p INT REFERENCES parent(p)) 217 ---- 218 219 # We don't need the FK check in this case because we are only setting NULL 220 # values. 221 build 222 UPDATE child_nullable SET p = NULL 223 ---- 224 update child_nullable 225 ├── columns: <none> 226 ├── fetch columns: c:3 p:4 227 ├── update-mapping: 228 │ └── p_new:5 => p:2 229 └── project 230 ├── columns: p_new:5 c:3!null p:4 231 ├── scan child_nullable 232 │ └── columns: c:3!null p:4 233 └── projections 234 └── NULL::INT8 [as=p_new:5] 235 236 # Multiple grandchild tables 237 exec-ddl 238 CREATE TABLE grandchild2 (g INT PRIMARY KEY, c INT NOT NULL REFERENCES child(c)) 239 ---- 240 241 build 242 UPDATE child SET p = 4 243 ---- 244 update child 245 ├── columns: <none> 246 ├── fetch columns: c:3 child.p:4 247 ├── update-mapping: 248 │ └── p_new:5 => child.p:2 249 ├── input binding: &1 250 ├── project 251 │ ├── columns: p_new:5!null c:3!null child.p:4!null 252 │ ├── scan child 253 │ │ └── columns: c:3!null child.p:4!null 254 │ └── projections 255 │ └── 4 [as=p_new:5] 256 └── f-k-checks 257 └── f-k-checks-item: child(p) -> parent(p) 258 └── anti-join (hash) 259 ├── columns: p_new:6!null 260 ├── with-scan &1 261 │ ├── columns: p_new:6!null 262 │ └── mapping: 263 │ └── p_new:5 => p_new:6 264 ├── scan parent 265 │ └── columns: parent.p:8!null 266 └── filters 267 └── p_new:6 = parent.p:8 268 269 exec-ddl 270 CREATE TABLE self (x INT PRIMARY KEY, y INT NOT NULL REFERENCES self(x)) 271 ---- 272 273 build 274 UPDATE self SET y = 3 275 ---- 276 update self 277 ├── columns: <none> 278 ├── fetch columns: x:3 y:4 279 ├── update-mapping: 280 │ └── y_new:5 => y:2 281 ├── input binding: &1 282 ├── project 283 │ ├── columns: y_new:5!null x:3!null y:4!null 284 │ ├── scan self 285 │ │ └── columns: x:3!null y:4!null 286 │ └── projections 287 │ └── 3 [as=y_new:5] 288 └── f-k-checks 289 └── f-k-checks-item: self(y) -> self(x) 290 └── anti-join (hash) 291 ├── columns: y_new:6!null 292 ├── with-scan &1 293 │ ├── columns: y_new:6!null 294 │ └── mapping: 295 │ └── y_new:5 => y_new:6 296 ├── scan self 297 │ └── columns: x:7!null 298 └── filters 299 └── y_new:6 = x:7 300 301 build 302 UPDATE self SET x = 3 303 ---- 304 update self 305 ├── columns: <none> 306 ├── fetch columns: self.x:3 y:4 307 ├── update-mapping: 308 │ └── x_new:5 => self.x:1 309 ├── input binding: &1 310 ├── project 311 │ ├── columns: x_new:5!null self.x:3!null y:4!null 312 │ ├── scan self 313 │ │ └── columns: self.x:3!null y:4!null 314 │ └── projections 315 │ └── 3 [as=x_new:5] 316 └── f-k-checks 317 └── f-k-checks-item: self(y) -> self(x) 318 └── semi-join (hash) 319 ├── columns: x:6!null 320 ├── except 321 │ ├── columns: x:6!null 322 │ ├── left columns: x:6!null 323 │ ├── right columns: x_new:7 324 │ ├── with-scan &1 325 │ │ ├── columns: x:6!null 326 │ │ └── mapping: 327 │ │ └── self.x:3 => x:6 328 │ └── with-scan &1 329 │ ├── columns: x_new:7!null 330 │ └── mapping: 331 │ └── x_new:5 => x_new:7 332 ├── scan self 333 │ └── columns: y:9!null 334 └── filters 335 └── x:6 = y:9 336 337 exec-ddl 338 CREATE TABLE parent_multicol (a INT, b INT, c INT, PRIMARY KEY (a,b,c)) 339 ---- 340 341 exec-ddl 342 CREATE TABLE child_multicol_simple ( 343 k INT PRIMARY KEY, 344 a INT, b INT, c INT, 345 CONSTRAINT fk FOREIGN KEY(a,b,c) REFERENCES parent_multicol(a,b,c) MATCH SIMPLE 346 ) 347 ---- 348 349 # With MATCH SIMPLE, we can elide the FK check if any FK column is NULL. 350 build 351 UPDATE child_multicol_simple SET a = 1, b = NULL, c = 1 WHERE k = 1 352 ---- 353 update child_multicol_simple 354 ├── columns: <none> 355 ├── fetch columns: k:5 a:6 b:7 c:8 356 ├── update-mapping: 357 │ ├── a_new:9 => a:2 358 │ ├── b_new:10 => b:3 359 │ └── a_new:9 => c:4 360 └── project 361 ├── columns: a_new:9!null b_new:10 k:5!null a:6 b:7 c:8 362 ├── select 363 │ ├── columns: k:5!null a:6 b:7 c:8 364 │ ├── scan child_multicol_simple 365 │ │ └── columns: k:5!null a:6 b:7 c:8 366 │ └── filters 367 │ └── k:5 = 1 368 └── projections 369 ├── 1 [as=a_new:9] 370 └── NULL::INT8 [as=b_new:10] 371 372 exec-ddl 373 CREATE TABLE child_multicol_full ( 374 k INT PRIMARY KEY, 375 a INT, b INT, c INT, 376 CONSTRAINT fk FOREIGN KEY(a,b,c) REFERENCES parent_multicol(a,b,c) MATCH FULL 377 ) 378 ---- 379 380 # With MATCH FULL, we can elide the FK check only if all FK columns are NULL. 381 build 382 UPDATE child_multicol_full SET a = 1, b = NULL, c = 1 WHERE k = 1 383 ---- 384 update child_multicol_full 385 ├── columns: <none> 386 ├── fetch columns: k:5 child_multicol_full.a:6 child_multicol_full.b:7 child_multicol_full.c:8 387 ├── update-mapping: 388 │ ├── a_new:9 => child_multicol_full.a:2 389 │ ├── b_new:10 => child_multicol_full.b:3 390 │ └── a_new:9 => child_multicol_full.c:4 391 ├── input binding: &1 392 ├── project 393 │ ├── columns: a_new:9!null b_new:10 k:5!null child_multicol_full.a:6 child_multicol_full.b:7 child_multicol_full.c:8 394 │ ├── select 395 │ │ ├── columns: k:5!null child_multicol_full.a:6 child_multicol_full.b:7 child_multicol_full.c:8 396 │ │ ├── scan child_multicol_full 397 │ │ │ └── columns: k:5!null child_multicol_full.a:6 child_multicol_full.b:7 child_multicol_full.c:8 398 │ │ └── filters 399 │ │ └── k:5 = 1 400 │ └── projections 401 │ ├── 1 [as=a_new:9] 402 │ └── NULL::INT8 [as=b_new:10] 403 └── f-k-checks 404 └── f-k-checks-item: child_multicol_full(a,b,c) -> parent_multicol(a,b,c) 405 └── anti-join (hash) 406 ├── columns: a_new:11!null b_new:12 a_new:13!null 407 ├── with-scan &1 408 │ ├── columns: a_new:11!null b_new:12 a_new:13!null 409 │ └── mapping: 410 │ ├── a_new:9 => a_new:11 411 │ ├── b_new:10 => b_new:12 412 │ └── a_new:9 => a_new:13 413 ├── scan parent_multicol 414 │ └── columns: parent_multicol.a:14!null parent_multicol.b:15!null parent_multicol.c:16!null 415 └── filters 416 ├── a_new:11 = parent_multicol.a:14 417 ├── b_new:12 = parent_multicol.b:15 418 └── a_new:13 = parent_multicol.c:16 419 420 build 421 UPDATE child_multicol_full SET a = NULL, b = NULL, c = NULL WHERE k = 1 422 ---- 423 update child_multicol_full 424 ├── columns: <none> 425 ├── fetch columns: k:5 a:6 b:7 c:8 426 ├── update-mapping: 427 │ ├── a_new:9 => a:2 428 │ ├── a_new:9 => b:3 429 │ └── a_new:9 => c:4 430 └── project 431 ├── columns: a_new:9 k:5!null a:6 b:7 c:8 432 ├── select 433 │ ├── columns: k:5!null a:6 b:7 c:8 434 │ ├── scan child_multicol_full 435 │ │ └── columns: k:5!null a:6 b:7 c:8 436 │ └── filters 437 │ └── k:5 = 1 438 └── projections 439 └── NULL::INT8 [as=a_new:9] 440 441 exec-ddl 442 CREATE TABLE two (a int, b int, primary key (a, b)) 443 ---- 444 445 exec-ddl 446 CREATE TABLE fam ( 447 a INT, 448 b INT, 449 c INT, 450 d INT, 451 e INT, 452 FAMILY (a, b, c), 453 FAMILY (d, e), 454 FOREIGN KEY (c, d) REFERENCES two (a, b) 455 ) 456 ---- 457 458 # Ensure that we fetch all relevant columns for a foreign key. 459 460 # NOTE: when we no longer require indexes to be created for FKs, ensure that 461 # these still scan all the relevant FK columns. 462 norm 463 UPDATE fam SET c = 3 464 ---- 465 update fam 466 ├── columns: <none> 467 ├── fetch columns: fam.a:7 fam.b:8 c:9 fam.d:10 rowid:12 468 ├── update-mapping: 469 │ └── c_new:13 => c:3 470 ├── input binding: &1 471 ├── project 472 │ ├── columns: c_new:13!null fam.a:7 fam.b:8 c:9 fam.d:10 rowid:12!null 473 │ ├── scan fam 474 │ │ └── columns: fam.a:7 fam.b:8 c:9 fam.d:10 rowid:12!null 475 │ └── projections 476 │ └── 3 [as=c_new:13] 477 └── f-k-checks 478 └── f-k-checks-item: fam(c,d) -> two(a,b) 479 └── anti-join (hash) 480 ├── columns: c_new:14!null d:15!null 481 ├── select 482 │ ├── columns: c_new:14!null d:15!null 483 │ ├── with-scan &1 484 │ │ ├── columns: c_new:14!null d:15 485 │ │ └── mapping: 486 │ │ ├── c_new:13 => c_new:14 487 │ │ └── fam.d:10 => d:15 488 │ └── filters 489 │ └── d:15 IS NOT NULL 490 ├── scan two 491 │ └── columns: two.a:16!null two.b:17!null 492 └── filters 493 ├── c_new:14 = two.a:16 494 └── d:15 = two.b:17 495 496 norm 497 UPDATE fam SET d = 3 498 ---- 499 update fam 500 ├── columns: <none> 501 ├── fetch columns: fam.c:9 d:10 e:11 rowid:12 502 ├── update-mapping: 503 │ └── d_new:13 => d:4 504 ├── input binding: &1 505 ├── project 506 │ ├── columns: d_new:13!null fam.c:9 d:10 e:11 rowid:12!null 507 │ ├── scan fam 508 │ │ └── columns: fam.c:9 d:10 e:11 rowid:12!null 509 │ └── projections 510 │ └── 3 [as=d_new:13] 511 └── f-k-checks 512 └── f-k-checks-item: fam(c,d) -> two(a,b) 513 └── anti-join (hash) 514 ├── columns: c:14!null d_new:15!null 515 ├── select 516 │ ├── columns: c:14!null d_new:15!null 517 │ ├── with-scan &1 518 │ │ ├── columns: c:14 d_new:15!null 519 │ │ └── mapping: 520 │ │ ├── fam.c:9 => c:14 521 │ │ └── d_new:13 => d_new:15 522 │ └── filters 523 │ └── c:14 IS NOT NULL 524 ├── scan two 525 │ └── columns: two.a:16!null two.b:17!null 526 └── filters 527 ├── c:14 = two.a:16 528 └── d_new:15 = two.b:17