# 2001 September 15 # # 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 statement. # # $Id: insert.test,v 1.25 2005/03/29 03:11:00 danielk1977 Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Try to insert into a non-existant table. # do_test insert-1.1 { set v [catch {execsql {INSERT INTO test1 VALUES(1,2,3)}} msg] lappend v $msg } {1 {no such table: test1}} # Try to insert into sqlite_master # do_test insert-1.2 { set v [catch {execsql {INSERT INTO sqlite_master VALUES(1,2,3,4)}} msg] lappend v $msg } {1 {table sqlite_master may not be modified}} # Try to insert the wrong number of entries. # do_test insert-1.3 { execsql {CREATE TABLE test1(one int, two int, three int)} set v [catch {execsql {INSERT INTO test1 VALUES(1,2)}} msg] lappend v $msg } {1 {table test1 has 3 columns but 2 values were supplied}} do_test insert-1.3b { set v [catch {execsql {INSERT INTO test1 VALUES(1,2,3,4)}} msg] lappend v $msg } {1 {table test1 has 3 columns but 4 values were supplied}} do_test insert-1.3c { set v [catch {execsql {INSERT INTO test1(one,two) VALUES(1,2,3,4)}} msg] lappend v $msg } {1 {4 values for 2 columns}} do_test insert-1.3d { set v [catch {execsql {INSERT INTO test1(one,two) VALUES(1)}} msg] lappend v $msg } {1 {1 values for 2 columns}} # Try to insert into a non-existant column of a table. # do_test insert-1.4 { set v [catch {execsql {INSERT INTO test1(one,four) VALUES(1,2)}} msg] lappend v $msg } {1 {table test1 has no column named four}} # Make sure the inserts actually happen # do_test insert-1.5 { execsql {INSERT INTO test1 VALUES(1,2,3)} execsql {SELECT * FROM test1} } {1 2 3} do_test insert-1.5b { execsql {INSERT INTO test1 VALUES(4,5,6)} execsql {SELECT * FROM test1 ORDER BY one} } {1 2 3 4 5 6} do_test insert-1.5c { execsql {INSERT INTO test1 VALUES(7,8,9)} execsql {SELECT * FROM test1 ORDER BY one} } {1 2 3 4 5 6 7 8 9} do_test insert-1.6 { execsql {DELETE FROM test1} execsql {INSERT INTO test1(one,two) VALUES(1,2)} execsql {SELECT * FROM test1 ORDER BY one} } {1 2 {}} do_test insert-1.6b { execsql {INSERT INTO test1(two,three) VALUES(5,6)} execsql {SELECT * FROM test1 ORDER BY one} } {{} 5 6 1 2 {}} do_test insert-1.6c { execsql {INSERT INTO test1(three,one) VALUES(7,8)} execsql {SELECT * FROM test1 ORDER BY one} } {{} 5 6 1 2 {} 8 {} 7} # A table to use for testing default values # do_test insert-2.1 { execsql { CREATE TABLE test2( f1 int default -111, f2 real default +4.32, f3 int default +222, f4 int default 7.89 ) } execsql {SELECT * from test2} } {} do_test insert-2.2 { execsql {INSERT INTO test2(f1,f3) VALUES(+10,-10)} execsql {SELECT * FROM test2} } {10 4.32 -10 7.89} do_test insert-2.3 { execsql {INSERT INTO test2(f2,f4) VALUES(1.23,-3.45)} execsql {SELECT * FROM test2 WHERE f1==-111} } {-111 1.23 222 -3.45} do_test insert-2.4 { execsql {INSERT INTO test2(f1,f2,f4) VALUES(77,+1.23,3.45)} execsql {SELECT * FROM test2 WHERE f1==77} } {77 1.23 222 3.45} do_test insert-2.10 { execsql { DROP TABLE test2; CREATE TABLE test2( f1 int default 111, f2 real default -4.32, f3 text default hi, f4 text default 'abc-123', f5 varchar(10) ) } execsql {SELECT * from test2} } {} do_test insert-2.11 { execsql {INSERT INTO test2(f2,f4) VALUES(-2.22,'hi!')} execsql {SELECT * FROM test2} } {111 -2.22 hi hi! {}} do_test insert-2.12 { execsql {INSERT INTO test2(f1,f5) VALUES(1,'xyzzy')} execsql {SELECT * FROM test2 ORDER BY f1} } {1 -4.32 hi abc-123 xyzzy 111 -2.22 hi hi! {}} # Do additional inserts with default values, but this time # on a table that has indices. In particular we want to verify # that the correct default values are inserted into the indices. # do_test insert-3.1 { execsql { DELETE FROM test2; CREATE INDEX index9 ON test2(f1,f2); CREATE INDEX indext ON test2(f4,f5); SELECT * from test2; } } {} # Update for sqlite3 v3: # Change the 111 to '111' in the following two test cases, because # the default value is being inserted as a string. TODO: It shouldn't be. do_test insert-3.2 { execsql {INSERT INTO test2(f2,f4) VALUES(-3.33,'hum')} execsql {SELECT * FROM test2 WHERE f1='111' AND f2=-3.33} } {111 -3.33 hi hum {}} do_test insert-3.3 { execsql {INSERT INTO test2(f1,f2,f5) VALUES(22,-4.44,'wham')} execsql {SELECT * FROM test2 WHERE f1='111' AND f2=-3.33} } {111 -3.33 hi hum {}} do_test insert-3.4 { execsql {SELECT * FROM test2 WHERE f1=22 AND f2=-4.44} } {22 -4.44 hi abc-123 wham} ifcapable {reindex} { do_test insert-3.5 { execsql REINDEX } {} } integrity_check insert-3.5 # Test of expressions in the VALUES clause # do_test insert-4.1 { execsql { CREATE TABLE t3(a,b,c); INSERT INTO t3 VALUES(1+2+3,4,5); SELECT * FROM t3; } } {6 4 5} do_test insert-4.2 { ifcapable subquery { execsql {INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1,5,6);} } else { set maxa [execsql {SELECT max(a) FROM t3}] execsql "INSERT INTO t3 VALUES($maxa+1,5,6);" } execsql { SELECT * FROM t3 ORDER BY a; } } {6 4 5 7 5 6} ifcapable subquery { do_test insert-4.3 { catchsql { INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1,t3.a,6); SELECT * FROM t3 ORDER BY a; } } {1 {no such column: t3.a}} } do_test insert-4.4 { ifcapable subquery { execsql {INSERT INTO t3 VALUES((SELECT b FROM t3 WHERE a=0),6,7);} } else { set b [execsql {SELECT b FROM t3 WHERE a = 0}] if {$b==""} {set b NULL} execsql "INSERT INTO t3 VALUES($b,6,7);" } execsql { SELECT * FROM t3 ORDER BY a; } } {{} 6 7 6 4 5 7 5 6} do_test insert-4.5 { execsql { SELECT b,c FROM t3 WHERE a IS NULL; } } {6 7} do_test insert-4.6 { catchsql { INSERT INTO t3 VALUES(notafunc(2,3),2,3); } } {1 {no such function: notafunc}} do_test insert-4.7 { execsql { INSERT INTO t3 VALUES(min(1,2,3),max(1,2,3),99); SELECT * FROM t3 WHERE c=99; } } {1 3 99} # Test the ability to insert from a temporary table into itself. # Ticket #275. # ifcapable tempdb { do_test insert-5.1 { execsql { CREATE TEMP TABLE t4(x); INSERT INTO t4 VALUES(1); SELECT * FROM t4; } } {1} do_test insert-5.2 { execsql { INSERT INTO t4 SELECT x+1 FROM t4; SELECT * FROM t4; } } {1 2} ifcapable {explain} { do_test insert-5.3 { # verify that a temporary table is used to copy t4 to t4 set x [execsql { EXPLAIN INSERT INTO t4 SELECT x+2 FROM t4; }] expr {[lsearch $x OpenTemp]>0} } {1} } do_test insert-5.4 { # Verify that table "test1" begins on page 3. This should be the same # page number used by "t4" above. # # Update for v3 - the first table now begins on page 2 of each file, not 3. execsql { SELECT rootpage FROM sqlite_master WHERE name='test1'; } } [expr $AUTOVACUUM?3:2] do_test insert-5.5 { # Verify that "t4" begins on page 3. # # Update for v3 - the first table now begins on page 2 of each file, not 3. execsql { SELECT rootpage FROM sqlite_temp_master WHERE name='t4'; } } {2} do_test insert-5.6 { # This should not use an intermediate temporary table. execsql { INSERT INTO t4 SELECT one FROM test1 WHERE three=7; SELECT * FROM t4 } } {1 2 8} ifcapable {explain} { do_test insert-5.7 { # verify that no temporary table is used to copy test1 to t4 set x [execsql { EXPLAIN INSERT INTO t4 SELECT one FROM test1; }] expr {[lsearch $x OpenTemp]>0} } {0} } } # Ticket #334: REPLACE statement corrupting indices. # do_test insert-6.1 { execsql { CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE); INSERT INTO t1 VALUES(1,2); INSERT INTO t1 VALUES(2,3); SELECT b FROM t1 WHERE b=2; } } {2} do_test insert-6.2 { execsql { REPLACE INTO t1 VALUES(1,4); SELECT b FROM t1 WHERE b=2; } } {} do_test insert-6.3 { execsql { UPDATE OR REPLACE t1 SET a=2 WHERE b=4; SELECT * FROM t1 WHERE b=4; } } {2 4} do_test insert-6.4 { execsql { SELECT * FROM t1 WHERE b=3; } } {} ifcapable {reindex} { do_test insert-6.7 { execsql REINDEX } {} } # Test that the special optimization for queries of the form # "SELECT max(x) FROM tbl" where there is an index on tbl(x) works with # INSERT statments. do_test insert-7.1 { execsql { DROP TABLE t1; CREATE TABLE t1(a); INSERT INTO t1 VALUES(1); INSERT INTO t1 VALUES(2); CREATE INDEX i1 ON t1(a); } } {} do_test insert-7.2 { execsql { INSERT INTO t1 SELECT max(a) FROM t1; } } {} do_test insert-7.3 { execsql { SELECT a FROM t1; } } {1 2 2} # Ticket #1140: Check for an infinite loop in the algorithm that tests # to see if the right-hand side of an INSERT...SELECT references the left-hand # side. # do_test insert-8.1 { execsql { INSERT INTO t3 SELECT * FROM (SELECT * FROM t3 UNION ALL SELECT 1,2,3) } } {} integrity_check insert-99.0 finish_test