/ Check-in [8b4f5080]
Login

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

Overview
Comment:When the block sorting optimization is used in a scalar subquery, be sure to exit the loop as soon as the first valid output row is received. Fix for ticket [cb3aa0641d9a4].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | branch-3.10
Files: files | file ages | folders
SHA1: 8b4f5080621a8c23d9d32bf578bb83edc43de5c3
User & Date: drh 2016-01-13 17:59:28
Context
2016-01-13
18:21
Add the SQLITE_FCNTL_JOURNAL_POINTER file control. check-in: ea9ce228 user: drh tags: branch-3.10
17:59
When the block sorting optimization is used in a scalar subquery, be sure to exit the loop as soon as the first valid output row is received. Fix for ticket [cb3aa0641d9a4]. check-in: 8b4f5080 user: drh tags: branch-3.10
17:50
When the block sorting optimization is used in a scalar subquery, be sure to exit the loop as soon as the first valid output row is received. Fix for ticket [cb3aa0641d9a4]. check-in: cdbb0947 user: drh tags: trunk
2016-01-06
11:01
Version 3.10.0 check-in: fd0a50f0 user: drh tags: trunk, release, version-3.10.0
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

    50     50   struct SortCtx {
    51     51     ExprList *pOrderBy;   /* The ORDER BY (or GROUP BY clause) */
    52     52     int nOBSat;           /* Number of ORDER BY terms satisfied by indices */
    53     53     int iECursor;         /* Cursor number for the sorter */
    54     54     int regReturn;        /* Register holding block-output return address */
    55     55     int labelBkOut;       /* Start label for the block-output subroutine */
    56     56     int addrSortIndex;    /* Address of the OP_SorterOpen or OP_OpenEphemeral */
           57  +  int labelDone;        /* Jump here when done, ex: LIMIT reached */
    57     58     u8 sortFlags;         /* Zero or more SORTFLAG_* bits */
    58     59   };
    59     60   #define SORTFLAG_UseSorter  0x01   /* Use SorterOpen instead of OpenEphemeral */
    60     61   
    61     62   /*
    62     63   ** Delete all the content of a Select structure.  Deallocate the structure
    63     64   ** itself only if bFree is true.
................................................................................
   504    505     int bSeq = ((pSort->sortFlags & SORTFLAG_UseSorter)==0);
   505    506     int nExpr = pSort->pOrderBy->nExpr;              /* No. of ORDER BY terms */
   506    507     int nBase = nExpr + bSeq + nData;                /* Fields in sorter record */
   507    508     int regBase;                                     /* Regs for sorter record */
   508    509     int regRecord = ++pParse->nMem;                  /* Assembled sorter record */
   509    510     int nOBSat = pSort->nOBSat;                      /* ORDER BY terms to skip */
   510    511     int op;                            /* Opcode to add sorter record to sorter */
          512  +  int iLimit;                        /* LIMIT counter */
   511    513   
   512    514     assert( bSeq==0 || bSeq==1 );
   513    515     assert( nData==1 || regData==regOrigData );
   514    516     if( nPrefixReg ){
   515    517       assert( nPrefixReg==nExpr+bSeq );
   516    518       regBase = regData - nExpr - bSeq;
   517    519     }else{
   518    520       regBase = pParse->nMem + 1;
   519    521       pParse->nMem += nBase;
   520    522     }
          523  +  assert( pSelect->iOffset==0 || pSelect->iLimit!=0 );
          524  +  iLimit = pSelect->iOffset ? pSelect->iOffset+1 : pSelect->iLimit;
          525  +  pSort->labelDone = sqlite3VdbeMakeLabel(v);
   521    526     sqlite3ExprCodeExprList(pParse, pSort->pOrderBy, regBase, regOrigData,
   522    527                             SQLITE_ECEL_DUP|SQLITE_ECEL_REF);
   523    528     if( bSeq ){
   524    529       sqlite3VdbeAddOp2(v, OP_Sequence, pSort->iECursor, regBase+nExpr);
   525    530     }
   526    531     if( nPrefixReg==0 ){
   527    532       sqlite3ExprCodeMove(pParse, regData, regBase+nExpr+bSeq, nData);
   528    533     }
   529         -
   530    534     sqlite3VdbeAddOp3(v, OP_MakeRecord, regBase+nOBSat, nBase-nOBSat, regRecord);
   531    535     if( nOBSat>0 ){
   532    536       int regPrevKey;   /* The first nOBSat columns of the previous row */
   533    537       int addrFirst;    /* Address of the OP_IfNot opcode */
   534    538       int addrJmp;      /* Address of the OP_Jump opcode */
   535    539       VdbeOp *pOp;      /* Opcode that opens the sorter */
   536    540       int nKey;         /* Number of sorting key columns, including OP_Sequence */
................................................................................
   557    561                                              pKI->nXField-1);
   558    562       addrJmp = sqlite3VdbeCurrentAddr(v);
   559    563       sqlite3VdbeAddOp3(v, OP_Jump, addrJmp+1, 0, addrJmp+1); VdbeCoverage(v);
   560    564       pSort->labelBkOut = sqlite3VdbeMakeLabel(v);
   561    565       pSort->regReturn = ++pParse->nMem;
   562    566       sqlite3VdbeAddOp2(v, OP_Gosub, pSort->regReturn, pSort->labelBkOut);
   563    567       sqlite3VdbeAddOp1(v, OP_ResetSorter, pSort->iECursor);
          568  +    if( iLimit ){
          569  +      sqlite3VdbeAddOp2(v, OP_IfNot, iLimit, pSort->labelDone);
          570  +      VdbeCoverage(v);
          571  +    }
   564    572       sqlite3VdbeJumpHere(v, addrFirst);
   565    573       sqlite3ExprCodeMove(pParse, regBase, regPrevKey, pSort->nOBSat);
   566    574       sqlite3VdbeJumpHere(v, addrJmp);
   567    575     }
   568    576     if( pSort->sortFlags & SORTFLAG_UseSorter ){
   569    577       op = OP_SorterInsert;
   570    578     }else{
   571    579       op = OP_IdxInsert;
   572    580     }
   573    581     sqlite3VdbeAddOp2(v, op, pSort->iECursor, regRecord);
   574         -  if( pSelect->iLimit ){
          582  +  if( iLimit ){
   575    583       int addr;
   576         -    int iLimit;
   577         -    if( pSelect->iOffset ){
   578         -      iLimit = pSelect->iOffset+1;
   579         -    }else{
   580         -      iLimit = pSelect->iLimit;
   581         -    }
   582    584       addr = sqlite3VdbeAddOp3(v, OP_IfNotZero, iLimit, 0, 1); VdbeCoverage(v);
   583    585       sqlite3VdbeAddOp1(v, OP_Last, pSort->iECursor);
   584    586       sqlite3VdbeAddOp1(v, OP_Delete, pSort->iECursor);
   585    587       sqlite3VdbeJumpHere(v, addr);
   586    588     }
   587    589   }
   588    590   
