3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
13 set testdir [file dirname $argv0]
14 source $testdir/tester.tcl
23 #-------------------------------------------------------------------------
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.
30 # eqp-7.*: "SELECT count(*) FROM tbl" statements (VDBE code OP_Count).
33 proc det {args} { uplevel do_eqp_test $args }
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);
44 SELECT * FROM t2, t1 WHERE t1.a=1 OR t1.b=2;
48 | |--SEARCH TABLE t1 USING INDEX i1 (a=?)
49 | `--SEARCH TABLE t1 USING INDEX i2 (b=?)
53 SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=1 OR t1.b=2;
58 |--SEARCH TABLE t1 USING INDEX i1 (a=?)
59 `--SEARCH TABLE t1 USING INDEX i2 (b=?)
62 SELECT a FROM t1 ORDER BY a
65 `--SCAN TABLE t1 USING COVERING INDEX i1
68 SELECT a FROM t1 ORDER BY +a
71 |--SCAN TABLE t1 USING COVERING INDEX i1
72 `--USE TEMP B-TREE FOR ORDER BY
75 SELECT a FROM t1 WHERE a=4
78 `--SEARCH TABLE t1 USING COVERING INDEX i1 (a=?)
81 SELECT DISTINCT count(*) FROM t3 GROUP BY a;
85 |--USE TEMP B-TREE FOR GROUP BY
86 `--USE TEMP B-TREE FOR DISTINCT
90 SELECT * FROM t3 JOIN (SELECT 1)
94 | `--SCAN CONSTANT ROW
95 |--SCAN SUBQUERY xxxxxx
99 SELECT * FROM t3 JOIN (SELECT 1 UNION SELECT 2)
102 |--MATERIALIZE xxxxxx
104 | |--LEFT-MOST SUBQUERY
105 | | `--SCAN CONSTANT ROW
106 | `--UNION USING TEMP B-TREE
107 | `--SCAN CONSTANT ROW
108 |--SCAN SUBQUERY xxxxxx
112 SELECT * FROM t3 JOIN (SELECT 1 EXCEPT SELECT a FROM t3 LIMIT 17)
115 |--MATERIALIZE xxxxxx
117 | |--LEFT-MOST SUBQUERY
118 | | `--SCAN CONSTANT ROW
119 | `--EXCEPT USING TEMP B-TREE
121 |--SCAN SUBQUERY xxxxxx
125 SELECT * FROM t3 JOIN (SELECT 1 INTERSECT SELECT a FROM t3 LIMIT 17)
128 |--MATERIALIZE xxxxxx
130 | |--LEFT-MOST SUBQUERY
131 | | `--SCAN CONSTANT ROW
132 | `--INTERSECT USING TEMP B-TREE
134 |--SCAN SUBQUERY xxxxxx
139 SELECT * FROM t3 JOIN (SELECT 1 UNION ALL SELECT a FROM t3 LIMIT 17)
142 |--MATERIALIZE xxxxxx
144 | |--LEFT-MOST SUBQUERY
145 | | `--SCAN CONSTANT ROW
148 |--SCAN SUBQUERY xxxxxx
152 #-------------------------------------------------------------------------
153 # Test cases eqp-2.* - tests for single select statements.
156 do_execsql_test 2.1 {
157 CREATE TABLE t1(x INT, y INT, ex TEXT);
159 CREATE TABLE t2(x INT, y INT, ex TEXT);
160 CREATE INDEX t2i1 ON t2(x);
163 det 2.2.1 "SELECT DISTINCT min(x), max(x) FROM t1 GROUP BY x ORDER BY 1" {
166 |--USE TEMP B-TREE FOR GROUP BY
167 |--USE TEMP B-TREE FOR DISTINCT
168 `--USE TEMP B-TREE FOR ORDER BY
170 det 2.2.2 "SELECT DISTINCT min(x), max(x) FROM t2 GROUP BY x ORDER BY 1" {
172 |--SCAN TABLE t2 USING COVERING INDEX t2i1
173 |--USE TEMP B-TREE FOR DISTINCT
174 `--USE TEMP B-TREE FOR ORDER BY
176 det 2.2.3 "SELECT DISTINCT * FROM t1" {
179 `--USE TEMP B-TREE FOR DISTINCT
181 det 2.2.4 "SELECT DISTINCT * FROM t1, t2" {
185 `--USE TEMP B-TREE FOR DISTINCT
187 det 2.2.5 "SELECT DISTINCT * FROM t1, t2 ORDER BY t1.x" {
191 |--USE TEMP B-TREE FOR DISTINCT
192 `--USE TEMP B-TREE FOR ORDER BY
194 det 2.2.6 "SELECT DISTINCT t2.x FROM t1, t2 ORDER BY t2.x" {
196 |--SCAN TABLE t2 USING COVERING INDEX t2i1
200 det 2.3.1 "SELECT max(x) FROM t2" {
202 `--SEARCH TABLE t2 USING COVERING INDEX t2i1
204 det 2.3.2 "SELECT min(x) FROM t2" {
206 `--SEARCH TABLE t2 USING COVERING INDEX t2i1
208 det 2.3.3 "SELECT min(x), max(x) FROM t2" {
210 `--SCAN TABLE t2 USING COVERING INDEX t2i1
213 det 2.4.1 "SELECT * FROM t1 WHERE rowid=?" {
215 `--SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)
220 #-------------------------------------------------------------------------
221 # Test cases eqp-3.* - tests for select statements that use sub-selects.
224 SELECT (SELECT x FROM t1 AS sub) FROM t1;
229 `--SCAN TABLE t1 AS sub
232 SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub);
237 `--SCAN TABLE t1 AS sub
240 SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub ORDER BY y);
245 |--SCAN TABLE t1 AS sub
246 `--USE TEMP B-TREE FOR ORDER BY
249 SELECT * FROM t1 WHERE (SELECT x FROM t2 ORDER BY x);
254 `--SCAN TABLE t2 USING COVERING INDEX t2i1
258 SELECT * FROM (SELECT * FROM t1 ORDER BY x LIMIT 10) ORDER BY y LIMIT 5
263 | `--USE TEMP B-TREE FOR ORDER BY
264 |--SCAN SUBQUERY xxxxxx
265 `--USE TEMP B-TREE FOR ORDER BY
269 (SELECT * FROM t1 ORDER BY x LIMIT 10) AS x1,
270 (SELECT * FROM t2 ORDER BY x LIMIT 10) AS x2
271 ORDER BY x2.y LIMIT 5
274 |--MATERIALIZE xxxxxx
276 | `--USE TEMP B-TREE FOR ORDER BY
277 |--MATERIALIZE xxxxxx
278 | `--SCAN TABLE t2 USING INDEX t2i1
279 |--SCAN SUBQUERY xxxxxx AS x1
280 |--SCAN SUBQUERY xxxxxx AS x2
281 `--USE TEMP B-TREE FOR ORDER BY
285 SELECT * FROM t1 WHERE y IN (SELECT y FROM t2)
293 SELECT * FROM t1 WHERE y IN (SELECT y FROM t2 WHERE t1.x!=t2.x)
297 `--CORRELATED LIST SUBQUERY
301 SELECT * FROM t1 WHERE EXISTS (SELECT y FROM t2 WHERE t1.x!=t2.x)
305 `--CORRELATED SCALAR SUBQUERY
309 #-------------------------------------------------------------------------
310 # Test cases eqp-4.* - tests for composite select statements.
313 SELECT * FROM t1 UNION ALL SELECT * FROM t2
317 |--LEFT-MOST SUBQUERY
323 SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 2
329 | `--USE TEMP B-TREE FOR ORDER BY
332 `--USE TEMP B-TREE FOR ORDER BY
335 SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 2
341 | `--USE TEMP B-TREE FOR ORDER BY
344 `--USE TEMP B-TREE FOR ORDER BY
347 SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 2
353 | `--USE TEMP B-TREE FOR ORDER BY
356 `--USE TEMP B-TREE FOR ORDER BY
359 SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 2
365 | `--USE TEMP B-TREE FOR ORDER BY
368 `--USE TEMP B-TREE FOR ORDER BY
372 SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 1
378 | `--USE TEMP B-TREE FOR ORDER BY
380 `--SCAN TABLE t2 USING INDEX t2i1
383 SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 1
389 | `--USE TEMP B-TREE FOR ORDER BY
391 |--SCAN TABLE t2 USING INDEX t2i1
392 `--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY
395 SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 1
401 | `--USE TEMP B-TREE FOR ORDER BY
403 |--SCAN TABLE t2 USING INDEX t2i1
404 `--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY
407 SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 1
413 | `--USE TEMP B-TREE FOR ORDER BY
415 |--SCAN TABLE t2 USING INDEX t2i1
416 `--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY
420 SELECT x FROM t1 UNION SELECT x FROM t2
424 |--LEFT-MOST SUBQUERY
426 `--UNION USING TEMP B-TREE
427 `--SCAN TABLE t2 USING COVERING INDEX t2i1
431 SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1
435 |--LEFT-MOST SUBQUERY
437 |--UNION USING TEMP B-TREE
438 | `--SCAN TABLE t2 USING COVERING INDEX t2i1
439 `--UNION USING TEMP B-TREE
443 SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1 ORDER BY 1
451 | | `--USE TEMP B-TREE FOR ORDER BY
453 | `--SCAN TABLE t2 USING COVERING INDEX t2i1
456 `--USE TEMP B-TREE FOR ORDER BY
460 #-------------------------------------------------------------------------
461 # This next block of tests verifies that the examples on the
462 # lang_explain.html page are correct.
466 # XVIDENCE-OF: R-47779-47605 sqlite> EXPLAIN QUERY PLAN SELECT a, b
468 # 0|0|0|SCAN TABLE t1
470 do_execsql_test 5.1.0 { CREATE TABLE t1(a INT, b INT, ex TEXT) }
471 det 5.1.1 "SELECT a, b FROM t1 WHERE a=1" {
472 0 0 0 {SCAN TABLE t1}
475 # XVIDENCE-OF: R-55852-17599 sqlite> CREATE INDEX i1 ON t1(a);
476 # sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
477 # 0|0|0|SEARCH TABLE t1 USING INDEX i1
479 do_execsql_test 5.2.0 { CREATE INDEX i1 ON t1(a) }
480 det 5.2.1 "SELECT a, b FROM t1 WHERE a=1" {
481 0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}
484 # XVIDENCE-OF: R-21179-11011 sqlite> CREATE INDEX i2 ON t1(a, b);
485 # sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
486 # 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)
488 do_execsql_test 5.3.0 { CREATE INDEX i2 ON t1(a, b) }
489 det 5.3.1 "SELECT a, b FROM t1 WHERE a=1" {
490 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)}
493 # XVIDENCE-OF: R-09991-48941 sqlite> EXPLAIN QUERY PLAN
494 # SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2;
495 # 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)
496 # 0|1|1|SCAN TABLE t2
498 do_execsql_test 5.4.0 {CREATE TABLE t2(c INT, d INT, ex TEXT)}
499 det 5.4.1 "SELECT t1.a, t2.c FROM t1, t2 WHERE t1.a=1 AND t1.b>2" {
500 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)}
501 0 1 1 {SCAN TABLE t2}
504 # XVIDENCE-OF: R-33626-61085 sqlite> EXPLAIN QUERY PLAN
505 # SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2;
506 # 0|0|1|SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)
507 # 0|1|0|SCAN TABLE t2
509 det 5.5 "SELECT t1.a, t2.c FROM t2, t1 WHERE t1.a=1 AND t1.b>2" {
510 0 0 1 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)}
511 0 1 0 {SCAN TABLE t2}
514 # XVIDENCE-OF: R-04002-25654 sqlite> CREATE INDEX i3 ON t1(b);
515 # sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2;
516 # 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)
517 # 0|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?)
519 do_execsql_test 5.5.0 {CREATE INDEX i3 ON t1(b)}
520 det 5.6.1 "SELECT a, b FROM t1 WHERE a=1 OR b=2" {
521 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)}
522 0 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?)}
525 # XVIDENCE-OF: R-24577-38891 sqlite> EXPLAIN QUERY PLAN
526 # SELECT c, d FROM t2 ORDER BY c;
527 # 0|0|0|SCAN TABLE t2
528 # 0|0|0|USE TEMP B-TREE FOR ORDER BY
530 det 5.7 "SELECT c, d FROM t2 ORDER BY c" {
531 0 0 0 {SCAN TABLE t2}
532 0 0 0 {USE TEMP B-TREE FOR ORDER BY}
535 # XVIDENCE-OF: R-58157-12355 sqlite> CREATE INDEX i4 ON t2(c);
536 # sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c;
537 # 0|0|0|SCAN TABLE t2 USING INDEX i4
539 do_execsql_test 5.8.0 {CREATE INDEX i4 ON t2(c)}
540 det 5.8.1 "SELECT c, d FROM t2 ORDER BY c" {
541 0 0 0 {SCAN TABLE t2 USING INDEX i4}
544 # XVIDENCE-OF: R-13931-10421 sqlite> EXPLAIN QUERY PLAN SELECT
545 # (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2;
546 # 0|0|0|SCAN TABLE t2
547 # 0|0|0|EXECUTE SCALAR SUBQUERY 1
548 # 1|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)
549 # 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
550 # 2|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?)
553 SELECT (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2
555 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i4}
556 0 0 0 {EXECUTE SCALAR SUBQUERY 1}
557 1 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)}
558 0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2}
559 2 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?)}
562 # XVIDENCE-OF: R-50892-45943 sqlite> EXPLAIN QUERY PLAN
563 # SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x;
564 # 1|0|0|SCAN TABLE t1 USING COVERING INDEX i2
565 # 0|0|0|SCAN SUBQUERY 1
566 # 0|0|0|USE TEMP B-TREE FOR GROUP BY
569 SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x
571 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2}
572 0 0 0 {SCAN SUBQUERY 1}
573 0 0 0 {USE TEMP B-TREE FOR GROUP BY}
576 # XVIDENCE-OF: R-46219-33846 sqlite> EXPLAIN QUERY PLAN
577 # SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1;
578 # 0|0|0|SEARCH TABLE t2 USING INDEX i4 (c=?)
579 # 0|1|1|SCAN TABLE t1
581 det 5.11 "SELECT a, b FROM (SELECT * FROM t2 WHERE c=1), t1" {
582 0 0 0 {SEARCH TABLE t2 USING INDEX i4 (c=?)}
583 0 1 1 {SCAN TABLE t1 USING COVERING INDEX i2}
586 # XVIDENCE-OF: R-37879-39987 sqlite> EXPLAIN QUERY PLAN
587 # SELECT a FROM t1 UNION SELECT c FROM t2;
588 # 1|0|0|SCAN TABLE t1
589 # 2|0|0|SCAN TABLE t2
590 # 0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)
592 det 5.12 "SELECT a,b FROM t1 UNION SELECT c, 99 FROM t2" {
593 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2}
594 2 0 0 {SCAN TABLE t2 USING COVERING INDEX i4}
595 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)}
598 # XVIDENCE-OF: R-44864-63011 sqlite> EXPLAIN QUERY PLAN
599 # SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1;
600 # 1|0|0|SCAN TABLE t1 USING COVERING INDEX i2
601 # 2|0|0|SCAN TABLE t2 2|0|0|USE TEMP B-TREE FOR ORDER BY
602 # 0|0|0|COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)
604 det 5.13 "SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1" {
605 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i1}
606 2 0 0 {SCAN TABLE t2}
607 2 0 0 {USE TEMP B-TREE FOR ORDER BY}
608 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)}
611 if {![nonzero_reserved_bytes]} {
612 #-------------------------------------------------------------------------
613 # The following tests - eqp-6.* - test that the example C code on
614 # documentation page eqp.html works. The C code is duplicated in test1.c
615 # and wrapped in Tcl command [print_explain_query_plan]
618 proc explain_query_plan {db sql} {
619 set stmt [sqlite3_prepare_v2 db $sql -1 DUMMY]
620 print_explain_query_plan $stmt
621 sqlite3_finalize $stmt
624 explain_query_plan db {%SQL%}
629 # Do a "Print Explain Query Plan" test.
630 proc do_peqp_test {tn sql res} {
631 set fd [open script.tcl w]
632 puts $fd [string map [list %SQL% $sql] $::boilerplate]
635 uplevel do_test $tn [list {
636 set fd [open "|[info nameofexec] script.tcl"]
644 SELECT a, b FROM t1 EXCEPT SELECT d, 99 FROM t2 ORDER BY 1
646 1 0 0 SCAN TABLE t1 USING COVERING INDEX i2
648 2 0 0 USE TEMP B-TREE FOR ORDER BY
649 0 0 0 COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)
654 #-------------------------------------------------------------------------
655 # The following tests - eqp-7.* - test that queries that use the OP_Count
656 # optimization return something sensible with EQP.
660 do_execsql_test 7.0 {
661 CREATE TABLE t1(a INT, b INT, ex CHAR(100));
662 CREATE TABLE t2(a INT, b INT, ex CHAR(100));
663 CREATE INDEX i1 ON t2(a);
666 det 7.1 "SELECT count(*) FROM t1" {
671 det 7.2 "SELECT count(*) FROM t2" {
673 `--SCAN TABLE t2 USING COVERING INDEX i1
676 do_execsql_test 7.3 {
677 INSERT INTO t1(a,b) VALUES(1, 2);
678 INSERT INTO t1(a,b) VALUES(3, 4);
680 INSERT INTO t2(a,b) VALUES(1, 2);
681 INSERT INTO t2(a,b) VALUES(3, 4);
682 INSERT INTO t2(a,b) VALUES(5, 6);
690 det 7.4 "SELECT count(*) FROM t1" {
695 det 7.5 "SELECT count(*) FROM t2" {
697 `--SCAN TABLE t2 USING COVERING INDEX i1
700 #-------------------------------------------------------------------------
701 # The following tests - eqp-8.* - test that queries that use the OP_Count
702 # optimization return something sensible with EQP.
706 do_execsql_test 8.0 {
707 CREATE TABLE t1(a, b, c, PRIMARY KEY(b, c)) WITHOUT ROWID;
708 CREATE TABLE t2(a, b, c);
711 det 8.1.1 "SELECT * FROM t2" {
716 det 8.1.2 "SELECT * FROM t2 WHERE rowid=?" {
718 `--SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)
721 det 8.1.3 "SELECT count(*) FROM t2" {
726 det 8.2.1 "SELECT * FROM t1" {
731 det 8.2.2 "SELECT * FROM t1 WHERE b=?" {
733 `--SEARCH TABLE t1 USING PRIMARY KEY (b=?)
736 det 8.2.3 "SELECT * FROM t1 WHERE b=? AND c=?" {
738 `--SEARCH TABLE t1 USING PRIMARY KEY (b=? AND c=?)
741 det 8.2.4 "SELECT count(*) FROM t1" {