SQLite

** This routine is invoked once per CTE by the parser while parsing a 
** WITH clause. 
*/
With *sqlite3WithAdd(
  Parse *pParse,          /* Parsing context */
  With *pWith,            /* Existing WITH clause, or NULL */
  Token *pName,           /* Name of the common-table */
  ExprList *pArglist,     /* Optional column name list for the table */
  Select *pQuery          /* Query used to initialize the table */
){
  sqlite3 *db = pParse->db;
  With *pNew;
  char *zName;

  /* Check that the CTE name is unique within this WITH clause. If
  ** not, store an error in the Parse structure. */
  zName = sqlite3NameFromToken(pParse->db, pName);
  if( zName && pWith ){
    int i;
    for(i=0; i<pWith->nCte; i++){
      if( sqlite3StrICmp(zName, pWith->a[i].zName)==0 ){
        sqlite3ErrorMsg(pParse, "duplicate cte name: %s", zName);
      }
    }
  }

  if( pWith ){
    int nByte = sizeof(*pWith) + (sizeof(pWith->a[1]) * pWith->nCte);
    pNew = sqlite3DbRealloc(db, pWith, nByte);
  }else{
    pNew = sqlite3DbMallocZero(db, sizeof(*pWith));
  }
  assert( zName!=0 || pNew==0 );

  if( pNew==0 ){
    sqlite3WithDelete(db, pWith);
    sqlite3ExprListDelete(db, pArglist);
    sqlite3SelectDelete(db, pQuery);
    sqlite3DbFree(db, zName);
  }else{
    pNew->a[pNew->nCte].pSelect = pQuery;
    pNew->a[pNew->nCte].pCols = pArglist;
    pNew->a[pNew->nCte].zName = zName;
    pNew->nCte++;
  }

  return pNew;
}

/*
** Free the contents of the With object passed as the second argument.
*/
void sqlite3WithDelete(sqlite3 *db, With *pWith){
  if( pWith ){
    int i;
    for(i=0; i<pWith->nCte; i++){
      struct Cte *pCte = &pWith->a[i];
      sqlite3ExprListDelete(db, pCte->pCols);
      sqlite3SelectDelete(db, pCte->pSelect);
      sqlite3DbFree(db, pCte->zName);
    }
    sqlite3DbFree(db, pWith);
  }
}
#endif /* !defined(SQLITE_OMIT_CTE) */

        }
      }

    }
  }
  return pNew;
}

With *withDup(sqlite3 *db, With *p){
  With *pRet = 0;
  if( p ){
    int nByte = sizeof(*p) + sizeof(p->a[0]) * (p->nCte-1);
    pRet = sqlite3DbMallocZero(db, nByte);
    if( pRet ){
      int i;
      pRet->nCte = p->nCte;
      for(i=0; i<p->nCte; i++){
        pRet->a[i].pSelect = sqlite3SelectDup(db, p->a[i].pSelect, 0);
        pRet->a[i].pCols = sqlite3ExprListDup(db, p->a[i].pCols, 0);
        pRet->a[i].zName = sqlite3DbStrDup(db, p->a[i].zName);
      }
    }
  }
  return pRet;
}

/*
** The following group of routines make deep copies of expressions,
** expression lists, ID lists, and select statements.  The copies can
** be deleted (by being passed to their respective ...Delete() routines)
** without effecting the originals.
**

  pNew->iLimit = 0;
  pNew->iOffset = 0;
  pNew->selFlags = p->selFlags & ~SF_UsesEphemeral;
  pNew->pRightmost = 0;
  pNew->addrOpenEphm[0] = -1;
  pNew->addrOpenEphm[1] = -1;
  pNew->addrOpenEphm[2] = -1;
  pNew->pWith = withDup(db, p->pWith);
  return pNew;
}
#else
Select *sqlite3SelectDup(sqlite3 *db, Select *p, int flags){
  assert( p==0 );
  return 0;
}

  **       INSERT INTO <table1> SELECT * FROM <table2>;
  **
  ** Then special optimizations can be applied that make the transfer
  ** very fast and which reduce fragmentation of indices.
  **
  ** This is the 2nd template.
  */
  if( pColumn==0 && pParse->pWith==0 
   && xferOptimization(pParse, pTab, pSelect, onError, iDb) ){
    assert( !pTrigger );
    assert( pList==0 );
    goto insert_end;
  }
