Import SQLite 3.7.6.3.

third_party/sqlite/src/test/eqp.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
+
+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=?) (~10 rows)} 
+  0 0 1 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~10 rows)} 
+  0 1 0 {SCAN TABLE t2 (~1000000 rows)}
+}
+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 (~1000000 rows)}
+  0 1 1 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)} 
+  0 1 1 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~10 rows)} 
+}
+do_eqp_test 1.3 {
+  SELECT a FROM t1 ORDER BY a
+} {
+  0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)}
+}
+do_eqp_test 1.4 {
+  SELECT a FROM t1 ORDER BY +a
+} {
+  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
+  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=?) (~10 rows)}
+}
+do_eqp_test 1.6 {
+  SELECT DISTINCT count(*) FROM t3 GROUP BY a;
+} {
+  0 0 0 {SCAN TABLE t3 (~1000000 rows)}
+  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 (~1 rows)}
+  0 1 0 {SCAN TABLE t3 (~1000000 rows)}
+}
+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 (~2 rows)}
+  0 1 0 {SCAN TABLE t3 (~1000000 rows)}
+}
+do_eqp_test 1.9 {
+  SELECT * FROM t3 JOIN (SELECT 1 EXCEPT SELECT a FROM t3 LIMIT 17)
+} {
+  3 0 0 {SCAN TABLE t3 (~1000000 rows)}
+  1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (EXCEPT)}
+  0 0 1 {SCAN SUBQUERY 1 (~17 rows)}
+  0 1 0 {SCAN TABLE t3 (~1000000 rows)}
+}
+do_eqp_test 1.10 {
+  SELECT * FROM t3 JOIN (SELECT 1 INTERSECT SELECT a FROM t3 LIMIT 17)
+} {
+  3 0 0 {SCAN TABLE t3 (~1000000 rows)}
+  1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (INTERSECT)}
+  0 0 1 {SCAN SUBQUERY 1 (~1 rows)}
+  0 1 0 {SCAN TABLE t3 (~1000000 rows)}
+}
+
+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 (~1000000 rows)}
+  1 0 0 {COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)}
+  0 0 1 {SCAN SUBQUERY 1 (~17 rows)}
+  0 1 0 {SCAN TABLE t3 (~1000000 rows)}
+}
+
+#-------------------------------------------------------------------------
+# 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 (~1000000 rows)}
+  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 (~1000000 rows)}
+  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 (~1000000 rows)}
+  0 0 0 {USE TEMP B-TREE FOR DISTINCT}
+}
+det 2.2.4 "SELECT DISTINCT * FROM t1, t2" {
+  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
+  0 1 1 {SCAN TABLE t2 (~1000000 rows)}
+  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 (~1000000 rows)}
+  0 1 1 {SCAN TABLE t2 (~1000000 rows)}
+  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 (~1000000 rows)}
+  0 1 0 {SCAN TABLE t1 (~1000000 rows)}
+}
+
+det 2.3.1 "SELECT max(x) FROM t2" {
+  0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2i1 (~1 rows)}
+}
+det 2.3.2 "SELECT min(x) FROM t2" {
+  0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2i1 (~1 rows)}
+}
+det 2.3.3 "SELECT min(x), max(x) FROM t2" {
+  0 0 0 {SCAN TABLE t2 (~1000000 rows)}
+}
+
+det 2.4.1 "SELECT * FROM t1 WHERE rowid=?" {
+  0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)}
+}
+
+
+
+#-------------------------------------------------------------------------
+# 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 (~1000000 rows)}
+  0 0 0 {EXECUTE SCALAR SUBQUERY 1}
+  1 0 0 {SCAN TABLE t1 AS sub (~1000000 rows)}
+}
+do_eqp_test 3.1.2 {
+  SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub);
+} {
+  0 0 0 {SCAN TABLE t1 (~1000000 rows)}
+  0 0 0 {EXECUTE SCALAR SUBQUERY 1}
+  1 0 0 {SCAN TABLE t1 AS sub (~1000000 rows)}
+}
+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 (~1000000 rows)}
+  0 0 0 {EXECUTE SCALAR SUBQUERY 1}
+  1 0 0 {SCAN TABLE t1 AS sub (~1000000 rows)}
+  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 (~1000000 rows)}
+  0 0 0 {EXECUTE SCALAR SUBQUERY 1}
+  1 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)}
+}
+
+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 (~1000000 rows)} 
+  1 0 0 {USE TEMP B-TREE FOR ORDER BY} 
+  0 0 0 {SCAN SUBQUERY 1 (~10 rows)} 
+  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 (~1000000 rows)} 
+  1 0 0 {USE TEMP B-TREE FOR ORDER BY} 
+  2 0 0 {SCAN TABLE t2 USING INDEX t2i1 (~1000000 rows)} 
+  0 0 0 {SCAN SUBQUERY 1 AS x1 (~10 rows)} 
+  0 1 1 {SCAN SUBQUERY 2 AS x2 (~10 rows)} 
+  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 (~100000 rows)} 
+  0 0 0 {EXECUTE LIST SUBQUERY 1} 
+  1 0 0 {SCAN TABLE t2 (~1000000 rows)}
+}
+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 (~500000 rows)} 
+  0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 
+  1 0 0 {SCAN TABLE t2 (~500000 rows)}
+}
+det 3.3.3 {
+  SELECT * FROM t1 WHERE EXISTS (SELECT y FROM t2 WHERE t1.x!=t2.x)
+} {
+  0 0 0 {SCAN TABLE t1 (~500000 rows)} 
+  0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 1} 
+  1 0 0 {SCAN TABLE t2 (~500000 rows)}
+}
+
+#-------------------------------------------------------------------------
+# 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 (~1000000 rows)} 
+  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
+  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 (~1000000 rows)} 
+  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
+  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
+  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 (~1000000 rows)} 
+  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
+  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
+  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 (~1000000 rows)} 
+  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
+  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
+  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 (~1000000 rows)} 
+  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
+  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
+  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 (~1000000 rows)} 
+  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
+  2 0 0 {SCAN TABLE t2 USING INDEX t2i1 (~1000000 rows)} 
+  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 (~1000000 rows)} 
+  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
+  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
+  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 (~1000000 rows)} 
+  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
+  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
+  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 (~1000000 rows)} 
+  1 0 0 {USE TEMP B-TREE FOR ORDER BY}
+  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
+  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 (~1000000 rows)} 
+  2 0 0 {SCAN TABLE t2 (~1000000 rows)} 
+  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 (~1000000 rows)} 
+  3 0 0 {SCAN TABLE t2 (~1000000 rows)} 
+  1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)}
+  4 0 0 {SCAN TABLE t1 (~1000000 rows)} 
+  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 (~1000000 rows)} 
+  2 0 0 {USE TEMP B-TREE FOR ORDER BY} 
+  3 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)} 
+  1 0 0 {COMPOUND SUBQUERIES 2 AND 3 (UNION)} 
+  4 0 0 {SCAN TABLE t1 (~1000000 rows)} 
+  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-64208-08323 sqlite> EXPLAIN QUERY PLAN SELECT a, b
+# FROM t1 WHERE a=1; 0|0|0|SCAN TABLE t1 (~100000 rows)
+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 (~100000 rows)}
+}
+
+# EVIDENCE-OF: R-09022-44606 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 (a=?) (~10 rows)
+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=?) (~10 rows)}
+}
+
+# EVIDENCE-OF: R-62228-34103 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=?) (~10 rows)
+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=?) (~10 rows)}
+}
+
+# EVIDENCE-OF: R-22253-05302 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>?) (~3 rows) 0|1|1|SCAN TABLE t2
+# (~1000000 rows)
+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>?) (~2 rows)}
+  0 1 1 {SCAN TABLE t2 (~1000000 rows)}
+}
+
+# EVIDENCE-OF: R-21040-07025 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>?) (~3 rows) 0|1|0|SCAN TABLE t2
+# (~1000000 rows)
+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>?) (~2 rows)}
+  0 1 0 {SCAN TABLE t2 (~1000000 rows)}
+}
+
+# EVIDENCE-OF: R-39007-61103 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=?) (~10 rows)
+# 0|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)
+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=?) (~10 rows)}
+  0 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)}
+}
+
+# EVIDENCE-OF: R-33025-54904 sqlite> EXPLAIN QUERY PLAN SELECT c, d
+# FROM t2 ORDER BY c; 0|0|0|SCAN TABLE t2 (~1000000 rows) 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 (~1000000 rows)}
+  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
+}
+
+# EVIDENCE-OF: R-38854-22809 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 (~1000000 rows)
+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 (~1000000 rows)}
+}
+
+# EVIDENCE-OF: R-29884-43993 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 (~1000000 rows) 0|0|0|EXECUTE SCALAR SUBQUERY 1
+# 1|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)
+# 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2 2|0|0|SEARCH TABLE t1 USING
+# INDEX i3 (b=?) (~10 rows)
+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 (~1000000 rows)}
+  0 0 0 {EXECUTE SCALAR SUBQUERY 1}
+  1 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)}
+  0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2}
+  2 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)}
+}
+
+# EVIDENCE-OF: R-17911-16445 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 (~1000000 rows) 0|0|0|SCAN
+# SUBQUERY 1 (~1000000 rows) 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 (~1000000 rows)}
+  0 0 0 {SCAN SUBQUERY 1 (~100 rows)}
+  0 0 0 {USE TEMP B-TREE FOR GROUP BY}
+}
+
+# EVIDENCE-OF: R-18544-33103 sqlite> EXPLAIN QUERY PLAN SELECT * FROM
+# (SELECT * FROM t2 WHERE c=1), t1; 0|0|0|SEARCH TABLE t2 USING INDEX i4
+# (c=?) (~10 rows) 0|1|1|SCAN TABLE t1 (~1000000 rows)
+det 5.11 "SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1" {
+  0 0 0 {SEARCH TABLE t2 USING INDEX i4 (c=?) (~10 rows)}
+  0 1 1 {SCAN TABLE t1 (~1000000 rows)}
+}
+
+# EVIDENCE-OF: R-40701-42164 sqlite> EXPLAIN QUERY PLAN SELECT a FROM
+# t1 UNION SELECT c FROM t2; 1|0|0|SCAN TABLE t1 (~1000000 rows)
+# 2|0|0|SCAN TABLE t2 (~1000000 rows) 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 (~1000000 rows)}
+  2 0 0 {SCAN TABLE t2 (~1000000 rows)}
+  0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)}
+}
+
+# EVIDENCE-OF: R-61538-24748 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 (~1000000 rows) 2|0|0|SCAN TABLE t2 (~1000000 rows)
+# 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 (~1000000 rows)}
+  2 0 0 {SCAN TABLE t2 (~1000000 rows)}
+  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 (~1000000 rows)
+2 0 0 SCAN TABLE t2 (~1000000 rows)
+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 (~1000000 rows)}
+}
+
+det 7.2 "SELECT count(*) FROM t2" {
+  0 0 0 {SCAN TABLE t2 USING COVERING INDEX i1(~1000000 rows)}
+}
+
+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 (~2 rows)}
+}
+
+det 7.5 "SELECT count(*) FROM t2" {
+  0 0 0 {SCAN TABLE t2 USING COVERING INDEX i1(~3 rows)}
+}
+
+
+finish_test