/ Check-in [6e2e9d38]
Login

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

Overview
Comment:Reenable the SQLITE_EXPR_REF optimization for "SELECT DISTINCT ... ORDER BY" queries.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | sorter-opt
Files: files | file ages | folders
SHA1:6e2e9d383f5fc4a0cbf05fe83ec7425812c0f556
User & Date: dan 2016-11-11 18:08:59
Context
2016-11-11
18:45
Merge trunk with this branch. Closed-Leaf check-in: dd62d2de user: dan tags: sorter-opt
18:08
Reenable the SQLITE_EXPR_REF optimization for "SELECT DISTINCT ... ORDER BY" queries. check-in: 6e2e9d38 user: dan tags: sorter-opt
2016-11-10
20:14
Avoid storing redundant fields in sorter records when the sort-key and data have fields in common (as in "SELECT a FROM t1 ORDER BY 1"). check-in: 0af62fdb user: dan tags: sorter-opt
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

   517    517     int regBase;                                     /* Regs for sorter record */
   518    518     int regRecord = ++pParse->nMem;                  /* Assembled sorter record */
   519    519     int nOBSat = pSort->nOBSat;                      /* ORDER BY terms to skip */
   520    520     int op;                            /* Opcode to add sorter record to sorter */
   521    521     int iLimit;                        /* LIMIT counter */
   522    522   
   523    523     assert( bSeq==0 || bSeq==1 );
   524         -  assert( nData==1 || regData==regOrigData );
          524  +  assert( nData==1 || regData==regOrigData || regOrigData==0 );
   525    525     if( nPrefixReg ){
   526    526       assert( nPrefixReg==nExpr+bSeq );
   527    527       regBase = regData - nExpr - bSeq;
   528    528     }else{
   529    529       regBase = pParse->nMem + 1;
   530    530       pParse->nMem += nBase;
   531    531     }
   532    532     assert( pSelect->iOffset==0 || pSelect->iLimit!=0 );
   533    533     iLimit = pSelect->iOffset ? pSelect->iOffset+1 : pSelect->iLimit;
   534    534     pSort->labelDone = sqlite3VdbeMakeLabel(v);
   535    535     sqlite3ExprCodeExprList(pParse, pSort->pOrderBy, regBase, regOrigData,
   536         -                          SQLITE_ECEL_DUP);
          536  +                          SQLITE_ECEL_DUP | (regOrigData? SQLITE_ECEL_REF : 0));
   537    537     if( bSeq ){
   538    538       sqlite3VdbeAddOp2(v, OP_Sequence, pSort->iECursor, regBase+nExpr);
   539    539     }
   540    540     if( nPrefixReg==0 && nData>0 ){
   541    541       sqlite3ExprCodeMove(pParse, regData, regBase+nExpr+bSeq, nData);
   542    542     }
   543    543     sqlite3VdbeAddOp3(v, OP_MakeRecord, regBase+nOBSat, nBase-nOBSat, regRecord);