#endif /* SQLITE_OMIT_XFER_OPT */

  /* If this is an AUTOINCREMENT table, look up the sequence number in the

%type select {Select*}
%destructor select {sqlite3SelectDelete(pParse->db, $$);}
%type selectnowith {Select*}
%destructor selectnowith {sqlite3SelectDelete(pParse->db, $$);}
%type oneselect {Select*}
%destructor oneselect {sqlite3SelectDelete(pParse->db, $$);}

select(A) ::= with(W) selectnowith(X). { if( X ) X->pWith = W; A = X; }

selectnowith(A) ::= oneselect(X).                      {A = X;}
%ifndef SQLITE_OMIT_COMPOUND_SELECT
selectnowith(A) ::= selectnowith(X) multiselect_op(Y) oneselect(Z).  {
  if( Z ){
    Z->op = (u8)Y;
    Z->pPrior = X;

                                      {A.pLimit = X.pExpr; A.pOffset = Y.pExpr;}
limit_opt(A) ::= LIMIT expr(X) COMMA expr(Y). 
                                      {A.pOffset = X.pExpr; A.pLimit = Y.pExpr;}

/////////////////////////// The DELETE statement /////////////////////////////
//
%ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
cmd ::= with(C) DELETE FROM fullname(X) indexed_opt(I) where_opt(W) 
        orderby_opt(O) limit_opt(L). {
  sqlite3WithPush(pParse,C);
  sqlite3SrcListIndexedBy(pParse, X, &I);
  W = sqlite3LimitWhere(pParse, X, W, O, L.pLimit, L.pOffset, "DELETE");
  sqlite3DeleteFrom(pParse,X,W);
}
%endif
%ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
cmd ::= with(C) DELETE FROM fullname(X) indexed_opt(I) where_opt(W). {
  sqlite3WithPush(pParse,C);
  sqlite3SrcListIndexedBy(pParse, X, &I);
  sqlite3DeleteFrom(pParse,X,W);
}
%endif

%type where_opt {Expr*}
%destructor where_opt {sqlite3ExprDelete(pParse->db, $$);}

  sqlite3SrcListIndexedBy(pParse, X, &I);
  sqlite3ExprListCheckLength(pParse,Y,"set list"); 
  W = sqlite3LimitWhere(pParse, X, W, O, L.pLimit, L.pOffset, "UPDATE");
  sqlite3Update(pParse,X,Y,W,R);
}
%endif
%ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
cmd ::= with(C) UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y)
        where_opt(W).  {
  sqlite3WithPush(pParse, C);
  sqlite3SrcListIndexedBy(pParse, X, &I);
  sqlite3ExprListCheckLength(pParse,Y,"set list"); 
  sqlite3Update(pParse,X,Y,W,R);
}
%endif

%type setlist {ExprList*}
%destructor setlist {sqlite3ExprListDelete(pParse->db, $$);}

setlist(A) ::= setlist(Z) COMMA nm(X) EQ expr(Y). {
  A = sqlite3ExprListAppend(pParse, Z, Y.pExpr);
  sqlite3ExprListSetName(pParse, A, &X, 1);
}
setlist(A) ::= nm(X) EQ expr(Y). {
  A = sqlite3ExprListAppend(pParse, 0, Y.pExpr);
  sqlite3ExprListSetName(pParse, A, &X, 1);
}

////////////////////////// The INSERT command /////////////////////////////////
//
cmd ::= with(W) insert_cmd(R) INTO fullname(X) inscollist_opt(F) select(S). {
  sqlite3WithPush(pParse, W);
  sqlite3Insert(pParse, X, S, F, R);
}
cmd ::= with(W) insert_cmd(R) INTO fullname(X) inscollist_opt(F) DEFAULT VALUES.
{
  sqlite3WithPush(pParse, W);
  sqlite3Insert(pParse, X, 0, F, R);
}

%type insert_cmd {u8}
insert_cmd(A) ::= INSERT orconf(R).   {A = R;}
insert_cmd(A) ::= REPLACE.            {A = OE_Replace;}

%type inscollist_opt {IdList*}
%destructor inscollist_opt {sqlite3IdListDelete(pParse->db, $$);}

%endif  SQLITE_OMIT_VIRTUALTABLE


//////////////////////// COMMON TABLE EXPRESSIONS ////////////////////////////
%type with {With*}
%type wqlist {With*}
%destructor with {sqlite3WithDelete(pParse->db, $$);}
%destructor wqlist {sqlite3WithDelete(pParse->db, $$);}

with(A) ::= . {A = 0;}
%ifndef SQLITE_OMIT_CTE
with(A) ::= WITH wqlist(W).              { A = W; }
with(A) ::= WITH RECURSIVE wqlist(W).    { A = W; }

wqlist(A) ::= nm(X) idxlist_opt(Y) AS LP select(Z) RP. {
  A = sqlite3WithAdd(pParse, 0, &X, Y, Z);
}
wqlist(A) ::= wqlist(W) COMMA nm(X) idxlist_opt(Y) AS LP select(Z) RP. {
  A = sqlite3WithAdd(pParse, W, &X, Y, Z);
}
%endif  SQLITE_OMIT_CTE

  sqlite3ExprDelete(db, p->pWhere);
  sqlite3ExprListDelete(db, p->pGroupBy);
  sqlite3ExprDelete(db, p->pHaving);
  sqlite3ExprListDelete(db, p->pOrderBy);
  sqlite3SelectDelete(db, p->pPrior);
  sqlite3ExprDelete(db, p->pLimit);
  sqlite3ExprDelete(db, p->pOffset);
  sqlite3WithDelete(db, p->pWith);
}

/*
** Initialize a SelectDest structure.
*/
void sqlite3SelectDestInit(SelectDest *pDest, int eDest, int iParm){
  pDest->eDest = (u8)eDest;

  pNew->pHaving = 0;
  pNew->pOrderBy = 0;
  p->pPrior = 0;
  pNew->pLimit = 0;
  pNew->pOffset = 0;
  return WRC_Continue;
}

