/ Check-in [a5c2a54a]
Login

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

Overview
Comment:Add code to handle recursive CTEs.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | common-table-expr
Files: files | file ages | folders
SHA1: a5c2a54a07d35166911abc792008c05dea897742
User & Date: dan 2014-01-14 20:14:09
Context
2014-01-15
02:40
Use the user-supplied table name in WITH RECURSIVE tables as the internal name of the table and the name of the table in VDBE comments. check-in: a2933023 user: drh tags: common-table-expr
2014-01-14
20:14
Add code to handle recursive CTEs. check-in: a5c2a54a user: dan tags: common-table-expr
2014-01-13
16:36
Fix some memory leaks and crashes that could follow an OOM condition during WITH clause parsing. check-in: 8839850c user: dan tags: common-table-expr
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/btree.c.

  7346   7346     int freePageFlag,        /* Deallocate page if true */
  7347   7347     int *pnChange            /* Add number of Cells freed to this counter */
  7348   7348   ){
  7349   7349     MemPage *pPage;
  7350   7350     int rc;
  7351   7351     unsigned char *pCell;
  7352   7352     int i;
         7353  +  int hdr;
  7353   7354   
  7354   7355     assert( sqlite3_mutex_held(pBt->mutex) );
  7355   7356     if( pgno>btreePagecount(pBt) ){
  7356   7357       return SQLITE_CORRUPT_BKPT;
  7357   7358     }
  7358   7359   
  7359   7360     rc = getAndInitPage(pBt, pgno, &pPage, 0);
  7360   7361     if( rc ) return rc;
         7362  +  hdr = pPage->hdrOffset;
  7361   7363     for(i=0; i<pPage->nCell; i++){
  7362   7364       pCell = findCell(pPage, i);
  7363   7365       if( !pPage->leaf ){
  7364   7366         rc = clearDatabasePage(pBt, get4byte(pCell), 1, pnChange);
  7365   7367         if( rc ) goto cleardatabasepage_out;
  7366   7368       }
  7367   7369       rc = clearCell(pPage, pCell);
  7368   7370       if( rc ) goto cleardatabasepage_out;
  7369   7371     }
  7370   7372     if( !pPage->leaf ){
  7371         -    rc = clearDatabasePage(pBt, get4byte(&pPage->aData[8]), 1, pnChange);
         7373  +    rc = clearDatabasePage(pBt, get4byte(&pPage->aData[hdr+8]), 1, pnChange);
  7372   7374       if( rc ) goto cleardatabasepage_out;
  7373   7375     }else if( pnChange ){
  7374   7376       assert( pPage->intKey );
  7375   7377       *pnChange += pPage->nCell;
  7376   7378     }
  7377   7379     if( freePageFlag ){
  7378   7380       freePage(pPage, &rc);
  7379   7381     }else if( (rc = sqlite3PagerWrite(pPage->pDbPage))==0 ){
  7380         -    zeroPage(pPage, pPage->aData[0] | PTF_LEAF);
         7382  +    zeroPage(pPage, pPage->aData[hdr] | PTF_LEAF);
  7381   7383     }
  7382   7384   
  7383   7385   cleardatabasepage_out:
  7384   7386     releasePage(pPage);
  7385   7387     return rc;
  7386   7388   }
  7387   7389   

Changes to src/expr.c.

  1051   1051     pNew->iOffset = 0;
  1052   1052     pNew->selFlags = p->selFlags & ~SF_UsesEphemeral;
  1053   1053     pNew->pRightmost = 0;
  1054   1054     pNew->addrOpenEphm[0] = -1;
  1055   1055     pNew->addrOpenEphm[1] = -1;
  1056   1056     pNew->addrOpenEphm[2] = -1;
  1057   1057     pNew->pWith = withDup(db, p->pWith);
         1058  +  pNew->pRecurse = p->pRecurse;
  1058   1059     return pNew;
  1059   1060   }
  1060   1061   #else
  1061   1062   Select *sqlite3SelectDup(sqlite3 *db, Select *p, int flags){
  1062   1063     assert( p==0 );
  1063   1064     return 0;
  1064   1065   }

