SQLite

                                      {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);
  sqlite3WithPush(pParse, C, 1);
  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);
  sqlite3WithPush(pParse, C, 1);
  sqlite3SrcListIndexedBy(pParse, X, &I);
  sqlite3DeleteFrom(pParse,X,W);
}
%endif

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

where_opt(A) ::= .                    {A = 0;}
where_opt(A) ::= WHERE expr(X).       {A = X.pExpr;}

////////////////////////// The UPDATE command ////////////////////////////////
//
%ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
cmd ::= with(C) UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y)
        where_opt(W) orderby_opt(O) limit_opt(L).  {
  sqlite3WithPush(pParse, C);
  sqlite3WithPush(pParse, C, 1);
  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);
  sqlite3WithPush(pParse, C, 1);
  sqlite3SrcListIndexedBy(pParse, X, &I);
  sqlite3ExprListCheckLength(pParse,Y,"set list"); 
  sqlite3Update(pParse,X,Y,W,R);
}
%endif

%type setlist {ExprList*}

  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);
  sqlite3WithPush(pParse, W, 1);
  sqlite3Insert(pParse, X, S, F, R);
}
cmd ::= with(W) insert_cmd(R) INTO fullname(X) inscollist_opt(F) DEFAULT VALUES.
{
  sqlite3WithPush(pParse, W);
  sqlite3WithPush(pParse, W, 1);
  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;}

  }
  return 0;
}

/* The code generator maintains a stack of active WITH clauses
** with the inner-most WITH clause being at the top of the stack.
**
** This routine pushes the WITH clause passed as the second argument
** onto the top of the stack. If argument bFree is true, then this
** These routines push and pull WITH clauses on the stack.
** WITH clause will never be popped from the stack. In this case it
** should be freed along with the Parse object. In other cases, when
** bFree==0, the With object will be freed along with the SELECT 
** statement with which it is associated.
*/
void sqlite3WithPush(Parse *pParse, With *pWith){
void sqlite3WithPush(Parse *pParse, With *pWith, u8 bFree){
  assert( bFree==0 || pParse->pWith==0 );
  if( pWith ){
    pWith->pOuter = pParse->pWith;
    pParse->pWith = pWith;
    pParse->bFreeWith = bFree;
  }
}

/*
** This function checks if argument pFrom refers to a CTE declared by 
** a WITH clause on the stack currently maintained by the parser. And,
** if currently processing a CTE expression, if it is a recursive

    pCte->zErr = 0;
  }

  return SQLITE_OK;
}
#endif

#ifndef SQLITE_OMIT_CTE
static void selectPopWith(Walker *pWalker, Select *p){
  Parse *pParse = pWalker->pParse;
  if( p->pWith ){
    assert( pParse->pWith==p->pWith );
    pParse->pWith = p->pWith->pOuter;
  }
  return WRC_Continue;
}
#else
#define selectPopWith 0
#endif

/*
** 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.
**

    return WRC_Abort;
  }
  if( NEVER(p->pSrc==0) || (selFlags & SF_Expanded)!=0 ){
    return WRC_Prune;
  }
  pTabList = p->pSrc;
  pEList = p->pEList;
  sqlite3WithPush(pParse, p->pWith, 0);

  /* Make sure cursor numbers have been assigned to all entries in
  ** the FROM clause of the SELECT statement.
  */
  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++){
    Table *pTab;
    assert( pFrom->isRecursive==0 || pFrom->pTab );
    if( pFrom->isRecursive ) continue;
    if( pFrom->pTab!=0 ){
      /* This statement has already been prepared.  There is no need
      ** to go further. */
      assert( i==0 );
#ifndef SQLITE_OMIT_CTE
      selectPopWith(pWalker, p);
#endif
      return WRC_Prune;
    }
#ifndef SQLITE_OMIT_CTE
    if( withExpand(pWalker, pFrom) ) return WRC_Abort;
    if( pFrom->pTab ) {} else
#endif
    if( pFrom->zName==0 ){

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

/*
** Function (or macro) selectExpanderWith is used as the SELECT callback
** by sqlite3SelectExpand(). In builds that do not support CTEs, this
** is equivalent to the selectExpander() function. In CTE-enabled builds,
** any WITH clause associated with the SELECT statement needs to be
** pushed onto the stack before calling selectExpander(), and popped
** off again afterwards. 
*/
#ifndef SQLITE_OMIT_CTE
static int selectExpanderWith(Walker *pWalker, Select *p){
  Parse *pParse = pWalker->pParse;
  int res;
  sqlite3WithPush(pParse, p->pWith);
  res = selectExpander(pWalker, p);
  if( p->pWith ){
    assert( pParse->pWith==p->pWith );
    pParse->pWith = p->pWith->pOuter;
  }
  return res;
}
#else
#define selectExpanderWith selectExpander
#endif

/*
** 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 
** Walker.xSelectCallback is set to do something useful for every 

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


#ifndef SQLITE_OMIT_SUBQUERY
/*
** This is a Walker.xSelectCallback callback for the sqlite3SelectTypeInfo()
** interface.
**
** For each FROM-clause subquery, add Column.zType and Column.zColl
** information to the Table structure that represents the result set
** of that subquery.
**
** The Table structure that represents the result set was constructed
** by selectExpander() but the type and collation information was omitted
** at that point because identifiers had not yet been resolved.  This
** routine is called after identifier resolution.
*/
static int selectAddSubqueryTypeInfo(Walker *pWalker, Select *p){
static void selectAddSubqueryTypeInfo(Walker *pWalker, Select *p){
  Parse *pParse;
  int i;
  SrcList *pTabList;
  struct SrcList_item *pFrom;

  assert( p->selFlags & SF_Resolved );
  if( (p->selFlags & SF_HasTypeInfo)==0 ){

        if( pSel ){
          while( pSel->pPrior ) pSel = pSel->pPrior;
          selectAddColumnTypeAndCollation(pParse, pTab, pSel);
        }
      }
    }
  }
  return WRC_Continue;
}
#endif


/*
** This routine adds datatype and collating sequence information to
** the Table structures of all FROM-clause subqueries in a
** SELECT statement.
**
** Use this routine after name resolution.
*/
static void sqlite3SelectAddTypeInfo(Parse *pParse, Select *pSelect){
#ifndef SQLITE_OMIT_SUBQUERY
  Walker w;
  memset(&w, 0, sizeof(w));
  w.xSelectCallback = selectAddSubqueryTypeInfo;
  w.xSelectCallback2 = selectAddSubqueryTypeInfo;
  w.xExprCallback = exprWalkNoop;
  w.pParse = pParse;
  w.bSelectDepthFirst = 1;
  sqlite3WalkSelect(&w, pSelect);
#endif
}


/*
** This routine sets up a SELECT statement for processing.  The

#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 */
  u8 bFreeWith;             /* True if pWith should be freed with parser */
};

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

