Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Do not assume that "col IS ?" matches at most a single row of a UNIQUE column unless the column is also NOT NULL. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
e038ce8955e785afcc07bb22499955bb |
| User & Date: | dan 2015-05-15 19:59:23 |
Context
|
2015-05-15
| ||
| 20:14 | Minor coding style change, adjust new local variable casing for consistency. (check-in: 1a4628c6 user: mistachkin tags: trunk) | |
| 19:59 | Do not assume that "col IS ?" matches at most a single row of a UNIQUE column unless the column is also NOT NULL. (check-in: e038ce89 user: dan tags: trunk) | |
| 04:13 | Simplifications to error message processing. Fix a possible problem in error message formatting when vacuuming a database with a corrupt schema. (check-in: 56ef98a0 user: drh tags: trunk) | |
Changes
Changes to src/where.c.
| ︙ | ︙ | |||
6326 6327 6328 6329 6330 6331 6332 6333 6334 6335 6336 6337 6338 |
pLoop->aLTerm[0] = pTerm;
pLoop->nLTerm = 1;
pLoop->u.btree.nEq = 1;
/* TUNING: Cost of a rowid lookup is 10 */
pLoop->rRun = 33; /* 33==sqlite3LogEst(10) */
}else{
for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
assert( pLoop->aLTermSpace==pLoop->aLTerm );
if( !IsUniqueIndex(pIdx)
|| pIdx->pPartIdxWhere!=0
|| pIdx->nKeyCol>ArraySize(pLoop->aLTermSpace)
) continue;
for(j=0; j<pIdx->nKeyCol; j++){
| > > | | | 6326 6327 6328 6329 6330 6331 6332 6333 6334 6335 6336 6337 6338 6339 6340 6341 6342 6343 6344 6345 6346 6347 6348 6349 6350 |
pLoop->aLTerm[0] = pTerm;
pLoop->nLTerm = 1;
pLoop->u.btree.nEq = 1;
/* TUNING: Cost of a rowid lookup is 10 */
pLoop->rRun = 33; /* 33==sqlite3LogEst(10) */
}else{
for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
int opmask;
assert( pLoop->aLTermSpace==pLoop->aLTerm );
if( !IsUniqueIndex(pIdx)
|| pIdx->pPartIdxWhere!=0
|| pIdx->nKeyCol>ArraySize(pLoop->aLTermSpace)
) continue;
opmask = pIdx->uniqNotNull ? (WO_EQ|WO_IS) : WO_EQ;
for(j=0; j<pIdx->nKeyCol; j++){
pTerm = findTerm(pWC, iCur, pIdx->aiColumn[j], 0, opmask, pIdx);
if( pTerm==0 ) break;
testcase( pTerm->eOperator & WO_IS );
pLoop->aLTerm[j] = pTerm;
}
if( j!=pIdx->nKeyCol ) continue;
pLoop->wsFlags = WHERE_COLUMN_EQ|WHERE_ONEROW|WHERE_INDEXED;
if( pIdx->isCovering || (pItem->colUsed & ~columnsInIndex(pIdx))==0 ){
pLoop->wsFlags |= WHERE_IDX_ONLY;
}
|
| ︙ | ︙ |
Changes to test/where4.test.
| ︙ | ︙ | |||
15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
# that IS NULL phrases are correctly optimized. But you can never
# have too many tests, so some other tests are thrown in as well.
#
# $Id: where4.test,v 1.6 2007/12/10 05:03:48 danielk1977 Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl
ifcapable !tclvar||!bloblit {
finish_test
return
}
# Build some test data
| > | 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
# that IS NULL phrases are correctly optimized. But you can never
# have too many tests, so some other tests are thrown in as well.
#
# $Id: where4.test,v 1.6 2007/12/10 05:03:48 danielk1977 Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix where4
ifcapable !tclvar||!bloblit {
finish_test
return
}
# Build some test data
|
| ︙ | ︙ | |||
280 281 282 283 284 285 286 287 288 |
SELECT sum((
SELECT d FROM t8 WHERE a = i AND b = i AND c < NULL
)) FROM t7;
}
} {{}}
}; #ifcapable subquery
finish_test
| > > > > > > > > > > > > > > > > > > | 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 |
SELECT sum((
SELECT d FROM t8 WHERE a = i AND b = i AND c < NULL
)) FROM t7;
}
} {{}}
}; #ifcapable subquery
#-------------------------------------------------------------------------
# Verify that "IS ?" with a NULL bound to the variable also functions
# correctly.
unset -nocomplain null
do_execsql_test 8.1 {
CREATE TABLE u9(a UNIQUE, b);
INSERT INTO u9 VALUES(NULL, 1);
INSERT INTO u9 VALUES(NULL, 2);
}
do_execsql_test 8.2 { SELECT * FROM u9 WHERE a IS NULL } {{} 1 {} 2}
do_execsql_test 8.2 { SELECT * FROM u9 WHERE a IS $null } {{} 1 {} 2}
finish_test
|