/ Check-in [45d8cc67]
Login

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

Overview
Comment:Change the names of SRT_DistTable and SRT_Table used by CTE to more meaningful SRT_DistFifo and SRT_Fifo, respectively. Simplify the IgnorableOrderby() macro in the process.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 45d8cc678d128f1dda6469864215a8ed9de4366a
User & Date: drh 2014-03-21 19:56:09
Context
2014-03-21
20:58
Add the ability to use indices for the first few terms of an ORDER BY clause, then sort in batches to handle the later terms. check-in: fa06a6fe user: drh tags: trunk
19:56
Change the names of SRT_DistTable and SRT_Table used by CTE to more meaningful SRT_DistFifo and SRT_Fifo, respectively. Simplify the IgnorableOrderby() macro in the process. check-in: 45d8cc67 user: drh tags: trunk
19:27
Avoid leaking memory in an obscure case where the flattener adds an ORDER BY clause to the recursive part of a recursive query. check-in: 1f413aca user: dan tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

   696    696         sqlite3VdbeAddOp3(v, OP_IdxDelete, iParm, regResult, nResultCol);
   697    697         break;
   698    698       }
   699    699   #endif /* SQLITE_OMIT_COMPOUND_SELECT */
   700    700   
   701    701       /* Store the result as data using a unique key.
   702    702       */
   703         -    case SRT_DistTable:
          703  +    case SRT_Fifo:
          704  +    case SRT_DistFifo:
   704    705       case SRT_Table:
   705    706       case SRT_EphemTab: {
   706    707         int r1 = sqlite3GetTempReg(pParse);
   707    708         testcase( eDest==SRT_Table );
   708    709         testcase( eDest==SRT_EphemTab );
   709    710         sqlite3VdbeAddOp3(v, OP_MakeRecord, regResult, nResultCol, r1);
   710    711   #ifndef SQLITE_OMIT_CTE
   711         -      if( eDest==SRT_DistTable ){
   712         -        /* If the destination is DistTable, then cursor (iParm+1) is open
          712  +      if( eDest==SRT_DistFifo ){
          713  +        /* If the destination is DistFifo, then cursor (iParm+1) is open
   713    714           ** on an ephemeral index. If the current row is already present
   714    715           ** in the index, do not write it to the output. If not, add the
   715    716           ** current row to the index and proceed with writing it to the
   716    717           ** output table as well.  */
   717    718           int addr = sqlite3VdbeCurrentAddr(v) + 4;
   718    719           sqlite3VdbeAddOp4Int(v, OP_Found, iParm+1, addr, r1, 0); VdbeCoverage(v);
   719    720           sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm+1, r1);
................................................................................
  1831   1832     Select *pSetup = p->pPrior;   /* The setup query */
  1832   1833     int addrTop;                  /* Top of the loop */
  1833   1834     int addrCont, addrBreak;      /* CONTINUE and BREAK addresses */
  1834   1835     int iCurrent = 0;             /* The Current table */
  1835   1836     int regCurrent;               /* Register holding Current table */
  1836   1837     int iQueue;                   /* The Queue table */
  1837   1838     int iDistinct = 0;            /* To ensure unique results if UNION */
  1838         -  int eDest = SRT_Table;        /* How to write to Queue */
         1839  +  int eDest = SRT_Fifo;         /* How to write to Queue */
  1839   1840     SelectDest destQueue;         /* SelectDest targetting the Queue table */
  1840   1841     int i;                        /* Loop counter */
  1841   1842     int rc;                       /* Result code */
  1842   1843     ExprList *pOrderBy;           /* The ORDER BY clause */
  1843   1844     Expr *pLimit, *pOffset;       /* Saved LIMIT and OFFSET */
  1844   1845     int regLimit, regOffset;      /* Registers used by LIMIT and OFFSET */
  1845   1846   
................................................................................
  1863   1864         iCurrent = pSrc->a[i].iCursor;
  1864   1865         break;
  1865   1866       }
  1866   1867     }
  1867   1868   
  1868   1869     /* Allocate cursors numbers for Queue and Distinct.  The cursor number for
  1869   1870     ** the Distinct table must be exactly one greater than Queue in order
  1870         -  ** for the SRT_DistTable and SRT_DistQueue destinations to work. */
         1871  +  ** for the SRT_DistFifo and SRT_DistQueue destinations to work. */
  1871   1872     iQueue = pParse->nTab++;
  1872   1873     if( p->op==TK_UNION ){
  1873         -    eDest = pOrderBy ? SRT_DistQueue : SRT_DistTable;
         1874  +    eDest = pOrderBy ? SRT_DistQueue : SRT_DistFifo;
  1874   1875       iDistinct = pParse->nTab++;
  1875   1876     }else{
  1876         -    eDest = pOrderBy ? SRT_Queue : SRT_Table;
         1877  +    eDest = pOrderBy ? SRT_Queue : SRT_Fifo;
  1877   1878     }
  1878   1879     sqlite3SelectDestInit(&destQueue, eDest, iQueue);
  1879   1880   
  1880   1881     /* Allocate cursors for Current, Queue, and Distinct. */
  1881   1882     regCurrent = ++pParse->nMem;
  1882   1883     sqlite3VdbeAddOp3(v, OP_OpenPseudo, iCurrent, regCurrent, nCol);
  1883   1884     if( pOrderBy ){
................................................................................
  4484   4485     db = pParse->db;
  4485   4486     if( p==0 || db->mallocFailed || pParse->nErr ){
  4486   4487       return 1;
  4487   4488     }
  4488   4489     if( sqlite3AuthCheck(pParse, SQLITE_SELECT, 0, 0, 0) ) return 1;
  4489   4490     memset(&sAggInfo, 0, sizeof(sAggInfo));
  4490   4491   
  4491         -  assert( p->pOrderBy==0 || pDest->eDest!=SRT_DistTable );
         4492  +  assert( p->pOrderBy==0 || pDest->eDest!=SRT_DistFifo );
         4493  +  assert( p->pOrderBy==0 || pDest->eDest!=SRT_Fifo );
  4492   4494     assert( p->pOrderBy==0 || pDest->eDest!=SRT_DistQueue );
  4493   4495     assert( p->pOrderBy==0 || pDest->eDest!=SRT_Queue );
  4494   4496     if( IgnorableOrderby(pDest) ){
  4495   4497       assert(pDest->eDest==SRT_Exists || pDest->eDest==SRT_Union || 
  4496   4498              pDest->eDest==SRT_Except || pDest->eDest==SRT_Discard ||
  4497         -           pDest->eDest==SRT_Queue  || pDest->eDest==SRT_DistTable ||
  4498         -           pDest->eDest==SRT_DistQueue);
         4499  +           pDest->eDest==SRT_Queue  || pDest->eDest==SRT_DistFifo ||
         4500  +           pDest->eDest==SRT_DistQueue || pDest->eDest==SRT_Fifo);
  4499   4501       /* If ORDER BY makes no difference in the output then neither does
  4500   4502       ** DISTINCT so it can be removed too. */
  4501   4503       sqlite3ExprListDelete(db, p->pOrderBy);
  4502   4504       p->pOrderBy = 0;
  4503   4505       p->selFlags &= ~SF_Distinct;
  4504   4506     }
  4505   4507     sqlite3SelectPrep(pParse, p, 0);

