/ Check-in [c3ae3697]
Login

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

Overview
Comment:Remove the sequence values from sorter records used by ORDER BY as well.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | orderby-planning
Files: files | file ages | folders
SHA1: c3ae3697832a00d4d5758988a8766bdbb691e6b8
User & Date: dan 2014-03-25 15:04:07
Context
2014-03-25
17:07
Fix a problem in the code added by [707ea170b3] causing vdbesort.c to sort unstably. check-in: d3e640af user: dan tags: orderby-planning
15:04
Remove the sequence values from sorter records used by ORDER BY as well. check-in: c3ae3697 user: dan tags: orderby-planning
2014-03-24
20:19
Omit the sequence value from sorter records used by GROUP BY queries that cannot use an index. check-in: 3f90abdd user: dan tags: orderby-planning
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

   463    463     SortCtx *pSort,        /* Information about the ORDER BY clause */
   464    464     Select *pSelect,       /* The whole SELECT statement */
   465    465     int regData,           /* First register holding data to be sorted */
   466    466     int nData,             /* Number of elements in the data array */
   467    467     int nPrefixReg         /* No. of reg prior to regData available for use */
   468    468   ){
   469    469     Vdbe *v = pParse->pVdbe;                         /* Stmt under construction */
          470  +  int bSeq = ((pSort->sortFlags & SORTFLAG_UseSorter)==0);
   470    471     int nExpr = pSort->pOrderBy->nExpr;              /* No. of ORDER BY terms */
   471         -  int nBase = nExpr + 1 + nData;                   /* Fields in sorter record */
          472  +  int nBase = nExpr + bSeq + nData;                /* Fields in sorter record */
   472    473     int regBase;                                     /* Regs for sorter record */
   473         -  int regRecord = sqlite3GetTempReg(pParse);       /* Assemblied sorter record */
   474         -  int nOBSat = pSort->nOBSat;                      /* No. ORDER BY terms to skip */
   475         -  int op;                               /* Opcode to add sorter record to sorter */
          474  +  int regRecord = sqlite3GetTempReg(pParse);       /* Assembled sorter record */
          475  +  int nOBSat = pSort->nOBSat;                      /* ORDER BY terms to skip */
          476  +  int op;                            /* Opcode to add sorter record to sorter */
   476    477   
          478  +  assert( bSeq==0 || bSeq==1 );
   477    479     if( nPrefixReg ){
   478         -    assert( nPrefixReg==nExpr+1 );
   479         -    regBase = regData - nExpr - 1;
          480  +    assert( nPrefixReg==nExpr+bSeq );
          481  +    regBase = regData - nExpr - bSeq;
   480    482     }else{
   481    483       regBase = sqlite3GetTempRange(pParse, nBase);
   482    484     }
   483    485     sqlite3ExprCodeExprList(pParse, pSort->pOrderBy, regBase, SQLITE_ECEL_DUP);
   484         -  sqlite3VdbeAddOp2(v, OP_Sequence, pSort->iECursor, regBase+nExpr);
          486  +  if( bSeq ){
          487  +    sqlite3VdbeAddOp2(v, OP_Sequence, pSort->iECursor, regBase+nExpr);
          488  +  }
   485    489     if( nPrefixReg==0 ){
   486         -    sqlite3VdbeAddOp3(v, OP_Move, regData, regBase+nExpr+1, nData);
          490  +    sqlite3VdbeAddOp3(v, OP_Move, regData, regBase+nExpr+bSeq, nData);
   487    491     }
          492  +
   488    493     sqlite3VdbeAddOp3(v, OP_MakeRecord, regBase+nOBSat, nBase-nOBSat, regRecord);
   489    494     if( nOBSat>0 ){
   490    495       int regPrevKey;   /* The first nOBSat columns of the previous row */
   491    496       int addrFirst;    /* Address of the OP_IfNot opcode */
   492    497       int addrJmp;      /* Address of the OP_Jump opcode */
   493    498       VdbeOp *pOp;      /* Opcode that opens the sorter */
   494    499       int nKey;         /* Number of sorting key columns, including OP_Sequence */
   495    500       KeyInfo *pKI;     /* Original KeyInfo on the sorter table */
   496    501   
   497    502       regPrevKey = pParse->nMem+1;
   498    503       pParse->nMem += pSort->nOBSat;
   499         -    nKey = nExpr - pSort->nOBSat + 1;
   500         -    addrFirst = sqlite3VdbeAddOp1(v, OP_IfNot, regBase+nExpr); VdbeCoverage(v);
          504  +    nKey = nExpr - pSort->nOBSat + bSeq;
          505  +    if( bSeq ){
          506  +      addrFirst = sqlite3VdbeAddOp1(v, OP_IfNot, regBase+nExpr); 
          507  +    }else{
          508  +      addrFirst = sqlite3VdbeAddOp1(v, OP_SequenceTest, pSort->iECursor);
          509  +    }
          510  +    VdbeCoverage(v);
   501    511       sqlite3VdbeAddOp3(v, OP_Compare, regPrevKey, regBase, pSort->nOBSat);
   502    512       pOp = sqlite3VdbeGetOp(v, pSort->addrSortIndex);
   503    513       if( pParse->db->mallocFailed ) return;
   504    514       pOp->p2 = nKey + 1;
   505    515       pKI = pOp->p4.pKeyInfo;
   506    516       memset(pKI->aSortOrder, 0, pKI->nField); /* Makes OP_Jump below testable */
   507    517       sqlite3VdbeChangeP4(v, -1, (char*)pKI, P4_KEYINFO);
................................................................................
   655    665   
   656    666     /* Pull the requested columns.
   657    667     */
   658    668     nResultCol = pEList->nExpr;
   659    669   
   660    670     if( pDest->iSdst==0 ){
   661    671       if( pSort ){
   662         -      nPrefixReg = pSort->pOrderBy->nExpr + 1;
          672  +      nPrefixReg = pSort->pOrderBy->nExpr;
          673  +      if( !(pSort->sortFlags & SORTFLAG_UseSorter) ) nPrefixReg++;
   663    674         pParse->nMem += nPrefixReg;
   664    675       }
   665    676       pDest->iSdst = pParse->nMem+1;
   666    677       pParse->nMem += nResultCol;
   667    678     }else if( pDest->iSdst+nResultCol > pParse->nMem ){
   668    679       /* This is an error condition that can result, for example, when a SELECT
   669    680       ** on the right-hand side of an INSERT contains more result columns than
................................................................................
  1148   1159     int regRow;
  1149   1160     int regRowid;
  1150   1161     int nKey;
  1151   1162     int iSortTab;                   /* Sorter cursor to read from */
  1152   1163     int nSortData;                  /* Trailing values to read from sorter */
  1153   1164     u8 p5;                          /* p5 parameter for 1st OP_Column */
  1154   1165     int i;
         1166  +  int bSeq;                       /* True if sorter record includes seq. no. */
  1155   1167   #ifdef SQLITE_ENABLE_EXPLAIN_COMMENTS
  1156   1168     struct ExprList_item *aOutEx = p->pEList->a;
  1157   1169   #endif
  1158   1170   
  1159   1171     if( pSort->labelBkOut ){
  1160   1172       sqlite3VdbeAddOp2(v, OP_Gosub, pSort->regReturn, pSort->labelBkOut);
  1161   1173       sqlite3VdbeAddOp2(v, OP_Goto, 0, addrBreak);
................................................................................
  1181   1193       sqlite3VdbeAddOp3(v, OP_OpenPseudo, iSortTab, regSortOut, nKey+1+nSortData);
  1182   1194       if( addrOnce ) sqlite3VdbeJumpHere(v, addrOnce);
  1183   1195       addr = 1 + sqlite3VdbeAddOp2(v, OP_SorterSort, iTab, addrBreak);
  1184   1196       VdbeCoverage(v);
  1185   1197       codeOffset(v, p->iOffset, addrContinue);
  1186   1198       sqlite3VdbeAddOp2(v, OP_SorterData, iTab, regSortOut);
  1187   1199       p5 = OPFLAG_CLEARCACHE;
         1200  +    bSeq = 0;
  1188   1201     }else{
  1189   1202       addr = 1 + sqlite3VdbeAddOp2(v, OP_Sort, iTab, addrBreak); VdbeCoverage(v);
  1190   1203       codeOffset(v, p->iOffset, addrContinue);
  1191   1204       iSortTab = iTab;
  1192   1205       p5 = 0;
         1206  +    bSeq = 1;
  1193   1207     }
  1194   1208     for(i=0; i<nSortData; i++){
  1195         -    sqlite3VdbeAddOp3(v, OP_Column, iSortTab, nKey+1+i, regRow+i);
         1209  +    sqlite3VdbeAddOp3(v, OP_Column, iSortTab, nKey+bSeq+i, regRow+i);
  1196   1210       if( i==0 ) sqlite3VdbeChangeP5(v, p5);
  1197   1211       VdbeComment((v, "%s", aOutEx[i].zName ? aOutEx[i].zName : aOutEx[i].zSpan));
  1198   1212     }
  1199   1213     switch( eDest ){
  1200   1214       case SRT_Table:
  1201   1215       case SRT_EphemTab: {
  1202   1216         testcase( eDest==SRT_Table );

Changes to src/vdbe.c.

  3380   3380     if( pCx==0 ) goto no_mem;
  3381   3381     pCx->pKeyInfo = pOp->p4.pKeyInfo;
  3382   3382     assert( pCx->pKeyInfo->db==db );
  3383   3383     assert( pCx->pKeyInfo->enc==ENC(db) );
  3384   3384     rc = sqlite3VdbeSorterInit(db, pCx);
  3385   3385     break;
  3386   3386   }
         3387  +
         3388  +/* Opcode: SequenceTest P1 P2 * * *
         3389  +** Synopsis: if( cursor[P1].ctr++ ) pc = P2
         3390  +**
         3391  +** P1 is a sorter cursor. If the sequence counter is currently zero, jump
         3392  +** to P2. Regardless of whether or not the jump is taken, increment the
         3393  +** the sequence value.
         3394  +*/
         3395  +case OP_SequenceTest: {
         3396  +  VdbeCursor *pC;
         3397  +  assert( pOp->p1>=0 && pOp->p1<p->nCursor );
         3398  +  pC = p->apCsr[pOp->p1];
         3399  +  assert( pC->pSorter );
         3400  +  if( (pC->seqCount++)==0 ){
         3401  +    pc = pOp->p2 - 1;
         3402  +  }
         3403  +  break;
         3404  +}
  3387   3405   
  3388   3406   /* Opcode: OpenPseudo P1 P2 P3 * *
  3389   3407   ** Synopsis: P3 columns in r[P2]
  3390   3408   **
  3391   3409   ** Open a new cursor that points to a fake table that contains a single
  3392   3410   ** row of data.  The content of that one row is the content of memory
  3393   3411   ** register P2.  In other words, cursor P1 becomes an alias for the