SQLite

Check-in [4e8796af7d]
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
Timelines: family | ancestors | descendants | both | ota-update
Files: files | file ages | folders
SHA1: 4e8796af7d40d6ca423e07c68877035e4aa2485c
User & Date: dan 2015-03-25 15:23:00.648
Context
2015-04-08
14:01
Merge all changes for version 3.8.9 into the ota-update branch. (check-in: ec9d907a57 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: 4e8796af7d user: dan tags: ota-update)
2015-03-24
18:21
Merge the latest trunk changes into this branch. (check-in: 9d9b6c883b user: dan tags: ota-update)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
4306
4307
4308
4309
4310
4311
4312



4313
4314
4315
4316
4317
4318
4319
  if( ALWAYS(pWInfo) ){
    whereClauseClear(&pWInfo->sWC);
    while( pWInfo->pLoops ){
      WhereLoop *p = pWInfo->pLoops;
      pWInfo->pLoops = p->pNextLoop;
      whereLoopDelete(db, p);
    }



    sqlite3DbFree(db, pWInfo);
  }
}

/*
** Return TRUE if all of the following are true:
**







>
>
>







4306
4307
4308
4309
4310
4311
4312
4313
4314
4315
4316
4317
4318
4319
4320
4321
4322
  if( ALWAYS(pWInfo) ){
    whereClauseClear(&pWInfo->sWC);
    while( pWInfo->pLoops ){
      WhereLoop *p = pWInfo->pLoops;
      pWInfo->pLoops = p->pNextLoop;
      whereLoopDelete(db, p);
    }
    if( pWInfo->bShortcut ){
      whereLoopClear(db, pWInfo->a[0].pWLoop);
    }
    sqlite3DbFree(db, pWInfo);
  }
}

/*
** Return TRUE if all of the following are true:
**
5556
5557
5558
5559
5560
5561
5562
5563
5564
5565
5566
5567
5568
5569
5570
  int nTabList = pWInfo->nLevel;
  int rc = SQLITE_OK;
  u8 priorJoinType = 0;
  WhereLoop *pNew;

  /* Loop over the tables in the join, from left to right */
  pNew = pBuilder->pNew;
  whereLoopInit(pNew);
  for(iTab=0, pItem=pTabList->a; iTab<nTabList; iTab++, pItem++){
    pNew->iTab = iTab;
    pNew->maskSelf = getMask(&pWInfo->sMaskSet, pItem->iCursor);
    if( ((pItem->jointype|priorJoinType) & (JT_LEFT|JT_CROSS))!=0 ){
      mExtra = mPrior;
    }
    priorJoinType = pItem->jointype;







<







5559
5560
5561
5562
5563
5564
5565

5566
5567
5568
5569
5570
5571
5572
  int nTabList = pWInfo->nLevel;
  int rc = SQLITE_OK;
  u8 priorJoinType = 0;
  WhereLoop *pNew;

  /* Loop over the tables in the join, from left to right */
  pNew = pBuilder->pNew;

  for(iTab=0, pItem=pTabList->a; iTab<nTabList; iTab++, pItem++){
    pNew->iTab = iTab;
    pNew->maskSelf = getMask(&pWInfo->sMaskSet, pItem->iCursor);
    if( ((pItem->jointype|priorJoinType) & (JT_LEFT|JT_CROSS))!=0 ){
      mExtra = mPrior;
    }
    priorJoinType = pItem->jointype;
6272
6273
6274
6275
6276
6277
6278
6279
6280
6281
6282
6283
6284
6285
6286
** general-purpose query planner, and thereby yield faster sqlite3_prepare()
** times for the common case.
**
** Return non-zero on success, if this query can be handled by this
** no-frills query planner.  Return zero if this query needs the 
** general-purpose query planner.
*/
static int whereShortCut(WhereLoopBuilder *pBuilder){
  WhereInfo *pWInfo;
  struct SrcList_item *pItem;
  WhereClause *pWC;
  WhereTerm *pTerm;
  WhereLoop *pLoop;
  int iCur;
  int j;







|







6274
6275
6276
6277
6278
6279
6280
6281
6282
6283
6284
6285
6286
6287
6288
** general-purpose query planner, and thereby yield faster sqlite3_prepare()
** times for the common case.
**
** Return non-zero on success, if this query can be handled by this
** no-frills query planner.  Return zero if this query needs the 
** general-purpose query planner.
*/
static int whereShortCut(sqlite3 *db, WhereLoopBuilder *pBuilder){
  WhereInfo *pWInfo;
  struct SrcList_item *pItem;
  WhereClause *pWC;
  WhereTerm *pTerm;
  WhereLoop *pLoop;
  int iCur;
  int j;
6307
6308
6309
6310
6311
6312
6313




6314
6315
6316
6317
6318
6319
6320
6321
6322
6323
6324
6325
    pLoop->u.btree.nEq = 1;
    /* TUNING: Cost of a rowid lookup is 10 */
    pLoop->rRun = 33;  /* 33==sqlite3LogEst(10) */
  }else{
    for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
      u32 mask = WO_EQ;
      assert( pLoop->aLTermSpace==pLoop->aLTerm );




      if( !IsUniqueIndex(pIdx)
       || pIdx->pPartIdxWhere!=0 
       || pIdx->nKeyCol>ArraySize(pLoop->aLTermSpace) 
      ) continue;
      if( HasRowid(pTab)==0 && IsPrimaryKeyIndex(pIdx) ) mask |= WO_IS;
      for(j=0; j<pIdx->nKeyCol; j++){
        pTerm = findTerm(pWC, iCur, pIdx->aiColumn[j], 0, mask, pIdx);
        if( pTerm==0 ) break;
        pLoop->aLTerm[j] = pTerm;
      }
      if( j!=pIdx->nKeyCol ) continue;
      pLoop->wsFlags = WHERE_COLUMN_EQ|WHERE_ONEROW|WHERE_INDEXED;







>
>
>
>




<







6309
6310
6311
6312
6313
6314
6315
6316
6317
6318
6319
6320
6321
6322
6323

6324
6325
6326
6327
6328
6329
6330
    pLoop->u.btree.nEq = 1;
    /* TUNING: Cost of a rowid lookup is 10 */
    pLoop->rRun = 33;  /* 33==sqlite3LogEst(10) */
  }else{
    for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
      u32 mask = WO_EQ;
      assert( pLoop->aLTermSpace==pLoop->aLTerm );
      if( HasRowid(pTab)==0 && IsPrimaryKeyIndex(pIdx) ){
        mask |= WO_IS;
        if( whereLoopResize(db, pLoop, pIdx->nKeyCol) ) return 1;
      }else
      if( !IsUniqueIndex(pIdx)
       || pIdx->pPartIdxWhere!=0 
       || pIdx->nKeyCol>ArraySize(pLoop->aLTermSpace) 
      ) continue;

      for(j=0; j<pIdx->nKeyCol; j++){
        pTerm = findTerm(pWC, iCur, pIdx->aiColumn[j], 0, mask, pIdx);
        if( pTerm==0 ) break;
        pLoop->aLTerm[j] = pTerm;
      }
      if( j!=pIdx->nKeyCol ) continue;
      pLoop->wsFlags = WHERE_COLUMN_EQ|WHERE_ONEROW|WHERE_INDEXED;
6343
6344
6345
6346
6347
6348
6349

6350
6351
6352
6353
6354
6355
6356
    if( pWInfo->pOrderBy ) pWInfo->nOBSat =  pWInfo->pOrderBy->nExpr;
    if( pWInfo->wctrlFlags & WHERE_WANT_DISTINCT ){
      pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE;
    }
#ifdef SQLITE_DEBUG
    pLoop->cId = '0';
#endif

    return 1;
  }
  return 0;
}

/*
** Generate the beginning of the loop used for WHERE clause processing.







>







6348
6349
6350
6351
6352
6353
6354
6355
6356
6357
6358
6359
6360
6361
6362
    if( pWInfo->pOrderBy ) pWInfo->nOBSat =  pWInfo->pOrderBy->nExpr;
    if( pWInfo->wctrlFlags & WHERE_WANT_DISTINCT ){
      pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE;
    }
#ifdef SQLITE_DEBUG
    pLoop->cId = '0';
#endif
    pWInfo->bShortcut = 1;
    return 1;
  }
  return 0;
}

/*
** Generate the beginning of the loop used for WHERE clause processing.
6613
6614
6615
6616
6617
6618
6619
6620
6621
6622
6623
6624
6625
6626
6627
    int i;
    for(i=0; i<sWLB.pWC->nTerm; i++){
      whereTermPrint(&sWLB.pWC->a[i], i);
    }
  }
#endif

  if( nTabList!=1 || whereShortCut(&sWLB)==0 ){
    rc = whereLoopAddAll(&sWLB);
    if( rc ) goto whereBeginError;
  
    /* Display all of the WhereLoop objects if wheretrace is enabled */
#ifdef WHERETRACE_ENABLED /* !=0 */
    if( sqlite3WhereTrace ){
      WhereLoop *p;







|







6619
6620
6621
6622
6623
6624
6625
6626
6627
6628
6629
6630
6631
6632
6633
    int i;
    for(i=0; i<sWLB.pWC->nTerm; i++){
      whereTermPrint(&sWLB.pWC->a[i], i);
    }
  }
#endif

  if( nTabList!=1 || whereShortCut(db, &sWLB)==0 ){
    rc = whereLoopAddAll(&sWLB);
    if( rc ) goto whereBeginError;
  
    /* Display all of the WhereLoop objects if wheretrace is enabled */
#ifdef WHERETRACE_ENABLED /* !=0 */
    if( sqlite3WhereTrace ){
      WhereLoop *p;
Changes to src/whereInt.h.
408
409
410
411
412
413
414

415
416
417
418
419
420
421
  u16 wctrlFlags;           /* Flags originally passed to sqlite3WhereBegin() */
  i8 nOBSat;                /* Number of ORDER BY terms satisfied by indices */
  u8 sorted;                /* True if really sorted (not just grouped) */
  u8 okOnePass;             /* Ok to use one-pass algorithm for UPDATE/DELETE */
  u8 untestedTerms;         /* Not all WHERE terms resolved by outer loop */
  u8 eDistinct;             /* One of the WHERE_DISTINCT_* values below */
  u8 nLevel;                /* Number of nested loop */

  int iTop;                 /* The very beginning of the WHERE loop */
  int iContinue;            /* Jump here to continue with next record */
  int iBreak;               /* Jump here to break out of the loop */
  int savedNQueryLoop;      /* pParse->nQueryLoop outside the WHERE loop */
  int aiCurOnePass[2];      /* OP_OpenWrite cursors for the ONEPASS opt */
  WhereMaskSet sMaskSet;    /* Map cursor numbers to bitmasks */
  WhereClause sWC;          /* Decomposition of the WHERE clause */







>







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














60
61
62
63
} {/SCAN TABLE t1/}

do_execsql_test 1.4.1 { SELECT v FROM t1 WHERE w IS 3   AND x = 3 } {C}
do_execsql_test 1.4.2 { SELECT v FROM t1 WHERE w = '4' AND x IS 4 } {d}

do_eqp_test 1.5.1 { SELECT b FROM t3 WHERE a IS ? } {/SCAN TABLE/}
do_eqp_test 1.5.2 { SELECT b FROM t3 WHERE a = ? } {/SEARCH TABLE/}















finish_test









>
>
>
>
>
>
>
>
>
>
>
>
>
>




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
} {/SCAN TABLE t1/}

do_execsql_test 1.4.1 { SELECT v FROM t1 WHERE w IS 3   AND x = 3 } {C}
do_execsql_test 1.4.2 { SELECT v FROM t1 WHERE w = '4' AND x IS 4 } {d}

do_eqp_test 1.5.1 { SELECT b FROM t3 WHERE a IS ? } {/SCAN TABLE/}
do_eqp_test 1.5.2 { SELECT b FROM t3 WHERE a = ? } {/SEARCH TABLE/}

#-------------------------------------------------------------------------
# Check that this works with PRIMARY KEY indexes that have large numbers
# of columns.

do_execsql_test 2.1.1 {
  CREATE TABLE x1(a, b, c, d, e, f, g, h, i,
    PRIMARY KEY(a, b, c, d, e, f, g)
  ) WITHOUT ROWID;
}
do_eqp_test 2.1.2 { 
  SELECT i FROM x1 WHERE 
  a IS ? AND b IS ? AND c IS ? AND d IS ? AND e IS ? AND f IS ? AND g IS ?
} {/SEARCH TABLE/}

finish_test