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