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 18 19ifcapable !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# 30proc xferopt_test {testname N} { 31 do_test $testname {set ::sqlite3_xferopt_count} $N 32} 33 34# Create tables used for testing. 35# 36execsql { 37 PRAGMA legacy_file_format = 0; 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# 47do_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 {constraint failed}} 58xferopt_test insert4-1.2 0 59do_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. 69do_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} 76xferopt_test insert4-2.1.2 0 77do_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}} 84xferopt_test insert4-2.2.2 0 85 86# Do not run the transfer optimization if there is a LIMIT clause 87# 88do_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} 97xferopt_test insert4-2.3.2 0 98do_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 {constraint failed}} 105xferopt_test insert4-2.3.4 0 106 107# Do not run the transfer optimization if there is a DISTINCT 108# 109do_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} 116xferopt_test insert4-2.4.2 0 117do_test insert4-2.4.3 { 118 catchsql { 119 DELETE FROM t1; 120 INSERT INTO t1 SELECT DISTINCT * FROM t2; 121 } 122} {1 {constraint failed}} 123xferopt_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# 138proc 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# 162xfer_check insert4-3.1 1 {1 9} \ 163 {a int, b int CHECK(b>a)} \ 164 {x int, y int CHECK(y>x)} 165xfer_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# 173xfer_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# 180xfer_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# 187xfer_check insert4-3.5 0 {1 9} \ 188 {a int, b int NOT NULL} \ 189 {x int, y int} 190xfer_check insert4-3.6 0 {1 9} \ 191 {a int, b int NOT NULL} \ 192 {x int NOT NULL, y int} 193xfer_check insert4-3.7 0 {1 9} \ 194 {a int NOT NULL, b int NOT NULL} \ 195 {x int NOT NULL, y int} 196xfer_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# 205xfer_check insert4-3.9 1 {1 9} \ 206 {a int, b int} \ 207 {x int NOT NULL, y int} 208xfer_check insert4-3.10 1 {1 9} \ 209 {a int, b int} \ 210 {x int NOT NULL, y int NOT NULL} 211xfer_check insert4-3.11 1 {1 9} \ 212 {a int NOT NULL, b int} \ 213 {x int NOT NULL, y int NOT NULL} 214xfer_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# 221xfer_check insert4-3.20 0 {1 9} \ 222 {a text, b int} \ 223 {x int, b int} 224xfer_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# 231xfer_check insert4-3.22 1 {1 9} \ 232 {a int, b int} \ 233 {x integer, b int} 234 235# Ticket #2291. 236# 237 238do_test insert4-4.1a { 239 execsql {CREATE TABLE t4(a, b, UNIQUE(a,b))} 240} {} 241ifcapable 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# 254do_test insert4-5.1 { 255 # Table does not exist. 256 catchsql { INSERT INTO t2 SELECT * FROM nosuchtable } 257} {1 {no such table: nosuchtable}} 258do_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 266do_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} 276do_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} 284do_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} 293do_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 304do_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} 311do_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 {constraint failed}} 319do_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 {constraint failed}} 328 329# Ticket [6284df89debdfa61db8073e062908af0c9b6118e] 330# Disable the xfer optimization if the destination table contains 331# a foreign key constraint 332# 333ifcapable 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# 393do_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} 405do_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} 417do_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} 429do_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} 441do_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 {PRIMARY KEY must be unique}} 456do_test insert4-8.6 { 457 execsql { 458 SELECT * FROM t1; 459 } 460} {-99 100 1 2} 461do_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 {PRIMARY KEY must be unique}} 476do_test insert4-8.8 { 477 execsql { 478 SELECT * FROM t1; 479 } 480} {1 2} 481do_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 {PRIMARY KEY must be unique}} 498do_test insert4-8.10 { 499 catchsql {COMMIT} 500} {1 {cannot commit - no transaction is active}} 501do_test insert4-8.11 { 502 execsql { 503 SELECT * FROM t1; 504 } 505} {1 2} 506 507do_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} 518do_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} 529do_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} 540do_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} 551do_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 564finish_test 565