/*
** Context pointer passed down through the tree-walk.
*/
struct Walker {
  int (*xExprCallback)(Walker*, Expr*);     /* Callback for expressions */
  int (*xSelectCallback)(Walker*,Select*);  /* Callback for SELECTs */
  void (*xSelectCallback2)(Walker*,Select*);/* Second callback for SELECTs */
  Parse *pParse;                            /* Parser context.  */
  int walkerDepth;                          /* Number of subqueries */
  u8 bSelectDepthFirst;                     /* Do subqueries first */
  union {                                   /* Extra data for callback */
    NameContext *pNC;                          /* Naming context */
    int i;                                     /* Integer value */
    SrcList *pSrcList;                         /* FROM clause */
    struct SrcCount *pSrcCount;                /* Counting column references */
  } u;
};

#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*);
  void sqlite3WithPush(Parse*, With*, u8);
#else
#define sqlite3WithPush(x,y)
#define sqlite3WithPush(x,y,z)
#define sqlite3WithDelete(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

    /* 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);
  if( pParse->bFreeWith ) 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);

  }
  return WRC_Continue;
} 

/*
** Call sqlite3WalkExpr() for every expression in Select statement p.
** Invoke sqlite3WalkSelect() for subqueries in the FROM clause and
** on the compound select chain, p->pPrior.  Invoke the xSelectCallback()
** either before or after the walk of expressions and FROM clause, depending
** on the compound select chain, p->pPrior. 
**
** If it is not NULL, the xSelectCallback() callback is invoked before
** the walk of the expressions and FROM clause. The xSelectCallback2()
** method, if it is not NULL, is invoked following the walk of the 
** expressions and FROM clause.
** on whether pWalker->bSelectDepthFirst is false or true, respectively.
**
** Return WRC_Continue under normal conditions.  Return WRC_Abort if
** there is an abort request.
**
** If the Walker does not have an xSelectCallback() then this routine
** is a no-op returning WRC_Continue.
*/
int sqlite3WalkSelect(Walker *pWalker, Select *p){
  int rc;
  if( p==0 || (pWalker->xSelectCallback==0 && pWalker->xSelectCallback2==0) ){
  if( p==0 || pWalker->xSelectCallback==0 ) return WRC_Continue;
    return WRC_Continue;
  }
  rc = WRC_Continue;
  pWalker->walkerDepth++;
  while( p ){
    if( !pWalker->bSelectDepthFirst ){
    if( pWalker->xSelectCallback ){
       rc = pWalker->xSelectCallback(pWalker, p);
       if( rc ) break;
    }
    if( sqlite3WalkSelectExpr(pWalker, p)
     || sqlite3WalkSelectFrom(pWalker, p)
    ){
      pWalker->walkerDepth--;
      return WRC_Abort;
    }
    if( pWalker->bSelectDepthFirst ){
      rc = pWalker->xSelectCallback(pWalker, p);
    if( pWalker->xSelectCallback2 ){
      pWalker->xSelectCallback2(pWalker, p);
      /* Depth-first search is currently only used for
      ** selectAddSubqueryTypeInfo() and that routine always returns
      ** WRC_Continue (0).  So the following branch is never taken. */
      if( NEVER(rc) ) break;
    }
    p = p->pPrior;
  }
  pWalker->walkerDepth--;
  return rc & WRC_Abort;
}

# 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
set ::testprefix with2

do_execsql_test 1.0 {
  CREATE TABLE t1(a);
  INSERT INTO t1 VALUES(1);
  INSERT INTO t1 VALUES(2);
}

do_execsql_test 1.1 {
  WITH x1 AS (SELECT * FROM t1)
  SELECT sum(a) FROM x1;
} {3}

do_execsql_test 1.2 {
  WITH x1 AS (SELECT * FROM t1)
  SELECT (SELECT sum(a) FROM x1);
} {3}

do_execsql_test 1.3 {
  WITH x1 AS (SELECT * FROM t1)
  SELECT (SELECT sum(a) FROM x1);
} {3}

do_execsql_test 1.4 {
  CREATE TABLE t2(i);
  INSERT INTO t2 VALUES(2);
  INSERT INTO t2 VALUES(3);
  INSERT INTO t2 VALUES(5);

  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}

finish_test