................................................................................
  1178   1180     Parse *pParse,    /* Parsing context */
  1179   1181     Select *p,        /* The SELECT statement */
  1180   1182     SortCtx *pSort,   /* Information on the ORDER BY clause */
  1181   1183     int nColumn,      /* Number of columns of data */
  1182   1184     SelectDest *pDest /* Write the sorted results here */
  1183   1185   ){
  1184   1186     Vdbe *v = pParse->pVdbe;                     /* The prepared statement */
  1185         -  int addrBreak = sqlite3VdbeMakeLabel(v);     /* Jump here to exit loop */
         1187  +  int addrBreak = pSort->labelDone;            /* Jump here to exit loop */
  1186   1188     int addrContinue = sqlite3VdbeMakeLabel(v);  /* Jump here for next cycle */
  1187   1189     int addr;
  1188   1190     int addrOnce = 0;
  1189   1191     int iTab;
  1190   1192     ExprList *pOrderBy = pSort->pOrderBy;
  1191   1193     int eDest = pDest->eDest;
  1192   1194     int iParm = pDest->iSDParm;
................................................................................
  1197   1199     int nSortData;                  /* Trailing values to read from sorter */
  1198   1200     int i;
  1199   1201     int bSeq;                       /* True if sorter record includes seq. no. */
  1200   1202   #ifdef SQLITE_ENABLE_EXPLAIN_COMMENTS
  1201   1203     struct ExprList_item *aOutEx = p->pEList->a;
  1202   1204   #endif
  1203   1205   
         1206  +  assert( addrBreak<0 );
  1204   1207     if( pSort->labelBkOut ){
  1205   1208       sqlite3VdbeAddOp2(v, OP_Gosub, pSort->regReturn, pSort->labelBkOut);
  1206   1209       sqlite3VdbeGoto(v, addrBreak);
  1207   1210       sqlite3VdbeResolveLabel(v, pSort->labelBkOut);
  1208   1211     }
  1209   1212     iTab = pSort->iECursor;
  1210   1213     if( eDest==SRT_Output || eDest==SRT_Coroutine ){

Changes to test/orderby1.test.

   523    523     INSERT INTO t1 SELECT i%2, randomblob(500) FROM cnt;
   524    524   }
   525    525   
   526    526   do_test 8.3 {
   527    527     db eval { SELECT * FROM t1 ORDER BY a, b } { incr res $a }
   528    528     set res
   529    529   } 5000
          530  +
          531  +#---------------------------------------------------------------------------
          532  +# https://www.sqlite.org/src/tktview/cb3aa0641d9a413841c004293a4fc06cdc122029
          533  +#
          534  +# Adverse interaction between scalar subqueries and the partial-sorting
          535  +# logic.
          536  +#
          537  +do_execsql_test 9.0 {
          538  +  DROP TABLE IF EXISTS t1;
          539  +  CREATE TABLE t1(x INTEGER PRIMARY KEY);
          540  +  INSERT INTO t1 VALUES(1),(2);
          541  +  DROP TABLE IF EXISTS t2;
          542  +  CREATE TABLE t2(y);
          543  +  INSERT INTO t2 VALUES(9),(8),(3),(4);
          544  +  SELECT (SELECT x||y FROM t2, t1 ORDER BY x, y);
          545  +} {13}
          546  +
   530    547   
   531    548   finish_test