1# 2014 June 17 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 FTS5 module. 13# 14# TESTRUNNER: slow 15 16source [file join [file dirname [info script]] fts5_common.tcl] 17set testprefix fts5ah 18 19# If SQLITE_ENABLE_FTS5 is defined, omit this file. 20ifcapable !fts5 { 21 finish_test 22 return 23} 24 25foreach_detail_mode $testprefix { 26 27#------------------------------------------------------------------------- 28# This file contains tests for very large doclists. 29# 30 31set Y [list] 32set W [list] 33do_test 1.0 { 34 execsql { CREATE VIRTUAL TABLE t1 USING fts5(a, detail=%DETAIL%) } 35 execsql { INSERT INTO t1(t1, rank) VALUES('pgsz', 128) } 36 set v {w w w w w w w w w w w w w w w w w w w w} 37 execsql { INSERT INTO t1(rowid, a) VALUES(0, $v) } 38 for {set i 1} {$i <= 10000} {incr i} { 39 set v {x x x x x x x x x x x x x x x x x x x x} 40 if {($i % 2139)==0} {lset v 3 Y ; lappend Y $i} 41 if {($i % 1577)==0} {lset v 5 W ; lappend W $i} 42 execsql { INSERT INTO t1 VALUES($v) } 43 } 44 set v {w w w w w w w w w w w w w w w w w w w w} 45 execsql { INSERT INTO t1 VALUES($v) } 46} {} 47 48do_execsql_test 1.1.1 { 49 SELECT rowid FROM t1 WHERE t1 MATCH 'x AND w' 50} [lsort -integer -incr $W] 51 52do_execsql_test 1.1.2 { 53 SELECT rowid FROM t1 WHERE t1 MATCH 'x* AND w*' 54} [lsort -integer -incr $W] 55 56do_execsql_test 1.2 { 57 SELECT rowid FROM t1 WHERE t1 MATCH 'y AND x' 58} [lsort -integer -incr $Y] 59 60do_execsql_test 1.3 { 61 INSERT INTO t1(t1) VALUES('integrity-check'); 62} 63 64proc reads {} { 65 db one {SELECT t1 FROM t1 WHERE t1 MATCH '*reads'} 66} 67 68proc execsql_reads {sql} { 69 set nRead [reads] 70 execsql $sql 71 expr [reads] - $nRead 72} 73 74do_test 1.4 { 75 set nRead [reads] 76 execsql { SELECT rowid FROM t1 WHERE t1 MATCH 'x' } 77 set nReadX [expr [reads] - $nRead] 78 #puts -nonewline "(nReadX=$nReadX)" 79 if {[detail_is_full]} { set expect 1000 } 80 if {[detail_is_col]} { set expect 250 } 81 if {[detail_is_none]} { set expect 80 } 82 83 expr $nReadX>$expect 84} {1} 85 86do_test 1.5 { 87 set fwd [execsql_reads {SELECT rowid FROM t1 WHERE t1 MATCH 'x' }] 88 set bwd [execsql_reads { 89 SELECT rowid FROM t1 WHERE t1 MATCH 'x' ORDER BY 1 ASC 90 }] 91 expr {$bwd < $fwd + 12} 92} {1} 93 94foreach {tn q res} " 95 1 { SELECT rowid FROM t1 WHERE t1 MATCH 'w + x' } [list $W] 96 2 { SELECT rowid FROM t1 WHERE t1 MATCH 'x + w' } [list $W] 97 3 { SELECT rowid FROM t1 WHERE t1 MATCH 'x AND w' } [list $W] 98 4 { SELECT rowid FROM t1 WHERE t1 MATCH 'y AND x' } [list $Y] 99" { 100 if {[detail_is_full]==0 && ($tn==1 || $tn==2)} continue 101 102 if {[detail_is_full]} { set ratio 8 } 103 if {[detail_is_col]} { set ratio 4 } 104 if {[detail_is_none]} { set ratio 2 } 105 106 do_test 1.6.$tn.1 { 107 set n [execsql_reads $q] 108 #puts -nonewline "(n=$n nReadX=$nReadX)" 109 expr {$n < ($nReadX / $ratio)} 110 } {1} 111 112 do_test 1.6.$tn.2 { 113 set n [execsql_reads "$q ORDER BY rowid DESC"] 114 #puts -nonewline "(n=$n nReadX=$nReadX)" 115 expr {$n < ($nReadX / $ratio)} 116 } {1} 117 118 do_execsql_test 1.6.$tn.3 $q [lsort -int -incr $res] 119 do_execsql_test 1.6.$tn.4 "$q ORDER BY rowid DESC" [lsort -int -decr $res] 120} 121 122#------------------------------------------------------------------------- 123# Now test that adding range constraints on the rowid field reduces the 124# number of pages loaded from disk. 125# 126foreach {tn fraction tail cnt} { 127 1 0.6 {rowid > 5000} 5000 128 2 0.2 {rowid > 9000} 1000 129 3 0.2 {rowid < 1000} 999 130 4 0.2 {rowid BETWEEN 4000 AND 5000} 1001 131 5 0.6 {rowid >= 5000} 5001 132 6 0.2 {rowid >= 9000} 1001 133 7 0.2 {rowid <= 1000} 1000 134 8 0.6 {rowid > '5000'} 5000 135 9 0.2 {rowid > '9000'} 1000 136 10 0.1 {rowid = 444} 1 137} { 138 set q "SELECT rowid FROM t1 WHERE t1 MATCH 'x' AND $tail" 139 set n [execsql_reads $q] 140 set ret [llength [execsql $q]] 141 142 # Because the position lists for 'x' are quite long in this db, the 143 # advantage is a bit smaller in detail=none mode. Update $fraction to 144 # reflect this. 145 if {[detail_is_none] && $fraction<0.5} { set fraction [expr $fraction*2] } 146 147 do_test "1.7.$tn.asc.(n=$n ret=$ret)" { 148 expr {$n < ($fraction*$nReadX) && $ret==$cnt} 149 } {1} 150 151 set q "SELECT rowid FROM t1 WHERE t1 MATCH 'x' AND $tail ORDER BY rowid DESC" 152 set n [execsql_reads $q] 153 set ret [llength [execsql $q]] 154 do_test "1.7.$tn.desc.(n=$n ret=$ret)" { 155 expr {$n < 2*$fraction*$nReadX && $ret==$cnt} 156 } {1} 157} 158 159do_execsql_test 1.8.1 { 160 SELECT count(*) FROM t1 WHERE t1 MATCH 'x' AND +rowid < 'text'; 161} {10000} 162do_execsql_test 1.8.2 { 163 SELECT count(*) FROM t1 WHERE t1 MATCH 'x' AND rowid < 'text'; 164} {10000} 165 166} ;# foreach_detail_mode 167 168#db eval {SELECT rowid, fts5_decode(rowid, block) aS r FROM t1_data} {puts $r} 169 170finish_test 171