Annotation of embedaddon/sqlite3/test/update.test, revision 1.1
1.1 ! misho 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 {PRIMARY KEY must be unique}}
! 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 {column b is not unique}}
! 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 {columns c, d are not unique}}
! 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:
! 608: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to update.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test