Debian Codegraph @

ext/fts5/test/fts5misc.test

raw
1 # 2019 September 02
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 script is testing the FTS5 module.
13 #
14
15 source [file join [file dirname [info script]] fts5_common.tcl]
16 set testprefix fts5misc
17
18 # If SQLITE_ENABLE_FTS5 is not defined, omit this file.
19 ifcapable !fts5 {
20 finish_test
21 return
22 }
23
24 do_execsql_test 1.0 {
25 CREATE VIRTUAL TABLE t1 USING fts5(a);
26 }
27
28 do_catchsql_test 1.1.1 {
29 SELECT highlight(t1, 4, '<b>', '</b>') FROM t1('*');
30 } {1 {unknown special query: }}
31 do_catchsql_test 1.1.2 {
32 SELECT a FROM t1
33 WHERE rank = (SELECT highlight(t1, 4, '<b>', '</b>') FROM t1('*'));
34 } {1 {unknown special query: }}
35
36 do_catchsql_test 1.2.1 {
37 SELECT highlight(t1, 4, '<b>', '</b>') FROM t1('*id');
38 } {1 {no such cursor: 4}}
39
40 do_catchsql_test 1.2.2 {
41 SELECT a FROM t1
42 WHERE rank = (SELECT highlight(t1, 4, '<b>', '</b>') FROM t1('*id'));
43 } {1 {no such cursor: 6}}
44
45 do_catchsql_test 1.3.1 {
46 SELECT highlight(t1, 4, '<b>', '</b>') FROM t1('*reads');
47 } {1 {no such cursor: 0}}
48
49 do_catchsql_test 1.3.2 {
50 SELECT a FROM t1
51 WHERE rank = (SELECT highlight(t1, 4, '<b>', '</b>') FROM t1('*reads'));
52 } {1 {no such cursor: 0}}
53
54 db close
55 sqlite3 db test.db
56
57 do_catchsql_test 1.3.3 {
58 SELECT a FROM t1
59 WHERE rank = (SELECT highlight(t1, 4, '<b>', '</b>') FROM t1('*reads'));
60 } {1 {no such cursor: 0}}
61
62 fts5_aux_test_functions db
63 do_catchsql_test 1.3.4 {
64 SELECT fts5_columntext(t1) FROM t1('*reads');
65 } {1 {no such cursor: 0}}
66
67 #-------------------------------------------------------------------------
68 reset_db
69 do_execsql_test 2.0 {
70 CREATE TABLE t0(c0);
71 CREATE VIRTUAL TABLE vt0 USING fts5(c0);
72 }
73 do_execsql_test 2.1.1 {
74 BEGIN TRANSACTION;
75 INSERT INTO vt0(c0) VALUES ('xyz');
76 }
77 do_execsql_test 2.1.2 {
78 ALTER TABLE t0 ADD COLUMN c5;
79 }
80 do_execsql_test 2.1.3 {
81 INSERT INTO vt0(vt0) VALUES('integrity-check');
82 }
83 do_execsql_test 2.1.4 {
84 INSERT INTO vt0(c0) VALUES ('abc');
85 COMMIT
86 }
87 do_execsql_test 2.1.5 {
88 INSERT INTO vt0(vt0) VALUES('integrity-check');
89 }
90
91 reset_db
92 do_execsql_test 2.2.1 {
93 CREATE TABLE t0(c0);
94 CREATE VIRTUAL TABLE vt0 USING fts5(c0);
95 BEGIN TRANSACTION;
96 INSERT INTO vt0(c0) VALUES ('xyz');
97 }
98
99 do_execsql_test 2.2.2 {
100 ALTER TABLE t0 RENAME TO t1;
101 }
102 do_execsql_test 2.2.3 {
103 INSERT INTO vt0(vt0) VALUES('integrity-check');
104 }
105 do_execsql_test 2.2.4 {
106 INSERT INTO vt0(c0) VALUES ('abc');
107 COMMIT;
108 }
109 do_execsql_test 2.2.5 {
110 INSERT INTO vt0(vt0) VALUES('integrity-check');
111 }
112
113 #-------------------------------------------------------------------------
114 reset_db
115 do_execsql_test 3.0 {
116 CREATE VIRTUAL TABLE vt0 USING fts5(a);
117 PRAGMA reverse_unordered_selects = true;
118 INSERT INTO vt0 VALUES('365062398'), (0), (0);
119 INSERT INTO vt0(vt0, rank) VALUES('pgsz', '38');
120 }
121 do_execsql_test 3.1 {
122 UPDATE vt0 SET a = 399905135; -- unexpected: database disk image is malformed
123 }
124 do_execsql_test 3.2 {
125 INSERT INTO vt0(vt0) VALUES('integrity-check');
126 }
127
128 #-------------------------------------------------------------------------
129 reset_db
130 do_execsql_test 4.0 {
131 CREATE VIRTUAL TABLE vt0 USING fts5(c0);
132 INSERT INTO vt0(c0) VALUES ('xyz');
133 }
134
135 do_execsql_test 4.1 {
136 BEGIN;
137 INSERT INTO vt0(c0) VALUES ('abc');
138 INSERT INTO vt0(vt0) VALUES('rebuild');
139 COMMIT;
140 }
141
142 do_execsql_test 4.2 {
143 INSERT INTO vt0(vt0) VALUES('integrity-check');
144 }
145
146 do_execsql_test 4.3 {
147 BEGIN;
148 INSERT INTO vt0(vt0) VALUES('rebuild');
149 INSERT INTO vt0(vt0) VALUES('rebuild');
150 COMMIT;
151 }
152
153 do_execsql_test 4.4 {
154 INSERT INTO vt0(vt0) VALUES('integrity-check');
155 }
156
157 #-------------------------------------------------------------------------
158 # Ticket [81a7f7b9].
159 #
160 reset_db
161 do_execsql_test 5.0 {
162 CREATE VIRTUAL TABLE vt0 USING fts5(c0, c1);
163 INSERT INTO vt0(vt0, rank) VALUES('pgsz', '65536');
164 WITH s(i) AS (
165 SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<1236
166 )
167 INSERT INTO vt0(c0) SELECT '0' FROM s;
168 } {}
169
170 do_execsql_test 5.1 {
171 UPDATE vt0 SET c1 = 'T,D&p^y/7#3*v<b<4j7|f';
172 }
173
174 do_execsql_test 5.2 {
175 INSERT INTO vt0(vt0) VALUES('integrity-check');
176 }
177
178 do_catchsql_test 5.3 {
179 INSERT INTO vt0(vt0, rank) VALUES('pgsz', '65537');
180 } {1 {SQL logic error}}
181
182 #-------------------------------------------------------------------------
183 # Ticket [d392017c].
184 #
185 reset_db
186 do_execsql_test 6.0 {
187 CREATE VIRTUAL TABLE vt0 USING fts5(c0);
188 WITH s(i) AS (
189 SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<10000
190 )
191 INSERT INTO vt0(c0) SELECT '0' FROM s;
192 INSERT INTO vt0(vt0, rank) VALUES('crisismerge', 2000);
193 INSERT INTO vt0(vt0, rank) VALUES('automerge', 0);
194 } {}
195
196 do_execsql_test 6.1 {
197 INSERT INTO vt0(vt0) VALUES('rebuild');
198 }
199
200 #-------------------------------------------------------------------------
201 #
202 reset_db
203 do_execsql_test 7.0 {
204 CREATE VIRTUAL TABLE t1 USING fts5(x);
205 INSERT INTO t1(rowid, x) VALUES(1, 'hello world');
206 INSERT INTO t1(rowid, x) VALUES(2, 'well said');
207 INSERT INTO t1(rowid, x) VALUES(3, 'hello said');
208 INSERT INTO t1(rowid, x) VALUES(4, 'well world');
209
210 CREATE TABLE t2 (a, b);
211 INSERT INTO t2 VALUES(1, 'hello');
212 INSERT INTO t2 VALUES(2, 'world');
213 INSERT INTO t2 VALUES(3, 'said');
214 INSERT INTO t2 VALUES(4, 'hello');
215 }
216
217 do_execsql_test 7.1 {
218 SELECT rowid FROM t1 WHERE (rowid, x) IN (SELECT a, b FROM t2);
219 }
220
221 do_execsql_test 7.2 {
222 SELECT rowid FROM t1 WHERE rowid=2 AND t1 = 'hello';
223 }
224
225 #-------------------------------------------------------------------------
226 #
227 reset_db
228 do_execsql_test 8.0 {
229 CREATE VIRTUAL TABLE vt0 USING fts5(c0, tokenize = "ascii", prefix = 1);
230 INSERT INTO vt0(c0) VALUES (x'd1');
231 }
232
233 do_execsql_test 8.1 {
234 INSERT INTO vt0(vt0) VALUES('integrity-check');
235 }
236
237 #-------------------------------------------------------------------------
238 #
239 reset_db
240 do_execsql_test 9.0 {
241 CREATE VIRTUAL TABLE t1 using FTS5(mailcontent);
242 insert into t1(rowid, mailcontent) values
243 (-4764623217061966105, 'we are going to upgrade'),
244 (8324454597464624651, 'we are going to upgrade');
245 }
246
247 do_execsql_test 9.1 {
248 INSERT INTO t1(t1) VALUES('integrity-check');
249 }
250
251 do_execsql_test 9.2 {
252 SELECT rowid FROM t1('upgrade');
253 } {
254 -4764623217061966105 8324454597464624651
255 }
256
257 #-------------------------------------------------------------------------
258 #
259 reset_db
260 do_execsql_test 10.0 {
261 CREATE VIRTUAL TABLE vt1 USING fts5(c1, c2, prefix = 1, tokenize = "ascii");
262 INSERT INTO vt1 VALUES (x'e4', '䔬');
263 }
264
265 do_execsql_test 10.1 {
266 SELECT quote(CAST(c1 AS blob)), quote(CAST(c2 AS blob)) FROM vt1
267 } {X'E4' X'E494AC'}
268
269 do_execsql_test 10.2 {
270 INSERT INTO vt1(vt1) VALUES('integrity-check');
271 }
272
273 #-------------------------------------------------------------------------
274 #
275 reset_db
276 do_execsql_test 11.0 {
277 CREATE VIRTUAL TABLE vt0 USING fts5(
278 c0, prefix = 71, tokenize = "porter ascii", prefix = 9
279 );
280 } {}
281 do_execsql_test 11.1 {
282 BEGIN;
283 INSERT INTO vt0(c0) VALUES (x'e8');
284 }
285 do_execsql_test 11.2 {
286 INSERT INTO vt0(vt0) VALUES('integrity-check');
287 }
288
289 #-------------------------------------------------------------------------
290 # Ticket [752fdbf6]
291 #
292 reset_db
293 do_execsql_test 11.0 {
294 PRAGMA encoding = 'UTF-16';
295 CREATE VIRTUAL TABLE vt0 USING fts5(c0, c1);
296 INSERT INTO vt0(vt0, rank) VALUES('pgsz', '37');
297 INSERT INTO vt0(c0, c1) VALUES (0.66077, 1957391816);
298 }
299 do_execsql_test 11.1 {
300 INSERT INTO vt0(vt0) VALUES('integrity-check');
301 }
302
303 #-------------------------------------------------------------------------
304 # Ticket [7c0e06b16]
305 #
306 do_execsql_test 12.0 {
307 CREATE TABLE t1(a, b, rank);
308 INSERT INTO t1 VALUES('a', 'hello', '');
309 INSERT INTO t1 VALUES('b', 'world', '');
310
311 CREATE VIRTUAL TABLE ft USING fts5(a);
312 INSERT INTO ft VALUES('b');
313 INSERT INTO ft VALUES('y');
314
315 CREATE TABLE t2(x, y, ft);
316 INSERT INTO t2 VALUES(1, 2, 'x');
317 INSERT INTO t2 VALUES(3, 4, 'b');
318 }
319
320 do_execsql_test 12.1 {
321 SELECT * FROM t1 NATURAL JOIN ft WHERE ft MATCH('b')
322 } {b world {}}
323 do_execsql_test 12.2 {
324 SELECT * FROM ft NATURAL JOIN t1 WHERE ft MATCH('b')
325 } {b world {}}
326 do_execsql_test 12.3 {
327 SELECT * FROM t2 JOIN ft USING (ft)
328 } {3 4 b b}
329
330 #-------------------------------------------------------------------------
331 # Forum post https://sqlite.org/forum/forumpost/21127c1160
332 #
333 reset_db
334 sqlite3_db_config db DEFENSIVE 1
335
336 do_execsql_test 13.1.0 {
337 CREATE TABLE a (id INTEGER PRIMARY KEY, name TEXT);
338 CREATE VIRTUAL TABLE b USING fts5(name);
339 CREATE TRIGGER a_trigger AFTER INSERT ON a BEGIN
340 INSERT INTO b (name) VALUES ('foo');
341 END;
342 }
343
344 do_test 13.1.1 {
345 set ::STMT [
346 sqlite3_prepare db "INSERT INTO a VALUES (1, 'foo') RETURNING id;" -1 dummy
347 ]
348 sqlite3_step $::STMT
349 } {SQLITE_ROW}
350
351 do_test 13.1.2 {
352 sqlite3_finalize $::STMT
353 } {SQLITE_OK}
354
355 do_test 13.1.3 {
356 sqlite3_errmsg db
357 } {not an error}
358
359 reset_db
360 sqlite3_db_config db DEFENSIVE 1
361 do_execsql_test 13.2.0 {
362 BEGIN;
363 CREATE TABLE a (id INTEGER PRIMARY KEY, name TEXT);
364 CREATE VIRTUAL TABLE b USING fts5(name);
365 CREATE TRIGGER a_trigger AFTER INSERT ON a BEGIN
366 INSERT INTO b (name) VALUES ('foo');
367 END;
368 }
369
370 do_test 13.2.1 {
371 set ::STMT [
372 sqlite3_prepare db "INSERT INTO a VALUES (1, 'foo') RETURNING id;" -1 dummy
373 ]
374 sqlite3_step $::STMT
375 } {SQLITE_ROW}
376
377 do_test 13.2.2 {
378 sqlite3_finalize $::STMT
379 } {SQLITE_OK}
380
381 do_test 13.2.3 {
382 sqlite3_errmsg db
383 } {not an error}
384
385 #-------------------------------------------------------------------------
386 reset_db
387 db close
388 sqlite3 db test.db -uri 1
389
390 do_execsql_test 14.0 {
391 PRAGMA locking_mode=EXCLUSIVE;
392 BEGIN;
393 ATTACH 'file:/one?vfs=memdb' AS aux1;
394 ATTACH 'file:/one?vfs=memdb' AS aux2;
395 CREATE VIRTUAL TABLE t1 USING fts5(x);
396 } {exclusive}
397 do_catchsql_test 14.1 {
398 ANALYZE;
399 } {1 {database is locked}}
400 do_catchsql_test 14.2 {
401 COMMIT;
402 } {1 {database is locked}}
403 do_catchsql_test 14.3 {
404 COMMIT;
405 } {1 {database is locked}}
406 do_catchsql_test 14.4 {
407 ROLLBACK;
408 } {0 {}}
409
410 #-------------------------------------------------------------------------
411 reset_db
412 sqlite3 db2 test.db
413
414 do_execsql_test 15.0 {
415 CREATE TABLE t1(a, b);
416 BEGIN;
417 SELECT * FROM t1;
418 }
419
420 do_execsql_test -db db2 15.1 {
421 BEGIN;
422 CREATE VIRTUAL TABLE x1 USING fts5(y);
423 }
424 do_test 15.2 {
425 list [catch { db2 eval COMMIT } msg] $msg
426 } {1 {database is locked}}
427 do_execsql_test -db db2 15.3 {
428 SAVEPOINT one;
429 } {}
430 do_execsql_test 15.4 END
431 do_test 15.5 {
432 list [catch { db2 eval COMMIT } msg] $msg
433 } {0 {}}
434
435 db2 close
436
437 #-------------------------------------------------------------------------
438 reset_db
439 forcedelete test.db2
440 sqlite3 db2 test.db
441 do_execsql_test 16.0 {
442
443 ATTACH 'test.db2' AS aux;
444 CREATE TABLE aux.t2(x,y);
445 INSERT INTO t2 VALUES(1, 2);
446 CREATE VIRTUAL TABLE x1 USING fts5(a);
447 BEGIN;
448 INSERT INTO x1 VALUES('abc');
449 INSERT INTO t2 VALUES(3, 4);
450 }
451
452 do_execsql_test -db db2 16.1 {
453 ATTACH 'test.db2' AS aux;
454 BEGIN;
455 SELECT * FROM t2
456 } {1 2}
457
458 do_catchsql_test 16.2 {
459 COMMIT;
460 } {1 {database is locked}}
461
462 do_execsql_test 16.3 {
463 INSERT INTO x1 VALUES('def');
464 }
465
466 do_execsql_test -db db2 16.4 {
467 END
468 }
469
470 do_execsql_test 16.5 {
471 COMMIT
472 }
473
474 do_execsql_test -db db2 16.6 {
475 SELECT * FROM x1
476 } {abc def}
477
478 db2 close
479
480 #-------------------------------------------------------------------------
481 reset_db
482 do_execsql_test 17.1 {
483 CREATE VIRTUAL TABLE ft USING fts5(x, tokenize="unicode61 separators 'X'");
484 }
485 do_execsql_test 17.2 {
486 SELECT 0 FROM ft WHERE ft MATCH 'X' AND ft MATCH 'X'
487 }
488 do_execsql_test 17.3 {
489 SELECT 0 FROM ft('X')
490 }
491
492 do_execsql_test 17.4 {
493 CREATE VIRTUAL TABLE t0 USING fts5(c0, t="trigram");
494 INSERT INTO t0 VALUES('assertionfaultproblem');
495 }
496 do_execsql_test 17.5 {
497 SELECT 0 FROM t0(0) WHERE c0 GLOB 0;
498 } {}
499
500 do_execsql_test 17.5 {
501 SELECT c0 FROM t0 WHERE c0 GLOB '*f*';
502 } {assertionfaultproblem}
503 do_execsql_test 17.5 {
504 SELECT c0 FROM t0 WHERE c0 GLOB '*faul*';
505 } {assertionfaultproblem}
506
507 #-------------------------------------------------------------------------
508 reset_db
509 do_execsql_test 18.0 {
510 BEGIN;
511 CREATE VIRTUAL TABLE t1 USING fts5(text);
512 ALTER TABLE t1 RENAME TO t2;
513 }
514
515 do_execsql_test 18.1 {
516 DROP TABLE t2;
517 }
518
519 do_execsql_test 18.2 {
520 COMMIT;
521 }
522
523 #-------------------------------------------------------------------------
524 reset_db
525 do_execsql_test 19.0 {
526 CREATE VIRTUAL TABLE t1 USING fts5(text);
527 CREATE TABLE t2(text);
528 BEGIN;
529 INSERT INTO t1 VALUES('one');
530 INSERT INTO t1 VALUES('two');
531 INSERT INTO t1 VALUES('three');
532 INSERT INTO t1 VALUES('one');
533 INSERT INTO t1 VALUES('two');
534 INSERT INTO t1 VALUES('three');
535 SAVEPOINT one;
536 INSERT INTO t2 VALUES('one');
537 INSERT INTO t2 VALUES('two');
538 INSERT INTO t2 VALUES('three');
539 ROLLBACK TO one;
540 COMMIT;
541 }
542
543 #-------------------------------------------------------------------------
544 reset_db
545 do_execsql_test 20.0 {
546 CREATE VIRTUAL TABLE x1 USING fts5(a);
547 INSERT INTO x1(rowid, a) VALUES
548 (1, 'a b c d'),
549 (2, 'x b c d'),
550 (3, 'x y z d'),
551 (4, 'a y c x');
552 }
553
554 do_execsql_test 20.1 {
555 SELECT rowid FROM x1 WHERE x1 MATCH 'a' AND x1 MATCH 'b';
556 } {1}
557
558 do_execsql_test 20.2 {
559 SELECT rowid FROM x1 WHERE x1 MATCH 'a' AND x1 MATCH 'y';
560 } {4}
561
562 do_execsql_test 20.3 {
563 SELECT rowid FROM x1 WHERE x1 MATCH 'a' OR x1 MATCH 'y';
564 } {1 4 3}
565
566 do_execsql_test 20.4 {
567 SELECT rowid FROM x1 WHERE x1 MATCH 'a' OR (x1 MATCH 'y' AND x1 MATCH 'd');
568 } {1 4 3}
569
570 do_execsql_test 20.5 {
571 SELECT rowid FROM x1 WHERE x1 MATCH 'z' OR (x1 MATCH 'a' AND x1 MATCH 'd');
572 } {3 1}
573
574 #-------------------------------------------------------------------------
575 reset_db
576 do_execsql_test 21.0 {
577 CREATE TABLE t1(ii INTEGER, x TEXT, y TEXT);
578 CREATE VIRTUAL TABLE xyz USING fts5(content_rowid=ii, content=t1, x, y);
579 INSERT INTO t1 VALUES(1, 'one', 'i');
580 INSERT INTO t1 VALUES(2, 'two', 'ii');
581 INSERT INTO t1 VALUES(3, 'tree', 'iii');
582 INSERT INTO xyz(xyz) VALUES('rebuild');
583 }
584
585 do_execsql_test 21.1 {
586 UPDATE xyz SET y='TWO' WHERE rowid=2;
587 UPDATE t1 SET y='TWO' WHERE ii=2;
588 }
589
590 do_execsql_test 21.2 {
591 PRAGMA integrity_check
592 } {ok}
593
594 sqlite3_db_config db DEFENSIVE 1
595 do_execsql_test 21.3 {
596 CREATE TABLE xyz_notashadow(x, y);
597 DROP TABLE xyz_notashadow;
598 }
599 sqlite3_db_config db DEFENSIVE 0
600
601 #-------------------------------------------------------------------------
602 reset_db
603 do_execsql_test 22.0 {
604 SELECT fts5(NULL);
605 } {{}}
606 do_execsql_test 22.1 {
607 SELECT count(*) FROM (
608 SELECT fts5_source_id()
609 )
610 } {1}
611 execsql_pp {
612 SELECT fts5_source_id()
613 }
614
615 #-------------------------------------------------------------------------
616 reset_db
617 do_execsql_test 23.0 {
618 CREATE VIRTUAL TABLE x1 USING fts5(x);
619 INSERT INTO x1 VALUES('one + two + three');
620 INSERT INTO x1 VALUES('one + xyz + three');
621 INSERT INTO x1 VALUES('xyz + two + xyz');
622 }
623 do_execsql_test 23.1 {
624 SELECT rowid FROM x1('one + two + three');
625 } {1}
626
627 do_execsql_test 23.2 {
628 SELECT rowid FROM x1('^".." AND one');
629 } {}
630
631 do_execsql_test 23.3 {
632 SELECT rowid FROM x1('abc NEAR ".." NEAR def');
633 } {}
634
635 #-------------------------------------------------------------------------
636 reset_db
637 do_execsql_test 24.0 {
638 CREATE VIRTUAL TABLE t1 USING fts5(a, detail='none');
639 INSERT INTO t1(a) VALUES('a');
640 }
641
642 do_execsql_test 24.2 {
643 SELECT rank FROM ( SELECT rank FROM t1('a NOT "" NOT def') ) ORDER BY 1;
644 } {-1e-06}
645
646 do_execsql_test 24.3 {
647 SELECT rank FROM ( SELECT rank FROM t1('a NOT � NOT def') ) ORDER BY 1;
648 } {-1e-06}
649
650 do_execsql_test 24.4 {
651 SELECT rank FROM ( SELECT rank FROM t1('a NOT "" NOT def') );
652 } {-1e-06}
653
654 #-------------------------------------------------------------------------
655 reset_db
656 fts5_aux_test_functions db
657
658 do_execsql_test 25.0 {
659 CREATE VIRTUAL TABLE t1 USING fts5(a, detail='none', content='');
660 INSERT INTO t1(a) VALUES('a b c');
661 }
662
663 do_execsql_test 25.0 {
664 SELECT fts5_test_poslist(t1) FROM t1('b') ORDER BY rank;
665 } {{}}
666
667 #-------------------------------------------------------------------------
668 reset_db
669
670 do_execsql_test 26.0 {
671 PRAGMA foreign_keys = ON;
672 CREATE TABLE t1(x INTEGER PRIMARY KEY);
673 CREATE TABLE t2(y INTEGER PRIMARY KEY,
674 z INTEGER REFERENCES t1(x) DEFERRABLE INITIALLY DEFERRED
675 );
676 CREATE VIRTUAL TABLE t3 USING fts5(a, b, content='', tokendata=1);
677 }
678
679 do_execsql_test 26.1 {
680 BEGIN;
681 INSERT INTO t2 VALUES(1,111);
682 INSERT INTO t3 VALUES(3,3);
683 PRAGMA defer_foreign_keys=ON;
684 DELETE FROM t2 WHERE y+1;
685 COMMIT;
686 }
687
688 #-------------------------------------------------------------------------
689 reset_db
690
691 do_execsql_test 27.0 {
692 CREATE VIRTUAL TABLE ft1 USING fts5(a, b);
693 INSERT INTO ft1(rowid, a, b) VALUES(3, '3', '3');
694 }
695
696 do_execsql_test 27.1 {
697 SELECT * FROM ft1 WHERE rowid=3 AND b MATCH 'hello';
698 }
699
700 finish_test
701