From 7bb481fda9ecb134804b49c2ce77ca28f7eea583 Mon Sep 17 00:00:00 2001 From: Hans-Christoph Steiner Date: Fri, 30 Mar 2012 20:42:12 -0400 Subject: Imported Upstream version 2.0.3 --- test/eqp.test | 578 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 578 insertions(+) create mode 100644 test/eqp.test (limited to 'test/eqp.test') diff --git a/test/eqp.test b/test/eqp.test new file mode 100644 index 0000000..91a18d0 --- /dev/null +++ b/test/eqp.test @@ -0,0 +1,578 @@ +# 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 -- cgit v1.2.3