aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
Diffstat (limited to 'src')
-rw-r--r--src/build.c4
-rw-r--r--src/parse.y9
-rw-r--r--src/select.c28
-rw-r--r--src/sqliteInt.h11
4 files changed, 39 insertions, 13 deletions
diff --git a/src/build.c b/src/build.c
index fad9a8bb8..4068c54da 100644
--- a/src/build.c
+++ b/src/build.c
@@ -5211,7 +5211,8 @@ 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 */
+ Select *pQuery, /* Query used to initialize the table */
+ u8 eM10d /* The MATERIALIZED flag */
){
Cte *pNew;
sqlite3 *db = pParse->db;
@@ -5226,6 +5227,7 @@ Cte *sqlite3CteNew(
pNew->pSelect = pQuery;
pNew->pCols = pArglist;
pNew->zName = sqlite3NameFromToken(pParse->db, pName);
+ pNew->eM10d = eM10d;
}
return pNew;
}
diff --git a/src/parse.y b/src/parse.y
index c84631846..825b67ed3 100644
--- a/src/parse.y
+++ b/src/parse.y
@@ -250,6 +250,7 @@ columnname(A) ::= nm(A) typetoken(Y). {sqlite3AddColumn(pParse,&A,&Y);}
%ifndef SQLITE_OMIT_GENERATED_COLUMNS
GENERATED ALWAYS
%endif
+ MATERIALIZED
REINDEX RENAME CTIME_KW IF
.
%wildcard ANY.
@@ -1666,8 +1667,12 @@ with ::= .
with ::= WITH wqlist(W). { sqlite3WithPush(pParse, W, 1); }
with ::= WITH RECURSIVE wqlist(W). { sqlite3WithPush(pParse, W, 1); }
-wqitem(A) ::= nm(X) eidlist_opt(Y) AS LP select(Z) RP. {
- A = sqlite3CteNew(pParse, &X, Y, Z); /*A-overwrites-X*/
+%type wqas {u8}
+wqas(A) ::= AS. {A = M10d_Any;}
+wqas(A) ::= AS MATERIALIZED. {A = M10d_Yes;}
+wqas(A) ::= AS NOT MATERIALIZED. {A = M10d_No;}
+wqitem(A) ::= nm(X) eidlist_opt(Y) wqas(M) LP select(Z) RP. {
+ A = sqlite3CteNew(pParse, &X, Y, Z, M); /*A-overwrites-X*/
}
wqlist(A) ::= wqitem(X). {
A = sqlite3WithAdd(pParse, 0, X); /*A-overwrites-X*/
diff --git a/src/select.c b/src/select.c
index 3da69943f..a13abe375 100644
--- a/src/select.c
+++ b/src/select.c
@@ -4540,6 +4540,10 @@ static int propagateConstants(
** changes to the WHERE clause of the inner query could change the
** window over which window functions are calculated).
**
+** (7) The inner query is a Common Table Expression (CTE) that should
+** be materialized. (This restriction is implemented in the calling
+** routine.)
+**
** Return 0 if no changes are made and non-zero if one or more WHERE clause
** terms are duplicated into the subquery.
*/
@@ -4923,6 +4927,7 @@ static int resolveFromTermToCte(
int bMayRecursive; /* True if compound joined by UNION [ALL] */
With *pSavedWith; /* Initial value of pParse->pWith */
int iRecTab = -1; /* Cursor for recursive table */
+ CteUse *pCteUse;
/* If pCte->zCteErr is non-NULL at this point, then this is an illegal
** recursive reference to CTE pCte. Leave an error in pParse and return
@@ -4937,14 +4942,16 @@ static int resolveFromTermToCte(
assert( pFrom->pTab==0 );
pTab = sqlite3DbMallocZero(db, sizeof(Table));
if( pTab==0 ) return 2;
- if( pCte->pUse==0 ){
- pCte->pUse = sqlite3DbMallocZero(db, sizeof(pCte->pUse[0]));
- if( pCte->pUse==0
- || sqlite3ParserAddCleanup(pParse,sqlite3DbFree,pCte->pUse)==0
+ pCteUse = pCte->pUse;
+ if( pCteUse==0 ){
+ pCte->pUse = pCteUse = sqlite3DbMallocZero(db, sizeof(pCteUse[0]));
+ if( pCteUse==0
+ || sqlite3ParserAddCleanup(pParse,sqlite3DbFree,pCteUse)==0
){
sqlite3DbFree(db, pTab);
return 2;
}
+ pCteUse->eM10d = pCte->eM10d;
}
pFrom->pTab = pTab;
pTab->nTabRef = 1;
@@ -4956,8 +4963,11 @@ static int resolveFromTermToCte(
if( db->mallocFailed ) return 2;
assert( pFrom->pSelect );
pFrom->fg.isCte = 1;
- pFrom->u2.pCteUse = pCte->pUse;
- pCte->pUse->nUse++;
+ pFrom->u2.pCteUse = pCteUse;
+ pCteUse->nUse++;
+ if( pCteUse->nUse>=2 && pCteUse->eM10d==M10d_Any ){
+ pCteUse->eM10d = M10d_Yes;
+ }
/* Check if this is a recursive CTE. */
pRecTerm = pSel = pFrom->pSelect;
@@ -6226,7 +6236,7 @@ int sqlite3Select(
** inside the subquery. This can help the subquery to run more efficiently.
*/
if( OptimizationEnabled(db, SQLITE_PushDown)
- && (pItem->fg.isCte==0 || pItem->u2.pCteUse->nUse<=1)
+ && (pItem->fg.isCte==0 || pItem->u2.pCteUse->eM10d==M10d_Yes)
&& pushDownWhereTerms(pParse, pSub, p->pWhere, pItem->iCursor,
(pItem->fg.jointype & JT_OUTER)!=0)
){
@@ -6250,7 +6260,7 @@ int sqlite3Select(
** 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), and
- ** (2) the subquery is not a CTE that is used more then once.
+ ** (2) the subquery is not a CTE that should be materialized
**
** TODO: Are there other reasons beside (1) and (2) to use a co-routine
** implementation?
@@ -6258,7 +6268,7 @@ int sqlite3Select(
if( i==0
&& (pTabList->nSrc==1
|| (pTabList->a[1].fg.jointype&(JT_LEFT|JT_CROSS))!=0) /* (1) */
- && (pItem->fg.isCte==0 || pItem->u2.pCteUse->nUse<2) /* (2) */
+ && (pItem->fg.isCte==0 || pItem->u2.pCteUse->eM10d!=M10d_Yes) /* (2) */
){
/* Implement a co-routine that will return a single row of the result
** set on each invocation.
diff --git a/src/sqliteInt.h b/src/sqliteInt.h
index ea6ff1381..c2db0f8b6 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -3895,9 +3895,17 @@ struct Cte {
Select *pSelect; /* The definition of this CTE */
const char *zCteErr; /* Error message for circular references */
CteUse *pUse; /* Usage information for this CTE */
+ u8 eM10d; /* The MATERIALIZED flag */
};
/*
+** Allowed values for the materialized flag (eM10d):
+*/
+#define M10d_Yes 0 /* AS MATERIALIZED */
+#define M10d_Any 1 /* Not specified. Query planner's choice */
+#define M10d_No 2 /* AS NOT MATERIALIZED */
+
+/*
** An instance of the With object represents a WITH clause containing
** one or more CTEs (common table expressions).
*/
@@ -3924,6 +3932,7 @@ struct CteUse {
int regRtn; /* Return address register for addrM9e subroutine */
int iCur; /* Ephemeral table holding the materialization */
LogEst nRowEst; /* Estimated number of rows in the table */
+ u8 eM10d; /* The MATERIALIZED flag */
};
@@ -4930,7 +4939,7 @@ const char *sqlite3JournalModename(int);
int sqlite3WalDefaultHook(void*,sqlite3*,const char*,int);
#endif
#ifndef SQLITE_OMIT_CTE
- Cte *sqlite3CteNew(Parse*,Token*,ExprList*,Select*);
+ Cte *sqlite3CteNew(Parse*,Token*,ExprList*,Select*,u8);
void sqlite3CteDelete(sqlite3*,Cte*);
With *sqlite3WithAdd(Parse*,With*,Cte*);
void sqlite3WithDelete(sqlite3*,With*);
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-20 21:43:55 +0000