Index: src/build.c ================================================================== --- src/build.c +++ src/build.c @@ -5202,28 +5202,74 @@ } return pKey; } #ifndef SQLITE_OMIT_CTE +/* +** Create a new CTE object +*/ +Cte *sqlite3CteNew( + Parse *pParse, /* Parsing context */ + Token *pName, /* Name of the common-table */ + ExprList *pArglist, /* Optional column name list for the table */ + Select *pQuery, /* Query used to initialize the table */ + int eMaterialized /* Force or prohibit materialization */ +){ + Cte *pNew; + sqlite3 *db = pParse->db; + + assert( eMaterialized==MAT_NotSpec || eMaterialized==MAT_Yes + || eMaterialized==MAT_No ); + pNew = sqlite3DbMallocZero(db, sizeof(*pNew)); + assert( pNew!=0 || db->mallocFailed ); + + if( db->mallocFailed ){ + sqlite3ExprListDelete(db, pArglist); + sqlite3SelectDelete(db, pQuery); + }else{ + pNew->pSelect = pQuery; + pNew->pCols = pArglist; + pNew->zName = sqlite3NameFromToken(pParse->db, pName); + pNew->zCteErr = 0; + pNew->eMaterialized = (u8)eMaterialized; + } + return pNew; +} + +/* +** Free the contents of the CTE object passed as the second argument. +*/ +void sqlite3CteDelete(sqlite3 *db, Cte *pCte){ + if( pCte ){ + sqlite3ExprListDelete(db, pCte->pCols); + sqlite3SelectDelete(db, pCte->pSelect); + sqlite3DbFree(db, pCte->zName); + sqlite3DbFree(db, pCte); + } +} + /* ** 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 */ + Cte *pCte /* The CTE to add to the WITH clause */ ){ sqlite3 *db = pParse->db; With *pNew; char *zName; + + if( pCte==0 ){ + return pWith; + } + /* 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); + zName = pCte->zName; if( zName && pWith ){ int i; for(i=0; inCte; i++){ if( sqlite3StrICmp(zName, pWith->a[i].zName)==0 ){ sqlite3ErrorMsg(pParse, "duplicate WITH table name: %s", zName); @@ -5235,25 +5281,19 @@ sqlite3_int64 nByte = sizeof(*pWith) + (sizeof(pWith->a[1]) * pWith->nCte); pNew = sqlite3DbRealloc(db, pWith, nByte); }else{ pNew = sqlite3DbMallocZero(db, sizeof(*pWith)); } - assert( (pNew!=0 && zName!=0) || db->mallocFailed ); if( db->mallocFailed ){ - sqlite3ExprListDelete(db, pArglist); - sqlite3SelectDelete(db, pQuery); - sqlite3DbFree(db, zName); + sqlite3CteDelete(db, pCte); pNew = pWith; }else{ - pNew->a[pNew->nCte].pSelect = pQuery; - pNew->a[pNew->nCte].pCols = pArglist; - pNew->a[pNew->nCte].zName = zName; - pNew->a[pNew->nCte].zCteErr = 0; + pNew->a[pNew->nCte] = *pCte; + sqlite3DbFree(db, pCte); pNew->nCte++; } - return pNew; } /* ** Free the contents of the With object passed as the second argument. Index: src/parse.y ================================================================== --- src/parse.y +++ src/parse.y @@ -247,10 +247,13 @@ CURRENT FOLLOWING PARTITION PRECEDING RANGE UNBOUNDED EXCLUDE GROUPS OTHERS TIES %endif SQLITE_OMIT_WINDOWFUNC %ifndef SQLITE_OMIT_GENERATED_COLUMNS GENERATED ALWAYS +%endif +%ifndef SQLITE_OMIT_CTE + MATERIALIZED %endif REINDEX RENAME CTIME_KW IF . %wildcard ANY. @@ -1656,21 +1659,30 @@ //////////////////////// COMMON TABLE EXPRESSIONS //////////////////////////// %type wqlist {With*} %destructor wqlist {sqlite3WithDelete(pParse->db, $$);} +%type wqitem {Cte*} +%destructor wqitem {sqlite3CteDelete(pParse->db, $$);} +%type wqas {int} with ::= . %ifndef SQLITE_OMIT_CTE with ::= WITH wqlist(W). { sqlite3WithPush(pParse, W, 1); } with ::= WITH RECURSIVE wqlist(W). { sqlite3WithPush(pParse, W, 1); } -wqlist(A) ::= nm(X) eidlist_opt(Y) AS LP select(Z) RP. { - A = sqlite3WithAdd(pParse, 0, &X, Y, Z); /*A-overwrites-X*/ +wqas(A) ::= AS. {A=MAT_NotSpec;} +wqas(A) ::= AS MATERIALIZED. {A=MAT_Yes;} +//wqas(A) ::= AS NOT MATERIALIZED. {A=MAT_No;} +wqitem(A) ::= nm(X) eidlist_opt(Y) wqas(F) LP select(Z) RP. { + A = sqlite3CteNew(pParse, &X, Y, Z, F); /*A-overwrites-X*/ +} +wqlist(A) ::= wqitem(X). { + A = sqlite3WithAdd(pParse, 0, X); /*A-overwrites-X*/ } -wqlist(A) ::= wqlist(A) COMMA nm(X) eidlist_opt(Y) AS LP select(Z) RP. { - A = sqlite3WithAdd(pParse, A, &X, Y, Z); +wqlist(A) ::= wqlist(A) COMMA wqitem(X). { + A = sqlite3WithAdd(pParse, A, X); } %endif SQLITE_OMIT_CTE //////////////////////// WINDOW FUNCTION EXPRESSIONS ///////////////////////// // These must be at the end of this file. Specifically, the rules that Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -4936,10 +4936,13 @@ pTab->nRowLogEst = 200; assert( 200==sqlite3LogEst(1048576) ); pTab->tabFlags |= TF_Ephemeral | TF_NoVisibleRowid; pFrom->pSelect = sqlite3SelectDup(db, pCte->pSelect, 0); if( db->mallocFailed ) return SQLITE_NOMEM_BKPT; assert( pFrom->pSelect ); + if( pCte->eMaterialized==MAT_Yes ){ + pFrom->pSelect->selFlags |= SF_OptBarrier; + } /* Check if this is a recursive CTE. */ pRecTerm = pSel = pFrom->pSelect; bMayRecursive = ( pSel->op==TK_ALL || pSel->op==TK_UNION ); while( bMayRecursive && pRecTerm->op==pSel->op ){ @@ -6068,10 +6071,13 @@ || (pTabList->a[1].fg.jointype&(JT_LEFT|JT_CROSS))!=0) ){ continue; } + /* Do not flatten across an optimization barrier */ + if( pSub->selFlags & SF_OptBarrier ) continue; + if( flattenSubquery(pParse, p, i, isAgg) ){ if( pParse->nErr ) goto select_end; /* This subquery can be absorbed into its parent. */ i = -1; } @@ -6192,10 +6198,11 @@ /* Make copies of constant WHERE-clause terms in the outer query down ** inside the subquery. This can help the subquery to run more efficiently. */ if( OptimizationEnabled(db, SQLITE_PushDown) + && (pSub->selFlags & SF_OptBarrier)==0 && pushDownWhereTerms(pParse, pSub, p->pWhere, pItem->iCursor, (pItem->fg.jointype & JT_OUTER)!=0) ){ #if SELECTTRACE_ENABLED if( sqlite3SelectTrace & 0x100 ){ @@ -6212,20 +6219,26 @@ zSavedAuthContext = pParse->zAuthContext; pParse->zAuthContext = pItem->zName; /* Generate code to implement the subquery ** - ** The subquery is implemented as a co-routine if the subquery is + ** The subquery is implemented as a co-routine if (1) the subquery is ** guaranteed to be the outer loop (so that it does not need to be - ** computed more than once) + ** computed more than once) and if (2) the subquery is not optimization + ** barrier. ** - ** TODO: Are there other reasons beside (1) to use a co-routine + ** TODO: Are there other reasons beside (1) and (2) to use a co-routine ** implementation? + ** + ** TODO: We might should allow a subquery that is an optimization barrier + ** to be implemented as a co-routine as long as we know that it is the + ** only use of the subquery. */ if( i==0 && (pTabList->nSrc==1 || (pTabList->a[1].fg.jointype&(JT_LEFT|JT_CROSS))!=0) /* (1) */ + && (pSub->selFlags & SF_OptBarrier)==0 /* (2) */ ){ /* Implement a co-routine that will return a single row of the result ** set on each invocation. */ int addrTop = sqlite3VdbeCurrentAddr(v)+1; Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -1134,10 +1134,11 @@ typedef struct AuthContext AuthContext; typedef struct AutoincInfo AutoincInfo; typedef struct Bitvec Bitvec; typedef struct CollSeq CollSeq; typedef struct Column Column; +typedef struct Cte Cte; typedef struct Db Db; typedef struct DbFixer DbFixer; typedef struct Schema Schema; typedef struct Expr Expr; typedef struct ExprList ExprList; @@ -3183,10 +3184,11 @@ #define SF_WinRewrite 0x0100000 /* Window function rewrite accomplished */ #define SF_View 0x0200000 /* SELECT statement is a view */ #define SF_NoopOrderBy 0x0400000 /* ORDER BY is ignored for this query */ #define SF_UpdateFrom 0x0800000 /* Statement is an UPDATE...FROM */ #define SF_PushDown 0x1000000 /* SELECT has be modified by push-down opt */ +#define SF_OptBarrier 0x2000000 /* SELECT is an optimization barrier */ /* ** The results of a SELECT can be distributed in several ways, as defined ** by one of the following macros. The "SRT" prefix means "SELECT Result ** Type". @@ -3865,23 +3867,32 @@ */ #define WRC_Continue 0 /* Continue down into children */ #define WRC_Prune 1 /* Omit children but continue walking siblings */ #define WRC_Abort 2 /* Abandon the tree walk */ +/* +** Allowed values for Cte.eMAterialized +*/ +#define MAT_NotSpec 0 /* Not specified */ +#define MAT_Yes 1 /* AS MATERIALIZED ... */ +#define MAT_No 2 /* AS NOT MATERIALIZED. Not currently used */ + /* ** An instance of this structure represents a set of one or more CTEs ** (common table expressions) created by a single WITH clause. */ +struct Cte { + char *zName; /* Name of this CTE */ + ExprList *pCols; /* List of explicit column names, or NULL */ + Select *pSelect; /* The definition of this CTE */ + const char *zCteErr; /* Error message for circular references */ + u8 eMaterialized; /* One of the MAT_* values */ +}; struct With { - int nCte; /* Number of CTEs in the WITH clause */ - With *pOuter; /* Containing WITH clause, or NULL */ - struct Cte { /* For each CTE in the WITH clause.... */ - char *zName; /* Name of this CTE */ - ExprList *pCols; /* List of explicit column names, or NULL */ - Select *pSelect; /* The definition of this CTE */ - const char *zCteErr; /* Error message for circular references */ - } a[1]; + int nCte; /* Number of CTEs in the WITH clause */ + With *pOuter; /* Containing WITH clause, or NULL */ + Cte a[1]; /* The CTEs of this WITH clause */ }; #ifdef SQLITE_DEBUG /* ** An instance of the TreeView object is used for printing the content of @@ -4881,11 +4892,13 @@ #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*); + Cte *sqlite3CteNew(Parse*,Token*,ExprList*,Select*,int); + With *sqlite3WithAdd(Parse*,With*,Cte*); + void sqlite3CteDelete(sqlite3*,Cte*); void sqlite3WithDelete(sqlite3*,With*); void sqlite3WithPush(Parse*, With*, u8); #else #define sqlite3WithPush(x,y,z) #define sqlite3WithDelete(x,y) Index: tool/mkkeywordhash.c ================================================================== --- tool/mkkeywordhash.c +++ tool/mkkeywordhash.c @@ -227,11 +227,11 @@ { "FOLLOWING", "TK_FOLLOWING", WINDOWFUNC, 4 }, { "FOR", "TK_FOR", TRIGGER, 2 }, { "FOREIGN", "TK_FOREIGN", FKEY, 1 }, { "FROM", "TK_FROM", ALWAYS, 10 }, { "FULL", "TK_JOIN_KW", ALWAYS, 3 }, - { "GENERATED", "TK_GENERATED", GENCOL, 1 }, + { "GENERATED", "TK_GENERATED", ALWAYS, 1 }, { "GLOB", "TK_LIKE_KW", ALWAYS, 3 }, { "GROUP", "TK_GROUP", ALWAYS, 5 }, { "GROUPS", "TK_GROUPS", WINDOWFUNC, 2 }, { "HAVING", "TK_HAVING", ALWAYS, 5 }, { "IF", "TK_IF", ALWAYS, 2 }, @@ -253,10 +253,11 @@ { "LAST", "TK_LAST", ALWAYS, 4 }, { "LEFT", "TK_JOIN_KW", ALWAYS, 5 }, { "LIKE", "TK_LIKE_KW", ALWAYS, 5 }, { "LIMIT", "TK_LIMIT", ALWAYS, 3 }, { "MATCH", "TK_MATCH", ALWAYS, 2 }, + { "MATERIALIZED", "TK_MATERIALIZED", CTE, 12 }, { "NATURAL", "TK_JOIN_KW", ALWAYS, 3 }, { "NO", "TK_NO", FKEY|WINDOWFUNC, 2 }, { "NOT", "TK_NOT", ALWAYS, 10 }, { "NOTHING", "TK_NOTHING", UPSERT, 1 }, { "NOTNULL", "TK_NOTNULL", ALWAYS, 3 },