/ Check-in [0fcfc36c]
Login

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

Overview
Comment:When processing an "ORDER BY ... LIMIT" that does not use an index, check whether or not a record may appear in the final result set before adding it to the temp b-tree used for sorting.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 0fcfc36ceb820fc70136b799a0405fe92e50646e697be2872bbe9a53a05ed5a9
User & Date: dan 2018-04-26 17:43:35
Context
2018-04-26
18:34
The previous fix for ticket [d85fffd6ffe856092ed8da] in check-in [0a514e62ad1ebe5c12da8dae] did not completely address the probably in that it only worked for cases where the OP_SCopy that loaded the register was the last instruction in the sequence for the expression, which is not necessarily the case for expressions like CASE...END. This revision prevents the registered that will be recomputed from being cached in the first place. check-in: 9fd0faf5 user: drh tags: trunk
17:54
Merge latest changes from trunk. Including the "ORDER BY ... LIMIT" optimization. check-in: d8ae7ba0 user: dan tags: begin-concurrent
17:43
When processing an "ORDER BY ... LIMIT" that does not use an index, check whether or not a record may appear in the final result set before adding it to the temp b-tree used for sorting. check-in: 0fcfc36c user: dan tags: trunk
16:13
When processing an "ORDER BY ... LIMIT" that does not use an index, check whether or not a record may appear in the final result set before adding it to the sorter. Closed-Leaf check-in: 71bf91c2 user: dan tags: sorter-limit-opt
15:50
Ensure that new.* values of an UPDATE do not get clobbered after the BEFORE triggers run when unmodified columns of the row being updated are reloaded. Fix for ticket [d85fffd6ffe856092ed8da] check-in: 0a514e62 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

   575    575                             SQLITE_ECEL_DUP | (regOrigData? SQLITE_ECEL_REF : 0));
   576    576     if( bSeq ){
   577    577       sqlite3VdbeAddOp2(v, OP_Sequence, pSort->iECursor, regBase+nExpr);
   578    578     }
   579    579     if( nPrefixReg==0 && nData>0 ){
   580    580       sqlite3ExprCodeMove(pParse, regData, regBase+nExpr+bSeq, nData);
   581    581     }
   582         -  sqlite3VdbeAddOp3(v, OP_MakeRecord, regBase+nOBSat, nBase-nOBSat, regRecord);
   583    582     if( nOBSat>0 ){
   584    583       int regPrevKey;   /* The first nOBSat columns of the previous row */
   585    584       int addrFirst;    /* Address of the OP_IfNot opcode */
   586    585       int addrJmp;      /* Address of the OP_Jump opcode */
   587    586       VdbeOp *pOp;      /* Opcode that opens the sorter */
   588    587       int nKey;         /* Number of sorting key columns, including OP_Sequence */
   589    588       KeyInfo *pKI;     /* Original KeyInfo on the sorter table */
   590    589   
          590  +    sqlite3VdbeAddOp3(v, OP_MakeRecord, regBase+nOBSat, nBase-nOBSat,regRecord);
   591    591       regPrevKey = pParse->nMem+1;
   592    592       pParse->nMem += pSort->nOBSat;
   593    593       nKey = nExpr - pSort->nOBSat + bSeq;
   594    594       if( bSeq ){
   595    595         addrFirst = sqlite3VdbeAddOp1(v, OP_IfNot, regBase+nExpr); 
   596    596       }else{
   597    597         addrFirst = sqlite3VdbeAddOp1(v, OP_SequenceTest, pSort->iECursor);
................................................................................
   617    617         sqlite3VdbeAddOp2(v, OP_IfNot, iLimit, pSort->labelDone);
   618    618         VdbeCoverage(v);
   619    619       }
   620    620       sqlite3VdbeJumpHere(v, addrFirst);
   621    621       sqlite3ExprCodeMove(pParse, regBase, regPrevKey, pSort->nOBSat);
   622    622       sqlite3VdbeJumpHere(v, addrJmp);
   623    623     }
          624  +  if( iLimit ){
          625  +    /* At this point the values for the new sorter entry are stored
          626  +    ** in an array of registers. They need to be composed into a record
          627  +    ** and inserted into the sorter if either (a) there are currently
          628  +    ** less than LIMIT+OFFSET items or (b) the new record is smaller than 
          629  +    ** the largest record currently in the sorter. If (b) is true and there
          630  +    ** are already LIMIT+OFFSET items in the sorter, delete the largest
          631  +    ** entry before inserting the new one. This way there are never more 
          632  +    ** than LIMIT+OFFSET items in the sorter.
          633  +    **
          634  +    ** If the new record does not need to be inserted into the sorter,
          635  +    ** jump to the next iteration of the loop. Or, if the
          636  +    ** pSort->bOrderedInnerLoop flag is set to indicate that the inner
          637  +    ** loop delivers items in sorted order, jump to the next iteration
          638  +    ** of the outer loop.
          639  +    */
          640  +    int iCsr = pSort->iECursor;
          641  +    int iJmp = sqlite3VdbeCurrentAddr(v)+5+(nOBSat<=0)+pSort->bOrderedInnerLoop;
          642  +    assert( pSort->bOrderedInnerLoop==0 || pSort->bOrderedInnerLoop==1 );
          643  +    sqlite3VdbeAddOp2(v, OP_IfNotZero, iLimit, sqlite3VdbeCurrentAddr(v)+4);
          644  +    VdbeCoverage(v);
          645  +    sqlite3VdbeAddOp2(v, OP_Last, iCsr, 0);
          646  +    sqlite3VdbeAddOp4Int(v, OP_IdxLE, iCsr, iJmp, regBase+nOBSat, nExpr-nOBSat);
          647  +    VdbeCoverage(v);
          648  +    sqlite3VdbeAddOp1(v, OP_Delete, iCsr);
          649  +  }
          650  +  if( nOBSat<=0 ){
          651  +    sqlite3VdbeAddOp3(v, OP_MakeRecord, regBase+nOBSat, nBase-nOBSat,regRecord);
          652  +  }
   624    653     if( pSort->sortFlags & SORTFLAG_UseSorter ){
   625    654       op = OP_SorterInsert;
   626    655     }else{
   627    656       op = OP_IdxInsert;
   628    657     }
   629    658     sqlite3VdbeAddOp4Int(v, op, pSort->iECursor, regRecord,
   630    659                          regBase+nOBSat, nBase-nOBSat);
   631         -  if( iLimit ){
   632         -    int addr;
   633         -    int r1 = 0;
   634         -    /* Fill the sorter until it contains LIMIT+OFFSET entries.  (The iLimit
   635         -    ** register is initialized with value of LIMIT+OFFSET.)  After the sorter
   636         -    ** fills up, delete the least entry in the sorter after each insert.
   637         -    ** Thus we never hold more than the LIMIT+OFFSET rows in memory at once */
   638         -    addr = sqlite3VdbeAddOp1(v, OP_IfNotZero, iLimit); VdbeCoverage(v);
   639         -    sqlite3VdbeAddOp1(v, OP_Last, pSort->iECursor);
   640         -    if( pSort->bOrderedInnerLoop ){
   641         -      r1 = ++pParse->nMem;
   642         -      sqlite3VdbeAddOp3(v, OP_Column, pSort->iECursor, nExpr, r1);
   643         -      VdbeComment((v, "seq"));
   644         -    }
   645         -    sqlite3VdbeAddOp1(v, OP_Delete, pSort->iECursor);
   646         -    if( pSort->bOrderedInnerLoop ){
   647         -      /* If the inner loop is driven by an index such that values from
   648         -      ** the same iteration of the inner loop are in sorted order, then
   649         -      ** immediately jump to the next iteration of an inner loop if the
   650         -      ** entry from the current iteration does not fit into the top
   651         -      ** LIMIT+OFFSET entries of the sorter. */
   652         -      int iBrk = sqlite3VdbeCurrentAddr(v) + 2;
   653         -      sqlite3VdbeAddOp3(v, OP_Eq, regBase+nExpr, iBrk, r1);
   654         -      sqlite3VdbeChangeP5(v, SQLITE_NULLEQ);
   655         -      VdbeCoverage(v);
   656         -    }
   657         -    sqlite3VdbeJumpHere(v, addr);
   658         -  }
   659    660   }
   660    661   
   661    662   /*
   662    663   ** Add code to implement the OFFSET
   663    664   */
   664    665   static void codeOffset(
   665    666     Vdbe *v,          /* Generate code into this VM */