github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/update (about) 1 # LogicTest: !3node-tenant 2 statement ok 3 CREATE TABLE kv ( 4 k INT PRIMARY KEY, 5 v INT 6 ) 7 8 statement error value type tuple{int, int} doesn't match type int of column "v" 9 UPDATE kv SET v = (SELECT (10, 11)) 10 11 statement error value type decimal doesn't match type int of column "v" 12 UPDATE kv SET v = 3.2 13 14 statement error value type decimal doesn't match type int of column "v" 15 UPDATE kv SET (k, v) = (3, 3.2) 16 17 statement error value type decimal doesn't match type int of column "v" 18 UPDATE kv SET (k, v) = (SELECT 3, 3.2) 19 20 statement count 4 21 INSERT INTO kv VALUES (1, 2), (3, 4), (5, 6), (7, 8) 22 23 statement count 2 24 UPDATE kv SET v = 9 WHERE k IN (1, 3) 25 26 query II rowsort 27 SELECT * FROM kv 28 ---- 29 1 9 30 3 9 31 5 6 32 7 8 33 34 statement count 4 35 UPDATE kv SET v = k + v 36 37 query II rowsort 38 SELECT * FROM kv 39 ---- 40 1 10 41 3 12 42 5 11 43 7 15 44 45 statement error pgcode 42703 column "m" does not exist 46 UPDATE kv SET m = 9 WHERE k IN (1, 3) 47 48 statement error at or near "k": syntax error: unimplemented 49 UPDATE kv SET kv.k = 9 50 51 statement error at or near "\*": syntax error: unimplemented 52 UPDATE kv SET k.* = 9 53 54 statement error at or near "v": syntax error: unimplemented 55 UPDATE kv SET k.v = 9 56 57 statement ok 58 CREATE VIEW kview as SELECT k,v from kv 59 60 query II rowsort 61 SELECT * FROM kview 62 ---- 63 1 10 64 3 12 65 5 11 66 7 15 67 68 statement error "kview" is not a table 69 UPDATE kview SET v = 99 WHERE k IN (1, 3) 70 71 query II rowsort 72 SELECT * FROM kview 73 ---- 74 1 10 75 3 12 76 5 11 77 7 15 78 79 statement ok 80 CREATE TABLE kv2 ( 81 k CHAR PRIMARY KEY, 82 v CHAR, 83 UNIQUE INDEX a (v), 84 FAMILY (k), 85 FAMILY (v) 86 ) 87 88 statement count 4 89 INSERT INTO kv2 VALUES ('a', 'b'), ('c', 'd'), ('e', 'f'), ('f', 'g') 90 91 query TT rowsort 92 SELECT * FROM kv2 93 ---- 94 a b 95 c d 96 e f 97 f g 98 99 statement error duplicate key value \(v\)=\('g'\) violates unique constraint "a" 100 UPDATE kv2 SET v = 'g' WHERE k IN ('a') 101 102 statement count 1 103 UPDATE kv2 SET v = 'i' WHERE k IN ('a') 104 105 query TT rowsort 106 SELECT * FROM kv2 107 ---- 108 a i 109 c d 110 e f 111 f g 112 113 statement count 1 114 UPDATE kv2 SET v = 'b' WHERE k IN ('a') 115 116 query TT rowsort 117 SELECT * FROM kv2 118 ---- 119 a b 120 c d 121 e f 122 f g 123 124 statement ok 125 CREATE TABLE kv3 ( 126 k CHAR PRIMARY KEY, 127 v CHAR NOT NULL 128 ) 129 130 statement count 1 131 INSERT INTO kv3 VALUES ('a', 'b') 132 133 statement error null value in column "v" violates not-null constraint 134 UPDATE kv3 SET v = NULL WHERE k = 'a' 135 136 query TT 137 SELECT * FROM kv3 138 ---- 139 a b 140 141 statement error column "nonexistent" does not exist 142 UPDATE kv3 SET v = NULL WHERE nonexistent = 'a' 143 144 statement ok 145 CREATE TABLE abc ( 146 a INT PRIMARY KEY, 147 b INT, 148 c INT, 149 UNIQUE INDEX d (c) 150 ) 151 152 statement count 1 153 INSERT INTO abc VALUES (1, 2, 3) 154 155 statement error number of columns \(2\) does not match number of values \(1\) 156 UPDATE abc SET (b, c) = (4) 157 158 statement error DEFAULT can only appear in a VALUES list within INSERT or on the right side of a SET 159 UPDATE abc SET (b, c) = (SELECT (VALUES (DEFAULT, DEFAULT))) 160 161 statement count 1 162 UPDATE abc SET (b, c) = (4, 5) 163 164 query III 165 SELECT * FROM abc 166 ---- 167 1 4 5 168 169 statement count 1 170 UPDATE abc SET a = 1, (b, c) = (SELECT 1, 2) 171 172 query III colnames 173 UPDATE abc SET (b, c) = (8, 9) RETURNING abc.b, c, 4 AS d 174 ---- 175 b c d 176 8 9 4 177 178 query III colnames 179 UPDATE abc SET (b, c) = (8, 9) RETURNING b as col1, c as col2, 4 as col3 180 ---- 181 col1 col2 col3 182 8 9 4 183 184 query I colnames 185 UPDATE abc SET (b, c) = (8, 9) RETURNING a 186 ---- 187 a 188 1 189 190 query IIII colnames 191 UPDATE abc SET (b, c) = (5, 6) RETURNING a, b, c, 4 AS d 192 ---- 193 a b c d 194 1 5 6 4 195 196 query III colnames 197 UPDATE abc SET (b, c) = (7, 8) RETURNING * 198 ---- 199 a b c 200 1 7 8 201 202 query IIII colnames 203 UPDATE abc SET (b, c) = (7, 8) RETURNING *, 4 AS d 204 ---- 205 a b c d 206 1 7 8 4 207 208 query III colnames 209 UPDATE abc SET (b, c) = (8, 9) RETURNING abc.* 210 ---- 211 a b c 212 1 8 9 213 214 statement error pq: "abc.*" cannot be aliased 215 UPDATE abc SET (b, c) = (8, 9) RETURNING abc.* as x 216 217 query III 218 SELECT * FROM abc 219 ---- 220 1 8 9 221 222 statement count 1 223 INSERT INTO abc VALUES (4, 5, 6) 224 225 statement error duplicate key value \(a\)=\(4\) violates unique constraint "primary" 226 UPDATE abc SET a = 4, b = 3 227 228 statement error duplicate key value \(c\)=\(6\) violates unique constraint "d" 229 UPDATE abc SET a = 2, c = 6 230 231 query III 232 UPDATE abc SET a = 2, b = 3 WHERE a = 1 RETURNING * 233 ---- 234 2 3 9 235 236 query III rowsort 237 SELECT * FROM abc 238 ---- 239 2 3 9 240 4 5 6 241 242 query III 243 SELECT * FROM abc@d WHERE c = 9 244 ---- 245 2 3 9 246 247 statement error multiple assignments to the same column "b" 248 UPDATE abc SET b = 10, b = 11 249 250 statement error multiple assignments to the same column "b" 251 UPDATE abc SET (b, b) = (10, 11) 252 253 statement error multiple assignments to the same column "b" 254 UPDATE abc SET (b, c) = (10, 11), b = 12 255 256 statement ok 257 CREATE TABLE xyz ( 258 x INT PRIMARY KEY, 259 y INT, 260 z INT 261 ) 262 263 statement count 1 264 INSERT INTO xyz VALUES (111, 222, 333) 265 266 267 statement count 1 268 UPDATE xyz SET (z, y) = (SELECT 666, 777), x = (SELECT 2) 269 270 query III 271 SELECT * from xyz 272 ---- 273 2 777 666 274 275 statement ok 276 CREATE TABLE lots ( 277 k1 INT, 278 k2 INT, 279 k3 INT, 280 k4 INT, 281 k5 INT 282 ) 283 284 statement count 1 285 INSERT INTO lots VALUES (1, 2, 3, 4, 5) 286 287 statement count 1 288 UPDATE lots SET (k1, k2) = (6, 7), k3 = 8, (k4, k5) = (9, 10) 289 290 query IIIII 291 SELECT * FROM lots 292 ---- 293 6 7 8 9 10 294 295 statement count 1 296 UPDATE lots SET (k5, k4, k3, k2, k1) = (SELECT * FROM lots) 297 298 query IIIII 299 SELECT * FROM lots 300 ---- 301 10 9 8 7 6 302 303 statement ok 304 CREATE TABLE pks ( 305 k1 INT, 306 k2 INT, 307 v INT, 308 PRIMARY KEY (k1, k2), 309 UNIQUE INDEX i (k2, v), 310 FAMILY (k1, k2), 311 FAMILY (v) 312 ) 313 314 statement count 2 315 INSERT INTO pks VALUES (1, 2, 3), (4, 5, 3) 316 317 statement error duplicate key value \(k2,v\)=\(5,3\) violates unique constraint "i" 318 UPDATE pks SET k2 = 5 where k1 = 1 319 320 # Test updating only one of the columns of a multi-column primary key. 321 322 statement count 1 323 UPDATE pks SET k1 = 2 WHERE k1 = 1 324 325 query III rowsort 326 SELECT * FROM pks 327 ---- 328 2 2 3 329 4 5 3 330 331 # Check that UPDATE properly supports ORDER BY (MySQL extension) 332 333 statement count 0 334 TRUNCATE kv 335 336 statement count 4 337 INSERT INTO kv VALUES (1, 9), (8, 2), (3, 7), (6, 4) 338 339 query II 340 UPDATE kv SET v = v + 1 ORDER BY v DESC LIMIT 3 RETURNING k,v 341 ---- 342 1 10 343 3 8 344 6 5 345 346 # Check that UPDATE properly supports LIMIT (MySQL extension) 347 348 statement count 3 349 TRUNCATE kv; INSERT INTO kv VALUES (1, 2), (2, 3), (3, 4) 350 351 query II 352 UPDATE kv SET v = v - 1 WHERE k < 10 ORDER BY k LIMIT 1 RETURNING k, v 353 ---- 354 1 1 355 356 query II rowsort 357 SELECT * FROM kv 358 ---- 359 1 1 360 2 3 361 3 4 362 363 # Check that updates on tables with multiple column families behave as 364 # they should. 365 366 statement ok 367 CREATE TABLE tu (a INT PRIMARY KEY, b INT, c INT, d INT, FAMILY (a), FAMILY (b), FAMILY (c,d)); 368 INSERT INTO tu VALUES (1, 2, 3, 4) 369 370 statement ok 371 UPDATE tu SET b = NULL, c = NULL, d = NULL 372 373 query IIII rowsort 374 SELECT * FROM tu 375 ---- 376 1 NULL NULL NULL 377 378 subtest contraint_check_validation_ordering 379 380 # Verification of column constraints vs CHECK handling. The column 381 # constraint verification must take place first. 382 # 383 # This test requires that the error message for a CHECK constraint 384 # validation error be different than a column validation error. So we 385 # test the former first, as a sanity check. 386 statement ok 387 CREATE TABLE tn(x INT NULL CHECK(x IS NOT NULL), y CHAR(4) CHECK(length(y) < 4)); 388 INSERT INTO tn(x, y) VALUES (123, 'abc'); 389 390 statement error failed to satisfy CHECK constraint 391 UPDATE tn SET x = NULL 392 393 statement error failed to satisfy CHECK constraint 394 UPDATE tn SET y = 'abcd' 395 396 # Now we test that the column validation occurs before the CHECK constraint. 397 statement ok 398 CREATE TABLE tn2(x INT NOT NULL CHECK(x IS NOT NULL), y CHAR(3) CHECK(length(y) < 4)); 399 INSERT INTO tn2(x, y) VALUES (123, 'abc'); 400 401 statement error null value in column "x" violates not-null constraint 402 UPDATE tn2 SET x = NULL 403 404 statement error value too long for type CHAR\(3\) 405 UPDATE tn2 SET y = 'abcd' 406 407 subtest fk_contraint_check_validation_ordering 408 409 # Verify that column constraints and CHECK handling occur before 410 # foreign key validation. 411 statement ok 412 CREATE TABLE src(x VARCHAR PRIMARY KEY); 413 INSERT INTO src(x) VALUES ('abc'); 414 CREATE TABLE derived(x CHAR(3) REFERENCES src(x), 415 y VARCHAR CHECK(length(y) < 4) REFERENCES src(x)); 416 INSERT INTO derived(x, y) VALUES ('abc', 'abc') 417 418 # Sanity check that the FK constraints gets actually validated 419 statement error foreign key 420 UPDATE derived SET x = 'xxx' 421 422 statement error value too long for type CHAR\(3\) 423 UPDATE derived SET x = 'abcd' 424 425 statement error failed to satisfy CHECK constraint 426 UPDATE derived SET y = 'abcd' 427 428 subtest regression_29494 429 430 statement ok 431 CREATE TABLE t29494(x INT PRIMARY KEY); INSERT INTO t29494 VALUES (12) 432 433 statement ok 434 BEGIN; ALTER TABLE t29494 ADD COLUMN y INT NOT NULL DEFAULT 123 435 436 # Check that the new column is not visible 437 query T 438 SELECT create_statement FROM [SHOW CREATE t29494] 439 ---- 440 CREATE TABLE t29494 ( 441 x INT8 NOT NULL, 442 CONSTRAINT "primary" PRIMARY KEY (x ASC), 443 FAMILY "primary" (x) 444 ) 445 446 # Check that the new column is not usable in RETURNING 447 statement error column "y" does not exist 448 UPDATE t29494 SET x = 123 RETURNING y 449 450 # Check the new column is not assignable. We need to restart 451 # the txn because the error above trashed it. 452 statement ok 453 ROLLBACK; BEGIN; ALTER TABLE t29494 ADD COLUMN y INT NOT NULL DEFAULT 123 454 455 # Returning * doesn't return y 456 query I 457 UPDATE t29494 SET x = 124 WHERE x = 12 RETURNING * 458 ---- 459 124 460 461 statement error column "y" does not exist 462 UPDATE t29494 SET y = 123 463 464 # Check the new column is not usable in assignments. We need to 465 # restart the txn because the error above trashed it. 466 statement ok 467 ROLLBACK; BEGIN; ALTER TABLE t29494 ADD COLUMN y INT NOT NULL DEFAULT 123 468 469 statement error column "y" is being backfilled 470 UPDATE t29494 SET x = y 471 472 statement ok 473 COMMIT 474 475 # Use delete-only mutation columns with default and computed expressions. 476 statement ok 477 CREATE TABLE mutation (m INT PRIMARY KEY, n INT) 478 479 statement ok 480 INSERT INTO mutation VALUES (1, 1) 481 482 statement ok 483 BEGIN; ALTER TABLE mutation add COLUMN o INT DEFAULT(10), ADD COLUMN p INT AS (o + n) STORED 484 485 statement ok 486 UPDATE mutation SET m=2 WHERE n=1 487 488 statement ok 489 COMMIT TRANSACTION 490 491 query IIII colnames 492 SELECT * FROM mutation 493 ---- 494 m n o p 495 2 1 10 11 496 497 #regression test for #32477 498 subtest reject_special_funcs_inset 499 500 statement ok 501 CREATE TABLE t32477(x) AS SELECT 1 502 503 statement error aggregate functions are not allowed in UPDATE SET 504 UPDATE t32477 SET x = count(x) 505 506 statement error window functions are not allowed in UPDATE SET 507 UPDATE t32477 SET x = rank() OVER () 508 509 statement error generator functions are not allowed in UPDATE SET 510 UPDATE t32477 SET x = generate_series(1,2) 511 512 #regression test for #32054 513 subtest empty_update_subquery 514 515 statement ok 516 CREATE TABLE t32054(x,y) AS SELECT 1,2 517 518 statement ok 519 CREATE TABLE t32054_empty(x INT, y INT) 520 521 statement ok 522 UPDATE t32054 SET (x,y) = (SELECT x,y FROM t32054_empty) 523 524 query II 525 SELECT * FROM t32054 526 ---- 527 NULL NULL 528 529 # ------------------------------------------------------------------------------ 530 # Regression for #35364. 531 # ------------------------------------------------------------------------------ 532 subtest regression_35364 533 534 statement ok 535 CREATE TABLE t35364(x DECIMAL(1,0) CHECK (x >= 1)) 536 537 statement ok 538 INSERT INTO t35364 VALUES (2) 539 540 statement ok 541 UPDATE t35364 SET x=0.5 542 543 query T 544 SELECT x FROM t35364 545 ---- 546 1 547 548 # ------------------------------------------------------------------------------ 549 # Regression for #35970. 550 # ------------------------------------------------------------------------------ 551 statement ok 552 CREATE TABLE table35970 ( 553 a INT PRIMARY KEY, 554 b INT, 555 c INT8[], 556 FAMILY fam0 (a, b), 557 FAMILY fam1 (c) 558 ) 559 560 statement ok 561 INSERT INTO table35970 VALUES (1, 1, NULL); 562 563 query I 564 UPDATE table35970 565 SET c = c 566 RETURNING b 567 ---- 568 1