wasCSharpSQLite – Blame information for rev 4

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 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  
151  
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  
160  
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  
170  
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  
248  
249 }
250  
251 finish_test