Merge Chromium + Blink git repositories
[chromium-blink-merge.git] / third_party / sqlite / sqlite-src-3080704 / test / index7.test
blob0f341a3129f84c523fef6cf9f70e1e415d8d7a00
1 # 2013-11-04
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 # Test cases for partial indices in WITHOUT ROWID tables
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
19 ifcapable !vtab {
20   finish_test
21   return
24 load_static_extension db wholenumber;
25 do_test index7-1.1 {
26   # Able to parse and manage partial indices
27   execsql {
28     CREATE TABLE t1(a,b,c PRIMARY KEY) WITHOUT rowid;
29     CREATE INDEX t1a ON t1(a) WHERE a IS NOT NULL;
30     CREATE INDEX t1b ON t1(b) WHERE b>10;
31     CREATE VIRTUAL TABLE nums USING wholenumber;
32     INSERT INTO t1(a,b,c)
33        SELECT CASE WHEN value%3!=0 THEN value END, value, value
34          FROM nums WHERE value<=20;
35     SELECT count(a), count(b) FROM t1;
36     PRAGMA integrity_check;
37   }
38 } {14 20 ok}
40 # Make sure the count(*) optimization works correctly with
41 # partial indices.  Ticket [a5c8ed66cae16243be6] 2013-10-03.
43 do_execsql_test index7-1.1.1 {
44   SELECT count(*) FROM t1;
45 } {20}
47 # Error conditions during parsing...
49 do_test index7-1.2 {
50   catchsql {
51     CREATE INDEX bad1 ON t1(a,b) WHERE x IS NOT NULL;
52   }
53 } {1 {no such column: x}}
54 do_test index7-1.3 {
55   catchsql {
56     CREATE INDEX bad1 ON t1(a,b) WHERE EXISTS(SELECT * FROM t1);
57   }
58 } {1 {subqueries prohibited in partial index WHERE clauses}}
59 do_test index7-1.4 {
60   catchsql {
61     CREATE INDEX bad1 ON t1(a,b) WHERE a!=?1;
62   }
63 } {1 {parameters prohibited in partial index WHERE clauses}}
64 do_test index7-1.5 {
65   catchsql {
66     CREATE INDEX bad1 ON t1(a,b) WHERE a!=random();
67   }
68 } {1 {functions prohibited in partial index WHERE clauses}}
69 do_test index7-1.6 {
70   catchsql {
71     CREATE INDEX bad1 ON t1(a,b) WHERE a NOT LIKE 'abc%';
72   }
73 } {1 {functions prohibited in partial index WHERE clauses}}
75 do_test index7-1.10 {
76   execsql {
77     ANALYZE;
78     SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
79     PRAGMA integrity_check;
80   }
81 } {t1 {20 1} t1a {14 1} t1b {10 1} ok}
83 # STAT1 shows the partial indices have a reduced number of
84 # rows.
86 do_test index7-1.11 {
87   execsql {
88     UPDATE t1 SET a=b;
89     ANALYZE;
90     SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
91     PRAGMA integrity_check;
92   }
93 } {t1 {20 1} t1a {20 1} t1b {10 1} ok}
95 do_test index7-1.11b {
96   execsql {
97     UPDATE t1 SET a=NULL WHERE b%3!=0;
98     UPDATE t1 SET b=b+100;
99     ANALYZE;
100     SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
101     PRAGMA integrity_check;
102   }
103 } {t1 {20 1} t1a {6 1} t1b {20 1} ok}
105 do_test index7-1.12 {
106   execsql {
107     UPDATE t1 SET a=CASE WHEN b%3!=0 THEN b END;
108     UPDATE t1 SET b=b-100;
109     ANALYZE;
110     SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
111     PRAGMA integrity_check;
112   }
113 } {t1 {20 1} t1a {13 1} t1b {10 1} ok}
115 do_test index7-1.13 {
116   execsql {
117     DELETE FROM t1 WHERE b BETWEEN 8 AND 12;
118     ANALYZE;
119     SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
120     PRAGMA integrity_check;
121   }
122 } {t1 {15 1} t1a {10 1} t1b {8 1} ok}
124 do_test index7-1.14 {
125   execsql {
126     REINDEX;
127     ANALYZE;
128     SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
129     PRAGMA integrity_check;
130   }
131 } {t1 {15 1} t1a {10 1} t1b {8 1} ok}
133 do_test index7-1.15 {
134   execsql {
135     CREATE INDEX t1c ON t1(c);
136     ANALYZE;
137     SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;
138     PRAGMA integrity_check;
139   }
140 } {t1 {15 1} t1a {10 1} t1b {8 1} t1c {15 1} ok}
142 # Queries use partial indices as appropriate times.
144 do_test index7-2.1 {
145   execsql {
146     CREATE TABLE t2(a,b PRIMARY KEY) without rowid;
147     INSERT INTO t2(a,b) SELECT value, value FROM nums WHERE value<1000;
148     UPDATE t2 SET a=NULL WHERE b%5==0;
149     CREATE INDEX t2a1 ON t2(a) WHERE a IS NOT NULL;
150     SELECT count(*) FROM t2 WHERE a IS NOT NULL;
151   }
152 } {800}
153 do_test index7-2.2 {
154   execsql {
155     EXPLAIN QUERY PLAN
156     SELECT * FROM t2 WHERE a=5;
157   }
158 } {/.* TABLE t2 USING COVERING INDEX t2a1 .*/}
159 ifcapable stat4||stat3 {
160   do_test index7-2.3stat4 {
161     execsql {
162       EXPLAIN QUERY PLAN
163       SELECT * FROM t2 WHERE a IS NOT NULL;
164     }
165   } {/.* TABLE t2 USING COVERING INDEX t2a1 .*/}
166 } else {
167   do_test index7-2.3stat4 {
168     execsql {
169       EXPLAIN QUERY PLAN
170       SELECT * FROM t2 WHERE a IS NOT NULL AND a>0;
171     }
172   } {/.* TABLE t2 USING COVERING INDEX t2a1 .*/}
174 do_test index7-2.4 {
175   execsql {
176     EXPLAIN QUERY PLAN
177     SELECT * FROM t2 WHERE a IS NULL;
178   }
179 } {~/.*INDEX t2a1.*/}
181 do_execsql_test index7-2.101 {
182   DROP INDEX t2a1;
183   UPDATE t2 SET a=b, b=b+10000;
184   SELECT b FROM t2 WHERE a=15;
185 } {10015}
186 do_execsql_test index7-2.102 {
187   CREATE INDEX t2a2 ON t2(a) WHERE a<100 OR a>200;
188   SELECT b FROM t2 WHERE a=15;
189   PRAGMA integrity_check;
190 } {10015 ok}
191 do_execsql_test index7-2.102eqp {
192   EXPLAIN QUERY PLAN
193   SELECT b FROM t2 WHERE a=15;
194 } {~/.*INDEX t2a2.*/}
195 do_execsql_test index7-2.103 {
196   SELECT b FROM t2 WHERE a=15 AND a<100;
197 } {10015}
198 do_execsql_test index7-2.103eqp {
199   EXPLAIN QUERY PLAN
200   SELECT b FROM t2 WHERE a=15 AND a<100;
201 } {/.*INDEX t2a2.*/}
202 do_execsql_test index7-2.104 {
203   SELECT b FROM t2 WHERE a=515 AND a>200;
204 } {10515}
205 do_execsql_test index7-2.104eqp {
206   EXPLAIN QUERY PLAN
207   SELECT b FROM t2 WHERE a=515 AND a>200;
208 } {/.*INDEX t2a2.*/}
210 # Partial UNIQUE indices
212 do_execsql_test index7-3.1 {
213   CREATE TABLE t3(a,b PRIMARY KEY) without rowid;
214   INSERT INTO t3 SELECT value, value FROM nums WHERE value<200;
215   UPDATE t3 SET a=999 WHERE b%5!=0;
216   CREATE UNIQUE INDEX t3a ON t3(a) WHERE a<>999;
217 } {}
218 do_test index7-3.2 {
219   # unable to insert a duplicate row a-value that is not 999.
220   catchsql {
221     INSERT INTO t3(a,b) VALUES(150, 'test1');
222   }
223 } {1 {UNIQUE constraint failed: t3.a}}
224 do_test index7-3.3 {
225   # can insert multiple rows with a==999 because such rows are not
226   # part of the unique index.
227   catchsql {
228     INSERT INTO t3(a,b) VALUES(999, 'test1'), (999, 'test2');
229   }
230 } {0 {}}
231 do_execsql_test index7-3.4 {
232   SELECT count(*) FROM t3 WHERE a=999;
233 } {162}
234 integrity_check index7-3.5
236 do_execsql_test index7-4.0 {
237   VACUUM;
238   PRAGMA integrity_check;
239 } {ok}
241 # Silently ignore database name qualifiers in partial indices.
243 do_execsql_test index7-5.0 {
244   CREATE INDEX t3b ON t3(b) WHERE xyzzy.t3.b BETWEEN 5 AND 10;
245                                /* ^^^^^-- ignored */
246   ANALYZE;
247   SELECT count(*) FROM t3 WHERE t3.b BETWEEN 5 AND 10;
248   SELECT stat+0 FROM sqlite_stat1 WHERE idx='t3b';
249 } {6 6}
251 # Verify that the problem identified by ticket [98d973b8f5] has been fixed.
253 do_execsql_test index7-6.1 {
254   CREATE TABLE t5(a, b);
255   CREATE TABLE t4(c, d);
256   INSERT INTO t5 VALUES(1, 'xyz');
257   INSERT INTO t4 VALUES('abc', 'not xyz');
258   SELECT * FROM (SELECT * FROM t5 WHERE a=1 AND b='xyz'), t4 WHERE c='abc';
259 } {
260   1 xyz abc {not xyz}
262 do_execsql_test index7-6.2 {
263   CREATE INDEX i4 ON t4(c) WHERE d='xyz';
264   SELECT * FROM (SELECT * FROM t5 WHERE a=1 AND b='xyz'), t4 WHERE c='abc';
265 } {
266   1 xyz abc {not xyz}
268 do_execsql_test index7-6.3 {
269   CREATE VIEW v4 AS SELECT * FROM t4;
270   INSERT INTO t4 VALUES('def', 'xyz');
271   SELECT * FROM v4 WHERE d='xyz' AND c='def'
272 } {
273   def xyz
275 do_eqp_test index7-6.4 {
276   SELECT * FROM v4 WHERE d='xyz' AND c='def'
277 } {
278   0 0 0 {SEARCH TABLE t4 USING INDEX i4 (c=?)}
281 finish_test