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

Changes to src/parse.y.

   657    657                                         {A.pOffset = X.pExpr; A.pLimit = Y.pExpr;}
   658    658   
   659    659   /////////////////////////// The DELETE statement /////////////////////////////
   660    660   //
   661    661   %ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
   662    662   cmd ::= with(C) DELETE FROM fullname(X) indexed_opt(I) where_opt(W) 
   663    663           orderby_opt(O) limit_opt(L). {
   664         -  sqlite3WithPush(pParse,C);
          664  +  sqlite3WithPush(pParse, C, 1);
   665    665     sqlite3SrcListIndexedBy(pParse, X, &I);
   666    666     W = sqlite3LimitWhere(pParse, X, W, O, L.pLimit, L.pOffset, "DELETE");
   667    667     sqlite3DeleteFrom(pParse,X,W);
   668    668   }
   669    669   %endif
   670    670   %ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
   671    671   cmd ::= with(C) DELETE FROM fullname(X) indexed_opt(I) where_opt(W). {
   672         -  sqlite3WithPush(pParse,C);
          672  +  sqlite3WithPush(pParse, C, 1);
   673    673     sqlite3SrcListIndexedBy(pParse, X, &I);
   674    674     sqlite3DeleteFrom(pParse,X,W);
   675    675   }
   676    676   %endif
   677    677   
   678    678   %type where_opt {Expr*}
   679    679   %destructor where_opt {sqlite3ExprDelete(pParse->db, $$);}
................................................................................
   682    682   where_opt(A) ::= WHERE expr(X).       {A = X.pExpr;}
   683    683   
   684    684   ////////////////////////// The UPDATE command ////////////////////////////////
   685    685   //
   686    686   %ifdef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
   687    687   cmd ::= with(C) UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y)
   688    688           where_opt(W) orderby_opt(O) limit_opt(L).  {
   689         -  sqlite3WithPush(pParse, C);
          689  +  sqlite3WithPush(pParse, C, 1);
   690    690     sqlite3SrcListIndexedBy(pParse, X, &I);
   691    691     sqlite3ExprListCheckLength(pParse,Y,"set list"); 
   692    692     W = sqlite3LimitWhere(pParse, X, W, O, L.pLimit, L.pOffset, "UPDATE");
   693    693     sqlite3Update(pParse,X,Y,W,R);
   694    694   }
   695    695   %endif
   696    696   %ifndef SQLITE_ENABLE_UPDATE_DELETE_LIMIT
   697    697   cmd ::= with(C) UPDATE orconf(R) fullname(X) indexed_opt(I) SET setlist(Y)
   698    698           where_opt(W).  {
   699         -  sqlite3WithPush(pParse, C);
          699  +  sqlite3WithPush(pParse, C, 1);
   700    700     sqlite3SrcListIndexedBy(pParse, X, &I);
   701    701     sqlite3ExprListCheckLength(pParse,Y,"set list"); 
   702    702     sqlite3Update(pParse,X,Y,W,R);
   703    703   }
   704    704   %endif
   705    705   
   706    706   %type setlist {ExprList*}
................................................................................
   714    714     A = sqlite3ExprListAppend(pParse, 0, Y.pExpr);
   715    715     sqlite3ExprListSetName(pParse, A, &X, 1);
   716    716   }
   717    717   
   718    718   ////////////////////////// The INSERT command /////////////////////////////////
   719    719   //
   720    720   cmd ::= with(W) insert_cmd(R) INTO fullname(X) inscollist_opt(F) select(S). {
   721         -  sqlite3WithPush(pParse, W);
          721  +  sqlite3WithPush(pParse, W, 1);
   722    722     sqlite3Insert(pParse, X, S, F, R);
   723    723   }
   724    724   cmd ::= with(W) insert_cmd(R) INTO fullname(X) inscollist_opt(F) DEFAULT VALUES.
   725    725   {
   726         -  sqlite3WithPush(pParse, W);
          726  +  sqlite3WithPush(pParse, W, 1);
   727    727     sqlite3Insert(pParse, X, 0, F, R);
   728    728   }
   729    729   
   730    730   %type insert_cmd {u8}
   731    731   insert_cmd(A) ::= INSERT orconf(R).   {A = R;}
   732    732   insert_cmd(A) ::= REPLACE.            {A = OE_Replace;}
   733    733   

