| 1 |
# 2015 Apr 24 |
| 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 |
# The tests in this file focus on testing the fts5vocab module. |
| 13 |
# |
| 14 |
|
| 15 |
source [file join [file dirname [info script]] fts5_common.tcl] |
| 16 |
set testprefix fts5vocab |
| 17 |
|
| 18 |
# If SQLITE_ENABLE_FTS5 is not defined, omit this file. |
| 19 |
ifcapable !fts5 { |
| 20 |
finish_test |
| 21 |
return |
| 22 |
} |
| 23 |
|
| 24 |
foreach_detail_mode $testprefix { |
| 25 |
|
| 26 |
proc null_list_entries {iFirst nInterval L} { |
| 27 |
for {set i $iFirst} {$i < [llength $L]} {incr i $nInterval} { |
| 28 |
lset L $i {} |
| 29 |
} |
| 30 |
return $L |
| 31 |
} |
| 32 |
|
| 33 |
proc star_from_row {L} { |
| 34 |
if {[detail_is_full]==0} { |
| 35 |
set L [null_list_entries 2 3 $L] |
| 36 |
} |
| 37 |
return $L |
| 38 |
} |
| 39 |
|
| 40 |
proc star_from_col {L} { |
| 41 |
if {[detail_is_col]} { |
| 42 |
set L [null_list_entries 3 4 $L] |
| 43 |
} |
| 44 |
if {[detail_is_none]} { |
| 45 |
set L [null_list_entries 1 4 $L] |
| 46 |
set L [null_list_entries 3 4 $L] |
| 47 |
} |
| 48 |
return $L |
| 49 |
} |
| 50 |
|
| 51 |
proc row_to_col {L} { |
| 52 |
if {[detail_is_none]==0} { error "this is for detail=none mode" } |
| 53 |
set ret [list] |
| 54 |
foreach {a b c} $L { |
| 55 |
lappend ret $a {} $b {} |
| 56 |
} |
| 57 |
set ret |
| 58 |
} |
| 59 |
|
| 60 |
if 1 { |
| 61 |
|
| 62 |
do_execsql_test 1.1.1 { |
| 63 |
CREATE VIRTUAL TABLE t1 USING fts5(one, prefix=1, detail=%DETAIL%); |
| 64 |
CREATE VIRTUAL TABLE v1 USING fts5vocab(t1, 'row'); |
| 65 |
PRAGMA table_info = v1; |
| 66 |
} { |
| 67 |
0 term {} 0 {} 0 |
| 68 |
1 doc {} 0 {} 0 |
| 69 |
2 cnt {} 0 {} 0 |
| 70 |
} |
| 71 |
|
| 72 |
do_execsql_test 1.1.2 { |
| 73 |
CREATE VIRTUAL TABLE v2 USING fts5vocab(t1, 'col'); |
| 74 |
PRAGMA table_info = v2; |
| 75 |
} { |
| 76 |
0 term {} 0 {} 0 |
| 77 |
1 col {} 0 {} 0 |
| 78 |
2 doc {} 0 {} 0 |
| 79 |
3 cnt {} 0 {} 0 |
| 80 |
} |
| 81 |
|
| 82 |
do_execsql_test 1.2.1 { SELECT * FROM v1 } {} |
| 83 |
do_execsql_test 1.2.2 { SELECT * FROM v2 } {} |
| 84 |
|
| 85 |
do_execsql_test 1.3 { |
| 86 |
INSERT INTO t1 VALUES('x y z'); |
| 87 |
INSERT INTO t1 VALUES('x x x'); |
| 88 |
} |
| 89 |
|
| 90 |
do_execsql_test 1.4.1 { |
| 91 |
SELECT * FROM v1; |
| 92 |
} [star_from_row {x 2 4 y 1 1 z 1 1}] |
| 93 |
|
| 94 |
do_execsql_test 1.4.2 { |
| 95 |
SELECT * FROM v2; |
| 96 |
} [star_from_col {x one 2 4 y one 1 1 z one 1 1}] |
| 97 |
|
| 98 |
do_execsql_test 1.5.1 { |
| 99 |
BEGIN; |
| 100 |
INSERT INTO t1 VALUES('a b c'); |
| 101 |
SELECT * FROM v1 WHERE term<'d'; |
| 102 |
} [star_from_row {a 1 1 b 1 1 c 1 1}] |
| 103 |
|
| 104 |
do_execsql_test 1.5.2 { |
| 105 |
SELECT * FROM v2 WHERE term<'d'; |
| 106 |
COMMIT; |
| 107 |
} [star_from_col {a one 1 1 b one 1 1 c one 1 1}] |
| 108 |
|
| 109 |
do_execsql_test 1.6 { |
| 110 |
DELETE FROM t1 WHERE one = 'a b c'; |
| 111 |
SELECT * FROM v1; |
| 112 |
} [star_from_row {x 2 4 y 1 1 z 1 1}] |
| 113 |
|
| 114 |
#------------------------------------------------------------------------- |
| 115 |
# |
| 116 |
do_execsql_test 2.0 { |
| 117 |
CREATE VIRTUAL TABLE tt USING fts5(a, b, detail=%DETAIL%); |
| 118 |
INSERT INTO tt VALUES('d g b f d f', 'f c e c d a'); |
| 119 |
INSERT INTO tt VALUES('f a e a a b', 'e d c f d d'); |
| 120 |
INSERT INTO tt VALUES('b c a a a b', 'f f c c b c'); |
| 121 |
INSERT INTO tt VALUES('f d c a c e', 'd g d e g d'); |
| 122 |
INSERT INTO tt VALUES('g d e f a g x', 'f f d a a b'); |
| 123 |
INSERT INTO tt VALUES('g c f b c g', 'a g f d c b'); |
| 124 |
INSERT INTO tt VALUES('c e c f g b', 'f e d b g a'); |
| 125 |
INSERT INTO tt VALUES('g d e f d e', 'a c d b a g'); |
| 126 |
INSERT INTO tt VALUES('e f a c c b', 'b f e a f d y'); |
| 127 |
INSERT INTO tt VALUES('c c a a c f', 'd g a e b g'); |
| 128 |
} |
| 129 |
|
| 130 |
set res_row [star_from_row { |
| 131 |
a 10 20 b 9 14 c 9 20 d 9 19 |
| 132 |
e 8 13 f 10 20 g 7 14 x 1 1 |
| 133 |
y 1 1 |
| 134 |
}] |
| 135 |
set res_col [star_from_col { |
| 136 |
a a 6 11 a b 7 9 |
| 137 |
b a 6 7 b b 7 7 |
| 138 |
c a 6 12 c b 5 8 |
| 139 |
d a 4 6 d b 9 13 |
| 140 |
e a 6 7 e b 6 6 |
| 141 |
f a 9 10 f b 7 10 |
| 142 |
g a 5 7 g b 5 7 |
| 143 |
x a 1 1 y b 1 1 |
| 144 |
}] |
| 145 |
if {[detail_is_none]} { |
| 146 |
set res_col [row_to_col $res_row] |
| 147 |
} |
| 148 |
|
| 149 |
foreach {tn tbl resname} { |
| 150 |
1 "fts5vocab(tt, 'col')" res_col |
| 151 |
2 "fts5vocab(tt, 'row')" res_row |
| 152 |
3 "fts5vocab(tt, \"row\")" res_row |
| 153 |
4 "fts5vocab(tt, [row])" res_row |
| 154 |
5 "fts5vocab(tt, `row`)" res_row |
| 155 |
|
| 156 |
6 "fts5vocab('tt', 'row')" res_row |
| 157 |
7 "fts5vocab(\"tt\", \"row\")" res_row |
| 158 |
8 "fts5vocab([tt], [row])" res_row |
| 159 |
9 "fts5vocab(`tt`, `row`)" res_row |
| 160 |
} { |
| 161 |
do_execsql_test 2.$tn " |
| 162 |
DROP TABLE IF EXISTS tv; |
| 163 |
CREATE VIRTUAL TABLE tv USING $tbl; |
| 164 |
SELECT * FROM tv; |
| 165 |
" [set $resname] |
| 166 |
} |
| 167 |
|
| 168 |
#------------------------------------------------------------------------- |
| 169 |
# Test errors in the CREATE VIRTUAL TABLE statement. |
| 170 |
# |
| 171 |
foreach {tn sql} { |
| 172 |
1 { CREATE VIRTUAL TABLE aa USING fts5vocab() } |
| 173 |
2 { CREATE VIRTUAL TABLE aa USING fts5vocab(x) } |
| 174 |
3 { CREATE VIRTUAL TABLE aa USING fts5vocab(x,y,z) } |
| 175 |
4 { CREATE VIRTUAL TABLE temp.aa USING fts5vocab(x,y,z,y) } |
| 176 |
} { |
| 177 |
do_catchsql_test 3.$tn $sql {1 {wrong number of vtable arguments}} |
| 178 |
} |
| 179 |
|
| 180 |
do_catchsql_test 4.0 { |
| 181 |
CREATE VIRTUAL TABLE cc USING fts5vocab(tbl, unknown); |
| 182 |
} {1 {fts5vocab: unknown table type: 'unknown'}} |
| 183 |
|
| 184 |
do_catchsql_test 4.1 { |
| 185 |
ATTACH 'test.db' AS aux; |
| 186 |
CREATE VIRTUAL TABLE aux.cc USING fts5vocab(main, tbl, row); |
| 187 |
} {1 {wrong number of vtable arguments}} |
| 188 |
|
| 189 |
#------------------------------------------------------------------------- |
| 190 |
# Test fts5vocab tables created in the temp schema. |
| 191 |
# |
| 192 |
reset_db |
| 193 |
forcedelete test.db2 |
| 194 |
do_execsql_test 5.0 { |
| 195 |
ATTACH 'test.db2' AS aux; |
| 196 |
CREATE VIRTUAL TABLE t1 USING fts5(x, detail=%DETAIL%); |
| 197 |
CREATE VIRTUAL TABLE temp.t1 USING fts5(x, detail=%DETAIL%); |
| 198 |
CREATE VIRTUAL TABLE aux.t1 USING fts5(x, detail=%DETAIL%); |
| 199 |
|
| 200 |
INSERT INTO main.t1 VALUES('a b c'); |
| 201 |
INSERT INTO main.t1 VALUES('d e f'); |
| 202 |
INSERT INTO main.t1 VALUES('a e c'); |
| 203 |
|
| 204 |
INSERT INTO temp.t1 VALUES('1 2 3'); |
| 205 |
INSERT INTO temp.t1 VALUES('4 5 6'); |
| 206 |
INSERT INTO temp.t1 VALUES('1 5 3'); |
| 207 |
|
| 208 |
INSERT INTO aux.t1 VALUES('x y z'); |
| 209 |
INSERT INTO aux.t1 VALUES('m n o'); |
| 210 |
INSERT INTO aux.t1 VALUES('x n z'); |
| 211 |
} |
| 212 |
|
| 213 |
do_execsql_test 5.1 { |
| 214 |
CREATE VIRTUAL TABLE temp.vm USING fts5vocab(main, t1, row); |
| 215 |
CREATE VIRTUAL TABLE temp.vt1 USING fts5vocab(t1, row); |
| 216 |
CREATE VIRTUAL TABLE temp.vt2 USING fts5vocab(temp, t1, row); |
| 217 |
CREATE VIRTUAL TABLE temp.va USING fts5vocab(aux, t1, row); |
| 218 |
} |
| 219 |
|
| 220 |
do_execsql_test 5.2 { SELECT * FROM vm } [star_from_row { |
| 221 |
a 2 2 b 1 1 c 2 2 d 1 1 e 2 2 f 1 1 |
| 222 |
}] |
| 223 |
do_execsql_test 5.3 { SELECT * FROM vt1 } [star_from_row { |
| 224 |
1 2 2 2 1 1 3 2 2 4 1 1 5 2 2 6 1 1 |
| 225 |
}] |
| 226 |
do_execsql_test 5.4 { SELECT * FROM vt2 } [star_from_row { |
| 227 |
1 2 2 2 1 1 3 2 2 4 1 1 5 2 2 6 1 1 |
| 228 |
}] |
| 229 |
do_execsql_test 5.5 { SELECT * FROM va } [star_from_row { |
| 230 |
m 1 1 n 2 2 o 1 1 x 2 2 y 1 1 z 2 2 |
| 231 |
}] |
| 232 |
|
| 233 |
#------------------------------------------------------------------------- |
| 234 |
# |
| 235 |
do_execsql_test 6.0 { |
| 236 |
CREATE TABLE iii(iii); |
| 237 |
CREATE TABLE jjj(x); |
| 238 |
} |
| 239 |
|
| 240 |
do_catchsql_test 6.1 { |
| 241 |
CREATE VIRTUAL TABLE vocab1 USING fts5vocab(iii, row); |
| 242 |
SELECT * FROM vocab1; |
| 243 |
} {1 {no such fts5 table: main.iii}} |
| 244 |
|
| 245 |
do_catchsql_test 6.2 { |
| 246 |
CREATE VIRTUAL TABLE vocab2 USING fts5vocab(jjj, row); |
| 247 |
SELECT * FROM vocab2; |
| 248 |
} {1 {no such fts5 table: main.jjj}} |
| 249 |
|
| 250 |
do_catchsql_test 6.2 { |
| 251 |
CREATE VIRTUAL TABLE vocab3 USING fts5vocab(lll, row); |
| 252 |
SELECT * FROM vocab3; |
| 253 |
} {1 {no such fts5 table: main.lll}} |
| 254 |
|
| 255 |
#------------------------------------------------------------------------- |
| 256 |
# Test single term queries on fts5vocab tables (i.e. those with term=? |
| 257 |
# constraints in the WHERE clause). |
| 258 |
# |
| 259 |
do_execsql_test 7.0 { |
| 260 |
CREATE VIRTUAL TABLE tx USING fts5(one, two, detail=%DETAIL%); |
| 261 |
INSERT INTO tx VALUES('g a ggg g a b eee', 'cc d aa ff g ee'); |
| 262 |
INSERT INTO tx VALUES('dd fff i a i jjj', 'f fff hh jj e f'); |
| 263 |
INSERT INTO tx VALUES('ggg a f f fff dd aa', 'd ggg f f j gg ddd'); |
| 264 |
INSERT INTO tx VALUES('e bb h jjj ii gg', 'e aa e f c fff'); |
| 265 |
INSERT INTO tx VALUES('j ff aa a h', 'h a j bbb bb'); |
| 266 |
INSERT INTO tx VALUES('cc i ff c d f', 'dd ii fff f c cc d'); |
| 267 |
INSERT INTO tx VALUES('jjj g i bb cc eee', 'hhh iii aaa b bbb aaa'); |
| 268 |
INSERT INTO tx VALUES('hhh hhh hhh bb fff f', 'fff gg aa ii h a'); |
| 269 |
INSERT INTO tx VALUES('b c cc aaa iii ggg f', 'iii ff ee a ff c cc'); |
| 270 |
INSERT INTO tx VALUES('hhh b hhh aaa j i i', 'dd ee ee aa bbb iii'); |
| 271 |
INSERT INTO tx VALUES('hh dd h b g ff i', 'ccc bb cc ccc f a d'); |
| 272 |
INSERT INTO tx VALUES('g d b ggg jj', 'fff jj ff jj g gg ee'); |
| 273 |
INSERT INTO tx VALUES('g ee ggg ggg cc bb eee', 'aa j jjj bbb dd eee ff'); |
| 274 |
INSERT INTO tx VALUES('c jjj hh ddd dd h', 'e aaa h jjj gg'); |
| 275 |
|
| 276 |
CREATE VIRTUAL TABLE txr USING fts5vocab(tx, row); |
| 277 |
CREATE VIRTUAL TABLE txc USING fts5vocab(tx, col); |
| 278 |
} |
| 279 |
|
| 280 |
proc cont {L elem} { |
| 281 |
set n 0 |
| 282 |
foreach e $L { if {$elem==$e} {incr n} } |
| 283 |
set n |
| 284 |
} |
| 285 |
db func cont cont |
| 286 |
|
| 287 |
foreach {term} { |
| 288 |
a aa aaa |
| 289 |
b bb bbb |
| 290 |
c cc ccc |
| 291 |
d dd ddd |
| 292 |
e ee eee |
| 293 |
f ff fff |
| 294 |
g gg ggg |
| 295 |
h hh hhh |
| 296 |
i ii iii |
| 297 |
j jj jjj |
| 298 |
} { |
| 299 |
set resr [db eval { |
| 300 |
SELECT $term, |
| 301 |
sum(cont(one || ' ' || two, $term) > 0), |
| 302 |
sum(cont(one || ' ' || two, $term)) |
| 303 |
FROM tx |
| 304 |
}] |
| 305 |
if {[lindex $resr 1]==0} {set resr [list]} |
| 306 |
|
| 307 |
set r1 [db eval { |
| 308 |
SELECT $term, 'one', sum(cont(one, $term)>0), sum(cont(one, $term)) FROM tx |
| 309 |
}] |
| 310 |
if {[lindex $r1 2]==0} {set r1 [list]} |
| 311 |
|
| 312 |
set r2 [db eval { |
| 313 |
SELECT $term, 'two', sum(cont(two, $term)>0), sum(cont(two, $term)) FROM tx |
| 314 |
}] |
| 315 |
if {[lindex $r2 2]==0} {set r2 [list]} |
| 316 |
|
| 317 |
set resc [concat $r1 $r2] |
| 318 |
|
| 319 |
set resc [star_from_col $resc] |
| 320 |
set resr [star_from_row $resr] |
| 321 |
if {[detail_is_none]} { set resc [row_to_col $resr] } |
| 322 |
do_execsql_test 7.$term.1 {SELECT * FROM txc WHERE term=$term} $resc |
| 323 |
do_execsql_test 7.$term.2 {SELECT * FROM txr WHERE term=$term} $resr |
| 324 |
} |
| 325 |
|
| 326 |
do_execsql_test 7.1 { |
| 327 |
CREATE TABLE txr_c AS SELECT * FROM txr; |
| 328 |
CREATE TABLE txc_c AS SELECT * FROM txc; |
| 329 |
} |
| 330 |
|
| 331 |
# Test range queries on the fts5vocab tables created above. |
| 332 |
# |
| 333 |
foreach {tn a b} { |
| 334 |
1 a jjj |
| 335 |
2 bb j |
| 336 |
3 ccc ddd |
| 337 |
4 dd xyz |
| 338 |
5 xzy dd |
| 339 |
6 h hh |
| 340 |
} { |
| 341 |
do_execsql_test 7.2.$tn.1 { |
| 342 |
SELECT * FROM txr WHERE term>=$a |
| 343 |
} [db eval {SELECT * FROM txr_c WHERE term>=$a}] |
| 344 |
do_execsql_test 7.2.$tn.2 { |
| 345 |
SELECT * FROM txr WHERE term<=$b |
| 346 |
} [db eval {SELECT * FROM txr_c WHERE term <=$b}] |
| 347 |
do_execsql_test 7.2.$tn.3 { |
| 348 |
SELECT * FROM txr WHERE term>=$a AND term<=$b |
| 349 |
} [db eval {SELECT * FROM txr_c WHERE term>=$a AND term <=$b}] |
| 350 |
|
| 351 |
do_execsql_test 7.2.$tn.4 { |
| 352 |
SELECT * FROM txc WHERE term>=$a |
| 353 |
} [db eval {SELECT * FROM txc_c WHERE term>=$a}] |
| 354 |
do_execsql_test 7.2.$tn.5 { |
| 355 |
SELECT * FROM txc WHERE term<=$b |
| 356 |
} [db eval {SELECT * FROM txc_c WHERE term <=$b}] |
| 357 |
do_execsql_test 7.2.$tn.6 { |
| 358 |
SELECT * FROM txc WHERE term>=$a AND term<=$b |
| 359 |
} [db eval {SELECT * FROM txc_c WHERE term>=$a AND term <=$b}] |
| 360 |
|
| 361 |
do_execsql_test 7.2.$tn.7 { |
| 362 |
SELECT * FROM txr WHERE term>$a |
| 363 |
} [db eval {SELECT * FROM txr_c WHERE term>$a}] |
| 364 |
do_execsql_test 7.2.$tn.8 { |
| 365 |
SELECT * FROM txr WHERE term<$b |
| 366 |
} [db eval {SELECT * FROM txr_c WHERE term<$b}] |
| 367 |
do_execsql_test 7.2.$tn.9 { |
| 368 |
SELECT * FROM txr WHERE term>$a AND term<$b |
| 369 |
} [db eval {SELECT * FROM txr_c WHERE term>$a AND term <$b}] |
| 370 |
|
| 371 |
do_execsql_test 7.2.$tn.10 { |
| 372 |
SELECT * FROM txc WHERE term>$a |
| 373 |
} [db eval {SELECT * FROM txc_c WHERE term>$a}] |
| 374 |
do_execsql_test 7.2.$tn.11 { |
| 375 |
SELECT * FROM txc WHERE term<$b |
| 376 |
} [db eval {SELECT * FROM txc_c WHERE term<$b}] |
| 377 |
do_execsql_test 7.2.$tn.12 { |
| 378 |
SELECT * FROM txc WHERE term>$a AND term<$b |
| 379 |
} [db eval {SELECT * FROM txc_c WHERE term>$a AND term <$b}] |
| 380 |
} |
| 381 |
|
| 382 |
do_execsql_test 7.3.1 { |
| 383 |
SELECT count(*) FROM txr, txr_c WHERE txr.term = txr_c.term; |
| 384 |
} {30} |
| 385 |
|
| 386 |
if {![detail_is_none]} { |
| 387 |
do_execsql_test 7.3.2 { |
| 388 |
SELECT count(*) FROM txc, txc_c |
| 389 |
WHERE txc.term = txc_c.term AND txc.col=txc_c.col; |
| 390 |
} {57} |
| 391 |
} |
| 392 |
|
| 393 |
} |
| 394 |
|
| 395 |
#------------------------------------------------------------------------- |
| 396 |
# Test the fts5vocab tables response to a specific types of corruption: |
| 397 |
# where the fts5 index contains hits for columns that do not exist. |
| 398 |
# |
| 399 |
do_execsql_test 8.0 { |
| 400 |
CREATE VIRTUAL TABLE x1 USING fts5(a, b, c, detail=%DETAIL%); |
| 401 |
INSERT INTO x1 VALUES('a b c', 'd e f', 'g h i'); |
| 402 |
INSERT INTO x1 VALUES('g h i', 'a b c', 'd e f'); |
| 403 |
INSERT INTO x1 VALUES('d e f', 'g h i', 'a b c'); |
| 404 |
CREATE VIRTUAL TABLE x1_r USING fts5vocab(x1, row); |
| 405 |
CREATE VIRTUAL TABLE x1_c USING fts5vocab(x1, col); |
| 406 |
} |
| 407 |
|
| 408 |
set resr [star_from_row {a 3 3 b 3 3 c 3 3 d 3 3 e 3 3 f 3 3 g 3 3 h 3 3 i 3 3}] |
| 409 |
set resc [star_from_col { |
| 410 |
a a 1 1 a b 1 1 a c 1 1 b a 1 1 |
| 411 |
b b 1 1 b c 1 1 c a 1 1 c b 1 1 |
| 412 |
c c 1 1 d a 1 1 d b 1 1 d c 1 1 |
| 413 |
e a 1 1 e b 1 1 e c 1 1 f a 1 1 |
| 414 |
f b 1 1 f c 1 1 g a 1 1 g b 1 1 |
| 415 |
g c 1 1 h a 1 1 h b 1 1 h c 1 1 |
| 416 |
i a 1 1 i b 1 1 i c 1 1 |
| 417 |
}] |
| 418 |
if {[detail_is_none]} { set resc [row_to_col $resr] } |
| 419 |
|
| 420 |
do_execsql_test 8.1.1 { SELECT * FROM x1_r; } $resr |
| 421 |
do_execsql_test 8.1.2 { SELECT * FROM x1_c } $resc |
| 422 |
|
| 423 |
sqlite3_db_config db DEFENSIVE 0 |
| 424 |
do_execsql_test 8.2 { |
| 425 |
PRAGMA writable_schema = 1; |
| 426 |
UPDATE sqlite_master |
| 427 |
SET sql = 'CREATE VIRTUAL TABLE x1 USING fts5(a, detail=%DETAIL%)' |
| 428 |
WHERE name = 'x1'; |
| 429 |
} |
| 430 |
db close |
| 431 |
sqlite3 db test.db |
| 432 |
sqlite3_fts5_may_be_corrupt 1 |
| 433 |
|
| 434 |
do_execsql_test 8.2.1 { SELECT * FROM x1_r } $resr |
| 435 |
|
| 436 |
if {[detail_is_none]} { |
| 437 |
do_execsql_test 8.2.2 { SELECT * FROM x1_c } $resc |
| 438 |
} else { |
| 439 |
do_catchsql_test 8.2.2 { |
| 440 |
SELECT * FROM x1_c |
| 441 |
} {1 {database disk image is malformed}} |
| 442 |
} |
| 443 |
|
| 444 |
sqlite3_fts5_may_be_corrupt 0 |
| 445 |
} |
| 446 |
|
| 447 |
#------------------------------------------------------------------------- |
| 448 |
# Test that both "ORDER BY term" and "ORDER BY term DESC" work. |
| 449 |
# |
| 450 |
reset_db |
| 451 |
do_execsql_test 9.1 { |
| 452 |
CREATE VIRTUAL TABLE x1 USING fts5(x); |
| 453 |
INSERT INTO x1 VALUES('def ABC ghi'); |
| 454 |
INSERT INTO x1 VALUES('DEF abc GHI'); |
| 455 |
} |
| 456 |
|
| 457 |
do_execsql_test 9.2 { |
| 458 |
CREATE VIRTUAL TABLE rrr USING fts5vocab(x1, row); |
| 459 |
SELECT * FROM rrr |
| 460 |
} { |
| 461 |
abc 2 2 def 2 2 ghi 2 2 |
| 462 |
} |
| 463 |
do_execsql_test 9.3 { |
| 464 |
SELECT * FROM rrr ORDER BY term ASC |
| 465 |
} { |
| 466 |
abc 2 2 def 2 2 ghi 2 2 |
| 467 |
} |
| 468 |
do_execsql_test 9.4 { |
| 469 |
SELECT * FROM rrr ORDER BY term DESC |
| 470 |
} { |
| 471 |
ghi 2 2 def 2 2 abc 2 2 |
| 472 |
} |
| 473 |
do_test 9.5 { |
| 474 |
set e2 [db eval { EXPLAIN SELECT * FROM rrr ORDER BY term ASC }] |
| 475 |
expr [lsearch $e2 SorterSort]<0 |
| 476 |
} 1 |
| 477 |
do_test 9.6 { |
| 478 |
set e2 [db eval { EXPLAIN SELECT * FROM rrr ORDER BY term DESC }] |
| 479 |
expr [lsearch $e2 SorterSort]<0 |
| 480 |
} 0 |
| 481 |
|
| 482 |
#------------------------------------------------------------------------- |
| 483 |
do_execsql_test 10.0 { |
| 484 |
CREATE VIRTUAL TABLE ft USING fts5(a, b, c); |
| 485 |
CREATE VIRTUAL TABLE t2 USING fts5vocab('ft','row'); |
| 486 |
CREATE VIRTUAL TABLE t3 USING fts5vocab('ft','row'); |
| 487 |
} |
| 488 |
|
| 489 |
do_execsql_test 10.1 { |
| 490 |
BEGIN; |
| 491 |
INSERT INTO ft(b) VALUES('x y'); |
| 492 |
} |
| 493 |
|
| 494 |
do_execsql_test 10.2 { |
| 495 |
SELECT t2.term FROM t2; |
| 496 |
} {x y} |
| 497 |
|
| 498 |
do_execsql_test 10.3 { |
| 499 |
SELECT t2.term, t3.term FROM t2, t3; |
| 500 |
} {x x x y y x y y} |
| 501 |
|
| 502 |
do_execsql_test 10.4 { |
| 503 |
COMMIT; |
| 504 |
} |
| 505 |
|
| 506 |
do_execsql_test 10.5 { |
| 507 |
BEGIN; |
| 508 |
INSERT INTO ft(a) VALUES('1 2 3'); |
| 509 |
INSERT INTO ft(a) VALUES('4 5 6'); |
| 510 |
INSERT INTO ft(a) VALUES('1 2 3'); |
| 511 |
INSERT INTO ft(a) VALUES('4 5 6'); |
| 512 |
INSERT INTO ft(a) VALUES('1 2 3'); |
| 513 |
INSERT INTO ft(a) VALUES('4 5 6'); |
| 514 |
} |
| 515 |
|
| 516 |
unset -nocomplain x res |
| 517 |
do_test 10.6 { |
| 518 |
set res [list] |
| 519 |
db eval { SELECT rowid FROM ft('4') } x { |
| 520 |
db eval { SELECT * FROM t2 } |
| 521 |
lappend res $x(rowid) |
| 522 |
} |
| 523 |
db eval COMMIT |
| 524 |
set res |
| 525 |
} {3 5 7} |
| 526 |
|
| 527 |
do_execsql_test 10.6.1 { |
| 528 |
SELECT * FROM t2 WHERE term<NULL; |
| 529 |
} |
| 530 |
do_execsql_test 10.6.2 { |
| 531 |
SELECT * FROM t2 WHERE term>NULL; |
| 532 |
} |
| 533 |
do_execsql_test 10.6.3 { |
| 534 |
SELECT * FROM t2 WHERE term=NULL; |
| 535 |
} |
| 536 |
do_execsql_test 10.7.1 { |
| 537 |
SELECT * FROM t2 WHERE term<?; |
| 538 |
} |
| 539 |
do_execsql_test 10.7.2 { |
| 540 |
SELECT * FROM t2 WHERE term>?; |
| 541 |
} |
| 542 |
do_execsql_test 10.7.3 { |
| 543 |
SELECT * FROM t2 WHERE term=?; |
| 544 |
} |
| 545 |
|
| 546 |
# 2020-02-16 Detect recursively define fts5vocab() tables. |
| 547 |
# Error found by dbsqlfuzz. |
| 548 |
# |
| 549 |
reset_db |
| 550 |
do_execsql_test 11.100 { |
| 551 |
CREATE VIRTUAL TABLE t3 USING fts5vocab(rowid , 'col'); |
| 552 |
CREATE VIRTUAL TABLE rowid USING fts5vocab(rowid , 'instance'); |
| 553 |
} {} |
| 554 |
do_catchsql_test 11.110 { |
| 555 |
SELECT rowid+1,rowid, * FROM t3 WHERE null>rowid ; |
| 556 |
} {1 {SQL logic error}} |
| 557 |
|
| 558 |
finish_test |