................................................................................
   677    677     DistinctCtx *pDistinct, /* If not NULL, info on how to process DISTINCT */
   678    678     SelectDest *pDest,      /* How to dispose of the results */
   679    679     int iContinue,          /* Jump here to continue with next row */
   680    680     int iBreak              /* Jump here to break out of the inner loop */
   681    681   ){
   682    682     Vdbe *v = pParse->pVdbe;
   683    683     int i;
   684         -  int hasDistinct;        /* True if the DISTINCT keyword is present */
   685         -  int regResult;              /* Start of memory holding result set */
          684  +  int hasDistinct;            /* True if the DISTINCT keyword is present */
   686    685     int eDest = pDest->eDest;   /* How to dispose of results */
   687    686     int iParm = pDest->iSDParm; /* First argument to disposal method */
   688    687     int nResultCol;             /* Number of result columns */
   689    688     int nPrefixReg = 0;         /* Number of extra registers before regResult */
   690    689   
          690  +  /* Usually, regResult is the first cell in an array of memory cells
          691  +  ** containing the current result row. In this case regOrig is set to the
          692  +  ** same value. However, if the results are being sent to the sorter, the
          693  +  ** values for any expressions that are also part of the sort-key are omitted
          694  +  ** from this array. In this case regOrig is set to zero.  */
          695  +  int regResult;              /* Start of memory holding current results */
          696  +  int regOrig;                /* Start of memory holding full result (or 0) */
          697  +
   691    698     assert( v );
   692    699     assert( pEList!=0 );
   693    700     hasDistinct = pDistinct ? pDistinct->eTnctType : WHERE_DISTINCT_NOOP;
   694    701     if( pSort && pSort->pOrderBy==0 ) pSort = 0;
   695    702     if( pSort==0 && !hasDistinct ){
   696    703       assert( iContinue!=0 );
   697    704       codeOffset(v, p->iOffset, iContinue);
................................................................................
   714    721       ** on the right-hand side of an INSERT contains more result columns than
   715    722       ** there are columns in the table on the left.  The error will be caught
   716    723       ** and reported later.  But we need to make sure enough memory is allocated
   717    724       ** to avoid other spurious errors in the meantime. */
   718    725       pParse->nMem += nResultCol;
   719    726     }
   720    727     pDest->nSdst = nResultCol;
   721         -  regResult = pDest->iSdst;
          728  +  regOrig = regResult = pDest->iSdst;
   722    729     if( srcTab>=0 ){
   723    730       for(i=0; i<nResultCol; i++){
   724    731         sqlite3VdbeAddOp3(v, OP_Column, srcTab, i, regResult+i);
   725    732         VdbeComment((v, "%s", pEList->a[i].zName));
   726    733       }
   727    734     }else if( eDest!=SRT_Exists ){
   728    735       /* If the destination is an EXISTS(...) expression, the actual
................................................................................
   730    737       */
   731    738       u8 ecelFlags;
   732    739       if( eDest==SRT_Mem || eDest==SRT_Output || eDest==SRT_Coroutine ){
   733    740         ecelFlags = SQLITE_ECEL_DUP;
   734    741       }else{
   735    742         ecelFlags = 0;
   736    743       }
   737         -    if( pSort && hasDistinct==0 && eDest!=SRT_EphemTab && eDest!=SRT_Table ){
          744  +    assert( eDest!=SRT_Table || pSort==0 );
          745  +    if( pSort && hasDistinct==0 && eDest!=SRT_EphemTab ){
   738    746         /* For each expression in pEList that is a copy of an expression in
   739    747         ** the ORDER BY clause (pSort->pOrderBy), set the associated 
   740    748         ** iOrderByCol value to one more than the index of the ORDER BY 
   741    749         ** expression within the sort-key that pushOntoSorter() will generate.
   742    750         ** This allows the pEList field to be omitted from the sorted record,
   743    751         ** saving space and CPU cycles.  */
   744    752         ecelFlags |= (SQLITE_ECEL_OMITREF|SQLITE_ECEL_REF);
   745    753         for(i=pSort->nOBSat; i<pSort->pOrderBy->nExpr; i++){
   746    754           int j;
   747    755           if( (j = pSort->pOrderBy->a[i].u.x.iOrderByCol)>0 ){
   748    756             pEList->a[j-1].u.x.iOrderByCol = i+1-pSort->nOBSat;
   749    757           }
   750    758         }
   751         -
          759  +      regOrig = 0;
   752    760         assert( eDest==SRT_Set || eDest==SRT_Mem 
   753    761              || eDest==SRT_Coroutine || eDest==SRT_Output );
   754    762       }
   755    763       nResultCol = sqlite3ExprCodeExprList(pParse,pEList,regResult,0,ecelFlags);
   756    764     }
   757    765   
   758    766     /* If the DISTINCT keyword was present on the SELECT statement
................................................................................
   888    896       case SRT_Set: {
   889    897         if( pSort ){
   890    898           /* At first glance you would think we could optimize out the
   891    899           ** ORDER BY in this case since the order of entries in the set
   892    900           ** does not matter.  But there might be a LIMIT clause, in which
   893    901           ** case the order does matter */
   894    902           pushOntoSorter(
   895         -            pParse, pSort, p, regResult, regResult, nResultCol, nPrefixReg);
          903  +            pParse, pSort, p, regResult, regOrig, nResultCol, nPrefixReg);
   896    904         }else{
   897    905           int r1 = sqlite3GetTempReg(pParse);
   898    906           assert( sqlite3Strlen30(pDest->zAffSdst)==nResultCol );
   899    907           sqlite3VdbeAddOp4(v, OP_MakeRecord, regResult, nResultCol, 
   900    908               r1, pDest->zAffSdst, nResultCol);
   901    909           sqlite3ExprCacheAffinityChange(pParse, regResult, nResultCol);
   902    910           sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm, r1);
................................................................................
   917    925       ** store the results in the appropriate memory cell or array of 
   918    926       ** memory cells and break out of the scan loop.
   919    927       */
   920    928       case SRT_Mem: {
   921    929         if( pSort ){
   922    930           assert( nResultCol<=pDest->nSdst );
   923    931           pushOntoSorter(
   924         -            pParse, pSort, p, regResult, regResult, nResultCol, nPrefixReg);
          932  +            pParse, pSort, p, regResult, regOrig, nResultCol, nPrefixReg);
   925    933         }else{
   926    934           assert( nResultCol==pDest->nSdst );
   927    935           assert( regResult==iParm );
   928    936           /* The LIMIT clause will jump out of the loop for us */
   929    937         }
   930    938         break;
   931    939       }
................................................................................
   932    940   #endif /* #ifndef SQLITE_OMIT_SUBQUERY */
   933    941   
   934    942       case SRT_Coroutine:       /* Send data to a co-routine */
   935    943       case SRT_Output: {        /* Return the results */
   936    944         testcase( eDest==SRT_Coroutine );
   937    945         testcase( eDest==SRT_Output );
   938    946         if( pSort ){
   939         -        pushOntoSorter(pParse, pSort, p, regResult, regResult, nResultCol,
          947  +        pushOntoSorter(pParse, pSort, p, regResult, regOrig, nResultCol,
   940    948                          nPrefixReg);
   941    949         }else if( eDest==SRT_Coroutine ){
   942    950           sqlite3VdbeAddOp1(v, OP_Yield, pDest->iSDParm);
   943    951         }else{
   944    952           sqlite3VdbeAddOp2(v, OP_ResultRow, regResult, nResultCol);
   945    953           sqlite3ExprCacheAffinityChange(pParse, regResult, nResultCol);
   946    954         }