github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/exec/execbuilder/testdata/update (about) 1 # LogicTest: local 2 3 statement ok 4 CREATE TABLE kv2 ( 5 k CHAR PRIMARY KEY, 6 v CHAR, 7 UNIQUE INDEX a (v), 8 FAMILY (k), 9 FAMILY (v) 10 ) 11 12 statement count 4 13 INSERT INTO kv2 VALUES ('a', 'b'), ('c', 'd'), ('e', 'f'), ('f', 'g') 14 15 statement ok 16 SET tracing = on,kv,results; SELECT * FROM kv2; SET tracing = off 17 18 query T 19 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 20 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 21 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 22 ---- 23 fetched: /kv2/primary/'a' -> NULL 24 fetched: /kv2/primary/'a'/v -> 'b' 25 fetched: /kv2/primary/'c' -> NULL 26 fetched: /kv2/primary/'c'/v -> 'd' 27 fetched: /kv2/primary/'e' -> NULL 28 fetched: /kv2/primary/'e'/v -> 'f' 29 fetched: /kv2/primary/'f' -> NULL 30 fetched: /kv2/primary/'f'/v -> 'g' 31 output row: ['a' 'b'] 32 output row: ['c' 'd'] 33 output row: ['e' 'f'] 34 output row: ['f' 'g'] 35 36 statement ok 37 SET tracing = on,kv,results; SELECT * FROM kv2@a; SET tracing = off 38 39 query T 40 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 41 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 42 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 43 ---- 44 fetched: /kv2/a/'b' -> /'a' 45 fetched: /kv2/a/'d' -> /'c' 46 fetched: /kv2/a/'f' -> /'e' 47 fetched: /kv2/a/'g' -> /'f' 48 output row: ['a' 'b'] 49 output row: ['c' 'd'] 50 output row: ['e' 'f'] 51 output row: ['f' 'g'] 52 53 statement error duplicate key value \(v\)=\('g'\) violates unique constraint "a" 54 UPDATE kv2 SET v = 'g' WHERE k IN ('a') 55 56 statement ok 57 SET tracing = on,kv,results; SELECT * FROM kv2; SET tracing = off 58 59 query T 60 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 61 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 62 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 63 ---- 64 fetched: /kv2/primary/'a' -> NULL 65 fetched: /kv2/primary/'a'/v -> 'b' 66 fetched: /kv2/primary/'c' -> NULL 67 fetched: /kv2/primary/'c'/v -> 'd' 68 fetched: /kv2/primary/'e' -> NULL 69 fetched: /kv2/primary/'e'/v -> 'f' 70 fetched: /kv2/primary/'f' -> NULL 71 fetched: /kv2/primary/'f'/v -> 'g' 72 output row: ['a' 'b'] 73 output row: ['c' 'd'] 74 output row: ['e' 'f'] 75 output row: ['f' 'g'] 76 77 statement ok 78 SET tracing = on,kv,results; SELECT * FROM kv2@a; SET tracing = off 79 80 query T 81 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 82 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 83 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 84 ---- 85 fetched: /kv2/a/'b' -> /'a' 86 fetched: /kv2/a/'d' -> /'c' 87 fetched: /kv2/a/'f' -> /'e' 88 fetched: /kv2/a/'g' -> /'f' 89 output row: ['a' 'b'] 90 output row: ['c' 'd'] 91 output row: ['e' 'f'] 92 output row: ['f' 'g'] 93 94 statement ok 95 CREATE TABLE xyz ( 96 x INT PRIMARY KEY, 97 y INT, 98 z INT 99 ) 100 101 query TTT 102 EXPLAIN UPDATE xyz SET y = x 103 ---- 104 · distributed false 105 · vectorized false 106 count · · 107 └── update · · 108 │ table xyz 109 │ set y 110 │ strategy updater 111 │ auto commit · 112 └── render · · 113 └── scan · · 114 · table xyz@primary 115 · spans FULL SCAN 116 · locking strength for update 117 118 query TTTTT 119 EXPLAIN (VERBOSE) UPDATE xyz SET (x, y) = (1, 2) 120 ---- 121 · distributed false · · 122 · vectorized false · · 123 count · · () · 124 └── update · · () · 125 │ table xyz · · 126 │ set x, y · · 127 │ strategy updater · · 128 │ auto commit · · · 129 └── render · · (x, y, z, x_new, y_new) · 130 │ render 0 x · · 131 │ render 1 y · · 132 │ render 2 z · · 133 │ render 3 1 · · 134 │ render 4 2 · · 135 └── scan · · (x, y, z) · 136 · table xyz@primary · · 137 · spans FULL SCAN · · 138 · locking strength for update · · 139 140 query TTTTT 141 EXPLAIN (VERBOSE) UPDATE xyz SET (x, y) = (y, x) 142 ---- 143 · distributed false · · 144 · vectorized false · · 145 count · · () · 146 └── update · · () · 147 │ table xyz · · 148 │ set x, y · · 149 │ strategy updater · · 150 │ auto commit · · · 151 └── render · · (x, y, z, y, x) · 152 │ render 0 x · · 153 │ render 1 y · · 154 │ render 2 z · · 155 │ render 3 y · · 156 │ render 4 x · · 157 └── scan · · (x, y, z) · 158 · table xyz@primary · · 159 · spans FULL SCAN · · 160 · locking strength for update · · 161 162 query TTTTT 163 EXPLAIN (VERBOSE) UPDATE xyz SET (x, y) = (2, 2) 164 ---- 165 · distributed false · · 166 · vectorized false · · 167 count · · () · 168 └── update · · () · 169 │ table xyz · · 170 │ set x, y · · 171 │ strategy updater · · 172 │ auto commit · · · 173 └── render · · (x, y, z, x_new, x_new) · 174 │ render 0 x · · 175 │ render 1 y · · 176 │ render 2 z · · 177 │ render 3 x_new · · 178 │ render 4 x_new · · 179 └── render · · (x_new, x, y, z) · 180 │ render 0 2 · · 181 │ render 1 x · · 182 │ render 2 y · · 183 │ render 3 z · · 184 └── scan · · (x, y, z) · 185 · table xyz@primary · · 186 · spans FULL SCAN · · 187 · locking strength for update · · 188 189 statement ok 190 CREATE TABLE pks ( 191 k1 INT, 192 k2 INT, 193 v INT, 194 PRIMARY KEY (k1, k2), 195 UNIQUE INDEX i (k2, v), 196 FAMILY (k1, k2), 197 FAMILY (v) 198 ) 199 200 statement count 2 201 INSERT INTO pks VALUES (1, 2, 3), (4, 5, 3) 202 203 statement error duplicate key value \(k2,v\)=\(5,3\) violates unique constraint "i" 204 UPDATE pks SET k2 = 5 where k1 = 1 205 206 # Test updating only one of the columns of a multi-column primary key. 207 208 statement count 1 209 UPDATE pks SET k1 = 2 WHERE k1 = 1 210 211 statement ok 212 SET tracing = on,kv,results; SELECT * FROM pks WHERE k1 = 2; SET tracing = off 213 214 query T 215 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 216 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 217 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 218 ---- 219 fetched: /pks/primary/2/2 -> NULL 220 fetched: /pks/primary/2/2/v -> 3 221 output row: [2 2 3] 222 223 # Check that UPDATE properly supports ORDER BY (MySQL extension) 224 225 statement ok 226 CREATE TABLE kv ( 227 k INT PRIMARY KEY, 228 v INT, 229 FAMILY (k, v) 230 ) 231 232 query TTT 233 EXPLAIN UPDATE kv SET v = v + 1 ORDER BY v DESC LIMIT 10 234 ---- 235 · distributed false 236 · vectorized false 237 count · · 238 └── update · · 239 │ table kv 240 │ set v 241 │ strategy updater 242 │ auto commit · 243 └── render · · 244 └── limit · · 245 │ count 10 246 └── sort · · 247 │ order -v 248 └── scan · · 249 · table kv@primary 250 · spans FULL SCAN 251 252 # Use case for UPDATE ... ORDER BY: renumbering a PK without unique violation. 253 query TTT 254 EXPLAIN UPDATE kv SET v = v - 1 WHERE k < 3 LIMIT 1 255 ---- 256 · distributed false 257 · vectorized false 258 count · · 259 └── update · · 260 │ table kv 261 │ set v 262 │ strategy updater 263 │ auto commit · 264 └── render · · 265 └── scan · · 266 · table kv@primary 267 · spans -/2/# 268 · limit 1 269 · locking strength for update 270 271 # Check that updates on tables with multiple column families behave as 272 # they should. 273 274 statement ok 275 CREATE TABLE tu (a INT PRIMARY KEY, b INT, c INT, d INT, FAMILY (a), FAMILY (b), FAMILY (c,d)); 276 INSERT INTO tu VALUES (1, 2, 3, 4) 277 278 # Update single column family. 279 query TTT 280 SELECT tree, field, description FROM [ 281 EXPLAIN (VERBOSE) UPDATE tu SET c=c+1 282 ] 283 ---- 284 · distributed false 285 · vectorized false 286 count · · 287 └── update · · 288 │ table tu 289 │ set c 290 │ strategy updater 291 │ auto commit · 292 └── render · · 293 │ render 0 a 294 │ render 1 c 295 │ render 2 d 296 │ render 3 c + 1 297 └── scan · · 298 · table tu@primary 299 · spans FULL SCAN 300 · locking strength for update 301 302 statement ok 303 SET tracing = on,kv,results; UPDATE tu SET c=c+1; SET tracing = off 304 305 query T 306 SELECT message FROM [SHOW KV TRACE FOR SESSION] 307 WHERE operation != 'dist sender send' 308 ---- 309 Scan /Table/57/{1-2} 310 fetched: /tu/primary/1 -> NULL 311 fetched: /tu/primary/1/b -> 2 312 fetched: /tu/primary/1/c/d -> /3/4 313 Put /Table/57/1/1/2/1 -> /TUPLE/3:3:Int/4/1:4:Int/4 314 fast path completed 315 rows affected: 1 316 317 statement ok 318 SET tracing = on,kv,results; UPDATE tu SET b = NULL, c = NULL, d = NULL; SET tracing = off 319 320 query T 321 SELECT message FROM [SHOW KV TRACE FOR SESSION] 322 WHERE operation != 'dist sender send' 323 ---- 324 Scan /Table/57/{1-2} 325 fetched: /tu/primary/1 -> NULL 326 fetched: /tu/primary/1/b -> 2 327 fetched: /tu/primary/1/c/d -> /4/4 328 Del /Table/57/1/1/1/1 329 Del /Table/57/1/1/2/1 330 fast path completed 331 rows affected: 1 332 333 # Regression test for #35564: make sure we use the Update's input required 334 # ordering for the internal projection. 335 336 statement ok 337 CREATE TABLE abc (a INT, b INT, c INT, INDEX(c) STORING(a,b)) 338 339 query TTTTT 340 EXPLAIN (VERBOSE) SELECT * FROM [ UPDATE abc SET a=c RETURNING a ] ORDER BY a 341 ---- 342 · distributed false · · 343 · vectorized false · · 344 root · · (a) +a 345 ├── sort · · (a) +a 346 │ │ order +a · · 347 │ └── scan buffer node · · (a) · 348 │ label buffer 1 · · 349 └── subquery · · · · 350 │ id @S1 · · 351 │ original sql UPDATE abc SET a = c RETURNING a · · 352 │ exec mode all rows · · 353 └── buffer node · · (a) · 354 │ label buffer 1 · · 355 └── spool · · (a) · 356 └── render · · (a) · 357 │ render 0 a · · 358 └── run · · (a, rowid[hidden]) · 359 └── update · · (a, rowid[hidden]) · 360 │ table abc · · 361 │ set a · · 362 │ strategy updater · · 363 └── render · · (a, b, c, rowid, c) · 364 │ render 0 a · · 365 │ render 1 b · · 366 │ render 2 c · · 367 │ render 3 rowid · · 368 │ render 4 c · · 369 └── scan · · (a, b, c, rowid[hidden]) · 370 · table abc@primary · · 371 · spans FULL SCAN · · 372 · locking strength for update · · 373 374 # ------------------------------------------------------------------------------ 375 # Regression for #35364. This tests behavior that is different between the CBO 376 # and the HP. The CBO will (deliberately) round any input columns *before* 377 # evaluating any computed columns, as well as rounding the output. 378 # ------------------------------------------------------------------------------ 379 380 statement ok 381 CREATE TABLE t35364( 382 x DECIMAL(10,0) CHECK(round(x) = x) PRIMARY KEY, 383 y DECIMAL(10,0) DEFAULT (1.5), 384 z DECIMAL(10,0) AS (x+y+2.5) STORED CHECK(z >= 7) 385 ) 386 387 query TTT 388 INSERT INTO t35364 (x) VALUES (1.5) RETURNING * 389 ---- 390 2 2 7 391 392 query TTT 393 UPDATE t35364 SET x=2.5 RETURNING * 394 ---- 395 3 2 8 396 397 # Ensure that index hints in UPDATE statements force the choice of a specific index 398 # as described in #38799. 399 statement ok 400 CREATE TABLE t38799 (a INT PRIMARY KEY, b INT, c INT, INDEX foo(b), FAMILY "primary" (a, b, c)) 401 402 query TTTTT 403 EXPLAIN (VERBOSE) UPDATE t38799@foo SET c=2 WHERE a=1 404 ---- 405 · distributed false · · 406 · vectorized false · · 407 count · · () · 408 └── update · · () · 409 │ table t38799 · · 410 │ set c · · 411 │ strategy updater · · 412 │ auto commit · · · 413 └── render · · (a, b, c, c_new) · 414 │ render 0 a · · 415 │ render 1 b · · 416 │ render 2 c · · 417 │ render 3 2 · · 418 └── filter · · (a, b, c) · 419 │ filter a = 1 · · 420 └── index-join · · (a, b, c) · 421 │ table t38799@primary · · 422 │ key columns a · · 423 └── scan · · (a, b) · 424 · table t38799@foo · · 425 · spans FULL SCAN · · 426 427 # ------------------------------------------------------------------------------ 428 # Test without implicit SELECT FOR UPDATE. 429 # Some cases were all tested earlier in this file with implicit SFU enabled. 430 # ------------------------------------------------------------------------------ 431 432 query TTT 433 EXPLAIN UPDATE kv SET v = 10 WHERE k = 3 434 ---- 435 · distributed false 436 · vectorized false 437 count · · 438 └── update · · 439 │ table kv 440 │ set v 441 │ strategy updater 442 │ auto commit · 443 └── render · · 444 └── scan · · 445 · table kv@primary 446 · spans /3-/3/# 447 · locking strength for update 448 449 query TTT 450 EXPLAIN UPDATE kv SET v = k WHERE k > 1 AND k < 10 451 ---- 452 · distributed false 453 · vectorized false 454 count · · 455 └── update · · 456 │ table kv 457 │ set v 458 │ strategy updater 459 │ auto commit · 460 └── render · · 461 └── scan · · 462 · table kv@primary 463 · spans /2-/9/# 464 · parallel · 465 · locking strength for update 466 467 query TTT 468 EXPLAIN UPDATE kv SET v = 10 469 ---- 470 · distributed false 471 · vectorized false 472 count · · 473 └── update · · 474 │ table kv 475 │ set v 476 │ strategy updater 477 │ auto commit · 478 └── render · · 479 └── scan · · 480 · table kv@primary 481 · spans FULL SCAN 482 · locking strength for update 483 484 statement ok 485 CREATE TABLE kv3 ( 486 k INT PRIMARY KEY, 487 v INT, 488 meta INT, 489 INDEX (v), 490 FAMILY (k, v, meta) 491 ) 492 493 query TTT 494 EXPLAIN UPDATE kv3 SET k = 3 WHERE v = 10 495 ---- 496 · distributed false 497 · vectorized false 498 count · · 499 └── update · · 500 │ table kv3 501 │ set k 502 │ strategy updater 503 │ auto commit · 504 └── render · · 505 └── index-join · · 506 │ table kv3@primary 507 │ key columns k 508 └── scan · · 509 · table kv3@kv3_v_idx 510 · spans /10-/11 511 · locking strength for update 512 513 query TTT 514 EXPLAIN UPDATE kv3 SET k = v WHERE v > 1 AND v < 10 515 ---- 516 · distributed false 517 · vectorized false 518 count · · 519 └── update · · 520 │ table kv3 521 │ set k 522 │ strategy updater 523 │ auto commit · 524 └── render · · 525 └── index-join · · 526 │ table kv3@primary 527 │ key columns k 528 └── scan · · 529 · table kv3@kv3_v_idx 530 · spans /2-/10 531 · locking strength for update 532 533 statement ok 534 SET enable_implicit_select_for_update = false 535 536 query TTT 537 EXPLAIN UPDATE kv SET v = 10 WHERE k = 3 538 ---- 539 · distributed false 540 · vectorized false 541 count · · 542 └── update · · 543 │ table kv 544 │ set v 545 │ strategy updater 546 │ auto commit · 547 └── render · · 548 └── scan · · 549 · table kv@primary 550 · spans /3-/3/# 551 552 query TTT 553 EXPLAIN UPDATE kv SET v = k WHERE k > 1 AND k < 10 554 ---- 555 · distributed false 556 · vectorized false 557 count · · 558 └── update · · 559 │ table kv 560 │ set v 561 │ strategy updater 562 │ auto commit · 563 └── render · · 564 └── scan · · 565 · table kv@primary 566 · spans /2-/9/# 567 · parallel · 568 569 query TTT 570 EXPLAIN UPDATE kv SET v = 10 571 ---- 572 · distributed false 573 · vectorized false 574 count · · 575 └── update · · 576 │ table kv 577 │ set v 578 │ strategy updater 579 │ auto commit · 580 └── render · · 581 └── scan · · 582 · table kv@primary 583 · spans FULL SCAN 584 585 query TTTTT 586 EXPLAIN (VERBOSE) UPDATE xyz SET (x, y) = (1, 2) 587 ---- 588 · distributed false · · 589 · vectorized false · · 590 count · · () · 591 └── update · · () · 592 │ table xyz · · 593 │ set x, y · · 594 │ strategy updater · · 595 │ auto commit · · · 596 └── render · · (x, y, z, x_new, y_new) · 597 │ render 0 x · · 598 │ render 1 y · · 599 │ render 2 z · · 600 │ render 3 1 · · 601 │ render 4 2 · · 602 └── scan · · (x, y, z) · 603 · table xyz@primary · · 604 · spans FULL SCAN · · 605 606 query TTT 607 EXPLAIN UPDATE kv SET v = v - 1 WHERE k < 3 LIMIT 1 608 ---- 609 · distributed false 610 · vectorized false 611 count · · 612 └── update · · 613 │ table kv 614 │ set v 615 │ strategy updater 616 │ auto commit · 617 └── render · · 618 └── scan · · 619 · table kv@primary 620 · spans -/2/# 621 · limit 1 622 623 query TTT 624 EXPLAIN UPDATE kv3 SET k = 3 WHERE v = 10 625 ---- 626 · distributed false 627 · vectorized false 628 count · · 629 └── update · · 630 │ table kv3 631 │ set k 632 │ strategy updater 633 │ auto commit · 634 └── render · · 635 └── index-join · · 636 │ table kv3@primary 637 │ key columns k 638 └── scan · · 639 · table kv3@kv3_v_idx 640 · spans /10-/11 641 642 query TTT 643 EXPLAIN UPDATE kv3 SET k = v WHERE v > 1 AND v < 10 644 ---- 645 · distributed false 646 · vectorized false 647 count · · 648 └── update · · 649 │ table kv3 650 │ set k 651 │ strategy updater 652 │ auto commit · 653 └── render · · 654 └── index-join · · 655 │ table kv3@primary 656 │ key columns k 657 └── scan · · 658 · table kv3@kv3_v_idx 659 · spans /2-/10 660 661 # Update single column family. 662 query TTT 663 SELECT tree, field, description FROM [ 664 EXPLAIN UPDATE tu SET c=c+1 665 ] 666 ---- 667 · distributed false 668 · vectorized false 669 count · · 670 └── update · · 671 │ table tu 672 │ set c 673 │ strategy updater 674 │ auto commit · 675 └── render · · 676 └── scan · · 677 · table tu@primary 678 · spans FULL SCAN 679 680 # Reset for rest of test. 681 statement ok 682 SET enable_implicit_select_for_update = true