github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/insert (about) 1 # LogicTest: !3node-tenant 2 statement error pgcode 42P01 relation "kv" does not exist 3 INSERT INTO kv VALUES ('a', 'b') 4 5 statement ok 6 CREATE TABLE kv ( 7 k VARCHAR PRIMARY KEY, 8 v VARCHAR, 9 UNIQUE INDEX a (v), 10 FAMILY (k), 11 FAMILY (v) 12 ) 13 14 query TT 15 SELECT * FROM kv 16 ---- 17 18 statement ok 19 INSERT INTO kv VALUES ('A') 20 21 statement error missing "k" primary key column 22 INSERT INTO kv (v) VALUES ('a') 23 24 statement ok 25 INSERT INTO kv (k) VALUES ('nil1') 26 27 statement ok 28 INSERT INTO kv (k) VALUES ('nil2') 29 30 statement ok 31 INSERT INTO kv VALUES ('nil3', NULL) 32 33 statement ok 34 INSERT INTO kv VALUES ('nil4', NULL) 35 36 statement ok 37 INSERT INTO kv (k,v) VALUES ('a', 'b'), ('c', 'd') 38 39 query T 40 SELECT v || 'hello' FROM [INSERT INTO kv VALUES ('e', 'f'), ('g', '') RETURNING v] 41 ---- 42 fhello 43 hello 44 45 statement error pgcode 23505 duplicate key value \(v\)=\('f'\) violates unique constraint "a" 46 INSERT INTO kv VALUES ('h', 'f') 47 48 statement ok 49 INSERT INTO kv VALUES ('f', 'g') 50 51 statement error duplicate key value \(v\)=\('g'\) violates unique constraint "a" 52 INSERT INTO kv VALUES ('h', 'g') 53 54 query TT 55 SELECT * FROM kv ORDER BY k 56 ---- 57 A NULL 58 a b 59 c d 60 e f 61 f g 62 g ยท 63 nil1 NULL 64 nil2 NULL 65 nil3 NULL 66 nil4 NULL 67 68 statement ok 69 CREATE TABLE kv2 ( 70 k CHAR, 71 v CHAR, 72 UNIQUE INDEX a (v), 73 PRIMARY KEY (k, v) 74 ) 75 76 statement ok 77 INSERT INTO kv2 VALUES ('a', 'b'), ('c', 'd'), ('e', 'f'), ('f', 'g') 78 79 query TT rowsort 80 SELECT * FROM kv2 81 ---- 82 a b 83 c d 84 e f 85 f g 86 87 statement ok 88 CREATE TABLE kv3 ( 89 k CHAR PRIMARY KEY, 90 v CHAR NOT NULL 91 ) 92 93 statement error null value in column "v" violates not-null constraint 94 INSERT INTO kv3 VALUES ('a') 95 96 statement error null value in column "v" violates not-null constraint 97 INSERT INTO kv3 VALUES ('a', NULL) 98 99 statement error null value in column "v" violates not-null constraint 100 INSERT INTO kv3 (k) VALUES ('a') 101 102 query TT 103 SELECT * FROM kv3 104 ---- 105 106 statement ok 107 CREATE TABLE kv4 ( 108 int INT PRIMARY KEY, 109 bit BIT, 110 bool BOOLEAN, 111 char CHAR, 112 float FLOAT 113 ) 114 115 statement error could not parse "a" as type int 116 INSERT INTO kv4 (int) VALUES ('a') 117 118 statement ok 119 INSERT INTO kv4 (int) VALUES (1) 120 121 statement error could not parse string as bit array: "a" is not a valid binary digit 122 INSERT INTO kv4 (int, bit) VALUES (2, 'a') 123 124 statement ok 125 INSERT INTO kv4 (int, bit) VALUES (2, B'1') 126 127 statement error could not parse "a" as type bool 128 INSERT INTO kv4 (int, bool) VALUES (3, 'a') 129 130 statement ok 131 INSERT INTO kv4 (int, bool) VALUES (3, true) 132 133 statement error value type int doesn't match type char of column "char" 134 INSERT INTO kv4 (int, char) VALUES (4, 1) 135 136 statement ok 137 INSERT INTO kv4 (int, char) VALUES (4, 'a') 138 139 statement error value type int doesn't match type float of column "float" 140 INSERT INTO kv4 (int, float) VALUES (5, 1::INT) 141 142 statement ok 143 INSERT INTO kv4 (int, float) VALUES (5, 2.3) 144 145 query ITBTR rowsort 146 SELECT * from kv4 147 ---- 148 1 NULL NULL NULL NULL 149 2 1 NULL NULL NULL 150 3 NULL true NULL NULL 151 4 NULL NULL a NULL 152 5 NULL NULL NULL 2.3 153 154 statement ok 155 CREATE TABLE kv5 ( 156 k CHAR PRIMARY KEY, 157 v CHAR, 158 UNIQUE INDEX a (v, k) 159 ) 160 161 statement ok 162 INSERT INTO kv5 VALUES ('a', NULL) 163 164 statement error VALUES lists must all be the same length, expected 1 columns, found 2 165 INSERT INTO kv5 VALUES ('b'), ('c', DEFAULT) 166 167 query TT 168 SELECT v, k FROM kv5@a 169 ---- 170 NULL a 171 172 statement error INSERT has more expressions than target columns, 3 expressions for 2 targets 173 INSERT INTO kv SELECT 'a', 'b', 'c' 174 175 statement error INSERT has more expressions than target columns, 2 expressions for 1 targets 176 INSERT INTO kv (k) SELECT 'a', 'b' 177 178 statement error INSERT has more target columns than expressions, 1 expressions for 2 targets 179 INSERT INTO kv5 (k, v) SELECT 'a' 180 181 # INSERT ... VALUES take a separate code path from INSERT ... SELECT. 182 183 statement error INSERT has more expressions than target columns, 3 expressions for 2 targets 184 INSERT INTO kv VALUES ('a', 'b', 'c') 185 186 statement error INSERT has more expressions than target columns, 2 expressions for 1 targets 187 INSERT INTO kv (k) VALUES ('a', 'b') 188 189 statement error INSERT has more target columns than expressions, 1 expressions for 2 targets 190 INSERT INTO kv5 (k, v) VALUES ('a') 191 192 statement ok 193 CREATE TABLE return (a INT DEFAULT 3, b INT) 194 195 query III 196 INSERT INTO return (a) VALUES (default), (8) RETURNING a, 2, a+4 197 ---- 198 3 2 7 199 8 2 12 200 201 query III 202 INSERT INTO return (b) VALUES (default), (8) RETURNING a, a+4, b 203 ---- 204 3 7 NULL 205 3 7 8 206 207 # All columns returned if none specified. 208 query II 209 INSERT INTO return VALUES (default) RETURNING a, b 210 ---- 211 3 NULL 212 213 # Test column names 214 query III colnames 215 INSERT INTO return VALUES (default) RETURNING a, b AS c, 4 AS d 216 ---- 217 a c d 218 3 NULL 4 219 220 # Return a qualified name 221 query I 222 INSERT INTO return VALUES (default) RETURNING return.a 223 ---- 224 3 225 226 # Can fetch rowid 227 statement ok 228 INSERT INTO return VALUES (default) RETURNING rowid != unique_rowid() 229 230 query I colnames 231 INSERT INTO return (a) VALUES (default) RETURNING b 232 ---- 233 b 234 NULL 235 236 query III 237 INSERT INTO return (b) VALUES (1) RETURNING *, a+1 238 ---- 239 3 1 4 240 241 query II colnames 242 INSERT INTO return VALUES (default) RETURNING * 243 ---- 244 a b 245 3 NULL 246 247 query II colnames 248 INSERT INTO return VALUES (1, 2), (3, 4) RETURNING return.a, b 249 ---- 250 a b 251 1 2 252 3 4 253 254 query II colnames 255 INSERT INTO return VALUES (1, 2), (3, 4) RETURNING * 256 ---- 257 a b 258 1 2 259 3 4 260 261 # Verify we return all columns even if we don't provide a value for all of them. 262 query II colnames 263 INSERT INTO return VALUES (1) RETURNING * 264 ---- 265 a b 266 1 NULL 267 268 query II colnames 269 INSERT INTO return (a) VALUES (1) RETURNING * 270 ---- 271 a b 272 1 NULL 273 274 statement error pq: "return.*" cannot be aliased 275 INSERT INTO return VALUES (1, 2), (3, 4) RETURNING return.* as x 276 277 query III colnames 278 INSERT INTO return VALUES (1, 2), (3, 4) RETURNING return.*, a + b AS c 279 ---- 280 a b c 281 1 2 3 282 3 4 7 283 284 # Table alias 285 statement ok 286 INSERT INTO return AS r VALUES (5, 6) 287 288 # TODO(knz) after #6092 is fixed 289 # statement ok 290 # INSERT INTO return AS r VALUES (5, 6) RETURNING r.a 291 292 # #17008: allow fully-qualified table names in RETURNING clauses 293 statement ok 294 INSERT INTO return VALUES (5, 6) RETURNING test.return.a 295 296 statement error no data source matches pattern: x.\* 297 INSERT INTO return VALUES (1, 2) RETURNING x.*[1] 298 299 statement error column "x" does not exist 300 INSERT INTO return VALUES (1, 2) RETURNING x[1] 301 302 statement ok 303 CREATE VIEW kview AS VALUES ('a', 'b'), ('c', 'd') 304 305 query TT 306 SELECT * FROM kview 307 ---- 308 a b 309 c d 310 311 statement error "kview" is not a table 312 INSERT INTO kview VALUES ('e', 'f') 313 314 query TT 315 SELECT * FROM kview 316 ---- 317 a b 318 c d 319 320 statement ok 321 CREATE TABLE abc ( 322 a INT, 323 b INT, 324 c INT, 325 PRIMARY KEY (a, b), 326 INDEX a (a) 327 ) 328 329 statement ok 330 INSERT INTO abc VALUES (1, 2, 10) 331 332 # Verify we get the correct message, even though internally the ConditionalPut 333 # for the index key will also fail. 334 statement error pgcode 23505 duplicate key value \(a,b\)=\(1,2\) violates unique constraint "primary" 335 INSERT INTO abc VALUES (1, 2, 20) 336 337 statement ok 338 CREATE TABLE decimal ( 339 a DECIMAL PRIMARY KEY 340 ) 341 342 statement ok 343 INSERT INTO decimal VALUES (4) 344 345 # Verify that the "blind" ConditionalPut optimization correctly handles a batch 346 # with two CPuts of the same key. 347 statement ok 348 CREATE TABLE blindcput ( 349 x INT, 350 v INT, 351 PRIMARY KEY (x) 352 ) 353 354 # The optimization thresholds at 10 k/v operations, so we need at least that 355 # many in one batch to trigger it. 356 statement error duplicate key value \(x\)=\(1\) violates unique constraint "primary" 357 INSERT INTO blindcput values (1, 1), (2, 2), (3, 3), (4, 4), (1, 5) 358 359 statement ok 360 CREATE TABLE nocols() 361 362 statement error INSERT has more expressions than target columns, 2 expressions for 0 targets 363 INSERT INTO nocols VALUES (true, default) 364 365 statement error at or near "k": syntax error: unimplemented 366 INSERT INTO kv (kv.k) VALUES ('hello') 367 368 statement error at or near "\*": syntax error: unimplemented 369 INSERT INTO kv (k.*) VALUES ('hello') 370 371 statement error at or near "v": syntax error: unimplemented 372 INSERT INTO kv (k.v) VALUES ('hello') 373 374 375 376 statement ok 377 CREATE TABLE insert_t (x INT, v INT) 378 379 statement ok 380 CREATE TABLE select_t (x INT, v INT) 381 382 statement ok 383 INSERT INTO select_t VALUES (1, 9), (8, 2), (3, 7), (6, 4) 384 385 # Check that INSERT supports ORDER BY (MySQL extension) 386 query II rowsort 387 INSERT INTO insert_t TABLE select_t ORDER BY v DESC LIMIT 3 RETURNING x, v 388 ---- 389 1 9 390 3 7 391 6 4 392 393 # Check that INSERT supports LIMIT (MySQL extension) 394 395 statement ok 396 TRUNCATE TABLE insert_t 397 398 statement ok 399 INSERT INTO insert_t SELECT * FROM select_t LIMIT 1 400 401 query II 402 SELECT * FROM insert_t 403 ---- 404 1 9 405 406 statement ok 407 TRUNCATE TABLE insert_t 408 409 statement ok 410 INSERT INTO insert_t (SELECT * FROM select_t LIMIT 1) 411 412 query II 413 SELECT * FROM insert_t 414 ---- 415 1 9 416 417 statement error pq: multiple LIMIT clauses not allowed 418 INSERT INTO insert_t (VALUES (1,1), (2,2) LIMIT 1) LIMIT 1 419 420 statement error pq: multiple ORDER BY clauses not allowed 421 INSERT INTO insert_t (VALUES (1,1), (2,2) ORDER BY 2) ORDER BY 2 422 423 statement error DEFAULT can only appear in a VALUES list within INSERT or on the right side of a SET 424 INSERT INTO insert_t (VALUES (1, DEFAULT), (2,'BBB') LIMIT 1) 425 426 statement error DEFAULT can only appear in a VALUES list within INSERT or on the right side of a SET 427 INSERT INTO insert_t (VALUES (1, DEFAULT), (2,'BBB')) LIMIT 1 428 429 subtest string_bytes_conflicts 430 431 statement ok 432 CREATE TABLE bytes_t ( 433 b BYTES PRIMARY KEY 434 ) 435 436 statement ok 437 INSERT INTO bytes_t VALUES ('byte') 438 439 statement ok 440 CREATE TABLE string_t ( 441 s STRING PRIMARY KEY 442 ) 443 444 statement ok 445 INSERT INTO string_t VALUES ('str') 446 447 query error value type string doesn't match type bytes of column "b" 448 INSERT INTO bytes_t SELECT * FROM string_t 449 450 query error value type bytes doesn't match type string of column "s" 451 INSERT INTO string_t SELECT * FROM bytes_t 452 453 subtest string_width_check 454 455 statement ok 456 CREATE TABLE sw ( 457 a CHAR, 458 b CHAR(3), 459 c VARCHAR, 460 d VARCHAR(3), 461 e STRING, 462 f STRING(3), 463 g "char", 464 ac CHAR COLLATE en, 465 bc CHAR(3) COLLATE en, 466 cc VARCHAR COLLATE en, 467 dc VARCHAR(3) COLLATE en, 468 ec STRING COLLATE en, 469 fc STRING(3) COLLATE en, 470 FAMILY "primary" (a, b, c, d, e, f, g, ac, bc, cc, dc, ec, fc, rowid) 471 ) 472 473 query T 474 SELECT create_statement FROM [SHOW CREATE TABLE sw] 475 ---- 476 CREATE TABLE sw ( 477 a CHAR NULL, 478 b CHAR(3) NULL, 479 c VARCHAR NULL, 480 d VARCHAR(3) NULL, 481 e STRING NULL, 482 f STRING(3) NULL, 483 g "char" NULL, 484 ac CHAR COLLATE en NULL, 485 bc CHAR(3) COLLATE en NULL, 486 cc VARCHAR COLLATE en NULL, 487 dc VARCHAR(3) COLLATE en NULL, 488 ec STRING COLLATE en NULL, 489 fc STRING(3) COLLATE en NULL, 490 FAMILY "primary" (a, b, c, d, e, f, g, ac, bc, cc, dc, ec, fc, rowid) 491 ) 492 493 statement ok 494 INSERT INTO sw VALUES ( 495 'a', 'b', 'c', 'd', 'e', 'f', 'g', 496 'A' COLLATE en, 'B' COLLATE en, 'C' COLLATE en, 'D' COLLATE en, 'E' COLLATE en, 'F' COLLATE en) 497 498 statement ok 499 INSERT INTO sw VALUES ( 500 '', '', '', '', '', '', '', 501 '' COLLATE en, '' COLLATE en, '' COLLATE en, '' COLLATE en, '' COLLATE en, '' COLLATE en) 502 503 statement error value too long for type CHAR \(column "a"\) 504 INSERT INTO sw(a) VALUES ('ab') 505 506 statement error value too long for type CHAR COLLATE en \(column "ac"\) 507 INSERT INTO sw(ac) VALUES ('ab' COLLATE en) 508 509 statement ok 510 INSERT INTO sw (b, c, d, e, f, g, bc, cc, dc, ec, fc) VALUES ( 511 'b22', 'c22', 'd22', 'e22', 'f22', 'g22', 512 'B22' COLLATE en, 'C22' COLLATE en, 'D22' COLLATE en, 'E22' COLLATE en, 'F22' COLLATE en) 513 514 statement error value too long for type CHAR\(3\) \(column "b"\) 515 INSERT INTO sw(b) VALUES ('abcd') 516 517 statement error value too long for type CHAR\(3\) COLLATE en \(column "bc"\) 518 INSERT INTO sw(bc) VALUES ('abcd' COLLATE en) 519 520 statement error value too long for type VARCHAR\(3\) \(column "d"\) 521 INSERT INTO sw(d) VALUES ('abcd') 522 523 statement error value too long for type VARCHAR\(3\) COLLATE en \(column "dc"\) 524 INSERT INTO sw(dc) VALUES ('abcd' COLLATE en) 525 526 statement error value too long for type STRING\(3\) \(column "f"\) 527 INSERT INTO sw(f) VALUES ('abcd') 528 529 statement error value too long for type STRING\(3\) COLLATE en \(column "fc"\) 530 INSERT INTO sw(fc) VALUES ('abcd' COLLATE en) 531 532 subtest regression_26742 533 534 statement ok 535 CREATE TABLE ct(x INT, derived INT AS (x+1) STORED) 536 537 statement error value type varchar doesn't match type int of column "x" 538 INSERT INTO ct(x) SELECT c FROM sw 539 540 subtest contraint_check_validation_ordering 541 542 # Verification of column constraints vs CHECK handling. The column 543 # constraint verification must take place first. 544 # 545 # This test requires that the error message for a CHECK constraint 546 # validation error be different than a column validation error. So we 547 # test the former first, as a sanity check. 548 statement ok 549 CREATE TABLE tn(x INT NULL CHECK(x IS NOT NULL), y CHAR(4) CHECK(length(y) < 4)); 550 551 statement error failed to satisfy CHECK constraint 552 INSERT INTO tn(x) VALUES (NULL) 553 554 statement error failed to satisfy CHECK constraint 555 INSERT INTO tn(y) VALUES ('abcd') 556 557 # Now we test that the column validation occurs before the CHECK constraint. 558 statement ok 559 CREATE TABLE tn2(x INT NOT NULL CHECK(x IS NOT NULL), y CHAR(3) CHECK(length(y) < 4)); 560 561 statement error null value in column "x" violates not-null constraint 562 INSERT INTO tn2(x) VALUES (NULL) 563 564 statement error value too long for type CHAR\(3\) 565 INSERT INTO tn2(x, y) VALUES (123, 'abcd') 566 567 subtest fk_contraint_check_validation_ordering 568 569 # Verify that column constraints and CHECK handling occur before 570 # foreign key validation. 571 statement ok 572 CREATE TABLE src(x VARCHAR PRIMARY KEY); 573 INSERT INTO src(x) VALUES ('abc'); 574 CREATE TABLE derived(x CHAR(3) REFERENCES src(x), 575 y VARCHAR CHECK(length(y) < 4) REFERENCES src(x)) 576 577 # Sanity check that the FK constraints gets actually validated 578 statement error foreign key 579 INSERT INTO derived(x) VALUES('xxx') 580 581 statement error value too long for type CHAR\(3\) 582 INSERT INTO derived(x) VALUES('abcd') 583 584 statement error failed to satisfy CHECK constraint 585 INSERT INTO derived(y) VALUES('abcd') 586 587 subtest regression_29494 588 589 statement ok 590 CREATE TABLE t29494(x INT); INSERT INTO t29494 VALUES (12) 591 592 statement ok 593 BEGIN; ALTER TABLE t29494 ADD COLUMN y INT NOT NULL DEFAULT 123 594 595 # Check that the new column is not visible 596 query T 597 SELECT create_statement FROM [SHOW CREATE t29494] 598 ---- 599 CREATE TABLE t29494 ( 600 x INT8 NULL, 601 FAMILY "primary" (x, rowid) 602 ) 603 604 # Check that the new column is not usable in RETURNING. 605 statement error column "y" does not exist 606 INSERT INTO t29494(x) VALUES (123) RETURNING y 607 608 statement ok 609 ROLLBACK 610 611 statement ok 612 BEGIN; ALTER TABLE t29494 ADD COLUMN y INT NOT NULL DEFAULT 123 613 614 query I 615 INSERT INTO t29494(x) VALUES (123) RETURNING * 616 ---- 617 123 618 619 statement ok 620 COMMIT 621 622 subtest regression_32759_33012 623 624 statement ok 625 CREATE TABLE t32759 ( 626 x INT, y STRING DEFAULT 'b' NOT NULL, z INT, 627 FAMILY "primary" (x, z, rowid) 628 ) 629 630 statement ok 631 BEGIN; ALTER TABLE t32759 DROP COLUMN y 632 633 # Check that the dropped column is not visible 634 query T 635 SELECT create_statement FROM [SHOW CREATE t32759] 636 ---- 637 CREATE TABLE t32759 ( 638 x INT8 NULL, 639 z INT8 NULL, 640 FAMILY "primary" (x, z, rowid) 641 ) 642 643 # Check that values cannot be inserted into the dropped column. 644 statement error column "y" does not exist 645 INSERT INTO t32759(x, y, z) VALUES (2, 'c', 2) 646 647 statement ok 648 ROLLBACK 649 650 statement ok 651 BEGIN; ALTER TABLE t32759 DROP COLUMN y 652 653 query II colnames 654 INSERT INTO t32759(x, z) VALUES (1, 4) RETURNING * 655 ---- 656 x z 657 1 4 658 659 statement ok 660 COMMIT 661 662 # Test ORDER BY with computed ordering column (requires extra column). 663 664 statement ok 665 CREATE TABLE xy(x INT PRIMARY KEY, y INT); 666 CREATE TABLE ab(a INT PRIMARY KEY, b INT); 667 INSERT INTO ab VALUES (1, 1), (2, 2) 668 669 query II rowsort 670 INSERT INTO xy (x, y) SELECT a, b FROM ab ORDER BY -b LIMIT 10 RETURNING *; 671 ---- 672 2 2 673 1 1 674 675 statement ok 676 DROP TABLE xy; DROP TABLE ab 677 678 subtest regression_35611 679 680 statement ok 681 CREATE TABLE t35611(a INT PRIMARY KEY, CHECK (a > 0)) 682 683 statement ok 684 BEGIN; ALTER TABLE t35611 ADD COLUMN b INT 685 686 statement ok 687 INSERT INTO t35611 (a) VALUES (1) 688 689 statement ok 690 COMMIT 691 692 # ------------------------------------------------------------------------------ 693 # Regression for #35364. 694 # ------------------------------------------------------------------------------ 695 subtest regression_35364 696 697 statement ok 698 CREATE TABLE t35364(x DECIMAL(1,0) CHECK (x = 0)) 699 700 statement ok 701 INSERT INTO t35364(x) VALUES (0.1) 702 703 query T 704 SELECT x FROM t35364 705 ---- 706 0 707 708 statement ok 709 DROP TABLE t35364