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