Changes to src/sqliteInt.h.

  2257   2257   **     SRT_Coroutine   Generate a co-routine that returns a new row of
  2258   2258   **                     results each time it is invoked.  The entry point
  2259   2259   **                     of the co-routine is stored in register pDest->iSDParm
  2260   2260   **                     and the result row is stored in pDest->nDest registers
  2261   2261   **                     starting with pDest->iSdst.
  2262   2262   **
  2263   2263   **     SRT_Table       Store results in temporary table pDest->iSDParm.
  2264         -**                     This is like SRT_EphemTab except that the table
  2265         -**                     is assumed to already be open.
         2264  +**     SRT_Fifo        This is like SRT_EphemTab except that the table
         2265  +**                     is assumed to already be open.  SRT_Fifo has
         2266  +**                     the additional property of being able to ignore
         2267  +**                     the ORDER BY clause.
  2266   2268   **
  2267         -**     SRT_DistTable   Store results in a temporary table pDest->iSDParm.
         2269  +**     SRT_DistFifo    Store results in a temporary table pDest->iSDParm.
  2268   2270   **                     But also use temporary table pDest->iSDParm+1 as
  2269   2271   **                     a record of all prior results and ignore any duplicate
  2270         -**                     rows.  Name means:  "Distinct Table".
         2272  +**                     rows.  Name means:  "Distinct Fifo".
  2271   2273   **
  2272   2274   **     SRT_Queue       Store results in priority queue pDest->iSDParm (really
  2273   2275   **                     an index).  Append a sequence number so that all entries
  2274   2276   **                     are distinct.
  2275   2277   **
  2276   2278   **     SRT_DistQueue   Store results in priority queue pDest->iSDParm only if
  2277   2279   **                     the same record has never been stored before.  The
  2278   2280   **                     index at pDest->iSDParm+1 hold all prior stores.
  2279   2281   */
  2280   2282   #define SRT_Union        1  /* Store result as keys in an index */
  2281   2283   #define SRT_Except       2  /* Remove result from a UNION index */
  2282   2284   #define SRT_Exists       3  /* Store 1 if the result is not empty */
  2283   2285   #define SRT_Discard      4  /* Do not save the results anywhere */
         2286  +#define SRT_Fifo         5  /* Store result as data with an automatic rowid */
         2287  +#define SRT_DistFifo     6  /* Like SRT_Fifo, but unique results only */
         2288  +#define SRT_Queue        7  /* Store result in an queue */
         2289  +#define SRT_DistQueue    8  /* Like SRT_Queue, but unique results only */
  2284   2290   
  2285   2291   /* The ORDER BY clause is ignored for all of the above */
  2286         -#define IgnorableOrderby(X) ((X->eDest)<=SRT_Discard || (X->eDest)>SRT_Table)
         2292  +#define IgnorableOrderby(X) ((X->eDest)<=SRT_DistQueue)
  2287   2293   
  2288         -#define SRT_Output       5  /* Output each row of result */
  2289         -#define SRT_Mem          6  /* Store result in a memory cell */
  2290         -#define SRT_Set          7  /* Store results as keys in an index */
  2291         -#define SRT_EphemTab     8  /* Create transient tab and store like SRT_Table */
  2292         -#define SRT_Coroutine    9  /* Generate a single row of result */
  2293         -#define SRT_Table       10  /* Store result as data with an automatic rowid */
  2294         -#define SRT_DistTable   11  /* Like SRT_Table, but unique results only */
  2295         -#define SRT_Queue       12  /* Store result in an queue */
  2296         -#define SRT_DistQueue   13  /* Like SRT_Queue, but unique results only */
         2294  +#define SRT_Output       9  /* Output each row of result */
         2295  +#define SRT_Mem         10  /* Store result in a memory cell */
         2296  +#define SRT_Set         11  /* Store results as keys in an index */
         2297  +#define SRT_EphemTab    12  /* Create transient tab and store like SRT_Table */
         2298  +#define SRT_Coroutine   13  /* Generate a single row of result */
         2299  +#define SRT_Table       14  /* Store result as data with an automatic rowid */
  2297   2300   
  2298   2301   /*
  2299   2302   ** An instance of this object describes where to put of the results of
  2300   2303   ** a SELECT statement.
  2301   2304   */
  2302   2305   struct SelectDest {
  2303   2306     u8 eDest;            /* How to dispose of the results.  On of SRT_* above. */