/ Check-in [0171e3bb]
Login

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

Overview
Comment:Add support for common table expressions (WITH clauses).
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 0171e3bb4f663a9414b0e8b64c87b5d0683855b5
User & Date: dan 2014-01-17 15:15:10
Context
2014-01-17
15:27
Fix a compiler warning in selectPopWith(). check-in: c8eb1163 user: drh tags: trunk
15:15
Add support for common table expressions (WITH clauses). check-in: 0171e3bb user: dan tags: trunk
14:59
Fix some problems to do with WITH clauses and name resolution. Closed-Leaf check-in: 6a549187 user: dan tags: common-table-expr
2014-01-16
15:31
Always use available indices to optimize LIKE operators even if the pattern of the LIKE operator has a COLLATE modifier. This fixes an ineffiency that was introduced into 3.7.15 by check-in [8542e6180d4] on 2012-12-08. check-in: 16bd5478 user: drh tags: trunk
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/build.c.

  4194   4194         }else{
  4195   4195           pIdx->pKeyInfo = pKey;
  4196   4196         }
  4197   4197       }
  4198   4198     }
  4199   4199     return sqlite3KeyInfoRef(pIdx->pKeyInfo);
  4200   4200   }
         4201  +
         4202  +#ifndef SQLITE_OMIT_CTE
         4203  +/* 
         4204  +** This routine is invoked once per CTE by the parser while parsing a 
         4205  +** WITH clause. 
         4206  +*/
         4207  +With *sqlite3WithAdd(
         4208  +  Parse *pParse,          /* Parsing context */
         4209  +  With *pWith,            /* Existing WITH clause, or NULL */
         4210  +  Token *pName,           /* Name of the common-table */
         4211  +  ExprList *pArglist,     /* Optional column name list for the table */
         4212  +  Select *pQuery          /* Query used to initialize the table */
         4213  +){
         4214  +  sqlite3 *db = pParse->db;
         4215  +  With *pNew;
         4216  +  char *zName;
         4217  +
         4218  +  /* Check that the CTE name is unique within this WITH clause. If
         4219  +  ** not, store an error in the Parse structure. */
         4220  +  zName = sqlite3NameFromToken(pParse->db, pName);
         4221  +  if( zName && pWith ){
         4222  +    int i;
         4223  +    for(i=0; i<pWith->nCte; i++){
         4224  +      if( sqlite3StrICmp(zName, pWith->a[i].zName)==0 ){
         4225  +        sqlite3ErrorMsg(pParse, "duplicate WITH table name: %s", zName);
         4226  +      }
         4227  +    }
         4228  +  }
         4229  +
         4230  +  if( pWith ){
         4231  +    int nByte = sizeof(*pWith) + (sizeof(pWith->a[1]) * pWith->nCte);
         4232  +    pNew = sqlite3DbRealloc(db, pWith, nByte);
         4233  +  }else{
         4234  +    pNew = sqlite3DbMallocZero(db, sizeof(*pWith));
         4235  +  }
         4236  +  assert( zName!=0 || pNew==0 );
         4237  +  assert( db->mallocFailed==0 || pNew==0 );
         4238  +
         4239  +  if( pNew==0 ){
         4240  +    sqlite3ExprListDelete(db, pArglist);
         4241  +    sqlite3SelectDelete(db, pQuery);
         4242  +    sqlite3DbFree(db, zName);
         4243  +    pNew = pWith;
         4244  +  }else{
         4245  +    pNew->a[pNew->nCte].pSelect = pQuery;
         4246  +    pNew->a[pNew->nCte].pCols = pArglist;
         4247  +    pNew->a[pNew->nCte].zName = zName;
         4248  +    pNew->a[pNew->nCte].zErr = 0;
         4249  +    pNew->nCte++;
         4250  +  }
         4251  +
         4252  +  return pNew;
         4253  +}
         4254  +
         4255  +/*
         4256  +** Free the contents of the With object passed as the second argument.
         4257  +*/
         4258  +void sqlite3WithDelete(sqlite3 *db, With *pWith){
         4259  +  if( pWith ){
         4260  +    int i;
         4261  +    for(i=0; i<pWith->nCte; i++){
         4262  +      struct Cte *pCte = &pWith->a[i];
         4263  +      sqlite3ExprListDelete(db, pCte->pCols);
         4264  +      sqlite3SelectDelete(db, pCte->pSelect);
         4265  +      sqlite3DbFree(db, pCte->zName);
         4266  +    }
         4267  +    sqlite3DbFree(db, pWith);
         4268  +  }
         4269  +}
         4270  +#endif /* !defined(SQLITE_OMIT_CTE) */

