Improvements to EXPLAIN QUERY PLAN formatting. The MULTI-INDEX OR now shows
a separate "INDEX" subtree for each index. SCALAR SUBQUERY entries provide
a subquery number that is related back to the .selecttrace output.
FossilOrigin-Name: 7153552bac51295c56a1c42ca79d57195851e232509f9e9610375692f48c7e86
diff --git a/src/expr.c b/src/expr.c
index 61a3386..f0a3a07 100644
--- a/src/expr.c
+++ b/src/expr.c
@@ -2691,10 +2691,15 @@
** might not have been invoked yet, so invoke it now as a subroutine.
*/
if( ExprHasProperty(pExpr, EP_Subrtn) ){
- sqlite3VdbeAddOp2(v, OP_Once, 0, sqlite3VdbeCurrentAddr(v)+3);
+ int addr = sqlite3VdbeAddOp0(v, OP_Once); VdbeCoverage(v);
+ if( ExprHasProperty(pExpr, EP_xIsSelect) ){
+ ExplainQueryPlan((pParse, 0, "REUSE LIST SUBQUERY %d",
+ pExpr->x.pSelect->selId));
+ }
sqlite3VdbeAddOp2(v, OP_Gosub, pExpr->y.sub.regReturn,
pExpr->y.sub.iAddr);
sqlite3VdbeAddOp2(v, OP_OpenDup, iTab, pExpr->iTable);
+ sqlite3VdbeJumpHere(v, addr);
return;
}
@@ -2746,7 +2751,7 @@
if( ALWAYS(pEList->nExpr==nVal) ){
SelectDest dest;
int i;
- sqlite3SelectDestInit(&dest, SRT_Set, pExpr->iTable);
+ sqlite3SelectDestInit(&dest, SRT_Set, iTab);
dest.zAffSdst = exprINAffinity(pParse, pExpr);
pSelect->iLimit = 0;
testcase( pSelect->selFlags & SF_Distinct );
@@ -2810,17 +2815,17 @@
/* Evaluate the expression and insert it into the temp table */
if( isRowid && sqlite3ExprIsInteger(pE2, &iValToIns) ){
- sqlite3VdbeAddOp3(v, OP_InsertInt, pExpr->iTable, r2, iValToIns);
+ sqlite3VdbeAddOp3(v, OP_InsertInt, iTab, r2, iValToIns);
}else{
r3 = sqlite3ExprCodeTarget(pParse, pE2, r1);
if( isRowid ){
sqlite3VdbeAddOp2(v, OP_MustBeInt, r3,
sqlite3VdbeCurrentAddr(v)+2);
VdbeCoverage(v);
- sqlite3VdbeAddOp3(v, OP_Insert, pExpr->iTable, r2, r3);
+ sqlite3VdbeAddOp3(v, OP_Insert, iTab, r2, r3);
}else{
sqlite3VdbeAddOp4(v, OP_MakeRecord, r3, 1, r2, &affinity, 1);
- sqlite3VdbeAddOp4Int(v, OP_IdxInsert, pExpr->iTable, r2, r3, 1);
+ sqlite3VdbeAddOp4Int(v, OP_IdxInsert, iTab, r2, r3, 1);
}
}
}
@@ -2864,6 +2869,11 @@
Vdbe *v = pParse->pVdbe;
assert( v!=0 );
+ testcase( pExpr->op==TK_EXISTS );
+ testcase( pExpr->op==TK_SELECT );
+ assert( pExpr->op==TK_EXISTS || pExpr->op==TK_SELECT );
+ assert( ExprHasProperty(pExpr, EP_xIsSelect) );
+ pSel = pExpr->x.pSelect;
/* The evaluation of the EXISTS/SELECT must be repeated every time it
** is encountered if any of the following is true:
@@ -2879,6 +2889,7 @@
/* If this routine has already been coded, then invoke it as a
** subroutine. */
if( ExprHasProperty(pExpr, EP_Subrtn) ){
+ ExplainQueryPlan((pParse, 0, "REUSE SUBQUERY %d", pSel->selId));
sqlite3VdbeAddOp2(v, OP_Gosub, pExpr->y.sub.regReturn,
pExpr->y.sub.iAddr);
return pExpr->iTable;
@@ -2904,14 +2915,8 @@
** In both cases, the query is augmented with "LIMIT 1". Any
** preexisting limit is discarded in place of the new LIMIT 1.
*/
- testcase( pExpr->op==TK_EXISTS );
- testcase( pExpr->op==TK_SELECT );
- assert( pExpr->op==TK_EXISTS || pExpr->op==TK_SELECT );
- assert( ExprHasProperty(pExpr, EP_xIsSelect) );
-
- pSel = pExpr->x.pSelect;
- ExplainQueryPlan((pParse, 1, "%sSCALAR SUBQUERY",
- addrOnce?"":"CORRELATED "));
+ ExplainQueryPlan((pParse, 1, "%sSCALAR SUBQUERY %d",
+ addrOnce?"":"CORRELATED ", pSel->selId));
nReg = pExpr->op==TK_SELECT ? pSel->pEList->nExpr : 1;
sqlite3SelectDestInit(&dest, 0, pParse->nMem+1);
pParse->nMem += nReg;
diff --git a/src/vdbe.h b/src/vdbe.h
index f4d360e..99c9c7f 100644
--- a/src/vdbe.h
+++ b/src/vdbe.h
@@ -208,6 +208,7 @@
void sqlite3VdbeExplain(Parse*,u8,const char*,...);
void sqlite3VdbeExplainPop(Parse*);
int sqlite3VdbeExplainParent(Parse*);
+ void sqlite3ExplainBreakpoint(const char*,const char*);
# define ExplainQueryPlan(P) sqlite3VdbeExplain P
# define ExplainQueryPlanPop(P) sqlite3VdbeExplainPop(P)
# define ExplainQueryPlanParent(P) sqlite3VdbeExplainParent(P)
@@ -215,6 +216,12 @@
# define ExplainQueryPlan(P)
# define ExplainQueryPlanPop(P)
# define ExplainQueryPlanParent(P) 0
+# define sqlite3ExplainBreakpoint(A,B) /*no-op*/
+#endif
+#if defined(SQLITE_DEBUG) && !defined(SQLITE_OMIT_EXPLAIN)
+ void sqlite3ExplainBreakpoint(const char*,const char*);
+#else
+# define sqlite3ExplainBreakpoint(A,B) /*no-op*/
#endif
void sqlite3VdbeAddParseSchemaOp(Vdbe*,int,char*);
void sqlite3VdbeChangeOpcode(Vdbe*, u32 addr, u8);
diff --git a/src/vdbeaux.c b/src/vdbeaux.c
index 5225cbc..286d4e9 100644
--- a/src/vdbeaux.c
+++ b/src/vdbeaux.c
@@ -350,13 +350,27 @@
}
/*
-** Add a new OP_Explain opcode.
+** Set a debugger breakpoint on the following routine in order to
+** monitor the EXPLAIN QUERY PLAN code generation.
+*/
+#if defined(SQLITE_DEBUG)
+void sqlite3ExplainBreakpoint(const char *z1, const char *z2){
+ (void)z1;
+ (void)z2;
+}
+#endif
+
+/*
+** Add a new OP_ opcode.
**
** If the bPush flag is true, then make this opcode the parent for
** subsequent Explains until sqlite3VdbeExplainPop() is called.
*/
void sqlite3VdbeExplain(Parse *pParse, u8 bPush, const char *zFmt, ...){
- if( pParse->explain==2 ){
+#if !defined(SQLITE_DEBUG)
+ if( pParse->explain==2 )
+#endif
+ {
char *zMsg;
Vdbe *v;
va_list ap;
@@ -368,7 +382,10 @@
iThis = v->nOp;
sqlite3VdbeAddOp4(v, OP_Explain, iThis, pParse->addrExplain, 0,
zMsg, P4_DYNAMIC);
- if( bPush) pParse->addrExplain = iThis;
+ sqlite3ExplainBreakpoint(bPush?"PUSH":"", sqlite3VdbeGetOp(v,-1)->p4.z);
+ if( bPush){
+ pParse->addrExplain = iThis;
+ }
}
}
@@ -376,6 +393,7 @@
** Pop the EXPLAIN QUERY PLAN stack one level.
*/
void sqlite3VdbeExplainPop(Parse *pParse){
+ sqlite3ExplainBreakpoint("POP", 0);
pParse->addrExplain = sqlite3VdbeExplainParent(pParse);
}
#endif /* SQLITE_OMIT_EXPLAIN */
diff --git a/src/wherecode.c b/src/wherecode.c
index d238efd..f7cfc43 100644
--- a/src/wherecode.c
+++ b/src/wherecode.c
@@ -213,6 +213,7 @@
}
#endif
zMsg = sqlite3StrAccumFinish(&str);
+ sqlite3ExplainBreakpoint("",zMsg);
ret = sqlite3VdbeAddOp4(v, OP_Explain, sqlite3VdbeCurrentAddr(v),
pParse->addrExplain, 0, zMsg,P4_DYNAMIC);
}
@@ -1951,6 +1952,7 @@
pOrExpr = pAndExpr;
}
/* Loop through table entries that match term pOrTerm. */
+ ExplainQueryPlan((pParse, 1, "INDEX %d", ii+1));
WHERETRACE(0xffff, ("Subplan for OR-clause:\n"));
pSubWInfo = sqlite3WhereBegin(pParse, pOrTab, pOrExpr, 0, 0,
wctrlFlags, iCovCur);
@@ -2054,6 +2056,7 @@
/* Finish the loop through table entries that match term pOrTerm. */
sqlite3WhereEnd(pSubWInfo);
+ ExplainQueryPlanPop(pParse);
}
}
}