Debian Codegraph @

test/fts3misc.test

raw
1 # 2017 March 22
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 #
14
15 set testdir [file dirname $argv0]
16 source $testdir/tester.tcl
17 set testprefix fts3misc
18
19 # If SQLITE_ENABLE_FTS3 is defined, omit this file.
20 ifcapable !fts3 {
21 finish_test
22 return
23 }
24
25 #-------------------------------------------------------------------------
26 # A self-join.
27 #
28 do_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
36 do_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 #
43 proc 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 res
50 }
51 proc 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 res
58 }
59 db func v1 v1
60 db func v2 v2
61
62 do_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<200
71 )
72 INSERT INTO t2(c198, c199) SELECT v1(i), v2(i) FROM data;
73 }
74 } {}
75 do_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 199
80 }
81 do_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 191
86 }
87 do_execsql_test 2.3 {
88 SELECT rowid FROM t2 WHERE t2 MATCH '"i h"'
89 } {
90 }
91 do_execsql_test 2.4 {
92 SELECT rowid FROM t2 WHERE t2 MATCH '"f e"'
93 } {
94 }
95 do_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 127
101 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 199
103 }
104
105 #-------------------------------------------------------------------------
106 # Range constraints on the docid using non-integer values.
107 #
108 do_execsql_test 2.6 {
109 SELECT rowid FROM t2 WHERE t2 MATCH 'e' AND rowid BETWEEN NULL AND 45;
110 } {}
111 do_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 48
116 }
117 do_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 48
122 }
123
124 #-------------------------------------------------------------------------
125 # Phrase query tests.
126 #
127 do_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 }
134 do_execsql_test 3.1.2 {
135 SELECT * FROM t3 WHERE t3 MATCH '"a b x y"' ORDER BY docid DESC
136 }
137 do_execsql_test 3.1.3 {
138 SELECT * FROM t3 WHERE t3 MATCH '"a b c" OR "a b x y"' ORDER BY docid DESC
139 } {{a b c}}
140 do_execsql_test 3.1.4 {
141 SELECT * FROM t3 WHERE t3 MATCH '"a* b* x* a*"'
142 }
143 do_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 #
149 reset_db
150 ifcapable 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=0
162 } {X'C03EC0B204C0A608'}
163 sqlite3_db_config db DEFENSIVE 0
164 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 #
180 reset_db
181 do_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 }
187 do_execsql_test 5.1 {
188 SELECT rowid FROM t5 WHERE t5 MATCH 'a NEAR/4 b NEAR/4 c'
189 } {1 2 3}
190 do_execsql_test 5.2 {
191 SELECT rowid FROM t5 WHERE t5 MATCH 'a NEAR/3 b NEAR/4 c'
192 } {}
193 do_execsql_test 5.3 {
194 SELECT rowid FROM t5 WHERE t5 MATCH 'a NEAR/4 b NEAR/3 c'
195 } {}
196 do_execsql_test 5.4 {
197 SELECT rowid FROM t5 WHERE t5 MATCH 'y NEAR/4 b NEAR/4 c'
198 } {}
199 do_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}
202 do_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 #
208 reset_db
209 do_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 }
225 do_execsql_test 6.1 {
226 SELECT rowid FROM t6 WHERE t6 MATCH 'b OR "x a"'
227 } {50001 50002 50003 50004}
228
229 #-------------------------------------------------------------------------
230 #
231 reset_db
232 do_execsql_test 7.0 {
233 CREATE VIRTUAL TABLE vt0 USING fts3(c0);
234 INSERT INTO vt0 VALUES (x'00');
235 }
236 do_execsql_test 7.1 {
237 INSERT INTO vt0(vt0) VALUES('integrity-check');
238 }
239
240 #-------------------------------------------------------------------------
241 # Ticket [8a6fa2bb].
242 #
243 reset_db
244 do_execsql_test 7.0.1 {
245 CREATE VIRTUAL TABLE vt0 USING fts4(c0, order=DESC);
246 INSERT INTO vt0(c0) VALUES (0), (0);
247 }
248 do_execsql_test 7.0.2 {
249 INSERT INTO vt0(vt0) VALUES('integrity-check');
250 }
251 reset_db
252 do_execsql_test 7.1.1 {
253 CREATE VIRTUAL TABLE vt0 USING fts4(c0, order=ASC);
254 INSERT INTO vt0(c0) VALUES (0), (0);
255 }
256 do_execsql_test 7.1.2 {
257 INSERT INTO vt0(vt0) VALUES('integrity-check');
258 }
259 do_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 }
265 do_execsql_test 7.2.2 {
266 INSERT INTO vt0(vt0) VALUES('integrity-check');
267 }
268
269 #-------------------------------------------------------------------------
270 # Ticket [745f1abc].
271 #
272 reset_db
273 do_execsql_test 8.1 {
274 CREATE VIRTUAL TABLE vt0 USING fts4(c0, prefix=1);
275 }
276 do_execsql_test 8.2 {
277 BEGIN;
278 INSERT INTO vt0 VALUES (0);
279 INSERT INTO vt0(vt0) VALUES('optimize');
280 COMMIT;
281 }
282 do_execsql_test 8.3 {
283 INSERT INTO vt0(vt0) VALUES('integrity-check');
284 }
285
286 #-------------------------------------------------------------------------
287 #
288 reset_db
289 do_execsql_test 9.0 {
290 CREATE VIRTUAL TABLE t1 using fts4(mailcontent);
291 insert into t1(rowid, mailcontent) values
292 (-4764623217061966105, 'we are going to upgrade'),
293 (8324454597464624651, 'we are going to upgrade');
294 }
295
296 do_execsql_test 9.1 {
297 INSERT INTO t1(t1) VALUES('integrity-check');
298 }
299
300 do_execsql_test 9.2 {
301 SELECT rowid FROM t1 WHERE t1 MATCH 'upgrade';
302 } {
303 -4764623217061966105 8324454597464624651
304 }
305
306 #-------------------------------------------------------------------------
307 reset_db
308 do_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
314 do_catchsql_test 10.1 {
315 INSERT INTO f(f) VALUES ('merge=69,59');
316 } {1 {database disk image is malformed}}
317
318 #-------------------------------------------------------------------------
319 do_execsql_test 11.0 {
320 CREATE VIRTUAL TABLE xyz USING fts3();
321 }
322 do_execsql_test 11.1 {
323 SELECT * FROM xyz WHERE xyz MATCH 'a NEAR/4294836224 a';
324 }
325
326 finish_test