1: # 2008 October 4
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: # $Id: indexedby.test,v 1.1.1.1 2012/02/21 17:04:16 misho Exp $
13:
14: set testdir [file dirname $argv0]
15: source $testdir/tester.tcl
16:
17: # Create a schema with some indexes.
18: #
19: do_test indexedby-1.1 {
20: execsql {
21: CREATE TABLE t1(a, b);
22: CREATE INDEX i1 ON t1(a);
23: CREATE INDEX i2 ON t1(b);
24:
25: CREATE TABLE t2(c, d);
26: CREATE INDEX i3 ON t2(c);
27: CREATE INDEX i4 ON t2(d);
28:
29: CREATE TABLE t3(e PRIMARY KEY, f);
30:
31: CREATE VIEW v1 AS SELECT * FROM t1;
32: }
33: } {}
34:
35: # Explain Query Plan
36: #
37: proc EQP {sql} {
38: uplevel "execsql {EXPLAIN QUERY PLAN $sql}"
39: }
40:
41: # These tests are to check that "EXPLAIN QUERY PLAN" is working as expected.
42: #
43: do_execsql_test indexedby-1.2 {
44: EXPLAIN QUERY PLAN select * from t1 WHERE a = 10;
45: } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)}}
46: do_execsql_test indexedby-1.3 {
47: EXPLAIN QUERY PLAN select * from t1 ;
48: } {0 0 0 {SCAN TABLE t1 (~1000000 rows)}}
49: do_execsql_test indexedby-1.4 {
50: EXPLAIN QUERY PLAN select * from t1, t2 WHERE c = 10;
51: } {
52: 0 0 1 {SEARCH TABLE t2 USING INDEX i3 (c=?) (~10 rows)}
53: 0 1 0 {SCAN TABLE t1 (~1000000 rows)}
54: }
55:
56: # Parser tests. Test that an INDEXED BY or NOT INDEX clause can be
57: # attached to a table in the FROM clause, but not to a sub-select or
58: # SQL view. Also test that specifying an index that does not exist or
59: # is attached to a different table is detected as an error.
60: #
61: do_test indexedby-2.1 {
62: execsql { SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two'}
63: } {}
64: do_test indexedby-2.2 {
65: execsql { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two'}
66: } {}
67: do_test indexedby-2.3 {
68: execsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two'}
69: } {}
70:
71: do_test indexedby-2.4 {
72: catchsql { SELECT * FROM t1 INDEXED BY i3 WHERE a = 'one' AND b = 'two'}
73: } {1 {no such index: i3}}
74: do_test indexedby-2.5 {
75: catchsql { SELECT * FROM t1 INDEXED BY i5 WHERE a = 'one' AND b = 'two'}
76: } {1 {no such index: i5}}
77: do_test indexedby-2.6 {
78: catchsql { SELECT * FROM t1 INDEXED BY WHERE a = 'one' AND b = 'two'}
79: } {1 {near "WHERE": syntax error}}
80: do_test indexedby-2.7 {
81: catchsql { SELECT * FROM v1 INDEXED BY i1 WHERE a = 'one' }
82: } {1 {no such index: i1}}
83:
84: # Tests for single table cases.
85: #
86: do_execsql_test indexedby-3.1 {
87: EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two'
88: } {0 0 0 {SCAN TABLE t1 (~10000 rows)}}
89: do_execsql_test indexedby-3.2 {
90: EXPLAIN QUERY PLAN
91: SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two'
92: } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~2 rows)}}
93: do_execsql_test indexedby-3.3 {
94: EXPLAIN QUERY PLAN
95: SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two'
96: } {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~2 rows)}}
97: do_test indexedby-3.4 {
98: catchsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' }
99: } {1 {cannot use index: i2}}
100: do_test indexedby-3.5 {
101: catchsql { SELECT * FROM t1 INDEXED BY i2 ORDER BY a }
102: } {1 {cannot use index: i2}}
103: do_test indexedby-3.6 {
104: catchsql { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' }
105: } {0 {}}
106: do_test indexedby-3.7 {
107: catchsql { SELECT * FROM t1 INDEXED BY i1 ORDER BY a }
108: } {0 {}}
109:
110: do_execsql_test indexedby-3.8 {
111: EXPLAIN QUERY PLAN
112: SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 ORDER BY e
113: } {0 0 0 {SCAN TABLE t3 USING INDEX sqlite_autoindex_t3_1 (~1000000 rows)}}
114: do_execsql_test indexedby-3.9 {
115: EXPLAIN QUERY PLAN
116: SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE e = 10
117: } {0 0 0 {SEARCH TABLE t3 USING INDEX sqlite_autoindex_t3_1 (e=?) (~1 rows)}}
118: do_test indexedby-3.10 {
119: catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE f = 10 }
120: } {1 {cannot use index: sqlite_autoindex_t3_1}}
121: do_test indexedby-3.11 {
122: catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_2 WHERE f = 10 }
123: } {1 {no such index: sqlite_autoindex_t3_2}}
124:
125: # Tests for multiple table cases.
126: #
127: do_execsql_test indexedby-4.1 {
128: EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE a = c
129: } {
130: 0 0 0 {SCAN TABLE t1 (~1000000 rows)}
131: 0 1 1 {SEARCH TABLE t2 USING INDEX i3 (c=?) (~10 rows)}
132: }
133: do_execsql_test indexedby-4.2 {
134: EXPLAIN QUERY PLAN SELECT * FROM t1 INDEXED BY i1, t2 WHERE a = c
135: } {
136: 0 0 1 {SCAN TABLE t2 (~1000000 rows)}
137: 0 1 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)}
138: }
139: do_test indexedby-4.3 {
140: catchsql {
141: SELECT * FROM t1 INDEXED BY i1, t2 INDEXED BY i3 WHERE a=c
142: }
143: } {1 {cannot use index: i1}}
144: do_test indexedby-4.4 {
145: catchsql {
146: SELECT * FROM t2 INDEXED BY i3, t1 INDEXED BY i1 WHERE a=c
147: }
148: } {1 {cannot use index: i3}}
149:
150: # Test embedding an INDEXED BY in a CREATE VIEW statement. This block
151: # also tests that nothing bad happens if an index refered to by
152: # a CREATE VIEW statement is dropped and recreated.
153: #
154: do_execsql_test indexedby-5.1 {
155: CREATE VIEW v2 AS SELECT * FROM t1 INDEXED BY i1 WHERE a > 5;
156: EXPLAIN QUERY PLAN SELECT * FROM v2
157: } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?) (~250000 rows)}}
158: do_execsql_test indexedby-5.2 {
159: EXPLAIN QUERY PLAN SELECT * FROM v2 WHERE b = 10
160: } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?) (~25000 rows)}}
161: do_test indexedby-5.3 {
162: execsql { DROP INDEX i1 }
163: catchsql { SELECT * FROM v2 }
164: } {1 {no such index: i1}}
165: do_test indexedby-5.4 {
166: # Recreate index i1 in such a way as it cannot be used by the view query.
167: execsql { CREATE INDEX i1 ON t1(b) }
168: catchsql { SELECT * FROM v2 }
169: } {1 {cannot use index: i1}}
170: do_test indexedby-5.5 {
171: # Drop and recreate index i1 again. This time, create it so that it can
172: # be used by the query.
173: execsql { DROP INDEX i1 ; CREATE INDEX i1 ON t1(a) }
174: catchsql { SELECT * FROM v2 }
175: } {0 {}}
176:
177: # Test that "NOT INDEXED" may use the rowid index, but not others.
178: #
179: do_execsql_test indexedby-6.1 {
180: EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 10 ORDER BY rowid
181: } {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~10 rows)}}
182: do_execsql_test indexedby-6.2 {
183: EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE b = 10 ORDER BY rowid
184: } {0 0 0 {SCAN TABLE t1 USING INTEGER PRIMARY KEY (~100000 rows)}}
185:
186: # Test that "INDEXED BY" can be used in a DELETE statement.
187: #
188: do_execsql_test indexedby-7.1 {
189: EXPLAIN QUERY PLAN DELETE FROM t1 WHERE a = 5
190: } {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}}
191: do_execsql_test indexedby-7.2 {
192: EXPLAIN QUERY PLAN DELETE FROM t1 NOT INDEXED WHERE a = 5
193: } {0 0 0 {SCAN TABLE t1 (~100000 rows)}}
194: do_execsql_test indexedby-7.3 {
195: EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5
196: } {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}}
197: do_execsql_test indexedby-7.4 {
198: EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5 AND b = 10
199: } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~2 rows)}}
200: do_execsql_test indexedby-7.5 {
201: EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i2 WHERE a = 5 AND b = 10
202: } {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~2 rows)}}
203: do_test indexedby-7.6 {
204: catchsql { DELETE FROM t1 INDEXED BY i2 WHERE a = 5}
205: } {1 {cannot use index: i2}}
206:
207: # Test that "INDEXED BY" can be used in an UPDATE statement.
208: #
209: do_execsql_test indexedby-8.1 {
210: EXPLAIN QUERY PLAN UPDATE t1 SET rowid=rowid+1 WHERE a = 5
211: } {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}}
212: do_execsql_test indexedby-8.2 {
213: EXPLAIN QUERY PLAN UPDATE t1 NOT INDEXED SET rowid=rowid+1 WHERE a = 5
214: } {0 0 0 {SCAN TABLE t1 (~100000 rows)}}
215: do_execsql_test indexedby-8.3 {
216: EXPLAIN QUERY PLAN UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5
217: } {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}}
218: do_execsql_test indexedby-8.4 {
219: EXPLAIN QUERY PLAN
220: UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 AND b = 10
221: } {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~2 rows)}}
222: do_execsql_test indexedby-8.5 {
223: EXPLAIN QUERY PLAN
224: UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5 AND b = 10
225: } {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~2 rows)}}
226: do_test indexedby-8.6 {
227: catchsql { UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5}
228: } {1 {cannot use index: i2}}
229:
230: # Test that bug #3560 is fixed.
231: #
232: do_test indexedby-9.1 {
233: execsql {
234: CREATE TABLE maintable( id integer);
235: CREATE TABLE joinme(id_int integer, id_text text);
236: CREATE INDEX joinme_id_text_idx on joinme(id_text);
237: CREATE INDEX joinme_id_int_idx on joinme(id_int);
238: }
239: } {}
240: do_test indexedby-9.2 {
241: catchsql {
242: select * from maintable as m inner join
243: joinme as j indexed by joinme_id_text_idx
244: on ( m.id = j.id_int)
245: }
246: } {1 {cannot use index: joinme_id_text_idx}}
247: do_test indexedby-9.3 {
248: catchsql { select * from maintable, joinme INDEXED by joinme_id_text_idx }
249: } {1 {cannot use index: joinme_id_text_idx}}
250:
251: # Make sure we can still create tables, indices, and columns whose name
252: # is "indexed".
253: #
254: do_test indexedby-10.1 {
255: execsql {
256: CREATE TABLE indexed(x,y);
257: INSERT INTO indexed VALUES(1,2);
258: SELECT * FROM indexed;
259: }
260: } {1 2}
261: do_test indexedby-10.2 {
262: execsql {
263: CREATE INDEX i10 ON indexed(x);
264: SELECT * FROM indexed indexed by i10 where x>0;
265: }
266: } {1 2}
267: do_test indexedby-10.3 {
268: execsql {
269: DROP TABLE indexed;
270: CREATE TABLE t10(indexed INTEGER);
271: INSERT INTO t10 VALUES(1);
272: CREATE INDEX indexed ON t10(indexed);
273: SELECT * FROM t10 indexed by indexed WHERE indexed>0
274: }
275: } {1}
276:
277: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to indexedby.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test