Annotation of embedaddon/sqlite3/test/fts3aux1.test, revision 1.1
1.1 ! misho 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:
! 15: set testdir [file dirname $argv0]
! 16: source $testdir/tester.tcl
! 17: ifcapable !fts3 { finish_test ; return }
! 18: set ::testprefix fts3aux1
! 19:
! 20: do_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:
! 33: do_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:
! 41: do_execsql_test 1.3.1 { DELETE FROM t1; }
! 42: do_execsql_test 1.3.2 {
! 43: SELECT term, documents, occurrences FROM terms WHERE col = '*';
! 44: }
! 45:
! 46: do_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:
! 79: do_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:
! 96: proc rec {varname x} {
! 97: global $varname
! 98: incr $varname
! 99: return 1
! 100: }
! 101: db 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: #
! 106: do_execsql_test 2.1.1.1 {
! 107: EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term='braid'
! 108: } { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~0 rows)} }
! 109: do_execsql_test 2.1.1.2 {
! 110: EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term='braid'
! 111: } {0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)}}
! 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: #
! 116: do_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: } {2}
! 121: do_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: } {38}
! 126:
! 127: # Similar to the test immediately above, but using a term ("breakfast") that
! 128: # is not featured in the dataset.
! 129: #
! 130: do_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}
! 135: do_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: } {38}
! 140:
! 141: do_execsql_test 2.1.4.1 { SELECT * FROM terms_v WHERE term='braid' } {braid 1 1}
! 142: do_execsql_test 2.1.4.2 { SELECT * FROM terms_v WHERE +term='braid'} {braid 1 1}
! 143: do_execsql_test 2.1.4.3 { SELECT * FROM terms_v WHERE term='breakfast' } {}
! 144: do_execsql_test 2.1.4.4 { SELECT * FROM terms_v WHERE +term='breakfast' } {}
! 145:
! 146: do_execsql_test 2.1.4.5 { SELECT * FROM terms_v WHERE term='cba' } {}
! 147: do_execsql_test 2.1.4.6 { SELECT * FROM terms_v WHERE +term='cba' } {}
! 148: do_execsql_test 2.1.4.7 { SELECT * FROM terms_v WHERE term='abc' } {}
! 149: do_execsql_test 2.1.4.8 { SELECT * FROM terms_v WHERE +term='abc' } {}
! 150:
! 151: # Special case: term=NULL
! 152: #
! 153: do_execsql_test 2.1.5 { SELECT * FROM terms WHERE term=NULL } {}
! 154:
! 155: do_execsql_test 2.2.1.1 {
! 156: EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term>'brain'
! 157: } { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 2: (~0 rows)} }
! 158: do_execsql_test 2.2.1.2 {
! 159: EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term>'brain'
! 160: } { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} }
! 161:
! 162: do_execsql_test 2.2.1.3 {
! 163: EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term<'brain'
! 164: } { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 4: (~0 rows)} }
! 165: do_execsql_test 2.2.1.4 {
! 166: EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term<'brain'
! 167: } { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} }
! 168:
! 169: do_execsql_test 2.2.1.5 {
! 170: EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term BETWEEN 'brags' AND 'brain'
! 171: } { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 6: (~0 rows)} }
! 172: do_execsql_test 2.2.1.6 {
! 173: EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term BETWEEN 'brags' AND 'brain'
! 174: } { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} }
! 175:
! 176: do_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}
! 181: do_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}
! 186: do_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: }
! 192: do_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: }
! 198: do_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: }
! 205: do_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:
! 213: do_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: }
! 222: do_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:
! 232: do_execsql_test 2.2.2.9 {
! 233: SELECT term, documents, occurrences FROM terms_v WHERE term>='brainstorms'
! 234: } {brainstorms 1 1}
! 235: do_execsql_test 2.2.2.10 {
! 236: SELECT term, documents, occurrences FROM terms_v WHERE term>='brainstorms'
! 237: } {brainstorms 1 1}
! 238: do_execsql_test 2.2.2.11 { SELECT * FROM terms_v WHERE term>'brainstorms' } {}
! 239: do_execsql_test 2.2.2.12 { SELECT * FROM terms_v WHERE term>'brainstorms' } {}
! 240:
! 241: do_execsql_test 2.2.2.13 { SELECT * FROM terms_v WHERE term>'cba' } {}
! 242: do_execsql_test 2.2.2.14 { SELECT * FROM terms_v WHERE term>'cba' } {}
! 243:
! 244: do_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}
! 249: do_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}
! 254: do_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: }
! 260: do_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: }
! 266: do_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: }
! 273: do_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:
! 281: do_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}
! 289: do_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}
! 297: do_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: }
! 303: do_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: }
! 309: do_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: }
! 315: do_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: #
! 326: foreach {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 [list 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)}]
! 339: if {$sort} { lappend res 0 0 0 {USE TEMP B-TREE FOR ORDER BY} }
! 340:
! 341: set sql "SELECT * FROM terms $orderby"
! 342: do_execsql_test 2.3.1.$tn "EXPLAIN QUERY PLAN $sql" $res
! 343: }
! 344:
! 345: #-------------------------------------------------------------------------
! 346: # The next set of tests, fts3aux1-3.*, test error conditions in the
! 347: # fts4aux module. Except, fault injection testing (OOM, IO error etc.) is
! 348: # done in fts3fault2.test
! 349: #
! 350:
! 351: do_execsql_test 3.1.1 {
! 352: CREATE VIRTUAL TABLE t2 USING fts4;
! 353: }
! 354:
! 355: do_catchsql_test 3.1.2 {
! 356: CREATE VIRTUAL TABLE terms2 USING fts4aux;
! 357: } {1 {wrong number of arguments to fts4aux constructor}}
! 358: do_catchsql_test 3.1.3 {
! 359: CREATE VIRTUAL TABLE terms2 USING fts4aux(t2, t2);
! 360: } {1 {wrong number of arguments to fts4aux constructor}}
! 361:
! 362: do_execsql_test 3.2.1 {
! 363: CREATE VIRTUAL TABLE terms3 USING fts4aux(does_not_exist)
! 364: }
! 365: do_catchsql_test 3.2.2 {
! 366: SELECT * FROM terms3
! 367: } {1 {SQL logic error or missing database}}
! 368: do_catchsql_test 3.2.3 {
! 369: SELECT * FROM terms3 WHERE term = 'abc'
! 370: } {1 {SQL logic error or missing database}}
! 371:
! 372: do_catchsql_test 3.3.1 {
! 373: INSERT INTO terms VALUES(1,2,3);
! 374: } {1 {table terms may not be modified}}
! 375: do_catchsql_test 3.3.2 {
! 376: DELETE FROM terms
! 377: } {1 {table terms may not be modified}}
! 378: do_catchsql_test 3.3.3 {
! 379: UPDATE terms set documents = documents+1;
! 380: } {1 {table terms may not be modified}}
! 381:
! 382:
! 383: #-------------------------------------------------------------------------
! 384: # The following tests - fts4aux-4.* - test that joins work with fts4aux
! 385: # tables. And that fts4aux provides reasonably sane cost information via
! 386: # xBestIndex to the query planner.
! 387: #
! 388: db close
! 389: forcedelete test.db
! 390: sqlite3 db test.db
! 391: do_execsql_test 4.1 {
! 392: CREATE VIRTUAL TABLE x1 USING fts4(x);
! 393: CREATE VIRTUAL TABLE terms USING fts4aux(x1);
! 394: CREATE TABLE x2(y);
! 395: CREATE TABLE x3(y);
! 396: CREATE INDEX i1 ON x3(y);
! 397:
! 398: INSERT INTO x1 VALUES('a b c d e');
! 399: INSERT INTO x1 VALUES('f g h i j');
! 400: INSERT INTO x1 VALUES('k k l l a');
! 401:
! 402: INSERT INTO x2 SELECT term FROM terms WHERE col = '*';
! 403: INSERT INTO x3 SELECT term FROM terms WHERE col = '*';
! 404: }
! 405:
! 406: proc do_plansql_test {tn sql r} {
! 407: uplevel do_execsql_test $tn [list "EXPLAIN QUERY PLAN $sql ; $sql"] [list $r]
! 408: }
! 409:
! 410: do_plansql_test 4.2 {
! 411: SELECT y FROM x2, terms WHERE y = term AND col = '*'
! 412: } {
! 413: 0 0 0 {SCAN TABLE x2 (~1000000 rows)}
! 414: 0 1 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~0 rows)}
! 415: a b c d e f g h i j k l
! 416: }
! 417:
! 418: do_plansql_test 4.3 {
! 419: SELECT y FROM terms, x2 WHERE y = term AND col = '*'
! 420: } {
! 421: 0 0 1 {SCAN TABLE x2 (~1000000 rows)}
! 422: 0 1 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~0 rows)}
! 423: a b c d e f g h i j k l
! 424: }
! 425:
! 426: do_plansql_test 4.4 {
! 427: SELECT y FROM x3, terms WHERE y = term AND col = '*'
! 428: } {
! 429: 0 0 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)}
! 430: 0 1 0 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?) (~10 rows)}
! 431: a b c d e f g h i j k l
! 432: }
! 433:
! 434: do_plansql_test 4.5 {
! 435: SELECT y FROM terms, x3 WHERE y = term AND occurrences>1 AND col = '*'
! 436: } {
! 437: 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)}
! 438: 0 1 1 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?) (~10 rows)}
! 439: a k l
! 440: }
! 441:
! 442: #-------------------------------------------------------------------------
! 443: # The following tests check that fts4aux can handle an fts table with an
! 444: # odd name (one that requires quoting for use in SQL statements). And that
! 445: # the argument to the fts4aux constructor is properly dequoted before use.
! 446: #
! 447: #
! 448: do_execsql_test 5.1 {
! 449: CREATE VIRTUAL TABLE "abc '!' def" USING fts4(x, y);
! 450: INSERT INTO "abc '!' def" VALUES('XX', 'YY');
! 451:
! 452: CREATE VIRTUAL TABLE terms3 USING fts4aux("abc '!' def");
! 453: SELECT * FROM terms3;
! 454: } {xx * 1 1 xx 0 1 1 yy * 1 1 yy 1 1 1}
! 455:
! 456: do_execsql_test 5.2 {
! 457: CREATE VIRTUAL TABLE "%%^^%%" USING fts4aux('abc ''!'' def');
! 458: SELECT * FROM "%%^^%%";
! 459: } {xx * 1 1 xx 0 1 1 yy * 1 1 yy 1 1 1}
! 460:
! 461:
! 462: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to fts3aux1.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test