test/analyze5.test

   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
  17 set testdir [file dirname $argv0]
  18 source $testdir/tester.tcl
  19
  20 ifcapable !stat4 {
  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 proc alpha {blob} {
  32   set ret ""
  33   foreach c [split $blob {}] {
  34     if {[string is alpha $c]} {append ret $c}
  35   }
  36   return $ret
  37 }
  38 db func alpha alpha
  39
  40 db func lindex lindex
  41
  42 unset -nocomplain i t u v w x y z
  43 do_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   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
  76 do_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 1
  80   }
  81 } {alpha bravo charlie delta}
  82 do_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 estimates
  87 #
  88 foreach {testid where index rows} {
  89     1  {z>=0 AND z<=0}       t1z  400
  90     2  {z>=1 AND z<=1}       t1z  300
  91     3  {z>=2 AND z<=2}       t1z  175
  92     4  {z>=3 AND z<=3}       t1z  125
  93     5  {z>=4 AND z<=4}       t1z    1
  94     6  {z>=-1 AND z<=-1}     t1z    1
  95     7  {z>1 AND z<3}         t1z  175
  96     8  {z>0 AND z<100}       t1z  600
  97     9  {z>=1 AND z<100}      t1z  600
  98    10  {z>1 AND z<100}       t1z  300
  99    11  {z>=2 AND z<100}      t1z  300
 100    12  {z>2 AND z<100}       t1z  125
 101    13  {z>=3 AND z<100}      t1z  125
 102    14  {z>3 AND z<100}       t1z    1
 103    15  {z>=4 AND z<100}      t1z    1
 104    16  {z>=-100 AND z<=-1}   t1z    1
 105    17  {z>=-100 AND z<=0}    t1z  400
 106    18  {z>=-100 AND z<0}     t1z    1
 107    19  {z>=-100 AND z<=1}    t1z  700
 108    20  {z>=-100 AND z<2}     t1z  700
 109    21  {z>=-100 AND z<=2}    t1z  875
 110    22  {z>=-100 AND z<3}     t1z  875
 111
 112    31  {z>=0.0 AND z<=0.0}   t1z  400
 113    32  {z>=1.0 AND z<=1.0}   t1z  300
 114    33  {z>=2.0 AND z<=2.0}   t1z  175
 115    34  {z>=3.0 AND z<=3.0}   t1z  125
 116    35  {z>=4.0 AND z<=4.0}   t1z    1
 117    36  {z>=-1.0 AND z<=-1.0} t1z    1
 118    37  {z>1.5 AND z<3.0}     t1z  174
 119    38  {z>0.5 AND z<100}     t1z  599
 120    39  {z>=1.0 AND z<100}    t1z  600
 121    40  {z>1.5 AND z<100}     t1z  299
 122    41  {z>=2.0 AND z<100}    t1z  300
 123    42  {z>2.1 AND z<100}     t1z  124
 124    43  {z>=3.0 AND z<100}    t1z  125
 125    44  {z>3.2 AND z<100}     t1z    1
 126    45  {z>=4.0 AND z<100}    t1z    1
 127    46  {z>=-100 AND z<=-1.0} t1z    1
 128    47  {z>=-100 AND z<=0.0}  t1z  400
 129    48  {z>=-100 AND z<0.0}   t1z    1
 130    49  {z>=-100 AND z<=1.0}  t1z  700
 131    50  {z>=-100 AND z<2.0}   t1z  700
 132    51  {z>=-100 AND z<=2.0}  t1z  875
 133    52  {z>=-100 AND z<3.0}   t1z  875
 134
 135   101  {z=-1}                t1z    1
 136   102  {z=0}                 t1z  400
 137   103  {z=1}                 t1z  300
 138   104  {z=2}                 t1z  175
 139   105  {z=3}                 t1z  125
 140   106  {z=4}                 t1z    1
 141   107  {z=-10.0}             t1z    1
 142   108  {z=0.0}               t1z  400
 143   109  {z=1.0}               t1z  300
 144   110  {z=2.0}               t1z  175
 145   111  {z=3.0}               t1z  125
 146   112  {z=4.0}               t1z    1
 147   113  {z=1.5}               t1z    1
 148   114  {z=2.5}               t1z    1
 149
 150   201  {z IN (-1)}           t1z    1
 151   202  {z IN (0)}            t1z  400
 152   203  {z IN (1)}            t1z  300
 153   204  {z IN (2)}            t1z  175
 154   205  {z IN (3)}            t1z  125
 155   206  {z IN (4)}            t1z    1
 156   207  {z IN (0.5)}          t1z    1
 157   208  {z IN (0,1)}          t1z  700
 158   209  {z IN (0,1,2)}        t1z  875
 159   210  {z IN (0,1,2,3)}      {}   100
 160   211  {z IN (0,1,2,3,4,5)}  {}   100
 161   212  {z IN (1,2)}          t1z  475
 162   213  {z IN (2,3)}          t1z  300
 163   214  {z=3 OR z=2}          t1z  300
 164   215  {z IN (-1,3)}         t1z  126
 165   216  {z=-1 OR z=3}         t1z  126
 166
 167   300  {y=0}                 t1y  974
 168   301  {y=1}                 t1y   26
 169   302  {y=0.1}               t1y    1
 170
 171   400  {x IS NULL}           t1x  400
 172
 173 } {
 174   # Verify that the expected index is used with the expected row count
 175   # No longer valid due to an EXPLAIN QUERY PLAN output format change
 176   # 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 idx
 180   #   regexp {~([0-9]+) rows} $x all nrow
 181   #   list $idx $nrow
 182   # } [list $index $rows]
 183
 184   # Verify that the same result is achieved regardless of whether or not
 185   # the index is used
 186   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 ok
 193     } else {
 194       set res "a1=\[$a1\] a2=\[$a2\]"
 195     }
 196     set res
 197   } {ok}
 198 }
 199
 200 # Increase the number of NULLs in column x
 201 #
 202 db eval {
 203    UPDATE t1 SET x=NULL;
 204    UPDATE t1 SET x=rowid
 205     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 estimates
 210 #
 211 foreach {testid where index rows} {
 212   500  {x IS NULL AND u='charlie'}         t1u  17
 213   501  {x=1 AND u='charlie'}               t1x   1
 214   502  {x IS NULL}                         t1x 995
 215   503  {x=1}                               t1x   1
 216   504  {x IS NOT NULL}                     t1x   2
 217   505  {+x IS NOT NULL}                     {} 500
 218   506  {upper(x) IS NOT NULL}               {} 500
 219
 220 } {
 221   # Verify that the expected index is used with the expected row count
 222   # No longer valid due to an EXPLAIN QUERY PLAN format change
 223   # 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 idx
 227   #   regexp {~([0-9]+) rows} $x all nrow
 228   #   list $idx $nrow
 229   # } [list $index $rows]
 230
 231   # Verify that the same result is achieved regardless of whether or not
 232   # the index is used
 233   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 ok
 240     } else {
 241       set res "a1=\[$a1\] a2=\[$a2\]"
 242     }
 243     set res
 244   } {ok}
 245 }
 246
 247 finish_test