# 2007 January 24 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the INSERT transfer optimization. # # $Id: insert4.test,v 1.10 2008/01/21 16:22:46 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix insert4 ifcapable !view||!subquery { finish_test return } # The sqlite3_xferopt_count variable is incremented whenever the # insert transfer optimization applies. # # This procedure runs a test to see if the sqlite3_xferopt_count is # set to N. # proc xferopt_test {testname N} { do_test $testname {set ::sqlite3_xferopt_count} $N } # Create tables used for testing. # execsql { PRAGMA legacy_file_format = 0; CREATE TABLE t1(a int, b int, check(b>a)); CREATE TABLE t2(x int, y int); CREATE VIEW v2 AS SELECT y, x FROM t2; CREATE TABLE t3(a int, b int); } # Ticket #2252. Make sure the an INSERT from identical tables # does not violate constraints. # do_test insert4-1.1 { set sqlite3_xferopt_count 0 execsql { DELETE FROM t1; DELETE FROM t2; INSERT INTO t2 VALUES(9,1); } catchsql { INSERT INTO t1 SELECT * FROM t2; } } {1 {CHECK constraint failed: t1}} xferopt_test insert4-1.2 0 do_test insert4-1.3 { execsql { SELECT * FROM t1; } } {} # Tests to make sure that the transfer optimization is not occurring # when it is not a valid optimization. # # The SELECT must be against a real table. do_test insert4-2.1.1 { execsql { DELETE FROM t1; INSERT INTO t1 SELECT 4, 8; SELECT * FROM t1; } } {4 8} xferopt_test insert4-2.1.2 0 do_test insert4-2.2.1 { catchsql { DELETE FROM t1; INSERT INTO t1 SELECT * FROM v2; SELECT * FROM t1; } } {0 {1 9}} xferopt_test insert4-2.2.2 0 # Do not run the transfer optimization if there is a LIMIT clause # do_test insert4-2.3.1 { execsql { DELETE FROM t2; INSERT INTO t2 VALUES(9,1); INSERT INTO t2 SELECT y, x FROM t2; INSERT INTO t3 SELECT * FROM t2 LIMIT 1; SELECT * FROM t3; } } {9 1} xferopt_test insert4-2.3.2 0 do_test insert4-2.3.3 { catchsql { DELETE FROM t1; INSERT INTO t1 SELECT * FROM t2 LIMIT 1; SELECT * FROM t1; } } {1 {CHECK constraint failed: t1}} xferopt_test insert4-2.3.4 0 # Do not run the transfer optimization if there is a DISTINCT # do_test insert4-2.4.1 { execsql { DELETE FROM t3; INSERT INTO t3 SELECT DISTINCT * FROM t2; SELECT * FROM t3; } } {9 1 1 9} xferopt_test insert4-2.4.2 0 do_test insert4-2.4.3 { catchsql { DELETE FROM t1; INSERT INTO t1 SELECT DISTINCT * FROM t2; } } {1 {CHECK constraint failed: t1}} xferopt_test insert4-2.4.4 0 # The following procedure constructs two tables then tries to transfer # data from one table to the other. Checks are made to make sure the # transfer is successful and that the transfer optimization was used or # not, as appropriate. # # xfer_check TESTID XFER-USED INIT-DATA DEST-SCHEMA SRC-SCHEMA # # The TESTID argument is the symbolic name for this test. The XFER-USED # argument is true if the transfer optimization should be employed and # false if not. INIT-DATA is a single row of data that is to be # transfered. DEST-SCHEMA and SRC-SCHEMA are table declarations for # the destination and source tables. # proc xfer_check {testid xferused initdata destschema srcschema} { execsql "CREATE TABLE dest($destschema)" execsql "CREATE TABLE src($srcschema)" execsql "INSERT INTO src VALUES([join $initdata ,])" set ::sqlite3_xferopt_count 0 do_test $testid.1 { execsql { INSERT INTO dest SELECT * FROM src; SELECT * FROM dest; } } $initdata do_test $testid.2 { set ::sqlite3_xferopt_count } $xferused execsql { DROP TABLE dest; DROP TABLE src; } } # Do run the transfer optimization if tables have identical # CHECK constraints. # xfer_check insert4-3.1 1 {1 9} \ {a int, b int CHECK(b>a)} \ {x int, y int CHECK(y>x)} xfer_check insert4-3.2 1 {1 9} \ {a int, b int CHECK(b>a)} \ {x int CHECK(y>x), y int} # Do run the transfer optimization if the destination table lacks # any CHECK constraints regardless of whether or not there are CHECK # constraints on the source table. # xfer_check insert4-3.3 1 {1 9} \ {a int, b int} \ {x int, y int CHECK(y>x)} # Do run the transfer optimization if the destination table omits # NOT NULL constraints that the source table has. # xfer_check insert4-3.4 0 {1 9} \ {a int, b int CHECK(b>a)} \ {x int, y int} # Do not run the optimization if the destination has NOT NULL # constraints that the source table lacks. # xfer_check insert4-3.5 0 {1 9} \ {a int, b int NOT NULL} \ {x int, y int} xfer_check insert4-3.6 0 {1 9} \ {a int, b int NOT NULL} \ {x int NOT NULL, y int} xfer_check insert4-3.7 0 {1 9} \ {a int NOT NULL, b int NOT NULL} \ {x int NOT NULL, y int} xfer_check insert4-3.8 0 {1 9} \ {a int NOT NULL, b int} \ {x int, y int} # Do run the transfer optimization if the destination table and # source table have the same NOT NULL constraints or if the # source table has extra NOT NULL constraints. # xfer_check insert4-3.9 1 {1 9} \ {a int, b int} \ {x int NOT NULL, y int} xfer_check insert4-3.10 1 {1 9} \ {a int, b int} \ {x int NOT NULL, y int NOT NULL} xfer_check insert4-3.11 1 {1 9} \ {a int NOT NULL, b int} \ {x int NOT NULL, y int NOT NULL} xfer_check insert4-3.12 1 {1 9} \ {a int, b int NOT NULL} \ {x int NOT NULL, y int NOT NULL} # Do not run the optimization if any corresponding table # columns have different affinities. # xfer_check insert4-3.20 0 {1 9} \ {a text, b int} \ {x int, b int} xfer_check insert4-3.21 0 {1 9} \ {a int, b int} \ {x text, b int} # "int" and "integer" are equivalent so the optimization should # run here. # xfer_check insert4-3.22 1 {1 9} \ {a int, b int} \ {x integer, b int} # Ticket #2291. # do_test insert4-4.1a { execsql {CREATE TABLE t4(a, b, UNIQUE(a,b))} } {} ifcapable vacuum { do_test insert4-4.1b { execsql { INSERT INTO t4 VALUES(NULL,0); INSERT INTO t4 VALUES(NULL,1); INSERT INTO t4 VALUES(NULL,1); VACUUM; } } {} } # Check some error conditions: # do_test insert4-5.1 { # Table does not exist. catchsql { INSERT INTO t2 SELECT a, b FROM nosuchtable } } {1 {no such table: nosuchtable}} do_test insert4-5.2 { # Number of columns does not match. catchsql { CREATE TABLE t5(a, b, c); INSERT INTO t4 SELECT * FROM t5; } } {1 {table t4 has 2 columns but 3 values were supplied}} do_test insert4-6.1 { set ::sqlite3_xferopt_count 0 execsql { CREATE INDEX t2_i2 ON t2(x, y COLLATE nocase); CREATE INDEX t2_i1 ON t2(x ASC, y DESC); CREATE INDEX t3_i1 ON t3(a, b); INSERT INTO t2 SELECT * FROM t3; } set ::sqlite3_xferopt_count } {0} do_test insert4-6.2 { set ::sqlite3_xferopt_count 0 execsql { DROP INDEX t2_i2; INSERT INTO t2 SELECT * FROM t3; } set ::sqlite3_xferopt_count } {0} do_test insert4-6.3 { set ::sqlite3_xferopt_count 0 execsql { DROP INDEX t2_i1; CREATE INDEX t2_i1 ON t2(x ASC, y ASC); INSERT INTO t2 SELECT * FROM t3; } set ::sqlite3_xferopt_count } {1} do_test insert4-6.4 { set ::sqlite3_xferopt_count 0 execsql { DROP INDEX t2_i1; CREATE INDEX t2_i1 ON t2(x ASC, y COLLATE RTRIM); INSERT INTO t2 SELECT * FROM t3; } set ::sqlite3_xferopt_count } {0} do_test insert4-6.5 { execsql { CREATE TABLE t6a(x CHECK( x<>'abc' )); INSERT INTO t6a VALUES('ABC'); SELECT * FROM t6a; } } {ABC} do_test insert4-6.6 { execsql { CREATE TABLE t6b(x CHECK( x<>'abc' COLLATE nocase )); } catchsql { INSERT INTO t6b SELECT * FROM t6a; } } {1 {CHECK constraint failed: t6b}} do_test insert4-6.7 { execsql { DROP TABLE t6b; CREATE TABLE t6b(x CHECK( x COLLATE nocase <>'abc' )); } catchsql { INSERT INTO t6b SELECT * FROM t6a; } } {1 {CHECK constraint failed: t6b}} # Ticket [6284df89debdfa61db8073e062908af0c9b6118e] # Disable the xfer optimization if the destination table contains # a foreign key constraint # ifcapable foreignkey { do_test insert4-7.1 { set ::sqlite3_xferopt_count 0 execsql { CREATE TABLE t7a(x INTEGER PRIMARY KEY); INSERT INTO t7a VALUES(123); CREATE TABLE t7b(y INTEGER REFERENCES t7a); CREATE TABLE t7c(z INT); INSERT INTO t7c VALUES(234); INSERT INTO t7b SELECT * FROM t7c; SELECT * FROM t7b; } } {234} do_test insert4-7.2 { set ::sqlite3_xferopt_count } {1} do_test insert4-7.3 { set ::sqlite3_xferopt_count 0 execsql { DELETE FROM t7b; PRAGMA foreign_keys=ON; } catchsql { INSERT INTO t7b SELECT * FROM t7c; } } {1 {FOREIGN KEY constraint failed}} do_test insert4-7.4 { execsql {SELECT * FROM t7b} } {} do_test insert4-7.5 { set ::sqlite3_xferopt_count } {0} do_test insert4-7.6 { set ::sqlite3_xferopt_count 0 execsql { DELETE FROM t7b; DELETE FROM t7c; INSERT INTO t7c VALUES(123); INSERT INTO t7b SELECT * FROM t7c; SELECT * FROM t7b; } } {123} do_test insert4-7.7 { set ::sqlite3_xferopt_count } {0} do_test insert4-7.7 { set ::sqlite3_xferopt_count 0 execsql { PRAGMA foreign_keys=OFF; DELETE FROM t7b; INSERT INTO t7b SELECT * FROM t7c; SELECT * FROM t7b; } } {123} do_test insert4-7.8 { set ::sqlite3_xferopt_count } {1} } # Ticket [676bc02b87176125635cb174d110b431581912bb] # Make sure INTEGER PRIMARY KEY ON CONFLICT ... works with the xfer # optimization. # do_test insert4-8.1 { execsql { DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b); CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT REPLACE, y); INSERT INTO t1 VALUES(1,2); INSERT INTO t2 VALUES(1,3); INSERT INTO t1 SELECT * FROM t2; SELECT * FROM t1; } } {1 3} do_test insert4-8.2 { execsql { DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b); CREATE TABLE t2(x, y); INSERT INTO t1 VALUES(1,2); INSERT INTO t2 VALUES(1,3); INSERT INTO t1 SELECT * FROM t2; SELECT * FROM t1; } } {1 3} do_test insert4-8.3 { execsql { DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT IGNORE, b); CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT IGNORE, y); INSERT INTO t1 VALUES(1,2); INSERT INTO t2 VALUES(1,3); INSERT INTO t1 SELECT * FROM t2; SELECT * FROM t1; } } {1 2} do_test insert4-8.4 { execsql { DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT IGNORE, b); CREATE TABLE t2(x, y); INSERT INTO t1 VALUES(1,2); INSERT INTO t2 VALUES(1,3); INSERT INTO t1 SELECT * FROM t2; SELECT * FROM t1; } } {1 2} do_test insert4-8.5 { execsql { DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT FAIL, b); CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT FAIL, y); INSERT INTO t1 VALUES(1,2); INSERT INTO t2 VALUES(-99,100); INSERT INTO t2 VALUES(1,3); SELECT * FROM t1; } catchsql { INSERT INTO t1 SELECT * FROM t2; } } {1 {UNIQUE constraint failed: t1.a}} do_test insert4-8.6 { execsql { SELECT * FROM t1; } } {-99 100 1 2} do_test insert4-8.7 { execsql { DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ABORT, b); CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ABORT, y); INSERT INTO t1 VALUES(1,2); INSERT INTO t2 VALUES(-99,100); INSERT INTO t2 VALUES(1,3); SELECT * FROM t1; } catchsql { INSERT INTO t1 SELECT * FROM t2; } } {1 {UNIQUE constraint failed: t1.a}} do_test insert4-8.8 { execsql { SELECT * FROM t1; } } {1 2} do_test insert4-8.9 { execsql { DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ROLLBACK, b); CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ROLLBACK, y); INSERT INTO t1 VALUES(1,2); INSERT INTO t2 VALUES(-99,100); INSERT INTO t2 VALUES(1,3); SELECT * FROM t1; } catchsql { BEGIN; INSERT INTO t1 VALUES(2,3); INSERT INTO t1 SELECT * FROM t2; } } {1 {UNIQUE constraint failed: t1.a}} do_test insert4-8.10 { catchsql {COMMIT} } {1 {cannot commit - no transaction is active}} do_test insert4-8.11 { execsql { SELECT * FROM t1; } } {1 2} do_test insert4-8.21 { execsql { DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b); CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT REPLACE, y); INSERT INTO t2 VALUES(1,3); INSERT INTO t1 SELECT * FROM t2; SELECT * FROM t1; } } {1 3} do_test insert4-8.22 { execsql { DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT IGNORE, b); CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT IGNORE, y); INSERT INTO t2 VALUES(1,3); INSERT INTO t1 SELECT * FROM t2; SELECT * FROM t1; } } {1 3} do_test insert4-8.23 { execsql { DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ABORT, b); CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ABORT, y); INSERT INTO t2 VALUES(1,3); INSERT INTO t1 SELECT * FROM t2; SELECT * FROM t1; } } {1 3} do_test insert4-8.24 { execsql { DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT FAIL, b); CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT FAIL, y); INSERT INTO t2 VALUES(1,3); INSERT INTO t1 SELECT * FROM t2; SELECT * FROM t1; } } {1 3} do_test insert4-8.25 { execsql { DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ROLLBACK, b); CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ROLLBACK, y); INSERT INTO t2 VALUES(1,3); INSERT INTO t1 SELECT * FROM t2; SELECT * FROM t1; } } {1 3} do_catchsql_test insert4-9.1 { DROP TABLE IF EXISTS t1; CREATE TABLE t1(x); INSERT INTO t1(x) VALUES(5 COLLATE xyzzy) UNION SELECT 0; } {1 {no such collation sequence: xyzzy}} #------------------------------------------------------------------------- # Check that running an integrity-check does not disable the xfer # optimization for tables with CHECK constraints. # do_execsql_test 10.1 { CREATE TABLE t8( rid INTEGER, pid INTEGER, mid INTEGER, px INTEGER DEFAULT(0) CHECK(px IN(0, 1)) ); CREATE TEMP TABLE x( rid INTEGER, pid INTEGER, mid INTEGER, px INTEGER DEFAULT(0) CHECK(px IN(0, 1)) ); } do_test 10.2 { set sqlite3_xferopt_count 0 execsql { INSERT INTO x SELECT * FROM t8 } set sqlite3_xferopt_count } {1} do_test 10.3 { execsql { PRAGMA integrity_check } set sqlite3_xferopt_count 0 execsql { INSERT INTO x SELECT * FROM t8 } set sqlite3_xferopt_count } {1} finish_test