SQLITE_NOTICE(283): recovered 2 frames from WAL file /fossil/sqlite.fossil-wal
Index: ext/expert/expert1.test
==================================================================
--- ext/expert/expert1.test
+++ ext/expert/expert1.test
@@ -241,12 +241,14 @@
SELECT * FROM t7 WHERE a=? OR b=?
} {
CREATE INDEX t7_idx_00000062 ON t7(b);
CREATE INDEX t7_idx_00000061 ON t7(a);
MULTI-INDEX OR
- SEARCH TABLE t7 USING INDEX t7_idx_00000061 (a=?)
- SEARCH TABLE t7 USING INDEX t7_idx_00000062 (b=?)
+ INDEX 1
+ SEARCH TABLE t7 USING INDEX t7_idx_00000061 (a=?)
+ INDEX 2
+ SEARCH TABLE t7 USING INDEX t7_idx_00000062 (b=?)
}
# rowid terms.
#
do_setup_rec_test $tn.13.1 {
Index: src/expr.c
==================================================================
--- src/expr.c
+++ src/expr.c
@@ -2348,11 +2348,12 @@
int sqlite3FindInIndex(
Parse *pParse, /* Parsing context */
Expr *pX, /* The right-hand side (RHS) of the IN operator */
u32 inFlags, /* IN_INDEX_LOOP, _MEMBERSHIP, and/or _NOOP_OK */
int *prRhsHasNull, /* Register holding NULL status. See notes */
- int *aiMap /* Mapping from Index fields to RHS fields */
+ int *aiMap, /* Mapping from Index fields to RHS fields */
+ int *piTab /* OUT: index to use */
){
Select *p; /* SELECT to the right of IN operator */
int eType = 0; /* Type of RHS table. IN_INDEX_* */
int iTab = pParse->nTab++; /* Cursor of the RHS table */
int mustBeUnique; /* True if RHS must be unique */
@@ -2541,24 +2542,23 @@
}
}else if( prRhsHasNull ){
*prRhsHasNull = rMayHaveNull = ++pParse->nMem;
}
assert( pX->op==TK_IN );
- sqlite3CodeRhsOfIN(pParse, pX, eType==IN_INDEX_ROWID);
+ sqlite3CodeRhsOfIN(pParse, pX, iTab, eType==IN_INDEX_ROWID);
if( rMayHaveNull ){
- sqlite3SetHasNullFlag(v, pX->iTable, rMayHaveNull);
+ sqlite3SetHasNullFlag(v, iTab, rMayHaveNull);
}
pParse->nQueryLoop = savedNQueryLoop;
- }else{
- pX->iTable = iTab;
}
if( aiMap && eType!=IN_INDEX_INDEX_ASC && eType!=IN_INDEX_INDEX_DESC ){
int i, n;
n = sqlite3ExprVectorSize(pX->pLeft);
for(i=0; iiTable,
+** however the cursor number returned might not be the same, as it might
+** have been duplicated using OP_OpenDup.
**
** If parameter isRowid is non-zero, then LHS of the IN operator is guaranteed
** to be a non-null integer. In this case, the ephemeral table can be an
** table B-Tree that keyed by only integers. The more general cases uses
** an index B-Tree which can have arbitrary keys, but is slower to both
@@ -2656,34 +2660,59 @@
** is used.
*/
void sqlite3CodeRhsOfIN(
Parse *pParse, /* Parsing context */
Expr *pExpr, /* The IN operator */
+ int iTab, /* Use this cursor number */
int isRowid /* If true, LHS is a rowid */
){
- int jmpIfDynamic = -1; /* One-time test address */
+ int addrOnce = 0; /* Address of the OP_Once instruction at top */
int addr; /* Address of OP_OpenEphemeral instruction */
Expr *pLeft; /* the LHS of the IN operator */
KeyInfo *pKeyInfo = 0; /* Key information */
int nVal; /* Size of vector pLeft */
Vdbe *v; /* The prepared statement under construction */
- v = sqlite3GetVdbe(pParse);
+ v = pParse->pVdbe;
assert( v!=0 );
- /* The evaluation of the RHS of IN operator must be repeated every time it
+ /* The evaluation of the IN must be repeated every time it
** is encountered if any of the following is true:
**
** * The right-hand side is a correlated subquery
** * The right-hand side is an expression list containing variables
** * We are inside a trigger
**
- ** If all of the above are false, then we can run this code just once
- ** save the results, and reuse the same result on subsequent invocations.
+ ** If all of the above are false, then we can compute the RHS just once
+ ** and reuse it many names.
*/
- if( !ExprHasProperty(pExpr, EP_VarSelect) ){
- jmpIfDynamic = sqlite3VdbeAddOp0(v, OP_Once); VdbeCoverage(v);
+ if( !ExprHasProperty(pExpr, EP_VarSelect) && pParse->iSelfTab==0 ){
+ /* Reuse of the RHS is allowed */
+ /* If this routine has already been coded, but the previous code
+ ** might not have been invoked yet, so invoke it now as a subroutine.
+ */
+ if( ExprHasProperty(pExpr, EP_Subrtn) ){
+ 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;
+ }
+
+ /* Begin coding the subroutine */
+ ExprSetProperty(pExpr, EP_Subrtn);
+ pExpr->y.sub.regReturn = ++pParse->nMem;
+ pExpr->y.sub.iAddr =
+ sqlite3VdbeAddOp2(v, OP_Integer, 0, pExpr->y.sub.regReturn) + 1;
+ VdbeComment((v, "return address"));
+
+ addrOnce = sqlite3VdbeAddOp0(v, OP_Once); VdbeCoverage(v);
}
/* Check to see if this is a vector IN operator */
pLeft = pExpr->pLeft;
nVal = sqlite3ExprVectorSize(pLeft);
@@ -2690,13 +2719,20 @@
assert( !isRowid || nVal==1 );
/* Construct the ephemeral table that will contain the content of
** RHS of the IN operator.
*/
- pExpr->iTable = pParse->nTab++;
+ pExpr->iTable = iTab;
addr = sqlite3VdbeAddOp2(v, OP_OpenEphemeral,
pExpr->iTable, (isRowid?0:nVal));
+#ifdef SQLITE_ENABLE_EXPLAIN_COMMENTS
+ if( ExprHasProperty(pExpr, EP_xIsSelect) ){
+ VdbeComment((v, "Result of SELECT %u", pExpr->x.pSelect->selId));
+ }else{
+ VdbeComment((v, "RHS of IN operator"));
+ }
+#endif
pKeyInfo = isRowid ? 0 : sqlite3KeyInfoAlloc(pParse->db, nVal, 1);
if( ExprHasProperty(pExpr, EP_xIsSelect) ){
/* Case 1: expr IN (SELECT ...)
**
@@ -2704,20 +2740,20 @@
** table allocated and opened above.
*/
Select *pSelect = pExpr->x.pSelect;
ExprList *pEList = pSelect->pEList;
- ExplainQueryPlan((pParse, 1, "%sLIST SUBQUERY",
- jmpIfDynamic>=0?"":"CORRELATED "
+ ExplainQueryPlan((pParse, 1, "%sLIST SUBQUERY %d",
+ addrOnce?"":"CORRELATED ", pSelect->selId
));
assert( !isRowid );
/* If the LHS and RHS of the IN operator do not match, that
** error will have been caught long before we reach this point. */
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 );
testcase( pKeyInfo==0 ); /* Caused by OOM in sqlite3KeyInfoAlloc() */
if( sqlite3Select(pParse, pSelect, &dest) ){
@@ -2770,39 +2806,42 @@
/* If the expression is not constant then we will need to
** disable the test that was generated above that makes sure
** this code only executes once. Because for a non-constant
** expression we need to rerun this code each time.
*/
- if( jmpIfDynamic>=0 && !sqlite3ExprIsConstant(pE2) ){
- sqlite3VdbeChangeToNoop(v, jmpIfDynamic);
- jmpIfDynamic = -1;
+ if( addrOnce && !sqlite3ExprIsConstant(pE2) ){
+ sqlite3VdbeChangeToNoop(v, addrOnce);
+ addrOnce = 0;
}
/* 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);
}
}
}
sqlite3ReleaseTempReg(pParse, r1);
sqlite3ReleaseTempReg(pParse, r2);
}
if( pKeyInfo ){
sqlite3VdbeChangeP4(v, addr, (void *)pKeyInfo, P4_KEYINFO);
}
- if( jmpIfDynamic>=0 ){
- sqlite3VdbeJumpHere(v, jmpIfDynamic);
+ if( addrOnce ){
+ sqlite3VdbeJumpHere(v, addrOnce);
+ /* Subroutine return */
+ sqlite3VdbeAddOp1(v, OP_Return, pExpr->y.sub.regReturn);
+ sqlite3VdbeChangeP1(v, pExpr->y.sub.iAddr-1, sqlite3VdbeCurrentAddr(v)-1);
}
}
#endif /* SQLITE_OMIT_SUBQUERY */
/*
@@ -2819,18 +2858,24 @@
** return value is the register of the left-most result column.
** Return 0 if an error occurs.
*/
#ifndef SQLITE_OMIT_SUBQUERY
int sqlite3CodeSubselect(Parse *pParse, Expr *pExpr){
- int jmpIfDynamic = -1; /* One-time test address */
+ int addrOnce = 0; /* Address of OP_Once at top of subroutine */
int rReg = 0; /* Register storing resulting */
Select *pSel; /* SELECT statement to encode */
SelectDest dest; /* How to deal with SELECT result */
int nReg; /* Registers to allocate */
Expr *pLimit; /* New limit expression */
- Vdbe *v = sqlite3GetVdbe(pParse);
+
+ 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:
**
** * The right-hand side is a correlated subquery
@@ -2839,11 +2884,27 @@
**
** If all of the above are false, then we can run this code just once
** save the results, and reuse the same result on subsequent invocations.
*/
if( !ExprHasProperty(pExpr, EP_VarSelect) ){
- jmpIfDynamic = sqlite3VdbeAddOp0(v, OP_Once); VdbeCoverage(v);
+ /* 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;
+ }
+
+ /* Begin coding the subroutine */
+ ExprSetProperty(pExpr, EP_Subrtn);
+ pExpr->y.sub.regReturn = ++pParse->nMem;
+ pExpr->y.sub.iAddr =
+ sqlite3VdbeAddOp2(v, OP_Integer, 0, pExpr->y.sub.regReturn) + 1;
+ VdbeComment((v, "return address"));
+
+ addrOnce = sqlite3VdbeAddOp0(v, OP_Once); VdbeCoverage(v);
}
/* For a SELECT, generate code to put the values for all columns of
** the first row into an array of registers and return the index of
** the first register.
@@ -2852,18 +2913,12 @@
** into a register and return that register number.
**
** 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",
- jmpIfDynamic>=0?"":"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;
if( pExpr->op==TK_SELECT ){
dest.eDest = SRT_Mem;
@@ -2885,15 +2940,18 @@
}
pSel->iLimit = 0;
if( sqlite3Select(pParse, pSel, &dest) ){
return 0;
}
- rReg = dest.iSDParm;
+ pExpr->iTable = rReg = dest.iSDParm;
ExprSetVVAProperty(pExpr, EP_NoReduce);
+ if( addrOnce ){
+ sqlite3VdbeJumpHere(v, addrOnce);
- if( jmpIfDynamic>=0 ){
- sqlite3VdbeJumpHere(v, jmpIfDynamic);
+ /* Subroutine return */
+ sqlite3VdbeAddOp1(v, OP_Return, pExpr->y.sub.regReturn);
+ sqlite3VdbeChangeP1(v, pExpr->y.sub.iAddr-1, sqlite3VdbeCurrentAddr(v)-1);
}
return rReg;
}
#endif /* SQLITE_OMIT_SUBQUERY */
@@ -2966,10 +3024,11 @@
int destStep2; /* Where to jump when NULLs seen in step 2 */
int destStep6 = 0; /* Start of code for Step 6 */
int addrTruthOp; /* Address of opcode that determines the IN is true */
int destNotNull; /* Jump here if a comparison is not true in step 6 */
int addrTop; /* Top of the step-6 loop */
+ int iTab = 0; /* Index to use */
pLeft = pExpr->pLeft;
if( sqlite3ExprCheckIN(pParse, pExpr) ) return;
zAff = exprINAffinity(pParse, pExpr);
nVector = sqlite3ExprVectorSize(pExpr->pLeft);
@@ -2977,19 +3036,20 @@
pParse->db, nVector*(sizeof(int) + sizeof(char)) + 1
);
if( pParse->db->mallocFailed ) goto sqlite3ExprCodeIN_oom_error;
/* Attempt to compute the RHS. After this step, if anything other than
- ** IN_INDEX_NOOP is returned, the table opened ith cursor pExpr->iTable
+ ** IN_INDEX_NOOP is returned, the table opened with cursor iTab
** contains the values that make up the RHS. If IN_INDEX_NOOP is returned,
** the RHS has not yet been coded. */
v = pParse->pVdbe;
assert( v!=0 ); /* OOM detected prior to this routine */
VdbeNoopComment((v, "begin IN expr"));
eType = sqlite3FindInIndex(pParse, pExpr,
IN_INDEX_MEMBERSHIP | IN_INDEX_NOOP_OK,
- destIfFalse==destIfNull ? 0 : &rRhsHasNull, aiMap);
+ destIfFalse==destIfNull ? 0 : &rRhsHasNull,
+ aiMap, &iTab);
assert( pParse->nErr || nVector==1 || eType==IN_INDEX_EPH
|| eType==IN_INDEX_INDEX_ASC || eType==IN_INDEX_INDEX_DESC
);
#ifdef SQLITE_DEBUG
@@ -3093,23 +3153,23 @@
*/
if( eType==IN_INDEX_ROWID ){
/* In this case, the RHS is the ROWID of table b-tree and so we also
** know that the RHS is non-NULL. Hence, we combine steps 3 and 4
** into a single opcode. */
- sqlite3VdbeAddOp3(v, OP_SeekRowid, pExpr->iTable, destIfFalse, rLhs);
+ sqlite3VdbeAddOp3(v, OP_SeekRowid, iTab, destIfFalse, rLhs);
VdbeCoverage(v);
addrTruthOp = sqlite3VdbeAddOp0(v, OP_Goto); /* Return True */
}else{
sqlite3VdbeAddOp4(v, OP_Affinity, rLhs, nVector, 0, zAff, nVector);
if( destIfFalse==destIfNull ){
/* Combine Step 3 and Step 5 into a single opcode */
- sqlite3VdbeAddOp4Int(v, OP_NotFound, pExpr->iTable, destIfFalse,
+ sqlite3VdbeAddOp4Int(v, OP_NotFound, iTab, destIfFalse,
rLhs, nVector); VdbeCoverage(v);
goto sqlite3ExprCodeIN_finished;
}
/* Ordinary Step 3, for the case where FALSE and NULL are distinct */
- addrTruthOp = sqlite3VdbeAddOp4Int(v, OP_Found, pExpr->iTable, 0,
+ addrTruthOp = sqlite3VdbeAddOp4Int(v, OP_Found, iTab, 0,
rLhs, nVector); VdbeCoverage(v);
}
/* Step 4. If the RHS is known to be non-NULL and we did not find
** an match on the search above, then the result must be FALSE.
@@ -3130,11 +3190,11 @@
**
** For a scalar LHS, it is sufficient to check just the first row
** of the RHS.
*/
if( destStep6 ) sqlite3VdbeResolveLabel(v, destStep6);
- addrTop = sqlite3VdbeAddOp2(v, OP_Rewind, pExpr->iTable, destIfFalse);
+ addrTop = sqlite3VdbeAddOp2(v, OP_Rewind, iTab, destIfFalse);
VdbeCoverage(v);
if( nVector>1 ){
destNotNull = sqlite3VdbeMakeLabel(pParse);
}else{
/* For nVector==1, combine steps 6 and 7 by immediately returning
@@ -3145,20 +3205,20 @@
Expr *p;
CollSeq *pColl;
int r3 = sqlite3GetTempReg(pParse);
p = sqlite3VectorFieldSubexpr(pLeft, i);
pColl = sqlite3ExprCollSeq(pParse, p);
- sqlite3VdbeAddOp3(v, OP_Column, pExpr->iTable, i, r3);
+ sqlite3VdbeAddOp3(v, OP_Column, iTab, i, r3);
sqlite3VdbeAddOp4(v, OP_Ne, rLhs+i, destNotNull, r3,
(void*)pColl, P4_COLLSEQ);
VdbeCoverage(v);
sqlite3ReleaseTempReg(pParse, r3);
}
sqlite3VdbeAddOp2(v, OP_Goto, 0, destIfNull);
if( nVector>1 ){
sqlite3VdbeResolveLabel(v, destNotNull);
- sqlite3VdbeAddOp2(v, OP_Next, pExpr->iTable, addrTop+1);
+ sqlite3VdbeAddOp2(v, OP_Next, iTab, addrTop+1);
VdbeCoverage(v);
/* Step 7: If we reach this point, we know that the result must
** be false. */
sqlite3VdbeAddOp2(v, OP_Goto, 0, destIfFalse);
Index: src/select.c
==================================================================
--- src/select.c
+++ src/select.c
@@ -5833,26 +5833,16 @@
/* Generate code for all sub-queries in the FROM clause
*/
pSub = pItem->pSelect;
if( pSub==0 ) continue;
- /* Sometimes the code for a subquery will be generated more than
- ** once, if the subquery is part of the WHERE clause in a LEFT JOIN,
- ** for example. In that case, do not regenerate the code to manifest
- ** a view or the co-routine to implement a view. The first instance
- ** is sufficient, though the subroutine to manifest the view does need
- ** to be invoked again. */
- if( pItem->addrFillSub ){
- if( pItem->fg.viaCoroutine==0 ){
- /* The subroutine that manifests the view might be a one-time routine,
- ** or it might need to be rerun on each iteration because it
- ** encodes a correlated subquery. */
- testcase( sqlite3VdbeGetOp(v, pItem->addrFillSub)->opcode==OP_Once );
- sqlite3VdbeAddOp2(v, OP_Gosub, pItem->regReturn, pItem->addrFillSub);
- }
- continue;
- }
+ /* The code for a subquery should only be generated once, though it is
+ ** technically harmless for it to be generated multiple times. The
+ ** following assert() will detect if something changes to cause
+ ** the same subquery to be coded multiple times, as a signal to the
+ ** developers to try to optimize the situation. */
+ assert( pItem->addrFillSub==0 );
/* Increment Parse.nHeight by the height of the largest expression
** tree referred to by this, the parent select. The child select
** may contain expression trees of at most
** (SQLITE_MAX_EXPR_DEPTH-Parse.nHeight) height. This is a bit
Index: src/sqliteInt.h
==================================================================
--- src/sqliteInt.h
+++ src/sqliteInt.h
@@ -2483,10 +2483,14 @@
AggInfo *pAggInfo; /* Used by TK_AGG_COLUMN and TK_AGG_FUNCTION */
union {
Table *pTab; /* TK_COLUMN: Table containing column. Can be NULL
** for a column of an index on an expression */
Window *pWin; /* TK_FUNCTION: Window definition for the func */
+ struct { /* TK_IN, TK_SELECT, and TK_EXISTS */
+ int iAddr; /* Subroutine entry address */
+ int regReturn; /* Register used to hold return address */
+ } sub;
} y;
};
/*
** The following are the meanings of bits in the Expr.flags field.
@@ -2514,10 +2518,11 @@
#define EP_CanBeNull 0x100000 /* Can be null despite NOT NULL constraint */
#define EP_Subquery 0x200000 /* Tree contains a TK_SELECT operator */
#define EP_Alias 0x400000 /* Is an alias for a result set column */
#define EP_Leaf 0x800000 /* Expr.pLeft, .pRight, .u.pSelect all NULL */
#define EP_WinFunc 0x1000000 /* TK_FUNCTION with Expr.y.pWin set */
+#define EP_Subrtn 0x2000000 /* Uses Expr.y.sub. TK_IN, _SELECT, or _EXISTS */
/*
** The EP_Propagate mask is a set of properties that automatically propagate
** upwards into parent nodes.
*/
@@ -4256,11 +4261,11 @@
void sqlite3AlterRenameTable(Parse*, SrcList*, Token*);
void sqlite3AlterRenameColumn(Parse*, SrcList*, Token*, Token*);
int sqlite3GetToken(const unsigned char *, int *);
void sqlite3NestedParse(Parse*, const char*, ...);
void sqlite3ExpirePreparedStatements(sqlite3*, int);
-void sqlite3CodeRhsOfIN(Parse*, Expr*, int);
+void sqlite3CodeRhsOfIN(Parse*, Expr*, int, int);
int sqlite3CodeSubselect(Parse*, Expr*);
void sqlite3SelectPrep(Parse*, Select*, NameContext*);
void sqlite3SelectWrongNumTermsError(Parse *pParse, Select *p);
int sqlite3MatchSpanName(const char*, const char*, const char*, const char*);
int sqlite3ResolveExprNames(NameContext*, Expr*);
@@ -4509,11 +4514,11 @@
** Allowed flags for the 3rd parameter to sqlite3FindInIndex().
*/
#define IN_INDEX_NOOP_OK 0x0001 /* OK to return IN_INDEX_NOOP */
#define IN_INDEX_MEMBERSHIP 0x0002 /* IN operator used for membership test */
#define IN_INDEX_LOOP 0x0004 /* IN operator used as a loop */
-int sqlite3FindInIndex(Parse *, Expr *, u32, int*, int*);
+int sqlite3FindInIndex(Parse *, Expr *, u32, int*, int*, int*);
int sqlite3JournalOpen(sqlite3_vfs *, const char *, sqlite3_file *, int, int);
int sqlite3JournalSize(sqlite3_vfs *);
#if defined(SQLITE_ENABLE_ATOMIC_WRITE) \
|| defined(SQLITE_ENABLE_BATCH_ATOMIC_WRITE)
Index: src/vdbe.c
==================================================================
--- src/vdbe.c
+++ src/vdbe.c
@@ -3608,11 +3608,12 @@
if( pCx==0 ) goto no_mem;
pCx->nullRow = 1;
pCx->isEphemeral = 1;
pCx->pKeyInfo = pOrig->pKeyInfo;
pCx->isTable = pOrig->isTable;
- rc = sqlite3BtreeCursor(pOrig->pBtx, MASTER_ROOT, BTREE_WRCSR,
+ pCx->pgnoRoot = pOrig->pgnoRoot;
+ rc = sqlite3BtreeCursor(pOrig->pBtx, pCx->pgnoRoot, BTREE_WRCSR,
pCx->pKeyInfo, pCx->uc.pCursor);
/* The sqlite3BtreeCursor() routine can only fail for the first cursor
** opened for a database. Since there is already an open cursor when this
** opcode is run, the sqlite3BtreeCursor() cannot fail */
assert( rc==SQLITE_OK );
Index: src/vdbe.h
==================================================================
--- src/vdbe.h
+++ src/vdbe.h
@@ -205,17 +205,24 @@
VdbeOp *sqlite3VdbeAddOpList(Vdbe*, int nOp, VdbeOpList const *aOp,int iLineno);
#ifndef SQLITE_OMIT_EXPLAIN
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)
#else
# 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);
void sqlite3VdbeChangeP1(Vdbe*, u32 addr, int P1);
void sqlite3VdbeChangeP2(Vdbe*, u32 addr, int P2);
Index: src/vdbeaux.c
==================================================================
--- src/vdbeaux.c
+++ src/vdbeaux.c
@@ -348,11 +348,22 @@
pOp = sqlite3VdbeGetOp(pParse->pVdbe, pParse->addrExplain);
return pOp->p2;
}
/*
-** 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, ...){
@@ -371,18 +382,22 @@
va_end(ap);
v = pParse->pVdbe;
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;
+ }
}
}
/*
** Pop the EXPLAIN QUERY PLAN stack one level.
*/
void sqlite3VdbeExplainPop(Parse *pParse){
+ sqlite3ExplainBreakpoint("POP", 0);
pParse->addrExplain = sqlite3VdbeExplainParent(pParse);
}
#endif /* SQLITE_OMIT_EXPLAIN */
/*
Index: src/where.c
==================================================================
--- src/where.c
+++ src/where.c
@@ -852,10 +852,11 @@
sqlite3VdbeChangeP2(v, addrCounter, regBase+n);
testcase( pParse->db->mallocFailed );
translateColumnToCopy(pParse, addrTop, pLevel->iTabCur,
pTabItem->regResult, 1);
sqlite3VdbeGoto(v, addrTop);
+ pTabItem->fg.viaCoroutine = 0;
}else{
sqlite3VdbeAddOp2(v, OP_Next, pLevel->iTabCur, addrTop+1); VdbeCoverage(v);
}
sqlite3VdbeChangeP5(v, SQLITE_STMTSTATUS_AUTOINDEX);
sqlite3VdbeJumpHere(v, addrTop);
@@ -5072,11 +5073,11 @@
#endif
addrExplain = sqlite3WhereExplainOneScan(
pParse, pTabList, pLevel, wctrlFlags
);
pLevel->addrBody = sqlite3VdbeCurrentAddr(v);
- notReady = sqlite3WhereCodeOneLoopStart(pWInfo, ii, notReady);
+ notReady = sqlite3WhereCodeOneLoopStart(pParse,v,pWInfo,ii,pLevel,notReady);
pWInfo->iContinue = pLevel->addrCont;
if( (wsFlags&WHERE_MULTI_OR)==0 && (wctrlFlags&WHERE_OR_SUBCLAUSE)==0 ){
sqlite3WhereAddScanStatus(v, pTabList, pLevel, addrExplain);
}
}
Index: src/whereInt.h
==================================================================
--- src/whereInt.h
+++ src/whereInt.h
@@ -505,12 +505,15 @@
);
#else
# define sqlite3WhereAddScanStatus(a, b, c, d) ((void)d)
#endif
Bitmask sqlite3WhereCodeOneLoopStart(
+ Parse *pParse, /* Parsing context */
+ Vdbe *v, /* Prepared statement under construction */
WhereInfo *pWInfo, /* Complete information about the WHERE clause */
int iLevel, /* Which level of pWInfo->a[] should be coded */
+ WhereLevel *pLevel, /* The current level pointer */
Bitmask notReady /* Which tables are currently available */
);
/* whereexpr.c: */
void sqlite3WhereClauseInit(WhereClause*,WhereInfo*);
Index: src/wherecode.c
==================================================================
--- src/wherecode.c
+++ src/wherecode.c
@@ -211,10 +211,11 @@
}else{
sqlite3_str_append(&str, " (~1 row)", 9);
}
#endif
zMsg = sqlite3StrAccumFinish(&str);
+ sqlite3ExplainBreakpoint("",zMsg);
ret = sqlite3VdbeAddOp4(v, OP_Explain, sqlite3VdbeCurrentAddr(v),
pParse->addrExplain, 0, zMsg,P4_DYNAMIC);
}
return ret;
}
@@ -536,30 +537,30 @@
for(i=iEq;inLTerm; i++){
assert( pLoop->aLTerm[i]!=0 );
if( pLoop->aLTerm[i]->pExpr==pX ) nEq++;
}
+ iTab = 0;
if( (pX->flags & EP_xIsSelect)==0 || pX->x.pSelect->pEList->nExpr==1 ){
- eType = sqlite3FindInIndex(pParse, pX, IN_INDEX_LOOP, 0, 0);
+ eType = sqlite3FindInIndex(pParse, pX, IN_INDEX_LOOP, 0, 0, &iTab);
}else{
sqlite3 *db = pParse->db;
pX = removeUnindexableInClauseTerms(pParse, iEq, pLoop, pX);
if( !db->mallocFailed ){
aiMap = (int*)sqlite3DbMallocZero(pParse->db, sizeof(int)*nEq);
- eType = sqlite3FindInIndex(pParse, pX, IN_INDEX_LOOP, 0, aiMap);
- pTerm->pExpr->iTable = pX->iTable;
+ eType = sqlite3FindInIndex(pParse, pX, IN_INDEX_LOOP, 0, aiMap, &iTab);
+ pTerm->pExpr->iTable = iTab;
}
sqlite3ExprDelete(db, pX);
pX = pTerm->pExpr;
}
if( eType==IN_INDEX_INDEX_DESC ){
testcase( bRev );
bRev = !bRev;
}
- iTab = pX->iTable;
sqlite3VdbeAddOp2(v, bRev ? OP_Last : OP_Rewind, iTab, 0);
VdbeCoverageIf(v, bRev);
VdbeCoverageIf(v, !bRev);
assert( (pLoop->wsFlags & WHERE_MULTI_OR)==0 );
@@ -1162,47 +1163,41 @@
/*
** Generate code for the start of the iLevel-th loop in the WHERE clause
** implementation described by pWInfo.
*/
Bitmask sqlite3WhereCodeOneLoopStart(
+ Parse *pParse, /* Parsing context */
+ Vdbe *v, /* Prepared statement under construction */
WhereInfo *pWInfo, /* Complete information about the WHERE clause */
int iLevel, /* Which level of pWInfo->a[] should be coded */
+ WhereLevel *pLevel, /* The current level pointer */
Bitmask notReady /* Which tables are currently available */
){
int j, k; /* Loop counters */
int iCur; /* The VDBE cursor for the table */
int addrNxt; /* Where to jump to continue with the next IN case */
- int omitTable; /* True if we use the index only */
int bRev; /* True if we need to scan in reverse order */
- WhereLevel *pLevel; /* The where level to be coded */
WhereLoop *pLoop; /* The WhereLoop object being coded */
WhereClause *pWC; /* Decomposition of the entire WHERE clause */
WhereTerm *pTerm; /* A WHERE clause term */
- Parse *pParse; /* Parsing context */
sqlite3 *db; /* Database connection */
- Vdbe *v; /* The prepared stmt under constructions */
struct SrcList_item *pTabItem; /* FROM clause term being coded */
int addrBrk; /* Jump here to break out of the loop */
int addrHalt; /* addrBrk for the outermost loop */
int addrCont; /* Jump here to continue with next cycle */
int iRowidReg = 0; /* Rowid is stored in this register, if not zero */
int iReleaseReg = 0; /* Temp register to free before returning */
Index *pIdx = 0; /* Index used by loop (if any) */
int iLoop; /* Iteration of constraint generator loop */
- pParse = pWInfo->pParse;
- v = pParse->pVdbe;
pWC = &pWInfo->sWC;
db = pParse->db;
- pLevel = &pWInfo->a[iLevel];
pLoop = pLevel->pWLoop;
pTabItem = &pWInfo->pTabList->a[pLevel->iFrom];
iCur = pTabItem->iCursor;
pLevel->notReady = notReady & ~sqlite3WhereGetMask(&pWInfo->sMaskSet, iCur);
bRev = (pWInfo->revMask>>iLevel)&1;
- omitTable = (pLoop->wsFlags & WHERE_IDX_ONLY)!=0
- && (pWInfo->wctrlFlags & WHERE_OR_SUBCLAUSE)==0;
VdbeModuleComment((v, "Begin WHERE-loop%d: %s",iLevel,pTabItem->pTab->zName));
/* Create labels for the "break" and "continue" instructions
** for the current loop. Jump to addrBrk to break out of a loop.
** Jump to cont to go immediately to the next iteration of the
@@ -1339,11 +1334,10 @@
*/
assert( pLoop->u.btree.nEq==1 );
pTerm = pLoop->aLTerm[0];
assert( pTerm!=0 );
assert( pTerm->pExpr!=0 );
- assert( omitTable==0 );
testcase( pTerm->wtFlags & TERM_VIRTUAL );
iReleaseReg = ++pParse->nMem;
iRowidReg = codeEqualityTerm(pParse, pTerm, pLevel, 0, bRev, iReleaseReg);
if( iRowidReg!=iReleaseReg ) sqlite3ReleaseTempReg(pParse, iReleaseReg);
addrNxt = pLevel->addrNxt;
@@ -1358,11 +1352,10 @@
int testOp = OP_Noop;
int start;
int memEndValue = 0;
WhereTerm *pStart, *pEnd;
- assert( omitTable==0 );
j = 0;
pStart = pEnd = 0;
if( pLoop->wsFlags & WHERE_BTM_LIMIT ) pStart = pLoop->aLTerm[j++];
if( pLoop->wsFlags & WHERE_TOP_LIMIT ) pEnd = pLoop->aLTerm[j++];
assert( pStart!=0 || pEnd!=0 );
@@ -1522,10 +1515,12 @@
int op; /* Instruction opcode */
char *zStartAff; /* Affinity for start of range constraint */
char *zEndAff = 0; /* Affinity for end of range constraint */
u8 bSeekPastNull = 0; /* True to seek past initial nulls */
u8 bStopAtNull = 0; /* Add condition to terminate at NULLs */
+ int omitTable; /* True if we use the index only */
+
pIdx = pLoop->u.btree.pIndex;
iIdxCur = pLevel->iIdxCur;
assert( nEq>=pLoop->nSkip );
@@ -1723,10 +1718,12 @@
if( pLoop->wsFlags & WHERE_IN_EARLYOUT ){
sqlite3VdbeAddOp2(v, OP_SeekHit, iIdxCur, 1);
}
/* Seek the table cursor, if required */
+ omitTable = (pLoop->wsFlags & WHERE_IDX_ONLY)!=0
+ && (pWInfo->wctrlFlags & WHERE_OR_SUBCLAUSE)==0;
if( omitTable ){
/* pIdx is a covering index. No need to access the main table. */
}else if( HasRowid(pIdx->pTable) ){
if( (pWInfo->wctrlFlags & WHERE_SEEK_TABLE) || (
(pWInfo->wctrlFlags & WHERE_SEEK_UNIQ_TABLE)
@@ -1949,10 +1946,11 @@
if( pAndExpr ){
pAndExpr->pLeft = pOrExpr;
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);
assert( pSubWInfo || pParse->nErr || db->mallocFailed );
if( pSubWInfo ){
@@ -2052,10 +2050,11 @@
pCov = 0;
}
/* Finish the loop through table entries that match term pOrTerm. */
sqlite3WhereEnd(pSubWInfo);
+ ExplainQueryPlanPop(pParse);
}
}
}
ExplainQueryPlanPop(pParse);
pLevel->u.pCovidx = pCov;
Index: test/autoindex1.test
==================================================================
--- test/autoindex1.test
+++ test/autoindex1.test
@@ -182,30 +182,30 @@
SELECT b FROM t501
WHERE t501.a IN (SELECT x FROM t502 WHERE y=?);
} {
QUERY PLAN
|--SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?)
- `--LIST SUBQUERY
+ `--LIST SUBQUERY xxxxxx
`--SCAN TABLE t502
}
do_eqp_test autoindex1-501 {
SELECT b FROM t501
WHERE t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
} {
QUERY PLAN
|--SCAN TABLE t501
- `--CORRELATED LIST SUBQUERY
+ `--CORRELATED LIST SUBQUERY xxxxxx
`--SEARCH TABLE t502 USING AUTOMATIC COVERING INDEX (y=?)
}
do_eqp_test autoindex1-502 {
SELECT b FROM t501
WHERE t501.a=123
AND t501.a IN (SELECT x FROM t502 WHERE y=t501.b);
} {
QUERY PLAN
|--SEARCH TABLE t501 USING INTEGER PRIMARY KEY (rowid=?)
- `--CORRELATED LIST SUBQUERY
+ `--CORRELATED LIST SUBQUERY xxxxxx
`--SCAN TABLE t502
}
# The following code checks a performance regression reported on the
# mailing list on 2010-10-19. The problem is that the nRowEst field
@@ -278,11 +278,11 @@
} {
QUERY PLAN
|--MATERIALIZE xxxxxx
| |--SCAN TABLE sheep AS s
| |--SEARCH TABLE flock_owner AS prev USING INDEX sqlite_autoindex_flock_owner_1 (flock_no=? AND owner_change_date? AND owner_change_date? AND x? AND x? AND b? AND b=950 AND b<=1010) OR (b IS NULL AND c NOT NULL)
ORDER BY a
} {
QUERY PLAN
|--MULTI-INDEX OR
- | |--SEARCH TABLE t1 USING INDEX t1b (b>? AND b? AND b?)
`--USE TEMP B-TREE FOR ORDER BY
}
finish_test
Index: test/where9.test
==================================================================
--- test/where9.test
+++ test/where9.test
@@ -362,23 +362,27 @@
WHERE t1.a=80 AND ((t1.c=t2.c AND t1.d=t2.d) OR t1.f=t2.f)
} [string map {"\n " \n} {
QUERY PLAN
|--SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)
`--MULTI-INDEX OR
- |--SEARCH TABLE t2 USING INDEX t2d (d=?)
- `--SEARCH TABLE t2 USING COVERING INDEX t2f (f=?)
+ |--INDEX 1
+ | `--SEARCH TABLE t2 USING INDEX t2d (d=?)
+ `--INDEX 3
+ `--SEARCH TABLE t2 USING COVERING INDEX t2f (f=?)
}]
do_eqp_test where9-3.2 {
SELECT coalesce(t2.a,9999)
FROM t1 LEFT JOIN t2 ON (t1.c+1=t2.c AND t1.d=t2.d) OR (t1.f||'x')=t2.f
WHERE t1.a=80
} [string map {"\n " \n} {
QUERY PLAN
|--SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)
`--MULTI-INDEX OR
- |--SEARCH TABLE t2 USING INDEX t2d (d=?)
- `--SEARCH TABLE t2 USING COVERING INDEX t2f (f=?)
+ |--INDEX 1
+ | `--SEARCH TABLE t2 USING INDEX t2d (d=?)
+ `--INDEX 2
+ `--SEARCH TABLE t2 USING COVERING INDEX t2f (f=?)
}]
}
# Make sure that INDEXED BY and multi-index OR clauses play well with
# one another.
@@ -454,12 +458,14 @@
do_eqp_test where9-5.1 {
SELECT a FROM t1 WHERE b>1000 AND (c=31031 OR d IS NULL)
} {
QUERY PLAN
`--MULTI-INDEX OR
- |--SEARCH TABLE t1 USING INDEX t1c (c=?)
- `--SEARCH TABLE t1 USING INDEX t1d (d=?)
+ |--INDEX 1
+ | `--SEARCH TABLE t1 USING INDEX t1c (c=?)
+ `--INDEX 2
+ `--SEARCH TABLE t1 USING INDEX t1d (d=?)
}
# In contrast, b=1000 is preferred over any OR-clause.
#
do_eqp_test where9-5.2 {
Index: test/whereI.test
==================================================================
--- test/whereI.test
+++ test/whereI.test
@@ -29,12 +29,14 @@
do_eqp_test 1.1 {
SELECT a FROM t1 WHERE b='b' OR c='x'
} {
QUERY PLAN
`--MULTI-INDEX OR
- |--SEARCH TABLE t1 USING INDEX i1 (b=?)
- `--SEARCH TABLE t1 USING INDEX i2 (c=?)
+ |--INDEX 1
+ | `--SEARCH TABLE t1 USING INDEX i1 (b=?)
+ `--INDEX 2
+ `--SEARCH TABLE t1 USING INDEX i2 (c=?)
}
do_execsql_test 1.2 {
SELECT a FROM t1 WHERE b='b' OR c='x'
} {2 3}
@@ -59,12 +61,14 @@
do_eqp_test 2.1 {
SELECT a FROM t2 WHERE b='b' OR c='x'
} {
QUERY PLAN
`--MULTI-INDEX OR
- |--SEARCH TABLE t2 USING INDEX i3 (b=?)
- `--SEARCH TABLE t2 USING INDEX i4 (c=?)
+ |--INDEX 1
+ | `--SEARCH TABLE t2 USING INDEX i3 (b=?)
+ `--INDEX 2
+ `--SEARCH TABLE t2 USING INDEX i4 (c=?)
}
do_execsql_test 2.2 {
SELECT a FROM t2 WHERE b='b' OR c='x'
} {ii iii}
Index: test/with3.test
==================================================================
--- test/with3.test
+++ test/with3.test
@@ -118,11 +118,11 @@
} {
QUERY PLAN
|--MATERIALIZE xxxxxx
| |--SETUP
| | |--SCAN CONSTANT ROW
- | | `--SCALAR SUBQUERY
+ | | `--SCALAR SUBQUERY xxxxxx
| | `--SCAN TABLE w2
| `--RECURSIVE STEP
| |--SCAN TABLE w1
| `--SCAN TABLE c
|--SCAN SUBQUERY xxxxxx