/ Check-in [614a038a]
Login

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 | SQL archive
Timelines: family | ancestors | descendants | both | desc-orderby-fix-1
Files: files | file ages | folders
SHA1: 614a038ad2fe111687d2bc1c0aa716d7eb8fdd7c
User & Date: drh 2013-03-12 18:34:50
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: 8b2eb7a4 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: 614a038a 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: f9027cb4 user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/sqliteInt.h.

  1927   1927   ** is only used when wsFlags&WHERE_VIRTUALTABLE is true.  It is never the
  1928   1928   ** case that more than one of these conditions is true.
  1929   1929   */
  1930   1930   struct WherePlan {
  1931   1931     u32 wsFlags;                   /* WHERE_* flags that describe the strategy */
  1932   1932     u16 nEq;                       /* Number of == constraints */
  1933   1933     u16 nOBSat;                    /* Number of ORDER BY terms satisfied */
         1934  +  u16 iOBSat;                    /* First ORDER BY term satisfied */
  1934   1935     double nRow;                   /* Estimated number of rows (for EQP) */
  1935   1936     union {
  1936   1937       Index *pIdx;                   /* Index when WHERE_INDEXED is true */
  1937   1938       struct WhereTerm *pTerm;       /* WHERE clause term for OR-search */
  1938   1939       sqlite3_index_info *pVtabIdx;  /* Virtual table index to use */
  1939   1940     } u;
  1940   1941   };

Changes to src/where.c.

  1830   1830         ** of pCost. */
  1831   1831         WHERETRACE(("... multi-index OR cost=%.9g nrow=%.9g\n", rTotal, nRow));
  1832   1832         if( rTotal<p->cost.rCost ){
  1833   1833           p->cost.rCost = rTotal;
  1834   1834           p->cost.used = used;
  1835   1835           p->cost.plan.nRow = nRow;
  1836   1836           p->cost.plan.nOBSat = p->i ? p->aLevel[p->i-1].plan.nOBSat : 0;
         1837  +        p->cost.plan.iOBSat = p->cost.plan.nOBSat;
  1837   1838           p->cost.plan.wsFlags = flags;
  1838   1839           p->cost.plan.u.pTerm = pTerm;
  1839   1840         }
  1840   1841       }
  1841   1842     }
  1842   1843   #endif /* SQLITE_OMIT_OR_OPTIMIZATION */
  1843   1844   }
