diff options
Diffstat (limited to 'test')
39 files changed, 466 insertions, 478 deletions
diff --git a/test/analyzeA.test b/test/analyzeA.test index a2da10edf..afcbe84b8 100644 --- a/test/analyzeA.test +++ b/test/analyzeA.test @@ -136,10 +136,10 @@ foreach {tn analyze_cmd} { do_eqp_test 1.$tn.2.5 { SELECT * FROM t1 WHERE b = 31 AND c = 0; - } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)}} + } {SEARCH TABLE t1 USING INDEX t1b (b=?)} do_eqp_test 1.$tn.2.6 { SELECT * FROM t1 WHERE b = 125 AND c = 16; - } {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c=?)}} + } {SEARCH TABLE t1 USING INDEX t1c (c=?)} do_execsql_test 1.$tn.3.1 { SELECT count(*) FROM t1 WHERE b BETWEEN 0 AND 50 @@ -156,31 +156,31 @@ foreach {tn analyze_cmd} { do_eqp_test 1.$tn.3.5 { SELECT * FROM t1 WHERE b BETWEEN 0 AND 50 AND c BETWEEN 0 AND 50 - } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)}} + } {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)} do_eqp_test 1.$tn.3.6 { SELECT * FROM t1 WHERE b BETWEEN 75 AND 125 AND c BETWEEN 75 AND 125 - } {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)}} + } {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)} do_eqp_test 1.$tn.3.7 { SELECT * FROM t1 WHERE b BETWEEN +0 AND +50 AND c BETWEEN +0 AND +50 - } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)}} + } {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)} do_eqp_test 1.$tn.3.8 { SELECT * FROM t1 WHERE b BETWEEN cast('0' AS int) AND cast('50.0' AS real) AND c BETWEEN cast('0' AS numeric) AND cast('50.0' AS real) - } {0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)}} + } {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)} do_eqp_test 1.$tn.3.9 { SELECT * FROM t1 WHERE b BETWEEN +75 AND +125 AND c BETWEEN +75 AND +125 - } {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)}} + } {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)} do_eqp_test 1.$tn.3.10 { SELECT * FROM t1 WHERE b BETWEEN cast('75' AS int) AND cast('125.0' AS real) AND c BETWEEN cast('75' AS numeric) AND cast('125.0' AS real) - } {0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)}} + } {SEARCH TABLE t1 USING INDEX t1c (c>? AND c<?)} } finish_test diff --git a/test/analyzeD.test b/test/analyzeD.test index 4588aaeeb..9c7ed7586 100644 --- a/test/analyzeD.test +++ b/test/analyzeD.test @@ -63,9 +63,7 @@ do_test 1.1 { # do_eqp_test 1.2 { SELECT * FROM t1 WHERE a=3001 AND c=150; -} { - 0 0 0 {SEARCH TABLE t1 USING INDEX t1_c (c=?)} -} +} {SEARCH TABLE t1 USING INDEX t1_c (c=?)} do_test 1.3 { execsql { DELETE FROM sqlite_stat1 } @@ -80,9 +78,7 @@ do_test 1.3 { # do_eqp_test 1.4 { SELECT * FROM t1 WHERE a=3001 AND c=150; -} { - 0 0 0 {SEARCH TABLE t1 USING INDEX t1_ab (a=?)} -} +} {SEARCH TABLE t1 USING INDEX t1_ab (a=?)} do_test 1.5 { execsql { @@ -93,9 +89,7 @@ do_test 1.5 { do_eqp_test 1.6 { SELECT * FROM t1 WHERE a=13 AND c=150; -} { - 0 0 0 {SEARCH TABLE t1 USING INDEX t1_c (c=?)} -} +} {SEARCH TABLE t1 USING INDEX t1_c (c=?)} do_test 1.7 { execsql { DELETE FROM sqlite_stat1 } @@ -108,8 +102,6 @@ do_test 1.7 { # gets this right, even without stat1 data. do_eqp_test 1.8 { SELECT * FROM t1 WHERE a=13 AND c=150; -} { - 0 0 0 {SEARCH TABLE t1 USING INDEX t1_c (c=?)} -} +} {SEARCH TABLE t1 USING INDEX t1_c (c=?)} finish_test diff --git a/test/analyzeF.test b/test/analyzeF.test index 76891ddfe..143cecdf5 100644 --- a/test/analyzeF.test +++ b/test/analyzeF.test @@ -62,7 +62,7 @@ foreach {tn where idx} { 11 "x = nullif('19', 0) AND y = nullif('4', 0)" {t1y (y=?)} 12 "x = nullif('4', 0) AND y = nullif('19', 0)" {t1y (y=?)} } { - set res "0 0 0 {SEARCH TABLE t1 USING INDEX $idx}" + set res "SEARCH TABLE t1 USING INDEX $idx" do_eqp_test 1.$tn "SELECT * FROM t1 WHERE $where" $res } @@ -92,7 +92,7 @@ foreach {tn where idx} { 3 "x = nondet4() AND y = nondet19()" {t1y (y=?)} 4 "x = nondet19() AND y = nondet4()" {t1y (y=?)} } { - set res "0 0 0 {SEARCH TABLE t1 USING INDEX $idx}" + set res "SEARCH TABLE t1 USING INDEX $idx" do_eqp_test 3.$tn "SELECT * FROM t1 WHERE $where" $res } diff --git a/test/autoindex3.test b/test/autoindex3.test index c99a175c6..9dcf2491f 100644 --- a/test/autoindex3.test +++ b/test/autoindex3.test @@ -84,8 +84,9 @@ do_execsql_test 210 { do_eqp_test 220 { select count(*) from u, v where u.b = v.b and v.e > 34; } { - 0 0 1 {SEARCH TABLE v USING INDEX ve (e>?)} - 0 1 0 {SEARCH TABLE u USING AUTOMATIC COVERING INDEX (b=?)} + QUERY PLAN + |--SEARCH TABLE v USING INDEX ve (e>?) + `--SEARCH TABLE u USING AUTOMATIC COVERING INDEX (b=?) } diff --git a/test/bestindex1.test b/test/bestindex1.test index 5857e7f00..1d79c7316 100644 --- a/test/bestindex1.test +++ b/test/bestindex1.test @@ -51,15 +51,11 @@ do_execsql_test 1.0 { do_eqp_test 1.1 { SELECT * FROM x1 WHERE a = 'abc' -} { - 0 0 0 {SCAN TABLE x1 VIRTUAL TABLE INDEX 555:eq!} -} +} {SCAN TABLE x1 VIRTUAL TABLE INDEX 555:eq!} do_eqp_test 1.2 { SELECT * FROM x1 WHERE a IN ('abc', 'def'); -} { - 0 0 0 {SCAN TABLE x1 VIRTUAL TABLE INDEX 555:eq!} -} +} {SCAN TABLE x1 VIRTUAL TABLE INDEX 555:eq!} #------------------------------------------------------------------------- # @@ -144,21 +140,24 @@ foreach {tn mode} { } {1 4} set plan(use) { - 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x WHERE a='%1%'} - 0 0 0 {USE TEMP B-TREE FOR ORDER BY} + QUERY PLAN + |--SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x WHERE a='%1%' + `--USE TEMP B-TREE FOR ORDER BY } set plan(omit) { - 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x WHERE a='%1%'} - 0 0 0 {USE TEMP B-TREE FOR ORDER BY} + QUERY PLAN + |--SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x WHERE a='%1%' + `--USE TEMP B-TREE FOR ORDER BY } set plan(use2) { - 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x} - 0 0 0 {USE TEMP B-TREE FOR ORDER BY} + QUERY PLAN + |--SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x + `--USE TEMP B-TREE FOR ORDER BY } do_eqp_test 2.2.$mode.6 { SELECT rowid FROM t1 WHERE a IN ('one', 'four') ORDER BY +rowid - } $plan($mode) + } [string map {"\n " "\n"} $plan($mode)] } # 2016-04-09. diff --git a/test/bestindex2.test b/test/bestindex2.test index 8bc3fbc32..c17665f6f 100644 --- a/test/bestindex2.test +++ b/test/bestindex2.test @@ -89,40 +89,40 @@ do_execsql_test 1.0 { do_eqp_test 1.1 { SELECT * FROM t1 WHERE a='abc' -} { - 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:indexed(a=?)} -} +} {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:indexed(a=?)} + do_eqp_test 1.2 { SELECT * FROM t1 WHERE a='abc' AND b='def' -} { - 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:indexed(a=? AND b=?)} -} +} {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:indexed(a=? AND b=?)} + do_eqp_test 1.3 { SELECT * FROM t1 WHERE a='abc' AND a='def' -} { - 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:indexed(a=?)} -} +} {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:indexed(a=?)} + do_eqp_test 1.4 { SELECT * FROM t1,t2 WHERE c=a } { - 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:} - 0 1 1 {SCAN TABLE t2 VIRTUAL TABLE INDEX 0:indexed(c=?)} + QUERY PLAN + |--SCAN TABLE t1 VIRTUAL TABLE INDEX 0: + `--SCAN TABLE t2 VIRTUAL TABLE INDEX 0:indexed(c=?) } do_eqp_test 1.5 { SELECT * FROM t1, t2 CROSS JOIN t3 WHERE t2.c = +t1.b AND t3.e=t2.d } { - 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:} - 0 1 1 {SCAN TABLE t2 VIRTUAL TABLE INDEX 0:indexed(c=?)} - 0 2 2 {SCAN TABLE t3 VIRTUAL TABLE INDEX 0:indexed(e=?)} + QUERY PLAN + |--SCAN TABLE t1 VIRTUAL TABLE INDEX 0: + |--SCAN TABLE t2 VIRTUAL TABLE INDEX 0:indexed(c=?) + `--SCAN TABLE t3 VIRTUAL TABLE INDEX 0:indexed(e=?) } do_eqp_test 1.6 { SELECT * FROM t1, t2, t3 WHERE t2.c = +t1.b AND t3.e = t2.d } { - 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:} - 0 1 1 {SCAN TABLE t2 VIRTUAL TABLE INDEX 0:indexed(c=?)} - 0 2 2 {SCAN TABLE t3 VIRTUAL TABLE INDEX 0:indexed(e=?)} + QUERY PLAN + |--SCAN TABLE t1 VIRTUAL TABLE INDEX 0: + |--SCAN TABLE t2 VIRTUAL TABLE INDEX 0:indexed(c=?) + `--SCAN TABLE t3 VIRTUAL TABLE INDEX 0:indexed(e=?) } do_execsql_test 1.7.1 { @@ -132,10 +132,11 @@ do_eqp_test 1.7.2 { SELECT * FROM x1 CROSS JOIN t1, t2, t3 WHERE t1.a = t2.c AND t1.b = t3.e } { - 0 0 0 {SCAN TABLE x1} - 0 1 1 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:} - 0 2 2 {SCAN TABLE t2 VIRTUAL TABLE INDEX 0:indexed(c=?)} - 0 3 3 {SCAN TABLE t3 VIRTUAL TABLE INDEX 0:indexed(e=?)} + QUERY PLAN + |--SCAN TABLE x1 + |--SCAN TABLE t1 VIRTUAL TABLE INDEX 0: + |--SCAN TABLE t2 VIRTUAL TABLE INDEX 0:indexed(c=?) + `--SCAN TABLE t3 VIRTUAL TABLE INDEX 0:indexed(e=?) } finish_test diff --git a/test/bestindex3.test b/test/bestindex3.test index 70c60d66d..3e9fb4f96 100644 --- a/test/bestindex3.test +++ b/test/bestindex3.test @@ -79,28 +79,26 @@ do_execsql_test 1.0 { do_eqp_test 1.1 { SELECT * FROM t1 WHERE a LIKE 'abc'; -} { - 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a LIKE ?} -} +} {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a LIKE ?} do_eqp_test 1.2 { SELECT * FROM t1 WHERE a = 'abc'; -} { - 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a EQ ?} -} +} {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a EQ ?} do_eqp_test 1.3 { SELECT * FROM t1 WHERE a = 'abc' OR b = 'def'; } { - 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a EQ ?} - 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:b EQ ?} + QUERY PLAN + |--SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a EQ ? + `--SCAN TABLE t1 VIRTUAL TABLE INDEX 0:b EQ ? } do_eqp_test 1.4 { SELECT * FROM t1 WHERE a LIKE 'abc%' OR b = 'def'; } { - 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a LIKE ?} - 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:b EQ ?} + QUERY PLAN + |--SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a LIKE ? + `--SCAN TABLE t1 VIRTUAL TABLE INDEX 0:b EQ ? } do_execsql_test 1.5 { @@ -148,8 +146,9 @@ ifcapable !icu { do_eqp_test 2.2 { SELECT * FROM t2 WHERE x LIKE 'abc%' OR y = 'def' } { - 0 0 0 {SEARCH TABLE t2 USING INDEX t2x (x>? AND x<?)} - 0 0 0 {SEARCH TABLE t2 USING INDEX t2y (y=?)} + QUERY PLAN + |--SEARCH TABLE t2 USING INDEX t2x (x>? AND x<?) + `--SEARCH TABLE t2 USING INDEX t2y (y=?) } } diff --git a/test/bigmmap.test b/test/bigmmap.test index 9284bda37..7da1df247 100644 --- a/test/bigmmap.test +++ b/test/bigmmap.test @@ -92,13 +92,13 @@ for {set i 0} {$i < 9} {incr i} { SELECT * FROM t$t AS o WHERE NOT EXISTS( SELECT * FROM t$t AS i WHERE a=o.a AND +b=o.b AND +c=o.c ) ORDER BY b, c; - " " - 0 0 0 {SCAN TABLE t$t AS o USING COVERING INDEX sqlite_autoindex_t${t}_1} - 0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 1} - 1 0 0 {SEARCH TABLE t$t AS i USING INTEGER PRIMARY KEY (rowid=?)} - " + " [string map {"\n " "\n"} " + QUERY PLAN + |--SCAN TABLE t$t AS o USING COVERING INDEX sqlite_autoindex_t${t}_1 + `--CORRELATED SCALAR SUBQUERY + `--SEARCH TABLE t$t AS i USING INTEGER PRIMARY KEY (rowid=?) + "] } } finish_test - diff --git a/test/cost.test b/test/cost.test index 9c10d821d..c2591b147 100644 --- a/test/cost.test +++ b/test/cost.test @@ -24,8 +24,9 @@ do_execsql_test 1.1 { do_eqp_test 1.2 { SELECT e FROM t3, t4 WHERE b=c ORDER BY b, d; } { - 0 0 0 {SCAN TABLE t3 USING COVERING INDEX i3} - 0 1 1 {SEARCH TABLE t4 USING INDEX i4 (c=?)} + QUERY PLAN + |--SCAN TABLE t3 USING COVERING INDEX i3 + `--SEARCH TABLE t4 USING INDEX i4 (c=?) } @@ -38,9 +39,7 @@ do_execsql_test 2.1 { # if the index is a non-covering index. do_eqp_test 2.2 { SELECT * FROM t1 ORDER BY a; -} { - 0 0 0 {SCAN TABLE t1 USING INDEX i1} -} +} {SCAN TABLE t1 USING INDEX i1} do_execsql_test 3.1 { CREATE TABLE t5(a INTEGER PRIMARY KEY,b,c,d,e,f,g); @@ -57,10 +56,11 @@ do_eqp_test 3.2 { WHERE b IS NULL OR c IS NULL OR d IS NULL ORDER BY a; } { - 0 0 0 {SEARCH TABLE t5 USING INDEX t5b (b=?)} - 0 0 0 {SEARCH TABLE t5 USING INDEX t5c (c=?)} - 0 0 0 {SEARCH TABLE t5 USING INDEX t5d (d=?)} - 0 0 0 {USE TEMP B-TREE FOR ORDER BY} + QUERY PLAN + |--SEARCH TABLE t5 USING INDEX t5b (b=?) + |--SEARCH TABLE t5 USING INDEX t5c (c=?) + |--SEARCH TABLE t5 USING INDEX t5d (d=?) + `--USE TEMP B-TREE FOR ORDER BY } #------------------------------------------------------------------------- @@ -79,14 +79,11 @@ do_execsql_test 4.1 { } do_eqp_test 4.2 { SELECT * FROM t1 WHERE likelihood(a=?, 0.014) AND b BETWEEN ? AND ?; -} { - 0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)} -} +} {SEARCH TABLE t1 USING INDEX i1 (a=?)} + do_eqp_test 4.3 { SELECT * FROM t1 WHERE likelihood(a=?, 0.016) AND b BETWEEN ? AND ?; -} { - 0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b>? AND b<?)} -} +} {SEARCH TABLE t1 USING INDEX i2 (b>? AND b<?)} #------------------------------------------------------------------------- @@ -100,15 +97,17 @@ do_execsql_test 5.1 { do_eqp_test 5.2 { SELECT * FROM t2 ORDER BY x, y; } { - 0 0 0 {SCAN TABLE t2 USING INDEX t2i1} - 0 0 0 {USE TEMP B-TREE FOR RIGHT PART OF ORDER BY} + QUERY PLAN + |--SCAN TABLE t2 USING INDEX t2i1 + `--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY } do_eqp_test 5.3 { SELECT * FROM t2 WHERE x BETWEEN ? AND ? ORDER BY rowid; } { - 0 0 0 {SEARCH TABLE t2 USING INDEX t2i1 (x>? AND x<?)} - 0 0 0 {USE TEMP B-TREE FOR ORDER BY} + QUERY PLAN + |--SEARCH TABLE t2 USING INDEX t2i1 (x>? AND x<?) + `--USE TEMP B-TREE FOR ORDER BY } # where7.test, where8.test: @@ -122,9 +121,10 @@ do_execsql_test 6.1 { do_eqp_test 6.2 { SELECT a FROM t3 WHERE (b BETWEEN 2 AND 4) OR c=100 ORDER BY a } { - 0 0 0 {SEARCH TABLE t3 USING INDEX t3i1 (b>? AND b<?)} - 0 0 0 {SEARCH TABLE t3 USING INDEX t3i2 (c=?)} - 0 0 0 {USE TEMP B-TREE FOR ORDER BY} + QUERY PLAN + |--SEARCH TABLE t3 USING INDEX t3i1 (b>? AND b<?) + |--SEARCH TABLE t3 USING INDEX t3i2 (c=?) + `--USE TEMP B-TREE FOR ORDER BY } #------------------------------------------------------------------------- @@ -145,9 +145,10 @@ do_eqp_test 7.2 { WHERE (b>=950 AND b<=1010) OR (b IS NULL AND c NOT NULL) ORDER BY a } { - 0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?)} - 0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)} - 0 0 0 {USE TEMP B-TREE FOR ORDER BY} + QUERY PLAN + |--SEARCH TABLE t1 USING INDEX t1b (b>? AND b<?) + |--SEARCH TABLE t1 USING INDEX t1b (b=?) + `--USE TEMP B-TREE FOR ORDER BY } do_eqp_test 7.3 { @@ -155,15 +156,11 @@ do_eqp_test 7.3 { WHERE (+b IS NULL AND c NOT NULL AND d NOT NULL) OR (b NOT NULL AND c IS NULL AND d NOT NULL) OR (b NOT NULL AND c NOT NULL AND d IS NULL) -} { - 0 0 0 {SCAN TABLE t1} -} +} {SCAN TABLE t1} do_eqp_test 7.4 { SELECT rowid FROM t1 WHERE (+b IS NULL AND c NOT NULL) OR c IS NULL -} { - 0 0 0 {SCAN TABLE t1} -} +} {SCAN TABLE t1} #------------------------------------------------------------------------- # @@ -194,10 +191,11 @@ do_eqp_test 8.2 { AND unlikely(composer.cid=track.cid) AND unlikely(album.aid=track.aid); } { - 0 0 2 {SCAN TABLE track} - 0 1 0 {SEARCH TABLE album USING INTEGER PRIMARY KEY (rowid=?)} - 0 2 1 {SEARCH TABLE composer USING INTEGER PRIMARY KEY (rowid=?)} - 0 0 0 {USE TEMP B-TREE FOR DISTINCT} + QUERY PLAN + |--SCAN TABLE track + |--SEARCH TABLE album USING INTEGER PRIMARY KEY (rowid=?) + |--SEARCH TABLE composer USING INTEGER PRIMARY KEY (rowid=?) + `--USE TEMP B-TREE FOR DISTINCT } #------------------------------------------------------------------------- @@ -263,27 +261,19 @@ ifcapable stat4 { do_eqp_test 10.3 { SELECT rowid FROM t6 WHERE a=0 AND c=0 - } { - 0 0 0 {SEARCH TABLE t6 USING INDEX t6i2 (c=?)} - } + } {SEARCH TABLE t6 USING INDEX t6i2 (c=?)} do_eqp_test 10.4 { SELECT rowid FROM t6 WHERE a=0 AND b='xyz' AND c=0 - } { - 0 0 0 {SEARCH TABLE t6 USING INDEX t6i2 (c=?)} - } + } {SEARCH TABLE t6 USING INDEX t6i2 (c=?)} do_eqp_test 10.5 { SELECT rowid FROM t6 WHERE likelihood(a=0, 0.1) AND c=0 - } { - 0 0 0 {SEARCH TABLE t6 USING INDEX t6i1 (a=?)} - } + } {SEARCH TABLE t6 USING INDEX t6i1 (a=?)} do_eqp_test 10.6 { SELECT rowid FROM t6 WHERE likelihood(a=0, 0.1) AND b='xyz' AND c=0 - } { - 0 0 0 {SEARCH TABLE t6 USING INDEX t6i1 (a=? AND b=?)} - } + } {SEARCH TABLE t6 USING INDEX t6i1 (a=? AND b=?)} } finish_test diff --git a/test/coveridxscan.test b/test/coveridxscan.test index a8d1a8ff7..cc9864439 100644 --- a/test/coveridxscan.test +++ b/test/coveridxscan.test @@ -109,16 +109,11 @@ do_execsql_test 5.1.0 " do_eqp_test 5.1.1 { SELECT * FROM t1 ORDER BY c1, c2; -} { - 0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1} -} +} {SCAN TABLE t1 USING COVERING INDEX i1} do_eqp_test 5.1.2 { SELECT * FROM t2 ORDER BY c1, c2; -} { - 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i2} -} - +} {SCAN TABLE t2 USING COVERING INDEX i2} finish_test diff --git a/test/fts3join.test b/test/fts3join.test index 0eb7f47c6..f3b9b3639 100644 --- a/test/fts3join.test +++ b/test/fts3join.test @@ -96,9 +96,11 @@ do_eqp_test 4.2 { ) AS rr ON t4.rowid=rr.docid WHERE t4.y = ?; } { - 1 0 0 {SCAN TABLE ft4 VIRTUAL TABLE INDEX 3:} - 0 0 0 {SCAN TABLE t4} - 0 1 1 {SEARCH SUBQUERY 1 AS rr USING AUTOMATIC COVERING INDEX (docid=?)} + QUERY PLAN + |--MATERIALIZE xxxxxx + | `--SCAN TABLE ft4 VIRTUAL TABLE INDEX 3: + |--SCAN TABLE t4 + `--SEARCH SUBQUERY xxxxxx AS rr USING AUTOMATIC COVERING INDEX (docid=?) } finish_test diff --git a/test/fts3query.test b/test/fts3query.test index 7d5ae991f..bc25699e1 100644 --- a/test/fts3query.test +++ b/test/fts3query.test @@ -118,26 +118,30 @@ do_test fts3query-4.1 { do_eqp_test fts3query-4.2 { SELECT t1.number FROM t1, ft WHERE t1.number=ft.rowid ORDER BY t1.date } { - 0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1} - 0 1 1 {SCAN TABLE ft VIRTUAL TABLE INDEX 1:} + QUERY PLAN + |--SCAN TABLE t1 USING COVERING INDEX i1 + `--SCAN TABLE ft VIRTUAL TABLE INDEX 1: } do_eqp_test fts3query-4.3 { SELECT t1.number FROM ft, t1 WHERE t1.number=ft.rowid ORDER BY t1.date } { - 0 0 1 {SCAN TABLE t1 USING COVERING INDEX i1} - 0 1 0 {SCAN TABLE ft VIRTUAL TABLE INDEX 1:} + QUERY PLAN + |--SCAN TABLE t1 USING COVERING INDEX i1 + `--SCAN TABLE ft VIRTUAL TABLE INDEX 1: } do_eqp_test fts3query-4.4 { SELECT t1.number FROM t1, bt WHERE t1.number=bt.rowid ORDER BY t1.date } { - 0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1} - 0 1 1 {SEARCH TABLE bt USING INTEGER PRIMARY KEY (rowid=?)} + QUERY PLAN + |--SCAN TABLE t1 USING COVERING INDEX i1 + `--SEARCH TABLE bt USING INTEGER PRIMARY KEY (rowid=?) } do_eqp_test fts3query-4.5 { SELECT t1.number FROM bt, t1 WHERE t1.number=bt.rowid ORDER BY t1.date } { - 0 0 1 {SCAN TABLE t1 USING COVERING INDEX i1} - 0 1 0 {SEARCH TABLE bt USING INTEGER PRIMARY KEY (rowid=?)} + QUERY PLAN + |--SCAN TABLE t1 USING COVERING INDEX i1 + `--SEARCH TABLE bt USING INTEGER PRIMARY KEY (rowid=?) } diff --git a/test/index6.test b/test/index6.test index f6177b44e..4ddce453f 100644 --- a/test/index6.test +++ b/test/index6.test @@ -318,8 +318,9 @@ do_execsql_test index6-8.0 { do_eqp_test index6-8.1 { SELECT * FROM t8a LEFT JOIN t8b ON (x = 'value' AND y = a) } { - 0 0 0 {SCAN TABLE t8a} - 0 1 1 {SEARCH TABLE t8b USING INDEX i8c (y=?)} + QUERY PLAN + |--SCAN TABLE t8a + `--SEARCH TABLE t8b USING INDEX i8c (y=?) } do_execsql_test index6-8.2 { diff --git a/test/index7.test b/test/index7.test index 0037a8a44..aa0cf8c1f 100644 --- a/test/index7.test +++ b/test/index7.test @@ -321,9 +321,8 @@ do_execsql_test index7-6.3 { } do_eqp_test index7-6.4 { SELECT * FROM v4 WHERE d='xyz' AND c='def' -} { - 0 0 0 {SEARCH TABLE t4 USING INDEX i4 (c=?)} -} +} {SEARCH TABLE t4 USING INDEX i4 (c=?)} + do_catchsql_test index7-6.5 { CREATE INDEX t5a ON t5(a) WHERE a=#1; } {1 {near "#1": syntax error}} diff --git a/test/indexedby.test b/test/indexedby.test index a0f7bea76..836da80e5 100644 --- a/test/indexedby.test +++ b/test/indexedby.test @@ -40,17 +40,18 @@ proc EQP {sql} { # These tests are to check that "EXPLAIN QUERY PLAN" is working as expected. # -do_execsql_test indexedby-1.2 { - EXPLAIN QUERY PLAN select * from t1 WHERE a = 10; -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}} -do_execsql_test indexedby-1.3 { - EXPLAIN QUERY PLAN select * from t1 ; -} {0 0 0 {SCAN TABLE t1}} -do_execsql_test indexedby-1.4 { - EXPLAIN QUERY PLAN select * from t1, t2 WHERE c = 10; +do_eqp_test indexedby-1.2 { + select * from t1 WHERE a = 10; +} {SEARCH TABLE t1 USING INDEX i1 (a=?)} +do_eqp_test indexedby-1.3 { + select * from t1 ; +} {SCAN TABLE t1} +do_eqp_test indexedby-1.4 { + select * from t1, t2 WHERE c = 10; } { - 0 0 1 {SEARCH TABLE t2 USING INDEX i3 (c=?)} - 0 1 0 {SCAN TABLE t1} + QUERY PLAN + |--SEARCH TABLE t2 USING INDEX i3 (c=?) + `--SCAN TABLE t1 } # Parser tests. Test that an INDEXED BY or NOT INDEX clause can be @@ -115,25 +116,23 @@ do_test indexedby-2.7 { # the rowid can still be used to look up entries even when "NOT INDEXED" # is specified. # -do_execsql_test indexedby-3.1 { - EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a = 'one' AND b = 'two' +do_eqp_test indexedby-3.1 { + SELECT * FROM t1 WHERE a = 'one' AND b = 'two' } {/SEARCH TABLE t1 USING INDEX/} -do_execsql_test indexedby-3.1.1 { - EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two' -} {0 0 0 {SCAN TABLE t1}} -do_execsql_test indexedby-3.1.2 { - EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE rowid=1 +do_eqp_test indexedby-3.1.1 { + SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two' +} {SCAN TABLE t1} +do_eqp_test indexedby-3.1.2 { + SELECT * FROM t1 NOT INDEXED WHERE rowid=1 } {/SEARCH TABLE t1 USING INTEGER PRIMARY KEY .rowid=/} -do_execsql_test indexedby-3.2 { - EXPLAIN QUERY PLAN +do_eqp_test indexedby-3.2 { SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two' -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}} -do_execsql_test indexedby-3.3 { - EXPLAIN QUERY PLAN +} {SEARCH TABLE t1 USING INDEX i1 (a=?)} +do_eqp_test indexedby-3.3 { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two' -} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?)}} +} {SEARCH TABLE t1 USING INDEX i2 (b=?)} do_test indexedby-3.4 { catchsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' } } {1 {no query solution}} @@ -147,14 +146,12 @@ do_test indexedby-3.7 { catchsql { SELECT * FROM t1 INDEXED BY i1 ORDER BY a } } {0 {}} -do_execsql_test indexedby-3.8 { - EXPLAIN QUERY PLAN +do_eqp_test indexedby-3.8 { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 ORDER BY e -} {0 0 0 {SCAN TABLE t3 USING INDEX sqlite_autoindex_t3_1}} -do_execsql_test indexedby-3.9 { - EXPLAIN QUERY PLAN +} {SCAN TABLE t3 USING INDEX sqlite_autoindex_t3_1} +do_eqp_test indexedby-3.9 { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE e = 10 -} {0 0 0 {SEARCH TABLE t3 USING INDEX sqlite_autoindex_t3_1 (e=?)}} +} {SEARCH TABLE t3 USING INDEX sqlite_autoindex_t3_1 (e=?)} do_test indexedby-3.10 { catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE f = 10 } } {1 {no query solution}} @@ -164,17 +161,19 @@ do_test indexedby-3.11 { # Tests for multiple table cases. # -do_execsql_test indexedby-4.1 { - EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE a = c +do_eqp_test indexedby-4.1 { + SELECT * FROM t1, t2 WHERE a = c } { - 0 0 0 {SCAN TABLE t1} - 0 1 1 {SEARCH TABLE t2 USING INDEX i3 (c=?)} + QUERY PLAN + |--SCAN TABLE t1 + `--SEARCH TABLE t2 USING INDEX i3 (c=?) } -do_execsql_test indexedby-4.2 { - EXPLAIN QUERY PLAN SELECT * FROM t1 INDEXED BY i1, t2 WHERE a = c +do_eqp_test indexedby-4.2 { + SELECT * FROM t1 INDEXED BY i1, t2 WHERE a = c } { - 0 0 1 {SCAN TABLE t2} - 0 1 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)} + QUERY PLAN + |--SCAN TABLE t2 + `--SEARCH TABLE t1 USING INDEX i1 (a=?) } do_test indexedby-4.3 { catchsql { @@ -194,10 +193,10 @@ do_test indexedby-4.4 { do_execsql_test indexedby-5.1 { CREATE VIEW v2 AS SELECT * FROM t1 INDEXED BY i1 WHERE a > 5; EXPLAIN QUERY PLAN SELECT * FROM v2 -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?)}} +} {/*SEARCH TABLE t1 USING INDEX i1 (a>?)*/} do_execsql_test indexedby-5.2 { EXPLAIN QUERY PLAN SELECT * FROM v2 WHERE b = 10 -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?)}} +} {/*SEARCH TABLE t1 USING INDEX i1 (a>?)*/} do_test indexedby-5.3 { execsql { DROP INDEX i1 } catchsql { SELECT * FROM v2 } @@ -216,12 +215,12 @@ do_test indexedby-5.5 { # Test that "NOT INDEXED" may use the rowid index, but not others. # -do_execsql_test indexedby-6.1 { - EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 10 ORDER BY rowid -} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?)}} -do_execsql_test indexedby-6.2 { - EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE b = 10 ORDER BY rowid -} {0 0 0 {SCAN TABLE t1}} +do_eqp_test indexedby-6.1 { + SELECT * FROM t1 WHERE b = 10 ORDER BY rowid +} {SEARCH TABLE t1 USING INDEX i2 (b=?)} +do_eqp_test indexedby-6.2 { + SELECT * FROM t1 NOT INDEXED WHERE b = 10 ORDER BY rowid +} {SCAN TABLE t1} # EVIDENCE-OF: R-40297-14464 The INDEXED BY phrase forces the SQLite # query planner to use a particular named index on a DELETE, SELECT, or @@ -229,44 +228,42 @@ do_execsql_test indexedby-6.2 { # # Test that "INDEXED BY" can be used in a DELETE statement. # -do_execsql_test indexedby-7.1 { - EXPLAIN QUERY PLAN DELETE FROM t1 WHERE a = 5 -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}} -do_execsql_test indexedby-7.2 { - EXPLAIN QUERY PLAN DELETE FROM t1 NOT INDEXED WHERE a = 5 -} {0 0 0 {SCAN TABLE t1}} -do_execsql_test indexedby-7.3 { - EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5 -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}} -do_execsql_test indexedby-7.4 { - EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5 AND b = 10 -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}} -do_execsql_test indexedby-7.5 { - EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i2 WHERE a = 5 AND b = 10 -} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?)}} +do_eqp_test indexedby-7.1 { + DELETE FROM t1 WHERE a = 5 +} {SEARCH TABLE t1 USING INDEX i1 (a=?)} +do_eqp_test indexedby-7.2 { + DELETE FROM t1 NOT INDEXED WHERE a = 5 +} {SCAN TABLE t1} +do_eqp_test indexedby-7.3 { + DELETE FROM t1 INDEXED BY i1 WHERE a = 5 +} {SEARCH TABLE t1 USING INDEX i1 (a=?)} +do_eqp_test indexedby-7.4 { + DELETE FROM t1 INDEXED BY i1 WHERE a = 5 AND b = 10 +} {SEARCH TABLE t1 USING INDEX i1 (a=?)} +do_eqp_test indexedby-7.5 { + DELETE FROM t1 INDEXED BY i2 WHERE a = 5 AND b = 10 +} {SEARCH TABLE t1 USING INDEX i2 (b=?)} do_test indexedby-7.6 { catchsql { DELETE FROM t1 INDEXED BY i2 WHERE a = 5} } {1 {no query solution}} # Test that "INDEXED BY" can be used in an UPDATE statement. # -do_execsql_test indexedby-8.1 { - EXPLAIN QUERY PLAN UPDATE t1 SET rowid=rowid+1 WHERE a = 5 -} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?)}} -do_execsql_test indexedby-8.2 { - EXPLAIN QUERY PLAN UPDATE t1 NOT INDEXED SET rowid=rowid+1 WHERE a = 5 -} {0 0 0 {SCAN TABLE t1}} -do_execsql_test indexedby-8.3 { - EXPLAIN QUERY PLAN UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 -} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?)}} -do_execsql_test indexedby-8.4 { - EXPLAIN QUERY PLAN +do_eqp_test indexedby-8.1 { + UPDATE t1 SET rowid=rowid+1 WHERE a = 5 +} {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?)} +do_eqp_test indexedby-8.2 { + UPDATE t1 NOT INDEXED SET rowid=rowid+1 WHERE a = 5 +} {SCAN TABLE t1} +do_eqp_test indexedby-8.3 { + UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 +} {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?)} +do_eqp_test indexedby-8.4 { UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 AND b = 10 -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}} -do_execsql_test indexedby-8.5 { - EXPLAIN QUERY PLAN +} {SEARCH TABLE t1 USING INDEX i1 (a=?)} +do_eqp_test indexedby-8.5 { UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5 AND b = 10 -} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?)}} +} {SEARCH TABLE t1 USING INDEX i2 (b=?)} do_test indexedby-8.6 { catchsql { UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5} } {1 {no query solution}} @@ -341,7 +338,7 @@ do_execsql_test 11.4 { } {1 1 3} do_eqp_test 11.5 { SELECT a,b,rowid FROM x1 INDEXED BY x1i WHERE a=1 AND b=1 AND rowid='3.0'; -} {0 0 0 {SEARCH TABLE x1 USING COVERING INDEX x1i (a=? AND b=? AND rowid=?)}} +} {SEARCH TABLE x1 USING COVERING INDEX x1i (a=? AND b=? AND rowid=?)} do_execsql_test 11.6 { CREATE TABLE x2(c INTEGER PRIMARY KEY, a, b TEXT); @@ -362,7 +359,7 @@ do_execsql_test 11.9 { } {1 1 3} do_eqp_test 11.10 { SELECT a,b,c FROM x2 INDEXED BY x2i WHERE a=1 AND b=1 AND c='3.0'; -} {0 0 0 {SEARCH TABLE x2 USING COVERING INDEX x2i (a=? AND b=? AND rowid=?)}} +} {SEARCH TABLE x2 USING COVERING INDEX x2i (a=? AND b=? AND rowid=?)} #------------------------------------------------------------------------- # Check INDEXED BY works (throws an exception) with partial indexes that diff --git a/test/indexexpr2.test b/test/indexexpr2.test index 037db0420..aed887a84 100644 --- a/test/indexexpr2.test +++ b/test/indexexpr2.test @@ -91,10 +91,11 @@ ifcapable json1 { WHERE json_extract(x, '$.b') IS NOT NULL AND json_extract(x, '$.a') IS NULL GROUP BY json_extract(x, '$.b') COLLATE nocase ORDER BY json_extract(x, '$.b') COLLATE nocase; - } { - 0 0 0 {SCAN TABLE t2} - 0 0 0 {USE TEMP B-TREE FOR GROUP BY} - } + } [string map {"\n " \n} { + QUERY PLAN + |--SCAN TABLE t2 + `--USE TEMP B-TREE FOR GROUP BY + }] do_execsql_test 3.3.2 { CREATE INDEX i3 ON t3(json_extract(x, '$.a'), json_extract(x, '$.b')); @@ -105,10 +106,11 @@ ifcapable json1 { WHERE json_extract(x, '$.b') IS NOT NULL AND json_extract(x, '$.a') IS NULL GROUP BY json_extract(x, '$.b') COLLATE nocase ORDER BY json_extract(x, '$.b') COLLATE nocase; - } { - 0 0 0 {SEARCH TABLE t3 USING INDEX i3 (<expr>=?)} - 0 0 0 {USE TEMP B-TREE FOR GROUP BY} - } + } [string map {"\n " \n} { + QUERY PLAN + |--SEARCH TABLE t3 USING INDEX i3 (<expr>=?) + `--USE TEMP B-TREE FOR GROUP BY + }] } do_execsql_test 3.4.0 { diff --git a/test/join2.test b/test/join2.test index b5b9e6be1..5a70573e0 100644 --- a/test/join2.test +++ b/test/join2.test @@ -112,15 +112,17 @@ do_execsql_test 3.0 { do_eqp_test 3.1 { SELECT v2 FROM t1 LEFT JOIN t2 USING (k2) LEFT JOIN t3_1 USING (k3); } { - 0 0 0 {SCAN TABLE t1} - 0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)} + QUERY PLAN + |--SCAN TABLE t1 + `--SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) } do_eqp_test 3.2 { SELECT v2 FROM t1 LEFT JOIN t2 USING (k2) LEFT JOIN t3_2 USING (k3); } { - 0 0 0 {SCAN TABLE t1} - 0 1 1 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)} + QUERY PLAN + |--SCAN TABLE t1 + `--SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?) } #------------------------------------------------------------------------- @@ -158,15 +160,17 @@ do_execsql_test 4.1.4 { do_eqp_test 4.1.5 { SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v2); } { - 0 0 0 {SCAN TABLE c1} - 0 1 1 {SEARCH TABLE c2 USING INTEGER PRIMARY KEY (rowid=?)} - 0 2 2 {SEARCH TABLE c3 USING INTEGER PRIMARY KEY (rowid=?)} + QUERY PLAN + |--SCAN TABLE c1 + |--SEARCH TABLE c2 USING INTEGER PRIMARY KEY (rowid=?) + `--SEARCH TABLE c3 USING INTEGER PRIMARY KEY (rowid=?) } do_eqp_test 4.1.6 { SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1); } { - 0 0 0 {SCAN TABLE c1} - 0 1 2 {SEARCH TABLE c3 USING INTEGER PRIMARY KEY (rowid=?)} + QUERY PLAN + |--SCAN TABLE c1 + `--SEARCH TABLE c3 USING INTEGER PRIMARY KEY (rowid=?) } do_execsql_test 4.2.0 { @@ -203,15 +207,17 @@ do_execsql_test 4.2.4 { do_eqp_test 4.2.5 { SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v2); } { - 0 0 0 {SCAN TABLE c1} - 0 1 1 {SEARCH TABLE c2 USING INDEX sqlite_autoindex_c2_1 (k=?)} - 0 2 2 {SEARCH TABLE c3 USING INDEX sqlite_autoindex_c3_1 (k=?)} + QUERY PLAN + |--SCAN TABLE c1 + |--SEARCH TABLE c2 USING INDEX sqlite_autoindex_c2_1 (k=?) + `--SEARCH TABLE c3 USING INDEX sqlite_autoindex_c3_1 (k=?) } do_eqp_test 4.2.6 { SELECT v1, v3 FROM c1 LEFT JOIN c2 ON (c2.k=v1) LEFT JOIN c3 ON (c3.k=v1+1); } { - 0 0 0 {SCAN TABLE c1} - 0 1 2 {SEARCH TABLE c3 USING INDEX sqlite_autoindex_c3_1 (k=?)} + QUERY PLAN + |--SCAN TABLE c1 + `--SEARCH TABLE c3 USING INDEX sqlite_autoindex_c3_1 (k=?) } # 2017-11-23 (Thanksgiving day) @@ -245,14 +251,11 @@ do_execsql_test 5.0 { } do_eqp_test 5.1 { SELECT s1.a FROM s1 left join s2 using (a); -} { - 0 0 0 {SCAN TABLE s1} -} +} {SCAN TABLE s1} + do_eqp_test 5.2 { SELECT s1.a FROM s1 left join s3 using (a); -} { - 0 0 0 {SCAN TABLE s1} -} +} {SCAN TABLE s1} do_execsql_test 6.0 { CREATE TABLE u1(a INTEGER PRIMARY KEY, b, c); @@ -261,9 +264,7 @@ do_execsql_test 6.0 { } do_eqp_test 6.1 { SELECT u2.* FROM u2 LEFT JOIN u1 ON( u1.a=u2.a AND u1.b=u2.b AND u1.c=u2.c ); -} { - 0 0 0 {SCAN TABLE u2} -} +} {SCAN TABLE u2} db close sqlite3 db :memory: diff --git a/test/join5.test b/test/join5.test index 12fe56cb9..043c626d4 100644 --- a/test/join5.test +++ b/test/join5.test @@ -264,9 +264,10 @@ do_eqp_test 7.2 { t2.x = t1.x AND (t2.y=? OR (t2.y=? AND t2.z IS NOT NULL)) ); } { - 0 0 0 {SCAN TABLE t1} - 0 1 1 {SEARCH TABLE t2 USING INDEX t2xy (x=? AND y=?)} - 0 1 1 {SEARCH TABLE t2 USING INDEX t2xy (x=? AND y=?)} + QUERY PLAN + |--SCAN TABLE t1 + |--SEARCH TABLE t2 USING INDEX t2xy (x=? AND y=?) + `--SEARCH TABLE t2 USING INDEX t2xy (x=? AND y=?) } do_execsql_test 7.3 { @@ -285,9 +286,9 @@ do_execsql_test 7.3 { do_eqp_test 7.4 { SELECT * FROM t3 LEFT JOIN t4 ON (t4.x = t3.x) WHERE (t4.y = ? OR t4.z = ?); } { - 0 0 0 {SCAN TABLE t3} - 0 1 1 {SEARCH TABLE t4 USING INDEX t4xz (x=?)} + QUERY PLAN + |--SCAN TABLE t3 + `--SEARCH TABLE t4 USING INDEX t4xz (x=?) } finish_test - diff --git a/test/mallocK.test b/test/mallocK.test index 45ee7905c..139644d83 100644 --- a/test/mallocK.test +++ b/test/mallocK.test @@ -121,10 +121,11 @@ do_execsql_test 6.0 { ifcapable stat4 { do_eqp_test 6.1 { SELECT DISTINCT c FROM t3 WHERE b BETWEEN '.xx..' AND '.xxxx'; - } { - 0 0 0 {SEARCH TABLE t3 USING INDEX i3 (ANY(a) AND b>? AND b<?)} - 0 0 0 {USE TEMP B-TREE FOR DISTINCT} - } + } [string map {"\n " \n} { + QUERY PLAN + |--SEARCH TABLE t3 USING INDEX i3 (ANY(a) AND b>? AND b<?) + `--USE TEMP B-TREE FOR DISTINCT + }] } do_faultsim_test 6 -faults oom* -body { diff --git a/test/orderby1.test b/test/orderby1.test index 831936ae9..3983ff5db 100644 --- a/test/orderby1.test +++ b/test/orderby1.test @@ -512,8 +512,9 @@ do_execsql_test 8.0 { do_eqp_test 8.1 { SELECT * FROM t1 ORDER BY a, b; } { - 0 0 0 {SCAN TABLE t1 USING INDEX i1} - 0 0 0 {USE TEMP B-TREE FOR RIGHT PART OF ORDER BY} + QUERY PLAN + |--SCAN TABLE t1 USING INDEX i1 + `--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY } do_execsql_test 8.2 { diff --git a/test/rollback2.test b/test/rollback2.test index 3ba0f3f9f..70aecc337 100644 --- a/test/rollback2.test +++ b/test/rollback2.test @@ -101,7 +101,7 @@ do_rollback_test 2.2 -setup { # do_eqp_test 3.1 { SELECT i FROM t1 WHERE (i%2)==0 ORDER BY h DESC; -} {0 0 0 {SCAN TABLE t1 USING INDEX i1}} +} {SCAN TABLE t1 USING INDEX i1} do_rollback_test 3.2 -setup { BEGIN; DELETE FROM t1 WHERE (i%2)==1; @@ -131,7 +131,7 @@ do_execsql_test 4.1 { UPDATE t1 SET h = $leader || h; } do_eqp_test 4.2 { SELECT i FROM t1 WHERE (i%2)==0 ORDER BY h ASC; -} {0 0 0 {SCAN TABLE t1 USING INDEX i1}} +} {SCAN TABLE t1 USING INDEX i1} do_rollback_test 4.3 -setup { BEGIN; DELETE FROM t1 WHERE (i%2)==1; diff --git a/test/rowvalue.test b/test/rowvalue.test index 00d939533..b8ba2e044 100644 --- a/test/rowvalue.test +++ b/test/rowvalue.test @@ -175,19 +175,19 @@ do_execsql_test 7.0 { foreach {tn sql res eqp} { 1 "SELECT * FROM xy WHERE (i, j) IS (2, 2)" {2 2 2} - "0 0 0 {SEARCH TABLE xy USING INTEGER PRIMARY KEY (rowid=?)}" + "SEARCH TABLE xy USING INTEGER PRIMARY KEY (rowid=?)" 2 "SELECT * FROM xy WHERE (k, j) < (2, 3)" {1 1 1 2 2 2} - "0 0 0 {SCAN TABLE xy}" + "SCAN TABLE xy" 3 "SELECT * FROM xy WHERE (i, j) < (2, 3)" {1 1 1 2 2 2} - "0 0 0 {SEARCH TABLE xy USING INTEGER PRIMARY KEY (rowid<?)}" + "SEARCH TABLE xy USING INTEGER PRIMARY KEY (rowid<?)" 4 "SELECT * FROM xy WHERE (i, j) > (2, 1)" {2 2 2 3 3 3 4 4 4} - "0 0 0 {SEARCH TABLE xy USING INTEGER PRIMARY KEY (rowid>?)}" + "SEARCH TABLE xy USING INTEGER PRIMARY KEY (rowid>?)" 5 "SELECT * FROM xy WHERE (i, j) > ('2', 1)" {2 2 2 3 3 3 4 4 4} - "0 0 0 {SEARCH TABLE xy USING INTEGER PRIMARY KEY (rowid>?)}" + "SEARCH TABLE xy USING INTEGER PRIMARY KEY (rowid>?)" } { do_eqp_test 7.$tn.1 $sql $eqp diff --git a/test/rowvalue4.test b/test/rowvalue4.test index 2e6a28c23..ce025fc92 100644 --- a/test/rowvalue4.test +++ b/test/rowvalue4.test @@ -184,34 +184,33 @@ ifcapable stat4 { ANALYZE; } - do_eqp_test 3.1.1 { SELECT * FROM c1 WHERE a=1 AND c=2 } { - 0 0 0 {SEARCH TABLE c1 USING INDEX c1cd (c=?)} - } - do_eqp_test 3.1.2 { SELECT * FROM c1 WHERE a=1 AND b>'d' AND c=2 } { - 0 0 0 {SEARCH TABLE c1 USING INDEX c1cd (c=?)} - } - do_eqp_test 3.1.3 { SELECT * FROM c1 WHERE a=1 AND b>'l' AND c=2 } { - 0 0 0 {SEARCH TABLE c1 USING INDEX c1ab (a=? AND b>?)} - } + do_eqp_test 3.1.1 { SELECT * FROM c1 WHERE a=1 AND c=2 } \ + {SEARCH TABLE c1 USING INDEX c1cd (c=?)} + + do_eqp_test 3.1.2 { SELECT * FROM c1 WHERE a=1 AND b>'d' AND c=2 } \ + {SEARCH TABLE c1 USING INDEX c1cd (c=?)} + + do_eqp_test 3.1.3 { SELECT * FROM c1 WHERE a=1 AND b>'l' AND c=2 } \ + {SEARCH TABLE c1 USING INDEX c1ab (a=? AND b>?)} + + do_eqp_test 3.2.1 { SELECT * FROM c1 WHERE a=1 AND c>1 } \ + {SEARCH TABLE c1 USING INDEX c1cd (c>?)} + + do_eqp_test 3.2.2 { SELECT * FROM c1 WHERE a=1 AND c>0 } \ + {SEARCH TABLE c1 USING INDEX c1ab (a=?)} + + do_eqp_test 3.2.3 { SELECT * FROM c1 WHERE a=1 AND c>=1 } \ + {SEARCH TABLE c1 USING INDEX c1ab (a=?)} + + do_eqp_test 3.2.4 { SELECT * FROM c1 WHERE a=1 AND (c, d)>(1, 'c') } \ + {SEARCH TABLE c1 USING INDEX c1ab (a=?)} + + do_eqp_test 3.2.5 { SELECT * FROM c1 WHERE a=1 AND (c, d)>(1, 'o') } \ + {SEARCH TABLE c1 USING INDEX c1cd ((c,d)>(?,?))} + + do_eqp_test 3.2.6 { SELECT * FROM c1 WHERE a=1 AND (c, +b)>(1, 'c') } \ + {SEARCH TABLE c1 USING INDEX c1ab (a=?)} - do_eqp_test 3.2.1 { SELECT * FROM c1 WHERE a=1 AND c>1 } { - 0 0 0 {SEARCH TABLE c1 USING INDEX c1cd (c>?)} - } - do_eqp_test 3.2.2 { SELECT * FROM c1 WHERE a=1 AND c>0 } { - 0 0 0 {SEARCH TABLE c1 USING INDEX c1ab (a=?)} - } - do_eqp_test 3.2.3 { SELECT * FROM c1 WHERE a=1 AND c>=1 } { - 0 0 0 {SEARCH TABLE c1 USING INDEX c1ab (a=?)} - } - do_eqp_test 3.2.4 { SELECT * FROM c1 WHERE a=1 AND (c, d)>(1, 'c') } { - 0 0 0 {SEARCH TABLE c1 USING INDEX c1ab (a=?)} - } - do_eqp_test 3.2.5 { SELECT * FROM c1 WHERE a=1 AND (c, d)>(1, 'o') } { - 0 0 0 {SEARCH TABLE c1 USING INDEX c1cd ((c,d)>(?,?))} - } - do_eqp_test 3.2.6 { SELECT * FROM c1 WHERE a=1 AND (c, +b)>(1, 'c') } { - 0 0 0 {SEARCH TABLE c1 USING INDEX c1ab (a=?)} - } } #------------------------------------------------------------------------ @@ -234,11 +233,12 @@ do_eqp_test 5.1 { (a, b) IN (SELECT x, y FROM d1) AND (c) IN (SELECT y FROM d1) } { - 0 0 0 {SEARCH TABLE d2 USING INDEX d2ab (a=? AND b=?)} - 0 0 0 {EXECUTE LIST SUBQUERY 1} - 1 0 0 {SCAN TABLE d1} - 0 0 0 {EXECUTE LIST SUBQUERY 2} - 2 0 0 {SCAN TABLE d1} + QUERY PLAN + |--SEARCH TABLE d2 USING INDEX d2ab (a=? AND b=?) + |--LIST SUBQUERY + | `--SCAN TABLE d1 + `--LIST SUBQUERY + `--SCAN TABLE d1 } do_execsql_test 6.0 { @@ -249,31 +249,23 @@ do_execsql_test 6.0 { do_eqp_test 6.1 { SELECT * FROM e1 WHERE (a, b) > (?, ?) -} { - 0 0 0 {SEARCH TABLE e1 USING INDEX e1ab ((a,b)>(?,?))} -} +} {SEARCH TABLE e1 USING INDEX e1ab ((a,b)>(?,?))} + do_eqp_test 6.2 { SELECT * FROM e1 WHERE (a, b) < (?, ?) -} { - 0 0 0 {SEARCH TABLE e1 USING INDEX e1ab ((a,b)<(?,?))} -} +} {SEARCH TABLE e1 USING INDEX e1ab ((a,b)<(?,?))} + do_eqp_test 6.3 { SELECT * FROM e1 WHERE c = ? AND (d, e) > (?, ?) -} { - 0 0 0 {SEARCH TABLE e1 USING INDEX e1cde (c=? AND (d,e)>(?,?))} -} +} {SEARCH TABLE e1 USING INDEX e1cde (c=? AND (d,e)>(?,?))} + do_eqp_test 6.4 { SELECT * FROM e1 WHERE c = ? AND (d, e) < (?, ?) -} { - 0 0 0 {SEARCH TABLE e1 USING INDEX e1cde (c=? AND (d,e)<(?,?))} -} +} {SEARCH TABLE e1 USING INDEX e1cde (c=? AND (d,e)<(?,?))} do_eqp_test 6.5 { SELECT * FROM e1 WHERE (d, e) BETWEEN (?, ?) AND (?, ?) AND c = ? -} { - 0 0 0 - {SEARCH TABLE e1 USING INDEX e1cde (c=? AND (d,e)>(?,?) AND (d,e)<(?,?))} -} +} {SEARCH TABLE e1 USING INDEX e1cde (c=? AND (d,e)>(?,?) AND (d,e)<(?,?))} #------------------------------------------------------------------------- diff --git a/test/scanstatus.test b/test/scanstatus.test index fdd6476ae..778a0c911 100644 --- a/test/scanstatus.test +++ b/test/scanstatus.test @@ -328,7 +328,7 @@ do_scanstatus_test 5.2.2 { do_eqp_test 5.3.1 { SELECT count(*) FROM t2 WHERE y = 'j'; -} {0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}} +} {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)} do_execsql_test 5.3.2 { SELECT count(*) FROM t2 WHERE y = 'j'; } {19} @@ -340,8 +340,9 @@ do_scanstatus_test 5.3.3 { do_eqp_test 5.4.1 { SELECT count(*) FROM t1, t2 WHERE y = c; } { - 0 0 0 {SCAN TABLE t1 USING COVERING INDEX t1bc} - 0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)} + QUERY PLAN + |--SCAN TABLE t1 USING COVERING INDEX t1bc + `--SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?) } do_execsql_test 5.4.2 { SELECT count(*) FROM t1, t2 WHERE y = c; @@ -356,8 +357,9 @@ do_scanstatus_test 5.4.3 { do_eqp_test 5.5.1 { SELECT count(*) FROM t1, t3 WHERE y = c; } { - 0 0 1 {SCAN TABLE t3} - 0 1 0 {SEARCH TABLE t1 USING AUTOMATIC COVERING INDEX (c=?)} + QUERY PLAN + |--SCAN TABLE t3 + `--SEARCH TABLE t1 USING AUTOMATIC COVERING INDEX (c=?) } do_execsql_test 5.5.2 { SELECT count(*) FROM t1, t3 WHERE y = c; diff --git a/test/selectA.test b/test/selectA.test index 78d04be15..838e5f432 100644 --- a/test/selectA.test +++ b/test/selectA.test @@ -1336,11 +1336,14 @@ do_eqp_test 4.1.2 { SELECT a, b FROM t4 WHERE f()==f() ORDER BY 1,2 } { - 1 0 0 {SCAN TABLE t5 USING INDEX i2} - 1 0 0 {USE TEMP B-TREE FOR RIGHT PART OF ORDER BY} - 2 0 0 {SCAN TABLE t4 USING INDEX i1} - 2 0 0 {USE TEMP B-TREE FOR RIGHT PART OF ORDER BY} - 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)} + QUERY PLAN + `--MERGE (UNION ALL) + |--LEFT + | |--SCAN TABLE t5 USING INDEX i2 + | `--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY + `--RIGHT + |--SCAN TABLE t4 USING INDEX i1 + `--USE TEMP B-TREE FOR RIGHT PART OF ORDER BY } do_execsql_test 4.1.3 { diff --git a/test/skipscan2.test b/test/skipscan2.test index a42ff2d05..df526b1dd 100644 --- a/test/skipscan2.test +++ b/test/skipscan2.test @@ -199,7 +199,7 @@ do_test skipscan2-3.2 { } {} do_eqp_test skipscan2-3.3eqp { SELECT * FROM t3 WHERE b=42; -} {0 0 0 {SEARCH TABLE t3 USING PRIMARY KEY (ANY(a) AND b=?)}} +} {SEARCH TABLE t3 USING PRIMARY KEY (ANY(a) AND b=?)} finish_test diff --git a/test/skipscan6.test b/test/skipscan6.test index 026c4d7b0..a53be1d95 100644 --- a/test/skipscan6.test +++ b/test/skipscan6.test @@ -179,22 +179,13 @@ do_execsql_test 3.0 { # do_eqp_test 3.1 { SELECT * FROM t3 WHERE a = ? AND c = ? -} { - 0 0 0 {SEARCH TABLE t3 USING INDEX t3_a (a=?)} -} +} {SEARCH TABLE t3 USING INDEX t3_a (a=?)} # The same query on table t2. This should use index "t2_a", for the # same reason. At one point though, it was mistakenly using a skip-scan. # do_eqp_test 3.2 { SELECT * FROM t2 WHERE a = ? AND c = ? -} { - 0 0 0 {SEARCH TABLE t2 USING INDEX t2_a (a=?)} -} - -finish_test - - - +} {SEARCH TABLE t2 USING INDEX t2_a (a=?)} finish_test diff --git a/test/tkt-385a5b56b9.test b/test/tkt-385a5b56b9.test index 1338435ed..22a9b38da 100644 --- a/test/tkt-385a5b56b9.test +++ b/test/tkt-385a5b56b9.test @@ -34,20 +34,17 @@ do_execsql_test 2.0 { CREATE UNIQUE INDEX t2y ON t2(y); } -do_eqp_test 2.1 { SELECT DISTINCT x FROM t2 } { - 0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2x} -} +do_eqp_test 2.1 { SELECT DISTINCT x FROM t2 } \ + {SCAN TABLE t2 USING COVERING INDEX t2x} -do_eqp_test 2.2 { SELECT DISTINCT y FROM t2 } { - 0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2y} -} +do_eqp_test 2.2 { SELECT DISTINCT y FROM t2 } \ + {SCAN TABLE t2 USING COVERING INDEX t2y} -do_eqp_test 2.3 { SELECT DISTINCT x, y FROM t2 WHERE y=10 } { - 0 0 0 {SEARCH TABLE t2 USING INDEX t2y (y=?)} -} +do_eqp_test 2.3 { SELECT DISTINCT x, y FROM t2 WHERE y=10 } \ + {SEARCH TABLE t2 USING INDEX t2y (y=?)} + +do_eqp_test 2.4 { SELECT DISTINCT x, y FROM t2 WHERE x=10 } \ + {SEARCH TABLE t2 USING INDEX t2x (x=?)} -do_eqp_test 2.4 { SELECT DISTINCT x, y FROM t2 WHERE x=10 } { - 0 0 0 {SEARCH TABLE t2 USING INDEX t2x (x=?)} -} finish_test diff --git a/test/tkt-b75a9ca6b0.test b/test/tkt-b75a9ca6b0.test index 0c81a534d..8fceb436c 100644 --- a/test/tkt-b75a9ca6b0.test +++ b/test/tkt-b75a9ca6b0.test @@ -32,41 +32,41 @@ do_execsql_test 1.1 { CREATE INDEX i1 ON t1(x, y); } -set idxscan {0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1}} -set tblscan {0 0 0 {SCAN TABLE t1}} -set grpsort {0 0 0 {USE TEMP B-TREE FOR GROUP BY}} -set sort {0 0 0 {USE TEMP B-TREE FOR ORDER BY}} +set idxscan {SCAN TABLE t1 USING COVERING INDEX i1} +set tblscan {SCAN TABLE t1} +set grpsort {USE TEMP B-TREE FOR GROUP BY} +set sort {USE TEMP B-TREE FOR ORDER BY} foreach {tn q res eqp} [subst -nocommands { 1 "SELECT * FROM t1 GROUP BY x, y ORDER BY x,y" {1 3 2 2 3 1} {$idxscan} 2 "SELECT * FROM t1 GROUP BY x, y ORDER BY x" - {1 3 2 2 3 1} {$idxscan $sort} + {1 3 2 2 3 1} {$idxscan*$sort} 3 "SELECT * FROM t1 GROUP BY y, x ORDER BY y, x" - {3 1 2 2 1 3} {$idxscan $sort} + {3 1 2 2 1 3} {$idxscan*$sort} 4 "SELECT * FROM t1 GROUP BY x ORDER BY x" {1 3 2 2 3 1} {$idxscan} 5 "SELECT * FROM t1 GROUP BY y ORDER BY y" - {3 1 2 2 1 3} {$tblscan $grpsort} + {3 1 2 2 1 3} {$tblscan*$grpsort} 6 "SELECT * FROM t1 GROUP BY y ORDER BY x" - {1 3 2 2 3 1} {$tblscan $grpsort $sort} + {1 3 2 2 3 1} {$tblscan*$grpsort*$sort} 7 "SELECT * FROM t1 GROUP BY x, y ORDER BY x, y DESC" - {1 3 2 2 3 1} {$idxscan $sort} + {1 3 2 2 3 1} {$idxscan*$sort} 8 "SELECT * FROM t1 GROUP BY x, y ORDER BY x DESC, y DESC" - {3 1 2 2 1 3} {$idxscan $sort} + {3 1 2 2 1 3} {$idxscan*$sort} 9 "SELECT * FROM t1 GROUP BY x, y ORDER BY x ASC, y ASC" {1 3 2 2 3 1} {$idxscan} 10 "SELECT * FROM t1 GROUP BY x, y ORDER BY x COLLATE nocase, y" - {1 3 2 2 3 1} {$idxscan $sort} + {1 3 2 2 3 1} {$idxscan*$sort} }] { do_execsql_test 1.$tn.1 $q $res diff --git a/test/tpch01.test b/test/tpch01.test index ce48f8ec2..0e51b82b0 100644 --- a/test/tpch01.test +++ b/test/tpch01.test @@ -165,7 +165,7 @@ do_test tpch01-1.1 { order by o_year;}] set ::eqpres -} {/0 0 0 {SEARCH TABLE part USING INDEX bootleg_pti .P_TYPE=..} 0 1 2 {SEARCH TABLE lineitem USING INDEX lpki2 .L_PARTKEY=..}.*/} +} {/*SEARCH TABLE part USING INDEX bootleg_pti *SEARCH TABLE lineitem USING INDEX lpki2*/} do_test tpch01-1.1b { set ::eqpres } {/.* customer .* nation AS n1 .*/} @@ -187,6 +187,14 @@ group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc; -} {0 0 1 {SEARCH TABLE orders USING INDEX odi (O_ORDERDATE>? AND O_ORDERDATE<?)} 0 1 0 {SEARCH TABLE customer USING INDEX cpki (C_CUSTKEY=?)} 0 2 3 {SEARCH TABLE nation USING INDEX npki (N_NATIONKEY=?)} 0 3 2 {SEARCH TABLE lineitem USING INDEX lpki (L_ORDERKEY=?)} 0 0 0 {USE TEMP B-TREE FOR GROUP BY} 0 0 0 {USE TEMP B-TREE FOR ORDER BY}} +} { + QUERY PLAN + |--SEARCH TABLE orders USING INDEX odi (O_ORDERDATE>? AND O_ORDERDATE<?) + |--SEARCH TABLE customer USING INDEX cpki (C_CUSTKEY=?) + |--SEARCH TABLE nation USING INDEX npki (N_NATIONKEY=?) + |--SEARCH TABLE lineitem USING INDEX lpki (L_ORDERKEY=?) + |--USE TEMP B-TREE FOR GROUP BY + `--USE TEMP B-TREE FOR ORDER BY +} finish_test diff --git a/test/unordered.test b/test/unordered.test index 147e91f0d..cdbbabeb3 100644 --- a/test/unordered.test +++ b/test/unordered.test @@ -40,28 +40,27 @@ foreach idxmode {ordered unordered} { sqlite3 db test.db foreach {tn sql r(ordered) r(unordered)} { 1 "SELECT * FROM t1 ORDER BY a" - {0 0 0 {SCAN TABLE t1 USING INDEX i1}} - {0 0 0 {SCAN TABLE t1} 0 0 0 {USE TEMP B-TREE FOR ORDER BY}} + {SCAN TABLE t1 USING INDEX i1} + {SCAN TABLE t1*USE TEMP B-TREE FOR ORDER BY} 2 "SELECT * FROM t1 WHERE a > 100" - {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?)}} - {0 0 0 {SCAN TABLE t1}} + {SEARCH TABLE t1 USING INDEX i1 (a>?)} + {SCAN TABLE t1} 3 "SELECT * FROM t1 WHERE a = ? ORDER BY rowid" - {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}} - {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)} - 0 0 0 {USE TEMP B-TREE FOR ORDER BY}} + {SEARCH TABLE t1 USING INDEX i1 (a=?)} + {SEARCH TABLE t1 USING INDEX i1 (a=?)*USE TEMP B-TREE FOR ORDER BY} 4 "SELECT max(a) FROM t1" - {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1}} - {0 0 0 {SEARCH TABLE t1}} + {SEARCH TABLE t1 USING COVERING INDEX i1} + {SEARCH TABLE t1} 5 "SELECT group_concat(b) FROM t1 GROUP BY a" - {0 0 0 {SCAN TABLE t1 USING INDEX i1}} - {0 0 0 {SCAN TABLE t1} 0 0 0 {USE TEMP B-TREE FOR GROUP BY}} + {SCAN TABLE t1 USING INDEX i1} + {SCAN TABLE t1*USE TEMP B-TREE FOR GROUP BY} 6 "SELECT * FROM t1 WHERE a = ?" - {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}} - {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}} + {SEARCH TABLE t1 USING INDEX i1 (a=?)} + {SEARCH TABLE t1 USING INDEX i1 (a=?)} 7 "SELECT count(*) FROM t1" - {0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1}} - {0 0 0 {SCAN TABLE t1}} + {SCAN TABLE t1 USING COVERING INDEX i1} + {SCAN TABLE t1} } { do_eqp_test 1.$idxmode.$tn $sql $r($idxmode) } diff --git a/test/where7.test b/test/where7.test index 00cf5eb27..983418ae2 100644 --- a/test/where7.test +++ b/test/where7.test @@ -23341,8 +23341,8 @@ do_execsql_test where7-3.1 { CREATE INDEX t302_c3 on t302(c3); CREATE INDEX t302_c8_c3 on t302(c8, c3); CREATE INDEX t302_c5 on t302(c5); - - EXPLAIN QUERY PLAN +} +do_eqp_test where7-3.2 { SELECT t302.c1 FROM t302 JOIN t301 ON t302.c8 = +t301.c8 WHERE t302.c2 = 19571 @@ -23351,10 +23351,11 @@ do_execsql_test where7-3.1 { OR t301.c8 = 1407424651264000) ORDER BY t302.c5 LIMIT 200; } { - 0 0 1 {SEARCH TABLE t301 USING COVERING INDEX t301_c4 (c4=?)} - 0 0 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?)} - 0 1 0 {SEARCH TABLE t302 USING INDEX t302_c8_c3 (c8=? AND c3>?)} - 0 0 0 {USE TEMP B-TREE FOR ORDER BY} + QUERY PLAN + |--SEARCH TABLE t301 USING COVERING INDEX t301_c4 (c4=?) + |--SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) + |--SEARCH TABLE t302 USING INDEX t302_c8_c3 (c8=? AND c3>?) + `--USE TEMP B-TREE FOR ORDER BY } finish_test diff --git a/test/where9.test b/test/where9.test index d073074d4..c04c17fa9 100644 --- a/test/where9.test +++ b/test/where9.test @@ -357,25 +357,25 @@ do_test where9-2.8 { ifcapable explain { - do_execsql_test where9-3.1 { - EXPLAIN QUERY PLAN + do_eqp_test where9-3.1 { SELECT t2.a FROM t1, t2 WHERE t1.a=80 AND ((t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f) - } { - 0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)} - 0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?)} - 0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?)} - } - do_execsql_test where9-3.2 { - EXPLAIN QUERY PLAN + } [string map {"\n " \n} { + QUERY PLAN + |--SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) + |--SEARCH TABLE t2 USING INDEX t2d (d=?) + `--SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) + }] + do_eqp_test where9-3.2 { SELECT coalesce(t2.a,9999) FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f WHERE t1.a=80 - } { - 0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)} - 0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?)} - 0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?)} - } + } [string map {"\n " \n} { + QUERY PLAN + |--SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) + |--SEARCH TABLE t2 USING INDEX t2d (d=?) + `--SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) + }] } # Make sure that INDEXED BY and multi-index OR clauses play well with @@ -446,35 +446,30 @@ do_test where9-4.8 { } } {1 {no query solution}} -ifcapable explain { - # The (c=31031 OR d IS NULL) clause is preferred over b>1000 because - # the former is an equality test which is expected to return fewer rows. - # - do_execsql_test where9-5.1 { - EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c=31031 OR d IS NULL) - } { - 0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c=?)} - 0 0 0 {SEARCH TABLE t1 USING INDEX t1d (d=?)} - } - - # In contrast, b=1000 is preferred over any OR-clause. - # - do_execsql_test where9-5.2 { - EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b=1000 AND (c=31031 OR d IS NULL) - } { - 0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?)} - } - - # Likewise, inequalities in an AND are preferred over inequalities in - # an OR. - # - do_execsql_test where9-5.3 { - EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c>=31031 OR d IS NULL) - } { - 0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>?)} - } +# The (c=31031 OR d IS NULL) clause is preferred over b>1000 because +# the former is an equality test which is expected to return fewer rows. +# +do_eqp_test where9-5.1 { + SELECT a FROM t1 WHERE b>1000 AND (c=31031 OR d IS NULL) +} { + QUERY PLAN + |--SEARCH TABLE t1 USING INDEX t1c (c=?) + `--SEARCH TABLE t1 USING INDEX t1d (d=?) } +# In contrast, b=1000 is preferred over any OR-clause. +# +do_eqp_test where9-5.2 { + SELECT a FROM t1 WHERE b=1000 AND (c=31031 OR d IS NULL) +} {SEARCH TABLE t1 USING INDEX t1b (b=?)} + +# Likewise, inequalities in an AND are preferred over inequalities in +# an OR. +# +do_eqp_test where9-5.3 { + SELECT a FROM t1 WHERE b>1000 AND (c>=31031 OR d IS NULL) +} {SEARCH TABLE t1 USING INDEX t1b (b>?)} + ############################################################################ # Make sure OR-clauses work correctly on UPDATE and DELETE statements. diff --git a/test/whereG.test b/test/whereG.test index 110ed5dbd..d2e6a4ee9 100644 --- a/test/whereG.test +++ b/test/whereG.test @@ -66,7 +66,7 @@ do_eqp_test whereG-1.1 { WHERE unlikely(cname LIKE '%bach%') AND composer.cid=track.cid AND album.aid=track.aid; -} {/.*composer.*track.*album.*/} +} {composer*track*album} do_execsql_test whereG-1.2 { SELECT DISTINCT aname FROM album, composer, track @@ -195,13 +195,13 @@ do_execsql_test 5.1 { } do_eqp_test 5.1.2 { SELECT * FROM t1 WHERE a>? -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?)}} +} {SEARCH TABLE t1 USING INDEX i1 (a>?)} do_eqp_test 5.1.3 { SELECT * FROM t1 WHERE likelihood(a>?, 0.9) -} {0 0 0 {SCAN TABLE t1}} +} {SCAN TABLE t1} do_eqp_test 5.1.4 { SELECT * FROM t1 WHERE likely(a>?) -} {0 0 0 {SCAN TABLE t1}} +} {SCAN TABLE t1} do_test 5.2 { for {set i 0} {$i < 100} {incr i} { @@ -212,23 +212,23 @@ do_test 5.2 { } {} do_eqp_test 5.2.2 { SELECT * FROM t1 WHERE likelihood(b>?, 0.01) -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (ANY(a) AND b>?)}} +} {SEARCH TABLE t1 USING INDEX i1 (ANY(a) AND b>?)} do_eqp_test 5.2.3 { SELECT * FROM t1 WHERE likelihood(b>?, 0.9) -} {0 0 0 {SCAN TABLE t1}} +} {SCAN TABLE t1} do_eqp_test 5.2.4 { SELECT * FROM t1 WHERE likely(b>?) -} {0 0 0 {SCAN TABLE t1}} +} {SCAN TABLE t1} do_eqp_test 5.3.1 { SELECT * FROM t1 WHERE a=? -} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)}} +} {SEARCH TABLE t1 USING INDEX i1 (a=?)} do_eqp_test 5.3.2 { SELECT * FROM t1 WHERE likelihood(a=?, 0.9) -} {0 0 0 {SCAN TABLE t1}} +} {SCAN TABLE t1} do_eqp_test 5.3.3 { SELECT * FROM t1 WHERE likely(a=?) -} {0 0 0 {SCAN TABLE t1}} +} {SCAN TABLE t1} # 2015-06-18 # Ticket [https://www.sqlite.org/see/tktview/472f0742a1868fb58862bc588ed70] diff --git a/test/whereI.test b/test/whereI.test index 29b08549b..73dd9009a 100644 --- a/test/whereI.test +++ b/test/whereI.test @@ -29,8 +29,9 @@ do_execsql_test 1.0 { do_eqp_test 1.1 { SELECT a FROM t1 WHERE b='b' OR c='x' } { - 0 0 0 {SEARCH TABLE t1 USING INDEX i1 (b=?)} - 0 0 0 {SEARCH TABLE t1 USING INDEX i2 (c=?)} + QUERY PLAN + |--SEARCH TABLE t1 USING INDEX i1 (b=?) + `--SEARCH TABLE t1 USING INDEX i2 (c=?) } do_execsql_test 1.2 { @@ -57,8 +58,9 @@ do_execsql_test 2.0 { do_eqp_test 2.1 { SELECT a FROM t2 WHERE b='b' OR c='x' } { - 0 0 0 {SEARCH TABLE t2 USING INDEX i3 (b=?)} - 0 0 0 {SEARCH TABLE t2 USING INDEX i4 (c=?)} + QUERY PLAN + |--SEARCH TABLE t2 USING INDEX i3 (b=?) + `--SEARCH TABLE t2 USING INDEX i4 (c=?) } do_execsql_test 2.2 { diff --git a/test/whereJ.test b/test/whereJ.test index 48924d0fc..af6ffafb3 100644 --- a/test/whereJ.test +++ b/test/whereJ.test @@ -402,9 +402,7 @@ do_eqp_test 3.4 { a = 4 AND b BETWEEN 20 AND 80 -- Matches 80 rows AND c BETWEEN 150 AND 160 -- Matches 10 rows -} { - 0 0 0 {SEARCH TABLE t1 USING INDEX idx_c (c>? AND c<?)} -} +} {SEARCH TABLE t1 USING INDEX idx_c (c>? AND c<?)} # This one should use index "idx_ab". do_eqp_test 3.5 { @@ -412,9 +410,7 @@ do_eqp_test 3.5 { a = 5 AND b BETWEEN 20 AND 80 -- Matches 1 row AND c BETWEEN 150 AND 160 -- Matches 10 rows -} { - 0 0 0 {SEARCH TABLE t1 USING INDEX idx_ab (a=? AND b>? AND b<?)} -} +} {SEARCH TABLE t1 USING INDEX idx_ab (a=? AND b>? AND b<?)} ########################################################################################### diff --git a/test/with1.test b/test/with1.test index 92fbc58fc..f1a062504 100644 --- a/test/with1.test +++ b/test/with1.test @@ -992,16 +992,22 @@ do_execsql_test 18.2 { # EXPLAIN QUERY PLAN on a self-join of a CTE # -do_execsql_test 19.1 { +do_execsql_test 19.1a { DROP TABLE IF EXISTS t1; CREATE TABLE t1(x); - EXPLAIN QUERY PLAN +} +do_eqp_test 19.1b { WITH x1(a) AS (values(100)) INSERT INTO t1(x) SELECT * FROM (WITH x2(y) AS (SELECT * FROM x1) SELECT y+a FROM x1, x2); SELECT * FROM t1; -} {0 0 0 {SCAN SUBQUERY 1} 0 1 1 {SCAN SUBQUERY 1}} +} { + QUERY PLAN + |--MATERIALIZE xxxxxx + |--SCAN SUBQUERY xxxxxx + `--SCAN SUBQUERY xxxxxx +} # 2017-10-28. # See check-in https://sqlite.org/src/info/0926df095faf72c2 diff --git a/test/with3.test b/test/with3.test index 5540a7fca..d13544cfa 100644 --- a/test/with3.test +++ b/test/with3.test @@ -79,22 +79,28 @@ ifcapable analyze { do_eqp_test 3.1.2 { WITH cnt(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM cnt LIMIT 1) SELECT * FROM cnt, y1 WHERE i=a - } { - 3 0 0 {SCAN TABLE cnt} - 1 0 0 {COMPOUND SUBQUERIES 0 AND 0 (UNION ALL)} - 0 0 0 {SCAN SUBQUERY 1} - 0 1 1 {SEARCH TABLE y1 USING INDEX y1a (a=?)} - } + } [string map {"\n " \n} { + QUERY PLAN + |--MATERIALIZE xxxxxx + | |--SETUP + | `--RECURSIVE STEP + | `--SCAN TABLE cnt + |--SCAN SUBQUERY xxxxxx + `--SEARCH TABLE y1 USING INDEX y1a (a=?) + }] do_eqp_test 3.1.3 { WITH cnt(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM cnt LIMIT 1000000) SELECT * FROM cnt, y1 WHERE i=a - } { - 3 0 0 {SCAN TABLE cnt} - 1 0 0 {COMPOUND SUBQUERIES 0 AND 0 (UNION ALL)} - 0 0 1 {SCAN TABLE y1} - 0 1 0 {SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX (i=?)} - } + } [string map {"\n " \n} { + QUERY PLAN + |--MATERIALIZE xxxxxx + | |--SETUP + | `--RECURSIVE STEP + | `--SCAN TABLE cnt + |--SCAN TABLE y1 + `--SEARCH SUBQUERY xxxxxx USING AUTOMATIC COVERING INDEX (i=?) + }] } do_execsql_test 3.2.1 { @@ -108,13 +114,17 @@ do_eqp_test 3.2.2 { SELECT * FROM c, w2, w1 WHERE c.id=w2.pk AND c.id=w1.pk; } { - 2 0 0 {EXECUTE SCALAR SUBQUERY 3} - 3 0 0 {SCAN TABLE w2} - 4 0 0 {SCAN TABLE w1} - 4 1 1 {SCAN TABLE c} - 1 0 0 {COMPOUND SUBQUERIES 0 AND 0 (UNION ALL)} 0 0 0 {SCAN SUBQUERY 1} - 0 1 1 {SEARCH TABLE w2 USING INTEGER PRIMARY KEY (rowid=?)} - 0 2 2 {SEARCH TABLE w1 USING INTEGER PRIMARY KEY (rowid=?)} + QUERY PLAN + |--MATERIALIZE xxxxxx + | |--SETUP + | | `--SCALAR SUBQUERY + | | `--SCAN TABLE w2 + | `--RECURSIVE STEP + | |--SCAN TABLE w1 + | `--SCAN TABLE c + |--SCAN SUBQUERY xxxxxx + |--SEARCH TABLE w2 USING INTEGER PRIMARY KEY (rowid=?) + `--SEARCH TABLE w1 USING INTEGER PRIMARY KEY (rowid=?) } finish_test diff --git a/test/without_rowid1.test b/test/without_rowid1.test index 644c3da94..d794420ef 100644 --- a/test/without_rowid1.test +++ b/test/without_rowid1.test @@ -238,7 +238,7 @@ do_execsql_test 5.0 { do_eqp_test 5.1 { SELECT * FROM t45 WHERE b=? AND a>? -} {/*USING INDEX i45 (b=? AND a>?)*/} +} {USING INDEX i45 (b=? AND a>?)} do_execsql_test 5.2 { SELECT * FROM t45 WHERE b='two' AND a>4 @@ -257,11 +257,11 @@ do_execsql_test 5.4 { } set queries { - 1 2 "c = 5 AND a = 1" {/*i46 (c=? AND a=?)*/} - 2 6 "c = 4 AND a < 3" {/*i46 (c=? AND a<?)*/} - 3 4 "c = 2 AND a >= 3" {/*i46 (c=? AND a>?)*/} - 4 1 "c = 2 AND a = 1 AND b<10" {/*i46 (c=? AND a=? AND b<?)*/} - 5 1 "c = 0 AND a = 0 AND b>5" {/*i46 (c=? AND a=? AND b>?)*/} + 1 2 "c = 5 AND a = 1" {i46 (c=? AND a=?)} + 2 6 "c = 4 AND a < 3" {i46 (c=? AND a<?)} + 3 4 "c = 2 AND a >= 3" {i46 (c=? AND a>?)} + 4 1 "c = 2 AND a = 1 AND b<10" {i46 (c=? AND a=? AND b<?)} + 5 1 "c = 0 AND a = 0 AND b>5" {i46 (c=? AND a=? AND b>?)} } foreach {tn cnt where eqp} $queries { |