# 2010 November 6 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable !compound { finish_test return } set testprefix eqp #------------------------------------------------------------------------- # # eqp-1.*: Assorted tests. # eqp-2.*: Tests for single select statements. # eqp-3.*: Select statements that execute sub-selects. # eqp-4.*: Compound select statements. # ... # eqp-7.*: "SELECT count(*) FROM tbl" statements (VDBE code OP_Count). # proc det {args} { uplevel do_eqp_test $args } do_execsql_test 1.1 { CREATE TABLE t1(a, b); CREATE INDEX i1 ON t1(a); CREATE INDEX i2 ON t1(b); CREATE TABLE t2(a, b); CREATE TABLE t3(a, b); } do_eqp_test 1.2 { SELECT * FROM t2, t1 WHERE t1.a=1 OR t1.b=2; } { 0 0 1 {SEARCH TABLE t1 USING INDEX i1 (a=?)} 0 0 1 {SEARCH TABLE t1 USING INDEX i2 (b=?)} 0 1 0 {SCAN TABLE t2} } do_eqp_test 1.3 { SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=1 OR t1.b=2; } { 0 0 0 {SCAN TABLE t2} 0 1 1 {SEARCH TABLE t1 USING INDEX i1 (a=?)} 0 1 1 {SEARCH TABLE t1 USING INDEX i2 (b=?)} } do_eqp_test 1.3 { SELECT a FROM t1 ORDER BY a } { 0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1} } do_eqp_test 1.4 { SELECT a FROM t1 ORDER BY +a } { 0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } do_eqp_test 1.5 { SELECT a FROM t1 WHERE a=4 } { 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?)} } do_eqp_test 1.6 { SELECT DISTINCT count(*) FROM t3 GROUP BY a; } { 0 0 0 {SCAN TABLE t3} 0 0 0 {USE TEMP B-TREE FOR GROUP BY} 0 0 0 {USE TEMP B-TREE FOR DISTINCT} } do_eqp_test 1.7 { SELECT * FROM t3 JOIN (SELECT 1) } { 0 0 1 {SCAN SUBQUERY 1} 0 1 0 {SCAN TABLE t3} } do_eqp_test 1.8 { SELECT * FROM t3 JOIN (SELECT 1 UNION SELECT 2) } { 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)} 0 0 1 {SCAN SUBQUERY 1} 0 1 0 {SCAN TABLE t3} } do_eqp_test 1.9 { SELECT * FROM t3 JOIN (SELECT 1 EXCEPT SELECT a FROM t3 LIMIT 17) } { 3 0 0 {SCAN TABLE t3} 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (EXCEPT)} 0 0 1 {SCAN SUBQUERY 1} 0 1 0 {SCAN TABLE t3} } do_eqp_test 1.10 { SELECT * FROM t3 JOIN (SELECT 1 INTERSECT SELECT a FROM t3 LIMIT 17) } { 3 0 0 {SCAN TABLE t3} 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (INTERSECT)} 0 0 1 {SCAN SUBQUERY 1} 0 1 0 {SCAN TABLE t3} } do_eqp_test 1.11 { SELECT * FROM t3 JOIN (SELECT 1 UNION ALL SELECT a FROM t3 LIMIT 17) } { 3 0 0 {SCAN TABLE t3} 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)} 0 0 1 {SCAN SUBQUERY 1} 0 1 0 {SCAN TABLE t3} } #------------------------------------------------------------------------- # Test cases eqp-2.* - tests for single select statements. # drop_all_tables do_execsql_test 2.1 { CREATE TABLE t1(x, y); CREATE TABLE t2(x, y); CREATE INDEX t2i1 ON t2(x); } det 2.2.1 "SELECT DISTINCT min(x), max(x) FROM t1 GROUP BY x ORDER BY 1" { 0 0 0 {SCAN TABLE t1} 0 0 0 {USE TEMP B-TREE FOR GROUP BY} 0 0 0 {USE TEMP B-TREE FOR DISTINCT} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } det 2.2.2 "SELECT DISTINCT min(x), max(x) FROM t2 GROUP BY x ORDER BY 1" { 0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1} 0 0 0 {USE TEMP B-TREE FOR DISTINCT} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } det 2.2.3 "SELECT DISTINCT * FROM t1" { 0 0 0 {SCAN TABLE t1} 0 0 0 {USE TEMP B-TREE FOR DISTINCT} } det 2.2.4 "SELECT DISTINCT * FROM t1, t2" { 0 0 0 {SCAN TABLE t1} 0 1 1 {SCAN TABLE t2} 0 0 0 {USE TEMP B-TREE FOR DISTINCT} } det 2.2.5 "SELECT DISTINCT * FROM t1, t2 ORDER BY t1.x" { 0 0 0 {SCAN TABLE t1} 0 1 1 {SCAN TABLE t2} 0 0 0 {USE TEMP B-TREE FOR DISTINCT} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } det 2.2.6 "SELECT DISTINCT t2.x FROM t1, t2 ORDER BY t2.x" { 0 0 1 {SCAN TABLE t2 USING COVERING INDEX t2i1} 0 1 0 {SCAN TABLE t1} } det 2.3.1 "SELECT max(x) FROM t2" { 0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2i1} } det 2.3.2 "SELECT min(x) FROM t2" { 0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2i1} } det 2.3.3 "SELECT min(x), max(x) FROM t2" { 0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1} } det 2.4.1 "SELECT * FROM t1 WHERE rowid=?" { 0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)} } #------------------------------------------------------------------------- # Test cases eqp-3.* - tests for select statements that use sub-selects. # do_eqp_test 3.1.1 { SELECT (SELECT x FROM t1 AS sub) FROM t1; } { 0 0 0 {SCAN TABLE t1} 0 0 0 {EXECUTE SCALAR SUBQUERY 1} 1 0 0 {SCAN TABLE t1 AS sub} } do_eqp_test 3.1.2 { SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub); } { 0 0 0 {SCAN TABLE t1} 0 0 0 {EXECUTE SCALAR SUBQUERY 1} 1 0 0 {SCAN TABLE t1 AS sub} } do_eqp_test 3.1.3 { SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub ORDER BY y); } { 0 0 0 {SCAN TABLE t1} 0 0 0 {EXECUTE SCALAR SUBQUERY 1} 1 0 0 {SCAN TABLE t1 AS sub} 1 0 0 {USE TEMP B-TREE FOR ORDER BY} } do_eqp_test 3.1.4 { SELECT * FROM t1 WHERE (SELECT x FROM t2 ORDER BY x); } { 0 0 0 {SCAN TABLE t1} 0 0 0 {EXECUTE SCALAR SUBQUERY 1} 1 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1} } det 3.2.1 { SELECT * FROM (SELECT * FROM t1 ORDER BY x LIMIT 10) ORDER BY y LIMIT 5 } { 1 0 0 {SCAN TABLE t1} 1 0 0 {USE TEMP B-TREE FOR ORDER BY} 0 0 0 {SCAN SUBQUERY 1} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } det 3.2.2 { SELECT * FROM (SELECT * FROM t1 ORDER BY x LIMIT 10) AS x1, (SELECT * FROM t2 ORDER BY x LIMIT 10) AS x2 ORDER BY x2.y LIMIT 5 } { 1 0 0 {SCAN TABLE t1} 1 0 0 {USE TEMP B-TREE FOR ORDER BY} 2 0 0 {SCAN TABLE t2 USING INDEX t2i1} 0 0 0 {SCAN SUBQUERY 1 AS x1} 0 1 1 {SCAN SUBQUERY 2 AS x2} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } det 3.3.1 { SELECT * FROM t1 WHERE y IN (SELECT y FROM t2) } { 0 0 0 {SCAN TABLE t1} 0 0 0 {EXECUTE LIST SUBQUERY 1} 1 0 0 {SCAN TABLE t2} } det 3.3.2 { SELECT * FROM t1 WHERE y IN (SELECT y FROM t2 WHERE t1.x!=t2.x) } { 0 0 0 {SCAN TABLE t1} 0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 1 0 0 {SCAN TABLE t2} } det 3.3.3 { SELECT * FROM t1 WHERE EXISTS (SELECT y FROM t2 WHERE t1.x!=t2.x) } { 0 0 0 {SCAN TABLE t1} 0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 1} 1 0 0 {SCAN TABLE t2} } #------------------------------------------------------------------------- # Test cases eqp-4.* - tests for composite select statements. # do_eqp_test 4.1.1 { SELECT * FROM t1 UNION ALL SELECT * FROM t2 } { 1 0 0 {SCAN TABLE t1} 2 0 0 {SCAN TABLE t2} 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)} } do_eqp_test 4.1.2 { SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 2 } { 1 0 0 {SCAN TABLE t1} 1 0 0 {USE TEMP B-TREE FOR ORDER BY} 2 0 0 {SCAN TABLE t2} 2 0 0 {USE TEMP B-TREE FOR ORDER BY} 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)} } do_eqp_test 4.1.3 { SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 2 } { 1 0 0 {SCAN TABLE t1} 1 0 0 {USE TEMP B-TREE FOR ORDER BY} 2 0 0 {SCAN TABLE t2} 2 0 0 {USE TEMP B-TREE FOR ORDER BY} 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION)} } do_eqp_test 4.1.4 { SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 2 } { 1 0 0 {SCAN TABLE t1} 1 0 0 {USE TEMP B-TREE FOR ORDER BY} 2 0 0 {SCAN TABLE t2} 2 0 0 {USE TEMP B-TREE FOR ORDER BY} 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (INTERSECT)} } do_eqp_test 4.1.5 { SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 2 } { 1 0 0 {SCAN TABLE t1} 1 0 0 {USE TEMP B-TREE FOR ORDER BY} 2 0 0 {SCAN TABLE t2} 2 0 0 {USE TEMP B-TREE FOR ORDER BY} 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)} } do_eqp_test 4.2.2 { SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 1 } { 1 0 0 {SCAN TABLE t1} 1 0 0 {USE TEMP B-TREE FOR ORDER BY} 2 0 0 {SCAN TABLE t2 USING INDEX t2i1} 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)} } do_eqp_test 4.2.3 { SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 1 } { 1 0 0 {SCAN TABLE t1} 1 0 0 {USE TEMP B-TREE FOR ORDER BY} 2 0 0 {SCAN TABLE t2} 2 0 0 {USE TEMP B-TREE FOR ORDER BY} 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION)} } do_eqp_test 4.2.4 { SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 1 } { 1 0 0 {SCAN TABLE t1} 1 0 0 {USE TEMP B-TREE FOR ORDER BY} 2 0 0 {SCAN TABLE t2} 2 0 0 {USE TEMP B-TREE FOR ORDER BY} 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (INTERSECT)} } do_eqp_test 4.2.5 { SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 1 } { 1 0 0 {SCAN TABLE t1} 1 0 0 {USE TEMP B-TREE FOR ORDER BY} 2 0 0 {SCAN TABLE t2} 2 0 0 {USE TEMP B-TREE FOR ORDER BY} 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)} } do_eqp_test 4.3.1 { SELECT x FROM t1 UNION SELECT x FROM t2 } { 1 0 0 {SCAN TABLE t1} 2 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1} 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)} } do_eqp_test 4.3.2 { SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1 } { 2 0 0 {SCAN TABLE t1} 3 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1} 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)} 4 0 0 {SCAN TABLE t1} 0 0 0 {COMPOUND SUBQUERIES 1 AND 4 USING TEMP B-TREE (UNION)} } do_eqp_test 4.3.3 { SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1 ORDER BY 1 } { 2 0 0 {SCAN TABLE t1} 2 0 0 {USE TEMP B-TREE FOR ORDER BY} 3 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1} 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 (UNION)} 4 0 0 {SCAN TABLE t1} 4 0 0 {USE TEMP B-TREE FOR ORDER BY} 0 0 0 {COMPOUND SUBQUERIES 1 AND 4 (UNION)} } #------------------------------------------------------------------------- # This next block of tests verifies that the examples on the # lang_explain.html page are correct. # drop_all_tables # EVIDENCE-OF: R-47779-47605 sqlite> EXPLAIN QUERY PLAN SELECT a, b # FROM t1 WHERE a=1; # 0|0|0|SCAN TABLE t1 # do_execsql_test 5.1.0 { CREATE TABLE t1(a, b) } det 5.1.1 "SELECT a, b FROM t1 WHERE a=1" { 0 0 0 {SCAN TABLE t1} } # EVIDENCE-OF: R-55852-17599 sqlite> CREATE INDEX i1 ON t1(a); # sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; # 0|0|0|SEARCH TABLE t1 USING INDEX i1 # do_execsql_test 5.2.0 { CREATE INDEX i1 ON t1(a) } det 5.2.1 "SELECT a, b FROM t1 WHERE a=1" { 0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)} } # EVIDENCE-OF: R-21179-11011 sqlite> CREATE INDEX i2 ON t1(a, b); # sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; # 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) # do_execsql_test 5.3.0 { CREATE INDEX i2 ON t1(a, b) } det 5.3.1 "SELECT a, b FROM t1 WHERE a=1" { 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)} } # EVIDENCE-OF: R-09991-48941 sqlite> EXPLAIN QUERY PLAN # SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2; # 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) # 0|1|1|SCAN TABLE t2 # do_execsql_test 5.4.0 {CREATE TABLE t2(c, d)} det 5.4.1 "SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2" { 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)} 0 1 1 {SCAN TABLE t2} } # EVIDENCE-OF: R-33626-61085 sqlite> EXPLAIN QUERY PLAN # SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2; # 0|0|1|SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) # 0|1|0|SCAN TABLE t2 # det 5.5 "SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2" { 0 0 1 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)} 0 1 0 {SCAN TABLE t2} } # EVIDENCE-OF: R-04002-25654 sqlite> CREATE INDEX i3 ON t1(b); # sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2; # 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) # 0|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?) # do_execsql_test 5.5.0 {CREATE INDEX i3 ON t1(b)} det 5.6.1 "SELECT * FROM t1 WHERE a=1 OR b=2" { 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)} 0 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?)} } # EVIDENCE-OF: R-24577-38891 sqlite> EXPLAIN QUERY PLAN # SELECT c, d FROM t2 ORDER BY c; # 0|0|0|SCAN TABLE t2 # 0|0|0|USE TEMP B-TREE FOR ORDER BY # det 5.7 "SELECT c, d FROM t2 ORDER BY c" { 0 0 0 {SCAN TABLE t2} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } # EVIDENCE-OF: R-58157-12355 sqlite> CREATE INDEX i4 ON t2(c); # sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c; # 0|0|0|SCAN TABLE t2 USING INDEX i4 # do_execsql_test 5.8.0 {CREATE INDEX i4 ON t2(c)} det 5.8.1 "SELECT c, d FROM t2 ORDER BY c" { 0 0 0 {SCAN TABLE t2 USING INDEX i4} } # EVIDENCE-OF: R-13931-10421 sqlite> EXPLAIN QUERY PLAN SELECT # (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2; # 0|0|0|SCAN TABLE t2 # 0|0|0|EXECUTE SCALAR SUBQUERY 1 # 1|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) # 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2 # 2|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?) # det 5.9 { SELECT (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2 } { 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i4} 0 0 0 {EXECUTE SCALAR SUBQUERY 1} 1 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)} 0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2} 2 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?)} } # EVIDENCE-OF: R-50892-45943 sqlite> EXPLAIN QUERY PLAN # SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x; # 1|0|0|SCAN TABLE t1 USING COVERING INDEX i2 # 0|0|0|SCAN SUBQUERY 1 # 0|0|0|USE TEMP B-TREE FOR GROUP BY # det 5.10 { SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x } { 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2} 0 0 0 {SCAN SUBQUERY 1} 0 0 0 {USE TEMP B-TREE FOR GROUP BY} } # EVIDENCE-OF: R-46219-33846 sqlite> EXPLAIN QUERY PLAN # SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1; # 0|0|0|SEARCH TABLE t2 USING INDEX i4 (c=?) # 0|1|1|SCAN TABLE t1 # det 5.11 "SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1" { 0 0 0 {SEARCH TABLE t2 USING INDEX i4 (c=?)} 0 1 1 {SCAN TABLE t1 USING COVERING INDEX i2} } # EVIDENCE-OF: R-37879-39987 sqlite> EXPLAIN QUERY PLAN # SELECT a FROM t1 UNION SELECT c FROM t2; # 1|0|0|SCAN TABLE t1 # 2|0|0|SCAN TABLE t2 # 0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION) # det 5.12 "SELECT a FROM t1 UNION SELECT c FROM t2" { 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2} 2 0 0 {SCAN TABLE t2 USING COVERING INDEX i4} 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)} } # EVIDENCE-OF: R-44864-63011 sqlite> EXPLAIN QUERY PLAN # SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1; # 1|0|0|SCAN TABLE t1 USING COVERING INDEX i2 # 2|0|0|SCAN TABLE t2 2|0|0|USE TEMP B-TREE FOR ORDER BY # 0|0|0|COMPOUND SUBQUERIES 1 AND 2 (EXCEPT) # det 5.13 "SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1" { 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2} 2 0 0 {SCAN TABLE t2} 2 0 0 {USE TEMP B-TREE FOR ORDER BY} 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)} } #------------------------------------------------------------------------- # The following tests - eqp-6.* - test that the example C code on # documentation page eqp.html works. The C code is duplicated in test1.c # and wrapped in Tcl command [print_explain_query_plan] # set boilerplate { proc explain_query_plan {db sql} { set stmt [sqlite3_prepare_v2 db $sql -1 DUMMY] print_explain_query_plan $stmt sqlite3_finalize $stmt } sqlite3 db test.db explain_query_plan db {%SQL%} db close exit } # Do a "Print Explain Query Plan" test. proc do_peqp_test {tn sql res} { set fd [open script.tcl w] puts $fd [string map [list %SQL% $sql] $::boilerplate] close $fd uplevel do_test $tn [list { set fd [open "|[info nameofexec] script.tcl"] set data [read $fd] close $fd set data }] [list $res] } do_peqp_test 6.1 { SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1 } [string trimleft { 1 0 0 SCAN TABLE t1 USING COVERING INDEX i2 2 0 0 SCAN TABLE t2 2 0 0 USE TEMP B-TREE FOR ORDER BY 0 0 0 COMPOUND SUBQUERIES 1 AND 2 (EXCEPT) }] #------------------------------------------------------------------------- # The following tests - eqp-7.* - test that queries that use the OP_Count # optimization return something sensible with EQP. # drop_all_tables do_execsql_test 7.0 { CREATE TABLE t1(a, b); CREATE TABLE t2(a, b); CREATE INDEX i1 ON t2(a); } det 7.1 "SELECT count(*) FROM t1" { 0 0 0 {SCAN TABLE t1} } det 7.2 "SELECT count(*) FROM t2" { 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i1} } do_execsql_test 7.3 { INSERT INTO t1 VALUES(1, 2); INSERT INTO t1 VALUES(3, 4); INSERT INTO t2 VALUES(1, 2); INSERT INTO t2 VALUES(3, 4); INSERT INTO t2 VALUES(5, 6); ANALYZE; } db close sqlite3 db test.db det 7.4 "SELECT count(*) FROM t1" { 0 0 0 {SCAN TABLE t1} } det 7.5 "SELECT count(*) FROM t2" { 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i1} } finish_test