github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/exec/execbuilder/testdata/insert (about) 1 # LogicTest: local 2 3 statement ok 4 CREATE TABLE kv ( 5 k VARCHAR PRIMARY KEY, 6 v VARCHAR, 7 UNIQUE INDEX a (v), 8 FAMILY (k), 9 FAMILY (v) 10 ) 11 12 statement ok 13 INSERT INTO kv VALUES ('A'); 14 INSERT INTO kv (k) VALUES ('nil1'); 15 INSERT INTO kv (k) VALUES ('nil2'); 16 INSERT INTO kv VALUES ('nil3', NULL); 17 INSERT INTO kv VALUES ('nil4', NULL); 18 INSERT INTO kv (k,v) VALUES ('a', 'b'), ('c', 'd'); 19 20 query T 21 SELECT v || 'hello' FROM [INSERT INTO kv VALUES ('e', 'f'), ('g', '') RETURNING v] 22 ---- 23 fhello 24 hello 25 26 statement ok 27 SET tracing = on,kv,results; SELECT * FROM kv; SET tracing = off 28 29 query T 30 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 31 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 32 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 33 ---- 34 fetched: /kv/primary/'A' -> NULL 35 fetched: /kv/primary/'a' -> NULL 36 fetched: /kv/primary/'a'/v -> 'b' 37 fetched: /kv/primary/'c' -> NULL 38 fetched: /kv/primary/'c'/v -> 'd' 39 fetched: /kv/primary/'e' -> NULL 40 fetched: /kv/primary/'e'/v -> 'f' 41 fetched: /kv/primary/'g' -> NULL 42 fetched: /kv/primary/'g'/v -> '' 43 fetched: /kv/primary/'nil1' -> NULL 44 fetched: /kv/primary/'nil2' -> NULL 45 fetched: /kv/primary/'nil3' -> NULL 46 fetched: /kv/primary/'nil4' -> NULL 47 output row: ['A' NULL] 48 output row: ['a' 'b'] 49 output row: ['c' 'd'] 50 output row: ['e' 'f'] 51 output row: ['g' ''] 52 output row: ['nil1' NULL] 53 output row: ['nil2' NULL] 54 output row: ['nil3' NULL] 55 output row: ['nil4' NULL] 56 57 statement ok 58 SET tracing = on,kv,results; SELECT * FROM kv@a; SET tracing = off 59 60 query T 61 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 62 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 63 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 64 ---- 65 fetched: /kv/a/NULL -> /'A' 66 fetched: /kv/a/NULL -> /'nil1' 67 fetched: /kv/a/NULL -> /'nil2' 68 fetched: /kv/a/NULL -> /'nil3' 69 fetched: /kv/a/NULL -> /'nil4' 70 fetched: /kv/a/'' -> /'g' 71 fetched: /kv/a/'b' -> /'a' 72 fetched: /kv/a/'d' -> /'c' 73 fetched: /kv/a/'f' -> /'e' 74 output row: ['A' NULL] 75 output row: ['nil1' NULL] 76 output row: ['nil2' NULL] 77 output row: ['nil3' NULL] 78 output row: ['nil4' NULL] 79 output row: ['g' ''] 80 output row: ['a' 'b'] 81 output row: ['c' 'd'] 82 output row: ['e' 'f'] 83 84 statement error pgcode 23505 duplicate key value \(v\)=\('f'\) violates unique constraint "a" 85 INSERT INTO kv VALUES ('h', 'f') 86 87 statement ok 88 SET tracing = on,kv,results; SELECT * FROM kv; SET tracing = off 89 90 query T 91 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 92 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 93 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 94 ---- 95 fetched: /kv/primary/'A' -> NULL 96 fetched: /kv/primary/'a' -> NULL 97 fetched: /kv/primary/'a'/v -> 'b' 98 fetched: /kv/primary/'c' -> NULL 99 fetched: /kv/primary/'c'/v -> 'd' 100 fetched: /kv/primary/'e' -> NULL 101 fetched: /kv/primary/'e'/v -> 'f' 102 fetched: /kv/primary/'g' -> NULL 103 fetched: /kv/primary/'g'/v -> '' 104 fetched: /kv/primary/'nil1' -> NULL 105 fetched: /kv/primary/'nil2' -> NULL 106 fetched: /kv/primary/'nil3' -> NULL 107 fetched: /kv/primary/'nil4' -> NULL 108 output row: ['A' NULL] 109 output row: ['a' 'b'] 110 output row: ['c' 'd'] 111 output row: ['e' 'f'] 112 output row: ['g' ''] 113 output row: ['nil1' NULL] 114 output row: ['nil2' NULL] 115 output row: ['nil3' NULL] 116 output row: ['nil4' NULL] 117 118 statement ok 119 SET tracing = on,kv,results; SELECT * FROM kv@a; SET tracing = off 120 121 query T 122 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 123 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 124 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 125 ---- 126 fetched: /kv/a/NULL -> /'A' 127 fetched: /kv/a/NULL -> /'nil1' 128 fetched: /kv/a/NULL -> /'nil2' 129 fetched: /kv/a/NULL -> /'nil3' 130 fetched: /kv/a/NULL -> /'nil4' 131 fetched: /kv/a/'' -> /'g' 132 fetched: /kv/a/'b' -> /'a' 133 fetched: /kv/a/'d' -> /'c' 134 fetched: /kv/a/'f' -> /'e' 135 output row: ['A' NULL] 136 output row: ['nil1' NULL] 137 output row: ['nil2' NULL] 138 output row: ['nil3' NULL] 139 output row: ['nil4' NULL] 140 output row: ['g' ''] 141 output row: ['a' 'b'] 142 output row: ['c' 'd'] 143 output row: ['e' 'f'] 144 145 statement ok 146 INSERT INTO kv VALUES ('f', 'g') 147 148 statement ok 149 SET tracing = on,kv,results; SELECT * FROM kv; SET tracing = off 150 151 query T 152 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 153 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 154 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 155 ---- 156 fetched: /kv/primary/'A' -> NULL 157 fetched: /kv/primary/'a' -> NULL 158 fetched: /kv/primary/'a'/v -> 'b' 159 fetched: /kv/primary/'c' -> NULL 160 fetched: /kv/primary/'c'/v -> 'd' 161 fetched: /kv/primary/'e' -> NULL 162 fetched: /kv/primary/'e'/v -> 'f' 163 fetched: /kv/primary/'f' -> NULL 164 fetched: /kv/primary/'f'/v -> 'g' 165 fetched: /kv/primary/'g' -> NULL 166 fetched: /kv/primary/'g'/v -> '' 167 fetched: /kv/primary/'nil1' -> NULL 168 fetched: /kv/primary/'nil2' -> NULL 169 fetched: /kv/primary/'nil3' -> NULL 170 fetched: /kv/primary/'nil4' -> NULL 171 output row: ['A' NULL] 172 output row: ['a' 'b'] 173 output row: ['c' 'd'] 174 output row: ['e' 'f'] 175 output row: ['f' 'g'] 176 output row: ['g' ''] 177 output row: ['nil1' NULL] 178 output row: ['nil2' NULL] 179 output row: ['nil3' NULL] 180 output row: ['nil4' NULL] 181 182 statement ok 183 SET tracing = on,kv,results; SELECT * FROM kv@a; SET tracing = off 184 185 query T 186 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 187 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 188 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 189 ---- 190 fetched: /kv/a/NULL -> /'A' 191 fetched: /kv/a/NULL -> /'nil1' 192 fetched: /kv/a/NULL -> /'nil2' 193 fetched: /kv/a/NULL -> /'nil3' 194 fetched: /kv/a/NULL -> /'nil4' 195 fetched: /kv/a/'' -> /'g' 196 fetched: /kv/a/'b' -> /'a' 197 fetched: /kv/a/'d' -> /'c' 198 fetched: /kv/a/'f' -> /'e' 199 fetched: /kv/a/'g' -> /'f' 200 output row: ['A' NULL] 201 output row: ['nil1' NULL] 202 output row: ['nil2' NULL] 203 output row: ['nil3' NULL] 204 output row: ['nil4' NULL] 205 output row: ['g' ''] 206 output row: ['a' 'b'] 207 output row: ['c' 'd'] 208 output row: ['e' 'f'] 209 output row: ['f' 'g'] 210 211 statement error duplicate key value \(v\)=\('g'\) violates unique constraint "a" 212 INSERT INTO kv VALUES ('h', 'g') 213 214 statement ok 215 SET tracing = on,kv,results; SELECT * FROM kv; SET tracing = off 216 217 query T 218 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 219 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 220 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 221 ---- 222 fetched: /kv/primary/'A' -> NULL 223 fetched: /kv/primary/'a' -> NULL 224 fetched: /kv/primary/'a'/v -> 'b' 225 fetched: /kv/primary/'c' -> NULL 226 fetched: /kv/primary/'c'/v -> 'd' 227 fetched: /kv/primary/'e' -> NULL 228 fetched: /kv/primary/'e'/v -> 'f' 229 fetched: /kv/primary/'f' -> NULL 230 fetched: /kv/primary/'f'/v -> 'g' 231 fetched: /kv/primary/'g' -> NULL 232 fetched: /kv/primary/'g'/v -> '' 233 fetched: /kv/primary/'nil1' -> NULL 234 fetched: /kv/primary/'nil2' -> NULL 235 fetched: /kv/primary/'nil3' -> NULL 236 fetched: /kv/primary/'nil4' -> NULL 237 output row: ['A' NULL] 238 output row: ['a' 'b'] 239 output row: ['c' 'd'] 240 output row: ['e' 'f'] 241 output row: ['f' 'g'] 242 output row: ['g' ''] 243 output row: ['nil1' NULL] 244 output row: ['nil2' NULL] 245 output row: ['nil3' NULL] 246 output row: ['nil4' NULL] 247 248 statement ok 249 SET tracing = on,kv,results; SELECT * FROM kv@a; SET tracing = off 250 251 query T 252 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 253 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 254 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 255 ---- 256 fetched: /kv/a/NULL -> /'A' 257 fetched: /kv/a/NULL -> /'nil1' 258 fetched: /kv/a/NULL -> /'nil2' 259 fetched: /kv/a/NULL -> /'nil3' 260 fetched: /kv/a/NULL -> /'nil4' 261 fetched: /kv/a/'' -> /'g' 262 fetched: /kv/a/'b' -> /'a' 263 fetched: /kv/a/'d' -> /'c' 264 fetched: /kv/a/'f' -> /'e' 265 fetched: /kv/a/'g' -> /'f' 266 output row: ['A' NULL] 267 output row: ['nil1' NULL] 268 output row: ['nil2' NULL] 269 output row: ['nil3' NULL] 270 output row: ['nil4' NULL] 271 output row: ['g' ''] 272 output row: ['a' 'b'] 273 output row: ['c' 'd'] 274 output row: ['e' 'f'] 275 output row: ['f' 'g'] 276 277 statement ok 278 CREATE TABLE kv5 ( 279 k CHAR PRIMARY KEY, 280 v CHAR, 281 UNIQUE INDEX a (v, k) 282 ) 283 284 statement ok 285 INSERT INTO kv5 VALUES ('a', NULL) 286 287 statement ok 288 SET tracing = on,kv,results; SELECT * FROM kv5@a; SET tracing = off 289 290 query T 291 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 292 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 293 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 294 ---- 295 fetched: /kv5/a/NULL/'a' -> NULL 296 output row: ['a' NULL] 297 298 statement ok 299 CREATE TABLE insert_t (x INT, v INT) 300 301 statement ok 302 CREATE TABLE select_t (x INT, v INT) 303 304 # Check that INSERT supports ORDER BY (MySQL extension) 305 query TTT 306 SELECT tree, field, description FROM [ 307 EXPLAIN (VERBOSE) INSERT INTO insert_t TABLE select_t ORDER BY v DESC LIMIT 10 308 ] 309 ---- 310 · distributed false 311 · vectorized false 312 count · · 313 └── insert · · 314 │ into insert_t(x, v, rowid) 315 │ strategy inserter 316 │ auto commit · 317 └── render · · 318 │ render 0 x 319 │ render 1 v 320 │ render 2 unique_rowid() 321 └── limit · · 322 │ count 10 323 └── sort · · 324 │ order -v 325 └── scan · · 326 · table select_t@primary 327 · spans FULL SCAN 328 329 # Check that INSERT supports LIMIT (MySQL extension) 330 query TTT 331 SELECT tree, field, description FROM [ 332 EXPLAIN (VERBOSE) INSERT INTO insert_t SELECT * FROM select_t LIMIT 1 333 ] 334 ---- 335 · distributed false 336 · vectorized false 337 count · · 338 └── insert · · 339 │ into insert_t(x, v, rowid) 340 │ strategy inserter 341 │ auto commit · 342 └── render · · 343 │ render 0 x 344 │ render 1 v 345 │ render 2 unique_rowid() 346 └── scan · · 347 · table select_t@primary 348 · spans LIMITED SCAN 349 · limit 1 350 351 # Check the grouping of LIMIT and ORDER BY 352 query TTT 353 EXPLAIN (PLAN) INSERT INTO insert_t VALUES (1,1), (2,2) LIMIT 1 354 ---- 355 · distributed false 356 · vectorized false 357 count · · 358 └── insert · · 359 │ into insert_t(x, v, rowid) 360 │ strategy inserter 361 │ auto commit · 362 └── render · · 363 └── limit · · 364 │ count 1 365 └── values · · 366 · size 2 columns, 2 rows 367 368 query TTT 369 EXPLAIN (PLAN) INSERT INTO insert_t VALUES (1,1), (2,2) ORDER BY 2 LIMIT 1 370 ---- 371 · distributed false 372 · vectorized false 373 count · · 374 └── insert · · 375 │ into insert_t(x, v, rowid) 376 │ strategy inserter 377 │ auto commit · 378 └── render · · 379 └── limit · · 380 │ count 1 381 └── sort · · 382 │ order +column2 383 └── values · · 384 · size 2 columns, 2 rows 385 386 query TTT 387 EXPLAIN (PLAN) INSERT INTO insert_t (VALUES (1,1), (2,2) ORDER BY 2) LIMIT 1 388 ---- 389 · distributed false 390 · vectorized false 391 count · · 392 └── insert · · 393 │ into insert_t(x, v, rowid) 394 │ strategy inserter 395 │ auto commit · 396 └── render · · 397 └── limit · · 398 │ count 1 399 └── sort · · 400 │ order +column2 401 └── values · · 402 · size 2 columns, 2 rows 403 404 query TTT 405 EXPLAIN (PLAN) INSERT INTO insert_t (VALUES (1,1), (2,2) ORDER BY 2 LIMIT 1) 406 ---- 407 · distributed false 408 · vectorized false 409 count · · 410 └── insert · · 411 │ into insert_t(x, v, rowid) 412 │ strategy inserter 413 │ auto commit · 414 └── render · · 415 └── limit · · 416 │ count 1 417 └── sort · · 418 │ order +column2 419 └── values · · 420 · size 2 columns, 2 rows 421 422 # ORDER BY expression that's not inserted into table. 423 query TTTTT 424 EXPLAIN (VERBOSE) 425 INSERT INTO insert_t (SELECT length(k), 2 FROM kv ORDER BY k || v LIMIT 10) RETURNING x+v 426 ---- 427 · distributed false · · 428 · vectorized false · · 429 render · · ("?column?") · 430 │ render 0 x + v · · 431 └── run · · (x, v, rowid[hidden]) · 432 └── insert · · (x, v, rowid[hidden]) · 433 │ into insert_t(x, v, rowid) · · 434 │ strategy inserter · · 435 └── render · · (length, "?column?", column9) · 436 │ render 0 length · · 437 │ render 1 "?column?" · · 438 │ render 2 unique_rowid() · · 439 └── limit · · (length, "?column?", column8) +column8 440 │ count 10 · · 441 └── sort · · (length, "?column?", column8) +column8 442 │ order +column8 · · 443 └── render · · (length, "?column?", column8) · 444 │ render 0 length(k) · · 445 │ render 1 2 · · 446 │ render 2 k || v · · 447 └── scan · · (k, v) · 448 · table kv@primary · · 449 · spans FULL SCAN · · 450 451 # ------------------------------------------------------------------------------ 452 # Insert rows into table during schema changes. 453 # ------------------------------------------------------------------------------ 454 455 statement ok 456 CREATE TABLE mutation(x INT, y INT NOT NULL DEFAULT(10)); INSERT INTO mutation VALUES (1, 1) 457 458 statement ok 459 BEGIN; ALTER TABLE mutation DROP COLUMN y 460 461 # Ensure that default value is still inserted into y, since y is write-only. 462 query TTTTT 463 EXPLAIN (VERBOSE) INSERT INTO mutation(x) VALUES (2) RETURNING * 464 ---- 465 · distributed false · · 466 · vectorized false · · 467 render · · (x) · 468 │ render 0 x · · 469 └── run · · (x, rowid[hidden]) · 470 └── insert-fast-path · · (x, rowid[hidden]) · 471 · into mutation(x, rowid, y) · · 472 · strategy inserter · · 473 · size 3 columns, 1 row · · 474 · row 0, expr 0 2 · · 475 · row 0, expr 1 unique_rowid() · · 476 · row 0, expr 2 10 · · 477 478 statement ok 479 ROLLBACK 480 481 statement ok 482 BEGIN; ALTER TABLE mutation ADD COLUMN z INT AS (x + y) STORED 483 484 # Ensure that value is *not* inserted into z, since z is delete-only. 485 query TTTTT 486 EXPLAIN (VERBOSE) INSERT INTO mutation(x, y) VALUES (2, 2) 487 ---- 488 · distributed false · · 489 · vectorized false · · 490 count · · () · 491 └── insert-fast-path · · () · 492 · into mutation(x, y, rowid) · · 493 · strategy inserter · · 494 · size 3 columns, 1 row · · 495 · row 0, expr 0 2 · · 496 · row 0, expr 1 2 · · 497 · row 0, expr 2 unique_rowid() · · 498 499 statement ok 500 ROLLBACK 501 502 # Regression test for #35564: make sure we use the Insert's input required 503 # ordering for the internal projection. 504 505 statement ok 506 CREATE TABLE abc (a INT, b INT, c INT, INDEX(c) STORING(a,b)) 507 508 statement ok 509 CREATE TABLE xyz (x INT, y INT, z INT) 510 511 query TTTTT 512 EXPLAIN (VERBOSE) SELECT * FROM [INSERT INTO xyz SELECT a, b, c FROM abc RETURNING z] ORDER BY z 513 ---- 514 · distributed false · · 515 · vectorized false · · 516 root · · (z) +z 517 ├── sort · · (z) +z 518 │ │ order +z · · 519 │ └── scan buffer node · · (z) · 520 │ label buffer 1 · · 521 └── subquery · · · · 522 │ id @S1 · · 523 │ original sql INSERT INTO xyz SELECT a, b, c FROM abc RETURNING z · · 524 │ exec mode all rows · · 525 └── buffer node · · (z) · 526 │ label buffer 1 · · 527 └── spool · · (z) · 528 └── render · · (z) · 529 │ render 0 z · · 530 └── run · · (z, rowid[hidden]) · 531 └── insert · · (z, rowid[hidden]) · 532 │ into xyz(x, y, z, rowid) · · 533 │ strategy inserter · · 534 └── render · · (a, b, c, column9) · 535 │ render 0 a · · 536 │ render 1 b · · 537 │ render 2 c · · 538 │ render 3 unique_rowid() · · 539 └── scan · · (a, b, c) · 540 · table abc@primary · · 541 · spans FULL SCAN · · 542 543 # ------------------------------------------------------------------------------ 544 # Regression for #35364. This tests behavior that is different between the CBO 545 # and the HP. The CBO will (deliberately) round any input columns *before* 546 # evaluating any computed columns, as well as rounding the output. 547 # ------------------------------------------------------------------------------ 548 549 statement ok 550 CREATE TABLE t35364( 551 x DECIMAL(10,0) CHECK(round(x) = x) PRIMARY KEY, 552 y DECIMAL(10,0) DEFAULT (1.5), 553 z DECIMAL(10,0) AS (x+y+2.5) STORED CHECK(z >= 7) 554 ) 555 556 query TTT 557 INSERT INTO t35364 (x) VALUES (1.5) RETURNING * 558 ---- 559 2 2 7