Changes to src/select.c.

   687    687         sqlite3VdbeAddOp3(v, OP_IdxDelete, iParm, regResult, nColumn);
   688    688         break;
   689    689       }
   690    690   #endif
   691    691   
   692    692       /* Store the result as data using a unique key.
   693    693       */
          694  +    case SRT_DistTable:
   694    695       case SRT_Table:
   695    696       case SRT_EphemTab: {
   696    697         int r1 = sqlite3GetTempReg(pParse);
   697    698         testcase( eDest==SRT_Table );
   698    699         testcase( eDest==SRT_EphemTab );
   699    700         sqlite3VdbeAddOp3(v, OP_MakeRecord, regResult, nColumn, r1);
          701  +#ifndef SQLITE_OMIT_CTE
          702  +      if( eDest==SRT_DistTable ){
          703  +        /* If the destination is DistTable, then cursor (iParm+1) is open
          704  +        ** on an ephemeral index. If the current row is already present
          705  +        ** in the index, do not write it to the output. If not, add the
          706  +        ** current row to the index and proceed with writing it to the
          707  +        ** output table as well.  */
          708  +        int addr = sqlite3VdbeCurrentAddr(v) + 4;
          709  +        sqlite3VdbeAddOp4Int(v, OP_Found, iParm+1, addr, r1, 0);
          710  +        sqlite3VdbeAddOp2(v, OP_IdxInsert, iParm+1, r1);
          711  +        assert( pOrderBy==0 );
          712  +      }
          713  +#endif
   700    714         if( pOrderBy ){
   701    715           pushOntoSorter(pParse, pOrderBy, p, r1);
   702    716         }else{
   703    717           int r2 = sqlite3GetTempReg(pParse);
   704    718           sqlite3VdbeAddOp2(v, OP_NewRowid, iParm, r2);
   705    719           sqlite3VdbeAddOp3(v, OP_Insert, iParm, r1, r2);
   706    720           sqlite3VdbeChangeP5(v, OPFLAG_APPEND);
................................................................................
  1725   1739     int iSub2;            /* EQP id of right-hand query */
  1726   1740   #endif
  1727   1741   
  1728   1742     /* Make sure there is no ORDER BY or LIMIT clause on prior SELECTs.  Only
  1729   1743     ** the last (right-most) SELECT in the series may have an ORDER BY or LIMIT.
  1730   1744     */
  1731   1745     assert( p && p->pPrior );  /* Calling function guarantees this much */
         1746  +  assert( p->pRecurse==0 || p->op==TK_ALL || p->op==TK_UNION );
  1732   1747     db = pParse->db;
  1733   1748     pPrior = p->pPrior;
  1734   1749     assert( pPrior->pRightmost!=pPrior );
  1735   1750     assert( pPrior->pRightmost==p->pRightmost );
  1736   1751     dest = *pDest;
  1737   1752     if( pPrior->pOrderBy ){
  1738   1753       sqlite3ErrorMsg(pParse,"ORDER BY clause should come after %s not before",
................................................................................
  1769   1784       }else{
  1770   1785         sqlite3ErrorMsg(pParse, "SELECTs to the left and right of %s"
  1771   1786           " do not have the same number of result columns", selectOpName(p->op));
  1772   1787       }
  1773   1788       rc = 1;
  1774   1789       goto multi_select_end;
  1775   1790     }
         1791  +
         1792  +  /* If this is a recursive query, check that there is no ORDER BY or
         1793  +  ** LIMIT clause. Neither of these are supported.  */
         1794  +  assert( p->pOffset==0 || p->pLimit );
         1795  +  if( p->pRecurse && (p->pOrderBy || p->pLimit) ){
         1796  +    sqlite3ErrorMsg(pParse, "%s in a recursive query is not allowed",
         1797  +        p->pOrderBy ? "ORDER BY" : "LIMIT"
         1798  +    );
         1799  +    goto multi_select_end;
         1800  +  }
  1776   1801   
  1777   1802     /* Compound SELECTs that have an ORDER BY clause are handled separately.
  1778   1803     */
  1779   1804     if( p->pOrderBy ){
  1780   1805       return multiSelectOrderBy(pParse, p, pDest);
  1781   1806     }
         1807  +
         1808  +#ifndef SQLITE_OMIT_CTE
         1809  +  if( p->pRecurse ){
         1810  +    int nCol = p->pEList->nExpr;
         1811  +    int addrNext;
         1812  +    int addrSwap;
         1813  +    int iCont, iBreak;
         1814  +    int tmp1, tmp2;               /* Cursors used to access temporary tables */
         1815  +    int tmp3 = 0;                 /* To ensure unique results if UNION */
         1816  +    int eDest = SRT_Table;
         1817  +    SelectDest tmp2dest;
         1818  +
         1819  +    iBreak = sqlite3VdbeMakeLabel(v);
         1820  +    iCont = sqlite3VdbeMakeLabel(v);
         1821  +
         1822  +    tmp1 = pParse->nTab++;
         1823  +    tmp2 = pParse->nTab++;
         1824  +    if( p->op==TK_UNION ){
         1825  +      eDest = SRT_DistTable;
         1826  +      tmp3 = pParse->nTab++;
         1827  +    }
         1828  +    sqlite3SelectDestInit(&tmp2dest, eDest, tmp2);
         1829  +
         1830  +    sqlite3VdbeAddOp2(v, OP_OpenEphemeral, tmp1, nCol);
         1831  +    sqlite3VdbeAddOp2(v, OP_OpenEphemeral, tmp2, nCol);
         1832  +    if( tmp3 ){
         1833  +      p->addrOpenEphm[0] = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, tmp3, 0);
         1834  +      p->selFlags |= SF_UsesEphemeral;
         1835  +    }
         1836  +
         1837  +    /* Store the results of the initial SELECT in tmp2. */
         1838  +    rc = sqlite3Select(pParse, pPrior, &tmp2dest);
         1839  +    if( rc ) goto multi_select_end;
         1840  +
         1841  +    /* Clear tmp1. Then switch the contents of tmp1 and tmp2. Then teturn 
         1842  +    ** the contents of tmp1 to the caller. Or, if tmp1 is empty at this
         1843  +    ** point, the recursive query has finished - jump to address iBreak.  */
         1844  +    addrSwap = sqlite3VdbeAddOp2(v, OP_SwapCursors, tmp1, tmp2);
         1845  +    sqlite3VdbeAddOp2(v, OP_Rewind, tmp1, iBreak);
         1846  +    addrNext = sqlite3VdbeCurrentAddr(v);
         1847  +    selectInnerLoop(pParse, p, p->pEList, tmp1, p->pEList->nExpr,
         1848  +        0, 0, &dest, iCont, iBreak);
         1849  +    sqlite3VdbeResolveLabel(v, iCont);
         1850  +    sqlite3VdbeAddOp2(v, OP_Next, tmp1, addrNext);
         1851  +
         1852  +    /* Execute the recursive SELECT. Store the results in tmp2. While this
         1853  +    ** SELECT is running, the contents of tmp1 are read by recursive 
         1854  +    ** references to the current CTE.  */
         1855  +    p->pPrior = 0;
         1856  +    p->pRecurse->tnum = tmp1;
         1857  +    p->pRecurse->tabFlags |= TF_Recursive;
         1858  +    rc = sqlite3Select(pParse, p, &tmp2dest);
         1859  +    p->pRecurse->tabFlags &= ~TF_Recursive;
         1860  +    p->pPrior = pPrior;
         1861  +    if( rc ) goto multi_select_end;
         1862  +
         1863  +    sqlite3VdbeAddOp2(v, OP_Goto, 0, addrSwap);
         1864  +    sqlite3VdbeResolveLabel(v, iBreak);
         1865  +  }else
         1866  +#endif
  1782   1867   
  1783   1868     /* Generate code for the left and right SELECT statements.
  1784   1869     */
  1785   1870     switch( p->op ){
  1786   1871       case TK_ALL: {
  1787   1872         int addr = 0;
  1788   1873         int nLimit;
................................................................................
  3445   3530   static void ctePop(Parse *pParse, struct Cte *pCte){
  3446   3531     if( pCte ){
  3447   3532       assert( pParse->pCte==pCte );
  3448   3533       pParse->pCte = pCte->pOuterCte;
  3449   3534     }
  3450   3535   }
  3451   3536   
         3537  +static int withExpand(
         3538  +  Walker *pWalker, 
         3539  +  struct SrcList_item *pFrom,
         3540  +  struct Cte *pCte
         3541  +){
         3542  +  Table *pTab;
         3543  +  Parse *pParse = pWalker->pParse;
         3544  +  sqlite3 *db = pParse->db;
         3545  +
         3546  +  assert( pFrom->pSelect==0 );
         3547  +  assert( pFrom->pTab==0 );
         3548  +
         3549  +  if( pCte==pParse->pCte && (pTab = pCte->pTab) ){
         3550  +    /* This is the recursive part of a recursive CTE */
         3551  +    pFrom->pTab = pTab;
         3552  +    pTab->nRef++;
         3553  +  }else{
         3554  +    ExprList *pEList;
         3555  +    Select *pSel;
         3556  +    int bRecursive;
         3557  +
         3558  +    pFrom->pTab = pTab = sqlite3DbMallocZero(db, sizeof(Table));
         3559  +    if( pTab==0 ) return WRC_Abort;
         3560  +    pTab->nRef = 1;
         3561  +    pTab->zName = sqlite3MPrintf(db, "sqlite_sq_%p", (void*)pTab);
         3562  +    pTab->iPKey = -1;
         3563  +    pTab->nRowEst = 1048576;
         3564  +    pTab->tabFlags |= TF_Ephemeral;
         3565  +    pFrom->pSelect = sqlite3SelectDup(db, pCte->pSelect, 0);
         3566  +    if( db->mallocFailed ) return SQLITE_NOMEM;
         3567  +    assert( pFrom->pSelect );
         3568  +
         3569  +    if( ctePush(pParse, pCte) ) return WRC_Abort;
         3570  +    pSel = pFrom->pSelect;
         3571  +    bRecursive = (pSel->op==TK_ALL || pSel->op==TK_UNION);
         3572  +    if( bRecursive ){
         3573  +      assert( pSel->pPrior );
         3574  +      sqlite3WalkSelect(pWalker, pSel->pPrior);
         3575  +    }else{
         3576  +      sqlite3WalkSelect(pWalker, pSel);
         3577  +    }
         3578  +
         3579  +    if( pCte->pCols ){
         3580  +      pEList = pCte->pCols;
         3581  +    }else{
         3582  +      Select *pLeft;
         3583  +      for(pLeft=pSel; pLeft->pPrior; pLeft=pLeft->pPrior);
         3584  +      pEList = pLeft->pEList;
         3585  +    }
         3586  +    selectColumnsFromExprList(pParse, pEList, &pTab->nCol, &pTab->aCol);
         3587  +
         3588  +    if( bRecursive ){
         3589  +      int nRef = pTab->nRef;
         3590  +      pCte->pTab = pTab;
         3591  +      sqlite3WalkSelect(pWalker, pSel);
         3592  +      pCte->pTab = 0;
         3593  +      if( pTab->nRef > nRef){
         3594  +        pSel->pRecurse = pTab;
         3595  +        assert( pTab->tnum==0 );
         3596  +      }
         3597  +    }
         3598  +
         3599  +    ctePop(pParse, pCte);
         3600  +  }
         3601  +
         3602  +  return SQLITE_OK;
         3603  +}
  3452   3604   
  3453   3605   /*
  3454   3606   ** This routine is a Walker callback for "expanding" a SELECT statement.
  3455   3607   ** "Expanding" means to do the following:
  3456   3608   **
  3457   3609   **    (1)  Make sure VDBE cursor numbers have been assigned to every
  3458   3610   **         element of the FROM clause.
................................................................................
  3511   3663         ** to go further. */
  3512   3664         assert( i==0 );
  3513   3665         return WRC_Prune;
  3514   3666       }
  3515   3667   #ifndef SQLITE_OMIT_CTE
  3516   3668       pCte = searchWith(pParse, pFrom);
  3517   3669       if( pCte ){
  3518         -      pFrom->pSelect = sqlite3SelectDup(db, pCte->pSelect, 0);
  3519         -      if( pFrom->pSelect==0 ) return WRC_Abort;
  3520         -    }
         3670  +      if( withExpand(pWalker, pFrom, pCte) ) return WRC_Abort;
         3671  +    }else
  3521   3672   #endif
  3522         -    if( pFrom->zName==0 || pCte ){
         3673  +    if( pFrom->zName==0 ){
  3523   3674   #ifndef SQLITE_OMIT_SUBQUERY
  3524         -      ExprList *pEList;
  3525   3675         Select *pSel = pFrom->pSelect;
  3526   3676         /* A sub-query in the FROM clause of a SELECT */
  3527   3677         assert( pSel!=0 );
  3528   3678         assert( pFrom->pTab==0 );
  3529         -      if( ctePush(pParse, pCte) ) return WRC_Abort;
  3530   3679         sqlite3WalkSelect(pWalker, pSel);
  3531         -      ctePop(pParse, pCte);
  3532   3680         pFrom->pTab = pTab = sqlite3DbMallocZero(db, sizeof(Table));
  3533   3681         if( pTab==0 ) return WRC_Abort;
  3534   3682         pTab->nRef = 1;
  3535   3683         pTab->zName = sqlite3MPrintf(db, "sqlite_sq_%p", (void*)pTab);
  3536   3684         while( pSel->pPrior ){ pSel = pSel->pPrior; }
  3537         -      if( pCte && pCte->pCols ){
  3538         -        pEList = pCte->pCols;
  3539         -      }else{
  3540         -        pEList = pSel->pEList;
  3541         -      }
  3542         -      selectColumnsFromExprList(pParse, pEList, &pTab->nCol, &pTab->aCol);
         3685  +      selectColumnsFromExprList(pParse, pSel->pEList, &pTab->nCol, &pTab->aCol);
  3543   3686         pTab->iPKey = -1;
  3544   3687         pTab->nRowEst = 1048576;
  3545   3688         pTab->tabFlags |= TF_Ephemeral;
  3546   3689   #endif
  3547   3690       }else{
  3548   3691         /* An ordinary table or view name in the FROM clause */
  3549   3692         assert( pFrom->pTab==0 );
................................................................................
  3827   3970       pParse = pWalker->pParse;
  3828   3971       pTabList = p->pSrc;
  3829   3972       for(i=0, pFrom=pTabList->a; i<pTabList->nSrc; i++, pFrom++){
  3830   3973         Table *pTab = pFrom->pTab;
  3831   3974         if( ALWAYS(pTab!=0) && (pTab->tabFlags & TF_Ephemeral)!=0 ){
  3832   3975           /* A sub-query in the FROM clause of a SELECT */
  3833   3976           Select *pSel = pFrom->pSelect;
  3834         -        assert( pSel );
  3835         -        while( pSel->pPrior ) pSel = pSel->pPrior;
  3836         -        selectAddColumnTypeAndCollation(pParse, pTab, pSel);
         3977  +        if( pSel ){
         3978  +          while( pSel->pPrior ) pSel = pSel->pPrior;
         3979  +          selectAddColumnTypeAndCollation(pParse, pTab, pSel);
         3980  +        }
  3837   3981         }
  3838   3982       }
  3839   3983     }
  3840   3984     return WRC_Continue;
  3841   3985   }
  3842   3986   #endif
  3843   3987   

Changes to src/sqliteInt.h.

  1425   1425   #endif
  1426   1426     Trigger *pTrigger;   /* List of triggers stored in pSchema */
  1427   1427     Schema *pSchema;     /* Schema that contains this table */
  1428   1428     Table *pNextZombie;  /* Next on the Parse.pZombieTab list */
  1429   1429   };
  1430   1430   
  1431   1431   /*
  1432         -** Allowed values for Tabe.tabFlags.
         1432  +** Allowed values for Table.tabFlags.
  1433   1433   */
  1434   1434   #define TF_Readonly        0x01    /* Read-only system table */
  1435   1435   #define TF_Ephemeral       0x02    /* An ephemeral table */
  1436   1436   #define TF_HasPrimaryKey   0x04    /* Table has a primary key */
  1437   1437   #define TF_Autoincrement   0x08    /* Integer primary key is autoincrement */
  1438   1438   #define TF_Virtual         0x10    /* Is a virtual table */
  1439   1439   #define TF_WithoutRowid    0x20    /* No rowid used. PRIMARY KEY is the key */
         1440  +#define TF_Recursive       0x40    /* Recursive reference within CTE */
  1440   1441   
  1441   1442   
  1442   1443   /*
  1443   1444   ** Test to see whether or not a table is a virtual table.  This is
  1444   1445   ** done as a macro so that it will be optimized out when virtual
  1445   1446   ** table support is omitted from the build.
  1446   1447   */
