Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Ignore IS NOT NULL and NOT NULL constraints on NOT NULL columns. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: | e476408e3c5ba5f3ba5e98ff264167c163d72e3f |
| User & Date: | drh 2013-08-20 17:00:55 |
Context
|
2013-08-20
| ||
| 17:14 | Fix an invalid assert() in where.c. Also a crash that can occur in the EXPLAIN QUERY PLAN code under obscure circumstances. check-in: ef192abb user: dan tags: trunk | |
| 17:00 | Ignore IS NOT NULL and NOT NULL constraints on NOT NULL columns. check-in: e476408e user: drh tags: trunk | |
| 16:08 | Test script changes to support testing sqlite3_interrupt(). check-in: 0cede9f8 user: dan tags: trunk | |
Changes
Changes to src/where.c.
4332 4333 4334 4335 4336 4337 4338 4339 4340 4341 4342 4343 4344 4345 4346 4347 4348 4349 4350 4351 4352 |
saved_prereq = pNew->prereq;
saved_nOut = pNew->nOut;
pNew->rSetup = 0;
rLogSize = estLog(whereCost(pProbe->aiRowEst[0]));
for(; rc==SQLITE_OK && pTerm!=0; pTerm = whereScanNext(&scan)){
int nIn = 0;
if( pTerm->prereqRight & pNew->maskSelf ) continue;
#ifdef SQLITE_ENABLE_STAT3
if( (pTerm->wtFlags & TERM_VNULL)!=0
&& (iCol<0 || pSrc->pTab->aCol[iCol].notNull)
){
continue; /* skip IS NOT NULL constraints on a NOT NULL column */
}
#endif
pNew->wsFlags = saved_wsFlags;
pNew->u.btree.nEq = saved_nEq;
pNew->nLTerm = saved_nLTerm;
if( whereLoopResize(db, pNew, pNew->nLTerm+1) ) break; /* OOM */
pNew->aLTerm[pNew->nLTerm++] = pTerm;
pNew->prereq = (saved_prereq | pTerm->prereqRight) & ~pNew->maskSelf;
pNew->rRun = rLogSize; /* Baseline cost is log2(N). Adjustments below */
|
| < < > < |
4332 4333 4334 4335 4336 4337 4338 4339 4340 4341 4342 4343 4344 4345 4346 4347 4348 4349 4350 |
saved_prereq = pNew->prereq;
saved_nOut = pNew->nOut;
pNew->rSetup = 0;
rLogSize = estLog(whereCost(pProbe->aiRowEst[0]));
for(; rc==SQLITE_OK && pTerm!=0; pTerm = whereScanNext(&scan)){
int nIn = 0;
if( pTerm->prereqRight & pNew->maskSelf ) continue;
if( (pTerm->eOperator==WO_ISNULL || (pTerm->wtFlags&TERM_VNULL)!=0)
&& (iCol<0 || pSrc->pTab->aCol[iCol].notNull)
){
continue; /* ignore IS [NOT] NULL constraints on NOT NULL columns */
}
pNew->wsFlags = saved_wsFlags;
pNew->u.btree.nEq = saved_nEq;
pNew->nLTerm = saved_nLTerm;
if( whereLoopResize(db, pNew, pNew->nLTerm+1) ) break; /* OOM */
pNew->aLTerm[pNew->nLTerm++] = pTerm;
pNew->prereq = (saved_prereq | pTerm->prereqRight) & ~pNew->maskSelf;
pNew->rRun = rLogSize; /* Baseline cost is log2(N). Adjustments below */
|
Changes to test/where3.test.
242 243 244 245 246 247 248 249 250 251 252 253 254 255 |
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 1 0 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?)}
}
if 0 { # Query planner no longer does this
# 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.
#
do_execsql_test where3-4.0 {
|
> > > > > > |
242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 |
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 1 0 {SEARCH TABLE t301 USING INTEGER PRIMARY KEY (rowid=?)}
}
do_execsql_test where3-3.2 {
SELECT * FROM t301 WHERE c=3 AND a IS NULL;
} {}
do_execsql_test where3-3.3 {
SELECT * FROM t301 WHERE c=3 AND a IS NOT NULL;
} {1 2 3}
if 0 { # Query planner no longer does this
# 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.
#
do_execsql_test where3-4.0 {
|
Changes to test/whereA.test.
64 65 66 67 68 69 70 71 72 73 74 75 76 77 |
sqlite3 db test.db
db eval {
PRAGMA reverse_unordered_selects=1;
VACUUM;
SELECT * FROM t1;
}
} {3 4.53 {} 2 hello world 1 2 3}
do_test whereA-2.1 {
db eval {
PRAGMA reverse_unordered_selects=0;
SELECT * FROM t1 WHERE a>0;
}
} {1 2 3 2 hello world 3 4.53 {}}
|
> > > > > > |
64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 |
sqlite3 db test.db
db eval {
PRAGMA reverse_unordered_selects=1;
VACUUM;
SELECT * FROM t1;
}
} {3 4.53 {} 2 hello world 1 2 3}
do_execsql_test whereA-1.8 {
SELECT * FROM t1 WHERE b=2 AND a IS NULL;
} {}
do_execsql_test whereA-1.9 {
SELECT * FROM t1 WHERE b=2 AND a IS NOT NULL;
} {1 2 3}
do_test whereA-2.1 {
db eval {
PRAGMA reverse_unordered_selects=0;
SELECT * FROM t1 WHERE a>0;
}
} {1 2 3 2 hello world 3 4.53 {}}
|