static struct Cte *searchWith(Parse *pParse, struct SrcList_item *p){
  if( p->zDatabase==0 ){
    char *zName = p->zName;
    With *pWith;

    for(pWith=pParse->pWith; pWith; pWith=pWith->pOuter){
      int i;
      for(i=0; i<pWith->nCte; i++){
        if( sqlite3StrICmp(zName, pWith->a[i].zName)==0 ){
          return &pWith->a[i];
        }
      }
    }
  }
  return 0;
}

void sqlite3WithPush(Parse *pParse, With *pWith){
  if( pWith ){
    pWith->pOuter = pParse->pWith;
    pParse->pWith = pWith;
  }
}
static void withPop(Parse *pParse, With *pWith){
  if( pWith ){
    assert( pParse->pWith==pWith );
    pParse->pWith = pWith->pOuter;
  }
}

static int ctePush(Parse *pParse, struct Cte *pCte){
  if( pCte ){
    struct Cte *p;
    for(p=pParse->pCte; p; p=p->pOuterCte){
      if( p==pCte ){
        sqlite3ErrorMsg(
            pParse, "illegal recursive defininition in cte: %s", pCte->zName
        );
        return SQLITE_ERROR;
      }
    }
    
    pCte->pOuterCte = pParse->pCte;
    pParse->pCte = pCte;
  }
  return SQLITE_OK;
}

static void ctePop(Parse *pParse, struct Cte *pCte){
  if( pCte ){
    assert( pParse->pCte==pCte );
    pParse->pCte = pCte->pOuterCte;
  }
}


