AryaWu/sqlite
0
1# 2007 January 242#3# The author disclaims copyright to this source code. In place of4# 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. The12# focus of this file is testing the INSERT transfer optimization.13#14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17set testprefix insert418 19ifcapable !view||!subquery {20 finish_test21 return22}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 is28# set to N.29#30proc xferopt_test {testname N} {31 do_test $testname {set ::sqlite3_xferopt_count} $N32}33 34# Create tables used for testing.35#36sqlite3_db_config db LEGACY_FILE_FORMAT 037execsql {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 tables45# does not violate constraints.46#47do_test insert4-1.1 {48 set sqlite3_xferopt_count 049 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}}58xferopt_test insert4-1.2 059do_test insert4-1.3 {60 execsql {61 SELECT * FROM t1;62 }63} {}64 65# Tests to make sure that the transfer optimization is not occurring66# 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 077do_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 085 86# Do not run the transfer optimization if there is a LIMIT clause87#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 098do_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}}105xferopt_test insert4-2.3.4 0106 107# Do not run the transfer optimization if there is a DISTINCT108#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 0117do_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}}123xferopt_test insert4-2.4.4 0124 125# The following procedure constructs two tables then tries to transfer126# data from one table to the other. Checks are made to make sure the127# transfer is successful and that the transfer optimization was used or128# 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-USED133# argument is true if the transfer optimization should be employed and134# 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 for136# 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 0143 do_test $testid.1 {144 execsql {145 INSERT INTO dest SELECT * FROM src;146 SELECT * FROM dest;147 }148 } $initdata149 do_test $testid.2 {150 set ::sqlite3_xferopt_count151 } $xferused152 execsql {153 DROP TABLE dest;154 DROP TABLE src;155 }156}157 158 159# Do run the transfer optimization if tables have identical160# 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 lacks170# any CHECK constraints regardless of whether or not there are CHECK171# 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 omits178# 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 NULL185# 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 and202# 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 table219# 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 should229# 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 a, b 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 0268 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_count275} {0}276do_test insert4-6.2 {277 set ::sqlite3_xferopt_count 0278 execsql {279 DROP INDEX t2_i2;280 INSERT INTO t2 SELECT * FROM t3;281 }282 set ::sqlite3_xferopt_count283} {0}284do_test insert4-6.3 {285 set ::sqlite3_xferopt_count 0286 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_count292} {1}293do_test insert4-6.4 {294 set ::sqlite3_xferopt_count 0295 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_count301} {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 {CHECK constraint failed: x<>'abc' COLLATE nocase}}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 {CHECK constraint failed: x COLLATE nocase <>'abc'}}328 329# Ticket [6284df89debdfa61db8073e062908af0c9b6118e]330# Disable the xfer optimization if the destination table contains331# a foreign key constraint332#333ifcapable foreignkey {334 do_test insert4-7.1 {335 set ::sqlite3_xferopt_count 0336 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_count346 } {1}347 do_test insert4-7.3 {348 set ::sqlite3_xferopt_count 0349 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_count362 } {0}363 do_test insert4-7.6 {364 set ::sqlite3_xferopt_count 0365 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_count374 } {0}375 do_test insert4-7.7 {376 set ::sqlite3_xferopt_count 0377 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_count386 } {1}387}388 389# Ticket [676bc02b87176125635cb174d110b431581912bb]390# Make sure INTEGER PRIMARY KEY ON CONFLICT ... works with the xfer391# 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 {UNIQUE constraint failed: t1.a}}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 {UNIQUE constraint failed: t1.a}}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 {UNIQUE constraint failed: t1.a}}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 563do_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#573do_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}587do_test 10.2 {588 set sqlite3_xferopt_count 0589 execsql { INSERT INTO x SELECT * FROM t8 }590 set sqlite3_xferopt_count591} {1}592 593do_test 10.3 {594 execsql { PRAGMA integrity_check }595 set sqlite3_xferopt_count 0596 execsql { INSERT INTO x SELECT * FROM t8 }597 set sqlite3_xferopt_count598} {1}599 600do_test 10.4 {601 execsql { PRAGMA integrity_check }602 set sqlite3_xferopt_count 0603 execsql { INSERT INTO x SELECT * FROM t8 RETURNING * }604 set sqlite3_xferopt_count605} {0}606 607#-------------------------------------------------------------------------608# xfer transfer between tables where the source has an empty partial index.609#610do_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 628finish_test629