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