github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/optbuilder/testdata/fk-checks-upsert (about) 1 # The upsert tests need to exercise these dimensions: 2 # - inbound vs outbound FKs 3 # - single vs multiple FK columns 4 # - all table columns specified vs subset of table (and FK) columns specified 5 # - statement type: 6 # - UPSERT 7 # - INSERT ON CONFLICT DO UPDATE 8 # - INSERT ON CONFLICT with a secondary index 9 # 10 # Note that ON CONFLICT DO NOTHING is not built as an Upsert so it is not 11 # tested here. 12 13 exec-ddl 14 CREATE TABLE xyzw (x INT, y INT, z INT, w INT) 15 ---- 16 17 exec-ddl 18 CREATE TABLE uv (u INT NOT NULL, v INT NOT NULL) 19 ---- 20 21 # --------------------------------------- 22 # Outbound FK tests with single FK column 23 # --------------------------------------- 24 25 exec-ddl 26 CREATE TABLE p (p INT PRIMARY KEY, other INT) 27 ---- 28 29 exec-ddl 30 CREATE TABLE c1 (c INT PRIMARY KEY, p INT NOT NULL DEFAULT 5 REFERENCES p(p), i INT) 31 ---- 32 33 build 34 UPSERT INTO c1 VALUES (100, 1), (200, 1) 35 ---- 36 upsert c1 37 ├── columns: <none> 38 ├── canary column: 7 39 ├── fetch columns: c:7 c1.p:8 i:9 40 ├── insert-mapping: 41 │ ├── column1:4 => c:1 42 │ ├── column2:5 => c1.p:2 43 │ └── column6:6 => i:3 44 ├── update-mapping: 45 │ ├── column2:5 => c1.p:2 46 │ └── column6:6 => i:3 47 ├── input binding: &1 48 ├── project 49 │ ├── columns: upsert_c:10 column1:4!null column2:5!null column6:6 c:7 c1.p:8 i:9 50 │ ├── left-join (hash) 51 │ │ ├── columns: column1:4!null column2:5!null column6:6 c:7 c1.p:8 i:9 52 │ │ ├── ensure-upsert-distinct-on 53 │ │ │ ├── columns: column1:4!null column2:5!null column6:6 54 │ │ │ ├── grouping columns: column1:4!null 55 │ │ │ ├── project 56 │ │ │ │ ├── columns: column6:6 column1:4!null column2:5!null 57 │ │ │ │ ├── values 58 │ │ │ │ │ ├── columns: column1:4!null column2:5!null 59 │ │ │ │ │ ├── (100, 1) 60 │ │ │ │ │ └── (200, 1) 61 │ │ │ │ └── projections 62 │ │ │ │ └── NULL::INT8 [as=column6:6] 63 │ │ │ └── aggregations 64 │ │ │ ├── first-agg [as=column2:5] 65 │ │ │ │ └── column2:5 66 │ │ │ └── first-agg [as=column6:6] 67 │ │ │ └── column6:6 68 │ │ ├── scan c1 69 │ │ │ └── columns: c:7!null c1.p:8!null i:9 70 │ │ └── filters 71 │ │ └── column1:4 = c:7 72 │ └── projections 73 │ └── CASE WHEN c:7 IS NULL THEN column1:4 ELSE c:7 END [as=upsert_c:10] 74 └── f-k-checks 75 └── f-k-checks-item: c1(p) -> p(p) 76 └── anti-join (hash) 77 ├── columns: column2:11!null 78 ├── with-scan &1 79 │ ├── columns: column2:11!null 80 │ └── mapping: 81 │ └── column2:5 => column2:11 82 ├── scan p 83 │ └── columns: p.p:12!null 84 └── filters 85 └── column2:11 = p.p:12 86 87 build 88 UPSERT INTO c1(c) VALUES (100), (200) 89 ---- 90 upsert c1 91 ├── columns: <none> 92 ├── canary column: 7 93 ├── fetch columns: c:7 c1.p:8 i:9 94 ├── insert-mapping: 95 │ ├── column1:4 => c:1 96 │ ├── column5:5 => c1.p:2 97 │ └── column6:6 => i:3 98 ├── input binding: &1 99 ├── project 100 │ ├── columns: upsert_c:10 upsert_p:11 upsert_i:12 column1:4!null column5:5!null column6:6 c:7 c1.p:8 i:9 101 │ ├── left-join (hash) 102 │ │ ├── columns: column1:4!null column5:5!null column6:6 c:7 c1.p:8 i:9 103 │ │ ├── ensure-upsert-distinct-on 104 │ │ │ ├── columns: column1:4!null column5:5!null column6:6 105 │ │ │ ├── grouping columns: column1:4!null 106 │ │ │ ├── project 107 │ │ │ │ ├── columns: column5:5!null column6:6 column1:4!null 108 │ │ │ │ ├── values 109 │ │ │ │ │ ├── columns: column1:4!null 110 │ │ │ │ │ ├── (100,) 111 │ │ │ │ │ └── (200,) 112 │ │ │ │ └── projections 113 │ │ │ │ ├── 5 [as=column5:5] 114 │ │ │ │ └── NULL::INT8 [as=column6:6] 115 │ │ │ └── aggregations 116 │ │ │ ├── first-agg [as=column5:5] 117 │ │ │ │ └── column5:5 118 │ │ │ └── first-agg [as=column6:6] 119 │ │ │ └── column6:6 120 │ │ ├── scan c1 121 │ │ │ └── columns: c:7!null c1.p:8!null i:9 122 │ │ └── filters 123 │ │ └── column1:4 = c:7 124 │ └── projections 125 │ ├── CASE WHEN c:7 IS NULL THEN column1:4 ELSE c:7 END [as=upsert_c:10] 126 │ ├── CASE WHEN c:7 IS NULL THEN column5:5 ELSE c1.p:8 END [as=upsert_p:11] 127 │ └── CASE WHEN c:7 IS NULL THEN column6:6 ELSE i:9 END [as=upsert_i:12] 128 └── f-k-checks 129 └── f-k-checks-item: c1(p) -> p(p) 130 └── anti-join (hash) 131 ├── columns: upsert_p:13 132 ├── with-scan &1 133 │ ├── columns: upsert_p:13 134 │ └── mapping: 135 │ └── upsert_p:11 => upsert_p:13 136 ├── scan p 137 │ └── columns: p.p:14!null 138 └── filters 139 └── upsert_p:13 = p.p:14 140 141 # Use a non-constant input. 142 build 143 UPSERT INTO c1 SELECT x, y FROM xyzw 144 ---- 145 upsert c1 146 ├── columns: <none> 147 ├── canary column: 10 148 ├── fetch columns: c:10 c1.p:11 i:12 149 ├── insert-mapping: 150 │ ├── x:4 => c:1 151 │ ├── xyzw.y:5 => c1.p:2 152 │ └── column9:9 => i:3 153 ├── update-mapping: 154 │ ├── xyzw.y:5 => c1.p:2 155 │ └── column9:9 => i:3 156 ├── input binding: &1 157 ├── project 158 │ ├── columns: upsert_c:13 x:4 xyzw.y:5 column9:9 c:10 c1.p:11 i:12 159 │ ├── left-join (hash) 160 │ │ ├── columns: x:4 xyzw.y:5 column9:9 c:10 c1.p:11 i:12 161 │ │ ├── ensure-upsert-distinct-on 162 │ │ │ ├── columns: x:4 xyzw.y:5 column9:9 163 │ │ │ ├── grouping columns: x:4 164 │ │ │ ├── project 165 │ │ │ │ ├── columns: column9:9 x:4 xyzw.y:5 166 │ │ │ │ ├── project 167 │ │ │ │ │ ├── columns: x:4 xyzw.y:5 168 │ │ │ │ │ └── scan xyzw 169 │ │ │ │ │ └── columns: x:4 xyzw.y:5 z:6 w:7 rowid:8!null 170 │ │ │ │ └── projections 171 │ │ │ │ └── NULL::INT8 [as=column9:9] 172 │ │ │ └── aggregations 173 │ │ │ ├── first-agg [as=xyzw.y:5] 174 │ │ │ │ └── xyzw.y:5 175 │ │ │ └── first-agg [as=column9:9] 176 │ │ │ └── column9:9 177 │ │ ├── scan c1 178 │ │ │ └── columns: c:10!null c1.p:11!null i:12 179 │ │ └── filters 180 │ │ └── x:4 = c:10 181 │ └── projections 182 │ └── CASE WHEN c:10 IS NULL THEN x:4 ELSE c:10 END [as=upsert_c:13] 183 └── f-k-checks 184 └── f-k-checks-item: c1(p) -> p(p) 185 └── anti-join (hash) 186 ├── columns: y:14 187 ├── with-scan &1 188 │ ├── columns: y:14 189 │ └── mapping: 190 │ └── xyzw.y:5 => y:14 191 ├── scan p 192 │ └── columns: p.p:15!null 193 └── filters 194 └── y:14 = p.p:15 195 196 build 197 INSERT INTO c1 VALUES (100, 1), (200, 1) ON CONFLICT (c) DO UPDATE SET p = excluded.p + 1 198 ---- 199 upsert c1 200 ├── columns: <none> 201 ├── canary column: 7 202 ├── fetch columns: c:7 c1.p:8 i:9 203 ├── insert-mapping: 204 │ ├── column1:4 => c:1 205 │ ├── column2:5 => c1.p:2 206 │ └── column6:6 => i:3 207 ├── update-mapping: 208 │ └── upsert_p:12 => c1.p:2 209 ├── input binding: &1 210 ├── project 211 │ ├── columns: upsert_c:11 upsert_p:12!null upsert_i:13 column1:4!null column2:5!null column6:6 c:7 c1.p:8 i:9 p_new:10!null 212 │ ├── project 213 │ │ ├── columns: p_new:10!null column1:4!null column2:5!null column6:6 c:7 c1.p:8 i:9 214 │ │ ├── left-join (hash) 215 │ │ │ ├── columns: column1:4!null column2:5!null column6:6 c:7 c1.p:8 i:9 216 │ │ │ ├── ensure-upsert-distinct-on 217 │ │ │ │ ├── columns: column1:4!null column2:5!null column6:6 218 │ │ │ │ ├── grouping columns: column1:4!null 219 │ │ │ │ ├── project 220 │ │ │ │ │ ├── columns: column6:6 column1:4!null column2:5!null 221 │ │ │ │ │ ├── values 222 │ │ │ │ │ │ ├── columns: column1:4!null column2:5!null 223 │ │ │ │ │ │ ├── (100, 1) 224 │ │ │ │ │ │ └── (200, 1) 225 │ │ │ │ │ └── projections 226 │ │ │ │ │ └── NULL::INT8 [as=column6:6] 227 │ │ │ │ └── aggregations 228 │ │ │ │ ├── first-agg [as=column2:5] 229 │ │ │ │ │ └── column2:5 230 │ │ │ │ └── first-agg [as=column6:6] 231 │ │ │ │ └── column6:6 232 │ │ │ ├── scan c1 233 │ │ │ │ └── columns: c:7!null c1.p:8!null i:9 234 │ │ │ └── filters 235 │ │ │ └── column1:4 = c:7 236 │ │ └── projections 237 │ │ └── column2:5 + 1 [as=p_new:10] 238 │ └── projections 239 │ ├── CASE WHEN c:7 IS NULL THEN column1:4 ELSE c:7 END [as=upsert_c:11] 240 │ ├── CASE WHEN c:7 IS NULL THEN column2:5 ELSE p_new:10 END [as=upsert_p:12] 241 │ └── CASE WHEN c:7 IS NULL THEN column6:6 ELSE i:9 END [as=upsert_i:13] 242 └── f-k-checks 243 └── f-k-checks-item: c1(p) -> p(p) 244 └── anti-join (hash) 245 ├── columns: upsert_p:14!null 246 ├── with-scan &1 247 │ ├── columns: upsert_p:14!null 248 │ └── mapping: 249 │ └── upsert_p:12 => upsert_p:14 250 ├── scan p 251 │ └── columns: p.p:15!null 252 └── filters 253 └── upsert_p:14 = p.p:15 254 255 build 256 INSERT INTO c1 SELECT u, v FROM uv ON CONFLICT (c) DO UPDATE SET i = c1.c + 1 257 ---- 258 upsert c1 259 ├── columns: <none> 260 ├── canary column: 8 261 ├── fetch columns: c:8 c1.p:9 i:10 262 ├── insert-mapping: 263 │ ├── u:4 => c:1 264 │ ├── v:5 => c1.p:2 265 │ └── column7:7 => i:3 266 ├── update-mapping: 267 │ └── upsert_i:14 => i:3 268 ├── input binding: &1 269 ├── project 270 │ ├── columns: upsert_c:12 upsert_p:13 upsert_i:14 u:4!null v:5!null column7:7 c:8 c1.p:9 i:10 i_new:11 271 │ ├── project 272 │ │ ├── columns: i_new:11 u:4!null v:5!null column7:7 c:8 c1.p:9 i:10 273 │ │ ├── left-join (hash) 274 │ │ │ ├── columns: u:4!null v:5!null column7:7 c:8 c1.p:9 i:10 275 │ │ │ ├── ensure-upsert-distinct-on 276 │ │ │ │ ├── columns: u:4!null v:5!null column7:7 277 │ │ │ │ ├── grouping columns: u:4!null 278 │ │ │ │ ├── project 279 │ │ │ │ │ ├── columns: column7:7 u:4!null v:5!null 280 │ │ │ │ │ ├── project 281 │ │ │ │ │ │ ├── columns: u:4!null v:5!null 282 │ │ │ │ │ │ └── scan uv 283 │ │ │ │ │ │ └── columns: u:4!null v:5!null rowid:6!null 284 │ │ │ │ │ └── projections 285 │ │ │ │ │ └── NULL::INT8 [as=column7:7] 286 │ │ │ │ └── aggregations 287 │ │ │ │ ├── first-agg [as=v:5] 288 │ │ │ │ │ └── v:5 289 │ │ │ │ └── first-agg [as=column7:7] 290 │ │ │ │ └── column7:7 291 │ │ │ ├── scan c1 292 │ │ │ │ └── columns: c:8!null c1.p:9!null i:10 293 │ │ │ └── filters 294 │ │ │ └── u:4 = c:8 295 │ │ └── projections 296 │ │ └── c:8 + 1 [as=i_new:11] 297 │ └── projections 298 │ ├── CASE WHEN c:8 IS NULL THEN u:4 ELSE c:8 END [as=upsert_c:12] 299 │ ├── CASE WHEN c:8 IS NULL THEN v:5 ELSE c1.p:9 END [as=upsert_p:13] 300 │ └── CASE WHEN c:8 IS NULL THEN column7:7 ELSE i_new:11 END [as=upsert_i:14] 301 └── f-k-checks 302 └── f-k-checks-item: c1(p) -> p(p) 303 └── anti-join (hash) 304 ├── columns: upsert_p:15 305 ├── with-scan &1 306 │ ├── columns: upsert_p:15 307 │ └── mapping: 308 │ └── upsert_p:13 => upsert_p:15 309 ├── scan p 310 │ └── columns: p.p:16!null 311 └── filters 312 └── upsert_p:15 = p.p:16 313 314 exec-ddl 315 CREATE TABLE c2 (c INT PRIMARY KEY, FOREIGN KEY (c) REFERENCES p(p)) 316 ---- 317 318 build 319 INSERT INTO c2 VALUES (1), (2) ON CONFLICT (c) DO UPDATE SET c = 1 320 ---- 321 upsert c2 322 ├── columns: <none> 323 ├── canary column: 3 324 ├── fetch columns: c:3 325 ├── insert-mapping: 326 │ └── column1:2 => c:1 327 ├── update-mapping: 328 │ └── upsert_c:5 => c:1 329 ├── input binding: &1 330 ├── project 331 │ ├── columns: upsert_c:5!null column1:2!null c:3 c_new:4!null 332 │ ├── project 333 │ │ ├── columns: c_new:4!null column1:2!null c:3 334 │ │ ├── left-join (hash) 335 │ │ │ ├── columns: column1:2!null c:3 336 │ │ │ ├── ensure-upsert-distinct-on 337 │ │ │ │ ├── columns: column1:2!null 338 │ │ │ │ ├── grouping columns: column1:2!null 339 │ │ │ │ └── values 340 │ │ │ │ ├── columns: column1:2!null 341 │ │ │ │ ├── (1,) 342 │ │ │ │ └── (2,) 343 │ │ │ ├── scan c2 344 │ │ │ │ └── columns: c:3!null 345 │ │ │ └── filters 346 │ │ │ └── column1:2 = c:3 347 │ │ └── projections 348 │ │ └── 1 [as=c_new:4] 349 │ └── projections 350 │ └── CASE WHEN c:3 IS NULL THEN column1:2 ELSE c_new:4 END [as=upsert_c:5] 351 └── f-k-checks 352 └── f-k-checks-item: c2(c) -> p(p) 353 └── anti-join (hash) 354 ├── columns: upsert_c:6!null 355 ├── with-scan &1 356 │ ├── columns: upsert_c:6!null 357 │ └── mapping: 358 │ └── upsert_c:5 => upsert_c:6 359 ├── scan p 360 │ └── columns: p:7!null 361 └── filters 362 └── upsert_c:6 = p:7 363 364 exec-ddl 365 CREATE TABLE c3 (c INT PRIMARY KEY, p INT REFERENCES p(p)); 366 ---- 367 368 # Because the input column can be NULL (in which case it requires no FK match), 369 # we have to add an extra filter. 370 build 371 UPSERT INTO c3 VALUES (100, 1), (200, NULL) 372 ---- 373 upsert c3 374 ├── columns: <none> 375 ├── canary column: 5 376 ├── fetch columns: c:5 c3.p:6 377 ├── insert-mapping: 378 │ ├── column1:3 => c:1 379 │ └── column2:4 => c3.p:2 380 ├── update-mapping: 381 │ └── column2:4 => c3.p:2 382 ├── input binding: &1 383 ├── project 384 │ ├── columns: upsert_c:7 column1:3!null column2:4 c:5 c3.p:6 385 │ ├── left-join (hash) 386 │ │ ├── columns: column1:3!null column2:4 c:5 c3.p:6 387 │ │ ├── ensure-upsert-distinct-on 388 │ │ │ ├── columns: column1:3!null column2:4 389 │ │ │ ├── grouping columns: column1:3!null 390 │ │ │ ├── values 391 │ │ │ │ ├── columns: column1:3!null column2:4 392 │ │ │ │ ├── (100, 1) 393 │ │ │ │ └── (200, NULL::INT8) 394 │ │ │ └── aggregations 395 │ │ │ └── first-agg [as=column2:4] 396 │ │ │ └── column2:4 397 │ │ ├── scan c3 398 │ │ │ └── columns: c:5!null c3.p:6 399 │ │ └── filters 400 │ │ └── column1:3 = c:5 401 │ └── projections 402 │ └── CASE WHEN c:5 IS NULL THEN column1:3 ELSE c:5 END [as=upsert_c:7] 403 └── f-k-checks 404 └── f-k-checks-item: c3(p) -> p(p) 405 └── anti-join (hash) 406 ├── columns: column2:8!null 407 ├── select 408 │ ├── columns: column2:8!null 409 │ ├── with-scan &1 410 │ │ ├── columns: column2:8 411 │ │ └── mapping: 412 │ │ └── column2:4 => column2:8 413 │ └── filters 414 │ └── column2:8 IS NOT NULL 415 ├── scan p 416 │ └── columns: p.p:9!null 417 └── filters 418 └── column2:8 = p.p:9 419 420 build 421 UPSERT INTO c3(c) VALUES (100), (200) 422 ---- 423 upsert c3 424 ├── columns: <none> 425 ├── canary column: 5 426 ├── fetch columns: c:5 c3.p:6 427 ├── insert-mapping: 428 │ ├── column1:3 => c:1 429 │ └── column4:4 => c3.p:2 430 ├── input binding: &1 431 ├── project 432 │ ├── columns: upsert_c:7 upsert_p:8 column1:3!null column4:4 c:5 c3.p:6 433 │ ├── left-join (hash) 434 │ │ ├── columns: column1:3!null column4:4 c:5 c3.p:6 435 │ │ ├── ensure-upsert-distinct-on 436 │ │ │ ├── columns: column1:3!null column4:4 437 │ │ │ ├── grouping columns: column1:3!null 438 │ │ │ ├── project 439 │ │ │ │ ├── columns: column4:4 column1:3!null 440 │ │ │ │ ├── values 441 │ │ │ │ │ ├── columns: column1:3!null 442 │ │ │ │ │ ├── (100,) 443 │ │ │ │ │ └── (200,) 444 │ │ │ │ └── projections 445 │ │ │ │ └── NULL::INT8 [as=column4:4] 446 │ │ │ └── aggregations 447 │ │ │ └── first-agg [as=column4:4] 448 │ │ │ └── column4:4 449 │ │ ├── scan c3 450 │ │ │ └── columns: c:5!null c3.p:6 451 │ │ └── filters 452 │ │ └── column1:3 = c:5 453 │ └── projections 454 │ ├── CASE WHEN c:5 IS NULL THEN column1:3 ELSE c:5 END [as=upsert_c:7] 455 │ └── CASE WHEN c:5 IS NULL THEN column4:4 ELSE c3.p:6 END [as=upsert_p:8] 456 └── f-k-checks 457 └── f-k-checks-item: c3(p) -> p(p) 458 └── anti-join (hash) 459 ├── columns: upsert_p:9!null 460 ├── select 461 │ ├── columns: upsert_p:9!null 462 │ ├── with-scan &1 463 │ │ ├── columns: upsert_p:9 464 │ │ └── mapping: 465 │ │ └── upsert_p:8 => upsert_p:9 466 │ └── filters 467 │ └── upsert_p:9 IS NOT NULL 468 ├── scan p 469 │ └── columns: p.p:10!null 470 └── filters 471 └── upsert_p:9 = p.p:10 472 473 exec-ddl 474 CREATE TABLE c4 (c INT PRIMARY KEY, a INT REFERENCES p(p), other INT, UNIQUE(a)) 475 ---- 476 477 build 478 INSERT INTO c4 SELECT x, y, z FROM xyzw ON CONFLICT (a) DO UPDATE SET other = 1 479 ---- 480 upsert c4 481 ├── columns: <none> 482 ├── canary column: 9 483 ├── fetch columns: c:9 a:10 c4.other:11 484 ├── insert-mapping: 485 │ ├── x:4 => c:1 486 │ ├── y:5 => a:2 487 │ └── z:6 => c4.other:3 488 ├── update-mapping: 489 │ └── upsert_other:15 => c4.other:3 490 ├── input binding: &1 491 ├── project 492 │ ├── columns: upsert_c:13 upsert_a:14 upsert_other:15 x:4 y:5 z:6 c:9 a:10 c4.other:11 other_new:12!null 493 │ ├── project 494 │ │ ├── columns: other_new:12!null x:4 y:5 z:6 c:9 a:10 c4.other:11 495 │ │ ├── left-join (hash) 496 │ │ │ ├── columns: x:4 y:5 z:6 c:9 a:10 c4.other:11 497 │ │ │ ├── ensure-upsert-distinct-on 498 │ │ │ │ ├── columns: x:4 y:5 z:6 499 │ │ │ │ ├── grouping columns: y:5 500 │ │ │ │ ├── project 501 │ │ │ │ │ ├── columns: x:4 y:5 z:6 502 │ │ │ │ │ └── scan xyzw 503 │ │ │ │ │ └── columns: x:4 y:5 z:6 w:7 rowid:8!null 504 │ │ │ │ └── aggregations 505 │ │ │ │ ├── first-agg [as=x:4] 506 │ │ │ │ │ └── x:4 507 │ │ │ │ └── first-agg [as=z:6] 508 │ │ │ │ └── z:6 509 │ │ │ ├── scan c4 510 │ │ │ │ └── columns: c:9!null a:10 c4.other:11 511 │ │ │ └── filters 512 │ │ │ └── y:5 = a:10 513 │ │ └── projections 514 │ │ └── 1 [as=other_new:12] 515 │ └── projections 516 │ ├── CASE WHEN c:9 IS NULL THEN x:4 ELSE c:9 END [as=upsert_c:13] 517 │ ├── CASE WHEN c:9 IS NULL THEN y:5 ELSE a:10 END [as=upsert_a:14] 518 │ └── CASE WHEN c:9 IS NULL THEN z:6 ELSE other_new:12 END [as=upsert_other:15] 519 └── f-k-checks 520 └── f-k-checks-item: c4(a) -> p(p) 521 └── anti-join (hash) 522 ├── columns: upsert_a:16!null 523 ├── select 524 │ ├── columns: upsert_a:16!null 525 │ ├── with-scan &1 526 │ │ ├── columns: upsert_a:16 527 │ │ └── mapping: 528 │ │ └── upsert_a:14 => upsert_a:16 529 │ └── filters 530 │ └── upsert_a:16 IS NOT NULL 531 ├── scan p 532 │ └── columns: p:17!null 533 └── filters 534 └── upsert_a:16 = p:17 535 536 build 537 INSERT INTO c4 SELECT x, y, z FROM xyzw ON CONFLICT (a) DO UPDATE SET a = 5 538 ---- 539 upsert c4 540 ├── columns: <none> 541 ├── canary column: 9 542 ├── fetch columns: c:9 a:10 c4.other:11 543 ├── insert-mapping: 544 │ ├── x:4 => c:1 545 │ ├── y:5 => a:2 546 │ └── z:6 => c4.other:3 547 ├── update-mapping: 548 │ └── upsert_a:14 => a:2 549 ├── input binding: &1 550 ├── project 551 │ ├── columns: upsert_c:13 upsert_a:14 upsert_other:15 x:4 y:5 z:6 c:9 a:10 c4.other:11 a_new:12!null 552 │ ├── project 553 │ │ ├── columns: a_new:12!null x:4 y:5 z:6 c:9 a:10 c4.other:11 554 │ │ ├── left-join (hash) 555 │ │ │ ├── columns: x:4 y:5 z:6 c:9 a:10 c4.other:11 556 │ │ │ ├── ensure-upsert-distinct-on 557 │ │ │ │ ├── columns: x:4 y:5 z:6 558 │ │ │ │ ├── grouping columns: y:5 559 │ │ │ │ ├── project 560 │ │ │ │ │ ├── columns: x:4 y:5 z:6 561 │ │ │ │ │ └── scan xyzw 562 │ │ │ │ │ └── columns: x:4 y:5 z:6 w:7 rowid:8!null 563 │ │ │ │ └── aggregations 564 │ │ │ │ ├── first-agg [as=x:4] 565 │ │ │ │ │ └── x:4 566 │ │ │ │ └── first-agg [as=z:6] 567 │ │ │ │ └── z:6 568 │ │ │ ├── scan c4 569 │ │ │ │ └── columns: c:9!null a:10 c4.other:11 570 │ │ │ └── filters 571 │ │ │ └── y:5 = a:10 572 │ │ └── projections 573 │ │ └── 5 [as=a_new:12] 574 │ └── projections 575 │ ├── CASE WHEN c:9 IS NULL THEN x:4 ELSE c:9 END [as=upsert_c:13] 576 │ ├── CASE WHEN c:9 IS NULL THEN y:5 ELSE a_new:12 END [as=upsert_a:14] 577 │ └── CASE WHEN c:9 IS NULL THEN z:6 ELSE c4.other:11 END [as=upsert_other:15] 578 └── f-k-checks 579 └── f-k-checks-item: c4(a) -> p(p) 580 └── anti-join (hash) 581 ├── columns: upsert_a:16!null 582 ├── select 583 │ ├── columns: upsert_a:16!null 584 │ ├── with-scan &1 585 │ │ ├── columns: upsert_a:16 586 │ │ └── mapping: 587 │ │ └── upsert_a:14 => upsert_a:16 588 │ └── filters 589 │ └── upsert_a:16 IS NOT NULL 590 ├── scan p 591 │ └── columns: p:17!null 592 └── filters 593 └── upsert_a:16 = p:17 594 595 596 # ------------------------------------------ 597 # Outbound FK tests with multiple FK columns 598 # ------------------------------------------ 599 600 exec-ddl 601 CREATE TABLE pq ( 602 k INT PRIMARY KEY, 603 p INT, 604 q INT, 605 other INT, 606 UNIQUE(p,q), 607 FAMILY (k), FAMILY (p), FAMILY (q), FAMILY (other) 608 ) 609 ---- 610 611 exec-ddl 612 CREATE TABLE cpq ( 613 c INT PRIMARY KEY, 614 p INT DEFAULT 4, 615 q INT DEFAULT 8, 616 other INT, 617 FAMILY (c), FAMILY (p), FAMILY (q), FAMILY (other), 618 CONSTRAINT fk FOREIGN KEY (p,q) REFERENCES pq(p,q) MATCH SIMPLE 619 ) 620 ---- 621 622 build 623 UPSERT INTO cpq VALUES (1, 1, 1, 1) 624 ---- 625 upsert cpq 626 ├── columns: <none> 627 ├── canary column: 9 628 ├── fetch columns: c:9 cpq.p:10 cpq.q:11 cpq.other:12 629 ├── insert-mapping: 630 │ ├── column1:5 => c:1 631 │ ├── column2:6 => cpq.p:2 632 │ ├── column3:7 => cpq.q:3 633 │ └── column4:8 => cpq.other:4 634 ├── update-mapping: 635 │ ├── column2:6 => cpq.p:2 636 │ ├── column3:7 => cpq.q:3 637 │ └── column4:8 => cpq.other:4 638 ├── input binding: &1 639 ├── project 640 │ ├── columns: upsert_c:13 column1:5!null column2:6!null column3:7!null column4:8!null c:9 cpq.p:10 cpq.q:11 cpq.other:12 641 │ ├── left-join (hash) 642 │ │ ├── columns: column1:5!null column2:6!null column3:7!null column4:8!null c:9 cpq.p:10 cpq.q:11 cpq.other:12 643 │ │ ├── ensure-upsert-distinct-on 644 │ │ │ ├── columns: column1:5!null column2:6!null column3:7!null column4:8!null 645 │ │ │ ├── grouping columns: column1:5!null 646 │ │ │ ├── values 647 │ │ │ │ ├── columns: column1:5!null column2:6!null column3:7!null column4:8!null 648 │ │ │ │ └── (1, 1, 1, 1) 649 │ │ │ └── aggregations 650 │ │ │ ├── first-agg [as=column2:6] 651 │ │ │ │ └── column2:6 652 │ │ │ ├── first-agg [as=column3:7] 653 │ │ │ │ └── column3:7 654 │ │ │ └── first-agg [as=column4:8] 655 │ │ │ └── column4:8 656 │ │ ├── scan cpq 657 │ │ │ └── columns: c:9!null cpq.p:10 cpq.q:11 cpq.other:12 658 │ │ └── filters 659 │ │ └── column1:5 = c:9 660 │ └── projections 661 │ └── CASE WHEN c:9 IS NULL THEN column1:5 ELSE c:9 END [as=upsert_c:13] 662 └── f-k-checks 663 └── f-k-checks-item: cpq(p,q) -> pq(p,q) 664 └── anti-join (hash) 665 ├── columns: column2:14!null column3:15!null 666 ├── with-scan &1 667 │ ├── columns: column2:14!null column3:15!null 668 │ └── mapping: 669 │ ├── column2:6 => column2:14 670 │ └── column3:7 => column3:15 671 ├── scan pq 672 │ └── columns: pq.p:17 pq.q:18 673 └── filters 674 ├── column2:14 = pq.p:17 675 └── column3:15 = pq.q:18 676 677 # In this case, the input columns can be null. 678 build 679 UPSERT INTO cpq SELECT x,y,z,w FROM xyzw 680 ---- 681 upsert cpq 682 ├── columns: <none> 683 ├── canary column: 10 684 ├── fetch columns: c:10 cpq.p:11 cpq.q:12 cpq.other:13 685 ├── insert-mapping: 686 │ ├── x:5 => c:1 687 │ ├── xyzw.y:6 => cpq.p:2 688 │ ├── xyzw.z:7 => cpq.q:3 689 │ └── w:8 => cpq.other:4 690 ├── update-mapping: 691 │ ├── xyzw.y:6 => cpq.p:2 692 │ ├── xyzw.z:7 => cpq.q:3 693 │ └── w:8 => cpq.other:4 694 ├── input binding: &1 695 ├── project 696 │ ├── columns: upsert_c:14 x:5 xyzw.y:6 xyzw.z:7 w:8 c:10 cpq.p:11 cpq.q:12 cpq.other:13 697 │ ├── left-join (hash) 698 │ │ ├── columns: x:5 xyzw.y:6 xyzw.z:7 w:8 c:10 cpq.p:11 cpq.q:12 cpq.other:13 699 │ │ ├── ensure-upsert-distinct-on 700 │ │ │ ├── columns: x:5 xyzw.y:6 xyzw.z:7 w:8 701 │ │ │ ├── grouping columns: x:5 702 │ │ │ ├── project 703 │ │ │ │ ├── columns: x:5 xyzw.y:6 xyzw.z:7 w:8 704 │ │ │ │ └── scan xyzw 705 │ │ │ │ └── columns: x:5 xyzw.y:6 xyzw.z:7 w:8 rowid:9!null 706 │ │ │ └── aggregations 707 │ │ │ ├── first-agg [as=xyzw.y:6] 708 │ │ │ │ └── xyzw.y:6 709 │ │ │ ├── first-agg [as=xyzw.z:7] 710 │ │ │ │ └── xyzw.z:7 711 │ │ │ └── first-agg [as=w:8] 712 │ │ │ └── w:8 713 │ │ ├── scan cpq 714 │ │ │ └── columns: c:10!null cpq.p:11 cpq.q:12 cpq.other:13 715 │ │ └── filters 716 │ │ └── x:5 = c:10 717 │ └── projections 718 │ └── CASE WHEN c:10 IS NULL THEN x:5 ELSE c:10 END [as=upsert_c:14] 719 └── f-k-checks 720 └── f-k-checks-item: cpq(p,q) -> pq(p,q) 721 └── anti-join (hash) 722 ├── columns: y:15!null z:16!null 723 ├── select 724 │ ├── columns: y:15!null z:16!null 725 │ ├── with-scan &1 726 │ │ ├── columns: y:15 z:16 727 │ │ └── mapping: 728 │ │ ├── xyzw.y:6 => y:15 729 │ │ └── xyzw.z:7 => z:16 730 │ └── filters 731 │ ├── y:15 IS NOT NULL 732 │ └── z:16 IS NOT NULL 733 ├── scan pq 734 │ └── columns: pq.p:18 pq.q:19 735 └── filters 736 ├── y:15 = pq.p:18 737 └── z:16 = pq.q:19 738 739 build 740 UPSERT INTO cpq(c,p) SELECT x,y FROM xyzw 741 ---- 742 upsert cpq 743 ├── columns: <none> 744 ├── canary column: 12 745 ├── fetch columns: c:12 cpq.p:13 cpq.q:14 cpq.other:15 746 ├── insert-mapping: 747 │ ├── x:5 => c:1 748 │ ├── xyzw.y:6 => cpq.p:2 749 │ ├── column10:10 => cpq.q:3 750 │ └── column11:11 => cpq.other:4 751 ├── update-mapping: 752 │ └── xyzw.y:6 => cpq.p:2 753 ├── input binding: &1 754 ├── project 755 │ ├── columns: upsert_c:16 upsert_q:17 upsert_other:18 x:5 xyzw.y:6 column10:10!null column11:11 c:12 cpq.p:13 cpq.q:14 cpq.other:15 756 │ ├── left-join (hash) 757 │ │ ├── columns: x:5 xyzw.y:6 column10:10!null column11:11 c:12 cpq.p:13 cpq.q:14 cpq.other:15 758 │ │ ├── ensure-upsert-distinct-on 759 │ │ │ ├── columns: x:5 xyzw.y:6 column10:10!null column11:11 760 │ │ │ ├── grouping columns: x:5 761 │ │ │ ├── project 762 │ │ │ │ ├── columns: column10:10!null column11:11 x:5 xyzw.y:6 763 │ │ │ │ ├── project 764 │ │ │ │ │ ├── columns: x:5 xyzw.y:6 765 │ │ │ │ │ └── scan xyzw 766 │ │ │ │ │ └── columns: x:5 xyzw.y:6 z:7 w:8 rowid:9!null 767 │ │ │ │ └── projections 768 │ │ │ │ ├── 8 [as=column10:10] 769 │ │ │ │ └── NULL::INT8 [as=column11:11] 770 │ │ │ └── aggregations 771 │ │ │ ├── first-agg [as=xyzw.y:6] 772 │ │ │ │ └── xyzw.y:6 773 │ │ │ ├── first-agg [as=column10:10] 774 │ │ │ │ └── column10:10 775 │ │ │ └── first-agg [as=column11:11] 776 │ │ │ └── column11:11 777 │ │ ├── scan cpq 778 │ │ │ └── columns: c:12!null cpq.p:13 cpq.q:14 cpq.other:15 779 │ │ └── filters 780 │ │ └── x:5 = c:12 781 │ └── projections 782 │ ├── CASE WHEN c:12 IS NULL THEN x:5 ELSE c:12 END [as=upsert_c:16] 783 │ ├── CASE WHEN c:12 IS NULL THEN column10:10 ELSE cpq.q:14 END [as=upsert_q:17] 784 │ └── CASE WHEN c:12 IS NULL THEN column11:11 ELSE cpq.other:15 END [as=upsert_other:18] 785 └── f-k-checks 786 └── f-k-checks-item: cpq(p,q) -> pq(p,q) 787 └── anti-join (hash) 788 ├── columns: y:19!null upsert_q:20!null 789 ├── select 790 │ ├── columns: y:19!null upsert_q:20!null 791 │ ├── with-scan &1 792 │ │ ├── columns: y:19 upsert_q:20 793 │ │ └── mapping: 794 │ │ ├── xyzw.y:6 => y:19 795 │ │ └── upsert_q:17 => upsert_q:20 796 │ └── filters 797 │ ├── y:19 IS NOT NULL 798 │ └── upsert_q:20 IS NOT NULL 799 ├── scan pq 800 │ └── columns: pq.p:22 pq.q:23 801 └── filters 802 ├── y:19 = pq.p:22 803 └── upsert_q:20 = pq.q:23 804 805 build 806 UPSERT INTO cpq(c) SELECT x FROM xyzw 807 ---- 808 upsert cpq 809 ├── columns: <none> 810 ├── canary column: 13 811 ├── fetch columns: c:13 cpq.p:14 cpq.q:15 cpq.other:16 812 ├── insert-mapping: 813 │ ├── x:5 => c:1 814 │ ├── column10:10 => cpq.p:2 815 │ ├── column11:11 => cpq.q:3 816 │ └── column12:12 => cpq.other:4 817 ├── input binding: &1 818 ├── project 819 │ ├── columns: upsert_c:17 upsert_p:18 upsert_q:19 upsert_other:20 x:5 column10:10!null column11:11!null column12:12 c:13 cpq.p:14 cpq.q:15 cpq.other:16 820 │ ├── left-join (hash) 821 │ │ ├── columns: x:5 column10:10!null column11:11!null column12:12 c:13 cpq.p:14 cpq.q:15 cpq.other:16 822 │ │ ├── ensure-upsert-distinct-on 823 │ │ │ ├── columns: x:5 column10:10!null column11:11!null column12:12 824 │ │ │ ├── grouping columns: x:5 825 │ │ │ ├── project 826 │ │ │ │ ├── columns: column10:10!null column11:11!null column12:12 x:5 827 │ │ │ │ ├── project 828 │ │ │ │ │ ├── columns: x:5 829 │ │ │ │ │ └── scan xyzw 830 │ │ │ │ │ └── columns: x:5 y:6 z:7 w:8 rowid:9!null 831 │ │ │ │ └── projections 832 │ │ │ │ ├── 4 [as=column10:10] 833 │ │ │ │ ├── 8 [as=column11:11] 834 │ │ │ │ └── NULL::INT8 [as=column12:12] 835 │ │ │ └── aggregations 836 │ │ │ ├── first-agg [as=column10:10] 837 │ │ │ │ └── column10:10 838 │ │ │ ├── first-agg [as=column11:11] 839 │ │ │ │ └── column11:11 840 │ │ │ └── first-agg [as=column12:12] 841 │ │ │ └── column12:12 842 │ │ ├── scan cpq 843 │ │ │ └── columns: c:13!null cpq.p:14 cpq.q:15 cpq.other:16 844 │ │ └── filters 845 │ │ └── x:5 = c:13 846 │ └── projections 847 │ ├── CASE WHEN c:13 IS NULL THEN x:5 ELSE c:13 END [as=upsert_c:17] 848 │ ├── CASE WHEN c:13 IS NULL THEN column10:10 ELSE cpq.p:14 END [as=upsert_p:18] 849 │ ├── CASE WHEN c:13 IS NULL THEN column11:11 ELSE cpq.q:15 END [as=upsert_q:19] 850 │ └── CASE WHEN c:13 IS NULL THEN column12:12 ELSE cpq.other:16 END [as=upsert_other:20] 851 └── f-k-checks 852 └── f-k-checks-item: cpq(p,q) -> pq(p,q) 853 └── anti-join (hash) 854 ├── columns: upsert_p:21!null upsert_q:22!null 855 ├── select 856 │ ├── columns: upsert_p:21!null upsert_q:22!null 857 │ ├── with-scan &1 858 │ │ ├── columns: upsert_p:21 upsert_q:22 859 │ │ └── mapping: 860 │ │ ├── upsert_p:18 => upsert_p:21 861 │ │ └── upsert_q:19 => upsert_q:22 862 │ └── filters 863 │ ├── upsert_p:21 IS NOT NULL 864 │ └── upsert_q:22 IS NOT NULL 865 ├── scan pq 866 │ └── columns: pq.p:24 pq.q:25 867 └── filters 868 ├── upsert_p:21 = pq.p:24 869 └── upsert_q:22 = pq.q:25 870 871 # This has different semantics from the UPSERT INTO cpq(c) version - here we 872 # upsert default values for all unspecified columns. 873 build 874 UPSERT INTO cpq SELECT x FROM xyzw 875 ---- 876 upsert cpq 877 ├── columns: <none> 878 ├── canary column: 13 879 ├── fetch columns: c:13 cpq.p:14 cpq.q:15 cpq.other:16 880 ├── insert-mapping: 881 │ ├── x:5 => c:1 882 │ ├── column10:10 => cpq.p:2 883 │ ├── column11:11 => cpq.q:3 884 │ └── column12:12 => cpq.other:4 885 ├── update-mapping: 886 │ ├── column10:10 => cpq.p:2 887 │ ├── column11:11 => cpq.q:3 888 │ └── column12:12 => cpq.other:4 889 ├── input binding: &1 890 ├── project 891 │ ├── columns: upsert_c:17 x:5 column10:10!null column11:11!null column12:12 c:13 cpq.p:14 cpq.q:15 cpq.other:16 892 │ ├── left-join (hash) 893 │ │ ├── columns: x:5 column10:10!null column11:11!null column12:12 c:13 cpq.p:14 cpq.q:15 cpq.other:16 894 │ │ ├── ensure-upsert-distinct-on 895 │ │ │ ├── columns: x:5 column10:10!null column11:11!null column12:12 896 │ │ │ ├── grouping columns: x:5 897 │ │ │ ├── project 898 │ │ │ │ ├── columns: column10:10!null column11:11!null column12:12 x:5 899 │ │ │ │ ├── project 900 │ │ │ │ │ ├── columns: x:5 901 │ │ │ │ │ └── scan xyzw 902 │ │ │ │ │ └── columns: x:5 y:6 z:7 w:8 rowid:9!null 903 │ │ │ │ └── projections 904 │ │ │ │ ├── 4 [as=column10:10] 905 │ │ │ │ ├── 8 [as=column11:11] 906 │ │ │ │ └── NULL::INT8 [as=column12:12] 907 │ │ │ └── aggregations 908 │ │ │ ├── first-agg [as=column10:10] 909 │ │ │ │ └── column10:10 910 │ │ │ ├── first-agg [as=column11:11] 911 │ │ │ │ └── column11:11 912 │ │ │ └── first-agg [as=column12:12] 913 │ │ │ └── column12:12 914 │ │ ├── scan cpq 915 │ │ │ └── columns: c:13!null cpq.p:14 cpq.q:15 cpq.other:16 916 │ │ └── filters 917 │ │ └── x:5 = c:13 918 │ └── projections 919 │ └── CASE WHEN c:13 IS NULL THEN x:5 ELSE c:13 END [as=upsert_c:17] 920 └── f-k-checks 921 └── f-k-checks-item: cpq(p,q) -> pq(p,q) 922 └── anti-join (hash) 923 ├── columns: column10:18!null column11:19!null 924 ├── with-scan &1 925 │ ├── columns: column10:18!null column11:19!null 926 │ └── mapping: 927 │ ├── column10:10 => column10:18 928 │ └── column11:11 => column11:19 929 ├── scan pq 930 │ └── columns: pq.p:21 pq.q:22 931 └── filters 932 ├── column10:18 = pq.p:21 933 └── column11:19 = pq.q:22 934 935 build 936 INSERT INTO cpq VALUES (1), (2) ON CONFLICT (c) DO UPDATE SET p = 10 937 ---- 938 upsert cpq 939 ├── columns: <none> 940 ├── canary column: 9 941 ├── fetch columns: c:9 cpq.p:10 cpq.q:11 cpq.other:12 942 ├── insert-mapping: 943 │ ├── column1:5 => c:1 944 │ ├── column6:6 => cpq.p:2 945 │ ├── column7:7 => cpq.q:3 946 │ └── column8:8 => cpq.other:4 947 ├── update-mapping: 948 │ └── upsert_p:15 => cpq.p:2 949 ├── input binding: &1 950 ├── project 951 │ ├── columns: upsert_c:14 upsert_p:15!null upsert_q:16 upsert_other:17 column1:5!null column6:6!null column7:7!null column8:8 c:9 cpq.p:10 cpq.q:11 cpq.other:12 p_new:13!null 952 │ ├── project 953 │ │ ├── columns: p_new:13!null column1:5!null column6:6!null column7:7!null column8:8 c:9 cpq.p:10 cpq.q:11 cpq.other:12 954 │ │ ├── left-join (hash) 955 │ │ │ ├── columns: column1:5!null column6:6!null column7:7!null column8:8 c:9 cpq.p:10 cpq.q:11 cpq.other:12 956 │ │ │ ├── ensure-upsert-distinct-on 957 │ │ │ │ ├── columns: column1:5!null column6:6!null column7:7!null column8:8 958 │ │ │ │ ├── grouping columns: column1:5!null 959 │ │ │ │ ├── project 960 │ │ │ │ │ ├── columns: column6:6!null column7:7!null column8:8 column1:5!null 961 │ │ │ │ │ ├── values 962 │ │ │ │ │ │ ├── columns: column1:5!null 963 │ │ │ │ │ │ ├── (1,) 964 │ │ │ │ │ │ └── (2,) 965 │ │ │ │ │ └── projections 966 │ │ │ │ │ ├── 4 [as=column6:6] 967 │ │ │ │ │ ├── 8 [as=column7:7] 968 │ │ │ │ │ └── NULL::INT8 [as=column8:8] 969 │ │ │ │ └── aggregations 970 │ │ │ │ ├── first-agg [as=column6:6] 971 │ │ │ │ │ └── column6:6 972 │ │ │ │ ├── first-agg [as=column7:7] 973 │ │ │ │ │ └── column7:7 974 │ │ │ │ └── first-agg [as=column8:8] 975 │ │ │ │ └── column8:8 976 │ │ │ ├── scan cpq 977 │ │ │ │ └── columns: c:9!null cpq.p:10 cpq.q:11 cpq.other:12 978 │ │ │ └── filters 979 │ │ │ └── column1:5 = c:9 980 │ │ └── projections 981 │ │ └── 10 [as=p_new:13] 982 │ └── projections 983 │ ├── CASE WHEN c:9 IS NULL THEN column1:5 ELSE c:9 END [as=upsert_c:14] 984 │ ├── CASE WHEN c:9 IS NULL THEN column6:6 ELSE p_new:13 END [as=upsert_p:15] 985 │ ├── CASE WHEN c:9 IS NULL THEN column7:7 ELSE cpq.q:11 END [as=upsert_q:16] 986 │ └── CASE WHEN c:9 IS NULL THEN column8:8 ELSE cpq.other:12 END [as=upsert_other:17] 987 └── f-k-checks 988 └── f-k-checks-item: cpq(p,q) -> pq(p,q) 989 └── anti-join (hash) 990 ├── columns: upsert_p:18!null upsert_q:19!null 991 ├── select 992 │ ├── columns: upsert_p:18!null upsert_q:19!null 993 │ ├── with-scan &1 994 │ │ ├── columns: upsert_p:18!null upsert_q:19 995 │ │ └── mapping: 996 │ │ ├── upsert_p:15 => upsert_p:18 997 │ │ └── upsert_q:16 => upsert_q:19 998 │ └── filters 999 │ └── upsert_q:19 IS NOT NULL 1000 ├── scan pq 1001 │ └── columns: pq.p:21 pq.q:22 1002 └── filters 1003 ├── upsert_p:18 = pq.p:21 1004 └── upsert_q:19 = pq.q:22 1005 1006 # ------------------------------------------ 1007 # Multiple outbound FKs 1008 # ------------------------------------------ 1009 1010 exec-ddl 1011 CREATE TABLE cmulti ( 1012 a INT, 1013 b INT, 1014 c INT DEFAULT 4, 1015 d INT DEFAULT 8, 1016 PRIMARY KEY (a,b), 1017 FOREIGN KEY (a) REFERENCES p(p), 1018 FOREIGN KEY (b,c) REFERENCES pq(p,q) MATCH FULL 1019 ) 1020 ---- 1021 1022 build 1023 UPSERT INTO cmulti SELECT x,y,z,w FROM xyzw 1024 ---- 1025 upsert cmulti 1026 ├── columns: <none> 1027 ├── canary column: 10 1028 ├── fetch columns: a:10 b:11 c:12 d:13 1029 ├── insert-mapping: 1030 │ ├── x:5 => a:1 1031 │ ├── y:6 => b:2 1032 │ ├── xyzw.z:7 => c:3 1033 │ └── w:8 => d:4 1034 ├── update-mapping: 1035 │ ├── xyzw.z:7 => c:3 1036 │ └── w:8 => d:4 1037 ├── input binding: &1 1038 ├── project 1039 │ ├── columns: upsert_a:14 upsert_b:15 x:5 y:6 xyzw.z:7 w:8 a:10 b:11 c:12 d:13 1040 │ ├── left-join (hash) 1041 │ │ ├── columns: x:5 y:6 xyzw.z:7 w:8 a:10 b:11 c:12 d:13 1042 │ │ ├── ensure-upsert-distinct-on 1043 │ │ │ ├── columns: x:5 y:6 xyzw.z:7 w:8 1044 │ │ │ ├── grouping columns: x:5 y:6 1045 │ │ │ ├── project 1046 │ │ │ │ ├── columns: x:5 y:6 xyzw.z:7 w:8 1047 │ │ │ │ └── scan xyzw 1048 │ │ │ │ └── columns: x:5 y:6 xyzw.z:7 w:8 rowid:9!null 1049 │ │ │ └── aggregations 1050 │ │ │ ├── first-agg [as=xyzw.z:7] 1051 │ │ │ │ └── xyzw.z:7 1052 │ │ │ └── first-agg [as=w:8] 1053 │ │ │ └── w:8 1054 │ │ ├── scan cmulti 1055 │ │ │ └── columns: a:10!null b:11!null c:12 d:13 1056 │ │ └── filters 1057 │ │ ├── x:5 = a:10 1058 │ │ └── y:6 = b:11 1059 │ └── projections 1060 │ ├── CASE WHEN a:10 IS NULL THEN x:5 ELSE a:10 END [as=upsert_a:14] 1061 │ └── CASE WHEN a:10 IS NULL THEN y:6 ELSE b:11 END [as=upsert_b:15] 1062 └── f-k-checks 1063 ├── f-k-checks-item: cmulti(a) -> p(p) 1064 │ └── anti-join (hash) 1065 │ ├── columns: upsert_a:16 1066 │ ├── with-scan &1 1067 │ │ ├── columns: upsert_a:16 1068 │ │ └── mapping: 1069 │ │ └── upsert_a:14 => upsert_a:16 1070 │ ├── scan p 1071 │ │ └── columns: p.p:17!null 1072 │ └── filters 1073 │ └── upsert_a:16 = p.p:17 1074 └── f-k-checks-item: cmulti(b,c) -> pq(p,q) 1075 └── anti-join (hash) 1076 ├── columns: upsert_b:19 z:20 1077 ├── with-scan &1 1078 │ ├── columns: upsert_b:19 z:20 1079 │ └── mapping: 1080 │ ├── upsert_b:15 => upsert_b:19 1081 │ └── xyzw.z:7 => z:20 1082 ├── scan pq 1083 │ └── columns: pq.p:22 q:23 1084 └── filters 1085 ├── upsert_b:19 = pq.p:22 1086 └── z:20 = q:23 1087 1088 build 1089 UPSERT INTO cmulti(a,b,c) SELECT x,y,z FROM xyzw 1090 ---- 1091 upsert cmulti 1092 ├── columns: <none> 1093 ├── canary column: 11 1094 ├── fetch columns: a:11 b:12 c:13 d:14 1095 ├── insert-mapping: 1096 │ ├── x:5 => a:1 1097 │ ├── y:6 => b:2 1098 │ ├── xyzw.z:7 => c:3 1099 │ └── column10:10 => d:4 1100 ├── update-mapping: 1101 │ └── xyzw.z:7 => c:3 1102 ├── input binding: &1 1103 ├── project 1104 │ ├── columns: upsert_a:15 upsert_b:16 upsert_d:17 x:5 y:6 xyzw.z:7 column10:10!null a:11 b:12 c:13 d:14 1105 │ ├── left-join (hash) 1106 │ │ ├── columns: x:5 y:6 xyzw.z:7 column10:10!null a:11 b:12 c:13 d:14 1107 │ │ ├── ensure-upsert-distinct-on 1108 │ │ │ ├── columns: x:5 y:6 xyzw.z:7 column10:10!null 1109 │ │ │ ├── grouping columns: x:5 y:6 1110 │ │ │ ├── project 1111 │ │ │ │ ├── columns: column10:10!null x:5 y:6 xyzw.z:7 1112 │ │ │ │ ├── project 1113 │ │ │ │ │ ├── columns: x:5 y:6 xyzw.z:7 1114 │ │ │ │ │ └── scan xyzw 1115 │ │ │ │ │ └── columns: x:5 y:6 xyzw.z:7 w:8 rowid:9!null 1116 │ │ │ │ └── projections 1117 │ │ │ │ └── 8 [as=column10:10] 1118 │ │ │ └── aggregations 1119 │ │ │ ├── first-agg [as=xyzw.z:7] 1120 │ │ │ │ └── xyzw.z:7 1121 │ │ │ └── first-agg [as=column10:10] 1122 │ │ │ └── column10:10 1123 │ │ ├── scan cmulti 1124 │ │ │ └── columns: a:11!null b:12!null c:13 d:14 1125 │ │ └── filters 1126 │ │ ├── x:5 = a:11 1127 │ │ └── y:6 = b:12 1128 │ └── projections 1129 │ ├── CASE WHEN a:11 IS NULL THEN x:5 ELSE a:11 END [as=upsert_a:15] 1130 │ ├── CASE WHEN a:11 IS NULL THEN y:6 ELSE b:12 END [as=upsert_b:16] 1131 │ └── CASE WHEN a:11 IS NULL THEN column10:10 ELSE d:14 END [as=upsert_d:17] 1132 └── f-k-checks 1133 ├── f-k-checks-item: cmulti(a) -> p(p) 1134 │ └── anti-join (hash) 1135 │ ├── columns: upsert_a:18 1136 │ ├── with-scan &1 1137 │ │ ├── columns: upsert_a:18 1138 │ │ └── mapping: 1139 │ │ └── upsert_a:15 => upsert_a:18 1140 │ ├── scan p 1141 │ │ └── columns: p.p:19!null 1142 │ └── filters 1143 │ └── upsert_a:18 = p.p:19 1144 └── f-k-checks-item: cmulti(b,c) -> pq(p,q) 1145 └── anti-join (hash) 1146 ├── columns: upsert_b:21 z:22 1147 ├── with-scan &1 1148 │ ├── columns: upsert_b:21 z:22 1149 │ └── mapping: 1150 │ ├── upsert_b:16 => upsert_b:21 1151 │ └── xyzw.z:7 => z:22 1152 ├── scan pq 1153 │ └── columns: pq.p:24 q:25 1154 └── filters 1155 ├── upsert_b:21 = pq.p:24 1156 └── z:22 = q:25 1157 1158 # --------------------------------------- 1159 # Inbound FK tests with single FK column 1160 # --------------------------------------- 1161 1162 # No need to check inbound FKs since PK values never get removed by an upsert. 1163 build 1164 UPSERT INTO p VALUES (1, 1), (2, 2) 1165 ---- 1166 upsert p 1167 ├── columns: <none> 1168 ├── upsert-mapping: 1169 │ ├── column1:3 => p:1 1170 │ └── column2:4 => other:2 1171 └── values 1172 ├── columns: column1:3!null column2:4!null 1173 ├── (1, 1) 1174 └── (2, 2) 1175 1176 exec-ddl 1177 CREATE TABLE p1 (p INT PRIMARY KEY, other INT, INDEX(other)) 1178 ---- 1179 1180 exec-ddl 1181 CREATE TABLE p1c (c INT PRIMARY KEY, p INT NOT NULL DEFAULT 5 REFERENCES p1(p)) 1182 ---- 1183 1184 # No need to check inbound FKs since PK values never get removed by an upsert. 1185 build 1186 UPSERT INTO p1 VALUES (1, 1), (2, 2) 1187 ---- 1188 upsert p1 1189 ├── columns: <none> 1190 ├── canary column: 5 1191 ├── fetch columns: p:5 other:6 1192 ├── insert-mapping: 1193 │ ├── column1:3 => p:1 1194 │ └── column2:4 => other:2 1195 ├── update-mapping: 1196 │ └── column2:4 => other:2 1197 └── project 1198 ├── columns: upsert_p:7 column1:3!null column2:4!null p:5 other:6 1199 ├── left-join (hash) 1200 │ ├── columns: column1:3!null column2:4!null p:5 other:6 1201 │ ├── ensure-upsert-distinct-on 1202 │ │ ├── columns: column1:3!null column2:4!null 1203 │ │ ├── grouping columns: column1:3!null 1204 │ │ ├── values 1205 │ │ │ ├── columns: column1:3!null column2:4!null 1206 │ │ │ ├── (1, 1) 1207 │ │ │ └── (2, 2) 1208 │ │ └── aggregations 1209 │ │ └── first-agg [as=column2:4] 1210 │ │ └── column2:4 1211 │ ├── scan p1 1212 │ │ └── columns: p:5!null other:6 1213 │ └── filters 1214 │ └── column1:3 = p:5 1215 └── projections 1216 └── CASE WHEN p:5 IS NULL THEN column1:3 ELSE p:5 END [as=upsert_p:7] 1217 1218 # This statement can modify existing values of p so we need to perform the FK 1219 # check. 1220 build 1221 INSERT INTO p1 VALUES (100, 1), (200, 1) ON CONFLICT (p) DO UPDATE SET p = excluded.p + 1 1222 ---- 1223 upsert p1 1224 ├── columns: <none> 1225 ├── canary column: 5 1226 ├── fetch columns: p1.p:5 other:6 1227 ├── insert-mapping: 1228 │ ├── column1:3 => p1.p:1 1229 │ └── column2:4 => other:2 1230 ├── update-mapping: 1231 │ └── upsert_p:8 => p1.p:1 1232 ├── input binding: &1 1233 ├── project 1234 │ ├── columns: upsert_p:8!null upsert_other:9 column1:3!null column2:4!null p1.p:5 other:6 p_new:7!null 1235 │ ├── project 1236 │ │ ├── columns: p_new:7!null column1:3!null column2:4!null p1.p:5 other:6 1237 │ │ ├── left-join (hash) 1238 │ │ │ ├── columns: column1:3!null column2:4!null p1.p:5 other:6 1239 │ │ │ ├── ensure-upsert-distinct-on 1240 │ │ │ │ ├── columns: column1:3!null column2:4!null 1241 │ │ │ │ ├── grouping columns: column1:3!null 1242 │ │ │ │ ├── values 1243 │ │ │ │ │ ├── columns: column1:3!null column2:4!null 1244 │ │ │ │ │ ├── (100, 1) 1245 │ │ │ │ │ └── (200, 1) 1246 │ │ │ │ └── aggregations 1247 │ │ │ │ └── first-agg [as=column2:4] 1248 │ │ │ │ └── column2:4 1249 │ │ │ ├── scan p1 1250 │ │ │ │ └── columns: p1.p:5!null other:6 1251 │ │ │ └── filters 1252 │ │ │ └── column1:3 = p1.p:5 1253 │ │ └── projections 1254 │ │ └── column1:3 + 1 [as=p_new:7] 1255 │ └── projections 1256 │ ├── CASE WHEN p1.p:5 IS NULL THEN column1:3 ELSE p_new:7 END [as=upsert_p:8] 1257 │ └── CASE WHEN p1.p:5 IS NULL THEN column2:4 ELSE other:6 END [as=upsert_other:9] 1258 └── f-k-checks 1259 └── f-k-checks-item: p1c(p) -> p1(p) 1260 └── semi-join (hash) 1261 ├── columns: p:10 1262 ├── except 1263 │ ├── columns: p:10 1264 │ ├── left columns: p:10 1265 │ ├── right columns: upsert_p:11 1266 │ ├── with-scan &1 1267 │ │ ├── columns: p:10 1268 │ │ └── mapping: 1269 │ │ └── p1.p:5 => p:10 1270 │ └── with-scan &1 1271 │ ├── columns: upsert_p:11!null 1272 │ └── mapping: 1273 │ └── upsert_p:8 => upsert_p:11 1274 ├── scan p1c 1275 │ └── columns: p1c.p:13!null 1276 └── filters 1277 └── p:10 = p1c.p:13 1278 1279 # No need to check the inbound FK: we never modify existing values of p. 1280 build 1281 INSERT INTO p1 VALUES (100, 1), (200, 1) ON CONFLICT (p) DO UPDATE SET other = p1.other + 1 1282 ---- 1283 upsert p1 1284 ├── columns: <none> 1285 ├── canary column: 5 1286 ├── fetch columns: p:5 other:6 1287 ├── insert-mapping: 1288 │ ├── column1:3 => p:1 1289 │ └── column2:4 => other:2 1290 ├── update-mapping: 1291 │ └── upsert_other:9 => other:2 1292 └── project 1293 ├── columns: upsert_p:8 upsert_other:9 column1:3!null column2:4!null p:5 other:6 other_new:7 1294 ├── project 1295 │ ├── columns: other_new:7 column1:3!null column2:4!null p:5 other:6 1296 │ ├── left-join (hash) 1297 │ │ ├── columns: column1:3!null column2:4!null p:5 other:6 1298 │ │ ├── ensure-upsert-distinct-on 1299 │ │ │ ├── columns: column1:3!null column2:4!null 1300 │ │ │ ├── grouping columns: column1:3!null 1301 │ │ │ ├── values 1302 │ │ │ │ ├── columns: column1:3!null column2:4!null 1303 │ │ │ │ ├── (100, 1) 1304 │ │ │ │ └── (200, 1) 1305 │ │ │ └── aggregations 1306 │ │ │ └── first-agg [as=column2:4] 1307 │ │ │ └── column2:4 1308 │ │ ├── scan p1 1309 │ │ │ └── columns: p:5!null other:6 1310 │ │ └── filters 1311 │ │ └── column1:3 = p:5 1312 │ └── projections 1313 │ └── other:6 + 1 [as=other_new:7] 1314 └── projections 1315 ├── CASE WHEN p:5 IS NULL THEN column1:3 ELSE p:5 END [as=upsert_p:8] 1316 └── CASE WHEN p:5 IS NULL THEN column2:4 ELSE other_new:7 END [as=upsert_other:9] 1317 1318 # Similar tests when the FK column is not the PK. 1319 exec-ddl 1320 CREATE TABLE p2 (p INT PRIMARY KEY, fk INT UNIQUE) 1321 ---- 1322 1323 exec-ddl 1324 CREATE TABLE p2c (c INT PRIMARY KEY, fk INT REFERENCES p2(fk)) 1325 ---- 1326 1327 build 1328 UPSERT INTO p2 VALUES (1, 1), (2, 2) 1329 ---- 1330 upsert p2 1331 ├── columns: <none> 1332 ├── canary column: 5 1333 ├── fetch columns: p:5 p2.fk:6 1334 ├── insert-mapping: 1335 │ ├── column1:3 => p:1 1336 │ └── column2:4 => p2.fk:2 1337 ├── update-mapping: 1338 │ └── column2:4 => p2.fk:2 1339 ├── input binding: &1 1340 ├── project 1341 │ ├── columns: upsert_p:7 column1:3!null column2:4!null p:5 p2.fk:6 1342 │ ├── left-join (hash) 1343 │ │ ├── columns: column1:3!null column2:4!null p:5 p2.fk:6 1344 │ │ ├── ensure-upsert-distinct-on 1345 │ │ │ ├── columns: column1:3!null column2:4!null 1346 │ │ │ ├── grouping columns: column1:3!null 1347 │ │ │ ├── values 1348 │ │ │ │ ├── columns: column1:3!null column2:4!null 1349 │ │ │ │ ├── (1, 1) 1350 │ │ │ │ └── (2, 2) 1351 │ │ │ └── aggregations 1352 │ │ │ └── first-agg [as=column2:4] 1353 │ │ │ └── column2:4 1354 │ │ ├── scan p2 1355 │ │ │ └── columns: p:5!null p2.fk:6 1356 │ │ └── filters 1357 │ │ └── column1:3 = p:5 1358 │ └── projections 1359 │ └── CASE WHEN p:5 IS NULL THEN column1:3 ELSE p:5 END [as=upsert_p:7] 1360 └── f-k-checks 1361 └── f-k-checks-item: p2c(fk) -> p2(fk) 1362 └── semi-join (hash) 1363 ├── columns: fk:8 1364 ├── except 1365 │ ├── columns: fk:8 1366 │ ├── left columns: fk:8 1367 │ ├── right columns: column2:9 1368 │ ├── with-scan &1 1369 │ │ ├── columns: fk:8 1370 │ │ └── mapping: 1371 │ │ └── p2.fk:6 => fk:8 1372 │ └── with-scan &1 1373 │ ├── columns: column2:9!null 1374 │ └── mapping: 1375 │ └── column2:4 => column2:9 1376 ├── scan p2c 1377 │ └── columns: p2c.fk:11 1378 └── filters 1379 └── fk:8 = p2c.fk:11 1380 1381 # This statement never removes existing values of the fk column; FK check is 1382 # not needed. 1383 build 1384 INSERT INTO p2 VALUES (1, 1), (2, 2) ON CONFLICT (p) DO UPDATE SET p = excluded.p + 1 1385 ---- 1386 upsert p2 1387 ├── columns: <none> 1388 ├── canary column: 5 1389 ├── fetch columns: p:5 fk:6 1390 ├── insert-mapping: 1391 │ ├── column1:3 => p:1 1392 │ └── column2:4 => fk:2 1393 ├── update-mapping: 1394 │ └── upsert_p:8 => p:1 1395 └── project 1396 ├── columns: upsert_p:8!null upsert_fk:9 column1:3!null column2:4!null p:5 fk:6 p_new:7!null 1397 ├── project 1398 │ ├── columns: p_new:7!null column1:3!null column2:4!null p:5 fk:6 1399 │ ├── left-join (hash) 1400 │ │ ├── columns: column1:3!null column2:4!null p:5 fk:6 1401 │ │ ├── ensure-upsert-distinct-on 1402 │ │ │ ├── columns: column1:3!null column2:4!null 1403 │ │ │ ├── grouping columns: column1:3!null 1404 │ │ │ ├── values 1405 │ │ │ │ ├── columns: column1:3!null column2:4!null 1406 │ │ │ │ ├── (1, 1) 1407 │ │ │ │ └── (2, 2) 1408 │ │ │ └── aggregations 1409 │ │ │ └── first-agg [as=column2:4] 1410 │ │ │ └── column2:4 1411 │ │ ├── scan p2 1412 │ │ │ └── columns: p:5!null fk:6 1413 │ │ └── filters 1414 │ │ └── column1:3 = p:5 1415 │ └── projections 1416 │ └── column1:3 + 1 [as=p_new:7] 1417 └── projections 1418 ├── CASE WHEN p:5 IS NULL THEN column1:3 ELSE p_new:7 END [as=upsert_p:8] 1419 └── CASE WHEN p:5 IS NULL THEN column2:4 ELSE fk:6 END [as=upsert_fk:9] 1420 1421 # This statement can change existing values of the fk column, so the FK check 1422 # is needed. 1423 build 1424 INSERT INTO p2 VALUES (1, 1), (2, 2) ON CONFLICT (p) DO UPDATE SET fk = excluded.fk + 1 1425 ---- 1426 upsert p2 1427 ├── columns: <none> 1428 ├── canary column: 5 1429 ├── fetch columns: p:5 p2.fk:6 1430 ├── insert-mapping: 1431 │ ├── column1:3 => p:1 1432 │ └── column2:4 => p2.fk:2 1433 ├── update-mapping: 1434 │ └── upsert_fk:9 => p2.fk:2 1435 ├── input binding: &1 1436 ├── project 1437 │ ├── columns: upsert_p:8 upsert_fk:9!null column1:3!null column2:4!null p:5 p2.fk:6 fk_new:7!null 1438 │ ├── project 1439 │ │ ├── columns: fk_new:7!null column1:3!null column2:4!null p:5 p2.fk:6 1440 │ │ ├── left-join (hash) 1441 │ │ │ ├── columns: column1:3!null column2:4!null p:5 p2.fk:6 1442 │ │ │ ├── ensure-upsert-distinct-on 1443 │ │ │ │ ├── columns: column1:3!null column2:4!null 1444 │ │ │ │ ├── grouping columns: column1:3!null 1445 │ │ │ │ ├── values 1446 │ │ │ │ │ ├── columns: column1:3!null column2:4!null 1447 │ │ │ │ │ ├── (1, 1) 1448 │ │ │ │ │ └── (2, 2) 1449 │ │ │ │ └── aggregations 1450 │ │ │ │ └── first-agg [as=column2:4] 1451 │ │ │ │ └── column2:4 1452 │ │ │ ├── scan p2 1453 │ │ │ │ └── columns: p:5!null p2.fk:6 1454 │ │ │ └── filters 1455 │ │ │ └── column1:3 = p:5 1456 │ │ └── projections 1457 │ │ └── column2:4 + 1 [as=fk_new:7] 1458 │ └── projections 1459 │ ├── CASE WHEN p:5 IS NULL THEN column1:3 ELSE p:5 END [as=upsert_p:8] 1460 │ └── CASE WHEN p:5 IS NULL THEN column2:4 ELSE fk_new:7 END [as=upsert_fk:9] 1461 └── f-k-checks 1462 └── f-k-checks-item: p2c(fk) -> p2(fk) 1463 └── semi-join (hash) 1464 ├── columns: fk:10 1465 ├── except 1466 │ ├── columns: fk:10 1467 │ ├── left columns: fk:10 1468 │ ├── right columns: upsert_fk:11 1469 │ ├── with-scan &1 1470 │ │ ├── columns: fk:10 1471 │ │ └── mapping: 1472 │ │ └── p2.fk:6 => fk:10 1473 │ └── with-scan &1 1474 │ ├── columns: upsert_fk:11!null 1475 │ └── mapping: 1476 │ └── upsert_fk:9 => upsert_fk:11 1477 ├── scan p2c 1478 │ └── columns: p2c.fk:13 1479 └── filters 1480 └── fk:10 = p2c.fk:13 1481 1482 # This statement never removes existing values of the fk column; the FK check is 1483 # not needed. 1484 build 1485 INSERT INTO p2 VALUES (1, 1), (2, 2) ON CONFLICT (fk) DO UPDATE SET p = excluded.p + 1 1486 ---- 1487 upsert p2 1488 ├── columns: <none> 1489 ├── canary column: 5 1490 ├── fetch columns: p:5 fk:6 1491 ├── insert-mapping: 1492 │ ├── column1:3 => p:1 1493 │ └── column2:4 => fk:2 1494 ├── update-mapping: 1495 │ └── upsert_p:8 => p:1 1496 └── project 1497 ├── columns: upsert_p:8!null upsert_fk:9 column1:3!null column2:4!null p:5 fk:6 p_new:7!null 1498 ├── project 1499 │ ├── columns: p_new:7!null column1:3!null column2:4!null p:5 fk:6 1500 │ ├── left-join (hash) 1501 │ │ ├── columns: column1:3!null column2:4!null p:5 fk:6 1502 │ │ ├── ensure-upsert-distinct-on 1503 │ │ │ ├── columns: column1:3!null column2:4!null 1504 │ │ │ ├── grouping columns: column2:4!null 1505 │ │ │ ├── values 1506 │ │ │ │ ├── columns: column1:3!null column2:4!null 1507 │ │ │ │ ├── (1, 1) 1508 │ │ │ │ └── (2, 2) 1509 │ │ │ └── aggregations 1510 │ │ │ └── first-agg [as=column1:3] 1511 │ │ │ └── column1:3 1512 │ │ ├── scan p2 1513 │ │ │ └── columns: p:5!null fk:6 1514 │ │ └── filters 1515 │ │ └── column2:4 = fk:6 1516 │ └── projections 1517 │ └── column1:3 + 1 [as=p_new:7] 1518 └── projections 1519 ├── CASE WHEN p:5 IS NULL THEN column1:3 ELSE p_new:7 END [as=upsert_p:8] 1520 └── CASE WHEN p:5 IS NULL THEN column2:4 ELSE fk:6 END [as=upsert_fk:9] 1521 1522 build 1523 INSERT INTO p2 VALUES (1, 1), (2, 2) ON CONFLICT (fk) DO UPDATE SET fk = excluded.fk + 1 1524 ---- 1525 upsert p2 1526 ├── columns: <none> 1527 ├── canary column: 5 1528 ├── fetch columns: p:5 p2.fk:6 1529 ├── insert-mapping: 1530 │ ├── column1:3 => p:1 1531 │ └── column2:4 => p2.fk:2 1532 ├── update-mapping: 1533 │ └── upsert_fk:9 => p2.fk:2 1534 ├── input binding: &1 1535 ├── project 1536 │ ├── columns: upsert_p:8 upsert_fk:9!null column1:3!null column2:4!null p:5 p2.fk:6 fk_new:7!null 1537 │ ├── project 1538 │ │ ├── columns: fk_new:7!null column1:3!null column2:4!null p:5 p2.fk:6 1539 │ │ ├── left-join (hash) 1540 │ │ │ ├── columns: column1:3!null column2:4!null p:5 p2.fk:6 1541 │ │ │ ├── ensure-upsert-distinct-on 1542 │ │ │ │ ├── columns: column1:3!null column2:4!null 1543 │ │ │ │ ├── grouping columns: column2:4!null 1544 │ │ │ │ ├── values 1545 │ │ │ │ │ ├── columns: column1:3!null column2:4!null 1546 │ │ │ │ │ ├── (1, 1) 1547 │ │ │ │ │ └── (2, 2) 1548 │ │ │ │ └── aggregations 1549 │ │ │ │ └── first-agg [as=column1:3] 1550 │ │ │ │ └── column1:3 1551 │ │ │ ├── scan p2 1552 │ │ │ │ └── columns: p:5!null p2.fk:6 1553 │ │ │ └── filters 1554 │ │ │ └── column2:4 = p2.fk:6 1555 │ │ └── projections 1556 │ │ └── column2:4 + 1 [as=fk_new:7] 1557 │ └── projections 1558 │ ├── CASE WHEN p:5 IS NULL THEN column1:3 ELSE p:5 END [as=upsert_p:8] 1559 │ └── CASE WHEN p:5 IS NULL THEN column2:4 ELSE fk_new:7 END [as=upsert_fk:9] 1560 └── f-k-checks 1561 └── f-k-checks-item: p2c(fk) -> p2(fk) 1562 └── semi-join (hash) 1563 ├── columns: fk:10 1564 ├── except 1565 │ ├── columns: fk:10 1566 │ ├── left columns: fk:10 1567 │ ├── right columns: upsert_fk:11 1568 │ ├── with-scan &1 1569 │ │ ├── columns: fk:10 1570 │ │ └── mapping: 1571 │ │ └── p2.fk:6 => fk:10 1572 │ └── with-scan &1 1573 │ ├── columns: upsert_fk:11!null 1574 │ └── mapping: 1575 │ └── upsert_fk:9 => upsert_fk:11 1576 ├── scan p2c 1577 │ └── columns: p2c.fk:13 1578 └── filters 1579 └── fk:10 = p2c.fk:13 1580 1581 # This partial upsert never removes existing values of the fk column; the FK 1582 # check is not needed. 1583 build 1584 UPSERT INTO p2(p) VALUES (1), (2) 1585 ---- 1586 upsert p2 1587 ├── columns: <none> 1588 ├── canary column: 5 1589 ├── fetch columns: p:5 fk:6 1590 ├── insert-mapping: 1591 │ ├── column1:3 => p:1 1592 │ └── column4:4 => fk:2 1593 └── project 1594 ├── columns: upsert_p:7 upsert_fk:8 column1:3!null column4:4 p:5 fk:6 1595 ├── left-join (hash) 1596 │ ├── columns: column1:3!null column4:4 p:5 fk:6 1597 │ ├── ensure-upsert-distinct-on 1598 │ │ ├── columns: column1:3!null column4:4 1599 │ │ ├── grouping columns: column1:3!null 1600 │ │ ├── project 1601 │ │ │ ├── columns: column4:4 column1:3!null 1602 │ │ │ ├── values 1603 │ │ │ │ ├── columns: column1:3!null 1604 │ │ │ │ ├── (1,) 1605 │ │ │ │ └── (2,) 1606 │ │ │ └── projections 1607 │ │ │ └── NULL::INT8 [as=column4:4] 1608 │ │ └── aggregations 1609 │ │ └── first-agg [as=column4:4] 1610 │ │ └── column4:4 1611 │ ├── scan p2 1612 │ │ └── columns: p:5!null fk:6 1613 │ └── filters 1614 │ └── column1:3 = p:5 1615 └── projections 1616 ├── CASE WHEN p:5 IS NULL THEN column1:3 ELSE p:5 END [as=upsert_p:7] 1617 └── CASE WHEN p:5 IS NULL THEN column4:4 ELSE fk:6 END [as=upsert_fk:8] 1618 1619 # ------------------------------------------ 1620 # Inbound FK tests with multiple FK columns 1621 # ------------------------------------------ 1622 1623 build 1624 UPSERT INTO pq VALUES (1, 1, 1, 1), (2, 2, 2, 2) 1625 ---- 1626 upsert pq 1627 ├── columns: <none> 1628 ├── canary column: 9 1629 ├── fetch columns: k:9 pq.p:10 pq.q:11 pq.other:12 1630 ├── insert-mapping: 1631 │ ├── column1:5 => k:1 1632 │ ├── column2:6 => pq.p:2 1633 │ ├── column3:7 => pq.q:3 1634 │ └── column4:8 => pq.other:4 1635 ├── update-mapping: 1636 │ ├── column2:6 => pq.p:2 1637 │ ├── column3:7 => pq.q:3 1638 │ └── column4:8 => pq.other:4 1639 ├── input binding: &1 1640 ├── project 1641 │ ├── columns: upsert_k:13 column1:5!null column2:6!null column3:7!null column4:8!null k:9 pq.p:10 pq.q:11 pq.other:12 1642 │ ├── left-join (hash) 1643 │ │ ├── columns: column1:5!null column2:6!null column3:7!null column4:8!null k:9 pq.p:10 pq.q:11 pq.other:12 1644 │ │ ├── ensure-upsert-distinct-on 1645 │ │ │ ├── columns: column1:5!null column2:6!null column3:7!null column4:8!null 1646 │ │ │ ├── grouping columns: column1:5!null 1647 │ │ │ ├── values 1648 │ │ │ │ ├── columns: column1:5!null column2:6!null column3:7!null column4:8!null 1649 │ │ │ │ ├── (1, 1, 1, 1) 1650 │ │ │ │ └── (2, 2, 2, 2) 1651 │ │ │ └── aggregations 1652 │ │ │ ├── first-agg [as=column2:6] 1653 │ │ │ │ └── column2:6 1654 │ │ │ ├── first-agg [as=column3:7] 1655 │ │ │ │ └── column3:7 1656 │ │ │ └── first-agg [as=column4:8] 1657 │ │ │ └── column4:8 1658 │ │ ├── scan pq 1659 │ │ │ └── columns: k:9!null pq.p:10 pq.q:11 pq.other:12 1660 │ │ └── filters 1661 │ │ └── column1:5 = k:9 1662 │ └── projections 1663 │ └── CASE WHEN k:9 IS NULL THEN column1:5 ELSE k:9 END [as=upsert_k:13] 1664 └── f-k-checks 1665 ├── f-k-checks-item: cpq(p,q) -> pq(p,q) 1666 │ └── semi-join (hash) 1667 │ ├── columns: p:14 q:15 1668 │ ├── except 1669 │ │ ├── columns: p:14 q:15 1670 │ │ ├── left columns: p:14 q:15 1671 │ │ ├── right columns: column2:16 column3:17 1672 │ │ ├── with-scan &1 1673 │ │ │ ├── columns: p:14 q:15 1674 │ │ │ └── mapping: 1675 │ │ │ ├── pq.p:10 => p:14 1676 │ │ │ └── pq.q:11 => q:15 1677 │ │ └── with-scan &1 1678 │ │ ├── columns: column2:16!null column3:17!null 1679 │ │ └── mapping: 1680 │ │ ├── column2:6 => column2:16 1681 │ │ └── column3:7 => column3:17 1682 │ ├── scan cpq 1683 │ │ └── columns: cpq.p:19 cpq.q:20 1684 │ └── filters 1685 │ ├── p:14 = cpq.p:19 1686 │ └── q:15 = cpq.q:20 1687 └── f-k-checks-item: cmulti(b,c) -> pq(p,q) 1688 └── semi-join (hash) 1689 ├── columns: p:22 q:23 1690 ├── except 1691 │ ├── columns: p:22 q:23 1692 │ ├── left columns: p:22 q:23 1693 │ ├── right columns: column2:24 column3:25 1694 │ ├── with-scan &1 1695 │ │ ├── columns: p:22 q:23 1696 │ │ └── mapping: 1697 │ │ ├── pq.p:10 => p:22 1698 │ │ └── pq.q:11 => q:23 1699 │ └── with-scan &1 1700 │ ├── columns: column2:24!null column3:25!null 1701 │ └── mapping: 1702 │ ├── column2:6 => column2:24 1703 │ └── column3:7 => column3:25 1704 ├── scan cmulti 1705 │ └── columns: b:27!null cmulti.c:28 1706 └── filters 1707 ├── p:22 = b:27 1708 └── q:23 = cmulti.c:28 1709 1710 # Partial UPSERT doesn't remove (p,q) values; FK check not needed. 1711 build 1712 UPSERT INTO pq (k) VALUES (1), (2) 1713 ---- 1714 upsert pq 1715 ├── columns: <none> 1716 ├── canary column: 7 1717 ├── fetch columns: k:7 p:8 q:9 other:10 1718 ├── insert-mapping: 1719 │ ├── column1:5 => k:1 1720 │ ├── column6:6 => p:2 1721 │ ├── column6:6 => q:3 1722 │ └── column6:6 => other:4 1723 └── project 1724 ├── columns: upsert_k:11 upsert_p:12 upsert_q:13 upsert_other:14 column1:5!null column6:6 k:7 p:8 q:9 other:10 1725 ├── left-join (hash) 1726 │ ├── columns: column1:5!null column6:6 k:7 p:8 q:9 other:10 1727 │ ├── ensure-upsert-distinct-on 1728 │ │ ├── columns: column1:5!null column6:6 1729 │ │ ├── grouping columns: column1:5!null 1730 │ │ ├── project 1731 │ │ │ ├── columns: column6:6 column1:5!null 1732 │ │ │ ├── values 1733 │ │ │ │ ├── columns: column1:5!null 1734 │ │ │ │ ├── (1,) 1735 │ │ │ │ └── (2,) 1736 │ │ │ └── projections 1737 │ │ │ └── NULL::INT8 [as=column6:6] 1738 │ │ └── aggregations 1739 │ │ └── first-agg [as=column6:6] 1740 │ │ └── column6:6 1741 │ ├── scan pq 1742 │ │ └── columns: k:7!null p:8 q:9 other:10 1743 │ └── filters 1744 │ └── column1:5 = k:7 1745 └── projections 1746 ├── CASE WHEN k:7 IS NULL THEN column1:5 ELSE k:7 END [as=upsert_k:11] 1747 ├── CASE WHEN k:7 IS NULL THEN column6:6 ELSE p:8 END [as=upsert_p:12] 1748 ├── CASE WHEN k:7 IS NULL THEN column6:6 ELSE q:9 END [as=upsert_q:13] 1749 └── CASE WHEN k:7 IS NULL THEN column6:6 ELSE other:10 END [as=upsert_other:14] 1750 1751 build 1752 UPSERT INTO pq (k,q) VALUES (1, 1), (2, 2) 1753 ---- 1754 upsert pq 1755 ├── columns: <none> 1756 ├── canary column: 8 1757 ├── fetch columns: k:8 pq.p:9 pq.q:10 pq.other:11 1758 ├── insert-mapping: 1759 │ ├── column1:5 => k:1 1760 │ ├── column7:7 => pq.p:2 1761 │ ├── column2:6 => pq.q:3 1762 │ └── column7:7 => pq.other:4 1763 ├── update-mapping: 1764 │ └── column2:6 => pq.q:3 1765 ├── input binding: &1 1766 ├── project 1767 │ ├── columns: upsert_k:12 upsert_p:13 upsert_other:14 column1:5!null column2:6!null column7:7 k:8 pq.p:9 pq.q:10 pq.other:11 1768 │ ├── left-join (hash) 1769 │ │ ├── columns: column1:5!null column2:6!null column7:7 k:8 pq.p:9 pq.q:10 pq.other:11 1770 │ │ ├── ensure-upsert-distinct-on 1771 │ │ │ ├── columns: column1:5!null column2:6!null column7:7 1772 │ │ │ ├── grouping columns: column1:5!null 1773 │ │ │ ├── project 1774 │ │ │ │ ├── columns: column7:7 column1:5!null column2:6!null 1775 │ │ │ │ ├── values 1776 │ │ │ │ │ ├── columns: column1:5!null column2:6!null 1777 │ │ │ │ │ ├── (1, 1) 1778 │ │ │ │ │ └── (2, 2) 1779 │ │ │ │ └── projections 1780 │ │ │ │ └── NULL::INT8 [as=column7:7] 1781 │ │ │ └── aggregations 1782 │ │ │ ├── first-agg [as=column2:6] 1783 │ │ │ │ └── column2:6 1784 │ │ │ └── first-agg [as=column7:7] 1785 │ │ │ └── column7:7 1786 │ │ ├── scan pq 1787 │ │ │ └── columns: k:8!null pq.p:9 pq.q:10 pq.other:11 1788 │ │ └── filters 1789 │ │ └── column1:5 = k:8 1790 │ └── projections 1791 │ ├── CASE WHEN k:8 IS NULL THEN column1:5 ELSE k:8 END [as=upsert_k:12] 1792 │ ├── CASE WHEN k:8 IS NULL THEN column7:7 ELSE pq.p:9 END [as=upsert_p:13] 1793 │ └── CASE WHEN k:8 IS NULL THEN column7:7 ELSE pq.other:11 END [as=upsert_other:14] 1794 └── f-k-checks 1795 ├── f-k-checks-item: cpq(p,q) -> pq(p,q) 1796 │ └── semi-join (hash) 1797 │ ├── columns: p:15 q:16 1798 │ ├── except 1799 │ │ ├── columns: p:15 q:16 1800 │ │ ├── left columns: p:15 q:16 1801 │ │ ├── right columns: upsert_p:17 column2:18 1802 │ │ ├── with-scan &1 1803 │ │ │ ├── columns: p:15 q:16 1804 │ │ │ └── mapping: 1805 │ │ │ ├── pq.p:9 => p:15 1806 │ │ │ └── pq.q:10 => q:16 1807 │ │ └── with-scan &1 1808 │ │ ├── columns: upsert_p:17 column2:18!null 1809 │ │ └── mapping: 1810 │ │ ├── upsert_p:13 => upsert_p:17 1811 │ │ └── column2:6 => column2:18 1812 │ ├── scan cpq 1813 │ │ └── columns: cpq.p:20 cpq.q:21 1814 │ └── filters 1815 │ ├── p:15 = cpq.p:20 1816 │ └── q:16 = cpq.q:21 1817 └── f-k-checks-item: cmulti(b,c) -> pq(p,q) 1818 └── semi-join (hash) 1819 ├── columns: p:23 q:24 1820 ├── except 1821 │ ├── columns: p:23 q:24 1822 │ ├── left columns: p:23 q:24 1823 │ ├── right columns: upsert_p:25 column2:26 1824 │ ├── with-scan &1 1825 │ │ ├── columns: p:23 q:24 1826 │ │ └── mapping: 1827 │ │ ├── pq.p:9 => p:23 1828 │ │ └── pq.q:10 => q:24 1829 │ └── with-scan &1 1830 │ ├── columns: upsert_p:25 column2:26!null 1831 │ └── mapping: 1832 │ ├── upsert_p:13 => upsert_p:25 1833 │ └── column2:6 => column2:26 1834 ├── scan cmulti 1835 │ └── columns: b:28!null cmulti.c:29 1836 └── filters 1837 ├── p:23 = b:28 1838 └── q:24 = cmulti.c:29 1839 1840 # Statement doesn't remove (p,q) values; FK check not needed. 1841 build 1842 INSERT INTO pq VALUES (1, 1, 1, 1), (2, 2, 2, 2) ON CONFLICT (p,q) DO UPDATE SET k = pq.k + 1 1843 ---- 1844 upsert pq 1845 ├── columns: <none> 1846 ├── canary column: 9 1847 ├── fetch columns: k:9 p:10 q:11 other:12 1848 ├── insert-mapping: 1849 │ ├── column1:5 => k:1 1850 │ ├── column2:6 => p:2 1851 │ ├── column3:7 => q:3 1852 │ └── column4:8 => other:4 1853 ├── update-mapping: 1854 │ └── upsert_k:14 => k:1 1855 └── project 1856 ├── columns: upsert_k:14 upsert_p:15 upsert_q:16 upsert_other:17 column1:5!null column2:6!null column3:7!null column4:8!null k:9 p:10 q:11 other:12 k_new:13 1857 ├── project 1858 │ ├── columns: k_new:13 column1:5!null column2:6!null column3:7!null column4:8!null k:9 p:10 q:11 other:12 1859 │ ├── left-join (hash) 1860 │ │ ├── columns: column1:5!null column2:6!null column3:7!null column4:8!null k:9 p:10 q:11 other:12 1861 │ │ ├── ensure-upsert-distinct-on 1862 │ │ │ ├── columns: column1:5!null column2:6!null column3:7!null column4:8!null 1863 │ │ │ ├── grouping columns: column2:6!null column3:7!null 1864 │ │ │ ├── values 1865 │ │ │ │ ├── columns: column1:5!null column2:6!null column3:7!null column4:8!null 1866 │ │ │ │ ├── (1, 1, 1, 1) 1867 │ │ │ │ └── (2, 2, 2, 2) 1868 │ │ │ └── aggregations 1869 │ │ │ ├── first-agg [as=column1:5] 1870 │ │ │ │ └── column1:5 1871 │ │ │ └── first-agg [as=column4:8] 1872 │ │ │ └── column4:8 1873 │ │ ├── scan pq 1874 │ │ │ └── columns: k:9!null p:10 q:11 other:12 1875 │ │ └── filters 1876 │ │ ├── column2:6 = p:10 1877 │ │ └── column3:7 = q:11 1878 │ └── projections 1879 │ └── k:9 + 1 [as=k_new:13] 1880 └── projections 1881 ├── CASE WHEN k:9 IS NULL THEN column1:5 ELSE k_new:13 END [as=upsert_k:14] 1882 ├── CASE WHEN k:9 IS NULL THEN column2:6 ELSE p:10 END [as=upsert_p:15] 1883 ├── CASE WHEN k:9 IS NULL THEN column3:7 ELSE q:11 END [as=upsert_q:16] 1884 └── CASE WHEN k:9 IS NULL THEN column4:8 ELSE other:12 END [as=upsert_other:17] 1885 1886 build 1887 INSERT INTO pq VALUES (1, 1, 1, 1), (2, 2, 2, 2) ON CONFLICT (p,q) DO UPDATE SET p = pq.p + 1 1888 ---- 1889 upsert pq 1890 ├── columns: <none> 1891 ├── canary column: 9 1892 ├── fetch columns: k:9 pq.p:10 pq.q:11 pq.other:12 1893 ├── insert-mapping: 1894 │ ├── column1:5 => k:1 1895 │ ├── column2:6 => pq.p:2 1896 │ ├── column3:7 => pq.q:3 1897 │ └── column4:8 => pq.other:4 1898 ├── update-mapping: 1899 │ └── upsert_p:15 => pq.p:2 1900 ├── input binding: &1 1901 ├── project 1902 │ ├── columns: upsert_k:14 upsert_p:15 upsert_q:16 upsert_other:17 column1:5!null column2:6!null column3:7!null column4:8!null k:9 pq.p:10 pq.q:11 pq.other:12 p_new:13 1903 │ ├── project 1904 │ │ ├── columns: p_new:13 column1:5!null column2:6!null column3:7!null column4:8!null k:9 pq.p:10 pq.q:11 pq.other:12 1905 │ │ ├── left-join (hash) 1906 │ │ │ ├── columns: column1:5!null column2:6!null column3:7!null column4:8!null k:9 pq.p:10 pq.q:11 pq.other:12 1907 │ │ │ ├── ensure-upsert-distinct-on 1908 │ │ │ │ ├── columns: column1:5!null column2:6!null column3:7!null column4:8!null 1909 │ │ │ │ ├── grouping columns: column2:6!null column3:7!null 1910 │ │ │ │ ├── values 1911 │ │ │ │ │ ├── columns: column1:5!null column2:6!null column3:7!null column4:8!null 1912 │ │ │ │ │ ├── (1, 1, 1, 1) 1913 │ │ │ │ │ └── (2, 2, 2, 2) 1914 │ │ │ │ └── aggregations 1915 │ │ │ │ ├── first-agg [as=column1:5] 1916 │ │ │ │ │ └── column1:5 1917 │ │ │ │ └── first-agg [as=column4:8] 1918 │ │ │ │ └── column4:8 1919 │ │ │ ├── scan pq 1920 │ │ │ │ └── columns: k:9!null pq.p:10 pq.q:11 pq.other:12 1921 │ │ │ └── filters 1922 │ │ │ ├── column2:6 = pq.p:10 1923 │ │ │ └── column3:7 = pq.q:11 1924 │ │ └── projections 1925 │ │ └── pq.p:10 + 1 [as=p_new:13] 1926 │ └── projections 1927 │ ├── CASE WHEN k:9 IS NULL THEN column1:5 ELSE k:9 END [as=upsert_k:14] 1928 │ ├── CASE WHEN k:9 IS NULL THEN column2:6 ELSE p_new:13 END [as=upsert_p:15] 1929 │ ├── CASE WHEN k:9 IS NULL THEN column3:7 ELSE pq.q:11 END [as=upsert_q:16] 1930 │ └── CASE WHEN k:9 IS NULL THEN column4:8 ELSE pq.other:12 END [as=upsert_other:17] 1931 └── f-k-checks 1932 ├── f-k-checks-item: cpq(p,q) -> pq(p,q) 1933 │ └── semi-join (hash) 1934 │ ├── columns: p:18 q:19 1935 │ ├── except 1936 │ │ ├── columns: p:18 q:19 1937 │ │ ├── left columns: p:18 q:19 1938 │ │ ├── right columns: upsert_p:20 upsert_q:21 1939 │ │ ├── with-scan &1 1940 │ │ │ ├── columns: p:18 q:19 1941 │ │ │ └── mapping: 1942 │ │ │ ├── pq.p:10 => p:18 1943 │ │ │ └── pq.q:11 => q:19 1944 │ │ └── with-scan &1 1945 │ │ ├── columns: upsert_p:20 upsert_q:21 1946 │ │ └── mapping: 1947 │ │ ├── upsert_p:15 => upsert_p:20 1948 │ │ └── upsert_q:16 => upsert_q:21 1949 │ ├── scan cpq 1950 │ │ └── columns: cpq.p:23 cpq.q:24 1951 │ └── filters 1952 │ ├── p:18 = cpq.p:23 1953 │ └── q:19 = cpq.q:24 1954 └── f-k-checks-item: cmulti(b,c) -> pq(p,q) 1955 └── semi-join (hash) 1956 ├── columns: p:26 q:27 1957 ├── except 1958 │ ├── columns: p:26 q:27 1959 │ ├── left columns: p:26 q:27 1960 │ ├── right columns: upsert_p:28 upsert_q:29 1961 │ ├── with-scan &1 1962 │ │ ├── columns: p:26 q:27 1963 │ │ └── mapping: 1964 │ │ ├── pq.p:10 => p:26 1965 │ │ └── pq.q:11 => q:27 1966 │ └── with-scan &1 1967 │ ├── columns: upsert_p:28 upsert_q:29 1968 │ └── mapping: 1969 │ ├── upsert_p:15 => upsert_p:28 1970 │ └── upsert_q:16 => upsert_q:29 1971 ├── scan cmulti 1972 │ └── columns: b:31!null cmulti.c:32 1973 └── filters 1974 ├── p:26 = b:31 1975 └── q:27 = cmulti.c:32 1976 1977 # Statement never removes (p,q) values; FK check not needed. 1978 build 1979 INSERT INTO pq VALUES (1, 1, 1, 1), (2, 2, 2, 2) ON CONFLICT (k) DO UPDATE SET other = 5 1980 ---- 1981 upsert pq 1982 ├── columns: <none> 1983 ├── canary column: 9 1984 ├── fetch columns: k:9 p:10 q:11 other:12 1985 ├── insert-mapping: 1986 │ ├── column1:5 => k:1 1987 │ ├── column2:6 => p:2 1988 │ ├── column3:7 => q:3 1989 │ └── column4:8 => other:4 1990 ├── update-mapping: 1991 │ └── upsert_other:17 => other:4 1992 └── project 1993 ├── columns: upsert_k:14 upsert_p:15 upsert_q:16 upsert_other:17!null column1:5!null column2:6!null column3:7!null column4:8!null k:9 p:10 q:11 other:12 other_new:13!null 1994 ├── project 1995 │ ├── columns: other_new:13!null column1:5!null column2:6!null column3:7!null column4:8!null k:9 p:10 q:11 other:12 1996 │ ├── left-join (hash) 1997 │ │ ├── columns: column1:5!null column2:6!null column3:7!null column4:8!null k:9 p:10 q:11 other:12 1998 │ │ ├── ensure-upsert-distinct-on 1999 │ │ │ ├── columns: column1:5!null column2:6!null column3:7!null column4:8!null 2000 │ │ │ ├── grouping columns: column1:5!null 2001 │ │ │ ├── values 2002 │ │ │ │ ├── columns: column1:5!null column2:6!null column3:7!null column4:8!null 2003 │ │ │ │ ├── (1, 1, 1, 1) 2004 │ │ │ │ └── (2, 2, 2, 2) 2005 │ │ │ └── aggregations 2006 │ │ │ ├── first-agg [as=column2:6] 2007 │ │ │ │ └── column2:6 2008 │ │ │ ├── first-agg [as=column3:7] 2009 │ │ │ │ └── column3:7 2010 │ │ │ └── first-agg [as=column4:8] 2011 │ │ │ └── column4:8 2012 │ │ ├── scan pq 2013 │ │ │ └── columns: k:9!null p:10 q:11 other:12 2014 │ │ └── filters 2015 │ │ └── column1:5 = k:9 2016 │ └── projections 2017 │ └── 5 [as=other_new:13] 2018 └── projections 2019 ├── CASE WHEN k:9 IS NULL THEN column1:5 ELSE k:9 END [as=upsert_k:14] 2020 ├── CASE WHEN k:9 IS NULL THEN column2:6 ELSE p:10 END [as=upsert_p:15] 2021 ├── CASE WHEN k:9 IS NULL THEN column3:7 ELSE q:11 END [as=upsert_q:16] 2022 └── CASE WHEN k:9 IS NULL THEN column4:8 ELSE other_new:13 END [as=upsert_other:17] 2023 2024 build 2025 INSERT INTO pq VALUES (1, 1, 1, 1), (2, 2, 2, 2) ON CONFLICT (k) DO UPDATE SET q = 5 2026 ---- 2027 upsert pq 2028 ├── columns: <none> 2029 ├── canary column: 9 2030 ├── fetch columns: k:9 pq.p:10 pq.q:11 pq.other:12 2031 ├── insert-mapping: 2032 │ ├── column1:5 => k:1 2033 │ ├── column2:6 => pq.p:2 2034 │ ├── column3:7 => pq.q:3 2035 │ └── column4:8 => pq.other:4 2036 ├── update-mapping: 2037 │ └── upsert_q:16 => pq.q:3 2038 ├── input binding: &1 2039 ├── project 2040 │ ├── columns: upsert_k:14 upsert_p:15 upsert_q:16!null upsert_other:17 column1:5!null column2:6!null column3:7!null column4:8!null k:9 pq.p:10 pq.q:11 pq.other:12 q_new:13!null 2041 │ ├── project 2042 │ │ ├── columns: q_new:13!null column1:5!null column2:6!null column3:7!null column4:8!null k:9 pq.p:10 pq.q:11 pq.other:12 2043 │ │ ├── left-join (hash) 2044 │ │ │ ├── columns: column1:5!null column2:6!null column3:7!null column4:8!null k:9 pq.p:10 pq.q:11 pq.other:12 2045 │ │ │ ├── ensure-upsert-distinct-on 2046 │ │ │ │ ├── columns: column1:5!null column2:6!null column3:7!null column4:8!null 2047 │ │ │ │ ├── grouping columns: column1:5!null 2048 │ │ │ │ ├── values 2049 │ │ │ │ │ ├── columns: column1:5!null column2:6!null column3:7!null column4:8!null 2050 │ │ │ │ │ ├── (1, 1, 1, 1) 2051 │ │ │ │ │ └── (2, 2, 2, 2) 2052 │ │ │ │ └── aggregations 2053 │ │ │ │ ├── first-agg [as=column2:6] 2054 │ │ │ │ │ └── column2:6 2055 │ │ │ │ ├── first-agg [as=column3:7] 2056 │ │ │ │ │ └── column3:7 2057 │ │ │ │ └── first-agg [as=column4:8] 2058 │ │ │ │ └── column4:8 2059 │ │ │ ├── scan pq 2060 │ │ │ │ └── columns: k:9!null pq.p:10 pq.q:11 pq.other:12 2061 │ │ │ └── filters 2062 │ │ │ └── column1:5 = k:9 2063 │ │ └── projections 2064 │ │ └── 5 [as=q_new:13] 2065 │ └── projections 2066 │ ├── CASE WHEN k:9 IS NULL THEN column1:5 ELSE k:9 END [as=upsert_k:14] 2067 │ ├── CASE WHEN k:9 IS NULL THEN column2:6 ELSE pq.p:10 END [as=upsert_p:15] 2068 │ ├── CASE WHEN k:9 IS NULL THEN column3:7 ELSE q_new:13 END [as=upsert_q:16] 2069 │ └── CASE WHEN k:9 IS NULL THEN column4:8 ELSE pq.other:12 END [as=upsert_other:17] 2070 └── f-k-checks 2071 ├── f-k-checks-item: cpq(p,q) -> pq(p,q) 2072 │ └── semi-join (hash) 2073 │ ├── columns: p:18 q:19 2074 │ ├── except 2075 │ │ ├── columns: p:18 q:19 2076 │ │ ├── left columns: p:18 q:19 2077 │ │ ├── right columns: upsert_p:20 upsert_q:21 2078 │ │ ├── with-scan &1 2079 │ │ │ ├── columns: p:18 q:19 2080 │ │ │ └── mapping: 2081 │ │ │ ├── pq.p:10 => p:18 2082 │ │ │ └── pq.q:11 => q:19 2083 │ │ └── with-scan &1 2084 │ │ ├── columns: upsert_p:20 upsert_q:21!null 2085 │ │ └── mapping: 2086 │ │ ├── upsert_p:15 => upsert_p:20 2087 │ │ └── upsert_q:16 => upsert_q:21 2088 │ ├── scan cpq 2089 │ │ └── columns: cpq.p:23 cpq.q:24 2090 │ └── filters 2091 │ ├── p:18 = cpq.p:23 2092 │ └── q:19 = cpq.q:24 2093 └── f-k-checks-item: cmulti(b,c) -> pq(p,q) 2094 └── semi-join (hash) 2095 ├── columns: p:26 q:27 2096 ├── except 2097 │ ├── columns: p:26 q:27 2098 │ ├── left columns: p:26 q:27 2099 │ ├── right columns: upsert_p:28 upsert_q:29 2100 │ ├── with-scan &1 2101 │ │ ├── columns: p:26 q:27 2102 │ │ └── mapping: 2103 │ │ ├── pq.p:10 => p:26 2104 │ │ └── pq.q:11 => q:27 2105 │ └── with-scan &1 2106 │ ├── columns: upsert_p:28 upsert_q:29!null 2107 │ └── mapping: 2108 │ ├── upsert_p:15 => upsert_p:28 2109 │ └── upsert_q:16 => upsert_q:29 2110 ├── scan cmulti 2111 │ └── columns: b:31!null cmulti.c:32 2112 └── filters 2113 ├── p:26 = b:31 2114 └── q:27 = cmulti.c:32 2115 2116 # ------------------------------------- 2117 # Inbound + outbound combination tests 2118 # ------------------------------------- 2119 2120 exec-ddl 2121 CREATE TABLE tab1 ( 2122 a INT PRIMARY KEY, 2123 b INT UNIQUE 2124 ) 2125 ---- 2126 2127 exec-ddl 2128 CREATE TABLE tab2 ( 2129 c INT PRIMARY KEY, 2130 d INT REFERENCES tab1(b), 2131 e INT UNIQUE 2132 ) 2133 ---- 2134 2135 exec-ddl 2136 CREATE TABLE tab3 ( 2137 f INT PRIMARY KEY, 2138 g INT REFERENCES tab2(e) 2139 ) 2140 ---- 2141 2142 build 2143 UPSERT INTO tab2 VALUES (1,NULL,NULL), (2,2,2) 2144 ---- 2145 upsert tab2 2146 ├── columns: <none> 2147 ├── canary column: 7 2148 ├── fetch columns: c:7 d:8 tab2.e:9 2149 ├── insert-mapping: 2150 │ ├── column1:4 => c:1 2151 │ ├── column2:5 => d:2 2152 │ └── column3:6 => tab2.e:3 2153 ├── update-mapping: 2154 │ ├── column2:5 => d:2 2155 │ └── column3:6 => tab2.e:3 2156 ├── input binding: &1 2157 ├── project 2158 │ ├── columns: upsert_c:10 column1:4!null column2:5 column3:6 c:7 d:8 tab2.e:9 2159 │ ├── left-join (hash) 2160 │ │ ├── columns: column1:4!null column2:5 column3:6 c:7 d:8 tab2.e:9 2161 │ │ ├── ensure-upsert-distinct-on 2162 │ │ │ ├── columns: column1:4!null column2:5 column3:6 2163 │ │ │ ├── grouping columns: column1:4!null 2164 │ │ │ ├── values 2165 │ │ │ │ ├── columns: column1:4!null column2:5 column3:6 2166 │ │ │ │ ├── (1, NULL::INT8, NULL::INT8) 2167 │ │ │ │ └── (2, 2, 2) 2168 │ │ │ └── aggregations 2169 │ │ │ ├── first-agg [as=column2:5] 2170 │ │ │ │ └── column2:5 2171 │ │ │ └── first-agg [as=column3:6] 2172 │ │ │ └── column3:6 2173 │ │ ├── scan tab2 2174 │ │ │ └── columns: c:7!null d:8 tab2.e:9 2175 │ │ └── filters 2176 │ │ └── column1:4 = c:7 2177 │ └── projections 2178 │ └── CASE WHEN c:7 IS NULL THEN column1:4 ELSE c:7 END [as=upsert_c:10] 2179 └── f-k-checks 2180 ├── f-k-checks-item: tab2(d) -> tab1(b) 2181 │ └── anti-join (hash) 2182 │ ├── columns: column2:11!null 2183 │ ├── select 2184 │ │ ├── columns: column2:11!null 2185 │ │ ├── with-scan &1 2186 │ │ │ ├── columns: column2:11 2187 │ │ │ └── mapping: 2188 │ │ │ └── column2:5 => column2:11 2189 │ │ └── filters 2190 │ │ └── column2:11 IS NOT NULL 2191 │ ├── scan tab1 2192 │ │ └── columns: b:13 2193 │ └── filters 2194 │ └── column2:11 = b:13 2195 └── f-k-checks-item: tab3(g) -> tab2(e) 2196 └── semi-join (hash) 2197 ├── columns: e:14 2198 ├── except 2199 │ ├── columns: e:14 2200 │ ├── left columns: e:14 2201 │ ├── right columns: column3:15 2202 │ ├── with-scan &1 2203 │ │ ├── columns: e:14 2204 │ │ └── mapping: 2205 │ │ └── tab2.e:9 => e:14 2206 │ └── with-scan &1 2207 │ ├── columns: column3:15 2208 │ └── mapping: 2209 │ └── column3:6 => column3:15 2210 ├── scan tab3 2211 │ └── columns: g:17 2212 └── filters 2213 └── e:14 = g:17 2214 2215 build 2216 INSERT INTO tab2 VALUES (1,1,1) ON CONFLICT (c) DO UPDATE SET e = tab2.e + 1 2217 ---- 2218 upsert tab2 2219 ├── columns: <none> 2220 ├── canary column: 7 2221 ├── fetch columns: c:7 d:8 tab2.e:9 2222 ├── insert-mapping: 2223 │ ├── column1:4 => c:1 2224 │ ├── column2:5 => d:2 2225 │ └── column3:6 => tab2.e:3 2226 ├── update-mapping: 2227 │ └── upsert_e:13 => tab2.e:3 2228 ├── input binding: &1 2229 ├── project 2230 │ ├── columns: upsert_c:11 upsert_d:12 upsert_e:13 column1:4!null column2:5!null column3:6!null c:7 d:8 tab2.e:9 e_new:10 2231 │ ├── project 2232 │ │ ├── columns: e_new:10 column1:4!null column2:5!null column3:6!null c:7 d:8 tab2.e:9 2233 │ │ ├── left-join (hash) 2234 │ │ │ ├── columns: column1:4!null column2:5!null column3:6!null c:7 d:8 tab2.e:9 2235 │ │ │ ├── ensure-upsert-distinct-on 2236 │ │ │ │ ├── columns: column1:4!null column2:5!null column3:6!null 2237 │ │ │ │ ├── grouping columns: column1:4!null 2238 │ │ │ │ ├── values 2239 │ │ │ │ │ ├── columns: column1:4!null column2:5!null column3:6!null 2240 │ │ │ │ │ └── (1, 1, 1) 2241 │ │ │ │ └── aggregations 2242 │ │ │ │ ├── first-agg [as=column2:5] 2243 │ │ │ │ │ └── column2:5 2244 │ │ │ │ └── first-agg [as=column3:6] 2245 │ │ │ │ └── column3:6 2246 │ │ │ ├── scan tab2 2247 │ │ │ │ └── columns: c:7!null d:8 tab2.e:9 2248 │ │ │ └── filters 2249 │ │ │ └── column1:4 = c:7 2250 │ │ └── projections 2251 │ │ └── tab2.e:9 + 1 [as=e_new:10] 2252 │ └── projections 2253 │ ├── CASE WHEN c:7 IS NULL THEN column1:4 ELSE c:7 END [as=upsert_c:11] 2254 │ ├── CASE WHEN c:7 IS NULL THEN column2:5 ELSE d:8 END [as=upsert_d:12] 2255 │ └── CASE WHEN c:7 IS NULL THEN column3:6 ELSE e_new:10 END [as=upsert_e:13] 2256 └── f-k-checks 2257 ├── f-k-checks-item: tab2(d) -> tab1(b) 2258 │ └── anti-join (hash) 2259 │ ├── columns: upsert_d:14!null 2260 │ ├── select 2261 │ │ ├── columns: upsert_d:14!null 2262 │ │ ├── with-scan &1 2263 │ │ │ ├── columns: upsert_d:14 2264 │ │ │ └── mapping: 2265 │ │ │ └── upsert_d:12 => upsert_d:14 2266 │ │ └── filters 2267 │ │ └── upsert_d:14 IS NOT NULL 2268 │ ├── scan tab1 2269 │ │ └── columns: b:16 2270 │ └── filters 2271 │ └── upsert_d:14 = b:16 2272 └── f-k-checks-item: tab3(g) -> tab2(e) 2273 └── semi-join (hash) 2274 ├── columns: e:17 2275 ├── except 2276 │ ├── columns: e:17 2277 │ ├── left columns: e:17 2278 │ ├── right columns: upsert_e:18 2279 │ ├── with-scan &1 2280 │ │ ├── columns: e:17 2281 │ │ └── mapping: 2282 │ │ └── tab2.e:9 => e:17 2283 │ └── with-scan &1 2284 │ ├── columns: upsert_e:18 2285 │ └── mapping: 2286 │ └── upsert_e:13 => upsert_e:18 2287 ├── scan tab3 2288 │ └── columns: g:20 2289 └── filters 2290 └── e:17 = g:20 2291 2292 # Statement never removes values from e column; the inbound check is not necessary. 2293 build 2294 INSERT INTO tab2 VALUES (1,1,1) ON CONFLICT (e) DO UPDATE SET d = tab2.d + 1 2295 ---- 2296 upsert tab2 2297 ├── columns: <none> 2298 ├── canary column: 7 2299 ├── fetch columns: c:7 d:8 e:9 2300 ├── insert-mapping: 2301 │ ├── column1:4 => c:1 2302 │ ├── column2:5 => d:2 2303 │ └── column3:6 => e:3 2304 ├── update-mapping: 2305 │ └── upsert_d:12 => d:2 2306 ├── input binding: &1 2307 ├── project 2308 │ ├── columns: upsert_c:11 upsert_d:12 upsert_e:13 column1:4!null column2:5!null column3:6!null c:7 d:8 e:9 d_new:10 2309 │ ├── project 2310 │ │ ├── columns: d_new:10 column1:4!null column2:5!null column3:6!null c:7 d:8 e:9 2311 │ │ ├── left-join (hash) 2312 │ │ │ ├── columns: column1:4!null column2:5!null column3:6!null c:7 d:8 e:9 2313 │ │ │ ├── ensure-upsert-distinct-on 2314 │ │ │ │ ├── columns: column1:4!null column2:5!null column3:6!null 2315 │ │ │ │ ├── grouping columns: column3:6!null 2316 │ │ │ │ ├── values 2317 │ │ │ │ │ ├── columns: column1:4!null column2:5!null column3:6!null 2318 │ │ │ │ │ └── (1, 1, 1) 2319 │ │ │ │ └── aggregations 2320 │ │ │ │ ├── first-agg [as=column1:4] 2321 │ │ │ │ │ └── column1:4 2322 │ │ │ │ └── first-agg [as=column2:5] 2323 │ │ │ │ └── column2:5 2324 │ │ │ ├── scan tab2 2325 │ │ │ │ └── columns: c:7!null d:8 e:9 2326 │ │ │ └── filters 2327 │ │ │ └── column3:6 = e:9 2328 │ │ └── projections 2329 │ │ └── d:8 + 1 [as=d_new:10] 2330 │ └── projections 2331 │ ├── CASE WHEN c:7 IS NULL THEN column1:4 ELSE c:7 END [as=upsert_c:11] 2332 │ ├── CASE WHEN c:7 IS NULL THEN column2:5 ELSE d_new:10 END [as=upsert_d:12] 2333 │ └── CASE WHEN c:7 IS NULL THEN column3:6 ELSE e:9 END [as=upsert_e:13] 2334 └── f-k-checks 2335 └── f-k-checks-item: tab2(d) -> tab1(b) 2336 └── anti-join (hash) 2337 ├── columns: upsert_d:14!null 2338 ├── select 2339 │ ├── columns: upsert_d:14!null 2340 │ ├── with-scan &1 2341 │ │ ├── columns: upsert_d:14 2342 │ │ └── mapping: 2343 │ │ └── upsert_d:12 => upsert_d:14 2344 │ └── filters 2345 │ └── upsert_d:14 IS NOT NULL 2346 ├── scan tab1 2347 │ └── columns: b:16 2348 └── filters 2349 └── upsert_d:14 = b:16 2350 2351 exec-ddl 2352 CREATE TABLE self ( 2353 a INT, 2354 b INT, 2355 c INT, 2356 d INT, 2357 PRIMARY KEY (a,b), 2358 UNIQUE (b,d), 2359 UNIQUE (c), 2360 FOREIGN KEY (a,b) REFERENCES self(b,d), 2361 FOREIGN KEY (d) REFERENCES self(c) 2362 ) 2363 ---- 2364 2365 build 2366 UPSERT INTO self SELECT x, y, z, w FROM xyzw 2367 ---- 2368 upsert self 2369 ├── columns: <none> 2370 ├── canary column: 10 2371 ├── fetch columns: a:10 self.b:11 self.c:12 self.d:13 2372 ├── insert-mapping: 2373 │ ├── x:5 => a:1 2374 │ ├── y:6 => self.b:2 2375 │ ├── xyzw.z:7 => self.c:3 2376 │ └── xyzw.w:8 => self.d:4 2377 ├── update-mapping: 2378 │ ├── xyzw.z:7 => self.c:3 2379 │ └── xyzw.w:8 => self.d:4 2380 ├── input binding: &1 2381 ├── project 2382 │ ├── columns: upsert_a:14 upsert_b:15 x:5 y:6 xyzw.z:7 xyzw.w:8 a:10 self.b:11 self.c:12 self.d:13 2383 │ ├── left-join (hash) 2384 │ │ ├── columns: x:5 y:6 xyzw.z:7 xyzw.w:8 a:10 self.b:11 self.c:12 self.d:13 2385 │ │ ├── ensure-upsert-distinct-on 2386 │ │ │ ├── columns: x:5 y:6 xyzw.z:7 xyzw.w:8 2387 │ │ │ ├── grouping columns: x:5 y:6 2388 │ │ │ ├── project 2389 │ │ │ │ ├── columns: x:5 y:6 xyzw.z:7 xyzw.w:8 2390 │ │ │ │ └── scan xyzw 2391 │ │ │ │ └── columns: x:5 y:6 xyzw.z:7 xyzw.w:8 rowid:9!null 2392 │ │ │ └── aggregations 2393 │ │ │ ├── first-agg [as=xyzw.z:7] 2394 │ │ │ │ └── xyzw.z:7 2395 │ │ │ └── first-agg [as=xyzw.w:8] 2396 │ │ │ └── xyzw.w:8 2397 │ │ ├── scan self 2398 │ │ │ └── columns: a:10!null self.b:11!null self.c:12 self.d:13 2399 │ │ └── filters 2400 │ │ ├── x:5 = a:10 2401 │ │ └── y:6 = self.b:11 2402 │ └── projections 2403 │ ├── CASE WHEN a:10 IS NULL THEN x:5 ELSE a:10 END [as=upsert_a:14] 2404 │ └── CASE WHEN a:10 IS NULL THEN y:6 ELSE self.b:11 END [as=upsert_b:15] 2405 └── f-k-checks 2406 ├── f-k-checks-item: self(a,b) -> self(b,d) 2407 │ └── anti-join (hash) 2408 │ ├── columns: upsert_a:16 upsert_b:17 2409 │ ├── with-scan &1 2410 │ │ ├── columns: upsert_a:16 upsert_b:17 2411 │ │ └── mapping: 2412 │ │ ├── upsert_a:14 => upsert_a:16 2413 │ │ └── upsert_b:15 => upsert_b:17 2414 │ ├── scan self 2415 │ │ └── columns: self.b:19!null self.d:21 2416 │ └── filters 2417 │ ├── upsert_a:16 = self.b:19 2418 │ └── upsert_b:17 = self.d:21 2419 ├── f-k-checks-item: self(d) -> self(c) 2420 │ └── anti-join (hash) 2421 │ ├── columns: w:22!null 2422 │ ├── select 2423 │ │ ├── columns: w:22!null 2424 │ │ ├── with-scan &1 2425 │ │ │ ├── columns: w:22 2426 │ │ │ └── mapping: 2427 │ │ │ └── xyzw.w:8 => w:22 2428 │ │ └── filters 2429 │ │ └── w:22 IS NOT NULL 2430 │ ├── scan self 2431 │ │ └── columns: self.c:25 2432 │ └── filters 2433 │ └── w:22 = self.c:25 2434 ├── f-k-checks-item: self(a,b) -> self(b,d) 2435 │ └── semi-join (hash) 2436 │ ├── columns: b:27 d:28 2437 │ ├── except 2438 │ │ ├── columns: b:27 d:28 2439 │ │ ├── left columns: b:27 d:28 2440 │ │ ├── right columns: upsert_b:29 w:30 2441 │ │ ├── with-scan &1 2442 │ │ │ ├── columns: b:27 d:28 2443 │ │ │ └── mapping: 2444 │ │ │ ├── self.b:11 => b:27 2445 │ │ │ └── self.d:13 => d:28 2446 │ │ └── with-scan &1 2447 │ │ ├── columns: upsert_b:29 w:30 2448 │ │ └── mapping: 2449 │ │ ├── upsert_b:15 => upsert_b:29 2450 │ │ └── xyzw.w:8 => w:30 2451 │ ├── scan self 2452 │ │ └── columns: a:31!null self.b:32!null 2453 │ └── filters 2454 │ ├── b:27 = a:31 2455 │ └── d:28 = self.b:32 2456 └── f-k-checks-item: self(d) -> self(c) 2457 └── semi-join (hash) 2458 ├── columns: c:35 2459 ├── except 2460 │ ├── columns: c:35 2461 │ ├── left columns: c:35 2462 │ ├── right columns: z:36 2463 │ ├── with-scan &1 2464 │ │ ├── columns: c:35 2465 │ │ └── mapping: 2466 │ │ └── self.c:12 => c:35 2467 │ └── with-scan &1 2468 │ ├── columns: z:36 2469 │ └── mapping: 2470 │ └── xyzw.z:7 => z:36 2471 ├── scan self 2472 │ └── columns: self.d:40 2473 └── filters 2474 └── c:35 = self.d:40