/*
** This routine is a Walker callback for "expanding" a SELECT statement.
** "Expanding" means to do the following:
**
**    (1)  Make sure VDBE cursor numbers have been assigned to every
**         element of the FROM clause.

  sqlite3SrcListAssignCursors(pParse, pTabList);

  /* Look up every table named in the FROM clause of the select.  If
  ** an entry of the FROM clause is a subquery instead of a table or view,
  ** then create a transient table structure to describe the subquery.
  */
  for(i=0, pFrom=pTabList->a; i<pTabList->nSrc; i++, pFrom++){
    struct Cte *pCte = 0;
    Table *pTab;
    if( pFrom->pTab!=0 ){
      /* This statement has already been prepared.  There is no need
      ** to go further. */
      assert( i==0 );
      return WRC_Prune;
    }
#ifndef SQLITE_OMIT_CTE
    pCte = searchWith(pParse, pFrom);
    if( pCte ){
      pFrom->pSelect = sqlite3SelectDup(db, pCte->pSelect, 0);
      if( pFrom->pSelect==0 ) return WRC_Abort;
    }
#endif
    if( pFrom->zName==0 || pCte ){
#ifndef SQLITE_OMIT_SUBQUERY
      ExprList *pEList;
      Select *pSel = pFrom->pSelect;
      /* A sub-query in the FROM clause of a SELECT */
      assert( pSel!=0 );
      assert( pFrom->pTab==0 );
      if( ctePush(pParse, pCte) ) return WRC_Abort;
      sqlite3WalkSelect(pWalker, pSel);
      ctePop(pParse, pCte);
      pFrom->pTab = pTab = sqlite3DbMallocZero(db, sizeof(Table));
      if( pTab==0 ) return WRC_Abort;
      pTab->nRef = 1;
      pTab->zName = sqlite3MPrintf(db, "sqlite_sq_%p", (void*)pTab);
      while( pSel->pPrior ){ pSel = pSel->pPrior; }
      if( pCte && pCte->pCols ){
        pEList = pCte->pCols;
      }else{
        pEList = pSel->pEList;
      }
      selectColumnsFromExprList(pParse, pEList, &pTab->nCol, &pTab->aCol);
      pTab->iPKey = -1;
      pTab->nRowEst = 1048576;
      pTab->tabFlags |= TF_Ephemeral;
#endif
    }else{
      /* An ordinary table or view name in the FROM clause */
      assert( pFrom->pTab==0 );

#if SQLITE_MAX_COLUMN
  if( p->pEList && p->pEList->nExpr>db->aLimit[SQLITE_LIMIT_COLUMN] ){
    sqlite3ErrorMsg(pParse, "too many columns in result set");
  }
#endif
  return WRC_Continue;
}

static int selectExpanderWith(Walker *pWalker, Select *p){
  int res;
  sqlite3WithPush(pWalker->pParse, p->pWith);
  res = selectExpander(pWalker, p);
  withPop(pWalker->pParse, p->pWith);
  return res;
}

/*
** No-op routine for the parse-tree walker.
**
** When this routine is the Walker.xExprCallback then expression trees
** are walked without any actions being taken at each node.  Presumably,
** when this routine is used for Walker.xExprCallback then 

  memset(&w, 0, sizeof(w));
  w.xExprCallback = exprWalkNoop;
  w.pParse = pParse;
  if( pParse->hasCompound ){
    w.xSelectCallback = convertCompoundSelectToSubquery;
    sqlite3WalkSelect(&w, pSelect);
  }
  w.xSelectCallback = selectExpanderWith;
  sqlite3WalkSelect(&w, pSelect);
}


#ifndef SQLITE_OMIT_SUBQUERY
/*
** This is a Walker.xSelectCallback callback for the sqlite3SelectTypeInfo()

  Expr *pHaving;         /* The HAVING clause */
  ExprList *pOrderBy;    /* The ORDER BY clause */
  Select *pPrior;        /* Prior select in a compound select statement */
  Select *pNext;         /* Next select to the left in a compound */
  Select *pRightmost;    /* Right-most select in a compound select statement */
  Expr *pLimit;          /* LIMIT expression. NULL means not used. */
  Expr *pOffset;         /* OFFSET expression. NULL means not used. */
  With *pWith;           /* WITH clause attached to this select. Or NULL. */
};

/*
** Allowed values for Select.selFlags.  The "SF" prefix stands for
** "Select Flag".
*/
#define SF_Distinct        0x0001  /* Output should be DISTINCT */

  Token sLastToken;         /* The last token parsed */
