AryaWu/sqlite
0
1# 2017 March 222#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.tcl17set testprefix fts3misc18 19# If SQLITE_ENABLE_FTS3 is defined, omit this file.20ifcapable !fts3 {21 finish_test22 return23}24 25#-------------------------------------------------------------------------26# A self-join.27#28do_execsql_test 1.0 {29 CREATE VIRTUAL TABLE t1 USING fts3(a, b);30 INSERT INTO t1 VALUES('one', 'i');31 INSERT INTO t1 VALUES('one', 'ii');32 INSERT INTO t1 VALUES('two', 'i');33 INSERT INTO t1 VALUES('two', 'ii');34}35 36do_execsql_test 1.1 {37 SELECT a.a, b.b FROM t1 a, t1 b WHERE a.t1 MATCH 'two' AND b.t1 MATCH 'i'38} {two i two i two i two i}39 40#-------------------------------------------------------------------------41# FTS tables with 128 or more columns.42#43proc v1 {v} {44 set vector [list a b c d e f g h]45 set res [list]46 for {set i 0} {$i<8} {incr i} {47 if {$v & (1 << $i)} { lappend res [lindex $vector $i] }48 }49 set res50}51proc v2 {v} {52 set vector [list d e f g h i j k]53 set res [list]54 for {set i 0} {$i<8} {incr i} {55 if {$v & (1 << $i)} { lappend res [lindex $vector $i] }56 }57 set res58}59db func v1 v160db func v2 v261 62do_test 2.0 {63 set cols [list]64 for {set i 0} {$i<200} {incr i} {65 lappend cols "c$i"66 }67 execsql "CREATE VIRTUAL TABLE t2 USING fts3([join $cols ,])"68 execsql {69 WITH data(i) AS (70 SELECT 1 UNION ALL SELECT i+1 FROM data WHERE i<20071 )72 INSERT INTO t2(c198, c199) SELECT v1(i), v2(i) FROM data;73 }74} {}75do_execsql_test 2.1 {76 SELECT rowid FROM t2 WHERE t2 MATCH '"a b c"'77} {78 7 15 23 31 39 47 55 63 71 79 87 95 103 111 79 119 127 135 143 151 159 167 175 183 191 19980}81do_execsql_test 2.2 {82 SELECT rowid FROM t2 WHERE t2 MATCH '"g h i"'83} {84 56 57 58 59 60 61 62 63 120 121 122 123 124 85 125 126 127 184 185 186 187 188 189 190 19186}87do_execsql_test 2.3 {88 SELECT rowid FROM t2 WHERE t2 MATCH '"i h"'89} {90}91do_execsql_test 2.4 {92 SELECT rowid FROM t2 WHERE t2 MATCH '"f e"'93} {94}95do_execsql_test 2.5 {96 SELECT rowid FROM t2 WHERE t2 MATCH '"e f"'97} {98 6 7 14 15 22 23 30 31 38 39 46 47 48 49 50 51 52 53 54 55 56 99 57 58 59 60 61 62 63 70 71 78 79 86 87 94 95 102 103 110 100 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127101 134 135 142 143 150 151 158 159 166 167 174 175 176 177 178 179 180 102 181 182 183 184 185 186 187 188 189 190 191 198 199103}104 105#-------------------------------------------------------------------------106# Range constraints on the docid using non-integer values.107#108do_execsql_test 2.6 {109 SELECT rowid FROM t2 WHERE t2 MATCH 'e' AND rowid BETWEEN NULL AND 45;110} {}111do_execsql_test 2.7 {112 SELECT rowid FROM t2 WHERE t2 MATCH 'e' AND rowid BETWEEN 11.5 AND 48.2;113} {114 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 115 29 30 31 34 35 38 39 42 43 46 47 48116}117do_execsql_test 2.8 {118 SELECT rowid FROM t2 WHERE t2 MATCH 'e' AND rowid BETWEEN '11.5' AND '48.2';119} {120 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 121 29 30 31 34 35 38 39 42 43 46 47 48122}123 124#-------------------------------------------------------------------------125# Phrase query tests. 126#127do_execsql_test 3.1.1 {128 CREATE VIRTUAL TABLE t3 USING fts3;129 INSERT INTO t3 VALUES('a b c');130 INSERT INTO t3 VALUES('d e f');131 INSERT INTO t3 VALUES('a b d');132 INSERT INTO t3 VALUES('1 2 3 4 5 6 7 8 9 10 11');133}134do_execsql_test 3.1.2 {135 SELECT * FROM t3 WHERE t3 MATCH '"a b x y"' ORDER BY docid DESC136}137do_execsql_test 3.1.3 {138 SELECT * FROM t3 WHERE t3 MATCH '"a b c" OR "a b x y"' ORDER BY docid DESC139} {{a b c}}140do_execsql_test 3.1.4 {141 SELECT * FROM t3 WHERE t3 MATCH '"a* b* x* a*"'142}143do_execsql_test 3.1.5 {144 SELECT rowid FROM t3 WHERE t3 MATCH '"2 3 4 5 6 7 8 9"'145} {4}146 147#-------------------------------------------------------------------------148#149reset_db150ifcapable fts4_deferred {151 do_execsql_test 4.0 {152 PRAGMA page_size = 512;153 CREATE VIRTUAL TABLE t4 USING fts4;154 WITH s(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<8000 )155 INSERT INTO t4 SELECT 'a b c a b c a b c' FROM s;156 }157 do_execsql_test 4.1 {158 SELECT count(*) FROM t4 WHERE t4 MATCH '"a b c" OR "c a b"'159 } {8000}160 do_execsql_test 4.2 {161 SELECT quote(value) from t4_stat where id=0162 } {X'C03EC0B204C0A608'}163 sqlite3_db_config db DEFENSIVE 0164 do_execsql_test 4.3 {165 UPDATE t4_stat SET value = X'C03EC0B204C0A60800' WHERE id=0;166 }167 do_catchsql_test 4.4 {168 SELECT count(*) FROM t4 WHERE t4 MATCH '"a b c" OR "c a b"'169 } {1 {database disk image is malformed}}170 do_execsql_test 4.5 {171 UPDATE t4_stat SET value = X'00C03EC0B204C0A608' WHERE id=0;172 }173 do_catchsql_test 4.6 {174 SELECT count(*) FROM t4 WHERE t4 MATCH '"a b c" OR "c a b"'175 } {1 {database disk image is malformed}}176}177 178#-------------------------------------------------------------------------179#180reset_db181do_execsql_test 5.0 {182 CREATE VIRTUAL TABLE t5 USING fts4;183 INSERT INTO t5 VALUES('a x x x x b x x x x c');184 INSERT INTO t5 VALUES('a x x x x b x x x x c');185 INSERT INTO t5 VALUES('a x x x x b x x x x c');186}187do_execsql_test 5.1 {188 SELECT rowid FROM t5 WHERE t5 MATCH 'a NEAR/4 b NEAR/4 c'189} {1 2 3}190do_execsql_test 5.2 {191 SELECT rowid FROM t5 WHERE t5 MATCH 'a NEAR/3 b NEAR/4 c'192} {}193do_execsql_test 5.3 {194 SELECT rowid FROM t5 WHERE t5 MATCH 'a NEAR/4 b NEAR/3 c'195} {}196do_execsql_test 5.4 {197 SELECT rowid FROM t5 WHERE t5 MATCH 'y NEAR/4 b NEAR/4 c'198} {}199do_execsql_test 5.5 {200 SELECT rowid FROM t5 WHERE t5 MATCH 'x OR a NEAR/3 b NEAR/3 c'201} {1 2 3}202do_execsql_test 5.5 {203 SELECT rowid FROM t5 WHERE t5 MATCH 'x OR y NEAR/3 b NEAR/3 c'204} {1 2 3}205 206#-------------------------------------------------------------------------207#208reset_db209do_execsql_test 6.0 {210 CREATE VIRTUAL TABLE t6 USING fts4;211 212 BEGIN;213 WITH s(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<50000)214 INSERT INTO t6 SELECT 'x x x x x x x x x x x' FROM s;215 216 INSERT INTO t6 VALUES('x x x x x x x x x x x A');217 INSERT INTO t6 VALUES('x x x x x x x x x x x B');218 INSERT INTO t6 VALUES('x x x x x x x x x x x A');219 INSERT INTO t6 VALUES('x x x x x x x x x x x B');220 221 WITH s(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<50000)222 INSERT INTO t6 SELECT 'x x x x x x x x x x x' FROM s;223 COMMIT;224}225do_execsql_test 6.1 {226 SELECT rowid FROM t6 WHERE t6 MATCH 'b OR "x a"'227} {50001 50002 50003 50004}228 229#-------------------------------------------------------------------------230#231reset_db232do_execsql_test 7.0 {233 CREATE VIRTUAL TABLE vt0 USING fts3(c0);234 INSERT INTO vt0 VALUES (x'00');235}236do_execsql_test 7.1 {237 INSERT INTO vt0(vt0) VALUES('integrity-check');238}239 240#-------------------------------------------------------------------------241# Ticket [8a6fa2bb].242#243reset_db244do_execsql_test 7.0.1 {245 CREATE VIRTUAL TABLE vt0 USING fts4(c0, order=DESC);246 INSERT INTO vt0(c0) VALUES (0), (0);247}248do_execsql_test 7.0.2 {249 INSERT INTO vt0(vt0) VALUES('integrity-check');250}251reset_db252do_execsql_test 7.1.1 {253 CREATE VIRTUAL TABLE vt0 USING fts4(c0, order=ASC);254 INSERT INTO vt0(c0) VALUES (0), (0);255}256do_execsql_test 7.1.2 {257 INSERT INTO vt0(vt0) VALUES('integrity-check');258}259do_execsql_test 7.2.1 {260 CREATE VIRTUAL TABLE ft USING fts4(c0, c1, order=DESC, prefix=1);261 INSERT INTO ft VALUES('a b c d', 'hello world');262 INSERT INTO ft VALUES('negative', 'positive');263 INSERT INTO ft VALUES('hello world', 'a b c d');264}265do_execsql_test 7.2.2 {266 INSERT INTO vt0(vt0) VALUES('integrity-check');267}268 269#-------------------------------------------------------------------------270# Ticket [745f1abc].271#272reset_db273do_execsql_test 8.1 {274 CREATE VIRTUAL TABLE vt0 USING fts4(c0, prefix=1);275}276do_execsql_test 8.2 {277 BEGIN;278 INSERT INTO vt0 VALUES (0);279 INSERT INTO vt0(vt0) VALUES('optimize');280 COMMIT;281}282do_execsql_test 8.3 {283 INSERT INTO vt0(vt0) VALUES('integrity-check');284}285 286#-------------------------------------------------------------------------287#288reset_db289do_execsql_test 9.0 {290 CREATE VIRTUAL TABLE t1 using fts4(mailcontent);291 insert into t1(rowid, mailcontent) values292 (-4764623217061966105, 'we are going to upgrade'),293 (8324454597464624651, 'we are going to upgrade');294}295 296do_execsql_test 9.1 {297 INSERT INTO t1(t1) VALUES('integrity-check');298}299 300do_execsql_test 9.2 {301 SELECT rowid FROM t1 WHERE t1 MATCH 'upgrade';302} {303 -4764623217061966105 8324454597464624651304}305 306#-------------------------------------------------------------------------307reset_db308do_execsql_test 10.0 {309 CREATE VIRTUAL TABLE f USING fts3(a,b);310 CREATE TABLE 'f_stat'(id INTEGER PRIMARY KEY, value BLOB);311 INSERT INTO f_stat VALUES (1,x'3b3b3b3b3b3b3b28ffffffffffffffffff1807f9073481f1d43bc93b3b3b3b3b3b3b3b3b3b18073b3b3b3b3b3b3b9b003b');312} {}313 314do_catchsql_test 10.1 {315 INSERT INTO f(f) VALUES ('merge=69,59');316} {1 {database disk image is malformed}}317 318#-------------------------------------------------------------------------319do_execsql_test 11.0 {320 CREATE VIRTUAL TABLE xyz USING fts3();321}322do_execsql_test 11.1 {323 SELECT * FROM xyz WHERE xyz MATCH 'a NEAR/4294836224 a';324}325 326finish_test327