For FROM-clause subqueries that cannot be flattened, try to push WHERE clause
terms of the outer query down into the subquery in order to help the subquery
run faster and/or use less memory.
FossilOrigin-Name: 297fae7551a2af9e600d833801ff79fca0602ad5
diff --git a/manifest b/manifest
index 879a00f..ca7b856 100644
--- a/manifest
+++ b/manifest
@@ -1,5 +1,5 @@
-C Corrections\sto\scomments\sin\sexpr.c.\s\sNo\scode\schanges.
-D 2015-06-01T18:13:16.094
+C For\sFROM-clause\ssubqueries\sthat\scannot\sbe\sflattened,\stry\sto\spush\sWHERE\sclause\nterms\sof\sthe\souter\squery\sdown\sinto\sthe\ssubquery\sin\sorder\sto\shelp\sthe\ssubquery\nrun\sfaster\sand/or\suse\sless\smemory.
+D 2015-06-01T20:28:03.248
F Makefile.arm-wince-mingw32ce-gcc d6df77f1f48d690bd73162294bbba7f59507c72f
F Makefile.in 994bab32a3a69e0c35bd148b65cde49879772964
F Makefile.linux-gcc 91d710bdc4998cb015f39edf3cb314ec4f4d7e23
@@ -250,7 +250,7 @@
F src/random.c ba2679f80ec82c4190062d756f22d0c358180696
F src/resolve.c 84c571794e3ee5806274d95158a4c0177c6c4708
F src/rowset.c eccf6af6d620aaa4579bd3b72c1b6395d9e9fa1e
-F src/select.c 5978cc521cb8fc1aa6a0089e35edaf531accb52a
+F src/select.c 7acdb105dccfa226ea30f3c08a847d448e216240
F src/shell.c 07dda7cd692911d2f22269953418d049f2e2c0ee
F src/sqlite.h.in d165beeceb6b40af60f352a4d4e37e02d9af7df0
F src/sqlite3.rc 992c9f5fb8285ae285d6be28240a7e8d3a7f2bad
@@ -1281,7 +1281,10 @@
F tool/warnings-clang.sh f6aa929dc20ef1f856af04a730772f59283631d4
F tool/warnings.sh 0abfd78ceb09b7f7c27c688c8e3fe93268a13b32
F tool/win/sqlite.vsix deb315d026cc8400325c5863eef847784a219a2f
-P 9b8e5823bccf69f5cdedd8655e75df6e9718b809
-R 79c21798380eea59e3e40be5d7ba64b0
+P f925389eaf5bf8962a28fcaa652b75caa606efba
+R 033219587dbe88756604bf887ec3da43
+T *branch * subquery-opt
+T *sym-subquery-opt *
+T -sym-trunk *
U drh
-Z 59ba12ae4d822d8c7d37e1e41f2f9dfd
+Z 48ce2765ca9875a4f4c27e6792269bfa
diff --git a/manifest.uuid b/manifest.uuid
index f27f0a3..d83252b 100644
--- a/manifest.uuid
+++ b/manifest.uuid
@@ -1 +1 @@
-f925389eaf5bf8962a28fcaa652b75caa606efba
\ No newline at end of file
+297fae7551a2af9e600d833801ff79fca0602ad5
\ No newline at end of file
diff --git a/src/select.c b/src/select.c
index e5e1a99..0558c79 100644
--- a/src/select.c
+++ b/src/select.c
@@ -3718,6 +3718,73 @@
}
#endif /* !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) */
+
+
+#if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
+/*
+** Make copies of relevant WHERE clause terms of the outer query into
+** the WHERE clause of subquery. Example:
+**
+** SELECT * FROM (SELECT a AS x, c-d AS y FROM t1) WHERE x=5 AND y=10;
+**
+** Transformed into:
+**
+** SELECT * FROM (SELECT a AS x, c-d AS y FROM t1 WHERE a=5 AND c-d=10)
+** WHERE x=5 AND y=10;
+**
+** The hope is that the terms added to the inner query will make it more
+** efficient.
+**
+** Do not attempt this optimization if:
+**
+** (1) The inner query is an aggregate. (In that case, we'd really want
+** to copy the outer WHERE-clause terms onto the HAVING clause of the
+** inner query. But they probably won't help there so do not bother.)
+**
+** (2) The inner query is the recursive part of a common table expression.
+**
+** (3) The inner query has a LIMIT clause (since the changes to the WHERE
+** close would change the meaning of the LIMIT).
+**
+** (4) The inner query is the right operand of a LEFT JOIN. (The caller
+** enforces this restriction since this routine does not have enough
+** information to know.)
+**
+** Return 0 if no changes are made and non-zero if one or more WHERE clause
+** terms are duplicated into the subquery.
+*/
+static int pushDownWhereTerms(
+ sqlite3 *db, /* The database connection (for malloc()) */
+ Select *pSubq, /* The subquery whose WHERE clause is to be augmented */
+ Expr *pWhere, /* The WHERE clause of the outer query */
+ int iCursor /* Cursor number of the subquery */
+){
+ Expr *pNew;
+ int nChng = 0;
+ if( pWhere==0 ) return 0;
+ if( (pSubq->selFlags & (SF_Aggregate|SF_Recursive))!=0 ){
+ return 0; /* restrictions (1) and (2) */
+ }
+ if( pSubq->pLimit!=0 ){
+ return 0; /* restriction (3) */
+ }
+ while( pWhere->op==TK_AND ){
+ nChng += pushDownWhereTerms(db, pSubq, pWhere->pRight, iCursor);
+ pWhere = pWhere->pLeft;
+ }
+ if( sqlite3ExprIsTableConstant(pWhere, iCursor) ){
+ nChng++;
+ while( pSubq ){
+ pNew = sqlite3ExprDup(db, pWhere, 0);
+ pNew = substExpr(db, pNew, iCursor, pSubq->pEList);
+ pSubq->pWhere = sqlite3ExprAnd(db, pSubq->pWhere, pNew);
+ pSubq = pSubq->pPrior;
+ }
+ }
+ return nChng;
+}
+#endif /* !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) */
+
/*
** Based on the contents of the AggInfo structure indicated by the first
** argument, this function checks if the following are true:
@@ -4816,60 +4883,72 @@
p->selFlags |= SF_Aggregate;
}
i = -1;
- }else if( pTabList->nSrc==1
- && (p->selFlags & SF_All)==0
- && OptimizationEnabled(db, SQLITE_SubqCoroutine)
- ){
- /* Implement a co-routine that will return a single row of the result
- ** set on each invocation.
- */
- int addrTop = sqlite3VdbeCurrentAddr(v)+1;
- pItem->regReturn = ++pParse->nMem;
- sqlite3VdbeAddOp3(v, OP_InitCoroutine, pItem->regReturn, 0, addrTop);
- VdbeComment((v, "%s", pItem->pTab->zName));
- pItem->addrFillSub = addrTop;
- sqlite3SelectDestInit(&dest, SRT_Coroutine, pItem->regReturn);
- explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId);
- sqlite3Select(pParse, pSub, &dest);
- pItem->pTab->nRowLogEst = sqlite3LogEst(pSub->nSelectRow);
- pItem->viaCoroutine = 1;
- pItem->regResult = dest.iSdst;
- sqlite3VdbeAddOp1(v, OP_EndCoroutine, pItem->regReturn);
- sqlite3VdbeJumpHere(v, addrTop-1);
- sqlite3ClearTempRegCache(pParse);
}else{
- /* Generate a subroutine that will fill an ephemeral table with
- ** the content of this subquery. pItem->addrFillSub will point
- ** to the address of the generated subroutine. pItem->regReturn
- ** is a register allocated to hold the subroutine return address
- */
- int topAddr;
- int onceAddr = 0;
- int retAddr;
- assert( pItem->addrFillSub==0 );
- pItem->regReturn = ++pParse->nMem;
- topAddr = sqlite3VdbeAddOp2(v, OP_Integer, 0, pItem->regReturn);
- pItem->addrFillSub = topAddr+1;
- if( pItem->isCorrelated==0 ){
- /* If the subquery is not correlated and if we are not inside of
- ** a trigger, then we only need to compute the value of the subquery
- ** once. */
- onceAddr = sqlite3CodeOnce(pParse); VdbeCoverage(v);
- VdbeComment((v, "materialize \"%s\"", pItem->pTab->zName));
- }else{
- VdbeNoopComment((v, "materialize \"%s\"", pItem->pTab->zName));
+ if( (pItem->jointype & JT_OUTER)==0
+ && pushDownWhereTerms(db, pSub, p->pWhere, pItem->iCursor)
+ ){
+#if SELECTTRACE_ENABLED
+ if( sqlite3SelectTrace & 0x100 ){
+ sqlite3DebugPrintf("After WHERE-clause push-down:\n");
+ sqlite3TreeViewSelect(0, p, 0);
+ }
+#endif
}
- sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor);
- explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId);
- sqlite3Select(pParse, pSub, &dest);
- pItem->pTab->nRowLogEst = sqlite3LogEst(pSub->nSelectRow);
- if( onceAddr ) sqlite3VdbeJumpHere(v, onceAddr);
- retAddr = sqlite3VdbeAddOp1(v, OP_Return, pItem->regReturn);
- VdbeComment((v, "end %s", pItem->pTab->zName));
- sqlite3VdbeChangeP1(v, topAddr, retAddr);
- sqlite3ClearTempRegCache(pParse);
+ if( pTabList->nSrc==1
+ && (p->selFlags & SF_All)==0
+ && OptimizationEnabled(db, SQLITE_SubqCoroutine)
+ ){
+ /* Implement a co-routine that will return a single row of the result
+ ** set on each invocation.
+ */
+ int addrTop = sqlite3VdbeCurrentAddr(v)+1;
+ pItem->regReturn = ++pParse->nMem;
+ sqlite3VdbeAddOp3(v, OP_InitCoroutine, pItem->regReturn, 0, addrTop);
+ VdbeComment((v, "%s", pItem->pTab->zName));
+ pItem->addrFillSub = addrTop;
+ sqlite3SelectDestInit(&dest, SRT_Coroutine, pItem->regReturn);
+ explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId);
+ sqlite3Select(pParse, pSub, &dest);
+ pItem->pTab->nRowLogEst = sqlite3LogEst(pSub->nSelectRow);
+ pItem->viaCoroutine = 1;
+ pItem->regResult = dest.iSdst;
+ sqlite3VdbeAddOp1(v, OP_EndCoroutine, pItem->regReturn);
+ sqlite3VdbeJumpHere(v, addrTop-1);
+ sqlite3ClearTempRegCache(pParse);
+ }else{
+ /* Generate a subroutine that will fill an ephemeral table with
+ ** the content of this subquery. pItem->addrFillSub will point
+ ** to the address of the generated subroutine. pItem->regReturn
+ ** is a register allocated to hold the subroutine return address
+ */
+ int topAddr;
+ int onceAddr = 0;
+ int retAddr;
+ assert( pItem->addrFillSub==0 );
+ pItem->regReturn = ++pParse->nMem;
+ topAddr = sqlite3VdbeAddOp2(v, OP_Integer, 0, pItem->regReturn);
+ pItem->addrFillSub = topAddr+1;
+ if( pItem->isCorrelated==0 ){
+ /* If the subquery is not correlated and if we are not inside of
+ ** a trigger, then we only need to compute the value of the subquery
+ ** once. */
+ onceAddr = sqlite3CodeOnce(pParse); VdbeCoverage(v);
+ VdbeComment((v, "materialize \"%s\"", pItem->pTab->zName));
+ }else{
+ VdbeNoopComment((v, "materialize \"%s\"", pItem->pTab->zName));
+ }
+ sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor);
+ explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId);
+ sqlite3Select(pParse, pSub, &dest);
+ pItem->pTab->nRowLogEst = sqlite3LogEst(pSub->nSelectRow);
+ if( onceAddr ) sqlite3VdbeJumpHere(v, onceAddr);
+ retAddr = sqlite3VdbeAddOp1(v, OP_Return, pItem->regReturn);
+ VdbeComment((v, "end %s", pItem->pTab->zName));
+ sqlite3VdbeChangeP1(v, topAddr, retAddr);
+ sqlite3ClearTempRegCache(pParse);
+ }
}
- if( /*pParse->nErr ||*/ db->mallocFailed ){
+ if( db->mallocFailed ){
goto select_end;
}
pParse->nHeight -= sqlite3SelectExprHeight(p);
