Annotation of embedaddon/sqlite3/test/analyze5.test, revision 1.1
1.1 ! misho 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:
! 17: set testdir [file dirname $argv0]
! 18: source $testdir/tester.tcl
! 19:
! 20: ifcapable !stat3 {
! 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_stat3 WHERE idx='t1u' ORDER BY nlt;
! 59: }
! 60: } {alpha bravo charlie delta}
! 61:
! 62: do_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}
! 66: do_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: #
! 72: foreach {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: #
! 185: db 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: #
! 194: foreach {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
! 205: if {$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]
! 213: if {$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:
! 231: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to analyze5.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test