| 1 |
# 2020 April 29 |
| 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 upfrom2 |
| 16 |
|
| 17 |
# Test cases: |
| 18 |
# |
| 19 |
# 1.*: Test that triggers are fired correctly for UPDATE FROM statements, |
| 20 |
# and only once for each row. Except for INSTEAD OF triggers on |
| 21 |
# views - these are fired once for each row returned by the join, |
| 22 |
# including duplicates. |
| 23 |
# |
| 24 |
# 2.*: Test adding ORDER BY and LIMIT clauses with UPDATE FROM statements. |
| 25 |
# |
| 26 |
# 5.*: Test that specifying the target table name or alias in the FROM |
| 27 |
# clause of an UPDATE statement is an error. |
| 28 |
# |
| 29 |
|
| 30 |
foreach {tn wo} { |
| 31 |
1 "" |
| 32 |
2 "WITHOUT ROWID" |
| 33 |
} { |
| 34 |
reset_db |
| 35 |
|
| 36 |
eval [string map [list %WO% $wo %TN% $tn] { |
| 37 |
do_execsql_test 1.%TN%.0 { |
| 38 |
CREATE TABLE log(t TEXT); |
| 39 |
CREATE TABLE t1(x PRIMARY KEY, y, z UNIQUE) %WO%; |
| 40 |
CREATE INDEX t1y ON t1(y); |
| 41 |
|
| 42 |
INSERT INTO t1 VALUES(1, 'i', 'one'); |
| 43 |
INSERT INTO t1 VALUES(2, 'ii', 'two'); |
| 44 |
INSERT INTO t1 VALUES(3, 'iii', 'three'); |
| 45 |
INSERT INTO t1 VALUES(4, 'iv', 'four'); |
| 46 |
|
| 47 |
CREATE TRIGGER tr1 BEFORE UPDATE ON t1 BEGIN |
| 48 |
INSERT INTO log VALUES(old.z || '->' || new.z); |
| 49 |
END; |
| 50 |
CREATE TRIGGER tr2 AFTER UPDATE ON t1 BEGIN |
| 51 |
INSERT INTO log VALUES(old.y || '->' || new.y); |
| 52 |
END; |
| 53 |
} |
| 54 |
|
| 55 |
do_execsql_test 1.%TN%.1 { |
| 56 |
WITH data(k, v) AS ( |
| 57 |
VALUES(3, 'thirty'), (1, 'ten') |
| 58 |
) |
| 59 |
UPDATE t1 SET z=v FROM data WHERE x=k; |
| 60 |
|
| 61 |
SELECT * FROM t1; |
| 62 |
SELECT * FROM log; |
| 63 |
} { |
| 64 |
1 i ten 2 ii two 3 iii thirty 4 iv four |
| 65 |
one->ten i->i |
| 66 |
three->thirty iii->iii |
| 67 |
} |
| 68 |
|
| 69 |
do_execsql_test 1.%TN%.2 { |
| 70 |
CREATE TABLE t2(a, b); |
| 71 |
CREATE TABLE t3(k, v); |
| 72 |
|
| 73 |
INSERT INTO t3 VALUES(5, 'v'); |
| 74 |
INSERT INTO t3 VALUES(12, 'xii'); |
| 75 |
|
| 76 |
INSERT INTO t2 VALUES(2, 12); |
| 77 |
INSERT INTO t2 VALUES(3, 5); |
| 78 |
|
| 79 |
DELETE FROM log; |
| 80 |
UPDATE t1 SET y=v FROM t2, t3 WHERE t1.x=t2.a AND t3.k=t2.b; |
| 81 |
|
| 82 |
SELECT * FROM t1; |
| 83 |
SELECT * FROM log; |
| 84 |
} { |
| 85 |
1 i ten 2 xii two 3 v thirty 4 iv four |
| 86 |
two->two ii->xii |
| 87 |
thirty->thirty iii->v |
| 88 |
} |
| 89 |
|
| 90 |
do_execsql_test 1.%TN%.3 { |
| 91 |
DELETE FROM log; |
| 92 |
WITH data(k, v) AS ( |
| 93 |
VALUES(1, 'seven'), (1, 'eight'), (2, 'eleven'), (2, 'twelve') |
| 94 |
) |
| 95 |
UPDATE t1 SET z=v FROM data WHERE x=k; |
| 96 |
|
| 97 |
SELECT * FROM t1; |
| 98 |
SELECT * FROM log; |
| 99 |
} { |
| 100 |
1 i eight 2 xii twelve 3 v thirty 4 iv four |
| 101 |
ten->eight i->i |
| 102 |
two->twelve xii->xii |
| 103 |
} |
| 104 |
|
| 105 |
do_test 1.%TN%.4 { db changes } {2} |
| 106 |
|
| 107 |
do_execsql_test 1.%TN%.5 { |
| 108 |
CREATE VIEW v1 AS SELECT * FROM t1; |
| 109 |
CREATE TRIGGER v1tr INSTEAD OF UPDATE ON v1 BEGIN |
| 110 |
UPDATE t1 SET y=new.y, z=new.z WHERE x=new.x; |
| 111 |
END; |
| 112 |
|
| 113 |
DELETE FROM log; |
| 114 |
WITH data(k, v) AS ( |
| 115 |
VALUES(3, 'thirteen'), (3, 'fourteen'), (4, 'fifteen'), (4, 'sixteen') |
| 116 |
) |
| 117 |
UPDATE v1 SET z=v FROM data WHERE x=k; |
| 118 |
} |
| 119 |
|
| 120 |
do_execsql_test 1.%TN%.6 { |
| 121 |
SELECT * FROM v1; |
| 122 |
SELECT * FROM log; |
| 123 |
} { |
| 124 |
1 i eight 2 xii twelve 3 v fourteen 4 iv sixteen |
| 125 |
thirty->thirteen v->v |
| 126 |
thirteen->fourteen v->v |
| 127 |
four->fifteen iv->iv |
| 128 |
fifteen->sixteen iv->iv |
| 129 |
} |
| 130 |
|
| 131 |
#-------------------------------------------------------------- |
| 132 |
|
| 133 |
do_execsql_test 1.%TN%.7 { |
| 134 |
CREATE TABLE o1(w, x, y, z UNIQUE, PRIMARY KEY(w, x)) %WO%; |
| 135 |
CREATE INDEX o1y ON t1(y); |
| 136 |
|
| 137 |
INSERT INTO o1 VALUES(0, 0, 'i', 'one'); |
| 138 |
INSERT INTO o1 VALUES(0, 1, 'ii', 'two'); |
| 139 |
INSERT INTO o1 VALUES(1, 0, 'iii', 'three'); |
| 140 |
INSERT INTO o1 VALUES(1, 1, 'iv', 'four'); |
| 141 |
|
| 142 |
CREATE TRIGGER tro1 BEFORE UPDATE ON o1 BEGIN |
| 143 |
INSERT INTO log VALUES(old.z || '->' || new.z); |
| 144 |
END; |
| 145 |
CREATE TRIGGER tro2 AFTER UPDATE ON o1 BEGIN |
| 146 |
INSERT INTO log VALUES(old.y || '->' || new.y); |
| 147 |
END; |
| 148 |
} |
| 149 |
|
| 150 |
do_execsql_test 1.%TN%.8 { |
| 151 |
DELETE FROM log; |
| 152 |
WITH data(k, v) AS ( |
| 153 |
VALUES(3, 'thirty'), (1, 'ten') |
| 154 |
) |
| 155 |
UPDATE o1 SET z=v FROM data WHERE (1+x+w*2)=k; |
| 156 |
|
| 157 |
SELECT * FROM o1; |
| 158 |
SELECT * FROM log; |
| 159 |
} { |
| 160 |
0 0 i ten 0 1 ii two 1 0 iii thirty 1 1 iv four |
| 161 |
one->ten i->i |
| 162 |
three->thirty iii->iii |
| 163 |
} |
| 164 |
|
| 165 |
do_execsql_test 1.%TN%.9 { |
| 166 |
DELETE FROM log; |
| 167 |
UPDATE o1 SET y=v FROM t2, t3 WHERE (1+o1.w*2+o1.x)=t2.a AND t3.k=t2.b; |
| 168 |
|
| 169 |
SELECT * FROM o1; |
| 170 |
SELECT * FROM log; |
| 171 |
} { |
| 172 |
0 0 i ten 0 1 xii two 1 0 v thirty 1 1 iv four |
| 173 |
two->two ii->xii |
| 174 |
thirty->thirty iii->v |
| 175 |
} |
| 176 |
|
| 177 |
do_execsql_test 1.%TN%.10 { |
| 178 |
DELETE FROM log; |
| 179 |
WITH data(k, v) AS ( |
| 180 |
VALUES(1, 'seven'), (1, 'eight'), (2, 'eleven'), (2, 'twelve') |
| 181 |
) |
| 182 |
UPDATE o1 SET z=v FROM data WHERE (1+w*2+x)=k; |
| 183 |
|
| 184 |
SELECT * FROM o1; |
| 185 |
SELECT * FROM log; |
| 186 |
} { |
| 187 |
0 0 i eight 0 1 xii twelve 1 0 v thirty 1 1 iv four |
| 188 |
ten->eight i->i |
| 189 |
two->twelve xii->xii |
| 190 |
} |
| 191 |
|
| 192 |
do_test 1.%TN%.11 { db changes } {2} |
| 193 |
|
| 194 |
do_execsql_test 1.%TN%.12 { |
| 195 |
CREATE VIEW w1 AS SELECT * FROM o1; |
| 196 |
CREATE TRIGGER w1tr INSTEAD OF UPDATE ON w1 BEGIN |
| 197 |
UPDATE o1 SET y=new.y, z=new.z WHERE w=new.w AND x=new.x; |
| 198 |
END; |
| 199 |
|
| 200 |
DELETE FROM log; |
| 201 |
WITH data(k, v) AS ( |
| 202 |
VALUES(3, 'thirteen'), (3, 'fourteen'), (4, 'fifteen'), (4, 'sixteen') |
| 203 |
) |
| 204 |
UPDATE w1 SET z=v FROM data WHERE (1+w*2+x)=k; |
| 205 |
} |
| 206 |
|
| 207 |
do_execsql_test 1.%TN%.13 { |
| 208 |
SELECT * FROM w1; |
| 209 |
SELECT * FROM log; |
| 210 |
} { |
| 211 |
0 0 i eight 0 1 xii twelve 1 0 v fourteen 1 1 iv sixteen |
| 212 |
thirty->thirteen v->v |
| 213 |
thirteen->fourteen v->v |
| 214 |
four->fifteen iv->iv |
| 215 |
fifteen->sixteen iv->iv |
| 216 |
} |
| 217 |
|
| 218 |
}] |
| 219 |
} |
| 220 |
|
| 221 |
ifcapable update_delete_limit { |
| 222 |
foreach {tn wo} { |
| 223 |
1 "" |
| 224 |
2 "WITHOUT ROWID" |
| 225 |
} { |
| 226 |
reset_db |
| 227 |
|
| 228 |
eval [string map [list %WO% $wo %TN% $tn] { |
| 229 |
do_execsql_test 2.%TN%.1 { |
| 230 |
CREATE TABLE x1(a INTEGER PRIMARY KEY, b) %WO%; |
| 231 |
INSERT INTO x1 VALUES |
| 232 |
(1, 'one'), (2, 'two'), (3, 'three'), (4, 'four'), |
| 233 |
(5, 'five'), (6, 'six'), (7, 'seven'), (8, 'eight'); |
| 234 |
} |
| 235 |
|
| 236 |
do_execsql_test 2.%TN%.2 { |
| 237 |
CREATE TABLE data1(x, y); |
| 238 |
INSERT INTO data1 VALUES |
| 239 |
(1, 'eleven'), (1, 'twenty-one'), (2, 'twelve'), (2, 'twenty-two'), |
| 240 |
(3, 'thirteen'), (3, 'twenty-three'), (4, 'fourteen'), (4, 'twenty-four'); |
| 241 |
} |
| 242 |
|
| 243 |
do_execsql_test 2.%TN%.3 { |
| 244 |
UPDATE x1 SET b=y FROM data1 WHERE a=x ORDER BY a LIMIT 3; |
| 245 |
SELECT * FROM x1; |
| 246 |
} { |
| 247 |
1 eleven 2 twelve 3 thirteen 4 four 5 five 6 six 7 seven 8 eight |
| 248 |
} |
| 249 |
|
| 250 |
do_execsql_test 2.%TN%.4 { |
| 251 |
UPDATE x1 SET b=b||y FROM data1 WHERE a=x ORDER BY b LIMIT 3; |
| 252 |
SELECT * FROM x1; |
| 253 |
} { |
| 254 |
1 eleveneleven 2 twelve 3 thirteenthirteen 4 fourfourteen |
| 255 |
5 five 6 six 7 seven 8 eight |
| 256 |
} |
| 257 |
|
| 258 |
do_catchsql_test 2.%TN%.5 { |
| 259 |
UPDATE x1 SET b=b||b ORDER BY b; |
| 260 |
} {1 {ORDER BY without LIMIT on UPDATE}} |
| 261 |
do_catchsql_test 2.%TN%.6 { |
| 262 |
UPDATE x1 SET b=b||y FROM data1 WHERE a=x ORDER BY b; |
| 263 |
} {1 {ORDER BY without LIMIT on UPDATE}} |
| 264 |
|
| 265 |
#----------------------------------------------------------------------- |
| 266 |
|
| 267 |
do_execsql_test 2.%TN%.6 { |
| 268 |
DROP TABLE x1; |
| 269 |
CREATE TABLE x1(u, v, b, PRIMARY KEY(u, v)) %WO%; |
| 270 |
INSERT INTO x1 VALUES |
| 271 |
(0, 1, 'one'), (1, 0, 'two'), (1, 1, 'three'), (2, 0, 'four'), |
| 272 |
(2, 1, 'five'), (3, 0, 'six'), (3, 1, 'seven'), (4, 0, 'eight'); |
| 273 |
} |
| 274 |
|
| 275 |
do_execsql_test 2.%TN%.7 { |
| 276 |
UPDATE x1 SET b=y FROM data1 WHERE (u*2+v)=x ORDER BY u, v LIMIT 3; |
| 277 |
SELECT * FROM x1; |
| 278 |
} { |
| 279 |
0 1 eleven 1 0 twelve 1 1 thirteen 2 0 four |
| 280 |
2 1 five 3 0 six 3 1 seven 4 0 eight |
| 281 |
} |
| 282 |
|
| 283 |
do_execsql_test 2.%TN%.8 { |
| 284 |
UPDATE x1 SET b=b||y FROM data1 WHERE (u*2+v)=x ORDER BY b LIMIT 3; |
| 285 |
SELECT * FROM x1; |
| 286 |
} { |
| 287 |
0 1 eleveneleven 1 0 twelve 1 1 thirteenthirteen 2 0 fourfourteen |
| 288 |
2 1 five 3 0 six 3 1 seven 4 0 eight |
| 289 |
} |
| 290 |
|
| 291 |
|
| 292 |
}] |
| 293 |
}} |
| 294 |
|
| 295 |
reset_db |
| 296 |
do_execsql_test 3.0 { |
| 297 |
CREATE TABLE data(x, y, z); |
| 298 |
CREATE VIEW t1 AS SELECT * FROM data; |
| 299 |
CREATE TRIGGER t1_insert INSTEAD OF INSERT ON t1 BEGIN |
| 300 |
INSERT INTO data VALUES(new.x, new.y, new.z); |
| 301 |
END; |
| 302 |
CREATE TRIGGER t1_update INSTEAD OF UPDATE ON t1 BEGIN |
| 303 |
INSERT INTO log VALUES(old.z || '->' || new.z); |
| 304 |
END; |
| 305 |
|
| 306 |
CREATE TABLE log(t TEXT); |
| 307 |
|
| 308 |
INSERT INTO t1 VALUES(1, 'i', 'one'); |
| 309 |
INSERT INTO t1 VALUES(2, 'ii', 'two'); |
| 310 |
INSERT INTO t1 VALUES(3, 'iii', 'three'); |
| 311 |
INSERT INTO t1 VALUES(4, 'iv', 'four'); |
| 312 |
} |
| 313 |
|
| 314 |
do_execsql_test 3.1 { |
| 315 |
WITH input(k, v) AS ( |
| 316 |
VALUES(3, 'thirty'), (1, 'ten') |
| 317 |
) |
| 318 |
UPDATE t1 SET z=v FROM input WHERE x=k; |
| 319 |
} |
| 320 |
|
| 321 |
foreach {tn sql} { |
| 322 |
2 { |
| 323 |
CREATE TABLE x1(a INT PRIMARY KEY, b, c) WITHOUT ROWID; |
| 324 |
} |
| 325 |
1 { |
| 326 |
CREATE TABLE x1(a INTEGER PRIMARY KEY, b, c); |
| 327 |
} |
| 328 |
3 { |
| 329 |
CREATE TABLE x1(a INT PRIMARY KEY, b, c); |
| 330 |
} |
| 331 |
} { |
| 332 |
|
| 333 |
reset_db |
| 334 |
execsql $sql |
| 335 |
|
| 336 |
do_execsql_test 4.$tn.0 { |
| 337 |
INSERT INTO x1 VALUES(1, 1, 1); |
| 338 |
INSERT INTO x1 VALUES(2, 2, 2); |
| 339 |
INSERT INTO x1 VALUES(3, 3, 3); |
| 340 |
INSERT INTO x1 VALUES(4, 4, 4); |
| 341 |
INSERT INTO x1 VALUES(5, 5, 5); |
| 342 |
CREATE TABLE map(o, t); |
| 343 |
INSERT INTO map VALUES(3, 30), (4, 40), (1, 10); |
| 344 |
} |
| 345 |
|
| 346 |
do_execsql_test 4.$tn.1 { |
| 347 |
UPDATE x1 SET a=t FROM map WHERE a=o; |
| 348 |
SELECT * FROM x1 ORDER BY a; |
| 349 |
} {2 2 2 5 5 5 10 1 1 30 3 3 40 4 4} |
| 350 |
} |
| 351 |
|
| 352 |
reset_db |
| 353 |
do_execsql_test 5.0 { |
| 354 |
CREATE TABLE x1(a, b, c); |
| 355 |
CREATE TABLE x2(a, b, c); |
| 356 |
} |
| 357 |
|
| 358 |
foreach {tn update nm} { |
| 359 |
1 "UPDATE x1 SET a=5 FROM x1" x1 |
| 360 |
2 "UPDATE x1 AS grapes SET a=5 FROM x1 AS grapes" grapes |
| 361 |
3 "UPDATE x1 SET a=5 FROM x2, x1" x1 |
| 362 |
4 "UPDATE x1 AS grapes SET a=5 FROM x2, x1 AS grapes" grapes |
| 363 |
} { |
| 364 |
do_catchsql_test 5.$tn $update \ |
| 365 |
"1 {target object/alias may not appear in FROM clause: $nm}" |
| 366 |
} |
| 367 |
|
| 368 |
#-------------------------------------------------------------------------- |
| 369 |
reset_db |
| 370 |
do_execsql_test 6.0 { |
| 371 |
CREATE TABLE t1(a); |
| 372 |
} |
| 373 |
|
| 374 |
do_execsql_test 6.1 { |
| 375 |
UPDATE t1 SET a = 1 FROM ( |
| 376 |
SELECT * FROM t1 |
| 377 |
) |
| 378 |
} {} |
| 379 |
do_execsql_test 6.2 { |
| 380 |
UPDATE t1 SET a = 1 FROM ( |
| 381 |
SELECT * FROM t1 UNION ALL SELECT * FROM t1 |
| 382 |
) |
| 383 |
} {} |
| 384 |
|
| 385 |
# 2022-03-21 |
| 386 |
# https://sqlite.org/forum/forumpost/929168fdd6 |
| 387 |
# |
| 388 |
reset_db |
| 389 |
do_execsql_test 7.0 { |
| 390 |
CREATE TABLE t1(a); |
| 391 |
INSERT INTO t1(a) VALUES(11),(22),(33),(44),(55); |
| 392 |
CREATE VIEW t2(b,c) AS SELECT a, COUNT(*) OVER () FROM t1; |
| 393 |
CREATE TABLE t3(x,y); |
| 394 |
CREATE TRIGGER t2r1 INSTEAD OF UPDATE ON t2 BEGIN |
| 395 |
INSERT INTO t3(x,y) VALUES(new.b,new.c); |
| 396 |
END; |
| 397 |
SELECT * FROM t2; |
| 398 |
} {11 5 22 5 33 5 44 5 55 5} |
| 399 |
do_execsql_test 7.1 { |
| 400 |
UPDATE t2 SET c=t1.a FROM t1 WHERE t2.b=t1.a; |
| 401 |
SELECT * FROM t3; |
| 402 |
} {11 11 22 22 33 33 44 44 55 55} |
| 403 |
|
| 404 |
|
| 405 |
finish_test |