SQLite

#ifndef SQLITE_OMIT_CTE
/*
** Argument pWith (which may be NULL) points to a linked list of nested 
** WITH contexts, from inner to outermost. If the table identified by 
** FROM clause element pItem is really a common-table-expression (CTE) 
** then return a pointer to the CTE definition for that table. Otherwise
** return NULL.
**
** If a non-NULL value is returned, set *ppContext to point to the With
** object that the returned CTE belongs to.
*/
static struct Cte *searchWith(
  With *pWith,                    /* Current outermost WITH clause */
  struct SrcList_item *pItem,     /* FROM clause element to resolve */
  With **ppContext                /* OUT: WITH clause return value belongs to */
){
  const char *zName;
  if( pItem->zDatabase==0 && (zName = pItem->zName)!=0 ){
    With *p;
    for(p=pWith; p; p=p->pOuter){
      int i;
      for(i=0; i<p->nCte; i++){
        if( sqlite3StrICmp(zName, p->a[i].zName)==0 ){
          *ppContext = p;
          return &p->a[i];
        }
      }
    }
  }
  return 0;
}

** occurs. If an error does occur, an error message is stored in the
** parser and some error code other than SQLITE_OK returned.
*/
static int withExpand(
  Walker *pWalker, 
  struct SrcList_item *pFrom
){

  Parse *pParse = pWalker->pParse;
  sqlite3 *db = pParse->db;
  struct Cte *pCte;               /* Matched CTE (or NULL if no match) */
  With *pWith;                    /* WITH clause that pCte belongs to */

  assert( pFrom->pTab==0 );

  pCte = searchWith(pParse->pWith, pFrom, &pWith);
  if( pCte ){
    Table *pTab;
    ExprList *pEList;
    Select *pSel;
    Select *pLeft;                /* Left-most SELECT statement */
    int bMayRecursive;            /* True if compound joined by UNION [ALL] */
    With *pSavedWith;             /* Initial value of pParse->pWith */

    /* If pCte->zErr is non-NULL at this point, then this is an illegal
    ** recursive reference to CTE pCte. Leave an error in pParse and return
    ** early. If pCte->zErr is NULL, then this is not a recursive reference.
    ** In this case, proceed.  */
    if( pCte->zErr ){
      sqlite3ErrorMsg(pParse, pCte->zErr, pCte->zName);
      return SQLITE_ERROR;
    }

    pFrom->pTab = pTab = sqlite3DbMallocZero(db, sizeof(Table));
    if( pTab==0 ) return WRC_Abort;
    pTab->nRef = 1;
    pTab->zName = sqlite3DbStrDup(db, pCte->zName);
    pTab->iPKey = -1;

    }

    /* Only one recursive reference is permitted. */ 
    if( pTab->nRef>2 ){
      sqlite3ErrorMsg(
          pParse, "multiple references to recursive table: %s", pCte->zName
      );
      return SQLITE_ERROR;
    }
    assert( pTab->nRef==1 || ((pSel->selFlags&SF_Recursive) && pTab->nRef==2 ));

    pCte->zErr = "circular reference: %s";
    pSavedWith = pParse->pWith;
    pParse->pWith = pWith;
    sqlite3WalkSelect(pWalker, bMayRecursive ? pSel->pPrior : pSel);

    for(pLeft=pSel; pLeft->pPrior; pLeft=pLeft->pPrior);
    pEList = pLeft->pEList;
    if( pCte->pCols ){
      if( pEList->nExpr!=pCte->pCols->nExpr ){
        sqlite3ErrorMsg(pParse, "table %s has %d values for %d columns",
            pCte->zName, pEList->nExpr, pCte->pCols->nExpr
        );
        pParse->pWith = pSavedWith;
        return SQLITE_ERROR;
      }
      pEList = pCte->pCols;
    }


    selectColumnsFromExprList(pParse, pEList, &pTab->nCol, &pTab->aCol);
    if( bMayRecursive ){
      if( pSel->selFlags & SF_Recursive ){
        pCte->zErr = "multiple recursive references: %s";
      }else{
        pCte->zErr = "recursive reference in a subquery: %s";
      }
      sqlite3WalkSelect(pWalker, pSel);
    }
    pCte->zErr = 0;
    pParse->pWith = pSavedWith;
  }

  return SQLITE_OK;
}
#endif

#ifndef SQLITE_OMIT_CTE

  WITH x1   AS (SELECT i FROM t2),
       i(a) AS (
         SELECT min(i)-1 FROM x1 UNION SELECT a+1 FROM i WHERE a<10
       )
  SELECT a FROM i WHERE a NOT IN x1
} {1 4 6 7 8 9 10}

do_execsql_test 1.5 {
  WITH x1 AS (SELECT a FROM t1),
       x2 AS (SELECT i FROM t2),
       x3 AS (SELECT * FROM x1, x2 WHERE x1.a IN x2 AND x2.i IN x1)
  SELECT * FROM x3 
} {2 2}

do_execsql_test 1.6 {
  CREATE TABLE t3 AS SELECT 3 AS x;
  CREATE TABLE t4 AS SELECT 4 AS x;

  WITH x1 AS (SELECT * FROM t3),
       x2 AS (
         WITH t3 AS (SELECT * FROM t4)
         SELECT * FROM x1
       )
  SELECT * FROM x2;
} {3}

finish_test