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