/ Check-in [9e14aa14]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Progress toward getting DISTINCT to use a seek to advance to the next distinct row, when driven by an appropriate index.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | skip-ahead-distinct
Files: files | file ages | folders
SHA1: 9e14aa14cff7ebf7a5e93ce7377838da129d585f
User & Date: drh 2016-04-15 01:55:49
Context
2016-04-15
13:24
Fixed crash bugs. Still sometimes gets the wrong answers. check-in: 2afcb2c7 user: drh tags: skip-ahead-distinct
01:55
Progress toward getting DISTINCT to use a seek to advance to the next distinct row, when driven by an appropriate index. check-in: 9e14aa14 user: drh tags: skip-ahead-distinct
2016-04-14
19:29
Work toward improving analysis and code generation for DISTINCT and aggregate queries. Leaf check-in: 129083bd user: drh tags: better-distinct-agg
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to src/select.c.

   742    742     /* If the DISTINCT keyword was present on the SELECT statement
   743    743     ** and this row has been seen before, then do not make this row
   744    744     ** part of the result.
   745    745     */
   746    746     if( hasDistinct ){
   747    747       switch( pDistinct->eTnctType ){
   748    748         case WHERE_DISTINCT_ORDERED: {
          749  +#if 0
   749    750           VdbeOp *pOp;            /* No longer required OpenEphemeral instr. */
   750    751           int iJump;              /* Jump destination */
   751    752           int regPrev;            /* Previous row content */
   752    753   
   753    754           /* Allocate space for the previous row */
   754    755           regPrev = pParse->nMem+1;
   755    756           pParse->nMem += nResultCol;
................................................................................
   778    779              }
   779    780             sqlite3VdbeChangeP4(v, -1, (const char *)pColl, P4_COLLSEQ);
   780    781             sqlite3VdbeChangeP5(v, SQLITE_NULLEQ);
   781    782           }
   782    783           assert( sqlite3VdbeCurrentAddr(v)==iJump || pParse->db->mallocFailed );
   783    784           sqlite3VdbeAddOp3(v, OP_Copy, regResult, regPrev, nResultCol-1);
   784    785           break;
          786  +#endif
   785    787         }
   786    788   
   787    789         case WHERE_DISTINCT_UNIQUE: {
   788    790           sqlite3VdbeChangeToNoop(v, pDistinct->addrTnct);
   789    791           break;
   790    792         }
   791    793   

Changes to src/shell.c.

  1729   1729   
  1730   1730       if( str_in_array(zOp, azNext) ){
  1731   1731         for(i=p2op; i<iOp; i++) p->aiIndent[i] += 2;
  1732   1732       }
  1733   1733       if( str_in_array(zOp, azGoto) && p2op<p->nIndent
  1734   1734        && (abYield[p2op] || sqlite3_column_int(pSql, 2))
  1735   1735       ){
  1736         -      for(i=p2op+1; i<iOp; i++) p->aiIndent[i] += 2;
         1736  +      for(i=p2op; i<iOp; i++) p->aiIndent[i] += 2;
  1737   1737       }
  1738   1738     }
  1739   1739   
  1740   1740     p->iIndent = 0;
  1741   1741     sqlite3_free(abYield);
  1742   1742     sqlite3_reset(pSql);
  1743   1743   }

