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