# 2015 March 20 # # 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. # #*********************************************************************** # # The tests in this file ensure that sorter objects are used by # "INSERT INTO ... SELECT ..." statements when possible. # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix insert6 # Return the number of OP_SorterOpen instructions in the SQL passed as # the only argument if it is compiled using connection [db]. # proc sorter_count {sql} { set res 0 db cache flush db eval "EXPLAIN $sql" x { if {$x(opcode) == "SorterOpen"} { incr res } } return $res } #------------------------------------------------------------------------- # Warm body test. This verifies that the simplest case works for both # regular and WITHOUT ROWID tables. # do_execsql_test 1.1 { CREATE TABLE t2(x UNIQUE ON CONFLICT IGNORE, y, z); WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<99 ) INSERT INTO t2 SELECT abs(random()), abs(random()), abs(random()) FROM cnt; } foreach {tn nSort schema} { 1 3 { CREATE TABLE t1(a, b, c) } 2 4 { CREATE TABLE t1(a PRIMARY KEY, b, c) WITHOUT ROWID } } { do_test 1.$tn.1 { execsql { DROP TABLE IF EXISTS t1 } execsql $schema } {} do_execsql_test 1.$tn.2 { CREATE INDEX t1a ON t1(a); CREATE INDEX t1b ON t1(b); CREATE INDEX t1c ON t1(c); } do_execsql_test 1.$tn.3 { INSERT INTO t1 SELECT x, y, z FROM t2; PRAGMA integrity_check; SELECT count(*) FROM t1; } {ok 100} do_execsql_test 1.$tn.4 { INSERT INTO t1 SELECT -x, y, z FROM t2; PRAGMA integrity_check; } {ok} do_execsql_test 1.$tn.5 { SELECT count(*) FROM t1; } {200} do_test 1.$tn.6 { sorter_count { INSERT INTO t1 SELECT * FROM t2 } } $nSort } #------------------------------------------------------------------------- # The following test cases check that the sorters are disabled if any # of the following are true: # # 2.1: There are one or more UNIQUE constraints or indexes and the # statement specifies "ON CONFLICT FAIL", "IGNORE" or "REPLACE". # # 2.2: The statement does not explicitly specify a conflict mode and # there are one or more PRIMARY KEY or UNIQUE constraints with # "OR FAIL", "OR IGNORE" or "OR REPLACE" as the conflict handling # mode. # # 2.3: There are one or more INSERT triggers on the target table. # # 2.4: The target table is the parent or child of an FK constraint. # do_execsql_test 2.1.1 { CREATE TABLE x1(a, b, c); CREATE INDEX x1a ON x1(a); CREATE TABLE x2(a, b, c); CREATE UNIQUE INDEX x2a ON x2(a); CREATE TABLE x3(a PRIMARY KEY, b, c); CREATE TABLE x4(a PRIMARY KEY, b, c) WITHOUT ROWID; } do_test 2.1.2 { sorter_count { INSERT OR REPLACE INTO x1 SELECT * FROM t2 } } 1 do_test 2.1.3 { sorter_count { INSERT OR REPLACE INTO x2 SELECT * FROM t2 } } 0 do_test 2.1.4 { sorter_count { INSERT OR REPLACE INTO x3 SELECT * FROM t2 } } 0 do_test 2.1.5 { sorter_count { INSERT OR REPLACE INTO x4 SELECT * FROM t2 } } 0 do_test 2.1.6 { sorter_count { INSERT OR IGNORE INTO x1 SELECT * FROM t2 } } 1 do_test 2.1.7 { sorter_count { INSERT OR IGNORE INTO x2 SELECT * FROM t2 } } 0 do_test 2.1.8 { sorter_count { INSERT OR IGNORE INTO x3 SELECT * FROM t2 } } 0 do_test 2.1.9 { sorter_count { INSERT OR IGNORE INTO x4 SELECT * FROM t2 } } 0 do_test 2.1.10 { sorter_count { INSERT OR FAIL INTO x1 SELECT * FROM t2 } } 1 do_test 2.1.11 { sorter_count { INSERT OR FAIL INTO x2 SELECT * FROM t2 } } 0 do_test 2.1.12 { sorter_count { INSERT OR FAIL INTO x3 SELECT * FROM t2 } } 0 do_test 2.1.13 { sorter_count { INSERT OR FAIL INTO x4 SELECT * FROM t2 } } 0 do_test 2.1.14 { sorter_count { INSERT OR ROLLBACK INTO x1 SELECT * FROM t2} } 1 do_test 2.1.15 { sorter_count { INSERT OR ROLLBACK INTO x2 SELECT * FROM t2} } 1 do_test 2.1.16 { sorter_count { INSERT OR ROLLBACK INTO x3 SELECT * FROM t2} } 1 do_test 2.1.17 { sorter_count { INSERT OR ROLLBACK INTO x4 SELECT * FROM t2} } 1 do_test 2.1.18 { sorter_count { INSERT OR ABORT INTO x1 SELECT * FROM t2 } } 1 do_test 2.1.19 { sorter_count { INSERT OR ABORT INTO x2 SELECT * FROM t2 } } 1 do_test 2.1.20 { sorter_count { INSERT OR ABORT INTO x3 SELECT * FROM t2 } } 1 do_test 2.1.21 { sorter_count { INSERT OR ABORT INTO x4 SELECT * FROM t2 } } 1 foreach {tn scount schema} { 2.1 0 { CREATE TABLE t1(a UNIQUE ON CONFLICT FAIL, b, c) } 2.2 0 { CREATE TABLE t1(a, b UNIQUE ON CONFLICT IGNORE, c) } 2.3 0 { CREATE TABLE t1(a, b, c UNIQUE ON CONFLICT REPLACE) } 2.4 0 { CREATE TABLE t1(a PRIMARY KEY ON CONFLICT FAIL, b, c) } 2.5 0 { CREATE TABLE t1(a, b PRIMARY KEY ON CONFLICT IGNORE, c) } 2.6 0 { CREATE TABLE t1(a, b, c PRIMARY KEY ON CONFLICT REPLACE) } 2.7 0 { CREATE TABLE t1(a PRIMARY KEY ON CONFLICT FAIL, b, c) WITHOUT ROWID } 2.8 0 { CREATE TABLE t1(a, b PRIMARY KEY ON CONFLICT IGNORE, c) WITHOUT ROWID } 2.9 0 { CREATE TABLE t1(a, b, c PRIMARY KEY ON CONFLICT REPLACE) WITHOUT ROWID } 3.1 1 { CREATE TABLE t1(a, b, c); CREATE INDEX i1 ON t1(a); } 3.2 0 { CREATE TABLE t1(a, b, c); CREATE INDEX i1 ON t1(a); CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN SELECT 1; END; } 3.3 0 { CREATE TABLE t1(a, b, c); CREATE INDEX i1 ON t1(a); CREATE TRIGGER tr2 BEFORE INSERT ON t1 BEGIN SELECT 1; END; } 4.1 2 { CREATE TABLE t1(a PRIMARY KEY, b, c); CREATE INDEX i1 ON t1(a); CREATE TABLE c1(x, y REFERENCES t1 DEFERRABLE INITIALLY DEFERRED); PRAGMA foreign_keys = 0; } 4.2 0 { CREATE TABLE t1(a PRIMARY KEY, b, c); CREATE INDEX i1 ON t1(a); CREATE TABLE c1(x, y REFERENCES t1 DEFERRABLE INITIALLY DEFERRED); PRAGMA foreign_keys = 1; } 4.3 1 { CREATE TABLE p1(x, y UNIQUE); CREATE TABLE t1(a, b, c REFERENCES p1(y)); CREATE INDEX i1 ON t1(a); PRAGMA foreign_keys = 0; } 4.4 0 { CREATE TABLE p1(x, y UNIQUE); CREATE TABLE t1(a, b, c REFERENCES p1(y)); CREATE INDEX i1 ON t1(a); PRAGMA foreign_keys = 1; } } { execsql { DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS c1; DROP TABLE IF EXISTS p1; } do_test 2.2.$tn { execsql $schema sorter_count { INSERT INTO t1 SELECT * FROM t2 } } $scount } #------------------------------------------------------------------------- # Test that if a UNIQUE constraint is violated and the on conflict mode # is either ABORT or ROLLBACK, the conflict is handled correctly. # # 3.2: Check that conflicts are actually detected. # 3.3: Check that OR ROLLBACK really does rollback the transaction. # 3.4: Check that OR ABORT does not. # do_execsql_test 3.1 { DROP TABLE IF EXISTS t1; CREATE TABLE t1(a PRIMARY KEY, b, c, UNIQUE(b, c)); INSERT INTO t1 VALUES(1, 2, 3); INSERT INTO t1 VALUES(4, 5, 6); INSERT INTO t1 VALUES(7, 8, 9); CREATE TABLE src(a, b, c); } do_catchsql_test 3.2.1 { INSERT INTO src VALUES (10, 11, 12), (7, 14, 12); INSERT INTO t1 SELECT * FROM src; } {1 {UNIQUE constraint failed: t1.a}} do_catchsql_test 3.2.2 { DELETE FROM src; INSERT INTO src VALUES (10, 11, 12), (13, 5, 6); INSERT INTO t1 SELECT * FROM src; } {1 {UNIQUE constraint failed: t1.b, t1.c}} do_catchsql_test 3.2.3.1 { CREATE TABLE t3(a); CREATE UNIQUE INDEX t3a ON t3(a); CREATE TABLE t3src(a); WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<10 ) INSERT INTO t3src SELECT 'abc' FROM cnt; } {0 {}} # execsql { PRAGMA vdbe_trace = 1 } do_catchsql_test 3.2.3.2 { INSERT INTO t3 SELECT * FROM t3src; } {1 {UNIQUE constraint failed: t3.a}} do_catchsql_test 3.3.1 { DELETE FROM src; BEGIN; INSERT INTO src VALUES (10, 11, 12), (7, 13, 14); INSERT OR ROLLBACK INTO t1 SELECT * FROM src; } {1 {UNIQUE constraint failed: t1.a}} do_catchsql_test 3.3.2 { DELETE FROM src; BEGIN; INSERT INTO src VALUES (10, 11, 12), (13, 5, 6); INSERT OR ROLLBACK INTO t1 SELECT * FROM src; } {1 {UNIQUE constraint failed: t1.b, t1.c}} do_test 3.3.3 { sqlite3_get_autocommit db } 1 do_catchsql_test 3.4.1 { DELETE FROM src; BEGIN; INSERT INTO src VALUES (10, 11, 12), (7, 14, 12); INSERT OR ABORT INTO t1 SELECT * FROM src; } {1 {UNIQUE constraint failed: t1.a}} do_catchsql_test 3.4.2 { ROLLBACK; DELETE FROM src; BEGIN; INSERT INTO src VALUES (10, 11, 12), (13, 5, 6); INSERT OR ABORT INTO t1 SELECT * FROM src; } {1 {UNIQUE constraint failed: t1.b, t1.c}} do_test 3.4.3 { sqlite3_get_autocommit db } 0 do_execsql_test 3.4.4 { ROLLBACK } #------------------------------------------------------------------------- # The following tests - 4.* - check that this optimization is actually # doing something helpful. They do this by executing a big # "INSERT INTO SELECT" statement in wal mode with a small pager cache. # Once with "OR FAIL" (so that the sorters are not used) and once with # the default "OR ABORT" (so that they are). # # If the sorters are doing their job, the wal file generated by the # "OR ABORT" case should be much smaller than the "OR FAIL" trial. # proc odd_collate {lhs rhs} { string compare [string range $lhs 6 end] [string range $rhs 6 end] } proc do_insert6_4_test {tn sql} { reset_db db collate odd_collate odd_collate execsql $sql db_save_and_close foreach {tn2 ::onerror ::var} { 1 "OR ABORT" ::sz1 2 "OR FAIL" ::sz2 } { do_test $tn.$tn2 { db_restore_and_reopen db collate odd_collate odd_collate execsql " PRAGMA journal_mode = wal; PRAGMA cache_size = 5; PRAGMA wal_autocheckpoint = 0; INSERT $onerror INTO t1 SELECT * FROM src; " set $var [file size test.db-wal] db close } {} } do_test $tn.3.($::sz1<$::sz2) { expr {$sz1 < ($sz2/2)} } 1 sqlite3 db test.db db collate odd_collate odd_collate integrity_check $tn.4 } do_insert6_4_test 4.1 { CREATE TABLE t1(a, b, c); CREATE UNIQUE INDEX t1a ON t1(a); CREATE UNIQUE INDEX t1bc ON t1(b, c); CREATE TABLE src(x, y, z); WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<2999 ) INSERT INTO src SELECT randomblob(50), randomblob(50), randomblob(50) FROM cnt; } do_insert6_4_test 4.2 { CREATE TABLE t1(a INTEGER PRIMARY KEY, b, x); CREATE UNIQUE INDEX t1b ON t1(b); CREATE INDEX t1x1 ON t1(x); CREATE INDEX t1x2 ON t1(x); CREATE INDEX t1x3 ON t1(x); CREATE INDEX t1x4 ON t1(x); CREATE TABLE src(a, b, x); WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<2999 ) INSERT INTO src SELECT random(), x, zeroblob(50) FROM cnt; } do_insert6_4_test 4.3 { CREATE TABLE t1(a, b, c); CREATE UNIQUE INDEX t1ab ON t1(a, b); CREATE UNIQUE INDEX t1ac ON t1(a, c); CREATE TABLE src(a, b, c); WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<2999 ) INSERT INTO src SELECT zeroblob(50), randomblob(50), randomblob(50) FROM cnt; } db collate odd_collate odd_collate do_insert6_4_test 4.5 { CREATE TABLE t1(t COLLATE odd_collate, v COLLATE odd_collate); CREATE UNIQUE INDEX t1t ON t1(t); CREATE UNIQUE INDEX t1v ON t1(v); CREATE TABLE src(t, v); WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<2999 ) INSERT INTO src SELECT hex(randomblob(50)), hex(randomblob(50)) FROM cnt; } db collate odd_collate odd_collate do_insert6_4_test 4.6 { CREATE TABLE t1(t COLLATE odd_collate PRIMARY KEY) WITHOUT ROWID; CREATE TABLE src(t); WITH cnt(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM cnt WHERE x<2999 ) INSERT INTO src SELECT hex(randomblob(50)) FROM cnt; } finish_test