Index: third_party/sqlite/src/test/eqp.test |
diff --git a/third_party/sqlite/src/test/eqp.test b/third_party/sqlite/src/test/eqp.test |
index 91a18d0026770fb62c9ebb3ddf71c0294fa1cdd1..046088c9c57dc6615f3a14a207997b21a9b1ec59 100644 |
--- a/third_party/sqlite/src/test/eqp.test |
+++ b/third_party/sqlite/src/test/eqp.test |
@@ -13,6 +13,11 @@ |
set testdir [file dirname $argv0] |
source $testdir/tester.tcl |
+ifcapable !compound { |
+ finish_test |
+ return |
+} |
+ |
set testprefix eqp |
#------------------------------------------------------------------------- |
@@ -28,47 +33,47 @@ set testprefix eqp |
proc det {args} { uplevel do_eqp_test $args } |
do_execsql_test 1.1 { |
- CREATE TABLE t1(a, b); |
+ 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, b); |
- CREATE TABLE t3(a, 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=?) (~10 rows)} |
- 0 0 1 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~10 rows)} |
- 0 1 0 {SCAN TABLE t2 (~1000000 rows)} |
+ 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 (~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)} |
+ 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 (~1000000 rows)} |
+ 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 (~1000000 rows)} |
+ 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=?) (~10 rows)} |
+ 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 (~1000000 rows)} |
+ 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} |
} |
@@ -76,40 +81,40 @@ do_eqp_test 1.6 { |
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)} |
+ 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 (~2 rows)} |
- 0 1 0 {SCAN TABLE t3 (~1000000 rows)} |
+ 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 (~1000000 rows)} |
+ 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 (~17 rows)} |
- 0 1 0 {SCAN TABLE t3 (~1000000 rows)} |
+ 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 (~1000000 rows)} |
+ 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 (~1 rows)} |
- 0 1 0 {SCAN TABLE t3 (~1000000 rows)} |
+ 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 (~1000000 rows)} |
+ 3 0 0 {SCAN TABLE t3} |
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)} |
+ 0 0 1 {SCAN SUBQUERY 1} |
+ 0 1 0 {SCAN TABLE t3} |
} |
#------------------------------------------------------------------------- |
@@ -117,55 +122,55 @@ do_eqp_test 1.11 { |
# |
drop_all_tables |
do_execsql_test 2.1 { |
- CREATE TABLE t1(x, y); |
+ CREATE TABLE t1(x INT, y INT, ex TEXT); |
- CREATE TABLE t2(x, y); |
+ 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 (~1000000 rows)} |
+ 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 (~1000000 rows)} |
+ 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 (~1000000 rows)} |
+ 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 (~1000000 rows)} |
- 0 1 1 {SCAN TABLE t2 (~1000000 rows)} |
+ 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 (~1000000 rows)} |
- 0 1 1 {SCAN TABLE t2 (~1000000 rows)} |
+ 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 (~1000000 rows)} |
- 0 1 0 {SCAN TABLE t1 (~1000000 rows)} |
+ 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 (~1 rows)} |
+ 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 (~1 rows)} |
+ 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 (~1000000 rows)} |
+ 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=?) (~1 rows)} |
+ 0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)} |
} |
@@ -176,39 +181,39 @@ det 2.4.1 "SELECT * FROM t1 WHERE rowid=?" { |
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 {SCAN TABLE t1} |
0 0 0 {EXECUTE SCALAR SUBQUERY 1} |
- 1 0 0 {SCAN TABLE t1 AS sub (~1000000 rows)} |
+ 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 (~1000000 rows)} |
+ 0 0 0 {SCAN TABLE t1} |
0 0 0 {EXECUTE SCALAR SUBQUERY 1} |
- 1 0 0 {SCAN TABLE t1 AS sub (~1000000 rows)} |
+ 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 (~1000000 rows)} |
+ 0 0 0 {SCAN TABLE t1} |
0 0 0 {EXECUTE SCALAR SUBQUERY 1} |
- 1 0 0 {SCAN TABLE t1 AS sub (~1000000 rows)} |
+ 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 (~1000000 rows)} |
+ 0 0 0 {SCAN TABLE t1} |
0 0 0 {EXECUTE SCALAR SUBQUERY 1} |
- 1 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)} |
+ 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 (~1000000 rows)} |
+ 1 0 0 {SCAN TABLE t1} |
1 0 0 {USE TEMP B-TREE FOR ORDER BY} |
- 0 0 0 {SCAN SUBQUERY 1 (~10 rows)} |
+ 0 0 0 {SCAN SUBQUERY 1} |
0 0 0 {USE TEMP B-TREE FOR ORDER BY} |
} |
det 3.2.2 { |
@@ -217,34 +222,34 @@ det 3.2.2 { |
(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 {SCAN TABLE t1} |
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)} |
+ 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 (~100000 rows)} |
+ 0 0 0 {SCAN TABLE t1} |
0 0 0 {EXECUTE LIST SUBQUERY 1} |
- 1 0 0 {SCAN TABLE t2 (~1000000 rows)} |
+ 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 (~500000 rows)} |
+ 0 0 0 {SCAN TABLE t1} |
0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} |
- 1 0 0 {SCAN TABLE t2 (~500000 rows)} |
+ 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 (~500000 rows)} |
+ 0 0 0 {SCAN TABLE t1} |
0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 1} |
- 1 0 0 {SCAN TABLE t2 (~500000 rows)} |
+ 1 0 0 {SCAN TABLE t2} |
} |
#------------------------------------------------------------------------- |
@@ -253,43 +258,43 @@ det 3.3.3 { |
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)} |
+ 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 (~1000000 rows)} |
+ 1 0 0 {SCAN TABLE t1} |
1 0 0 {USE TEMP B-TREE FOR ORDER BY} |
- 2 0 0 {SCAN TABLE t2 (~1000000 rows)} |
+ 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 (~1000000 rows)} |
+ 1 0 0 {SCAN TABLE t1} |
1 0 0 {USE TEMP B-TREE FOR ORDER BY} |
- 2 0 0 {SCAN TABLE t2 (~1000000 rows)} |
+ 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 (~1000000 rows)} |
+ 1 0 0 {SCAN TABLE t1} |
1 0 0 {USE TEMP B-TREE FOR ORDER BY} |
- 2 0 0 {SCAN TABLE t2 (~1000000 rows)} |
+ 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 (~1000000 rows)} |
+ 1 0 0 {SCAN TABLE t1} |
1 0 0 {USE TEMP B-TREE FOR ORDER BY} |
- 2 0 0 {SCAN TABLE t2 (~1000000 rows)} |
+ 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)} |
} |
@@ -297,64 +302,64 @@ do_eqp_test 4.1.5 { |
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 {SCAN TABLE t1} |
1 0 0 {USE TEMP B-TREE FOR ORDER BY} |
- 2 0 0 {SCAN TABLE t2 USING INDEX t2i1 (~1000000 rows)} |
+ 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 (~1000000 rows)} |
+ 1 0 0 {SCAN TABLE t1} |
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} |
+ 2 0 0 {SCAN TABLE t2 USING INDEX t2i1} |
+ 2 0 0 {USE TEMP B-TREE FOR RIGHT PART OF 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 {SCAN TABLE t1} |
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} |
+ 2 0 0 {SCAN TABLE t2 USING INDEX t2i1} |
+ 2 0 0 {USE TEMP B-TREE FOR RIGHT PART OF 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 {SCAN TABLE t1} |
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} |
+ 2 0 0 {SCAN TABLE t2 USING INDEX t2i1} |
+ 2 0 0 {USE TEMP B-TREE FOR RIGHT PART OF 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)} |
+ 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 (~1000000 rows)} |
- 3 0 0 {SCAN TABLE t2 (~1000000 rows)} |
+ 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 (~1000000 rows)} |
+ 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 (~1000000 rows)} |
+ 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 (~1000000 rows)} |
+ 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 (~1000000 rows)} |
+ 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)} |
} |
@@ -365,128 +370,147 @@ do_eqp_test 4.3.3 { |
# |
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) } |
+# 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 (~100000 rows)} |
+ 0 0 0 {SCAN TABLE t1} |
} |
-# EVIDENCE-OF: R-09022-44606 sqlite> CREATE INDEX i1 ON t1(a); |
+# 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 (a=?) (~10 rows) |
+# 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=?) (~10 rows)} |
+ 0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)} |
} |
-# EVIDENCE-OF: R-62228-34103 sqlite> CREATE INDEX i2 ON t1(a, b); |
+# 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=?) (~10 rows) |
+# 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=?) (~10 rows)} |
+ 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)} |
} |
-# 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-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-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-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-39007-61103 sqlite> CREATE INDEX i3 ON t1(b); |
+# 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=?) (~10 rows) |
-# 0|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows) |
+# 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=?) (~10 rows)} |
- 0 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)} |
+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-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 |
+# 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 (~1000000 rows)} |
+ 0 0 0 {SCAN TABLE t2} |
0 0 0 {USE TEMP B-TREE FOR ORDER BY} |
} |
-# EVIDENCE-OF: R-38854-22809 sqlite> CREATE INDEX i4 ON t2(c); |
+# 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 (~1000000 rows) |
+# 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 (~1000000 rows)} |
+ 0 0 0 {SCAN TABLE t2 USING INDEX i4} |
} |
-# EVIDENCE-OF: R-29884-43993 sqlite> EXPLAIN QUERY PLAN SELECT |
+# 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 (~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) |
+# 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 (~1000000 rows)} |
+ 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=?) (~10 rows)} |
+ 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=?) (~10 rows)} |
+ 2 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?)} |
} |
-# 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 |
+# 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 (~1000000 rows)} |
- 0 0 0 {SCAN SUBQUERY 1 (~100 rows)} |
+ 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-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-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-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)} |
+# 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-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) |
+# 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 (~1000000 rows)} |
- 2 0 0 {SCAN TABLE t2 (~1000000 rows)} |
+ 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)} |
} |
@@ -524,10 +548,10 @@ proc do_peqp_test {tn sql res} { |
} |
do_peqp_test 6.1 { |
- SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 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 (~1000000 rows) |
-2 0 0 SCAN TABLE t2 (~1000000 rows) |
+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) |
}] |
@@ -539,26 +563,26 @@ do_peqp_test 6.1 { |
drop_all_tables |
do_execsql_test 7.0 { |
- CREATE TABLE t1(a, b); |
- CREATE TABLE t2(a, b); |
+ 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 (~1000000 rows)} |
+ 0 0 0 {SCAN TABLE t1} |
} |
det 7.2 "SELECT count(*) FROM t2" { |
- 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i1(~1000000 rows)} |
+ 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 t1(a,b) VALUES(1, 2); |
+ INSERT INTO t1(a,b) VALUES(3, 4); |
- INSERT INTO t2 VALUES(1, 2); |
- INSERT INTO t2 VALUES(3, 4); |
- INSERT INTO t2 VALUES(5, 6); |
+ 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; |
} |
@@ -567,12 +591,56 @@ db close |
sqlite3 db test.db |
det 7.4 "SELECT count(*) FROM t1" { |
- 0 0 0 {SCAN TABLE t1 (~2 rows)} |
+ 0 0 0 {SCAN TABLE t1} |
} |
det 7.5 "SELECT count(*) FROM t2" { |
- 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i1(~3 rows)} |
+ 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i1} |
+} |
+ |
+#------------------------------------------------------------------------- |
+# The following tests - eqp-8.* - test that queries that use the OP_Count |
+# optimization return something sensible with EQP. |
+# |
+drop_all_tables |
+ |
+do_execsql_test 8.0 { |
+ CREATE TABLE t1(a, b, c, PRIMARY KEY(b, c)) WITHOUT ROWID; |
+ CREATE TABLE t2(a, b, c); |
+} |
+ |
+det 8.1.1 "SELECT * FROM t2" { |
+ 0 0 0 {SCAN TABLE t2} |
+} |
+ |
+det 8.1.2 "SELECT * FROM t2 WHERE rowid=?" { |
+ 0 0 0 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)} |
+} |
+ |
+det 8.1.3 "SELECT count(*) FROM t2" { |
+ 0 0 0 {SCAN TABLE t2} |
+} |
+ |
+det 8.2.1 "SELECT * FROM t1" { |
+ 0 0 0 {SCAN TABLE t1} |
} |
+det 8.2.2 "SELECT * FROM t1 WHERE b=?" { |
+ 0 0 0 {SEARCH TABLE t1 USING PRIMARY KEY (b=?)} |
+} |
+ |
+det 8.2.3 "SELECT * FROM t1 WHERE b=? AND c=?" { |
+ 0 0 0 {SEARCH TABLE t1 USING PRIMARY KEY (b=? AND c=?)} |
+} |
+ |
+det 8.2.4 "SELECT count(*) FROM t1" { |
+ 0 0 0 {SCAN TABLE t1} |
+} |
+ |
+ |
+ |
+ |
+ |
+ |
finish_test |