test/update2.test

   1 # 2017 January 9
   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 #
  12
  13 set testdir [file dirname $argv0]
  14 source $testdir/tester.tcl
  15 set testprefix update2
  16
  17 db func repeat [list string repeat]
  18
  19 #-------------------------------------------------------------------------
  20 # 1.1.* A one-pass UPDATE that does balance() operations on the IPK index
  21 #       that it is scanning.
  22 #
  23 # 1.2.* Same again, but with a WITHOUT ROWID table.
  24 #
  25 set nrow [expr 10]
  26 do_execsql_test 1.1.0 {
  27   CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
  28   CREATE TABLE t2(a INTEGER PRIMARY KEY, b);
  29   WITH s(i) AS ( SELECT 0 UNION ALL SELECT i+1 FROM s WHERE i<$nrow )
  30   INSERT INTO t1(b) SELECT char((i % 26) + 65) FROM s;
  31   INSERT INTO t2 SELECT * FROM t1;
  32 }
  33
  34 do_execsql_test 1.1.1 {
  35   UPDATE t1 SET b = repeat(b, 100)
  36 }
  37
  38 do_execsql_test 1.1.2 {
  39   SELECT * FROM t1;
  40 } [db eval { SELECT a, repeat(b, 100) FROM t2 }]
  41
  42 do_execsql_test 1.2.0 {
  43   DROP TABLE t1;
  44   CREATE TABLE t1(a INT PRIMARY KEY, b) WITHOUT ROWID;
  45   WITH s(i) AS ( SELECT 0 UNION ALL SELECT i+1 FROM s WHERE i<$nrow )
  46   INSERT INTO t1(a, b) SELECT i+1, char((i % 26) + 65) FROM s;
  47 }
  48
  49 #explain_i { UPDATE t1 SET b = repeat(b, 100) }
  50 do_execsql_test 1.2.1 {
  51   UPDATE t1 SET b = repeat(b, 100)
  52 }
  53
  54 do_execsql_test 1.2.2 {
  55   SELECT * FROM t1;
  56 } [db eval { SELECT a, repeat(b, 100) FROM t2 }]
  57
  58
  59 #-------------------------------------------------------------------------
  60 # A one-pass UPDATE that does balance() operations on the IPK index
  61 # that it is scanning.
  62 #
  63 do_execsql_test 2.1 {
  64   CREATE TABLE t3(a PRIMARY KEY, b, c);
  65   CREATE INDEX t3i ON t3(b);
  66 } {}
  67 do_execsql_test 2.2 { UPDATE t3 SET c=1 WHERE b=?      } {}
  68 do_execsql_test 2.3 { UPDATE t3 SET c=1 WHERE rowid=?  } {}
  69
  70 #-------------------------------------------------------------------------
  71 #
  72 do_execsql_test 3.0 {
  73   CREATE TABLE t4(a PRIMARY KEY, b, c) WITHOUT ROWID;
  74   CREATE INDEX t4c ON t4(c);
  75   INSERT INTO t4 VALUES(1, 2, 3);
  76   INSERT INTO t4 VALUES(2, 3, 4);
  77 }
  78
  79 do_execsql_test 3.1 {
  80   UPDATE t4 SET c=c+2 WHERE c>2;
  81   SELECT a, c FROM t4 ORDER BY a;
  82 } {1 5 2 6}
  83
  84 #-------------------------------------------------------------------------
  85 #
  86 foreach {tn sql} {
  87   1 {
  88     CREATE TABLE b1(a INTEGER PRIMARY KEY, b, c);
  89     CREATE TABLE c1(a INTEGER PRIMARY KEY, b, c, d)
  90   }
  91   2 {
  92     CREATE TABLE b1(a INT PRIMARY KEY, b, c) WITHOUT ROWID;
  93     CREATE TABLE c1(a INT PRIMARY KEY, b, c, d) WITHOUT ROWID;
  94   }
  95 } {
  96   execsql { DROP TABLE IF EXISTS b1; DROP TABLE IF EXISTS c1; }
  97   execsql $sql
  98
  99   do_execsql_test 4.$tn.0 {
 100     CREATE UNIQUE INDEX b1c ON b1(c);
 101     INSERT INTO b1 VALUES(1, 'a', 1);
 102     INSERT INTO b1 VALUES(2, 'b', 15);
 103     INSERT INTO b1 VALUES(3, 'c', 3);
 104     INSERT INTO b1 VALUES(4, 'd', 4);
 105     INSERT INTO b1 VALUES(5, 'e', 5);
 106     INSERT INTO b1 VALUES(6, 'f', 6);
 107     INSERT INTO b1 VALUES(7, 'g', 7);
 108   }
 109
 110   do_execsql_test 4.$tn.1 {
 111     UPDATE OR REPLACE b1 SET c=c+10 WHERE a BETWEEN 4 AND 7;
 112     SELECT * FROM b1 ORDER BY a;
 113   } {
 114     1 a 1
 115     3 c 3
 116     4 d 14
 117     5 e 15
 118     6 f 16
 119     7 g 17
 120   }
 121
 122   do_execsql_test 4.$tn.2 {
 123     CREATE INDEX c1d ON c1(d, b);
 124     CREATE UNIQUE INDEX c1c ON c1(c, b);
 125
 126     INSERT INTO c1 VALUES(1, 'a', 1,  1);
 127     INSERT INTO c1 VALUES(2, 'a', 15, 2);
 128     INSERT INTO c1 VALUES(3, 'a', 3,  3);
 129     INSERT INTO c1 VALUES(4, 'a', 4,  4);
 130     INSERT INTO c1 VALUES(5, 'a', 5,  5);
 131     INSERT INTO c1 VALUES(6, 'a', 6,  6);
 132     INSERT INTO c1 VALUES(7, 'a', 7,  7);
 133   }
 134
 135   do_execsql_test 4.$tn.3 {
 136     UPDATE OR REPLACE c1 SET c=c+10 WHERE d BETWEEN 4 AND 7;
 137     SELECT * FROM c1 ORDER BY a;
 138   } {
 139     1 a 1 1
 140     3 a 3 3
 141     4 a 14 4
 142     5 a 15 5
 143     6 a 16 6
 144     7 a 17 7
 145   }
 146
 147   do_execsql_test 4.$tn.4 { PRAGMA integrity_check } ok
 148
 149   do_execsql_test 4.$tn.5 {
 150     DROP INDEX c1d;
 151     DROP INDEX c1c;
 152     DELETE FROM c1;
 153
 154     INSERT INTO c1 VALUES(1, 'a', 1,  1);
 155     INSERT INTO c1 VALUES(2, 'a', 15, 2);
 156     INSERT INTO c1 VALUES(3, 'a', 3,  3);
 157     INSERT INTO c1 VALUES(4, 'a', 4,  4);
 158     INSERT INTO c1 VALUES(5, 'a', 5,  5);
 159     INSERT INTO c1 VALUES(6, 'a', 6,  6);
 160     INSERT INTO c1 VALUES(7, 'a', 7,  7);
 161
 162     CREATE INDEX c1d ON c1(d);
 163     CREATE UNIQUE INDEX c1c ON c1(c);
 164   }
 165
 166   do_execsql_test 4.$tn.6 {
 167     UPDATE OR REPLACE c1 SET c=c+10 WHERE d BETWEEN 4 AND 7;
 168     SELECT * FROM c1 ORDER BY a;
 169   } {
 170     1 a 1 1
 171     3 a 3 3
 172     4 a 14 4
 173     5 a 15 5
 174     6 a 16 6
 175     7 a 17 7
 176   }
 177 }
 178
 179 #-------------------------------------------------------------------------
 180 #
 181 do_execsql_test 5.0 {
 182   CREATE TABLE x1(a INTEGER PRIMARY KEY, b, c);
 183   CREATE INDEX x1c ON x1(b, c);
 184   INSERT INTO x1 VALUES(1, 'a', 1);
 185   INSERT INTO x1 VALUES(2, 'a', 2);
 186   INSERT INTO x1 VALUES(3, 'a', 3);
 187 }
 188
 189 do_execsql_test 5.1.1 {
 190   UPDATE x1 SET c=c+1 WHERE b='a';
 191 }
 192
 193 do_execsql_test 5.1.2 {
 194   SELECT * FROM x1;
 195 } {1 a 2 2 a 3 3 a 4}
 196
 197 do_test 5.2 {
 198   catch { array unset A }
 199   db eval { EXPLAIN UPDATE x1 SET c=c+1 WHERE b='a' } { incr A($opcode) }
 200   set A(NotExists)
 201 } {1}
 202
 203 #-------------------------------------------------------------------------
 204 do_execsql_test 6.0 {
 205   CREATE TABLE d1(a,b);
 206   CREATE INDEX d1b ON d1(a);
 207   CREATE INDEX d1c ON d1(b);
 208   INSERT INTO d1 VALUES(1,2);
 209 }
 210
 211 do_execsql_test 6.1 {
 212   UPDATE d1 SET a = a+2 WHERE a>0 OR b>0;
 213 }
 214
 215 do_execsql_test 6.2 {
 216   SELECT * FROM d1;
 217 } {3 2}
 218
 219 # 2019-01-22 Bug in UPDATE OR REPLACE discovered by the
 220 # Matt Denton's LPM fuzzer
 221 #
 222 do_execsql_test 7.100 {
 223   DROP TABLE IF EXISTS t1;
 224   CREATE TABLE t1(x,y);
 225   CREATE UNIQUE INDEX t1x1 ON t1(x) WHERE x IS NOT NULL;
 226   INSERT INTO t1(x) VALUES(NULL),(NULL);
 227   CREATE INDEX t1x2 ON t1(y);
 228   SELECT quote(x), quote(y), '|' FROM t1;
 229 } {NULL NULL | NULL NULL |}
 230 do_execsql_test 7.110 {
 231   UPDATE OR REPLACE t1 SET x=1;
 232   SELECT quote(x), quote(y), '|' FROM t1;
 233 } {1 NULL |}
 234
 235 finish_test