#ifndef SQLITE_OMIT_VIRTUALTABLE
  Token sArg;               /* Complete text of a module argument */
  Table **apVtabLock;       /* Pointer to virtual tables needing locking */
#endif
  Table *pZombieTab;        /* List of Table objects to delete after code gen */
  TriggerPrg *pTriggerPrg;  /* Linked list of coded triggers */
  With *pWith;              /* Current WITH clause, or NULL */
  struct Cte *pCte;         /* Current CTE, or NULL */
};

/*
** Return true if currently inside an sqlite3_declare_vtab() call.
*/
#ifdef SQLITE_OMIT_VIRTUALTABLE
  #define IN_DECLARE_VTAB 0

/*
** An instance of this structure represents a set of CTEs (common table 
** expressions) created by a single WITH clause.
*/
struct With {
  int nCte;                       /* Number of CTEs */
  With *pOuter;                   /* Containing WITH clause, or NULL */
  struct Cte {
    char *zName;                  /* Name of this CTE */
    ExprList *pCols;              /* List of explicit column names, or NULL */
    Select *pSelect;              /* The contents of the CTE */
    struct Cte *pOuterCte;
  } a[1];
};

/*
** Assuming zIn points to the first byte of a UTF-8 character,
** advance zIn to point to the first byte of the next UTF-8 character.
*/

int sqlite3TempInMemory(const sqlite3*);
const char *sqlite3JournalModename(int);
#ifndef SQLITE_OMIT_WAL
  int sqlite3Checkpoint(sqlite3*, int, int, int*, int*);
  int sqlite3WalDefaultHook(void*,sqlite3*,const char*,int);
#endif
#ifndef SQLITE_OMIT_CTE
  With *sqlite3WithAdd(Parse*,With*,Token*,ExprList*,Select*);
  void sqlite3WithDelete(sqlite3*,With*);
  void sqlite3WithPush(Parse*, With*);
#else
#define sqlite3WithPush(x,y)
#endif

/* Declarations for functions in fkey.c. All of these are replaced by
** no-op macros if OMIT_FOREIGN_KEY is defined. In this case no foreign
** key functionality is available. If OMIT_TRIGGER is defined but
** OMIT_FOREIGN_KEY is not, only some of the functions are no-oped. In
** this case foreign keys are parsed, but no other functionality is 

    /* If the pParse->declareVtab flag is set, do not delete any table 
    ** structure built up in pParse->pNewTable. The calling code (see vtab.c)
    ** will take responsibility for freeing the Table structure.
    */
    sqlite3DeleteTable(db, pParse->pNewTable);
  }

  assert( pParse->pWith==0 || pParse->pWith->pOuter==0 );
  sqlite3WithDelete(db, pParse->pWith);
  sqlite3DeleteTrigger(db, pParse->pNewTrigger);
  for(i=pParse->nzVar-1; i>=0; i--) sqlite3DbFree(db, pParse->azVar[i]);
  sqlite3DbFree(db, pParse->azVar);
  while( pParse->pAinc ){
    AutoincInfo *p = pParse->pAinc;
    pParse->pAinc = p->pNext;
    sqlite3DbFree(db, p);

} -test {
  faultsim_test_result {0 4} 
  faultsim_integrity_check
  if {[db one { SELECT count(*) FROM t1 }] != 4} {
    error "Database content appears incorrect"
  }
}
finish_test

#-------------------------------------------------------------------------
# Test fault-injection while rolling back a hot-journal file with a 
# page-size different from the current value stored on page 1 of the
# database file.
#
do_test pagerfault-2-pre1 {

  WITH x(a) AS ( SELECT * FROM t1) DELETE FROM t1;
} {}

do_execsql_test 1.4 {
  WITH x(a) AS ( SELECT * FROM t1) UPDATE t1 SET x = y;
} {}
 
#--------------------------------------------------------------------------

do_execsql_test 2.1 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(x);
  INSERT INTO t1 VALUES(1);
  INSERT INTO t1 VALUES(2);
  WITH tmp AS ( SELECT * FROM t1 ) SELECT x FROM tmp;
} {1 2}

