1 # 2001 September 15 2 # 3 # The author disclaims copyright to this source code. In place of 4 # 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. The 12 # focus of this file is testing the INSERT statement. 13 # 14 # $Id: insert.test,v 1.31 2007/04/05 11:25:59 drh Exp $ 15 16 set testdir [file dirname $argv0] 17 source $testdir/tester.tcl 18 19 # Try to insert into a non-existant table. 20 # 21 do_test insert-1.1 { 22 set v [catch {execsql {INSERT INTO test1 VALUES(1,2,3)}} msg] 23 lappend v $msg 24 } {1 {no such table: test1}} 25 26 # Try to insert into sqlite_master 27 # 28 do_test insert-1.2 { 29 set v [catch {execsql {INSERT INTO sqlite_master VALUES(1,2,3,4)}} msg] 30 lappend v $msg 31 } {1 {table sqlite_master may not be modified}} 32 33 # Try to insert the wrong number of entries. 34 # 35 do_test insert-1.3 { 36 execsql {CREATE TABLE test1(one int, two int, three int)} 37 set v [catch {execsql {INSERT INTO test1 VALUES(1,2)}} msg] 38 lappend v $msg 39 } {1 {table test1 has 3 columns but 2 values were supplied}} 40 do_test insert-1.3b { 41 set v [catch {execsql {INSERT INTO test1 VALUES(1,2,3,4)}} msg] 42 lappend v $msg 43 } {1 {table test1 has 3 columns but 4 values were supplied}} 44 do_test insert-1.3c { 45 set v [catch {execsql {INSERT INTO test1(one,two) VALUES(1,2,3,4)}} msg] 46 lappend v $msg 47 } {1 {4 values for 2 columns}} 48 do_test insert-1.3d { 49 set v [catch {execsql {INSERT INTO test1(one,two) VALUES(1)}} msg] 50 lappend v $msg 51 } {1 {1 values for 2 columns}} 52 53 # Try to insert into a non-existant column of a table. 54 # 55 do_test insert-1.4 { 56 set v [catch {execsql {INSERT INTO test1(one,four) VALUES(1,2)}} msg] 57 lappend v $msg 58 } {1 {table test1 has no column named four}} 59 60 # Make sure the inserts actually happen 61 # 62 do_test insert-1.5 { 63 execsql {INSERT INTO test1 VALUES(1,2,3)} 64 execsql {SELECT * FROM test1} 65 } {1 2 3} 66 do_test insert-1.5b { 67 execsql {INSERT INTO test1 VALUES(4,5,6)} 68 execsql {SELECT * FROM test1 ORDER BY one} 69 } {1 2 3 4 5 6} 70 do_test insert-1.5c { 71 execsql {INSERT INTO test1 VALUES(7,8,9)} 72 execsql {SELECT * FROM test1 ORDER BY one} 73 } {1 2 3 4 5 6 7 8 9} 74 75 do_test insert-1.6 { 76 execsql {DELETE FROM test1} 77 execsql {INSERT INTO test1(one,two) VALUES(1,2)} 78 execsql {SELECT * FROM test1 ORDER BY one} 79 } {1 2 {}} 80 do_test insert-1.6b { 81 execsql {INSERT INTO test1(two,three) VALUES(5,6)} 82 execsql {SELECT * FROM test1 ORDER BY one} 83 } {{} 5 6 1 2 {}} 84 do_test insert-1.6c { 85 execsql {INSERT INTO test1(three,one) VALUES(7,8)} 86 execsql {SELECT * FROM test1 ORDER BY one} 87 } {{} 5 6 1 2 {} 8 {} 7} 88 89 # A table to use for testing default values 90 # 91 do_test insert-2.1 { 92 execsql { 93 CREATE TABLE test2( 94 f1 int default -111, 95 f2 real default +4.32, 96 f3 int default +222, 97 f4 int default 7.89 98 ) 99 } 100 execsql {SELECT * from test2} 101 } {} 102 do_test insert-2.2 { 103 execsql {INSERT INTO test2(f1,f3) VALUES(+10,-10)} 104 execsql {SELECT * FROM test2} 105 } {10 4.32 -10 7.89} 106 do_test insert-2.3 { 107 execsql {INSERT INTO test2(f2,f4) VALUES(1.23,-3.45)} 108 execsql {SELECT * FROM test2 WHERE f1==-111} 109 } {-111 1.23 222 -3.45} 110 do_test insert-2.4 { 111 execsql {INSERT INTO test2(f1,f2,f4) VALUES(77,+1.23,3.45)} 112 execsql {SELECT * FROM test2 WHERE f1==77} 113 } {77 1.23 222 3.45} 114 do_test insert-2.10 { 115 execsql { 116 DROP TABLE test2; 117 CREATE TABLE test2( 118 f1 int default 111, 119 f2 real default -4.32, 120 f3 text default hi, 121 f4 text default 'abc-123', 122 f5 varchar(10) 123 ) 124 } 125 execsql {SELECT * from test2} 126 } {} 127 do_test insert-2.11 { 128 execsql {INSERT INTO test2(f2,f4) VALUES(-2.22,'hi!')} 129 execsql {SELECT * FROM test2} 130 } {111 -2.22 hi hi! {}} 131 do_test insert-2.12 { 132 execsql {INSERT INTO test2(f1,f5) VALUES(1,'xyzzy')} 133 execsql {SELECT * FROM test2 ORDER BY f1} 134 } {1 -4.32 hi abc-123 xyzzy 111 -2.22 hi hi! {}} 135 136 # Do additional inserts with default values, but this time 137 # on a table that has indices. In particular we want to verify 138 # that the correct default values are inserted into the indices. 139 # 140 do_test insert-3.1 { 141 execsql { 142 DELETE FROM test2; 143 CREATE INDEX index9 ON test2(f1,f2); 144 CREATE INDEX indext ON test2(f4,f5); 145 SELECT * from test2; 146 } 147 } {} 148 149 # Update for sqlite3 v3: 150 # Change the 111 to '111' in the following two test cases, because 151 # the default value is being inserted as a string. TODO: It shouldn't be. 152 do_test insert-3.2 { 153 execsql {INSERT INTO test2(f2,f4) VALUES(-3.33,'hum')} 154 execsql {SELECT * FROM test2 WHERE f1='111' AND f2=-3.33} 155 } {111 -3.33 hi hum {}} 156 do_test insert-3.3 { 157 execsql {INSERT INTO test2(f1,f2,f5) VALUES(22,-4.44,'wham')} 158 execsql {SELECT * FROM test2 WHERE f1='111' AND f2=-3.33} 159 } {111 -3.33 hi hum {}} 160 do_test insert-3.4 { 161 execsql {SELECT * FROM test2 WHERE f1=22 AND f2=-4.44} 162 } {22 -4.44 hi abc-123 wham} 163 ifcapable {reindex} { 164 do_test insert-3.5 { 165 execsql REINDEX 166 } {} 167 } 168 integrity_check insert-3.5 169 170 # Test of expressions in the VALUES clause 171 # 172 do_test insert-4.1 { 173 execsql { 174 CREATE TABLE t3(a,b,c); 175 INSERT INTO t3 VALUES(1+2+3,4,5); 176 SELECT * FROM t3; 177 } 178 } {6 4 5} 179 do_test insert-4.2 { 180 ifcapable subquery { 181 execsql {INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1,5,6);} 182 } else { 183 set maxa [execsql {SELECT max(a) FROM t3}] 184 execsql "INSERT INTO t3 VALUES($maxa+1,5,6);" 185 } 186 execsql { 187 SELECT * FROM t3 ORDER BY a; 188 } 189 } {6 4 5 7 5 6} 190 ifcapable subquery { 191 do_test insert-4.3 { 192 catchsql { 193 INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1,t3.a,6); 194 SELECT * FROM t3 ORDER BY a; 195 } 196 } {1 {no such column: t3.a}} 197 } 198 do_test insert-4.4 { 199 ifcapable subquery { 200 execsql {INSERT INTO t3 VALUES((SELECT b FROM t3 WHERE a=0),6,7);} 201 } else { 202 set b [execsql {SELECT b FROM t3 WHERE a = 0}] 203 if {$b==""} {set b NULL} 204 execsql "INSERT INTO t3 VALUES($b,6,7);" 205 } 206 execsql { 207 SELECT * FROM t3 ORDER BY a; 208 } 209 } {{} 6 7 6 4 5 7 5 6} 210 do_test insert-4.5 { 211 execsql { 212 SELECT b,c FROM t3 WHERE a IS NULL; 213 } 214 } {6 7} 215 do_test insert-4.6 { 216 catchsql { 217 INSERT INTO t3 VALUES(notafunc(2,3),2,3); 218 } 219 } {1 {no such function: notafunc}} 220 do_test insert-4.7 { 221 execsql { 222 INSERT INTO t3 VALUES(min(1,2,3),max(1,2,3),99); 223 SELECT * FROM t3 WHERE c=99; 224 } 225 } {1 3 99} 226 227 # Test the ability to insert from a temporary table into itself. 228 # Ticket #275. 229 # 230 ifcapable tempdb { 231 do_test insert-5.1 { 232 execsql { 233 CREATE TEMP TABLE t4(x); 234 INSERT INTO t4 VALUES(1); 235 SELECT * FROM t4; 236 } 237 } {1} 238 do_test insert-5.2 { 239 execsql { 240 INSERT INTO t4 SELECT x+1 FROM t4; 241 SELECT * FROM t4; 242 } 243 } {1 2} 244 ifcapable {explain} { 245 do_test insert-5.3 { 246 # verify that a temporary table is used to copy t4 to t4 247 set x [execsql { 248 EXPLAIN INSERT INTO t4 SELECT x+2 FROM t4; 249 }] 250 expr {[lsearch $x OpenEphemeral]>0} 251 } {1} 252 } 253 254 do_test insert-5.4 { 255 # Verify that table "test1" begins on page 3. This should be the same 256 # page number used by "t4" above. 257 # 258 # Update for v3 - the first table now begins on page 2 of each file, not 3. 259 execsql { 260 SELECT rootpage FROM sqlite_master WHERE name='test1'; 261 } 262 } [expr $AUTOVACUUM?3:2] 263 do_test insert-5.5 { 264 # Verify that "t4" begins on page 3. 265 # 266 # Update for v3 - the first table now begins on page 2 of each file, not 3. 267 execsql { 268 SELECT rootpage FROM sqlite_temp_master WHERE name='t4'; 269 } 270 } {2} 271 do_test insert-5.6 { 272 # This should not use an intermediate temporary table. 273 execsql { 274 INSERT INTO t4 SELECT one FROM test1 WHERE three=7; 275 SELECT * FROM t4 276 } 277 } {1 2 8} 278 ifcapable {explain} { 279 do_test insert-5.7 { 280 # verify that no temporary table is used to copy test1 to t4 281 set x [execsql { 282 EXPLAIN INSERT INTO t4 SELECT one FROM test1; 283 }] 284 expr {[lsearch $x OpenTemp]>0} 285 } {0} 286 } 287 } 288 289 # Ticket #334: REPLACE statement corrupting indices. 290 # 291 ifcapable conflict { 292 # The REPLACE command is not available if SQLITE_OMIT_CONFLICT is 293 # defined at compilation time. 294 do_test insert-6.1 { 295 execsql { 296 CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE); 297 INSERT INTO t1 VALUES(1,2); 298 INSERT INTO t1 VALUES(2,3); 299 SELECT b FROM t1 WHERE b=2; 300 } 301 } {2} 302 do_test insert-6.2 { 303 execsql { 304 REPLACE INTO t1 VALUES(1,4); 305 SELECT b FROM t1 WHERE b=2; 306 } 307 } {} 308 do_test insert-6.3 { 309 execsql { 310 UPDATE OR REPLACE t1 SET a=2 WHERE b=4; 311 SELECT * FROM t1 WHERE b=4; 312 } 313 } {2 4} 314 do_test insert-6.4 { 315 execsql { 316 SELECT * FROM t1 WHERE b=3; 317 } 318 } {} 319 ifcapable {reindex} { 320 do_test insert-6.5 { 321 execsql REINDEX 322 } {} 323 } 324 do_test insert-6.6 { 325 execsql { 326 DROP TABLE t1; 327 } 328 } {} 329 } 330 331 # Test that the special optimization for queries of the form 332 # "SELECT max(x) FROM tbl" where there is an index on tbl(x) works with 333 # INSERT statments. 334 do_test insert-7.1 { 335 execsql { 336 CREATE TABLE t1(a); 337 INSERT INTO t1 VALUES(1); 338 INSERT INTO t1 VALUES(2); 339 CREATE INDEX i1 ON t1(a); 340 } 341 } {} 342 do_test insert-7.2 { 343 execsql { 344 INSERT INTO t1 SELECT max(a) FROM t1; 345 } 346 } {} 347 do_test insert-7.3 { 348 execsql { 349 SELECT a FROM t1; 350 } 351 } {1 2 2} 352 353 # Ticket #1140: Check for an infinite loop in the algorithm that tests 354 # to see if the right-hand side of an INSERT...SELECT references the left-hand 355 # side. 356 # 357 ifcapable subquery&&compound { 358 do_test insert-8.1 { 359 execsql { 360 INSERT INTO t3 SELECT * FROM (SELECT * FROM t3 UNION ALL SELECT 1,2,3) 361 } 362 } {} 363 } 364 365 # Make sure the rowid cache in the VDBE is reset correctly when 366 # an explicit rowid is given. 367 # 368 do_test insert-9.1 { 369 execsql { 370 CREATE TABLE t5(x); 371 INSERT INTO t5 VALUES(1); 372 INSERT INTO t5 VALUES(2); 373 INSERT INTO t5 VALUES(3); 374 INSERT INTO t5(rowid, x) SELECT nullif(x*2+10,14), x+100 FROM t5; 375 SELECT rowid, x FROM t5; 376 } 377 } {1 1 2 2 3 3 12 101 13 102 16 103} 378 do_test insert-9.2 { 379 execsql { 380 CREATE TABLE t6(x INTEGER PRIMARY KEY, y); 381 INSERT INTO t6 VALUES(1,1); 382 INSERT INTO t6 VALUES(2,2); 383 INSERT INTO t6 VALUES(3,3); 384 INSERT INTO t6 SELECT nullif(y*2+10,14), y+100 FROM t6; 385 SELECT x, y FROM t6; 386 } 387 } {1 1 2 2 3 3 12 101 13 102 16 103} 388 389 integrity_check insert-99.0 390 391 finish_test 392