................................................................................
  2125   2126   ** enough information about the compound query is known at that point.
  2126   2127   ** The KeyInfo for addrOpenTran[0] and [1] contains collating sequences
  2127   2128   ** for the result set.  The KeyInfo for addrOpenEphm[2] contains collating
  2128   2129   ** sequences for the ORDER BY clause.
  2129   2130   */
  2130   2131   struct Select {
  2131   2132     ExprList *pEList;      /* The fields of the result */
         2133  +  Table *pRecurse;       /* Non-NULL for the recursive part of recursive CTE */
  2132   2134     u8 op;                 /* One of: TK_UNION TK_ALL TK_INTERSECT TK_EXCEPT */
  2133   2135     u16 selFlags;          /* Various SF_* values */
  2134   2136     int iLimit, iOffset;   /* Memory registers holding LIMIT & OFFSET counters */
  2135   2137     int addrOpenEphm[3];   /* OP_OpenEphem opcodes related to this select */
  2136   2138     u64 nSelectRow;        /* Estimated number of result rows */
  2137   2139     SrcList *pSrc;         /* The FROM clause */
  2138   2140     Expr *pWhere;          /* The WHERE clause */
................................................................................
  2178   2180   
  2179   2181   #define SRT_Output       5  /* Output each row of result */
  2180   2182   #define SRT_Mem          6  /* Store result in a memory cell */
  2181   2183   #define SRT_Set          7  /* Store results as keys in an index */
  2182   2184   #define SRT_Table        8  /* Store result as data with an automatic rowid */
  2183   2185   #define SRT_EphemTab     9  /* Create transient tab and store like SRT_Table */
  2184   2186   #define SRT_Coroutine   10  /* Generate a single row of result */
         2187  +#define SRT_DistTable   11  /* Like SRT_TABLE, but unique results only */
  2185   2188   
  2186   2189   /*
  2187   2190   ** An instance of this object describes where to put of the results of
  2188   2191   ** a SELECT statement.
  2189   2192   */
  2190   2193   struct SelectDest {
  2191   2194     u8 eDest;         /* How to dispose of the results.  On of SRT_* above. */
................................................................................
  2643   2646     int nCte;                       /* Number of CTEs */
  2644   2647     With *pOuter;                   /* Containing WITH clause, or NULL */
  2645   2648     struct Cte {
  2646   2649       char *zName;                  /* Name of this CTE */
  2647   2650       ExprList *pCols;              /* List of explicit column names, or NULL */
  2648   2651       Select *pSelect;              /* The contents of the CTE */
  2649   2652       struct Cte *pOuterCte;
         2653  +    Table *pTab;
  2650   2654     } a[1];
  2651   2655   };
  2652   2656   
  2653   2657   /*
  2654   2658   ** Assuming zIn points to the first byte of a UTF-8 character,
  2655   2659   ** advance zIn to point to the first byte of the next UTF-8 character.
  2656   2660   */

Changes to src/vdbe.c.

  3364   3364         rc = sqlite3BtreeCursor(pCx->pBt, MASTER_ROOT, 1, 0, pCx->pCursor);
  3365   3365         pCx->isTable = 1;
  3366   3366       }
  3367   3367     }
  3368   3368     pCx->isOrdered = (pOp->p5!=BTREE_UNORDERED);
  3369   3369     break;
  3370   3370   }
         3371  +
         3372  +/* Opcode: OpenEphreader P1 P2 * * *
         3373  +**
         3374  +** P2 is a cursor opened by the OpenEphemeral opcode. This opcode opens
         3375  +** a new read-only cursor named P1 that accesses the same epheremal table 
         3376  +** as P2.
         3377  +*/
         3378  +case OP_OpenEphreader: {
         3379  +  VdbeCursor *pEph;
         3380  +  VdbeCursor *pCx;
         3381  +  Pgno pgno;
         3382  +
         3383  +  pEph = p->apCsr[pOp->p2];
         3384  +  pCx = allocateCursor(p, pOp->p1, pEph->nField, -1, 1);
         3385  +  if( pCx==0 ) goto no_mem;
         3386  +  pCx->nullRow = 1;
         3387  +  pCx->pKeyInfo = pEph->pKeyInfo;
         3388  +  pCx->isTable = pEph->isTable;
         3389  +  pCx->isOrdered = pEph->isOrdered;
         3390  +  pgno = MASTER_ROOT + !pCx->isTable;
         3391  +  rc = sqlite3BtreeCursor(pEph->pBt, pgno, 0, pCx->pKeyInfo, pCx->pCursor);
         3392  +  break;
         3393  +}
         3394  +
         3395  +/* Opcode: SwapCursors P1 P2 * * *
         3396  +**
         3397  +** Parameters P1 and P2 are both cursors opened by the OpenEphemeral
         3398  +** opcode. This opcode deletes the contents of epheremal table P1,
         3399  +** then renames P2 to P1 and P1 to P2. In other words, following this
         3400  +** opcode cursor P2 is open on an empty table and P1 is open on the
         3401  +** table that was initially accessed by P2.
         3402  +*/
         3403  +case OP_SwapCursors: {
         3404  +  Mem tmp;
         3405  +  VdbeCursor *pTmp;
         3406  +
         3407  +  tmp = p->aMem[p->nMem - pOp->p1];
         3408  +  p->aMem[p->nMem - pOp->p1] = p->aMem[p->nMem - pOp->p2];
         3409  +  p->aMem[p->nMem - pOp->p2] = tmp;
         3410  +
         3411  +  pTmp = p->apCsr[pOp->p1];
         3412  +  p->apCsr[pOp->p1] = p->apCsr[pOp->p2];
         3413  +  p->apCsr[pOp->p2] = pTmp;
         3414  +
         3415  +  rc = sqlite3BtreeClearTable(pTmp->pBt, MASTER_ROOT + !pTmp->isTable, 0);
         3416  +  break;
         3417  +}
  3371   3418   
  3372   3419   /* Opcode: SorterOpen P1 * * P4 *
  3373   3420   **
  3374   3421   ** This opcode works like OP_OpenEphemeral except that it opens
  3375   3422   ** a transient index that is specifically designed to sort large
  3376   3423   ** tables using an external merge-sort algorithm.
  3377   3424   */

