test/eqp.test - chromium.googlesource.com/chromium/deps/sqlite - Gitiles

 # 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 INT, b INT, ex TEXT);
   CREATE INDEX i1 ON t1(a);
   CREATE INDEX i2 ON t1(b);
   CREATE TABLE t2(a INT, b INT, ex TEXT);
   CREATE TABLE t3(a INT, b INT, ex TEXT);
 }

 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 INT, y INT, ex TEXT);

   CREATE TABLE t2(x INT, y INT, ex TEXT);
   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 INT, b INT, ex TEXT) }
 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 INT, d INT, ex TEXT)}
 det 5.4.1 "SELECT t1.a, t2.c 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.a, t2.c 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 a, b 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 a, b 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,b FROM t1 UNION SELECT c, 99 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 i1}
   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, b FROM t1 EXCEPT SELECT d, 99 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 INT, b INT, ex CHAR(100));
   CREATE TABLE t2(a INT, b INT, ex CHAR(100));
   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(a,b) VALUES(1, 2);
   INSERT INTO t1(a,b) VALUES(3, 4);

   INSERT INTO t2(a,b) VALUES(1, 2);
   INSERT INTO t2(a,b) VALUES(3, 4);
   INSERT INTO t2(a,b) 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
	# 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 INT, b INT, ex TEXT);
	CREATE INDEX i1 ON t1(a);
	CREATE INDEX i2 ON t1(b);
	CREATE TABLE t2(a INT, b INT, ex TEXT);
	CREATE TABLE t3(a INT, b INT, ex TEXT);
	}

	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 INT, y INT, ex TEXT);

	CREATE TABLE t2(x INT, y INT, ex TEXT);
	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 INT, b INT, ex TEXT) }
	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 INT, d INT, ex TEXT)}
	det 5.4.1 "SELECT t1.a, t2.c 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.a, t2.c 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 a, b 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 a, b 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,b FROM t1 UNION SELECT c, 99 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 i1}
	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, b FROM t1 EXCEPT SELECT d, 99 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 INT, b INT, ex CHAR(100));
	CREATE TABLE t2(a INT, b INT, ex CHAR(100));
	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(a,b) VALUES(1, 2);
	INSERT INTO t1(a,b) VALUES(3, 4);

	INSERT INTO t2(a,b) VALUES(1, 2);
	INSERT INTO t2(a,b) VALUES(3, 4);
	INSERT INTO t2(a,b) 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