AryaWu/sqlite
0
1# 2011 January 272#3# The author disclaims copyright to this source code. In place of4# 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. The12# focus of this script is testing the FTS3 module.13#14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17ifcapable !fts3 { finish_test ; return }18set ::testprefix fts3aux119 20do_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');25 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 131}32 33do_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 139}40 41do_execsql_test 1.3.1 { DELETE FROM t1; }42do_execsql_test 1.3.2 {43 SELECT term, documents, occurrences FROM terms WHERE col = '*';44}45 46do_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}58 59#-------------------------------------------------------------------------60# The following tests verify that the fts4aux module uses the full-text61# 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# 78 79do_execsql_test 2.0 {80 DROP TABLE t1;81 DROP TABLE terms;82 83 CREATE VIRTUAL TABLE x1 USING fts4(x);84 INSERT INTO x1(x1) VALUES('nodesize=24');85 CREATE VIRTUAL TABLE terms USING fts4aux(x1);86 87 CREATE VIEW terms_v AS 88 SELECT term, documents, occurrences FROM terms WHERE col = '*';89 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}95 96proc rec {varname x} {97 global $varname98 incr $varname99 return 1100}101db func rec rec102 103# Use EQP to show that the WHERE expression "term='braid'" uses a different104# index number (1) than "+term='braid'" (0).105#106do_execsql_test 2.1.1.1 {107 EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term='braid'108} {/*SCAN terms VIRTUAL TABLE INDEX 1:*/}109do_execsql_test 2.1.1.2 {110 EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term='braid'111} {/*SCAN terms VIRTUAL TABLE INDEX 0:*/}112 113# Now show that using "term='braid'" means the virtual table returns114# only 1 row to SQLite, but "+term='braid'" means all 19 are returned.115#116do_test 2.1.2.1 {117 set cnt 0118 execsql { SELECT * FROM terms_v WHERE rec('cnt', term) AND term='braid' }119 set cnt120} {1}121do_test 2.1.2.2 {122 set cnt 0123 execsql { SELECT * FROM terms_v WHERE rec('cnt', term) AND +term='braid' }124 set cnt125} {19}126 127# Similar to the test immediately above, but using a term ("breakfast") that 128# is not featured in the dataset.129#130do_test 2.1.3.1 {131 set cnt 0132 execsql { SELECT * FROM terms_v WHERE rec('cnt', term) AND term='breakfast' }133 set cnt134} {0}135do_test 2.1.3.2 {136 set cnt 0137 execsql { SELECT * FROM terms_v WHERE rec('cnt', term) AND +term='breakfast' }138 set cnt139} {19}140 141do_execsql_test 2.1.4.1 { SELECT * FROM terms_v WHERE term='braid' } {braid 1 1}142do_execsql_test 2.1.4.2 { SELECT * FROM terms_v WHERE +term='braid'} {braid 1 1}143do_execsql_test 2.1.4.3 { SELECT * FROM terms_v WHERE term='breakfast' } {}144do_execsql_test 2.1.4.4 { SELECT * FROM terms_v WHERE +term='breakfast' } {}145 146do_execsql_test 2.1.4.5 { SELECT * FROM terms_v WHERE term='cba' } {}147do_execsql_test 2.1.4.6 { SELECT * FROM terms_v WHERE +term='cba' } {}148do_execsql_test 2.1.4.7 { SELECT * FROM terms_v WHERE term='abc' } {}149do_execsql_test 2.1.4.8 { SELECT * FROM terms_v WHERE +term='abc' } {}150 151# Special case: term=NULL152#153do_execsql_test 2.1.5 { SELECT * FROM terms WHERE term=NULL } {}154 155do_execsql_test 2.2.1.1 {156 EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term>'brain'157} {/*SCAN terms VIRTUAL TABLE INDEX 2:*/}158do_execsql_test 2.2.1.2 {159 EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term>'brain'160} {/*SCAN terms VIRTUAL TABLE INDEX 0:*/}161 162do_execsql_test 2.2.1.3 {163 EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term<'brain'164} {/*SCAN terms VIRTUAL TABLE INDEX 4:*/}165do_execsql_test 2.2.1.4 {166 EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term<'brain'167} {/*SCAN terms VIRTUAL TABLE INDEX 0:*/}168 169do_execsql_test 2.2.1.5 {170 EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term BETWEEN 'brags' AND 'brain'171} {/*SCAN terms VIRTUAL TABLE INDEX 6:*/}172do_execsql_test 2.2.1.6 {173 EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term BETWEEN 'brags' AND 'brain'174} {/*SCAN terms VIRTUAL TABLE INDEX 0:*/}175 176do_test 2.2.2.1 {177 set cnt 0178 execsql { SELECT * FROM terms WHERE rec('cnt', term) AND term>'brain' }179 set cnt180} {18}181do_test 2.2.2.2 {182 set cnt 0183 execsql { SELECT * FROM terms WHERE rec('cnt', term) AND +term>'brain' }184 set cnt185} {38}186do_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 1191}192do_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 1197}198do_execsql_test 2.2.2.5 {199 SELECT term, documents, occurrences FROM terms_v WHERE term>='brain'200} {201 brain 1 1202 brainchild 1 1 brained 1 1 braining 1 1 brains 1 1 203 brainstem 1 1 brainstems 1 1 brainstorm 1 1 brainstorms 1 1204}205do_execsql_test 2.2.2.6 {206 SELECT term, documents, occurrences FROM terms_v WHERE +term>='brain'207} {208 brain 1 1209 brainchild 1 1 brained 1 1 braining 1 1 brains 1 1 210 brainstem 1 1 brainstems 1 1 brainstorm 1 1 brainstorms 1 1211}212 213do_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 1221}222do_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 1230}231 232do_execsql_test 2.2.2.9 {233 SELECT term, documents, occurrences FROM terms_v WHERE term>='brainstorms'234} {brainstorms 1 1}235do_execsql_test 2.2.2.10 {236 SELECT term, documents, occurrences FROM terms_v WHERE term>='brainstorms'237} {brainstorms 1 1}238do_execsql_test 2.2.2.11 { SELECT * FROM terms_v WHERE term>'brainstorms' } {}239do_execsql_test 2.2.2.12 { SELECT * FROM terms_v WHERE term>'brainstorms' } {}240 241do_execsql_test 2.2.2.13 { SELECT * FROM terms_v WHERE term>'cba' } {}242do_execsql_test 2.2.2.14 { SELECT * FROM terms_v WHERE term>'cba' } {}243 244do_test 2.2.3.1 {245 set cnt 0246 execsql { SELECT * FROM terms WHERE rec('cnt', term) AND term<'brain' }247 set cnt248} {22}249do_test 2.2.3.2 {250 set cnt 0251 execsql { SELECT * FROM terms WHERE rec('cnt', term) AND +term<'brain' }252 set cnt253} {38}254do_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 1259}260do_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 1265}266do_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 1271 brain 1 1272}273do_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 1278 brain 1 1279}280 281do_test 2.2.4.1 {282 set cnt 0283 execsql { 284 SELECT term, documents, occurrences FROM terms 285 WHERE rec('cnt', term) AND term BETWEEN 'brags' AND 'brain' 286 }287 set cnt288} {12}289do_test 2.2.4.2 {290 set cnt 0291 execsql { 292 SELECT term, documents, occurrences FROM terms 293 WHERE rec('cnt', term) AND +term BETWEEN 'brags' AND 'brain' 294 }295 set cnt296} {38}297do_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}303do_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}309do_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 1314}315do_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 1320}321 322# Check that "ORDER BY term ASC" and equivalents are sorted by the323# virtual table implementation. Any other ORDER BY clause requires324# SQLite to sort results using a temporary b-tree.325#326foreach {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} {337 338 set res {SCAN terms VIRTUAL TABLE INDEX 0:}339 if {$sort} { append res {*USE TEMP B-TREE FOR ORDER BY} }340 set res "/*$res*/"341 342 set sql "SELECT * FROM terms $orderby"343 do_execsql_test 2.3.1.$tn "EXPLAIN QUERY PLAN $sql" $res344}345 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.test350#351 352do_execsql_test 3.1.1 {353 CREATE VIRTUAL TABLE t2 USING fts4;354}355 356do_catchsql_test 3.1.2 {357 CREATE VIRTUAL TABLE terms2 USING fts4aux;358} {1 {invalid arguments to fts4aux constructor}}359do_catchsql_test 3.1.3 {360 CREATE VIRTUAL TABLE terms2 USING fts4aux(t2, t2);361} {1 {invalid arguments to fts4aux constructor}}362 363do_execsql_test 3.2.1 {364 CREATE VIRTUAL TABLE terms3 USING fts4aux(does_not_exist)365}366do_catchsql_test 3.2.2 {367 SELECT * FROM terms3368} {1 {SQL logic error}}369do_catchsql_test 3.2.3 {370 SELECT * FROM terms3 WHERE term = 'abc'371} {1 {SQL logic error}}372 373do_catchsql_test 3.3.1 {374 INSERT INTO terms VALUES(1,2,3);375} {1 {table terms may not be modified}}376do_catchsql_test 3.3.2 {377 DELETE FROM terms378} {1 {table terms may not be modified}}379do_catchsql_test 3.3.3 {380 UPDATE terms set documents = documents+1;381} {1 {table terms may not be modified}}382 383 384#-------------------------------------------------------------------------385# The following tests - fts4aux-4.* - test that joins work with fts4aux386# tables. And that fts4aux provides reasonably sane cost information via387# xBestIndex to the query planner.388#389db close390forcedelete test.db391sqlite3 db test.db392do_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);398 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');402 403 INSERT INTO x2 SELECT term FROM terms WHERE col = '*';404 INSERT INTO x3 SELECT term FROM terms WHERE col = '*';405}406 407proc do_plansql_test {tn sql r1 r2} {408 do_eqp_test $tn.eqp $sql $r1409 do_execsql_test $tn $sql $r2410}411 412do_plansql_test 4.2 {413 SELECT y FROM x2, terms WHERE y = term AND col = '*'414} {415 QUERY PLAN416 |--SCAN x2417 `--SCAN terms VIRTUAL TABLE INDEX 1:418} {419 a b c d e f g h i j k l420}421 422do_plansql_test 4.3 {423 SELECT y FROM terms, x2 WHERE y = term AND col = '*'424} {425 QUERY PLAN426 |--SCAN x2427 `--SCAN terms VIRTUAL TABLE INDEX 1:428} {429 a b c d e f g h i j k l430}431 432do_plansql_test 4.4 {433 SELECT y FROM x3, terms WHERE y = term AND col = '*'434} {435 QUERY PLAN436 |--SCAN terms VIRTUAL TABLE INDEX 0:437 `--SEARCH x3 USING COVERING INDEX i1 (y=?)438} {439 a b c d e f g h i j k l440}441 442do_plansql_test 4.5 {443 SELECT y FROM terms, x3 WHERE y = term AND occurrences>1 AND col = '*'444} {445 QUERY PLAN446 |--SCAN terms VIRTUAL TABLE INDEX 0:447 `--SEARCH x3 USING COVERING INDEX i1 (y=?)448} {449 a k l450}451 452#-------------------------------------------------------------------------453# The following tests check that fts4aux can handle an fts table with an454# odd name (one that requires quoting for use in SQL statements). And that455# the argument to the fts4aux constructor is properly dequoted before use.456#457do_execsql_test 5.1 {458 CREATE VIRTUAL TABLE "abc '!' def" USING fts4(x, y);459 INSERT INTO "abc '!' def" VALUES('XX', 'YY');460 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}464 465do_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}469 470#-------------------------------------------------------------------------471# Test that we can create an fts4aux table in the temp database.472#473forcedelete test.db2474do_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 2487}488 489do_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 2503}504 505foreach {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} 508 509 3 { SELECT * FROM %%% WHERE term >= 'y' } 510 {} {y * 2 2 y 1 2 2 z * 1 2 z 0 1 2}511 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]517 518 do_execsql_test 7.$tn.1 $sql1 $res1519 do_execsql_test 7.$tn.2 $sql2 $res2520}521 522do_test 8.1 {523 catchsql { CREATE VIRTUAL TABLE att.aux3 USING fts4aux(main, ft1) }524} {1 {invalid arguments to fts4aux constructor}}525 526do_test 8.2 {527 execsql {DETACH att}528 catchsql { SELECT * FROM aux2 }529} {1 {SQL logic error}}530 531finish_test532