Index: src/expr.c ================================================================== --- src/expr.c +++ src/expr.c @@ -899,11 +899,12 @@ pNewItem->zDatabase = sqlite3DbStrDup(db, pOldItem->zDatabase); pNewItem->zName = sqlite3DbStrDup(db, pOldItem->zName); pNewItem->zAlias = sqlite3DbStrDup(db, pOldItem->zAlias); pNewItem->jointype = pOldItem->jointype; pNewItem->iCursor = pOldItem->iCursor; - pNewItem->isPopulated = pOldItem->isPopulated; + pNewItem->addrFillSub = pOldItem->addrFillSub; + pNewItem->regReturn = pOldItem->regReturn; pNewItem->isCorrelated = pOldItem->isCorrelated; pNewItem->zIndex = sqlite3DbStrDup(db, pOldItem->zIndex); pNewItem->notIndexed = pOldItem->notIndexed; pNewItem->pIndex = pOldItem->pIndex; pTab = pNewItem->pTab = pOldItem->pTab; @@ -1459,12 +1460,11 @@ assert(v); if( iCol<0 ){ int iMem = ++pParse->nMem; int iAddr; - iAddr = sqlite3VdbeAddOp1(v, OP_If, iMem); - sqlite3VdbeAddOp2(v, OP_Integer, 1, iMem); + iAddr = sqlite3VdbeAddOp1(v, OP_Once, iMem); sqlite3OpenTable(pParse, iTab, iDb, pTab, OP_OpenRead); eType = IN_INDEX_ROWID; sqlite3VdbeJumpHere(v, iAddr); @@ -1491,12 +1491,11 @@ int iMem = ++pParse->nMem; int iAddr; char *pKey; pKey = (char *)sqlite3IndexKeyinfo(pParse, pIdx); - iAddr = sqlite3VdbeAddOp1(v, OP_If, iMem); - sqlite3VdbeAddOp2(v, OP_Integer, 1, iMem); + iAddr = sqlite3VdbeAddOp1(v, OP_Once, iMem); sqlite3VdbeAddOp4(v, OP_OpenRead, iTab, pIdx->tnum, iDb, pKey,P4_KEYINFO_HANDOFF); VdbeComment((v, "%s", pIdx->zName)); eType = IN_INDEX_INDEX; @@ -1577,10 +1576,11 @@ ){ int testAddr = 0; /* One-time test address */ int rReg = 0; /* Register storing resulting */ Vdbe *v = sqlite3GetVdbe(pParse); if( NEVER(v==0) ) return 0; + assert( sqlite3VdbeCurrentAddr(v)>0 ); sqlite3ExprCachePush(pParse); /* This code must be run in its entirety every time it is encountered ** if any of the following is true: ** @@ -1591,12 +1591,11 @@ ** 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( !ExprHasAnyProperty(pExpr, EP_VarSelect) && !pParse->pTriggerTab ){ int mem = ++pParse->nMem; - sqlite3VdbeAddOp1(v, OP_If, mem); - testAddr = sqlite3VdbeAddOp2(v, OP_Integer, 1, mem); + testAddr = sqlite3VdbeAddOp1(v, OP_Once, mem); assert( testAddr>0 || pParse->db->mallocFailed ); } #ifndef SQLITE_OMIT_EXPLAIN if( pParse->explain==2 ){ @@ -1692,11 +1691,11 @@ ** 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( testAddr && !sqlite3ExprIsConstant(pE2) ){ - sqlite3VdbeChangeToNoop(v, testAddr-1, 2); + sqlite3VdbeChangeToNoop(v, testAddr); testAddr = 0; } /* Evaluate the expression and insert it into the temp table */ if( isRowid && sqlite3ExprIsInteger(pE2, &iValToIns) ){ @@ -1763,11 +1762,11 @@ break; } } if( testAddr ){ - sqlite3VdbeJumpHere(v, testAddr-1); + sqlite3VdbeJumpHere(v, testAddr); } sqlite3ExprCachePop(pParse, 1); return rReg; } Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -3799,11 +3799,15 @@ struct SrcList_item *pItem = &pTabList->a[i]; SelectDest dest; Select *pSub = pItem->pSelect; int isAggSub; - if( pSub==0 || pItem->isPopulated ) continue; + if( pSub==0 ) continue; + if( pItem->addrFillSub ){ + sqlite3VdbeAddOp2(v, OP_Gosub, pItem->regReturn, pItem->addrFillSub); + continue; + } /* Increment Parse.nHeight by the height of the largest expression ** tree refered 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 @@ -3810,25 +3814,44 @@ ** more conservative than necessary, but much easier than enforcing ** an exact limit. */ pParse->nHeight += sqlite3SelectExprHeight(p); - /* Check to see if the subquery can be absorbed into the parent. */ isAggSub = (pSub->selFlags & SF_Aggregate)!=0; if( flattenSubquery(pParse, p, i, isAgg, isAggSub) ){ + /* This subquery can be absorbed into its parent. */ if( isAggSub ){ isAgg = 1; p->selFlags |= SF_Aggregate; } i = -1; }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 = sqlite3VdbeAddOp0(v, OP_Goto); + int onceAddr = 0; + assert( pItem->addrFillSub==0 ); + pItem->addrFillSub = topAddr+1; + pItem->regReturn = ++pParse->nMem; + if( pItem->isCorrelated==0 && pParse->pTriggerTab==0 ){ + /* If the subquery is no correlated and if we are not inside of + ** a trigger, then we only need to compute the value of the subquery + ** once. */ + int regOnce = ++pParse->nMem; + onceAddr = sqlite3VdbeAddOp1(v, OP_Once, regOnce); + } sqlite3SelectDestInit(&dest, SRT_EphemTab, pItem->iCursor); - assert( pItem->isPopulated==0 ); explainSetInteger(pItem->iSelectId, (u8)pParse->iNextSelectId); sqlite3Select(pParse, pSub, &dest); - pItem->isPopulated = 1; pItem->pTab->nRowEst = (unsigned)pSub->nSelectRow; + if( onceAddr ) sqlite3VdbeJumpHere(v, onceAddr); + sqlite3VdbeAddOp1(v, OP_Return, pItem->regReturn); + sqlite3VdbeJumpHere(v, topAddr); + sqlite3VdbeAddOp2(v, OP_Gosub, pItem->regReturn, topAddr+1); } if( /*pParse->nErr ||*/ db->mallocFailed ){ goto select_end; } pParse->nHeight -= sqlite3SelectExprHeight(p); @@ -3965,11 +3988,11 @@ /* If sorting index that was created by a prior OP_OpenEphemeral ** instruction ended up not being needed, then change the OP_OpenEphemeral ** into an OP_Noop. */ if( addrSortIndex>=0 && pOrderBy==0 ){ - sqlite3VdbeChangeToNoop(v, addrSortIndex, 1); + sqlite3VdbeChangeToNoop(v, addrSortIndex); p->addrOpenEphm[2] = -1; } if( pWInfo->eDistinct ){ VdbeOp *pOp; /* No longer required OpenEphemeral instr. */ @@ -4248,11 +4271,11 @@ */ if( groupBySort ){ sqlite3VdbeAddOp2(v, OP_SorterNext, sAggInfo.sortingIdx, addrTopOfLoop); }else{ sqlite3WhereEnd(pWInfo); - sqlite3VdbeChangeToNoop(v, addrSortingIdx, 1); + sqlite3VdbeChangeToNoop(v, addrSortingIdx); } /* Output the final row of result */ sqlite3VdbeAddOp2(v, OP_Gosub, regOutputRow, addrOutputRow); Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -1850,11 +1850,12 @@ char *zDatabase; /* Name of database holding this table */ char *zName; /* Name of the table */ char *zAlias; /* The "B" part of a "A AS B" phrase. zName is the "A" */ Table *pTab; /* An SQL table corresponding to zName */ Select *pSelect; /* A SELECT statement used in place of a table name */ - u8 isPopulated; /* Temporary table associated with SELECT is populated */ + int addrFillSub; /* Address of subroutine to manifest a subquery */ + int regReturn; /* Register holding return address of addrFillSub */ u8 jointype; /* Type of join between this able and the previous */ u8 notIndexed; /* True if there is a NOT INDEXED clause */ u8 isCorrelated; /* True if sub-query is correlated */ #ifndef SQLITE_OMIT_EXPLAIN u8 iSelectId; /* If pSelect!=0, the id of the sub-select in EQP */ Index: src/vdbe.c ================================================================== --- src/vdbe.c +++ src/vdbe.c @@ -2019,10 +2019,20 @@ sqlite3VdbeMemSetInt64(pOut, ~sqlite3VdbeIntValue(pIn1)); } break; } +/* Opcode: Once P1 P2 * * * +** +** Jump to P2 if the value in register P1 is a not null or zero. If +** the value is NULL or zero, fall through and change the P1 register +** to an integer 1. +** +** When P1 is not used otherwise in a program, this opcode falls through +** once and jumps on all subsequent invocations. It is the equivalent +** of "OP_If P1 P2", followed by "OP_Integer 1 P1". +*/ /* Opcode: If P1 P2 P3 * * ** ** Jump to P2 if the value in register P1 is true. The value ** is considered true if it is numeric and non-zero. If the value ** in P1 is NULL then take the jump if P3 is true. @@ -2031,10 +2041,11 @@ ** ** Jump to P2 if the value in register P1 is False. The value ** is considered true if it has a numeric value of zero. If the value ** in P1 is NULL then take the jump if P3 is true. */ +case OP_Once: /* jump, in1 */ case OP_If: /* jump, in1 */ case OP_IfNot: { /* jump, in1 */ int c; pIn1 = &aMem[pOp->p1]; if( pIn1->flags & MEM_Null ){ @@ -2047,10 +2058,16 @@ #endif if( pOp->opcode==OP_IfNot ) c = !c; } if( c ){ pc = pOp->p2-1; + }else if( pOp->opcode==OP_Once ){ + assert( (pIn1->flags & (MEM_Agg|MEM_Dyn|MEM_RowSet|MEM_Frame))==0 ); + memAboutToChange(p, pIn1); + pIn1->flags = MEM_Int; + pIn1->u.i = 1; + REGISTER_TRACE(pOp->p1, pIn1); } break; } /* Opcode: IsNull P1 P2 * * * Index: src/vdbe.h ================================================================== --- src/vdbe.h +++ src/vdbe.h @@ -178,11 +178,11 @@ void sqlite3VdbeChangeP1(Vdbe*, u32 addr, int P1); void sqlite3VdbeChangeP2(Vdbe*, u32 addr, int P2); void sqlite3VdbeChangeP3(Vdbe*, u32 addr, int P3); void sqlite3VdbeChangeP5(Vdbe*, u8 P5); void sqlite3VdbeJumpHere(Vdbe*, int addr); -void sqlite3VdbeChangeToNoop(Vdbe*, int addr, int N); +void sqlite3VdbeChangeToNoop(Vdbe*, int addr); void sqlite3VdbeChangeP4(Vdbe*, int addr, const char *zP4, int N); void sqlite3VdbeUsesBtree(Vdbe*, int); VdbeOp *sqlite3VdbeGetOp(Vdbe*, int); int sqlite3VdbeMakeLabel(Vdbe*); void sqlite3VdbeRunOnlyOnce(Vdbe*); Index: src/vdbeaux.c ================================================================== --- src/vdbeaux.c +++ src/vdbeaux.c @@ -668,22 +668,19 @@ p->pNext = pVdbe->pProgram; pVdbe->pProgram = p; } /* -** Change N opcodes starting at addr to No-ops. +** Change the opcode at addr into OP_Noop */ -void sqlite3VdbeChangeToNoop(Vdbe *p, int addr, int N){ +void sqlite3VdbeChangeToNoop(Vdbe *p, int addr){ if( p->aOp ){ VdbeOp *pOp = &p->aOp[addr]; sqlite3 *db = p->db; - while( N-- ){ - freeP4(db, pOp->p4type, pOp->p4.p); - memset(pOp, 0, sizeof(pOp[0])); - pOp->opcode = OP_Noop; - pOp++; - } + freeP4(db, pOp->p4type, pOp->p4.p); + memset(pOp, 0, sizeof(pOp[0])); + pOp->opcode = OP_Noop; } } /* ** Change the value of the P4 operand for a specific instruction. Index: src/vdbeblob.c ================================================================== --- src/vdbeblob.c +++ src/vdbeblob.c @@ -271,21 +271,21 @@ /* Make sure a mutex is held on the table to be accessed */ sqlite3VdbeUsesBtree(v, iDb); /* Configure the OP_TableLock instruction */ #ifdef SQLITE_OMIT_SHARED_CACHE - sqlite3VdbeChangeToNoop(v, 2, 1); + sqlite3VdbeChangeToNoop(v, 2); #else sqlite3VdbeChangeP1(v, 2, iDb); sqlite3VdbeChangeP2(v, 2, pTab->tnum); sqlite3VdbeChangeP3(v, 2, flags); sqlite3VdbeChangeP4(v, 2, pTab->zName, P4_TRANSIENT); #endif /* Remove either the OP_OpenWrite or OpenRead. Set the P2 ** parameter of the other to pTab->tnum. */ - sqlite3VdbeChangeToNoop(v, 4 - flags, 1); + sqlite3VdbeChangeToNoop(v, 4 - flags); sqlite3VdbeChangeP2(v, 3 + flags, pTab->tnum); sqlite3VdbeChangeP3(v, 3 + flags, iDb); /* Configure the number of columns. Configure the cursor to ** think that the table has one more column than it really Index: src/where.c ================================================================== --- src/where.c +++ src/where.c @@ -1992,12 +1992,11 @@ /* Generate code to skip over the creation and initialization of the ** transient index on 2nd and subsequent iterations of the loop. */ v = pParse->pVdbe; assert( v!=0 ); regIsInit = ++pParse->nMem; - addrInit = sqlite3VdbeAddOp1(v, OP_If, regIsInit); - sqlite3VdbeAddOp2(v, OP_Integer, 1, regIsInit); + addrInit = sqlite3VdbeAddOp1(v, OP_Once, regIsInit); /* Count the number of columns that will be added to the index ** and used to match WHERE clause constraints */ nColumn = 0; pTable = pSrc->pTab; Index: test/tkt-31338dca7e.test ================================================================== --- test/tkt-31338dca7e.test +++ test/tkt-31338dca7e.test @@ -71,7 +71,45 @@ WHERE (v=111 AND x=w AND z!=999) OR (v=333 AND x=444) ORDER BY v, w, x, y, z; } } {111 222 222 333 888 333 444 444 555 888 333 444 444 555 999} + +# Ticket [2c2de252666662f5459904fc33a9f2956cbff23c] +# +do_test tkt-31338-3.1 { + foreach x [db eval {SELECT name FROM sqlite_master WHERE type='table'}] { + db eval "DROP TABLE $x" + } + db eval { + CREATE TABLE t1(a,b,c,d); + CREATE TABLE t2(e,f); + INSERT INTO t1 VALUES(1,2,3,4); + INSERT INTO t2 VALUES(10,-8); + CREATE INDEX t1a ON t1(a); + CREATE INDEX t1b ON t1(b); + CREATE TABLE t3(g); + INSERT INTO t3 VALUES(4); + CREATE TABLE t4(h); + INSERT INTO t4 VALUES(5); + + SELECT * FROM t3 LEFT JOIN t1 ON d=g LEFT JOIN t4 ON c=h + WHERE (a=1 AND h=4) + OR (b IN ( + SELECT x FROM (SELECT e+f AS x, e FROM t2 ORDER BY 1 LIMIT 2) + GROUP BY e + )); + } +} {4 1 2 3 4 {}} +do_test tkt-31338-3.2 { + db eval { + SELECT * FROM t3 LEFT JOIN t1 ON d=g LEFT JOIN t4 ON c=h + WHERE (a=1 AND h=4) + OR (b=2 AND b NOT IN ( + SELECT x+1 FROM (SELECT e+f AS x, e FROM t2 ORDER BY 1 LIMIT 2) + GROUP BY e + )); + } +} {4 1 2 3 4 {}} + finish_test