diff options
Diffstat (limited to 'test/fts3aux1.test')
-rw-r--r-- | test/fts3aux1.test | 462 |
1 files changed, 462 insertions, 0 deletions
diff --git a/test/fts3aux1.test b/test/fts3aux1.test new file mode 100644 index 0000000..adda586 --- /dev/null +++ b/test/fts3aux1.test @@ -0,0 +1,462 @@ +# 2011 January 27 +# +# The author disclaims copyright to this source code. In place of +# a legal notice, here is a blessing: +# +# May you do good and not evil. +# May you find forgiveness for yourself and forgive others. +# May you share freely, never taking more than you give. +# +#************************************************************************* +# This file implements regression tests for SQLite library. The +# focus of this script is testing the FTS3 module. +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +ifcapable !fts3 { finish_test ; return } +set ::testprefix fts3aux1 + +do_execsql_test 1.1 { + CREATE VIRTUAL TABLE t1 USING fts4; + INSERT INTO t1 VALUES('one two three four'); + INSERT INTO t1 VALUES('three four five six'); + INSERT INTO t1 VALUES('one three five seven'); + + CREATE VIRTUAL TABLE terms USING fts4aux(t1); + SELECT term, documents, occurrences FROM terms WHERE col = '*'; +} { + five 2 2 four 2 2 one 2 2 seven 1 1 + six 1 1 three 3 3 two 1 1 +} + +do_execsql_test 1.2 { + INSERT INTO t1 VALUES('one one one three three three'); + SELECT term, documents, occurrences FROM terms WHERE col = '*'; +} { + five 2 2 four 2 2 one 3 5 seven 1 1 + six 1 1 three 4 6 two 1 1 +} + +do_execsql_test 1.3.1 { DELETE FROM t1; } +do_execsql_test 1.3.2 { + SELECT term, documents, occurrences FROM terms WHERE col = '*'; +} + +do_execsql_test 1.4 { + INSERT INTO t1 VALUES('a b a b a b a'); + INSERT INTO t1 SELECT * FROM t1; + INSERT INTO t1 SELECT * FROM t1; + INSERT INTO t1 SELECT * FROM t1; + INSERT INTO t1 SELECT * FROM t1; + INSERT INTO t1 SELECT * FROM t1; + INSERT INTO t1 SELECT * FROM t1; + INSERT INTO t1 SELECT * FROM t1; + INSERT INTO t1 SELECT * FROM t1; + SELECT term, documents, occurrences FROM terms WHERE col = '*'; +} {a 256 1024 b 256 768} + +#------------------------------------------------------------------------- +# The following tests verify that the fts4aux module uses the full-text +# index to reduce the number of rows scanned in the following circumstances: +# +# * when there is equality comparison against the term column using the +# BINARY collating sequence. +# +# * when there is a range constraint on the term column using the BINARY +# collating sequence. +# +# And also uses the full-text index to optimize ORDER BY clauses of the +# form "ORDER BY term ASC" or equivalent. +# +# Test organization is: +# +# fts3aux1-2.1.*: equality constraints. +# fts3aux1-2.2.*: range constraints. +# fts3aux1-2.3.*: ORDER BY optimization. +# + +do_execsql_test 2.0 { + DROP TABLE t1; + DROP TABLE terms; + + CREATE VIRTUAL TABLE x1 USING fts4(x); + INSERT INTO x1(x1) VALUES('nodesize=24'); + CREATE VIRTUAL TABLE terms USING fts4aux(x1); + + CREATE VIEW terms_v AS + SELECT term, documents, occurrences FROM terms WHERE col = '*'; + + INSERT INTO x1 VALUES('braes brag bragged bragger bragging'); + INSERT INTO x1 VALUES('brags braid braided braiding braids'); + INSERT INTO x1 VALUES('brain brainchild brained braining brains'); + INSERT INTO x1 VALUES('brainstem brainstems brainstorm brainstorms'); +} + +proc rec {varname x} { + global $varname + incr $varname + return 1 +} +db func rec rec + +# Use EQP to show that the WHERE expression "term='braid'" uses a different +# index number (1) than "+term='braid'" (0). +# +do_execsql_test 2.1.1.1 { + EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term='braid' +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~0 rows)} } +do_execsql_test 2.1.1.2 { + EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term='braid' +} {0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)}} + +# Now show that using "term='braid'" means the virtual table returns +# only 1 row to SQLite, but "+term='braid'" means all 19 are returned. +# +do_test 2.1.2.1 { + set cnt 0 + execsql { SELECT * FROM terms_v WHERE rec('cnt', term) AND term='braid' } + set cnt +} {2} +do_test 2.1.2.2 { + set cnt 0 + execsql { SELECT * FROM terms_v WHERE rec('cnt', term) AND +term='braid' } + set cnt +} {38} + +# Similar to the test immediately above, but using a term ("breakfast") that +# is not featured in the dataset. +# +do_test 2.1.3.1 { + set cnt 0 + execsql { SELECT * FROM terms_v WHERE rec('cnt', term) AND term='breakfast' } + set cnt +} {0} +do_test 2.1.3.2 { + set cnt 0 + execsql { SELECT * FROM terms_v WHERE rec('cnt', term) AND +term='breakfast' } + set cnt +} {38} + +do_execsql_test 2.1.4.1 { SELECT * FROM terms_v WHERE term='braid' } {braid 1 1} +do_execsql_test 2.1.4.2 { SELECT * FROM terms_v WHERE +term='braid'} {braid 1 1} +do_execsql_test 2.1.4.3 { SELECT * FROM terms_v WHERE term='breakfast' } {} +do_execsql_test 2.1.4.4 { SELECT * FROM terms_v WHERE +term='breakfast' } {} + +do_execsql_test 2.1.4.5 { SELECT * FROM terms_v WHERE term='cba' } {} +do_execsql_test 2.1.4.6 { SELECT * FROM terms_v WHERE +term='cba' } {} +do_execsql_test 2.1.4.7 { SELECT * FROM terms_v WHERE term='abc' } {} +do_execsql_test 2.1.4.8 { SELECT * FROM terms_v WHERE +term='abc' } {} + +# Special case: term=NULL +# +do_execsql_test 2.1.5 { SELECT * FROM terms WHERE term=NULL } {} + +do_execsql_test 2.2.1.1 { + EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term>'brain' +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 2: (~0 rows)} } +do_execsql_test 2.2.1.2 { + EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term>'brain' +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} } + +do_execsql_test 2.2.1.3 { + EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term<'brain' +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 4: (~0 rows)} } +do_execsql_test 2.2.1.4 { + EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term<'brain' +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} } + +do_execsql_test 2.2.1.5 { + EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term BETWEEN 'brags' AND 'brain' +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 6: (~0 rows)} } +do_execsql_test 2.2.1.6 { + EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term BETWEEN 'brags' AND 'brain' +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} } + +do_test 2.2.2.1 { + set cnt 0 + execsql { SELECT * FROM terms WHERE rec('cnt', term) AND term>'brain' } + set cnt +} {18} +do_test 2.2.2.2 { + set cnt 0 + execsql { SELECT * FROM terms WHERE rec('cnt', term) AND +term>'brain' } + set cnt +} {38} +do_execsql_test 2.2.2.3 { + SELECT term, documents, occurrences FROM terms_v WHERE term>'brain' +} { + brainchild 1 1 brained 1 1 braining 1 1 brains 1 1 + brainstem 1 1 brainstems 1 1 brainstorm 1 1 brainstorms 1 1 +} +do_execsql_test 2.2.2.4 { + SELECT term, documents, occurrences FROM terms_v WHERE +term>'brain' +} { + brainchild 1 1 brained 1 1 braining 1 1 brains 1 1 + brainstem 1 1 brainstems 1 1 brainstorm 1 1 brainstorms 1 1 +} +do_execsql_test 2.2.2.5 { + SELECT term, documents, occurrences FROM terms_v WHERE term>='brain' +} { + brain 1 1 + brainchild 1 1 brained 1 1 braining 1 1 brains 1 1 + brainstem 1 1 brainstems 1 1 brainstorm 1 1 brainstorms 1 1 +} +do_execsql_test 2.2.2.6 { + SELECT term, documents, occurrences FROM terms_v WHERE +term>='brain' +} { + brain 1 1 + brainchild 1 1 brained 1 1 braining 1 1 brains 1 1 + brainstem 1 1 brainstems 1 1 brainstorm 1 1 brainstorms 1 1 +} + +do_execsql_test 2.2.2.7 { + SELECT term, documents, occurrences FROM terms_v WHERE term>='abc' +} { + braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 + bragging 1 1 brags 1 1 braid 1 1 braided 1 1 + braiding 1 1 braids 1 1 brain 1 1 brainchild 1 1 + brained 1 1 braining 1 1 brains 1 1 brainstem 1 1 + brainstems 1 1 brainstorm 1 1 brainstorms 1 1 +} +do_execsql_test 2.2.2.8 { + SELECT term, documents, occurrences FROM terms_v WHERE +term>='abc' +} { + braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 + bragging 1 1 brags 1 1 braid 1 1 braided 1 1 + braiding 1 1 braids 1 1 brain 1 1 brainchild 1 1 + brained 1 1 braining 1 1 brains 1 1 brainstem 1 1 + brainstems 1 1 brainstorm 1 1 brainstorms 1 1 +} + +do_execsql_test 2.2.2.9 { + SELECT term, documents, occurrences FROM terms_v WHERE term>='brainstorms' +} {brainstorms 1 1} +do_execsql_test 2.2.2.10 { + SELECT term, documents, occurrences FROM terms_v WHERE term>='brainstorms' +} {brainstorms 1 1} +do_execsql_test 2.2.2.11 { SELECT * FROM terms_v WHERE term>'brainstorms' } {} +do_execsql_test 2.2.2.12 { SELECT * FROM terms_v WHERE term>'brainstorms' } {} + +do_execsql_test 2.2.2.13 { SELECT * FROM terms_v WHERE term>'cba' } {} +do_execsql_test 2.2.2.14 { SELECT * FROM terms_v WHERE term>'cba' } {} + +do_test 2.2.3.1 { + set cnt 0 + execsql { SELECT * FROM terms WHERE rec('cnt', term) AND term<'brain' } + set cnt +} {22} +do_test 2.2.3.2 { + set cnt 0 + execsql { SELECT * FROM terms WHERE rec('cnt', term) AND +term<'brain' } + set cnt +} {38} +do_execsql_test 2.2.3.3 { + SELECT term, documents, occurrences FROM terms_v WHERE term<'brain' +} { + braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 bragging 1 1 + brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1 +} +do_execsql_test 2.2.3.4 { + SELECT term, documents, occurrences FROM terms_v WHERE +term<'brain' +} { + braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 bragging 1 1 + brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1 +} +do_execsql_test 2.2.3.5 { + SELECT term, documents, occurrences FROM terms_v WHERE term<='brain' +} { + braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 bragging 1 1 + brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1 + brain 1 1 +} +do_execsql_test 2.2.3.6 { + SELECT term, documents, occurrences FROM terms_v WHERE +term<='brain' +} { + braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 bragging 1 1 + brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1 + brain 1 1 +} + +do_test 2.2.4.1 { + set cnt 0 + execsql { + SELECT term, documents, occurrences FROM terms + WHERE rec('cnt', term) AND term BETWEEN 'brags' AND 'brain' + } + set cnt +} {12} +do_test 2.2.4.2 { + set cnt 0 + execsql { + SELECT term, documents, occurrences FROM terms + WHERE rec('cnt', term) AND +term BETWEEN 'brags' AND 'brain' + } + set cnt +} {38} +do_execsql_test 2.2.4.3 { + SELECT term, documents, occurrences FROM terms_v + WHERE rec('cnt', term) AND term BETWEEN 'brags' AND 'brain' +} { + brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1 brain 1 1 +} +do_execsql_test 2.2.4.4 { + SELECT term, documents, occurrences FROM terms_v + WHERE rec('cnt', term) AND +term BETWEEN 'brags' AND 'brain' +} { + brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1 brain 1 1 +} +do_execsql_test 2.2.4.5 { + SELECT term, documents, occurrences FROM terms_v + WHERE rec('cnt', term) AND term > 'brags' AND term < 'brain' +} { + braid 1 1 braided 1 1 braiding 1 1 braids 1 1 +} +do_execsql_test 2.2.4.6 { + SELECT term, documents, occurrences FROM terms_v + WHERE rec('cnt', term) AND +term > 'brags' AND +term < 'brain' +} { + braid 1 1 braided 1 1 braiding 1 1 braids 1 1 +} + +# Check that "ORDER BY term ASC" and equivalents are sorted by the +# virtual table implementation. Any other ORDER BY clause requires +# SQLite to sort results using a temporary b-tree. +# +foreach {tn sort orderby} { + 1 0 "ORDER BY term ASC" + 2 0 "ORDER BY term" + 3 1 "ORDER BY term DESC" + 4 1 "ORDER BY documents ASC" + 5 1 "ORDER BY documents" + 6 1 "ORDER BY documents DESC" + 7 1 "ORDER BY occurrences ASC" + 8 1 "ORDER BY occurrences" + 9 1 "ORDER BY occurrences DESC" +} { + + set res [list 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)}] + if {$sort} { lappend res 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } + + set sql "SELECT * FROM terms $orderby" + do_execsql_test 2.3.1.$tn "EXPLAIN QUERY PLAN $sql" $res +} + +#------------------------------------------------------------------------- +# The next set of tests, fts3aux1-3.*, test error conditions in the +# fts4aux module. Except, fault injection testing (OOM, IO error etc.) is +# done in fts3fault2.test +# + +do_execsql_test 3.1.1 { + CREATE VIRTUAL TABLE t2 USING fts4; +} + +do_catchsql_test 3.1.2 { + CREATE VIRTUAL TABLE terms2 USING fts4aux; +} {1 {wrong number of arguments to fts4aux constructor}} +do_catchsql_test 3.1.3 { + CREATE VIRTUAL TABLE terms2 USING fts4aux(t2, t2); +} {1 {wrong number of arguments to fts4aux constructor}} + +do_execsql_test 3.2.1 { + CREATE VIRTUAL TABLE terms3 USING fts4aux(does_not_exist) +} +do_catchsql_test 3.2.2 { + SELECT * FROM terms3 +} {1 {SQL logic error or missing database}} +do_catchsql_test 3.2.3 { + SELECT * FROM terms3 WHERE term = 'abc' +} {1 {SQL logic error or missing database}} + +do_catchsql_test 3.3.1 { + INSERT INTO terms VALUES(1,2,3); +} {1 {table terms may not be modified}} +do_catchsql_test 3.3.2 { + DELETE FROM terms +} {1 {table terms may not be modified}} +do_catchsql_test 3.3.3 { + UPDATE terms set documents = documents+1; +} {1 {table terms may not be modified}} + + +#------------------------------------------------------------------------- +# The following tests - fts4aux-4.* - test that joins work with fts4aux +# tables. And that fts4aux provides reasonably sane cost information via +# xBestIndex to the query planner. +# +db close +forcedelete test.db +sqlite3 db test.db +do_execsql_test 4.1 { + CREATE VIRTUAL TABLE x1 USING fts4(x); + CREATE VIRTUAL TABLE terms USING fts4aux(x1); + CREATE TABLE x2(y); + CREATE TABLE x3(y); + CREATE INDEX i1 ON x3(y); + + INSERT INTO x1 VALUES('a b c d e'); + INSERT INTO x1 VALUES('f g h i j'); + INSERT INTO x1 VALUES('k k l l a'); + + INSERT INTO x2 SELECT term FROM terms WHERE col = '*'; + INSERT INTO x3 SELECT term FROM terms WHERE col = '*'; +} + +proc do_plansql_test {tn sql r} { + uplevel do_execsql_test $tn [list "EXPLAIN QUERY PLAN $sql ; $sql"] [list $r] +} + +do_plansql_test 4.2 { + SELECT y FROM x2, terms WHERE y = term AND col = '*' +} { + 0 0 0 {SCAN TABLE x2 (~1000000 rows)} + 0 1 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~0 rows)} + a b c d e f g h i j k l +} + +do_plansql_test 4.3 { + SELECT y FROM terms, x2 WHERE y = term AND col = '*' +} { + 0 0 1 {SCAN TABLE x2 (~1000000 rows)} + 0 1 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~0 rows)} + a b c d e f g h i j k l +} + +do_plansql_test 4.4 { + SELECT y FROM x3, terms WHERE y = term AND col = '*' +} { + 0 0 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} + 0 1 0 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?) (~10 rows)} + a b c d e f g h i j k l +} + +do_plansql_test 4.5 { + SELECT y FROM terms, x3 WHERE y = term AND occurrences>1 AND col = '*' +} { + 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} + 0 1 1 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?) (~10 rows)} + a k l +} + +#------------------------------------------------------------------------- +# The following tests check that fts4aux can handle an fts table with an +# odd name (one that requires quoting for use in SQL statements). And that +# the argument to the fts4aux constructor is properly dequoted before use. +# +# +do_execsql_test 5.1 { + CREATE VIRTUAL TABLE "abc '!' def" USING fts4(x, y); + INSERT INTO "abc '!' def" VALUES('XX', 'YY'); + + CREATE VIRTUAL TABLE terms3 USING fts4aux("abc '!' def"); + SELECT * FROM terms3; +} {xx * 1 1 xx 0 1 1 yy * 1 1 yy 1 1 1} + +do_execsql_test 5.2 { + CREATE VIRTUAL TABLE "%%^^%%" USING fts4aux('abc ''!'' def'); + SELECT * FROM "%%^^%%"; +} {xx * 1 1 xx 0 1 1 yy * 1 1 yy 1 1 1} + + +finish_test |