Merge Chromium + Blink git repositories
[chromium-blink-merge.git] / third_party / sqlite / sqlite-src-3080704 / test / fts3aux1.test
blobd17ac85df5dda4adbfe73a19e5ea481cc6dc37db
1 # 2011 January 27
3 # The author disclaims copyright to this source code.  In place of
4 # a legal notice, here is a blessing:
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.
10 #*************************************************************************
11 # This file implements regression tests for SQLite library.  The
12 # focus of this script is testing the FTS3 module.
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
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
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 = '*';
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:
63 #   * when there is equality comparison against the term column using the 
64 #     BINARY collating sequence. 
66 #   * when there is a range constraint on the term column using the BINARY 
67 #     collating sequence. 
69 # And also uses the full-text index to optimize ORDER BY clauses of the 
70 # form "ORDER BY term ASC" or equivalent.
72 # Test organization is:
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'); 
96 proc rec {varname x} {
97   global $varname
98   incr $varname
99   return 1
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).
106 do_execsql_test 2.1.1.1 {
107   EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term='braid'
108 } { 0 0 0 {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 } {0 0 0 {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.
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 } {2}
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 } {38}
127 # Similar to the test immediately above, but using a term ("breakfast") that 
128 # is not featured in the dataset.
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 } {38}
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
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 } { 0 0 0 {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 } { 0 0 0 {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 } { 0 0 0 {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 } { 0 0 0 {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 } { 0 0 0 {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 } { 0 0 0 {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
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
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
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
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
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
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
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
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
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
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 
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 
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
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
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.
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 [list 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:}]
339   if {$sort} { lappend res 0 0 0 {USE TEMP B-TREE FOR ORDER BY} }
341   set sql "SELECT * FROM terms $orderby"
342   do_execsql_test 2.3.1.$tn "EXPLAIN QUERY PLAN $sql" $res
345 #-------------------------------------------------------------------------
346 # The next set of tests, fts3aux1-3.*, test error conditions in the 
347 # fts4aux module. Except, fault injection testing (OOM, IO error etc.) is 
348 # done in fts3fault2.test
351 do_execsql_test 3.1.1 {
352   CREATE VIRTUAL TABLE t2 USING fts4;
355 do_catchsql_test 3.1.2 {
356   CREATE VIRTUAL TABLE terms2 USING fts4aux;
357 } {1 {invalid arguments to fts4aux constructor}}
358 do_catchsql_test 3.1.3 {
359   CREATE VIRTUAL TABLE terms2 USING fts4aux(t2, t2);
360 } {1 {invalid arguments to fts4aux constructor}}
362 do_execsql_test 3.2.1 {
363   CREATE VIRTUAL TABLE terms3 USING fts4aux(does_not_exist)
365 do_catchsql_test 3.2.2 {
366   SELECT * FROM terms3
367 } {1 {SQL logic error or missing database}}
368 do_catchsql_test 3.2.3 {
369   SELECT * FROM terms3 WHERE term = 'abc'
370 } {1 {SQL logic error or missing database}}
372 do_catchsql_test 3.3.1 {
373   INSERT INTO terms VALUES(1,2,3);
374 } {1 {table terms may not be modified}}
375 do_catchsql_test 3.3.2 {
376   DELETE FROM terms
377 } {1 {table terms may not be modified}}
378 do_catchsql_test 3.3.3 {
379   UPDATE terms set documents = documents+1;
380 } {1 {table terms may not be modified}}
383 #-------------------------------------------------------------------------
384 # The following tests - fts4aux-4.* - test that joins work with fts4aux
385 # tables. And that fts4aux provides reasonably sane cost information via
386 # xBestIndex to the query planner.
388 db close
389 forcedelete test.db
390 sqlite3 db test.db
391 do_execsql_test 4.1 {
392   CREATE VIRTUAL TABLE x1 USING fts4(x);
393   CREATE VIRTUAL TABLE terms USING fts4aux(x1);
394   CREATE TABLE x2(y);
395   CREATE TABLE x3(y);
396   CREATE INDEX i1 ON x3(y);
398   INSERT INTO x1 VALUES('a b c d e');
399   INSERT INTO x1 VALUES('f g h i j');
400   INSERT INTO x1 VALUES('k k l l a');
402   INSERT INTO x2 SELECT term FROM terms WHERE col = '*';
403   INSERT INTO x3 SELECT term FROM terms WHERE col = '*';
406 proc do_plansql_test {tn sql r} {
407   uplevel do_execsql_test $tn [list "EXPLAIN QUERY PLAN $sql ; $sql"] [list $r]
410 do_plansql_test 4.2 {
411   SELECT y FROM x2, terms WHERE y = term AND col = '*'
412 } {
413   0 0 0 {SCAN TABLE x2} 
414   0 1 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 1:} 
415   a b c d e f g h i j k l
418 do_plansql_test 4.3 {
419   SELECT y FROM terms, x2 WHERE y = term AND col = '*'
420 } {
421   0 0 1 {SCAN TABLE x2} 
422   0 1 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 1:} 
423   a b c d e f g h i j k l
426 do_plansql_test 4.4 {
427   SELECT y FROM x3, terms WHERE y = term AND col = '*'
428 } {
429   0 0 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:} 
430   0 1 0 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?)}
431   a b c d e f g h i j k l
434 do_plansql_test 4.5 {
435   SELECT y FROM terms, x3 WHERE y = term AND occurrences>1 AND col = '*'
436 } {
437   0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0:} 
438   0 1 1 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?)}
439   a k l
442 #-------------------------------------------------------------------------
443 # The following tests check that fts4aux can handle an fts table with an
444 # odd name (one that requires quoting for use in SQL statements). And that
445 # the argument to the fts4aux constructor is properly dequoted before use.
447 do_execsql_test 5.1 {
448   CREATE VIRTUAL TABLE "abc '!' def" USING fts4(x, y);
449   INSERT INTO "abc '!' def" VALUES('XX', 'YY');
451   CREATE VIRTUAL TABLE terms3 USING fts4aux("abc '!' def");
452   SELECT * FROM terms3;
453 } {xx * 1 1 xx 0 1 1 yy * 1 1 yy 1 1 1}
455 do_execsql_test 5.2 {
456   CREATE VIRTUAL TABLE "%%^^%%" USING fts4aux('abc ''!'' def');
457   SELECT * FROM "%%^^%%";
458 } {xx * 1 1 xx 0 1 1 yy * 1 1 yy 1 1 1}
460 #-------------------------------------------------------------------------
461 # Test that we can create an fts4aux table in the temp database.
463 forcedelete test.db2
464 do_execsql_test 6.1 {
465   CREATE VIRTUAL TABLE ft1 USING fts4(x, y);
466   INSERT INTO ft1 VALUES('a b', 'c d');
467   INSERT INTO ft1 VALUES('e e', 'c d');
468   INSERT INTO ft1 VALUES('a a', 'b b');
469   CREATE VIRTUAL TABLE temp.aux1 USING fts4aux(main, ft1);
470   SELECT * FROM aux1;
471 } {
472     a * 2 3 a 0 2 3 
473     b * 2 3 b 0 1 1 b 1 1 2 
474     c * 2 2 c 1 2 2 
475     d * 2 2 d 1 2 2 
476     e * 1 2 e 0 1 2
479 do_execsql_test 6.2 {
480   ATTACH 'test.db2' AS att;
481   CREATE VIRTUAL TABLE att.ft1 USING fts4(x, y);
482   INSERT INTO att.ft1 VALUES('v w', 'x y');
483   INSERT INTO att.ft1 VALUES('z z', 'x y');
484   INSERT INTO att.ft1 VALUES('v v', 'w w');
485   CREATE VIRTUAL TABLE temp.aux2 USING fts4aux(att, ft1);
486   SELECT * FROM aux2;
487 } {
488     v * 2 3 v 0 2 3 
489     w * 2 3 w 0 1 1 w 1 1 2 
490     x * 2 2 x 1 2 2 
491     y * 2 2 y 1 2 2 
492     z * 1 2 z 0 1 2
495 foreach {tn q res1 res2} {
496   1  { SELECT * FROM %%% WHERE term = 'a' } {a * 2 3 a 0 2 3} {}
497   2  { SELECT * FROM %%% WHERE term = 'x' } {} {x * 2 2 x 1 2 2} 
499   3  { SELECT * FROM %%% WHERE term >= 'y' } 
500      {} {y * 2 2 y 1 2 2 z * 1 2 z 0 1 2}
502   4  { SELECT * FROM %%% WHERE term <= 'c' } 
503      {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} {}
504 } {
505   set sql1 [string map {%%% aux1} $q]
506   set sql2 [string map {%%% aux2} $q]
508   do_execsql_test 7.$tn.1 $sql1 $res1
509   do_execsql_test 7.$tn.2 $sql2 $res2
512 do_test 8.1 {
513   catchsql { CREATE VIRTUAL TABLE att.aux3 USING fts4aux(main, ft1) }
514 } {1 {invalid arguments to fts4aux constructor}}
516 do_test 8.2 {
517   execsql {DETACH att}
518   catchsql { SELECT * FROM aux2 }
519 } {1 {SQL logic error or missing database}}
521 finish_test