Debian Codegraph @

ext/fts5/test/fts5rank.test

raw
1 # 2014 Dec 20
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 #
12 # This file focuses on testing queries that use the "rank" column.
13 #
14
15 source [file join [file dirname [info script]] fts5_common.tcl]
16 set testprefix fts5rank
17
18 # If SQLITE_ENABLE_FTS5 is not defined, omit this file.
19 ifcapable !fts5 {
20 finish_test
21 return
22 }
23
24
25 #-------------------------------------------------------------------------
26 # "ORDER BY rank" + highlight() + large poslists.
27 #
28 do_execsql_test 1.0 {
29 CREATE VIRTUAL TABLE xyz USING fts5(z);
30 }
31 do_test 1.1 {
32 set doc [string trim [string repeat "x y " 500]]
33 execsql { INSERT INTO xyz VALUES($doc) }
34 } {}
35 do_execsql_test 1.2 {
36 SELECT highlight(xyz, 0, '[', ']') FROM xyz WHERE xyz MATCH 'x' ORDER BY rank
37 } [list [string map {x [x]} $doc]]
38
39 do_execsql_test 1.3 {
40 SELECT highlight(xyz, 0, '[', ']') FROM xyz
41 WHERE xyz MATCH 'x AND y' ORDER BY rank
42 } [list [string map {x [x] y [y]} $doc]]
43
44 #-------------------------------------------------------------------------
45 # Check that the 'rank' option really is persistent.
46 #
47 do_execsql_test 2.0 {
48 CREATE VIRTUAL TABLE tt USING fts5(a);
49 INSERT INTO tt VALUES('a x x x x');
50 INSERT INTO tt VALUES('x x a a a');
51 INSERT INTO tt VALUES('x a a x x');
52 }
53
54 proc firstinst {cmd} {
55 foreach {p c o} [$cmd xInst 0] {}
56 return $o
57 }
58 sqlite3_fts5_create_function db firstinst firstinst
59
60 do_execsql_test 2.1 {
61 SELECT rowid FROM tt('a') ORDER BY rank;
62 } {2 3 1}
63
64 do_execsql_test 2.2 {
65 SELECT rowid FROM tt('a', 'firstinst()') ORDER BY rank;
66 } {1 3 2}
67
68 do_execsql_test 2.3 {
69 INSERT INTO tt(tt, rank) VALUES('rank', 'firstinst()');
70 SELECT rowid FROM tt('a') ORDER BY rank;
71 } {1 3 2}
72
73 do_test 2.4 {
74 sqlite3 db2 test.db
75 catchsql { SELECT rowid FROM tt('a') ORDER BY rank; } db2
76 } {1 {no such function: firstinst}}
77
78 do_test 2.5 {
79 db2 close
80 sqlite3 db2 test.db
81 sqlite3_fts5_create_function db2 firstinst firstinst
82 execsql { SELECT rowid FROM tt('a') ORDER BY rank; } db2
83 } {1 3 2}
84
85 do_test 2.6 {
86 execsql { SELECT rowid FROM tt('a') ORDER BY rank; } db2
87 } {1 3 2}
88
89 do_test 2.7 {
90 execsql { SELECT rowid FROM tt('a') ORDER BY rank; } db
91 } {1 3 2}
92
93 db2 close
94
95 #--------------------------------------------------------------------------
96 # At one point there was a problem with queries such as:
97 #
98 # ... MATCH 'x OR y' ORDER BY rank;
99 #
100 # if there were zero occurrences of token 'y' in the dataset. The
101 # following tests verify that that problem has been addressed.
102 #
103 foreach_detail_mode $::testprefix {
104 do_execsql_test 3.1.0 {
105 CREATE VIRTUAL TABLE y1 USING fts5(z, detail=%DETAIL%);
106 INSERT INTO y1 VALUES('test xyz');
107 INSERT INTO y1 VALUES('test test xyz test');
108 INSERT INTO y1 VALUES('test test xyz');
109 }
110
111 do_execsql_test 3.1.1 {
112 SELECT rowid FROM y1('test OR tset');
113 } {1 2 3}
114
115 do_execsql_test 3.1.2 {
116 SELECT rowid FROM y1('test OR tset') ORDER BY bm25(y1)
117 } {2 3 1}
118
119 do_execsql_test 3.1.3 {
120 SELECT rowid FROM y1('test OR tset') ORDER BY +rank
121 } {2 3 1}
122
123 do_execsql_test 3.1.4 {
124 SELECT rowid FROM y1('test OR tset') ORDER BY rank
125 } {2 3 1}
126
127 do_execsql_test 3.1.5 {
128 SELECT rowid FROM y1('test OR xyz') ORDER BY rank
129 } {3 2 1}
130
131
132 do_execsql_test 3.2.1 {
133 CREATE VIRTUAL TABLE z1 USING fts5(a, detail=%DETAIL%);
134 INSERT INTO z1 VALUES('wrinkle in time');
135 SELECT * FROM z1 WHERE z1 MATCH 'wrinkle in time OR a wrinkle in time';
136 } {{wrinkle in time}}
137 }
138
139 do_execsql_test 4.1 {
140 DROP TABLE IF EXISTS VTest;
141 CREATE virtual TABLE VTest USING FTS5(
142 Title, AUthor, tokenize ='porter unicode61 remove_diacritics 1',
143 columnsize='1', detail=full
144 );
145 INSERT INTO VTest (Title, Author) VALUES ('wrinkle in time', 'Bill Smith');
146
147 SELECT * FROM VTest WHERE
148 VTest MATCH 'wrinkle in time OR a wrinkle in time' ORDER BY rank;
149 } {{wrinkle in time} {Bill Smith}}
150
151 #-------------------------------------------------------------------------
152 reset_db
153 do_execsql_test 5.0 {
154 CREATE VIRTUAL TABLE ttt USING fts5(a);
155 WITH s(i) AS (
156 SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<100
157 )
158 INSERT INTO ttt SELECT 'word ' || i FROM s;
159 }
160
161 do_execsql_test 5.1 {
162 SELECT rowid FROM ttt('word') WHERE rowid BETWEEN 30 AND 40 ORDER BY rank;
163 } {30 31 32 33 34 35 36 37 38 39 40}
164
165 #-------------------------------------------------------------------------
166 reset_db
167 do_execsql_test 6.0 {
168 CREATE VIRTUAL TABLE "My.Table" USING fts5(Text);
169
170 INSERT INTO "My.Table" VALUES ('hello this is a test');
171 INSERT INTO "My.Table" VALUES ('of trying to order by');
172 INSERT INTO "My.Table" VALUES ('rank on an fts5 table');
173 INSERT INTO "My.Table" VALUES ('that have periods in');
174 INSERT INTO "My.Table" VALUES ('the table names.');
175 INSERT INTO "My.Table" VALUES ('table table table');
176 }
177 do_execsql_test 6.1 {
178 SELECT * FROM "My.Table" WHERE Text MATCH 'table' ORDER BY rank;
179 } {
180 {table table table} {the table names.} {rank on an fts5 table}
181 }
182
183
184 #-------------------------------------------------------------------------
185 # forum post: https://sqlite.org/forum/forumpost/a2dd636330
186 #
187 reset_db
188 do_execsql_test 1.0 {
189 CREATE VIRTUAL TABLE t USING fts5 (a, b);
190 INSERT INTO t (a, b) VALUES ('data1', 'sentence1'), ('data2', 'sentence2');
191 INSERT INTO t(t, rank) VALUES ('rank', 'bm25(10.0,1.0)');
192 }
193
194 sqlite3 db2 test.db
195 do_execsql_test -db db2 1.1 {
196 SELECT *, rank<0.0 FROM t('data*') ORDER BY RANK;
197 } {data1 sentence1 1 data2 sentence2 1}
198
199 do_execsql_test 1.2 {
200 INSERT INTO t(t, rank) VALUES ('rank', 'bm25(10.0,1.0)');
201 }
202 do_execsql_test -db db2 1.3 {
203 SELECT *, rank<0.0 FROM t('data*') ORDER BY RANK;
204 } {data1 sentence1 1 data2 sentence2 1}
205 db2 close
206
207 finish_test