add pragma page_size compatibility so it will operate on encrypted databases
[sqlcipher.git] / test / cost.test
blob5684177a137baf80d38521d7d7a95daac6fa3da7
1 # 2014-04-26
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 #***********************************************************************
11
13 set testdir [file dirname $argv0]
14 source $testdir/tester.tcl
15 set testprefix cost
18 do_execsql_test 1.1 {
19   CREATE TABLE t3(id INTEGER PRIMARY KEY, b NOT NULL);
20   CREATE TABLE t4(c, d, e);
21   CREATE UNIQUE INDEX i3 ON t3(b);
22   CREATE UNIQUE INDEX i4 ON t4(c, d);
24 do_eqp_test 1.2 {
25   SELECT e FROM t3, t4 WHERE b=c ORDER BY b, d;
26 } {
27   QUERY PLAN
28   |--SCAN t3 USING COVERING INDEX i3
29   `--SEARCH t4 USING INDEX i4 (c=?)
33 do_execsql_test 2.1 {
34   CREATE TABLE t1(a, b);
35   CREATE INDEX i1 ON t1(a);
38 # It is better to use an index for ORDER BY than sort externally, even 
39 # if the index is a non-covering index.
40 do_eqp_test 2.2 {
41   SELECT * FROM t1 ORDER BY a;
42 } {SCAN t1 USING INDEX i1}
44 do_execsql_test 3.1 {
45   CREATE TABLE t5(a INTEGER PRIMARY KEY,b,c,d,e,f,g);
46   CREATE INDEX t5b ON t5(b);
47   CREATE INDEX t5c ON t5(c);
48   CREATE INDEX t5d ON t5(d);
49   CREATE INDEX t5e ON t5(e);
50   CREATE INDEX t5f ON t5(f);
51   CREATE INDEX t5g ON t5(g);
54 do_eqp_test 3.2 {
55   SELECT a FROM t5 
56   WHERE b IS NULL OR c IS NULL OR d IS NULL 
57   ORDER BY a;
58 } {
59   QUERY PLAN
60   |--MULTI-INDEX OR
61   |  |--INDEX 1
62   |  |  `--SEARCH t5 USING INDEX t5b (b=?)
63   |  |--INDEX 2
64   |  |  `--SEARCH t5 USING INDEX t5c (c=?)
65   |  `--INDEX 3
66   |     `--SEARCH t5 USING INDEX t5d (d=?)
67   `--USE TEMP B-TREE FOR ORDER BY
70 #-------------------------------------------------------------------------
71 # If there is no likelihood() or stat3 data, SQLite assumes that a closed
72 # range scan (e.g. one constrained by "col BETWEEN ? AND ?" constraint)
73 # visits 1/64 of the rows in a table.
75 # Note: 1/63 =~ 0.016
76 # Note: 1/65 =~ 0.015
78 reset_db
79 do_execsql_test 4.1 {
80   CREATE TABLE t1(a, b);
81   CREATE INDEX i1 ON t1(a);
82   CREATE INDEX i2 ON t1(b);
84 do_eqp_test 4.2 {
85   SELECT * FROM t1 WHERE likelihood(a=?, 0.014) AND b BETWEEN ? AND ?;
86 } {SEARCH t1 USING INDEX i1 (a=?)}
88 do_eqp_test 4.3 {
89   SELECT * FROM t1 WHERE likelihood(a=?, 0.016) AND b BETWEEN ? AND ?;
90 } {SEARCH t1 USING INDEX i2 (b>? AND b<?)}
93 #-------------------------------------------------------------------------
95 reset_db
96 do_execsql_test 5.1 {
97   CREATE TABLE t2(x, y);
98   CREATE INDEX t2i1 ON t2(x);
101 do_eqp_test 5.2 {
102   SELECT * FROM t2 ORDER BY x, y;
103 } {
104   QUERY PLAN
105   |--SCAN t2 USING INDEX t2i1
106   `--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY
109 do_eqp_test 5.3 {
110   SELECT * FROM t2 WHERE x BETWEEN ? AND ? ORDER BY rowid;
111 } {
112   QUERY PLAN
113   |--SEARCH t2 USING INDEX t2i1 (x>? AND x<?)
114   `--USE TEMP B-TREE FOR ORDER BY
117 # where7.test, where8.test:
119 do_execsql_test 6.1 {
120   CREATE TABLE t3(a INTEGER PRIMARY KEY, b, c);
121   CREATE INDEX t3i1 ON t3(b);
122   CREATE INDEX t3i2 ON t3(c);
125 do_eqp_test 6.2 {
126   SELECT a FROM t3 WHERE (b BETWEEN 2 AND 4) OR c=100 ORDER BY a
127 } {
128   QUERY PLAN
129   |--MULTI-INDEX OR
130   |  |--INDEX 1
131   |  |  `--SEARCH t3 USING INDEX t3i1 (b>? AND b<?)
132   |  `--INDEX 2
133   |     `--SEARCH t3 USING INDEX t3i2 (c=?)
134   `--USE TEMP B-TREE FOR ORDER BY
137 #-------------------------------------------------------------------------
139 reset_db
140 do_execsql_test 7.1 {
141   CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c,d,e,f,g);
142   CREATE INDEX t1b ON t1(b);
143   CREATE INDEX t1c ON t1(c);
144   CREATE INDEX t1d ON t1(d);
145   CREATE INDEX t1e ON t1(e);
146   CREATE INDEX t1f ON t1(f);
147   CREATE INDEX t1g ON t1(g);
150 do_eqp_test 7.2 {
151   SELECT a FROM t1
152      WHERE (b>=950 AND b<=1010) OR (b IS NULL AND c NOT NULL)
153   ORDER BY a
154 } {
155   QUERY PLAN
156   |--MULTI-INDEX OR
157   |  |--INDEX 1
158   |  |  `--SEARCH t1 USING INDEX t1b (b>? AND b<?)
159   |  `--INDEX 2
160   |     `--SEARCH t1 USING INDEX t1b (b=?)
161   `--USE TEMP B-TREE FOR ORDER BY
164 do_eqp_test 7.3 {
165   SELECT rowid FROM t1
166   WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL)
167         OR (b NOT NULL AND c IS NULL AND d NOT NULL)
168         OR (b NOT NULL AND c NOT NULL AND d IS NULL)
169 } {SCAN t1}
171 do_eqp_test 7.4 {
172   SELECT rowid FROM t1 WHERE (+b IS NULL AND c NOT NULL) OR c IS NULL
173 } {SCAN t1}
175 #-------------------------------------------------------------------------
177 reset_db
178 do_execsql_test 8.1 {
179   CREATE TABLE composer(
180     cid INTEGER PRIMARY KEY,
181     cname TEXT
182   );
183   CREATE TABLE album(
184     aid INTEGER PRIMARY KEY,
185     aname TEXT
186   );
187   CREATE TABLE track(
188     tid INTEGER PRIMARY KEY,
189     cid INTEGER REFERENCES composer,
190     aid INTEGER REFERENCES album,
191     title TEXT
192   );
193   CREATE INDEX track_i1 ON track(cid);
194   CREATE INDEX track_i2 ON track(aid);
197 do_eqp_test 8.2 {
198   SELECT DISTINCT aname
199     FROM album, composer, track
200    WHERE cname LIKE '%bach%'
201      AND unlikely(composer.cid=track.cid)
202      AND unlikely(album.aid=track.aid);
203 } {
204   QUERY PLAN
205   |--SCAN track
206   |--SEARCH album USING INTEGER PRIMARY KEY (rowid=?)
207   |--SEARCH composer USING INTEGER PRIMARY KEY (rowid=?)
208   `--USE TEMP B-TREE FOR DISTINCT
211 #-------------------------------------------------------------------------
213 do_execsql_test 9.1 {
214   CREATE TABLE t1(
215     a,b,c,d,e, f,g,h,i,j,
216     k,l,m,n,o, p,q,r,s,t
217   );
218   CREATE INDEX i1 ON t1(k,l,m,n,o,p,q,r,s,t);
220 do_test 9.2 {
221   for {set i 0} {$i < 100} {incr i} {
222     execsql { INSERT INTO t1 DEFAULT VALUES }
223   }
224   execsql {
225     ANALYZE;
226     CREATE INDEX i2 ON t1(a,b,c,d,e,f,g,h,i,j);
227   }
228 } {}
230 set L [list a=? b=? c=? d=? e=? f=? g=? h=? i=? j=?]
231 foreach {tn nTerm nRow} {
232   1   1 10
233   2   2 10
234   3   3  8
235   4   4  7
236   5   5  7
237   6   6  5
238   7   7  5
239   8   8  5
240   9   9  5
241   10 10  5
242 } {
243   set w [join [lrange $L 0 [expr $nTerm-1]] " AND "]
244   set p1 [expr ($nRow-1) / 100.0]
245   set p2 [expr ($nRow+1) / 100.0]
247   set sql1 "SELECT * FROM t1 WHERE likelihood(k=?, $p1) AND $w"
248   set sql2 "SELECT * FROM t1 WHERE likelihood(k=?, $p2) AND $w"
250   do_eqp_test 9.3.$tn.1 $sql1 {/INDEX i1/}
251   do_eqp_test 9.3.$tn.2 $sql2 {/INDEX i2/}
255 #-------------------------------------------------------------------------
258 ifcapable stat4 {
259   do_execsql_test 10.1 {
260     CREATE TABLE t6(a, b, c);
261     CREATE INDEX t6i1 ON t6(a, b);
262     CREATE INDEX t6i2 ON t6(c);
263   }
264   
265   do_test 10.2 {
266     for {set i 0} {$i < 16} {incr i} {
267       execsql { INSERT INTO t6 VALUES($i%4, 'xyz', $i%8) }
268     }
269     execsql ANALYZE
270   } {}
272   do_eqp_test 10.3 {
273     SELECT rowid FROM t6 WHERE a=0 AND c=0
274   } {SEARCH t6 USING INDEX t6i2 (c=?)}
276   do_eqp_test 10.4 {
277     SELECT rowid FROM t6 WHERE a=0 AND b='xyz' AND c=0
278   } {SEARCH t6 USING INDEX t6i2 (c=?)}
280   do_eqp_test 10.5 {
281     SELECT rowid FROM t6 WHERE likelihood(a=0, 0.1) AND c=0
282   } {SEARCH t6 USING INDEX t6i1 (a=?)}
284   do_eqp_test 10.6 {
285     SELECT rowid FROM t6 WHERE likelihood(a=0, 0.1) AND b='xyz' AND c=0
286   } {SEARCH t6 USING INDEX t6i1 (a=? AND b=?)}
289 finish_test