1# 2011 January 27 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 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17ifcapable !fts3 { finish_test ; return } 18set ::testprefix fts3aux1 19 20do_execsql_test 1.1 { 21 CREATE VIRTUAL TABLE t1 USING fts4; 22 INSERT INTO t1 VALUES('one two three four'); 23 INSERT INTO t1 VALUES('three four five six'); 24 INSERT INTO t1 VALUES('one three five seven'); 25 26 CREATE VIRTUAL TABLE terms USING fts4aux(t1); 27 SELECT term, documents, occurrences FROM terms WHERE col = '*'; 28} { 29 five 2 2 four 2 2 one 2 2 seven 1 1 30 six 1 1 three 3 3 two 1 1 31} 32 33do_execsql_test 1.2 { 34 INSERT INTO t1 VALUES('one one one three three three'); 35 SELECT term, documents, occurrences FROM terms WHERE col = '*'; 36} { 37 five 2 2 four 2 2 one 3 5 seven 1 1 38 six 1 1 three 4 6 two 1 1 39} 40 41do_execsql_test 1.3.1 { DELETE FROM t1; } 42do_execsql_test 1.3.2 { 43 SELECT term, documents, occurrences FROM terms WHERE col = '*'; 44} 45 46do_execsql_test 1.4 { 47 INSERT INTO t1 VALUES('a b a b a b a'); 48 INSERT INTO t1 SELECT * FROM t1; 49 INSERT INTO t1 SELECT * FROM t1; 50 INSERT INTO t1 SELECT * FROM t1; 51 INSERT INTO t1 SELECT * FROM t1; 52 INSERT INTO t1 SELECT * FROM t1; 53 INSERT INTO t1 SELECT * FROM t1; 54 INSERT INTO t1 SELECT * FROM t1; 55 INSERT INTO t1 SELECT * FROM t1; 56 SELECT term, documents, occurrences FROM terms WHERE col = '*'; 57} {a 256 1024 b 256 768} 58 59#------------------------------------------------------------------------- 60# The following tests verify that the fts4aux module uses the full-text 61# index to reduce the number of rows scanned in the following circumstances: 62# 63# * when there is equality comparison against the term column using the 64# BINARY collating sequence. 65# 66# * when there is a range constraint on the term column using the BINARY 67# collating sequence. 68# 69# And also uses the full-text index to optimize ORDER BY clauses of the 70# form "ORDER BY term ASC" or equivalent. 71# 72# Test organization is: 73# 74# fts3aux1-2.1.*: equality constraints. 75# fts3aux1-2.2.*: range constraints. 76# fts3aux1-2.3.*: ORDER BY optimization. 77# 78 79do_execsql_test 2.0 { 80 DROP TABLE t1; 81 DROP TABLE terms; 82 83 CREATE VIRTUAL TABLE x1 USING fts4(x); 84 INSERT INTO x1(x1) VALUES('nodesize=24'); 85 CREATE VIRTUAL TABLE terms USING fts4aux(x1); 86 87 CREATE VIEW terms_v AS 88 SELECT term, documents, occurrences FROM terms WHERE col = '*'; 89 90 INSERT INTO x1 VALUES('braes brag bragged bragger bragging'); 91 INSERT INTO x1 VALUES('brags braid braided braiding braids'); 92 INSERT INTO x1 VALUES('brain brainchild brained braining brains'); 93 INSERT INTO x1 VALUES('brainstem brainstems brainstorm brainstorms'); 94} 95 96proc rec {varname x} { 97 global $varname 98 incr $varname 99 return 1 100} 101db func rec rec 102 103# Use EQP to show that the WHERE expression "term='braid'" uses a different 104# index number (1) than "+term='braid'" (0). 105# 106do_execsql_test 2.1.1.1 { 107 EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term='braid' 108} {/*SCAN TABLE terms VIRTUAL TABLE INDEX 1:*/} 109do_execsql_test 2.1.1.2 { 110 EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term='braid' 111} {/*SCAN TABLE terms VIRTUAL TABLE INDEX 0:*/} 112 113# Now show that using "term='braid'" means the virtual table returns 114# only 1 row to SQLite, but "+term='braid'" means all 19 are returned. 115# 116do_test 2.1.2.1 { 117 set cnt 0 118 execsql { SELECT * FROM terms_v WHERE rec('cnt', term) AND term='braid' } 119 set cnt 120} {1} 121do_test 2.1.2.2 { 122 set cnt 0 123 execsql { SELECT * FROM terms_v WHERE rec('cnt', term) AND +term='braid' } 124 set cnt 125} {19} 126 127# Similar to the test immediately above, but using a term ("breakfast") that 128# is not featured in the dataset. 129# 130do_test 2.1.3.1 { 131 set cnt 0 132 execsql { SELECT * FROM terms_v WHERE rec('cnt', term) AND term='breakfast' } 133 set cnt 134} {0} 135do_test 2.1.3.2 { 136 set cnt 0 137 execsql { SELECT * FROM terms_v WHERE rec('cnt', term) AND +term='breakfast' } 138 set cnt 139} {19} 140 141do_execsql_test 2.1.4.1 { SELECT * FROM terms_v WHERE term='braid' } {braid 1 1} 142do_execsql_test 2.1.4.2 { SELECT * FROM terms_v WHERE +term='braid'} {braid 1 1} 143do_execsql_test 2.1.4.3 { SELECT * FROM terms_v WHERE term='breakfast' } {} 144do_execsql_test 2.1.4.4 { SELECT * FROM terms_v WHERE +term='breakfast' } {} 145 146do_execsql_test 2.1.4.5 { SELECT * FROM terms_v WHERE term='cba' } {} 147do_execsql_test 2.1.4.6 { SELECT * FROM terms_v WHERE +term='cba' } {} 148do_execsql_test 2.1.4.7 { SELECT * FROM terms_v WHERE term='abc' } {} 149do_execsql_test 2.1.4.8 { SELECT * FROM terms_v WHERE +term='abc' } {} 150 151# Special case: term=NULL 152# 153do_execsql_test 2.1.5 { SELECT * FROM terms WHERE term=NULL } {} 154 155do_execsql_test 2.2.1.1 { 156 EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term>'brain' 157} {/*SCAN TABLE terms VIRTUAL TABLE INDEX 2:*/} 158do_execsql_test 2.2.1.2 { 159 EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term>'brain' 160} {/*SCAN TABLE terms VIRTUAL TABLE INDEX 0:*/} 161 162do_execsql_test 2.2.1.3 { 163 EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term<'brain' 164} {/*SCAN TABLE terms VIRTUAL TABLE INDEX 4:*/} 165do_execsql_test 2.2.1.4 { 166 EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term<'brain' 167} {/*SCAN TABLE terms VIRTUAL TABLE INDEX 0:*/} 168 169do_execsql_test 2.2.1.5 { 170 EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term BETWEEN 'brags' AND 'brain' 171} {/*SCAN TABLE terms VIRTUAL TABLE INDEX 6:*/} 172do_execsql_test 2.2.1.6 { 173 EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term BETWEEN 'brags' AND 'brain' 174} {/*SCAN TABLE terms VIRTUAL TABLE INDEX 0:*/} 175 176do_test 2.2.2.1 { 177 set cnt 0 178 execsql { SELECT * FROM terms WHERE rec('cnt', term) AND term>'brain' } 179 set cnt 180} {18} 181do_test 2.2.2.2 { 182 set cnt 0 183 execsql { SELECT * FROM terms WHERE rec('cnt', term) AND +term>'brain' } 184 set cnt 185} {38} 186do_execsql_test 2.2.2.3 { 187 SELECT term, documents, occurrences FROM terms_v WHERE term>'brain' 188} { 189 brainchild 1 1 brained 1 1 braining 1 1 brains 1 1 190 brainstem 1 1 brainstems 1 1 brainstorm 1 1 brainstorms 1 1 191} 192do_execsql_test 2.2.2.4 { 193 SELECT term, documents, occurrences FROM terms_v WHERE +term>'brain' 194} { 195 brainchild 1 1 brained 1 1 braining 1 1 brains 1 1 196 brainstem 1 1 brainstems 1 1 brainstorm 1 1 brainstorms 1 1 197} 198do_execsql_test 2.2.2.5 { 199 SELECT term, documents, occurrences FROM terms_v WHERE term>='brain' 200} { 201 brain 1 1 202 brainchild 1 1 brained 1 1 braining 1 1 brains 1 1 203 brainstem 1 1 brainstems 1 1 brainstorm 1 1 brainstorms 1 1 204} 205do_execsql_test 2.2.2.6 { 206 SELECT term, documents, occurrences FROM terms_v WHERE +term>='brain' 207} { 208 brain 1 1 209 brainchild 1 1 brained 1 1 braining 1 1 brains 1 1 210 brainstem 1 1 brainstems 1 1 brainstorm 1 1 brainstorms 1 1 211} 212 213do_execsql_test 2.2.2.7 { 214 SELECT term, documents, occurrences FROM terms_v WHERE term>='abc' 215} { 216 braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 217 bragging 1 1 brags 1 1 braid 1 1 braided 1 1 218 braiding 1 1 braids 1 1 brain 1 1 brainchild 1 1 219 brained 1 1 braining 1 1 brains 1 1 brainstem 1 1 220 brainstems 1 1 brainstorm 1 1 brainstorms 1 1 221} 222do_execsql_test 2.2.2.8 { 223 SELECT term, documents, occurrences FROM terms_v WHERE +term>='abc' 224} { 225 braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 226 bragging 1 1 brags 1 1 braid 1 1 braided 1 1 227 braiding 1 1 braids 1 1 brain 1 1 brainchild 1 1 228 brained 1 1 braining 1 1 brains 1 1 brainstem 1 1 229 brainstems 1 1 brainstorm 1 1 brainstorms 1 1 230} 231 232do_execsql_test 2.2.2.9 { 233 SELECT term, documents, occurrences FROM terms_v WHERE term>='brainstorms' 234} {brainstorms 1 1} 235do_execsql_test 2.2.2.10 { 236 SELECT term, documents, occurrences FROM terms_v WHERE term>='brainstorms' 237} {brainstorms 1 1} 238do_execsql_test 2.2.2.11 { SELECT * FROM terms_v WHERE term>'brainstorms' } {} 239do_execsql_test 2.2.2.12 { SELECT * FROM terms_v WHERE term>'brainstorms' } {} 240 241do_execsql_test 2.2.2.13 { SELECT * FROM terms_v WHERE term>'cba' } {} 242do_execsql_test 2.2.2.14 { SELECT * FROM terms_v WHERE term>'cba' } {} 243 244do_test 2.2.3.1 { 245 set cnt 0 246 execsql { SELECT * FROM terms WHERE rec('cnt', term) AND term<'brain' } 247 set cnt 248} {22} 249do_test 2.2.3.2 { 250 set cnt 0 251 execsql { SELECT * FROM terms WHERE rec('cnt', term) AND +term<'brain' } 252 set cnt 253} {38} 254do_execsql_test 2.2.3.3 { 255 SELECT term, documents, occurrences FROM terms_v WHERE term<'brain' 256} { 257 braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 bragging 1 1 258 brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1 259} 260do_execsql_test 2.2.3.4 { 261 SELECT term, documents, occurrences FROM terms_v WHERE +term<'brain' 262} { 263 braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 bragging 1 1 264 brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1 265} 266do_execsql_test 2.2.3.5 { 267 SELECT term, documents, occurrences FROM terms_v WHERE term<='brain' 268} { 269 braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 bragging 1 1 270 brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1 271 brain 1 1 272} 273do_execsql_test 2.2.3.6 { 274 SELECT term, documents, occurrences FROM terms_v WHERE +term<='brain' 275} { 276 braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 bragging 1 1 277 brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1 278 brain 1 1 279} 280 281do_test 2.2.4.1 { 282 set cnt 0 283 execsql { 284 SELECT term, documents, occurrences FROM terms 285 WHERE rec('cnt', term) AND term BETWEEN 'brags' AND 'brain' 286 } 287 set cnt 288} {12} 289do_test 2.2.4.2 { 290 set cnt 0 291 execsql { 292 SELECT term, documents, occurrences FROM terms 293 WHERE rec('cnt', term) AND +term BETWEEN 'brags' AND 'brain' 294 } 295 set cnt 296} {38} 297do_execsql_test 2.2.4.3 { 298 SELECT term, documents, occurrences FROM terms_v 299 WHERE rec('cnt', term) AND term BETWEEN 'brags' AND 'brain' 300} { 301 brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1 brain 1 1 302} 303do_execsql_test 2.2.4.4 { 304 SELECT term, documents, occurrences FROM terms_v 305 WHERE rec('cnt', term) AND +term BETWEEN 'brags' AND 'brain' 306} { 307 brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1 brain 1 1 308} 309do_execsql_test 2.2.4.5 { 310 SELECT term, documents, occurrences FROM terms_v 311 WHERE rec('cnt', term) AND term > 'brags' AND term < 'brain' 312} { 313 braid 1 1 braided 1 1 braiding 1 1 braids 1 1 314} 315do_execsql_test 2.2.4.6 { 316 SELECT term, documents, occurrences FROM terms_v 317 WHERE rec('cnt', term) AND +term > 'brags' AND +term < 'brain' 318} { 319 braid 1 1 braided 1 1 braiding 1 1 braids 1 1 320} 321 322# Check that "ORDER BY term ASC" and equivalents are sorted by the 323# virtual table implementation. Any other ORDER BY clause requires 324# SQLite to sort results using a temporary b-tree. 325# 326foreach {tn sort orderby} { 327 1 0 "ORDER BY term ASC" 328 2 0 "ORDER BY term" 329 3 1 "ORDER BY term DESC" 330 4 1 "ORDER BY documents ASC" 331 5 1 "ORDER BY documents" 332 6 1 "ORDER BY documents DESC" 333 7 1 "ORDER BY occurrences ASC" 334 8 1 "ORDER BY occurrences" 335 9 1 "ORDER BY occurrences DESC" 336} { 337 338 set res {SCAN TABLE terms VIRTUAL TABLE INDEX 0:} 339 if {$sort} { append res {*USE TEMP B-TREE FOR ORDER BY} } 340 set res "/*$res*/" 341 342 set sql "SELECT * FROM terms $orderby" 343 do_execsql_test 2.3.1.$tn "EXPLAIN QUERY PLAN $sql" $res 344} 345 346#------------------------------------------------------------------------- 347# The next set of tests, fts3aux1-3.*, test error conditions in the 348# fts4aux module. Except, fault injection testing (OOM, IO error etc.) is 349# done in fts3fault2.test 350# 351 352do_execsql_test 3.1.1 { 353 CREATE VIRTUAL TABLE t2 USING fts4; 354} 355 356do_catchsql_test 3.1.2 { 357 CREATE VIRTUAL TABLE terms2 USING fts4aux; 358} {1 {invalid arguments to fts4aux constructor}} 359do_catchsql_test 3.1.3 { 360 CREATE VIRTUAL TABLE terms2 USING fts4aux(t2, t2); 361} {1 {invalid arguments to fts4aux constructor}} 362 363do_execsql_test 3.2.1 { 364 CREATE VIRTUAL TABLE terms3 USING fts4aux(does_not_exist) 365} 366do_catchsql_test 3.2.2 { 367 SELECT * FROM terms3 368} {1 {SQL logic error}} 369do_catchsql_test 3.2.3 { 370 SELECT * FROM terms3 WHERE term = 'abc' 371} {1 {SQL logic error}} 372 373do_catchsql_test 3.3.1 { 374 INSERT INTO terms VALUES(1,2,3); 375} {1 {table terms may not be modified}} 376do_catchsql_test 3.3.2 { 377 DELETE FROM terms 378} {1 {table terms may not be modified}} 379do_catchsql_test 3.3.3 { 380 UPDATE terms set documents = documents+1; 381} {1 {table terms may not be modified}} 382 383 384#------------------------------------------------------------------------- 385# The following tests - fts4aux-4.* - test that joins work with fts4aux 386# tables. And that fts4aux provides reasonably sane cost information via 387# xBestIndex to the query planner. 388# 389db close 390forcedelete test.db 391sqlite3 db test.db 392do_execsql_test 4.1 { 393 CREATE VIRTUAL TABLE x1 USING fts4(x); 394 CREATE VIRTUAL TABLE terms USING fts4aux(x1); 395 CREATE TABLE x2(y); 396 CREATE TABLE x3(y); 397 CREATE INDEX i1 ON x3(y); 398 399 INSERT INTO x1 VALUES('a b c d e'); 400 INSERT INTO x1 VALUES('f g h i j'); 401 INSERT INTO x1 VALUES('k k l l a'); 402 403 INSERT INTO x2 SELECT term FROM terms WHERE col = '*'; 404 INSERT INTO x3 SELECT term FROM terms WHERE col = '*'; 405} 406 407proc do_plansql_test {tn sql r1 r2} { 408 do_eqp_test $tn.eqp $sql $r1 409 do_execsql_test $tn $sql $r2 410} 411 412do_plansql_test 4.2 { 413 SELECT y FROM x2, terms WHERE y = term AND col = '*' 414} { 415 QUERY PLAN 416 |--SCAN TABLE x2 417 `--SCAN TABLE terms VIRTUAL TABLE INDEX 1: 418} { 419 a b c d e f g h i j k l 420} 421 422do_plansql_test 4.3 { 423 SELECT y FROM terms, x2 WHERE y = term AND col = '*' 424} { 425 QUERY PLAN 426 |--SCAN TABLE x2 427 `--SCAN TABLE terms VIRTUAL TABLE INDEX 1: 428} { 429 a b c d e f g h i j k l 430} 431 432do_plansql_test 4.4 { 433 SELECT y FROM x3, terms WHERE y = term AND col = '*' 434} { 435 QUERY PLAN 436 |--SCAN TABLE terms VIRTUAL TABLE INDEX 0: 437 `--SEARCH TABLE x3 USING COVERING INDEX i1 (y=?) 438} { 439 a b c d e f g h i j k l 440} 441 442do_plansql_test 4.5 { 443 SELECT y FROM terms, x3 WHERE y = term AND occurrences>1 AND col = '*' 444} { 445 QUERY PLAN 446 |--SCAN TABLE terms VIRTUAL TABLE INDEX 0: 447 `--SEARCH TABLE x3 USING COVERING INDEX i1 (y=?) 448} { 449 a k l 450} 451 452#------------------------------------------------------------------------- 453# The following tests check that fts4aux can handle an fts table with an 454# odd name (one that requires quoting for use in SQL statements). And that 455# the argument to the fts4aux constructor is properly dequoted before use. 456# 457do_execsql_test 5.1 { 458 CREATE VIRTUAL TABLE "abc '!' def" USING fts4(x, y); 459 INSERT INTO "abc '!' def" VALUES('XX', 'YY'); 460 461 CREATE VIRTUAL TABLE terms3 USING fts4aux("abc '!' def"); 462 SELECT * FROM terms3; 463} {xx * 1 1 xx 0 1 1 yy * 1 1 yy 1 1 1} 464 465do_execsql_test 5.2 { 466 CREATE VIRTUAL TABLE "%%^^%%" USING fts4aux('abc ''!'' def'); 467 SELECT * FROM "%%^^%%"; 468} {xx * 1 1 xx 0 1 1 yy * 1 1 yy 1 1 1} 469 470#------------------------------------------------------------------------- 471# Test that we can create an fts4aux table in the temp database. 472# 473forcedelete test.db2 474do_execsql_test 6.1 { 475 CREATE VIRTUAL TABLE ft1 USING fts4(x, y); 476 INSERT INTO ft1 VALUES('a b', 'c d'); 477 INSERT INTO ft1 VALUES('e e', 'c d'); 478 INSERT INTO ft1 VALUES('a a', 'b b'); 479 CREATE VIRTUAL TABLE temp.aux1 USING fts4aux(main, ft1); 480 SELECT * FROM aux1; 481} { 482 a * 2 3 a 0 2 3 483 b * 2 3 b 0 1 1 b 1 1 2 484 c * 2 2 c 1 2 2 485 d * 2 2 d 1 2 2 486 e * 1 2 e 0 1 2 487} 488 489do_execsql_test 6.2 { 490 ATTACH 'test.db2' AS att; 491 CREATE VIRTUAL TABLE att.ft1 USING fts4(x, y); 492 INSERT INTO att.ft1 VALUES('v w', 'x y'); 493 INSERT INTO att.ft1 VALUES('z z', 'x y'); 494 INSERT INTO att.ft1 VALUES('v v', 'w w'); 495 CREATE VIRTUAL TABLE temp.aux2 USING fts4aux(att, ft1); 496 SELECT * FROM aux2; 497} { 498 v * 2 3 v 0 2 3 499 w * 2 3 w 0 1 1 w 1 1 2 500 x * 2 2 x 1 2 2 501 y * 2 2 y 1 2 2 502 z * 1 2 z 0 1 2 503} 504 505foreach {tn q res1 res2} { 506 1 { SELECT * FROM %%% WHERE term = 'a' } {a * 2 3 a 0 2 3} {} 507 2 { SELECT * FROM %%% WHERE term = 'x' } {} {x * 2 2 x 1 2 2} 508 509 3 { SELECT * FROM %%% WHERE term >= 'y' } 510 {} {y * 2 2 y 1 2 2 z * 1 2 z 0 1 2} 511 512 4 { SELECT * FROM %%% WHERE term <= 'c' } 513 {a * 2 3 a 0 2 3 b * 2 3 b 0 1 1 b 1 1 2 c * 2 2 c 1 2 2} {} 514} { 515 set sql1 [string map {%%% aux1} $q] 516 set sql2 [string map {%%% aux2} $q] 517 518 do_execsql_test 7.$tn.1 $sql1 $res1 519 do_execsql_test 7.$tn.2 $sql2 $res2 520} 521 522do_test 8.1 { 523 catchsql { CREATE VIRTUAL TABLE att.aux3 USING fts4aux(main, ft1) } 524} {1 {invalid arguments to fts4aux constructor}} 525 526do_test 8.2 { 527 execsql {DETACH att} 528 catchsql { SELECT * FROM aux2 } 529} {1 {SQL logic error}} 530 531finish_test 532