/ Check-in [4e8796af]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Extend [52e73eec] so that the IS optimization may be used on primary keys with more than 3 columns.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | ota-update
Files: files | file ages | folders
SHA1: 4e8796af7d40d6ca423e07c68877035e4aa2485c
User & Date: dan 2015-03-25 15:23:00
Context
2015-04-08
14:01
Merge all changes for version 3.8.9 into the ota-update branch. check-in: ec9d907a user: drh tags: ota-update
2015-03-25
15:23
Extend [52e73eec] so that the IS optimization may be used on primary keys with more than 3 columns. check-in: 4e8796af user: dan tags: ota-update
2015-03-24
18:21
Merge the latest trunk changes into this branch. check-in: 9d9b6c88 user: dan tags: ota-update
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to src/where.c.

  4306   4306     if( ALWAYS(pWInfo) ){
  4307   4307       whereClauseClear(&pWInfo->sWC);
  4308   4308       while( pWInfo->pLoops ){
  4309   4309         WhereLoop *p = pWInfo->pLoops;
  4310   4310         pWInfo->pLoops = p->pNextLoop;
  4311   4311         whereLoopDelete(db, p);
  4312   4312       }
         4313  +    if( pWInfo->bShortcut ){
         4314  +      whereLoopClear(db, pWInfo->a[0].pWLoop);
         4315  +    }
  4313   4316       sqlite3DbFree(db, pWInfo);
  4314   4317     }
  4315   4318   }
  4316   4319   
  4317   4320   /*
  4318   4321   ** Return TRUE if all of the following are true:
  4319   4322   **
................................................................................
  5556   5559     int nTabList = pWInfo->nLevel;
  5557   5560     int rc = SQLITE_OK;
  5558   5561     u8 priorJoinType = 0;
  5559   5562     WhereLoop *pNew;
  5560   5563   
  5561   5564     /* Loop over the tables in the join, from left to right */
  5562   5565     pNew = pBuilder->pNew;
  5563         -  whereLoopInit(pNew);
  5564   5566     for(iTab=0, pItem=pTabList->a; iTab<nTabList; iTab++, pItem++){
  5565   5567       pNew->iTab = iTab;
  5566   5568       pNew->maskSelf = getMask(&pWInfo->sMaskSet, pItem->iCursor);
  5567   5569       if( ((pItem->jointype|priorJoinType) & (JT_LEFT|JT_CROSS))!=0 ){
  5568   5570         mExtra = mPrior;
  5569   5571       }
  5570   5572       priorJoinType = pItem->jointype;
................................................................................
  6272   6274   ** general-purpose query planner, and thereby yield faster sqlite3_prepare()
  6273   6275   ** times for the common case.
  6274   6276   **
  6275   6277   ** Return non-zero on success, if this query can be handled by this
  6276   6278   ** no-frills query planner.  Return zero if this query needs the 
  6277   6279   ** general-purpose query planner.
  6278   6280   */
  6279         -static int whereShortCut(WhereLoopBuilder *pBuilder){
         6281  +static int whereShortCut(sqlite3 *db, WhereLoopBuilder *pBuilder){
  6280   6282     WhereInfo *pWInfo;
  6281   6283     struct SrcList_item *pItem;
  6282   6284     WhereClause *pWC;
  6283   6285     WhereTerm *pTerm;
  6284   6286     WhereLoop *pLoop;
  6285   6287     int iCur;
  6286   6288     int j;
................................................................................
  6307   6309       pLoop->u.btree.nEq = 1;
  6308   6310       /* TUNING: Cost of a rowid lookup is 10 */
  6309   6311       pLoop->rRun = 33;  /* 33==sqlite3LogEst(10) */
  6310   6312     }else{
  6311   6313       for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
  6312   6314         u32 mask = WO_EQ;
  6313   6315         assert( pLoop->aLTermSpace==pLoop->aLTerm );
         6316  +      if( HasRowid(pTab)==0 && IsPrimaryKeyIndex(pIdx) ){
         6317  +        mask |= WO_IS;
         6318  +        if( whereLoopResize(db, pLoop, pIdx->nKeyCol) ) return 1;
         6319  +      }else
  6314   6320         if( !IsUniqueIndex(pIdx)
  6315   6321          || pIdx->pPartIdxWhere!=0 
  6316   6322          || pIdx->nKeyCol>ArraySize(pLoop->aLTermSpace) 
  6317   6323         ) continue;
  6318         -      if( HasRowid(pTab)==0 && IsPrimaryKeyIndex(pIdx) ) mask |= WO_IS;
  6319   6324         for(j=0; j<pIdx->nKeyCol; j++){
  6320   6325           pTerm = findTerm(pWC, iCur, pIdx->aiColumn[j], 0, mask, pIdx);
  6321   6326           if( pTerm==0 ) break;
  6322   6327           pLoop->aLTerm[j] = pTerm;
  6323   6328         }
  6324   6329         if( j!=pIdx->nKeyCol ) continue;
  6325   6330         pLoop->wsFlags = WHERE_COLUMN_EQ|WHERE_ONEROW|WHERE_INDEXED;
................................................................................
  6343   6348       if( pWInfo->pOrderBy ) pWInfo->nOBSat =  pWInfo->pOrderBy->nExpr;
  6344   6349       if( pWInfo->wctrlFlags & WHERE_WANT_DISTINCT ){
  6345   6350         pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE;
  6346   6351       }
  6347   6352   #ifdef SQLITE_DEBUG
  6348   6353       pLoop->cId = '0';
  6349   6354   #endif
         6355  +    pWInfo->bShortcut = 1;
  6350   6356       return 1;
  6351   6357     }
  6352   6358     return 0;
  6353   6359   }
  6354   6360   
  6355   6361   /*
  6356   6362   ** Generate the beginning of the loop used for WHERE clause processing.
................................................................................
  6613   6619       int i;
  6614   6620       for(i=0; i<sWLB.pWC->nTerm; i++){
  6615   6621         whereTermPrint(&sWLB.pWC->a[i], i);
  6616   6622       }
  6617   6623     }
  6618   6624   #endif
  6619   6625   
  6620         -  if( nTabList!=1 || whereShortCut(&sWLB)==0 ){
         6626  +  if( nTabList!=1 || whereShortCut(db, &sWLB)==0 ){
  6621   6627       rc = whereLoopAddAll(&sWLB);
  6622   6628       if( rc ) goto whereBeginError;
  6623   6629     
  6624   6630       /* Display all of the WhereLoop objects if wheretrace is enabled */
  6625   6631   #ifdef WHERETRACE_ENABLED /* !=0 */
  6626   6632       if( sqlite3WhereTrace ){
  6627   6633         WhereLoop *p;

Changes to src/whereInt.h.

   408    408     u16 wctrlFlags;           /* Flags originally passed to sqlite3WhereBegin() */
   409    409     i8 nOBSat;                /* Number of ORDER BY terms satisfied by indices */
   410    410     u8 sorted;                /* True if really sorted (not just grouped) */
   411    411     u8 okOnePass;             /* Ok to use one-pass algorithm for UPDATE/DELETE */
   412    412     u8 untestedTerms;         /* Not all WHERE terms resolved by outer loop */
   413    413     u8 eDistinct;             /* One of the WHERE_DISTINCT_* values below */
   414    414     u8 nLevel;                /* Number of nested loop */
          415  +  u8 bShortcut;             /* Plan generated by whereShortCut() */
   415    416     int iTop;                 /* The very beginning of the WHERE loop */
   416    417     int iContinue;            /* Jump here to continue with next record */
   417    418     int iBreak;               /* Jump here to break out of the loop */
   418    419     int savedNQueryLoop;      /* pParse->nQueryLoop outside the WHERE loop */
   419    420     int aiCurOnePass[2];      /* OP_OpenWrite cursors for the ONEPASS opt */
   420    421     WhereMaskSet sMaskSet;    /* Map cursor numbers to bitmasks */
   421    422     WhereClause sWC;          /* Decomposition of the WHERE clause */

Changes to test/whereK.test.

    53     53   } {/SCAN TABLE t1/}
    54     54   
    55     55   do_execsql_test 1.4.1 { SELECT v FROM t1 WHERE w IS 3   AND x = 3 } {C}
    56     56   do_execsql_test 1.4.2 { SELECT v FROM t1 WHERE w = '4' AND x IS 4 } {d}
    57     57   
    58     58   do_eqp_test 1.5.1 { SELECT b FROM t3 WHERE a IS ? } {/SCAN TABLE/}
    59     59   do_eqp_test 1.5.2 { SELECT b FROM t3 WHERE a = ? } {/SEARCH TABLE/}
           60  +
           61  +#-------------------------------------------------------------------------
           62  +# Check that this works with PRIMARY KEY indexes that have large numbers
           63  +# of columns.
           64  +
           65  +do_execsql_test 2.1.1 {
           66  +  CREATE TABLE x1(a, b, c, d, e, f, g, h, i,
           67  +    PRIMARY KEY(a, b, c, d, e, f, g)
           68  +  ) WITHOUT ROWID;
           69  +}
           70  +do_eqp_test 2.1.2 { 
           71  +  SELECT i FROM x1 WHERE 
           72  +  a IS ? AND b IS ? AND c IS ? AND d IS ? AND e IS ? AND f IS ? AND g IS ?
           73  +} {/SEARCH TABLE/}
    60     74   
    61     75   finish_test
    62     76   
    63     77