diff options
Diffstat (limited to 'test')
| -rw-r--r-- | test/autoindex4.test | 52 | ||||
| -rw-r--r-- | test/e_changes.test | 441 | ||||
| -rw-r--r-- | test/e_totalchanges.test | 213 | ||||
| -rw-r--r-- | test/e_wal.test | 229 | ||||
| -rw-r--r-- | test/printf2.test | 20 | ||||
| -rw-r--r-- | test/skipscan6.test | 145 | ||||
| -rw-r--r-- | test/trigger9.test | 33 | ||||
| -rw-r--r-- | test/update.test | 14 |
8 files changed, 1147 insertions, 0 deletions
diff --git a/test/autoindex4.test b/test/autoindex4.test new file mode 100644 index 000000000..6d0865bf7 --- /dev/null +++ b/test/autoindex4.test @@ -0,0 +1,52 @@ +# 2014-10-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 script is testing automatic index creation logic, +# and specifically creation of automatic partial indexes. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl + +do_execsql_test autoindex4-1.0 { + CREATE TABLE t1(a,b); + INSERT INTO t1 VALUES(123,'abc'),(234,'def'),(234,'ghi'),(345,'jkl'); + CREATE TABLE t2(x,y); + INSERT INTO t2 VALUES(987,'zyx'),(654,'wvu'),(987,'rqp'); + + SELECT *, '|' FROM t1, t2 WHERE a=234 AND x=987 ORDER BY +b; +} {234 def 987 rqp | 234 def 987 zyx | 234 ghi 987 rqp | 234 ghi 987 zyx |} +do_execsql_test autoindex4-1.1 { + SELECT *, '|' FROM t1, t2 WHERE a=234 AND x=555; +} {} + +do_execsql_test autoindex4-1.2 { + SELECT *, '|' FROM t1 LEFT JOIN t2 ON a=234 AND x=555; +} {123 abc {} {} | 234 def {} {} | 234 ghi {} {} | 345 jkl {} {} |} +do_execsql_test autoindex4-1.3 { + SELECT *, '|' FROM t1 LEFT JOIN t2 ON x=555 WHERE a=234; +} {234 def {} {} | 234 ghi {} {} |} +do_execsql_test autoindex4-1.4 { + SELECT *, '|' FROM t1 LEFT JOIN t2 WHERE a=234 AND x=555; +} {} + + +do_execsql_test autoindex4-2.0 { + CREATE TABLE t3(e,f); + INSERT INTO t3 VALUES(123,654),(555,444),(234,987); + + SELECT (SELECT count(*) FROM t1, t2 WHERE a=e AND x=f), e, f, '|' + FROM t3 + ORDER BY rowid; +} {1 123 654 | 0 555 444 | 4 234 987 |} + +finish_test diff --git a/test/e_changes.test b/test/e_changes.test new file mode 100644 index 000000000..a77e22a2e --- /dev/null +++ b/test/e_changes.test @@ -0,0 +1,441 @@ +# 2011 October 28 +# +# 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. +# +#*********************************************************************** +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix e_changes + +# Like [do_execsql_test], except it appends the value returned by +# [db changes] to the result of executing the SQL script. +# +proc do_changes_test {tn sql res} { + uplevel [list \ + do_test $tn "concat \[execsql {$sql}\] \[db changes\]" $res + ] +} + + +#-------------------------------------------------------------------------- +# EVIDENCE-OF: R-15996-49369 This function returns the number of rows +# modified, inserted or deleted by the most recently completed INSERT, +# UPDATE or DELETE statement on the database connection specified by the +# only parameter. +# +do_execsql_test 1.0 { + CREATE TABLE t1(a, b); + CREATE TABLE t2(x, y, PRIMARY KEY(x, y)) WITHOUT ROWID; + CREATE INDEX i1 ON t1(a); + CREATE INDEX i2 ON t2(y); +} +foreach {tn schema} { + 1 { + CREATE TABLE t1(a, b); + CREATE INDEX i1 ON t1(b); + } + 2 { + CREATE TABLE t1(a, b, PRIMARY KEY(a, b)) WITHOUT ROWID; + CREATE INDEX i1 ON t1(b); + } +} { + reset_db + execsql $schema + + # Insert 1 row. + do_changes_test 1.$tn.1 { INSERT INTO t1 VALUES(0, 0) } 1 + + # Insert 10 rows. + do_changes_test 1.$tn.2 { + WITH rows(i, j) AS ( + SELECT 1, 1 UNION ALL SELECT i+1, j+i FROM rows WHERE i<10 + ) + INSERT INTO t1 SELECT * FROM rows + } 10 + + # Modify 5 rows. + do_changes_test 1.$tn.3 { + UPDATE t1 SET b=b+1 WHERE a<5; + } 5 + + # Delete 4 rows + do_changes_test 1.$tn.4 { + DELETE FROM t1 WHERE a>6 + } 4 + + # Check the "on the database connecton specified" part of hte + # requirement - changes made by other connections do not show up in + # the return value of sqlite3_changes(). + do_test 1.$tn.5 { + sqlite3 db2 test.db + execsql { INSERT INTO t1 VALUES(-1, -1) } db2 + db2 changes + } 1 + do_test 1.$tn.6 { + db changes + } 4 + db2 close + + # Test that statements that modify no rows because they hit UNIQUE + # constraints set the sqlite3_changes() value to 0. Regardless of + # whether or not they are executed inside an explicit transaction. + # + # 1.$tn.8-9: outside of a transaction + # 1.$tn.10-12: inside a transaction + # + do_changes_test 1.$tn.7 { + CREATE UNIQUE INDEX i2 ON t1(a); + } 4 + do_catchsql_test 1.$tn.8 { + INSERT INTO t1 VALUES('a', 0), ('b', 0), ('c', 0), (0, 11); + } {1 {UNIQUE constraint failed: t1.a}} + do_test 1.$tn.9 { db changes } 0 + do_catchsql_test 1.$tn.10 { + BEGIN; + INSERT INTO t1 VALUES('a', 0), ('b', 0), ('c', 0), (0, 11); + } {1 {UNIQUE constraint failed: t1.a}} + do_test 1.$tn.11 { db changes } 0 + do_changes_test 1.$tn.12 COMMIT 0 + +} + + +#-------------------------------------------------------------------------- +# EVIDENCE-OF: R-44877-05564 Executing any other type of SQL statement +# does not modify the value returned by this function. +# +reset_db +do_changes_test 2.1 { CREATE TABLE t1(x) } 0 +do_changes_test 2.2 { + WITH d(y) AS (SELECT 1 UNION ALL SELECT y+1 FROM d WHERE y<47) + INSERT INTO t1 SELECT y FROM d; +} 47 + +# The statement above set changes() to 47. Check that none of the following +# modify this. +do_changes_test 2.3 { SELECT count(x) FROM t1 } {47 47} +do_changes_test 2.4 { DROP TABLE t1 } 47 +do_changes_test 2.5 { CREATE TABLE t1(x) } 47 +do_changes_test 2.6 { ALTER TABLE t1 ADD COLUMN b } 47 + + +#-------------------------------------------------------------------------- +# EVIDENCE-OF: R-53938-27527 Only changes made directly by the INSERT, +# UPDATE or DELETE statement are considered - auxiliary changes caused +# by triggers, foreign key actions or REPLACE constraint resolution are +# not counted. +# +# 3.1.*: triggers +# 3.2.*: foreign key actions +# 3.3.*: replace constraints +# +reset_db +do_execsql_test 3.1.0 { + CREATE TABLE log(x); + CREATE TABLE p1(one PRIMARY KEY, two); + + CREATE TRIGGER tr_ai AFTER INSERT ON p1 BEGIN + INSERT INTO log VALUES('insert'); + END; + CREATE TRIGGER tr_bd BEFORE DELETE ON p1 BEGIN + INSERT INTO log VALUES('delete'); + END; + CREATE TRIGGER tr_au AFTER UPDATE ON p1 BEGIN + INSERT INTO log VALUES('update'); + END; + +} + +do_changes_test 3.1.1 { + INSERT INTO p1 VALUES('a', 'A'), ('b', 'B'), ('c', 'C'); +} 3 +do_changes_test 3.1.2 { + UPDATE p1 SET two = two||two; +} 3 +do_changes_test 3.1.3 { + DELETE FROM p1 WHERE one IN ('a', 'c'); +} 2 +do_execsql_test 3.1.4 { + -- None of the inserts on table log were counted. + SELECT count(*) FROM log +} 8 + +do_execsql_test 3.2.0 { + DELETE FROM p1; + INSERT INTO p1 VALUES('a', 'A'), ('b', 'B'), ('c', 'C'); + + CREATE TABLE c1(a, b, FOREIGN KEY(a) REFERENCES p1 ON DELETE SET NULL); + CREATE TABLE c2(a, b, FOREIGN KEY(a) REFERENCES p1 ON DELETE SET DEFAULT); + CREATE TABLE c3(a, b, FOREIGN KEY(a) REFERENCES p1 ON DELETE CASCADE); + INSERT INTO c1 VALUES('a', 'aaa'); + INSERT INTO c2 VALUES('b', 'bbb'); + INSERT INTO c3 VALUES('c', 'ccc'); + + INSERT INTO p1 VALUES('d', 'D'), ('e', 'E'), ('f', 'F'); + CREATE TABLE c4(a, b, FOREIGN KEY(a) REFERENCES p1 ON UPDATE SET NULL); + CREATE TABLE c5(a, b, FOREIGN KEY(a) REFERENCES p1 ON UPDATE SET DEFAULT); + CREATE TABLE c6(a, b, FOREIGN KEY(a) REFERENCES p1 ON UPDATE CASCADE); + INSERT INTO c4 VALUES('d', 'aaa'); + INSERT INTO c5 VALUES('e', 'bbb'); + INSERT INTO c6 VALUES('f', 'ccc'); + + PRAGMA foreign_keys = ON; +} + +do_changes_test 3.2.1 { DELETE FROM p1 WHERE one = 'a' } 1 +do_changes_test 3.2.2 { DELETE FROM p1 WHERE one = 'b' } 1 +do_changes_test 3.2.3 { DELETE FROM p1 WHERE one = 'c' } 1 +do_execsql_test 3.2.4 { + SELECT * FROM c1; + SELECT * FROM c2; + SELECT * FROM c3; +} {{} aaa {} bbb} + +do_changes_test 3.2.5 { UPDATE p1 SET one = 'g' WHERE one = 'd' } 1 +do_changes_test 3.2.6 { UPDATE p1 SET one = 'h' WHERE one = 'e' } 1 +do_changes_test 3.2.7 { UPDATE p1 SET one = 'i' WHERE one = 'f' } 1 +do_execsql_test 3.2.8 { + SELECT * FROM c4; + SELECT * FROM c5; + SELECT * FROM c6; +} {{} aaa {} bbb i ccc} + +do_execsql_test 3.3.0 { + CREATE TABLE r1(a UNIQUE, b UNIQUE); + INSERT INTO r1 VALUES('i', 'i'); + INSERT INTO r1 VALUES('ii', 'ii'); + INSERT INTO r1 VALUES('iii', 'iii'); + INSERT INTO r1 VALUES('iv', 'iv'); + INSERT INTO r1 VALUES('v', 'v'); + INSERT INTO r1 VALUES('vi', 'vi'); + INSERT INTO r1 VALUES('vii', 'vii'); +} + +do_changes_test 3.3.1 { INSERT OR REPLACE INTO r1 VALUES('i', 1) } 1 +do_changes_test 3.3.2 { INSERT OR REPLACE INTO r1 VALUES('iv', 'v') } 1 +do_changes_test 3.3.3 { UPDATE OR REPLACE r1 SET b='v' WHERE a='iii' } 1 +do_changes_test 3.3.4 { UPDATE OR REPLACE r1 SET b='vi',a='vii' WHERE a='ii' } 1 +do_execsql_test 3.3.5 { + SELECT * FROM r1 ORDER BY a; +} {i 1 iii v vii vi} + + +#-------------------------------------------------------------------------- +# EVIDENCE-OF: R-09813-48563 The value returned by sqlite3_changes() +# immediately after an INSERT, UPDATE or DELETE statement run on a view +# is always zero. +# +reset_db +do_execsql_test 4.1 { + CREATE TABLE log(log); + CREATE TABLE t1(x, y); + INSERT INTO t1 VALUES(1, 2); + INSERT INTO t1 VALUES(3, 4); + INSERT INTO t1 VALUES(5, 6); + + CREATE VIEW v1 AS SELECT * FROM t1; + CREATE TRIGGER v1_i INSTEAD OF INSERT ON v1 BEGIN + INSERT INTO log VALUES('insert'); + END; + CREATE TRIGGER v1_u INSTEAD OF UPDATE ON v1 BEGIN + INSERT INTO log VALUES('update'), ('update'); + END; + CREATE TRIGGER v1_d INSTEAD OF DELETE ON v1 BEGIN + INSERT INTO log VALUES('delete'), ('delete'), ('delete'); + END; +} + +do_changes_test 4.2.1 { INSERT INTO t1 SELECT * FROM t1 } 3 +do_changes_test 4.2.2 { INSERT INTO v1 VALUES(1, 2) } 0 + +do_changes_test 4.3.1 { INSERT INTO t1 SELECT * FROM t1 } 6 +do_changes_test 4.3.2 { UPDATE v1 SET y='xyz' WHERE x=1 } 0 + +do_changes_test 4.4.1 { INSERT INTO t1 SELECT * FROM t1 } 12 +do_changes_test 4.4.2 { DELETE FROM v1 WHERE x=5 } 0 + + +#-------------------------------------------------------------------------- +# EVIDENCE-OF: R-32918-61474 Before entering a trigger program the value +# returned by sqlite3_changes() function is saved. After the trigger +# program has finished, the original value is restored. +# +reset_db +db func my_changes my_changes +set ::changes [list] +proc my_changes {x} { + set res [db changes] + lappend ::changes $x $res + return $res +} + +do_execsql_test 5.1.0 { + CREATE TABLE t1(a INTEGER PRIMARY KEY, b); + CREATE TABLE t2(x); + INSERT INTO t1 VALUES(1, NULL); + INSERT INTO t1 VALUES(2, NULL); + INSERT INTO t1 VALUES(3, NULL); + CREATE TRIGGER AFTER UPDATE ON t1 BEGIN + INSERT INTO t2 VALUES('a'), ('b'), ('c'); + SELECT my_changes('trigger'); + END; +} + +do_execsql_test 5.1.1 { + INSERT INTO t2 VALUES('a'), ('b'); + UPDATE t1 SET b = my_changes('update'); + SELECT * FROM t1; +} {1 2 2 2 3 2} + +# Value is being restored to "2" when the trigger program exits. +do_test 5.1.2 { + set ::changes +} {update 2 trigger 3 update 2 trigger 3 update 2 trigger 3} + + +reset_db +do_execsql_test 5.2.0 { + CREATE TABLE t1(a, b); + CREATE TABLE log(x); + INSERT INTO t1 VALUES(1, 0); + INSERT INTO t1 VALUES(2, 0); + INSERT INTO t1 VALUES(3, 0); + CREATE TRIGGER t1_a_u AFTER UPDATE ON t1 BEGIN + INSERT INTO log VALUES(old.b || ' -> ' || new.b || ' c = ' || changes() ); + END; + CREATE TABLE t2(a); + INSERT INTO t2 VALUES(1), (2), (3); + UPDATE t1 SET b = changes(); +} +do_execsql_test 5.2.1 { + SELECT * FROM t1; +} {1 3 2 3 3 3} +do_execsql_test 5.2.2 { + SELECT * FROM log; +} {{0 -> 3 c = 3} {0 -> 3 c = 3} {0 -> 3 c = 3}} + + +#-------------------------------------------------------------------------- +# EVIDENCE-OF: R-17146-37073 Within a trigger program each INSERT, +# UPDATE and DELETE statement sets the value returned by +# sqlite3_changes() upon completion as normal. Of course, this value +# will not include any changes performed by sub-triggers, as the +# sqlite3_changes() value will be saved and restored after each +# sub-trigger has run. +reset_db +do_execsql_test 6.0 { + + CREATE TABLE t1(a, b); + CREATE TABLE t2(a, b); + CREATE TABLE t3(a, b); + CREATE TABLE log(x); + + CREATE TRIGGER t1_i BEFORE INSERT ON t1 BEGIN + INSERT INTO t2 VALUES(new.a, new.b), (new.a, new.b); + INSERT INTO log VALUES('t2->' || changes()); + END; + + CREATE TRIGGER t2_i AFTER INSERT ON t2 BEGIN + INSERT INTO t3 VALUES(new.a, new.b), (new.a, new.b), (new.a, new.b); + INSERT INTO log VALUES('t3->' || changes()); + END; + + CREATE TRIGGER t1_u AFTER UPDATE ON t1 BEGIN + UPDATE t2 SET b=new.b WHERE a=old.a; + INSERT INTO log VALUES('t2->' || changes()); + END; + + CREATE TRIGGER t2_u BEFORE UPDATE ON t2 BEGIN + UPDATE t3 SET b=new.b WHERE a=old.a; + INSERT INTO log VALUES('t3->' || changes()); + END; + + CREATE TRIGGER t1_d AFTER DELETE ON t1 BEGIN + DELETE FROM t2 WHERE a=old.a AND b=old.b; + INSERT INTO log VALUES('t2->' || changes()); + END; + + CREATE TRIGGER t2_d BEFORE DELETE ON t2 BEGIN + DELETE FROM t3 WHERE a=old.a AND b=old.b; + INSERT INTO log VALUES('t3->' || changes()); + END; +} + +do_changes_test 6.1 { + INSERT INTO t1 VALUES('+', 'o'); + SELECT * FROM log; +} {t3->3 t3->3 t2->2 1} + +do_changes_test 6.2 { + DELETE FROM log; + UPDATE t1 SET b='*'; + SELECT * FROM log; +} {t3->6 t3->6 t2->2 1} + +do_changes_test 6.3 { + DELETE FROM log; + DELETE FROM t1; + SELECT * FROM log; +} {t3->6 t3->0 t2->2 1} + + +#-------------------------------------------------------------------------- +# EVIDENCE-OF: R-43399-09409 This means that if the changes() SQL +# function (or similar) is used by the first INSERT, UPDATE or DELETE +# statement within a trigger, it returns the value as set when the +# calling statement began executing. +# +# EVIDENCE-OF: R-53215-27584 If it is used by the second or subsequent +# such statement within a trigger program, the value returned reflects +# the number of rows modified by the previous INSERT, UPDATE or DELETE +# statement within the same trigger. +# +reset_db +do_execsql_test 7.1 { + CREATE TABLE q1(t); + CREATE TABLE q2(u, v); + CREATE TABLE q3(w); + + CREATE TRIGGER q2_insert BEFORE INSERT ON q2 BEGIN + + /* changes() returns value from previous I/U/D in callers context */ + INSERT INTO q1 VALUES('1:' || changes()); + + /* changes() returns value of previous I/U/D in this context */ + INSERT INTO q3 VALUES(changes()), (2), (3); + INSERT INTO q1 VALUES('2:' || changes()); + INSERT INTO q3 VALUES(changes() + 3), (changes()+4); + SELECT 'this does not affect things!'; + INSERT INTO q1 VALUES('3:' || changes()); + UPDATE q3 SET w = w+10 WHERE w%2; + INSERT INTO q1 VALUES('4:' || changes()); + DELETE FROM q3; + INSERT INTO q1 VALUES('5:' || changes()); + END; +} + +do_execsql_test 7.2 { + INSERT INTO q2 VALUES('x', 'y'); + SELECT * FROM q1; +} { + 1:0 2:3 3:2 4:3 5:5 +} + +do_execsql_test 7.3 { + DELETE FROM q1; + INSERT INTO q2 VALUES('x', 'y'); + SELECT * FROM q1; +} { + 1:5 2:3 3:2 4:3 5:5 +} + + + +finish_test diff --git a/test/e_totalchanges.test b/test/e_totalchanges.test new file mode 100644 index 000000000..ee163c914 --- /dev/null +++ b/test/e_totalchanges.test @@ -0,0 +1,213 @@ +# 2011 May 06 +# +# 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. +# +#*********************************************************************** +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix e_totalchanges + +# Like [do_execsql_test], except it appends the value returned by +# [db total_changes] to the result of executing the SQL script. +# +proc do_tc_test {tn sql res} { + uplevel [list \ + do_test $tn "concat \[execsql {$sql}\] \[db total_changes\]" $res + ] +} + +do_execsql_test 1.0 { + CREATE TABLE t1(a, b); + CREATE INDEX t1_b ON t1(b); + CREATE TABLE t2(x, y, PRIMARY KEY(x, y)) WITHOUT ROWID; + CREATE INDEX t2_y ON t2(y); +} + + +#-------------------------------------------------------------------------- +# EVIDENCE-OF: R-65438-26258 This function returns the total number of +# rows inserted, modified or deleted by all INSERT, UPDATE or DELETE +# statements completed since the database connection was opened, +# including those executed as part of trigger programs. +# +# 1.1.*: different types of I/U/D statements, +# 1.2.*: trigger programs. +# +do_tc_test 1.1.1 { + INSERT INTO t1 VALUES(1, 2); + INSERT INTO t1 VALUES(3, 4); + UPDATE t1 SET a = a+1; + DELETE FROM t1; +} {6} +do_tc_test 1.1.2 { + DELETE FROM t1 +} {6} + +do_tc_test 1.1.3 { + WITH data(a,b) AS ( + SELECT 0, 0 UNION ALL SELECT a+1, b+1 FROM data WHERE a<99 + ) + INSERT INTO t1 SELECT * FROM data; +} {106} + +do_tc_test 1.1.4 { + INSERT INTO t2 SELECT * FROM t1 WHERE a<50; + UPDATE t2 SET y=y+1; +} {206} + +do_tc_test 1.1.5 { + DELETE FROM t2 WHERE y<=25 +} {231} + +do_execsql_test 1.2.1 { + DELETE FROM t1; + DELETE FROM t2; +} +sqlite3 db test.db ; # To reset total_changes +do_tc_test 1.2.2 { + CREATE TABLE log(detail); + CREATE TRIGGER t1_after_insert AFTER INSERT ON t1 BEGIN + INSERT INTO log VALUES('inserted into t1'); + END; + + CREATE TRIGGER t1_before_delete BEFORE DELETE ON t1 BEGIN + INSERT INTO log VALUES('deleting from t1'); + INSERT INTO log VALUES('here we go!'); + END; + + CREATE TRIGGER t1_after_update AFTER UPDATE ON t1 BEGIN + INSERT INTO log VALUES('update'); + DELETE FROM log; + END; + + INSERT INTO t1 VALUES('a', 'b'); -- 1 + 1 + UPDATE t1 SET b='c'; -- 1 + 1 + 2 + DELETE FROM t1; -- 1 + 1 + 1 +} {9} + +#-------------------------------------------------------------------------- +# EVIDENCE-OF: R-61766-15253 Executing any other type of SQL statement +# does not affect the value returned by sqlite3_total_changes(). +do_tc_test 2.1 { + INSERT INTO t1 VALUES(1, 2), (3, 4); + INSERT INTO t2 VALUES(1, 2), (3, 4); +} {15} +do_tc_test 2.2 { + SELECT count(*) FROM t1; +} {2 15} +do_tc_test 2.3 { + CREATE TABLE t4(a, b); + ALTER TABLE t4 ADD COLUMN c; + CREATE INDEX i4 ON t4(c); + ALTER TABLE t4 RENAME TO t5; + ANALYZE; + BEGIN; + DROP TABLE t2; + ROLLBACK; + VACUUM; +} {15} + + +#-------------------------------------------------------------------------- +# EVIDENCE-OF: R-36043-10590 Changes made as part of foreign key +# actions are included in the count, but those made as part of REPLACE +# constraint resolution are not. +# +# 3.1.*: foreign key actions +# 3.2.*: REPLACE constraints. +# +sqlite3 db test.db ; # To reset total_changes +do_tc_test 3.1.1 { + CREATE TABLE p1(c PRIMARY KEY, d); + CREATE TABLE c1(a, b, FOREIGN KEY(a) REFERENCES p1 ON DELETE SET NULL); + CREATE TABLE c2(a, b, FOREIGN KEY(a) REFERENCES p1 ON DELETE CASCADE); + CREATE TABLE c3(a, b, FOREIGN KEY(a) REFERENCES p1 ON DELETE SET DEFAULT); + + INSERT INTO p1 VALUES(1, 'one'); + INSERT INTO p1 VALUES(2, 'two'); + INSERT INTO p1 VALUES(3, 'three'); + INSERT INTO p1 VALUES(4, 'four'); + + INSERT INTO c1 VALUES(1, 'i'); + INSERT INTO c2 VALUES(2, 'ii'); + INSERT INTO c3 VALUES(3, 'iii'); + PRAGMA foreign_keys = ON; +} {7} + +do_tc_test 3.1.2 { DELETE FROM p1 WHERE c=1; } {9} +do_tc_test 3.1.3 { DELETE FROM p1 WHERE c=2; } {11} +do_tc_test 3.1.4 { DELETE FROM p1 WHERE c=3; } {13} +do_tc_test 3.1.5 { DELETE FROM p1 WHERE c=4; } {14} ; # only 1 this time. + +sqlite3 db test.db ; # To reset total_changes +do_tc_test 3.1.6 { + DROP TABLE c1; + DROP TABLE c2; + DROP TABLE c3; + CREATE TABLE c1(a, b, FOREIGN KEY(a) REFERENCES p1 ON UPDATE SET NULL); + CREATE TABLE c2(a, b, FOREIGN KEY(a) REFERENCES p1 ON UPDATE CASCADE); + CREATE TABLE c3(a, b, FOREIGN KEY(a) REFERENCES p1 ON UPDATE SET DEFAULT); + + INSERT INTO p1 VALUES(1, 'one'); + INSERT INTO p1 VALUES(2, 'two'); + INSERT INTO p1 VALUES(3, 'three'); + INSERT INTO p1 VALUES(4, 'four'); + + INSERT INTO c1 VALUES(1, 'i'); + INSERT INTO c2 VALUES(2, 'ii'); + INSERT INTO c3 VALUES(3, 'iii'); + PRAGMA foreign_keys = ON; +} {7} + +do_tc_test 3.1.7 { UPDATE p1 SET c=c+4 WHERE c=1; } {9} +do_tc_test 3.1.8 { UPDATE p1 SET c=c+4 WHERE c=2; } {11} +do_tc_test 3.1.9 { UPDATE p1 SET c=c+4 WHERE c=3; } {13} +do_tc_test 3.1.10 { UPDATE p1 SET c=c+4 WHERE c=4; } {14} ; # only 1 this time. + +sqlite3 db test.db ; # To reset total_changes +do_tc_test 3.2.1 { + CREATE TABLE t3(a UNIQUE, b UNIQUE); + INSERT INTO t3 VALUES('one', 'one'); + INSERT INTO t3 VALUES('two', 'two'); + INSERT OR REPLACE INTO t3 VALUES('one', 'two'); +} {3} + +do_tc_test 3.2.2 { + INSERT INTO t3 VALUES('three', 'one'); + UPDATE OR REPLACE t3 SET b='two' WHERE b='one'; + SELECT * FROM t3; +} {three two 5} + +#-------------------------------------------------------------------------- +# EVIDENCE-OF: R-54872-08741 Changes to a view that are intercepted by +# INSTEAD OF triggers are not counted. +# +sqlite3 db test.db ; # To reset total_changes +do_tc_test 4.1 { + CREATE TABLE t6(x); + CREATE VIEW v1 AS SELECT * FROM t6; + CREATE TRIGGER v1_tr1 INSTEAD OF INSERT ON v1 BEGIN + SELECT 'no-op'; + END; + + INSERT INTO v1 VALUES('a'); + INSERT INTO v1 VALUES('b'); +} {0} +do_tc_test 4.2 { + CREATE TRIGGER v1_tr2 INSTEAD OF INSERT ON v1 BEGIN + INSERT INTO t6 VALUES(new.x); + END; + + INSERT INTO v1 VALUES('c'); + INSERT INTO v1 VALUES('d'); +} {2} + + +finish_test diff --git a/test/e_wal.test b/test/e_wal.test new file mode 100644 index 000000000..a5e074f49 --- /dev/null +++ b/test/e_wal.test @@ -0,0 +1,229 @@ +# 2011 May 06 +# +# 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. +# +#*********************************************************************** +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix e_wal + +db close +testvfs oldvfs -iversion 1 + + +# EVIDENCE-OF: R-58297-14483 WAL databases can be created, read, and +# written even if shared memory is unavailable as long as the +# locking_mode is set to EXCLUSIVE before the first attempted access. +# +# EVIDENCE-OF: R-00449-33772 This feature allows WAL databases to be +# created, read, and written by legacy VFSes that lack the "version 2" +# shared-memory methods xShmMap, xShmLock, xShmBarrier, and xShmUnmap on +# the sqlite3_io_methods object. +# +# 1.1: "create" tests. +# 1.2: "read" tests. +# 1.3: "write" tests. +# +# All three done with VFS "oldvfs", which has iVersion==1 and so does +# not support shared memory. +# +sqlite3 db test.db -vfs oldvfs +do_execsql_test 1.1.1 { + PRAGMA journal_mode = WAL; +} {delete} +do_execsql_test 1.1.2 { + PRAGMA locking_mode = EXCLUSIVE; + PRAGMA journal_mode = WAL; +} {exclusive wal} +do_execsql_test 1.1.3 { + CREATE TABLE t1(x, y); + INSERT INTO t1 VALUES(1, 2); +} {} +do_test 1.1.4 { + list [file exists test.db-shm] [file exists test.db-wal] +} {0 1} + +do_test 1.2.1 { + db close + sqlite3 db test.db -vfs oldvfs + catchsql { SELECT * FROM t1 } +} {1 {unable to open database file}} +do_test 1.2.2 { + execsql { PRAGMA locking_mode = EXCLUSIVE } + execsql { SELECT * FROM t1 } +} {1 2} +do_test 1.2.3 { + list [file exists test.db-shm] [file exists test.db-wal] +} {0 1} + +do_test 1.3.1 { + db close + sqlite3 db test.db -vfs oldvfs + catchsql { INSERT INTO t1 VALUES(3, 4) } +} {1 {unable to open database file}} +do_test 1.3.2 { + execsql { PRAGMA locking_mode = EXCLUSIVE } + execsql { INSERT INTO t1 VALUES(3, 4) } + execsql { SELECT * FROM t1 } +} {1 2 3 4} +do_test 1.3.3 { + list [file exists test.db-shm] [file exists test.db-wal] +} {0 1} + +# EVIDENCE-OF: R-31969-57825 If EXCLUSIVE locking mode is set prior to +# the first WAL-mode database access, then SQLite never attempts to call +# any of the shared-memory methods and hence no shared-memory wal-index +# is ever created. +# +db close +sqlite3 db test.db +do_execsql_test 2.1.1 { + PRAGMA locking_mode = EXCLUSIVE; + SELECT * FROM t1; +} {exclusive 1 2 3 4} +do_test 2.1.2 { + list [file exists test.db-shm] [file exists test.db-wal] +} {0 1} + +# EVIDENCE-OF: R-36328-16367 In that case, the database connection +# remains in EXCLUSIVE mode as long as the journal mode is WAL; attempts +# to change the locking mode using "PRAGMA locking_mode=NORMAL;" are +# no-ops. +# +do_execsql_test 2.2.1 { + PRAGMA locking_mode = NORMAL; + SELECT * FROM t1; +} {exclusive 1 2 3 4} +do_test 2.2.2 { + sqlite3 db2 test.db + catchsql {SELECT * FROM t1} db2 +} {1 {database is locked}} +db2 close + +# EVIDENCE-OF: R-63522-46088 The only way to change out of EXCLUSIVE +# locking mode is to first change out of WAL journal mode. +# +do_execsql_test 2.3.1 { + PRAGMA journal_mode = DELETE; + SELECT * FROM t1; +} {delete 1 2 3 4} +do_test 2.3.2 { + sqlite3 db2 test.db + catchsql {SELECT * FROM t1} db2 +} {1 {database is locked}} +do_execsql_test 2.3.3 { + PRAGMA locking_mode = NORMAL; + SELECT * FROM t1; +} {normal 1 2 3 4} +do_test 2.3.4 { + sqlite3 db2 test.db + catchsql {SELECT * FROM t1} db2 +} {0 {1 2 3 4}} +db2 close +db close + + +# EVIDENCE-OF: R-57239-11845 If NORMAL locking mode is in effect for the +# first WAL-mode database access, then the shared-memory wal-index is +# created. +# +do_test 3.0 { + sqlite3 db test.db + execsql { PRAGMA journal_mode = WAL } + db close +} {} +do_test 3.1 { + sqlite3 db test.db + execsql { SELECT * FROM t1 } + list [file exists test.db-shm] [file exists test.db-wal] +} {1 1} + +# EVIDENCE-OF: R-13779-07711 As long as exactly one connection is using +# a shared-memory wal-index, the locking mode can be changed freely +# between NORMAL and EXCLUSIVE. +# +do_execsql_test 3.2.1 { + PRAGMA locking_mode = EXCLUSIVE; + PRAGMA locking_mode = NORMAL; + PRAGMA locking_mode = EXCLUSIVE; + INSERT INTO t1 VALUES(5, 6); +} {exclusive normal exclusive} +do_test 3.2.2 { + sqlite3 db2 test.db + catchsql { SELECT * FROM t1 } db2 +} {1 {database is locked}} + +# EVIDENCE-OF: R-10993-11647 It is only when the shared-memory wal-index +# is omitted, when the locking mode is EXCLUSIVE prior to the first +# WAL-mode database access, that the locking mode is stuck in EXCLUSIVE. +# +do_execsql_test 3.2.3 { + PRAGMA locking_mode = NORMAL; + SELECT * FROM t1; +} {normal 1 2 3 4 5 6} +do_test 3.2.4 { + catchsql { SELECT * FROM t1 } db2 +} {0 {1 2 3 4 5 6}} + +do_catchsql_test 3.2.5 { + PRAGMA locking_mode = EXCLUSIVE; + INSERT INTO t1 VALUES(7, 8); +} {1 {database is locked}} + +db2 close + +# EVIDENCE-OF: R-46197-42811 This means that the underlying VFS must +# support the "version 2" shared-memory. +# +# EVIDENCE-OF: R-55316-21772 If the VFS does not support shared-memory +# methods, then the attempt to open a database that is already in WAL +# mode, or the attempt convert a database into WAL mode, will fail. +# +db close +do_test 3.4.1 { + sqlite3 db test.db -vfs oldvfs + catchsql { SELECT * FROM t1 } +} {1 {unable to open database file}} +db close +do_test 3.4.2 { + forcedelete test.db2 + sqlite3 db test.db2 -vfs oldvfs + catchsql { PRAGMA journal_mode = WAL } +} {0 delete} +db close + + +# EVIDENCE-OF: R-22428-28959 To prevent older versions of SQLite from +# trying to recover a WAL-mode database (and making matters worse) the +# database file format version numbers (bytes 18 and 19 in the database +# header) are increased from 1 to 2 in WAL mode. +# +reset_db +do_execsql_test 4.1.1 { CREATE TABLE t1(x, y) } +do_test 4.1.2 { hexio_read test.db 18 2 } {0101} +do_execsql_test 4.1.3 { PRAGMA journal_mode = wAL } {wal} +do_test 4.1.4 { hexio_read test.db 18 2 } {0202} + + +# EVIDENCE-OF: R-02535-05811 One can explicitly change out of WAL mode +# using a pragma such as this: PRAGMA journal_mode=DELETE; +# +do_execsql_test 4.2.1 { INSERT INTO t1 VALUES(1, 1); } {} +do_test 4.2.2 { file exists test.db-wal } {1} +do_execsql_test 4.2.3 { PRAGMA journal_mode = delete } {delete} +do_test 4.2.4 { file exists test.db-wal } {0} + +# EVIDENCE-OF: R-60175-02388 Deliberately changing out of WAL mode +# changes the database file format version numbers back to 1 so that +# older versions of SQLite can once again access the database file. +# +do_test 4.3 { hexio_read test.db 18 2 } {0101} + +finish_test diff --git a/test/printf2.test b/test/printf2.test index 4cb1783bf..21deeb779 100644 --- a/test/printf2.test +++ b/test/printf2.test @@ -95,5 +95,25 @@ do_execsql_test printf2-2.3 { SELECT printf('%s=(%d/%g/%s)',a) FROM t1 ORDER BY a; } {-1=(0/0/) 1=(0/0/) 1.5=(0/0/) abc=(0/0/)} +# The precision of the %c conversion causes the character to repeat. +# +do_execsql_test printf2-3.1 { + SELECT printf('|%110.100c|','*'); +} {{| ****************************************************************************************************|}} +do_execsql_test printf2-3.2 { + SELECT printf('|%-110.100c|','*'); +} {{|**************************************************************************************************** |}} +do_execsql_test printf2-3.3 { + SELECT printf('|%9.8c|%-9.8c|','*','*'); +} {{| ********|******** |}} +do_execsql_test printf2-3.4 { + SELECT printf('|%8.8c|%-8.8c|','*','*'); +} {|********|********|} +do_execsql_test printf2-3.5 { + SELECT printf('|%7.8c|%-7.8c|','*','*'); +} {|********|********|} + + + finish_test diff --git a/test/skipscan6.test b/test/skipscan6.test new file mode 100644 index 000000000..9eda9a66f --- /dev/null +++ b/test/skipscan6.test @@ -0,0 +1,145 @@ +# 2014-10-21 +# +# 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 tests of the "skip-scan" query strategy. In +# particular, this file verifies that use of all columns of an index +# is always preferred over the use of a skip-scan on some columns of +# the same index. Because of difficulties in scoring a skip-scan, +# the skip-scan can sometimes come out with a lower raw score when +# using STAT4. But the query planner should detect this and use the +# full index rather than the skip-scan. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set testprefix skipscan6 + +ifcapable !stat4 { + finish_test + return +} + +do_execsql_test 1.1 { + CREATE TABLE t1( + aa int, + bb int, + cc int, + dd int, + ee int + ); + CREATE INDEX ix on t1(aa, bb, cc, dd DESC); + ANALYZE sqlite_master; + INSERT INTO sqlite_stat1 VALUES('t1','ix','2695116 1347558 264 18 2'); + INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 196859 196859 32 1','0 15043 15043 92468 92499','0 19 286 81846 92499',X'0609010804031552977BD725BD28'); + INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 14687 161 1 1','0 289067 299306 299457 299457','0 199 6772 273984 299457',X'060902020403013406314D67456415B819'); + INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 19313 19308 22 1','0 325815 325815 343725 343746','0 261 9545 315009 343746',X'060902080403018A49B0A3AD1ED931'); + INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 25047 9051 15 1','0 350443 350443 356590 356604','0 266 9795 325519 356604',X'06090208040301914C2DD2E91F93CF'); + INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 42327 9906 7 1','0 376381 376381 380291 380297','0 268 10100 344232 380297',X'06090208040301934BF672511F7ED3'); + INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 24513 2237 1 1','0 455150 467779 470015 470015','0 286 10880 425401 470015',X'06090202040301A703464A28F2611EF1EE'); + INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 18730 18724 15 1','0 479663 479663 498271 498285','0 287 10998 450793 498285',X'06090208040301A8494AF3A41EC50C'); + INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 119603 47125 1 1','0 572425 572425 598915 598915','0 404 14230 546497 598915',X'06090208040302474FD1929A03194F'); + INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 1454 1454 1 1','0 898346 898346 898373 898373','0 952 31165 827562 898373',X'06090208040304FD53F6A2A2097F64'); + INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 57138 7069 1 1','0 1122389 1122389 1129457 1129457','0 1967 46801 1045943 1129457',X'06090208040309884BC4C52F1F6EB7'); + INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 285 11 1 1','0 1197683 1197824 1197831 1197831','0 2033 50990 1112280 1197831',X'06090202040309D80346503FE2A9038E4F'); + INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 25365 9773 1 1','0 1301013 1301013 1310785 1310785','0 2561 58806 1217877 1310785',X'0609020804030C5F4C8F88AB0AF2A2'); + INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 45180 7222 1 1','0 1326378 1326378 1333599 1333599','0 2562 59921 1240187 1333599',X'0609020804030C604CAB75490B0351'); + INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 8537 41 1 1','0 1496959 1497288 1497289 1497289','0 3050 68246 1394126 1497289',X'0609020204030EA0057F527459B0257C4B'); + INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 26139 26131 17 1','0 1507977 1507977 1520578 1520594','0 3074 69188 1416111 1520594',X'0609020804030EB95169453423D4EA'); + INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 102894 29678 1 1','0 1537421 1550467 1564894 1564894','0 3109 69669 1459820 1564894',X'0609020204030EE3183652A6ED3006EBCB'); + INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 319 3 1 1','0 1796728 1796746 1796747 1796747','0 3650 86468 1682243 1796747',X'0609020204031163033550D0C41018C28D'); + INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 127 127 1 1','0 2096194 2096194 2096205 2096205','0 5145 106437 1951535 2096205',X'060902080403180F53BB1AF727EE50'); + INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 66574 5252 1 1','0 2230524 2265961 2271212 2271212','0 5899 114976 2085829 2271212',X'0609020204031B8A05195009976D223B90'); + INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 19440 19440 1 1','0 2391680 2391680 2395663 2395663','0 6718 123714 2184781 2395663',X'0609020804031F7452E00A7B07431A'); + INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 18321 2177 1 1','0 2522928 2523231 2525407 2525407','0 7838 139084 2299958 2525407',X'06090201040324A7475231103B1AA7B8'); + INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 22384 1361 1 1','0 2541249 2544834 2546194 2546194','0 7839 139428 2308416 2546194',X'06090202040324A8011652323D4B1AA9EB'); + INSERT INTO sqlite_stat4 VALUES('t1','ix','2677151 18699 855 1 1','0 2563633 2578178 2579032 2579032','0 7840 139947 2321671 2579032',X'06090202040324A9077452323D7D1052C5'); + INSERT INTO sqlite_stat4 VALUES('t1','ix','17965 1579 1579 1 1','2677151 2690666 2690666 2692244 2692244','1 9870 153959 2418294 2692244',X'060102080403021B8A4FE1AB84032B35'); + ANALYZE sqlite_master; +} {} +do_execsql_test 1.2 { + EXPLAIN QUERY PLAN + SELECT COUNT(*) + FROM t1 + WHERE bb=21 + AND aa=1 + AND dd BETWEEN 1413833728 and 1413837331; +} {/INDEX ix .aa=. AND bb=../} + +do_execsql_test 2.1 { + DROP INDEX ix; + CREATE INDEX good on t1(bb, aa, dd DESC); + CREATE INDEX bad on t1(aa, bb, cc, dd DESC); + DELETE FROM sqlite_stat1; + DELETE FROM sqlite_stat4; + INSERT INTO sqlite_stat1 VALUES('t1','good','2695116 299 264 2'); + INSERT INTO sqlite_stat1 VALUES('t1','bad','2695116 1347558 264 18 2'); + INSERT INTO sqlite_stat4 VALUES('t1','good','197030 196859 32 1','15086 15086 92511 92536','19 25 81644 92536',X'05010904031552977BD725BD22'); + INSERT INTO sqlite_stat4 VALUES('t1','good','14972 14687 1 1','289878 289878 299457 299457','199 244 267460 299457',X'050209040301344F7E569402C419'); + INSERT INTO sqlite_stat4 VALUES('t1','good','19600 19313 22 1','327127 327127 346222 346243','261 319 306884 346243',X'0502090403018A49503BC01EC577'); + INSERT INTO sqlite_stat4 VALUES('t1','good','25666 25047 15 1','352087 352087 372692 372706','266 327 325601 372706',X'050209040301914C2DD2E91F93CF'); + INSERT INTO sqlite_stat4 VALUES('t1','good','42392 42327 26 1','378657 378657 382547 382572','268 331 333529 382572',X'05020904030193533B2FE326ED48'); + INSERT INTO sqlite_stat4 VALUES('t1','good','24619 24513 11 1','457872 457872 461748 461758','286 358 399322 461758',X'050209040301A752B1557825EA7C'); + INSERT INTO sqlite_stat4 VALUES('t1','good','18969 18730 15 1','482491 482491 501105 501119','287 360 433605 501119',X'050209040301A8494AF3A41EC50C'); + INSERT INTO sqlite_stat4 VALUES('t1','good','119710 119603 1 1','576500 576500 598915 598915','404 505 519877 598915',X'05020904030247539A7A7912F617'); + INSERT INTO sqlite_stat4 VALUES('t1','good','11955 11946 1 1','889796 889796 898373 898373','938 1123 794694 898373',X'050209040304EF4DF9C4150BBB28'); + INSERT INTO sqlite_stat4 VALUES('t1','good','57197 57138 24 1','1129865 1129865 1151492 1151515','1967 2273 1027048 1151515',X'05020904030988533510BC26E20A'); + INSERT INTO sqlite_stat4 VALUES('t1','good','3609 3543 1 1','1196265 1196265 1197831 1197831','2002 2313 1070108 1197831',X'050209040309B050E95CD718D94D'); + INSERT INTO sqlite_stat4 VALUES('t1','good','25391 25365 13 1','1309378 1309378 1315567 1315579','2561 2936 1178358 1315579',X'05020904030C5F53DF9E13283570'); + INSERT INTO sqlite_stat4 VALUES('t1','good','45232 45180 17 1','1334769 1334769 1337946 1337962','2562 2938 1198998 1337962',X'05020904030C60541CACEE28BCAC'); + INSERT INTO sqlite_stat4 VALUES('t1','good','5496 5493 1 1','1495882 1495882 1497289 1497289','3043 3479 1348695 1497289',X'05020904030E99515C62AD0F0B34'); + INSERT INTO sqlite_stat4 VALUES('t1','good','26348 26139 17 1','1517381 1517381 1529990 1530006','3074 3519 1378320 1530006',X'05020904030EB95169453423D4EA'); + INSERT INTO sqlite_stat4 VALUES('t1','good','102927 102894 10 1','1547088 1547088 1649950 1649959','3109 3559 1494260 1649959',X'05020904030EE34D309F671FFA47'); + INSERT INTO sqlite_stat4 VALUES('t1','good','3602 3576 1 1','1793873 1793873 1796747 1796747','3601 4128 1630783 1796747',X'050209040311294FE88B432219B9'); + INSERT INTO sqlite_stat4 VALUES('t1','good','154 154 1 1','2096059 2096059 2096205 2096205','5037 5779 1893039 2096205',X'050209040317994EFF05A016DCED'); + INSERT INTO sqlite_stat4 VALUES('t1','good','68153 66574 60 1','2244039 2244039 2268892 2268951','5899 6749 2027553 2268951',X'05020904031B8A532DBC5A26D2BA'); + INSERT INTO sqlite_stat4 VALUES('t1','good','321 321 1 1','2395618 2395618 2395663 2395663','6609 7528 2118435 2395663',X'05020904031EFA54078EEE1E2D65'); + INSERT INTO sqlite_stat4 VALUES('t1','good','19449 19440 22 1','2407769 2407769 2426049 2426070','6718 7651 2146904 2426070',X'05020904031F7450E6118C2336BD'); + INSERT INTO sqlite_stat4 VALUES('t1','good','18383 18321 56 1','2539949 2539949 2551080 2551135','7838 8897 2245459 2551135',X'050209040324A752EA2E1E2642B2'); + INSERT INTO sqlite_stat4 VALUES('t1','good','22479 22384 60 1','2558332 2558332 2565233 2565292','7839 8899 2251202 2565292',X'050209040324A853926538279A5F'); + INSERT INTO sqlite_stat4 VALUES('t1','good','18771 18699 63 1','2580811 2580811 2596914 2596976','7840 8901 2263572 2596976',X'050209040324A9526C1DE9256E72'); + INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 196859 196859 32 1','0 15043 15043 92468 92499','0 19 286 81846 92499',X'0609010804031552977BD725BD28'); + INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 14687 161 1 1','0 289067 299306 299457 299457','0 199 6772 273984 299457',X'060902020403013406314D67456415B819'); + INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 19313 19308 22 1','0 325815 325815 343725 343746','0 261 9545 315009 343746',X'060902080403018A49B0A3AD1ED931'); + INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 25047 9051 15 1','0 350443 350443 356590 356604','0 266 9795 325519 356604',X'06090208040301914C2DD2E91F93CF'); + INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 42327 9906 7 1','0 376381 376381 380291 380297','0 268 10100 344232 380297',X'06090208040301934BF672511F7ED3'); + INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 24513 2237 1 1','0 455150 467779 470015 470015','0 286 10880 425401 470015',X'06090202040301A703464A28F2611EF1EE'); + INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 18730 18724 15 1','0 479663 479663 498271 498285','0 287 10998 450793 498285',X'06090208040301A8494AF3A41EC50C'); + INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 119603 47125 1 1','0 572425 572425 598915 598915','0 404 14230 546497 598915',X'06090208040302474FD1929A03194F'); + INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 1454 1454 1 1','0 898346 898346 898373 898373','0 952 31165 827562 898373',X'06090208040304FD53F6A2A2097F64'); + INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 57138 7069 1 1','0 1122389 1122389 1129457 1129457','0 1967 46801 1045943 1129457',X'06090208040309884BC4C52F1F6EB7'); + INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 285 11 1 1','0 1197683 1197824 1197831 1197831','0 2033 50990 1112280 1197831',X'06090202040309D80346503FE2A9038E4F'); + INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 25365 9773 1 1','0 1301013 1301013 1310785 1310785','0 2561 58806 1217877 1310785',X'0609020804030C5F4C8F88AB0AF2A2'); + INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 45180 7222 1 1','0 1326378 1326378 1333599 1333599','0 2562 59921 1240187 1333599',X'0609020804030C604CAB75490B0351'); + INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 8537 41 1 1','0 1496959 1497288 1497289 1497289','0 3050 68246 1394126 1497289',X'0609020204030EA0057F527459B0257C4B'); + INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 26139 26131 17 1','0 1507977 1507977 1520578 1520594','0 3074 69188 1416111 1520594',X'0609020804030EB95169453423D4EA'); + INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 102894 29678 1 1','0 1537421 1550467 1564894 1564894','0 3109 69669 1459820 1564894',X'0609020204030EE3183652A6ED3006EBCB'); + INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 319 3 1 1','0 1796728 1796746 1796747 1796747','0 3650 86468 1682243 1796747',X'0609020204031163033550D0C41018C28D'); + INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 127 127 1 1','0 2096194 2096194 2096205 2096205','0 5145 106437 1951535 2096205',X'060902080403180F53BB1AF727EE50'); + INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 66574 5252 1 1','0 2230524 2265961 2271212 2271212','0 5899 114976 2085829 2271212',X'0609020204031B8A05195009976D223B90'); + INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 19440 19440 1 1','0 2391680 2391680 2395663 2395663','0 6718 123714 2184781 2395663',X'0609020804031F7452E00A7B07431A'); + INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 18321 2177 1 1','0 2522928 2523231 2525407 2525407','0 7838 139084 2299958 2525407',X'06090201040324A7475231103B1AA7B8'); + INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 22384 1361 1 1','0 2541249 2544834 2546194 2546194','0 7839 139428 2308416 2546194',X'06090202040324A8011652323D4B1AA9EB'); + INSERT INTO sqlite_stat4 VALUES('t1','bad','2677151 18699 855 1 1','0 2563633 2578178 2579032 2579032','0 7840 139947 2321671 2579032',X'06090202040324A9077452323D7D1052C5'); + INSERT INTO sqlite_stat4 VALUES('t1','bad','17965 1579 1579 1 1','2677151 2690666 2690666 2692244 2692244','1 9870 153959 2418294 2692244',X'060102080403021B8A4FE1AB84032B35'); + ANALYZE sqlite_master; +} {} +do_execsql_test 2.2 { + EXPLAIN QUERY PLAN + SELECT COUNT(*) + FROM t1 + WHERE bb=21 + AND aa=1 + AND dd BETWEEN 1413833728 and 1413837331; +} {/INDEX good .bb=. AND aa=. AND dd>. AND dd<../} + + + +finish_test diff --git a/test/trigger9.test b/test/trigger9.test index f56c8acbc..326fa63d4 100644 --- a/test/trigger9.test +++ b/test/trigger9.test @@ -32,6 +32,7 @@ ifcapable {!trigger} { finish_test return } +set ::testprefix trigger9 proc has_rowdata {sql} { expr {[lsearch [execsql "explain $sql"] RowData]>=0} @@ -220,4 +221,36 @@ ifcapable compound { } {2} } +reset_db +do_execsql_test 4.1 { + CREATE TABLE t1(a, b); + CREATE TABLE log(x); + INSERT INTO t1 VALUES(1, 2); + INSERT INTO t1 VALUES(3, 4); + CREATE VIEW v1 AS SELECT a, b FROM t1; + + CREATE TRIGGER tr1 INSTEAD OF DELETE ON v1 BEGIN + INSERT INTO log VALUES('delete'); + END; + + CREATE TRIGGER tr2 INSTEAD OF UPDATE ON v1 BEGIN + INSERT INTO log VALUES('update'); + END; + + CREATE TRIGGER tr3 INSTEAD OF INSERT ON v1 BEGIN + INSERT INTO log VALUES('insert'); + END; +} + +do_execsql_test 4.2 { + DELETE FROM v1 WHERE rowid=1; +} {} + +do_execsql_test 4.3 { + UPDATE v1 SET a=b WHERE rowid=2; +} {} + + + + finish_test diff --git a/test/update.test b/test/update.test index e67b0efdd..d7baf6e70 100644 --- a/test/update.test +++ b/test/update.test @@ -604,5 +604,19 @@ do_test update-14.4 { } ;# ifcapable {trigger} +# Ticket [https://www.sqlite.org/src/tktview/43107840f1c02] on 2014-10-29 +# An assertion fault on UPDATE +# +do_execsql_test update-15.1 { + CREATE TABLE t15(a INTEGER PRIMARY KEY, b); + INSERT INTO t15(a,b) VALUES(10,'abc'),(20,'def'),(30,'ghi'); + ALTER TABLE t15 ADD COLUMN c; + CREATE INDEX t15c ON t15(c); + INSERT INTO t15(a,b) + VALUES(5,'zyx'),(15,'wvu'),(25,'tsr'),(35,'qpo'); + UPDATE t15 SET c=printf("y%d",a) WHERE c IS NULL; + SELECT a,b,c,'|' FROM t15 ORDER BY a; +} {5 zyx y5 | 10 abc y10 | 15 wvu y15 | 20 def y20 | 25 tsr y25 | 30 ghi y30 | 35 qpo y35 |} + finish_test |