Debian Codegraph @

test/eqp.test

raw
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 ifcapable !compound {
17 finish_test
18 return
19 }
20
21 set testprefix eqp
22
23 #-------------------------------------------------------------------------
24 #
25 # eqp-1.*: Assorted tests.
26 # eqp-2.*: Tests for single select statements.
27 # eqp-3.*: Select statements that execute sub-selects.
28 # eqp-4.*: Compound select statements.
29 # ...
30 # eqp-7.*: "SELECT count(*) FROM tbl" statements (VDBE code OP_Count).
31 #
32
33 proc det {args} { uplevel do_eqp_test $args }
34
35 do_execsql_test 1.1 {
36 CREATE TABLE t1(a INT, b INT, ex TEXT);
37 CREATE INDEX i1 ON t1(a);
38 CREATE INDEX i2 ON t1(b);
39 CREATE TABLE t2(a INT, b INT, ex TEXT);
40 CREATE TABLE t3(a INT, b INT, ex TEXT);
41 }
42
43 do_eqp_test 1.2 {
44 SELECT * FROM t2, t1 WHERE t1.a=1 OR t1.b=2;
45 } {
46 QUERY PLAN
47 |--MULTI-INDEX OR
48 | |--INDEX 1
49 | | `--SEARCH t1 USING INDEX i1 (a=?)
50 | `--INDEX 2
51 | `--SEARCH t1 USING INDEX i2 (b=?)
52 `--SCAN t2
53 }
54 do_eqp_test 1.3 {
55 SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=1 OR t1.b=2;
56 } {
57 QUERY PLAN
58 |--SCAN t2
59 `--MULTI-INDEX OR
60 |--INDEX 1
61 | `--SEARCH t1 USING INDEX i1 (a=?)
62 `--INDEX 2
63 `--SEARCH t1 USING INDEX i2 (b=?)
64 }
65 do_eqp_test 1.3 {
66 SELECT a FROM t1 ORDER BY a
67 } {
68 QUERY PLAN
69 `--SCAN t1 USING COVERING INDEX i1
70 }
71 do_eqp_test 1.4 {
72 SELECT a FROM t1 ORDER BY +a
73 } {
74 QUERY PLAN
75 |--SCAN t1 USING COVERING INDEX i1
76 `--USE TEMP B-TREE FOR ORDER BY
77 }
78 do_eqp_test 1.5 {
79 SELECT a FROM t1 WHERE a=4
80 } {
81 QUERY PLAN
82 `--SEARCH t1 USING COVERING INDEX i1 (a=?)
83 }
84 do_eqp_test 1.6 {
85 SELECT DISTINCT count(*) FROM t3 GROUP BY a;
86 } {
87 QUERY PLAN
88 |--SCAN t3
89 |--USE TEMP B-TREE FOR GROUP BY
90 `--USE TEMP B-TREE FOR DISTINCT
91 }
92
93 do_eqp_test 1.7.1 {
94 SELECT * FROM t3 JOIN (SELECT 1)
95 } {
96 QUERY PLAN
97 |--CO-ROUTINE (subquery-xxxxxx)
98 | `--SCAN CONSTANT ROW
99 |--SCAN (subquery-xxxxxx)
100 `--SCAN t3
101 }
102 do_eqp_test 1.7.2 {
103 SELECT * FROM t3 JOIN (SELECT 1) AS v1
104 } {
105 QUERY PLAN
106 |--CO-ROUTINE v1
107 | `--SCAN CONSTANT ROW
108 |--SCAN v1
109 `--SCAN t3
110 }
111 do_eqp_test 1.7.3 {
112 SELECT * FROM t3 AS xx JOIN (SELECT 1) AS yy
113 } {
114 QUERY PLAN
115 |--CO-ROUTINE yy
116 | `--SCAN CONSTANT ROW
117 |--SCAN yy
118 `--SCAN xx
119 }
120
121
122 do_eqp_test 1.8 {
123 SELECT * FROM t3 JOIN (SELECT 1 UNION SELECT 2)
124 } {
125 QUERY PLAN
126 |--CO-ROUTINE (subquery-xxxxxx)
127 | `--MERGE (UNION)
128 | |--LEFT
129 | | `--SCAN CONSTANT ROW
130 | `--RIGHT
131 | `--SCAN CONSTANT ROW
132 |--SCAN (subquery-xxxxxx)
133 `--SCAN t3
134 }
135 do_eqp_test 1.9 {
136 SELECT * FROM t3 JOIN (SELECT 1 EXCEPT SELECT a FROM t3 LIMIT 17) AS abc
137 } {
138 QUERY PLAN
139 |--CO-ROUTINE abc
140 | `--MERGE (EXCEPT)
141 | |--LEFT
142 | | `--SCAN CONSTANT ROW
143 | `--RIGHT
144 | |--SCAN t3
145 | `--USE TEMP B-TREE FOR ORDER BY
146 |--SCAN t3
147 `--SCAN abc
148 }
149 do_eqp_test 1.10 {
150 SELECT * FROM t3 JOIN (SELECT 1 INTERSECT SELECT a FROM t3 LIMIT 17) AS abc
151 } {
152 QUERY PLAN
153 |--CO-ROUTINE abc
154 | `--MERGE (INTERSECT)
155 | |--LEFT
156 | | `--SCAN CONSTANT ROW
157 | `--RIGHT
158 | |--SCAN t3
159 | `--USE TEMP B-TREE FOR ORDER BY
160 |--SCAN abc
161 `--SCAN t3
162 }
163
164 do_eqp_test 1.11 {
165 SELECT * FROM t3 JOIN (SELECT 1 UNION ALL SELECT a FROM t3 LIMIT 17) abc
166 } {
167 QUERY PLAN
168 |--CO-ROUTINE abc
169 | `--COMPOUND QUERY
170 | |--LEFT-MOST SUBQUERY
171 | | `--SCAN CONSTANT ROW
172 | `--UNION ALL
173 | `--SCAN t3
174 |--SCAN abc
175 `--SCAN t3
176 }
177
178 #-------------------------------------------------------------------------
179 # Test cases eqp-2.* - tests for single select statements.
180 #
181 drop_all_tables
182 do_execsql_test 2.1 {
183 CREATE TABLE t1(x INT, y INT, ex TEXT);
184
185 CREATE TABLE t2(x INT, y INT, ex TEXT);
186 CREATE INDEX t2i1 ON t2(x);
187 }
188
189 det 2.2.1 "SELECT DISTINCT min(x), max(x) FROM t1 GROUP BY x ORDER BY 1" {
190 QUERY PLAN
191 |--SCAN t1
192 |--USE TEMP B-TREE FOR GROUP BY
193 |--USE TEMP B-TREE FOR DISTINCT
194 `--USE TEMP B-TREE FOR ORDER BY
195 }
196 det 2.2.2 "SELECT DISTINCT min(x), max(x) FROM t2 GROUP BY x ORDER BY 1" {
197 QUERY PLAN
198 |--SCAN t2 USING COVERING INDEX t2i1
199 |--USE TEMP B-TREE FOR DISTINCT
200 `--USE TEMP B-TREE FOR ORDER BY
201 }
202 det 2.2.3 "SELECT DISTINCT * FROM t1" {
203 QUERY PLAN
204 |--SCAN t1
205 `--USE TEMP B-TREE FOR DISTINCT
206 }
207 det 2.2.4 "SELECT DISTINCT * FROM t1, t2" {
208 QUERY PLAN
209 |--SCAN t1
210 |--SCAN t2
211 `--USE TEMP B-TREE FOR DISTINCT
212 }
213 det 2.2.5 "SELECT DISTINCT * FROM t1, t2 ORDER BY t1.x" {
214 QUERY PLAN
215 |--SCAN t1
216 |--SCAN t2
217 |--USE TEMP B-TREE FOR DISTINCT
218 `--USE TEMP B-TREE FOR ORDER BY
219 }
220 det 2.2.6 "SELECT DISTINCT t2.x FROM t1, t2 ORDER BY t2.x" {
221 QUERY PLAN
222 |--SCAN t2 USING COVERING INDEX t2i1
223 `--SCAN t1
224 }
225
226 det 2.3.1 "SELECT max(x) FROM t2" {
227 QUERY PLAN
228 `--SEARCH t2 USING COVERING INDEX t2i1
229 }
230 det 2.3.2 "SELECT min(x) FROM t2" {
231 QUERY PLAN
232 `--SEARCH t2 USING COVERING INDEX t2i1
233 }
234 det 2.3.3 "SELECT min(x), max(x) FROM t2" {
235 QUERY PLAN
236 `--SCAN t2 USING COVERING INDEX t2i1
237 }
238
239 det 2.4.1 "SELECT * FROM t1 WHERE rowid=?" {
240 QUERY PLAN
241 `--SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?)
242 }
243
244
245
246 #-------------------------------------------------------------------------
247 # Test cases eqp-3.* - tests for select statements that use sub-selects.
248 #
249 do_eqp_test 3.1.1 {
250 SELECT (SELECT x FROM t1 AS sub) FROM t1;
251 } {
252 QUERY PLAN
253 |--SCAN t1
254 `--SCALAR SUBQUERY xxxxxx
255 `--SCAN sub
256 }
257 do_eqp_test 3.1.2 {
258 SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub);
259 } {
260 QUERY PLAN
261 |--SCAN t1
262 `--SCALAR SUBQUERY xxxxxx
263 `--SCAN sub
264 }
265 do_eqp_test 3.1.3 {
266 SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub ORDER BY y);
267 } {
268 QUERY PLAN
269 |--SCAN t1
270 `--SCALAR SUBQUERY xxxxxx
271 |--SCAN sub
272 `--USE TEMP B-TREE FOR ORDER BY
273 }
274 do_eqp_test 3.1.4 {
275 SELECT * FROM t1 WHERE (SELECT x FROM t2 ORDER BY x);
276 } {
277 QUERY PLAN
278 |--SCAN t1
279 `--SCALAR SUBQUERY xxxxxx
280 `--SCAN t2 USING COVERING INDEX t2i1
281 }
282
283 det 3.2.1 {
284 SELECT * FROM (SELECT * FROM t1 ORDER BY x LIMIT 10) ORDER BY y LIMIT 5
285 } {
286 QUERY PLAN
287 |--CO-ROUTINE (subquery-xxxxxx)
288 | |--SCAN t1
289 | `--USE TEMP B-TREE FOR ORDER BY
290 |--SCAN (subquery-xxxxxx)
291 `--USE TEMP B-TREE FOR ORDER BY
292 }
293
294 det 3.2.2 {
295 SELECT * FROM
296 (SELECT * FROM t1 ORDER BY x LIMIT 10) AS x1,
297 (SELECT * FROM t2 ORDER BY x LIMIT 10) AS x2
298 ORDER BY x2.y LIMIT 5
299 } {
300 QUERY PLAN
301 |--CO-ROUTINE x1
302 | |--SCAN t1
303 | `--USE TEMP B-TREE FOR ORDER BY
304 |--MATERIALIZE x2
305 | `--SCAN t2 USING INDEX t2i1
306 |--SCAN x1
307 |--SCAN x2
308 `--USE TEMP B-TREE FOR ORDER BY
309 }
310
311 det 3.2.3 {
312 SELECT * FROM (SELECT * FROM t1 ORDER BY x LIMIT 10) ORDER BY x LIMIT 5
313 } {
314 QUERY PLAN
315 |--CO-ROUTINE (subquery-xxxxxx)
316 | |--SCAN t1
317 | `--USE TEMP B-TREE FOR ORDER BY
318 `--SCAN (subquery-xxxxxx)
319 }
320
321 det 3.3.1 {
322 SELECT * FROM t1 WHERE y IN (SELECT y FROM t2)
323 } {
324 QUERY PLAN
325 |--SCAN t1
326 `--LIST SUBQUERY xxxxxx
327 |--SCAN t2
328 `--CREATE BLOOM FILTER
329 }
330 det 3.3.2 {
331 SELECT * FROM t1 WHERE y IN (SELECT y FROM t2 WHERE t1.x!=t2.x)
332 } {
333 QUERY PLAN
334 |--SCAN t1
335 `--CORRELATED LIST SUBQUERY xxxxxx
336 `--SCAN t2
337 }
338 det 3.3.3 {
339 SELECT * FROM t1 WHERE EXISTS (SELECT y FROM t2 WHERE t1.x!=t2.x)
340 } {
341 QUERY PLAN
342 |--SCAN t1
343 `--SCAN t2 EXISTS
344 }
345
346 #-------------------------------------------------------------------------
347 # Test cases eqp-4.* - tests for composite select statements.
348 #
349 do_eqp_test 4.1.1 {
350 SELECT * FROM t1 UNION ALL SELECT * FROM t2
351 } {
352 QUERY PLAN
353 `--COMPOUND QUERY
354 |--LEFT-MOST SUBQUERY
355 | `--SCAN t1
356 `--UNION ALL
357 `--SCAN t2
358 }
359 do_eqp_test 4.1.2 {
360 SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 2
361 } {
362 QUERY PLAN
363 `--MERGE (UNION ALL)
364 |--LEFT
365 | |--SCAN t1
366 | `--USE TEMP B-TREE FOR ORDER BY
367 `--RIGHT
368 |--SCAN t2
369 `--USE TEMP B-TREE FOR ORDER BY
370 }
371 do_eqp_test 4.1.3 {
372 SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 2
373 } {
374 QUERY PLAN
375 `--MERGE (UNION)
376 |--LEFT
377 | |--SCAN t1
378 | `--USE TEMP B-TREE FOR ORDER BY
379 `--RIGHT
380 |--SCAN t2
381 `--USE TEMP B-TREE FOR ORDER BY
382 }
383 do_eqp_test 4.1.4 {
384 SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 2
385 } {
386 QUERY PLAN
387 `--MERGE (INTERSECT)
388 |--LEFT
389 | |--SCAN t1
390 | `--USE TEMP B-TREE FOR ORDER BY
391 `--RIGHT
392 |--SCAN t2
393 `--USE TEMP B-TREE FOR ORDER BY
394 }
395 do_eqp_test 4.1.5 {
396 SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 2
397 } {
398 QUERY PLAN
399 `--MERGE (EXCEPT)
400 |--LEFT
401 | |--SCAN t1
402 | `--USE TEMP B-TREE FOR ORDER BY
403 `--RIGHT
404 |--SCAN t2
405 `--USE TEMP B-TREE FOR ORDER BY
406 }
407
408 do_eqp_test 4.2.2 {
409 SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 1
410 } {
411 QUERY PLAN
412 `--MERGE (UNION ALL)
413 |--LEFT
414 | |--SCAN t1
415 | `--USE TEMP B-TREE FOR ORDER BY
416 `--RIGHT
417 `--SCAN t2 USING INDEX t2i1
418 }
419 do_eqp_test 4.2.3 {
420 SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 1
421 } {
422 QUERY PLAN
423 `--MERGE (UNION)
424 |--LEFT
425 | |--SCAN t1
426 | `--USE TEMP B-TREE FOR ORDER BY
427 `--RIGHT
428 |--SCAN t2 USING INDEX t2i1
429 `--USE TEMP B-TREE FOR LAST 2 TERMS OF ORDER BY
430 }
431 do_eqp_test 4.2.4 {
432 SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 1
433 } {
434 QUERY PLAN
435 `--MERGE (INTERSECT)
436 |--LEFT
437 | |--SCAN t1
438 | `--USE TEMP B-TREE FOR ORDER BY
439 `--RIGHT
440 |--SCAN t2 USING INDEX t2i1
441 `--USE TEMP B-TREE FOR LAST 2 TERMS OF ORDER BY
442 }
443 do_eqp_test 4.2.5 {
444 SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 1
445 } {
446 QUERY PLAN
447 `--MERGE (EXCEPT)
448 |--LEFT
449 | |--SCAN t1
450 | `--USE TEMP B-TREE FOR ORDER BY
451 `--RIGHT
452 |--SCAN t2 USING INDEX t2i1
453 `--USE TEMP B-TREE FOR LAST 2 TERMS OF ORDER BY
454 }
455
456 do_eqp_test 4.3.1 {
457 SELECT x FROM t1 UNION SELECT x FROM t2
458 } {
459 QUERY PLAN
460 `--MERGE (UNION)
461 |--LEFT
462 | |--SCAN t1
463 | `--USE TEMP B-TREE FOR ORDER BY
464 `--RIGHT
465 `--SCAN t2 USING COVERING INDEX t2i1
466 }
467
468 do_eqp_test 4.3.2 {
469 SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1
470 } {
471 QUERY PLAN
472 `--MERGE (UNION)
473 |--LEFT
474 | `--MERGE (UNION)
475 | |--LEFT
476 | | |--SCAN t1
477 | | `--USE TEMP B-TREE FOR ORDER BY
478 | `--RIGHT
479 | `--SCAN t2 USING COVERING INDEX t2i1
480 `--RIGHT
481 |--SCAN t1
482 `--USE TEMP B-TREE FOR ORDER BY
483 }
484 do_eqp_test 4.3.3 {
485 SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1 ORDER BY 1
486 } {
487 QUERY PLAN
488 `--MERGE (UNION)
489 |--LEFT
490 | `--MERGE (UNION)
491 | |--LEFT
492 | | |--SCAN t1
493 | | `--USE TEMP B-TREE FOR ORDER BY
494 | `--RIGHT
495 | `--SCAN t2 USING COVERING INDEX t2i1
496 `--RIGHT
497 |--SCAN t1
498 `--USE TEMP B-TREE FOR ORDER BY
499 }
500
501 if 0 {
502 #-------------------------------------------------------------------------
503 # This next block of tests verifies that the examples on the
504 # lang_explain.html page are correct.
505 #
506 drop_all_tables
507
508 # XVIDENCE-OF: R-47779-47605 sqlite> EXPLAIN QUERY PLAN SELECT a, b
509 # FROM t1 WHERE a=1;
510 # 0|0|0|SCAN t1
511 #
512 do_execsql_test 5.1.0 { CREATE TABLE t1(a INT, b INT, ex TEXT) }
513 det 5.1.1 "SELECT a, b FROM t1 WHERE a=1" {
514 0 0 0 {SCAN t1}
515 }
516
517 # XVIDENCE-OF: R-55852-17599 sqlite> CREATE INDEX i1 ON t1(a);
518 # sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
519 # 0|0|0|SEARCH t1 USING INDEX i1
520 #
521 do_execsql_test 5.2.0 { CREATE INDEX i1 ON t1(a) }
522 det 5.2.1 "SELECT a, b FROM t1 WHERE a=1" {
523 0 0 0 {SEARCH t1 USING INDEX i1 (a=?)}
524 }
525
526 # XVIDENCE-OF: R-21179-11011 sqlite> CREATE INDEX i2 ON t1(a, b);
527 # sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
528 # 0|0|0|SEARCH t1 USING COVERING INDEX i2 (a=?)
529 #
530 do_execsql_test 5.3.0 { CREATE INDEX i2 ON t1(a, b) }
531 det 5.3.1 "SELECT a, b FROM t1 WHERE a=1" {
532 0 0 0 {SEARCH t1 USING COVERING INDEX i2 (a=?)}
533 }
534
535 # XVIDENCE-OF: R-09991-48941 sqlite> EXPLAIN QUERY PLAN
536 # SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2;
537 # 0|0|0|SEARCH t1 USING COVERING INDEX i2 (a=? AND b>?)
538 # 0|1|1|SCAN t2
539 #
540 do_execsql_test 5.4.0 {CREATE TABLE t2(c INT, d INT, ex TEXT)}
541 det 5.4.1 "SELECT t1.a, t2.c FROM t1, t2 WHERE t1.a=1 AND t1.b>2" {
542 0 0 0 {SEARCH t1 USING COVERING INDEX i2 (a=? AND b>?)}
543 0 1 1 {SCAN t2}
544 }
545
546 # XVIDENCE-OF: R-33626-61085 sqlite> EXPLAIN QUERY PLAN
547 # SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2;
548 # 0|0|1|SEARCH t1 USING COVERING INDEX i2 (a=? AND b>?)
549 # 0|1|0|SCAN t2
550 #
551 det 5.5 "SELECT t1.a, t2.c FROM t2, t1 WHERE t1.a=1 AND t1.b>2" {
552 0 0 1 {SEARCH t1 USING COVERING INDEX i2 (a=? AND b>?)}
553 0 1 0 {SCAN t2}
554 }
555
556 # XVIDENCE-OF: R-04002-25654 sqlite> CREATE INDEX i3 ON t1(b);
557 # sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2;
558 # 0|0|0|SEARCH t1 USING COVERING INDEX i2 (a=?)
559 # 0|0|0|SEARCH t1 USING INDEX i3 (b=?)
560 #
561 do_execsql_test 5.5.0 {CREATE INDEX i3 ON t1(b)}
562 det 5.6.1 "SELECT a, b FROM t1 WHERE a=1 OR b=2" {
563 0 0 0 {SEARCH t1 USING COVERING INDEX i2 (a=?)}
564 0 0 0 {SEARCH t1 USING INDEX i3 (b=?)}
565 }
566
567 # XVIDENCE-OF: R-24577-38891 sqlite> EXPLAIN QUERY PLAN
568 # SELECT c, d FROM t2 ORDER BY c;
569 # 0|0|0|SCAN t2
570 # 0|0|0|USE TEMP B-TREE FOR ORDER BY
571 #
572 det 5.7 "SELECT c, d FROM t2 ORDER BY c" {
573 0 0 0 {SCAN t2}
574 0 0 0 {USE TEMP B-TREE FOR ORDER BY}
575 }
576
577 # XVIDENCE-OF: R-58157-12355 sqlite> CREATE INDEX i4 ON t2(c);
578 # sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c;
579 # 0|0|0|SCAN t2 USING INDEX i4
580 #
581 do_execsql_test 5.8.0 {CREATE INDEX i4 ON t2(c)}
582 det 5.8.1 "SELECT c, d FROM t2 ORDER BY c" {
583 0 0 0 {SCAN t2 USING INDEX i4}
584 }
585
586 # XVIDENCE-OF: R-13931-10421 sqlite> EXPLAIN QUERY PLAN SELECT
587 # (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2;
588 # 0|0|0|SCAN t2
589 # 0|0|0|EXECUTE SCALAR SUBQUERY 1
590 # 1|0|0|SEARCH t1 USING COVERING INDEX i2 (a=?)
591 # 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
592 # 2|0|0|SEARCH t1 USING INDEX i3 (b=?)
593 #
594 det 5.9 {
595 SELECT (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2
596 } {
597 0 0 0 {SCAN t2 USING COVERING INDEX i4}
598 0 0 0 {EXECUTE SCALAR SUBQUERY 1}
599 1 0 0 {SEARCH t1 USING COVERING INDEX i2 (a=?)}
600 0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2}
601 2 0 0 {SEARCH t1 USING INDEX i3 (b=?)}
602 }
603
604 # XVIDENCE-OF: R-50892-45943 sqlite> EXPLAIN QUERY PLAN
605 # SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x;
606 # 1|0|0|SCAN t1 USING COVERING INDEX i2
607 # 0|0|0|SCAN SUBQUERY 1
608 # 0|0|0|USE TEMP B-TREE FOR GROUP BY
609 #
610 det 5.10 {
611 SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x
612 } {
613 1 0 0 {SCAN t1 USING COVERING INDEX i2}
614 0 0 0 {SCAN SUBQUERY 1}
615 0 0 0 {USE TEMP B-TREE FOR GROUP BY}
616 }
617
618 # XVIDENCE-OF: R-46219-33846 sqlite> EXPLAIN QUERY PLAN
619 # SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1;
620 # 0|0|0|SEARCH t2 USING INDEX i4 (c=?)
621 # 0|1|1|SCAN t1
622 #
623 det 5.11 "SELECT a, b FROM (SELECT * FROM t2 WHERE c=1), t1" {
624 0 0 0 {SEARCH t2 USING INDEX i4 (c=?)}
625 0 1 1 {SCAN t1 USING COVERING INDEX i2}
626 }
627
628 # XVIDENCE-OF: R-37879-39987 sqlite> EXPLAIN QUERY PLAN
629 # SELECT a FROM t1 UNION SELECT c FROM t2;
630 # 1|0|0|SCAN t1
631 # 2|0|0|SCAN t2
632 # 0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)
633 #
634 det 5.12 "SELECT a,b FROM t1 UNION SELECT c, 99 FROM t2" {
635 1 0 0 {SCAN t1 USING COVERING INDEX i2}
636 2 0 0 {SCAN t2 USING COVERING INDEX i4}
637 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)}
638 }
639
640 # XVIDENCE-OF: R-44864-63011 sqlite> EXPLAIN QUERY PLAN
641 # SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1;
642 # 1|0|0|SCAN t1 USING COVERING INDEX i2
643 # 2|0|0|SCAN t2 2|0|0|USE TEMP B-TREE FOR ORDER BY
644 # 0|0|0|COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)
645 #
646 det 5.13 "SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1" {
647 1 0 0 {SCAN t1 USING COVERING INDEX i1}
648 2 0 0 {SCAN t2}
649 2 0 0 {USE TEMP B-TREE FOR ORDER BY}
650 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)}
651 }
652
653 if {![nonzero_reserved_bytes]} {
654 #-------------------------------------------------------------------------
655 # The following tests - eqp-6.* - test that the example C code on
656 # documentation page eqp.html works. The C code is duplicated in test1.c
657 # and wrapped in Tcl command [print_explain_query_plan]
658 #
659 set boilerplate {
660 proc explain_query_plan {db sql} {
661 set stmt [sqlite3_prepare_v2 db $sql -1 DUMMY]
662 print_explain_query_plan $stmt
663 sqlite3_finalize $stmt
664 }
665 sqlite3 db test.db
666 explain_query_plan db {%SQL%}
667 db close
668 exit
669 }
670
671 # Do a "Print Explain Query Plan" test.
672 proc do_peqp_test {tn sql res} {
673 set fd [open script.tcl w]
674 puts $fd [string map [list %SQL% $sql] $::boilerplate]
675 close $fd
676
677 uplevel do_test $tn [list {
678 set fd [open "|[info nameofexec] script.tcl"]
679 set data [read $fd]
680 close $fd
681 set data
682 }] [list $res]
683 }
684
685 do_peqp_test 6.1 {
686 SELECT a, b FROM t1 EXCEPT SELECT d, 99 FROM t2 ORDER BY 1
687 } [string trimleft {
688 1 0 0 SCAN t1 USING COVERING INDEX i2
689 2 0 0 SCAN t2
690 2 0 0 USE TEMP B-TREE FOR ORDER BY
691 0 0 0 COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)
692 }]
693 }
694 }
695
696 #-------------------------------------------------------------------------
697 # The following tests - eqp-7.* - test that queries that use the OP_Count
698 # optimization return something sensible with EQP.
699 #
700 drop_all_tables
701
702 do_execsql_test 7.0 {
703 CREATE TABLE t1(a INT, b INT, ex CHAR(100));
704 CREATE TABLE t2(a INT, b INT, ex CHAR(100));
705 CREATE INDEX i1 ON t2(a);
706 }
707
708 det 7.1 "SELECT count(*) FROM t1" {
709 QUERY PLAN
710 `--SCAN t1
711 }
712
713 det 7.2 "SELECT count(*) FROM t2" {
714 QUERY PLAN
715 `--SCAN t2 USING COVERING INDEX i1
716 }
717
718 do_execsql_test 7.3 {
719 INSERT INTO t1(a,b) VALUES(1, 2);
720 INSERT INTO t1(a,b) VALUES(3, 4);
721
722 INSERT INTO t2(a,b) VALUES(1, 2);
723 INSERT INTO t2(a,b) VALUES(3, 4);
724 INSERT INTO t2(a,b) VALUES(5, 6);
725
726 ANALYZE;
727 }
728
729 db close
730 sqlite3 db test.db
731
732 det 7.4 "SELECT count(*) FROM t1" {
733 QUERY PLAN
734 `--SCAN t1
735 }
736
737 det 7.5 "SELECT count(*) FROM t2" {
738 QUERY PLAN
739 `--SCAN t2 USING COVERING INDEX i1
740 }
741
742 #-------------------------------------------------------------------------
743 # The following tests - eqp-8.* - test that queries that use the OP_Count
744 # optimization return something sensible with EQP.
745 #
746 drop_all_tables
747
748 do_execsql_test 8.0 {
749 CREATE TABLE t1(a, b, c, PRIMARY KEY(b, c)) WITHOUT ROWID;
750 CREATE TABLE t2(a, b, c);
751 }
752
753 det 8.1.1 "SELECT * FROM t2" {
754 QUERY PLAN
755 `--SCAN t2
756 }
757
758 det 8.1.2 "SELECT * FROM t2 WHERE rowid=?" {
759 QUERY PLAN
760 `--SEARCH t2 USING INTEGER PRIMARY KEY (rowid=?)
761 }
762
763 det 8.1.3 "SELECT count(*) FROM t2" {
764 QUERY PLAN
765 `--SCAN t2
766 }
767
768 det 8.2.1 "SELECT * FROM t1" {
769 QUERY PLAN
770 `--SCAN t1
771 }
772
773 det 8.2.2 "SELECT * FROM t1 WHERE b=?" {
774 QUERY PLAN
775 `--SEARCH t1 USING PRIMARY KEY (b=?)
776 }
777
778 det 8.2.3 "SELECT * FROM t1 WHERE b=? AND c=?" {
779 QUERY PLAN
780 `--SEARCH t1 USING PRIMARY KEY (b=? AND c=?)
781 }
782
783 det 8.2.4 "SELECT count(*) FROM t1" {
784 QUERY PLAN
785 `--SCAN t1
786 }
787
788 # 2018-08-16: While working on Fossil I discovered that EXPLAIN QUERY PLAN
789 # did not describe IN operators implemented using a ROWID lookup. These
790 # test cases ensure that problem as been fixed.
791 #
792 do_execsql_test 9.0 {
793 -- Schema from Fossil 2018-08-16
794 CREATE TABLE forumpost(
795 fpid INTEGER PRIMARY KEY,
796 froot INT,
797 fprev INT,
798 firt INT,
799 fmtime REAL
800 );
801 CREATE INDEX forumthread ON forumpost(froot,fmtime);
802 CREATE TABLE blob(
803 rid INTEGER PRIMARY KEY,
804 rcvid INTEGER,
805 size INTEGER,
806 uuid TEXT UNIQUE NOT NULL,
807 content BLOB,
808 CHECK( length(uuid)>=40 AND rid>0 )
809 );
810 CREATE TABLE event(
811 type TEXT,
812 mtime DATETIME,
813 objid INTEGER PRIMARY KEY,
814 tagid INTEGER,
815 uid INTEGER REFERENCES user,
816 bgcolor TEXT,
817 euser TEXT,
818 user TEXT,
819 ecomment TEXT,
820 comment TEXT,
821 brief TEXT,
822 omtime DATETIME
823 );
824 CREATE INDEX event_i1 ON event(mtime);
825 CREATE TABLE private(rid INTEGER PRIMARY KEY);
826 }
827 optimization_control db order-by-subquery off
828 do_eqp_test 9.1 {
829 WITH thread(age,duration,cnt,root,last) AS (
830 SELECT
831 julianday('now') - max(fmtime) AS age,
832 max(fmtime) - min(fmtime) AS duration,
833 sum(fprev IS NULL) AS msg_count,
834 froot,
835 (SELECT fpid FROM forumpost
836 WHERE froot=x.froot
837 AND fpid NOT IN private
838 ORDER BY fmtime DESC LIMIT 1)
839 FROM forumpost AS x
840 WHERE fpid NOT IN private --- Ensure this table mentioned in EQP output!
841 GROUP BY froot
842 ORDER BY 1 LIMIT 26 OFFSET 5
843 )
844 SELECT
845 thread.age,
846 thread.duration,
847 thread.cnt,
848 blob.uuid,
849 substr(event.comment,instr(event.comment,':')+1)
850 FROM thread, blob, event
851 WHERE blob.rid=thread.last
852 AND event.objid=thread.last
853 ORDER BY 1;
854 } {
855 QUERY PLAN
856 |--CO-ROUTINE thread
857 | |--SCAN x USING INDEX forumthread
858 | |--USING ROWID SEARCH ON TABLE private FOR IN-OPERATOR
859 | |--CORRELATED SCALAR SUBQUERY xxxxxx
860 | | |--SEARCH forumpost USING COVERING INDEX forumthread (froot=?)
861 | | `--USING ROWID SEARCH ON TABLE private FOR IN-OPERATOR
862 | `--USE TEMP B-TREE FOR ORDER BY
863 |--SCAN thread
864 |--SEARCH blob USING INTEGER PRIMARY KEY (rowid=?)
865 |--SEARCH event USING INTEGER PRIMARY KEY (rowid=?)
866 `--USE TEMP B-TREE FOR ORDER BY
867 }
868 optimization_control db all on
869 db cache flush
870 do_eqp_test 9.2 {
871 WITH thread(age,duration,cnt,root,last) AS (
872 SELECT
873 julianday('now') - max(fmtime) AS age,
874 max(fmtime) - min(fmtime) AS duration,
875 sum(fprev IS NULL) AS msg_count,
876 froot,
877 (SELECT fpid FROM forumpost
878 WHERE froot=x.froot
879 AND fpid NOT IN private
880 ORDER BY fmtime DESC LIMIT 1)
881 FROM forumpost AS x
882 WHERE fpid NOT IN private --- Ensure this table mentioned in EQP output!
883 GROUP BY froot
884 ORDER BY 1 LIMIT 26 OFFSET 5
885 )
886 SELECT
887 thread.age,
888 thread.duration,
889 thread.cnt,
890 blob.uuid,
891 substr(event.comment,instr(event.comment,':')+1)
892 FROM thread, blob, event
893 WHERE blob.rid=thread.last
894 AND event.objid=thread.last
895 ORDER BY 1;
896 } {
897 QUERY PLAN
898 |--CO-ROUTINE thread
899 | |--SCAN x USING INDEX forumthread
900 | |--USING ROWID SEARCH ON TABLE private FOR IN-OPERATOR
901 | |--CORRELATED SCALAR SUBQUERY xxxxxx
902 | | |--SEARCH forumpost USING COVERING INDEX forumthread (froot=?)
903 | | `--USING ROWID SEARCH ON TABLE private FOR IN-OPERATOR
904 | `--USE TEMP B-TREE FOR ORDER BY
905 |--SCAN thread
906 |--SEARCH blob USING INTEGER PRIMARY KEY (rowid=?)
907 `--SEARCH event USING INTEGER PRIMARY KEY (rowid=?)
908 }
909
910 finish_test