gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/update2.test (about) 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