wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 # 2006 January 31
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 file is testing the join reordering optimization
13 # in cases that include a LEFT JOIN.
14 #
15 # $Id: where3.test,v 1.4 2008/04/17 19:14:02 drh Exp $
16  
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
19  
20 # The following is from ticket #1652.
21 #
22 # A comma join then a left outer join: A,B left join C.
23 # Arrange indices so that the B table is chosen to go first.
24 # Also put an index on C, but make sure that A is chosen before C.
25 #
26 do_test where3-1.1 {
27 execsql {
28 CREATE TABLE t1(a, b);
29 CREATE TABLE t2(p, q);
30 CREATE TABLE t3(x, y);
31  
32 INSERT INTO t1 VALUES(111,'one');
33 INSERT INTO t1 VALUES(222,'two');
34 INSERT INTO t1 VALUES(333,'three');
35  
36 INSERT INTO t2 VALUES(1,111);
37 INSERT INTO t2 VALUES(2,222);
38 INSERT INTO t2 VALUES(4,444);
39 CREATE INDEX t2i1 ON t2(p);
40  
41 INSERT INTO t3 VALUES(999,'nine');
42 CREATE INDEX t3i1 ON t3(x);
43  
44 SELECT * FROM t1, t2 LEFT JOIN t3 ON q=x WHERE p=2 AND a=q;
45 }
46 } {222 two 2 222 {} {}}
47  
48 ifcapable explain {
49 do_test where3-1.1.1 {
50 explain_no_trace {SELECT * FROM t1, t2 LEFT JOIN t3 ON q=x
51 WHERE p=2 AND a=q}
52 } [explain_no_trace {SELECT * FROM t1, t2 LEFT JOIN t3 ON x=q
53 WHERE p=2 AND a=q}]
54 }
55  
56 # Ticket #1830
57 #
58 # This is similar to the above but with the LEFT JOIN on the
59 # other side.
60 #
61 do_test where3-1.2 {
62 execsql {
63 CREATE TABLE parent1(parent1key, child1key, Child2key, child3key);
64 CREATE TABLE child1 ( child1key NVARCHAR, value NVARCHAR );
65 CREATE UNIQUE INDEX PKIDXChild1 ON child1 ( child1key );
66 CREATE TABLE child2 ( child2key NVARCHAR, value NVARCHAR );
67  
68 INSERT INTO parent1(parent1key,child1key,child2key)
69 VALUES ( 1, 'C1.1', 'C2.1' );
70 INSERT INTO child1 ( child1key, value ) VALUES ( 'C1.1', 'Value for C1.1' );
71 INSERT INTO child2 ( child2key, value ) VALUES ( 'C2.1', 'Value for C2.1' );
72  
73 INSERT INTO parent1 ( parent1key, child1key, child2key )
74 VALUES ( 2, 'C1.2', 'C2.2' );
75 INSERT INTO child2 ( child2key, value ) VALUES ( 'C2.2', 'Value for C2.2' );
76  
77 INSERT INTO parent1 ( parent1key, child1key, child2key )
78 VALUES ( 3, 'C1.3', 'C2.3' );
79 INSERT INTO child1 ( child1key, value ) VALUES ( 'C1.3', 'Value for C1.3' );
80 INSERT INTO child2 ( child2key, value ) VALUES ( 'C2.3', 'Value for C2.3' );
81  
82 SELECT parent1.parent1key, child1.value, child2.value
83 FROM parent1
84 LEFT OUTER JOIN child1 ON child1.child1key = parent1.child1key
85 INNER JOIN child2 ON child2.child2key = parent1.child2key;
86 }
87 } {1 {Value for C1.1} {Value for C2.1} 2 {} {Value for C2.2} 3 {Value for C1.3} {Value for C2.3}}
88  
89 ifcapable explain {
90 do_test where3-1.2.1 {
91 explain_no_trace {
92 SELECT parent1.parent1key, child1.value, child2.value
93 FROM parent1
94 LEFT OUTER JOIN child1 ON child1.child1key = parent1.child1key
95 INNER JOIN child2 ON child2.child2key = parent1.child2key;
96 }
97 } [explain_no_trace {
98 SELECT parent1.parent1key, child1.value, child2.value
99 FROM parent1
100 LEFT OUTER JOIN child1 ON parent1.child1key = child1.child1key
101 INNER JOIN child2 ON child2.child2key = parent1.child2key;
102 }]
103 }
104  
105 # This procedure executes the SQL. Then it appends
106 # the ::sqlite_query_plan variable.
107 #
108 proc queryplan {sql} {
109 set ::sqlite_sort_count 0
110 set data [execsql $sql]
111 return [concat $data $::sqlite_query_plan]
112 }
113  
114  
115 # If you have a from clause of the form: A B C left join D
116 # then make sure the query optimizer is able to reorder the
117 # A B C part anyway it wants.
118 #
119 # Following the fix to ticket #1652, there was a time when
120 # the C table would not reorder. So the following reorderings
121 # were possible:
122 #
123 # A B C left join D
124 # B A C left join D
125 #
126 # But these reorders were not allowed
127 #
128 # C A B left join D
129 # A C B left join D
130 # C B A left join D
131 # B C A left join D
132 #
133 # The following tests are here to verify that the latter four
134 # reorderings are allowed again.
135 #
136 do_test where3-2.1 {
137 execsql {
138 CREATE TABLE tA(apk integer primary key, ax);
139 CREATE TABLE tB(bpk integer primary key, bx);
140 CREATE TABLE tC(cpk integer primary key, cx);
141 CREATE TABLE tD(dpk integer primary key, dx);
142 }
143 queryplan {
144 SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
145 WHERE cpk=bx AND bpk=ax
146 }
147 } {tA {} tB * tC * tD *}
148 do_test where3-2.1.1 {
149 queryplan {
150 SELECT * FROM tA, tB, tC LEFT JOIN tD ON cx=dpk
151 WHERE cpk=bx AND bpk=ax
152 }
153 } {tA {} tB * tC * tD *}
154 do_test where3-2.1.2 {
155 queryplan {
156 SELECT * FROM tA, tB, tC LEFT JOIN tD ON cx=dpk
157 WHERE bx=cpk AND bpk=ax
158 }
159 } {tA {} tB * tC * tD *}
160 do_test where3-2.1.3 {
161 queryplan {
162 SELECT * FROM tA, tB, tC LEFT JOIN tD ON cx=dpk
163 WHERE bx=cpk AND ax=bpk
164 }
165 } {tA {} tB * tC * tD *}
166 do_test where3-2.1.4 {
167 queryplan {
168 SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
169 WHERE bx=cpk AND ax=bpk
170 }
171 } {tA {} tB * tC * tD *}
172 do_test where3-2.1.5 {
173 queryplan {
174 SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
175 WHERE cpk=bx AND ax=bpk
176 }
177 } {tA {} tB * tC * tD *}
178 do_test where3-2.2 {
179 queryplan {
180 SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
181 WHERE cpk=bx AND apk=bx
182 }
183 } {tB {} tA * tC * tD *}
184 do_test where3-2.3 {
185 queryplan {
186 SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
187 WHERE cpk=bx AND apk=bx
188 }
189 } {tB {} tA * tC * tD *}
190 do_test where3-2.4 {
191 queryplan {
192 SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
193 WHERE apk=cx AND bpk=ax
194 }
195 } {tC {} tA * tB * tD *}
196 do_test where3-2.5 {
197 queryplan {
198 SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
199 WHERE cpk=ax AND bpk=cx
200 }
201 } {tA {} tC * tB * tD *}
202 do_test where3-2.6 {
203 queryplan {
204 SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
205 WHERE bpk=cx AND apk=bx
206 }
207 } {tC {} tB * tA * tD *}
208 do_test where3-2.7 {
209 queryplan {
210 SELECT * FROM tA, tB, tC LEFT JOIN tD ON dpk=cx
211 WHERE cpk=bx AND apk=cx
212 }
213 } {tB {} tC * tA * tD *}
214  
215 # Ticket [13f033c865f878953]
216 # If the outer loop must be a full table scan, do not let ANALYZE trick
217 # the planner into use a table for the outer loop that might be indexable
218 # if held until an inner loop.
219 #
220 do_execsql_test where3-3.0 {
221 CREATE TABLE t301(a INTEGER PRIMARY KEY,b,c);
222 CREATE INDEX t301c ON t301(c);
223 INSERT INTO t301 VALUES(1,2,3);
224 CREATE TABLE t302(x, y);
225 INSERT INTO t302 VALUES(4,5);
226 ANALYZE;
227 explain query plan SELECT * FROM t302, t301 WHERE t302.x=5 AND t301.a=t302.y;
228 } {
229  
230  
231 }
232 do_execsql_test where3-3.1 {
233 explain query plan
234 SELECT * FROM t301, t302 WHERE t302.x=5 AND t301.a=t302.y;
235 } {
236  
237  
238 }
239  
240 # Verify that when there are multiple tables in a join which must be
241 # full table scans that the query planner attempts put the table with
242 # the fewest number of output rows as the outer loop.
243 #
244 do_execsql_test where3-4.0 {
245 CREATE TABLE t400(a INTEGER PRIMARY KEY, b, c);
246 CREATE TABLE t401(p INTEGER PRIMARY KEY, q, r);
247 CREATE TABLE t402(x INTEGER PRIMARY KEY, y, z);
248 EXPLAIN QUERY PLAN
249 SELECT * FROM t400, t401, t402 WHERE t402.z GLOB 'abc*';
250 } {
251  
252  
253  
254 }
255 do_execsql_test where3-4.1 {
256 EXPLAIN QUERY PLAN
257 SELECT * FROM t400, t401, t402 WHERE t401.r GLOB 'abc*';
258 } {
259  
260  
261  
262 }
263 do_execsql_test where3-4.2 {
264 EXPLAIN QUERY PLAN
265 SELECT * FROM t400, t401, t402 WHERE t400.c GLOB 'abc*';
266 } {
267  
268  
269  
270 }
271  
272 # Verify that a performance regression encountered by firefox
273 # has been fixed.
274 #
275 do_execsql_test where3-5.0 {
276 CREATE TABLE aaa (id INTEGER PRIMARY KEY, type INTEGER,
277 fk INTEGER DEFAULT NULL, parent INTEGER,
278 position INTEGER, title LONGVARCHAR,
279 keyword_id INTEGER, folder_type TEXT,
280 dateAdded INTEGER, lastModified INTEGER);
281 CREATE INDEX aaa_111 ON aaa (fk, type);
282 CREATE INDEX aaa_222 ON aaa (parent, position);
283 CREATE INDEX aaa_333 ON aaa (fk, lastModified);
284 CREATE TABLE bbb (id INTEGER PRIMARY KEY, type INTEGER,
285 fk INTEGER DEFAULT NULL, parent INTEGER,
286 position INTEGER, title LONGVARCHAR,
287 keyword_id INTEGER, folder_type TEXT,
288 dateAdded INTEGER, lastModified INTEGER);
289 CREATE INDEX bbb_111 ON bbb (fk, type);
290 CREATE INDEX bbb_222 ON bbb (parent, position);
291 CREATE INDEX bbb_333 ON bbb (fk, lastModified);
292  
293 EXPLAIN QUERY PLAN
294 SELECT bbb.title AS tag_title
295 FROM aaa JOIN bbb ON bbb.id = aaa.parent
296 WHERE aaa.fk = 'constant'
297 AND LENGTH(bbb.title) > 0
298 AND bbb.parent = 4
299 ORDER BY bbb.title COLLATE NOCASE ASC;
300 } {
301  
302  
303  
304 }
305 do_execsql_test where3-5.1 {
306 EXPLAIN QUERY PLAN
307 SELECT bbb.title AS tag_title
308 FROM aaa JOIN aaa AS bbb ON bbb.id = aaa.parent
309 WHERE aaa.fk = 'constant'
310 AND LENGTH(bbb.title) > 0
311 AND bbb.parent = 4
312 ORDER BY bbb.title COLLATE NOCASE ASC;
313 } {
314  
315  
316  
317 }
318 do_execsql_test where3-5.2 {
319 EXPLAIN QUERY PLAN
320 SELECT bbb.title AS tag_title
321 FROM bbb JOIN aaa ON bbb.id = aaa.parent
322 WHERE aaa.fk = 'constant'
323 AND LENGTH(bbb.title) > 0
324 AND bbb.parent = 4
325 ORDER BY bbb.title COLLATE NOCASE ASC;
326 } {
327  
328  
329  
330 }
331 do_execsql_test where3-5.3 {
332 EXPLAIN QUERY PLAN
333 SELECT bbb.title AS tag_title
334 FROM aaa AS bbb JOIN aaa ON bbb.id = aaa.parent
335 WHERE aaa.fk = 'constant'
336 AND LENGTH(bbb.title) > 0
337 AND bbb.parent = 4
338 ORDER BY bbb.title COLLATE NOCASE ASC;
339 } {
340  
341  
342  
343 }
344  
345 finish_test