github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/exec/execbuilder/testdata/upsert (about) 1 # LogicTest: local 2 3 statement ok 4 CREATE TABLE kv ( 5 k INT PRIMARY KEY, 6 v INT 7 ) 8 9 # Use implicit target columns (which can use blind KV Put). 10 query TTT 11 SELECT tree, field, description FROM [ 12 EXPLAIN (VERBOSE) UPSERT INTO kv TABLE kv ORDER BY v DESC LIMIT 2 13 ] 14 ---- 15 · distributed false 16 · vectorized false 17 count · · 18 └── upsert · · 19 │ into kv(k, v) 20 │ strategy opt upserter 21 │ auto commit · 22 └── render · · 23 │ render 0 k 24 │ render 1 v 25 │ render 2 v 26 └── limit · · 27 │ count 2 28 └── sort · · 29 │ order -v 30 └── scan · · 31 · table kv@primary 32 · spans FULL SCAN 33 34 # Use explicit target columns (which can use blind KV Put). 35 query TTT 36 SELECT tree, field, description FROM [ 37 EXPLAIN (VERBOSE) UPSERT INTO kv (k, v) TABLE kv ORDER BY v DESC LIMIT 2 38 ] 39 ---- 40 · distributed false 41 · vectorized false 42 count · · 43 └── upsert · · 44 │ into kv(k, v) 45 │ strategy opt upserter 46 │ auto commit · 47 └── render · · 48 │ render 0 k 49 │ render 1 v 50 │ render 2 v 51 └── limit · · 52 │ count 2 53 └── sort · · 54 │ order -v 55 └── scan · · 56 · table kv@primary 57 · spans FULL SCAN 58 59 # Add RETURNING clause (should still use blind KV Put). 60 query TTT 61 SELECT tree, field, description FROM [ 62 EXPLAIN (VERBOSE) UPSERT INTO kv (k, v) TABLE kv ORDER BY v DESC LIMIT 2 RETURNING * 63 ] 64 ---- 65 · distributed false 66 · vectorized false 67 run · · 68 └── upsert · · 69 │ into kv(k, v) 70 │ strategy opt upserter 71 │ auto commit · 72 └── render · · 73 │ render 0 k 74 │ render 1 v 75 │ render 2 v 76 └── limit · · 77 │ count 2 78 └── sort · · 79 │ order -v 80 └── scan · · 81 · table kv@primary 82 · spans FULL SCAN 83 84 # Use subset of explicit target columns (which cannot use blind KV Put). 85 query TTT 86 SELECT tree, field, description FROM [ 87 EXPLAIN (VERBOSE) UPSERT INTO kv (k) SELECT k FROM kv ORDER BY v DESC LIMIT 2 88 ] 89 ---- 90 · distributed false 91 · vectorized false 92 count · · 93 └── upsert · · 94 │ into kv(k, v) 95 │ strategy opt upserter 96 │ auto commit · 97 └── render · · 98 │ render 0 k 99 │ render 1 column5 100 │ render 2 k 101 └── lookup-join · · 102 │ table kv@primary 103 │ type inner 104 │ equality (k) = (k) 105 │ equality cols are key · 106 │ parallel · 107 └── distinct · · 108 │ distinct on k 109 │ nulls are distinct · 110 │ error on duplicate · 111 └── render · · 112 │ render 0 CAST(NULL AS INT8) 113 │ render 1 k 114 └── limit · · 115 │ count 2 116 └── sort · · 117 │ order -v 118 └── scan · · 119 · table kv@primary 120 · spans FULL SCAN 121 122 # Use Upsert with indexed table, default columns, computed columns, and check 123 # columns. 124 statement ok 125 CREATE TABLE indexed ( 126 a INT PRIMARY KEY, 127 b INT, 128 c INT DEFAULT(10), 129 d INT AS (a + c) STORED, 130 FAMILY (a, b, c, d), 131 UNIQUE INDEX secondary (d, b), 132 CHECK (c > 0) 133 ) 134 135 # Should fetch existing values since there is a secondary index. 136 query TTT 137 SELECT tree, field, description FROM [ 138 EXPLAIN (VERBOSE) UPSERT INTO indexed VALUES (1) 139 ] 140 ---- 141 · distributed false 142 · vectorized false 143 count · · 144 └── upsert · · 145 │ into indexed(a, b, c, d) 146 │ strategy opt upserter 147 │ auto commit · 148 └── render · · 149 │ render 0 column1 150 │ render 1 column6 151 │ render 2 column7 152 │ render 3 column8 153 │ render 4 a 154 │ render 5 b 155 │ render 6 c 156 │ render 7 d 157 │ render 8 column6 158 │ render 9 column7 159 │ render 10 column8 160 │ render 11 a 161 │ render 12 check1 162 └── render · · 163 │ render 0 column7 > 0 164 │ render 1 column1 165 │ render 2 column6 166 │ render 3 column7 167 │ render 4 column8 168 │ render 5 a 169 │ render 6 b 170 │ render 7 c 171 │ render 8 d 172 └── cross-join · · 173 │ type left outer 174 ├── values · · 175 │ size 4 columns, 1 row 176 │ row 0, expr 0 1 177 │ row 0, expr 1 CAST(NULL AS INT8) 178 │ row 0, expr 2 10 179 │ row 0, expr 3 11 180 └── scan · · 181 · table indexed@primary 182 · spans /1-/1/# 183 184 # Drop index and verify that existing values no longer need to be fetched. 185 statement ok 186 DROP INDEX indexed@secondary CASCADE 187 188 query TTT 189 SELECT tree, field, description FROM [ 190 EXPLAIN (VERBOSE) UPSERT INTO indexed VALUES (1) RETURNING * 191 ] 192 ---- 193 · distributed false 194 · vectorized false 195 run · · 196 └── upsert · · 197 │ into indexed(a, b, c, d) 198 │ strategy opt upserter 199 │ auto commit · 200 └── render · · 201 │ render 0 column1 202 │ render 1 column6 203 │ render 2 column7 204 │ render 3 column8 205 │ render 4 column6 206 │ render 5 column7 207 │ render 6 column8 208 │ render 7 check1 209 └── values · · 210 · size 5 columns, 1 row 211 · row 0, expr 0 1 212 · row 0, expr 1 CAST(NULL AS INT8) 213 · row 0, expr 2 10 214 · row 0, expr 3 11 215 · row 0, expr 4 true 216 217 # Regression test for #25726. 218 # UPSERT over tables with column families, on the fast path, use the 219 # INSERT logic. This has special casing for column families of 1 220 # column, and another special casing for column families of 2+ 221 # columns. The special casing is only for families that do not include 222 # the primary key. So we need a table with 3 families: 1 for the PK, 1 223 # with just 1 col, and 1 with 2+ cols. 224 statement ok 225 CREATE TABLE tu (a INT PRIMARY KEY, b INT, c INT, d INT, FAMILY (a), FAMILY (b), FAMILY (c,d)); 226 INSERT INTO tu VALUES (1, 2, 3, 4) 227 228 statement ok 229 SET tracing = on,kv,results; UPSERT INTO tu VALUES (1, NULL, NULL, NULL); SET tracing = off 230 231 query T 232 SELECT message FROM [SHOW KV TRACE FOR SESSION] 233 WHERE operation != 'dist sender send' 234 ---- 235 Put /Table/55/1/1/0 -> /TUPLE/ 236 Del /Table/55/1/1/1/1 237 Del /Table/55/1/1/2/1 238 fast path completed 239 rows affected: 1 240 241 # KV operations. 242 statement ok 243 CREATE DATABASE t; CREATE TABLE t.kv(k INT PRIMARY KEY, v INT, FAMILY "primary" (k, v)) 244 245 statement ok 246 CREATE UNIQUE INDEX woo ON t.kv(v) 247 248 statement ok 249 SET tracing = on,kv,results; UPSERT INTO t.kv(k, v) VALUES (2,3); SET tracing = off 250 251 query TT 252 SELECT operation, message FROM [SHOW KV TRACE FOR SESSION] 253 WHERE operation != 'dist sender send' 254 ---- 255 table reader Scan /Table/57/1/2{-/#} 256 flow CPut /Table/57/1/2/0 -> /TUPLE/2:2:Int/3 257 flow InitPut /Table/57/2/3/0 -> /BYTES/0x8a 258 kv.DistSender: sending partial batch r32: sending batch 1 CPut, 1 EndTxn to (n1,s1):1 259 flow fast path completed 260 exec stmt rows affected: 1 261 262 statement ok 263 SET tracing = on,kv,results; UPSERT INTO t.kv(k, v) VALUES (1,2); SET tracing = off 264 265 query TT 266 SELECT operation, message FROM [SHOW KV TRACE FOR SESSION] 267 WHERE operation != 'dist sender send' 268 ---- 269 table reader Scan /Table/57/1/1{-/#} 270 flow CPut /Table/57/1/1/0 -> /TUPLE/2:2:Int/2 271 flow InitPut /Table/57/2/2/0 -> /BYTES/0x89 272 kv.DistSender: sending partial batch r32: sending batch 1 CPut, 1 EndTxn to (n1,s1):1 273 flow fast path completed 274 exec stmt rows affected: 1 275 276 statement error duplicate key value 277 SET tracing = on,kv,results; UPSERT INTO t.kv(k, v) VALUES (2,2); SET tracing = off 278 279 query TT 280 set tracing=off; 281 SELECT operation, message FROM [SHOW KV TRACE FOR SESSION] 282 WHERE operation != 'dist sender send' 283 ---- 284 table reader Scan /Table/57/1/2{-/#} 285 table reader fetched: /kv/primary/2/v -> /3 286 flow Put /Table/57/1/2/0 -> /TUPLE/2:2:Int/2 287 flow Del /Table/57/2/3/0 288 flow CPut /Table/57/2/2/0 -> /BYTES/0x8a (expecting does not exist) 289 kv.DistSender: sending partial batch r32: sending batch 1 Put, 1 EndTxn to (n1,s1):1 290 exec stmt execution failed after 0 rows: duplicate key value (v)=(2) violates unique constraint "woo" 291 292 293 subtest regression_32473 294 295 statement ok 296 CREATE TABLE customers ( 297 customer_id serial PRIMARY KEY, 298 name VARCHAR UNIQUE, 299 email VARCHAR NOT NULL 300 ); 301 302 statement ok 303 INSERT INTO customers (name, email) VALUES ('bob', 'bob@email.com') ON CONFLICT (name) 304 DO UPDATE SET (name, email) = ( 305 SELECT 'bob', 'otherbob@email.com' 306 ) 307 308 query TT 309 SELECT name, email FROM customers 310 ---- 311 bob bob@email.com 312 313 # This statement only works with the optimizer enabled. 314 statement ok 315 INSERT INTO customers (name, email) VALUES ('bob', 'bob@email.com') ON CONFLICT (name) 316 DO UPDATE SET (name, email) = ( 317 SELECT 'bob2', 'otherbob@email.com' 318 ) 319 320 query TT 321 SELECT name, email FROM customers 322 ---- 323 bob2 otherbob@email.com 324 325 statement ok 326 DROP TABLE customers 327 328 # The CBO behaves differently than the HP and PG in this case. It only checks 329 # constraints if an insert or update actually occurs. In this case, the DO 330 # NOTHING clause skips the update, so there is no need to check the constraint. 331 statement ok 332 CREATE TABLE t5 (k INT PRIMARY KEY, a INT, b int CHECK (a > b)) 333 334 statement ok 335 INSERT INTO t5 VALUES (1, 10, 9) ON CONFLICT (k) DO NOTHING 336 337 statement ok 338 INSERT INTO t5 VALUES (1, 10, 20) ON CONFLICT (k) DO NOTHING 339 340 # Regression test for #35564: make sure we use the Upsert's input required 341 # ordering for the internal projection. 342 343 statement ok 344 CREATE TABLE abc (a INT, b INT, c INT, INDEX(c) STORING(a,b)) 345 346 statement ok 347 CREATE TABLE xyz (x INT, y INT, z INT) 348 349 query TTTTT 350 EXPLAIN (VERBOSE) SELECT * FROM [UPSERT INTO xyz SELECT a, b, c FROM abc RETURNING z] ORDER BY z 351 ---- 352 · distributed false · · 353 · vectorized false · · 354 root · · (z) +z 355 ├── sort · · (z) +z 356 │ │ order +z · · 357 │ └── scan buffer node · · (z) · 358 │ label buffer 1 · · 359 └── subquery · · · · 360 │ id @S1 · · 361 │ original sql UPSERT INTO xyz SELECT a, b, c FROM abc RETURNING z · · 362 │ exec mode all rows · · 363 └── buffer node · · (z) · 364 │ label buffer 1 · · 365 └── spool · · (z) · 366 └── render · · (z) · 367 │ render 0 z · · 368 └── run · · (z, rowid[hidden]) · 369 └── upsert · · (z, rowid[hidden]) · 370 │ into xyz(x, y, z, rowid) · · 371 │ strategy opt upserter · · 372 └── render · · (a, b, c, column9, a, b, c) · 373 │ render 0 a · · 374 │ render 1 b · · 375 │ render 2 c · · 376 │ render 3 column9 · · 377 │ render 4 a · · 378 │ render 5 b · · 379 │ render 6 c · · 380 └── render · · (column9, a, b, c) · 381 │ render 0 unique_rowid() · · 382 │ render 1 a · · 383 │ render 2 b · · 384 │ render 3 c · · 385 └── scan · · (a, b, c) · 386 · table abc@primary · · 387 · spans FULL SCAN · · 388 389 # ------------------------------------------------------------------------------ 390 # Regression for #35364. This tests behavior that is different between the CBO 391 # and the HP. The CBO will (deliberately) round any input columns *before* 392 # evaluating any computed columns, as well as rounding the output. 393 # ------------------------------------------------------------------------------ 394 395 statement ok 396 CREATE TABLE t35364( 397 x DECIMAL(10,0) CHECK(round(x) = x) PRIMARY KEY, 398 y DECIMAL(10,0) DEFAULT (1.5), 399 z DECIMAL(10,0) AS (x+y+2.5) STORED CHECK(z >= 7) 400 ) 401 402 query TTT 403 UPSERT INTO t35364 (x) VALUES (1.5) RETURNING * 404 ---- 405 2 2 7 406 407 query TTT 408 UPSERT INTO t35364 (x, y) VALUES (1.5, 2.5) RETURNING * 409 ---- 410 2 3 8 411 412 query TTT 413 INSERT INTO t35364 (x) VALUES (1.5) ON CONFLICT (x) DO UPDATE SET x=2.5 RETURNING * 414 ---- 415 3 3 9 416 417 statement error pq: failed to satisfy CHECK constraint \(z >= 7:::DECIMAL\) 418 UPSERT INTO t35364 (x) VALUES (0) 419 420 # ------------------------------------------------------------------------------ 421 # Regression for #38627. Combined with the equivalent logic test, make sure that 422 # UPSERT in the presence of column mutations uses a lookup join without a 423 # problem. 424 # ------------------------------------------------------------------------------ 425 426 statement ok 427 CREATE TABLE table38627 (a INT PRIMARY KEY, b INT, FAMILY (a, b)); INSERT INTO table38627 VALUES(1,1) 428 429 statement ok 430 BEGIN; ALTER TABLE table38627 ADD COLUMN c INT NOT NULL DEFAULT 5 431 432 query TTTTT 433 EXPLAIN (VERBOSE) UPSERT INTO table38627 SELECT * FROM table38627 WHERE a=1 434 ---- 435 · distributed false · · 436 · vectorized false · · 437 count · · () · 438 └── upsert · · () · 439 │ into table38627(a, b) · · 440 │ strategy opt upserter · · 441 └── render · · (a, b, a, b, c, b, a) · 442 │ render 0 a · · 443 │ render 1 b · · 444 │ render 2 a · · 445 │ render 3 b · · 446 │ render 4 c · · 447 │ render 5 b · · 448 │ render 6 a · · 449 └── lookup-join · · (a, b, a, b, c) · 450 │ table table38627@primary · · 451 │ type inner · · 452 │ equality (a) = (a) · · 453 │ equality cols are key · · · 454 │ parallel · · · 455 └── scan · · (a, b) · 456 · table table38627@primary · · 457 · spans /1-/1/# · · 458 459 statement ok 460 COMMIT 461 462 # ------------------------------------------------------------------------------ 463 # Show UPSERT plans with Distinct execution operator. 464 # ------------------------------------------------------------------------------ 465 466 statement ok 467 CREATE TABLE tdup (x INT PRIMARY KEY, y INT, z INT, UNIQUE (y, z)) 468 469 # Show unsorted upsert-distinct-on. Plan should not contain "order key". 470 # Ensure this test stays synchronized to the test in logic_test/upsert. 471 query TTTTT 472 EXPLAIN (VERBOSE) 473 INSERT INTO tdup VALUES (2, 2, 2), (3, 2, 2) ON CONFLICT (z, y) DO UPDATE SET z=1 474 ---- 475 · distributed false · · 476 · vectorized false · · 477 count · · () · 478 └── upsert · · () · 479 │ into tdup(x, y, z) · · 480 │ strategy opt upserter · · 481 │ auto commit · · · 482 └── render · · (column1, column2, column3, x, y, z, upsert_z, x) · 483 │ render 0 column1 · · 484 │ render 1 column2 · · 485 │ render 2 column3 · · 486 │ render 3 x · · 487 │ render 4 y · · 488 │ render 5 z · · 489 │ render 6 upsert_z · · 490 │ render 7 x · · 491 └── render · · (upsert_z, column1, column2, column3, x, y, z) · 492 │ render 0 CASE WHEN x IS NULL THEN column3 ELSE 1 END · · 493 │ render 1 column1 · · 494 │ render 2 column2 · · 495 │ render 3 column3 · · 496 │ render 4 x · · 497 │ render 5 y · · 498 │ render 6 z · · 499 └── lookup-join · · (column1, column2, column3, x, y, z) · 500 │ table tdup@tdup_y_z_key · · 501 │ type left outer · · 502 │ equality (column2, column3) = (y, z) · · 503 │ equality cols are key · · · 504 │ parallel · · · 505 └── distinct · · (column1, column2, column3) · 506 │ distinct on column2, column3 · · 507 │ nulls are distinct · · · 508 │ error on duplicate · · · 509 └── values · · (column1, column2, column3) · 510 · size 3 columns, 2 rows · · 511 · row 0, expr 0 2 · · 512 · row 0, expr 1 2 · · 513 · row 0, expr 2 2 · · 514 · row 1, expr 0 3 · · 515 · row 1, expr 1 2 · · 516 · row 1, expr 2 2 · · 517 518 statement ok 519 CREATE TABLE target (a INT PRIMARY KEY, b INT, c INT, UNIQUE (b, c)) 520 521 statement ok 522 CREATE TABLE source (x INT PRIMARY KEY, y INT, z INT, INDEX (y, z)) 523 524 # Show sorted upsert-distinct-on. "order key = y, z" should be set below. 525 # Ensure this test stays synchronized to the test in logic_test/upsert. 526 query TTTTT 527 EXPLAIN (VERBOSE) 528 INSERT INTO target SELECT x, y, z FROM source WHERE (y IS NULL OR y > 0) AND x <> 1 529 ON CONFLICT (b, c) DO UPDATE SET b=5 530 ---- 531 · distributed false · · 532 · vectorized false · · 533 count · · () · 534 └── upsert · · () · 535 │ into target(a, b, c) · · 536 │ strategy opt upserter · · 537 │ auto commit · · · 538 └── render · · (x, y, z, a, b, c, upsert_b, a) · 539 │ render 0 x · · 540 │ render 1 y · · 541 │ render 2 z · · 542 │ render 3 a · · 543 │ render 4 b · · 544 │ render 5 c · · 545 │ render 6 upsert_b · · 546 │ render 7 a · · 547 └── render · · (upsert_b, x, y, z, a, b, c) · 548 │ render 0 CASE WHEN a IS NULL THEN y ELSE 5 END · · 549 │ render 1 x · · 550 │ render 2 y · · 551 │ render 3 z · · 552 │ render 4 a · · 553 │ render 5 b · · 554 │ render 6 c · · 555 └── merge-join · · (a, b, c, x, y, z) · 556 │ type right outer · · 557 │ equality (b, c) = (y, z) · · 558 │ mergeJoinOrder +"(b=y)",+"(c=z)" · · 559 ├── scan · · (a, b, c) +b,+c 560 │ table target@target_b_c_key · · 561 │ spans FULL SCAN · · 562 └── distinct · · (x, y, z) +y,+z 563 │ distinct on y, z · · 564 │ nulls are distinct · · · 565 │ error on duplicate · · · 566 │ order key y, z · · 567 └── scan · · (x, y, z) +y,+z 568 · table source@source_y_z_idx · · 569 · spans /NULL-/!NULL /1- · · 570 · filter x != 1 · ·