wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2010 November 6 |
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 | |||
13 | set testdir [file dirname $argv0] |
||
14 | source $testdir/tester.tcl |
||
15 | |||
16 | set testprefix eqp |
||
17 | |||
18 | #------------------------------------------------------------------------- |
||
19 | # |
||
20 | # eqp-1.*: Assorted tests. |
||
21 | # eqp-2.*: Tests for single select statements. |
||
22 | # eqp-3.*: Select statements that execute sub-selects. |
||
23 | # eqp-4.*: Compound select statements. |
||
24 | # ... |
||
25 | # eqp-7.*: "SELECT count(*) FROM tbl" statements (VDBE code OP_Count). |
||
26 | # |
||
27 | |||
28 | proc det {args} { uplevel do_eqp_test $args } |
||
29 | |||
30 | do_execsql_test 1.1 { |
||
31 | CREATE TABLE t1(a, b); |
||
32 | CREATE INDEX i1 ON t1(a); |
||
33 | CREATE INDEX i2 ON t1(b); |
||
34 | CREATE TABLE t2(a, b); |
||
35 | CREATE TABLE t3(a, b); |
||
36 | } |
||
37 | |||
38 | do_eqp_test 1.2 { |
||
39 | SELECT * FROM t2, t1 WHERE t1.a=1 OR t1.b=2; |
||
40 | } { |
||
41 | |||
42 | |||
43 | |||
44 | } |
||
45 | do_eqp_test 1.3 { |
||
46 | SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=1 OR t1.b=2; |
||
47 | } { |
||
48 | |||
49 | |||
50 | |||
51 | } |
||
52 | do_eqp_test 1.3 { |
||
53 | SELECT a FROM t1 ORDER BY a |
||
54 | } { |
||
55 | |||
56 | } |
||
57 | do_eqp_test 1.4 { |
||
58 | SELECT a FROM t1 ORDER BY +a |
||
59 | } { |
||
60 | |||
61 | |||
62 | } |
||
63 | do_eqp_test 1.5 { |
||
64 | SELECT a FROM t1 WHERE a=4 |
||
65 | } { |
||
66 | |||
67 | } |
||
68 | do_eqp_test 1.6 { |
||
69 | SELECT DISTINCT count(*) FROM t3 GROUP BY a; |
||
70 | } { |
||
71 | |||
72 | |||
73 | |||
74 | } |
||
75 | |||
76 | do_eqp_test 1.7 { |
||
77 | SELECT * FROM t3 JOIN (SELECT 1) |
||
78 | } { |
||
79 | |||
80 | |||
81 | } |
||
82 | do_eqp_test 1.8 { |
||
83 | SELECT * FROM t3 JOIN (SELECT 1 UNION SELECT 2) |
||
84 | } { |
||
85 | 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)} |
||
86 | |||
87 | |||
88 | } |
||
89 | do_eqp_test 1.9 { |
||
90 | SELECT * FROM t3 JOIN (SELECT 1 EXCEPT SELECT a FROM t3 LIMIT 17) |
||
91 | } { |
||
92 | 3 0 0 {SCAN TABLE t3 (~1000000 rows)} |
||
93 | 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (EXCEPT)} |
||
94 | |||
95 | |||
96 | } |
||
97 | do_eqp_test 1.10 { |
||
98 | SELECT * FROM t3 JOIN (SELECT 1 INTERSECT SELECT a FROM t3 LIMIT 17) |
||
99 | } { |
||
100 | 3 0 0 {SCAN TABLE t3 (~1000000 rows)} |
||
101 | 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (INTERSECT)} |
||
102 | |||
103 | |||
104 | } |
||
105 | |||
106 | do_eqp_test 1.11 { |
||
107 | SELECT * FROM t3 JOIN (SELECT 1 UNION ALL SELECT a FROM t3 LIMIT 17) |
||
108 | } { |
||
109 | 3 0 0 {SCAN TABLE t3 (~1000000 rows)} |
||
110 | 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)} |
||
111 | |||
112 | |||
113 | } |
||
114 | |||
115 | #------------------------------------------------------------------------- |
||
116 | # Test cases eqp-2.* - tests for single select statements. |
||
117 | # |
||
118 | drop_all_tables |
||
119 | do_execsql_test 2.1 { |
||
120 | CREATE TABLE t1(x, y); |
||
121 | |||
122 | CREATE TABLE t2(x, y); |
||
123 | CREATE INDEX t2i1 ON t2(x); |
||
124 | } |
||
125 | |||
126 | det 2.2.1 "SELECT DISTINCT min(x), max(x) FROM t1 GROUP BY x ORDER BY 1" { |
||
127 | |||
128 | |||
129 | |||
130 | |||
131 | } |
||
132 | det 2.2.2 "SELECT DISTINCT min(x), max(x) FROM t2 GROUP BY x ORDER BY 1" { |
||
133 | |||
134 | |||
135 | |||
136 | } |
||
137 | det 2.2.3 "SELECT DISTINCT * FROM t1" { |
||
138 | |||
139 | |||
140 | } |
||
141 | det 2.2.4 "SELECT DISTINCT * FROM t1, t2" { |
||
142 | |||
143 | |||
144 | |||
145 | } |
||
146 | det 2.2.5 "SELECT DISTINCT * FROM t1, t2 ORDER BY t1.x" { |
||
147 | |||
148 | |||
149 | |||
150 | |||
151 | } |
||
152 | det 2.2.6 "SELECT DISTINCT t2.x FROM t1, t2 ORDER BY t2.x" { |
||
153 | |||
154 | |||
155 | } |
||
156 | |||
157 | det 2.3.1 "SELECT max(x) FROM t2" { |
||
158 | |||
159 | } |
||
160 | det 2.3.2 "SELECT min(x) FROM t2" { |
||
161 | |||
162 | } |
||
163 | det 2.3.3 "SELECT min(x), max(x) FROM t2" { |
||
164 | |||
165 | } |
||
166 | |||
167 | det 2.4.1 "SELECT * FROM t1 WHERE rowid=?" { |
||
168 | |||
169 | } |
||
170 | |||
171 | |||
172 | |||
173 | #------------------------------------------------------------------------- |
||
174 | # Test cases eqp-3.* - tests for select statements that use sub-selects. |
||
175 | # |
||
176 | do_eqp_test 3.1.1 { |
||
177 | SELECT (SELECT x FROM t1 AS sub) FROM t1; |
||
178 | } { |
||
179 | |||
180 | |||
181 | 1 0 0 {SCAN TABLE t1 AS sub (~1000000 rows)} |
||
182 | } |
||
183 | do_eqp_test 3.1.2 { |
||
184 | SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub); |
||
185 | } { |
||
186 | |||
187 | |||
188 | 1 0 0 {SCAN TABLE t1 AS sub (~1000000 rows)} |
||
189 | } |
||
190 | do_eqp_test 3.1.3 { |
||
191 | SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub ORDER BY y); |
||
192 | } { |
||
193 | |||
194 | |||
195 | 1 0 0 {SCAN TABLE t1 AS sub (~1000000 rows)} |
||
196 | 1 0 0 {USE TEMP B-TREE FOR ORDER BY} |
||
197 | } |
||
198 | do_eqp_test 3.1.4 { |
||
199 | SELECT * FROM t1 WHERE (SELECT x FROM t2 ORDER BY x); |
||
200 | } { |
||
201 | |||
202 | |||
203 | 1 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)} |
||
204 | } |
||
205 | |||
206 | det 3.2.1 { |
||
207 | SELECT * FROM (SELECT * FROM t1 ORDER BY x LIMIT 10) ORDER BY y LIMIT 5 |
||
208 | } { |
||
209 | 1 0 0 {SCAN TABLE t1 (~1000000 rows)} |
||
210 | 1 0 0 {USE TEMP B-TREE FOR ORDER BY} |
||
211 | |||
212 | |||
213 | } |
||
214 | det 3.2.2 { |
||
215 | SELECT * FROM |
||
216 | (SELECT * FROM t1 ORDER BY x LIMIT 10) AS x1, |
||
217 | (SELECT * FROM t2 ORDER BY x LIMIT 10) AS x2 |
||
218 | ORDER BY x2.y LIMIT 5 |
||
219 | } { |
||
220 | 1 0 0 {SCAN TABLE t1 (~1000000 rows)} |
||
221 | 1 0 0 {USE TEMP B-TREE FOR ORDER BY} |
||
222 | 2 0 0 {SCAN TABLE t2 USING INDEX t2i1 (~1000000 rows)} |
||
223 | |||
224 | |||
225 | |||
226 | } |
||
227 | |||
228 | det 3.3.1 { |
||
229 | SELECT * FROM t1 WHERE y IN (SELECT y FROM t2) |
||
230 | } { |
||
231 | |||
232 | |||
233 | 1 0 0 {SCAN TABLE t2 (~1000000 rows)} |
||
234 | } |
||
235 | det 3.3.2 { |
||
236 | SELECT * FROM t1 WHERE y IN (SELECT y FROM t2 WHERE t1.x!=t2.x) |
||
237 | } { |
||
238 | |||
239 | |||
240 | 1 0 0 {SCAN TABLE t2 (~500000 rows)} |
||
241 | } |
||
242 | det 3.3.3 { |
||
243 | SELECT * FROM t1 WHERE EXISTS (SELECT y FROM t2 WHERE t1.x!=t2.x) |
||
244 | } { |
||
245 | |||
246 | |||
247 | 1 0 0 {SCAN TABLE t2 (~500000 rows)} |
||
248 | } |
||
249 | |||
250 | #------------------------------------------------------------------------- |
||
251 | # Test cases eqp-4.* - tests for composite select statements. |
||
252 | # |
||
253 | do_eqp_test 4.1.1 { |
||
254 | SELECT * FROM t1 UNION ALL SELECT * FROM t2 |
||
255 | } { |
||
256 | 1 0 0 {SCAN TABLE t1 (~1000000 rows)} |
||
257 | 2 0 0 {SCAN TABLE t2 (~1000000 rows)} |
||
258 | |||
259 | } |
||
260 | do_eqp_test 4.1.2 { |
||
261 | SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 2 |
||
262 | } { |
||
263 | 1 0 0 {SCAN TABLE t1 (~1000000 rows)} |
||
264 | 1 0 0 {USE TEMP B-TREE FOR ORDER BY} |
||
265 | 2 0 0 {SCAN TABLE t2 (~1000000 rows)} |
||
266 | 2 0 0 {USE TEMP B-TREE FOR ORDER BY} |
||
267 | |||
268 | } |
||
269 | do_eqp_test 4.1.3 { |
||
270 | SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 2 |
||
271 | } { |
||
272 | 1 0 0 {SCAN TABLE t1 (~1000000 rows)} |
||
273 | 1 0 0 {USE TEMP B-TREE FOR ORDER BY} |
||
274 | 2 0 0 {SCAN TABLE t2 (~1000000 rows)} |
||
275 | 2 0 0 {USE TEMP B-TREE FOR ORDER BY} |
||
276 | |||
277 | } |
||
278 | do_eqp_test 4.1.4 { |
||
279 | SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 2 |
||
280 | } { |
||
281 | 1 0 0 {SCAN TABLE t1 (~1000000 rows)} |
||
282 | 1 0 0 {USE TEMP B-TREE FOR ORDER BY} |
||
283 | 2 0 0 {SCAN TABLE t2 (~1000000 rows)} |
||
284 | 2 0 0 {USE TEMP B-TREE FOR ORDER BY} |
||
285 | |||
286 | } |
||
287 | do_eqp_test 4.1.5 { |
||
288 | SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 2 |
||
289 | } { |
||
290 | 1 0 0 {SCAN TABLE t1 (~1000000 rows)} |
||
291 | 1 0 0 {USE TEMP B-TREE FOR ORDER BY} |
||
292 | 2 0 0 {SCAN TABLE t2 (~1000000 rows)} |
||
293 | 2 0 0 {USE TEMP B-TREE FOR ORDER BY} |
||
294 | |||
295 | } |
||
296 | |||
297 | do_eqp_test 4.2.2 { |
||
298 | SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 1 |
||
299 | } { |
||
300 | 1 0 0 {SCAN TABLE t1 (~1000000 rows)} |
||
301 | 1 0 0 {USE TEMP B-TREE FOR ORDER BY} |
||
302 | 2 0 0 {SCAN TABLE t2 USING INDEX t2i1 (~1000000 rows)} |
||
303 | |||
304 | } |
||
305 | do_eqp_test 4.2.3 { |
||
306 | SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 1 |
||
307 | } { |
||
308 | 1 0 0 {SCAN TABLE t1 (~1000000 rows)} |
||
309 | 1 0 0 {USE TEMP B-TREE FOR ORDER BY} |
||
310 | 2 0 0 {SCAN TABLE t2 (~1000000 rows)} |
||
311 | 2 0 0 {USE TEMP B-TREE FOR ORDER BY} |
||
312 | |||
313 | } |
||
314 | do_eqp_test 4.2.4 { |
||
315 | SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 1 |
||
316 | } { |
||
317 | 1 0 0 {SCAN TABLE t1 (~1000000 rows)} |
||
318 | 1 0 0 {USE TEMP B-TREE FOR ORDER BY} |
||
319 | 2 0 0 {SCAN TABLE t2 (~1000000 rows)} |
||
320 | 2 0 0 {USE TEMP B-TREE FOR ORDER BY} |
||
321 | |||
322 | } |
||
323 | do_eqp_test 4.2.5 { |
||
324 | SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 1 |
||
325 | } { |
||
326 | 1 0 0 {SCAN TABLE t1 (~1000000 rows)} |
||
327 | 1 0 0 {USE TEMP B-TREE FOR ORDER BY} |
||
328 | 2 0 0 {SCAN TABLE t2 (~1000000 rows)} |
||
329 | 2 0 0 {USE TEMP B-TREE FOR ORDER BY} |
||
330 | |||
331 | } |
||
332 | |||
333 | do_eqp_test 4.3.1 { |
||
334 | SELECT x FROM t1 UNION SELECT x FROM t2 |
||
335 | } { |
||
336 | 1 0 0 {SCAN TABLE t1 (~1000000 rows)} |
||
337 | 2 0 0 {SCAN TABLE t2 (~1000000 rows)} |
||
338 | |||
339 | } |
||
340 | |||
341 | do_eqp_test 4.3.2 { |
||
342 | SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1 |
||
343 | } { |
||
344 | 2 0 0 {SCAN TABLE t1 (~1000000 rows)} |
||
345 | 3 0 0 {SCAN TABLE t2 (~1000000 rows)} |
||
346 | 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)} |
||
347 | 4 0 0 {SCAN TABLE t1 (~1000000 rows)} |
||
348 | |||
349 | } |
||
350 | do_eqp_test 4.3.3 { |
||
351 | SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1 ORDER BY 1 |
||
352 | } { |
||
353 | 2 0 0 {SCAN TABLE t1 (~1000000 rows)} |
||
354 | 2 0 0 {USE TEMP B-TREE FOR ORDER BY} |
||
355 | 3 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)} |
||
356 | 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 (UNION)} |
||
357 | 4 0 0 {SCAN TABLE t1 (~1000000 rows)} |
||
358 | 4 0 0 {USE TEMP B-TREE FOR ORDER BY} |
||
359 | |||
360 | } |
||
361 | |||
362 | #------------------------------------------------------------------------- |
||
363 | # This next block of tests verifies that the examples on the |
||
364 | # lang_explain.html page are correct. |
||
365 | # |
||
366 | drop_all_tables |
||
367 | |||
368 | # EVIDENCE-OF: R-64208-08323 sqlite> EXPLAIN QUERY PLAN SELECT a, b |
||
369 | # FROM t1 WHERE a=1; 0|0|0|SCAN TABLE t1 (~100000 rows) |
||
370 | do_execsql_test 5.1.0 { CREATE TABLE t1(a, b) } |
||
371 | det 5.1.1 "SELECT a, b FROM t1 WHERE a=1" { |
||
372 | |||
373 | } |
||
374 | |||
375 | # EVIDENCE-OF: R-09022-44606 sqlite> CREATE INDEX i1 ON t1(a); |
||
376 | # sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; |
||
377 | # 0|0|0|SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows) |
||
378 | do_execsql_test 5.2.0 { CREATE INDEX i1 ON t1(a) } |
||
379 | det 5.2.1 "SELECT a, b FROM t1 WHERE a=1" { |
||
380 | |||
381 | } |
||
382 | |||
383 | # EVIDENCE-OF: R-62228-34103 sqlite> CREATE INDEX i2 ON t1(a, b); |
||
384 | # sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; |
||
385 | # 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows) |
||
386 | do_execsql_test 5.3.0 { CREATE INDEX i2 ON t1(a, b) } |
||
387 | det 5.3.1 "SELECT a, b FROM t1 WHERE a=1" { |
||
388 | |||
389 | } |
||
390 | |||
391 | # EVIDENCE-OF: R-22253-05302 sqlite> EXPLAIN QUERY PLAN SELECT t1.*, |
||
392 | # t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2; 0|0|0|SEARCH TABLE t1 |
||
393 | # USING COVERING INDEX i2 (a=? AND b>?) (~3 rows) 0|1|1|SCAN TABLE t2 |
||
394 | # (~1000000 rows) |
||
395 | do_execsql_test 5.4.0 {CREATE TABLE t2(c, d)} |
||
396 | det 5.4.1 "SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2" { |
||
397 | |||
398 | |||
399 | } |
||
400 | |||
401 | # EVIDENCE-OF: R-21040-07025 sqlite> EXPLAIN QUERY PLAN SELECT t1.*, |
||
402 | # t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2; 0|0|1|SEARCH TABLE t1 |
||
403 | # USING COVERING INDEX i2 (a=? AND b>?) (~3 rows) 0|1|0|SCAN TABLE t2 |
||
404 | # (~1000000 rows) |
||
405 | det 5.5 "SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2" { |
||
406 | |||
407 | |||
408 | } |
||
409 | |||
410 | # EVIDENCE-OF: R-39007-61103 sqlite> CREATE INDEX i3 ON t1(b); |
||
411 | # sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2; |
||
412 | # 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows) |
||
413 | # 0|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows) |
||
414 | do_execsql_test 5.5.0 {CREATE INDEX i3 ON t1(b)} |
||
415 | det 5.6.1 "SELECT * FROM t1 WHERE a=1 OR b=2" { |
||
416 | |||
417 | |||
418 | } |
||
419 | |||
420 | # EVIDENCE-OF: R-33025-54904 sqlite> EXPLAIN QUERY PLAN SELECT c, d |
||
421 | # FROM t2 ORDER BY c; 0|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|USE TEMP |
||
422 | # B-TREE FOR ORDER BY |
||
423 | det 5.7 "SELECT c, d FROM t2 ORDER BY c" { |
||
424 | |||
425 | |||
426 | } |
||
427 | |||
428 | # EVIDENCE-OF: R-38854-22809 sqlite> CREATE INDEX i4 ON t2(c); |
||
429 | # sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c; |
||
430 | # 0|0|0|SCAN TABLE t2 USING INDEX i4 (~1000000 rows) |
||
431 | do_execsql_test 5.8.0 {CREATE INDEX i4 ON t2(c)} |
||
432 | det 5.8.1 "SELECT c, d FROM t2 ORDER BY c" { |
||
433 | |||
434 | } |
||
435 | |||
436 | # EVIDENCE-OF: R-29884-43993 sqlite> EXPLAIN QUERY PLAN SELECT |
||
437 | # (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2; |
||
438 | # 0|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|EXECUTE SCALAR SUBQUERY 1 |
||
439 | # 1|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows) |
||
440 | # 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2 2|0|0|SEARCH TABLE t1 USING |
||
441 | # INDEX i3 (b=?) (~10 rows) |
||
442 | det 5.9 { |
||
443 | SELECT (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2 |
||
444 | } { |
||
445 | |||
446 | |||
447 | 1 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)} |
||
448 | |||
449 | 2 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)} |
||
450 | } |
||
451 | |||
452 | # EVIDENCE-OF: R-17911-16445 sqlite> EXPLAIN QUERY PLAN SELECT |
||
453 | # count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x; |
||
454 | # 1|0|0|SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows) 0|0|0|SCAN |
||
455 | # SUBQUERY 1 (~1000000 rows) 0|0|0|USE TEMP B-TREE FOR GROUP BY |
||
456 | det 5.10 { |
||
457 | SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x |
||
458 | } { |
||
459 | 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows)} |
||
460 | |||
461 | |||
462 | } |
||
463 | |||
464 | # EVIDENCE-OF: R-18544-33103 sqlite> EXPLAIN QUERY PLAN SELECT * FROM |
||
465 | # (SELECT * FROM t2 WHERE c=1), t1; 0|0|0|SEARCH TABLE t2 USING INDEX i4 |
||
466 | # (c=?) (~10 rows) 0|1|1|SCAN TABLE t1 (~1000000 rows) |
||
467 | det 5.11 "SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1" { |
||
468 | |||
469 | |||
470 | } |
||
471 | |||
472 | # EVIDENCE-OF: R-40701-42164 sqlite> EXPLAIN QUERY PLAN SELECT a FROM |
||
473 | # t1 UNION SELECT c FROM t2; 1|0|0|SCAN TABLE t1 (~1000000 rows) |
||
474 | # 2|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|COMPOUND SUBQUERIES 1 AND 2 |
||
475 | # USING TEMP B-TREE (UNION) |
||
476 | det 5.12 "SELECT a FROM t1 UNION SELECT c FROM t2" { |
||
477 | 1 0 0 {SCAN TABLE t1 (~1000000 rows)} |
||
478 | 2 0 0 {SCAN TABLE t2 (~1000000 rows)} |
||
479 | |||
480 | } |
||
481 | |||
482 | # EVIDENCE-OF: R-61538-24748 sqlite> EXPLAIN QUERY PLAN SELECT a FROM |
||
483 | # t1 EXCEPT SELECT d FROM t2 ORDER BY 1; 1|0|0|SCAN TABLE t1 USING |
||
484 | # COVERING INDEX i2 (~1000000 rows) 2|0|0|SCAN TABLE t2 (~1000000 rows) |
||
485 | # 2|0|0|USE TEMP B-TREE FOR ORDER BY 0|0|0|COMPOUND SUBQUERIES 1 AND 2 |
||
486 | # (EXCEPT) |
||
487 | det 5.13 "SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1" { |
||
488 | 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows)} |
||
489 | 2 0 0 {SCAN TABLE t2 (~1000000 rows)} |
||
490 | 2 0 0 {USE TEMP B-TREE FOR ORDER BY} |
||
491 | |||
492 | } |
||
493 | |||
494 | if 0 { |
||
495 | #------------------------------------------------------------------------- |
||
496 | # The following tests - eqp-6.* - test that the example C code on |
||
497 | # documentation page eqp.html works. The C code is duplicated in test1.c |
||
498 | # and wrapped in Tcl command [print_explain_query_plan] |
||
499 | # |
||
500 | set boilerplate { |
||
501 | proc explain_query_plan {db sql} { |
||
502 | set stmt [sqlite3_prepare_v2 db $sql -1 DUMMY] |
||
503 | print_explain_query_plan $stmt |
||
504 | sqlite3_finalize $stmt |
||
505 | } |
||
506 | sqlite3 db test.db |
||
507 | explain_query_plan db {%SQL%} |
||
508 | db close |
||
509 | exit |
||
510 | } |
||
511 | |||
512 | # Do a "Print Explain Query Plan" test. |
||
513 | proc do_peqp_test {tn sql res} { |
||
514 | set fd [open script.tcl w] |
||
515 | puts $fd [string map [list %SQL% $sql] $::boilerplate] |
||
516 | close $fd |
||
517 | |||
518 | uplevel do_test $tn [list { |
||
519 | set fd [open "|[info nameofexec] script.tcl"] |
||
520 | set data [read $fd] |
||
521 | close $fd |
||
522 | set data |
||
523 | }] [list $res] |
||
524 | } |
||
525 | |||
526 | do_peqp_test 6.1 { |
||
527 | SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1 |
||
528 | } [string trimleft { |
||
529 | 1 0 0 SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows) |
||
530 | 2 0 0 SCAN TABLE t2 (~1000000 rows) |
||
531 | 2 0 0 USE TEMP B-TREE FOR ORDER BY |
||
532 | |||
533 | }] |
||
534 | } |
||
535 | #------------------------------------------------------------------------- |
||
536 | # The following tests - eqp-7.* - test that queries that use the OP_Count |
||
537 | # optimization return something sensible with EQP. |
||
538 | # |
||
539 | drop_all_tables |
||
540 | |||
541 | do_execsql_test 7.0 { |
||
542 | CREATE TABLE t1(a, b); |
||
543 | CREATE TABLE t2(a, b); |
||
544 | CREATE INDEX i1 ON t2(a); |
||
545 | } |
||
546 | |||
547 | det 7.1 "SELECT count(*) FROM t1" { |
||
548 | |||
549 | } |
||
550 | |||
551 | det 7.2 "SELECT count(*) FROM t2" { |
||
552 | |||
553 | } |
||
554 | |||
555 | do_execsql_test 7.3 { |
||
556 | INSERT INTO t1 VALUES(1, 2); |
||
557 | INSERT INTO t1 VALUES(3, 4); |
||
558 | |||
559 | INSERT INTO t2 VALUES(1, 2); |
||
560 | INSERT INTO t2 VALUES(3, 4); |
||
561 | INSERT INTO t2 VALUES(5, 6); |
||
562 | |||
563 | ANALYZE; |
||
564 | } |
||
565 | |||
566 | db close |
||
567 | sqlite3 db test.db |
||
568 | |||
569 | det 7.4 "SELECT count(*) FROM t1" { |
||
570 | |||
571 | } |
||
572 | |||
573 | det 7.5 "SELECT count(*) FROM t2" { |
||
574 | |||
575 | } |
||
576 | |||
577 | |||
578 | finish_test |