Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
| Comment: | Fix a bug in the EXPLAIN QUERY PLAN code. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | experimental |
| Files: | files | file ages | folders |
| SHA1: |
7ae068952fba4395b4aa437613a5ed2b |
| User & Date: | dan 2010-11-11 11:43:01 |
|
2010-11-11
| ||
| 16:46 | Add a row of EXPLAIN QUERY PLAN output for each composite select operation (UNION, EXCEPT etc.) in the query. (check-in: 00fb8468 user: dan tags: experimental) | |
| 11:43 | Fix a bug in the EXPLAIN QUERY PLAN code. (check-in: 7ae06895 user: dan tags: experimental) | |
| 10:36 | Modifications to test cases to account for new EXPLAIN QUERY PLAN output. (check-in: 30904ef8 user: dan tags: experimental) | |
Changes to src/where.c.
3154 3155 3156 3157 3158 3159 3160 3161 3162 3163 3164 3165 3166 3167 3168 3169 3170 3171 3172 3173 3174 3175 3176 3177 3178 3179 |
WherePlan *pPlan = &pLevel->plan;
Index *pIndex = pPlan->u.pIdx;
int nEq = pPlan->nEq;
char *zRet = 0;
int i;
for(i=0; i<nEq; i++){
char *zCol = pTab->aCol[pIndex->aiColumn[i]].zName;
zRet = sqlite3MAppendf(db, zRet,
"%s%s%s=?", (zRet?zRet:""), (zRet?" AND ":""), zCol);
}
if( pPlan->wsFlags&WHERE_BTM_LIMIT ){
zRet = sqlite3MAppendf(db, zRet,
"%s%s%s>?", (zRet?zRet:""), (zRet?" AND ":""), pTab->aCol[nEq].zName);
}
if( pPlan->wsFlags&WHERE_TOP_LIMIT ){
zRet = sqlite3MAppendf(db, zRet,
"%s%s%s<?", (zRet?zRet:""), (zRet?" AND ":""), pTab->aCol[nEq].zName);
}
if( zRet ){
zRet = sqlite3MAppendf(db, zRet, " (%s)", zRet);
}
return zRet;
|
< | > > | > > | > > |
3154 3155 3156 3157 3158 3159 3160 3161 3162 3163 3164 3165 3166 3167 3168 3169 3170 3171 3172 3173 3174 3175 3176 3177 3178 3179 3180 3181 3182 3183 3184 |
WherePlan *pPlan = &pLevel->plan;
Index *pIndex = pPlan->u.pIdx;
int nEq = pPlan->nEq;
char *zRet = 0;
int i;
for(i=0; i<nEq; i++){
zRet = sqlite3MAppendf(db, zRet,
"%s%s%s=?", (zRet?zRet:""), (zRet?" AND ":""),
pTab->aCol[pIndex->aiColumn[i]].zName
);
}
if( pPlan->wsFlags&WHERE_BTM_LIMIT ){
zRet = sqlite3MAppendf(db, zRet,
"%s%s%s>?", (zRet?zRet:""), (zRet?" AND ":""),
pTab->aCol[pIndex->aiColumn[i]].zName
);
}
if( pPlan->wsFlags&WHERE_TOP_LIMIT ){
zRet = sqlite3MAppendf(db, zRet,
"%s%s%s<?", (zRet?zRet:""), (zRet?" AND ":""),
pTab->aCol[pIndex->aiColumn[i]].zName
);
}
if( zRet ){
zRet = sqlite3MAppendf(db, zRet, " (%s)", zRet);
}
return zRet;
|
Changes to test/autoindex1.test.
237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 |
AND later.owner_change_date > prev.owner_change_date
AND later.owner_change_date <= s.date_of_registration||' 00:00:00')
) y ON x.sheep_no = y.sheep_no
WHERE y.sheep_no IS NULL
ORDER BY x.registering_flock;
} {
1 0 0 {SCAN TABLE sheep AS s (~1000000 rows)}
1 1 1 {SCAN TABLE flock_owner AS prev BY INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND flock_no<?) (~2 rows)}
1 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2}
2 0 0 {SCAN TABLE flock_owner AS later BY COVERING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND flock_no>? AND flock_no<?) (~1 rows)}
0 0 0 {SCAN TABLE sheep AS x BY INDEX sheep_reg_flock_index (~1000000 rows)}
0 1 1 {SCAN SUBQUERY 1 AS y BY AUTOMATIC COVERING INDEX (sheep_no=?) (~7 rows)}
}
finish_test
|
| | |
237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 |
AND later.owner_change_date > prev.owner_change_date
AND later.owner_change_date <= s.date_of_registration||' 00:00:00')
) y ON x.sheep_no = y.sheep_no
WHERE y.sheep_no IS NULL
ORDER BY x.registering_flock;
} {
1 0 0 {SCAN TABLE sheep AS s (~1000000 rows)}
1 1 1 {SCAN TABLE flock_owner AS prev BY INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date<?) (~2 rows)}
1 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2}
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)}
0 0 0 {SCAN TABLE sheep AS x BY INDEX sheep_reg_flock_index (~1000000 rows)}
0 1 1 {SCAN SUBQUERY 1 AS y BY AUTOMATIC COVERING INDEX (sheep_no=?) (~7 rows)}
}
finish_test
|
Changes to test/e_createtable.test.
1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 |
1 "EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 5"
{0 0 0 {SCAN TABLE t1 BY INDEX sqlite_autoindex_t1_1 (b=?) (~1 rows)}}
2 "EXPLAIN QUERY PLAN SELECT * FROM t2 ORDER BY b, c"
{0 0 0 {SCAN TABLE t2 BY INDEX sqlite_autoindex_t2_1 (~1000000 rows)}}
3 "EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE b=10 AND c>10"
{0 0 0 {SCAN TABLE t2 BY INDEX sqlite_autoindex_t2_1 (b=? AND b>?) (~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.
#
# All the tests that deal with CHECK constraints below (4.11.* and
|
| |
1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 |
1 "EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE b = 5"
{0 0 0 {SCAN TABLE t1 BY INDEX sqlite_autoindex_t1_1 (b=?) (~1 rows)}}
2 "EXPLAIN QUERY PLAN SELECT * FROM t2 ORDER BY b, c"
{0 0 0 {SCAN TABLE t2 BY INDEX sqlite_autoindex_t2_1 (~1000000 rows)}}
3 "EXPLAIN QUERY PLAN SELECT * FROM t2 WHERE b=10 AND c>10"
{0 0 0 {SCAN TABLE t2 BY 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.
#
# All the tests that deal with CHECK constraints below (4.11.* and
|
Changes to test/fts3fault.test.
10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
#***********************************************************************
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set ::testprefix fts3fault
# Test error handling in the sqlite3Fts3Init() function. This is the
# function that registers the FTS3 module and various support functions
# with SQLite.
#
do_faultsim_test 1 -body {
sqlite3 db test.db
|
> > > |
10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
#*********************************************************************** # set testdir [file dirname $argv0] source $testdir/tester.tcl set ::testprefix fts3fault # If SQLITE_ENABLE_FTS3 is not defined, omit this file. ifcapable !fts3 { finish_test ; return } # Test error handling in the sqlite3Fts3Init() function. This is the # function that registers the FTS3 module and various support functions # with SQLite. # do_faultsim_test 1 -body { sqlite3 db test.db |
Changes to test/where7.test.
23339 23340 23341 23342 23343 23344 23345 23346 23347 23348 23349 23350 |
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 c8>?) (~2 rows)}
0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}
finish_test
|
| |
23339 23340 23341 23342 23343 23344 23345 23346 23347 23348 23349 23350 |
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 0 {USE TEMP B-TREE FOR ORDER BY}
}
finish_test
|
Changes to test/where9.test.
468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 |
# 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 (a>?) (~165000 rows)}
}
}
############################################################################
# Make sure OR-clauses work correctly on UPDATE and DELETE statements.
do_test where9-6.2.1 {
|
| |
468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 |
# 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)}
}
}
############################################################################
# Make sure OR-clauses work correctly on UPDATE and DELETE statements.
do_test where9-6.2.1 {
|