Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -3219,34 +3219,38 @@ Vdbe *v = pParse->pVdbe; /* VM being constructed */ sqlite3 *db = pParse->db; /* Database handle */ char *zMsg; /* Text to add to EQP output */ sqlite3_int64 nRow; /* Expected number of rows visited by scan */ int iId = pParse->iSelectId; /* Select id (left-most output column) */ + int isSearch; /* True for a SEARCH. False for SCAN. */ if( (flags&WHERE_MULTI_OR) || (wctrlFlags&WHERE_ONETABLE_ONLY) ) return; + isSearch = (pLevel->plan.nEq>0 || flags&(WHERE_BTM_LIMIT|WHERE_TOP_LIMIT)); + + zMsg = sqlite3MPrintf(db, "%s", isSearch?"SEARCH":"SCAN"); if( pItem->pSelect ){ - zMsg = sqlite3MPrintf(db, "SCAN SUBQUERY %d", pItem->iSelectId); + zMsg = sqlite3MAppendf(db, zMsg, "%s SUBQUERY %d", zMsg,pItem->iSelectId); }else{ - zMsg = sqlite3MPrintf(db, "SCAN TABLE %s", pItem->zName); + zMsg = sqlite3MAppendf(db, zMsg, "%s TABLE %s", zMsg, pItem->zName); } if( pItem->zAlias ){ zMsg = sqlite3MAppendf(db, zMsg, "%s AS %s", zMsg, pItem->zAlias); } if( (flags & WHERE_INDEXED)!=0 ){ char *zWhere = explainIndexRange(db, pLevel, pItem->pTab); - zMsg = sqlite3MAppendf(db, zMsg, "%s BY %s%sINDEX%s%s%s", zMsg, + zMsg = sqlite3MAppendf(db, zMsg, "%s USING %s%sINDEX%s%s%s", zMsg, ((flags & WHERE_TEMP_INDEX)?"AUTOMATIC ":""), ((flags & WHERE_IDX_ONLY)?"COVERING ":""), ((flags & WHERE_TEMP_INDEX)?"":" "), ((flags & WHERE_TEMP_INDEX)?"": pLevel->plan.u.pIdx->zName), zWhere ); sqlite3DbFree(db, zWhere); }else if( flags & (WHERE_ROWID_EQ|WHERE_ROWID_RANGE) ){ - zMsg = sqlite3MAppendf(db, zMsg, "%s BY INTEGER PRIMARY KEY", zMsg); + zMsg = sqlite3MAppendf(db, zMsg, "%s USING INTEGER PRIMARY KEY", zMsg); if( flags&WHERE_ROWID_EQ ){ zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid=?)", zMsg); }else if( flags&WHERE_BTM_LIMIT && flags&WHERE_TOP_LIMIT ){ zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid>? AND rowid? AND owner_change_date? AND owner_change_date10" - {0 0 0 {SCAN TABLE t2 BY INDEX sqlite_autoindex_t2_1 (b=? AND c>?) (~3 rows)}} + {0 0 0 {SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (b=? AND c>?) (~3 rows)}} } # EVIDENCE-OF: R-45493-35653 A CHECK constraint may be attached to a # column definition or specified as a table constraint. In practice it # makes no difference. Index: test/e_fkey.test ================================================================== --- test/e_fkey.test +++ test/e_fkey.test @@ -1097,18 +1097,18 @@ } {} do_execsql_test e_fkey-27.3 { EXPLAIN QUERY PLAN UPDATE artist SET artistid = ?, artistname = ? } { 0 0 0 {SCAN TABLE artist (~1000000 rows)} - 0 0 0 {SCAN TABLE track BY COVERING INDEX trackindex (trackartist=?) (~10 rows)} - 0 0 0 {SCAN TABLE track BY COVERING INDEX trackindex (trackartist=?) (~10 rows)} + 0 0 0 {SEARCH TABLE track USING COVERING INDEX trackindex (trackartist=?) (~10 rows)} + 0 0 0 {SEARCH TABLE track USING COVERING INDEX trackindex (trackartist=?) (~10 rows)} } do_execsql_test e_fkey-27.4 { EXPLAIN QUERY PLAN DELETE FROM artist } { 0 0 0 {SCAN TABLE artist (~1000000 rows)} - 0 0 0 {SCAN TABLE track BY COVERING INDEX trackindex (trackartist=?) (~10 rows)} + 0 0 0 {SEARCH TABLE track USING COVERING INDEX trackindex (trackartist=?) (~10 rows)} } ########################################################################### ### SECTION 4.1: Composite Foreign Key Constraints Index: test/eqp.test ================================================================== --- test/eqp.test +++ test/eqp.test @@ -37,25 +37,25 @@ } do_eqp_test 1.2 { SELECT * FROM t2, t1 WHERE t1.a=1 OR t1.b=2; } { - 0 0 1 {SCAN TABLE t1 BY INDEX i1 (a=?) (~10 rows)} - 0 0 1 {SCAN TABLE t1 BY INDEX i2 (b=?) (~10 rows)} + 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 {SCAN TABLE t1 BY INDEX i1 (a=?) (~10 rows)} - 0 1 1 {SCAN TABLE t1 BY INDEX i2 (b=?) (~10 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 BY COVERING INDEX i1 (~1000000 rows)} + 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)} @@ -62,11 +62,11 @@ 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 {SCAN TABLE t1 BY COVERING INDEX i1 (a=?) (~10 rows)} + 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)} @@ -90,11 +90,11 @@ 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 BY COVERING INDEX t2i1 (~1000000 rows)} + 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)} @@ -110,26 +110,26 @@ 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 BY COVERING INDEX t2i1 (~1000000 rows)} + 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 {SCAN TABLE t2 BY COVERING INDEX t2i1 (~1 rows)} + 0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1 rows)} } det 2.3.2 "SELECT min(x) FROM t2" { - 0 0 0 {SCAN TABLE t2 BY COVERING INDEX t2i1 (~1 rows)} + 0 0 0 {SCAN 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 {SCAN TABLE t1 BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} + 0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} } #------------------------------------------------------------------------- @@ -160,11 +160,11 @@ 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 BY COVERING INDEX t2i1 (~1000000 rows)} + 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 } { @@ -179,11 +179,11 @@ (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 BY INDEX t2i1 (~1000000 rows)} + 2 0 0 {SCAN TABLE t2 USING INDEX t2i1 (~1000000 rows)} 0 0 0 {SCAN SUBQUERY 1 AS x1 (~1000000 rows)} 0 1 1 {SCAN SUBQUERY 2 AS x2 (~1000000 rows)} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } @@ -259,11 +259,11 @@ 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 BY INDEX t2i1 (~1000000 rows)} + 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 } { @@ -312,11 +312,11 @@ 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 BY COVERING INDEX t2i1 (~1000000 rows)} + 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)} } @@ -327,59 +327,58 @@ # 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-03114-52867 sqlite> CREATE INDEX i1 ON t1(a); +# 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|SCAN TABLE t1 BY INDEX i1 (a=?) (~10 rows) +# 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 {SCAN TABLE t1 BY INDEX i1 (a=?) (~10 rows)} + 0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)} } -# EVIDENCE-OF: R-20407-61322 sqlite> CREATE INDEX i2 ON t1(a, b); +# 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|SCAN TABLE t1 BY COVERING INDEX i2 (a=?) (~10 rows) +# 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 {SCAN TABLE t1 BY COVERING INDEX i2 (a=?) (~10 rows)} + 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)} } -# EVIDENCE-OF: R-01893-00096 sqlite> EXPLAIN QUERY PLAN SELECT t1.*, -# t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2; 0|0|0|SCAN TABLE t1 BY -# COVERING INDEX i2 (a=? AND b>?) (~3 rows) 0|1|1|SCAN TABLE t2 +# 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 {SCAN TABLE t1 BY COVERING INDEX i2 (a=? AND b>?) (~3 rows)} + 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) (~3 rows)} 0 1 1 {SCAN TABLE t2 (~1000000 rows)} } -# EVIDENCE-OF: R-26531-36629 sqlite> EXPLAIN QUERY PLAN SELECT t1.*, -# t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2; 0|0|1|SCAN TABLE t1 BY -# COVERING INDEX i2 (a=? AND b>?) (~3 rows) 0|1|0|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 {SCAN TABLE t1 BY COVERING INDEX i2 (a=? AND b>?) (~3 rows)} + 0 0 1 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) (~3 rows)} 0 1 0 {SCAN TABLE t2 (~1000000 rows)} } -# EVIDENCE-OF: R-17671-37431 sqlite> CREATE INDEX i3 ON t1(b); +# 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|SCAN TABLE t1 BY COVERING INDEX i2 (a=?) (~10 rows) 0|0|0|SCAN -# TABLE t1 BY INDEX i3 (b=?) (~10 rows) +# 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 {SCAN TABLE t1 BY COVERING INDEX i2 (a=?) (~10 rows)} - 0 0 0 {SCAN TABLE t1 BY INDEX i3 (b=?) (~10 rows)} + 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 @@ -386,51 +385,51 @@ 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-08354-12138 sqlite> CREATE INDEX i4 ON t2(c); +# 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 BY INDEX i4 (~1000000 rows) +# 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 BY INDEX i4 (~1000000 rows)} + 0 0 0 {SCAN TABLE t2 USING INDEX i4 (~1000000 rows)} } -# EVIDENCE-OF: R-01895-58356 sqlite> EXPLAIN QUERY PLAN SELECT +# 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|SCAN TABLE t1 BY COVERING INDEX i2 (a=?) (~10 rows) -# 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2 2|0|0|SCAN TABLE t1 BY +# 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 {SCAN TABLE t1 BY COVERING INDEX i2 (a=?) (~10 rows)} + 1 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)} 0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2} - 2 0 0 {SCAN TABLE t1 BY INDEX i3 (b=?) (~10 rows)} + 2 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)} } -# EVIDENCE-OF: R-43933-45972 sqlite> EXPLAIN QUERY PLAN SELECT +# 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 BY COVERING INDEX i2 (~1000000 rows) 0|0|0|SCAN +# 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 BY COVERING INDEX i2 (~1000000 rows)} + 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-15989-23611 sqlite> EXPLAIN QUERY PLAN SELECT * FROM -# (SELECT * FROM t2 WHERE c=1), t1; 0|0|0|SCAN TABLE t2 BY INDEX i4 +# 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 {SCAN TABLE t2 BY INDEX i4 (c=?) (~10 rows)} + 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) @@ -440,16 +439,17 @@ 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-34523-61710 sqlite> EXPLAIN QUERY PLAN SELECT a FROM -# t1 EXCEPT SELECT d FROM t2 ORDER BY 1; 1|0|0|SCAN TABLE t1 BY 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-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 BY COVERING INDEX i2 (~1000000 rows)} + 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)} } Index: test/indexedby.test ================================================================== --- test/indexedby.test +++ test/indexedby.test @@ -40,18 +40,18 @@ # 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 {SCAN TABLE t1 BY INDEX i1 (a=?) (~10 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)}} do_execsql_test indexedby-1.3 { EXPLAIN QUERY PLAN select * from t1 ; } {0 0 0 {SCAN TABLE t1 (~1000000 rows)}} do_execsql_test indexedby-1.4 { EXPLAIN QUERY PLAN select * from t1, t2 WHERE c = 10; } { - 0 0 1 {SCAN TABLE t2 BY INDEX i3 (c=?) (~10 rows)} + 0 0 1 {SEARCH TABLE t2 USING INDEX i3 (c=?) (~10 rows)} 0 1 0 {SCAN TABLE t1 (~1000000 rows)} } # Parser tests. Test that an INDEXED BY or NOT INDEX clause can be # attached to a table in the FROM clause, but not to a sub-select or @@ -87,15 +87,15 @@ EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two' } {0 0 0 {SCAN TABLE t1 (~10000 rows)}} do_execsql_test indexedby-3.2 { EXPLAIN QUERY PLAN SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two' -} {0 0 0 {SCAN TABLE t1 BY INDEX i1 (a=?) (~2 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~2 rows)}} do_execsql_test indexedby-3.3 { EXPLAIN QUERY PLAN SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two' -} {0 0 0 {SCAN TABLE t1 BY INDEX i2 (b=?) (~2 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~2 rows)}} do_test indexedby-3.4 { catchsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' } } {1 {cannot use index: i2}} do_test indexedby-3.5 { catchsql { SELECT * FROM t1 INDEXED BY i2 ORDER BY a } @@ -108,15 +108,15 @@ } {0 {}} do_execsql_test indexedby-3.8 { EXPLAIN QUERY PLAN SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 ORDER BY e -} {0 0 0 {SCAN TABLE t3 BY INDEX sqlite_autoindex_t3_1 (~1000000 rows)}} +} {0 0 0 {SCAN TABLE t3 USING INDEX sqlite_autoindex_t3_1 (~1000000 rows)}} do_execsql_test indexedby-3.9 { EXPLAIN QUERY PLAN SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE e = 10 -} {0 0 0 {SCAN TABLE t3 BY INDEX sqlite_autoindex_t3_1 (e=?) (~1 rows)}} +} {0 0 0 {SEARCH TABLE t3 USING INDEX sqlite_autoindex_t3_1 (e=?) (~1 rows)}} do_test indexedby-3.10 { catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE f = 10 } } {1 {cannot use index: sqlite_autoindex_t3_1}} do_test indexedby-3.11 { catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_2 WHERE f = 10 } @@ -126,17 +126,17 @@ # do_execsql_test indexedby-4.1 { EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE a = c } { 0 0 0 {SCAN TABLE t1 (~1000000 rows)} - 0 1 1 {SCAN TABLE t2 BY INDEX i3 (c=?) (~10 rows)} + 0 1 1 {SEARCH TABLE t2 USING INDEX i3 (c=?) (~10 rows)} } do_execsql_test indexedby-4.2 { EXPLAIN QUERY PLAN SELECT * FROM t1 INDEXED BY i1, t2 WHERE a = c } { 0 0 1 {SCAN TABLE t2 (~1000000 rows)} - 0 1 0 {SCAN TABLE t1 BY INDEX i1 (a=?) (~10 rows)} + 0 1 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)} } do_test indexedby-4.3 { catchsql { SELECT * FROM t1 INDEXED BY i1, t2 INDEXED BY i3 WHERE a=c } @@ -152,14 +152,14 @@ # a CREATE VIEW statement is dropped and recreated. # 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 {SCAN TABLE t1 BY INDEX i1 (a>?) (~330000 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?) (~330000 rows)}} do_execsql_test indexedby-5.2 { EXPLAIN QUERY PLAN SELECT * FROM v2 WHERE b = 10 -} {0 0 0 {SCAN TABLE t1 BY INDEX i1 (a>?) (~33000 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?) (~33000 rows)}} do_test indexedby-5.3 { execsql { DROP INDEX i1 } catchsql { SELECT * FROM v2 } } {1 {no such index: i1}} do_test indexedby-5.4 { @@ -176,55 +176,55 @@ # 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 {SCAN TABLE t1 BY INDEX i2 (b=?) (~10 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~10 rows)}} 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 BY INTEGER PRIMARY KEY (~100000 rows)}} +} {0 0 0 {SCAN TABLE t1 USING INTEGER PRIMARY KEY (~100000 rows)}} # 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 {SCAN TABLE t1 BY COVERING INDEX i1 (a=?) (~10 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}} do_execsql_test indexedby-7.2 { EXPLAIN QUERY PLAN DELETE FROM t1 NOT INDEXED WHERE a = 5 } {0 0 0 {SCAN TABLE t1 (~100000 rows)}} do_execsql_test indexedby-7.3 { EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5 -} {0 0 0 {SCAN TABLE t1 BY COVERING INDEX i1 (a=?) (~10 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}} do_execsql_test indexedby-7.4 { EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5 AND b = 10 -} {0 0 0 {SCAN TABLE t1 BY INDEX i1 (a=?) (~2 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~2 rows)}} do_execsql_test indexedby-7.5 { EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i2 WHERE a = 5 AND b = 10 -} {0 0 0 {SCAN TABLE t1 BY INDEX i2 (b=?) (~2 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~2 rows)}} do_test indexedby-7.6 { catchsql { DELETE FROM t1 INDEXED BY i2 WHERE a = 5} } {1 {cannot use index: i2}} # 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 {SCAN TABLE t1 BY COVERING INDEX i1 (a=?) (~10 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}} 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 (~100000 rows)}} do_execsql_test indexedby-8.3 { EXPLAIN QUERY PLAN UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 -} {0 0 0 {SCAN TABLE t1 BY COVERING INDEX i1 (a=?) (~10 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}} do_execsql_test indexedby-8.4 { EXPLAIN QUERY PLAN UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 AND b = 10 -} {0 0 0 {SCAN TABLE t1 BY INDEX i1 (a=?) (~2 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~2 rows)}} do_execsql_test indexedby-8.5 { EXPLAIN QUERY PLAN UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5 AND b = 10 -} {0 0 0 {SCAN TABLE t1 BY INDEX i2 (b=?) (~2 rows)}} +} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~2 rows)}} do_test indexedby-8.6 { catchsql { UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5} } {1 {cannot use index: i2}} # Test that bug #3560 is fixed. Index: test/tkt-78e04e52ea.test ================================================================== --- test/tkt-78e04e52ea.test +++ test/tkt-78e04e52ea.test @@ -55,14 +55,14 @@ do_test tkt-78e04-2.1 { execsql { CREATE INDEX "" ON t2(x); EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE x=5; } -} {0 0 0 {SCAN TABLE t2 BY COVERING INDEX (x=?) (~10 rows)}} +} {0 0 0 {SEARCH TABLE t2 USING COVERING INDEX (x=?) (~10 rows)}} do_test tkt-78e04-2.2 { execsql { DROP INDEX ""; EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE x=2; } } {0 0 0 {SCAN TABLE t2 (~100000 rows)}} finish_test Index: test/tkt3442.test ================================================================== --- test/tkt3442.test +++ test/tkt3442.test @@ -47,26 +47,26 @@ # and verify that the query plan is the same. # ifcapable explain { do_test tkt3442-1.2 { EQP { SELECT node FROM listhash WHERE id='5000' LIMIT 1; } - } {0 0 0 {SCAN TABLE listhash BY INDEX ididx (id=?) (~1 rows)}} + } {0 0 0 {SEARCH TABLE listhash USING INDEX ididx (id=?) (~1 rows)}} do_test tkt3442-1.3 { EQP { SELECT node FROM listhash WHERE id="5000" LIMIT 1; } - } {0 0 0 {SCAN TABLE listhash BY INDEX ididx (id=?) (~1 rows)}} + } {0 0 0 {SEARCH TABLE listhash USING INDEX ididx (id=?) (~1 rows)}} } # Some extra tests testing other permutations of 5000. # ifcapable explain { do_test tkt3442-1.4 { EQP { SELECT node FROM listhash WHERE id=5000 LIMIT 1; } - } {0 0 0 {SCAN TABLE listhash BY INDEX ididx (id=?) (~1 rows)}} + } {0 0 0 {SEARCH TABLE listhash USING INDEX ididx (id=?) (~1 rows)}} } do_test tkt3442-1.5 { catchsql { SELECT node FROM listhash WHERE id=[5000] LIMIT 1; } } {1 {no such column: 5000}} finish_test Index: test/where3.test ================================================================== --- test/where3.test +++ test/where3.test @@ -224,18 +224,18 @@ CREATE TABLE t302(x, y); ANALYZE; explain query plan SELECT * FROM t302, t301 WHERE t302.x=5 AND t301.a=t302.y; } { 0 0 0 {SCAN TABLE t302 (~0 rows)} - 0 1 1 {SCAN TABLE t301 BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} + 0 1 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} } do_execsql_test where3-3.1 { explain query plan SELECT * FROM t301, t302 WHERE t302.x=5 AND t301.a=t302.y; } { 0 0 1 {SCAN TABLE t302 (~0 rows)} - 0 1 0 {SCAN TABLE t301 BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} + 0 1 0 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} } # Verify that when there are multiple tables in a join which must be # full table scans that the query planner attempts put the table with # the fewest number of output rows as the outer loop. @@ -295,12 +295,12 @@ WHERE aaa.fk = 'constant' AND LENGTH(bbb.title) > 0 AND bbb.parent = 4 ORDER BY bbb.title COLLATE NOCASE ASC; } { - 0 0 0 {SCAN TABLE aaa BY INDEX aaa_333 (fk=?) (~10 rows)} - 0 1 1 {SCAN TABLE bbb BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} + 0 0 0 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)} + 0 1 1 {SEARCH TABLE bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } do_execsql_test where3-5.1 { EXPLAIN QUERY PLAN SELECT bbb.title AS tag_title @@ -308,12 +308,12 @@ WHERE aaa.fk = 'constant' AND LENGTH(bbb.title) > 0 AND bbb.parent = 4 ORDER BY bbb.title COLLATE NOCASE ASC; } { - 0 0 0 {SCAN TABLE aaa BY INDEX aaa_333 (fk=?) (~10 rows)} - 0 1 1 {SCAN TABLE aaa AS bbb BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} + 0 0 0 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)} + 0 1 1 {SEARCH TABLE aaa AS bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } do_execsql_test where3-5.2 { EXPLAIN QUERY PLAN SELECT bbb.title AS tag_title @@ -321,12 +321,12 @@ WHERE aaa.fk = 'constant' AND LENGTH(bbb.title) > 0 AND bbb.parent = 4 ORDER BY bbb.title COLLATE NOCASE ASC; } { - 0 0 1 {SCAN TABLE aaa BY INDEX aaa_333 (fk=?) (~10 rows)} - 0 1 0 {SCAN TABLE bbb BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} + 0 0 1 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)} + 0 1 0 {SEARCH TABLE bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } do_execsql_test where3-5.3 { EXPLAIN QUERY PLAN SELECT bbb.title AS tag_title @@ -334,12 +334,12 @@ WHERE aaa.fk = 'constant' AND LENGTH(bbb.title) > 0 AND bbb.parent = 4 ORDER BY bbb.title COLLATE NOCASE ASC; } { - 0 0 1 {SCAN TABLE aaa BY INDEX aaa_333 (fk=?) (~10 rows)} - 0 1 0 {SCAN TABLE aaa AS bbb BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} + 0 0 1 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)} + 0 1 0 {SEARCH TABLE aaa AS bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } finish_test Index: test/where7.test ================================================================== --- test/where7.test +++ test/where7.test @@ -23339,12 +23339,12 @@ AND t302.c3 > 1287603136 AND (t301.c4 = 1407449685622784 OR t301.c8 = 1407424651264000) ORDER BY t302.c5 LIMIT 200; } { - 0 0 1 {SCAN TABLE t301 BY COVERING INDEX t301_c4 (c4=?) (~10 rows)} - 0 0 1 {SCAN TABLE t301 BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} - 0 1 0 {SCAN TABLE t302 BY INDEX t302_c8_c3 (c8=? AND c3>?) (~2 rows)} + 0 0 1 {SEARCH TABLE t301 USING COVERING INDEX t301_c4 (c4=?) (~10 rows)} + 0 0 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} + 0 1 0 {SEARCH TABLE t302 USING INDEX t302_c8_c3 (c8=? AND c3>?) (~2 rows)} 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } finish_test Index: test/where9.test ================================================================== --- test/where9.test +++ test/where9.test @@ -361,23 +361,23 @@ do_execsql_test where9-3.1 { EXPLAIN QUERY PLAN 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 {SCAN TABLE t1 BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} - 0 1 1 {SCAN TABLE t2 BY INDEX t2d (d=?) (~2 rows)} - 0 1 1 {SCAN TABLE t2 BY COVERING INDEX t2f (f=?) (~10 rows)} + 0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} + 0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?) (~2 rows)} + 0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~10 rows)} } do_execsql_test where9-3.2 { EXPLAIN QUERY PLAN 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 {SCAN TABLE t1 BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} - 0 1 1 {SCAN TABLE t2 BY INDEX t2d (d=?) (~2 rows)} - 0 1 1 {SCAN TABLE t2 BY COVERING INDEX t2f (f=?) (~10 rows)} + 0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} + 0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?) (~2 rows)} + 0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~10 rows)} } } # Make sure that INDEXED BY and multi-index OR clauses play well with # one another. @@ -452,29 +452,29 @@ # 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 {SCAN TABLE t1 BY INDEX t1c (c=?) (~10 rows)} - 0 0 0 {SCAN TABLE t1 BY INDEX t1d (d=?) (~10 rows)} + 0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c=?) (~10 rows)} + 0 0 0 {SEARCH TABLE t1 USING INDEX t1d (d=?) (~10 rows)} } # 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 {SCAN TABLE t1 BY INDEX t1b (b=?) (~5 rows)} + 0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~5 rows)} } # 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 {SCAN TABLE t1 BY INDEX t1b (b>?) (~165000 rows)} + 0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>?) (~165000 rows)} } } ############################################################################ # Make sure OR-clauses work correctly on UPDATE and DELETE statements.