wasCSharpSQLite – Blame information for rev
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2011 January 19 |
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 | # This file implements tests for SQLite library. The focus of the tests |
||
13 | # in this file is the use of the sqlite_stat2 histogram data on tables |
||
14 | # with many repeated values and only a few distinct values. |
||
15 | # |
||
16 | |||
17 | set testdir [file dirname $argv0] |
||
18 | source $testdir/tester.tcl |
||
19 | |||
20 | ifcapable !stat2 { |
||
21 | finish_test |
||
22 | return |
||
23 | } |
||
24 | |||
25 | set testprefix analyze5 |
||
26 | do_not_use_codec |
||
27 | |||
28 | proc eqp {sql {db db}} { |
||
29 | uplevel execsql [list "EXPLAIN QUERY PLAN $sql"] $db |
||
30 | } |
||
31 | |||
32 | unset -nocomplain i t u v w x y z |
||
33 | do_test analyze5-1.0 { |
||
34 | db eval {CREATE TABLE t1(t,u,v TEXT COLLATE nocase,w,x,y,z)} |
||
35 | for {set i [expr 0]} {$i < 1000} {incr i} { |
||
36 | set y [expr {$i>=25 && $i<=50}] |
||
37 | set z [expr {($i>=400) + ($i>=700) + ($i>=875)}] |
||
38 | set x $z |
||
39 | set w $z |
||
40 | set t [expr {$z+0.5}] |
||
41 | switch $z { |
||
42 | |||
43 | 1 {set u "bravo"} |
||
44 | 2 {set u "charlie"} |
||
45 | 3 {set u "delta"; unset w} |
||
46 | } |
||
47 | if {$i%2} {set v $u} {set v [string toupper $u]} |
||
48 | db eval {INSERT INTO t1 VALUES($t,$u,$v,$w,$x,$y,$z)} |
||
49 | } |
||
50 | db eval { |
||
51 | CREATE INDEX t1t ON t1(t); -- 0.5, 1.5, 2.5, and 3.5 |
||
52 | CREATE INDEX t1u ON t1(u); -- text |
||
53 | CREATE INDEX t1v ON t1(v); -- mixed case text |
||
54 | CREATE INDEX t1w ON t1(w); -- integers 0, 1, 2 and a few NULLs |
||
55 | CREATE INDEX t1x ON t1(x); -- integers 1, 2, 3 and many NULLs |
||
56 | CREATE INDEX t1y ON t1(y); -- integers 0 and very few 1s |
||
57 | CREATE INDEX t1z ON t1(z); -- integers 0, 1, 2, and 3 |
||
58 | ANALYZE; |
||
59 | SELECT sample FROM sqlite_stat2 WHERE idx='t1u' ORDER BY sampleno; |
||
60 | } |
||
61 | } {alpha alpha alpha alpha bravo bravo bravo charlie charlie delta} |
||
62 | do_test analyze5-1.1 { |
||
63 | string tolower \ |
||
64 | [db eval {SELECT sample from sqlite_stat2 WHERE idx='t1v' ORDER BY sampleno}] |
||
65 | } {alpha alpha alpha alpha bravo bravo bravo charlie charlie delta} |
||
66 | do_test analyze5-1.2 { |
||
67 | db eval {SELECT sample from sqlite_stat2 WHERE idx='t1w' ORDER BY sampleno} |
||
68 | } {{} 0 0 0 0 1 1 1 2 2} |
||
69 | do_test analyze5-1.3 { |
||
70 | db eval {SELECT sample from sqlite_stat2 WHERE idx='t1x' ORDER BY sampleno} |
||
71 | } {{} {} {} {} 1 1 1 2 2 3} |
||
72 | do_test analyze5-1.4 { |
||
73 | db eval {SELECT sample from sqlite_stat2 WHERE idx='t1y' ORDER BY sampleno} |
||
74 | } {0 0 0 0 0 0 0 0 0 0} |
||
75 | do_test analyze5-1.5 { |
||
76 | db eval {SELECT sample from sqlite_stat2 WHERE idx='t1z' ORDER BY sampleno} |
||
77 | } {0 0 0 0 1 1 1 2 2 3} |
||
78 | do_test analyze5-1.6 { |
||
79 | db eval {SELECT sample from sqlite_stat2 WHERE idx='t1t' ORDER BY sampleno} |
||
80 | } {0.5 0.5 0.5 0.5 1.5 1.5 1.5 2.5 2.5 3.5} |
||
81 | |||
82 | |||
83 | # Verify that range queries generate the correct row count estimates |
||
84 | # |
||
85 | foreach {testid where index rows} { |
||
86 | 1 {z>=0 AND z<=0} t1z 400 |
||
87 | 2 {z>=1 AND z<=1} t1z 300 |
||
88 | 3 {z>=2 AND z<=2} t1z 200 |
||
89 | 4 {z>=3 AND z<=3} t1z 100 |
||
90 | 5 {z>=4 AND z<=4} t1z 50 |
||
91 | 6 {z>=-1 AND z<=-1} t1z 50 |
||
92 | 7 {z>1 AND z<3} t1z 200 |
||
93 | 8 {z>0 AND z<100} t1z 600 |
||
94 | 9 {z>=1 AND z<100} t1z 600 |
||
95 | 10 {z>1 AND z<100} t1z 300 |
||
96 | 11 {z>=2 AND z<100} t1z 300 |
||
97 | 12 {z>2 AND z<100} t1z 100 |
||
98 | 13 {z>=3 AND z<100} t1z 100 |
||
99 | 14 {z>3 AND z<100} t1z 50 |
||
100 | 15 {z>=4 AND z<100} t1z 50 |
||
101 | 16 {z>=-100 AND z<=-1} t1z 50 |
||
102 | 17 {z>=-100 AND z<=0} t1z 400 |
||
103 | 18 {z>=-100 AND z<0} t1z 50 |
||
104 | 19 {z>=-100 AND z<=1} t1z 700 |
||
105 | 20 {z>=-100 AND z<2} t1z 700 |
||
106 | 21 {z>=-100 AND z<=2} t1z 900 |
||
107 | 22 {z>=-100 AND z<3} t1z 900 |
||
108 | |||
109 | 31 {z>=0.0 AND z<=0.0} t1z 400 |
||
110 | 32 {z>=1.0 AND z<=1.0} t1z 300 |
||
111 | 33 {z>=2.0 AND z<=2.0} t1z 200 |
||
112 | 34 {z>=3.0 AND z<=3.0} t1z 100 |
||
113 | 35 {z>=4.0 AND z<=4.0} t1z 50 |
||
114 | 36 {z>=-1.0 AND z<=-1.0} t1z 50 |
||
115 | 37 {z>1.5 AND z<3.0} t1z 200 |
||
116 | 38 {z>0.5 AND z<100} t1z 600 |
||
117 | 39 {z>=1.0 AND z<100} t1z 600 |
||
118 | 40 {z>1.5 AND z<100} t1z 300 |
||
119 | 41 {z>=2.0 AND z<100} t1z 300 |
||
120 | 42 {z>2.1 AND z<100} t1z 100 |
||
121 | 43 {z>=3.0 AND z<100} t1z 100 |
||
122 | 44 {z>3.2 AND z<100} t1z 50 |
||
123 | 45 {z>=4.0 AND z<100} t1z 50 |
||
124 | 46 {z>=-100 AND z<=-1.0} t1z 50 |
||
125 | 47 {z>=-100 AND z<=0.0} t1z 400 |
||
126 | 48 {z>=-100 AND z<0.0} t1z 50 |
||
127 | 49 {z>=-100 AND z<=1.0} t1z 700 |
||
128 | 50 {z>=-100 AND z<2.0} t1z 700 |
||
129 | 51 {z>=-100 AND z<=2.0} t1z 900 |
||
130 | 52 {z>=-100 AND z<3.0} t1z 900 |
||
131 | |||
132 | 101 {z=-1} t1z 50 |
||
133 | 102 {z=0} t1z 400 |
||
134 | 103 {z=1} t1z 300 |
||
135 | 104 {z=2} t1z 200 |
||
136 | 105 {z=3} t1z 100 |
||
137 | 106 {z=4} t1z 50 |
||
138 | 107 {z=-10.0} t1z 50 |
||
139 | 108 {z=0.0} t1z 400 |
||
140 | 109 {z=1.0} t1z 300 |
||
141 | 110 {z=2.0} t1z 200 |
||
142 | 111 {z=3.0} t1z 100 |
||
143 | 112 {z=4.0} t1z 50 |
||
144 | 113 {z=1.5} t1z 50 |
||
145 | 114 {z=2.5} t1z 50 |
||
146 | |||
147 | 201 {z IN (-1)} t1z 50 |
||
148 | 202 {z IN (0)} t1z 400 |
||
149 | 203 {z IN (1)} t1z 300 |
||
150 | 204 {z IN (2)} t1z 200 |
||
151 | 205 {z IN (3)} t1z 100 |
||
152 | 206 {z IN (4)} t1z 50 |
||
153 | 207 {z IN (0.5)} t1z 50 |
||
154 | 208 {z IN (0,1)} t1z 700 |
||
155 | 209 {z IN (0,1,2)} t1z 900 |
||
156 | 210 {z IN (0,1,2,3)} {} 100 |
||
157 | 211 {z IN (0,1,2,3,4,5)} {} 100 |
||
158 | 212 {z IN (1,2)} t1z 500 |
||
159 | 213 {z IN (2,3)} t1z 300 |
||
160 | 214 {z=3 OR z=2} t1z 300 |
||
161 | 215 {z IN (-1,3)} t1z 150 |
||
162 | 216 {z=-1 OR z=3} t1z 150 |
||
163 | |||
164 | 300 {y=0} {} 100 |
||
165 | 301 {y=1} t1y 50 |
||
166 | 302 {y=0.1} t1y 50 |
||
167 | |||
168 | 400 {x IS NULL} t1x 400 |
||
169 | |||
170 | } { |
||
171 | # Verify that the expected index is used with the expected row count |
||
172 | do_test analyze5-1.${testid}a { |
||
173 | set x [lindex [eqp "SELECT * FROM t1 WHERE $where"] 3] |
||
174 | set idx {} |
||
175 | regexp {INDEX (t1.) } $x all idx |
||
176 | regexp {~([0-9]+) rows} $x all nrow |
||
177 | list $idx $nrow |
||
178 | } [list $index $rows] |
||
179 | |||
180 | # Verify that the same result is achieved regardless of whether or not |
||
181 | # the index is used |
||
182 | do_test analyze5-1.${testid}b { |
||
183 | set w2 [string map {y +y z +z} $where] |
||
184 | set a1 [db eval "SELECT rowid FROM t1 NOT INDEXED WHERE $w2\ |
||
185 | ORDER BY +rowid"] |
||
186 | set a2 [db eval "SELECT rowid FROM t1 WHERE $where ORDER BY +rowid"] |
||
187 | if {$a1==$a2} { |
||
188 | set res ok |
||
189 | } else { |
||
190 | set res "a1=\[$a1\] a2=\[$a2\]" |
||
191 | } |
||
192 | set res |
||
193 | } {ok} |
||
194 | } |
||
195 | |||
196 | # Increase the number of NULLs in column x |
||
197 | # |
||
198 | db eval { |
||
199 | UPDATE t1 SET x=NULL; |
||
200 | UPDATE t1 SET x=rowid |
||
201 | WHERE rowid IN (SELECT rowid FROM t1 ORDER BY random() LIMIT 5); |
||
202 | ANALYZE; |
||
203 | } |
||
204 | |||
205 | # Verify that range queries generate the correct row count estimates |
||
206 | # |
||
207 | foreach {testid where index rows} { |
||
208 | 500 {x IS NULL AND u='charlie'} t1u 20 |
||
209 | 501 {x=1 AND u='charlie'} t1x 5 |
||
210 | 502 {x IS NULL} {} 100 |
||
211 | 503 {x=1} t1x 50 |
||
212 | 504 {x IS NOT NULL} t1x 25 |
||
213 | 505 {+x IS NOT NULL} {} 500 |
||
214 | 506 {upper(x) IS NOT NULL} {} 500 |
||
215 | |||
216 | } { |
||
217 | # Verify that the expected index is used with the expected row count |
||
218 | do_test analyze5-1.${testid}a { |
||
219 | set x [lindex [eqp "SELECT * FROM t1 WHERE $where"] 3] |
||
220 | set idx {} |
||
221 | regexp {INDEX (t1.) } $x all idx |
||
222 | regexp {~([0-9]+) rows} $x all nrow |
||
223 | list $idx $nrow |
||
224 | } [list $index $rows] |
||
225 | |||
226 | # Verify that the same result is achieved regardless of whether or not |
||
227 | # the index is used |
||
228 | do_test analyze5-1.${testid}b { |
||
229 | set w2 [string map {y +y z +z} $where] |
||
230 | set a1 [db eval "SELECT rowid FROM t1 NOT INDEXED WHERE $w2\ |
||
231 | ORDER BY +rowid"] |
||
232 | set a2 [db eval "SELECT rowid FROM t1 WHERE $where ORDER BY +rowid"] |
||
233 | if {$a1==$a2} { |
||
234 | set res ok |
||
235 | } else { |
||
236 | set res "a1=\[$a1\] a2=\[$a2\]" |
||
237 | } |
||
238 | set res |
||
239 | } {ok} |
||
240 | } |
||
241 | |||
242 | finish_test |