wasCSharpSQLite – Blame information for rev

Subversion Repositories:
Rev:
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