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: 0 0 1 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)}
42: 0 0 1 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~10 rows)}
43: 0 1 0 {SCAN TABLE t2 (~1000000 rows)}
44: }
45: do_eqp_test 1.3 {
46: SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=1 OR t1.b=2;
47: } {
48: 0 0 0 {SCAN TABLE t2 (~1000000 rows)}
49: 0 1 1 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)}
50: 0 1 1 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~10 rows)}
51: }
52: do_eqp_test 1.3 {
53: SELECT a FROM t1 ORDER BY a
54: } {
55: 0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)}
56: }
57: do_eqp_test 1.4 {
58: SELECT a FROM t1 ORDER BY +a
59: } {
60: 0 0 0 {SCAN TABLE t1 (~1000000 rows)}
61: 0 0 0 {USE TEMP B-TREE FOR ORDER BY}
62: }
63: do_eqp_test 1.5 {
64: SELECT a FROM t1 WHERE a=4
65: } {
66: 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}
67: }
68: do_eqp_test 1.6 {
69: SELECT DISTINCT count(*) FROM t3 GROUP BY a;
70: } {
71: 0 0 0 {SCAN TABLE t3 (~1000000 rows)}
72: 0 0 0 {USE TEMP B-TREE FOR GROUP BY}
73: 0 0 0 {USE TEMP B-TREE FOR DISTINCT}
74: }
75:
76: do_eqp_test 1.7 {
77: SELECT * FROM t3 JOIN (SELECT 1)
78: } {
79: 0 0 1 {SCAN SUBQUERY 1 (~1 rows)}
80: 0 1 0 {SCAN TABLE t3 (~1000000 rows)}
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: 0 0 1 {SCAN SUBQUERY 1 (~2 rows)}
87: 0 1 0 {SCAN TABLE t3 (~1000000 rows)}
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: 0 0 1 {SCAN SUBQUERY 1 (~17 rows)}
95: 0 1 0 {SCAN TABLE t3 (~1000000 rows)}
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: 0 0 1 {SCAN SUBQUERY 1 (~1 rows)}
103: 0 1 0 {SCAN TABLE t3 (~1000000 rows)}
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: 0 0 1 {SCAN SUBQUERY 1 (~17 rows)}
112: 0 1 0 {SCAN TABLE t3 (~1000000 rows)}
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: 0 0 0 {SCAN TABLE t1 (~1000000 rows)}
128: 0 0 0 {USE TEMP B-TREE FOR GROUP BY}
129: 0 0 0 {USE TEMP B-TREE FOR DISTINCT}
130: 0 0 0 {USE TEMP B-TREE FOR ORDER BY}
131: }
132: det 2.2.2 "SELECT DISTINCT min(x), max(x) FROM t2 GROUP BY x ORDER BY 1" {
133: 0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)}
134: 0 0 0 {USE TEMP B-TREE FOR DISTINCT}
135: 0 0 0 {USE TEMP B-TREE FOR ORDER BY}
136: }
137: det 2.2.3 "SELECT DISTINCT * FROM t1" {
138: 0 0 0 {SCAN TABLE t1 (~1000000 rows)}
139: 0 0 0 {USE TEMP B-TREE FOR DISTINCT}
140: }
141: det 2.2.4 "SELECT DISTINCT * FROM t1, t2" {
142: 0 0 0 {SCAN TABLE t1 (~1000000 rows)}
143: 0 1 1 {SCAN TABLE t2 (~1000000 rows)}
144: 0 0 0 {USE TEMP B-TREE FOR DISTINCT}
145: }
146: det 2.2.5 "SELECT DISTINCT * FROM t1, t2 ORDER BY t1.x" {
147: 0 0 0 {SCAN TABLE t1 (~1000000 rows)}
148: 0 1 1 {SCAN TABLE t2 (~1000000 rows)}
149: 0 0 0 {USE TEMP B-TREE FOR DISTINCT}
150: 0 0 0 {USE TEMP B-TREE FOR ORDER BY}
151: }
152: det 2.2.6 "SELECT DISTINCT t2.x FROM t1, t2 ORDER BY t2.x" {
153: 0 0 1 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)}
154: 0 1 0 {SCAN TABLE t1 (~1000000 rows)}
155: }
156:
157: det 2.3.1 "SELECT max(x) FROM t2" {
158: 0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2i1 (~1 rows)}
159: }
160: det 2.3.2 "SELECT min(x) FROM t2" {
161: 0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2i1 (~1 rows)}
162: }
163: det 2.3.3 "SELECT min(x), max(x) FROM t2" {
164: 0 0 0 {SCAN TABLE t2 (~1000000 rows)}
165: }
166:
167: det 2.4.1 "SELECT * FROM t1 WHERE rowid=?" {
168: 0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
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: 0 0 0 {SCAN TABLE t1 (~1000000 rows)}
180: 0 0 0 {EXECUTE SCALAR SUBQUERY 1}
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: 0 0 0 {SCAN TABLE t1 (~1000000 rows)}
187: 0 0 0 {EXECUTE SCALAR SUBQUERY 1}
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: 0 0 0 {SCAN TABLE t1 (~1000000 rows)}
194: 0 0 0 {EXECUTE SCALAR SUBQUERY 1}
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: 0 0 0 {SCAN TABLE t1 (~1000000 rows)}
202: 0 0 0 {EXECUTE SCALAR SUBQUERY 1}
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: 0 0 0 {SCAN SUBQUERY 1 (~10 rows)}
212: 0 0 0 {USE TEMP B-TREE FOR ORDER BY}
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: 0 0 0 {SCAN SUBQUERY 1 AS x1 (~10 rows)}
224: 0 1 1 {SCAN SUBQUERY 2 AS x2 (~10 rows)}
225: 0 0 0 {USE TEMP B-TREE FOR ORDER BY}
226: }
227:
228: det 3.3.1 {
229: SELECT * FROM t1 WHERE y IN (SELECT y FROM t2)
230: } {
231: 0 0 0 {SCAN TABLE t1 (~100000 rows)}
232: 0 0 0 {EXECUTE LIST SUBQUERY 1}
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: 0 0 0 {SCAN TABLE t1 (~500000 rows)}
239: 0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1}
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: 0 0 0 {SCAN TABLE t1 (~500000 rows)}
246: 0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 1}
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: 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)}
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: 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)}
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: 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION)}
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: 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (INTERSECT)}
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: 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)}
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: 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)}
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: 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION)}
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: 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (INTERSECT)}
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: 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)}
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: 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)}
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: 0 0 0 {COMPOUND SUBQUERIES 1 AND 4 USING TEMP B-TREE (UNION)}
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: 0 0 0 {COMPOUND SUBQUERIES 1 AND 4 (UNION)}
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: 0 0 0 {SCAN TABLE t1 (~100000 rows)}
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: 0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)}
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: 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)}
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: 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) (~2 rows)}
398: 0 1 1 {SCAN TABLE t2 (~1000000 rows)}
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: 0 0 1 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) (~2 rows)}
407: 0 1 0 {SCAN TABLE t2 (~1000000 rows)}
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: 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)}
417: 0 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)}
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: 0 0 0 {SCAN TABLE t2 (~1000000 rows)}
425: 0 0 0 {USE TEMP B-TREE FOR ORDER BY}
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: 0 0 0 {SCAN TABLE t2 USING INDEX i4 (~1000000 rows)}
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: 0 0 0 {SCAN TABLE t2 (~1000000 rows)}
446: 0 0 0 {EXECUTE SCALAR SUBQUERY 1}
447: 1 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)}
448: 0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2}
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: 0 0 0 {SCAN SUBQUERY 1 (~100 rows)}
461: 0 0 0 {USE TEMP B-TREE FOR GROUP BY}
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: 0 0 0 {SEARCH TABLE t2 USING INDEX i4 (c=?) (~10 rows)}
469: 0 1 1 {SCAN TABLE t1 (~1000000 rows)}
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: 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)}
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: 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)}
492: }
493:
494:
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: 0 0 0 COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)
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: 0 0 0 {SCAN TABLE t1 (~1000000 rows)}
549: }
550:
551: det 7.2 "SELECT count(*) FROM t2" {
552: 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i1(~1000000 rows)}
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: 0 0 0 {SCAN TABLE t1 (~2 rows)}
571: }
572:
573: det 7.5 "SELECT count(*) FROM t2" {
574: 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i1(~3 rows)}
575: }
576:
577:
578: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to eqp.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