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