github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/optbuilder/testdata/fk-checks-insert (about) 1 exec-ddl 2 CREATE TABLE parent (p INT PRIMARY KEY, other INT) 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 INSERT INTO child VALUES (100, 1), (200, 1) 11 ---- 12 insert child 13 ├── columns: <none> 14 ├── insert-mapping: 15 │ ├── column1:3 => c:1 16 │ └── column2:4 => child.p:2 17 ├── input binding: &1 18 ├── values 19 │ ├── columns: column1:3!null column2:4!null 20 │ ├── (100, 1) 21 │ └── (200, 1) 22 └── f-k-checks 23 └── f-k-checks-item: child(p) -> parent(p) 24 └── anti-join (hash) 25 ├── columns: column2:5!null 26 ├── with-scan &1 27 │ ├── columns: column2:5!null 28 │ └── mapping: 29 │ └── column2:4 => column2:5 30 ├── scan parent 31 │ └── columns: parent.p:6!null 32 └── filters 33 └── column2:5 = parent.p:6 34 35 build 36 INSERT INTO child VALUES (100, 1), (200, 1) ON CONFLICT DO NOTHING 37 ---- 38 insert child 39 ├── columns: <none> 40 ├── insert-mapping: 41 │ ├── column1:3 => c:1 42 │ └── column2:4 => child.p:2 43 ├── input binding: &1 44 ├── upsert-distinct-on 45 │ ├── columns: column1:3!null column2:4!null 46 │ ├── grouping columns: column1:3!null 47 │ ├── project 48 │ │ ├── columns: column1:3!null column2:4!null 49 │ │ └── select 50 │ │ ├── columns: column1:3!null column2:4!null c:5 child.p:6 51 │ │ ├── left-join (hash) 52 │ │ │ ├── columns: column1:3!null column2:4!null c:5 child.p:6 53 │ │ │ ├── values 54 │ │ │ │ ├── columns: column1:3!null column2:4!null 55 │ │ │ │ ├── (100, 1) 56 │ │ │ │ └── (200, 1) 57 │ │ │ ├── scan child 58 │ │ │ │ └── columns: c:5!null child.p:6!null 59 │ │ │ └── filters 60 │ │ │ └── column1:3 = c:5 61 │ │ └── filters 62 │ │ └── c:5 IS NULL 63 │ └── aggregations 64 │ └── first-agg [as=column2:4] 65 │ └── column2:4 66 └── f-k-checks 67 └── f-k-checks-item: child(p) -> parent(p) 68 └── anti-join (hash) 69 ├── columns: column2:7!null 70 ├── with-scan &1 71 │ ├── columns: column2:7!null 72 │ └── mapping: 73 │ └── column2:4 => column2:7 74 ├── scan parent 75 │ └── columns: parent.p:8!null 76 └── filters 77 └── column2:7 = parent.p:8 78 79 # Use a non-constant input. 80 exec-ddl 81 CREATE TABLE xy (x INT, y INT) 82 ---- 83 84 build 85 INSERT INTO child SELECT x, y FROM xy 86 ---- 87 insert child 88 ├── columns: <none> 89 ├── insert-mapping: 90 │ ├── x:3 => c:1 91 │ └── xy.y:4 => child.p:2 92 ├── input binding: &1 93 ├── project 94 │ ├── columns: x:3 xy.y:4 95 │ └── scan xy 96 │ └── columns: x:3 xy.y:4 rowid:5!null 97 └── f-k-checks 98 └── f-k-checks-item: child(p) -> parent(p) 99 └── anti-join (hash) 100 ├── columns: y:6 101 ├── with-scan &1 102 │ ├── columns: y:6 103 │ └── mapping: 104 │ └── xy.y:4 => y:6 105 ├── scan parent 106 │ └── columns: parent.p:7!null 107 └── filters 108 └── y:6 = parent.p:7 109 110 exec-ddl 111 CREATE TABLE child_nullable (c INT PRIMARY KEY, p INT REFERENCES parent(p)); 112 ---- 113 114 # Because the input column can be NULL (in which case it requires no FK match), 115 # we have to add an extra filter. 116 build 117 INSERT INTO child_nullable VALUES (100, 1), (200, NULL) 118 ---- 119 insert child_nullable 120 ├── columns: <none> 121 ├── insert-mapping: 122 │ ├── column1:3 => c:1 123 │ └── column2:4 => child_nullable.p:2 124 ├── input binding: &1 125 ├── values 126 │ ├── columns: column1:3!null column2:4 127 │ ├── (100, 1) 128 │ └── (200, NULL::INT8) 129 └── f-k-checks 130 └── f-k-checks-item: child_nullable(p) -> parent(p) 131 └── anti-join (hash) 132 ├── columns: column2:5!null 133 ├── select 134 │ ├── columns: column2:5!null 135 │ ├── with-scan &1 136 │ │ ├── columns: column2:5 137 │ │ └── mapping: 138 │ │ └── column2:4 => column2:5 139 │ └── filters 140 │ └── column2:5 IS NOT NULL 141 ├── scan parent 142 │ └── columns: parent.p:6!null 143 └── filters 144 └── column2:5 = parent.p:6 145 146 # The column is nullable but we know that the input is not null, so we don't 147 # need to plan the filter. 148 build 149 INSERT INTO child_nullable VALUES (100, 1), (200, 1) 150 ---- 151 insert child_nullable 152 ├── columns: <none> 153 ├── insert-mapping: 154 │ ├── column1:3 => c:1 155 │ └── column2:4 => child_nullable.p:2 156 ├── input binding: &1 157 ├── values 158 │ ├── columns: column1:3!null column2:4!null 159 │ ├── (100, 1) 160 │ └── (200, 1) 161 └── f-k-checks 162 └── f-k-checks-item: child_nullable(p) -> parent(p) 163 └── anti-join (hash) 164 ├── columns: column2:5!null 165 ├── with-scan &1 166 │ ├── columns: column2:5!null 167 │ └── mapping: 168 │ └── column2:4 => column2:5 169 ├── scan parent 170 │ └── columns: parent.p:6!null 171 └── filters 172 └── column2:5 = parent.p:6 173 174 # In this case, we know that we are inserting *only* NULL values, so we don't 175 # need to check any FKs. 176 build 177 INSERT INTO child_nullable VALUES (100, NULL), (200, NULL) 178 ---- 179 insert child_nullable 180 ├── columns: <none> 181 ├── insert-mapping: 182 │ ├── column1:3 => c:1 183 │ └── column2:4 => p:2 184 └── values 185 ├── columns: column1:3!null column2:4 186 ├── (100, NULL::INT8) 187 └── (200, NULL::INT8) 188 189 # Same as above. 190 build 191 INSERT INTO child_nullable (c) VALUES (100), (200) 192 ---- 193 insert child_nullable 194 ├── columns: <none> 195 ├── insert-mapping: 196 │ ├── column1:3 => c:1 197 │ └── column4:4 => p:2 198 └── project 199 ├── columns: column4:4 column1:3!null 200 ├── values 201 │ ├── columns: column1:3!null 202 │ ├── (100,) 203 │ └── (200,) 204 └── projections 205 └── NULL::INT8 [as=column4:4] 206 207 # Check planning of filter with FULL match (which should be the same on a 208 # single column). 209 exec-ddl 210 CREATE TABLE child_nullable_full (c INT PRIMARY KEY, p INT REFERENCES parent(p) MATCH FULL) 211 ---- 212 213 build 214 INSERT INTO child_nullable_full VALUES (100, 1), (200, NULL) 215 ---- 216 insert child_nullable_full 217 ├── columns: <none> 218 ├── insert-mapping: 219 │ ├── column1:3 => c:1 220 │ └── column2:4 => child_nullable_full.p:2 221 ├── input binding: &1 222 ├── values 223 │ ├── columns: column1:3!null column2:4 224 │ ├── (100, 1) 225 │ └── (200, NULL::INT8) 226 └── f-k-checks 227 └── f-k-checks-item: child_nullable_full(p) -> parent(p) 228 └── anti-join (hash) 229 ├── columns: column2:5!null 230 ├── select 231 │ ├── columns: column2:5!null 232 │ ├── with-scan &1 233 │ │ ├── columns: column2:5 234 │ │ └── mapping: 235 │ │ └── column2:4 => column2:5 236 │ └── filters 237 │ └── column2:5 IS NOT NULL 238 ├── scan parent 239 │ └── columns: parent.p:6!null 240 └── filters 241 └── column2:5 = parent.p:6 242 243 # No FK check needed. 244 build 245 INSERT INTO child_nullable_full (c) VALUES (100), (200) 246 ---- 247 insert child_nullable_full 248 ├── columns: <none> 249 ├── insert-mapping: 250 │ ├── column1:3 => c:1 251 │ └── column4:4 => p:2 252 └── project 253 ├── columns: column4:4 column1:3!null 254 ├── values 255 │ ├── columns: column1:3!null 256 │ ├── (100,) 257 │ └── (200,) 258 └── projections 259 └── NULL::INT8 [as=column4:4] 260 261 # Tests with multicolumn FKs. 262 exec-ddl 263 CREATE TABLE multi_col_parent (p INT, q INT, r INT, other INT, PRIMARY KEY (p, q, r)) 264 ---- 265 266 exec-ddl 267 CREATE TABLE multi_col_child ( 268 c INT PRIMARY KEY, 269 p INT, q INT, r INT, 270 CONSTRAINT fk FOREIGN KEY (p,q,r) REFERENCES multi_col_parent(p,q,r) MATCH SIMPLE 271 ) 272 ---- 273 274 # All columns are nullable and must be part of the filter. 275 build 276 INSERT INTO multi_col_child VALUES (4, NULL, NULL, NULL), (5, 1, 2, 3) 277 ---- 278 insert multi_col_child 279 ├── columns: <none> 280 ├── insert-mapping: 281 │ ├── column1:5 => c:1 282 │ ├── column2:6 => multi_col_child.p:2 283 │ ├── column3:7 => multi_col_child.q:3 284 │ └── column4:8 => multi_col_child.r:4 285 ├── input binding: &1 286 ├── values 287 │ ├── columns: column1:5!null column2:6 column3:7 column4:8 288 │ ├── (4, NULL::INT8, NULL::INT8, NULL::INT8) 289 │ └── (5, 1, 2, 3) 290 └── f-k-checks 291 └── f-k-checks-item: multi_col_child(p,q,r) -> multi_col_parent(p,q,r) 292 └── anti-join (hash) 293 ├── columns: column2:9!null column3:10!null column4:11!null 294 ├── select 295 │ ├── columns: column2:9!null column3:10!null column4:11!null 296 │ ├── with-scan &1 297 │ │ ├── columns: column2:9 column3:10 column4:11 298 │ │ └── mapping: 299 │ │ ├── column2:6 => column2:9 300 │ │ ├── column3:7 => column3:10 301 │ │ └── column4:8 => column4:11 302 │ └── filters 303 │ ├── column2:9 IS NOT NULL 304 │ ├── column3:10 IS NOT NULL 305 │ └── column4:11 IS NOT NULL 306 ├── scan multi_col_parent 307 │ └── columns: multi_col_parent.p:12!null multi_col_parent.q:13!null multi_col_parent.r:14!null 308 └── filters 309 ├── column2:9 = multi_col_parent.p:12 310 ├── column3:10 = multi_col_parent.q:13 311 └── column4:11 = multi_col_parent.r:14 312 313 # Only p and q are nullable. 314 build 315 INSERT INTO multi_col_child VALUES (2, NULL, 20, 20), (3, 20, NULL, 20) 316 ---- 317 insert multi_col_child 318 ├── columns: <none> 319 ├── insert-mapping: 320 │ ├── column1:5 => c:1 321 │ ├── column2:6 => multi_col_child.p:2 322 │ ├── column3:7 => multi_col_child.q:3 323 │ └── column4:8 => multi_col_child.r:4 324 ├── input binding: &1 325 ├── values 326 │ ├── columns: column1:5!null column2:6 column3:7 column4:8!null 327 │ ├── (2, NULL::INT8, 20, 20) 328 │ └── (3, 20, NULL::INT8, 20) 329 └── f-k-checks 330 └── f-k-checks-item: multi_col_child(p,q,r) -> multi_col_parent(p,q,r) 331 └── anti-join (hash) 332 ├── columns: column2:9!null column3:10!null column4:11!null 333 ├── select 334 │ ├── columns: column2:9!null column3:10!null column4:11!null 335 │ ├── with-scan &1 336 │ │ ├── columns: column2:9 column3:10 column4:11!null 337 │ │ └── mapping: 338 │ │ ├── column2:6 => column2:9 339 │ │ ├── column3:7 => column3:10 340 │ │ └── column4:8 => column4:11 341 │ └── filters 342 │ ├── column2:9 IS NOT NULL 343 │ └── column3:10 IS NOT NULL 344 ├── scan multi_col_parent 345 │ └── columns: multi_col_parent.p:12!null multi_col_parent.q:13!null multi_col_parent.r:14!null 346 └── filters 347 ├── column2:9 = multi_col_parent.p:12 348 ├── column3:10 = multi_col_parent.q:13 349 └── column4:11 = multi_col_parent.r:14 350 351 # All the FK columns are not-null; no filter necessary. 352 build 353 INSERT INTO multi_col_child VALUES (1, 10, 10, 10) 354 ---- 355 insert multi_col_child 356 ├── columns: <none> 357 ├── insert-mapping: 358 │ ├── column1:5 => c:1 359 │ ├── column2:6 => multi_col_child.p:2 360 │ ├── column3:7 => multi_col_child.q:3 361 │ └── column4:8 => multi_col_child.r:4 362 ├── input binding: &1 363 ├── values 364 │ ├── columns: column1:5!null column2:6!null column3:7!null column4:8!null 365 │ └── (1, 10, 10, 10) 366 └── f-k-checks 367 └── f-k-checks-item: multi_col_child(p,q,r) -> multi_col_parent(p,q,r) 368 └── anti-join (hash) 369 ├── columns: column2:9!null column3:10!null column4:11!null 370 ├── with-scan &1 371 │ ├── columns: column2:9!null column3:10!null column4:11!null 372 │ └── mapping: 373 │ ├── column2:6 => column2:9 374 │ ├── column3:7 => column3:10 375 │ └── column4:8 => column4:11 376 ├── scan multi_col_parent 377 │ └── columns: multi_col_parent.p:12!null multi_col_parent.q:13!null multi_col_parent.r:14!null 378 └── filters 379 ├── column2:9 = multi_col_parent.p:12 380 ├── column3:10 = multi_col_parent.q:13 381 └── column4:11 = multi_col_parent.r:14 382 383 # No FK check needed - we have only NULL values for a FK column. 384 build 385 INSERT INTO multi_col_child VALUES (1, 10, NULL, 10) 386 ---- 387 insert multi_col_child 388 ├── columns: <none> 389 ├── insert-mapping: 390 │ ├── column1:5 => c:1 391 │ ├── column2:6 => p:2 392 │ ├── column3:7 => q:3 393 │ └── column4:8 => r:4 394 └── values 395 ├── columns: column1:5!null column2:6!null column3:7 column4:8!null 396 └── (1, 10, NULL::INT8, 10) 397 398 exec-ddl 399 CREATE TABLE multi_col_child_full ( 400 c INT PRIMARY KEY, 401 p INT, q INT, r INT, 402 CONSTRAINT fk FOREIGN KEY (p,q,r) REFERENCES multi_col_parent(p,q,r) MATCH FULL 403 ) 404 ---- 405 406 # All columns are nullable and must be part of the filter. 407 build 408 INSERT INTO multi_col_child_full VALUES (4, NULL, NULL, NULL), (5, 1, 2, 3) 409 ---- 410 insert multi_col_child_full 411 ├── columns: <none> 412 ├── insert-mapping: 413 │ ├── column1:5 => c:1 414 │ ├── column2:6 => multi_col_child_full.p:2 415 │ ├── column3:7 => multi_col_child_full.q:3 416 │ └── column4:8 => multi_col_child_full.r:4 417 ├── input binding: &1 418 ├── values 419 │ ├── columns: column1:5!null column2:6 column3:7 column4:8 420 │ ├── (4, NULL::INT8, NULL::INT8, NULL::INT8) 421 │ └── (5, 1, 2, 3) 422 └── f-k-checks 423 └── f-k-checks-item: multi_col_child_full(p,q,r) -> multi_col_parent(p,q,r) 424 └── anti-join (hash) 425 ├── columns: column2:9 column3:10 column4:11 426 ├── select 427 │ ├── columns: column2:9 column3:10 column4:11 428 │ ├── with-scan &1 429 │ │ ├── columns: column2:9 column3:10 column4:11 430 │ │ └── mapping: 431 │ │ ├── column2:6 => column2:9 432 │ │ ├── column3:7 => column3:10 433 │ │ └── column4:8 => column4:11 434 │ └── filters 435 │ └── ((column2:9 IS NOT NULL) OR (column3:10 IS NOT NULL)) OR (column4:11 IS NOT NULL) 436 ├── scan multi_col_parent 437 │ └── columns: multi_col_parent.p:12!null multi_col_parent.q:13!null multi_col_parent.r:14!null 438 └── filters 439 ├── column2:9 = multi_col_parent.p:12 440 ├── column3:10 = multi_col_parent.q:13 441 └── column4:11 = multi_col_parent.r:14 442 443 # Only p and q are nullable; no filter necessary. 444 build 445 INSERT INTO multi_col_child_full VALUES (2, NULL, 20, 20), (3, 20, NULL, 20) 446 ---- 447 insert multi_col_child_full 448 ├── columns: <none> 449 ├── insert-mapping: 450 │ ├── column1:5 => c:1 451 │ ├── column2:6 => multi_col_child_full.p:2 452 │ ├── column3:7 => multi_col_child_full.q:3 453 │ └── column4:8 => multi_col_child_full.r:4 454 ├── input binding: &1 455 ├── values 456 │ ├── columns: column1:5!null column2:6 column3:7 column4:8!null 457 │ ├── (2, NULL::INT8, 20, 20) 458 │ └── (3, 20, NULL::INT8, 20) 459 └── f-k-checks 460 └── f-k-checks-item: multi_col_child_full(p,q,r) -> multi_col_parent(p,q,r) 461 └── anti-join (hash) 462 ├── columns: column2:9 column3:10 column4:11!null 463 ├── with-scan &1 464 │ ├── columns: column2:9 column3:10 column4:11!null 465 │ └── mapping: 466 │ ├── column2:6 => column2:9 467 │ ├── column3:7 => column3:10 468 │ └── column4:8 => column4:11 469 ├── scan multi_col_parent 470 │ └── columns: multi_col_parent.p:12!null multi_col_parent.q:13!null multi_col_parent.r:14!null 471 └── filters 472 ├── column2:9 = multi_col_parent.p:12 473 ├── column3:10 = multi_col_parent.q:13 474 └── column4:11 = multi_col_parent.r:14 475 476 # All the FK columns are not-null; no filter necessary. 477 build 478 INSERT INTO multi_col_child_full VALUES (1, 10, 10, 10) 479 ---- 480 insert multi_col_child_full 481 ├── columns: <none> 482 ├── insert-mapping: 483 │ ├── column1:5 => c:1 484 │ ├── column2:6 => multi_col_child_full.p:2 485 │ ├── column3:7 => multi_col_child_full.q:3 486 │ └── column4:8 => multi_col_child_full.r:4 487 ├── input binding: &1 488 ├── values 489 │ ├── columns: column1:5!null column2:6!null column3:7!null column4:8!null 490 │ └── (1, 10, 10, 10) 491 └── f-k-checks 492 └── f-k-checks-item: multi_col_child_full(p,q,r) -> multi_col_parent(p,q,r) 493 └── anti-join (hash) 494 ├── columns: column2:9!null column3:10!null column4:11!null 495 ├── with-scan &1 496 │ ├── columns: column2:9!null column3:10!null column4:11!null 497 │ └── mapping: 498 │ ├── column2:6 => column2:9 499 │ ├── column3:7 => column3:10 500 │ └── column4:8 => column4:11 501 ├── scan multi_col_parent 502 │ └── columns: multi_col_parent.p:12!null multi_col_parent.q:13!null multi_col_parent.r:14!null 503 └── filters 504 ├── column2:9 = multi_col_parent.p:12 505 ├── column3:10 = multi_col_parent.q:13 506 └── column4:11 = multi_col_parent.r:14 507 508 # No FK check needed when all FK columns only have NULL values. 509 build 510 INSERT INTO multi_col_child_full VALUES (1, NULL, NULL, NULL) 511 ---- 512 insert multi_col_child_full 513 ├── columns: <none> 514 ├── insert-mapping: 515 │ ├── column1:5 => c:1 516 │ ├── column2:6 => p:2 517 │ ├── column3:7 => q:3 518 │ └── column4:8 => r:4 519 └── values 520 ├── columns: column1:5!null column2:6 column3:7 column4:8 521 └── (1, NULL::INT8, NULL::INT8, NULL::INT8) 522 523 # But with MATCH FULL, the FK check is needed when only a subset of the columns 524 # only have NULL values. 525 build 526 INSERT INTO multi_col_child_full VALUES (1, NULL, 2, NULL) 527 ---- 528 insert multi_col_child_full 529 ├── columns: <none> 530 ├── insert-mapping: 531 │ ├── column1:5 => c:1 532 │ ├── column2:6 => multi_col_child_full.p:2 533 │ ├── column3:7 => multi_col_child_full.q:3 534 │ └── column4:8 => multi_col_child_full.r:4 535 ├── input binding: &1 536 ├── values 537 │ ├── columns: column1:5!null column2:6 column3:7!null column4:8 538 │ └── (1, NULL::INT8, 2, NULL::INT8) 539 └── f-k-checks 540 └── f-k-checks-item: multi_col_child_full(p,q,r) -> multi_col_parent(p,q,r) 541 └── anti-join (hash) 542 ├── columns: column2:9 column3:10!null column4:11 543 ├── with-scan &1 544 │ ├── columns: column2:9 column3:10!null column4:11 545 │ └── mapping: 546 │ ├── column2:6 => column2:9 547 │ ├── column3:7 => column3:10 548 │ └── column4:8 => column4:11 549 ├── scan multi_col_parent 550 │ └── columns: multi_col_parent.p:12!null multi_col_parent.q:13!null multi_col_parent.r:14!null 551 └── filters 552 ├── column2:9 = multi_col_parent.p:12 553 ├── column3:10 = multi_col_parent.q:13 554 └── column4:11 = multi_col_parent.r:14 555 556 exec-ddl 557 CREATE TABLE multi_ref_parent_a (a INT PRIMARY KEY, other INT) 558 ---- 559 560 exec-ddl 561 CREATE TABLE multi_ref_parent_bc (b INT, c INT, PRIMARY KEY (b,c), other INT) 562 ---- 563 564 exec-ddl 565 CREATE TABLE multi_ref_child ( 566 k INT PRIMARY KEY, 567 a INT, 568 b INT, 569 c INT, 570 CONSTRAINT fk FOREIGN KEY (a) REFERENCES multi_ref_parent_a(a), 571 CONSTRAINT fk FOREIGN KEY (b,c) REFERENCES multi_ref_parent_bc(b,c) 572 ) 573 ---- 574 575 build 576 INSERT INTO multi_ref_child VALUES (1, 1, NULL, NULL), (2, NULL, 2, NULL), (3, NULL, NULL, 3) 577 ---- 578 insert multi_ref_child 579 ├── columns: <none> 580 ├── insert-mapping: 581 │ ├── column1:5 => k:1 582 │ ├── column2:6 => multi_ref_child.a:2 583 │ ├── column3:7 => multi_ref_child.b:3 584 │ └── column4:8 => multi_ref_child.c:4 585 ├── input binding: &1 586 ├── values 587 │ ├── columns: column1:5!null column2:6 column3:7 column4:8 588 │ ├── (1, 1, NULL::INT8, NULL::INT8) 589 │ ├── (2, NULL::INT8, 2, NULL::INT8) 590 │ └── (3, NULL::INT8, NULL::INT8, 3) 591 └── f-k-checks 592 ├── f-k-checks-item: multi_ref_child(a) -> multi_ref_parent_a(a) 593 │ └── anti-join (hash) 594 │ ├── columns: column2:9!null 595 │ ├── select 596 │ │ ├── columns: column2:9!null 597 │ │ ├── with-scan &1 598 │ │ │ ├── columns: column2:9 599 │ │ │ └── mapping: 600 │ │ │ └── column2:6 => column2:9 601 │ │ └── filters 602 │ │ └── column2:9 IS NOT NULL 603 │ ├── scan multi_ref_parent_a 604 │ │ └── columns: multi_ref_parent_a.a:10!null 605 │ └── filters 606 │ └── column2:9 = multi_ref_parent_a.a:10 607 └── f-k-checks-item: multi_ref_child(b,c) -> multi_ref_parent_bc(b,c) 608 └── anti-join (hash) 609 ├── columns: column3:12!null column4:13!null 610 ├── select 611 │ ├── columns: column3:12!null column4:13!null 612 │ ├── with-scan &1 613 │ │ ├── columns: column3:12 column4:13 614 │ │ └── mapping: 615 │ │ ├── column3:7 => column3:12 616 │ │ └── column4:8 => column4:13 617 │ └── filters 618 │ ├── column3:12 IS NOT NULL 619 │ └── column4:13 IS NOT NULL 620 ├── scan multi_ref_parent_bc 621 │ └── columns: multi_ref_parent_bc.b:14!null multi_ref_parent_bc.c:15!null 622 └── filters 623 ├── column3:12 = multi_ref_parent_bc.b:14 624 └── column4:13 = multi_ref_parent_bc.c:15 625 626 build 627 INSERT INTO multi_ref_child VALUES (1, NULL, NULL, NULL) 628 ---- 629 insert multi_ref_child 630 ├── columns: <none> 631 ├── insert-mapping: 632 │ ├── column1:5 => k:1 633 │ ├── column2:6 => a:2 634 │ ├── column3:7 => b:3 635 │ └── column4:8 => c:4 636 └── values 637 ├── columns: column1:5!null column2:6 column3:7 column4:8 638 └── (1, NULL::INT8, NULL::INT8, NULL::INT8)