SQLite

    pNewItem->zAlias = sqlite3DbStrDup(db, pOldItem->zAlias);
    pNewItem->jointype = pOldItem->jointype;
    pNewItem->iCursor = pOldItem->iCursor;
    pNewItem->addrFillSub = pOldItem->addrFillSub;
    pNewItem->regReturn = pOldItem->regReturn;
    pNewItem->isCorrelated = pOldItem->isCorrelated;
    pNewItem->viaCoroutine = pOldItem->viaCoroutine;
    pNewItem->isRecursive = pOldItem->isRecursive;
    pNewItem->zIndex = sqlite3DbStrDup(db, pOldItem->zIndex);
    pNewItem->notIndexed = pOldItem->notIndexed;
    pNewItem->pIndex = pOldItem->pIndex;
    pTab = pNewItem->pTab = pOldItem->pTab;
    if( pTab ){
      pTab->nRef++;
    }

  pExpr->op = (isTrigger ? TK_TRIGGER : TK_COLUMN);
lookupname_end:
  if( cnt==1 ){
    assert( pNC!=0 );
    if( pExpr->op!=TK_AS ){
      sqlite3AuthRead(pParse, pExpr, pSchema, pNC->pSrcList);
    }

    /* If this expression reads a column value from a recursive CTE 
    ** reference, then this is equivalent to reading from the outermost
    ** available name-context.  */
    if( pMatch && pMatch->isRecursive ){
      while( pNC->pNext ) pNC = pNC->pNext;
    }

    /* Increment the nRef value on all name contexts from TopNC up to
    ** the point where the name matched. */
    for(;;){
      assert( pTopNC!=0 );
      pTopNC->nRef++;
      if( pTopNC==pNC ) break;
      pTopNC = pTopNC->pNext;

  sqlite3 *db = pParse->db;

  assert( pFrom->pSelect==0 );
  assert( pFrom->pTab==0 );

  if( pCte==pParse->pCte && (pTab = pCte->pTab) ){
    /* This is the recursive part of a recursive CTE */
    assert( pFrom->pTab==0 && pFrom->isRecursive==0 );
    pFrom->pTab = pTab;
    pFrom->isRecursive = 1;
    pTab->nRef++;
  }else{
    ExprList *pEList;
    Select *pSel;
    Select *pLeft;                /* Left-most SELECT statement */
    int bRecursive;

    Select *pSelect;  /* A SELECT statement used in place of a table name */
    int addrFillSub;  /* Address of subroutine to manifest a subquery */
    int regReturn;    /* Register holding return address of addrFillSub */
    u8 jointype;      /* Type of join between this able and the previous */
    unsigned notIndexed :1;    /* True if there is a NOT INDEXED clause */
    unsigned isCorrelated :1;  /* True if sub-query is correlated */
    unsigned viaCoroutine :1;  /* Implemented as a co-routine */
    unsigned isRecursive :1;   /* True for recursive reference in WITH */
#ifndef SQLITE_OMIT_EXPLAIN
    u8 iSelectId;     /* If pSelect!=0, the id of the sub-select in EQP */
#endif
    int iCursor;      /* The VDBE cursor number used to access this table */
    Expr *pOn;        /* The ON clause of a join */
    IdList *pUsing;   /* The USING clause of a join */
    Bitmask colUsed;  /* Bit N (1<<N) set if column N of pTab is used */

  /etc/rc.d/rc.apache /etc/rc.d/rc.samba 
  /home 
  /home/dan 
  /home/dan/public_html 
  /home/dan/public_html/index.html 
  /home/dan/public_html/index.html/logo.gif
}

do_execsql_test 6.3 {
  WITH flat(fid, fpath) AS (
    SELECT id, '' FROM f WHERE parentid IS NULL
    UNION ALL
    SELECT id, fpath || '/' || name FROM f, flat WHERE parentid=+fid
  )
  SELECT count(*) FROM flat;
} {15}

do_execsql_test 6.4 {
  WITH x(i) AS (
    SELECT 1
    UNION ALL
    SELECT i+1 FROM x WHERE i<10
  )
  SELECT count(*) FROM x
} {10}


finish_test