1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/insert.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,391 @@
1.4 +# 2001 September 15
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library. The
1.15 +# focus of this file is testing the INSERT statement.
1.16 +#
1.17 +# $Id: insert.test,v 1.31 2007/04/05 11:25:59 drh Exp $
1.18 +
1.19 +set testdir [file dirname $argv0]
1.20 +source $testdir/tester.tcl
1.21 +
1.22 +# Try to insert into a non-existant table.
1.23 +#
1.24 +do_test insert-1.1 {
1.25 + set v [catch {execsql {INSERT INTO test1 VALUES(1,2,3)}} msg]
1.26 + lappend v $msg
1.27 +} {1 {no such table: test1}}
1.28 +
1.29 +# Try to insert into sqlite_master
1.30 +#
1.31 +do_test insert-1.2 {
1.32 + set v [catch {execsql {INSERT INTO sqlite_master VALUES(1,2,3,4)}} msg]
1.33 + lappend v $msg
1.34 +} {1 {table sqlite_master may not be modified}}
1.35 +
1.36 +# Try to insert the wrong number of entries.
1.37 +#
1.38 +do_test insert-1.3 {
1.39 + execsql {CREATE TABLE test1(one int, two int, three int)}
1.40 + set v [catch {execsql {INSERT INTO test1 VALUES(1,2)}} msg]
1.41 + lappend v $msg
1.42 +} {1 {table test1 has 3 columns but 2 values were supplied}}
1.43 +do_test insert-1.3b {
1.44 + set v [catch {execsql {INSERT INTO test1 VALUES(1,2,3,4)}} msg]
1.45 + lappend v $msg
1.46 +} {1 {table test1 has 3 columns but 4 values were supplied}}
1.47 +do_test insert-1.3c {
1.48 + set v [catch {execsql {INSERT INTO test1(one,two) VALUES(1,2,3,4)}} msg]
1.49 + lappend v $msg
1.50 +} {1 {4 values for 2 columns}}
1.51 +do_test insert-1.3d {
1.52 + set v [catch {execsql {INSERT INTO test1(one,two) VALUES(1)}} msg]
1.53 + lappend v $msg
1.54 +} {1 {1 values for 2 columns}}
1.55 +
1.56 +# Try to insert into a non-existant column of a table.
1.57 +#
1.58 +do_test insert-1.4 {
1.59 + set v [catch {execsql {INSERT INTO test1(one,four) VALUES(1,2)}} msg]
1.60 + lappend v $msg
1.61 +} {1 {table test1 has no column named four}}
1.62 +
1.63 +# Make sure the inserts actually happen
1.64 +#
1.65 +do_test insert-1.5 {
1.66 + execsql {INSERT INTO test1 VALUES(1,2,3)}
1.67 + execsql {SELECT * FROM test1}
1.68 +} {1 2 3}
1.69 +do_test insert-1.5b {
1.70 + execsql {INSERT INTO test1 VALUES(4,5,6)}
1.71 + execsql {SELECT * FROM test1 ORDER BY one}
1.72 +} {1 2 3 4 5 6}
1.73 +do_test insert-1.5c {
1.74 + execsql {INSERT INTO test1 VALUES(7,8,9)}
1.75 + execsql {SELECT * FROM test1 ORDER BY one}
1.76 +} {1 2 3 4 5 6 7 8 9}
1.77 +
1.78 +do_test insert-1.6 {
1.79 + execsql {DELETE FROM test1}
1.80 + execsql {INSERT INTO test1(one,two) VALUES(1,2)}
1.81 + execsql {SELECT * FROM test1 ORDER BY one}
1.82 +} {1 2 {}}
1.83 +do_test insert-1.6b {
1.84 + execsql {INSERT INTO test1(two,three) VALUES(5,6)}
1.85 + execsql {SELECT * FROM test1 ORDER BY one}
1.86 +} {{} 5 6 1 2 {}}
1.87 +do_test insert-1.6c {
1.88 + execsql {INSERT INTO test1(three,one) VALUES(7,8)}
1.89 + execsql {SELECT * FROM test1 ORDER BY one}
1.90 +} {{} 5 6 1 2 {} 8 {} 7}
1.91 +
1.92 +# A table to use for testing default values
1.93 +#
1.94 +do_test insert-2.1 {
1.95 + execsql {
1.96 + CREATE TABLE test2(
1.97 + f1 int default -111,
1.98 + f2 real default +4.32,
1.99 + f3 int default +222,
1.100 + f4 int default 7.89
1.101 + )
1.102 + }
1.103 + execsql {SELECT * from test2}
1.104 +} {}
1.105 +do_test insert-2.2 {
1.106 + execsql {INSERT INTO test2(f1,f3) VALUES(+10,-10)}
1.107 + execsql {SELECT * FROM test2}
1.108 +} {10 4.32 -10 7.89}
1.109 +do_test insert-2.3 {
1.110 + execsql {INSERT INTO test2(f2,f4) VALUES(1.23,-3.45)}
1.111 + execsql {SELECT * FROM test2 WHERE f1==-111}
1.112 +} {-111 1.23 222 -3.45}
1.113 +do_test insert-2.4 {
1.114 + execsql {INSERT INTO test2(f1,f2,f4) VALUES(77,+1.23,3.45)}
1.115 + execsql {SELECT * FROM test2 WHERE f1==77}
1.116 +} {77 1.23 222 3.45}
1.117 +do_test insert-2.10 {
1.118 + execsql {
1.119 + DROP TABLE test2;
1.120 + CREATE TABLE test2(
1.121 + f1 int default 111,
1.122 + f2 real default -4.32,
1.123 + f3 text default hi,
1.124 + f4 text default 'abc-123',
1.125 + f5 varchar(10)
1.126 + )
1.127 + }
1.128 + execsql {SELECT * from test2}
1.129 +} {}
1.130 +do_test insert-2.11 {
1.131 + execsql {INSERT INTO test2(f2,f4) VALUES(-2.22,'hi!')}
1.132 + execsql {SELECT * FROM test2}
1.133 +} {111 -2.22 hi hi! {}}
1.134 +do_test insert-2.12 {
1.135 + execsql {INSERT INTO test2(f1,f5) VALUES(1,'xyzzy')}
1.136 + execsql {SELECT * FROM test2 ORDER BY f1}
1.137 +} {1 -4.32 hi abc-123 xyzzy 111 -2.22 hi hi! {}}
1.138 +
1.139 +# Do additional inserts with default values, but this time
1.140 +# on a table that has indices. In particular we want to verify
1.141 +# that the correct default values are inserted into the indices.
1.142 +#
1.143 +do_test insert-3.1 {
1.144 + execsql {
1.145 + DELETE FROM test2;
1.146 + CREATE INDEX index9 ON test2(f1,f2);
1.147 + CREATE INDEX indext ON test2(f4,f5);
1.148 + SELECT * from test2;
1.149 + }
1.150 +} {}
1.151 +
1.152 +# Update for sqlite3 v3:
1.153 +# Change the 111 to '111' in the following two test cases, because
1.154 +# the default value is being inserted as a string. TODO: It shouldn't be.
1.155 +do_test insert-3.2 {
1.156 + execsql {INSERT INTO test2(f2,f4) VALUES(-3.33,'hum')}
1.157 + execsql {SELECT * FROM test2 WHERE f1='111' AND f2=-3.33}
1.158 +} {111 -3.33 hi hum {}}
1.159 +do_test insert-3.3 {
1.160 + execsql {INSERT INTO test2(f1,f2,f5) VALUES(22,-4.44,'wham')}
1.161 + execsql {SELECT * FROM test2 WHERE f1='111' AND f2=-3.33}
1.162 +} {111 -3.33 hi hum {}}
1.163 +do_test insert-3.4 {
1.164 + execsql {SELECT * FROM test2 WHERE f1=22 AND f2=-4.44}
1.165 +} {22 -4.44 hi abc-123 wham}
1.166 +ifcapable {reindex} {
1.167 + do_test insert-3.5 {
1.168 + execsql REINDEX
1.169 + } {}
1.170 +}
1.171 +integrity_check insert-3.5
1.172 +
1.173 +# Test of expressions in the VALUES clause
1.174 +#
1.175 +do_test insert-4.1 {
1.176 + execsql {
1.177 + CREATE TABLE t3(a,b,c);
1.178 + INSERT INTO t3 VALUES(1+2+3,4,5);
1.179 + SELECT * FROM t3;
1.180 + }
1.181 +} {6 4 5}
1.182 +do_test insert-4.2 {
1.183 + ifcapable subquery {
1.184 + execsql {INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1,5,6);}
1.185 + } else {
1.186 + set maxa [execsql {SELECT max(a) FROM t3}]
1.187 + execsql "INSERT INTO t3 VALUES($maxa+1,5,6);"
1.188 + }
1.189 + execsql {
1.190 + SELECT * FROM t3 ORDER BY a;
1.191 + }
1.192 +} {6 4 5 7 5 6}
1.193 +ifcapable subquery {
1.194 + do_test insert-4.3 {
1.195 + catchsql {
1.196 + INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1,t3.a,6);
1.197 + SELECT * FROM t3 ORDER BY a;
1.198 + }
1.199 + } {1 {no such column: t3.a}}
1.200 +}
1.201 +do_test insert-4.4 {
1.202 + ifcapable subquery {
1.203 + execsql {INSERT INTO t3 VALUES((SELECT b FROM t3 WHERE a=0),6,7);}
1.204 + } else {
1.205 + set b [execsql {SELECT b FROM t3 WHERE a = 0}]
1.206 + if {$b==""} {set b NULL}
1.207 + execsql "INSERT INTO t3 VALUES($b,6,7);"
1.208 + }
1.209 + execsql {
1.210 + SELECT * FROM t3 ORDER BY a;
1.211 + }
1.212 +} {{} 6 7 6 4 5 7 5 6}
1.213 +do_test insert-4.5 {
1.214 + execsql {
1.215 + SELECT b,c FROM t3 WHERE a IS NULL;
1.216 + }
1.217 +} {6 7}
1.218 +do_test insert-4.6 {
1.219 + catchsql {
1.220 + INSERT INTO t3 VALUES(notafunc(2,3),2,3);
1.221 + }
1.222 +} {1 {no such function: notafunc}}
1.223 +do_test insert-4.7 {
1.224 + execsql {
1.225 + INSERT INTO t3 VALUES(min(1,2,3),max(1,2,3),99);
1.226 + SELECT * FROM t3 WHERE c=99;
1.227 + }
1.228 +} {1 3 99}
1.229 +
1.230 +# Test the ability to insert from a temporary table into itself.
1.231 +# Ticket #275.
1.232 +#
1.233 +ifcapable tempdb {
1.234 + do_test insert-5.1 {
1.235 + execsql {
1.236 + CREATE TEMP TABLE t4(x);
1.237 + INSERT INTO t4 VALUES(1);
1.238 + SELECT * FROM t4;
1.239 + }
1.240 + } {1}
1.241 + do_test insert-5.2 {
1.242 + execsql {
1.243 + INSERT INTO t4 SELECT x+1 FROM t4;
1.244 + SELECT * FROM t4;
1.245 + }
1.246 + } {1 2}
1.247 + ifcapable {explain} {
1.248 + do_test insert-5.3 {
1.249 + # verify that a temporary table is used to copy t4 to t4
1.250 + set x [execsql {
1.251 + EXPLAIN INSERT INTO t4 SELECT x+2 FROM t4;
1.252 + }]
1.253 + expr {[lsearch $x OpenEphemeral]>0}
1.254 + } {1}
1.255 + }
1.256 +
1.257 + do_test insert-5.4 {
1.258 + # Verify that table "test1" begins on page 3. This should be the same
1.259 + # page number used by "t4" above.
1.260 + #
1.261 + # Update for v3 - the first table now begins on page 2 of each file, not 3.
1.262 + execsql {
1.263 + SELECT rootpage FROM sqlite_master WHERE name='test1';
1.264 + }
1.265 + } [expr $AUTOVACUUM?3:2]
1.266 + do_test insert-5.5 {
1.267 + # Verify that "t4" begins on page 3.
1.268 + #
1.269 + # Update for v3 - the first table now begins on page 2 of each file, not 3.
1.270 + execsql {
1.271 + SELECT rootpage FROM sqlite_temp_master WHERE name='t4';
1.272 + }
1.273 + } {2}
1.274 + do_test insert-5.6 {
1.275 + # This should not use an intermediate temporary table.
1.276 + execsql {
1.277 + INSERT INTO t4 SELECT one FROM test1 WHERE three=7;
1.278 + SELECT * FROM t4
1.279 + }
1.280 + } {1 2 8}
1.281 + ifcapable {explain} {
1.282 + do_test insert-5.7 {
1.283 + # verify that no temporary table is used to copy test1 to t4
1.284 + set x [execsql {
1.285 + EXPLAIN INSERT INTO t4 SELECT one FROM test1;
1.286 + }]
1.287 + expr {[lsearch $x OpenTemp]>0}
1.288 + } {0}
1.289 + }
1.290 +}
1.291 +
1.292 +# Ticket #334: REPLACE statement corrupting indices.
1.293 +#
1.294 +ifcapable conflict {
1.295 + # The REPLACE command is not available if SQLITE_OMIT_CONFLICT is
1.296 + # defined at compilation time.
1.297 + do_test insert-6.1 {
1.298 + execsql {
1.299 + CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE);
1.300 + INSERT INTO t1 VALUES(1,2);
1.301 + INSERT INTO t1 VALUES(2,3);
1.302 + SELECT b FROM t1 WHERE b=2;
1.303 + }
1.304 + } {2}
1.305 + do_test insert-6.2 {
1.306 + execsql {
1.307 + REPLACE INTO t1 VALUES(1,4);
1.308 + SELECT b FROM t1 WHERE b=2;
1.309 + }
1.310 + } {}
1.311 + do_test insert-6.3 {
1.312 + execsql {
1.313 + UPDATE OR REPLACE t1 SET a=2 WHERE b=4;
1.314 + SELECT * FROM t1 WHERE b=4;
1.315 + }
1.316 + } {2 4}
1.317 + do_test insert-6.4 {
1.318 + execsql {
1.319 + SELECT * FROM t1 WHERE b=3;
1.320 + }
1.321 + } {}
1.322 + ifcapable {reindex} {
1.323 + do_test insert-6.5 {
1.324 + execsql REINDEX
1.325 + } {}
1.326 + }
1.327 + do_test insert-6.6 {
1.328 + execsql {
1.329 + DROP TABLE t1;
1.330 + }
1.331 + } {}
1.332 +}
1.333 +
1.334 +# Test that the special optimization for queries of the form
1.335 +# "SELECT max(x) FROM tbl" where there is an index on tbl(x) works with
1.336 +# INSERT statments.
1.337 +do_test insert-7.1 {
1.338 + execsql {
1.339 + CREATE TABLE t1(a);
1.340 + INSERT INTO t1 VALUES(1);
1.341 + INSERT INTO t1 VALUES(2);
1.342 + CREATE INDEX i1 ON t1(a);
1.343 + }
1.344 +} {}
1.345 +do_test insert-7.2 {
1.346 + execsql {
1.347 + INSERT INTO t1 SELECT max(a) FROM t1;
1.348 + }
1.349 +} {}
1.350 +do_test insert-7.3 {
1.351 + execsql {
1.352 + SELECT a FROM t1;
1.353 + }
1.354 +} {1 2 2}
1.355 +
1.356 +# Ticket #1140: Check for an infinite loop in the algorithm that tests
1.357 +# to see if the right-hand side of an INSERT...SELECT references the left-hand
1.358 +# side.
1.359 +#
1.360 +ifcapable subquery&&compound {
1.361 + do_test insert-8.1 {
1.362 + execsql {
1.363 + INSERT INTO t3 SELECT * FROM (SELECT * FROM t3 UNION ALL SELECT 1,2,3)
1.364 + }
1.365 + } {}
1.366 +}
1.367 +
1.368 +# Make sure the rowid cache in the VDBE is reset correctly when
1.369 +# an explicit rowid is given.
1.370 +#
1.371 +do_test insert-9.1 {
1.372 + execsql {
1.373 + CREATE TABLE t5(x);
1.374 + INSERT INTO t5 VALUES(1);
1.375 + INSERT INTO t5 VALUES(2);
1.376 + INSERT INTO t5 VALUES(3);
1.377 + INSERT INTO t5(rowid, x) SELECT nullif(x*2+10,14), x+100 FROM t5;
1.378 + SELECT rowid, x FROM t5;
1.379 + }
1.380 +} {1 1 2 2 3 3 12 101 13 102 16 103}
1.381 +do_test insert-9.2 {
1.382 + execsql {
1.383 + CREATE TABLE t6(x INTEGER PRIMARY KEY, y);
1.384 + INSERT INTO t6 VALUES(1,1);
1.385 + INSERT INTO t6 VALUES(2,2);
1.386 + INSERT INTO t6 VALUES(3,3);
1.387 + INSERT INTO t6 SELECT nullif(y*2+10,14), y+100 FROM t6;
1.388 + SELECT x, y FROM t6;
1.389 + }
1.390 +} {1 1 2 2 3 3 12 101 13 102 16 103}
1.391 +
1.392 +integrity_check insert-99.0
1.393 +
1.394 +finish_test