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