Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Change the EXPLAIN QUERY PLAN output to use "USING INDEX" instead of "BY INDEX", and to use "SEARCH" instead of "SCAN" for loops that are not full-table scans. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | experimental |
| Files: | files | file ages | folders |
| SHA1: |
6611b76b0296875fb9903b25dfaa783a |
| User & Date: | dan 2010-11-13 16:42:27 |
Context
|
2010-11-15
| ||
| 14:44 | Merge the EXPLAIN QUERY PLAN changes from experimental into trunk. check-in: ce27bf38 user: drh tags: trunk | |
|
2010-11-13
| ||
| 16:42 | Change the EXPLAIN QUERY PLAN output to use "USING INDEX" instead of "BY INDEX", and to use "SEARCH" instead of "SCAN" for loops that are not full-table scans. Closed-Leaf check-in: 6611b76b user: dan tags: experimental | |
|
2010-11-12
| ||
| 17:41 | Add EXPLAIN QUERY PLAN test cases to check that the examples in the documentation work. check-in: 85fdad85 user: dan tags: experimental | |
Changes
Changes to src/where.c.
3217 3217 u32 flags = pLevel->plan.wsFlags; 3218 3218 struct SrcList_item *pItem = &pTabList->a[pLevel->iFrom]; 3219 3219 Vdbe *v = pParse->pVdbe; /* VM being constructed */ 3220 3220 sqlite3 *db = pParse->db; /* Database handle */ 3221 3221 char *zMsg; /* Text to add to EQP output */ 3222 3222 sqlite3_int64 nRow; /* Expected number of rows visited by scan */ 3223 3223 int iId = pParse->iSelectId; /* Select id (left-most output column) */ 3224 + int isSearch; /* True for a SEARCH. False for SCAN. */ 3224 3225 3225 3226 if( (flags&WHERE_MULTI_OR) || (wctrlFlags&WHERE_ONETABLE_ONLY) ) return; 3226 3227 3228 + isSearch = (pLevel->plan.nEq>0 || flags&(WHERE_BTM_LIMIT|WHERE_TOP_LIMIT)); 3229 + 3230 + zMsg = sqlite3MPrintf(db, "%s", isSearch?"SEARCH":"SCAN"); 3227 3231 if( pItem->pSelect ){ 3228 - zMsg = sqlite3MPrintf(db, "SCAN SUBQUERY %d", pItem->iSelectId); 3232 + zMsg = sqlite3MAppendf(db, zMsg, "%s SUBQUERY %d", zMsg,pItem->iSelectId); 3229 3233 }else{ 3230 - zMsg = sqlite3MPrintf(db, "SCAN TABLE %s", pItem->zName); 3234 + zMsg = sqlite3MAppendf(db, zMsg, "%s TABLE %s", zMsg, pItem->zName); 3231 3235 } 3232 3236 3233 3237 if( pItem->zAlias ){ 3234 3238 zMsg = sqlite3MAppendf(db, zMsg, "%s AS %s", zMsg, pItem->zAlias); 3235 3239 } 3236 3240 if( (flags & WHERE_INDEXED)!=0 ){ 3237 3241 char *zWhere = explainIndexRange(db, pLevel, pItem->pTab); 3238 - zMsg = sqlite3MAppendf(db, zMsg, "%s BY %s%sINDEX%s%s%s", zMsg, 3242 + zMsg = sqlite3MAppendf(db, zMsg, "%s USING %s%sINDEX%s%s%s", zMsg, 3239 3243 ((flags & WHERE_TEMP_INDEX)?"AUTOMATIC ":""), 3240 3244 ((flags & WHERE_IDX_ONLY)?"COVERING ":""), 3241 3245 ((flags & WHERE_TEMP_INDEX)?"":" "), 3242 3246 ((flags & WHERE_TEMP_INDEX)?"": pLevel->plan.u.pIdx->zName), 3243 3247 zWhere 3244 3248 ); 3245 3249 sqlite3DbFree(db, zWhere); 3246 3250 }else if( flags & (WHERE_ROWID_EQ|WHERE_ROWID_RANGE) ){ 3247 - zMsg = sqlite3MAppendf(db, zMsg, "%s BY INTEGER PRIMARY KEY", zMsg); 3251 + zMsg = sqlite3MAppendf(db, zMsg, "%s USING INTEGER PRIMARY KEY", zMsg); 3248 3252 3249 3253 if( flags&WHERE_ROWID_EQ ){ 3250 3254 zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid=?)", zMsg); 3251 3255 }else if( flags&WHERE_BTM_LIMIT && flags&WHERE_TOP_LIMIT ){ 3252 3256 zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid>? AND rowid<?)", zMsg); 3253 3257 }else if( flags&WHERE_BTM_LIMIT ){ 3254 3258 zMsg = sqlite3MAppendf(db, zMsg, "%s (rowid>?)", zMsg);
Changes to test/autoindex1.test.
143 143 do_execsql_test autoindex1-500 { 144 144 CREATE TABLE t501(a INTEGER PRIMARY KEY, b); 145 145 CREATE TABLE t502(x INTEGER PRIMARY KEY, y); 146 146 EXPLAIN QUERY PLAN 147 147 SELECT b FROM t501 148 148 WHERE t501.a IN (SELECT x FROM t502 WHERE y=?); 149 149 } { 150 - 0 0 0 {SCAN TABLE t501 BY INTEGER PRIMARY KEY (rowid=?) (~25 rows)} 150 + 0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?) (~25 rows)} 151 151 0 0 0 {EXECUTE LIST SUBQUERY 1} 152 152 1 0 0 {SCAN TABLE t502 (~100000 rows)} 153 153 } 154 154 do_execsql_test autoindex1-501 { 155 155 EXPLAIN QUERY PLAN 156 156 SELECT b FROM t501 157 157 WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b); 158 158 } { 159 159 0 0 0 {SCAN TABLE t501 (~500000 rows)} 160 160 0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 161 - 1 0 0 {SCAN TABLE t502 BY AUTOMATIC COVERING INDEX (y=?) (~7 rows)} 161 + 1 0 0 {SEARCH TABLE t502 USING AUTOMATIC COVERING INDEX (y=?) (~7 rows)} 162 162 } 163 163 do_execsql_test autoindex1-502 { 164 164 EXPLAIN QUERY PLAN 165 165 SELECT b FROM t501 166 166 WHERE t501.a=123 167 167 AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b); 168 168 } { 169 - 0 0 0 {SCAN TABLE t501 BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 169 + 0 0 0 {SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 170 170 0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} 171 171 1 0 0 {SCAN TABLE t502 (~100000 rows)} 172 172 } 173 173 174 174 175 175 # The following code checks a performance regression reported on the 176 176 # mailing list on 2010-10-19. The problem is that the nRowEst field ................................................................................ 237 237 AND later.owner_change_date > prev.owner_change_date 238 238 AND later.owner_change_date <= s.date_of_registration||' 00:00:00') 239 239 ) y ON x.sheep_no = y.sheep_no 240 240 WHERE y.sheep_no IS NULL 241 241 ORDER BY x.registering_flock; 242 242 } { 243 243 1 0 0 {SCAN TABLE sheep AS s (~1000000 rows)} 244 - 1 1 1 {SCAN TABLE flock_owner AS prev BY INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?) (~2 rows)} 244 + 1 1 1 {SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?) (~2 rows)} 245 245 1 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2} 246 - 2 0 0 {SCAN TABLE flock_owner AS later BY COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date>? AND owner_change_date<?) (~1 rows)} 247 - 0 0 0 {SCAN TABLE sheep AS x BY INDEX sheep_reg_flock_index (~1000000 rows)} 248 - 0 1 1 {SCAN SUBQUERY 1 AS y BY AUTOMATIC COVERING INDEX (sheep_no=?) (~7 rows)} 246 + 2 0 0 {SEARCH TABLE flock_owner AS later USING COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date>? AND owner_change_date<?) (~1 rows)} 247 + 0 0 0 {SCAN TABLE sheep AS x USING INDEX sheep_reg_flock_index (~1000000 rows)} 248 + 0 1 1 {SEARCH SUBQUERY 1 AS y USING AUTOMATIC COVERING INDEX (sheep_no=?) (~7 rows)} 249 249 } 250 250 251 251 finish_test
Changes to test/e_createtable.test.
1369 1369 # 1370 1370 do_execsql_test 4.10.0 { 1371 1371 CREATE TABLE t1(a, b PRIMARY KEY); 1372 1372 CREATE TABLE t2(a, b, c, UNIQUE(b, c)); 1373 1373 } 1374 1374 do_createtable_tests 4.10 { 1375 1375 1 "EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 5" 1376 - {0 0 0 {SCAN TABLE t1 BY INDEX sqlite_autoindex_t1_1 (b=?) (~1 rows)}} 1376 + {0 0 0 {SEARCH TABLE t1 USING INDEX sqlite_autoindex_t1_1 (b=?) (~1 rows)}} 1377 1377 1378 1378 2 "EXPLAIN QUERY PLAN SELECT * FROM t2 ORDER BY b, c" 1379 - {0 0 0 {SCAN TABLE t2 BY INDEX sqlite_autoindex_t2_1 (~1000000 rows)}} 1379 + {0 0 0 {SCAN TABLE t2 USING INDEX sqlite_autoindex_t2_1 (~1000000 rows)}} 1380 1380 1381 1381 3 "EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE b=10 AND c>10" 1382 - {0 0 0 {SCAN TABLE t2 BY INDEX sqlite_autoindex_t2_1 (b=? AND c>?) (~3 rows)}} 1382 + {0 0 0 {SEARCH TABLE t2 USING INDEX sqlite_autoindex_t2_1 (b=? AND c>?) (~3 rows)}} 1383 1383 } 1384 1384 1385 1385 # EVIDENCE-OF: R-45493-35653 A CHECK constraint may be attached to a 1386 1386 # column definition or specified as a table constraint. In practice it 1387 1387 # makes no difference. 1388 1388 # 1389 1389 # All the tests that deal with CHECK constraints below (4.11.* and
Changes to test/e_fkey.test.
1095 1095 do_test e_fkey-27.2 { 1096 1096 eqp { INSERT INTO artist VALUES(?, ?) } 1097 1097 } {} 1098 1098 do_execsql_test e_fkey-27.3 { 1099 1099 EXPLAIN QUERY PLAN UPDATE artist SET artistid = ?, artistname = ? 1100 1100 } { 1101 1101 0 0 0 {SCAN TABLE artist (~1000000 rows)} 1102 - 0 0 0 {SCAN TABLE track BY COVERING INDEX trackindex (trackartist=?) (~10 rows)} 1103 - 0 0 0 {SCAN TABLE track BY COVERING INDEX trackindex (trackartist=?) (~10 rows)} 1102 + 0 0 0 {SEARCH TABLE track USING COVERING INDEX trackindex (trackartist=?) (~10 rows)} 1103 + 0 0 0 {SEARCH TABLE track USING COVERING INDEX trackindex (trackartist=?) (~10 rows)} 1104 1104 } 1105 1105 do_execsql_test e_fkey-27.4 { 1106 1106 EXPLAIN QUERY PLAN DELETE FROM artist 1107 1107 } { 1108 1108 0 0 0 {SCAN TABLE artist (~1000000 rows)} 1109 - 0 0 0 {SCAN TABLE track BY COVERING INDEX trackindex (trackartist=?) (~10 rows)} 1109 + 0 0 0 {SEARCH TABLE track USING COVERING INDEX trackindex (trackartist=?) (~10 rows)} 1110 1110 } 1111 1111 1112 1112 1113 1113 ########################################################################### 1114 1114 ### SECTION 4.1: Composite Foreign Key Constraints 1115 1115 ########################################################################### 1116 1116
Changes to test/eqp.test.
35 35 CREATE TABLE t2(a, b); 36 36 CREATE TABLE t3(a, b); 37 37 } 38 38 39 39 do_eqp_test 1.2 { 40 40 SELECT * FROM t2, t1 WHERE t1.a=1 OR t1.b=2; 41 41 } { 42 - 0 0 1 {SCAN TABLE t1 BY INDEX i1 (a=?) (~10 rows)} 43 - 0 0 1 {SCAN TABLE t1 BY INDEX i2 (b=?) (~10 rows)} 42 + 0 0 1 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)} 43 + 0 0 1 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~10 rows)} 44 44 0 1 0 {SCAN TABLE t2 (~1000000 rows)} 45 45 } 46 46 do_eqp_test 1.3 { 47 47 SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=1 OR t1.b=2; 48 48 } { 49 49 0 0 0 {SCAN TABLE t2 (~1000000 rows)} 50 - 0 1 1 {SCAN TABLE t1 BY INDEX i1 (a=?) (~10 rows)} 51 - 0 1 1 {SCAN TABLE t1 BY INDEX i2 (b=?) (~10 rows)} 50 + 0 1 1 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)} 51 + 0 1 1 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~10 rows)} 52 52 } 53 53 do_eqp_test 1.3 { 54 54 SELECT a FROM t1 ORDER BY a 55 55 } { 56 - 0 0 0 {SCAN TABLE t1 BY COVERING INDEX i1 (~1000000 rows)} 56 + 0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1 (~1000000 rows)} 57 57 } 58 58 do_eqp_test 1.4 { 59 59 SELECT a FROM t1 ORDER BY +a 60 60 } { 61 61 0 0 0 {SCAN TABLE t1 (~1000000 rows)} 62 62 0 0 0 {USE TEMP B-TREE FOR ORDER BY} 63 63 } 64 64 do_eqp_test 1.5 { 65 65 SELECT a FROM t1 WHERE a=4 66 66 } { 67 - 0 0 0 {SCAN TABLE t1 BY COVERING INDEX i1 (a=?) (~10 rows)} 67 + 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)} 68 68 } 69 69 do_eqp_test 1.6 { 70 70 SELECT DISTINCT count(*) FROM t3 GROUP BY a; 71 71 } { 72 72 0 0 0 {SCAN TABLE t3 (~1000000 rows)} 73 73 0 0 0 {USE TEMP B-TREE FOR GROUP BY} 74 74 0 0 0 {USE TEMP B-TREE FOR DISTINCT} ................................................................................ 88 88 det 2.2.1 "SELECT DISTINCT min(x), max(x) FROM t1 GROUP BY x ORDER BY 1" { 89 89 0 0 0 {SCAN TABLE t1 (~1000000 rows)} 90 90 0 0 0 {USE TEMP B-TREE FOR GROUP BY} 91 91 0 0 0 {USE TEMP B-TREE FOR DISTINCT} 92 92 0 0 0 {USE TEMP B-TREE FOR ORDER BY} 93 93 } 94 94 det 2.2.2 "SELECT DISTINCT min(x), max(x) FROM t2 GROUP BY x ORDER BY 1" { 95 - 0 0 0 {SCAN TABLE t2 BY COVERING INDEX t2i1 (~1000000 rows)} 95 + 0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)} 96 96 0 0 0 {USE TEMP B-TREE FOR DISTINCT} 97 97 0 0 0 {USE TEMP B-TREE FOR ORDER BY} 98 98 } 99 99 det 2.2.3 "SELECT DISTINCT * FROM t1" { 100 100 0 0 0 {SCAN TABLE t1 (~1000000 rows)} 101 101 0 0 0 {USE TEMP B-TREE FOR DISTINCT} 102 102 } ................................................................................ 108 108 det 2.2.5 "SELECT DISTINCT * FROM t1, t2 ORDER BY t1.x" { 109 109 0 0 0 {SCAN TABLE t1 (~1000000 rows)} 110 110 0 1 1 {SCAN TABLE t2 (~1000000 rows)} 111 111 0 0 0 {USE TEMP B-TREE FOR DISTINCT} 112 112 0 0 0 {USE TEMP B-TREE FOR ORDER BY} 113 113 } 114 114 det 2.2.6 "SELECT DISTINCT t2.x FROM t1, t2 ORDER BY t2.x" { 115 - 0 0 1 {SCAN TABLE t2 BY COVERING INDEX t2i1 (~1000000 rows)} 115 + 0 0 1 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)} 116 116 0 1 0 {SCAN TABLE t1 (~1000000 rows)} 117 117 } 118 118 119 119 det 2.3.1 "SELECT max(x) FROM t2" { 120 - 0 0 0 {SCAN TABLE t2 BY COVERING INDEX t2i1 (~1 rows)} 120 + 0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1 rows)} 121 121 } 122 122 det 2.3.2 "SELECT min(x) FROM t2" { 123 - 0 0 0 {SCAN TABLE t2 BY COVERING INDEX t2i1 (~1 rows)} 123 + 0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1 rows)} 124 124 } 125 125 det 2.3.3 "SELECT min(x), max(x) FROM t2" { 126 126 0 0 0 {SCAN TABLE t2 (~1000000 rows)} 127 127 } 128 128 129 129 det 2.4.1 "SELECT * FROM t1 WHERE rowid=?" { 130 - 0 0 0 {SCAN TABLE t1 BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 130 + 0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 131 131 } 132 132 133 133 134 134 135 135 #------------------------------------------------------------------------- 136 136 # Test cases eqp-3.* - tests for select statements that use sub-selects. 137 137 # ................................................................................ 158 158 1 0 0 {USE TEMP B-TREE FOR ORDER BY} 159 159 } 160 160 do_eqp_test 3.1.4 { 161 161 SELECT * FROM t1 WHERE (SELECT x FROM t2 ORDER BY x); 162 162 } { 163 163 0 0 0 {SCAN TABLE t1 (~1000000 rows)} 164 164 0 0 0 {EXECUTE SCALAR SUBQUERY 1} 165 - 1 0 0 {SCAN TABLE t2 BY COVERING INDEX t2i1 (~1000000 rows)} 165 + 1 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)} 166 166 } 167 167 168 168 det 3.2.1 { 169 169 SELECT * FROM (SELECT * FROM t1 ORDER BY x LIMIT 10) ORDER BY y LIMIT 5 170 170 } { 171 171 1 0 0 {SCAN TABLE t1 (~1000000 rows)} 172 172 1 0 0 {USE TEMP B-TREE FOR ORDER BY} ................................................................................ 177 177 SELECT * FROM 178 178 (SELECT * FROM t1 ORDER BY x LIMIT 10) AS x1, 179 179 (SELECT * FROM t2 ORDER BY x LIMIT 10) AS x2 180 180 ORDER BY x2.y LIMIT 5 181 181 } { 182 182 1 0 0 {SCAN TABLE t1 (~1000000 rows)} 183 183 1 0 0 {USE TEMP B-TREE FOR ORDER BY} 184 - 2 0 0 {SCAN TABLE t2 BY INDEX t2i1 (~1000000 rows)} 184 + 2 0 0 {SCAN TABLE t2 USING INDEX t2i1 (~1000000 rows)} 185 185 0 0 0 {SCAN SUBQUERY 1 AS x1 (~1000000 rows)} 186 186 0 1 1 {SCAN SUBQUERY 2 AS x2 (~1000000 rows)} 187 187 0 0 0 {USE TEMP B-TREE FOR ORDER BY} 188 188 } 189 189 190 190 det 3.3.1 { 191 191 SELECT * FROM t1 WHERE y IN (SELECT y FROM t2) ................................................................................ 257 257 } 258 258 259 259 do_eqp_test 4.2.2 { 260 260 SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 1 261 261 } { 262 262 1 0 0 {SCAN TABLE t1 (~1000000 rows)} 263 263 1 0 0 {USE TEMP B-TREE FOR ORDER BY} 264 - 2 0 0 {SCAN TABLE t2 BY INDEX t2i1 (~1000000 rows)} 264 + 2 0 0 {SCAN TABLE t2 USING INDEX t2i1 (~1000000 rows)} 265 265 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)} 266 266 } 267 267 do_eqp_test 4.2.3 { 268 268 SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 1 269 269 } { 270 270 1 0 0 {SCAN TABLE t1 (~1000000 rows)} 271 271 1 0 0 {USE TEMP B-TREE FOR ORDER BY} ................................................................................ 310 310 0 0 0 {COMPOUND SUBQUERIES 1 AND 4 USING TEMP B-TREE (UNION)} 311 311 } 312 312 do_eqp_test 4.3.3 { 313 313 SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1 ORDER BY 1 314 314 } { 315 315 2 0 0 {SCAN TABLE t1 (~1000000 rows)} 316 316 2 0 0 {USE TEMP B-TREE FOR ORDER BY} 317 - 3 0 0 {SCAN TABLE t2 BY COVERING INDEX t2i1 (~1000000 rows)} 317 + 3 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1 (~1000000 rows)} 318 318 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 (UNION)} 319 319 4 0 0 {SCAN TABLE t1 (~1000000 rows)} 320 320 4 0 0 {USE TEMP B-TREE FOR ORDER BY} 321 321 0 0 0 {COMPOUND SUBQUERIES 1 AND 4 (UNION)} 322 322 } 323 323 324 324 #------------------------------------------------------------------------- ................................................................................ 325 325 # This next block of tests verifies that the examples on the 326 326 # lang_explain.html page are correct. 327 327 # 328 328 drop_all_tables 329 329 330 330 # EVIDENCE-OF: R-64208-08323 sqlite> EXPLAIN QUERY PLAN SELECT a, b 331 331 # FROM t1 WHERE a=1; 0|0|0|SCAN TABLE t1 (~100000 rows) 332 -# 333 332 do_execsql_test 5.1.0 { CREATE TABLE t1(a, b) } 334 333 det 5.1.1 "SELECT a, b FROM t1 WHERE a=1" { 335 334 0 0 0 {SCAN TABLE t1 (~100000 rows)} 336 335 } 337 336 338 -# EVIDENCE-OF: R-03114-52867 sqlite> CREATE INDEX i1 ON t1(a); 337 +# EVIDENCE-OF: R-09022-44606 sqlite> CREATE INDEX i1 ON t1(a); 339 338 # sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; 340 -# 0|0|0|SCAN TABLE t1 BY INDEX i1 (a=?) (~10 rows) 339 +# 0|0|0|SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows) 341 340 do_execsql_test 5.2.0 { CREATE INDEX i1 ON t1(a) } 342 341 det 5.2.1 "SELECT a, b FROM t1 WHERE a=1" { 343 - 0 0 0 {SCAN TABLE t1 BY INDEX i1 (a=?) (~10 rows)} 342 + 0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)} 344 343 } 345 344 346 -# EVIDENCE-OF: R-20407-61322 sqlite> CREATE INDEX i2 ON t1(a, b); 345 +# EVIDENCE-OF: R-62228-34103 sqlite> CREATE INDEX i2 ON t1(a, b); 347 346 # sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; 348 -# 0|0|0|SCAN TABLE t1 BY COVERING INDEX i2 (a=?) (~10 rows) 347 +# 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows) 349 348 do_execsql_test 5.3.0 { CREATE INDEX i2 ON t1(a, b) } 350 349 det 5.3.1 "SELECT a, b FROM t1 WHERE a=1" { 351 - 0 0 0 {SCAN TABLE t1 BY COVERING INDEX i2 (a=?) (~10 rows)} 350 + 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)} 352 351 } 353 352 354 -# EVIDENCE-OF: R-01893-00096 sqlite> EXPLAIN QUERY PLAN SELECT t1.*, 355 -# t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2; 0|0|0|SCAN TABLE t1 BY 356 -# COVERING INDEX i2 (a=? AND b>?) (~3 rows) 0|1|1|SCAN TABLE t2 353 +# EVIDENCE-OF: R-22253-05302 sqlite> EXPLAIN QUERY PLAN SELECT t1.*, 354 +# t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2; 0|0|0|SEARCH TABLE t1 355 +# USING COVERING INDEX i2 (a=? AND b>?) (~3 rows) 0|1|1|SCAN TABLE t2 357 356 # (~1000000 rows) 358 357 do_execsql_test 5.4.0 {CREATE TABLE t2(c, d)} 359 358 det 5.4.1 "SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2" { 360 - 0 0 0 {SCAN TABLE t1 BY COVERING INDEX i2 (a=? AND b>?) (~3 rows)} 359 + 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) (~3 rows)} 361 360 0 1 1 {SCAN TABLE t2 (~1000000 rows)} 362 361 } 363 362 364 -# EVIDENCE-OF: R-26531-36629 sqlite> EXPLAIN QUERY PLAN SELECT t1.*, 365 -# t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2; 0|0|1|SCAN TABLE t1 BY 366 -# COVERING INDEX i2 (a=? AND b>?) (~3 rows) 0|1|0|SCAN TABLE t2 363 +# EVIDENCE-OF: R-21040-07025 sqlite> EXPLAIN QUERY PLAN SELECT t1.*, 364 +# t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2; 0|0|1|SEARCH TABLE t1 365 +# USING COVERING INDEX i2 (a=? AND b>?) (~3 rows) 0|1|0|SCAN TABLE t2 367 366 # (~1000000 rows) 368 367 det 5.5 "SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2" { 369 - 0 0 1 {SCAN TABLE t1 BY COVERING INDEX i2 (a=? AND b>?) (~3 rows)} 368 + 0 0 1 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) (~3 rows)} 370 369 0 1 0 {SCAN TABLE t2 (~1000000 rows)} 371 370 } 372 371 373 -# EVIDENCE-OF: R-17671-37431 sqlite> CREATE INDEX i3 ON t1(b); 372 +# EVIDENCE-OF: R-39007-61103 sqlite> CREATE INDEX i3 ON t1(b); 374 373 # sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2; 375 -# 0|0|0|SCAN TABLE t1 BY COVERING INDEX i2 (a=?) (~10 rows) 0|0|0|SCAN 376 -# TABLE t1 BY INDEX i3 (b=?) (~10 rows) 374 +# 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows) 375 +# 0|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows) 377 376 do_execsql_test 5.5.0 {CREATE INDEX i3 ON t1(b)} 378 377 det 5.6.1 "SELECT * FROM t1 WHERE a=1 OR b=2" { 379 - 0 0 0 {SCAN TABLE t1 BY COVERING INDEX i2 (a=?) (~10 rows)} 380 - 0 0 0 {SCAN TABLE t1 BY INDEX i3 (b=?) (~10 rows)} 378 + 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)} 379 + 0 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)} 381 380 } 382 381 383 382 # EVIDENCE-OF: R-33025-54904 sqlite> EXPLAIN QUERY PLAN SELECT c, d 384 383 # FROM t2 ORDER BY c; 0|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|USE TEMP 385 384 # B-TREE FOR ORDER BY 386 385 det 5.7 "SELECT c, d FROM t2 ORDER BY c" { 387 386 0 0 0 {SCAN TABLE t2 (~1000000 rows)} 388 387 0 0 0 {USE TEMP B-TREE FOR ORDER BY} 389 388 } 390 389 391 -# EVIDENCE-OF: R-08354-12138 sqlite> CREATE INDEX i4 ON t2(c); 390 +# EVIDENCE-OF: R-38854-22809 sqlite> CREATE INDEX i4 ON t2(c); 392 391 # sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c; 393 -# 0|0|0|SCAN TABLE t2 BY INDEX i4 (~1000000 rows) 392 +# 0|0|0|SCAN TABLE t2 USING INDEX i4 (~1000000 rows) 394 393 do_execsql_test 5.8.0 {CREATE INDEX i4 ON t2(c)} 395 394 det 5.8.1 "SELECT c, d FROM t2 ORDER BY c" { 396 - 0 0 0 {SCAN TABLE t2 BY INDEX i4 (~1000000 rows)} 395 + 0 0 0 {SCAN TABLE t2 USING INDEX i4 (~1000000 rows)} 397 396 } 398 397 399 -# EVIDENCE-OF: R-01895-58356 sqlite> EXPLAIN QUERY PLAN SELECT 398 +# EVIDENCE-OF: R-29884-43993 sqlite> EXPLAIN QUERY PLAN SELECT 400 399 # (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2; 401 400 # 0|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|EXECUTE SCALAR SUBQUERY 1 402 -# 1|0|0|SCAN TABLE t1 BY COVERING INDEX i2 (a=?) (~10 rows) 403 -# 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2 2|0|0|SCAN TABLE t1 BY 401 +# 1|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows) 402 +# 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2 2|0|0|SEARCH TABLE t1 USING 404 403 # INDEX i3 (b=?) (~10 rows) 405 404 det 5.9 { 406 405 SELECT (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2 407 406 } { 408 407 0 0 0 {SCAN TABLE t2 (~1000000 rows)} 409 408 0 0 0 {EXECUTE SCALAR SUBQUERY 1} 410 - 1 0 0 {SCAN TABLE t1 BY COVERING INDEX i2 (a=?) (~10 rows)} 409 + 1 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) (~10 rows)} 411 410 0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2} 412 - 2 0 0 {SCAN TABLE t1 BY INDEX i3 (b=?) (~10 rows)} 411 + 2 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?) (~10 rows)} 413 412 } 414 413 415 -# EVIDENCE-OF: R-43933-45972 sqlite> EXPLAIN QUERY PLAN SELECT 414 +# EVIDENCE-OF: R-17911-16445 sqlite> EXPLAIN QUERY PLAN SELECT 416 415 # count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x; 417 -# 1|0|0|SCAN TABLE t1 BY COVERING INDEX i2 (~1000000 rows) 0|0|0|SCAN 416 +# 1|0|0|SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows) 0|0|0|SCAN 418 417 # SUBQUERY 1 (~1000000 rows) 0|0|0|USE TEMP B-TREE FOR GROUP BY 419 418 det 5.10 { 420 419 SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x 421 420 } { 422 - 1 0 0 {SCAN TABLE t1 BY COVERING INDEX i2 (~1000000 rows)} 421 + 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows)} 423 422 0 0 0 {SCAN SUBQUERY 1 (~1000000 rows)} 424 423 0 0 0 {USE TEMP B-TREE FOR GROUP BY} 425 424 } 426 425 427 -# EVIDENCE-OF: R-15989-23611 sqlite> EXPLAIN QUERY PLAN SELECT * FROM 428 -# (SELECT * FROM t2 WHERE c=1), t1; 0|0|0|SCAN TABLE t2 BY INDEX i4 426 +# EVIDENCE-OF: R-18544-33103 sqlite> EXPLAIN QUERY PLAN SELECT * FROM 427 +# (SELECT * FROM t2 WHERE c=1), t1; 0|0|0|SEARCH TABLE t2 USING INDEX i4 429 428 # (c=?) (~10 rows) 0|1|1|SCAN TABLE t1 (~1000000 rows) 430 429 det 5.11 "SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1" { 431 - 0 0 0 {SCAN TABLE t2 BY INDEX i4 (c=?) (~10 rows)} 430 + 0 0 0 {SEARCH TABLE t2 USING INDEX i4 (c=?) (~10 rows)} 432 431 0 1 1 {SCAN TABLE t1 (~1000000 rows)} 433 432 } 434 433 435 434 # EVIDENCE-OF: R-40701-42164 sqlite> EXPLAIN QUERY PLAN SELECT a FROM 436 435 # t1 UNION SELECT c FROM t2; 1|0|0|SCAN TABLE t1 (~1000000 rows) 437 436 # 2|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|COMPOUND SUBQUERIES 1 AND 2 438 437 # USING TEMP B-TREE (UNION) 439 438 det 5.12 "SELECT a FROM t1 UNION SELECT c FROM t2" { 440 439 1 0 0 {SCAN TABLE t1 (~1000000 rows)} 441 440 2 0 0 {SCAN TABLE t2 (~1000000 rows)} 442 441 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)} 443 442 } 444 443 445 -# EVIDENCE-OF: R-34523-61710 sqlite> EXPLAIN QUERY PLAN SELECT a FROM 446 -# t1 EXCEPT SELECT d FROM t2 ORDER BY 1; 1|0|0|SCAN TABLE t1 BY COVERING 447 -# INDEX i2 (~1000000 rows) 2|0|0|SCAN TABLE t2 (~1000000 rows) 2|0|0|USE 448 -# TEMP B-TREE FOR ORDER BY 0|0|0|COMPOUND SUBQUERIES 1 AND 2 (EXCEPT) 444 +# EVIDENCE-OF: R-61538-24748 sqlite> EXPLAIN QUERY PLAN SELECT a FROM 445 +# t1 EXCEPT SELECT d FROM t2 ORDER BY 1; 1|0|0|SCAN TABLE t1 USING 446 +# COVERING INDEX i2 (~1000000 rows) 2|0|0|SCAN TABLE t2 (~1000000 rows) 447 +# 2|0|0|USE TEMP B-TREE FOR ORDER BY 0|0|0|COMPOUND SUBQUERIES 1 AND 2 448 +# (EXCEPT) 449 449 det 5.13 "SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1" { 450 - 1 0 0 {SCAN TABLE t1 BY COVERING INDEX i2 (~1000000 rows)} 450 + 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2 (~1000000 rows)} 451 451 2 0 0 {SCAN TABLE t2 (~1000000 rows)} 452 452 2 0 0 {USE TEMP B-TREE FOR ORDER BY} 453 453 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)} 454 454 } 455 455 456 456 457 457 458 458 459 459 460 460 finish_test 461 461
Changes to test/indexedby.test.
38 38 uplevel "execsql {EXPLAIN QUERY PLAN $sql}" 39 39 } 40 40 41 41 # These tests are to check that "EXPLAIN QUERY PLAN" is working as expected. 42 42 # 43 43 do_execsql_test indexedby-1.2 { 44 44 EXPLAIN QUERY PLAN select * from t1 WHERE a = 10; 45 -} {0 0 0 {SCAN TABLE t1 BY INDEX i1 (a=?) (~10 rows)}} 45 +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)}} 46 46 do_execsql_test indexedby-1.3 { 47 47 EXPLAIN QUERY PLAN select * from t1 ; 48 48 } {0 0 0 {SCAN TABLE t1 (~1000000 rows)}} 49 49 do_execsql_test indexedby-1.4 { 50 50 EXPLAIN QUERY PLAN select * from t1, t2 WHERE c = 10; 51 51 } { 52 - 0 0 1 {SCAN TABLE t2 BY INDEX i3 (c=?) (~10 rows)} 52 + 0 0 1 {SEARCH TABLE t2 USING INDEX i3 (c=?) (~10 rows)} 53 53 0 1 0 {SCAN TABLE t1 (~1000000 rows)} 54 54 } 55 55 56 56 # Parser tests. Test that an INDEXED BY or NOT INDEX clause can be 57 57 # attached to a table in the FROM clause, but not to a sub-select or 58 58 # SQL view. Also test that specifying an index that does not exist or 59 59 # is attached to a different table is detected as an error. ................................................................................ 85 85 # 86 86 do_execsql_test indexedby-3.1 { 87 87 EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE a = 'one' AND b = 'two' 88 88 } {0 0 0 {SCAN TABLE t1 (~10000 rows)}} 89 89 do_execsql_test indexedby-3.2 { 90 90 EXPLAIN QUERY PLAN 91 91 SELECT * FROM t1 INDEXED BY i1 WHERE a = 'one' AND b = 'two' 92 -} {0 0 0 {SCAN TABLE t1 BY INDEX i1 (a=?) (~2 rows)}} 92 +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~2 rows)}} 93 93 do_execsql_test indexedby-3.3 { 94 94 EXPLAIN QUERY PLAN 95 95 SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' AND b = 'two' 96 -} {0 0 0 {SCAN TABLE t1 BY INDEX i2 (b=?) (~2 rows)}} 96 +} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~2 rows)}} 97 97 do_test indexedby-3.4 { 98 98 catchsql { SELECT * FROM t1 INDEXED BY i2 WHERE a = 'one' } 99 99 } {1 {cannot use index: i2}} 100 100 do_test indexedby-3.5 { 101 101 catchsql { SELECT * FROM t1 INDEXED BY i2 ORDER BY a } 102 102 } {1 {cannot use index: i2}} 103 103 do_test indexedby-3.6 { ................................................................................ 106 106 do_test indexedby-3.7 { 107 107 catchsql { SELECT * FROM t1 INDEXED BY i1 ORDER BY a } 108 108 } {0 {}} 109 109 110 110 do_execsql_test indexedby-3.8 { 111 111 EXPLAIN QUERY PLAN 112 112 SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 ORDER BY e 113 -} {0 0 0 {SCAN TABLE t3 BY INDEX sqlite_autoindex_t3_1 (~1000000 rows)}} 113 +} {0 0 0 {SCAN TABLE t3 USING INDEX sqlite_autoindex_t3_1 (~1000000 rows)}} 114 114 do_execsql_test indexedby-3.9 { 115 115 EXPLAIN QUERY PLAN 116 116 SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE e = 10 117 -} {0 0 0 {SCAN TABLE t3 BY INDEX sqlite_autoindex_t3_1 (e=?) (~1 rows)}} 117 +} {0 0 0 {SEARCH TABLE t3 USING INDEX sqlite_autoindex_t3_1 (e=?) (~1 rows)}} 118 118 do_test indexedby-3.10 { 119 119 catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_1 WHERE f = 10 } 120 120 } {1 {cannot use index: sqlite_autoindex_t3_1}} 121 121 do_test indexedby-3.11 { 122 122 catchsql { SELECT * FROM t3 INDEXED BY sqlite_autoindex_t3_2 WHERE f = 10 } 123 123 } {1 {no such index: sqlite_autoindex_t3_2}} 124 124 125 125 # Tests for multiple table cases. 126 126 # 127 127 do_execsql_test indexedby-4.1 { 128 128 EXPLAIN QUERY PLAN SELECT * FROM t1, t2 WHERE a = c 129 129 } { 130 130 0 0 0 {SCAN TABLE t1 (~1000000 rows)} 131 - 0 1 1 {SCAN TABLE t2 BY INDEX i3 (c=?) (~10 rows)} 131 + 0 1 1 {SEARCH TABLE t2 USING INDEX i3 (c=?) (~10 rows)} 132 132 } 133 133 do_execsql_test indexedby-4.2 { 134 134 EXPLAIN QUERY PLAN SELECT * FROM t1 INDEXED BY i1, t2 WHERE a = c 135 135 } { 136 136 0 0 1 {SCAN TABLE t2 (~1000000 rows)} 137 - 0 1 0 {SCAN TABLE t1 BY INDEX i1 (a=?) (~10 rows)} 137 + 0 1 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~10 rows)} 138 138 } 139 139 do_test indexedby-4.3 { 140 140 catchsql { 141 141 SELECT * FROM t1 INDEXED BY i1, t2 INDEXED BY i3 WHERE a=c 142 142 } 143 143 } {1 {cannot use index: i1}} 144 144 do_test indexedby-4.4 { ................................................................................ 150 150 # Test embedding an INDEXED BY in a CREATE VIEW statement. This block 151 151 # also tests that nothing bad happens if an index refered to by 152 152 # a CREATE VIEW statement is dropped and recreated. 153 153 # 154 154 do_execsql_test indexedby-5.1 { 155 155 CREATE VIEW v2 AS SELECT * FROM t1 INDEXED BY i1 WHERE a > 5; 156 156 EXPLAIN QUERY PLAN SELECT * FROM v2 157 -} {0 0 0 {SCAN TABLE t1 BY INDEX i1 (a>?) (~330000 rows)}} 157 +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?) (~330000 rows)}} 158 158 do_execsql_test indexedby-5.2 { 159 159 EXPLAIN QUERY PLAN SELECT * FROM v2 WHERE b = 10 160 -} {0 0 0 {SCAN TABLE t1 BY INDEX i1 (a>?) (~33000 rows)}} 160 +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a>?) (~33000 rows)}} 161 161 do_test indexedby-5.3 { 162 162 execsql { DROP INDEX i1 } 163 163 catchsql { SELECT * FROM v2 } 164 164 } {1 {no such index: i1}} 165 165 do_test indexedby-5.4 { 166 166 # Recreate index i1 in such a way as it cannot be used by the view query. 167 167 execsql { CREATE INDEX i1 ON t1(b) } ................................................................................ 174 174 catchsql { SELECT * FROM v2 } 175 175 } {0 {}} 176 176 177 177 # Test that "NOT INDEXED" may use the rowid index, but not others. 178 178 # 179 179 do_execsql_test indexedby-6.1 { 180 180 EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 10 ORDER BY rowid 181 -} {0 0 0 {SCAN TABLE t1 BY INDEX i2 (b=?) (~10 rows)}} 181 +} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~10 rows)}} 182 182 do_execsql_test indexedby-6.2 { 183 183 EXPLAIN QUERY PLAN SELECT * FROM t1 NOT INDEXED WHERE b = 10 ORDER BY rowid 184 -} {0 0 0 {SCAN TABLE t1 BY INTEGER PRIMARY KEY (~100000 rows)}} 184 +} {0 0 0 {SCAN TABLE t1 USING INTEGER PRIMARY KEY (~100000 rows)}} 185 185 186 186 # Test that "INDEXED BY" can be used in a DELETE statement. 187 187 # 188 188 do_execsql_test indexedby-7.1 { 189 189 EXPLAIN QUERY PLAN DELETE FROM t1 WHERE a = 5 190 -} {0 0 0 {SCAN TABLE t1 BY COVERING INDEX i1 (a=?) (~10 rows)}} 190 +} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}} 191 191 do_execsql_test indexedby-7.2 { 192 192 EXPLAIN QUERY PLAN DELETE FROM t1 NOT INDEXED WHERE a = 5 193 193 } {0 0 0 {SCAN TABLE t1 (~100000 rows)}} 194 194 do_execsql_test indexedby-7.3 { 195 195 EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5 196 -} {0 0 0 {SCAN TABLE t1 BY COVERING INDEX i1 (a=?) (~10 rows)}} 196 +} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}} 197 197 do_execsql_test indexedby-7.4 { 198 198 EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i1 WHERE a = 5 AND b = 10 199 -} {0 0 0 {SCAN TABLE t1 BY INDEX i1 (a=?) (~2 rows)}} 199 +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~2 rows)}} 200 200 do_execsql_test indexedby-7.5 { 201 201 EXPLAIN QUERY PLAN DELETE FROM t1 INDEXED BY i2 WHERE a = 5 AND b = 10 202 -} {0 0 0 {SCAN TABLE t1 BY INDEX i2 (b=?) (~2 rows)}} 202 +} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~2 rows)}} 203 203 do_test indexedby-7.6 { 204 204 catchsql { DELETE FROM t1 INDEXED BY i2 WHERE a = 5} 205 205 } {1 {cannot use index: i2}} 206 206 207 207 # Test that "INDEXED BY" can be used in an UPDATE statement. 208 208 # 209 209 do_execsql_test indexedby-8.1 { 210 210 EXPLAIN QUERY PLAN UPDATE t1 SET rowid=rowid+1 WHERE a = 5 211 -} {0 0 0 {SCAN TABLE t1 BY COVERING INDEX i1 (a=?) (~10 rows)}} 211 +} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}} 212 212 do_execsql_test indexedby-8.2 { 213 213 EXPLAIN QUERY PLAN UPDATE t1 NOT INDEXED SET rowid=rowid+1 WHERE a = 5 214 214 } {0 0 0 {SCAN TABLE t1 (~100000 rows)}} 215 215 do_execsql_test indexedby-8.3 { 216 216 EXPLAIN QUERY PLAN UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 217 -} {0 0 0 {SCAN TABLE t1 BY COVERING INDEX i1 (a=?) (~10 rows)}} 217 +} {0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?) (~10 rows)}} 218 218 do_execsql_test indexedby-8.4 { 219 219 EXPLAIN QUERY PLAN 220 220 UPDATE t1 INDEXED BY i1 SET rowid=rowid+1 WHERE a = 5 AND b = 10 221 -} {0 0 0 {SCAN TABLE t1 BY INDEX i1 (a=?) (~2 rows)}} 221 +} {0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?) (~2 rows)}} 222 222 do_execsql_test indexedby-8.5 { 223 223 EXPLAIN QUERY PLAN 224 224 UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5 AND b = 10 225 -} {0 0 0 {SCAN TABLE t1 BY INDEX i2 (b=?) (~2 rows)}} 225 +} {0 0 0 {SEARCH TABLE t1 USING INDEX i2 (b=?) (~2 rows)}} 226 226 do_test indexedby-8.6 { 227 227 catchsql { UPDATE t1 INDEXED BY i2 SET rowid=rowid+1 WHERE a = 5} 228 228 } {1 {cannot use index: i2}} 229 229 230 230 # Test that bug #3560 is fixed. 231 231 # 232 232 do_test indexedby-9.1 {
Changes to test/tkt-78e04e52ea.test.
53 53 } {t2} 54 54 55 55 do_test tkt-78e04-2.1 { 56 56 execsql { 57 57 CREATE INDEX "" ON t2(x); 58 58 EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE x=5; 59 59 } 60 -} {0 0 0 {SCAN TABLE t2 BY COVERING INDEX (x=?) (~10 rows)}} 60 +} {0 0 0 {SEARCH TABLE t2 USING COVERING INDEX (x=?) (~10 rows)}} 61 61 do_test tkt-78e04-2.2 { 62 62 execsql { 63 63 DROP INDEX ""; 64 64 EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE x=2; 65 65 } 66 66 } {0 0 0 {SCAN TABLE t2 (~100000 rows)}} 67 67 68 68 finish_test
Changes to test/tkt3442.test.
45 45 # These tests perform an EXPLAIN QUERY PLAN on both versions of the 46 46 # SELECT referenced in ticket #3442 (both '5000' and "5000") 47 47 # and verify that the query plan is the same. 48 48 # 49 49 ifcapable explain { 50 50 do_test tkt3442-1.2 { 51 51 EQP { SELECT node FROM listhash WHERE id='5000' LIMIT 1; } 52 - } {0 0 0 {SCAN TABLE listhash BY INDEX ididx (id=?) (~1 rows)}} 52 + } {0 0 0 {SEARCH TABLE listhash USING INDEX ididx (id=?) (~1 rows)}} 53 53 do_test tkt3442-1.3 { 54 54 EQP { SELECT node FROM listhash WHERE id="5000" LIMIT 1; } 55 - } {0 0 0 {SCAN TABLE listhash BY INDEX ididx (id=?) (~1 rows)}} 55 + } {0 0 0 {SEARCH TABLE listhash USING INDEX ididx (id=?) (~1 rows)}} 56 56 } 57 57 58 58 59 59 # Some extra tests testing other permutations of 5000. 60 60 # 61 61 ifcapable explain { 62 62 do_test tkt3442-1.4 { 63 63 EQP { SELECT node FROM listhash WHERE id=5000 LIMIT 1; } 64 - } {0 0 0 {SCAN TABLE listhash BY INDEX ididx (id=?) (~1 rows)}} 64 + } {0 0 0 {SEARCH TABLE listhash USING INDEX ididx (id=?) (~1 rows)}} 65 65 } 66 66 do_test tkt3442-1.5 { 67 67 catchsql { 68 68 SELECT node FROM listhash WHERE id=[5000] LIMIT 1; 69 69 } 70 70 } {1 {no such column: 5000}} 71 71 72 72 finish_test
Changes to test/where3.test.
222 222 CREATE INDEX t301c ON t301(c); 223 223 INSERT INTO t301 VALUES(1,2,3); 224 224 CREATE TABLE t302(x, y); 225 225 ANALYZE; 226 226 explain query plan SELECT * FROM t302, t301 WHERE t302.x=5 AND t301.a=t302.y; 227 227 } { 228 228 0 0 0 {SCAN TABLE t302 (~0 rows)} 229 - 0 1 1 {SCAN TABLE t301 BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 229 + 0 1 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 230 230 } 231 231 do_execsql_test where3-3.1 { 232 232 explain query plan 233 233 SELECT * FROM t301, t302 WHERE t302.x=5 AND t301.a=t302.y; 234 234 } { 235 235 0 0 1 {SCAN TABLE t302 (~0 rows)} 236 - 0 1 0 {SCAN TABLE t301 BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 236 + 0 1 0 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 237 237 } 238 238 239 239 # Verify that when there are multiple tables in a join which must be 240 240 # full table scans that the query planner attempts put the table with 241 241 # the fewest number of output rows as the outer loop. 242 242 # 243 243 do_execsql_test where3-4.0 { ................................................................................ 293 293 SELECT bbb.title AS tag_title 294 294 FROM aaa JOIN bbb ON bbb.id = aaa.parent 295 295 WHERE aaa.fk = 'constant' 296 296 AND LENGTH(bbb.title) > 0 297 297 AND bbb.parent = 4 298 298 ORDER BY bbb.title COLLATE NOCASE ASC; 299 299 } { 300 - 0 0 0 {SCAN TABLE aaa BY INDEX aaa_333 (fk=?) (~10 rows)} 301 - 0 1 1 {SCAN TABLE bbb BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 300 + 0 0 0 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)} 301 + 0 1 1 {SEARCH TABLE bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 302 302 0 0 0 {USE TEMP B-TREE FOR ORDER BY} 303 303 } 304 304 do_execsql_test where3-5.1 { 305 305 EXPLAIN QUERY PLAN 306 306 SELECT bbb.title AS tag_title 307 307 FROM aaa JOIN aaa AS bbb ON bbb.id = aaa.parent 308 308 WHERE aaa.fk = 'constant' 309 309 AND LENGTH(bbb.title) > 0 310 310 AND bbb.parent = 4 311 311 ORDER BY bbb.title COLLATE NOCASE ASC; 312 312 } { 313 - 0 0 0 {SCAN TABLE aaa BY INDEX aaa_333 (fk=?) (~10 rows)} 314 - 0 1 1 {SCAN TABLE aaa AS bbb BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 313 + 0 0 0 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)} 314 + 0 1 1 {SEARCH TABLE aaa AS bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 315 315 0 0 0 {USE TEMP B-TREE FOR ORDER BY} 316 316 } 317 317 do_execsql_test where3-5.2 { 318 318 EXPLAIN QUERY PLAN 319 319 SELECT bbb.title AS tag_title 320 320 FROM bbb JOIN aaa ON bbb.id = aaa.parent 321 321 WHERE aaa.fk = 'constant' 322 322 AND LENGTH(bbb.title) > 0 323 323 AND bbb.parent = 4 324 324 ORDER BY bbb.title COLLATE NOCASE ASC; 325 325 } { 326 - 0 0 1 {SCAN TABLE aaa BY INDEX aaa_333 (fk=?) (~10 rows)} 327 - 0 1 0 {SCAN TABLE bbb BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 326 + 0 0 1 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)} 327 + 0 1 0 {SEARCH TABLE bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 328 328 0 0 0 {USE TEMP B-TREE FOR ORDER BY} 329 329 } 330 330 do_execsql_test where3-5.3 { 331 331 EXPLAIN QUERY PLAN 332 332 SELECT bbb.title AS tag_title 333 333 FROM aaa AS bbb JOIN aaa ON bbb.id = aaa.parent 334 334 WHERE aaa.fk = 'constant' 335 335 AND LENGTH(bbb.title) > 0 336 336 AND bbb.parent = 4 337 337 ORDER BY bbb.title COLLATE NOCASE ASC; 338 338 } { 339 - 0 0 1 {SCAN TABLE aaa BY INDEX aaa_333 (fk=?) (~10 rows)} 340 - 0 1 0 {SCAN TABLE aaa AS bbb BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 339 + 0 0 1 {SEARCH TABLE aaa USING INDEX aaa_333 (fk=?) (~10 rows)} 340 + 0 1 0 {SEARCH TABLE aaa AS bbb USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 341 341 0 0 0 {USE TEMP B-TREE FOR ORDER BY} 342 342 } 343 343 344 344 345 345 finish_test
Changes to test/where7.test.
23337 23337 FROM t302 JOIN t301 ON t302.c8 = t301.c8 23338 23338 WHERE t302.c2 = 19571 23339 23339 AND t302.c3 > 1287603136 23340 23340 AND (t301.c4 = 1407449685622784 23341 23341 OR t301.c8 = 1407424651264000) 23342 23342 ORDER BY t302.c5 LIMIT 200; 23343 23343 } { 23344 - 0 0 1 {SCAN TABLE t301 BY COVERING INDEX t301_c4 (c4=?) (~10 rows)} 23345 - 0 0 1 {SCAN TABLE t301 BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 23346 - 0 1 0 {SCAN TABLE t302 BY INDEX t302_c8_c3 (c8=? AND c3>?) (~2 rows)} 23344 + 0 0 1 {SEARCH TABLE t301 USING COVERING INDEX t301_c4 (c4=?) (~10 rows)} 23345 + 0 0 1 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 23346 + 0 1 0 {SEARCH TABLE t302 USING INDEX t302_c8_c3 (c8=? AND c3>?) (~2 rows)} 23347 23347 0 0 0 {USE TEMP B-TREE FOR ORDER BY} 23348 23348 } 23349 23349 23350 23350 finish_test
Changes to test/where9.test.
359 359 360 360 ifcapable explain { 361 361 do_execsql_test where9-3.1 { 362 362 EXPLAIN QUERY PLAN 363 363 SELECT t2.a FROM t1, t2 364 364 WHERE t1.a=80 AND ((t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f) 365 365 } { 366 - 0 0 0 {SCAN TABLE t1 BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 367 - 0 1 1 {SCAN TABLE t2 BY INDEX t2d (d=?) (~2 rows)} 368 - 0 1 1 {SCAN TABLE t2 BY COVERING INDEX t2f (f=?) (~10 rows)} 366 + 0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 367 + 0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?) (~2 rows)} 368 + 0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~10 rows)} 369 369 } 370 370 do_execsql_test where9-3.2 { 371 371 EXPLAIN QUERY PLAN 372 372 SELECT coalesce(t2.a,9999) 373 373 FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f 374 374 WHERE t1.a=80 375 375 } { 376 - 0 0 0 {SCAN TABLE t1 BY INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 377 - 0 1 1 {SCAN TABLE t2 BY INDEX t2d (d=?) (~2 rows)} 378 - 0 1 1 {SCAN TABLE t2 BY COVERING INDEX t2f (f=?) (~10 rows)} 376 + 0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)} 377 + 0 1 1 {SEARCH TABLE t2 USING INDEX t2d (d=?) (~2 rows)} 378 + 0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2f (f=?) (~10 rows)} 379 379 } 380 380 } 381 381 382 382 # Make sure that INDEXED BY and multi-index OR clauses play well with 383 383 # one another. 384 384 # 385 385 do_test where9-4.1 { ................................................................................ 450 450 ifcapable explain { 451 451 # The (c=31031 OR d IS NULL) clause is preferred over b>1000 because 452 452 # the former is an equality test which is expected to return fewer rows. 453 453 # 454 454 do_execsql_test where9-5.1 { 455 455 EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c=31031 OR d IS NULL) 456 456 } { 457 - 0 0 0 {SCAN TABLE t1 BY INDEX t1c (c=?) (~10 rows)} 458 - 0 0 0 {SCAN TABLE t1 BY INDEX t1d (d=?) (~10 rows)} 457 + 0 0 0 {SEARCH TABLE t1 USING INDEX t1c (c=?) (~10 rows)} 458 + 0 0 0 {SEARCH TABLE t1 USING INDEX t1d (d=?) (~10 rows)} 459 459 } 460 460 461 461 # In contrast, b=1000 is preferred over any OR-clause. 462 462 # 463 463 do_execsql_test where9-5.2 { 464 464 EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b=1000 AND (c=31031 OR d IS NULL) 465 465 } { 466 - 0 0 0 {SCAN TABLE t1 BY INDEX t1b (b=?) (~5 rows)} 466 + 0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b=?) (~5 rows)} 467 467 } 468 468 469 469 # Likewise, inequalities in an AND are preferred over inequalities in 470 470 # an OR. 471 471 # 472 472 do_execsql_test where9-5.3 { 473 473 EXPLAIN QUERY PLAN SELECT a FROM t1 WHERE b>1000 AND (c>=31031 OR d IS NULL) 474 474 } { 475 - 0 0 0 {SCAN TABLE t1 BY INDEX t1b (b>?) (~165000 rows)} 475 + 0 0 0 {SEARCH TABLE t1 USING INDEX t1b (b>?) (~165000 rows)} 476 476 } 477 477 } 478 478 479 479 ############################################################################ 480 480 # Make sure OR-clauses work correctly on UPDATE and DELETE statements. 481 481 482 482 do_test where9-6.2.1 {