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 13set testdir [file dirname $argv0] 14source $testdir/tester.tcl 15set testprefix update2 16 17db 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# 25set nrow [expr 10] 26do_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 34do_execsql_test 1.1.1 { 35 UPDATE t1 SET b = repeat(b, 100) 36} 37 38do_execsql_test 1.1.2 { 39 SELECT * FROM t1; 40} [db eval { SELECT a, repeat(b, 100) FROM t2 }] 41 42do_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) } 50do_execsql_test 1.2.1 { 51 UPDATE t1 SET b = repeat(b, 100) 52} 53 54do_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# 63do_execsql_test 2.1 { 64 CREATE TABLE t3(a PRIMARY KEY, b, c); 65 CREATE INDEX t3i ON t3(b); 66} {} 67do_execsql_test 2.2 { UPDATE t3 SET c=1 WHERE b=? } {} 68do_execsql_test 2.3 { UPDATE t3 SET c=1 WHERE rowid=? } {} 69 70#------------------------------------------------------------------------- 71# 72do_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 79do_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# 86foreach {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# 181do_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 189do_execsql_test 5.1.1 { 190 UPDATE x1 SET c=c+1 WHERE b='a'; 191} 192 193do_execsql_test 5.1.2 { 194 SELECT * FROM x1; 195} {1 a 2 2 a 3 3 a 4} 196 197do_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#------------------------------------------------------------------------- 204do_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 211do_execsql_test 6.1 { 212 UPDATE d1 SET a = a+2 WHERE a>0 OR b>0; 213} 214 215do_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# 222do_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 |} 230do_execsql_test 7.110 { 231 UPDATE OR REPLACE t1 SET x=1; 232 SELECT quote(x), quote(y), '|' FROM t1; 233} {1 NULL |} 234 235finish_test 236