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