gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/insert4.test (about) 1 # 2007 January 24 2 # 3 # The author disclaims copyright to this source code. In place of 4 # a legal notice, here is a blessing: 5 # 6 # May you do good and not evil. 7 # May you find forgiveness for yourself and forgive others. 8 # May you share freely, never taking more than you give. 9 # 10 #*********************************************************************** 11 # This file implements regression tests for SQLite library. The 12 # focus of this file is testing the INSERT transfer optimization. 13 # 14 15 set testdir [file dirname $argv0] 16 source $testdir/tester.tcl 17 set testprefix insert4 18 19 ifcapable !view||!subquery { 20 finish_test 21 return 22 } 23 24 # The sqlite3_xferopt_count variable is incremented whenever the 25 # insert transfer optimization applies. 26 # 27 # This procedure runs a test to see if the sqlite3_xferopt_count is 28 # set to N. 29 # 30 proc xferopt_test {testname N} { 31 do_test $testname {set ::sqlite3_xferopt_count} $N 32 } 33 34 # Create tables used for testing. 35 # 36 sqlite3_db_config db LEGACY_FILE_FORMAT 0 37 execsql { 38 CREATE TABLE t1(a int, b int, check(b>a)); 39 CREATE TABLE t2(x int, y int); 40 CREATE VIEW v2 AS SELECT y, x FROM t2; 41 CREATE TABLE t3(a int, b int); 42 } 43 44 # Ticket #2252. Make sure the an INSERT from identical tables 45 # does not violate constraints. 46 # 47 do_test insert4-1.1 { 48 set sqlite3_xferopt_count 0 49 execsql { 50 DELETE FROM t1; 51 DELETE FROM t2; 52 INSERT INTO t2 VALUES(9,1); 53 } 54 catchsql { 55 INSERT INTO t1 SELECT * FROM t2; 56 } 57 } {1 {CHECK constraint failed: b>a}} 58 xferopt_test insert4-1.2 0 59 do_test insert4-1.3 { 60 execsql { 61 SELECT * FROM t1; 62 } 63 } {} 64 65 # Tests to make sure that the transfer optimization is not occurring 66 # when it is not a valid optimization. 67 # 68 # The SELECT must be against a real table. 69 do_test insert4-2.1.1 { 70 execsql { 71 DELETE FROM t1; 72 INSERT INTO t1 SELECT 4, 8; 73 SELECT * FROM t1; 74 } 75 } {4 8} 76 xferopt_test insert4-2.1.2 0 77 do_test insert4-2.2.1 { 78 catchsql { 79 DELETE FROM t1; 80 INSERT INTO t1 SELECT * FROM v2; 81 SELECT * FROM t1; 82 } 83 } {0 {1 9}} 84 xferopt_test insert4-2.2.2 0 85 86 # Do not run the transfer optimization if there is a LIMIT clause 87 # 88 do_test insert4-2.3.1 { 89 execsql { 90 DELETE FROM t2; 91 INSERT INTO t2 VALUES(9,1); 92 INSERT INTO t2 SELECT y, x FROM t2; 93 INSERT INTO t3 SELECT * FROM t2 LIMIT 1; 94 SELECT * FROM t3; 95 } 96 } {9 1} 97 xferopt_test insert4-2.3.2 0 98 do_test insert4-2.3.3 { 99 catchsql { 100 DELETE FROM t1; 101 INSERT INTO t1 SELECT * FROM t2 LIMIT 1; 102 SELECT * FROM t1; 103 } 104 } {1 {CHECK constraint failed: b>a}} 105 xferopt_test insert4-2.3.4 0 106 107 # Do not run the transfer optimization if there is a DISTINCT 108 # 109 do_test insert4-2.4.1 { 110 execsql { 111 DELETE FROM t3; 112 INSERT INTO t3 SELECT DISTINCT * FROM t2; 113 SELECT * FROM t3; 114 } 115 } {9 1 1 9} 116 xferopt_test insert4-2.4.2 0 117 do_test insert4-2.4.3 { 118 catchsql { 119 DELETE FROM t1; 120 INSERT INTO t1 SELECT DISTINCT * FROM t2; 121 } 122 } {1 {CHECK constraint failed: b>a}} 123 xferopt_test insert4-2.4.4 0 124 125 # The following procedure constructs two tables then tries to transfer 126 # data from one table to the other. Checks are made to make sure the 127 # transfer is successful and that the transfer optimization was used or 128 # not, as appropriate. 129 # 130 # xfer_check TESTID XFER-USED INIT-DATA DEST-SCHEMA SRC-SCHEMA 131 # 132 # The TESTID argument is the symbolic name for this test. The XFER-USED 133 # argument is true if the transfer optimization should be employed and 134 # false if not. INIT-DATA is a single row of data that is to be 135 # transfered. DEST-SCHEMA and SRC-SCHEMA are table declarations for 136 # the destination and source tables. 137 # 138 proc xfer_check {testid xferused initdata destschema srcschema} { 139 execsql "CREATE TABLE dest($destschema)" 140 execsql "CREATE TABLE src($srcschema)" 141 execsql "INSERT INTO src VALUES([join $initdata ,])" 142 set ::sqlite3_xferopt_count 0 143 do_test $testid.1 { 144 execsql { 145 INSERT INTO dest SELECT * FROM src; 146 SELECT * FROM dest; 147 } 148 } $initdata 149 do_test $testid.2 { 150 set ::sqlite3_xferopt_count 151 } $xferused 152 execsql { 153 DROP TABLE dest; 154 DROP TABLE src; 155 } 156 } 157 158 159 # Do run the transfer optimization if tables have identical 160 # CHECK constraints. 161 # 162 xfer_check insert4-3.1 1 {1 9} \ 163 {a int, b int CHECK(b>a)} \ 164 {x int, y int CHECK(y>x)} 165 xfer_check insert4-3.2 1 {1 9} \ 166 {a int, b int CHECK(b>a)} \ 167 {x int CHECK(y>x), y int} 168 169 # Do run the transfer optimization if the destination table lacks 170 # any CHECK constraints regardless of whether or not there are CHECK 171 # constraints on the source table. 172 # 173 xfer_check insert4-3.3 1 {1 9} \ 174 {a int, b int} \ 175 {x int, y int CHECK(y>x)} 176 177 # Do run the transfer optimization if the destination table omits 178 # NOT NULL constraints that the source table has. 179 # 180 xfer_check insert4-3.4 0 {1 9} \ 181 {a int, b int CHECK(b>a)} \ 182 {x int, y int} 183 184 # Do not run the optimization if the destination has NOT NULL 185 # constraints that the source table lacks. 186 # 187 xfer_check insert4-3.5 0 {1 9} \ 188 {a int, b int NOT NULL} \ 189 {x int, y int} 190 xfer_check insert4-3.6 0 {1 9} \ 191 {a int, b int NOT NULL} \ 192 {x int NOT NULL, y int} 193 xfer_check insert4-3.7 0 {1 9} \ 194 {a int NOT NULL, b int NOT NULL} \ 195 {x int NOT NULL, y int} 196 xfer_check insert4-3.8 0 {1 9} \ 197 {a int NOT NULL, b int} \ 198 {x int, y int} 199 200 201 # Do run the transfer optimization if the destination table and 202 # source table have the same NOT NULL constraints or if the 203 # source table has extra NOT NULL constraints. 204 # 205 xfer_check insert4-3.9 1 {1 9} \ 206 {a int, b int} \ 207 {x int NOT NULL, y int} 208 xfer_check insert4-3.10 1 {1 9} \ 209 {a int, b int} \ 210 {x int NOT NULL, y int NOT NULL} 211 xfer_check insert4-3.11 1 {1 9} \ 212 {a int NOT NULL, b int} \ 213 {x int NOT NULL, y int NOT NULL} 214 xfer_check insert4-3.12 1 {1 9} \ 215 {a int, b int NOT NULL} \ 216 {x int NOT NULL, y int NOT NULL} 217 218 # Do not run the optimization if any corresponding table 219 # columns have different affinities. 220 # 221 xfer_check insert4-3.20 0 {1 9} \ 222 {a text, b int} \ 223 {x int, b int} 224 xfer_check insert4-3.21 0 {1 9} \ 225 {a int, b int} \ 226 {x text, b int} 227 228 # "int" and "integer" are equivalent so the optimization should 229 # run here. 230 # 231 xfer_check insert4-3.22 1 {1 9} \ 232 {a int, b int} \ 233 {x integer, b int} 234 235 # Ticket #2291. 236 # 237 238 do_test insert4-4.1a { 239 execsql {CREATE TABLE t4(a, b, UNIQUE(a,b))} 240 } {} 241 ifcapable vacuum { 242 do_test insert4-4.1b { 243 execsql { 244 INSERT INTO t4 VALUES(NULL,0); 245 INSERT INTO t4 VALUES(NULL,1); 246 INSERT INTO t4 VALUES(NULL,1); 247 VACUUM; 248 } 249 } {} 250 } 251 252 # Check some error conditions: 253 # 254 do_test insert4-5.1 { 255 # Table does not exist. 256 catchsql { INSERT INTO t2 SELECT a, b FROM nosuchtable } 257 } {1 {no such table: nosuchtable}} 258 do_test insert4-5.2 { 259 # Number of columns does not match. 260 catchsql { 261 CREATE TABLE t5(a, b, c); 262 INSERT INTO t4 SELECT * FROM t5; 263 } 264 } {1 {table t4 has 2 columns but 3 values were supplied}} 265 266 do_test insert4-6.1 { 267 set ::sqlite3_xferopt_count 0 268 execsql { 269 CREATE INDEX t2_i2 ON t2(x, y COLLATE nocase); 270 CREATE INDEX t2_i1 ON t2(x ASC, y DESC); 271 CREATE INDEX t3_i1 ON t3(a, b); 272 INSERT INTO t2 SELECT * FROM t3; 273 } 274 set ::sqlite3_xferopt_count 275 } {0} 276 do_test insert4-6.2 { 277 set ::sqlite3_xferopt_count 0 278 execsql { 279 DROP INDEX t2_i2; 280 INSERT INTO t2 SELECT * FROM t3; 281 } 282 set ::sqlite3_xferopt_count 283 } {0} 284 do_test insert4-6.3 { 285 set ::sqlite3_xferopt_count 0 286 execsql { 287 DROP INDEX t2_i1; 288 CREATE INDEX t2_i1 ON t2(x ASC, y ASC); 289 INSERT INTO t2 SELECT * FROM t3; 290 } 291 set ::sqlite3_xferopt_count 292 } {1} 293 do_test insert4-6.4 { 294 set ::sqlite3_xferopt_count 0 295 execsql { 296 DROP INDEX t2_i1; 297 CREATE INDEX t2_i1 ON t2(x ASC, y COLLATE RTRIM); 298 INSERT INTO t2 SELECT * FROM t3; 299 } 300 set ::sqlite3_xferopt_count 301 } {0} 302 303 304 do_test insert4-6.5 { 305 execsql { 306 CREATE TABLE t6a(x CHECK( x<>'abc' )); 307 INSERT INTO t6a VALUES('ABC'); 308 SELECT * FROM t6a; 309 } 310 } {ABC} 311 do_test insert4-6.6 { 312 execsql { 313 CREATE TABLE t6b(x CHECK( x<>'abc' COLLATE nocase )); 314 } 315 catchsql { 316 INSERT INTO t6b SELECT * FROM t6a; 317 } 318 } {1 {CHECK constraint failed: x<>'abc' COLLATE nocase}} 319 do_test insert4-6.7 { 320 execsql { 321 DROP TABLE t6b; 322 CREATE TABLE t6b(x CHECK( x COLLATE nocase <>'abc' )); 323 } 324 catchsql { 325 INSERT INTO t6b SELECT * FROM t6a; 326 } 327 } {1 {CHECK constraint failed: x COLLATE nocase <>'abc'}} 328 329 # Ticket [6284df89debdfa61db8073e062908af0c9b6118e] 330 # Disable the xfer optimization if the destination table contains 331 # a foreign key constraint 332 # 333 ifcapable foreignkey { 334 do_test insert4-7.1 { 335 set ::sqlite3_xferopt_count 0 336 execsql { 337 CREATE TABLE t7a(x INTEGER PRIMARY KEY); INSERT INTO t7a VALUES(123); 338 CREATE TABLE t7b(y INTEGER REFERENCES t7a); 339 CREATE TABLE t7c(z INT); INSERT INTO t7c VALUES(234); 340 INSERT INTO t7b SELECT * FROM t7c; 341 SELECT * FROM t7b; 342 } 343 } {234} 344 do_test insert4-7.2 { 345 set ::sqlite3_xferopt_count 346 } {1} 347 do_test insert4-7.3 { 348 set ::sqlite3_xferopt_count 0 349 execsql { 350 DELETE FROM t7b; 351 PRAGMA foreign_keys=ON; 352 } 353 catchsql { 354 INSERT INTO t7b SELECT * FROM t7c; 355 } 356 } {1 {FOREIGN KEY constraint failed}} 357 do_test insert4-7.4 { 358 execsql {SELECT * FROM t7b} 359 } {} 360 do_test insert4-7.5 { 361 set ::sqlite3_xferopt_count 362 } {0} 363 do_test insert4-7.6 { 364 set ::sqlite3_xferopt_count 0 365 execsql { 366 DELETE FROM t7b; DELETE FROM t7c; 367 INSERT INTO t7c VALUES(123); 368 INSERT INTO t7b SELECT * FROM t7c; 369 SELECT * FROM t7b; 370 } 371 } {123} 372 do_test insert4-7.7 { 373 set ::sqlite3_xferopt_count 374 } {0} 375 do_test insert4-7.7 { 376 set ::sqlite3_xferopt_count 0 377 execsql { 378 PRAGMA foreign_keys=OFF; 379 DELETE FROM t7b; 380 INSERT INTO t7b SELECT * FROM t7c; 381 SELECT * FROM t7b; 382 } 383 } {123} 384 do_test insert4-7.8 { 385 set ::sqlite3_xferopt_count 386 } {1} 387 } 388 389 # Ticket [676bc02b87176125635cb174d110b431581912bb] 390 # Make sure INTEGER PRIMARY KEY ON CONFLICT ... works with the xfer 391 # optimization. 392 # 393 do_test insert4-8.1 { 394 execsql { 395 DROP TABLE IF EXISTS t1; 396 DROP TABLE IF EXISTS t2; 397 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b); 398 CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT REPLACE, y); 399 INSERT INTO t1 VALUES(1,2); 400 INSERT INTO t2 VALUES(1,3); 401 INSERT INTO t1 SELECT * FROM t2; 402 SELECT * FROM t1; 403 } 404 } {1 3} 405 do_test insert4-8.2 { 406 execsql { 407 DROP TABLE IF EXISTS t1; 408 DROP TABLE IF EXISTS t2; 409 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b); 410 CREATE TABLE t2(x, y); 411 INSERT INTO t1 VALUES(1,2); 412 INSERT INTO t2 VALUES(1,3); 413 INSERT INTO t1 SELECT * FROM t2; 414 SELECT * FROM t1; 415 } 416 } {1 3} 417 do_test insert4-8.3 { 418 execsql { 419 DROP TABLE IF EXISTS t1; 420 DROP TABLE IF EXISTS t2; 421 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT IGNORE, b); 422 CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT IGNORE, y); 423 INSERT INTO t1 VALUES(1,2); 424 INSERT INTO t2 VALUES(1,3); 425 INSERT INTO t1 SELECT * FROM t2; 426 SELECT * FROM t1; 427 } 428 } {1 2} 429 do_test insert4-8.4 { 430 execsql { 431 DROP TABLE IF EXISTS t1; 432 DROP TABLE IF EXISTS t2; 433 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT IGNORE, b); 434 CREATE TABLE t2(x, y); 435 INSERT INTO t1 VALUES(1,2); 436 INSERT INTO t2 VALUES(1,3); 437 INSERT INTO t1 SELECT * FROM t2; 438 SELECT * FROM t1; 439 } 440 } {1 2} 441 do_test insert4-8.5 { 442 execsql { 443 DROP TABLE IF EXISTS t1; 444 DROP TABLE IF EXISTS t2; 445 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT FAIL, b); 446 CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT FAIL, y); 447 INSERT INTO t1 VALUES(1,2); 448 INSERT INTO t2 VALUES(-99,100); 449 INSERT INTO t2 VALUES(1,3); 450 SELECT * FROM t1; 451 } 452 catchsql { 453 INSERT INTO t1 SELECT * FROM t2; 454 } 455 } {1 {UNIQUE constraint failed: t1.a}} 456 do_test insert4-8.6 { 457 execsql { 458 SELECT * FROM t1; 459 } 460 } {-99 100 1 2} 461 do_test insert4-8.7 { 462 execsql { 463 DROP TABLE IF EXISTS t1; 464 DROP TABLE IF EXISTS t2; 465 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ABORT, b); 466 CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ABORT, y); 467 INSERT INTO t1 VALUES(1,2); 468 INSERT INTO t2 VALUES(-99,100); 469 INSERT INTO t2 VALUES(1,3); 470 SELECT * FROM t1; 471 } 472 catchsql { 473 INSERT INTO t1 SELECT * FROM t2; 474 } 475 } {1 {UNIQUE constraint failed: t1.a}} 476 do_test insert4-8.8 { 477 execsql { 478 SELECT * FROM t1; 479 } 480 } {1 2} 481 do_test insert4-8.9 { 482 execsql { 483 DROP TABLE IF EXISTS t1; 484 DROP TABLE IF EXISTS t2; 485 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ROLLBACK, b); 486 CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ROLLBACK, y); 487 INSERT INTO t1 VALUES(1,2); 488 INSERT INTO t2 VALUES(-99,100); 489 INSERT INTO t2 VALUES(1,3); 490 SELECT * FROM t1; 491 } 492 catchsql { 493 BEGIN; 494 INSERT INTO t1 VALUES(2,3); 495 INSERT INTO t1 SELECT * FROM t2; 496 } 497 } {1 {UNIQUE constraint failed: t1.a}} 498 do_test insert4-8.10 { 499 catchsql {COMMIT} 500 } {1 {cannot commit - no transaction is active}} 501 do_test insert4-8.11 { 502 execsql { 503 SELECT * FROM t1; 504 } 505 } {1 2} 506 507 do_test insert4-8.21 { 508 execsql { 509 DROP TABLE IF EXISTS t1; 510 DROP TABLE IF EXISTS t2; 511 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b); 512 CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT REPLACE, y); 513 INSERT INTO t2 VALUES(1,3); 514 INSERT INTO t1 SELECT * FROM t2; 515 SELECT * FROM t1; 516 } 517 } {1 3} 518 do_test insert4-8.22 { 519 execsql { 520 DROP TABLE IF EXISTS t1; 521 DROP TABLE IF EXISTS t2; 522 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT IGNORE, b); 523 CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT IGNORE, y); 524 INSERT INTO t2 VALUES(1,3); 525 INSERT INTO t1 SELECT * FROM t2; 526 SELECT * FROM t1; 527 } 528 } {1 3} 529 do_test insert4-8.23 { 530 execsql { 531 DROP TABLE IF EXISTS t1; 532 DROP TABLE IF EXISTS t2; 533 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ABORT, b); 534 CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ABORT, y); 535 INSERT INTO t2 VALUES(1,3); 536 INSERT INTO t1 SELECT * FROM t2; 537 SELECT * FROM t1; 538 } 539 } {1 3} 540 do_test insert4-8.24 { 541 execsql { 542 DROP TABLE IF EXISTS t1; 543 DROP TABLE IF EXISTS t2; 544 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT FAIL, b); 545 CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT FAIL, y); 546 INSERT INTO t2 VALUES(1,3); 547 INSERT INTO t1 SELECT * FROM t2; 548 SELECT * FROM t1; 549 } 550 } {1 3} 551 do_test insert4-8.25 { 552 execsql { 553 DROP TABLE IF EXISTS t1; 554 DROP TABLE IF EXISTS t2; 555 CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ROLLBACK, b); 556 CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ROLLBACK, y); 557 INSERT INTO t2 VALUES(1,3); 558 INSERT INTO t1 SELECT * FROM t2; 559 SELECT * FROM t1; 560 } 561 } {1 3} 562 563 do_catchsql_test insert4-9.1 { 564 DROP TABLE IF EXISTS t1; 565 CREATE TABLE t1(x); 566 INSERT INTO t1(x) VALUES(5 COLLATE xyzzy) UNION SELECT 0; 567 } {1 {no such collation sequence: xyzzy}} 568 569 #------------------------------------------------------------------------- 570 # Check that running an integrity-check does not disable the xfer 571 # optimization for tables with CHECK constraints. 572 # 573 do_execsql_test 10.1 { 574 CREATE TABLE t8( 575 rid INTEGER, 576 pid INTEGER, 577 mid INTEGER, 578 px INTEGER DEFAULT(0) CHECK(px IN(0, 1)) 579 ); 580 CREATE TEMP TABLE x( 581 rid INTEGER, 582 pid INTEGER, 583 mid INTEGER, 584 px INTEGER DEFAULT(0) CHECK(px IN(0, 1)) 585 ); 586 } 587 do_test 10.2 { 588 set sqlite3_xferopt_count 0 589 execsql { INSERT INTO x SELECT * FROM t8 } 590 set sqlite3_xferopt_count 591 } {1} 592 593 do_test 10.3 { 594 execsql { PRAGMA integrity_check } 595 set sqlite3_xferopt_count 0 596 execsql { INSERT INTO x SELECT * FROM t8 } 597 set sqlite3_xferopt_count 598 } {1} 599 600 do_test 10.4 { 601 execsql { PRAGMA integrity_check } 602 set sqlite3_xferopt_count 0 603 execsql { INSERT INTO x SELECT * FROM t8 RETURNING * } 604 set sqlite3_xferopt_count 605 } {0} 606 607 #------------------------------------------------------------------------- 608 # xfer transfer between tables where the source has an empty partial index. 609 # 610 do_execsql_test 11.0 { 611 CREATE TABLE t9(a, b, c); 612 CREATE INDEX t9a ON t9(a); 613 CREATE INDEX t9b ON t9(b) WHERE c=0; 614 615 INSERT INTO t9 VALUES(1, 1, 1); 616 INSERT INTO t9 VALUES(2, 2, 2); 617 INSERT INTO t9 VALUES(3, 3, 3); 618 619 CREATE TABLE t10(a, b, c); 620 CREATE INDEX t10a ON t10(a); 621 CREATE INDEX t10b ON t10(b) WHERE c=0; 622 623 INSERT INTO t10 SELECT * FROM t9; 624 SELECT * FROM t10; 625 PRAGMA integrity_check; 626 } {1 1 1 2 2 2 3 3 3 ok} 627 628 finish_test