| OLD | NEW |
|---|
| (Empty) |
| 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 UPDATE statement. | |
| 13 # | |
| 14 # $Id: update.test,v 1.19 2008/04/10 18:44:36 drh Exp $ | |
| 15 | |
| 16 set testdir [file dirname $argv0] | |
| 17 source $testdir/tester.tcl | |
| 18 | |
| 19 # Try to update an non-existent table | |
| 20 # | |
| 21 do_test update-1.1 { | |
| 22 set v [catch {execsql {UPDATE test1 SET f2=5 WHERE f1<1}} msg] | |
| 23 lappend v $msg | |
| 24 } {1 {no such table: test1}} | |
| 25 | |
| 26 # Try to update a read-only table | |
| 27 # | |
| 28 do_test update-2.1 { | |
| 29 set v [catch \ | |
| 30 {execsql {UPDATE sqlite_master SET name='xyz' WHERE name='123'}} msg] | |
| 31 lappend v $msg | |
| 32 } {1 {table sqlite_master may not be modified}} | |
| 33 | |
| 34 # Create a table to work with | |
| 35 # | |
| 36 do_test update-3.1 { | |
| 37 execsql {CREATE TABLE test1(f1 int,f2 int)} | |
| 38 for {set i 1} {$i<=10} {incr i} { | |
| 39 set sql "INSERT INTO test1 VALUES($i,[expr {1<<$i}])" | |
| 40 execsql $sql | |
| 41 } | |
| 42 execsql {SELECT * FROM test1 ORDER BY f1} | |
| 43 } {1 2 2 4 3 8 4 16 5 32 6 64 7 128 8 256 9 512 10 1024} | |
| 44 | |
| 45 # Unknown column name in an expression | |
| 46 # | |
| 47 do_test update-3.2 { | |
| 48 set v [catch {execsql {UPDATE test1 SET f1=f3*2 WHERE f2==32}} msg] | |
| 49 lappend v $msg | |
| 50 } {1 {no such column: f3}} | |
| 51 do_test update-3.3 { | |
| 52 set v [catch {execsql {UPDATE test1 SET f1=test2.f1*2 WHERE f2==32}} msg] | |
| 53 lappend v $msg | |
| 54 } {1 {no such column: test2.f1}} | |
| 55 do_test update-3.4 { | |
| 56 set v [catch {execsql {UPDATE test1 SET f3=f1*2 WHERE f2==32}} msg] | |
| 57 lappend v $msg | |
| 58 } {1 {no such column: f3}} | |
| 59 | |
| 60 # Actually do some updates | |
| 61 # | |
| 62 do_test update-3.5 { | |
| 63 execsql {UPDATE test1 SET f2=f2*3} | |
| 64 } {} | |
| 65 do_test update-3.5.1 { | |
| 66 db changes | |
| 67 } {10} | |
| 68 | |
| 69 # verify that SELECT does not reset the change counter | |
| 70 do_test update-3.5.2 { | |
| 71 db eval {SELECT count(*) FROM test1} | |
| 72 } {10} | |
| 73 do_test update-3.5.3 { | |
| 74 db changes | |
| 75 } {10} | |
| 76 | |
| 77 do_test update-3.6 { | |
| 78 execsql {SELECT * FROM test1 ORDER BY f1} | |
| 79 } {1 6 2 12 3 24 4 48 5 96 6 192 7 384 8 768 9 1536 10 3072} | |
| 80 do_test update-3.7 { | |
| 81 execsql {PRAGMA count_changes=on} | |
| 82 execsql {UPDATE test1 SET f2=f2/3 WHERE f1<=5} | |
| 83 } {5} | |
| 84 do_test update-3.8 { | |
| 85 execsql {SELECT * FROM test1 ORDER BY f1} | |
| 86 } {1 2 2 4 3 8 4 16 5 32 6 192 7 384 8 768 9 1536 10 3072} | |
| 87 do_test update-3.9 { | |
| 88 execsql {UPDATE test1 SET f2=f2/3 WHERE f1>5} | |
| 89 } {5} | |
| 90 do_test update-3.10 { | |
| 91 execsql {SELECT * FROM test1 ORDER BY f1} | |
| 92 } {1 2 2 4 3 8 4 16 5 32 6 64 7 128 8 256 9 512 10 1024} | |
| 93 | |
| 94 # Swap the values of f1 and f2 for all elements | |
| 95 # | |
| 96 do_test update-3.11 { | |
| 97 execsql {UPDATE test1 SET F2=f1, F1=f2} | |
| 98 } {10} | |
| 99 do_test update-3.12 { | |
| 100 execsql {SELECT * FROM test1 ORDER BY F1} | |
| 101 } {2 1 4 2 8 3 16 4 32 5 64 6 128 7 256 8 512 9 1024 10} | |
| 102 do_test update-3.13 { | |
| 103 execsql {PRAGMA count_changes=off} | |
| 104 execsql {UPDATE test1 SET F2=f1, F1=f2} | |
| 105 } {} | |
| 106 do_test update-3.14 { | |
| 107 execsql {SELECT * FROM test1 ORDER BY F1} | |
| 108 } {1 2 2 4 3 8 4 16 5 32 6 64 7 128 8 256 9 512 10 1024} | |
| 109 | |
| 110 # Create duplicate entries and make sure updating still | |
| 111 # works. | |
| 112 # | |
| 113 do_test update-4.0 { | |
| 114 execsql { | |
| 115 DELETE FROM test1 WHERE f1<=5; | |
| 116 INSERT INTO test1(f1,f2) VALUES(8,88); | |
| 117 INSERT INTO test1(f1,f2) VALUES(8,888); | |
| 118 INSERT INTO test1(f1,f2) VALUES(77,128); | |
| 119 INSERT INTO test1(f1,f2) VALUES(777,128); | |
| 120 } | |
| 121 execsql {SELECT * FROM test1 ORDER BY f1,f2} | |
| 122 } {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128} | |
| 123 do_test update-4.1 { | |
| 124 execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8} | |
| 125 execsql {SELECT * FROM test1 ORDER BY f1,f2} | |
| 126 } {6 64 7 128 8 89 8 257 8 889 9 512 10 1024 77 128 777 128} | |
| 127 do_test update-4.2 { | |
| 128 execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2>800} | |
| 129 execsql {SELECT * FROM test1 ORDER BY f1,f2} | |
| 130 } {6 64 7 128 8 89 8 257 8 888 9 512 10 1024 77 128 777 128} | |
| 131 do_test update-4.3 { | |
| 132 execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2<800} | |
| 133 execsql {SELECT * FROM test1 ORDER BY f1,f2} | |
| 134 } {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128} | |
| 135 do_test update-4.4 { | |
| 136 execsql {UPDATE test1 SET f1=f1+1 WHERE f2==128} | |
| 137 execsql {SELECT * FROM test1 ORDER BY f1,f2} | |
| 138 } {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 778 128} | |
| 139 do_test update-4.5 { | |
| 140 execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and f2==128} | |
| 141 execsql {SELECT * FROM test1 ORDER BY f1,f2} | |
| 142 } {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 777 128} | |
| 143 do_test update-4.6 { | |
| 144 execsql { | |
| 145 PRAGMA count_changes=on; | |
| 146 UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128; | |
| 147 } | |
| 148 } {2} | |
| 149 do_test update-4.7 { | |
| 150 execsql { | |
| 151 PRAGMA count_changes=off; | |
| 152 SELECT * FROM test1 ORDER BY f1,f2 | |
| 153 } | |
| 154 } {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128} | |
| 155 | |
| 156 # Repeat the previous sequence of tests with an index. | |
| 157 # | |
| 158 do_test update-5.0 { | |
| 159 execsql {CREATE INDEX idx1 ON test1(f1)} | |
| 160 execsql {SELECT * FROM test1 ORDER BY f1,f2} | |
| 161 } {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128} | |
| 162 do_test update-5.1 { | |
| 163 execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8} | |
| 164 execsql {SELECT * FROM test1 ORDER BY f1,f2} | |
| 165 } {6 64 7 128 8 89 8 257 8 889 9 512 10 1024 77 128 777 128} | |
| 166 do_test update-5.2 { | |
| 167 execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2>800} | |
| 168 execsql {SELECT * FROM test1 ORDER BY f1,f2} | |
| 169 } {6 64 7 128 8 89 8 257 8 888 9 512 10 1024 77 128 777 128} | |
| 170 do_test update-5.3 { | |
| 171 execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2<800} | |
| 172 execsql {SELECT * FROM test1 ORDER BY f1,f2} | |
| 173 } {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128} | |
| 174 do_test update-5.4 { | |
| 175 execsql {UPDATE test1 SET f1=f1+1 WHERE f2==128} | |
| 176 execsql {SELECT * FROM test1 ORDER BY f1,f2} | |
| 177 } {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 778 128} | |
| 178 do_test update-5.4.1 { | |
| 179 execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2} | |
| 180 } {78 128} | |
| 181 do_test update-5.4.2 { | |
| 182 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2} | |
| 183 } {778 128} | |
| 184 do_test update-5.4.3 { | |
| 185 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} | |
| 186 } {8 88 8 128 8 256 8 888} | |
| 187 do_test update-5.5 { | |
| 188 execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and f2==128} | |
| 189 } {} | |
| 190 do_test update-5.5.1 { | |
| 191 execsql {SELECT * FROM test1 ORDER BY f1,f2} | |
| 192 } {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 777 128} | |
| 193 do_test update-5.5.2 { | |
| 194 execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2} | |
| 195 } {78 128} | |
| 196 do_test update-5.5.3 { | |
| 197 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2} | |
| 198 } {} | |
| 199 do_test update-5.5.4 { | |
| 200 execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2} | |
| 201 } {777 128} | |
| 202 do_test update-5.5.5 { | |
| 203 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} | |
| 204 } {8 88 8 128 8 256 8 888} | |
| 205 do_test update-5.6 { | |
| 206 execsql { | |
| 207 PRAGMA count_changes=on; | |
| 208 UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128; | |
| 209 } | |
| 210 } {2} | |
| 211 do_test update-5.6.1 { | |
| 212 execsql { | |
| 213 PRAGMA count_changes=off; | |
| 214 SELECT * FROM test1 ORDER BY f1,f2 | |
| 215 } | |
| 216 } {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128} | |
| 217 do_test update-5.6.2 { | |
| 218 execsql {SELECT * FROM test1 WHERE f1==77 ORDER BY f1,f2} | |
| 219 } {77 128} | |
| 220 do_test update-5.6.3 { | |
| 221 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2} | |
| 222 } {} | |
| 223 do_test update-5.6.4 { | |
| 224 execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2} | |
| 225 } {777 128} | |
| 226 do_test update-5.6.5 { | |
| 227 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} | |
| 228 } {8 88 8 256 8 888} | |
| 229 | |
| 230 # Repeat the previous sequence of tests with a different index. | |
| 231 # | |
| 232 execsql {PRAGMA synchronous=FULL} | |
| 233 do_test update-6.0 { | |
| 234 execsql {DROP INDEX idx1} | |
| 235 execsql {CREATE INDEX idx1 ON test1(f2)} | |
| 236 execsql {SELECT * FROM test1 ORDER BY f1,f2} | |
| 237 } {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128} | |
| 238 do_test update-6.1 { | |
| 239 execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8} | |
| 240 execsql {SELECT * FROM test1 ORDER BY f1,f2} | |
| 241 } {6 64 7 128 8 89 8 257 8 889 9 512 10 1024 77 128 777 128} | |
| 242 do_test update-6.1.1 { | |
| 243 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} | |
| 244 } {8 89 8 257 8 889} | |
| 245 do_test update-6.1.2 { | |
| 246 execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2} | |
| 247 } {8 89} | |
| 248 do_test update-6.1.3 { | |
| 249 execsql {SELECT * FROM test1 WHERE f1==88 ORDER BY f1,f2} | |
| 250 } {} | |
| 251 do_test update-6.2 { | |
| 252 execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2>800} | |
| 253 execsql {SELECT * FROM test1 ORDER BY f1,f2} | |
| 254 } {6 64 7 128 8 89 8 257 8 888 9 512 10 1024 77 128 777 128} | |
| 255 do_test update-6.3 { | |
| 256 execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2<800} | |
| 257 execsql {SELECT * FROM test1 ORDER BY f1,f2} | |
| 258 } {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128} | |
| 259 do_test update-6.3.1 { | |
| 260 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} | |
| 261 } {8 88 8 256 8 888} | |
| 262 do_test update-6.3.2 { | |
| 263 execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2} | |
| 264 } {} | |
| 265 do_test update-6.3.3 { | |
| 266 execsql {SELECT * FROM test1 WHERE f2==88 ORDER BY f1,f2} | |
| 267 } {8 88} | |
| 268 do_test update-6.4 { | |
| 269 execsql {UPDATE test1 SET f1=f1+1 WHERE f2==128} | |
| 270 execsql {SELECT * FROM test1 ORDER BY f1,f2} | |
| 271 } {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 778 128} | |
| 272 do_test update-6.4.1 { | |
| 273 execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2} | |
| 274 } {78 128} | |
| 275 do_test update-6.4.2 { | |
| 276 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2} | |
| 277 } {778 128} | |
| 278 do_test update-6.4.3 { | |
| 279 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} | |
| 280 } {8 88 8 128 8 256 8 888} | |
| 281 do_test update-6.5 { | |
| 282 execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and f2==128} | |
| 283 execsql {SELECT * FROM test1 ORDER BY f1,f2} | |
| 284 } {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 777 128} | |
| 285 do_test update-6.5.1 { | |
| 286 execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2} | |
| 287 } {78 128} | |
| 288 do_test update-6.5.2 { | |
| 289 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2} | |
| 290 } {} | |
| 291 do_test update-6.5.3 { | |
| 292 execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2} | |
| 293 } {777 128} | |
| 294 do_test update-6.5.4 { | |
| 295 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} | |
| 296 } {8 88 8 128 8 256 8 888} | |
| 297 do_test update-6.6 { | |
| 298 execsql {UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128} | |
| 299 execsql {SELECT * FROM test1 ORDER BY f1,f2} | |
| 300 } {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128} | |
| 301 do_test update-6.6.1 { | |
| 302 execsql {SELECT * FROM test1 WHERE f1==77 ORDER BY f1,f2} | |
| 303 } {77 128} | |
| 304 do_test update-6.6.2 { | |
| 305 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2} | |
| 306 } {} | |
| 307 do_test update-6.6.3 { | |
| 308 execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2} | |
| 309 } {777 128} | |
| 310 do_test update-6.6.4 { | |
| 311 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} | |
| 312 } {8 88 8 256 8 888} | |
| 313 | |
| 314 # Repeat the previous sequence of tests with multiple | |
| 315 # indices | |
| 316 # | |
| 317 do_test update-7.0 { | |
| 318 execsql {CREATE INDEX idx2 ON test1(f2)} | |
| 319 execsql {CREATE INDEX idx3 ON test1(f1,f2)} | |
| 320 execsql {SELECT * FROM test1 ORDER BY f1,f2} | |
| 321 } {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128} | |
| 322 do_test update-7.1 { | |
| 323 execsql {UPDATE test1 SET f2=f2+1 WHERE f1==8} | |
| 324 execsql {SELECT * FROM test1 ORDER BY f1,f2} | |
| 325 } {6 64 7 128 8 89 8 257 8 889 9 512 10 1024 77 128 777 128} | |
| 326 do_test update-7.1.1 { | |
| 327 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} | |
| 328 } {8 89 8 257 8 889} | |
| 329 do_test update-7.1.2 { | |
| 330 execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2} | |
| 331 } {8 89} | |
| 332 do_test update-7.1.3 { | |
| 333 execsql {SELECT * FROM test1 WHERE f1==88 ORDER BY f1,f2} | |
| 334 } {} | |
| 335 do_test update-7.2 { | |
| 336 execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2>800} | |
| 337 execsql {SELECT * FROM test1 ORDER BY f1,f2} | |
| 338 } {6 64 7 128 8 89 8 257 8 888 9 512 10 1024 77 128 777 128} | |
| 339 do_test update-7.3 { | |
| 340 # explain {UPDATE test1 SET f2=f2-1 WHERE f1==8 and F2<300} | |
| 341 execsql {UPDATE test1 SET f2=f2-1 WHERE f1==8 and f2<800} | |
| 342 execsql {SELECT * FROM test1 ORDER BY f1,f2} | |
| 343 } {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128} | |
| 344 do_test update-7.3.1 { | |
| 345 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} | |
| 346 } {8 88 8 256 8 888} | |
| 347 do_test update-7.3.2 { | |
| 348 execsql {SELECT * FROM test1 WHERE f2==89 ORDER BY f1,f2} | |
| 349 } {} | |
| 350 do_test update-7.3.3 { | |
| 351 execsql {SELECT * FROM test1 WHERE f2==88 ORDER BY f1,f2} | |
| 352 } {8 88} | |
| 353 do_test update-7.4 { | |
| 354 execsql {UPDATE test1 SET f1=f1+1 WHERE f2==128} | |
| 355 execsql {SELECT * FROM test1 ORDER BY f1,f2} | |
| 356 } {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 778 128} | |
| 357 do_test update-7.4.1 { | |
| 358 execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2} | |
| 359 } {78 128} | |
| 360 do_test update-7.4.2 { | |
| 361 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2} | |
| 362 } {778 128} | |
| 363 do_test update-7.4.3 { | |
| 364 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} | |
| 365 } {8 88 8 128 8 256 8 888} | |
| 366 do_test update-7.5 { | |
| 367 execsql {UPDATE test1 SET f1=f1-1 WHERE f1>100 and f2==128} | |
| 368 execsql {SELECT * FROM test1 ORDER BY f1,f2} | |
| 369 } {6 64 8 88 8 128 8 256 8 888 9 512 10 1024 78 128 777 128} | |
| 370 do_test update-7.5.1 { | |
| 371 execsql {SELECT * FROM test1 WHERE f1==78 ORDER BY f1,f2} | |
| 372 } {78 128} | |
| 373 do_test update-7.5.2 { | |
| 374 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2} | |
| 375 } {} | |
| 376 do_test update-7.5.3 { | |
| 377 execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2} | |
| 378 } {777 128} | |
| 379 do_test update-7.5.4 { | |
| 380 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} | |
| 381 } {8 88 8 128 8 256 8 888} | |
| 382 do_test update-7.6 { | |
| 383 execsql {UPDATE test1 SET f1=f1-1 WHERE f1<=100 and f2==128} | |
| 384 execsql {SELECT * FROM test1 ORDER BY f1,f2} | |
| 385 } {6 64 7 128 8 88 8 256 8 888 9 512 10 1024 77 128 777 128} | |
| 386 do_test update-7.6.1 { | |
| 387 execsql {SELECT * FROM test1 WHERE f1==77 ORDER BY f1,f2} | |
| 388 } {77 128} | |
| 389 do_test update-7.6.2 { | |
| 390 execsql {SELECT * FROM test1 WHERE f1==778 ORDER BY f1,f2} | |
| 391 } {} | |
| 392 do_test update-7.6.3 { | |
| 393 execsql {SELECT * FROM test1 WHERE f1==777 ORDER BY f1,f2} | |
| 394 } {777 128} | |
| 395 do_test update-7.6.4 { | |
| 396 execsql {SELECT * FROM test1 WHERE f1==8 ORDER BY f1,f2} | |
| 397 } {8 88 8 256 8 888} | |
| 398 | |
| 399 # Error messages | |
| 400 # | |
| 401 do_test update-9.1 { | |
| 402 set v [catch {execsql { | |
| 403 UPDATE test1 SET x=11 WHERE f1=1025 | |
| 404 }} msg] | |
| 405 lappend v $msg | |
| 406 } {1 {no such column: x}} | |
| 407 do_test update-9.2 { | |
| 408 set v [catch {execsql { | |
| 409 UPDATE test1 SET f1=x(11) WHERE f1=1025 | |
| 410 }} msg] | |
| 411 lappend v $msg | |
| 412 } {1 {no such function: x}} | |
| 413 do_test update-9.3 { | |
| 414 set v [catch {execsql { | |
| 415 UPDATE test1 SET f1=11 WHERE x=1025 | |
| 416 }} msg] | |
| 417 lappend v $msg | |
| 418 } {1 {no such column: x}} | |
| 419 do_test update-9.4 { | |
| 420 set v [catch {execsql { | |
| 421 UPDATE test1 SET f1=11 WHERE x(f1)=1025 | |
| 422 }} msg] | |
| 423 lappend v $msg | |
| 424 } {1 {no such function: x}} | |
| 425 | |
| 426 # Try doing updates on a unique column where the value does not | |
| 427 # really change. | |
| 428 # | |
| 429 do_test update-10.1 { | |
| 430 execsql { | |
| 431 DROP TABLE test1; | |
| 432 CREATE TABLE t1( | |
| 433 a integer primary key, | |
| 434 b UNIQUE, | |
| 435 c, d, | |
| 436 e, f, | |
| 437 UNIQUE(c,d) | |
| 438 ); | |
| 439 INSERT INTO t1 VALUES(1,2,3,4,5,6); | |
| 440 INSERT INTO t1 VALUES(2,3,4,4,6,7); | |
| 441 SELECT * FROM t1 | |
| 442 } | |
| 443 } {1 2 3 4 5 6 2 3 4 4 6 7} | |
| 444 do_test update-10.2 { | |
| 445 catchsql { | |
| 446 UPDATE t1 SET a=1, e=9 WHERE f=6; | |
| 447 SELECT * FROM t1; | |
| 448 } | |
| 449 } {0 {1 2 3 4 9 6 2 3 4 4 6 7}} | |
| 450 do_test update-10.3 { | |
| 451 catchsql { | |
| 452 UPDATE t1 SET a=1, e=10 WHERE f=7; | |
| 453 SELECT * FROM t1; | |
| 454 } | |
| 455 } {1 {UNIQUE constraint failed: t1.a}} | |
| 456 do_test update-10.4 { | |
| 457 catchsql { | |
| 458 SELECT * FROM t1; | |
| 459 } | |
| 460 } {0 {1 2 3 4 9 6 2 3 4 4 6 7}} | |
| 461 do_test update-10.5 { | |
| 462 catchsql { | |
| 463 UPDATE t1 SET b=2, e=11 WHERE f=6; | |
| 464 SELECT * FROM t1; | |
| 465 } | |
| 466 } {0 {1 2 3 4 11 6 2 3 4 4 6 7}} | |
| 467 do_test update-10.6 { | |
| 468 catchsql { | |
| 469 UPDATE t1 SET b=2, e=12 WHERE f=7; | |
| 470 SELECT * FROM t1; | |
| 471 } | |
| 472 } {1 {UNIQUE constraint failed: t1.b}} | |
| 473 do_test update-10.7 { | |
| 474 catchsql { | |
| 475 SELECT * FROM t1; | |
| 476 } | |
| 477 } {0 {1 2 3 4 11 6 2 3 4 4 6 7}} | |
| 478 do_test update-10.8 { | |
| 479 catchsql { | |
| 480 UPDATE t1 SET c=3, d=4, e=13 WHERE f=6; | |
| 481 SELECT * FROM t1; | |
| 482 } | |
| 483 } {0 {1 2 3 4 13 6 2 3 4 4 6 7}} | |
| 484 do_test update-10.9 { | |
| 485 catchsql { | |
| 486 UPDATE t1 SET c=3, d=4, e=14 WHERE f=7; | |
| 487 SELECT * FROM t1; | |
| 488 } | |
| 489 } {1 {UNIQUE constraint failed: t1.c, t1.d}} | |
| 490 do_test update-10.10 { | |
| 491 catchsql { | |
| 492 SELECT * FROM t1; | |
| 493 } | |
| 494 } {0 {1 2 3 4 13 6 2 3 4 4 6 7}} | |
| 495 | |
| 496 # Make sure we can handle a subquery in the where clause. | |
| 497 # | |
| 498 ifcapable subquery { | |
| 499 do_test update-11.1 { | |
| 500 execsql { | |
| 501 UPDATE t1 SET e=e+1 WHERE b IN (SELECT b FROM t1); | |
| 502 SELECT b,e FROM t1; | |
| 503 } | |
| 504 } {2 14 3 7} | |
| 505 do_test update-11.2 { | |
| 506 execsql { | |
| 507 UPDATE t1 SET e=e+1 WHERE a IN (SELECT a FROM t1); | |
| 508 SELECT a,e FROM t1; | |
| 509 } | |
| 510 } {1 15 2 8} | |
| 511 } | |
| 512 | |
| 513 integrity_check update-12.1 | |
| 514 | |
| 515 # Ticket 602. Updates should occur in the same order as the records | |
| 516 # were discovered in the WHERE clause. | |
| 517 # | |
| 518 do_test update-13.1 { | |
| 519 execsql { | |
| 520 BEGIN; | |
| 521 CREATE TABLE t2(a); | |
| 522 INSERT INTO t2 VALUES(1); | |
| 523 INSERT INTO t2 VALUES(2); | |
| 524 INSERT INTO t2 SELECT a+2 FROM t2; | |
| 525 INSERT INTO t2 SELECT a+4 FROM t2; | |
| 526 INSERT INTO t2 SELECT a+8 FROM t2; | |
| 527 INSERT INTO t2 SELECT a+16 FROM t2; | |
| 528 INSERT INTO t2 SELECT a+32 FROM t2; | |
| 529 INSERT INTO t2 SELECT a+64 FROM t2; | |
| 530 INSERT INTO t2 SELECT a+128 FROM t2; | |
| 531 INSERT INTO t2 SELECT a+256 FROM t2; | |
| 532 INSERT INTO t2 SELECT a+512 FROM t2; | |
| 533 INSERT INTO t2 SELECT a+1024 FROM t2; | |
| 534 COMMIT; | |
| 535 SELECT count(*) FROM t2; | |
| 536 } | |
| 537 } {2048} | |
| 538 do_test update-13.2 { | |
| 539 execsql { | |
| 540 SELECT count(*) FROM t2 WHERE a=rowid; | |
| 541 } | |
| 542 } {2048} | |
| 543 do_test update-13.3 { | |
| 544 execsql { | |
| 545 UPDATE t2 SET rowid=rowid-1; | |
| 546 SELECT count(*) FROM t2 WHERE a=rowid+1; | |
| 547 } | |
| 548 } {2048} | |
| 549 do_test update-13.3 { | |
| 550 execsql { | |
| 551 UPDATE t2 SET rowid=rowid+10000; | |
| 552 UPDATE t2 SET rowid=rowid-9999; | |
| 553 SELECT count(*) FROM t2 WHERE a=rowid; | |
| 554 } | |
| 555 } {2048} | |
| 556 do_test update-13.4 { | |
| 557 execsql { | |
| 558 BEGIN; | |
| 559 INSERT INTO t2 SELECT a+2048 FROM t2; | |
| 560 INSERT INTO t2 SELECT a+4096 FROM t2; | |
| 561 INSERT INTO t2 SELECT a+8192 FROM t2; | |
| 562 SELECT count(*) FROM t2 WHERE a=rowid; | |
| 563 COMMIT; | |
| 564 } | |
| 565 } 16384 | |
| 566 do_test update-13.5 { | |
| 567 execsql { | |
| 568 UPDATE t2 SET rowid=rowid-1; | |
| 569 SELECT count(*) FROM t2 WHERE a=rowid+1; | |
| 570 } | |
| 571 } 16384 | |
| 572 | |
| 573 integrity_check update-13.6 | |
| 574 | |
| 575 ifcapable {trigger} { | |
| 576 # Test for proper detection of malformed WHEN clauses on UPDATE triggers. | |
| 577 # | |
| 578 do_test update-14.1 { | |
| 579 execsql { | |
| 580 CREATE TABLE t3(a,b,c); | |
| 581 CREATE TRIGGER t3r1 BEFORE UPDATE on t3 WHEN nosuchcol BEGIN | |
| 582 SELECT 'illegal WHEN clause'; | |
| 583 END; | |
| 584 } | |
| 585 } {} | |
| 586 do_test update-14.2 { | |
| 587 catchsql { | |
| 588 UPDATE t3 SET a=1; | |
| 589 } | |
| 590 } {1 {no such column: nosuchcol}} | |
| 591 do_test update-14.3 { | |
| 592 execsql { | |
| 593 CREATE TABLE t4(a,b,c); | |
| 594 CREATE TRIGGER t4r1 AFTER UPDATE on t4 WHEN nosuchcol BEGIN | |
| 595 SELECT 'illegal WHEN clause'; | |
| 596 END; | |
| 597 } | |
| 598 } {} | |
| 599 do_test update-14.4 { | |
| 600 catchsql { | |
| 601 UPDATE t4 SET a=1; | |
| 602 } | |
| 603 } {1 {no such column: nosuchcol}} | |
| 604 | |
| 605 } ;# ifcapable {trigger} | |
| 606 | |
| 607 # Ticket [https://www.sqlite.org/src/tktview/43107840f1c02] on 2014-10-29 | |
| 608 # An assertion fault on UPDATE | |
| 609 # | |
| 610 do_execsql_test update-15.1 { | |
| 611 CREATE TABLE t15(a INTEGER PRIMARY KEY, b); | |
| 612 INSERT INTO t15(a,b) VALUES(10,'abc'),(20,'def'),(30,'ghi'); | |
| 613 ALTER TABLE t15 ADD COLUMN c; | |
| 614 CREATE INDEX t15c ON t15(c); | |
| 615 INSERT INTO t15(a,b) | |
| 616 VALUES(5,'zyx'),(15,'wvu'),(25,'tsr'),(35,'qpo'); | |
| 617 UPDATE t15 SET c=printf("y%d",a) WHERE c IS NULL; | |
| 618 SELECT a,b,c,'|' FROM t15 ORDER BY a; | |
| 619 } {5 zyx y5 | 10 abc y10 | 15 wvu y15 | 20 def y20 | 25 tsr y25 | 30 ghi y30 | 3
5 qpo y35 |} | |
| 620 | |
| 621 | |
| 622 finish_test | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2363173002:
[sqlite] Remove obsolete reference version 3.8.7.4. (Closed)
