Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix an adverse interaction between the IS NOT NULL optimization (available only with SQLITE_ENABLE_STAT3) and the transitive constraint processing. Fix for ticket [d805526eae253] |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: | 3b30b75b342bb6b424ad2bf7cd841b2c |
| User & Date: | drh 2013-07-08 21:12:57 |
Context
|
2013-07-08
| ||
| 22:33 | Make sure an adequate number of digits are shown for binary-to-text rendering of very small floating point values. check-in: 776e65f9 user: drh tags: trunk | |
| 21:12 | Fix an adverse interaction between the IS NOT NULL optimization (available only with SQLITE_ENABLE_STAT3) and the transitive constraint processing. Fix for ticket [d805526eae253] check-in: 3b30b75b user: drh tags: trunk | |
| 01:27 | Add an optional 5th parameter to the next_char() function that is the collating sequence to use for comparison. check-in: 9415db6e user: drh tags: trunk | |
Changes
Changes to src/where.c.
3331
3332
3333
3334
3335
3336
3337
3338
3339
3340
3341
3342
3343
3344
3345
3346
3347
3348
3349
3350
3351
3352
3353
3354
3355
3356
3357
3358
3359
3360
3361
3362
....
4278
4279
4280
4281
4282
4283
4284
4285
4286
4287
4288
4289
4290
4291
|
/* TK_LT */ OP_SeekLt,
/* TK_GE */ OP_SeekGe
};
assert( TK_LE==TK_GT+1 ); /* Make sure the ordering.. */
assert( TK_LT==TK_GT+2 ); /* ... of the TK_xx values... */
assert( TK_GE==TK_GT+3 ); /* ... is correcct. */
testcase( pStart->wtFlags & TERM_VIRTUAL ); /* EV: R-30575-11662 */
pX = pStart->pExpr;
assert( pX!=0 );
assert( pStart->leftCursor==iCur );
r1 = sqlite3ExprCodeTemp(pParse, pX->pRight, &rTemp);
sqlite3VdbeAddOp3(v, aMoveOp[pX->op-TK_GT], iCur, addrBrk, r1);
VdbeComment((v, "pk"));
sqlite3ExprCacheAffinityChange(pParse, r1, 1);
sqlite3ReleaseTempReg(pParse, rTemp);
disableTerm(pLevel, pStart);
}else{
sqlite3VdbeAddOp2(v, bRev ? OP_Last : OP_Rewind, iCur, addrBrk);
}
if( pEnd ){
Expr *pX;
pX = pEnd->pExpr;
assert( pX!=0 );
assert( pEnd->leftCursor==iCur );
testcase( pEnd->wtFlags & TERM_VIRTUAL ); /* EV: R-30575-11662 */
memEndValue = ++pParse->nMem;
sqlite3ExprCode(pParse, pX->pRight, memEndValue);
if( pX->op==TK_LT || pX->op==TK_GT ){
testOp = bRev ? OP_Le : OP_Ge;
}else{
testOp = bRev ? OP_Lt : OP_Gt;
................................................................................
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;
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 */
|
>
|
>
|
>
>
>
>
>
|
3331
3332
3333
3334
3335
3336
3337
3338
3339
3340
3341
3342
3343
3344
3345
3346
3347
3348
3349
3350
3351
3352
3353
3354
3355
3356
3357
3358
3359
3360
3361
3362
3363
3364
....
4280
4281
4282
4283
4284
4285
4286
4287
4288
4289
4290
4291
4292
4293
4294
4295
4296
4297
4298
|
/* TK_LT */ OP_SeekLt,
/* TK_GE */ OP_SeekGe
};
assert( TK_LE==TK_GT+1 ); /* Make sure the ordering.. */
assert( TK_LT==TK_GT+2 ); /* ... of the TK_xx values... */
assert( TK_GE==TK_GT+3 ); /* ... is correcct. */
assert( (pStart->wtFlags & TERM_VNULL)==0 );
testcase( pStart->wtFlags & TERM_VIRTUAL ); /* EV: R-30575-11662 */
pX = pStart->pExpr;
assert( pX!=0 );
testcase( pStart->leftCursor!=iCur ); /* transitive constraints */
r1 = sqlite3ExprCodeTemp(pParse, pX->pRight, &rTemp);
sqlite3VdbeAddOp3(v, aMoveOp[pX->op-TK_GT], iCur, addrBrk, r1);
VdbeComment((v, "pk"));
sqlite3ExprCacheAffinityChange(pParse, r1, 1);
sqlite3ReleaseTempReg(pParse, rTemp);
disableTerm(pLevel, pStart);
}else{
sqlite3VdbeAddOp2(v, bRev ? OP_Last : OP_Rewind, iCur, addrBrk);
}
if( pEnd ){
Expr *pX;
pX = pEnd->pExpr;
assert( pX!=0 );
assert( (pEnd->wtFlags & TERM_VNULL)==0 );
testcase( pEnd->leftCursor!=iCur ); /* Transitive constraints */
testcase( pEnd->wtFlags & TERM_VIRTUAL ); /* EV: R-30575-11662 */
memEndValue = ++pParse->nMem;
sqlite3ExprCode(pParse, pX->pRight, memEndValue);
if( pX->op==TK_LT || pX->op==TK_GT ){
testOp = bRev ? OP_Le : OP_Ge;
}else{
testOp = bRev ? OP_Lt : OP_Gt;
................................................................................
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 && 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 */
|
Changes to test/transitive1.test.
42 43 44 45 46 47 48 49 50 |
} {20 20 20}
do_execsql_test transitive1-210 {
SELECT * FROM t2 WHERE a=b AND c=b AND c>=20 ORDER BY +a;
} {3 3 3 20 20 20}
do_execsql_test transitive1-220 {
SELECT * FROM t2 WHERE a=b AND c=b AND c<=20 ORDER BY +a;
} {20 20 20 100 100 100}
finish_test
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 |
} {20 20 20}
do_execsql_test transitive1-210 {
SELECT * FROM t2 WHERE a=b AND c=b AND c>=20 ORDER BY +a;
} {3 3 3 20 20 20}
do_execsql_test transitive1-220 {
SELECT * FROM t2 WHERE a=b AND c=b AND c<=20 ORDER BY +a;
} {20 20 20 100 100 100}
# Test cases for ticket [[d805526eae253103] 2013-07-08
# "Incorrect join result or assertion fault due to transitive constraints"
#
do_execsql_test transitive1-300 {
CREATE TABLE t301(w INTEGER PRIMARY KEY, x);
CREATE TABLE t302(y INTEGER UNIQUE, z);
INSERT INTO t301 VALUES(1,2),(3,4),(5,6);
INSERT INTO t302 VALUES(1,3),(3,6),(5,7);
SELECT *
FROM t301 CROSS JOIN t302
WHERE w=y AND y IS NOT NULL
ORDER BY +w;
} {1 2 1 3 3 4 3 6 5 6 5 7}
do_execsql_test transitive1-301 {
SELECT *
FROM t301 CROSS JOIN t302
WHERE w=y AND y IS NOT NULL
ORDER BY w;
} {1 2 1 3 3 4 3 6 5 6 5 7}
do_execsql_test transitive1-310 {
SELECT *
FROM t301 CROSS JOIN t302 ON w=y
WHERE y>1
ORDER BY +w
} {3 4 3 6 5 6 5 7}
do_execsql_test transitive1-311 {
SELECT *
FROM t301 CROSS JOIN t302 ON w=y
WHERE y>1
ORDER BY w
} {3 4 3 6 5 6 5 7}
do_execsql_test transitive1-312 {
SELECT *
FROM t301 CROSS JOIN t302 ON w=y
WHERE y>1
ORDER BY w DESC
} {5 6 5 7 3 4 3 6}
do_execsql_test transitive1-320 {
SELECT *
FROM t301 CROSS JOIN t302 ON w=y
WHERE y BETWEEN 2 AND 4;
} {3 4 3 6}
do_execsql_test transitive1-331 {
SELECT *
FROM t301 CROSS JOIN t302 ON w=y
WHERE y BETWEEN 1 AND 4
ORDER BY w;
} {1 2 1 3 3 4 3 6}
do_execsql_test transitive1-332 {
SELECT *
FROM t301 CROSS JOIN t302 ON w=y
WHERE y BETWEEN 1 AND 4
ORDER BY w DESC;
} {3 4 3 6 1 2 1 3}
finish_test
|