Merge Chromium + Blink git repositories
[chromium-blink-merge.git] / third_party / sqlite / sqlite-src-3080704 / test / analyze5.test
blobac175c07b5953b74a3541a398b34ad2005994fda
1 # 2011 January 19
3 # The author disclaims copyright to this source code.  In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
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.
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
20 ifcapable !stat4&&!stat3 {
21   finish_test
22   return
25 set testprefix analyze5
27 proc eqp {sql {db db}} {
28   uplevel execsql [list "EXPLAIN QUERY PLAN $sql"] $db
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
38 db func alpha alpha
40 db func lindex lindex
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}
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}
104 # Verify that range queries generate the correct row count estimates
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
185   300  {y=0}                 t1y  974
186   301  {y=1}                 t1y   26
187   302  {y=0.1}               t1y    1
189   400  {x IS NULL}           t1x  400
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]
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}
218 # Increase the number of NULLs in column x
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;
227 # Verify that range queries generate the correct row count estimates
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
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]
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}
265 finish_test