Annotation of embedaddon/sqlite3/test/autoindex1.test, revision 1.1.1.1
1.1 misho 1: # 2010 April 07
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: # This file implements regression tests for SQLite library. The
12: # focus of this script is testing automatic index creation logic.
13: #
14:
15: set testdir [file dirname $argv0]
16: source $testdir/tester.tcl
17:
18: # If the library is not compiled with automatic index support then
19: # skip all tests in this file.
20: #
21: ifcapable {!autoindex} {
22: finish_test
23: return
24: }
25:
26: # With automatic index turned off, we do a full scan of the T2 table
27: do_test autoindex1-100 {
28: db eval {
29: CREATE TABLE t1(a,b);
30: INSERT INTO t1 VALUES(1,11);
31: INSERT INTO t1 VALUES(2,22);
32: INSERT INTO t1 SELECT a+2, b+22 FROM t1;
33: INSERT INTO t1 SELECT a+4, b+44 FROM t1;
34: CREATE TABLE t2(c,d);
35: INSERT INTO t2 SELECT a, 900+b FROM t1;
36: }
37: db eval {
38: PRAGMA automatic_index=OFF;
39: SELECT b, d FROM t1 JOIN t2 ON a=c ORDER BY b;
40: }
41: } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
42: do_test autoindex1-101 {
43: db status step
44: } {63}
45: do_test autoindex1-102 {
46: db status autoindex
47: } {0}
48:
49: # With autoindex turned on, we build an index once and then use that index
50: # to find T2 values.
51: do_test autoindex1-110 {
52: db eval {
53: PRAGMA automatic_index=ON;
54: SELECT b, d FROM t1 JOIN t2 ON a=c ORDER BY b;
55: }
56: } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
57: do_test autoindex1-111 {
58: db status step
59: } {7}
60: do_test autoindex1-112 {
61: db status autoindex
62: } {7}
63:
64: # The same test as above, but this time the T2 query is a subquery rather
65: # than a join.
66: do_test autoindex1-200 {
67: db eval {
68: PRAGMA automatic_index=OFF;
69: SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1;
70: }
71: } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
72: do_test autoindex1-201 {
73: db status step
74: } {35}
75: do_test autoindex1-202 {
76: db status autoindex
77: } {0}
78: do_test autoindex1-210 {
79: db eval {
80: PRAGMA automatic_index=ON;
81: SELECT b, (SELECT d FROM t2 WHERE c=a) FROM t1;
82: }
83: } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
84: do_test autoindex1-211 {
85: db status step
86: } {7}
87: do_test autoindex1-212 {
88: db status autoindex
89: } {7}
90:
91:
92: # Modify the second table of the join while the join is in progress
93: #
94: do_test autoindex1-300 {
95: set r {}
96: db eval {SELECT b, d FROM t1 JOIN t2 ON (c=a)} {
97: lappend r $b $d
98: db eval {UPDATE t2 SET d=d+1}
99: }
100: set r
101: } {11 911 22 922 33 933 44 944 55 955 66 966 77 977 88 988}
102: do_test autoindex1-310 {
103: db eval {SELECT d FROM t2 ORDER BY d}
104: } {919 930 941 952 963 974 985 996}
105:
106: # The next test does a 10-way join on unindexed tables. Without
107: # automatic indices, the join will take a long time to complete.
108: # With automatic indices, it should only take about a second.
109: #
110: do_test autoindex1-400 {
111: db eval {
112: CREATE TABLE t4(a, b);
113: INSERT INTO t4 VALUES(1,2);
114: INSERT INTO t4 VALUES(2,3);
115: }
116: for {set n 2} {$n<4096} {set n [expr {$n+$n}]} {
117: db eval {INSERT INTO t4 SELECT a+$n, b+$n FROM t4}
118: }
119: db eval {
120: SELECT count(*) FROM t4;
121: }
122: } {4096}
123: do_test autoindex1-401 {
124: db eval {
125: SELECT count(*)
126: FROM t4 AS x1
127: JOIN t4 AS x2 ON x2.a=x1.b
128: JOIN t4 AS x3 ON x3.a=x2.b
129: JOIN t4 AS x4 ON x4.a=x3.b
130: JOIN t4 AS x5 ON x5.a=x4.b
131: JOIN t4 AS x6 ON x6.a=x5.b
132: JOIN t4 AS x7 ON x7.a=x6.b
133: JOIN t4 AS x8 ON x8.a=x7.b
134: JOIN t4 AS x9 ON x9.a=x8.b
135: JOIN t4 AS x10 ON x10.a=x9.b;
136: }
137: } {4087}
138:
139: # Ticket [8011086c85c6c404014c947fcf3eb9f42b184a0d] from 2010-07-08
140: # Make sure automatic indices are not created for the RHS of an IN expression
141: # that is not a correlated subquery.
142: #
143: do_execsql_test autoindex1-500 {
144: CREATE TABLE t501(a INTEGER PRIMARY KEY, b);
145: CREATE TABLE t502(x INTEGER PRIMARY KEY, y);
146: EXPLAIN QUERY PLAN
147: SELECT b FROM t501
148: WHERE t501.a IN (SELECT x FROM t502 WHERE y=?);
149: } {
150: 0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?) (~25 rows)}
151: 0 0 0 {EXECUTE LIST SUBQUERY 1}
152: 1 0 0 {SCAN TABLE t502 (~100000 rows)}
153: }
154: do_execsql_test autoindex1-501 {
155: EXPLAIN QUERY PLAN
156: SELECT b FROM t501
157: WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
158: } {
159: 0 0 0 {SCAN TABLE t501 (~500000 rows)}
160: 0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1}
161: 1 0 0 {SEARCH TABLE t502 USING AUTOMATIC COVERING INDEX (y=?) (~7 rows)}
162: }
163: do_execsql_test autoindex1-502 {
164: EXPLAIN QUERY PLAN
165: SELECT b FROM t501
166: WHERE t501.a=123
167: AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
168: } {
169: 0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
170: 0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1}
171: 1 0 0 {SCAN TABLE t502 (~100000 rows)}
172: }
173:
174:
175: # The following code checks a performance regression reported on the
176: # mailing list on 2010-10-19. The problem is that the nRowEst field
177: # of ephermeral tables was not being initialized correctly and so no
178: # automatic index was being created for the emphemeral table when it was
179: # used as part of a join.
180: #
181: do_execsql_test autoindex1-600 {
182: CREATE TABLE flock_owner(
183: owner_rec_id INTEGER CONSTRAINT flock_owner_key PRIMARY KEY,
184: flock_no VARCHAR(6) NOT NULL REFERENCES flock (flock_no),
185: owner_person_id INTEGER NOT NULL REFERENCES person (person_id),
186: owner_change_date TEXT, last_changed TEXT NOT NULL,
187: CONSTRAINT fo_owner_date UNIQUE (flock_no, owner_change_date)
188: );
189: CREATE TABLE sheep (
190: Sheep_No char(7) NOT NULL,
191: Date_of_Birth char(8),
192: Sort_DoB text,
193: Flock_Book_Vol char(2),
194: Breeder_No char(6),
195: Breeder_Person integer,
196: Originating_Flock char(6),
197: Registering_Flock char(6),
198: Tag_Prefix char(9),
199: Tag_No char(15),
200: Sort_Tag_No integer,
201: Breeders_Temp_Tag char(15),
202: Sex char(1),
203: Sheep_Name char(32),
204: Sire_No char(7),
205: Dam_No char(7),
206: Register_Code char(1),
207: Colour char(48),
208: Colour_Code char(2),
209: Pattern_Code char(8),
210: Horns char(1),
211: Litter_Size char(1),
212: Coeff_of_Inbreeding real,
213: Date_of_Registration text,
214: Date_Last_Changed text,
215: UNIQUE(Sheep_No));
216: CREATE INDEX fo_flock_no_index
217: ON flock_owner (flock_no);
218: CREATE INDEX fo_owner_change_date_index
219: ON flock_owner (owner_change_date);
220: CREATE INDEX fo_owner_person_id_index
221: ON flock_owner (owner_person_id);
222: CREATE INDEX sheep_org_flock_index
223: ON sheep (originating_flock);
224: CREATE INDEX sheep_reg_flock_index
225: ON sheep (registering_flock);
226: EXPLAIN QUERY PLAN
227: SELECT x.sheep_no, x.registering_flock, x.date_of_registration
228: FROM sheep x LEFT JOIN
229: (SELECT s.sheep_no, prev.flock_no, prev.owner_person_id,
230: s.date_of_registration, prev.owner_change_date
231: FROM sheep s JOIN flock_owner prev ON s.registering_flock =
232: prev.flock_no
233: AND (prev.owner_change_date <= s.date_of_registration || ' 00:00:00')
234: WHERE NOT EXISTS
235: (SELECT 'x' FROM flock_owner later
236: WHERE prev.flock_no = later.flock_no
237: AND later.owner_change_date > prev.owner_change_date
238: AND later.owner_change_date <= s.date_of_registration||' 00:00:00')
239: ) y ON x.sheep_no = y.sheep_no
240: WHERE y.sheep_no IS NULL
241: ORDER BY x.registering_flock;
242: } {
243: 1 0 0 {SCAN TABLE sheep AS s (~1000000 rows)}
244: 1 1 1 {SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?) (~2 rows)}
245: 1 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2}
246: 2 0 0 {SEARCH TABLE flock_owner AS later USING COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date>? AND owner_change_date<?) (~1 rows)}
247: 0 0 0 {SCAN TABLE sheep AS x USING INDEX sheep_reg_flock_index (~1000000 rows)}
248: 0 1 1 {SEARCH SUBQUERY 1 AS y USING AUTOMATIC COVERING INDEX (sheep_no=?) (~8 rows)}
249: }
250:
251:
252: do_execsql_test autoindex1-700 {
253: CREATE TABLE t5(a, b, c);
254: EXPLAIN QUERY PLAN SELECT a FROM t5 WHERE b=10 ORDER BY c;
255: } {
256: 0 0 0 {SCAN TABLE t5 (~100000 rows)}
257: 0 0 0 {USE TEMP B-TREE FOR ORDER BY}
258: }
259:
260:
261: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to autoindex1.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