Symaptic: os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/update.test@bde4ae8d615e

     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 $

    16 set testdir [file dirname $argv0]

    17 source $testdir/tester.tcl

    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}}

    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}}

    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}

    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}}

    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}

    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}

    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}

    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}

   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}

   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}

   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}

   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}

   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}}

   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}}

   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 }

   513 integrity_check update-12.1

   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

   573 integrity_check update-13.6

   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}}

   605 } ;# ifcapable {trigger}

   608 finish_test

author	sl@SLION-WIN7.fritz.box
	Fri, 15 Jun 2012 03:10:57 +0200
changeset 0	bde4ae8d615e
permissions	-rw-r--r--