Debian Codegraph @

test/returning1.test

raw
1 # 2021-01-28
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 the new RETURNING clause
13 #
14
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17 set testprefix returning1
18
19 do_execsql_test 1.0 {
20 CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c DEFAULT 'pax');
21 INSERT INTO t1(b) VALUES(10),('happy'),(NULL) RETURNING a,b,c;
22 } {1 10 pax 2 happy pax 3 {} pax}
23 do_execsql_test 1.1 {
24 SELECT * FROM t1;
25 } {1 10 pax 2 happy pax 3 {} pax}
26 do_execsql_test 1.2 {
27 INSERT INTO t1(b,c) VALUES(5,99) RETURNING b,c,a,rowid;
28 } {5 99 4 4}
29 do_execsql_test 1.3 {
30 SELECT * FROM t1;
31 } {1 10 pax 2 happy pax 3 {} pax 4 5 99}
32 do_execsql_test 1.4 {
33 INSERT INTO t1 DEFAULT VALUES RETURNING *;
34 } {5 {} pax}
35 do_execsql_test 1.5 {
36 SELECT * FROM t1;
37 } {1 10 pax 2 happy pax 3 {} pax 4 5 99 5 {} pax}
38 do_execsql_test 1.6 {
39 CREATE TABLE t2(x,y,z);
40 INSERT INTO t2 VALUES(11,12,13),(21,'b','c'),(31,'b-value',4.75);
41 }
42 do_execsql_test 1.7 {
43 INSERT INTO t1 SELECT * FROM t2 RETURNING *;
44 } {11 12 13 21 b c 31 b-value 4.75}
45 do_execsql_test 1.8 {
46 SELECT *, '|' FROM t1;
47 } {1 10 pax | 2 happy pax | 3 {} pax | 4 5 99 | 5 {} pax | 11 12 13 | 21 b c | 31 b-value 4.75 |}
48
49 do_execsql_test 2.1 {
50 UPDATE t1 SET c='bellum' WHERE c='pax' RETURNING rowid, b, '|';
51 } {1 10 | 2 happy | 3 {} | 5 {} |}
52 do_execsql_test 2.2 {
53 SELECT *, '|' FROM t1;
54 } {1 10 bellum | 2 happy bellum | 3 {} bellum | 4 5 99 | 5 {} bellum | 11 12 13 | 21 b c | 31 b-value 4.75 |}
55
56 do_execsql_test 3.1 {
57 DELETE FROM t1 WHERE c='bellum' RETURNING rowid, *, '|';
58 } {1 1 10 bellum | 2 2 happy bellum | 3 3 {} bellum | 5 5 {} bellum |}
59 do_execsql_test 3.2 {
60 SELECT *, '|' FROM t1;
61 } {4 5 99 | 11 12 13 | 21 b c | 31 b-value 4.75 |}
62
63 do_execsql_test 4.1 {
64 CREATE TABLE t4(a INT, b INT DEFAULT 1234, c INT DEFAULT -16);
65 CREATE UNIQUE INDEX t4a ON t4(a);
66 INSERT INTO t4(a,b,c) VALUES(1,2,3);
67 } {}
68 do_execsql_test 4.2 {
69 INSERT INTO t4(a,b,c) VALUES(1,22,33)
70 ON CONFLICT(a) DO UPDATE SET b=44
71 RETURNING *;
72 } {1 44 3}
73 do_execsql_test 4.3 {
74 SELECT * FROM t4;
75 } {1 44 3}
76 do_execsql_test 4.4 {
77 DELETE FROM t4;
78 INSERT INTO t4 VALUES(1,2,3),(4,5,6),(7,8,9);
79 } {}
80 do_execsql_test 4.5 {
81 INSERT INTO t4(a,b,c) VALUES(2,3,4),(4,5,6),(5,6,7)
82 ON CONFLICT(a) DO UPDATE SET b=100
83 RETURNING *, '|';
84 } {2 3 4 | 4 100 6 | 5 6 7 |}
85
86 #-------------------------------------------------------------------------
87 # Test RETURNING on a table with virtual columns.
88 #
89 reset_db
90 do_execsql_test 5.0 {
91 CREATE TABLE t1(xyz);
92 CREATE TABLE t2(a as (1+1), b);
93 }
94
95 do_execsql_test 5.1 {
96 UPDATE t2 SET b='123' WHERE b='abc' RETURNING (SELECT b FROM t1);
97 } {}
98
99 do_execsql_test 5.2 {
100 INSERT INTO t2(b) VALUES('abc');
101 }
102
103 do_execsql_test 5.3 {
104 UPDATE t2 SET b='123' WHERE b='abc' RETURNING (SELECT b FROM t1);
105 } {{}}
106
107 do_execsql_test 5.4 {
108 INSERT INTO t2(b) VALUES('abc');
109 INSERT INTO t1(xyz) VALUES(1);
110 UPDATE t2 SET b='123' WHERE b='abc' RETURNING b;
111 } {123}
112
113 do_execsql_test 5.5 {
114 INSERT INTO t2(b) VALUES('abc');
115 UPDATE t2 SET b='123' WHERE b='abc' RETURNING (SELECT b FROM t1);
116 } {123}
117
118 # Ticket 132994c8b1063bfb
119 reset_db
120 do_catchsql_test 6.0 {
121 CREATE TABLE t1(id INTEGER PRIMARY KEY);
122 CREATE TABLE t2(x INT, y INT);
123 INSERT INTO t1 VALUES(1),(2),(4),(9);
124 INSERT INTO t2 VALUES(3,7), (4,25), (5,99);
125 UPDATE t1 SET id=id+y FROM t2 WHERE t1.id=t2.x RETURNING t2.*;
126 } {1 {RETURNING may not use "TABLE.*" wildcards}}
127 do_catchsql_test 6.1 {
128 UPDATE t1 SET id=id+y FROM t2 WHERE t1.id=t2.x RETURNING *, '|';
129 SELECT * FROM t1 ORDER BY id;
130 } {0 {29 | 1 2 9 29}}
131
132 # Forum https://sqlite.org/forum/forumpost/85aef8bc01
133 # Do not silently ignore nonsense table names in the RETURNING clause.
134 # Raise an error.
135 #
136 reset_db
137 do_execsql_test 7.1 {
138 CREATE TABLE t1(a INT, b INT);
139 CREATE TABLE t2(x INT, y INT);
140 INSERT INTO t1(a,b) VALUES(1,2);
141 INSERT INTO t2(x,y) VALUES(1,30);
142 } {}
143 do_catchsql_test 7.2 {
144 UPDATE t1 SET b=b+1 RETURNING new.b;
145 } {1 {no such column: new.b}}
146 do_catchsql_test 7.3 {
147 UPDATE t1 SET b=b+1 RETURNING old.b;
148 } {1 {no such column: old.b}}
149 do_catchsql_test 7.4 {
150 UPDATE t1 SET b=b+1 RETURNING another.b;
151 } {1 {no such column: another.b}}
152 do_catchsql_test 7.5 {
153 UPDATE t1 SET b=b+y FROM t2 WHERE t2.x=t1.a RETURNING t2.x;
154 } {1 {no such column: t2.x}}
155 do_catchsql_test 7.6 {
156 UPDATE t1 SET b=b+y FROM t2 WHERE t2.x=t1.a RETURNING t1.b;
157 } {0 32}
158
159 # This is goofy: The RETURNING clause does not honor the alias
160 # for the table being modified. This might change in the future.
161 #
162 do_catchsql_test 7.7 {
163 UPDATE t1 AS alias SET b=123 RETURNING alias.b;
164 } {1 {no such column: alias.b}}
165 do_catchsql_test 7.8 {
166 UPDATE t1 AS alias SET b=alias.b+1000 RETURNING t1.b;
167 } {0 1032}
168
169 # Forum: https://sqlite.org/forum/info/34c81d83c9177f46
170 reset_db
171 do_execsql_test 8.1 {
172 CREATE TABLE t1(a);
173 CREATE TABLE t2(b,c);
174 INSERT INTO t1 VALUES(1);
175 INSERT INTO t2 VALUES(3,40);
176 } {}
177 do_catchsql_test 8.2 {
178 INSERT INTO t1 VALUES(3) RETURNING a, (SELECT c FROM t2 WHERE new.a=t2.b) AS x;
179 } {1 {no such column: new.a}}
180 do_catchsql_test 8.3 {
181 INSERT INTO t1 VALUES(3) RETURNING a, (SELECT c FROM t2 WHERE old.a=t2.b) AS x;
182 } {1 {no such column: old.a}}
183 do_catchsql_test 8.4 {
184 INSERT INTO t1 VALUES(3) RETURNING a, (SELECT c FROM t2 WHERE t1.a=t2.b) AS x;
185 } {0 {3 40}}
186
187 ifcapable vtab {
188 # dbsqlfuzz finds/crash-486f791cbe2dc45839310073e71367a1d8ad22dd
189 do_catchsql_test 9.1 {
190 UPDATE pragma_encoding SET encoding='UTF-8' RETURNING a, b, *;
191 } {1 {table pragma_encoding may not be modified}}
192 } ;# ifcapable vtab
193
194 # dbsqlfuzz crash-0081f863d7b2002045ac2361879fc80dfebb98f1
195 reset_db
196 do_execsql_test 10.1 {
197 CREATE TABLE t1_a(a, b);
198 CREATE VIEW t1 AS SELECT a, b FROM t1_a;
199
200 INSERT INTO t1_a VALUES('x', 'y');
201 INSERT INTO t1_a VALUES('x', 'y');
202 INSERT INTO t1_a VALUES('x', 'y');
203
204 CREATE TABLE log(op, r, a, b);
205 }
206 do_execsql_test 10.2 {
207 CREATE TRIGGER tr1 INSTEAD OF INSERT ON t1 BEGIN
208 INSERT INTO log VALUES('insert', new.rowid, new.a, new.b);
209 END;
210 CREATE TRIGGER tr2 INSTEAD OF UPDATE ON t1 BEGIN
211 INSERT INTO log VALUES('update', new.rowid, new.a, new.b);
212 END;
213 }
214
215 ifcapable !allow_rowid_in_view {
216 do_catchsql_test 10.3a {
217 INSERT INTO t1(a, b) VALUES(1234, 5678) RETURNING rowid;
218 } {1 {no such column: new.rowid}}
219
220 do_catchsql_test 10.3b {
221 UPDATE t1 SET a='z' WHERE b='y' RETURNING rowid;
222 } {1 {no such column: new.rowid}}
223
224 do_execsql_test 10.4 {
225 SELECT * FROM log;
226 } {}
227 } else {
228 # Note: The values returned by the RETURNING clauses of the following
229 # two statements are the rowid columns of views. These values are not
230 # well defined, so the INSERT returns -1, and the UPDATE returns NULL.
231 # These match the values used for new.rowid expressions, but not much
232 # else.
233 do_catchsql_test 10.3a {
234 INSERT INTO t1(a, b) VALUES(1234, 5678) RETURNING rowid;
235 } {0 -1}
236
237 do_catchsql_test 10.3b {
238 UPDATE t1 SET a='z' WHERE b='y' RETURNING rowid;
239 } {0 {{} {} {}}}
240
241 do_execsql_test 10.4 {
242 SELECT * FROM log;
243 } {
244 insert -1 1234 5678 update {} z y update {} z y update {} z y
245 }
246 }
247
248 # 2021-04-27 dbsqlfuzz 78b9400770ef8cc7d9427dfba26f4fcf46ea7dc2
249 # Returning clauses on TEMP tables with triggers.
250 #
251 reset_db
252 do_execsql_test 11.1 {
253 CREATE TEMP TABLE t1(a,b);
254 CREATE TEMP TABLE t2(c,d);
255 CREATE TEMP TABLE t3(e,f);
256 CREATE TEMP TABLE log(op,x,y);
257 CREATE TEMP TRIGGER t1r1 AFTER INSERT ON t1 BEGIN
258 INSERT INTO log(op,x,y) VALUES('I1',new.a,new.b);
259 END;
260 CREATE TEMP TRIGGER t1r2 BEFORE DELETE ON t1 BEGIN
261 INSERT INTO log(op,x,y) VALUES('D1',old.a,old.b);
262 END;
263 CREATE TEMP TRIGGER t2r3 AFTER UPDATE ON t1 BEGIN
264 INSERT INTO log(op,x,y) VALUES('U1',new.a,new.b);
265 END;
266 CREATE TEMP TRIGGER t2r1 BEFORE INSERT ON t2 BEGIN
267 INSERT INTO log(op,x,y) VALUES('I2',new.c,new.d);
268 END;
269 CREATE TEMP TRIGGER t3r1 AFTER DELETE ON t3 BEGIN
270 INSERT INTO log(op,x,y) VALUES('D3',old.e,old.f);
271 END;
272 CREATE TEMP TRIGGER t3r2 BEFORE UPDATE ON t3 BEGIN
273 INSERT INTO log(op,x,y) VALUES('U3',new.e,new.f);
274 END;
275 INSERT INTO t1(a,b) VALUES(1,2),('happy','glad') RETURNING a, b, '|';
276 } {1 2 | happy glad |}
277 do_execsql_test 11.2 {
278 UPDATE t1 SET b=9 WHERE a=1 RETURNING a, b, 'x';
279 } {1 9 x}
280 do_execsql_test 11.3 {
281 DELETE FROM t1 WHERE a<>'xray' RETURNING a, b, '@';
282 } {1 9 @ happy glad @}
283 do_execsql_test 11.4 {
284 SELECT * FROM log;
285 DELETE FROM log;
286 } {I1 1 2 I1 happy glad U1 1 9 D1 1 9 D1 happy glad}
287 do_execsql_test 11.5 {
288 INSERT INTO t2 VALUES('bravo','charlie') RETURNING d, c, 'z';
289 } {charlie bravo z}
290 do_execsql_test 11.6 {
291 SELECT * FROM log;
292 DELETE FROM log;
293 } {I2 bravo charlie}
294 do_execsql_test 11.7 {
295 INSERT INTO t3(e) VALUES(1),(2),(3) RETURNING 'I', e;
296 UPDATE t3 SET f=e+100 RETURNING 'U', e, f;
297 DELETE FROM t3 WHERE f>100 RETURNING 'D', e, f;
298 } {I 1 I 2 I 3 U 1 101 U 2 102 U 3 103 D 1 101 D 2 102 D 3 103}
299 do_execsql_test 11.6 {
300 SELECT * FROM log;
301 DELETE FROM log;
302 } {U3 1 101 U3 2 102 U3 3 103 D3 1 101 D3 2 102 D3 3 103}
303
304 reset_db
305 do_execsql_test 11.11 {
306 CREATE TEMP TABLE t1(a,b);
307 CREATE TRIGGER r1 BEFORE INSERT ON t1 BEGIN SELECT 1; END;
308 DELETE FROM t1 RETURNING *;
309 DROP TRIGGER r1;
310 INSERT INTO t1 VALUES(5,30);
311 } {}
312 do_execsql_test 11.12 {
313 SELECT * FROM t1;
314 } {5 30}
315
316 # RETURNING column names are dequoted.
317 # https://sqlite.org/forum/forumpost/033daf0b32
318 #
319 reset_db
320 do_test 12.1 {
321 db eval {CREATE TABLE t1(x INT, y INT)}
322 unset -nocomplain cname
323 db eval {INSERT INTO t1(x) VALUES(1) RETURNING "x";} cname {}
324 lsort [array names cname]
325 } {* x}
326 do_test 12.2 {
327 unset -nocomplain cname
328 db eval {INSERT INTO t1(x) VALUES(2) RETURNING [x];} cname {}
329 lsort [array names cname]
330 } {* x}
331 do_test 12.3 {
332 unset -nocomplain cname
333 db eval {INSERT INTO t1(x) VALUES(3) RETURNING x AS [xyz];} cname {}
334 lsort [array names cname]
335 } {* xyz}
336 do_test 12.4 {
337 unset -nocomplain cname
338 db eval {INSERT INTO t1(x,y) VALUES(4,5) RETURNING "x"+"y";} cname {}
339 lsort [array names cname]
340 } {{"x"+"y"} *}
341
342 ifcapable rtree {
343 #-------------------------------------------------------------------------
344 # Based on dbsqlfuzz find crash-ffbba524cac354b2a61bfd677cec9d2a4333f49a
345 reset_db
346 do_execsql_test 13.0 {
347 CREATE VIRTUAL TABLE t1 USING rtree(a, b, c);
348 CREATE TABLE t2(x);
349 }
350
351 do_execsql_test 13.1 {
352 INSERT INTO t1(a,b,c) VALUES(1,2,3)
353 RETURNING (SELECT b FROM t2);
354 } {{}}
355 } ;# end ifcapable rtree
356
357 # 2021-12-01 Forum post https://sqlite.org/forum/forumpost/793beaf322
358 # Need to report foreign key constraint errors prior to RETURNING
359 #
360 reset_db
361 do_execsql_test 14.0 {
362 PRAGMA foreign_keys(1);
363 CREATE TABLE Parent(id INTEGER PRIMARY KEY);
364 CREATE TABLE Child(id INTEGER PRIMARY KEY, parent_id INTEGER REFERENCES Parent(id));
365 } {}
366 do_catchsql_test 14.1 {
367 INSERT INTO child(parent_id) VALUES(123) RETURNING id;
368 } {1 {FOREIGN KEY constraint failed}}
369
370 # 2021-12-28 Forum post https://sqlite.org/forum/forumpost/e0c7574ab2
371 # Incorrect affinity for REAL values that can be represented as integers.
372 #
373 reset_db
374 sqlite3_test_control SQLITE_TESTCTRL_INTERNAL_FUNCTIONS db
375 do_execsql_test 15.0 {
376 CREATE TABLE t1(x REAL);
377 INSERT INTO t1(x) VALUES(5.0) RETURNING x, affinity(x);
378 } {5.0 real}
379 do_execsql_test 15.1 {
380 UPDATE t1 SET x=x+1 RETURNING x, affinity(x);
381 } {6.0 real}
382 do_execsql_test 15.2 {
383 DELETE FROM t1 RETURNING x, affinity(x);
384 } {6.0 real}
385
386 # 2022-02-28 Forum post https://sqlite.org/forum/forumpost/595e132f71
387 # RETURNING with the xfer optimization
388 #
389 reset_db
390 do_execsql_test 16.0 {
391 CREATE TABLE t1(a,b,c);
392 INSERT INTO t1 VALUES(1,2,3),('a','b','c');
393 CREATE TEMP TABLE t2(x,y,z);
394 INSERT INTO t2 SELECT * FROM t1 RETURNING *;
395 } {1 2 3 a b c}
396 do_execsql_test 16.1 {
397 SELECT * FROM t2;
398 } {1 2 3 a b c}
399
400
401 foreach {tn temp} {
402 1 ""
403 2 TEMP
404 } {
405 reset_db
406 do_execsql_test 17.$tn.0 "
407 CREATE $temp TABLE foo (
408 fooid INTEGER PRIMARY KEY,
409 fooval INTEGER NOT NULL UNIQUE,
410 refcnt INTEGER NOT NULL DEFAULT 1
411 );
412 "
413 do_execsql_test 17.$tn.1 {
414 INSERT INTO foo (fooval) VALUES (17), (4711), (17)
415 ON CONFLICT DO
416 UPDATE SET refcnt = refcnt+1
417 RETURNING fooid;
418 } {
419 1 2 1
420 }
421 }
422
423 # 2022-01-13 https://sqlite.org/forum/forumpost/d010a26798
424 #
425 reset_db
426 do_execsql_test 17.0 {
427 CREATE TABLE bug(id INTEGER PRIMARY KEY NOT NULL, x);
428 INSERT INTO bug(id,x) VALUES(20, NULL);
429 UPDATE bug SET x=NULL WHERE id = 20 RETURNING quote(x), x IS NULL;
430 } {NULL 1}
431
432 # 2023-03-08 https://sqlite.org/forum/forumpost/f5a2b1db87
433 # NULL pointer dereference following an error.
434 #
435 do_execsql_test 18.0 {
436 CREATE TABLE v0(c1 INT);
437 CREATE VIEW view_2(c1) AS SELECT CASE WHEN c1 COLLATE TRUE THEN TRUE ELSE TRUE END FROM v0;
438 CREATE TRIGGER x1 INSTEAD OF INSERT ON view_2 BEGIN SELECT true; END;
439 }
440 do_catchsql_test 18.1 {
441 INSERT INTO view_2 DEFAULT VALUES RETURNING *;
442 } {1 {no such collation sequence: TRUE}}
443
444 # 2023-03-16
445 # https://sqlite.org/forum/forumpost/c99d6e0329
446 # ticket d15b3a4ea901ef0d
447 # ticket 89d259d45b855a0d
448 #
449 # A RETURNING clause on an IF NOT EXISTS trigger does not generate
450 # an error if the trigger already exists.
451 #
452 do_execsql_test 19.0 {
453 DROP TABLE IF EXISTS t1;CREATE TABLE t1(a);
454 CREATE TRIGGER r1 AFTER UPDATE ON t1 BEGIN VALUES(0); END;
455 } {}
456 do_catchsql_test 19.1 {
457 CREATE TRIGGER IF NOT EXISTS r1 AFTER DELETE ON t1 BEGIN
458 INSERT INTO t1(a) VALUES (1) RETURNING FALSE;
459 INSERT INTO t1(a) VALUES (2) RETURNING TRUE;
460 END;
461 } {0 {}}
462
463 # 2024-04-24
464 # https://sqlite.org/forum/forumpost/2c83569ce8945d39
465 #
466 # If the RETURNING clause includes subqueries that reference the
467 # table being modified, make sure that the subqueries are identified
468 # as correlated so that the results are recomputed after each step
469 # instead of being computed once and reused.
470 #
471 reset_db
472 db null N
473 do_execsql_test 20.1 {
474 CREATE TABLE t1(a INTEGER PRIMARY KEY, b INT);
475 INSERT INTO t1 VALUES(1,10),(2,20),(3,30),(4,40),(6,60),(8,80);
476 BEGIN;
477 DELETE FROM t1 WHERE a<>3
478 RETURNING a,
479 (SELECT min(a) FROM t1),
480 (SELECT max(a) FROM t1),
481 (SELECT round(avg(a),2) FROM t1);
482 ROLLBACK;
483 } {
484 1 2 8 4.6
485 2 3 8 5.25
486 4 3 8 5.67
487 6 3 8 5.5
488 8 3 3 3.0
489 }
490 do_execsql_test 20.2 {
491 BEGIN;
492 DELETE FROM t1
493 RETURNING a,
494 (SELECT min(a) FROM t1),
495 (SELECT max(a) FROM t1),
496 (SELECT round(avg(a),2) FROM t1);
497 ROLLBACK;
498 } {
499 1 2 8 4.6
500 2 3 8 5.25
501 3 4 8 6.0
502 4 6 8 7.0
503 6 8 8 8.0
504 8 N N N
505 }
506 do_execsql_test 20.3 {
507 BEGIN;
508 DELETE FROM t1
509 RETURNING a,
510 (SELECT min(t2.a)+t1.a*100 FROM t1 AS t2),
511 (SELECT max(t2.a)+t1.a*100 FROM t1 AS t2),
512 (SELECT round(avg(t2.a),2)+t1.a*100 FROM t1 AS t2);
513 ROLLBACK;
514 } {
515 1 102 108 104.6
516 2 203 208 205.25
517 3 304 308 306.0
518 4 406 408 407.0
519 6 608 608 608.0
520 8 N N N
521 }
522
523 #-------------------------------------------------------------------------
524 reset_db
525 do_execsql_test 21.0 {
526 PRAGMA writable_schema=ON;
527 INSERT INTO sqlite_schema DEFAULT VALUES RETURNING sqlite_schema.name;
528 } {{}}
529
530 do_execsql_test 21.1 {
531 INSERT INTO sqlite_temp_schema DEFAULT VALUES RETURNING sqlite_temp_schema.name;
532 } {{}}
533
534 #-------------------------------------------------------------------------
535 reset_db
536 do_execsql_test 22.0 {
537 PRAGMA writable_schema=ON;
538 CREATE TABLE xyz (a);
539 }
540 do_catchsql_test 22.1 {
541 INSERT INTO sqlite_temp_schema DEFAULT VALUES
542 RETURNING
543 (SELECT * FROM xyz AS sqlite_master WHERE a=sqlite_master.name);
544 } {1 {no such column: sqlite_master.name}}
545
546
547 #-------------------------------------------------------------------------
548 reset_db
549 do_execsql_test 23.0 {
550 PRAGMA recursive_triggers = 1;
551 CREATE TABLE t1(x, y);
552 CREATE TRIGGER t1insert AFTER INSERT ON t1 WHEN new.x<5 BEGIN
553 INSERT INTO t1 VALUES(new.x+1, new.y);
554 END;
555 }
556
557 do_execsql_test 23.1 {
558 INSERT INTO t1 VALUES(1, 'one') RETURNING *;
559 } {1 one}
560
561 do_execsql_test 23.2 {
562 SELECT * FROM t1
563 } {1 one 2 one 3 one 4 one 5 one}
564
565 #-------------------------------------------------------------------------
566 reset_db
567 ifcapable fts5 {
568
569 do_execsql_test 24.0 {
570 CREATE VIRTUAL TABLE ft USING fts5(c);
571 CREATE TABLE t1(x);
572 INSERT INTO t1 VALUES('x');
573 }
574
575 db close
576
577 sqlite3 db test.db
578 sqlite3 db2 test.db
579
580 do_execsql_test 24.1 {
581 SELECT * FROM t1
582 } {x}
583
584 do_execsql_test -db db2 24.2 {
585 CREATE TABLE t2(y);
586 INSERT INTO t2 VALUES('y');
587 } {}
588
589 db2 close
590
591 do_execsql_test 24.3 {
592 INSERT INTO ft VALUES('hello world') RETURNING *
593 } {{hello world}}
594 }
595
596
597 finish_test