src/third_party/sqlite/src/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_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