Index: addopcodes.awk ================================================================== --- addopcodes.awk +++ addopcodes.awk @@ -29,6 +29,7 @@ printf "#define TK_%-29s %4d\n", "AGG_FUNCTION", ++max printf "#define TK_%-29s %4d\n", "AGG_COLUMN", ++max printf "#define TK_%-29s %4d\n", "UMINUS", ++max printf "#define TK_%-29s %4d\n", "UPLUS", ++max printf "#define TK_%-29s %4d\n", "REGISTER", ++max + printf "#define TK_%-29s %4d\n", "LEVEL", ++max } Index: src/expr.c ================================================================== --- src/expr.c +++ src/expr.c @@ -2456,10 +2456,16 @@ inReg = sqlite3ExprCodeGetColumn(pParse, pExpr->pTab, pExpr->iColumn, iTab, target, pExpr->op2); break; } +#ifndef SQLITE_OMIT_CTE + case TK_LEVEL: { + inReg = pParse->regLevel; + break; + } +#endif case TK_INTEGER: { codeInteger(pParse, pExpr, 0, target); break; } #ifndef SQLITE_OMIT_FLOATING_POINT @@ -3079,10 +3085,11 @@ int sqlite3ExprCodeTemp(Parse *pParse, Expr *pExpr, int *pReg){ int r2; pExpr = sqlite3ExprSkipCollate(pExpr); if( ConstFactorOk(pParse) && pExpr->op!=TK_REGISTER + && pExpr->op!=TK_LEVEL && sqlite3ExprIsConstantNotJoin(pExpr) ){ ExprList *p = pParse->pConstExpr; int i; *pReg = 0; Index: src/resolve.c ================================================================== --- src/resolve.c +++ src/resolve.c @@ -224,11 +224,11 @@ sqlite3 *db = pParse->db; /* The database connection */ struct SrcList_item *pItem; /* Use for looping over pSrcList items */ struct SrcList_item *pMatch = 0; /* The matching pSrcList item */ NameContext *pTopNC = pNC; /* First namecontext in the list */ Schema *pSchema = 0; /* Schema of the expression */ - int isTrigger = 0; /* True if resolved to a trigger column */ + u8 newOp = TK_COLUMN; /* pExpr->op after resolving name */ Table *pTab = 0; /* Table hold the row */ Column *pCol; /* A column of pTab */ assert( pNC ); /* the name context cannot be NULL. */ assert( zCol ); /* The Z in X.Y.Z cannot be NULL */ @@ -264,10 +264,30 @@ /* Start at the inner-most context and move outward until a match is found */ while( pNC && cnt==0 ){ ExprList *pEList; SrcList *pSrcList = pNC->pSrcList; + +#ifndef SQLITE_OMIT_CTE + /* The identifier "LEVEL", without a table or database qualifier and + ** within a recursive common table expression, resolves to the special + ** LEVEL pseudo-column. To access table names called "level", add a + ** table qualifier. + */ + if( (pNC->ncFlags&NC_Recursive)!=0 + && zTab==0 + && sqlite3_stricmp(zCol,"level")==0 + ){ + assert( cnt==0 ); + cnt = 1; + newOp = TK_LEVEL; + pExpr->iColumn = -1; + pExpr->affinity = SQLITE_AFF_INTEGER; + pNC->ncFlags |= NC_UsesLevel; + break; + } +#endif if( pSrcList ){ for(i=0, pItem=pSrcList->a; inSrc; i++, pItem++){ pTab = pItem->pTab; assert( pTab!=0 && pTab->zName!=0 ); @@ -369,11 +389,11 @@ testcase( iCol==32 ); pParse->newmask |= (iCol>=32 ? 0xffffffff : (((u32)1)<iColumn = (i16)iCol; pExpr->pTab = pTab; - isTrigger = 1; + newOp = TK_TRIGGER; } } } #endif /* !defined(SQLITE_OMIT_TRIGGER) */ @@ -493,15 +513,15 @@ */ sqlite3ExprDelete(db, pExpr->pLeft); pExpr->pLeft = 0; sqlite3ExprDelete(db, pExpr->pRight); pExpr->pRight = 0; - pExpr->op = (isTrigger ? TK_TRIGGER : TK_COLUMN); + pExpr->op = newOp; lookupname_end: if( cnt==1 ){ assert( pNC!=0 ); - if( pExpr->op!=TK_AS ){ + if( pExpr->op!=TK_AS && pExpr->op!=TK_LEVEL ){ sqlite3AuthRead(pParse, pExpr, pSchema, pNC->pSrcList); } /* Increment the nRef value on all name contexts from TopNC up to ** the point where the name matched. */ for(;;){ @@ -1194,10 +1214,11 @@ /* Set up the local name-context to pass to sqlite3ResolveExprNames() to ** resolve the result-set expression list. */ sNC.ncFlags = NC_AllowAgg; + if( p->selFlags & SF_Recursive ) sNC.ncFlags |= NC_Recursive; sNC.pSrcList = p->pSrc; sNC.pNext = pOuterNC; /* Resolve names in the result set. */ pEList = p->pEList; @@ -1272,10 +1293,14 @@ "the GROUP BY clause"); return WRC_Abort; } } } + if( sNC.ncFlags & NC_UsesLevel ){ + p->selFlags |= SF_UsesLevel; + } + sNC.ncFlags &= ~(NC_Recursive|NC_UsesLevel); /* Advance to the next term of the compound */ p = p->pPrior; nCompound++; Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -1790,21 +1790,23 @@ goto multi_select_end; } #ifndef SQLITE_OMIT_CTE if( p->selFlags & SF_Recursive ){ - SrcList *pSrc = p->pSrc; - int nCol = p->pEList->nExpr; + SrcList *pSrc = p->pSrc; /* The FROM clause of the right-most SELECT */ + int nCol = p->pEList->nExpr; /* Number of columns in the result set */ int addrNext; int addrSwap; int iCont, iBreak; int tmp1; /* Intermediate table */ int tmp2; /* Next intermediate table */ int tmp3 = 0; /* To ensure unique results if UNION */ int eDest = SRT_Table; SelectDest tmp2dest; int i; + int regLevel = 0; /* Register for LEVEL value */ + int savedRegLevel; /* Saved value of pParse->regLevel */ /* Check that there is no ORDER BY or LIMIT clause. Neither of these ** are supported on recursive queries. */ assert( p->pOffset==0 || p->pLimit ); if( p->pOrderBy || p->pLimit ){ @@ -1842,10 +1844,18 @@ } /* Store the results of the initial SELECT in tmp2. */ rc = sqlite3Select(pParse, pPrior, &tmp2dest); if( rc ) goto multi_select_end; + + /* Allocate and initialize a register to hold the LEVEL pseudo-column */ + savedRegLevel = pParse->regLevel; + if( p->selFlags & SF_UsesLevel ){ + regLevel = pParse->regLevel = ++pParse->nMem; + sqlite3VdbeAddOp2(v, OP_Integer, 0, regLevel); + VdbeComment((v, "level=0")); + } /* Clear tmp1. Then switch the contents of tmp1 and tmp2. Then return ** the contents of tmp1 to the caller. Or, if tmp1 is empty at this ** point, the recursive query has finished - jump to address iBreak. */ addrSwap = sqlite3VdbeAddOp2(v, OP_SwapCursors, tmp1, tmp2); @@ -1853,10 +1863,14 @@ addrNext = sqlite3VdbeCurrentAddr(v); selectInnerLoop(pParse, p, p->pEList, tmp1, p->pEList->nExpr, 0, 0, &dest, iCont, iBreak); sqlite3VdbeResolveLabel(v, iCont); sqlite3VdbeAddOp2(v, OP_Next, tmp1, addrNext); + if( regLevel ){ + sqlite3VdbeAddOp2(v, OP_AddImm, regLevel, 1); + VdbeComment((v, "level++")); + } /* Execute the recursive SELECT. Store the results in tmp2. While this ** SELECT is running, the contents of tmp1 are read by recursive ** references to the current CTE. */ p->pPrior = 0; @@ -1865,10 +1879,11 @@ p->pPrior = pPrior; if( rc ) goto multi_select_end; sqlite3VdbeAddOp2(v, OP_Goto, 0, addrSwap); sqlite3VdbeResolveLabel(v, iBreak); + pParse->regLevel = savedRegLevel; }else #endif /* Compound SELECTs that have an ORDER BY clause are handled separately. */ Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -2105,10 +2105,12 @@ #define NC_AllowAgg 0x01 /* Aggregate functions are allowed here */ #define NC_HasAgg 0x02 /* One or more aggregate functions seen */ #define NC_IsCheck 0x04 /* True if resolving names in a CHECK constraint */ #define NC_InAggFunc 0x08 /* True if analyzing arguments to an agg func */ #define NC_PartIdx 0x10 /* True if resolving a partial index WHERE */ +#define NC_Recursive 0x20 /* Resolving a recursive CTE definition */ +#define NC_UsesLevel 0x40 /* The LEVEL pseudo-column has been seen */ /* ** An instance of the following structure contains all information ** needed to generate code for a single SELECT statement. ** @@ -2162,10 +2164,11 @@ #define SF_Values 0x0080 /* Synthesized from VALUES clause */ #define SF_Materialize 0x0100 /* Force materialization of views */ #define SF_NestedFrom 0x0200 /* Part of a parenthesized FROM clause */ #define SF_MaybeConvert 0x0400 /* Need convertCompoundSelectToSubquery() */ #define SF_Recursive 0x0800 /* The recursive part of a recursive CTE */ +#define SF_UsesLevel 0x1000 /* Uses the LEVEL pseudo-column */ /* ** The results of a select can be distributed in several ways. The ** "SRT" prefix means "SELECT Result Type". @@ -2368,10 +2371,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 */ + int regLevel; /* Register holding the LEVEL variable */ u8 bFreeWith; /* True if pWith should be freed with parser */ }; /* ** Return true if currently inside an sqlite3_declare_vtab() call. ADDED test/with3.test Index: test/with3.test ================================================================== --- /dev/null +++ test/with3.test @@ -0,0 +1,106 @@ +# 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 and in particular the +# LEVEL pseudo-column +# + +set testdir [file dirname $argv0] +source $testdir/tester.tcl +set ::testprefix with3 + +ifcapable {!cte} { + finish_test + return +} + +do_execsql_test 1.0 { + WITH RECURSIVE + cnt(x) AS (VALUES(10) UNION SELECT x+1 FROM cnt WHERE level<10) + SELECT * FROM cnt; +} {10 11 12 13 14 15 16 17 18 19} +do_execsql_test 1.1 { + WITH RECURSIVE + cnt(x,y) AS (VALUES(10,0) UNION SELECT x+1,level FROM cnt WHERE level<6) + SELECT x, y, '|' FROM cnt; +} {10 0 | 11 1 | 12 2 | 13 3 | 14 4 | 15 5 |} +do_execsql_test 1.2 { + WITH RECURSIVE + cnt(x,level) AS ( + VALUES(10,99) + UNION + SELECT x+1, level FROM cnt WHERE level<6 + ) + SELECT x, level, '|' FROM cnt; +} {10 99 | 11 1 | 12 2 | 13 3 | 14 4 | 15 5 |} +do_execsql_test 1.3 { + WITH RECURSIVE + cnt(x,level) AS ( + VALUES(10,99) + UNION + SELECT x+1, cnt.level FROM cnt WHERE level<6 + ) + SELECT x, level, '|' FROM cnt; +} {10 99 | 11 99 | 12 99 | 13 99 | 14 99 | 15 99 |} +do_execsql_test 1.4 { + WITH RECURSIVE + cnt(x,level) AS ( + VALUES(10,0) + UNION + SELECT x+1, cnt.level+level FROM cnt WHERE level<6 + ) + SELECT x, level, '|' FROM cnt; +} {10 0 | 11 1 | 12 3 | 13 6 | 14 10 | 15 15 |} +do_execsql_test 1.5 { + CREATE TABLE t1(level); + WITH RECURSIVE + cnt(x) AS (VALUES(10) UNION SELECT x*10 FROM cnt WHERE level<4) + INSERT INTO t1 SELECT x FROM cnt; + SELECT * FROM t1; +} {10 100 1000 10000} +do_execsql_test 1.6 { + WITH RECURSIVE + cnt(x, level) AS ( + VALUES(1,1) + UNION + SELECT x+1, level*t1.level FROM cnt, t1 WHERE level<3 + ) + SELECT x, level FROM cnt ORDER BY x, level; +} {1 1 2 10 2 100 2 1000 2 10000 3 20 3 200 3 2000 3 20000} + +do_execsql_test 1.11 { + CREATE TEMP TABLE powersoftwo(a,b); + WITH RECURSIVE + tmp(a,b) AS (VALUES(0,1) UNION SELECT a+1, b*2 FROM tmp WHERE level<32) + INSERT INTO powersoftwo SELECT a, b FROM tmp; + WITH RECURSIVE + cnt(x,y) AS ( + VALUES(0,0) UNION + SELECT x+1, (x+1)*(SELECT b FROM powersoftwo WHERE a=level) FROM cnt + WHERE level<5 + ) + SELECT * FROM cnt; +} {0 0 1 2 2 8 3 24 4 64} + +do_catchsql_test 2.1 { + WITH RECURSIVE + cnt(x) AS (VALUES(1) UNION SELECT x+1 FROM cnt WHERE level<10) + SELECT x, level FROM cnt; +} {1 {no such column: level}} +do_catchsql_test 2.2 { + WITH RECURSIVE + cnt(x) AS (VALUES(level) UNION SELECT x+1 FROM cnt WHERE level<10) + SELECT x FROM cnt; +} {1 {no such column: level}} + + + +finish_test