Changes to src/select.c.

  3523   3523     }
  3524   3524     return 0;
  3525   3525   }
  3526   3526   
  3527   3527   /* The code generator maintains a stack of active WITH clauses
  3528   3528   ** with the inner-most WITH clause being at the top of the stack.
  3529   3529   **
  3530         -** These routines push and pull WITH clauses on the stack.
         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.
  3531   3536   */
  3532         -void sqlite3WithPush(Parse *pParse, With *pWith){
         3537  +void sqlite3WithPush(Parse *pParse, With *pWith, u8 bFree){
         3538  +  assert( bFree==0 || pParse->pWith==0 );
  3533   3539     if( pWith ){
  3534   3540       pWith->pOuter = pParse->pWith;
  3535   3541       pParse->pWith = pWith;
         3542  +    pParse->bFreeWith = bFree;
  3536   3543     }
  3537   3544   }
  3538   3545   
  3539   3546   /*
  3540   3547   ** This function checks if argument pFrom refers to a CTE declared by 
  3541   3548   ** a WITH clause on the stack currently maintained by the parser. And,
  3542   3549   ** if currently processing a CTE expression, if it is a recursive
................................................................................
  3645   3652       pCte->zErr = 0;
  3646   3653     }
  3647   3654   
  3648   3655     return SQLITE_OK;
  3649   3656   }
  3650   3657   #endif
  3651   3658   
         3659  +#ifndef SQLITE_OMIT_CTE
         3660  +static void selectPopWith(Walker *pWalker, Select *p){
         3661  +  Parse *pParse = pWalker->pParse;
         3662  +  if( p->pWith ){
         3663  +    assert( pParse->pWith==p->pWith );
         3664  +    pParse->pWith = p->pWith->pOuter;
         3665  +  }
         3666  +  return WRC_Continue;
         3667  +}
         3668  +#else
         3669  +#define selectPopWith 0
         3670  +#endif
         3671  +
  3652   3672   /*
  3653   3673   ** This routine is a Walker callback for "expanding" a SELECT statement.
  3654   3674   ** "Expanding" means to do the following:
  3655   3675   **
  3656   3676   **    (1)  Make sure VDBE cursor numbers have been assigned to every
  3657   3677   **         element of the FROM clause.
  3658   3678   **
................................................................................
  3688   3708       return WRC_Abort;
  3689   3709     }
  3690   3710     if( NEVER(p->pSrc==0) || (selFlags & SF_Expanded)!=0 ){
  3691   3711       return WRC_Prune;
  3692   3712     }
  3693   3713     pTabList = p->pSrc;
  3694   3714     pEList = p->pEList;
         3715  +  sqlite3WithPush(pParse, p->pWith, 0);
  3695   3716   
  3696   3717     /* Make sure cursor numbers have been assigned to all entries in
  3697   3718     ** the FROM clause of the SELECT statement.
  3698   3719     */
  3699   3720     sqlite3SrcListAssignCursors(pParse, pTabList);
  3700   3721   
  3701   3722     /* Look up every table named in the FROM clause of the select.  If
................................................................................
  3706   3727       Table *pTab;
  3707   3728       assert( pFrom->isRecursive==0 || pFrom->pTab );
  3708   3729       if( pFrom->isRecursive ) continue;
  3709   3730       if( pFrom->pTab!=0 ){
  3710   3731         /* This statement has already been prepared.  There is no need
  3711   3732         ** to go further. */
  3712   3733         assert( i==0 );
         3734  +#ifndef SQLITE_OMIT_CTE
         3735  +      selectPopWith(pWalker, p);
         3736  +#endif
  3713   3737         return WRC_Prune;
  3714   3738       }
  3715   3739   #ifndef SQLITE_OMIT_CTE
  3716   3740       if( withExpand(pWalker, pFrom) ) return WRC_Abort;
  3717   3741       if( pFrom->pTab ) {} else
  3718   3742   #endif
  3719   3743       if( pFrom->zName==0 ){
................................................................................
  3937   3961     if( p->pEList && p->pEList->nExpr>db->aLimit[SQLITE_LIMIT_COLUMN] ){
  3938   3962       sqlite3ErrorMsg(pParse, "too many columns in result set");
  3939   3963     }
  3940   3964   #endif
  3941   3965     return WRC_Continue;
  3942   3966   }
  3943   3967   
  3944         -/*
  3945         -** Function (or macro) selectExpanderWith is used as the SELECT callback
  3946         -** by sqlite3SelectExpand(). In builds that do not support CTEs, this
  3947         -** is equivalent to the selectExpander() function. In CTE-enabled builds,
  3948         -** any WITH clause associated with the SELECT statement needs to be
  3949         -** pushed onto the stack before calling selectExpander(), and popped
  3950         -** off again afterwards. 
  3951         -*/
  3952         -#ifndef SQLITE_OMIT_CTE
  3953         -static int selectExpanderWith(Walker *pWalker, Select *p){
  3954         -  Parse *pParse = pWalker->pParse;
  3955         -  int res;
  3956         -  sqlite3WithPush(pParse, p->pWith);
  3957         -  res = selectExpander(pWalker, p);
  3958         -  if( p->pWith ){
  3959         -    assert( pParse->pWith==p->pWith );
  3960         -    pParse->pWith = p->pWith->pOuter;
  3961         -  }
  3962         -  return res;
  3963         -}
  3964         -#else
  3965         -#define selectExpanderWith selectExpander
  3966         -#endif
  3967         -
  3968   3968   /*
  3969   3969   ** No-op routine for the parse-tree walker.
  3970   3970   **
  3971   3971   ** When this routine is the Walker.xExprCallback then expression trees
  3972   3972   ** are walked without any actions being taken at each node.  Presumably,
  3973   3973   ** when this routine is used for Walker.xExprCallback then 
  3974   3974   ** Walker.xSelectCallback is set to do something useful for every 
................................................................................
  3997   3997     memset(&w, 0, sizeof(w));
  3998   3998     w.xExprCallback = exprWalkNoop;
  3999   3999     w.pParse = pParse;
  4000   4000     if( pParse->hasCompound ){
  4001   4001       w.xSelectCallback = convertCompoundSelectToSubquery;
  4002   4002       sqlite3WalkSelect(&w, pSelect);
  4003   4003     }
  4004         -  w.xSelectCallback = selectExpanderWith;
         4004  +  w.xSelectCallback = selectExpander;
         4005  +  w.xSelectCallback2 = selectPopWith;
  4005   4006     sqlite3WalkSelect(&w, pSelect);
  4006   4007   }
  4007   4008   
  4008   4009   
  4009   4010   #ifndef SQLITE_OMIT_SUBQUERY
  4010   4011   /*
  4011   4012   ** This is a Walker.xSelectCallback callback for the sqlite3SelectTypeInfo()
................................................................................
  4016   4017   ** of that subquery.
  4017   4018   **
  4018   4019   ** The Table structure that represents the result set was constructed
  4019   4020   ** by selectExpander() but the type and collation information was omitted
  4020   4021   ** at that point because identifiers had not yet been resolved.  This
  4021   4022   ** routine is called after identifier resolution.
  4022   4023   */
  4023         -static int selectAddSubqueryTypeInfo(Walker *pWalker, Select *p){
         4024  +static void selectAddSubqueryTypeInfo(Walker *pWalker, Select *p){
  4024   4025     Parse *pParse;
  4025   4026     int i;
  4026   4027     SrcList *pTabList;
  4027   4028     struct SrcList_item *pFrom;
  4028   4029   
  4029   4030     assert( p->selFlags & SF_Resolved );
  4030   4031     if( (p->selFlags & SF_HasTypeInfo)==0 ){
................................................................................
  4039   4040           if( pSel ){
  4040   4041             while( pSel->pPrior ) pSel = pSel->pPrior;
  4041   4042             selectAddColumnTypeAndCollation(pParse, pTab, pSel);
  4042   4043           }
  4043   4044         }
  4044   4045       }
  4045   4046     }
  4046         -  return WRC_Continue;
  4047   4047   }
  4048   4048   #endif
  4049   4049   
  4050   4050   
  4051   4051   /*
  4052   4052   ** This routine adds datatype and collating sequence information to
  4053   4053   ** the Table structures of all FROM-clause subqueries in a
................................................................................
  4055   4055   **
  4056   4056   ** Use this routine after name resolution.
  4057   4057   */
  4058   4058   static void sqlite3SelectAddTypeInfo(Parse *pParse, Select *pSelect){
  4059   4059   #ifndef SQLITE_OMIT_SUBQUERY
  4060   4060     Walker w;
  4061   4061     memset(&w, 0, sizeof(w));
  4062         -  w.xSelectCallback = selectAddSubqueryTypeInfo;
         4062  +  w.xSelectCallback2 = selectAddSubqueryTypeInfo;
  4063   4063     w.xExprCallback = exprWalkNoop;
  4064   4064     w.pParse = pParse;
  4065         -  w.bSelectDepthFirst = 1;
  4066   4065     sqlite3WalkSelect(&w, pSelect);
  4067   4066   #endif
  4068   4067   }
  4069   4068   
  4070   4069   
  4071   4070   /*
  4072   4071   ** This routine sets up a SELECT statement for processing.  The

Changes to src/sqliteInt.h.

  2367   2367   #ifndef SQLITE_OMIT_VIRTUALTABLE
  2368   2368     Token sArg;               /* Complete text of a module argument */
  2369   2369     Table **apVtabLock;       /* Pointer to virtual tables needing locking */
  2370   2370   #endif
  2371   2371     Table *pZombieTab;        /* List of Table objects to delete after code gen */
  2372   2372     TriggerPrg *pTriggerPrg;  /* Linked list of coded triggers */
  2373   2373     With *pWith;              /* Current WITH clause, or NULL */
         2374  +  u8 bFreeWith;             /* True if pWith should be freed with parser */
  2374   2375   };
  2375   2376   
  2376   2377   /*
  2377   2378   ** Return true if currently inside an sqlite3_declare_vtab() call.
  2378   2379   */
  2379   2380   #ifdef SQLITE_OMIT_VIRTUALTABLE
  2380   2381     #define IN_DECLARE_VTAB 0
