Debian Codegraph @

test/insert.test

raw
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://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://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://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