................................................................................
  3771   3772   ** For a constraint of the form X=expr, the expression is evaluated and its
  3772   3773   ** result is left on the stack.  For constraints of the form X IN (...)
  3773   3774   ** this routine sets up a loop that will iterate over all values of X.
  3774   3775   */
  3775   3776   static int codeEqualityTerm(
  3776   3777     Parse *pParse,      /* The parsing context */
  3777   3778     WhereTerm *pTerm,   /* The term of the WHERE clause to be coded */
  3778         -  WhereLevel *pLevel, /* When level of the FROM clause we are working on */
         3779  +  WhereLevel *pLevel, /* The level of the FROM clause we are working on */
         3780  +  int iEq,            /* Index of the equality term within this level */
  3779   3781     int iTarget         /* Attempt to leave results in this register */
  3780   3782   ){
  3781   3783     Expr *pX = pTerm->pExpr;
  3782   3784     Vdbe *v = pParse->pVdbe;
  3783   3785     int iReg;                  /* Register holding results */
  3784   3786   
  3785   3787     assert( iTarget>0 );
................................................................................
  3789   3791       iReg = iTarget;
  3790   3792       sqlite3VdbeAddOp2(v, OP_Null, 0, iReg);
  3791   3793   #ifndef SQLITE_OMIT_SUBQUERY
  3792   3794     }else{
  3793   3795       int eType;
  3794   3796       int iTab;
  3795   3797       struct InLoop *pIn;
  3796         -    u8 bRev = (pLevel->plan.wsFlags & WHERE_REVERSE)!=0;
         3798  +    u8 bRev;
  3797   3799   
         3800  +    if( (pLevel->plan.wsFlags & WHERE_INDEXED)!=0 ){
         3801  +      Index *pIdx = pLevel->plan.u.pIdx;
         3802  +      bRev = pIdx->aSortOrder[iEq+pLevel->plan.iOBSat];
         3803  +    }else{
         3804  +      bRev = 0;
         3805  +    }
         3806  +    if( pLevel->plan.wsFlags & WHERE_REVERSE ) bRev = 1 - bRev;
  3798   3807       assert( pX->op==TK_IN );
  3799   3808       iReg = iTarget;
  3800   3809       eType = sqlite3FindInIndex(pParse, pX, 0);
  3801   3810       iTab = pX->iTable;
  3802   3811       sqlite3VdbeAddOp2(v, bRev ? OP_Last : OP_Rewind, iTab, 0);
  3803   3812       assert( pLevel->plan.wsFlags & WHERE_IN_ABLE );
  3804   3813       if( pLevel->u.in.nIn==0 ){
................................................................................
  3908   3917       int k = pIdx->aiColumn[j];
  3909   3918       pTerm = findTerm(pWC, iCur, k, notReady, pLevel->plan.wsFlags, pIdx);
  3910   3919       if( pTerm==0 ) break;
  3911   3920       /* The following true for indices with redundant columns. 
  3912   3921       ** Ex: CREATE INDEX i1 ON t1(a,b,a); SELECT * FROM t1 WHERE a=0 AND b=0; */
  3913   3922       testcase( (pTerm->wtFlags & TERM_CODED)!=0 );
  3914   3923       testcase( pTerm->wtFlags & TERM_VIRTUAL ); /* EV: R-30575-11662 */
  3915         -    r1 = codeEqualityTerm(pParse, pTerm, pLevel, regBase+j);
         3924  +    r1 = codeEqualityTerm(pParse, pTerm, pLevel, j, regBase+j);
  3916   3925       if( r1!=regBase+j ){
  3917   3926         if( nReg==1 ){
  3918   3927           sqlite3ReleaseTempReg(pParse, regBase);
  3919   3928           regBase = r1;
  3920   3929         }else{
  3921   3930           sqlite3VdbeAddOp2(v, OP_SCopy, r1, regBase+j);
  3922   3931         }
................................................................................
  4185   4194       addrNotFound = pLevel->addrBrk;
  4186   4195       for(j=1; j<=nConstraint; j++){
  4187   4196         for(k=0; k<nConstraint; k++){
  4188   4197           if( aUsage[k].argvIndex==j ){
  4189   4198             int iTarget = iReg+j+1;
  4190   4199             pTerm = &pWC->a[aConstraint[k].iTermOffset];
  4191   4200             if( pTerm->eOperator & WO_IN ){
  4192         -            codeEqualityTerm(pParse, pTerm, pLevel, iTarget);
         4201  +            codeEqualityTerm(pParse, pTerm, pLevel, k, iTarget);
  4193   4202               addrNotFound = pLevel->addrNxt;
  4194   4203             }else{
  4195   4204               sqlite3ExprCode(pParse, pTerm->pExpr->pRight, iTarget);
  4196   4205             }
  4197   4206             break;
  4198   4207           }
  4199   4208         }
................................................................................
  4226   4235       */
  4227   4236       iReleaseReg = sqlite3GetTempReg(pParse);
  4228   4237       pTerm = findTerm(pWC, iCur, -1, notReady, WO_EQ|WO_IN, 0);
  4229   4238       assert( pTerm!=0 );
  4230   4239       assert( pTerm->pExpr!=0 );
  4231   4240       assert( omitTable==0 );
  4232   4241       testcase( pTerm->wtFlags & TERM_VIRTUAL ); /* EV: R-30575-11662 */
  4233         -    iRowidReg = codeEqualityTerm(pParse, pTerm, pLevel, iReleaseReg);
         4242  +    iRowidReg = codeEqualityTerm(pParse, pTerm, pLevel, 0, iReleaseReg);
  4234   4243       addrNxt = pLevel->addrNxt;
  4235   4244       sqlite3VdbeAddOp2(v, OP_MustBeInt, iRowidReg, addrNxt);
  4236   4245       sqlite3VdbeAddOp3(v, OP_NotExists, iCur, addrNxt, iRowidReg);
  4237   4246       sqlite3ExprCacheAffinityChange(pParse, iRowidReg, 1);
  4238   4247       sqlite3ExprCacheStore(pParse, iCur, -1, iRowidReg);
  4239   4248       VdbeComment((v, "pk"));
  4240   4249       pLevel->op = OP_Noop;