Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -3970,11 +3970,11 @@ ** ** 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 */ + With *pWith, /* Current innermost 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 ){ @@ -4001,15 +4001,16 @@ ** 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, u8 bFree){ - assert( bFree==0 || pParse->pWith==0 ); + assert( bFree==0 || (pParse->pWith==0 && pParse->pWithToFree==0) ); if( pWith ){ + assert( pParse->pWith!=pWith ); pWith->pOuter = pParse->pWith; pParse->pWith = pWith; - pParse->bFreeWith = bFree; + if( bFree ) pParse->pWithToFree = pWith; } } /* ** This function checks if argument pFrom refers to a CTE declared by @@ -4098,10 +4099,11 @@ pCte->zCteErr = "circular reference: %s"; pSavedWith = pParse->pWith; pParse->pWith = pWith; sqlite3WalkSelect(pWalker, bMayRecursive ? pSel->pPrior : pSel); + pParse->pWith = pWith; for(pLeft=pSel; pLeft->pPrior; pLeft=pLeft->pPrior); pEList = pLeft->pEList; if( pCte->pCols ){ if( pEList && pEList->nExpr!=pCte->pCols->nExpr ){ Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -2752,11 +2752,10 @@ ************************************************************************/ int nVar; /* Number of '?' variables seen in the SQL so far */ int nzVar; /* Number of available slots in azVar[] */ u8 iPkSortOrder; /* ASC or DESC for INTEGER PRIMARY KEY */ - u8 bFreeWith; /* True if pWith should be freed with parser */ u8 explain; /* True if the EXPLAIN flag is found on the query */ #ifndef SQLITE_OMIT_VIRTUALTABLE u8 declareVtab; /* True if inside sqlite3_declare_vtab() */ int nVtabLock; /* Number of virtual tables to lock */ #endif @@ -2779,10 +2778,11 @@ 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 */ + With *pWithToFree; /* Free this WITH object at the end of the parse */ }; /* ** Return true if currently inside an sqlite3_declare_vtab() call. */ @@ -3269,10 +3269,11 @@ #if defined(SQLITE_DEBUG) void sqlite3TreeViewExpr(TreeView*, const Expr*, u8); void sqlite3TreeViewExprList(TreeView*, const ExprList*, u8, const char*); void sqlite3TreeViewSelect(TreeView*, const Select*, u8); + void sqlite3TreeViewWith(TreeView*, const With*, u8); #endif void sqlite3SetString(char **, sqlite3*, const char*); void sqlite3ErrorMsg(Parse*, const char*, ...); Index: src/tokenize.c ================================================================== --- src/tokenize.c +++ src/tokenize.c @@ -508,11 +508,11 @@ ** will take responsibility for freeing the Table structure. */ sqlite3DeleteTable(db, pParse->pNewTable); } - if( pParse->bFreeWith ) sqlite3WithDelete(db, pParse->pWith); + sqlite3WithDelete(db, pParse->pWithToFree); 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; Index: src/treeview.c ================================================================== --- src/treeview.c +++ src/treeview.c @@ -77,18 +77,62 @@ static void sqlite3TreeViewItem(TreeView *p, const char *zLabel,u8 moreFollows){ p = sqlite3TreeViewPush(p, moreFollows); sqlite3TreeViewLine(p, "%s", zLabel); } +/* +** Generate a human-readable description of a WITH clause. +*/ +void sqlite3TreeViewWith(TreeView *pView, const With *pWith, u8 moreToFollow){ + int i; + if( pWith==0 ) return; + if( pWith->nCte==0 ) return; + if( pWith->pOuter ){ + sqlite3TreeViewLine(pView, "WITH (0x%p, pOuter=0x%p)",pWith,pWith->pOuter); + }else{ + sqlite3TreeViewLine(pView, "WITH (0x%p)", pWith); + } + if( pWith->nCte>0 ){ + pView = sqlite3TreeViewPush(pView, 1); + for(i=0; inCte; i++){ + StrAccum x; + char zLine[1000]; + const struct Cte *pCte = &pWith->a[i]; + sqlite3StrAccumInit(&x, 0, zLine, sizeof(zLine), 0); + sqlite3XPrintf(&x, 0, "%s", pCte->zName); + if( pCte->pCols && pCte->pCols->nExpr>0 ){ + char cSep = '('; + int j; + for(j=0; jpCols->nExpr; j++){ + sqlite3XPrintf(&x, 0, "%c%s", cSep, pCte->pCols->a[j].zName); + cSep = ','; + } + sqlite3XPrintf(&x, 0, ")"); + } + sqlite3XPrintf(&x, 0, " AS"); + sqlite3StrAccumFinish(&x); + sqlite3TreeViewItem(pView, zLine, inCte-1); + sqlite3TreeViewSelect(pView, pCte->pSelect, 0); + sqlite3TreeViewPop(pView); + } + sqlite3TreeViewPop(pView); + } +} + /* ** Generate a human-readable description of a the Select object. */ void sqlite3TreeViewSelect(TreeView *pView, const Select *p, u8 moreToFollow){ int n = 0; int cnt = 0; pView = sqlite3TreeViewPush(pView, moreToFollow); + if( p->pWith ){ + sqlite3TreeViewWith(pView, p->pWith, 1); + cnt = 1; + sqlite3TreeViewPush(pView, 1); + } do{ sqlite3TreeViewLine(pView, "SELECT%s%s (0x%p) selFlags=0x%x", ((p->selFlags & SF_Distinct) ? " DISTINCT" : ""), ((p->selFlags & SF_Aggregate) ? " agg_flag" : ""), p, p->selFlags ); Index: test/with1.test ================================================================== --- test/with1.test +++ test/with1.test @@ -862,7 +862,117 @@ do_catchsql_test 16.1 { WITH RECURSIVE i(x) AS (VALUES(1) UNION SELECT count(*) FROM i) SELECT * FROM i; } {1 {recursive aggregate queries not supported}} + +#------------------------------------------------------------------------- +do_execsql_test 17.1 { + WITH x(a) AS ( + WITH y(b) AS (SELECT 10) + SELECT 9 UNION ALL SELECT * FROM y + ) + SELECT * FROM x +} {9 10} + +do_execsql_test 17.2 { + WITH x AS ( + WITH y(b) AS (SELECT 10) + SELECT * FROM y UNION ALL SELECT * FROM y + ) + SELECT * FROM x +} {10 10} + +do_test 17.2 { + db eval { + WITH x AS ( + WITH y(b) AS (SELECT 10) + SELECT * FROM y UNION ALL SELECT * FROM y + ) + SELECT * FROM x + } A { + # no op + } + set A(*) +} {b} + +do_catchsql_test 17.3 { + WITH i AS ( + WITH j AS (SELECT 5) + SELECT 5 FROM i UNION SELECT 8 FROM i + ) + SELECT * FROM i; +} {1 {circular reference: i}} + +do_catchsql_test 17.4 { + WITH i AS ( + WITH j AS (SELECT 5) + SELECT 5 FROM t1 UNION SELECT 8 FROM t11 + ) + SELECT * FROM i; +} {1 {no such table: t11}} + +do_execsql_test 17.5 { + WITH + x1 AS (SELECT 10), + x2 AS (SELECT * FROM x1), + x3 AS ( + WITH x1 AS (SELECT 11) + SELECT * FROM x2 UNION ALL SELECT * FROM x2 + ) + SELECT * FROM x3; +} {10 10} + +do_execsql_test 17.6 { + WITH + x1 AS (SELECT 10), + x2 AS (SELECT * FROM x1), + x3 AS ( + WITH x1 AS (SELECT 11) + SELECT * FROM x2 UNION ALL SELECT * FROM x1 + ) + SELECT * FROM x3; +} {10 11} + +do_execsql_test 17.7 { + WITH + x1 AS (SELECT 10), + x2 AS (SELECT * FROM x1), + x3 AS ( + WITH + x1 AS ( SELECT 11 ), + x4 AS ( SELECT * FROM x2 ) + SELECT * FROM x4 UNION ALL SELECT * FROM x1 + ) + SELECT * FROM x3; +} {10 11} + +do_execsql_test 17.8 { + WITH + x1 AS (SELECT 10), + x2 AS (SELECT * FROM x1), + x3 AS ( + WITH + x1 AS ( SELECT 11 ), + x4 AS ( SELECT * FROM x2 ) + SELECT * FROM x4 UNION ALL SELECT * FROM x1 + ) + SELECT * FROM x3; +} {10 11} + +do_execsql_test 17.9 { + WITH + x1 AS (SELECT 10), + x2 AS (SELECT 11), + x3 AS ( + SELECT * FROM x1 UNION ALL SELECT * FROM x2 + ), + x4 AS ( + WITH + x1 AS (SELECT 12), + x2 AS (SELECT 13) + SELECT * FROM x3 + ) + SELECT * FROM x4; +} {10 11} finish_test ADDED test/with3.test Index: test/with3.test ================================================================== --- /dev/null +++ test/with3.test @@ -0,0 +1,64 @@ +# 2015-11-07 +# +# 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 with3 + +ifcapable {!cte} { + finish_test + return +} + +# Test problems found by Kostya Serebryany using +# LibFuzzer. (http://llvm.org/docs/LibFuzzer.html) +# +do_catchsql_test 1.0 { + WITH i(x) AS ( + WITH j AS (SELECT 10) + SELECT 5 FROM t0 UNION SELECT 8 FROM m + ) + SELECT * FROM i; +} {1 {no such table: m}} + +# Additional test cases that came out of the work to +# fix for Kostya's problem. +# +do_execsql_test 2.0 { + WITH + x1 AS (SELECT 10), + x2 AS (SELECT 11), + x3 AS ( + SELECT * FROM x1 UNION ALL SELECT * FROM x2 + ), + x4 AS ( + WITH + x1 AS (SELECT 12), + x2 AS (SELECT 13) + SELECT * FROM x3 + ) + SELECT * FROM x4; + +} {10 11} + +do_execsql_test 2.1 { + CREATE TABLE t1(x); + WITH + x1(a) AS (values(100)) + INSERT INTO t1(x) + SELECT * FROM (WITH x2(y) AS (SELECT * FROM x1) SELECT y+a FROM x1, x2); + SELECT * FROM t1; +} {200} + +finish_test