SQLite

Check-in [1aa0ea8db7]
Login

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

Overview
Comment:Fix a couple of unreachable branches.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | skip-ahead-distinct
Files: files | file ages | folders
SHA3-256: 1aa0ea8db7580aff8bed2f78117ed50019e5545cef48cab782f512a2599fd0c5
User & Date: drh 2017-04-14 00:45:51.643
Context
2017-04-14
22:41
Fix to the decision logic for when to use the skip-ahead-distinct optimization. (Closed-Leaf check-in: e50fd48969 user: drh tags: skip-ahead-distinct)
17:30
When doing a DISTINCT query using an index, try to use the index to skip ahead to the next distinct element, rather than doing a full scan of the index. (This is the "skip-ahead-distinct" optimization.) (check-in: f489b5bb6b user: drh tags: trunk)
00:45
Fix a couple of unreachable branches. (check-in: 1aa0ea8db7 user: drh tags: skip-ahead-distinct)
2017-04-13
21:29
Fix the skip-ahead-distinct optimization so that it works with indexes that have repeated columns with different collating sequences. (check-in: ce1e2b8877 user: drh tags: skip-ahead-distinct)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/sqliteInt.h.
1497
1498
1499
1500
1501
1502
1503
1504
1505
1506
1507
1508
1509
1510
1511
#define SQLITE_DistinctOpt    0x0020   /* DISTINCT using indexes */
#define SQLITE_CoverIdxScan   0x0040   /* Covering index scans */
#define SQLITE_OrderByIdxJoin 0x0080   /* ORDER BY of joins via index */
#define SQLITE_SubqCoroutine  0x0100   /* Evaluate subqueries as coroutines */
#define SQLITE_Transitive     0x0200   /* Transitive constraints */
#define SQLITE_OmitNoopJoin   0x0400   /* Omit unused tables in joins */
#define SQLITE_Stat34         0x0800   /* Use STAT3 or STAT4 data */
#define SQLITE_SkipAhead      0x1000   /* Skip ahead on DISTINCT */
#define SQLITE_CursorHints    0x2000   /* Add OP_CursorHint opcodes */
#define SQLITE_AllOpts        0xffff   /* All optimizations */

/*
** Macros for testing whether or not optimizations are enabled or disabled.
*/
#define OptimizationDisabled(db, mask)  (((db)->dbOptFlags&(mask))!=0)







<







1497
1498
1499
1500
1501
1502
1503

1504
1505
1506
1507
1508
1509
1510
#define SQLITE_DistinctOpt    0x0020   /* DISTINCT using indexes */
#define SQLITE_CoverIdxScan   0x0040   /* Covering index scans */
#define SQLITE_OrderByIdxJoin 0x0080   /* ORDER BY of joins via index */
#define SQLITE_SubqCoroutine  0x0100   /* Evaluate subqueries as coroutines */
#define SQLITE_Transitive     0x0200   /* Transitive constraints */
#define SQLITE_OmitNoopJoin   0x0400   /* Omit unused tables in joins */
#define SQLITE_Stat34         0x0800   /* Use STAT3 or STAT4 data */

#define SQLITE_CursorHints    0x2000   /* Add OP_CursorHint opcodes */
#define SQLITE_AllOpts        0xffff   /* All optimizations */

/*
** Macros for testing whether or not optimizations are enabled or disabled.
*/
#define OptimizationDisabled(db, mask)  (((db)->dbOptFlags&(mask))!=0)
Changes to src/where.c.
3675
3676
3677
3678
3679
3680
3681
3682
3683
3684
3685
3686
3687
3688
3689
            }
          }
          if( iColumn>=0 ){
            pColl = sqlite3ExprCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr);
            if( !pColl ) pColl = db->pDfltColl;
            if( sqlite3StrICmp(pColl->zName, pIndex->azColl[j])!=0 ) continue;
          }
          if( pLoop->u.btree.nIdxCol<=j ) pLoop->u.btree.nIdxCol = j+1;
          isMatch = 1;
          break;
        }
        if( isMatch && (wctrlFlags & WHERE_GROUPBY)==0 ){
          /* Make sure the sort order is compatible in an ORDER BY clause.
          ** Sort order is irrelevant for a GROUP BY clause. */
          if( revSet ){







|







3675
3676
3677
3678
3679
3680
3681
3682
3683
3684
3685
3686
3687
3688
3689
            }
          }
          if( iColumn>=0 ){
            pColl = sqlite3ExprCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr);
            if( !pColl ) pColl = db->pDfltColl;
            if( sqlite3StrICmp(pColl->zName, pIndex->azColl[j])!=0 ) continue;
          }
          pLoop->u.btree.nIdxCol = j+1;
          isMatch = 1;
          break;
        }
        if( isMatch && (wctrlFlags & WHERE_GROUPBY)==0 ){
          /* Make sure the sort order is compatible in an ORDER BY clause.
          ** Sort order is irrelevant for a GROUP BY clause. */
          if( revSet ){
4853
4854
4855
4856
4857
4858
4859
4860
4861
4862
4863
4864
4865
4866
4867
    if( pLevel->op!=OP_Noop ){
#ifndef SQLITE_DISABLE_SKIPAHEAD_DISTINCT
      int addrSeek = 0;
      Index *pIdx;
      int n;
      if( pWInfo->eDistinct==WHERE_DISTINCT_ORDERED
       && (pLoop->wsFlags & WHERE_INDEXED)!=0
       && OptimizationEnabled(db, SQLITE_SkipAhead)
       && (pIdx = pLoop->u.btree.pIndex)->hasStat1
       && pIdx->aiRowLogEst[(n = pLoop->u.btree.nIdxCol)-1]>=36
      ){
        int r1 = pParse->nMem+1;
        int j, op;
        for(j=0; j<n; j++){
          sqlite3VdbeAddOp3(v, OP_Column, pLevel->iIdxCur, j, r1+j);







<







4853
4854
4855
4856
4857
4858
4859

4860
4861
4862
4863
4864
4865
4866
    if( pLevel->op!=OP_Noop ){
#ifndef SQLITE_DISABLE_SKIPAHEAD_DISTINCT
      int addrSeek = 0;
      Index *pIdx;
      int n;
      if( pWInfo->eDistinct==WHERE_DISTINCT_ORDERED
       && (pLoop->wsFlags & WHERE_INDEXED)!=0

       && (pIdx = pLoop->u.btree.pIndex)->hasStat1
       && pIdx->aiRowLogEst[(n = pLoop->u.btree.nIdxCol)-1]>=36
      ){
        int r1 = pParse->nMem+1;
        int j, op;
        for(j=0; j<n; j++){
          sqlite3VdbeAddOp3(v, OP_Column, pLevel->iIdxCur, j, r1+j);