do_execsql_test 2.2 {
  WITH tmp(a) AS ( SELECT * FROM t1 ) SELECT a FROM tmp;
} {1 2}

do_execsql_test 2.3 {
  SELECT * FROM (
    WITH tmp(a) AS ( SELECT * FROM t1 ) SELECT a FROM tmp
  );
} {1 2}

do_execsql_test 2.4 {
  WITH tmp1(a) AS ( SELECT * FROM t1 ),
       tmp2(x) AS ( SELECT * FROM tmp1)
  SELECT * FROM tmp2;
} {1 2}

do_execsql_test 2.5 {
  WITH tmp2(x) AS ( SELECT * FROM tmp1),
       tmp1(a) AS ( SELECT * FROM t1 )
  SELECT * FROM tmp2;
} {1 2}

#-------------------------------------------------------------------------
do_catchsql_test 3.1 {
  WITH tmp2(x) AS ( SELECT * FROM tmp1),
       tmp1(a) AS ( SELECT * FROM tmp2 )
  SELECT * FROM tmp1;
} {1 {illegal recursive defininition in cte: tmp1}}

do_catchsql_test 3.2 {
  CREATE TABLE t2(x INTEGER);
  WITH tmp(a) AS (SELECT * FROM t1),
       tmp(a) AS (SELECT * FROM t1)
  SELECT * FROM tmp;
} {1 {duplicate cte name: tmp}}

do_execsql_test 3.3 {
  CREATE TABLE t3(x);
  CREATE TABLE t4(x);

  INSERT INTO t3 VALUES('T3');
  INSERT INTO t4 VALUES('T4');

  WITH t3(a) AS (SELECT * FROM t4)
  SELECT * FROM t3;
} {T4}

do_execsql_test 3.4 {
  WITH tmp  AS ( SELECT * FROM t3 ),
       tmp2 AS ( WITH tmp AS ( SELECT * FROM t4 ) SELECT * FROM tmp )
  SELECT * FROM tmp2;
} {T4}

do_execsql_test 3.5 {
  WITH tmp  AS ( SELECT * FROM t3 ),
       tmp2 AS ( WITH xxxx AS ( SELECT * FROM t4 ) SELECT * FROM tmp )
  SELECT * FROM tmp2;
} {T3}

do_catchsql_test 3.6 {
  WITH tmp AS ( SELECT * FROM t3 ),
  SELECT * FROM tmp;
} {1 {near "SELECT": syntax error}}

#-------------------------------------------------------------------------
do_execsql_test 4.1 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(x);
  INSERT INTO t1 VALUES(1);
  INSERT INTO t1 VALUES(2);
  INSERT INTO t1 VALUES(3);
  INSERT INTO t1 VALUES(4);

  WITH dset AS ( SELECT 2 UNION ALL SELECT 4 )
  DELETE FROM t1 WHERE x IN dset;
  SELECT * FROM t1;
} {1 3}

do_execsql_test 4.2 {
  WITH iset AS ( SELECT 2 UNION ALL SELECT 4 )
  INSERT INTO t1 SELECT * FROM iset;
  SELECT * FROM t1;
} {1 3 2 4}

do_execsql_test 4.3 {
  WITH uset(a, b) AS ( SELECT 2, 8 UNION ALL SELECT 4, 9 )
  UPDATE t1 SET x = COALESCE( (SELECT b FROM uset WHERE a=x), x );
  SELECT * FROM t1;
} {1 3 8 9}

finish_test

# 2014 January 11
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the WITH clause.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
source $testdir/malloc_common.tcl
set ::testprefix withM

do_execsql_test 1.0 {
  CREATE TABLE t1(x INTEGER, y INTEGER);
  INSERT INTO t1 VALUES(123, 456);
}

do_faultsim_test withM-1 -prep {
  sqlite3 db test.db
} -body {
  execsql { 
    WITH tmp AS ( SELECT * FROM t1 )
    SELECT * FROM tmp;
  }
} -test {
  faultsim_test_result {0 {123 456}}
  db close
}

finish_test