Changes to src/where.c.

  5649   5649       struct SrcList_item *pTabItem;
  5650   5650   
  5651   5651       pTabItem = &pTabList->a[pLevel->iFrom];
  5652   5652       pTab = pTabItem->pTab;
  5653   5653       iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
  5654   5654       pLoop = pLevel->pWLoop;
  5655   5655       if( (pTab->tabFlags & TF_Ephemeral)!=0 || pTab->pSelect ){
  5656         -      /* Do nothing */
         5656  +      if( pTab->tabFlags & TF_Recursive ){
         5657  +        int iCur = pTabItem->iCursor;
         5658  +        sqlite3VdbeAddOp2(v, OP_OpenEphreader, iCur, pTab->tnum);
         5659  +      }
         5660  +      /* Otherwise do nothing */
  5657   5661       }else
  5658   5662   #ifndef SQLITE_OMIT_VIRTUALTABLE
  5659   5663       if( (pLoop->wsFlags & WHERE_VIRTUALTABLE)!=0 ){
  5660   5664         const char *pVTab = (const char *)sqlite3GetVTable(db, pTab);
  5661   5665         int iCur = pTabItem->iCursor;
  5662   5666         sqlite3VdbeAddOp4(v, OP_VOpen, iCur, 0, 0, pVTab, P4_VTAB);
  5663   5667       }else if( IsVirtual(pTab) ){

Changes to test/with1.test.

   132    132   } {1 3 2 4}
   133    133   
   134    134   do_execsql_test 4.3 {
   135    135     WITH uset(a, b) AS ( SELECT 2, 8 UNION ALL SELECT 4, 9 )
   136    136     UPDATE t1 SET x = COALESCE( (SELECT b FROM uset WHERE a=x), x );
   137    137     SELECT * FROM t1;
   138    138   } {1 3 8 9}
          139  +
          140  +#-------------------------------------------------------------------------
          141  +#
          142  +do_execsql_test 5.1 {
          143  +  WITH i(x) AS ( VALUES(1) UNION ALL SELECT x+1 FROM i)
          144  +  SELECT x FROM i LIMIT 10;
          145  +} {1 2 3 4 5 6 7 8 9 10}
          146  +
          147  +do_catchsql_test 5.2 {
          148  +  WITH i(x) AS ( VALUES(1) UNION ALL SELECT x+1 FROM i ORDER BY 1)
          149  +  SELECT x FROM i LIMIT 10;
          150  +} {1 {ORDER BY in a recursive query is not allowed}}
          151  +
          152  +do_catchsql_test 5.3 {
          153  +  WITH i(x) AS ( VALUES(1) UNION ALL SELECT x+1 FROM i LIMIT 10 )
          154  +  SELECT x FROM i LIMIT 10;
          155  +} {1 {LIMIT in a recursive query is not allowed}}
          156  +
          157  +do_execsql_test 5.4 {
          158  +  WITH i(x) AS ( VALUES(1) UNION ALL SELECT (x+1)%10 FROM i)
          159  +  SELECT x FROM i LIMIT 20;
          160  +} {1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0}
          161  +
          162  +do_execsql_test 5.5 {
          163  +  WITH i(x) AS ( VALUES(1) UNION SELECT (x+1)%10 FROM i)
          164  +  SELECT x FROM i LIMIT 20;
          165  +} {1 2 3 4 5 6 7 8 9 0}
   139    166   
   140    167   finish_test
   141    168   
   142    169   
   143    170