Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | A proposed fix for the DESC ORDER BY bug of ticket [4dd95f6943fbd18]. Seems to work, but lots more testing is needed prior to moving to trunk. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | desc-orderby-fix-1 |
Files: | files | file ages | folders |
SHA1: |
614a038ad2fe111687d2bc1c0aa716d7 |
User & Date: | drh 2013-03-12 18:34:50.356 |
Context
2013-03-12
| ||
18:40 | Revised fix for the DESC ORDER BY and IN constraint bug, ticket [4dd95f6943fbd18]. The previous check-in was incorrect. (check-in: 8b2eb7a43b user: drh tags: desc-orderby-fix-1) | |
18:34 | A proposed fix for the DESC ORDER BY bug of ticket [4dd95f6943fbd18]. Seems to work, but lots more testing is needed prior to moving to trunk. (check-in: 614a038ad2 user: drh tags: desc-orderby-fix-1) | |
2013-03-11
| ||
13:37 | Define _BSD_SOURCE when building on OpenBSD. This is required for fchmod(). (check-in: f9027cb47b user: dan tags: trunk) | |
Changes
Changes to src/sqliteInt.h.
︙ | ︙ | |||
1927 1928 1929 1930 1931 1932 1933 1934 1935 1936 1937 1938 1939 1940 | ** is only used when wsFlags&WHERE_VIRTUALTABLE is true. It is never the ** case that more than one of these conditions is true. */ struct WherePlan { u32 wsFlags; /* WHERE_* flags that describe the strategy */ u16 nEq; /* Number of == constraints */ u16 nOBSat; /* Number of ORDER BY terms satisfied */ double nRow; /* Estimated number of rows (for EQP) */ union { Index *pIdx; /* Index when WHERE_INDEXED is true */ struct WhereTerm *pTerm; /* WHERE clause term for OR-search */ sqlite3_index_info *pVtabIdx; /* Virtual table index to use */ } u; }; | > | 1927 1928 1929 1930 1931 1932 1933 1934 1935 1936 1937 1938 1939 1940 1941 | ** is only used when wsFlags&WHERE_VIRTUALTABLE is true. It is never the ** case that more than one of these conditions is true. */ struct WherePlan { u32 wsFlags; /* WHERE_* flags that describe the strategy */ u16 nEq; /* Number of == constraints */ u16 nOBSat; /* Number of ORDER BY terms satisfied */ u16 iOBSat; /* First ORDER BY term satisfied */ double nRow; /* Estimated number of rows (for EQP) */ union { Index *pIdx; /* Index when WHERE_INDEXED is true */ struct WhereTerm *pTerm; /* WHERE clause term for OR-search */ sqlite3_index_info *pVtabIdx; /* Virtual table index to use */ } u; }; |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
1830 1831 1832 1833 1834 1835 1836 1837 1838 1839 1840 1841 1842 1843 | ** of pCost. */ WHERETRACE(("... multi-index OR cost=%.9g nrow=%.9g\n", rTotal, nRow)); if( rTotal<p->cost.rCost ){ p->cost.rCost = rTotal; p->cost.used = used; p->cost.plan.nRow = nRow; p->cost.plan.nOBSat = p->i ? p->aLevel[p->i-1].plan.nOBSat : 0; p->cost.plan.wsFlags = flags; p->cost.plan.u.pTerm = pTerm; } } } #endif /* SQLITE_OMIT_OR_OPTIMIZATION */ } | > | 1830 1831 1832 1833 1834 1835 1836 1837 1838 1839 1840 1841 1842 1843 1844 | ** of pCost. */ WHERETRACE(("... multi-index OR cost=%.9g nrow=%.9g\n", rTotal, nRow)); if( rTotal<p->cost.rCost ){ p->cost.rCost = rTotal; p->cost.used = used; p->cost.plan.nRow = nRow; p->cost.plan.nOBSat = p->i ? p->aLevel[p->i-1].plan.nOBSat : 0; p->cost.plan.iOBSat = p->cost.plan.nOBSat; p->cost.plan.wsFlags = flags; p->cost.plan.u.pTerm = pTerm; } } } #endif /* SQLITE_OMIT_OR_OPTIMIZATION */ } |
︙ | ︙ | |||
3771 3772 3773 3774 3775 3776 3777 | ** For a constraint of the form X=expr, the expression is evaluated and its ** result is left on the stack. For constraints of the form X IN (...) ** this routine sets up a loop that will iterate over all values of X. */ static int codeEqualityTerm( Parse *pParse, /* The parsing context */ WhereTerm *pTerm, /* The term of the WHERE clause to be coded */ | | > > > | > > > > | > | 3772 3773 3774 3775 3776 3777 3778 3779 3780 3781 3782 3783 3784 3785 3786 3787 3788 3789 3790 3791 3792 3793 3794 3795 3796 3797 3798 3799 3800 3801 3802 3803 3804 3805 3806 3807 3808 3809 3810 3811 3812 3813 | ** For a constraint of the form X=expr, the expression is evaluated and its ** result is left on the stack. For constraints of the form X IN (...) ** this routine sets up a loop that will iterate over all values of X. */ static int codeEqualityTerm( Parse *pParse, /* The parsing context */ WhereTerm *pTerm, /* The term of the WHERE clause to be coded */ WhereLevel *pLevel, /* The level of the FROM clause we are working on */ int iEq, /* Index of the equality term within this level */ int iTarget /* Attempt to leave results in this register */ ){ Expr *pX = pTerm->pExpr; Vdbe *v = pParse->pVdbe; int iReg; /* Register holding results */ assert( iTarget>0 ); if( pX->op==TK_EQ ){ iReg = sqlite3ExprCodeTarget(pParse, pX->pRight, iTarget); }else if( pX->op==TK_ISNULL ){ iReg = iTarget; sqlite3VdbeAddOp2(v, OP_Null, 0, iReg); #ifndef SQLITE_OMIT_SUBQUERY }else{ int eType; int iTab; struct InLoop *pIn; u8 bRev; if( (pLevel->plan.wsFlags & WHERE_INDEXED)!=0 ){ Index *pIdx = pLevel->plan.u.pIdx; bRev = pIdx->aSortOrder[iEq+pLevel->plan.iOBSat]; }else{ bRev = 0; } if( pLevel->plan.wsFlags & WHERE_REVERSE ) bRev = 1 - bRev; assert( pX->op==TK_IN ); iReg = iTarget; eType = sqlite3FindInIndex(pParse, pX, 0); iTab = pX->iTable; sqlite3VdbeAddOp2(v, bRev ? OP_Last : OP_Rewind, iTab, 0); assert( pLevel->plan.wsFlags & WHERE_IN_ABLE ); if( pLevel->u.in.nIn==0 ){ |
︙ | ︙ | |||
3908 3909 3910 3911 3912 3913 3914 | int k = pIdx->aiColumn[j]; pTerm = findTerm(pWC, iCur, k, notReady, pLevel->plan.wsFlags, pIdx); if( pTerm==0 ) break; /* The following true for indices with redundant columns. ** Ex: CREATE INDEX i1 ON t1(a,b,a); SELECT * FROM t1 WHERE a=0 AND b=0; */ testcase( (pTerm->wtFlags & TERM_CODED)!=0 ); testcase( pTerm->wtFlags & TERM_VIRTUAL ); /* EV: R-30575-11662 */ | | | 3917 3918 3919 3920 3921 3922 3923 3924 3925 3926 3927 3928 3929 3930 3931 | int k = pIdx->aiColumn[j]; pTerm = findTerm(pWC, iCur, k, notReady, pLevel->plan.wsFlags, pIdx); if( pTerm==0 ) break; /* The following true for indices with redundant columns. ** Ex: CREATE INDEX i1 ON t1(a,b,a); SELECT * FROM t1 WHERE a=0 AND b=0; */ testcase( (pTerm->wtFlags & TERM_CODED)!=0 ); testcase( pTerm->wtFlags & TERM_VIRTUAL ); /* EV: R-30575-11662 */ r1 = codeEqualityTerm(pParse, pTerm, pLevel, j, regBase+j); if( r1!=regBase+j ){ if( nReg==1 ){ sqlite3ReleaseTempReg(pParse, regBase); regBase = r1; }else{ sqlite3VdbeAddOp2(v, OP_SCopy, r1, regBase+j); } |
︙ | ︙ | |||
4185 4186 4187 4188 4189 4190 4191 | addrNotFound = pLevel->addrBrk; for(j=1; j<=nConstraint; j++){ for(k=0; k<nConstraint; k++){ if( aUsage[k].argvIndex==j ){ int iTarget = iReg+j+1; pTerm = &pWC->a[aConstraint[k].iTermOffset]; if( pTerm->eOperator & WO_IN ){ | | | 4194 4195 4196 4197 4198 4199 4200 4201 4202 4203 4204 4205 4206 4207 4208 | addrNotFound = pLevel->addrBrk; for(j=1; j<=nConstraint; j++){ for(k=0; k<nConstraint; k++){ if( aUsage[k].argvIndex==j ){ int iTarget = iReg+j+1; pTerm = &pWC->a[aConstraint[k].iTermOffset]; if( pTerm->eOperator & WO_IN ){ codeEqualityTerm(pParse, pTerm, pLevel, k, iTarget); addrNotFound = pLevel->addrNxt; }else{ sqlite3ExprCode(pParse, pTerm->pExpr->pRight, iTarget); } break; } } |
︙ | ︙ | |||
4226 4227 4228 4229 4230 4231 4232 | */ iReleaseReg = sqlite3GetTempReg(pParse); pTerm = findTerm(pWC, iCur, -1, notReady, WO_EQ|WO_IN, 0); assert( pTerm!=0 ); assert( pTerm->pExpr!=0 ); assert( omitTable==0 ); testcase( pTerm->wtFlags & TERM_VIRTUAL ); /* EV: R-30575-11662 */ | | | 4235 4236 4237 4238 4239 4240 4241 4242 4243 4244 4245 4246 4247 4248 4249 | */ iReleaseReg = sqlite3GetTempReg(pParse); pTerm = findTerm(pWC, iCur, -1, notReady, WO_EQ|WO_IN, 0); assert( pTerm!=0 ); assert( pTerm->pExpr!=0 ); assert( omitTable==0 ); testcase( pTerm->wtFlags & TERM_VIRTUAL ); /* EV: R-30575-11662 */ iRowidReg = codeEqualityTerm(pParse, pTerm, pLevel, 0, iReleaseReg); addrNxt = pLevel->addrNxt; sqlite3VdbeAddOp2(v, OP_MustBeInt, iRowidReg, addrNxt); sqlite3VdbeAddOp3(v, OP_NotExists, iCur, addrNxt, iRowidReg); sqlite3ExprCacheAffinityChange(pParse, iRowidReg, 1); sqlite3ExprCacheStore(pParse, iCur, -1, iRowidReg); VdbeComment((v, "pk")); pLevel->op = OP_Noop; |
︙ | ︙ |