Changes to src/where.c.

  3871   3871     /* Load the lowest cost path into pWInfo */
  3872   3872     for(iLoop=0; iLoop<nLoop; iLoop++){
  3873   3873       WhereLevel *pLevel = pWInfo->a + iLoop;
  3874   3874       pLevel->pWLoop = pWLoop = pFrom->aLoop[iLoop];
  3875   3875       pLevel->iFrom = pWLoop->iTab;
  3876   3876       pLevel->iTabCur = pWInfo->pTabList->a[pLevel->iFrom].iCursor;
  3877   3877     }
         3878  +  assert( pWInfo->pDistinctSet || (pWInfo->wctrlFlags&WHERE_WANT_DISTINCT)==0 );
  3878   3879     if( (pWInfo->wctrlFlags & WHERE_WANT_DISTINCT)!=0
  3879   3880      && (pWInfo->wctrlFlags & WHERE_DISTINCTBY)==0
  3880   3881      && pWInfo->eDistinct==WHERE_DISTINCT_NOOP
  3881   3882      && nRowEst
  3882   3883     ){
  3883   3884       Bitmask notUsed;
  3884   3885       int rc = wherePathSatisfiesOrderBy(pWInfo, pWInfo->pDistinctSet, pFrom,
................................................................................
  4484   4485         assert( iIndexCur>=0 );
  4485   4486         if( op ){
  4486   4487           sqlite3VdbeAddOp3(v, op, iIndexCur, pIx->tnum, iDb);
  4487   4488           sqlite3VdbeSetP4KeyInfo(pParse, pIx);
  4488   4489           if( (pLoop->wsFlags & WHERE_CONSTRAINT)!=0
  4489   4490            && (pLoop->wsFlags & (WHERE_COLUMN_RANGE|WHERE_SKIPSCAN))==0
  4490   4491            && (pWInfo->wctrlFlags&WHERE_ORDERBY_MIN)==0
         4492  +         && pWInfo->eDistinct!=WHERE_DISTINCT_ORDERED
  4491   4493           ){
  4492   4494             sqlite3VdbeChangeP5(v, OPFLAG_SEEKEQ); /* Hint to COMDB2 */
  4493   4495           }
  4494   4496           VdbeComment((v, "%s", pIx->zName));
  4495   4497   #ifdef SQLITE_ENABLE_COLUMN_USED_MASK
  4496   4498           {
  4497   4499             u64 colUsed = 0;
................................................................................
  4567   4569     WhereLoop *pLoop;
  4568   4570     SrcList *pTabList = pWInfo->pTabList;
  4569   4571     sqlite3 *db = pParse->db;
  4570   4572   
  4571   4573     /* Generate loop termination code.
  4572   4574     */
  4573   4575     VdbeModuleComment((v, "End WHERE-core"));
         4576  +  assert( pWInfo->eDistinct!=WHERE_DISTINCT_ORDERED || pWInfo->pDistinctSet!=0);
  4574   4577     sqlite3ExprCacheClear(pParse);
  4575   4578     for(i=pWInfo->nLevel-1; i>=0; i--){
  4576   4579       int addr;
  4577   4580       pLevel = &pWInfo->a[i];
  4578   4581       pLoop = pLevel->pWLoop;
  4579   4582       sqlite3VdbeResolveLabel(v, pLevel->addrCont);
  4580   4583       if( pLevel->op!=OP_Noop ){
         4584  +      if( pWInfo->eDistinct==WHERE_DISTINCT_ORDERED ){
         4585  +        int j, k, op;
         4586  +        int r1 = pParse->nMem+1;
         4587  +        int n = 0;
         4588  +        ExprList *pX = pWInfo->pDistinctSet;
         4589  +        for(j=0; j<pX->nExpr; j++){
         4590  +          Expr *pE = sqlite3ExprSkipCollate(pX->a[j].pExpr);
         4591  +          if( pE->op==TK_COLUMN && pE->iTable==pLevel->iTabCur ) n++;
         4592  +        }
         4593  +        for(j=0; j<n; j++){
         4594  +          sqlite3VdbeAddOp3(v, OP_Column, pLevel->iIdxCur, j, r1+j);
         4595  +        }
         4596  +        pParse->nMem += n;
         4597  +        op = pLevel->op==OP_Prev ? OP_SeekLT : OP_SeekGT;
         4598  +        k = sqlite3VdbeAddOp4Int(v, op, pLevel->iIdxCur, 0, r1, n);
         4599  +        VdbeCoverageIf(v, op==OP_SeekLT);
         4600  +        VdbeCoverageIf(v, op==OP_SeekGT);
         4601  +        sqlite3VdbeAddOp2(v, OP_Goto, 1, pLevel->p2);
         4602  +        sqlite3VdbeJumpHere(v, k);
         4603  +      }else{
  4581   4604         sqlite3VdbeAddOp3(v, pLevel->op, pLevel->p1, pLevel->p2, pLevel->p3);
  4582   4605         sqlite3VdbeChangeP5(v, pLevel->p5);
  4583   4606         VdbeCoverage(v);
  4584   4607         VdbeCoverageIf(v, pLevel->op==OP_Next);
  4585   4608         VdbeCoverageIf(v, pLevel->op==OP_Prev);
  4586   4609         VdbeCoverageIf(v, pLevel->op==OP_VNext);
  4587   4610       }
         4611  +    }
  4588   4612       if( pLoop->wsFlags & WHERE_IN_ABLE && pLevel->u.in.nIn>0 ){
  4589   4613         struct InLoop *pIn;
  4590   4614         int j;
  4591   4615         sqlite3VdbeResolveLabel(v, pLevel->addrNxt);
  4592   4616         for(j=pLevel->u.in.nIn, pIn=&pLevel->u.in.aInLoop[j-1]; j>0; j--, pIn--){
  4593   4617           sqlite3VdbeJumpHere(v, pIn->addrInTop+1);
  4594   4618           sqlite3VdbeAddOp2(v, pIn->eEndLoopOp, pIn->iCur, pIn->addrInTop);