................................................................................
  2608   2609   
  2609   2610   /*
  2610   2611   ** Context pointer passed down through the tree-walk.
  2611   2612   */
  2612   2613   struct Walker {
  2613   2614     int (*xExprCallback)(Walker*, Expr*);     /* Callback for expressions */
  2614   2615     int (*xSelectCallback)(Walker*,Select*);  /* Callback for SELECTs */
         2616  +  void (*xSelectCallback2)(Walker*,Select*);/* Second callback for SELECTs */
  2615   2617     Parse *pParse;                            /* Parser context.  */
  2616   2618     int walkerDepth;                          /* Number of subqueries */
  2617         -  u8 bSelectDepthFirst;                     /* Do subqueries first */
  2618   2619     union {                                   /* Extra data for callback */
  2619   2620       NameContext *pNC;                          /* Naming context */
  2620   2621       int i;                                     /* Integer value */
  2621   2622       SrcList *pSrcList;                         /* FROM clause */
  2622   2623       struct SrcCount *pSrcCount;                /* Counting column references */
  2623   2624     } u;
  2624   2625   };
................................................................................
  3350   3351   #ifndef SQLITE_OMIT_WAL
  3351   3352     int sqlite3Checkpoint(sqlite3*, int, int, int*, int*);
  3352   3353     int sqlite3WalDefaultHook(void*,sqlite3*,const char*,int);
  3353   3354   #endif
  3354   3355   #ifndef SQLITE_OMIT_CTE
  3355   3356     With *sqlite3WithAdd(Parse*,With*,Token*,ExprList*,Select*);
  3356   3357     void sqlite3WithDelete(sqlite3*,With*);
  3357         -  void sqlite3WithPush(Parse*, With*);
         3358  +  void sqlite3WithPush(Parse*, With*, u8);
  3358   3359   #else
  3359         -#define sqlite3WithPush(x,y)
         3360  +#define sqlite3WithPush(x,y,z)
  3360   3361   #define sqlite3WithDelete(x,y)
  3361   3362   #endif
  3362   3363   
  3363   3364   /* Declarations for functions in fkey.c. All of these are replaced by
  3364   3365   ** no-op macros if OMIT_FOREIGN_KEY is defined. In this case no foreign
  3365   3366   ** key functionality is available. If OMIT_TRIGGER is defined but
  3366   3367   ** OMIT_FOREIGN_KEY is not, only some of the functions are no-oped. In

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         -  assert( pParse->pWith==0 || pParse->pWith->pOuter==0 );
   498         -  sqlite3WithDelete(db, pParse->pWith);
          497  +  if( pParse->bFreeWith ) sqlite3WithDelete(db, pParse->pWith);
   499    498     sqlite3DeleteTrigger(db, pParse->pNewTrigger);
   500    499     for(i=pParse->nzVar-1; i>=0; i--) sqlite3DbFree(db, pParse->azVar[i]);
   501    500     sqlite3DbFree(db, pParse->azVar);
   502    501     while( pParse->pAinc ){
   503    502       AutoincInfo *p = pParse->pAinc;
   504    503       pParse->pAinc = p->pNext;
   505    504       sqlite3DbFree(db, p);

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   }

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  +