gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/insert.test (about) 1 # 2001-09-15 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 # This file implements regression tests for SQLite library. The 12 # focus of this file is testing the INSERT statement. 13 # 14 15 set testdir [file dirname $argv0] 16 source $testdir/tester.tcl 17 18 # Try to insert into a non-existant table. 19 # 20 do_test insert-1.1 { 21 set v [catch {execsql {INSERT INTO test1 VALUES(1,2,3)}} msg] 22 lappend v $msg 23 } {1 {no such table: test1}} 24 25 # Try to insert into sqlite_master 26 # 27 do_test insert-1.2 { 28 set v [catch {execsql {INSERT INTO sqlite_master VALUES(1,2,3,4)}} msg] 29 lappend v $msg 30 } {1 {table sqlite_master may not be modified}} 31 32 # Try to insert the wrong number of entries. 33 # 34 do_test insert-1.3 { 35 execsql {CREATE TABLE test1(one int, two int, three int)} 36 set v [catch {execsql {INSERT INTO test1 VALUES(1,2)}} msg] 37 lappend v $msg 38 } {1 {table test1 has 3 columns but 2 values were supplied}} 39 do_test insert-1.3b { 40 set v [catch {execsql {INSERT INTO test1 VALUES(1,2,3,4)}} msg] 41 lappend v $msg 42 } {1 {table test1 has 3 columns but 4 values were supplied}} 43 do_test insert-1.3c { 44 set v [catch {execsql {INSERT INTO test1(one,two) VALUES(1,2,3,4)}} msg] 45 lappend v $msg 46 } {1 {4 values for 2 columns}} 47 do_test insert-1.3d { 48 set v [catch {execsql {INSERT INTO test1(one,two) VALUES(1)}} msg] 49 lappend v $msg 50 } {1 {1 values for 2 columns}} 51 52 # Try to insert into a non-existant column of a table. 53 # 54 do_test insert-1.4 { 55 set v [catch {execsql {INSERT INTO test1(one,four) VALUES(1,2)}} msg] 56 lappend v $msg 57 } {1 {table test1 has no column named four}} 58 59 # Make sure the inserts actually happen 60 # 61 do_test insert-1.5 { 62 execsql {INSERT INTO test1 VALUES(1,2,3)} 63 execsql {SELECT * FROM test1} 64 } {1 2 3} 65 do_test insert-1.5b { 66 execsql {INSERT INTO test1 VALUES(4,5,6)} 67 execsql {SELECT * FROM test1 ORDER BY one} 68 } {1 2 3 4 5 6} 69 do_test insert-1.5c { 70 execsql {INSERT INTO test1 VALUES(7,8,9)} 71 execsql {SELECT * FROM test1 ORDER BY one} 72 } {1 2 3 4 5 6 7 8 9} 73 74 do_test insert-1.6 { 75 execsql {DELETE FROM test1} 76 execsql {INSERT INTO test1(one,two) VALUES(1,2)} 77 execsql {SELECT * FROM test1 ORDER BY one} 78 } {1 2 {}} 79 do_test insert-1.6b { 80 execsql {INSERT INTO test1(two,three) VALUES(5,6)} 81 execsql {SELECT * FROM test1 ORDER BY one} 82 } {{} 5 6 1 2 {}} 83 do_test insert-1.6c { 84 execsql {INSERT INTO test1(three,one) VALUES(7,8)} 85 execsql {SELECT * FROM test1 ORDER BY one} 86 } {{} 5 6 1 2 {} 8 {} 7} 87 88 # A table to use for testing default values 89 # 90 do_test insert-2.1 { 91 execsql { 92 CREATE TABLE test2( 93 f1 int default -111, 94 f2 real default +4.32, 95 f3 int default +222, 96 f4 int default 7.89 97 ) 98 } 99 execsql {SELECT * from test2} 100 } {} 101 do_test insert-2.2 { 102 execsql {INSERT INTO test2(f1,f3) VALUES(+10,-10)} 103 execsql {SELECT * FROM test2} 104 } {10 4.32 -10 7.89} 105 do_test insert-2.3 { 106 execsql {INSERT INTO test2(f2,f4) VALUES(1.23,-3.45)} 107 execsql {SELECT * FROM test2 WHERE f1==-111} 108 } {-111 1.23 222 -3.45} 109 do_test insert-2.4 { 110 execsql {INSERT INTO test2(f1,f2,f4) VALUES(77,+1.23,3.45)} 111 execsql {SELECT * FROM test2 WHERE f1==77} 112 } {77 1.23 222 3.45} 113 do_test insert-2.10 { 114 execsql { 115 DROP TABLE test2; 116 CREATE TABLE test2( 117 f1 int default 111, 118 f2 real default -4.32, 119 f3 text default hi, 120 f4 text default 'abc-123', 121 f5 varchar(10) 122 ) 123 } 124 execsql {SELECT * from test2} 125 } {} 126 do_test insert-2.11 { 127 execsql {INSERT INTO test2(f2,f4) VALUES(-2.22,'hi!')} 128 execsql {SELECT * FROM test2} 129 } {111 -2.22 hi hi! {}} 130 do_test insert-2.12 { 131 execsql {INSERT INTO test2(f1,f5) VALUES(1,'xyzzy')} 132 execsql {SELECT * FROM test2 ORDER BY f1} 133 } {1 -4.32 hi abc-123 xyzzy 111 -2.22 hi hi! {}} 134 135 # Do additional inserts with default values, but this time 136 # on a table that has indices. In particular we want to verify 137 # that the correct default values are inserted into the indices. 138 # 139 do_test insert-3.1 { 140 execsql { 141 DELETE FROM test2; 142 CREATE INDEX index9 ON test2(f1,f2); 143 CREATE INDEX indext ON test2(f4,f5); 144 SELECT * from test2; 145 } 146 } {} 147 148 # Update for sqlite3 v3: 149 # Change the 111 to '111' in the following two test cases, because 150 # the default value is being inserted as a string. TODO: It shouldn't be. 151 do_test insert-3.2 { 152 execsql {INSERT INTO test2(f2,f4) VALUES(-3.33,'hum')} 153 execsql {SELECT * FROM test2 WHERE f1='111' AND f2=-3.33} 154 } {111 -3.33 hi hum {}} 155 do_test insert-3.3 { 156 execsql {INSERT INTO test2(f1,f2,f5) VALUES(22,-4.44,'wham')} 157 execsql {SELECT * FROM test2 WHERE f1='111' AND f2=-3.33} 158 } {111 -3.33 hi hum {}} 159 do_test insert-3.4 { 160 execsql {SELECT * FROM test2 WHERE f1=22 AND f2=-4.44} 161 } {22 -4.44 hi abc-123 wham} 162 ifcapable {reindex} { 163 do_test insert-3.5 { 164 execsql REINDEX 165 } {} 166 } 167 integrity_check insert-3.5 168 169 # Test of expressions in the VALUES clause 170 # 171 do_test insert-4.1 { 172 execsql { 173 CREATE TABLE t3(a,b,c); 174 INSERT INTO t3 VALUES(1+2+3,4,5); 175 SELECT * FROM t3; 176 } 177 } {6 4 5} 178 do_test insert-4.2 { 179 ifcapable subquery { 180 execsql {INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1,5,6);} 181 } else { 182 set maxa [execsql {SELECT max(a) FROM t3}] 183 execsql "INSERT INTO t3 VALUES($maxa+1,5,6);" 184 } 185 execsql { 186 SELECT * FROM t3 ORDER BY a; 187 } 188 } {6 4 5 7 5 6} 189 ifcapable subquery { 190 do_test insert-4.3 { 191 catchsql { 192 INSERT INTO t3 VALUES((SELECT max(a) FROM t3)+1,t3.a,6); 193 SELECT * FROM t3 ORDER BY a; 194 } 195 } {1 {no such column: t3.a}} 196 } 197 do_test insert-4.4 { 198 ifcapable subquery { 199 execsql {INSERT INTO t3 VALUES((SELECT b FROM t3 WHERE a=0),6,7);} 200 } else { 201 set b [execsql {SELECT b FROM t3 WHERE a = 0}] 202 if {$b==""} {set b NULL} 203 execsql "INSERT INTO t3 VALUES($b,6,7);" 204 } 205 execsql { 206 SELECT * FROM t3 ORDER BY a; 207 } 208 } {{} 6 7 6 4 5 7 5 6} 209 do_test insert-4.5 { 210 execsql { 211 SELECT b,c FROM t3 WHERE a IS NULL; 212 } 213 } {6 7} 214 do_test insert-4.6 { 215 catchsql { 216 INSERT INTO t3 VALUES(notafunc(2,3),2,3); 217 } 218 } {1 {no such function: notafunc}} 219 do_test insert-4.7 { 220 execsql { 221 INSERT INTO t3 VALUES(min(1,2,3),max(1,2,3),99); 222 SELECT * FROM t3 WHERE c=99; 223 } 224 } {1 3 99} 225 226 # Test the ability to insert from a temporary table into itself. 227 # Ticket #275. 228 # 229 ifcapable tempdb { 230 do_test insert-5.1 { 231 execsql { 232 CREATE TEMP TABLE t4(x); 233 INSERT INTO t4 VALUES(1); 234 SELECT * FROM t4; 235 } 236 } {1} 237 do_test insert-5.2 { 238 execsql { 239 INSERT INTO t4 SELECT x+1 FROM t4; 240 SELECT * FROM t4; 241 } 242 } {1 2} 243 ifcapable {explain} { 244 do_test insert-5.3 { 245 # verify that a temporary table is used to copy t4 to t4 246 set x [execsql { 247 EXPLAIN INSERT INTO t4 SELECT x+2 FROM t4; 248 }] 249 expr {[lsearch $x OpenEphemeral]>0} 250 } {1} 251 } 252 253 do_test insert-5.4 { 254 # Verify that table "test1" begins on page 3. This should be the same 255 # page number used by "t4" above. 256 # 257 # Update for v3 - the first table now begins on page 2 of each file, not 3. 258 execsql { 259 SELECT rootpage FROM sqlite_master WHERE name='test1'; 260 } 261 } [expr $AUTOVACUUM?3:2] 262 do_test insert-5.5 { 263 # Verify that "t4" begins on page 3. 264 # 265 # Update for v3 - the first table now begins on page 2 of each file, not 3. 266 execsql { 267 SELECT rootpage FROM sqlite_temp_master WHERE name='t4'; 268 } 269 } {2} 270 do_test insert-5.6 { 271 # This should not use an intermediate temporary table. 272 execsql { 273 INSERT INTO t4 SELECT one FROM test1 WHERE three=7; 274 SELECT * FROM t4 275 } 276 } {1 2 8} 277 ifcapable {explain} { 278 do_test insert-5.7 { 279 # verify that no temporary table is used to copy test1 to t4 280 set x [execsql { 281 EXPLAIN INSERT INTO t4 SELECT one FROM test1; 282 }] 283 expr {[lsearch $x OpenTemp]>0} 284 } {0} 285 } 286 } 287 288 # Ticket #334: REPLACE statement corrupting indices. 289 # 290 ifcapable conflict { 291 # The REPLACE command is not available if SQLITE_OMIT_CONFLICT is 292 # defined at compilation time. 293 do_test insert-6.1 { 294 execsql { 295 CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE); 296 INSERT INTO t1 VALUES(1,2); 297 INSERT INTO t1 VALUES(2,3); 298 SELECT b FROM t1 WHERE b=2; 299 } 300 } {2} 301 do_test insert-6.2 { 302 execsql { 303 REPLACE INTO t1 VALUES(1,4); 304 SELECT b FROM t1 WHERE b=2; 305 } 306 } {} 307 do_test insert-6.3 { 308 execsql { 309 UPDATE OR REPLACE t1 SET a=2 WHERE b=4; 310 SELECT * FROM t1 WHERE b=4; 311 } 312 } {2 4} 313 do_test insert-6.4 { 314 execsql { 315 SELECT * FROM t1 WHERE b=3; 316 } 317 } {} 318 ifcapable {reindex} { 319 do_test insert-6.5 { 320 execsql REINDEX 321 } {} 322 } 323 do_test insert-6.6 { 324 execsql { 325 DROP TABLE t1; 326 } 327 } {} 328 } 329 330 # Test that the special optimization for queries of the form 331 # "SELECT max(x) FROM tbl" where there is an index on tbl(x) works with 332 # INSERT statments. 333 do_test insert-7.1 { 334 execsql { 335 CREATE TABLE t1(a); 336 INSERT INTO t1 VALUES(1); 337 INSERT INTO t1 VALUES(2); 338 CREATE INDEX i1 ON t1(a); 339 } 340 } {} 341 do_test insert-7.2 { 342 execsql { 343 INSERT INTO t1 SELECT max(a) FROM t1; 344 } 345 } {} 346 do_test insert-7.3 { 347 execsql { 348 SELECT a FROM t1; 349 } 350 } {1 2 2} 351 352 # Ticket #1140: Check for an infinite loop in the algorithm that tests 353 # to see if the right-hand side of an INSERT...SELECT references the left-hand 354 # side. 355 # 356 ifcapable subquery&&compound { 357 do_test insert-8.1 { 358 execsql { 359 INSERT INTO t3 SELECT * FROM (SELECT * FROM t3 UNION ALL SELECT 1,2,3) 360 } 361 } {} 362 } 363 364 # Make sure the rowid cache in the VDBE is reset correctly when 365 # an explicit rowid is given. 366 # 367 do_test insert-9.1 { 368 execsql { 369 CREATE TABLE t5(x); 370 INSERT INTO t5 VALUES(1); 371 INSERT INTO t5 VALUES(2); 372 INSERT INTO t5 VALUES(3); 373 INSERT INTO t5(rowid, x) SELECT nullif(x*2+10,14), x+100 FROM t5; 374 SELECT rowid, x FROM t5; 375 } 376 } {1 1 2 2 3 3 12 101 13 102 16 103} 377 do_test insert-9.2 { 378 execsql { 379 CREATE TABLE t6(x INTEGER PRIMARY KEY, y); 380 INSERT INTO t6 VALUES(1,1); 381 INSERT INTO t6 VALUES(2,2); 382 INSERT INTO t6 VALUES(3,3); 383 INSERT INTO t6 SELECT nullif(y*2+10,14), y+100 FROM t6; 384 SELECT x, y FROM t6; 385 } 386 } {1 1 2 2 3 3 12 101 13 102 16 103} 387 388 # Multiple VALUES clauses 389 # 390 ifcapable compound { 391 do_test insert-10.1 { 392 execsql { 393 CREATE TABLE t10(a,b,c); 394 INSERT INTO t10 VALUES(1,2,3), (4,5,6), (7,8,9); 395 SELECT * FROM t10; 396 } 397 } {1 2 3 4 5 6 7 8 9} 398 do_test insert-10.2 { 399 catchsql { 400 INSERT INTO t10 VALUES(11,12,13), (14,15), (16,17,28); 401 } 402 } {1 {all VALUES must have the same number of terms}} 403 } 404 405 # Need for the OP_SoftNull opcode 406 # 407 do_execsql_test insert-11.1 { 408 CREATE TABLE t11a AS SELECT '123456789' AS x; 409 CREATE TABLE t11b (a INTEGER PRIMARY KEY, b, c); 410 INSERT INTO t11b SELECT x, x, x FROM t11a; 411 SELECT quote(a), quote(b), quote(c) FROM t11b; 412 } {123456789 '123456789' '123456789'} 413 414 415 # More columns of input than there are columns in the table. 416 # Ticket http://www.sqlite.org/src/info/e9654505cfda9361 417 # 418 do_execsql_test insert-12.1 { 419 CREATE TABLE t12a(a,b,c,d,e,f,g); 420 INSERT INTO t12a VALUES(101,102,103,104,105,106,107); 421 CREATE TABLE t12b(x); 422 INSERT INTO t12b(x,rowid,x,x,x,x,x) SELECT * FROM t12a; 423 SELECT rowid, x FROM t12b; 424 } {102 101} 425 do_execsql_test insert-12.2 { 426 CREATE TABLE tab1( value INTEGER); 427 INSERT INTO tab1 (value, _rowid_) values( 11, 1); 428 INSERT INTO tab1 (value, _rowid_) SELECT 22,999; 429 SELECT * FROM tab1; 430 } {11 22} 431 do_execsql_test insert-12.3 { 432 CREATE TABLE t12c(a, b DEFAULT 'xyzzy', c); 433 INSERT INTO t12c(a, rowid, c) SELECT 'one', 999, 'two'; 434 SELECT * FROM t12c; 435 } {one xyzzy two} 436 437 # 2018-06-11. From OSSFuzz. A column cache malfunction in 438 # the constraint checking on an index of expressions causes 439 # an assertion fault in a REPLACE. Ticket 440 # https://www.sqlite.org/src/info/c2432ef9089ee73b 441 # 442 do_execsql_test insert-13.1 { 443 DROP TABLE IF EXISTS t13; 444 CREATE TABLE t13(a INTEGER PRIMARY KEY,b UNIQUE); 445 CREATE INDEX t13x1 ON t13(-b=b); 446 INSERT INTO t13 VALUES(1,5),(6,2); 447 REPLACE INTO t13 SELECT b,0 FROM t13; 448 SELECT * FROM t13 ORDER BY +b; 449 } {2 0 6 2 1 5} 450 451 # 2019-01-17. From the chromium fuzzer. 452 # 453 do_execsql_test insert-14.1 { 454 DROP TABLE IF EXISTS t14; 455 CREATE TABLE t14(x INTEGER PRIMARY KEY); 456 INSERT INTO t14 VALUES(CASE WHEN 1 THEN null END); 457 SELECT x FROM t14; 458 } {1} 459 460 integrity_check insert-14.2 461 462 # 2019-08-12. 463 # 464 do_execsql_test insert-15.1 { 465 DROP TABLE IF EXISTS t1; 466 DROP TABLE IF EXISTS t2; 467 CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT); 468 CREATE INDEX i1 ON t1(b); 469 CREATE TABLE t2(a, b); 470 INSERT INTO t2 VALUES(4, randomblob(31000)); 471 INSERT INTO t2 VALUES(4, randomblob(32000)); 472 INSERT INTO t2 VALUES(4, randomblob(33000)); 473 REPLACE INTO t1 SELECT a, b FROM t2; 474 SELECT a, length(b) FROM t1; 475 } {4 33000} 476 477 # 2019-10-16 478 # ticket https://www.sqlite.org/src/info/a8a4847a2d96f5de 479 # On a REPLACE INTO, if an AFTER trigger adds back the conflicting 480 # row, you can end up with the wrong number of rows in an index. 481 # 482 db close 483 sqlite3 db :memory: 484 do_catchsql_test insert-16.1 { 485 PRAGMA recursive_triggers = true; 486 CREATE TABLE t0(c0,c1); 487 CREATE UNIQUE INDEX i0 ON t0(c0); 488 INSERT INTO t0(c0,c1) VALUES(123,1); 489 CREATE TRIGGER tr0 AFTER DELETE ON t0 490 BEGIN 491 INSERT INTO t0 VALUES(123,2); 492 END; 493 REPLACE INTO t0(c0,c1) VALUES(123,3); 494 } {1 {UNIQUE constraint failed: t0.c0}} 495 do_execsql_test insert-16.2 { 496 SELECT * FROM t0; 497 } {123 1} 498 integrity_check insert-16.3 499 do_catchsql_test insert-16.4 { 500 CREATE TABLE t1(a INTEGER PRIMARY KEY, b); 501 CREATE INDEX t1b ON t1(b); 502 INSERT INTO t1 VALUES(1, 'one'); 503 CREATE TRIGGER tr3 AFTER DELETE ON t1 BEGIN 504 INSERT INTO t1 VALUES(1, 'three'); 505 END; 506 REPLACE INTO t1 VALUES(1, 'two'); 507 } {1 {UNIQUE constraint failed: t1.a}} 508 integrity_check insert-16.5 509 do_catchsql_test insert-16.6 { 510 PRAGMA foreign_keys = 1; 511 CREATE TABLE p1(a, b UNIQUE); 512 CREATE TABLE c1(c, d REFERENCES p1(b) ON DELETE CASCADE); 513 CREATE TRIGGER tr6 AFTER DELETE ON c1 BEGIN 514 INSERT INTO p1 VALUES(4, 1); 515 END; 516 INSERT INTO p1 VALUES(1, 1); 517 INSERT INTO c1 VALUES(2, 1); 518 REPLACE INTO p1 VALUES(3, 1);2 519 } {1 {UNIQUE constraint failed: p1.b}} 520 integrity_check insert-16.7 521 522 # 2019-10-25 ticket c1e19e12046d23fe 523 do_catchsql_test insert-17.1 { 524 PRAGMA temp.recursive_triggers = true; 525 DROP TABLE IF EXISTS t0; 526 CREATE TABLE t0(aa, bb); 527 CREATE UNIQUE INDEX t0bb ON t0(bb); 528 CREATE TRIGGER "r17.1" BEFORE DELETE ON t0 529 BEGIN INSERT INTO t0(aa,bb) VALUES(99,1); 530 END; 531 INSERT INTO t0(aa,bb) VALUES(10,20); 532 REPLACE INTO t0(aa,bb) VALUES(30,20); 533 } {1 {UNIQUE constraint failed: t0.rowid}} 534 integrity_check insert-17.2 535 do_catchsql_test insert-17.3 { 536 DROP TABLE IF EXISTS t1; 537 CREATE TABLE t1(a, b UNIQUE, c UNIQUE); 538 INSERT INTO t1(a,b,c) VALUES(1,1,1),(2,2,2),(3,3,3),(4,4,4); 539 CREATE TRIGGER "r17.3" AFTER DELETE ON t1 WHEN OLD.c<>3 BEGIN 540 INSERT INTO t1(rowid,a,b,c) VALUES(100,100,100,3); 541 END; 542 REPLACE INTO t1(rowid,a,b,c) VALUES(200,1,2,3); 543 } {1 {UNIQUE constraint failed: t1.c}} 544 integrity_check insert-17.4 545 do_execsql_test insert-17.5 { 546 CREATE TABLE t2(a INTEGER PRIMARY KEY, b); 547 CREATE UNIQUE INDEX t2b ON t2(b); 548 INSERT INTO t2(a,b) VALUES(1,1),(2,2),(3,3),(4,4); 549 CREATE TABLE fire(x); 550 CREATE TRIGGER t2r1 AFTER DELETE ON t2 BEGIN 551 INSERT INTO fire VALUES(old.a); 552 END; 553 UPDATE OR REPLACE t2 SET a=4, b=3 WHERE a=1; 554 SELECT *, 'x' FROM t2 ORDER BY a; 555 } {2 2 x 4 3 x} 556 do_execsql_test insert-17.6 { 557 SELECT x FROM fire ORDER BY x; 558 } {3 4} 559 do_execsql_test insert-17.7 { 560 DELETE FROM t2; 561 DELETE FROM fire; 562 INSERT INTO t2(a,b) VALUES(1,1),(2,2),(3,3),(4,4); 563 UPDATE OR REPLACE t2 SET a=1, b=3 WHERE a=1; 564 SELECT *, 'x' FROM t2 ORDER BY a; 565 } {1 3 x 2 2 x 4 4 x} 566 do_execsql_test insert-17.8 { 567 SELECT x FROM fire ORDER BY x; 568 } {3} 569 do_execsql_test insert-17.10 { 570 CREATE TABLE t3(a INTEGER PRIMARY KEY, b INT, c INT, d INT); 571 CREATE UNIQUE INDEX t3bpi ON t3(b) WHERE c<=d; 572 CREATE UNIQUE INDEX t3d ON t3(d); 573 INSERT INTO t3(a,b,c,d) VALUES(1,1,1,1),(2,1,3,2),(3,4,5,6); 574 CREATE TRIGGER t3r1 AFTER DELETE ON t3 BEGIN 575 SELECT 'hi'; 576 END; 577 REPLACE INTO t3(a,b,c,d) VALUES(4,4,8,9); 578 } {} 579 do_execsql_test insert-17.11 { 580 SELECT *, 'x' FROM t3 ORDER BY a; 581 } {1 1 1 1 x 2 1 3 2 x 4 4 8 9 x} 582 do_execsql_test insert-17.12 { 583 REPLACE INTO t3(a,b,c,d) VALUES(5,1,11,2); 584 SELECT *, 'x' FROM t3 ORDER BY a; 585 } {1 1 1 1 x 4 4 8 9 x 5 1 11 2 x} 586 587 do_execsql_test insert-17.13 { 588 DELETE FROM t3; 589 INSERT INTO t3(a,b,c,d) VALUES(1,1,1,1),(2,1,3,2),(3,4,5,6); 590 DROP TRIGGER t3r1; 591 CREATE TRIGGER t3r1 AFTER DELETE ON t3 BEGIN 592 INSERT INTO t3(b,c,d) VALUES(old.b,old.c,old.d); 593 END; 594 } {} 595 do_catchsql_test insert-17.14 { 596 REPLACE INTO t3(a,b,c,d) VALUES(4,4,8,9); 597 } {1 {UNIQUE constraint failed: t3.b}} 598 do_catchsql_test insert-17.15 { 599 REPLACE INTO t3(a,b,c,d) VALUES(5,1,11,2); 600 } {1 {UNIQUE constraint failed: t3.d}} 601 602 603 finish_test