Debian Codegraph @

ext/fts5/test/fts5aa.test

raw
1 # 2014 June 17
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 fts5aa
17
18 # If SQLITE_ENABLE_FTS5 is not defined, omit this file.
19 ifcapable !fts5 {
20 finish_test
21 return
22 }
23
24 foreach_detail_mode $::testprefix {
25 foreach_tokenizer_mode $::testprefix {
26
27 do_execsql_test 1.0 {
28 CREATE VIRTUAL TABLE t1 USING fts5(a, b, c);
29 SELECT name, sql FROM sqlite_master;
30 } {
31 t1 {CREATE VIRTUAL TABLE t1 USING fts5(a, b, c)}
32 t1_data {CREATE TABLE 't1_data'(id INTEGER PRIMARY KEY, block BLOB)}
33 t1_idx {CREATE TABLE 't1_idx'(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID}
34 t1_content {CREATE TABLE 't1_content'(id INTEGER PRIMARY KEY, c0, c1, c2)}
35 t1_docsize {CREATE TABLE 't1_docsize'(id INTEGER PRIMARY KEY, sz BLOB)}
36 t1_config {CREATE TABLE 't1_config'(k PRIMARY KEY, v) WITHOUT ROWID}
37 }
38
39 do_execsql_test 1.1 {
40 DROP TABLE t1;
41 SELECT name, sql FROM sqlite_master;
42 } {}
43
44 #-------------------------------------------------------------------------
45 #
46
47 do_execsql_test 2.0 {
48 CREATE VIRTUAL TABLE t1 USING fts5(x, y, detail=%DETAIL% %TOKENIZER%);
49 }
50 do_execsql_test 2.1 {
51 INSERT INTO t1 VALUES('a b c', 'd e f');
52 }
53
54 do_test 2.2 {
55 execsql { SELECT fts5_decode(id, block) FROM t1_data WHERE id==10 }
56 } {/{{structure} {lvl=0 nMerge=0 nSeg=1 {id=[0123456789]* leaves=1..1}}}/}
57
58 foreach w {a b c d e f} {
59 do_execsql_test 2.3.$w.asc {
60 SELECT rowid FROM t1 WHERE t1 MATCH $w;
61 } {1}
62 do_execsql_test 2.3.$w.desc {
63 SELECT rowid FROM t1 WHERE t1 MATCH $w ORDER BY rowid DESC;
64 } {1}
65 }
66
67 do_execsql_test 2.4 {
68 INSERT INTO t1(t1) VALUES('integrity-check');
69 PRAGMA integrity_check;
70 PRAGMA integrity_check(t1);
71 } {ok ok}
72
73
74 #-------------------------------------------------------------------------
75 #
76 reset_db
77 sqlite3_fts5_register_origintext db
78 do_execsql_test 3.0 {
79 CREATE VIRTUAL TABLE t1 USING fts5(x,y, detail=%DETAIL% %TOKENIZER%);
80 }
81 foreach {i x y} {
82 1 {g f d b f} {h h e i a}
83 2 {f i g j e} {i j c f f}
84 3 {e e i f a} {e h f d f}
85 4 {h j f j i} {h a c f j}
86 5 {d b j c g} {f e i b e}
87 6 {a j a e e} {j d f d e}
88 7 {g i j c h} {j d h c a}
89 8 {j j i d d} {e e d f b}
90 9 {c j j d c} {h j i f g}
91 10 {b f h i a} {c f b b j}
92 } {
93 do_execsql_test 3.$i.1 { INSERT INTO t1 VALUES($x, $y) }
94 do_execsql_test 3.$i.2 { INSERT INTO t1(t1) VALUES('integrity-check') }
95 do_execsql_test 3.$i.3 { PRAGMA integrity_check(t1) } ok
96 if {[set_test_counter errors]} break
97 }
98
99 #-------------------------------------------------------------------------
100 #
101 reset_db
102 sqlite3_fts5_register_origintext db
103 do_execsql_test 4.0 {
104 CREATE VIRTUAL TABLE t1 USING fts5(x,y, detail=%DETAIL% %TOKENIZER%);
105 INSERT INTO t1(t1, rank) VALUES('pgsz', 32);
106 }
107 foreach {i x y} {
108 1 {g f d b f} {h h e i a}
109 2 {f i g j e} {i j c f f}
110 3 {e e i f a} {e h f d f}
111 4 {h j f j i} {h a c f j}
112 5 {d b j c g} {f e i b e}
113 6 {a j a e e} {j d f d e}
114 7 {g i j c h} {j d h c a}
115 8 {j j i d d} {e e d f b}
116 9 {c j j d c} {h j i f g}
117 10 {b f h i a} {c f b b j}
118 } {
119 do_execsql_test 4.$i.1 { INSERT INTO t1 VALUES($x, $y) }
120 do_execsql_test 4.$i.2 { INSERT INTO t1(t1) VALUES('integrity-check') }
121 if {[set_test_counter errors]} break
122 }
123
124 #-------------------------------------------------------------------------
125 #
126 reset_db
127 sqlite3_fts5_register_origintext db
128 do_execsql_test 5.0 {
129 CREATE VIRTUAL TABLE t1 USING fts5(x,y, detail=%DETAIL% %TOKENIZER%);
130 INSERT INTO t1(t1, rank) VALUES('pgsz', 32);
131 }
132 foreach {i x y} {
133 1 {dd abc abc abc abcde} {aaa dd ddd ddd aab}
134 2 {dd aab d aaa b} {abcde c aaa aaa aaa}
135 3 {abcde dd b b dd} {abc abc d abc ddddd}
136 4 {aaa abcde dddd dddd abcde} {abc b b abcde abc}
137 5 {aab dddd d dddd c} {ddd abcde dddd abcde c}
138 6 {ddd dd b aab abcde} {d ddddd dddd c abc}
139 7 {d ddddd ddd c abcde} {c aab d abcde ddd}
140 8 {abcde aaa aab c c} {ddd c dddd b aaa}
141 9 {abcde aab ddddd c aab} {dddd dddd b c dd}
142 10 {ddd abcde dddd dd c} {dddd c c d abcde}
143 } {
144 do_execsql_test 5.$i.1 { INSERT INTO t1 VALUES($x, $y) }
145 do_execsql_test 5.$i.2 { PRAGMA integrity_check(t1) } ok
146 if {[set_test_counter errors]} break
147 }
148
149 #-------------------------------------------------------------------------
150 #
151 reset_db
152 sqlite3_fts5_register_origintext db
153 do_execsql_test 6.0 {
154 CREATE VIRTUAL TABLE t1 USING fts5(x,y, detail=%DETAIL% %TOKENIZER%);
155 INSERT INTO t1(t1, rank) VALUES('pgsz', 32);
156 }
157
158 do_execsql_test 6.1 {
159 INSERT INTO t1(rowid, x, y) VALUES(22, 'a b c', 'c b a');
160 REPLACE INTO t1(rowid, x, y) VALUES(22, 'd e f', 'f e d');
161 }
162
163 do_execsql_test 6.2 {
164 INSERT INTO t1(t1) VALUES('integrity-check')
165 }
166
167 do_execsql_test 6.3 {
168 REPLACE INTO t1(rowid, x, y) VALUES('22', 'l l l', 'l l l');
169 }
170
171 do_execsql_test 6.4 {
172 REPLACE INTO t1(x, y) VALUES('x y z', 'x y z');
173 }
174
175 do_execsql_test 6.5 {
176 INSERT INTO t1(t1) VALUES('integrity-check')
177 }
178
179 do_execsql_test 6.6 {
180 SELECT rowid, * FROM t1;
181 } {
182 22 {l l l} {l l l}
183 23 {x y z} {x y z}
184 }
185
186 #-------------------------------------------------------------------------
187 #
188 reset_db
189 sqlite3_fts5_register_origintext db
190 expr srand(0)
191 do_execsql_test 7.0 {
192 CREATE VIRTUAL TABLE t1 USING fts5(x,y,z);
193 INSERT INTO t1(t1, rank) VALUES('pgsz', 32);
194 }
195
196 proc doc {} {
197 set v [list aaa aab abc abcde b c d dd ddd dddd ddddd]
198 set ret [list]
199 for {set j 0} {$j < 20} {incr j} {
200 lappend ret [lindex $v [expr int(rand()*[llength $v])]]
201 }
202 return $ret
203 }
204
205 proc dump_structure {} {
206 db eval {SELECT fts5_decode(id, block) AS t FROM t1_data WHERE id=10} {
207 foreach lvl [lrange $t 1 end] {
208 set seg [string repeat . [expr [llength $lvl]-2]]
209 puts "[lrange $lvl 0 1] $seg"
210 }
211 }
212 }
213
214 for {set i 1} {$i <= 10} {incr i} {
215 do_test 7.$i {
216 for {set j 0} {$j < 10} {incr j} {
217 set x [doc]
218 set y [doc]
219 set z [doc]
220 set rowid [expr int(rand() * 100)]
221 execsql { REPLACE INTO t1(rowid,x,y,z) VALUES($rowid, $x, $y, $z) }
222 }
223 execsql { INSERT INTO t1(t1) VALUES('integrity-check'); }
224 } {}
225 if {[set_test_counter errors]} break
226 }
227
228 #-------------------------------------------------------------------------
229 #
230 reset_db
231 sqlite3_fts5_register_origintext db
232 do_execsql_test 8.0 {
233 CREATE VIRTUAL TABLE t1 USING fts5(x, prefix="1,2,3");
234 INSERT INTO t1(t1, rank) VALUES('pgsz', 32);
235 }
236
237 do_execsql_test 8.1 {
238 INSERT INTO t1 VALUES('the quick brown fox');
239 INSERT INTO t1(t1) VALUES('integrity-check');
240 }
241
242
243 #-------------------------------------------------------------------------
244 #
245 reset_db
246 sqlite3_fts5_register_origintext db
247
248 expr srand(0)
249
250 do_execsql_test 9.0 {
251 CREATE VIRTUAL TABLE t1 USING fts5(x,y,z, prefix="1,2,3");
252 INSERT INTO t1(t1, rank) VALUES('pgsz', 32);
253 }
254
255 proc doc {} {
256 set v [list aaa aab abc abcde b c d dd ddd dddd ddddd]
257 set ret [list]
258 for {set j 0} {$j < 20} {incr j} {
259 lappend ret [lindex $v [expr int(rand()*[llength $v])]]
260 }
261 return $ret
262 }
263
264 proc dump_structure {} {
265 db eval {SELECT fts5_decode(id, block) AS t FROM t1_data WHERE id=10} {
266 foreach lvl [lrange $t 1 end] {
267 set seg [string repeat . [expr [llength $lvl]-2]]
268 puts "[lrange $lvl 0 1] $seg"
269 }
270 }
271 }
272
273 for {set i 1} {$i <= 10} {incr i} {
274 do_test 9.$i {
275 for {set j 0} {$j < 100} {incr j} {
276 set x [doc]
277 set y [doc]
278 set z [doc]
279 set rowid [expr int(rand() * 100)]
280 execsql { REPLACE INTO t1(rowid,x,y,z) VALUES($rowid, $x, $y, $z) }
281 }
282 execsql { INSERT INTO t1(t1) VALUES('integrity-check'); }
283 } {}
284 if {[set_test_counter errors]} break
285 }
286
287
288 #-------------------------------------------------------------------------
289 #
290 reset_db
291 sqlite3_fts5_register_origintext db
292 do_execsql_test 10.0 {
293 CREATE VIRTUAL TABLE t1 USING fts5(x,y, detail=%DETAIL% %TOKENIZER%);
294 }
295 set d10 {
296 1 {g f d b f} {h h e i a}
297 2 {f i g j e} {i j c f f}
298 3 {e e i f a} {e h f d f}
299 4 {h j f j i} {h a c f j}
300 5 {d b j c g} {f e i b e}
301 6 {a j a e e} {j d f d e}
302 7 {g i j c h} {j d h c a}
303 8 {j j i d d} {e e d f b}
304 9 {c j j d c} {h j i f g}
305 10 {b f h i a} {c f b b j}
306 }
307 foreach {rowid x y} $d10 {
308 do_execsql_test 10.1.$rowid.1 { INSERT INTO t1 VALUES($x, $y) }
309 do_execsql_test 10.1.$rowid.2 { INSERT INTO t1(t1) VALUES('integrity-check') }
310 }
311 foreach rowid {5 9 8 1 2 4 10 7 3 5 6} {
312 do_execsql_test 10.2.$rowid.1 { DELETE FROM t1 WHERE rowid = $rowid }
313 do_execsql_test 10.2.$rowid.2 { INSERT INTO t1(t1) VALUES('integrity-check') }
314 }
315 foreach {rowid x y} $d10 {
316 do_execsql_test 10.3.$rowid.1 { INSERT INTO t1 VALUES($x, $y) }
317 do_execsql_test 10.3.$rowid.2 { INSERT INTO t1(t1) VALUES('integrity-check') }
318 }
319
320 do_execsql_test 10.4.1 { DELETE FROM t1 }
321 do_execsql_test 10.4.2 { INSERT INTO t1(t1) VALUES('integrity-check') }
322
323 #-------------------------------------------------------------------------
324 #
325 do_catchsql_test 11.1 {
326 CREATE VIRTUAL TABLE t2 USING fts5(a, b, c, rank, detail=%DETAIL% %TOKENIZER%);
327 } {1 {reserved fts5 column name: rank}}
328 do_catchsql_test 11.2 {
329 CREATE VIRTUAL TABLE rank USING fts5(a, b, c, detail=%DETAIL% %TOKENIZER%);
330 } {1 {reserved fts5 table name: rank}}
331 do_catchsql_test 11.3 {
332 CREATE VIRTUAL TABLE t2 USING fts5(a, b, c, rowid, detail=%DETAIL% %TOKENIZER%);
333 } {1 {reserved fts5 column name: rowid}}
334
335 #-------------------------------------------------------------------------
336 #
337 do_execsql_test 12.1 {
338 CREATE VIRTUAL TABLE t2 USING fts5(x,y, detail=%DETAIL% %TOKENIZER%);
339 } {}
340
341 do_catchsql_test 12.2 {
342 SELECT t2 FROM t2 WHERE t2 MATCH '*stuff'
343 } {1 {unknown special query: stuff}}
344
345 do_test 12.3 {
346 set res [db eval { SELECT t2 FROM t2 WHERE t2 MATCH '* reads ' }]
347 string is integer $res
348 } {1}
349
350 #-------------------------------------------------------------------------
351 #
352 reset_db
353 sqlite3_fts5_register_origintext db
354 do_execsql_test 13.1 {
355 CREATE VIRTUAL TABLE t1 USING fts5(x, detail=%DETAIL% %TOKENIZER%);
356 INSERT INTO t1(rowid, x) VALUES(1, 'o n e'), (2, 't w o');
357 } {}
358
359 do_execsql_test 13.2 {
360 SELECT rowid FROM t1 WHERE t1 MATCH 'o';
361 } {1 2}
362
363 do_execsql_test 13.4 {
364 DELETE FROM t1 WHERE rowid=2;
365 } {}
366
367 do_execsql_test 13.5 {
368 SELECT rowid FROM t1 WHERE t1 MATCH 'o';
369 } {1}
370
371 do_execsql_test 13.6 {
372 SELECT rowid FROM t1 WHERE t1 MATCH '""';
373 } {}
374
375 #-------------------------------------------------------------------------
376 #
377 reset_db
378 sqlite3_fts5_register_origintext db
379 do_execsql_test 14.1 {
380 CREATE VIRTUAL TABLE t1 USING fts5(x, y, detail=%DETAIL% %TOKENIZER%);
381 INSERT INTO t1(t1, rank) VALUES('pgsz', 32);
382 WITH d(x,y) AS (
383 SELECT NULL, 'xyz xyz xyz xyz xyz xyz'
384 UNION ALL
385 SELECT NULL, 'xyz xyz xyz xyz xyz xyz' FROM d
386 )
387 INSERT INTO t1 SELECT * FROM d LIMIT 200;
388 }
389
390 do_execsql_test 15.x {
391 INSERT INTO t1(t1) VALUES('integrity-check');
392 }
393
394 do_test 14.2 {
395 set nRow 0
396 db eval { SELECT * FROM t1 WHERE t1 MATCH 'xyz' } {
397 db eval {
398 BEGIN;
399 CREATE TABLE t2(a, b);
400 ROLLBACK;
401 }
402 incr nRow
403 }
404 set nRow
405 } {200}
406
407 do_test 14.3 {
408 set nRow 0
409 db eval { BEGIN; }
410 db eval { SELECT * FROM t1 WHERE t1 MATCH 'xyz' } {
411 db eval {
412 SAVEPOINT aaa;
413 CREATE TABLE t2(a, b);
414 ROLLBACK TO aaa;
415 RELEASE aaa;
416 }
417 incr nRow
418 }
419 set nRow
420 } {200}
421
422 do_execsql_test 15.0 {
423 INSERT INTO t1(t1) VALUES('integrity-check');
424 }
425 sqlite3_db_config db DEFENSIVE 0
426 do_execsql_test 15.1 {
427 UPDATE t1_content SET c1 = 'xyz xyz xyz xyz xyz abc' WHERE rowid = 1;
428 }
429 do_catchsql_test 15.2 {
430 INSERT INTO t1(t1) VALUES('integrity-check');
431 } {1 {fts5: checksum mismatch for table "t1"}}
432
433 #-------------------------------------------------------------------------
434 #
435 do_execsql_test 16.1 {
436 CREATE VIRTUAL TABLE n1 USING fts5(a);
437 INSERT INTO n1 VALUES('a b c d');
438 }
439
440 proc funk {} {
441 db eval { UPDATE n1_config SET v=50 WHERE k='version' }
442 set fd [db incrblob main n1_data block 10]
443 fconfigure $fd -translation binary
444 # puts -nonewline $fd "\x44\x45"
445 close $fd
446 }
447 db func funk funk
448
449 # This test case corrupts the structure record within the first invocation
450 # of function funk(). Which used to cause the bm25() function to throw an
451 # exception. But since bm25() can now used the cached structure record,
452 # it never sees the corruption introduced by funk() and so the following
453 # statement no longer fails.
454 #
455 do_catchsql_test 16.2 {
456 SELECT funk(), format('%g',bm25(n1)), funk() FROM n1 WHERE n1 MATCH 'a+b+c+d'
457 } {0 {{} -1e-06 {}}}
458 # {1 {SQL logic error}}
459
460 #-------------------------------------------------------------------------
461 #
462 reset_db
463 sqlite3_fts5_register_origintext db
464 do_execsql_test 17.1 {
465 CREATE VIRTUAL TABLE b2 USING fts5(x, detail=%DETAIL% %TOKENIZER%);
466 INSERT INTO b2 VALUES('a');
467 INSERT INTO b2 VALUES('b');
468 INSERT INTO b2 VALUES('c');
469 }
470
471 do_test 17.2 {
472 set res [list]
473 db eval { SELECT * FROM b2 ORDER BY rowid ASC } {
474 lappend res [execsql { SELECT * FROM b2 ORDER BY rowid ASC }]
475 }
476 set res
477 } {{a b c} {a b c} {a b c}}
478
479 if {[string match n* %DETAIL%]==0} {
480 reset_db
481 sqlite3_fts5_register_origintext db
482 do_execsql_test 17.3 {
483 CREATE VIRTUAL TABLE c2 USING fts5(x, y, detail=%DETAIL% %TOKENIZER%);
484 INSERT INTO c2 VALUES('x x x', 'x x x');
485 SELECT rowid FROM c2 WHERE c2 MATCH 'y:x';
486 } {1}
487 }
488
489 #-------------------------------------------------------------------------
490 #
491 reset_db
492 sqlite3_fts5_register_origintext db
493 do_execsql_test 17.1 {
494 CREATE VIRTUAL TABLE uio USING fts5(ttt, detail=%DETAIL% %TOKENIZER%);
495 INSERT INTO uio VALUES(NULL);
496 INSERT INTO uio SELECT NULL FROM uio;
497 INSERT INTO uio SELECT NULL FROM uio;
498 INSERT INTO uio SELECT NULL FROM uio;
499 INSERT INTO uio SELECT NULL FROM uio;
500 INSERT INTO uio SELECT NULL FROM uio;
501 INSERT INTO uio SELECT NULL FROM uio;
502 INSERT INTO uio SELECT NULL FROM uio;
503 INSERT INTO uio SELECT NULL FROM uio;
504 SELECT count(*) FROM uio;
505 } {256}
506
507 do_execsql_test 17.2 {
508 SELECT count(*) FROM uio WHERE rowid BETWEEN 8 AND 17
509 } {10}
510 do_execsql_test 17.3 {
511 SELECT rowid FROM uio WHERE rowid BETWEEN 8 AND 17
512 } {8 9 10 11 12 13 14 15 16 17}
513 do_execsql_test 17.4 {
514 SELECT rowid FROM uio WHERE rowid BETWEEN 8 AND 17 ORDER BY rowid DESC
515 } {17 16 15 14 13 12 11 10 9 8}
516 do_execsql_test 17.5 {
517 SELECT count(*) FROM uio
518 } {256}
519
520 do_execsql_test 17.6 {
521 INSERT INTO uio(rowid) VALUES(9223372036854775807);
522 INSERT INTO uio(rowid) VALUES(-9223372036854775808);
523 SELECT count(*) FROM uio;
524 } {258}
525 do_execsql_test 17.7 {
526 SELECT min(rowid), max(rowid) FROM uio;
527 } {-9223372036854775808 9223372036854775807}
528
529 do_execsql_test 17.8 {
530 INSERT INTO uio DEFAULT VALUES;
531 SELECT min(rowid), max(rowid), count(*) FROM uio;
532 } {-9223372036854775808 9223372036854775807 259}
533
534 do_execsql_test 17.9 {
535 SELECT min(rowid), max(rowid), count(*) FROM uio WHERE rowid < 10;
536 } {-9223372036854775808 9 10}
537
538 #--------------------------------------------------------------------
539 #
540 do_execsql_test 18.1 {
541 CREATE VIRTUAL TABLE t1 USING fts5(a, b, detail=%DETAIL% %TOKENIZER%);
542 CREATE VIRTUAL TABLE t2 USING fts5(c, d, detail=%DETAIL% %TOKENIZER%);
543 INSERT INTO t1 VALUES('abc*', NULL);
544 INSERT INTO t2 VALUES(1, 'abcdefg');
545 }
546 do_execsql_test 18.2 {
547 SELECT t1.rowid, t2.rowid FROM t1, t2 WHERE t2 MATCH t1.a AND t1.rowid = t2.c
548 } {1 1}
549 do_execsql_test 18.3 {
550 SELECT t1.rowid, t2.rowid FROM t2, t1 WHERE t2 MATCH t1.a AND t1.rowid = t2.c
551 } {1 1}
552
553 #--------------------------------------------------------------------
554 # fts5 table in the temp schema.
555 #
556 reset_db
557 sqlite3_fts5_register_origintext db
558 do_execsql_test 19.0 {
559 CREATE VIRTUAL TABLE temp.t1 USING fts5(x, detail=%DETAIL% %TOKENIZER%);
560 INSERT INTO t1 VALUES('x y z');
561 INSERT INTO t1 VALUES('w x 1');
562 SELECT rowid FROM t1 WHERE t1 MATCH 'x';
563 } {1 2}
564
565 #--------------------------------------------------------------------
566 # Test that 6 and 7 byte varints can be read.
567 #
568 reset_db
569 sqlite3_fts5_register_origintext db
570 do_execsql_test 20.0 {
571 CREATE VIRTUAL TABLE temp.tmp USING fts5(x, detail=%DETAIL% %TOKENIZER%);
572 }
573 set ::ids [list \
574 0 [expr 1<<36] [expr 2<<36] [expr 1<<43] [expr 2<<43]
575 ]
576 do_test 20.1 {
577 foreach id $::ids {
578 execsql { INSERT INTO tmp(rowid, x) VALUES($id, 'x y z') }
579 }
580 execsql { SELECT rowid FROM tmp WHERE tmp MATCH 'y' }
581 } $::ids
582
583 #--------------------------------------------------------------------
584 # Test that a DROP TABLE may be executed within a transaction that
585 # writes to an FTS5 table.
586 #
587 do_execsql_test 21.0 {
588 CREATE TEMP TABLE t8(a, b);
589 CREATE VIRTUAL TABLE ft USING fts5(x, detail=%DETAIL% %TOKENIZER%);
590 }
591
592 do_execsql_test 21.1 {
593 BEGIN;
594 INSERT INTO ft VALUES('a b c');
595 DROP TABLE t8;
596 COMMIT;
597 }
598
599 do_execsql_test 22.0 {
600 CREATE VIRTUAL TABLE t9 USING fts5(x, detail=%DETAIL% %TOKENIZER%);
601 INSERT INTO t9(rowid, x) VALUES(2, 'bbb');
602 BEGIN;
603 INSERT INTO t9(rowid, x) VALUES(1, 'aaa');
604 DELETE FROM t9 WHERE rowid = 2;
605 INSERT INTO t9(rowid, x) VALUES(3, 'bbb');
606 COMMIT;
607 }
608
609 do_execsql_test 22.1 {
610 SELECT rowid FROM t9('a*')
611 } {1}
612
613 #-------------------------------------------------------------------------
614 do_execsql_test 23.0 {
615 CREATE VIRTUAL TABLE t10 USING fts5(x, detail=%DETAIL% %TOKENIZER%);
616 CREATE TABLE t11(x);
617 }
618 do_execsql_test 23.1 {
619 SELECT * FROM t11, t10 WHERE t11.x = t10.x AND t10.rowid IS NULL;
620 }
621 do_execsql_test 23.2 {
622 SELECT * FROM t11, t10 WHERE t10.rowid IS NULL;
623 }
624
625 #-------------------------------------------------------------------------
626 do_execsql_test 24.0 {
627 CREATE VIRTUAL TABLE t12 USING fts5(x, detail=%DETAIL% %TOKENIZER%);
628 INSERT INTO t12 VALUES('aaaa');
629 }
630 do_execsql_test 24.1 {
631 BEGIN;
632 DELETE FROM t12 WHERE rowid=1;
633 SELECT * FROM t12('aaaa');
634 INSERT INTO t12 VALUES('aaaa');
635 END;
636 }
637 execsql_pp {
638 SELECT rowid, hex(block) FROM t12_data
639 }
640 do_execsql_test 24.2 {
641 INSERT INTO t12(t12) VALUES('integrity-check');
642 }
643 do_execsql_test 24.3 {
644 SELECT * FROM t12('aaaa');
645 } {aaaa}
646
647 #-------------------------------------------------------------------------
648 do_execsql_test 25.0 {
649 CREATE VIRTUAL TABLE t13 USING fts5(x, detail=%DETAIL% %TOKENIZER%);
650 }
651 do_execsql_test 25.1 {
652 BEGIN;
653 INSERT INTO t13 VALUES('AAAA');
654 SELECT * FROM t13('BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB*');
655
656 END;
657 }
658
659
660 }
661 }
662
663 expand_all_sql db
664 finish_test