1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/update.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,608 @@
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 UPDATE statement.
1.16 +#
1.17 +# $Id: update.test,v 1.19 2008/04/10 18:44:36 drh Exp $
1.18 +
1.19 +set testdir [file dirname $argv0]
1.20 +source $testdir/tester.tcl
1.21 +
1.22 +# Try to update an non-existent table
1.23 +#
1.24 +do_test update-1.1 {
1.25 + set v [catch {execsql {UPDATE test1 SET f2=5 WHERE f1<1}} msg]
1.26 + lappend v $msg
1.27 +} {1 {no such table: test1}}
1.28 +
1.29 +# Try to update a read-only table
1.30 +#
1.31 +do_test update-2.1 {
1.32 + set v [catch \
1.33 + {execsql {UPDATE sqlite_master SET name='xyz' WHERE name='123'}} msg]
1.34 + lappend v $msg
1.35 +} {1 {table sqlite_master may not be modified}}
1.36 +
1.37 +# Create a table to work with
1.38 +#
1.39 +do_test update-3.1 {
1.40 + execsql {CREATE TABLE test1(f1 int,f2 int)}
1.41 + for {set i 1} {$i<=10} {incr i} {
1.42 + set sql "INSERT INTO test1 VALUES($i,[expr {1<<$i}])"
1.43 + execsql $sql
1.44 + }
1.45 + execsql {SELECT * FROM test1 ORDER BY f1}
1.46 +} {1 2 2 4 3 8 4 16 5 32 6 64 7 128 8 256 9 512 10 1024}
1.47 +
1.48 +# Unknown column name in an expression
1.49 +#
1.50 +do_test update-3.2 {
1.51 + set v [catch {execsql {UPDATE test1 SET f1=f3*2 WHERE f2==32}} msg]
1.52 + lappend v $msg
1.53 +} {1 {no such column: f3}}
1.54 +do_test update-3.3 {
1.55 + set v [catch {execsql {UPDATE test1 SET f1=test2.f1*2 WHERE f2==32}} msg]
1.56 + lappend v $msg
1.57 +} {1 {no such column: test2.f1}}
1.58 +do_test update-3.4 {
1.59 + set v [catch {execsql {UPDATE test1 SET f3=f1*2 WHERE f2==32}} msg]
1.60 + lappend v $msg
1.61 +} {1 {no such column: f3}}
1.62 +
1.63 +# Actually do some updates
1.64 +#
1.65 +do_test update-3.5 {
1.66 + execsql {UPDATE test1 SET f2=f2*3}
1.67 +} {}
1.68 +do_test update-3.5.1 {
1.69 + db changes
1.70 +} {10}
1.71 +
1.72 +# verify that SELECT does not reset the change counter
1.73 +do_test update-3.5.2 {
1.74 + db eval {SELECT count(*) FROM test1}
1.75 +} {10}
1.76 +do_test update-3.5.3 {
1.77 + db changes
1.78 +} {10}
1.79 +
1.80 +do_test update-3.6 {
1.81 + execsql {SELECT * FROM test1 ORDER BY f1}
1.82 +} {1 6 2 12 3 24 4 48 5 96 6 192 7 384 8 768 9 1536 10 3072}
1.83 +do_test update-3.7 {
1.84 + execsql {PRAGMA count_changes=on}
1.85 + execsql {UPDATE test1 SET f2=f2/3 WHERE f1<=5}
1.86 +} {5}
1.87 +do_test update-3.8 {
1.88 + execsql {SELECT * FROM test1 ORDER BY f1}
1.89 +} {1 2 2 4 3 8 4 16 5 32 6 192 7 384 8 768 9 1536 10 3072}
1.90 +do_test update-3.9 {
1.91 + execsql {UPDATE test1 SET f2=f2/3 WHERE f1>5}
1.92 +} {5}
1.93 +do_test update-3.10 {
1.94 + execsql {SELECT * FROM test1 ORDER BY f1}
1.95 +} {1 2 2 4 3 8 4 16 5 32 6 64 7 128 8 256 9 512 10 1024}
1.96 +
1.97 +# Swap the values of f1 and f2 for all elements
1.98 +#
1.99 +do_test update-3.11 {
1.100 + execsql {UPDATE test1 SET F2=f1, F1=f2}
1.101 +} {10}
1.102 +do_test update-3.12 {
1.103 + execsql {SELECT * FROM test1 ORDER BY F1}
1.104 +} {2 1 4 2 8 3 16 4 32 5 64 6 128 7 256 8 512 9 1024 10}
1.105 +do_test update-3.13 {
1.106 + execsql {PRAGMA count_changes=off}
1.107 + execsql {UPDATE test1 SET F2=f1, F1=f2}
1.108 +} {}
1.109 +do_test update-3.14 {
1.110 + execsql {SELECT * FROM test1 ORDER BY F1}
1.111 +} {1 2 2 4 3 8 4 16 5 32 6 64 7 128 8 256 9 512 10 1024}
1.112 +
1.113 +# Create duplicate entries and make sure updating still
1.114 +# works.
1.115 +#
1.116 +do_test update-4.0 {
1.117 + execsql {
1.118 + DELETE FROM test1 WHERE f1<=5;
1.119 + INSERT INTO test1(f1,f2) VALUES(8,88);
1.120 + INSERT INTO test1(f1,f2) VALUES(8,888);
1.121 + INSERT INTO test1(f1,f2) VALUES(77,128);
1.122 + INSERT INTO test1(f1,f2) VALUES(777,128);
1.123 + }
1.124 + execsql {SELECT * FROM test1 ORDER BY f1,f2}
1.125 +} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
1.126 +do_test update-4.1 {
1.127 + execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8}
1.128 + execsql {SELECT * FROM test1 ORDER BY f1,f2}
1.129 +} {6 64 7 128 8 89 8 257 8 889 9 512 10 1024 77 128 777 128}
1.130 +do_test update-4.2 {
1.131 + execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2>800}
1.132 + execsql {SELECT * FROM test1 ORDER BY f1,f2}
1.133 +} {6 64 7 128 8 89 8 257 8 888 9 512 10 1024 77 128 777 128}
1.134 +do_test update-4.3 {
1.135 + execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2<800}
1.136 + execsql {SELECT * FROM test1 ORDER BY f1,f2}
1.137 +} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
1.138 +do_test update-4.4 {
1.139 + execsql {UPDATE test1 SET f1=f1+1 WHERE f2==128}
1.140 + execsql {SELECT * FROM test1 ORDER BY f1,f2}
1.141 +} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 778 128}
1.142 +do_test update-4.5 {
1.143 + execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and f2==128}
1.144 + execsql {SELECT * FROM test1 ORDER BY f1,f2}
1.145 +} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 777 128}
1.146 +do_test update-4.6 {
1.147 + execsql {
1.148 + PRAGMA count_changes=on;
1.149 + UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128;
1.150 + }
1.151 +} {2}
1.152 +do_test update-4.7 {
1.153 + execsql {
1.154 + PRAGMA count_changes=off;
1.155 + SELECT * FROM test1 ORDER BY f1,f2
1.156 + }
1.157 +} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
1.158 +
1.159 +# Repeat the previous sequence of tests with an index.
1.160 +#
1.161 +do_test update-5.0 {
1.162 + execsql {CREATE INDEX idx1 ON test1(f1)}
1.163 + execsql {SELECT * FROM test1 ORDER BY f1,f2}
1.164 +} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
1.165 +do_test update-5.1 {
1.166 + execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8}
1.167 + execsql {SELECT * FROM test1 ORDER BY f1,f2}
1.168 +} {6 64 7 128 8 89 8 257 8 889 9 512 10 1024 77 128 777 128}
1.169 +do_test update-5.2 {
1.170 + execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2>800}
1.171 + execsql {SELECT * FROM test1 ORDER BY f1,f2}
1.172 +} {6 64 7 128 8 89 8 257 8 888 9 512 10 1024 77 128 777 128}
1.173 +do_test update-5.3 {
1.174 + execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2<800}
1.175 + execsql {SELECT * FROM test1 ORDER BY f1,f2}
1.176 +} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
1.177 +do_test update-5.4 {
1.178 + execsql {UPDATE test1 SET f1=f1+1 WHERE f2==128}
1.179 + execsql {SELECT * FROM test1 ORDER BY f1,f2}
1.180 +} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 778 128}
1.181 +do_test update-5.4.1 {
1.182 + execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
1.183 +} {78 128}
1.184 +do_test update-5.4.2 {
1.185 + execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
1.186 +} {778 128}
1.187 +do_test update-5.4.3 {
1.188 + execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
1.189 +} {8 88 8 128 8 256 8 888}
1.190 +do_test update-5.5 {
1.191 + execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and f2==128}
1.192 +} {}
1.193 +do_test update-5.5.1 {
1.194 + execsql {SELECT * FROM test1 ORDER BY f1,f2}
1.195 +} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 777 128}
1.196 +do_test update-5.5.2 {
1.197 + execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
1.198 +} {78 128}
1.199 +do_test update-5.5.3 {
1.200 + execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
1.201 +} {}
1.202 +do_test update-5.5.4 {
1.203 + execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
1.204 +} {777 128}
1.205 +do_test update-5.5.5 {
1.206 + execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
1.207 +} {8 88 8 128 8 256 8 888}
1.208 +do_test update-5.6 {
1.209 + execsql {
1.210 + PRAGMA count_changes=on;
1.211 + UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128;
1.212 + }
1.213 +} {2}
1.214 +do_test update-5.6.1 {
1.215 + execsql {
1.216 + PRAGMA count_changes=off;
1.217 + SELECT * FROM test1 ORDER BY f1,f2
1.218 + }
1.219 +} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
1.220 +do_test update-5.6.2 {
1.221 + execsql {SELECT * FROM test1 WHERE f1==77 ORDER BY f1,f2}
1.222 +} {77 128}
1.223 +do_test update-5.6.3 {
1.224 + execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
1.225 +} {}
1.226 +do_test update-5.6.4 {
1.227 + execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
1.228 +} {777 128}
1.229 +do_test update-5.6.5 {
1.230 + execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
1.231 +} {8 88 8 256 8 888}
1.232 +
1.233 +# Repeat the previous sequence of tests with a different index.
1.234 +#
1.235 +execsql {PRAGMA synchronous=FULL}
1.236 +do_test update-6.0 {
1.237 + execsql {DROP INDEX idx1}
1.238 + execsql {CREATE INDEX idx1 ON test1(f2)}
1.239 + execsql {SELECT * FROM test1 ORDER BY f1,f2}
1.240 +} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
1.241 +do_test update-6.1 {
1.242 + execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8}
1.243 + execsql {SELECT * FROM test1 ORDER BY f1,f2}
1.244 +} {6 64 7 128 8 89 8 257 8 889 9 512 10 1024 77 128 777 128}
1.245 +do_test update-6.1.1 {
1.246 + execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
1.247 +} {8 89 8 257 8 889}
1.248 +do_test update-6.1.2 {
1.249 + execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2}
1.250 +} {8 89}
1.251 +do_test update-6.1.3 {
1.252 + execsql {SELECT * FROM test1 WHERE f1==88 ORDER BY f1,f2}
1.253 +} {}
1.254 +do_test update-6.2 {
1.255 + execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2>800}
1.256 + execsql {SELECT * FROM test1 ORDER BY f1,f2}
1.257 +} {6 64 7 128 8 89 8 257 8 888 9 512 10 1024 77 128 777 128}
1.258 +do_test update-6.3 {
1.259 + execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2<800}
1.260 + execsql {SELECT * FROM test1 ORDER BY f1,f2}
1.261 +} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
1.262 +do_test update-6.3.1 {
1.263 + execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
1.264 +} {8 88 8 256 8 888}
1.265 +do_test update-6.3.2 {
1.266 + execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2}
1.267 +} {}
1.268 +do_test update-6.3.3 {
1.269 + execsql {SELECT * FROM test1 WHERE f2==88 ORDER BY f1,f2}
1.270 +} {8 88}
1.271 +do_test update-6.4 {
1.272 + execsql {UPDATE test1 SET f1=f1+1 WHERE f2==128}
1.273 + execsql {SELECT * FROM test1 ORDER BY f1,f2}
1.274 +} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 778 128}
1.275 +do_test update-6.4.1 {
1.276 + execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
1.277 +} {78 128}
1.278 +do_test update-6.4.2 {
1.279 + execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
1.280 +} {778 128}
1.281 +do_test update-6.4.3 {
1.282 + execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
1.283 +} {8 88 8 128 8 256 8 888}
1.284 +do_test update-6.5 {
1.285 + execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and f2==128}
1.286 + execsql {SELECT * FROM test1 ORDER BY f1,f2}
1.287 +} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 777 128}
1.288 +do_test update-6.5.1 {
1.289 + execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
1.290 +} {78 128}
1.291 +do_test update-6.5.2 {
1.292 + execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
1.293 +} {}
1.294 +do_test update-6.5.3 {
1.295 + execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
1.296 +} {777 128}
1.297 +do_test update-6.5.4 {
1.298 + execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
1.299 +} {8 88 8 128 8 256 8 888}
1.300 +do_test update-6.6 {
1.301 + execsql {UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128}
1.302 + execsql {SELECT * FROM test1 ORDER BY f1,f2}
1.303 +} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
1.304 +do_test update-6.6.1 {
1.305 + execsql {SELECT * FROM test1 WHERE f1==77 ORDER BY f1,f2}
1.306 +} {77 128}
1.307 +do_test update-6.6.2 {
1.308 + execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
1.309 +} {}
1.310 +do_test update-6.6.3 {
1.311 + execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
1.312 +} {777 128}
1.313 +do_test update-6.6.4 {
1.314 + execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
1.315 +} {8 88 8 256 8 888}
1.316 +
1.317 +# Repeat the previous sequence of tests with multiple
1.318 +# indices
1.319 +#
1.320 +do_test update-7.0 {
1.321 + execsql {CREATE INDEX idx2 ON test1(f2)}
1.322 + execsql {CREATE INDEX idx3 ON test1(f1,f2)}
1.323 + execsql {SELECT * FROM test1 ORDER BY f1,f2}
1.324 +} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
1.325 +do_test update-7.1 {
1.326 + execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8}
1.327 + execsql {SELECT * FROM test1 ORDER BY f1,f2}
1.328 +} {6 64 7 128 8 89 8 257 8 889 9 512 10 1024 77 128 777 128}
1.329 +do_test update-7.1.1 {
1.330 + execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
1.331 +} {8 89 8 257 8 889}
1.332 +do_test update-7.1.2 {
1.333 + execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2}
1.334 +} {8 89}
1.335 +do_test update-7.1.3 {
1.336 + execsql {SELECT * FROM test1 WHERE f1==88 ORDER BY f1,f2}
1.337 +} {}
1.338 +do_test update-7.2 {
1.339 + execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2>800}
1.340 + execsql {SELECT * FROM test1 ORDER BY f1,f2}
1.341 +} {6 64 7 128 8 89 8 257 8 888 9 512 10 1024 77 128 777 128}
1.342 +do_test update-7.3 {
1.343 + # explain {UPDATE test1 SET f2=f2-1 WHERE f1==8 and F2<300}
1.344 + execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2<800}
1.345 + execsql {SELECT * FROM test1 ORDER BY f1,f2}
1.346 +} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
1.347 +do_test update-7.3.1 {
1.348 + execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
1.349 +} {8 88 8 256 8 888}
1.350 +do_test update-7.3.2 {
1.351 + execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2}
1.352 +} {}
1.353 +do_test update-7.3.3 {
1.354 + execsql {SELECT * FROM test1 WHERE f2==88 ORDER BY f1,f2}
1.355 +} {8 88}
1.356 +do_test update-7.4 {
1.357 + execsql {UPDATE test1 SET f1=f1+1 WHERE f2==128}
1.358 + execsql {SELECT * FROM test1 ORDER BY f1,f2}
1.359 +} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 778 128}
1.360 +do_test update-7.4.1 {
1.361 + execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
1.362 +} {78 128}
1.363 +do_test update-7.4.2 {
1.364 + execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
1.365 +} {778 128}
1.366 +do_test update-7.4.3 {
1.367 + execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
1.368 +} {8 88 8 128 8 256 8 888}
1.369 +do_test update-7.5 {
1.370 + execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and f2==128}
1.371 + execsql {SELECT * FROM test1 ORDER BY f1,f2}
1.372 +} {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 777 128}
1.373 +do_test update-7.5.1 {
1.374 + execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2}
1.375 +} {78 128}
1.376 +do_test update-7.5.2 {
1.377 + execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
1.378 +} {}
1.379 +do_test update-7.5.3 {
1.380 + execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
1.381 +} {777 128}
1.382 +do_test update-7.5.4 {
1.383 + execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
1.384 +} {8 88 8 128 8 256 8 888}
1.385 +do_test update-7.6 {
1.386 + execsql {UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128}
1.387 + execsql {SELECT * FROM test1 ORDER BY f1,f2}
1.388 +} {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128}
1.389 +do_test update-7.6.1 {
1.390 + execsql {SELECT * FROM test1 WHERE f1==77 ORDER BY f1,f2}
1.391 +} {77 128}
1.392 +do_test update-7.6.2 {
1.393 + execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2}
1.394 +} {}
1.395 +do_test update-7.6.3 {
1.396 + execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2}
1.397 +} {777 128}
1.398 +do_test update-7.6.4 {
1.399 + execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2}
1.400 +} {8 88 8 256 8 888}
1.401 +
1.402 +# Error messages
1.403 +#
1.404 +do_test update-9.1 {
1.405 + set v [catch {execsql {
1.406 + UPDATE test1 SET x=11 WHERE f1=1025
1.407 + }} msg]
1.408 + lappend v $msg
1.409 +} {1 {no such column: x}}
1.410 +do_test update-9.2 {
1.411 + set v [catch {execsql {
1.412 + UPDATE test1 SET f1=x(11) WHERE f1=1025
1.413 + }} msg]
1.414 + lappend v $msg
1.415 +} {1 {no such function: x}}
1.416 +do_test update-9.3 {
1.417 + set v [catch {execsql {
1.418 + UPDATE test1 SET f1=11 WHERE x=1025
1.419 + }} msg]
1.420 + lappend v $msg
1.421 +} {1 {no such column: x}}
1.422 +do_test update-9.4 {
1.423 + set v [catch {execsql {
1.424 + UPDATE test1 SET f1=11 WHERE x(f1)=1025
1.425 + }} msg]
1.426 + lappend v $msg
1.427 +} {1 {no such function: x}}
1.428 +
1.429 +# Try doing updates on a unique column where the value does not
1.430 +# really change.
1.431 +#
1.432 +do_test update-10.1 {
1.433 + execsql {
1.434 + DROP TABLE test1;
1.435 + CREATE TABLE t1(
1.436 + a integer primary key,
1.437 + b UNIQUE,
1.438 + c, d,
1.439 + e, f,
1.440 + UNIQUE(c,d)
1.441 + );
1.442 + INSERT INTO t1 VALUES(1,2,3,4,5,6);
1.443 + INSERT INTO t1 VALUES(2,3,4,4,6,7);
1.444 + SELECT * FROM t1
1.445 + }
1.446 +} {1 2 3 4 5 6 2 3 4 4 6 7}
1.447 +do_test update-10.2 {
1.448 + catchsql {
1.449 + UPDATE t1 SET a=1, e=9 WHERE f=6;
1.450 + SELECT * FROM t1;
1.451 + }
1.452 +} {0 {1 2 3 4 9 6 2 3 4 4 6 7}}
1.453 +do_test update-10.3 {
1.454 + catchsql {
1.455 + UPDATE t1 SET a=1, e=10 WHERE f=7;
1.456 + SELECT * FROM t1;
1.457 + }
1.458 +} {1 {PRIMARY KEY must be unique}}
1.459 +do_test update-10.4 {
1.460 + catchsql {
1.461 + SELECT * FROM t1;
1.462 + }
1.463 +} {0 {1 2 3 4 9 6 2 3 4 4 6 7}}
1.464 +do_test update-10.5 {
1.465 + catchsql {
1.466 + UPDATE t1 SET b=2, e=11 WHERE f=6;
1.467 + SELECT * FROM t1;
1.468 + }
1.469 +} {0 {1 2 3 4 11 6 2 3 4 4 6 7}}
1.470 +do_test update-10.6 {
1.471 + catchsql {
1.472 + UPDATE t1 SET b=2, e=12 WHERE f=7;
1.473 + SELECT * FROM t1;
1.474 + }
1.475 +} {1 {column b is not unique}}
1.476 +do_test update-10.7 {
1.477 + catchsql {
1.478 + SELECT * FROM t1;
1.479 + }
1.480 +} {0 {1 2 3 4 11 6 2 3 4 4 6 7}}
1.481 +do_test update-10.8 {
1.482 + catchsql {
1.483 + UPDATE t1 SET c=3, d=4, e=13 WHERE f=6;
1.484 + SELECT * FROM t1;
1.485 + }
1.486 +} {0 {1 2 3 4 13 6 2 3 4 4 6 7}}
1.487 +do_test update-10.9 {
1.488 + catchsql {
1.489 + UPDATE t1 SET c=3, d=4, e=14 WHERE f=7;
1.490 + SELECT * FROM t1;
1.491 + }
1.492 +} {1 {columns c, d are not unique}}
1.493 +do_test update-10.10 {
1.494 + catchsql {
1.495 + SELECT * FROM t1;
1.496 + }
1.497 +} {0 {1 2 3 4 13 6 2 3 4 4 6 7}}
1.498 +
1.499 +# Make sure we can handle a subquery in the where clause.
1.500 +#
1.501 +ifcapable subquery {
1.502 + do_test update-11.1 {
1.503 + execsql {
1.504 + UPDATE t1 SET e=e+1 WHERE b IN (SELECT b FROM t1);
1.505 + SELECT b,e FROM t1;
1.506 + }
1.507 + } {2 14 3 7}
1.508 + do_test update-11.2 {
1.509 + execsql {
1.510 + UPDATE t1 SET e=e+1 WHERE a IN (SELECT a FROM t1);
1.511 + SELECT a,e FROM t1;
1.512 + }
1.513 + } {1 15 2 8}
1.514 +}
1.515 +
1.516 +integrity_check update-12.1
1.517 +
1.518 +# Ticket 602. Updates should occur in the same order as the records
1.519 +# were discovered in the WHERE clause.
1.520 +#
1.521 +do_test update-13.1 {
1.522 + execsql {
1.523 + BEGIN;
1.524 + CREATE TABLE t2(a);
1.525 + INSERT INTO t2 VALUES(1);
1.526 + INSERT INTO t2 VALUES(2);
1.527 + INSERT INTO t2 SELECT a+2 FROM t2;
1.528 + INSERT INTO t2 SELECT a+4 FROM t2;
1.529 + INSERT INTO t2 SELECT a+8 FROM t2;
1.530 + INSERT INTO t2 SELECT a+16 FROM t2;
1.531 + INSERT INTO t2 SELECT a+32 FROM t2;
1.532 + INSERT INTO t2 SELECT a+64 FROM t2;
1.533 + INSERT INTO t2 SELECT a+128 FROM t2;
1.534 + INSERT INTO t2 SELECT a+256 FROM t2;
1.535 + INSERT INTO t2 SELECT a+512 FROM t2;
1.536 + INSERT INTO t2 SELECT a+1024 FROM t2;
1.537 + COMMIT;
1.538 + SELECT count(*) FROM t2;
1.539 + }
1.540 +} {2048}
1.541 +do_test update-13.2 {
1.542 + execsql {
1.543 + SELECT count(*) FROM t2 WHERE a=rowid;
1.544 + }
1.545 +} {2048}
1.546 +do_test update-13.3 {
1.547 + execsql {
1.548 + UPDATE t2 SET rowid=rowid-1;
1.549 + SELECT count(*) FROM t2 WHERE a=rowid+1;
1.550 + }
1.551 +} {2048}
1.552 +do_test update-13.3 {
1.553 + execsql {
1.554 + UPDATE t2 SET rowid=rowid+10000;
1.555 + UPDATE t2 SET rowid=rowid-9999;
1.556 + SELECT count(*) FROM t2 WHERE a=rowid;
1.557 + }
1.558 +} {2048}
1.559 +do_test update-13.4 {
1.560 + execsql {
1.561 + BEGIN;
1.562 + INSERT INTO t2 SELECT a+2048 FROM t2;
1.563 + INSERT INTO t2 SELECT a+4096 FROM t2;
1.564 + INSERT INTO t2 SELECT a+8192 FROM t2;
1.565 + SELECT count(*) FROM t2 WHERE a=rowid;
1.566 + COMMIT;
1.567 + }
1.568 +} 16384
1.569 +do_test update-13.5 {
1.570 + execsql {
1.571 + UPDATE t2 SET rowid=rowid-1;
1.572 + SELECT count(*) FROM t2 WHERE a=rowid+1;
1.573 + }
1.574 +} 16384
1.575 +
1.576 +integrity_check update-13.6
1.577 +
1.578 +ifcapable {trigger} {
1.579 +# Test for proper detection of malformed WHEN clauses on UPDATE triggers.
1.580 +#
1.581 +do_test update-14.1 {
1.582 + execsql {
1.583 + CREATE TABLE t3(a,b,c);
1.584 + CREATE TRIGGER t3r1 BEFORE UPDATE on t3 WHEN nosuchcol BEGIN
1.585 + SELECT 'illegal WHEN clause';
1.586 + END;
1.587 + }
1.588 +} {}
1.589 +do_test update-14.2 {
1.590 + catchsql {
1.591 + UPDATE t3 SET a=1;
1.592 + }
1.593 +} {1 {no such column: nosuchcol}}
1.594 +do_test update-14.3 {
1.595 + execsql {
1.596 + CREATE TABLE t4(a,b,c);
1.597 + CREATE TRIGGER t4r1 AFTER UPDATE on t4 WHEN nosuchcol BEGIN
1.598 + SELECT 'illegal WHEN clause';
1.599 + END;
1.600 + }
1.601 +} {}
1.602 +do_test update-14.4 {
1.603 + catchsql {
1.604 + UPDATE t4 SET a=1;
1.605 + }
1.606 +} {1 {no such column: nosuchcol}}
1.607 +
1.608 +} ;# ifcapable {trigger}
1.609 +
1.610 +
1.611 +finish_test