Changes to src/expr.c.

   891    891         }
   892    892   
   893    893       }
   894    894     }
   895    895     return pNew;
   896    896   }
   897    897   
          898  +/*
          899  +** Create and return a deep copy of the object passed as the second 
          900  +** argument. If an OOM condition is encountered, NULL is returned
          901  +** and the db->mallocFailed flag set.
          902  +*/
          903  +#ifndef SQLITE_OMIT_CTE
          904  +static With *withDup(sqlite3 *db, With *p){
          905  +  With *pRet = 0;
          906  +  if( p ){
          907  +    int nByte = sizeof(*p) + sizeof(p->a[0]) * (p->nCte-1);
          908  +    pRet = sqlite3DbMallocZero(db, nByte);
          909  +    if( pRet ){
          910  +      int i;
          911  +      pRet->nCte = p->nCte;
          912  +      for(i=0; i<p->nCte; i++){
          913  +        pRet->a[i].pSelect = sqlite3SelectDup(db, p->a[i].pSelect, 0);
          914  +        pRet->a[i].pCols = sqlite3ExprListDup(db, p->a[i].pCols, 0);
          915  +        pRet->a[i].zName = sqlite3DbStrDup(db, p->a[i].zName);
          916  +      }
          917  +    }
          918  +  }
          919  +  return pRet;
          920  +}
          921  +#else
          922  +# define withDup(x,y) 0
          923  +#endif
          924  +
   898    925   /*
   899    926   ** The following group of routines make deep copies of expressions,
   900    927   ** expression lists, ID lists, and select statements.  The copies can
   901    928   ** be deleted (by being passed to their respective ...Delete() routines)
   902    929   ** without effecting the originals.
   903    930   **
   904    931   ** The expression list, ID, and source lists return by sqlite3ExprListDup(),
................................................................................
   971    998       pNewItem->zAlias = sqlite3DbStrDup(db, pOldItem->zAlias);
   972    999       pNewItem->jointype = pOldItem->jointype;
   973   1000       pNewItem->iCursor = pOldItem->iCursor;
   974   1001       pNewItem->addrFillSub = pOldItem->addrFillSub;
   975   1002       pNewItem->regReturn = pOldItem->regReturn;
   976   1003       pNewItem->isCorrelated = pOldItem->isCorrelated;
   977   1004       pNewItem->viaCoroutine = pOldItem->viaCoroutine;
         1005  +    pNewItem->isRecursive = pOldItem->isRecursive;
   978   1006       pNewItem->zIndex = sqlite3DbStrDup(db, pOldItem->zIndex);
   979   1007       pNewItem->notIndexed = pOldItem->notIndexed;
   980   1008       pNewItem->pIndex = pOldItem->pIndex;
   981   1009       pTab = pNewItem->pTab = pOldItem->pTab;
   982   1010       if( pTab ){
   983   1011         pTab->nRef++;
   984   1012       }
................................................................................
  1032   1060     pNew->iLimit = 0;
  1033   1061     pNew->iOffset = 0;
  1034   1062     pNew->selFlags = p->selFlags & ~SF_UsesEphemeral;
  1035   1063     pNew->pRightmost = 0;
  1036   1064     pNew->addrOpenEphm[0] = -1;
  1037   1065     pNew->addrOpenEphm[1] = -1;
  1038   1066     pNew->addrOpenEphm[2] = -1;
         1067  +  pNew->pWith = withDup(db, p->pWith);
  1039   1068     return pNew;
  1040   1069   }
  1041   1070   #else
  1042   1071   Select *sqlite3SelectDup(sqlite3 *db, Select *p, int flags){
  1043   1072     assert( p==0 );
  1044   1073     return 0;
  1045   1074   }
................................................................................
  1551   1580       assert( p->pEList->a[0].pExpr!=0 ); /* Because of isCandidateForInOpt(p) */
  1552   1581       assert( p->pSrc!=0 );               /* Because of isCandidateForInOpt(p) */
  1553   1582       pTab = p->pSrc->a[0].pTab;
  1554   1583       pExpr = p->pEList->a[0].pExpr;
  1555   1584       iCol = (i16)pExpr->iColumn;
  1556   1585      
  1557   1586       /* Code an OP_VerifyCookie and OP_TableLock for <table>. */
  1558         -    iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
  1559         -    sqlite3CodeVerifySchema(pParse, iDb);
  1560         -    sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);
         1587  +    if( pTab->pSchema ){
         1588  +      iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
         1589  +      sqlite3CodeVerifySchema(pParse, iDb);
         1590  +      sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);
         1591  +    }
  1561   1592   
  1562   1593       /* This function is only called from two places. In both cases the vdbe
  1563   1594       ** has already been allocated. So assume sqlite3GetVdbe() is always
  1564   1595       ** successful here.
  1565   1596       */
  1566   1597       assert(v);
  1567   1598       if( iCol<0 ){

Changes to src/insert.c.

   663    663     **       INSERT INTO <table1> SELECT * FROM <table2>;
   664    664     **
   665    665     ** Then special optimizations can be applied that make the transfer
   666    666     ** very fast and which reduce fragmentation of indices.
   667    667     **
   668    668     ** This is the 2nd template.
   669    669     */
   670         -  if( pColumn==0 && xferOptimization(pParse, pTab, pSelect, onError, iDb) ){
          670  +  if( pColumn==0 && pParse->pWith==0 
          671  +   && xferOptimization(pParse, pTab, pSelect, onError, iDb) ){
   671    672       assert( !pTrigger );
   672    673       assert( pList==0 );
   673    674       goto insert_end;
   674    675     }
   675    676   #endif /* SQLITE_OMIT_XFER_OPT */
   676    677   
   677    678     /* If this is an AUTOINCREMENT table, look up the sequence number in the

Changes to src/parse.y.

   200    200   // fallback to ID if they will not parse as their original value.
   201    201   // This obviates the need for the "id" nonterminal.
   202    202   //
   203    203   %fallback ID
   204    204     ABORT ACTION AFTER ANALYZE ASC ATTACH BEFORE BEGIN BY CASCADE CAST COLUMNKW
   205    205     CONFLICT DATABASE DEFERRED DESC DETACH EACH END EXCLUSIVE EXPLAIN FAIL FOR
   206    206     IGNORE IMMEDIATE INITIALLY INSTEAD LIKE_KW MATCH NO PLAN
   207         -  QUERY KEY OF OFFSET PRAGMA RAISE RELEASE REPLACE RESTRICT ROW ROLLBACK
   208         -  SAVEPOINT TEMP TRIGGER VACUUM VIEW VIRTUAL WITHOUT
          207  +  QUERY KEY OF OFFSET PRAGMA RAISE RECURSIVE RELEASE REPLACE RESTRICT ROW
          208  +  ROLLBACK SAVEPOINT TEMP TRIGGER VACUUM VIEW VIRTUAL WITH WITHOUT
   209    209   %ifdef SQLITE_OMIT_COMPOUND_SELECT
   210    210     EXCEPT INTERSECT UNION
   211    211   %endif SQLITE_OMIT_COMPOUND_SELECT
   212    212     REINDEX RENAME CTIME_KW IF
   213    213     .
   214    214   %wildcard ANY.
   215    215   
................................................................................
   403    403     sqlite3ExplainSelect(pParse->pVdbe, X);
   404    404     sqlite3ExplainFinish(pParse->pVdbe);
   405    405     sqlite3SelectDelete(pParse->db, X);
   406    406   }
   407    407   
   408    408   %type select {Select*}
   409    409   %destructor select {sqlite3SelectDelete(pParse->db, $$);}
          410  +%type selectnowith {Select*}
          411  +%destructor selectnowith {sqlite3SelectDelete(pParse->db, $$);}
   410    412   %type oneselect {Select*}
   411    413   %destructor oneselect {sqlite3SelectDelete(pParse->db, $$);}
   412    414   
   413         -select(A) ::= oneselect(X).                      {A = X;}
          415  +select(A) ::= with(W) selectnowith(X). { 
          416  +  if( X ){
          417  +    X->pWith = W; 
          418  +  }else{
          419  +    sqlite3WithDelete(pParse->db, W);
          420  +  }
          421  +  A = X; 
          422  +}
          423  +
          424  +selectnowith(A) ::= oneselect(X).                      {A = X;}
   414    425   %ifndef SQLITE_OMIT_COMPOUND_SELECT
   415         -select(A) ::= select(X) multiselect_op(Y) oneselect(Z).  {
          426  +selectnowith(A) ::= selectnowith(X) multiselect_op(Y) oneselect(Z).  {
   416    427     if( Z ){
   417    428       Z->op = (u8)Y;
   418    429       Z->pPrior = X;
   419    430       if( Y!=TK_ALL ) pParse->hasCompound = 1;
   420    431     }else{
   421    432       sqlite3SelectDelete(pParse->db, X);
   422    433     }
................................................................................
   644    655                                         {A.pLimit = X.pExpr; A.pOffset = Y.pExpr;}
   645    656   limit_opt(A) ::= LIMIT expr(X) COMMA expr(Y). 
   646    657                                         {A.pOffset = X.pExpr; A.pLimit = Y.pExpr;}
   647    658   
   648    659   /////////////////////////// The DELETE statement /////////////////////////////
   649    660   //
   650    661   %ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
   651         -cmd ::= DELETE FROM fullname(X) indexed_opt(I) where_opt(W) 
          662  +cmd ::= with(C) DELETE FROM fullname(X) indexed_opt(I) where_opt(W) 
   652    663           orderby_opt(O) limit_opt(L). {
          664  +  sqlite3WithPush(pParse, C, 1);
   653    665     sqlite3SrcListIndexedBy(pParse, X, &I);
   654    666     W = sqlite3LimitWhere(pParse, X, W, O, L.pLimit, L.pOffset, "DELETE");
   655    667     sqlite3DeleteFrom(pParse,X,W);
   656    668   }
   657    669   %endif
   658    670   %ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
   659         -cmd ::= DELETE FROM fullname(X) indexed_opt(I) where_opt(W). {
          671  +cmd ::= with(C) DELETE FROM fullname(X) indexed_opt(I) where_opt(W). {
          672  +  sqlite3WithPush(pParse, C, 1);
   660    673     sqlite3SrcListIndexedBy(pParse, X, &I);
   661    674     sqlite3DeleteFrom(pParse,X,W);
   662    675   }
   663    676   %endif
   664    677   
   665    678   %type where_opt {Expr*}
   666    679   %destructor where_opt {sqlite3ExprDelete(pParse->db, $$);}
................................................................................
   667    680   
   668    681   where_opt(A) ::= .                    {A = 0;}
   669    682   where_opt(A) ::= WHERE expr(X).       {A = X.pExpr;}
   670    683   
   671    684   ////////////////////////// The UPDATE command ////////////////////////////////
   672    685   //
   673    686   %ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
   674         -cmd ::= UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y) where_opt(W)
   675         -        orderby_opt(O) limit_opt(L).  {
          687  +cmd ::= with(C) UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y)
          688  +        where_opt(W) orderby_opt(O) limit_opt(L).  {
          689  +  sqlite3WithPush(pParse, C, 1);
   676    690     sqlite3SrcListIndexedBy(pParse, X, &I);
   677    691     sqlite3ExprListCheckLength(pParse,Y,"set list"); 
   678    692     W = sqlite3LimitWhere(pParse, X, W, O, L.pLimit, L.pOffset, "UPDATE");
   679    693     sqlite3Update(pParse,X,Y,W,R);
   680    694   }
   681    695   %endif
   682    696   %ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
   683         -cmd ::= UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y)
          697  +cmd ::= with(C) UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y)
   684    698           where_opt(W).  {
          699  +  sqlite3WithPush(pParse, C, 1);
   685    700     sqlite3SrcListIndexedBy(pParse, X, &I);
   686    701     sqlite3ExprListCheckLength(pParse,Y,"set list"); 
   687    702     sqlite3Update(pParse,X,Y,W,R);
   688    703   }
   689    704   %endif
   690    705   
   691    706   %type setlist {ExprList*}
................................................................................
   698    713   setlist(A) ::= nm(X) EQ expr(Y). {
   699    714     A = sqlite3ExprListAppend(pParse, 0, Y.pExpr);
   700    715     sqlite3ExprListSetName(pParse, A, &X, 1);
   701    716   }
   702    717   
   703    718   ////////////////////////// The INSERT command /////////////////////////////////
   704    719   //
   705         -cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F) select(S).
   706         -            {sqlite3Insert(pParse, X, S, F, R);}
   707         -cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F) DEFAULT VALUES.
   708         -            {sqlite3Insert(pParse, X, 0, F, R);}
          720  +cmd ::= with(W) insert_cmd(R) INTO fullname(X) inscollist_opt(F) select(S). {
          721  +  sqlite3WithPush(pParse, W, 1);
          722  +  sqlite3Insert(pParse, X, S, F, R);
          723  +}
          724  +cmd ::= with(W) insert_cmd(R) INTO fullname(X) inscollist_opt(F) DEFAULT VALUES.
          725  +{
          726  +  sqlite3WithPush(pParse, W, 1);
          727  +  sqlite3Insert(pParse, X, 0, F, R);
          728  +}
   709    729   
   710    730   %type insert_cmd {u8}
   711    731   insert_cmd(A) ::= INSERT orconf(R).   {A = R;}
   712    732   insert_cmd(A) ::= REPLACE.            {A = OE_Replace;}
   713    733   
   714    734   %type inscollist_opt {IdList*}
   715    735   %destructor inscollist_opt {sqlite3IdListDelete(pParse->db, $$);}
................................................................................
   847    867                                           {spanBinaryExpr(&A,pParse,@OP,&X,&Y);}
   848    868   expr(A) ::= expr(X) PLUS|MINUS(OP) expr(Y).
   849    869                                           {spanBinaryExpr(&A,pParse,@OP,&X,&Y);}
   850    870   expr(A) ::= expr(X) STAR|SLASH|REM(OP) expr(Y).
   851    871                                           {spanBinaryExpr(&A,pParse,@OP,&X,&Y);}
   852    872   expr(A) ::= expr(X) CONCAT(OP) expr(Y). {spanBinaryExpr(&A,pParse,@OP,&X,&Y);}
   853    873   %type likeop {struct LikeOp}
   854         -likeop(A) ::= LIKE_KW(X).     {A.eOperator = X; A.bNot = 0;}
   855         -likeop(A) ::= NOT LIKE_KW(X). {A.eOperator = X; A.bNot = 1;}
   856         -likeop(A) ::= MATCH(X).       {A.eOperator = X; A.bNot = 0;}
   857         -likeop(A) ::= NOT MATCH(X).   {A.eOperator = X; A.bNot = 1;}
          874  +likeop(A) ::= LIKE_KW|MATCH(X).     {A.eOperator = X; A.bNot = 0;}
          875  +likeop(A) ::= NOT LIKE_KW|MATCH(X). {A.eOperator = X; A.bNot = 1;}
   858    876   expr(A) ::= expr(X) likeop(OP) expr(Y).  [LIKE_KW]  {
   859    877     ExprList *pList;
   860    878     pList = sqlite3ExprListAppend(pParse,0, Y.pExpr);
   861    879     pList = sqlite3ExprListAppend(pParse,pList, X.pExpr);
   862    880     A.pExpr = sqlite3ExprFunction(pParse, pList, &OP.eOperator);
   863    881     if( OP.bNot ) A.pExpr = sqlite3PExpr(pParse, TK_NOT, A.pExpr, 0, 0);
   864    882     A.zStart = X.zStart;
................................................................................
  1360   1378   vtabargtoken ::= ANY(X).            {sqlite3VtabArgExtend(pParse,&X);}
  1361   1379   vtabargtoken ::= lp anylist RP(X).  {sqlite3VtabArgExtend(pParse,&X);}
  1362   1380   lp ::= LP(X).                       {sqlite3VtabArgExtend(pParse,&X);}
  1363   1381   anylist ::= .
  1364   1382   anylist ::= anylist LP anylist RP.
  1365   1383   anylist ::= anylist ANY.
  1366   1384   %endif  SQLITE_OMIT_VIRTUALTABLE
         1385  +
         1386  +
         1387  +//////////////////////// COMMON TABLE EXPRESSIONS ////////////////////////////
         1388  +%type with {With*}
         1389  +%type wqlist {With*}
         1390  +%destructor with {sqlite3WithDelete(pParse->db, $$);}
         1391  +%destructor wqlist {sqlite3WithDelete(pParse->db, $$);}
         1392  +
         1393  +with(A) ::= . {A = 0;}
         1394  +%ifndef SQLITE_OMIT_CTE
         1395  +with(A) ::= WITH wqlist(W).              { A = W; }
         1396  +with(A) ::= WITH RECURSIVE wqlist(W).    { A = W; }
         1397  +
         1398  +wqlist(A) ::= nm(X) idxlist_opt(Y) AS LP select(Z) RP. {
         1399  +  A = sqlite3WithAdd(pParse, 0, &X, Y, Z);
         1400  +}
         1401  +wqlist(A) ::= wqlist(W) COMMA nm(X) idxlist_opt(Y) AS LP select(Z) RP. {
         1402  +  A = sqlite3WithAdd(pParse, W, &X, Y, Z);
         1403  +}
         1404  +%endif  SQLITE_OMIT_CTE

Changes to src/select.c.

    25     25     sqlite3ExprDelete(db, p->pWhere);
    26     26     sqlite3ExprListDelete(db, p->pGroupBy);
    27     27     sqlite3ExprDelete(db, p->pHaving);
    28     28     sqlite3ExprListDelete(db, p->pOrderBy);
    29     29     sqlite3SelectDelete(db, p->pPrior);
    30     30     sqlite3ExprDelete(db, p->pLimit);
    31     31     sqlite3ExprDelete(db, p->pOffset);
           32  +  sqlite3WithDelete(db, p->pWith);
    32     33   }
    33     34   
    34     35   /*
    35     36   ** Initialize a SelectDest structure.
    36     37   */
    37     38   void sqlite3SelectDestInit(SelectDest *pDest, int eDest, int iParm){
    38     39     pDest->eDest = (u8)eDest;
................................................................................
   686    687         sqlite3VdbeAddOp3(v, OP_IdxDelete, iParm, regResult, nColumn);
   687    688         break;
   688    689       }
   689    690   #endif
   690    691   
   691    692       /* Store the result as data using a unique key.
   692    693       */
          694  +    case SRT_DistTable:
   693    695       case SRT_Table:
   694    696       case SRT_EphemTab: {
   695    697         int r1 = sqlite3GetTempReg(pParse);
   696    698         testcase( eDest==SRT_Table );
   697    699         testcase( eDest==SRT_EphemTab );
   698    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
   699    714         if( pOrderBy ){
   700    715           pushOntoSorter(pParse, pOrderBy, p, r1);
   701    716         }else{
   702    717           int r2 = sqlite3GetTempReg(pParse);
   703    718           sqlite3VdbeAddOp2(v, OP_NewRowid, iParm, r2);
   704    719           sqlite3VdbeAddOp3(v, OP_Insert, iParm, r1, r2);
   705    720           sqlite3VdbeChangeP5(v, OPFLAG_APPEND);
................................................................................
  1198   1213             NameContext sNC;
  1199   1214             Expr *p = pS->pEList->a[iCol].pExpr;
  1200   1215             sNC.pSrcList = pS->pSrc;
  1201   1216             sNC.pNext = pNC;
  1202   1217             sNC.pParse = pNC->pParse;
  1203   1218             zType = columnType(&sNC, p,&zOrigDb,&zOrigTab,&zOrigCol, &estWidth); 
  1204   1219           }
  1205         -      }else if( ALWAYS(pTab->pSchema) ){
         1220  +      }else if( pTab->pSchema ){
  1206   1221           /* A real table */
  1207   1222           assert( !pS );
  1208   1223           if( iCol<0 ) iCol = pTab->iPKey;
  1209   1224           assert( iCol==-1 || (iCol>=0 && iCol<pTab->nCol) );
  1210   1225   #ifdef SQLITE_ENABLE_COLUMN_METADATA
  1211   1226           if( iCol<0 ){
  1212   1227             zType = "INTEGER";
................................................................................
  1725   1740     int iSub2;            /* EQP id of right-hand query */
  1726   1741   #endif
  1727   1742   
  1728   1743     /* Make sure there is no ORDER BY or LIMIT clause on prior SELECTs.  Only
  1729   1744     ** the last (right-most) SELECT in the series may have an ORDER BY or LIMIT.
  1730   1745     */
  1731   1746     assert( p && p->pPrior );  /* Calling function guarantees this much */
         1747  +  assert( (p->selFlags & SF_Recursive)==0 || p->op==TK_ALL || p->op==TK_UNION );
  1732   1748     db = pParse->db;
  1733   1749     pPrior = p->pPrior;
  1734   1750     assert( pPrior->pRightmost!=pPrior );
  1735   1751     assert( pPrior->pRightmost==p->pRightmost );
  1736   1752     dest = *pDest;
  1737   1753     if( pPrior->pOrderBy ){
  1738   1754       sqlite3ErrorMsg(pParse,"ORDER BY clause should come after %s not before",
................................................................................
  1769   1785       }else{
  1770   1786         sqlite3ErrorMsg(pParse, "SELECTs to the left and right of %s"
  1771   1787           " do not have the same number of result columns", selectOpName(p->op));
  1772   1788       }
  1773   1789       rc = 1;
  1774   1790       goto multi_select_end;
  1775   1791     }
         1792  +
         1793  +#ifndef SQLITE_OMIT_CTE
         1794  +  if( p->selFlags & SF_Recursive ){
         1795  +    SrcList *pSrc = p->pSrc;
         1796  +    int nCol = p->pEList->nExpr;
         1797  +    int addrNext;
         1798  +    int addrSwap;
         1799  +    int iCont, iBreak;
         1800  +    int tmp1;                     /* Intermediate table */
         1801  +    int tmp2;                     /* Next intermediate table */
         1802  +    int tmp3 = 0;                 /* To ensure unique results if UNION */
         1803  +    int eDest = SRT_Table;
         1804  +    SelectDest tmp2dest;
         1805  +    int i;
         1806  +
         1807  +    /* Check that there is no ORDER BY or LIMIT clause. Neither of these 
         1808  +    ** are supported on recursive queries.  */
         1809  +    assert( p->pOffset==0 || p->pLimit );
         1810  +    if( p->pOrderBy || p->pLimit ){
         1811  +      sqlite3ErrorMsg(pParse, "%s in a recursive query is not allowed",
         1812  +          p->pOrderBy ? "ORDER BY" : "LIMIT"
         1813  +      );
         1814  +      goto multi_select_end;
         1815  +    }
         1816  +
         1817  +    if( sqlite3AuthCheck(pParse, SQLITE_RECURSIVE, 0, 0, 0) ){
         1818  +      goto multi_select_end;
         1819  +    }
         1820  +    iBreak = sqlite3VdbeMakeLabel(v);
         1821  +    iCont = sqlite3VdbeMakeLabel(v);
         1822  +
         1823  +    for(i=0; ALWAYS(i<pSrc->nSrc); i++){
         1824  +      if( pSrc->a[i].isRecursive ){
         1825  +        tmp1 = pSrc->a[i].iCursor;
         1826  +        break;
         1827  +      }
         1828  +    }
         1829  +
         1830  +    tmp2 = pParse->nTab++;
         1831  +    if( p->op==TK_UNION ){
         1832  +      eDest = SRT_DistTable;
         1833  +      tmp3 = pParse->nTab++;
         1834  +    }
         1835  +    sqlite3SelectDestInit(&tmp2dest, eDest, tmp2);
         1836  +
         1837  +    sqlite3VdbeAddOp2(v, OP_OpenEphemeral, tmp1, nCol);
         1838  +    sqlite3VdbeAddOp2(v, OP_OpenEphemeral, tmp2, nCol);
         1839  +    if( tmp3 ){
         1840  +      p->addrOpenEphm[0] = sqlite3VdbeAddOp2(v, OP_OpenEphemeral, tmp3, 0);
         1841  +      p->selFlags |= SF_UsesEphemeral;
         1842  +    }
         1843  +
         1844  +    /* Store the results of the initial SELECT in tmp2. */
         1845  +    rc = sqlite3Select(pParse, pPrior, &tmp2dest);
         1846  +    if( rc ) goto multi_select_end;
         1847  +
         1848  +    /* Clear tmp1. Then switch the contents of tmp1 and tmp2. Then return 
         1849  +    ** the contents of tmp1 to the caller. Or, if tmp1 is empty at this
         1850  +    ** point, the recursive query has finished - jump to address iBreak.  */
         1851  +    addrSwap = sqlite3VdbeAddOp2(v, OP_SwapCursors, tmp1, tmp2);
         1852  +    sqlite3VdbeAddOp2(v, OP_Rewind, tmp1, iBreak);
         1853  +    addrNext = sqlite3VdbeCurrentAddr(v);
         1854  +    selectInnerLoop(pParse, p, p->pEList, tmp1, p->pEList->nExpr,
         1855  +        0, 0, &dest, iCont, iBreak);
         1856  +    sqlite3VdbeResolveLabel(v, iCont);
         1857  +    sqlite3VdbeAddOp2(v, OP_Next, tmp1, addrNext);
         1858  +
         1859  +    /* Execute the recursive SELECT. Store the results in tmp2. While this
         1860  +    ** SELECT is running, the contents of tmp1 are read by recursive 
         1861  +    ** references to the current CTE.  */
         1862  +    p->pPrior = 0;
         1863  +    rc = sqlite3Select(pParse, p, &tmp2dest);
         1864  +    assert( p->pPrior==0 );
         1865  +    p->pPrior = pPrior;
         1866  +    if( rc ) goto multi_select_end;
         1867  +
         1868  +    sqlite3VdbeAddOp2(v, OP_Goto, 0, addrSwap);
         1869  +    sqlite3VdbeResolveLabel(v, iBreak);
         1870  +  }else
         1871  +#endif
  1776   1872   
  1777   1873     /* Compound SELECTs that have an ORDER BY clause are handled separately.
  1778   1874     */
  1779   1875     if( p->pOrderBy ){
  1780   1876       return multiSelectOrderBy(pParse, p, pDest);
  1781         -  }
         1877  +  }else
  1782   1878   
  1783   1879     /* Generate code for the left and right SELECT statements.
  1784   1880     */
  1785   1881     switch( p->op ){
  1786   1882       case TK_ALL: {
  1787   1883         int addr = 0;
  1788   1884         int nLimit;
................................................................................
  2837   2933   **        an ORDER BY clause.  Ticket #3773.  We could relax this constraint
  2838   2934   **        somewhat by saying that the terms of the ORDER BY clause must
  2839   2935   **        appear as unmodified result columns in the outer query.  But we
  2840   2936   **        have other optimizations in mind to deal with that case.
  2841   2937   **
  2842   2938   **  (21)  The subquery does not use LIMIT or the outer query is not
  2843   2939   **        DISTINCT.  (See ticket [752e1646fc]).
         2940  +**
         2941  +**  (22)  The subquery is not a recursive CTE.
         2942  +**
         2943  +**  (23)  The parent is not a recursive CTE, or the sub-query is not a
         2944  +**        compound query. This restriction is because transforming the
         2945  +**        parent to a compound query confuses the code that handles
         2946  +**        recursive queries in multiSelect().
         2947  +**
  2844   2948   **
  2845   2949   ** In this routine, the "p" parameter is a pointer to the outer query.
  2846   2950   ** The subquery is p->pSrc->a[iFrom].  isAgg is true if the outer query
  2847   2951   ** uses aggregates and subqueryIsAgg is true if the subquery uses aggregates.
  2848   2952   **
  2849   2953   ** If flattening is not attempted, this routine is a no-op and returns 0.
  2850   2954   ** If flattening is attempted this routine returns 1.
................................................................................
  2909   3013        return 0;                                           /* Restriction (11) */
  2910   3014     }
  2911   3015     if( isAgg && pSub->pOrderBy ) return 0;                /* Restriction (16) */
  2912   3016     if( pSub->pLimit && p->pWhere ) return 0;              /* Restriction (19) */
  2913   3017     if( pSub->pLimit && (p->selFlags & SF_Distinct)!=0 ){
  2914   3018        return 0;         /* Restriction (21) */
  2915   3019     }
         3020  +  if( pSub->selFlags & SF_Recursive ) return 0;          /* Restriction (22)  */
         3021  +  if( (p->selFlags & SF_Recursive) && pSub->pPrior ) return 0;       /* (23)  */
  2916   3022   
  2917   3023     /* OBSOLETE COMMENT 1:
  2918   3024     ** Restriction 3:  If the subquery is a join, make sure the subquery is 
  2919   3025     ** not used as the right operand of an outer join.  Examples of why this
  2920   3026     ** is not allowed:
  2921   3027     **
  2922   3028     **         t1 LEFT OUTER JOIN (t2 JOIN t3)
................................................................................
  3390   3496     pNew->pOrderBy = 0;
  3391   3497     p->pPrior = 0;
  3392   3498     pNew->pLimit = 0;
  3393   3499     pNew->pOffset = 0;
  3394   3500     return WRC_Continue;
  3395   3501   }
  3396   3502   
         3503  +#ifndef SQLITE_OMIT_CTE
         3504  +/*
         3505  +** Argument pWith (which may be NULL) points to a linked list of nested 
         3506  +** WITH contexts, from inner to outermost. If the table identified by 
         3507  +** FROM clause element pItem is really a common-table-expression (CTE) 
         3508  +** then return a pointer to the CTE definition for that table. Otherwise
         3509  +** return NULL.
         3510  +*/
         3511  +static struct Cte *searchWith(With *pWith, struct SrcList_item *pItem){
         3512  +  const char *zName;
         3513  +  if( pItem->zDatabase==0 && (zName = pItem->zName)!=0 ){
         3514  +    With *p;
         3515  +    for(p=pWith; p; p=p->pOuter){
         3516  +      int i;
         3517  +      for(i=0; i<p->nCte; i++){
         3518  +        if( sqlite3StrICmp(zName, p->a[i].zName)==0 ){
         3519  +          return &p->a[i];
         3520  +        }
         3521  +      }
         3522  +    }
         3523  +  }
         3524  +  return 0;
         3525  +}
         3526  +
         3527  +/* The code generator maintains a stack of active WITH clauses
         3528  +** with the inner-most WITH clause being at the top of the stack.
         3529  +**
         3530  +** This routine pushes the WITH clause passed as the second argument
         3531  +** onto the top of the stack. If argument bFree is true, then this
         3532  +** WITH clause will never be popped from the stack. In this case it
         3533  +** should be freed along with the Parse object. In other cases, when
         3534  +** bFree==0, the With object will be freed along with the SELECT 
         3535  +** statement with which it is associated.
         3536  +*/
         3537  +void sqlite3WithPush(Parse *pParse, With *pWith, u8 bFree){
         3538  +  assert( bFree==0 || pParse->pWith==0 );
         3539  +  if( pWith ){
         3540  +    pWith->pOuter = pParse->pWith;
         3541  +    pParse->pWith = pWith;
         3542  +    pParse->bFreeWith = bFree;
         3543  +  }
         3544  +}
         3545  +
         3546  +/*
         3547  +** This function checks if argument pFrom refers to a CTE declared by 
         3548  +** a WITH clause on the stack currently maintained by the parser. And,
         3549  +** if currently processing a CTE expression, if it is a recursive
         3550  +** reference to the current CTE.
         3551  +**
         3552  +** If pFrom falls into either of the two categories above, pFrom->pTab
         3553  +** and other fields are populated accordingly. The caller should check
         3554  +** (pFrom->pTab!=0) to determine whether or not a successful match
         3555  +** was found.
         3556  +**
         3557  +** Whether or not a match is found, SQLITE_OK is returned if no error
         3558  +** occurs. If an error does occur, an error message is stored in the
         3559  +** parser and some error code other than SQLITE_OK returned.
         3560  +*/
         3561  +static int withExpand(
         3562  +  Walker *pWalker, 
         3563  +  struct SrcList_item *pFrom
         3564  +){
         3565  +  Table *pTab;
         3566  +  Parse *pParse = pWalker->pParse;
         3567  +  sqlite3 *db = pParse->db;
         3568  +  struct Cte *pCte;
         3569  +
         3570  +  assert( pFrom->pTab==0 );
         3571  +
         3572  +  pCte = searchWith(pParse->pWith, pFrom);
         3573  +  if( pCte ){
         3574  +    ExprList *pEList;
         3575  +    Select *pSel;
         3576  +    Select *pLeft;                /* Left-most SELECT statement */
         3577  +    int bMayRecursive;            /* True if compound joined by UNION [ALL] */
         3578  +
         3579  +    /* If pCte->zErr is non-NULL at this point, then this is an illegal
         3580  +    ** recursive reference to CTE pCte. Leave an error in pParse and return
         3581  +    ** early. If pCte->zErr is NULL, then this is not a recursive reference.
         3582  +    ** In this case, proceed.  */
         3583  +    if( pCte->zErr ){
         3584  +      sqlite3ErrorMsg(pParse, pCte->zErr, pCte->zName);
         3585  +      return WRC_Abort;
         3586  +    }
         3587  +
         3588  +    pFrom->pTab = pTab = sqlite3DbMallocZero(db, sizeof(Table));
         3589  +    if( pTab==0 ) return WRC_Abort;
         3590  +    pTab->nRef = 1;
         3591  +    pTab->zName = sqlite3DbStrDup(db, pCte->zName);
         3592  +    pTab->iPKey = -1;
         3593  +    pTab->nRowEst = 1048576;
         3594  +    pTab->tabFlags |= TF_Ephemeral;
         3595  +    pFrom->pSelect = sqlite3SelectDup(db, pCte->pSelect, 0);
         3596  +    if( db->mallocFailed ) return SQLITE_NOMEM;
         3597  +    assert( pFrom->pSelect );
         3598  +
         3599  +    /* Check if this is a recursive CTE. */
         3600  +    pSel = pFrom->pSelect;
         3601  +    bMayRecursive = ( pSel->op==TK_ALL || pSel->op==TK_UNION );
         3602  +    if( bMayRecursive ){
         3603  +      int i;
         3604  +      SrcList *pSrc = pFrom->pSelect->pSrc;
         3605  +      for(i=0; i<pSrc->nSrc; i++){
         3606  +        struct SrcList_item *pItem = &pSrc->a[i];
         3607  +        if( pItem->zDatabase==0 
         3608  +         && pItem->zName!=0 
         3609  +         && 0==sqlite3StrICmp(pItem->zName, pCte->zName)
         3610  +          ){
         3611  +          pItem->pTab = pTab;
         3612  +          pItem->isRecursive = 1;
         3613  +          pTab->nRef++;
         3614  +          pSel->selFlags |= SF_Recursive;
         3615  +        }
         3616  +      }
         3617  +    }
         3618  +
         3619  +    /* Only one recursive reference is permitted. */ 
         3620  +    if( pTab->nRef>2 ){
         3621  +      sqlite3ErrorMsg(
         3622  +          pParse, "multiple references to recursive table: %s", pCte->zName
         3623  +      );
         3624  +      return WRC_Abort;
         3625  +    }
         3626  +    assert( pTab->nRef==1 || ((pSel->selFlags&SF_Recursive) && pTab->nRef==2 ));
         3627  +
         3628  +    pCte->zErr = "circular reference: %s";
         3629  +    sqlite3WalkSelect(pWalker, bMayRecursive ? pSel->pPrior : pSel);
         3630  +
         3631  +    for(pLeft=pSel; pLeft->pPrior; pLeft=pLeft->pPrior);
         3632  +    pEList = pLeft->pEList;
         3633  +    if( pCte->pCols ){
         3634  +      if( pEList->nExpr!=pCte->pCols->nExpr ){
         3635  +        sqlite3ErrorMsg(pParse, "table %s has %d values for %d columns",
         3636  +            pCte->zName, pEList->nExpr, pCte->pCols->nExpr
         3637  +        );
         3638  +        return WRC_Abort;
         3639  +      }
         3640  +      pEList = pCte->pCols;
         3641  +    }
         3642  +    selectColumnsFromExprList(pParse, pEList, &pTab->nCol, &pTab->aCol);
         3643  +
         3644  +    if( bMayRecursive ){
         3645  +      if( pSel->selFlags & SF_Recursive ){
         3646  +        pCte->zErr = "multiple recursive references: %s";
         3647  +      }else{
         3648  +        pCte->zErr = "recursive reference in a subquery: %s";
         3649  +      }
         3650  +      sqlite3WalkSelect(pWalker, pSel);
         3651  +    }
         3652  +    pCte->zErr = 0;
         3653  +  }
         3654  +
         3655  +  return SQLITE_OK;
         3656  +}
         3657  +#endif
         3658  +
         3659  +#ifndef SQLITE_OMIT_CTE
         3660  +/*
         3661  +** If the SELECT passed as the second argument has an associated WITH 
         3662  +** clause, pop it from the stack stored as part of the Parse object.
         3663  +**
         3664  +** This function is used as the xSelectCallback2() callback by
         3665  +** sqlite3SelectExpand() when walking a SELECT tree to resolve table
         3666  +** names and other FROM clause elements. 
         3667  +*/
         3668  +static void selectPopWith(Walker *pWalker, Select *p){
         3669  +  Parse *pParse = pWalker->pParse;
         3670  +  if( p->pWith ){
         3671  +    assert( pParse->pWith==p->pWith );
         3672  +    pParse->pWith = p->pWith->pOuter;
         3673  +  }
         3674  +  return WRC_Continue;
         3675  +}
         3676  +#else
         3677  +#define selectPopWith 0
         3678  +#endif
         3679  +
  3397   3680   /*
  3398   3681   ** This routine is a Walker callback for "expanding" a SELECT statement.
  3399   3682   ** "Expanding" means to do the following:
  3400   3683   **
  3401   3684   **    (1)  Make sure VDBE cursor numbers have been assigned to every
  3402   3685   **         element of the FROM clause.
  3403   3686   **
................................................................................
  3433   3716       return WRC_Abort;
  3434   3717     }
  3435   3718     if( NEVER(p->pSrc==0) || (selFlags & SF_Expanded)!=0 ){
  3436   3719       return WRC_Prune;
  3437   3720     }
  3438   3721     pTabList = p->pSrc;
  3439   3722     pEList = p->pEList;
         3723  +  sqlite3WithPush(pParse, p->pWith, 0);
  3440   3724   
  3441   3725     /* Make sure cursor numbers have been assigned to all entries in
  3442   3726     ** the FROM clause of the SELECT statement.
  3443   3727     */
  3444   3728     sqlite3SrcListAssignCursors(pParse, pTabList);
  3445   3729   
  3446   3730     /* Look up every table named in the FROM clause of the select.  If
  3447   3731     ** an entry of the FROM clause is a subquery instead of a table or view,
  3448   3732     ** then create a transient table structure to describe the subquery.
  3449   3733     */
  3450   3734     for(i=0, pFrom=pTabList->a; i<pTabList->nSrc; i++, pFrom++){
  3451   3735       Table *pTab;
         3736  +    assert( pFrom->isRecursive==0 || pFrom->pTab );
         3737  +    if( pFrom->isRecursive ) continue;
  3452   3738       if( pFrom->pTab!=0 ){
  3453   3739         /* This statement has already been prepared.  There is no need
  3454   3740         ** to go further. */
  3455   3741         assert( i==0 );
         3742  +#ifndef SQLITE_OMIT_CTE
         3743  +      selectPopWith(pWalker, p);
         3744  +#endif
  3456   3745         return WRC_Prune;
  3457   3746       }
         3747  +#ifndef SQLITE_OMIT_CTE
         3748  +    if( withExpand(pWalker, pFrom) ) return WRC_Abort;
         3749  +    if( pFrom->pTab ) {} else
         3750  +#endif
  3458   3751       if( pFrom->zName==0 ){
  3459   3752   #ifndef SQLITE_OMIT_SUBQUERY
  3460   3753         Select *pSel = pFrom->pSelect;
  3461   3754         /* A sub-query in the FROM clause of a SELECT */
  3462   3755         assert( pSel!=0 );
  3463   3756         assert( pFrom->pTab==0 );
  3464   3757         sqlite3WalkSelect(pWalker, pSel);
................................................................................
  3713   4006     w.xExprCallback = exprWalkNoop;
  3714   4007     w.pParse = pParse;
  3715   4008     if( pParse->hasCompound ){
  3716   4009       w.xSelectCallback = convertCompoundSelectToSubquery;
  3717   4010       sqlite3WalkSelect(&w, pSelect);
  3718   4011     }
  3719   4012     w.xSelectCallback = selectExpander;
         4013  +  w.xSelectCallback2 = selectPopWith;
  3720   4014     sqlite3WalkSelect(&w, pSelect);
  3721   4015   }
  3722   4016   
  3723   4017   
  3724   4018   #ifndef SQLITE_OMIT_SUBQUERY
  3725   4019   /*
  3726   4020   ** This is a Walker.xSelectCallback callback for the sqlite3SelectTypeInfo()
................................................................................
  3731   4025   ** of that subquery.
  3732   4026   **
  3733   4027   ** The Table structure that represents the result set was constructed
  3734   4028   ** by selectExpander() but the type and collation information was omitted
  3735   4029   ** at that point because identifiers had not yet been resolved.  This
  3736   4030   ** routine is called after identifier resolution.
  3737   4031   */
  3738         -static int selectAddSubqueryTypeInfo(Walker *pWalker, Select *p){
         4032  +static void selectAddSubqueryTypeInfo(Walker *pWalker, Select *p){
  3739   4033     Parse *pParse;
  3740   4034     int i;
  3741   4035     SrcList *pTabList;
  3742   4036     struct SrcList_item *pFrom;
  3743   4037   
  3744   4038     assert( p->selFlags & SF_Resolved );
  3745   4039     if( (p->selFlags & SF_HasTypeInfo)==0 ){
................................................................................
  3747   4041       pParse = pWalker->pParse;
  3748   4042       pTabList = p->pSrc;
  3749   4043       for(i=0, pFrom=pTabList->a; i<pTabList->nSrc; i++, pFrom++){
  3750   4044         Table *pTab = pFrom->pTab;
  3751   4045         if( ALWAYS(pTab!=0) && (pTab->tabFlags & TF_Ephemeral)!=0 ){
  3752   4046           /* A sub-query in the FROM clause of a SELECT */
  3753   4047           Select *pSel = pFrom->pSelect;
  3754         -        assert( pSel );
  3755         -        while( pSel->pPrior ) pSel = pSel->pPrior;
  3756         -        selectAddColumnTypeAndCollation(pParse, pTab, pSel);
         4048  +        if( pSel ){
         4049  +          while( pSel->pPrior ) pSel = pSel->pPrior;
         4050  +          selectAddColumnTypeAndCollation(pParse, pTab, pSel);
         4051  +        }
  3757   4052         }
  3758   4053       }
  3759   4054     }
  3760         -  return WRC_Continue;
  3761   4055   }
  3762   4056   #endif
  3763   4057   
  3764   4058   
  3765   4059   /*
  3766   4060   ** This routine adds datatype and collating sequence information to
  3767   4061   ** the Table structures of all FROM-clause subqueries in a
................................................................................
  3769   4063   **
  3770   4064   ** Use this routine after name resolution.
  3771   4065   */
  3772   4066   static void sqlite3SelectAddTypeInfo(Parse *pParse, Select *pSelect){
  3773   4067   #ifndef SQLITE_OMIT_SUBQUERY
  3774   4068     Walker w;
  3775   4069     memset(&w, 0, sizeof(w));
  3776         -  w.xSelectCallback = selectAddSubqueryTypeInfo;
         4070  +  w.xSelectCallback2 = selectAddSubqueryTypeInfo;
  3777   4071     w.xExprCallback = exprWalkNoop;
  3778   4072     w.pParse = pParse;
  3779         -  w.bSelectDepthFirst = 1;
  3780   4073     sqlite3WalkSelect(&w, pSelect);
  3781   4074   #endif
  3782   4075   }
  3783   4076   
  3784   4077   
  3785   4078   /*
  3786   4079   ** This routine sets up a SELECT statement for processing.  The

Changes to src/sqlite.h.in.

  2557   2557   #define SQLITE_REINDEX              27   /* Index Name      NULL            */
  2558   2558   #define SQLITE_ANALYZE              28   /* Table Name      NULL            */
  2559   2559   #define SQLITE_CREATE_VTABLE        29   /* Table Name      Module Name     */
  2560   2560   #define SQLITE_DROP_VTABLE          30   /* Table Name      Module Name     */
  2561   2561   #define SQLITE_FUNCTION             31   /* NULL            Function Name   */
  2562   2562   #define SQLITE_SAVEPOINT            32   /* Operation       Savepoint Name  */
  2563   2563   #define SQLITE_COPY                  0   /* No longer used */
         2564  +#define SQLITE_RECURSIVE            33   /* NULL            NULL            */
  2564   2565   
  2565   2566   /*
  2566   2567   ** CAPI3REF: Tracing And Profiling Functions
  2567   2568   **
  2568   2569   ** These routines register callback functions that can be used for
  2569   2570   ** tracing and profiling the execution of SQL statements.
  2570   2571   **

Changes to src/sqliteInt.h.

   756    756   typedef struct TriggerPrg TriggerPrg;
   757    757   typedef struct TriggerStep TriggerStep;
   758    758   typedef struct UnpackedRecord UnpackedRecord;
   759    759   typedef struct VTable VTable;
   760    760   typedef struct VtabCtx VtabCtx;
   761    761   typedef struct Walker Walker;
   762    762   typedef struct WhereInfo WhereInfo;
          763  +typedef struct With With;
   763    764   
   764    765   /*
   765    766   ** Defer sourcing vdbe.h and btree.h until after the "u8" and 
   766    767   ** "BusyHandler" typedefs. vdbe.h also requires a few of the opaque
   767    768   ** pointer types (i.e. FuncDef) defined above.
   768    769   */
   769    770   #include "btree.h"
................................................................................
  1424   1425   #endif
  1425   1426     Trigger *pTrigger;   /* List of triggers stored in pSchema */
  1426   1427     Schema *pSchema;     /* Schema that contains this table */
  1427   1428     Table *pNextZombie;  /* Next on the Parse.pZombieTab list */
  1428   1429   };
  1429   1430   
  1430   1431   /*
  1431         -** Allowed values for Tabe.tabFlags.
         1432  +** Allowed values for Table.tabFlags.
  1432   1433   */
  1433   1434   #define TF_Readonly        0x01    /* Read-only system table */
  1434   1435   #define TF_Ephemeral       0x02    /* An ephemeral table */
  1435   1436   #define TF_HasPrimaryKey   0x04    /* Table has a primary key */
  1436   1437   #define TF_Autoincrement   0x08    /* Integer primary key is autoincrement */
  1437   1438   #define TF_Virtual         0x10    /* Is a virtual table */
  1438   1439   #define TF_WithoutRowid    0x20    /* No rowid used. PRIMARY KEY is the key */
         1440  +#define TF_Recursive       0x40    /* Recursive reference within CTE */
  1439   1441   
  1440   1442   
  1441   1443   /*
  1442   1444   ** Test to see whether or not a table is a virtual table.  This is
  1443   1445   ** done as a macro so that it will be optimized out when virtual
  1444   1446   ** table support is omitted from the build.
  1445   1447   */
................................................................................
  2013   2015       Select *pSelect;  /* A SELECT statement used in place of a table name */
  2014   2016       int addrFillSub;  /* Address of subroutine to manifest a subquery */
  2015   2017       int regReturn;    /* Register holding return address of addrFillSub */
  2016   2018       u8 jointype;      /* Type of join between this able and the previous */
  2017   2019       unsigned notIndexed :1;    /* True if there is a NOT INDEXED clause */
  2018   2020       unsigned isCorrelated :1;  /* True if sub-query is correlated */
  2019   2021       unsigned viaCoroutine :1;  /* Implemented as a co-routine */
         2022  +    unsigned isRecursive :1;   /* True for recursive reference in WITH */
  2020   2023   #ifndef SQLITE_OMIT_EXPLAIN
  2021   2024       u8 iSelectId;     /* If pSelect!=0, the id of the sub-select in EQP */
  2022   2025   #endif
  2023   2026       int iCursor;      /* The VDBE cursor number used to access this table */
  2024   2027       Expr *pOn;        /* The ON clause of a join */
  2025   2028       IdList *pUsing;   /* The USING clause of a join */
  2026   2029       Bitmask colUsed;  /* Bit N (1<<N) set if column N of pTab is used */
................................................................................
  2139   2142     Expr *pHaving;         /* The HAVING clause */
  2140   2143     ExprList *pOrderBy;    /* The ORDER BY clause */
  2141   2144     Select *pPrior;        /* Prior select in a compound select statement */
  2142   2145     Select *pNext;         /* Next select to the left in a compound */
  2143   2146     Select *pRightmost;    /* Right-most select in a compound select statement */
  2144   2147     Expr *pLimit;          /* LIMIT expression. NULL means not used. */
  2145   2148     Expr *pOffset;         /* OFFSET expression. NULL means not used. */
         2149  +  With *pWith;           /* WITH clause attached to this select. Or NULL. */
  2146   2150   };
  2147   2151   
  2148   2152   /*
  2149   2153   ** Allowed values for Select.selFlags.  The "SF" prefix stands for
  2150   2154   ** "Select Flag".
  2151   2155   */
  2152   2156   #define SF_Distinct        0x0001  /* Output should be DISTINCT */
................................................................................
  2156   2160   #define SF_Expanded        0x0010  /* sqlite3SelectExpand() called on this */
  2157   2161   #define SF_HasTypeInfo     0x0020  /* FROM subqueries have Table metadata */
  2158   2162   #define SF_UseSorter       0x0040  /* Sort using a sorter */
  2159   2163   #define SF_Values          0x0080  /* Synthesized from VALUES clause */
  2160   2164   #define SF_Materialize     0x0100  /* Force materialization of views */
  2161   2165   #define SF_NestedFrom      0x0200  /* Part of a parenthesized FROM clause */
  2162   2166   #define SF_MaybeConvert    0x0400  /* Need convertCompoundSelectToSubquery() */
         2167  +#define SF_Recursive       0x0800  /* The recursive part of a recursive CTE */
  2163   2168   
  2164   2169   
  2165   2170   /*
  2166   2171   ** The results of a select can be distributed in several ways.  The
  2167   2172   ** "SRT" prefix means "SELECT Result Type".
  2168   2173   */
  2169   2174   #define SRT_Union        1  /* Store result as keys in an index */
................................................................................
  2176   2181   
  2177   2182   #define SRT_Output       5  /* Output each row of result */
  2178   2183   #define SRT_Mem          6  /* Store result in a memory cell */
  2179   2184   #define SRT_Set          7  /* Store results as keys in an index */
  2180   2185   #define SRT_Table        8  /* Store result as data with an automatic rowid */
  2181   2186   #define SRT_EphemTab     9  /* Create transient tab and store like SRT_Table */
  2182   2187   #define SRT_Coroutine   10  /* Generate a single row of result */
         2188  +#define SRT_DistTable   11  /* Like SRT_TABLE, but unique results only */
  2183   2189   
  2184   2190   /*
  2185   2191   ** An instance of this object describes where to put of the results of
  2186   2192   ** a SELECT statement.
  2187   2193   */
  2188   2194   struct SelectDest {
  2189   2195     u8 eDest;         /* How to dispose of the results.  On of SRT_* above. */
................................................................................
  2360   2366     Token sLastToken;         /* The last token parsed */
  2361   2367   #ifndef SQLITE_OMIT_VIRTUALTABLE
  2362   2368     Token sArg;               /* Complete text of a module argument */
  2363   2369     Table **apVtabLock;       /* Pointer to virtual tables needing locking */
  2364   2370   #endif
  2365   2371     Table *pZombieTab;        /* List of Table objects to delete after code gen */
  2366   2372     TriggerPrg *pTriggerPrg;  /* Linked list of coded triggers */
         2373  +  With *pWith;              /* Current WITH clause, or NULL */
         2374  +  u8 bFreeWith;             /* True if pWith should be freed with parser */
  2367   2375   };
  2368   2376   
  2369   2377   /*
  2370   2378   ** Return true if currently inside an sqlite3_declare_vtab() call.
  2371   2379   */
  2372   2380   #ifdef SQLITE_OMIT_VIRTUALTABLE
  2373   2381     #define IN_DECLARE_VTAB 0
................................................................................
  2601   2609   
  2602   2610   /*
  2603   2611   ** Context pointer passed down through the tree-walk.
  2604   2612   */
  2605   2613   struct Walker {
  2606   2614     int (*xExprCallback)(Walker*, Expr*);     /* Callback for expressions */
  2607   2615     int (*xSelectCallback)(Walker*,Select*);  /* Callback for SELECTs */
         2616  +  void (*xSelectCallback2)(Walker*,Select*);/* Second callback for SELECTs */
  2608   2617     Parse *pParse;                            /* Parser context.  */
  2609   2618     int walkerDepth;                          /* Number of subqueries */
  2610         -  u8 bSelectDepthFirst;                     /* Do subqueries first */
  2611   2619     union {                                   /* Extra data for callback */
  2612   2620       NameContext *pNC;                          /* Naming context */
  2613   2621       int i;                                     /* Integer value */
  2614   2622       SrcList *pSrcList;                         /* FROM clause */
  2615   2623       struct SrcCount *pSrcCount;                /* Counting column references */
  2616   2624     } u;
  2617   2625   };
................................................................................
  2626   2634   /*
  2627   2635   ** Return code from the parse-tree walking primitives and their
  2628   2636   ** callbacks.
  2629   2637   */
  2630   2638   #define WRC_Continue    0   /* Continue down into children */
  2631   2639   #define WRC_Prune       1   /* Omit children but continue walking siblings */
  2632   2640   #define WRC_Abort       2   /* Abandon the tree walk */
         2641  +
         2642  +/*
         2643  +** An instance of this structure represents a set of one or more CTEs
         2644  +** (common table expressions) created by a single WITH clause.
         2645  +*/
         2646  +struct With {
         2647  +  int nCte;                       /* Number of CTEs in the WITH clause */
         2648  +  With *pOuter;                   /* Containing WITH clause, or NULL */
         2649  +  struct Cte {                    /* For each CTE in the WITH clause.... */
         2650  +    char *zName;                    /* Name of this CTE */
         2651  +    ExprList *pCols;                /* List of explicit column names, or NULL */
         2652  +    Select *pSelect;                /* The definition of this CTE */
         2653  +    const char *zErr;               /* Error message for circular references */
         2654  +  } a[1];
         2655  +};
  2633   2656   
  2634   2657   /*
  2635   2658   ** Assuming zIn points to the first byte of a UTF-8 character,
  2636   2659   ** advance zIn to point to the first byte of the next UTF-8 character.
  2637   2660   */
  2638   2661   #define SQLITE_SKIP_UTF8(zIn) {                        \
  2639   2662     if( (*(zIn++))>=0xc0 ){                              \
................................................................................
  3325   3348   CollSeq *sqlite3BinaryCompareCollSeq(Parse *, Expr *, Expr *);
  3326   3349   int sqlite3TempInMemory(const sqlite3*);
  3327   3350   const char *sqlite3JournalModename(int);
  3328   3351   #ifndef SQLITE_OMIT_WAL
  3329   3352     int sqlite3Checkpoint(sqlite3*, int, int, int*, int*);
  3330   3353     int sqlite3WalDefaultHook(void*,sqlite3*,const char*,int);
  3331   3354   #endif
         3355  +#ifndef SQLITE_OMIT_CTE
         3356  +  With *sqlite3WithAdd(Parse*,With*,Token*,ExprList*,Select*);
         3357  +  void sqlite3WithDelete(sqlite3*,With*);
         3358  +  void sqlite3WithPush(Parse*, With*, u8);
         3359  +#else
         3360  +#define sqlite3WithPush(x,y,z)
         3361  +#define sqlite3WithDelete(x,y)
         3362  +#endif
  3332   3363   
  3333   3364   /* Declarations for functions in fkey.c. All of these are replaced by
  3334   3365   ** no-op macros if OMIT_FOREIGN_KEY is defined. In this case no foreign
  3335   3366   ** key functionality is available. If OMIT_TRIGGER is defined but
  3336   3367   ** OMIT_FOREIGN_KEY is not, only some of the functions are no-oped. In
  3337   3368   ** this case foreign keys are parsed, but no other functionality is 
  3338   3369   ** provided (enforcement of FK constraints requires the triggers sub-system).

Changes to src/tclsqlite.c.

   910    910       case SQLITE_ALTER_TABLE       : zCode="SQLITE_ALTER_TABLE"; break;
   911    911       case SQLITE_REINDEX           : zCode="SQLITE_REINDEX"; break;
   912    912       case SQLITE_ANALYZE           : zCode="SQLITE_ANALYZE"; break;
   913    913       case SQLITE_CREATE_VTABLE     : zCode="SQLITE_CREATE_VTABLE"; break;
   914    914       case SQLITE_DROP_VTABLE       : zCode="SQLITE_DROP_VTABLE"; break;
   915    915       case SQLITE_FUNCTION          : zCode="SQLITE_FUNCTION"; break;
   916    916       case SQLITE_SAVEPOINT         : zCode="SQLITE_SAVEPOINT"; break;
          917  +    case SQLITE_RECURSIVE         : zCode="SQLITE_RECURSIVE"; break;
   917    918       default                       : zCode="????"; break;
   918    919     }
   919    920     Tcl_DStringInit(&str);
   920    921     Tcl_DStringAppend(&str, pDb->zAuth, -1);
   921    922     Tcl_DStringAppendElement(&str, zCode);
   922    923     Tcl_DStringAppendElement(&str, zArg1 ? zArg1 : "");
   923    924     Tcl_DStringAppendElement(&str, zArg2 ? zArg2 : "");

Changes to src/test_config.c.

   220    220   #endif
   221    221   
   222    222   #ifdef SQLITE_OMIT_CHECK
   223    223     Tcl_SetVar2(interp, "sqlite_options", "check", "0", TCL_GLOBAL_ONLY);
   224    224   #else
   225    225     Tcl_SetVar2(interp, "sqlite_options", "check", "1", TCL_GLOBAL_ONLY);
   226    226   #endif
          227  +
          228  +#ifdef SQLITE_OMIT_CTE
          229  +  Tcl_SetVar2(interp, "sqlite_options", "cte", "0", TCL_GLOBAL_ONLY);
          230  +#else
          231  +  Tcl_SetVar2(interp, "sqlite_options", "cte", "1", TCL_GLOBAL_ONLY);
          232  +#endif
   227    233   
   228    234   #ifdef SQLITE_ENABLE_COLUMN_METADATA
   229    235     Tcl_SetVar2(interp, "sqlite_options", "columnmetadata", "1", TCL_GLOBAL_ONLY);
   230    236   #else
   231    237     Tcl_SetVar2(interp, "sqlite_options", "columnmetadata", "0", TCL_GLOBAL_ONLY);
   232    238   #endif
   233    239   

Changes to src/tokenize.c.

   490    490       /* If the pParse->declareVtab flag is set, do not delete any table 
   491    491       ** structure built up in pParse->pNewTable. The calling code (see vtab.c)
   492    492       ** will take responsibility for freeing the Table structure.
   493    493       */
   494    494       sqlite3DeleteTable(db, pParse->pNewTable);
   495    495     }
   496    496   
          497  +  if( pParse->bFreeWith ) sqlite3WithDelete(db, pParse->pWith);
   497    498     sqlite3DeleteTrigger(db, pParse->pNewTrigger);
   498    499     for(i=pParse->nzVar-1; i>=0; i--) sqlite3DbFree(db, pParse->azVar[i]);
   499    500     sqlite3DbFree(db, pParse->azVar);
   500    501     while( pParse->pAinc ){
   501    502       AutoincInfo *p = pParse->pAinc;
   502    503       pParse->pAinc = p->pNext;
   503    504       sqlite3DbFree(db, p);

Changes to src/vdbe.c.

  3365   3365         pCx->isTable = 1;
  3366   3366       }
  3367   3367     }
  3368   3368     pCx->isOrdered = (pOp->p5!=BTREE_UNORDERED);
  3369   3369     break;
  3370   3370   }
  3371   3371   
         3372  +#ifndef SQLITE_OMIT_CTE
         3373  +/* Opcode: SwapCursors P1 P2 * * *
         3374  +**
         3375  +** Parameters P1 and P2 are both cursors opened by the OpenEphemeral
         3376  +** opcode. This opcode deletes the contents of epheremal table P1,
         3377  +** then renames P2 to P1 and P1 to P2. In other words, following this
         3378  +** opcode cursor P2 is open on an empty table and P1 is open on the
         3379  +** table that was initially accessed by P2.
         3380  +*/
         3381  +case OP_SwapCursors: {
         3382  +  Mem tmp;
         3383  +  VdbeCursor *pTmp;
         3384  +
         3385  +  tmp = p->aMem[p->nMem - pOp->p1];
         3386  +  p->aMem[p->nMem - pOp->p1] = p->aMem[p->nMem - pOp->p2];
         3387  +  p->aMem[p->nMem - pOp->p2] = tmp;
         3388  +
         3389  +  pTmp = p->apCsr[pOp->p1];
         3390  +  p->apCsr[pOp->p1] = p->apCsr[pOp->p2];
         3391  +  p->apCsr[pOp->p2] = pTmp;
         3392  +
         3393  +  rc = sqlite3BtreeClearTable(pTmp->pBt, MASTER_ROOT + !pTmp->isTable, 0);
         3394  +  break;
         3395  +}
         3396  +#endif /* ifndef SQLITE_OMIT_CTE */
         3397  +
  3372   3398   /* Opcode: SorterOpen P1 * * P4 *
  3373   3399   **
  3374   3400   ** This opcode works like OP_OpenEphemeral except that it opens
  3375   3401   ** a transient index that is specifically designed to sort large
  3376   3402   ** tables using an external merge-sort algorithm.
  3377   3403   */
  3378   3404   case OP_SorterOpen: {

Changes to src/walker.c.

   109    109     }
   110    110     return WRC_Continue;
   111    111   } 
   112    112   
   113    113   /*
   114    114   ** Call sqlite3WalkExpr() for every expression in Select statement p.
   115    115   ** Invoke sqlite3WalkSelect() for subqueries in the FROM clause and
   116         -** on the compound select chain, p->pPrior.  Invoke the xSelectCallback()
   117         -** either before or after the walk of expressions and FROM clause, depending
   118         -** on whether pWalker->bSelectDepthFirst is false or true, respectively.
          116  +** on the compound select chain, p->pPrior. 
          117  +**
          118  +** If it is not NULL, the xSelectCallback() callback is invoked before
          119  +** the walk of the expressions and FROM clause. The xSelectCallback2()
          120  +** method, if it is not NULL, is invoked following the walk of the 
          121  +** expressions and FROM clause.
   119    122   **
   120    123   ** Return WRC_Continue under normal conditions.  Return WRC_Abort if
   121    124   ** there is an abort request.
   122    125   **
   123    126   ** If the Walker does not have an xSelectCallback() then this routine
   124    127   ** is a no-op returning WRC_Continue.
   125    128   */
   126    129   int sqlite3WalkSelect(Walker *pWalker, Select *p){
   127    130     int rc;
   128         -  if( p==0 || pWalker->xSelectCallback==0 ) return WRC_Continue;
          131  +  if( p==0 || (pWalker->xSelectCallback==0 && pWalker->xSelectCallback2==0) ){
          132  +    return WRC_Continue;
          133  +  }
   129    134     rc = WRC_Continue;
   130    135     pWalker->walkerDepth++;
   131    136     while( p ){
   132         -    if( !pWalker->bSelectDepthFirst ){
          137  +    if( pWalker->xSelectCallback ){
   133    138          rc = pWalker->xSelectCallback(pWalker, p);
   134    139          if( rc ) break;
   135    140       }
   136    141       if( sqlite3WalkSelectExpr(pWalker, p)
   137    142        || sqlite3WalkSelectFrom(pWalker, p)
   138    143       ){
   139    144         pWalker->walkerDepth--;
   140    145         return WRC_Abort;
   141    146       }
   142         -    if( pWalker->bSelectDepthFirst ){
   143         -      rc = pWalker->xSelectCallback(pWalker, p);
   144         -      /* Depth-first search is currently only used for
   145         -      ** selectAddSubqueryTypeInfo() and that routine always returns
   146         -      ** WRC_Continue (0).  So the following branch is never taken. */
   147         -      if( NEVER(rc) ) break;
          147  +    if( pWalker->xSelectCallback2 ){
          148  +      pWalker->xSelectCallback2(pWalker, p);
   148    149       }
   149    150       p = p->pPrior;
   150    151     }
   151    152     pWalker->walkerDepth--;
   152    153     return rc & WRC_Abort;
   153    154   }

Changes to src/where.c.

  4192   4192     if( !pBuilder->pOrSet
  4193   4193      && (pWInfo->pParse->db->flags & SQLITE_AutoIndex)!=0
  4194   4194      && pSrc->pIndex==0
  4195   4195      && !pSrc->viaCoroutine
  4196   4196      && !pSrc->notIndexed
  4197   4197      && HasRowid(pTab)
  4198   4198      && !pSrc->isCorrelated
         4199  +   && !pSrc->isRecursive
  4199   4200     ){
  4200   4201       /* Generate auto-index WhereLoops */
  4201   4202       WhereTerm *pTerm;
  4202   4203       WhereTerm *pWCEnd = pWC->a + pWC->nTerm;
  4203   4204       for(pTerm=pWC->a; rc==SQLITE_OK && pTerm<pWCEnd; pTerm++){
  4204   4205         if( pTerm->prereqRight & pNew->maskSelf ) continue;
  4205   4206         if( termCanDriveIndex(pTerm, pSrc, 0) ){

Changes to test/auth.test.

  2076   2076   
  2077   2077     do_test auth-1.308 {
  2078   2078       set authargs
  2079   2079     } {main t5 {} {}}
  2080   2080     execsql {DROP TABLE t5}
  2081   2081   } ;# ifcapable altertable
  2082   2082   
         2083  +ifcapable {cte} {
         2084  +  do_test auth-1.310 {
         2085  +    proc auth {code arg1 arg2 arg3 arg4} {
         2086  +      if {$code=="SQLITE_RECURSIVE"} {
         2087  +        return SQLITE_DENY
         2088  +      }
         2089  +      return SQLITE_OK
         2090  +    }
         2091  +    db eval {
         2092  +       DROP TABLE IF EXISTS t1;
         2093  +       CREATE TABLE t1(a,b);
         2094  +       INSERT INTO t1 VALUES(1,2),(3,4),(5,6);
         2095  +    }
         2096  +  } {}
         2097  +  do_catchsql_test auth-1.311 {
         2098  +    WITH
         2099  +       auth1311(x,y) AS (SELECT a+b, b-a FROM t1)
         2100  +    SELECT * FROM auth1311 ORDER BY x;
         2101  +  } {0 {3 1 7 1 11 1}}
         2102  +  do_catchsql_test auth-1.312 {
         2103  +    WITH RECURSIVE
         2104  +       auth1312(x,y) AS (SELECT a+b, b-a FROM t1)
         2105  +    SELECT x, y FROM auth1312 ORDER BY x;
         2106  +  } {0 {3 1 7 1 11 1}}
         2107  +  do_catchsql_test auth-1.313 {
         2108  +    WITH RECURSIVE
         2109  +       auth1313(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM auth1313 WHERE x<5)
         2110  +    SELECT * FROM t1;
         2111  +  } {0 {1 2 3 4 5 6}}
         2112  +  do_catchsql_test auth-1.314 {
         2113  +    WITH RECURSIVE
         2114  +       auth1314(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM auth1314 WHERE x<5)
         2115  +    SELECT * FROM t1 LEFT JOIN auth1314;
         2116  +  } {1 {not authorized}}
         2117  +} ;# ifcapable cte
         2118  +
  2083   2119   do_test auth-2.1 {
  2084   2120     proc auth {code arg1 arg2 arg3 arg4} {
  2085   2121       if {$code=="SQLITE_READ" && $arg1=="t3" && $arg2=="x"} {
  2086   2122         return SQLITE_DENY
  2087   2123       }
  2088   2124       return SQLITE_OK
  2089   2125     }

Added test/with1.test.

            1  +# 2014 January 11
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library.  The
           12  +# focus of this file is testing the WITH clause.
           13  +#
           14  +
           15  +set testdir [file dirname $argv0]
           16  +source $testdir/tester.tcl
           17  +set ::testprefix with1
           18  +
           19  +ifcapable {!cte} {
           20  +  finish_test
           21  +  return
           22  +}
           23  +
           24  +do_execsql_test 1.0 {
           25  +  CREATE TABLE t1(x INTEGER, y INTEGER);
           26  +  WITH x(a) AS ( SELECT * FROM t1) SELECT 10
           27  +} {10}
           28  +
           29  +do_execsql_test 1.1 {
           30  +  SELECT * FROM ( WITH x AS ( SELECT * FROM t1) SELECT 10 );
           31  +} {10}
           32  +
           33  +do_execsql_test 1.2 {
           34  +  WITH x(a) AS ( SELECT * FROM t1) INSERT INTO t1 VALUES(1,2);
           35  +} {}
           36  +
           37  +do_execsql_test 1.3 {
           38  +  WITH x(a) AS ( SELECT * FROM t1) DELETE FROM t1;
           39  +} {}
           40  +
           41  +do_execsql_test 1.4 {
           42  +  WITH x(a) AS ( SELECT * FROM t1) UPDATE t1 SET x = y;
           43  +} {}
           44  + 
           45  +#--------------------------------------------------------------------------
           46  +
           47  +do_execsql_test 2.1 {
           48  +  DROP TABLE IF EXISTS t1;
           49  +  CREATE TABLE t1(x);
           50  +  INSERT INTO t1 VALUES(1);
           51  +  INSERT INTO t1 VALUES(2);
           52  +  WITH tmp AS ( SELECT * FROM t1 ) SELECT x FROM tmp;
           53  +} {1 2}
           54  +
           55  +do_execsql_test 2.2 {
           56  +  WITH tmp(a) AS ( SELECT * FROM t1 ) SELECT a FROM tmp;
           57  +} {1 2}
           58  +
           59  +do_execsql_test 2.3 {
           60  +  SELECT * FROM (
           61  +    WITH tmp(a) AS ( SELECT * FROM t1 ) SELECT a FROM tmp
           62  +  );
           63  +} {1 2}
           64  +
           65  +do_execsql_test 2.4 {
           66  +  WITH tmp1(a) AS ( SELECT * FROM t1 ),
           67  +       tmp2(x) AS ( SELECT * FROM tmp1)
           68  +  SELECT * FROM tmp2;
           69  +} {1 2}
           70  +
           71  +do_execsql_test 2.5 {
           72  +  WITH tmp2(x) AS ( SELECT * FROM tmp1),
           73  +       tmp1(a) AS ( SELECT * FROM t1 )
           74  +  SELECT * FROM tmp2;
           75  +} {1 2}
           76  +
           77  +#-------------------------------------------------------------------------
           78  +do_catchsql_test 3.1 {
           79  +  WITH tmp2(x) AS ( SELECT * FROM tmp1 ),
           80  +       tmp1(a) AS ( SELECT * FROM tmp2 )
           81  +  SELECT * FROM tmp1;
           82  +} {1 {circular reference: tmp1}}
           83  +
           84  +do_catchsql_test 3.2 {
           85  +  CREATE TABLE t2(x INTEGER);
           86  +  WITH tmp(a) AS (SELECT * FROM t1),
           87  +       tmp(a) AS (SELECT * FROM t1)
           88  +  SELECT * FROM tmp;
           89  +} {1 {duplicate WITH table name: tmp}}
           90  +
           91  +do_execsql_test 3.3 {
           92  +  CREATE TABLE t3(x);
           93  +  CREATE TABLE t4(x);
           94  +
           95  +  INSERT INTO t3 VALUES('T3');
           96  +  INSERT INTO t4 VALUES('T4');
           97  +
           98  +  WITH t3(a) AS (SELECT * FROM t4)
           99  +  SELECT * FROM t3;
          100  +} {T4}
          101  +
          102  +do_execsql_test 3.4 {
          103  +  WITH tmp  AS ( SELECT * FROM t3 ),
          104  +       tmp2 AS ( WITH tmp AS ( SELECT * FROM t4 ) SELECT * FROM tmp )
          105  +  SELECT * FROM tmp2;
          106  +} {T4}
          107  +
          108  +do_execsql_test 3.5 {
          109  +  WITH tmp  AS ( SELECT * FROM t3 ),
          110  +       tmp2 AS ( WITH xxxx AS ( SELECT * FROM t4 ) SELECT * FROM tmp )
          111  +  SELECT * FROM tmp2;
          112  +} {T3}
          113  +
          114  +do_catchsql_test 3.6 {
          115  +  WITH tmp AS ( SELECT * FROM t3 ),
          116  +  SELECT * FROM tmp;
          117  +} {1 {near "SELECT": syntax error}}
          118  +
          119  +#-------------------------------------------------------------------------
          120  +do_execsql_test 4.1 {
          121  +  DROP TABLE IF EXISTS t1;
          122  +  CREATE TABLE t1(x);
          123  +  INSERT INTO t1 VALUES(1);
          124  +  INSERT INTO t1 VALUES(2);
          125  +  INSERT INTO t1 VALUES(3);
          126  +  INSERT INTO t1 VALUES(4);
          127  +
          128  +  WITH dset AS ( SELECT 2 UNION ALL SELECT 4 )
          129  +  DELETE FROM t1 WHERE x IN dset;
          130  +  SELECT * FROM t1;
          131  +} {1 3}
          132  +
          133  +do_execsql_test 4.2 {
          134  +  WITH iset AS ( SELECT 2 UNION ALL SELECT 4 )
          135  +  INSERT INTO t1 SELECT * FROM iset;
          136  +  SELECT * FROM t1;
          137  +} {1 3 2 4}
          138  +
          139  +do_execsql_test 4.3 {
          140  +  WITH uset(a, b) AS ( SELECT 2, 8 UNION ALL SELECT 4, 9 )
          141  +  UPDATE t1 SET x = COALESCE( (SELECT b FROM uset WHERE a=x), x );
          142  +  SELECT * FROM t1;
          143  +} {1 3 8 9}
          144  +
          145  +#-------------------------------------------------------------------------
          146  +#
          147  +do_execsql_test 5.1 {
          148  +  WITH i(x) AS ( VALUES(1) UNION ALL SELECT x+1 FROM i)
          149  +  SELECT x FROM i LIMIT 10;
          150  +} {1 2 3 4 5 6 7 8 9 10}
          151  +
          152  +do_catchsql_test 5.2 {
          153  +  WITH i(x) AS ( VALUES(1) UNION ALL SELECT x+1 FROM i ORDER BY 1)
          154  +  SELECT x FROM i LIMIT 10;
          155  +} {1 {ORDER BY in a recursive query is not allowed}}
          156  +
          157  +do_catchsql_test 5.3 {
          158  +  WITH i(x) AS ( VALUES(1) UNION ALL SELECT x+1 FROM i LIMIT 10 )
          159  +  SELECT x FROM i LIMIT 10;
          160  +} {1 {LIMIT in a recursive query is not allowed}}
          161  +
          162  +do_execsql_test 5.4 {
          163  +  WITH i(x) AS ( VALUES(1) UNION ALL SELECT (x+1)%10 FROM i)
          164  +  SELECT x FROM i LIMIT 20;
          165  +} {1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0}
          166  +
          167  +do_execsql_test 5.5 {
          168  +  WITH i(x) AS ( VALUES(1) UNION SELECT (x+1)%10 FROM i)
          169  +  SELECT x FROM i LIMIT 20;
          170  +} {1 2 3 4 5 6 7 8 9 0}
          171  +
          172  +do_catchsql_test 5.6.1 {
          173  +  WITH i(x, y) AS ( VALUES(1) )
          174  +  SELECT * FROM i;
          175  +} {1 {table i has 1 values for 2 columns}}
          176  +
          177  +do_catchsql_test 5.6.2 {
          178  +  WITH i(x) AS ( VALUES(1,2) )
          179  +  SELECT * FROM i;
          180  +} {1 {table i has 2 values for 1 columns}}
          181  +
          182  +do_catchsql_test 5.6.3 {
          183  +  CREATE TABLE t5(a, b);
          184  +  WITH i(x) AS ( SELECT * FROM t5 )
          185  +  SELECT * FROM i;
          186  +} {1 {table i has 2 values for 1 columns}}
          187  +
          188  +do_catchsql_test 5.6.4 {
          189  +  WITH i(x) AS ( SELECT 1, 2 UNION ALL SELECT 1 )
          190  +  SELECT * FROM i;
          191  +} {1 {table i has 2 values for 1 columns}}
          192  +
          193  +do_catchsql_test 5.6.5 {
          194  +  WITH i(x) AS ( SELECT 1 UNION ALL SELECT 1, 2 )
          195  +  SELECT * FROM i;
          196  +} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
          197  +
          198  +do_catchsql_test 5.6.6 {
          199  +  WITH i(x) AS ( SELECT 1 UNION ALL SELECT x+1, x*2 FROM i )
          200  +  SELECT * FROM i;
          201  +} {1 {SELECTs to the left and right of UNION ALL do not have the same number of result columns}}
          202  +
          203  +do_catchsql_test 5.6.7 {
          204  +  WITH i(x) AS ( SELECT 1, 2 UNION SELECT x+1 FROM i )
          205  +  SELECT * FROM i;
          206  +} {1 {table i has 2 values for 1 columns}}
          207  +
          208  +#-------------------------------------------------------------------------
          209  +#
          210  +do_execsql_test 6.1 {
          211  +  CREATE TABLE f(
          212  +      id INTEGER PRIMARY KEY, parentid REFERENCES f, name TEXT
          213  +  );
          214  +
          215  +  INSERT INTO f VALUES(0, NULL, '');
          216  +  INSERT INTO f VALUES(1, 0, 'bin');
          217  +    INSERT INTO f VALUES(2, 1, 'true');
          218  +    INSERT INTO f VALUES(3, 1, 'false');
          219  +    INSERT INTO f VALUES(4, 1, 'ls');
          220  +    INSERT INTO f VALUES(5, 1, 'grep');
          221  +  INSERT INTO f VALUES(6, 0, 'etc');
          222  +    INSERT INTO f VALUES(7, 6, 'rc.d');
          223  +      INSERT INTO f VALUES(8, 7, 'rc.apache');
          224  +      INSERT INTO f VALUES(9, 7, 'rc.samba');
          225  +  INSERT INTO f VALUES(10, 0, 'home');
          226  +    INSERT INTO f VALUES(11, 10, 'dan');
          227  +      INSERT INTO f VALUES(12, 11, 'public_html');
          228  +        INSERT INTO f VALUES(13, 12, 'index.html');
          229  +          INSERT INTO f VALUES(14, 13, 'logo.gif');
          230  +}
          231  +
          232  +do_execsql_test 6.2 {
          233  +  WITH flat(fid, fpath) AS (
          234  +    SELECT id, '' FROM f WHERE parentid IS NULL
          235  +    UNION ALL
          236  +    SELECT id, fpath || '/' || name FROM f, flat WHERE parentid=fid
          237  +  )
          238  +  SELECT fpath FROM flat WHERE fpath!='' ORDER BY 1;
          239  +} {
          240  +  /bin 
          241  +  /bin/false /bin/grep /bin/ls /bin/true 
          242  +  /etc 
          243  +  /etc/rc.d 
          244  +  /etc/rc.d/rc.apache /etc/rc.d/rc.samba 
          245  +  /home 
          246  +  /home/dan 
          247  +  /home/dan/public_html 
          248  +  /home/dan/public_html/index.html 
          249  +  /home/dan/public_html/index.html/logo.gif
          250  +}
          251  +
          252  +do_execsql_test 6.3 {
          253  +  WITH flat(fid, fpath) AS (
          254  +    SELECT id, '' FROM f WHERE parentid IS NULL
          255  +    UNION ALL
          256  +    SELECT id, fpath || '/' || name FROM f, flat WHERE parentid=fid
          257  +  )
          258  +  SELECT count(*) FROM flat;
          259  +} {15}
          260  +
          261  +do_execsql_test 6.4 {
          262  +  WITH x(i) AS (
          263  +    SELECT 1
          264  +    UNION ALL
          265  +    SELECT i+1 FROM x WHERE i<10
          266  +  )
          267  +  SELECT count(*) FROM x
          268  +} {10}
          269  +
          270  +
          271  +#-------------------------------------------------------------------------
          272  +
          273  +do_execsql_test 7.1 {
          274  +  CREATE TABLE tree(i, p);
          275  +  INSERT INTO tree VALUES(1, NULL);
          276  +  INSERT INTO tree VALUES(2, 1);
          277  +  INSERT INTO tree VALUES(3, 1);
          278  +  INSERT INTO tree VALUES(4, 2);
          279  +  INSERT INTO tree VALUES(5, 4);
          280  +}
          281  +
          282  +do_execsql_test 7.2 {
          283  +  WITH t(id, path) AS (
          284  +    SELECT i, '' FROM tree WHERE p IS NULL
          285  +    UNION ALL
          286  +    SELECT i, path || '/' || i FROM tree, t WHERE p = id
          287  +  ) 
          288  +  SELECT path FROM t;
          289  +} {{} /2 /3 /2/4 /2/4/5}
          290  +
          291  +do_execsql_test 7.3 {
          292  +  WITH t(id) AS (
          293  +    VALUES(2)
          294  +    UNION ALL
          295  +    SELECT i FROM tree, t WHERE p = id
          296  +  ) 
          297  +  SELECT id FROM t;
          298  +} {2 4 5}
          299  +
          300  +do_catchsql_test 7.4 {
          301  +  WITH t(id) AS (
          302  +    VALUES(2)
          303  +    UNION ALL
          304  +    SELECT i FROM tree WHERE p IN (SELECT id FROM t)
          305  +  ) 
          306  +  SELECT id FROM t;
          307  +} {1 {recursive reference in a subquery: t}}
          308  +
          309  +do_catchsql_test 7.5 {
          310  +  WITH t(id) AS (
          311  +    VALUES(2)
          312  +    UNION ALL
          313  +    SELECT i FROM tree, t WHERE p = id AND p IN (SELECT id FROM t)
          314  +  ) 
          315  +  SELECT id FROM t;
          316  +} {1 {multiple recursive references: t}}
          317  +
          318  +do_catchsql_test 7.6 {
          319  +  WITH t(id) AS (
          320  +    SELECT i FROM tree WHERE 2 IN (SELECT id FROM t)
          321  +    UNION ALL
          322  +    SELECT i FROM tree, t WHERE p = id
          323  +  ) 
          324  +  SELECT id FROM t;
          325  +} {1 {circular reference: t}}
          326  +
          327  +
          328  +
          329  +finish_test

Added test/with2.test.

            1  +# 2014 January 11
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library.  The
           12  +# focus of this file is testing the WITH clause.
           13  +#
           14  +
           15  +set testdir [file dirname $argv0]
           16  +source $testdir/tester.tcl
           17  +set ::testprefix with2
           18  +
           19  +do_execsql_test 1.0 {
           20  +  CREATE TABLE t1(a);
           21  +  INSERT INTO t1 VALUES(1);
           22  +  INSERT INTO t1 VALUES(2);
           23  +}
           24  +
           25  +do_execsql_test 1.1 {
           26  +  WITH x1 AS (SELECT * FROM t1)
           27  +  SELECT sum(a) FROM x1;
           28  +} {3}
           29  +
           30  +do_execsql_test 1.2 {
           31  +  WITH x1 AS (SELECT * FROM t1)
           32  +  SELECT (SELECT sum(a) FROM x1);
           33  +} {3}
           34  +
           35  +do_execsql_test 1.3 {
           36  +  WITH x1 AS (SELECT * FROM t1)
           37  +  SELECT (SELECT sum(a) FROM x1);
           38  +} {3}
           39  +
           40  +do_execsql_test 1.4 {
           41  +  CREATE TABLE t2(i);
           42  +  INSERT INTO t2 VALUES(2);
           43  +  INSERT INTO t2 VALUES(3);
           44  +  INSERT INTO t2 VALUES(5);
           45  +
           46  +  WITH x1   AS (SELECT i FROM t2),
           47  +       i(a) AS (
           48  +         SELECT min(i)-1 FROM x1 UNION SELECT a+1 FROM i WHERE a<10
           49  +       )
           50  +  SELECT a FROM i WHERE a NOT IN x1
           51  +} {1 4 6 7 8 9 10}
           52  +
           53  +finish_test
           54  +
           55  +
           56  +

Added test/withM.test.

            1  +# 2014 January 11
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library.  The
           12  +# focus of this file is testing the WITH clause.
           13  +#
           14  +
           15  +set testdir [file dirname $argv0]
           16  +source $testdir/tester.tcl
           17  +source $testdir/malloc_common.tcl
           18  +set ::testprefix withM
           19  +
           20  +ifcapable {!cte} {
           21  +  finish_test
           22  +  return
           23  +}
           24  +
           25  +do_execsql_test 1.0 {
           26  +  CREATE TABLE t1(x INTEGER, y INTEGER);
           27  +  INSERT INTO t1 VALUES(123, 456);
           28  +}
           29  +
           30  +do_faultsim_test withM-1.1 -prep {
           31  +  sqlite3 db test.db
           32  +} -body {
           33  +  execsql { 
           34  +    WITH tmp AS ( SELECT * FROM t1 )
           35  +    SELECT * FROM tmp;
           36  +  }
           37  +} -test {
           38  +  faultsim_test_result {0 {123 456}}
           39  +  db close
           40  +}
           41  +
           42  +do_faultsim_test withM-1.2 -prep {
           43  +  sqlite3 db test.db
           44  +} -body {
           45  +  execsql { 
           46  +    WITH w1 AS ( SELECT * FROM t1 ),
           47  +         w2 AS ( 
           48  +           WITH w3 AS ( SELECT * FROM w1 )
           49  +           SELECT * FROM w3
           50  +         )
           51  +    SELECT * FROM w2;
           52  +  }
           53  +} -test {
           54  +  faultsim_test_result {0 {123 456}}
           55  +  db close
           56  +}
           57  +
           58  +finish_test
           59  +
           60  +
           61  +

Changes to tool/mkkeywordhash.c.

   134    134   #  define VTAB       0x00010000
   135    135   #endif
   136    136   #ifdef SQLITE_OMIT_AUTOVACUUM
   137    137   #  define AUTOVACUUM 0
   138    138   #else
   139    139   #  define AUTOVACUUM 0x00020000
   140    140   #endif
          141  +#ifdef SQLITE_OMIT_CTE
          142  +#  define CTE        0
          143  +#else
          144  +#  define CTE        0x00040000
          145  +#endif
   141    146   
   142    147   /*
   143    148   ** These are the keywords
   144    149   */
   145    150   static Keyword aKeywordTable[] = {
   146    151     { "ABORT",            "TK_ABORT",        CONFLICT|TRIGGER       },
   147    152     { "ACTION",           "TK_ACTION",       FKEY                   },
................................................................................
   230    235     { "ORDER",            "TK_ORDER",        ALWAYS                 },
   231    236     { "OUTER",            "TK_JOIN_KW",      ALWAYS                 },
   232    237     { "PLAN",             "TK_PLAN",         EXPLAIN                },
   233    238     { "PRAGMA",           "TK_PRAGMA",       PRAGMA                 },
   234    239     { "PRIMARY",          "TK_PRIMARY",      ALWAYS                 },
   235    240     { "QUERY",            "TK_QUERY",        EXPLAIN                },
   236    241     { "RAISE",            "TK_RAISE",        TRIGGER                },
          242  +  { "RECURSIVE",        "TK_RECURSIVE",    CTE                    },
   237    243     { "REFERENCES",       "TK_REFERENCES",   FKEY                   },
   238    244     { "REGEXP",           "TK_LIKE_KW",      ALWAYS                 },
   239    245     { "REINDEX",          "TK_REINDEX",      REINDEX                },
   240    246     { "RELEASE",          "TK_RELEASE",      ALWAYS                 },
   241    247     { "RENAME",           "TK_RENAME",       ALTER                  },
   242    248     { "REPLACE",          "TK_REPLACE",      CONFLICT               },
   243    249     { "RESTRICT",         "TK_RESTRICT",     FKEY                   },
................................................................................
   258    264     { "UNIQUE",           "TK_UNIQUE",       ALWAYS                 },
   259    265     { "UPDATE",           "TK_UPDATE",       ALWAYS                 },
   260    266     { "USING",            "TK_USING",        ALWAYS                 },
   261    267     { "VACUUM",           "TK_VACUUM",       VACUUM                 },
   262    268     { "VALUES",           "TK_VALUES",       ALWAYS                 },
   263    269     { "VIEW",             "TK_VIEW",         VIEW                   },
   264    270     { "VIRTUAL",          "TK_VIRTUAL",      VTAB                   },
          271  +  { "WITH",             "TK_WITH",         CTE                    },
   265    272     { "WITHOUT",          "TK_WITHOUT",      ALWAYS                 },
   266    273     { "WHEN",             "TK_WHEN",         ALWAYS                 },
   267    274     { "WHERE",            "TK_WHERE",        ALWAYS                 },
   268    275   };
   269    276   
   270    277   /* Number of keywords */
   271    278   static int nKeyword = (sizeof(aKeywordTable)/sizeof(aKeywordTable[0]));