| blob | c7e1ac1a5ca77c3bb80f1c2852fd6237e0ae5f8f |
1 # 2011 January 27
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 FTS3 module.
13 #
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17 ifcapable !fts3 { finish_test ; return }
18 set ::testprefix fts3aux1
20 do_execsql_test 1.1 {
21 CREATE VIRTUAL TABLE t1 USING fts4;
22 INSERT INTO t1 VALUES('one two three four');
23 INSERT INTO t1 VALUES('three four five six');
24 INSERT INTO t1 VALUES('one three five seven');
26 CREATE VIRTUAL TABLE terms USING fts4aux(t1);
27 SELECT term, documents, occurrences FROM terms WHERE col = '*';
28 } {
29 five 2 2 four 2 2 one 2 2 seven 1 1
30 six 1 1 three 3 3 two 1 1
31 }
33 do_execsql_test 1.2 {
34 INSERT INTO t1 VALUES('one one one three three three');
35 SELECT term, documents, occurrences FROM terms WHERE col = '*';
36 } {
37 five 2 2 four 2 2 one 3 5 seven 1 1
38 six 1 1 three 4 6 two 1 1
39 }
41 do_execsql_test 1.3.1 { DELETE FROM t1; }
42 do_execsql_test 1.3.2 {
43 SELECT term, documents, occurrences FROM terms WHERE col = '*';
44 }
46 do_execsql_test 1.4 {
47 INSERT INTO t1 VALUES('a b a b a b a');
48 INSERT INTO t1 SELECT * FROM t1;
49 INSERT INTO t1 SELECT * FROM t1;
50 INSERT INTO t1 SELECT * FROM t1;
51 INSERT INTO t1 SELECT * FROM t1;
52 INSERT INTO t1 SELECT * FROM t1;
53 INSERT INTO t1 SELECT * FROM t1;
54 INSERT INTO t1 SELECT * FROM t1;
55 INSERT INTO t1 SELECT * FROM t1;
56 SELECT term, documents, occurrences FROM terms WHERE col = '*';
57 } {a 256 1024 b 256 768}
59 #-------------------------------------------------------------------------
60 # The following tests verify that the fts4aux module uses the full-text
61 # index to reduce the number of rows scanned in the following circumstances:
62 #
63 # * when there is equality comparison against the term column using the
64 # BINARY collating sequence.
65 #
66 # * when there is a range constraint on the term column using the BINARY
67 # collating sequence.
68 #
69 # And also uses the full-text index to optimize ORDER BY clauses of the
70 # form "ORDER BY term ASC" or equivalent.
71 #
72 # Test organization is:
73 #
74 # fts3aux1-2.1.*: equality constraints.
75 # fts3aux1-2.2.*: range constraints.
76 # fts3aux1-2.3.*: ORDER BY optimization.
77 #
79 do_execsql_test 2.0 {
80 DROP TABLE t1;
81 DROP TABLE terms;
83 CREATE VIRTUAL TABLE x1 USING fts4(x);
84 INSERT INTO x1(x1) VALUES('nodesize=24');
85 CREATE VIRTUAL TABLE terms USING fts4aux(x1);
87 CREATE VIEW terms_v AS
88 SELECT term, documents, occurrences FROM terms WHERE col = '*';
90 INSERT INTO x1 VALUES('braes brag bragged bragger bragging');
91 INSERT INTO x1 VALUES('brags braid braided braiding braids');
92 INSERT INTO x1 VALUES('brain brainchild brained braining brains');
93 INSERT INTO x1 VALUES('brainstem brainstems brainstorm brainstorms');
94 }
96 proc rec {varname x} {
97 global $varname
98 incr $varname
99 return 1
100 }
101 db func rec rec
103 # Use EQP to show that the WHERE expression "term='braid'" uses a different
104 # index number (1) than "+term='braid'" (0).
105 #
106 do_execsql_test 2.1.1.1 {
107 EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term='braid'
108 } {/*SCAN TABLE terms VIRTUAL TABLE INDEX 1:*/}
109 do_execsql_test 2.1.1.2 {
110 EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term='braid'
111 } {/*SCAN TABLE terms VIRTUAL TABLE INDEX 0:*/}
113 # Now show that using "term='braid'" means the virtual table returns
114 # only 1 row to SQLite, but "+term='braid'" means all 19 are returned.
115 #
116 do_test 2.1.2.1 {
117 set cnt 0
118 execsql { SELECT * FROM terms_v WHERE rec('cnt', term) AND term='braid' }
119 set cnt
120 } {1}
121 do_test 2.1.2.2 {
122 set cnt 0
123 execsql { SELECT * FROM terms_v WHERE rec('cnt', term) AND +term='braid' }
124 set cnt
125 } {19}
127 # Similar to the test immediately above, but using a term ("breakfast") that
128 # is not featured in the dataset.
129 #
130 do_test 2.1.3.1 {
131 set cnt 0
132 execsql { SELECT * FROM terms_v WHERE rec('cnt', term) AND term='breakfast' }
133 set cnt
134 } {0}
135 do_test 2.1.3.2 {
136 set cnt 0
137 execsql { SELECT * FROM terms_v WHERE rec('cnt', term) AND +term='breakfast' }
138 set cnt
139 } {19}
141 do_execsql_test 2.1.4.1 { SELECT * FROM terms_v WHERE term='braid' } {braid 1 1}
142 do_execsql_test 2.1.4.2 { SELECT * FROM terms_v WHERE +term='braid'} {braid 1 1}
143 do_execsql_test 2.1.4.3 { SELECT * FROM terms_v WHERE term='breakfast' } {}
144 do_execsql_test 2.1.4.4 { SELECT * FROM terms_v WHERE +term='breakfast' } {}
146 do_execsql_test 2.1.4.5 { SELECT * FROM terms_v WHERE term='cba' } {}
147 do_execsql_test 2.1.4.6 { SELECT * FROM terms_v WHERE +term='cba' } {}
148 do_execsql_test 2.1.4.7 { SELECT * FROM terms_v WHERE term='abc' } {}
149 do_execsql_test 2.1.4.8 { SELECT * FROM terms_v WHERE +term='abc' } {}
151 # Special case: term=NULL
152 #
153 do_execsql_test 2.1.5 { SELECT * FROM terms WHERE term=NULL } {}
155 do_execsql_test 2.2.1.1 {
156 EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term>'brain'
157 } {/*SCAN TABLE terms VIRTUAL TABLE INDEX 2:*/}
158 do_execsql_test 2.2.1.2 {
159 EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term>'brain'
160 } {/*SCAN TABLE terms VIRTUAL TABLE INDEX 0:*/}
162 do_execsql_test 2.2.1.3 {
163 EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term<'brain'
164 } {/*SCAN TABLE terms VIRTUAL TABLE INDEX 4:*/}
165 do_execsql_test 2.2.1.4 {
166 EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term<'brain'
167 } {/*SCAN TABLE terms VIRTUAL TABLE INDEX 0:*/}
169 do_execsql_test 2.2.1.5 {
170 EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term BETWEEN 'brags' AND 'brain'
171 } {/*SCAN TABLE terms VIRTUAL TABLE INDEX 6:*/}
172 do_execsql_test 2.2.1.6 {
173 EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term BETWEEN 'brags' AND 'brain'
174 } {/*SCAN TABLE terms VIRTUAL TABLE INDEX 0:*/}
176 do_test 2.2.2.1 {
177 set cnt 0
178 execsql { SELECT * FROM terms WHERE rec('cnt', term) AND term>'brain' }
179 set cnt
180 } {18}
181 do_test 2.2.2.2 {
182 set cnt 0
183 execsql { SELECT * FROM terms WHERE rec('cnt', term) AND +term>'brain' }
184 set cnt
185 } {38}
186 do_execsql_test 2.2.2.3 {
187 SELECT term, documents, occurrences FROM terms_v WHERE term>'brain'
188 } {
189 brainchild 1 1 brained 1 1 braining 1 1 brains 1 1
190 brainstem 1 1 brainstems 1 1 brainstorm 1 1 brainstorms 1 1
191 }
192 do_execsql_test 2.2.2.4 {
193 SELECT term, documents, occurrences FROM terms_v WHERE +term>'brain'
194 } {
195 brainchild 1 1 brained 1 1 braining 1 1 brains 1 1
196 brainstem 1 1 brainstems 1 1 brainstorm 1 1 brainstorms 1 1
197 }
198 do_execsql_test 2.2.2.5 {
199 SELECT term, documents, occurrences FROM terms_v WHERE term>='brain'
200 } {
201 brain 1 1
202 brainchild 1 1 brained 1 1 braining 1 1 brains 1 1
203 brainstem 1 1 brainstems 1 1 brainstorm 1 1 brainstorms 1 1
204 }
205 do_execsql_test 2.2.2.6 {
206 SELECT term, documents, occurrences FROM terms_v WHERE +term>='brain'
207 } {
208 brain 1 1
209 brainchild 1 1 brained 1 1 braining 1 1 brains 1 1
210 brainstem 1 1 brainstems 1 1 brainstorm 1 1 brainstorms 1 1
211 }
213 do_execsql_test 2.2.2.7 {
214 SELECT term, documents, occurrences FROM terms_v WHERE term>='abc'
215 } {
216 braes 1 1 brag 1 1 bragged 1 1 bragger 1 1
217 bragging 1 1 brags 1 1 braid 1 1 braided 1 1
218 braiding 1 1 braids 1 1 brain 1 1 brainchild 1 1
219 brained 1 1 braining 1 1 brains 1 1 brainstem 1 1
220 brainstems 1 1 brainstorm 1 1 brainstorms 1 1
221 }
222 do_execsql_test 2.2.2.8 {
223 SELECT term, documents, occurrences FROM terms_v WHERE +term>='abc'
224 } {
225 braes 1 1 brag 1 1 bragged 1 1 bragger 1 1
226 bragging 1 1 brags 1 1 braid 1 1 braided 1 1
227 braiding 1 1 braids 1 1 brain 1 1 brainchild 1 1
228 brained 1 1 braining 1 1 brains 1 1 brainstem 1 1
229 brainstems 1 1 brainstorm 1 1 brainstorms 1 1
230 }
232 do_execsql_test 2.2.2.9 {
233 SELECT term, documents, occurrences FROM terms_v WHERE term>='brainstorms'
234 } {brainstorms 1 1}
235 do_execsql_test 2.2.2.10 {
236 SELECT term, documents, occurrences FROM terms_v WHERE term>='brainstorms'
237 } {brainstorms 1 1}
238 do_execsql_test 2.2.2.11 { SELECT * FROM terms_v WHERE term>'brainstorms' } {}
239 do_execsql_test 2.2.2.12 { SELECT * FROM terms_v WHERE term>'brainstorms' } {}
241 do_execsql_test 2.2.2.13 { SELECT * FROM terms_v WHERE term>'cba' } {}
242 do_execsql_test 2.2.2.14 { SELECT * FROM terms_v WHERE term>'cba' } {}
244 do_test 2.2.3.1 {
245 set cnt 0
246 execsql { SELECT * FROM terms WHERE rec('cnt', term) AND term<'brain' }
247 set cnt
248 } {22}
249 do_test 2.2.3.2 {
250 set cnt 0
251 execsql { SELECT * FROM terms WHERE rec('cnt', term) AND +term<'brain' }
252 set cnt
253 } {38}
254 do_execsql_test 2.2.3.3 {
255 SELECT term, documents, occurrences FROM terms_v WHERE term<'brain'
256 } {
257 braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 bragging 1 1
258 brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1
259 }
260 do_execsql_test 2.2.3.4 {
261 SELECT term, documents, occurrences FROM terms_v WHERE +term<'brain'
262 } {
263 braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 bragging 1 1
264 brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1
265 }
266 do_execsql_test 2.2.3.5 {
267 SELECT term, documents, occurrences FROM terms_v WHERE term<='brain'
268 } {
269 braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 bragging 1 1
270 brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1
271 brain 1 1
272 }
273 do_execsql_test 2.2.3.6 {
274 SELECT term, documents, occurrences FROM terms_v WHERE +term<='brain'
275 } {
276 braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 bragging 1 1
277 brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1
278 brain 1 1
279 }
281 do_test 2.2.4.1 {
282 set cnt 0
283 execsql {
284 SELECT term, documents, occurrences FROM terms
285 WHERE rec('cnt', term) AND term BETWEEN 'brags' AND 'brain'
286 }
287 set cnt
288 } {12}
289 do_test 2.2.4.2 {
290 set cnt 0
291 execsql {
292 SELECT term, documents, occurrences FROM terms
293 WHERE rec('cnt', term) AND +term BETWEEN 'brags' AND 'brain'
294 }
295 set cnt
296 } {38}
297 do_execsql_test 2.2.4.3 {
298 SELECT term, documents, occurrences FROM terms_v
299 WHERE rec('cnt', term) AND term BETWEEN 'brags' AND 'brain'
300 } {
301 brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1 brain 1 1
302 }
303 do_execsql_test 2.2.4.4 {
304 SELECT term, documents, occurrences FROM terms_v
305 WHERE rec('cnt', term) AND +term BETWEEN 'brags' AND 'brain'
306 } {
307 brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1 brain 1 1
308 }
309 do_execsql_test 2.2.4.5 {
310 SELECT term, documents, occurrences FROM terms_v
311 WHERE rec('cnt', term) AND term > 'brags' AND term < 'brain'
312 } {
313 braid 1 1 braided 1 1 braiding 1 1 braids 1 1
314 }
315 do_execsql_test 2.2.4.6 {
316 SELECT term, documents, occurrences FROM terms_v
317 WHERE rec('cnt', term) AND +term > 'brags' AND +term < 'brain'
318 } {
319 braid 1 1 braided 1 1 braiding 1 1 braids 1 1
320 }
322 # Check that "ORDER BY term ASC" and equivalents are sorted by the
323 # virtual table implementation. Any other ORDER BY clause requires
324 # SQLite to sort results using a temporary b-tree.
325 #
326 foreach {tn sort orderby} {
327 1 0 "ORDER BY term ASC"
328 2 0 "ORDER BY term"
329 3 1 "ORDER BY term DESC"
330 4 1 "ORDER BY documents ASC"
331 5 1 "ORDER BY documents"
332 6 1 "ORDER BY documents DESC"
333 7 1 "ORDER BY occurrences ASC"
334 8 1 "ORDER BY occurrences"
335 9 1 "ORDER BY occurrences DESC"
336 } {
338 set res {SCAN TABLE terms VIRTUAL TABLE INDEX 0:}
339 if {$sort} { append res {*USE TEMP B-TREE FOR ORDER BY} }
340 set res "/*$res*/"
342 set sql "SELECT * FROM terms $orderby"
343 do_execsql_test 2.3.1.$tn "EXPLAIN QUERY PLAN $sql" $res
344 }
346 #-------------------------------------------------------------------------
347 # The next set of tests, fts3aux1-3.*, test error conditions in the
348 # fts4aux module. Except, fault injection testing (OOM, IO error etc.) is
349 # done in fts3fault2.test
350 #
352 do_execsql_test 3.1.1 {
353 CREATE VIRTUAL TABLE t2 USING fts4;
354 }
356 do_catchsql_test 3.1.2 {
357 CREATE VIRTUAL TABLE terms2 USING fts4aux;
358 } {1 {invalid arguments to fts4aux constructor}}
359 do_catchsql_test 3.1.3 {
360 CREATE VIRTUAL TABLE terms2 USING fts4aux(t2, t2);
361 } {1 {invalid arguments to fts4aux constructor}}
363 do_execsql_test 3.2.1 {
364 CREATE VIRTUAL TABLE terms3 USING fts4aux(does_not_exist)
365 }
366 do_catchsql_test 3.2.2 {
367 SELECT * FROM terms3
368 } {1 {SQL logic error}}
369 do_catchsql_test 3.2.3 {
370 SELECT * FROM terms3 WHERE term = 'abc'
371 } {1 {SQL logic error}}
373 do_catchsql_test 3.3.1 {
374 INSERT INTO terms VALUES(1,2,3);
375 } {1 {table terms may not be modified}}
376 do_catchsql_test 3.3.2 {
377 DELETE FROM terms
378 } {1 {table terms may not be modified}}
379 do_catchsql_test 3.3.3 {
380 UPDATE terms set documents = documents+1;
381 } {1 {table terms may not be modified}}
384 #-------------------------------------------------------------------------
385 # The following tests - fts4aux-4.* - test that joins work with fts4aux
386 # tables. And that fts4aux provides reasonably sane cost information via
387 # xBestIndex to the query planner.
388 #
389 db close
390 forcedelete test.db
391 sqlite3 db test.db
392 do_execsql_test 4.1 {
393 CREATE VIRTUAL TABLE x1 USING fts4(x);
394 CREATE VIRTUAL TABLE terms USING fts4aux(x1);
395 CREATE TABLE x2(y);
396 CREATE TABLE x3(y);
397 CREATE INDEX i1 ON x3(y);
399 INSERT INTO x1 VALUES('a b c d e');
400 INSERT INTO x1 VALUES('f g h i j');
401 INSERT INTO x1 VALUES('k k l l a');
403 INSERT INTO x2 SELECT term FROM terms WHERE col = '*';
404 INSERT INTO x3 SELECT term FROM terms WHERE col = '*';
405 }
407 proc do_plansql_test {tn sql r1 r2} {
408 do_eqp_test $tn.eqp $sql $r1
409 do_execsql_test $tn $sql $r2
410 }
412 do_plansql_test 4.2 {
413 SELECT y FROM x2, terms WHERE y = term AND col = '*'
414 } {
415 QUERY PLAN
416 |--SCAN TABLE x2
417 `--SCAN TABLE terms VIRTUAL TABLE INDEX 1:
418 } {
419 a b c d e f g h i j k l
420 }
422 do_plansql_test 4.3 {
423 SELECT y FROM terms, x2 WHERE y = term AND col = '*'
424 } {
425 QUERY PLAN
426 |--SCAN TABLE x2
427 `--SCAN TABLE terms VIRTUAL TABLE INDEX 1:
428 } {
429 a b c d e f g h i j k l
430 }
432 do_plansql_test 4.4 {
433 SELECT y FROM x3, terms WHERE y = term AND col = '*'
434 } {
435 QUERY PLAN
436 |--SCAN TABLE terms VIRTUAL TABLE INDEX 0:
437 `--SEARCH TABLE x3 USING COVERING INDEX i1 (y=?)
438 } {
439 a b c d e f g h i j k l
440 }
442 do_plansql_test 4.5 {
443 SELECT y FROM terms, x3 WHERE y = term AND occurrences>1 AND col = '*'
444 } {
445 QUERY PLAN
446 |--SCAN TABLE terms VIRTUAL TABLE INDEX 0:
447 `--SEARCH TABLE x3 USING COVERING INDEX i1 (y=?)
448 } {
449 a k l
450 }
452 #-------------------------------------------------------------------------
453 # The following tests check that fts4aux can handle an fts table with an
454 # odd name (one that requires quoting for use in SQL statements). And that
455 # the argument to the fts4aux constructor is properly dequoted before use.
456 #
457 do_execsql_test 5.1 {
458 CREATE VIRTUAL TABLE "abc '!' def" USING fts4(x, y);
459 INSERT INTO "abc '!' def" VALUES('XX', 'YY');
461 CREATE VIRTUAL TABLE terms3 USING fts4aux("abc '!' def");
462 SELECT * FROM terms3;
463 } {xx * 1 1 xx 0 1 1 yy * 1 1 yy 1 1 1}
465 do_execsql_test 5.2 {
466 CREATE VIRTUAL TABLE "%%^^%%" USING fts4aux('abc ''!'' def');
467 SELECT * FROM "%%^^%%";
468 } {xx * 1 1 xx 0 1 1 yy * 1 1 yy 1 1 1}
470 #-------------------------------------------------------------------------
471 # Test that we can create an fts4aux table in the temp database.
472 #
473 forcedelete test.db2
474 do_execsql_test 6.1 {
475 CREATE VIRTUAL TABLE ft1 USING fts4(x, y);
476 INSERT INTO ft1 VALUES('a b', 'c d');
477 INSERT INTO ft1 VALUES('e e', 'c d');
478 INSERT INTO ft1 VALUES('a a', 'b b');
479 CREATE VIRTUAL TABLE temp.aux1 USING fts4aux(main, ft1);
480 SELECT * FROM aux1;
481 } {
482 a * 2 3 a 0 2 3
483 b * 2 3 b 0 1 1 b 1 1 2
484 c * 2 2 c 1 2 2
485 d * 2 2 d 1 2 2
486 e * 1 2 e 0 1 2
487 }
489 do_execsql_test 6.2 {
490 ATTACH 'test.db2' AS att;
491 CREATE VIRTUAL TABLE att.ft1 USING fts4(x, y);
492 INSERT INTO att.ft1 VALUES('v w', 'x y');
493 INSERT INTO att.ft1 VALUES('z z', 'x y');
494 INSERT INTO att.ft1 VALUES('v v', 'w w');
495 CREATE VIRTUAL TABLE temp.aux2 USING fts4aux(att, ft1);
496 SELECT * FROM aux2;
497 } {
498 v * 2 3 v 0 2 3
499 w * 2 3 w 0 1 1 w 1 1 2
500 x * 2 2 x 1 2 2
501 y * 2 2 y 1 2 2
502 z * 1 2 z 0 1 2
503 }
505 foreach {tn q res1 res2} {
506 1 { SELECT * FROM %%% WHERE term = 'a' } {a * 2 3 a 0 2 3} {}
507 2 { SELECT * FROM %%% WHERE term = 'x' } {} {x * 2 2 x 1 2 2}
509 3 { SELECT * FROM %%% WHERE term >= 'y' }
510 {} {y * 2 2 y 1 2 2 z * 1 2 z 0 1 2}
512 4 { SELECT * FROM %%% WHERE term <= 'c' }
513 {a * 2 3 a 0 2 3 b * 2 3 b 0 1 1 b 1 1 2 c * 2 2 c 1 2 2} {}
514 } {
515 set sql1 [string map {%%% aux1} $q]
516 set sql2 [string map {%%% aux2} $q]
518 do_execsql_test 7.$tn.1 $sql1 $res1
519 do_execsql_test 7.$tn.2 $sql2 $res2
520 }
522 do_test 8.1 {
523 catchsql { CREATE VIRTUAL TABLE att.aux3 USING fts4aux(main, ft1) }
524 } {1 {invalid arguments to fts4aux constructor}}
526 do_test 8.2 {
527 execsql {DETACH att}
528 catchsql { SELECT * FROM aux2 }
529 } {1 {SQL logic error}}
531 finish_test