SQLite

Check-in [57c5173b63]
Login

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

Overview
Comment:Simplification of the skip-ahead-distinct logic. There is still an issue with handling COLLATE.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | skip-ahead-distinct
Files: files | file ages | folders
SHA3-256: 57c5173b6376a76013b0611ce9770100bd8c04e80402e35d821d8614709f4795
User & Date: drh 2017-04-13 19:48:29.675
Context
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)
19:48
Simplification of the skip-ahead-distinct logic. There is still an issue with handling COLLATE. (check-in: 57c5173b63 user: drh tags: skip-ahead-distinct)
18:33
New test cases and minor fixes for the optimization on this branch. (check-in: 70ac9ea1a6 user: dan tags: skip-ahead-distinct)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/where.c.
︙︙
3433
3434
3435
3436
3437
3438
3439
3440
3441
3442
3443
3444
3445
3446
3447

  }

  whereLoopClear(db, pNew);
  return rc;
}

/*
** Examine a WherePath (with the addition of the extra WhereLoop of the 5th
** parameters) to see if it outputs rows in the requested ORDER BY
** (or GROUP BY) without requiring a separate sort operation.  Return N:
** 
**   N>0:   N terms of the ORDER BY clause are satisfied
**   N==0:  No terms of the ORDER BY clause are satisfied
**   N<0:   Unknown yet how many terms of ORDER BY might be satisfied.   
**








|








3433
3434
3435
3436
3437
3438
3439
3440
3441
3442
3443
3444
3445
3446
3447

  }

  whereLoopClear(db, pNew);
  return rc;
}

/*
** Examine a WherePath (with the addition of the extra WhereLoop of the 6th
** parameters) to see if it outputs rows in the requested ORDER BY
** (or GROUP BY) without requiring a separate sort operation.  Return N:
** 
**   N>0:   N terms of the ORDER BY clause are satisfied
**   N==0:  No terms of the ORDER BY clause are satisfied
**   N<0:   Unknown yet how many terms of ORDER BY might be satisfied.   
**
︙︙
4845
4846
4847
4848
4849
4850
4851

4852
4853
4854
4855
4856
4857
4858

  sqlite3ExprCacheClear(pParse);
  for(i=pWInfo->nLevel-1; i>=0; i--){
    int addr;
    pLevel = &pWInfo->a[i];
    pLoop = pLevel->pWLoop;
    if( pLevel->op!=OP_Noop ){
#ifndef SQLITE_DISABLE_SKIPAHEAD_DISTINCT

      int n = -1;
      int j, k, op;
      int r1 = pParse->nMem+1;
      Index *pIdx;
      if( pWInfo->eDistinct==WHERE_DISTINCT_ORDERED
       && (pLoop->wsFlags & WHERE_INDEXED)!=0
       && OptimizationEnabled(db, SQLITE_SkipAhead)








>








4845
4846
4847
4848
4849
4850
4851
4852
4853
4854
4855
4856
4857
4858
4859

  sqlite3ExprCacheClear(pParse);
  for(i=pWInfo->nLevel-1; i>=0; i--){
    int addr;
    pLevel = &pWInfo->a[i];
    pLoop = pLevel->pWLoop;
    if( pLevel->op!=OP_Noop ){
#ifndef SQLITE_DISABLE_SKIPAHEAD_DISTINCT
      int addrSeek = 0;
      int n = -1;
      int j, k, op;
      int r1 = pParse->nMem+1;
      Index *pIdx;
      if( pWInfo->eDistinct==WHERE_DISTINCT_ORDERED
       && (pLoop->wsFlags & WHERE_INDEXED)!=0
       && OptimizationEnabled(db, SQLITE_SkipAhead)
︙︙
4893
4894
4895
4896
4897
4898
4899
4900
4901
4902
4903
4904
4905
4906
4907

4908
4909
4910
4911
4912
4913
4914
4915
4916
4917
4918



4919
4920
4921
4922
4923
4924
4925

      assert( 36==sqlite3LogEst(12) );
      if( n>0 && pIdx->aiRowLogEst[n]>=36 ){
        for(j=0; j<n; j++){
          sqlite3VdbeAddOp3(v, OP_Column, pLevel->iIdxCur, j, r1+j);
        }
        pParse->nMem += n;
        op = pLevel->op==OP_Prev ? OP_SeekLT : OP_SeekGT;
        k = sqlite3VdbeAddOp4Int(v, op, pLevel->iIdxCur, 0, r1, n);
        VdbeCoverageIf(v, op==OP_SeekLT);
        VdbeCoverageIf(v, op==OP_SeekGT);
        sqlite3VdbeAddOp2(v, OP_Goto, 1, pLevel->p2);
        sqlite3VdbeResolveLabel(v, pLevel->addrCont);
        sqlite3VdbeAddOp3(v, pLevel->op, pLevel->p1, pLevel->p2, pLevel->p3);
        sqlite3VdbeJumpHere(v, k);
      }else

#endif /* SQLITE_DISABLE_SKIPAHEAD_DISTINCT */
      {
        /* The common case: Advance to the next row */
        sqlite3VdbeResolveLabel(v, pLevel->addrCont);
        sqlite3VdbeAddOp3(v, pLevel->op, pLevel->p1, pLevel->p2, pLevel->p3);
        sqlite3VdbeChangeP5(v, pLevel->p5);
        VdbeCoverage(v);
        VdbeCoverageIf(v, pLevel->op==OP_Next);
        VdbeCoverageIf(v, pLevel->op==OP_Prev);
        VdbeCoverageIf(v, pLevel->op==OP_VNext);
      }



    }else{
      sqlite3VdbeResolveLabel(v, pLevel->addrCont);
    }
    if( pLoop->wsFlags & WHERE_IN_ABLE && pLevel->u.in.nIn>0 ){
      struct InLoop *pIn;
      int j;
      sqlite3VdbeResolveLabel(v, pLevel->addrNxt);








|



<
<
<
<
>

<
|
|
|
|
|
|
|
|
<
>
>
>








4894
4895
4896
4897
4898
4899
4900
4901
4902
4903
4904




4905
4906

4907
4908
4909
4910
4911
4912
4913
4914

4915
4916
4917
4918
4919
4920
4921
4922
4923
4924

      assert( 36==sqlite3LogEst(12) );
      if( n>0 && pIdx->aiRowLogEst[n]>=36 ){
        for(j=0; j<n; j++){
          sqlite3VdbeAddOp3(v, OP_Column, pLevel->iIdxCur, j, r1+j);
        }
        pParse->nMem += n;
        op = pLevel->op==OP_Prev ? OP_SeekLT : OP_SeekGT;
        addrSeek = sqlite3VdbeAddOp4Int(v, op, pLevel->iIdxCur, 0, r1, n);
        VdbeCoverageIf(v, op==OP_SeekLT);
        VdbeCoverageIf(v, op==OP_SeekGT);
        sqlite3VdbeAddOp2(v, OP_Goto, 1, pLevel->p2);




      }
#endif /* SQLITE_DISABLE_SKIPAHEAD_DISTINCT */

      /* The common case: Advance to the next row */
      sqlite3VdbeResolveLabel(v, pLevel->addrCont);
      sqlite3VdbeAddOp3(v, pLevel->op, pLevel->p1, pLevel->p2, pLevel->p3);
      sqlite3VdbeChangeP5(v, pLevel->p5);
      VdbeCoverage(v);
      VdbeCoverageIf(v, pLevel->op==OP_Next);
      VdbeCoverageIf(v, pLevel->op==OP_Prev);
      VdbeCoverageIf(v, pLevel->op==OP_VNext);

#ifndef SQLITE_DISABLE_SKIPAHEAD_DISTINCT
      if( addrSeek ) sqlite3VdbeJumpHere(v, addrSeek);
#endif
    }else{
      sqlite3VdbeResolveLabel(v, pLevel->addrCont);
    }
    if( pLoop->wsFlags & WHERE_IN_ABLE && pLevel->u.in.nIn>0 ){
      struct InLoop *pIn;
      int j;
      sqlite3VdbeResolveLabel(v, pLevel->addrNxt);
︙︙