AryaWu/sqlite
0
1# 2011 January 192#3# The author disclaims copyright to this source code. In place of4# 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 tests13# in this file is the use of the sqlite_stat4 histogram data on tables14# with many repeated values and only a few distinct values.15#16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19 20ifcapable !stat4 {21 finish_test22 return23}24 25set testprefix analyze526 27proc eqp {sql {db db}} {28 uplevel execsql [list "EXPLAIN QUERY PLAN $sql"] $db29}30 31proc alpha {blob} {32 set ret ""33 foreach c [split $blob {}] {34 if {[string is alpha $c]} {append ret $c}35 }36 return $ret37}38db func alpha alpha39 40db func lindex lindex41 42unset -nocomplain i t u v w x y z43do_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 $z49 set w $z50 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.562 CREATE INDEX t1u ON t1(u); -- text63 CREATE INDEX t1v ON t1(v); -- mixed case text64 CREATE INDEX t1w ON t1(w); -- integers 0, 1, 2 and a few NULLs65 CREATE INDEX t1x ON t1(x); -- integers 1, 2, 3 and many NULLs66 CREATE INDEX t1y ON t1(y); -- integers 0 and very few 1s67 CREATE INDEX t1z ON t1(z); -- integers 0, 1, 2, and 368 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 76do_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 180 }81} {alpha bravo charlie delta}82do_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 estimates87#88foreach {testid where index rows} {89 1 {z>=0 AND z<=0} t1z 40090 2 {z>=1 AND z<=1} t1z 30091 3 {z>=2 AND z<=2} t1z 17592 4 {z>=3 AND z<=3} t1z 12593 5 {z>=4 AND z<=4} t1z 194 6 {z>=-1 AND z<=-1} t1z 195 7 {z>1 AND z<3} t1z 17596 8 {z>0 AND z<100} t1z 60097 9 {z>=1 AND z<100} t1z 60098 10 {z>1 AND z<100} t1z 30099 11 {z>=2 AND z<100} t1z 300100 12 {z>2 AND z<100} t1z 125101 13 {z>=3 AND z<100} t1z 125102 14 {z>3 AND z<100} t1z 1103 15 {z>=4 AND z<100} t1z 1104 16 {z>=-100 AND z<=-1} t1z 1105 17 {z>=-100 AND z<=0} t1z 400106 18 {z>=-100 AND z<0} t1z 1107 19 {z>=-100 AND z<=1} t1z 700108 20 {z>=-100 AND z<2} t1z 700109 21 {z>=-100 AND z<=2} t1z 875110 22 {z>=-100 AND z<3} t1z 875111 112 31 {z>=0.0 AND z<=0.0} t1z 400113 32 {z>=1.0 AND z<=1.0} t1z 300114 33 {z>=2.0 AND z<=2.0} t1z 175115 34 {z>=3.0 AND z<=3.0} t1z 125116 35 {z>=4.0 AND z<=4.0} t1z 1117 36 {z>=-1.0 AND z<=-1.0} t1z 1118 37 {z>1.5 AND z<3.0} t1z 174119 38 {z>0.5 AND z<100} t1z 599120 39 {z>=1.0 AND z<100} t1z 600121 40 {z>1.5 AND z<100} t1z 299122 41 {z>=2.0 AND z<100} t1z 300123 42 {z>2.1 AND z<100} t1z 124124 43 {z>=3.0 AND z<100} t1z 125125 44 {z>3.2 AND z<100} t1z 1126 45 {z>=4.0 AND z<100} t1z 1127 46 {z>=-100 AND z<=-1.0} t1z 1128 47 {z>=-100 AND z<=0.0} t1z 400129 48 {z>=-100 AND z<0.0} t1z 1130 49 {z>=-100 AND z<=1.0} t1z 700131 50 {z>=-100 AND z<2.0} t1z 700132 51 {z>=-100 AND z<=2.0} t1z 875133 52 {z>=-100 AND z<3.0} t1z 875134 135 101 {z=-1} t1z 1136 102 {z=0} t1z 400137 103 {z=1} t1z 300138 104 {z=2} t1z 175139 105 {z=3} t1z 125140 106 {z=4} t1z 1141 107 {z=-10.0} t1z 1142 108 {z=0.0} t1z 400143 109 {z=1.0} t1z 300144 110 {z=2.0} t1z 175145 111 {z=3.0} t1z 125146 112 {z=4.0} t1z 1147 113 {z=1.5} t1z 1148 114 {z=2.5} t1z 1149 150 201 {z IN (-1)} t1z 1151 202 {z IN (0)} t1z 400152 203 {z IN (1)} t1z 300153 204 {z IN (2)} t1z 175154 205 {z IN (3)} t1z 125155 206 {z IN (4)} t1z 1156 207 {z IN (0.5)} t1z 1157 208 {z IN (0,1)} t1z 700158 209 {z IN (0,1,2)} t1z 875159 210 {z IN (0,1,2,3)} {} 100160 211 {z IN (0,1,2,3,4,5)} {} 100161 212 {z IN (1,2)} t1z 475162 213 {z IN (2,3)} t1z 300163 214 {z=3 OR z=2} t1z 300164 215 {z IN (-1,3)} t1z 126165 216 {z=-1 OR z=3} t1z 126166 167 300 {y=0} t1y 974168 301 {y=1} t1y 26169 302 {y=0.1} t1y 1170 171 400 {x IS NULL} t1x 400172 173} {174 # Verify that the expected index is used with the expected row count175 # No longer valid due to an EXPLAIN QUERY PLAN output format change176 # 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 idx180 # regexp {~([0-9]+) rows} $x all nrow181 # list $idx $nrow182 # } [list $index $rows]183 184 # Verify that the same result is achieved regardless of whether or not185 # the index is used186 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 ok193 } else {194 set res "a1=\[$a1\] a2=\[$a2\]"195 }196 set res197 } {ok}198}199 200# Increase the number of NULLs in column x201#202db eval {203 UPDATE t1 SET x=NULL;204 UPDATE t1 SET x=rowid205 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 estimates210#211foreach {testid where index rows} {212 500 {x IS NULL AND u='charlie'} t1u 17213 501 {x=1 AND u='charlie'} t1x 1214 502 {x IS NULL} t1x 995215 503 {x=1} t1x 1216 504 {x IS NOT NULL} t1x 2217 505 {+x IS NOT NULL} {} 500218 506 {upper(x) IS NOT NULL} {} 500219 220} {221 # Verify that the expected index is used with the expected row count222 # No longer valid due to an EXPLAIN QUERY PLAN format change223 # 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 idx227 # regexp {~([0-9]+) rows} $x all nrow228 # list $idx $nrow229 # } [list $index $rows]230 231 # Verify that the same result is achieved regardless of whether or not232 # the index is used233 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 ok240 } else {241 set res "a1=\[$a1\] a2=\[$a2\]"242 }243 set res244 } {ok}245}246 247finish_test248