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 |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
3b30b75b342bb6b424ad2bf7cd841b2c |
User & Date: | drh 2013-07-08 21:12:57.346 |
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: 776e65f98c 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: 3b30b75b34 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: 9415db6ef2 user: drh tags: trunk) | |
Changes
Changes to src/where.c.
︙ | ︙ | |||
3331 3332 3333 3334 3335 3336 3337 3338 3339 3340 | /* 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 ); | > | > | | 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 | /* 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; |
︙ | ︙ | |||
4278 4279 4280 4281 4282 4283 4284 4285 4286 4287 4288 4289 4290 4291 | 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 */ | > > > > > | 4280 4281 4282 4283 4284 4285 4286 4287 4288 4289 4290 4291 4292 4293 4294 4295 4296 4297 4298 | 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 |