diff options
Diffstat (limited to 'src/where.c')
| -rw-r--r-- | src/where.c | 980 |
1 files changed, 744 insertions, 236 deletions
diff --git a/src/where.c b/src/where.c index 4ff2815ba..2b835009d 100644 --- a/src/where.c +++ b/src/where.c @@ -303,6 +303,42 @@ static Expr *whereRightSubexprIsColumn(Expr *p){ } /* +** Term pTerm is guaranteed to be a WO_IN term. It may be a component term +** of a vector IN expression of the form "(x, y, ...) IN (SELECT ...)". +** This function checks to see if the term is compatible with an index +** column with affinity idxaff (one of the SQLITE_AFF_XYZ values). If so, +** it returns a pointer to the name of the collation sequence (e.g. "BINARY" +** or "NOCASE") used by the comparison in pTerm. If it is not compatible +** with affinity idxaff, NULL is returned. +*/ +static SQLITE_NOINLINE const char *indexInAffinityOk( + Parse *pParse, + WhereTerm *pTerm, + u8 idxaff +){ + Expr *pX = pTerm->pExpr; + Expr inexpr; + + assert( pTerm->eOperator & WO_IN ); + + if( sqlite3ExprIsVector(pX->pLeft) ){ + int iField = pTerm->u.x.iField - 1; + inexpr.flags = 0; + inexpr.op = TK_EQ; + inexpr.pLeft = pX->pLeft->x.pList->a[iField].pExpr; + assert( ExprUseXSelect(pX) ); + inexpr.pRight = pX->x.pSelect->pEList->a[iField].pExpr; + pX = &inexpr; + } + + if( sqlite3IndexAffinityOk(pX, idxaff) ){ + CollSeq *pRet = sqlite3ExprCompareCollSeq(pParse, pX); + return pRet ? pRet->zName : sqlite3StrBINARY; + } + return 0; +} + +/* ** Advance to the next WhereTerm that matches according to the criteria ** established when the pScan object was initialized by whereScanInit(). ** Return NULL if there are no more matching WhereTerms. @@ -352,16 +388,24 @@ static WhereTerm *whereScanNext(WhereScan *pScan){ if( (pTerm->eOperator & pScan->opMask)!=0 ){ /* Verify the affinity and collating sequence match */ if( pScan->zCollName && (pTerm->eOperator & WO_ISNULL)==0 ){ - CollSeq *pColl; + const char *zCollName; Parse *pParse = pWC->pWInfo->pParse; pX = pTerm->pExpr; - if( !sqlite3IndexAffinityOk(pX, pScan->idxaff) ){ - continue; + + if( (pTerm->eOperator & WO_IN) ){ + zCollName = indexInAffinityOk(pParse, pTerm, pScan->idxaff); + if( !zCollName ) continue; + }else{ + CollSeq *pColl; + if( !sqlite3IndexAffinityOk(pX, pScan->idxaff) ){ + continue; + } + assert(pX->pLeft); + pColl = sqlite3ExprCompareCollSeq(pParse, pX); + zCollName = pColl ? pColl->zName : sqlite3StrBINARY; } - assert(pX->pLeft); - pColl = sqlite3ExprCompareCollSeq(pParse, pX); - if( pColl==0 ) pColl = pParse->db->pDfltColl; - if( sqlite3StrICmp(pColl->zName, pScan->zCollName) ){ + + if( sqlite3StrICmp(zCollName, pScan->zCollName) ){ continue; } } @@ -600,7 +644,7 @@ static int isDistinctRedundant( ** clause is redundant. */ if( pTabList->nSrc!=1 ) return 0; iBase = pTabList->a[0].iCursor; - pTab = pTabList->a[0].pTab; + pTab = pTabList->a[0].pSTab; /* If any of the expressions is an IPK column on table iBase, then return ** true. Note: The (p->iTable==iBase) part of this test may be false if the @@ -675,6 +719,12 @@ static void translateColumnToCopy( VdbeOp *pOp = sqlite3VdbeGetOp(v, iStart); int iEnd = sqlite3VdbeCurrentAddr(v); if( pParse->db->mallocFailed ) return; +#ifdef SQLITE_DEBUG + if( pParse->db->flags & SQLITE_VdbeAddopTrace ){ + printf("CHECKING for column-to-copy on cursor %d for %d..%d\n", + iTabCur, iStart, iEnd); + } +#endif for(; iStart<iEnd; iStart++, pOp++){ if( pOp->p1!=iTabCur ) continue; if( pOp->opcode==OP_Column ){ @@ -713,9 +763,13 @@ static void translateColumnToCopy( ** are no-ops. */ #if !defined(SQLITE_OMIT_VIRTUALTABLE) && defined(WHERETRACE_ENABLED) -static void whereTraceIndexInfoInputs(sqlite3_index_info *p){ +static void whereTraceIndexInfoInputs( + sqlite3_index_info *p, /* The IndexInfo object */ + Table *pTab /* The TABLE that is the virtual table */ +){ int i; if( (sqlite3WhereTrace & 0x10)==0 ) return; + sqlite3DebugPrintf("sqlite3_index_info inputs for %s:\n", pTab->zName); for(i=0; i<p->nConstraint; i++){ sqlite3DebugPrintf( " constraint[%d]: col=%d termid=%d op=%d usabled=%d collseq=%s\n", @@ -733,9 +787,13 @@ static void whereTraceIndexInfoInputs(sqlite3_index_info *p){ p->aOrderBy[i].desc); } } -static void whereTraceIndexInfoOutputs(sqlite3_index_info *p){ +static void whereTraceIndexInfoOutputs( + sqlite3_index_info *p, /* The IndexInfo object */ + Table *pTab /* The TABLE that is the virtual table */ +){ int i; if( (sqlite3WhereTrace & 0x10)==0 ) return; + sqlite3DebugPrintf("sqlite3_index_info outputs for %s:\n", pTab->zName); for(i=0; i<p->nConstraint; i++){ sqlite3DebugPrintf(" usage[%d]: argvIdx=%d omit=%d\n", i, @@ -749,8 +807,8 @@ static void whereTraceIndexInfoOutputs(sqlite3_index_info *p){ sqlite3DebugPrintf(" estimatedRows=%lld\n", p->estimatedRows); } #else -#define whereTraceIndexInfoInputs(A) -#define whereTraceIndexInfoOutputs(A) +#define whereTraceIndexInfoInputs(A,B) +#define whereTraceIndexInfoOutputs(A,B) #endif /* @@ -788,6 +846,40 @@ static int constraintCompatibleWithOuterJoin( return 1; } +#ifndef SQLITE_OMIT_AUTOMATIC_INDEX +/* +** Return true if column iCol of table pTab seem like it might be a +** good column to use as part of a query-time index. +** +** Current algorithm (subject to improvement!): +** +** 1. If iCol is already the left-most column of some other index, +** then return false. +** +** 2. If iCol is part of an existing index that has an aiRowLogEst of +** more than 20, then return false. +** +** 3. If no disqualifying conditions above are found, return true. +*/ +static SQLITE_NOINLINE int columnIsGoodIndexCandidate( + const Table *pTab, + int iCol +){ + const Index *pIdx; + for(pIdx = pTab->pIndex; pIdx!=0; pIdx=pIdx->pNext){ + int j; + for(j=0; j<pIdx->nKeyCol; j++){ + if( pIdx->aiColumn[j]==iCol ){ + if( j==0 ) return 0; + if( pIdx->hasStat1 && pIdx->aiRowLogEst[j+1]>20 ) return 0; + break; + } + } + } + return 1; +} +#endif /* SQLITE_OMIT_AUTOMATIC_INDEX */ + #ifndef SQLITE_OMIT_AUTOMATIC_INDEX @@ -802,6 +894,8 @@ static int termCanDriveIndex( const Bitmask notReady /* Tables in outer loops of the join */ ){ char aff; + int leftCol; + if( pTerm->leftCursor!=pSrc->iCursor ) return 0; if( (pTerm->eOperator & (WO_EQ|WO_IS))==0 ) return 0; assert( (pSrc->fg.jointype & JT_RIGHT)==0 ); @@ -812,11 +906,12 @@ static int termCanDriveIndex( } if( (pTerm->prereqRight & notReady)!=0 ) return 0; assert( (pTerm->eOperator & (WO_OR|WO_AND))==0 ); - if( pTerm->u.x.leftColumn<0 ) return 0; - aff = pSrc->pTab->aCol[pTerm->u.x.leftColumn].affinity; + leftCol = pTerm->u.x.leftColumn; + if( leftCol<0 ) return 0; + aff = pSrc->pSTab->aCol[leftCol].affinity; if( !sqlite3IndexAffinityOk(pTerm->pExpr, aff) ) return 0; testcase( pTerm->pExpr->op==TK_IS ); - return 1; + return columnIsGoodIndexCandidate(pSrc->pSTab, leftCol); } #endif @@ -924,7 +1019,7 @@ static SQLITE_NOINLINE void constructAutomaticIndex( nKeyCol = 0; pTabList = pWC->pWInfo->pTabList; pSrc = &pTabList->a[pLevel->iFrom]; - pTable = pSrc->pTab; + pTable = pSrc->pSTab; pWCEnd = &pWC->a[pWC->nTerm]; pLoop = pLevel->pWLoop; idxCols = 0; @@ -934,7 +1029,7 @@ static SQLITE_NOINLINE void constructAutomaticIndex( ** WHERE clause (or the ON clause of a LEFT join) that constrain which ** rows of the target table (pSrc) that can be used. */ if( (pTerm->wtFlags & TERM_VIRTUAL)==0 - && sqlite3ExprIsSingleTableConstraint(pExpr, pTabList, pLevel->iFrom) + && sqlite3ExprIsSingleTableConstraint(pExpr, pTabList, pLevel->iFrom, 0) ){ pPartial = sqlite3ExprAnd(pParse, pPartial, sqlite3ExprDup(pParse->db, pExpr, 0)); @@ -976,7 +1071,7 @@ static SQLITE_NOINLINE void constructAutomaticIndex( ** if they go out of sync. */ if( IsView(pTable) ){ - extraCols = ALLBITS; + extraCols = ALLBITS & ~idxCols; }else{ extraCols = pSrc->colUsed & (~idxCols | MASKBIT(BMS-1)); } @@ -1066,12 +1161,17 @@ static SQLITE_NOINLINE void constructAutomaticIndex( /* Fill the automatic index with content */ assert( pSrc == &pWC->pWInfo->pTabList->a[pLevel->iFrom] ); if( pSrc->fg.viaCoroutine ){ - int regYield = pSrc->regReturn; + int regYield; + Subquery *pSubq; + assert( pSrc->fg.isSubquery ); + pSubq = pSrc->u4.pSubq; + assert( pSubq!=0 ); + regYield = pSubq->regReturn; addrCounter = sqlite3VdbeAddOp2(v, OP_Integer, 0, 0); - sqlite3VdbeAddOp3(v, OP_InitCoroutine, regYield, 0, pSrc->addrFillSub); + sqlite3VdbeAddOp3(v, OP_InitCoroutine, regYield, 0, pSubq->addrFillSub); addrTop = sqlite3VdbeAddOp1(v, OP_Yield, regYield); VdbeCoverage(v); - VdbeComment((v, "next row of %s", pSrc->pTab->zName)); + VdbeComment((v, "next row of %s", pSrc->pSTab->zName)); }else{ addrTop = sqlite3VdbeAddOp1(v, OP_Rewind, pLevel->iTabCur); VdbeCoverage(v); } @@ -1093,11 +1193,12 @@ static SQLITE_NOINLINE void constructAutomaticIndex( sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT); if( pPartial ) sqlite3VdbeResolveLabel(v, iContinue); if( pSrc->fg.viaCoroutine ){ + assert( pSrc->fg.isSubquery && pSrc->u4.pSubq!=0 ); sqlite3VdbeChangeP2(v, addrCounter, regBase+n); testcase( pParse->db->mallocFailed ); assert( pLevel->iIdxCur>0 ); translateColumnToCopy(pParse, addrTop, pLevel->iTabCur, - pSrc->regResult, pLevel->iIdxCur); + pSrc->u4.pSubq->regResult, pLevel->iIdxCur); sqlite3VdbeGoto(v, addrTop); pSrc->fg.viaCoroutine = 0; }else{ @@ -1188,7 +1289,7 @@ static SQLITE_NOINLINE void sqlite3ConstructBloomFilter( iSrc = pLevel->iFrom; pItem = &pTabList->a[iSrc]; assert( pItem!=0 ); - pTab = pItem->pTab; + pTab = pItem->pSTab; assert( pTab!=0 ); sz = sqlite3LogEstToInt(pTab->nRowLogEst); if( sz<10000 ){ @@ -1203,7 +1304,7 @@ static SQLITE_NOINLINE void sqlite3ConstructBloomFilter( for(pTerm=pWInfo->sWC.a; pTerm<pWCEnd; pTerm++){ Expr *pExpr = pTerm->pExpr; if( (pTerm->wtFlags & TERM_VIRTUAL)==0 - && sqlite3ExprIsSingleTableConstraint(pExpr, pTabList, iSrc) + && sqlite3ExprIsSingleTableConstraint(pExpr, pTabList, iSrc, 0) ){ sqlite3ExprIfFalse(pParse, pTerm->pExpr, addrCont, SQLITE_JUMPIFNULL); } @@ -1219,7 +1320,7 @@ static SQLITE_NOINLINE void sqlite3ConstructBloomFilter( int r1 = sqlite3GetTempRange(pParse, n); int jj; for(jj=0; jj<n; jj++){ - assert( pIdx->pTable==pItem->pTab ); + assert( pIdx->pTable==pItem->pSTab ); sqlite3ExprCodeLoadIndexColumn(pParse, pIdx, iCur, jj, r1+jj); } sqlite3VdbeAddOp4Int(v, OP_FilterAdd, pLevel->regFilter, 0, r1, n); @@ -1258,6 +1359,20 @@ static SQLITE_NOINLINE void sqlite3ConstructBloomFilter( #ifndef SQLITE_OMIT_VIRTUALTABLE /* +** Return term iTerm of the WhereClause passed as the first argument. Terms +** are numbered from 0 upwards, starting with the terms in pWC->a[], then +** those in pWC->pOuter->a[] (if any), and so on. +*/ +static WhereTerm *termFromWhereClause(WhereClause *pWC, int iTerm){ + WhereClause *p; + for(p=pWC; p; p=p->pOuter){ + if( iTerm<p->nTerm ) return &p->a[iTerm]; + iTerm -= p->nTerm; + } + return 0; +} + +/* ** Allocate and populate an sqlite3_index_info structure. It is the ** responsibility of the caller to eventually release the structure ** by passing the pointer returned by this function to freeIndexInfo(). @@ -1283,9 +1398,10 @@ static sqlite3_index_info *allocateIndexInfo( const Table *pTab; int eDistinct = 0; ExprList *pOrderBy = pWInfo->pOrderBy; + WhereClause *p; assert( pSrc!=0 ); - pTab = pSrc->pTab; + pTab = pSrc->pSTab; assert( pTab!=0 ); assert( IsVirtual(pTab) ); @@ -1293,28 +1409,30 @@ static sqlite3_index_info *allocateIndexInfo( ** Mark each term with the TERM_OK flag. Set nTerm to the number of ** terms found. */ - for(i=nTerm=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){ - pTerm->wtFlags &= ~TERM_OK; - if( pTerm->leftCursor != pSrc->iCursor ) continue; - if( pTerm->prereqRight & mUnusable ) continue; - assert( IsPowerOfTwo(pTerm->eOperator & ~WO_EQUIV) ); - testcase( pTerm->eOperator & WO_IN ); - testcase( pTerm->eOperator & WO_ISNULL ); - testcase( pTerm->eOperator & WO_IS ); - testcase( pTerm->eOperator & WO_ALL ); - if( (pTerm->eOperator & ~(WO_EQUIV))==0 ) continue; - if( pTerm->wtFlags & TERM_VNULL ) continue; + for(p=pWC, nTerm=0; p; p=p->pOuter){ + for(i=0, pTerm=p->a; i<p->nTerm; i++, pTerm++){ + pTerm->wtFlags &= ~TERM_OK; + if( pTerm->leftCursor != pSrc->iCursor ) continue; + if( pTerm->prereqRight & mUnusable ) continue; + assert( IsPowerOfTwo(pTerm->eOperator & ~WO_EQUIV) ); + testcase( pTerm->eOperator & WO_IN ); + testcase( pTerm->eOperator & WO_ISNULL ); + testcase( pTerm->eOperator & WO_IS ); + testcase( pTerm->eOperator & WO_ALL ); + if( (pTerm->eOperator & ~(WO_EQUIV))==0 ) continue; + if( pTerm->wtFlags & TERM_VNULL ) continue; - assert( (pTerm->eOperator & (WO_OR|WO_AND))==0 ); - assert( pTerm->u.x.leftColumn>=XN_ROWID ); - assert( pTerm->u.x.leftColumn<pTab->nCol ); - if( (pSrc->fg.jointype & (JT_LEFT|JT_LTORJ|JT_RIGHT))!=0 - && !constraintCompatibleWithOuterJoin(pTerm,pSrc) - ){ - continue; + assert( (pTerm->eOperator & (WO_OR|WO_AND))==0 ); + assert( pTerm->u.x.leftColumn>=XN_ROWID ); + assert( pTerm->u.x.leftColumn<pTab->nCol ); + if( (pSrc->fg.jointype & (JT_LEFT|JT_LTORJ|JT_RIGHT))!=0 + && !constraintCompatibleWithOuterJoin(pTerm,pSrc) + ){ + continue; + } + nTerm++; + pTerm->wtFlags |= TERM_OK; } - nTerm++; - pTerm->wtFlags |= TERM_OK; } /* If the ORDER BY clause contains only columns in the current @@ -1329,7 +1447,7 @@ static sqlite3_index_info *allocateIndexInfo( Expr *pE2; /* Skip over constant terms in the ORDER BY clause */ - if( sqlite3ExprIsConstant(pExpr) ){ + if( sqlite3ExprIsConstant(0, pExpr) ){ continue; } @@ -1364,7 +1482,7 @@ static sqlite3_index_info *allocateIndexInfo( } if( i==n ){ nOrderBy = n; - if( (pWInfo->wctrlFlags & WHERE_DISTINCTBY) ){ + if( (pWInfo->wctrlFlags & WHERE_DISTINCTBY) && !pSrc->fg.rowidUsed ){ eDistinct = 2 + ((pWInfo->wctrlFlags & WHERE_SORTBYGROUP)!=0); }else if( pWInfo->wctrlFlags & WHERE_GROUPBY ){ eDistinct = 1; @@ -1389,59 +1507,75 @@ static sqlite3_index_info *allocateIndexInfo( pIdxInfo->aConstraint = pIdxCons; pIdxInfo->aOrderBy = pIdxOrderBy; pIdxInfo->aConstraintUsage = pUsage; + pIdxInfo->colUsed = (sqlite3_int64)pSrc->colUsed; + if( HasRowid(pTab)==0 ){ + /* Ensure that all bits associated with PK columns are set. This is to + ** ensure they are available for cases like RIGHT joins or OR loops. */ + Index *pPk = sqlite3PrimaryKeyIndex((Table*)pTab); + assert( pPk!=0 ); + for(i=0; i<pPk->nKeyCol; i++){ + int iCol = pPk->aiColumn[i]; + assert( iCol>=0 ); + if( iCol>=BMS-1 ) iCol = BMS-1; + pIdxInfo->colUsed |= MASKBIT(iCol); + } + } pHidden->pWC = pWC; pHidden->pParse = pParse; pHidden->eDistinct = eDistinct; pHidden->mIn = 0; - for(i=j=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){ - u16 op; - if( (pTerm->wtFlags & TERM_OK)==0 ) continue; - pIdxCons[j].iColumn = pTerm->u.x.leftColumn; - pIdxCons[j].iTermOffset = i; - op = pTerm->eOperator & WO_ALL; - if( op==WO_IN ){ - if( (pTerm->wtFlags & TERM_SLICE)==0 ){ - pHidden->mIn |= SMASKBIT32(j); + for(p=pWC, i=j=0; p; p=p->pOuter){ + int nLast = i+p->nTerm;; + for(pTerm=p->a; i<nLast; i++, pTerm++){ + u16 op; + if( (pTerm->wtFlags & TERM_OK)==0 ) continue; + pIdxCons[j].iColumn = pTerm->u.x.leftColumn; + pIdxCons[j].iTermOffset = i; + op = pTerm->eOperator & WO_ALL; + if( op==WO_IN ){ + if( (pTerm->wtFlags & TERM_SLICE)==0 ){ + pHidden->mIn |= SMASKBIT32(j); + } + op = WO_EQ; } - op = WO_EQ; - } - if( op==WO_AUX ){ - pIdxCons[j].op = pTerm->eMatchOp; - }else if( op & (WO_ISNULL|WO_IS) ){ - if( op==WO_ISNULL ){ - pIdxCons[j].op = SQLITE_INDEX_CONSTRAINT_ISNULL; + if( op==WO_AUX ){ + pIdxCons[j].op = pTerm->eMatchOp; + }else if( op & (WO_ISNULL|WO_IS) ){ + if( op==WO_ISNULL ){ + pIdxCons[j].op = SQLITE_INDEX_CONSTRAINT_ISNULL; + }else{ + pIdxCons[j].op = SQLITE_INDEX_CONSTRAINT_IS; + } }else{ - pIdxCons[j].op = SQLITE_INDEX_CONSTRAINT_IS; - } - }else{ - pIdxCons[j].op = (u8)op; - /* The direct assignment in the previous line is possible only because - ** the WO_ and SQLITE_INDEX_CONSTRAINT_ codes are identical. The - ** following asserts verify this fact. */ - assert( WO_EQ==SQLITE_INDEX_CONSTRAINT_EQ ); - assert( WO_LT==SQLITE_INDEX_CONSTRAINT_LT ); - assert( WO_LE==SQLITE_INDEX_CONSTRAINT_LE ); - assert( WO_GT==SQLITE_INDEX_CONSTRAINT_GT ); - assert( WO_GE==SQLITE_INDEX_CONSTRAINT_GE ); - assert( pTerm->eOperator&(WO_IN|WO_EQ|WO_LT|WO_LE|WO_GT|WO_GE|WO_AUX) ); - - if( op & (WO_LT|WO_LE|WO_GT|WO_GE) - && sqlite3ExprIsVector(pTerm->pExpr->pRight) - ){ - testcase( j!=i ); - if( j<16 ) mNoOmit |= (1 << j); - if( op==WO_LT ) pIdxCons[j].op = WO_LE; - if( op==WO_GT ) pIdxCons[j].op = WO_GE; + pIdxCons[j].op = (u8)op; + /* The direct assignment in the previous line is possible only because + ** the WO_ and SQLITE_INDEX_CONSTRAINT_ codes are identical. The + ** following asserts verify this fact. */ + assert( WO_EQ==SQLITE_INDEX_CONSTRAINT_EQ ); + assert( WO_LT==SQLITE_INDEX_CONSTRAINT_LT ); + assert( WO_LE==SQLITE_INDEX_CONSTRAINT_LE ); + assert( WO_GT==SQLITE_INDEX_CONSTRAINT_GT ); + assert( WO_GE==SQLITE_INDEX_CONSTRAINT_GE ); + assert( pTerm->eOperator&(WO_IN|WO_EQ|WO_LT|WO_LE|WO_GT|WO_GE|WO_AUX) ); + + if( op & (WO_LT|WO_LE|WO_GT|WO_GE) + && sqlite3ExprIsVector(pTerm->pExpr->pRight) + ){ + testcase( j!=i ); + if( j<16 ) mNoOmit |= (1 << j); + if( op==WO_LT ) pIdxCons[j].op = WO_LE; + if( op==WO_GT ) pIdxCons[j].op = WO_GE; + } } - } - j++; + j++; + } } assert( j==nTerm ); pIdxInfo->nConstraint = j; for(i=j=0; i<nOrderBy; i++){ Expr *pExpr = pOrderBy->a[i].pExpr; - if( sqlite3ExprIsConstant(pExpr) ) continue; + if( sqlite3ExprIsConstant(0, pExpr) ) continue; assert( pExpr->op==TK_COLUMN || (pExpr->op==TK_COLLATE && pExpr->pLeft->op==TK_COLUMN && pExpr->iColumn==pExpr->pLeft->iColumn) ); @@ -1456,6 +1590,17 @@ static sqlite3_index_info *allocateIndexInfo( } /* +** Free and zero the sqlite3_index_info.idxStr value if needed. +*/ +static void freeIdxStr(sqlite3_index_info *pIdxInfo){ + if( pIdxInfo->needToFreeIdxStr ){ + sqlite3_free(pIdxInfo->idxStr); + pIdxInfo->idxStr = 0; + pIdxInfo->needToFreeIdxStr = 0; + } +} + +/* ** Free an sqlite3_index_info structure allocated by allocateIndexInfo() ** and possibly modified by xBestIndex methods. */ @@ -1470,6 +1615,7 @@ static void freeIndexInfo(sqlite3 *db, sqlite3_index_info *pIdxInfo){ sqlite3ValueFree(pHidden->aRhs[i]); /* IMP: R-14553-25174 */ pHidden->aRhs[i] = 0; } + freeIdxStr(pIdxInfo); sqlite3DbFree(db, pIdxInfo); } @@ -1493,11 +1639,11 @@ static int vtabBestIndex(Parse *pParse, Table *pTab, sqlite3_index_info *p){ sqlite3_vtab *pVtab = sqlite3GetVTable(pParse->db, pTab)->pVtab; int rc; - whereTraceIndexInfoInputs(p); + whereTraceIndexInfoInputs(p, pTab); pParse->db->nSchemaLock++; rc = pVtab->pModule->xBestIndex(pVtab, p); pParse->db->nSchemaLock--; - whereTraceIndexInfoOutputs(p); + whereTraceIndexInfoOutputs(p, pTab); if( rc!=SQLITE_OK && rc!=SQLITE_CONSTRAINT ){ if( rc==SQLITE_NOMEM ){ @@ -2263,7 +2409,7 @@ void sqlite3WhereLoopPrint(const WhereLoop *p, const WhereClause *pWC){ WhereInfo *pWInfo = pWC->pWInfo; int nb = 1+(pWInfo->pTabList->nSrc+3)/4; SrcItem *pItem = pWInfo->pTabList->a + p->iTab; - Table *pTab = pItem->pTab; + Table *pTab = pItem->pSTab; Bitmask mAll = (((Bitmask)1)<<(nb*4)) - 1; sqlite3DebugPrintf("%c%2d.%0*llx.%0*llx", p->cId, p->iTab, nb, p->maskSelf, nb, p->prereq & mAll); @@ -2821,7 +2967,7 @@ static void whereLoopOutputAdjust( Expr *pRight = pTerm->pExpr->pRight; int k = 0; testcase( pTerm->pExpr->op==TK_IS ); - if( sqlite3ExprIsInteger(pRight, &k) && k>=(-1) && k<=1 ){ + if( sqlite3ExprIsInteger(pRight, &k, 0) && k>=(-1) && k<=1 ){ k = 10; }else{ k = 20; @@ -2975,7 +3121,9 @@ static int whereLoopAddBtreeIndex( } if( pProbe->bUnordered || pProbe->bLowQual ){ if( pProbe->bUnordered ) opMask &= ~(WO_GT|WO_GE|WO_LT|WO_LE); - if( pProbe->bLowQual ) opMask &= ~(WO_EQ|WO_IN|WO_IS); + if( pProbe->bLowQual && pSrc->fg.isIndexedBy==0 ){ + opMask &= ~(WO_EQ|WO_IN|WO_IS); + } } assert( pNew->u.btree.nEq<pProbe->nColumn ); @@ -3116,7 +3264,7 @@ static int whereLoopAddBtreeIndex( || (iCol>=0 && nInMul==0 && saved_nEq==pProbe->nKeyCol-1) ){ if( iCol==XN_ROWID || pProbe->uniqNotNull - || (pProbe->nKeyCol==1 && pProbe->onError && eOp==WO_EQ) + || (pProbe->nKeyCol==1 && pProbe->onError && (eOp & WO_EQ)) ){ pNew->wsFlags |= WHERE_ONEROW; }else{ @@ -3242,11 +3390,14 @@ static int whereLoopAddBtreeIndex( } } - /* Set rCostIdx to the cost of visiting selected rows in index. Add - ** it to pNew->rRun, which is currently set to the cost of the index - ** seek only. Then, if this is a non-covering index, add the cost of - ** visiting the rows in the main table. */ - assert( pSrc->pTab->szTabRow>0 ); + /* Set rCostIdx to the estimated cost of visiting selected rows in the + ** index. The estimate is the sum of two values: + ** 1. The cost of doing one search-by-key to find the first matching + ** entry + ** 2. Stepping forward in the index pNew->nOut times to find all + ** additional matching entries. + */ + assert( pSrc->pSTab->szTabRow>0 ); if( pProbe->idxType==SQLITE_IDXTYPE_IPK ){ /* The pProbe->szIdxRow is low for an IPK table since the interior ** pages are small. Thus szIdxRow gives a good estimate of seek cost. @@ -3254,9 +3405,17 @@ static int whereLoopAddBtreeIndex( ** under-estimate the scanning cost. */ rCostIdx = pNew->nOut + 16; }else{ - rCostIdx = pNew->nOut + 1 + (15*pProbe->szIdxRow)/pSrc->pTab->szTabRow; + rCostIdx = pNew->nOut + 1 + (15*pProbe->szIdxRow)/pSrc->pSTab->szTabRow; } - pNew->rRun = sqlite3LogEstAdd(rLogSize, rCostIdx); + rCostIdx = sqlite3LogEstAdd(rLogSize, rCostIdx); + + /* Estimate the cost of running the loop. If all data is coming + ** from the index, then this is just the cost of doing the index + ** lookup and scan. But if some data is coming out of the main table, + ** we also have to add in the cost of doing pNew->nOut searches to + ** locate the row in the main table that corresponds to the index entry. + */ + pNew->rRun = rCostIdx; if( (pNew->wsFlags & (WHERE_IDX_ONLY|WHERE_IPK|WHERE_EXPRIDX))==0 ){ pNew->rRun = sqlite3LogEstAdd(pNew->rRun, pNew->nOut + 16); } @@ -3362,7 +3521,9 @@ static int indexMightHelpWithOrderBy( for(ii=0; ii<pOB->nExpr; ii++){ Expr *pExpr = sqlite3ExprSkipCollateAndLikely(pOB->a[ii].pExpr); if( NEVER(pExpr==0) ) continue; - if( pExpr->op==TK_COLUMN && pExpr->iTable==iCursor ){ + if( (pExpr->op==TK_COLUMN || pExpr->op==TK_AGG_COLUMN) + && pExpr->iTable==iCursor + ){ if( pExpr->iColumn<0 ) return 1; for(jj=0; jj<pIndex->nKeyCol; jj++){ if( pExpr->iColumn==pIndex->aiColumn[jj] ) return 1; @@ -3619,7 +3780,7 @@ static void wherePartIdxExpr( u8 aff; if( pLeft->op!=TK_COLUMN ) return; - if( !sqlite3ExprIsConstant(pRight) ) return; + if( !sqlite3ExprIsConstant(0, pRight) ) return; if( !sqlite3IsBinary(sqlite3ExprCompareCollSeq(pParse, pPart)) ) return; if( pLeft->iColumn<0 ) return; aff = pIdx->pTable->aCol[pLeft->iColumn].affinity; @@ -3709,9 +3870,9 @@ static int whereLoopAddBtree( pWInfo = pBuilder->pWInfo; pTabList = pWInfo->pTabList; pSrc = pTabList->a + pNew->iTab; - pTab = pSrc->pTab; + pTab = pSrc->pSTab; pWC = pBuilder->pWC; - assert( !IsVirtual(pSrc->pTab) ); + assert( !IsVirtual(pSrc->pSTab) ); if( pSrc->fg.isIndexedBy ){ assert( pSrc->fg.isCte==0 ); @@ -3736,7 +3897,7 @@ static int whereLoopAddBtree( sPk.idxType = SQLITE_IDXTYPE_IPK; aiRowEstPk[0] = pTab->nRowLogEst; aiRowEstPk[1] = 0; - pFirst = pSrc->pTab->pIndex; + pFirst = pSrc->pSTab->pIndex; if( pSrc->fg.notIndexed==0 ){ /* The real indices of the table are only considered if the ** NOT INDEXED qualifier is omitted from the FROM clause */ @@ -3826,6 +3987,7 @@ static int whereLoopAddBtree( pNew->prereq = mPrereq; pNew->nOut = rSize; pNew->u.btree.pIndex = pProbe; + pNew->u.btree.pOrderBy = 0; b = indexMightHelpWithOrderBy(pBuilder, pProbe, pSrc->iCursor); /* The ONEPASS_DESIRED flags never occurs together with ORDER BY */ @@ -3855,6 +4017,10 @@ static int whereLoopAddBtree( #endif ApplyCostMultiplier(pNew->rRun, pTab->costMult); whereLoopOutputAdjust(pWC, pNew, rSize); + if( pSrc->fg.isSubquery ){ + if( pSrc->fg.viaCoroutine ) pNew->wsFlags |= WHERE_COROUTINE; + pNew->u.btree.pOrderBy = pSrc->u4.pSubq->pSelect->pOrderBy; + } rc = whereLoopInsert(pBuilder, pNew); pNew->nOut = rSize; if( rc ) break; @@ -3892,7 +4058,9 @@ static int whereLoopAddBtree( " according to whereIsCoveringIndex()\n", pProbe->zName)); } } - }else if( m==0 ){ + }else if( m==0 + && (HasRowid(pTab) || pWInfo->pSelect!=0 || sqlite3FaultSim(700)) + ){ WHERETRACE(0x200, ("-> %s a covering index according to bitmasks\n", pProbe->zName, m==0 ? "is" : "is not")); @@ -3968,7 +4136,7 @@ static int whereLoopAddBtree( ** unique index is used (making the index functionally non-unique) ** then the sqlite_stat1 data becomes important for scoring the ** plan */ - pTab->tabFlags |= TF_StatsUsed; + pTab->tabFlags |= TF_MaybeReanalyze; } #ifdef SQLITE_ENABLE_STAT4 sqlite3Stat4ProbeFree(pBuilder->pRec); @@ -3991,6 +4159,21 @@ static int isLimitTerm(WhereTerm *pTerm){ } /* +** Return true if the first nCons constraints in the pUsage array are +** marked as in-use (have argvIndex>0). False otherwise. +*/ +static int allConstraintsUsed( + struct sqlite3_index_constraint_usage *aUsage, + int nCons +){ + int ii; + for(ii=0; ii<nCons; ii++){ + if( aUsage[ii].argvIndex<=0 ) return 0; + } + return 1; +} + +/* ** Argument pIdxInfo is already populated with all constraints that may ** be used by the virtual table identified by pBuilder->pNew->iTab. This ** function marks a subset of those constraints usable, invokes the @@ -4040,7 +4223,7 @@ static int whereLoopAddVirtualOne( ** arguments mUsable and mExclude. */ pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint; for(i=0; i<nConstraint; i++, pIdxCons++){ - WhereTerm *pTerm = &pWC->a[pIdxCons->iTermOffset]; + WhereTerm *pTerm = termFromWhereClause(pWC, pIdxCons->iTermOffset); pIdxCons->usable = 0; if( (pTerm->prereqRight & mUsable)==pTerm->prereqRight && (pTerm->eOperator & mExclude)==0 @@ -4059,11 +4242,10 @@ static int whereLoopAddVirtualOne( pIdxInfo->estimatedCost = SQLITE_BIG_DBL / (double)2; pIdxInfo->estimatedRows = 25; pIdxInfo->idxFlags = 0; - pIdxInfo->colUsed = (sqlite3_int64)pSrc->colUsed; pHidden->mHandleIn = 0; /* Invoke the virtual table xBestIndex() method */ - rc = vtabBestIndex(pParse, pSrc->pTab, pIdxInfo); + rc = vtabBestIndex(pParse, pSrc->pSTab, pIdxInfo); if( rc ){ if( rc==SQLITE_CONSTRAINT ){ /* If the xBestIndex method returns SQLITE_CONSTRAINT, that means @@ -4071,6 +4253,7 @@ static int whereLoopAddVirtualOne( ** Make no entries in the loop table. */ WHERETRACE(0xffffffff, (" ^^^^--- non-viable plan rejected!\n")); + freeIdxStr(pIdxInfo); return SQLITE_OK; } return rc; @@ -4088,18 +4271,17 @@ static int whereLoopAddVirtualOne( int j = pIdxCons->iTermOffset; if( iTerm>=nConstraint || j<0 - || j>=pWC->nTerm + || (pTerm = termFromWhereClause(pWC, j))==0 || pNew->aLTerm[iTerm]!=0 || pIdxCons->usable==0 ){ - sqlite3ErrorMsg(pParse,"%s.xBestIndex malfunction",pSrc->pTab->zName); - testcase( pIdxInfo->needToFreeIdxStr ); + sqlite3ErrorMsg(pParse,"%s.xBestIndex malfunction",pSrc->pSTab->zName); + freeIdxStr(pIdxInfo); return SQLITE_ERROR; } testcase( iTerm==nConstraint-1 ); testcase( j==0 ); testcase( j==pWC->nTerm-1 ); - pTerm = &pWC->a[j]; pNew->prereq |= pTerm->prereqRight; assert( iTerm<pNew->nLSlot ); pNew->aLTerm[iTerm] = pTerm; @@ -4130,18 +4312,21 @@ static int whereLoopAddVirtualOne( *pbIn = 1; assert( (mExclude & WO_IN)==0 ); } + /* Unless pbRetryLimit is non-NULL, there should be no LIMIT/OFFSET + ** terms. And if there are any, they should follow all other terms. */ assert( pbRetryLimit || !isLimitTerm(pTerm) ); - if( isLimitTerm(pTerm) && *pbIn ){ + assert( !isLimitTerm(pTerm) || i>=nConstraint-2 ); + assert( !isLimitTerm(pTerm) || i==nConstraint-1 || isLimitTerm(pTerm+1) ); + + if( isLimitTerm(pTerm) && (*pbIn || !allConstraintsUsed(pUsage, i)) ){ /* If there is an IN(...) term handled as an == (separate call to ** xFilter for each value on the RHS of the IN) and a LIMIT or - ** OFFSET term handled as well, the plan is unusable. Set output - ** variable *pbRetryLimit to true to tell the caller to retry with - ** LIMIT and OFFSET disabled. */ - if( pIdxInfo->needToFreeIdxStr ){ - sqlite3_free(pIdxInfo->idxStr); - pIdxInfo->idxStr = 0; - pIdxInfo->needToFreeIdxStr = 0; - } + ** OFFSET term handled as well, the plan is unusable. Similarly, + ** if there is a LIMIT/OFFSET and there are other unused terms, + ** the plan cannot be used. In these cases set variable *pbRetryLimit + ** to true to tell the caller to retry with LIMIT and OFFSET + ** disabled. */ + freeIdxStr(pIdxInfo); *pbRetryLimit = 1; return SQLITE_OK; } @@ -4153,8 +4338,8 @@ static int whereLoopAddVirtualOne( if( pNew->aLTerm[i]==0 ){ /* The non-zero argvIdx values must be contiguous. Raise an ** error if they are not */ - sqlite3ErrorMsg(pParse,"%s.xBestIndex malfunction",pSrc->pTab->zName); - testcase( pIdxInfo->needToFreeIdxStr ); + sqlite3ErrorMsg(pParse,"%s.xBestIndex malfunction",pSrc->pSTab->zName); + freeIdxStr(pIdxInfo); return SQLITE_ERROR; } } @@ -4165,6 +4350,7 @@ static int whereLoopAddVirtualOne( pNew->u.vtab.idxStr = pIdxInfo->idxStr; pNew->u.vtab.isOrdered = (i8)(pIdxInfo->orderByConsumed ? pIdxInfo->nOrderBy : 0); + pNew->u.vtab.bIdxNumHex = (pIdxInfo->idxFlags&SQLITE_INDEX_SCAN_HEX)!=0; pNew->rSetup = 0; pNew->rRun = sqlite3LogEstFromDouble(pIdxInfo->estimatedCost); pNew->nOut = sqlite3LogEst(pIdxInfo->estimatedRows); @@ -4209,7 +4395,7 @@ const char *sqlite3_vtab_collation(sqlite3_index_info *pIdxInfo, int iCons){ if( iCons>=0 && iCons<pIdxInfo->nConstraint ){ CollSeq *pC = 0; int iTerm = pIdxInfo->aConstraint[iCons].iTermOffset; - Expr *pX = pHidden->pWC->a[iTerm].pExpr; + Expr *pX = termFromWhereClause(pHidden->pWC, iTerm)->pExpr; if( pX->pLeft ){ pC = sqlite3ExprCompareCollSeq(pHidden->pParse, pX); } @@ -4255,7 +4441,9 @@ int sqlite3_vtab_rhs_value( rc = SQLITE_MISUSE_BKPT; /* EV: R-30545-25046 */ }else{ if( pH->aRhs[iCons]==0 ){ - WhereTerm *pTerm = &pH->pWC->a[pIdxInfo->aConstraint[iCons].iTermOffset]; + WhereTerm *pTerm = termFromWhereClause( + pH->pWC, pIdxInfo->aConstraint[iCons].iTermOffset + ); rc = sqlite3ValueFromExpr( pH->pParse->db, pTerm->pExpr->pRight, ENC(pH->pParse->db), SQLITE_AFF_BLOB, &pH->aRhs[iCons] @@ -4353,7 +4541,7 @@ static int whereLoopAddVirtual( pWC = pBuilder->pWC; pNew = pBuilder->pNew; pSrc = &pWInfo->pTabList->a[pNew->iTab]; - assert( IsVirtual(pSrc->pTab) ); + assert( IsVirtual(pSrc->pSTab) ); p = allocateIndexInfo(pWInfo, pWC, mUnusable, pSrc, &mNoOmit); if( p==0 ) return SQLITE_NOMEM_BKPT; pNew->rSetup = 0; @@ -4367,7 +4555,7 @@ static int whereLoopAddVirtual( } /* First call xBestIndex() with all constraints usable. */ - WHERETRACE(0x800, ("BEGIN %s.addVirtual()\n", pSrc->pTab->zName)); + WHERETRACE(0x800, ("BEGIN %s.addVirtual()\n", pSrc->pSTab->zName)); WHERETRACE(0x800, (" VirtualOne: all usable\n")); rc = whereLoopAddVirtualOne( pBuilder, mPrereq, ALLBITS, 0, p, mNoOmit, &bIn, &bRetry @@ -4411,9 +4599,8 @@ static int whereLoopAddVirtual( Bitmask mNext = ALLBITS; assert( mNext>0 ); for(i=0; i<nConstraint; i++){ - Bitmask mThis = ( - pWC->a[p->aConstraint[i].iTermOffset].prereqRight & ~mPrereq - ); + int iTerm = p->aConstraint[i].iTermOffset; + Bitmask mThis = termFromWhereClause(pWC, iTerm)->prereqRight & ~mPrereq; if( mThis>mPrev && mThis<mNext ) mNext = mThis; } mPrev = mNext; @@ -4449,9 +4636,8 @@ static int whereLoopAddVirtual( } } - if( p->needToFreeIdxStr ) sqlite3_free(p->idxStr); freeIndexInfo(pParse->db, p); - WHERETRACE(0x800, ("END %s.addVirtual(), rc=%d\n", pSrc->pTab->zName, rc)); + WHERETRACE(0x800, ("END %s.addVirtual(), rc=%d\n", pSrc->pSTab->zName, rc)); return rc; } #endif /* SQLITE_OMIT_VIRTUALTABLE */ @@ -4523,7 +4709,7 @@ static int whereLoopAddOr( } #endif #ifndef SQLITE_OMIT_VIRTUALTABLE - if( IsVirtual(pItem->pTab) ){ + if( IsVirtual(pItem->pSTab) ){ rc = whereLoopAddVirtual(&sSubBuild, mPrereq, mUnusable); }else #endif @@ -4637,7 +4823,7 @@ static int whereLoopAddAll(WhereLoopBuilder *pBuilder){ mPrereq = 0; } #ifndef SQLITE_OMIT_VIRTUALTABLE - if( IsVirtual(pItem->pTab) ){ + if( IsVirtual(pItem->pSTab) ){ SrcItem *p; for(p=&pItem[1]; p<pEnd; p++){ if( mUnusable || (p->fg.jointype & (JT_OUTER|JT_CROSS)) ){ @@ -4669,6 +4855,97 @@ static int whereLoopAddAll(WhereLoopBuilder *pBuilder){ return rc; } +/* Implementation of the order-by-subquery optimization: +** +** WhereLoop pLoop, which the iLoop-th term of the nested loop, is really +** a subquery or CTE that has an ORDER BY clause. See if any of the terms +** in the subquery ORDER BY clause will satisfy pOrderBy from the outer +** query. Mark off all satisfied terms (by setting bits in *pOBSat) and +** return TRUE if they do. If not, return false. +** +** Example: +** +** CREATE TABLE t1(a,b,c, PRIMARY KEY(a,b)); +** CREATE TABLE t2(x,y); +** WITH t3(p,q) AS MATERIALIZED (SELECT x+y, x-y FROM t2 ORDER BY x+y) +** SELECT * FROM t3 JOIN t1 ON a=q ORDER BY p, b; +** +** The CTE named "t3" comes out in the natural order of "p", so the first +** first them of "ORDER BY p,b" is satisfied by a sequential scan of "t3" +** and sorting only needs to occur on the second term "b". +** +** Limitations: +** +** (1) The optimization is not applied if the outer ORDER BY contains +** a COLLATE clause. The optimization might be applied if the +** outer ORDER BY uses NULLS FIRST, NULLS LAST, ASC, and/or DESC as +** long as the subquery ORDER BY does the same. But if the +** outer ORDER BY uses COLLATE, even a redundant COLLATE, the +** optimization is bypassed. +** +** (2) The subquery ORDER BY terms must exactly match subquery result +** columns, including any COLLATE annotations. This routine relies +** on iOrderByCol to do matching between order by terms and result +** columns, and iOrderByCol will not be set if the result column +** and ORDER BY collations differ. +** +** (3) The subquery and outer ORDER BY can be in opposite directions as +** long as the subquery is materialized. If the subquery is +** implemented as a co-routine, the sort orders must be in the same +** direction because there is no way to run a co-routine backwards. +*/ +static SQLITE_NOINLINE int wherePathMatchSubqueryOB( + WhereInfo *pWInfo, /* The WHERE clause */ + WhereLoop *pLoop, /* The nested loop term that is a subquery */ + int iLoop, /* Which level of the nested loop. 0==outermost */ + int iCur, /* Cursor used by the this loop */ + ExprList *pOrderBy, /* The ORDER BY clause on the whole query */ + Bitmask *pRevMask, /* When loops need to go in reverse order */ + Bitmask *pOBSat /* Which terms of pOrderBy are satisfied so far */ +){ + int iOB; /* Index into pOrderBy->a[] */ + int jSub; /* Index into pSubOB->a[] */ + u8 rev = 0; /* True if iOB and jSub sort in opposite directions */ + u8 revIdx = 0; /* Sort direction for jSub */ + Expr *pOBExpr; /* Current term of outer ORDER BY */ + ExprList *pSubOB; /* Complete ORDER BY on the subquery */ + + pSubOB = pLoop->u.btree.pOrderBy; + assert( pSubOB!=0 ); + for(iOB=0; (MASKBIT(iOB) & *pOBSat)!=0; iOB++){} + for(jSub=0; jSub<pSubOB->nExpr && iOB<pOrderBy->nExpr; jSub++, iOB++){ + if( pSubOB->a[jSub].u.x.iOrderByCol==0 ) break; + pOBExpr = pOrderBy->a[iOB].pExpr; + if( pOBExpr->op!=TK_COLUMN && pOBExpr->op!=TK_AGG_COLUMN ) break; + if( pOBExpr->iTable!=iCur ) break; + if( pOBExpr->iColumn!=pSubOB->a[jSub].u.x.iOrderByCol-1 ) break; + if( (pWInfo->wctrlFlags & WHERE_GROUPBY)==0 ){ + u8 sfOB = pOrderBy->a[iOB].fg.sortFlags; /* sortFlags for iOB */ + u8 sfSub = pSubOB->a[jSub].fg.sortFlags; /* sortFlags for jSub */ + if( (sfSub & KEYINFO_ORDER_BIGNULL) != (sfOB & KEYINFO_ORDER_BIGNULL) ){ + break; + } + revIdx = sfSub & KEYINFO_ORDER_DESC; + if( jSub>0 ){ + if( (rev^revIdx)!=(sfOB & KEYINFO_ORDER_DESC) ){ + break; + } + }else{ + rev = revIdx ^ (sfOB & KEYINFO_ORDER_DESC); + if( rev ){ + if( (pLoop->wsFlags & WHERE_COROUTINE)!=0 ){ + /* Cannot run a co-routine in reverse order */ + break; + } + *pRevMask |= MASKBIT(iLoop); + } + } + } + *pOBSat |= MASKBIT(iOB); + } + return jSub>0; +} + /* ** Examine a WherePath (with the addition of the extra WhereLoop of the 6th ** parameters) to see if it outputs rows in the requested ORDER BY @@ -4814,9 +5091,18 @@ static i8 wherePathSatisfiesOrderBy( if( (pLoop->wsFlags & WHERE_ONEROW)==0 ){ if( pLoop->wsFlags & WHERE_IPK ){ + if( pLoop->u.btree.pOrderBy + && OptimizationEnabled(db, SQLITE_OrderBySubq) + && wherePathMatchSubqueryOB(pWInfo,pLoop,iLoop,iCur, + pOrderBy,pRevMask, &obSat) + ){ + nColumn = 0; + isOrderDistinct = 0; + }else{ + nColumn = 1; + } pIndex = 0; nKeyCol = 0; - nColumn = 1; }else if( (pIndex = pLoop->u.btree.pIndex)==0 || pIndex->bUnordered ){ return 0; }else{ @@ -4826,7 +5112,7 @@ static i8 wherePathSatisfiesOrderBy( assert( pIndex->aiColumn[nColumn-1]==XN_ROWID || !HasRowid(pIndex->pTable)); /* All relevant terms of the index must also be non-NULL in order - ** for isOrderDistinct to be true. So the isOrderDistint value + ** for isOrderDistinct to be true. So the isOrderDistinct value ** computed here might be a false positive. Corrections will be ** made at tag-20210426-1 below */ isOrderDistinct = IsUniqueIndex(pIndex) @@ -4911,7 +5197,7 @@ static i8 wherePathSatisfiesOrderBy( } /* Find the ORDER BY term that corresponds to the j-th column - ** of the index and mark that ORDER BY term off + ** of the index and mark that ORDER BY term having been satisfied. */ isMatch = 0; for(i=0; bOnce && i<nOrderBy; i++){ @@ -4993,7 +5279,7 @@ static i8 wherePathSatisfiesOrderBy( if( MASKBIT(i) & obSat ) continue; p = pOrderBy->a[i].pExpr; mTerm = sqlite3WhereExprUsage(&pWInfo->sMaskSet,p); - if( mTerm==0 && !sqlite3ExprIsConstant(p) ) continue; + if( mTerm==0 && !sqlite3ExprIsConstant(0,p) ) continue; if( (mTerm&~orderDistinctMask)==0 ){ obSat |= MASKBIT(i); } @@ -5119,6 +5405,83 @@ static LogEst whereSortingCost( } /* +** Compute the maximum number of paths in the solver algorithm, for +** queries that have three or more terms in the FROM clause. Queries with +** two or fewer FROM clause terms are handled by the caller. +** +** Query planning is NP-hard. We must limit the number of paths at +** each step of the solver search algorithm to avoid exponential behavior. +** +** The value returned is a tuning parameter. Currently the value is: +** +** 18 for star queries +** 12 otherwise +** +** For the purposes of SQLite, a star-query is defined as a query +** with a large central table that is joined against four or more +** smaller tables. The central table is called the "fact" table. +** The smaller tables that get joined are "dimension tables". +** +** SIDE EFFECT: (and really the whole point of this subroutine) +** +** If pWInfo describes a star-query, then the cost on WhereLoops for the +** fact table is reduced. This heuristic helps keep fact tables in +** outer loops. Without this heuristic, paths with fact tables in outer +** loops tend to get pruned by the mxChoice limit on the number of paths, +** resulting in poor query plans. The total amount of heuristic cost +** adjustment is stored in pWInfo->nOutStarDelta and the cost adjustment +** for each WhereLoop is stored in its rStarDelta field. +*/ +static int computeMxChoice(WhereInfo *pWInfo, LogEst nRowEst){ + int nLoop = pWInfo->nLevel; /* Number of terms in the join */ + if( nRowEst==0 && nLoop>=5 ){ + /* Check to see if we are dealing with a star schema and if so, reduce + ** the cost of fact tables relative to dimension tables, as a heuristic + ** to help keep the fact tables in outer loops. + */ + int iLoop; /* Counter over join terms */ + Bitmask m; /* Bitmask for current loop */ + assert( pWInfo->nOutStarDelta==0 ); + for(iLoop=0, m=1; iLoop<nLoop; iLoop++, m<<=1){ + WhereLoop *pWLoop; /* For looping over WhereLoops */ + int nDep = 0; /* Number of dimension tables */ + LogEst rDelta; /* Heuristic cost adjustment */ + Bitmask mSeen = 0; /* Mask of dimension tables */ + for(pWLoop=pWInfo->pLoops; pWLoop; pWLoop=pWLoop->pNextLoop){ + if( (pWLoop->prereq & m)!=0 && (pWLoop->maskSelf & mSeen)==0 ){ + nDep++; + mSeen |= pWLoop->maskSelf; + } + } + if( nDep<=3 ) continue; + rDelta = 15*(nDep-3); +#ifdef WHERETRACE_ENABLED /* 0x4 */ + if( sqlite3WhereTrace&0x4 ){ + SrcItem *pItem = pWInfo->pTabList->a + iLoop; + sqlite3DebugPrintf("Fact-table %s: %d dimensions, cost reduced %d\n", + pItem->zAlias ? pItem->zAlias : pItem->pSTab->zName, + nDep, rDelta); + } +#endif + if( pWInfo->nOutStarDelta==0 ){ + for(pWLoop=pWInfo->pLoops; pWLoop; pWLoop=pWLoop->pNextLoop){ + pWLoop->rStarDelta = 0; + } + } + pWInfo->nOutStarDelta += rDelta; + for(pWLoop=pWInfo->pLoops; pWLoop; pWLoop=pWLoop->pNextLoop){ + if( pWLoop->maskSelf==m ){ + pWLoop->rRun -= rDelta; + pWLoop->nOut -= rDelta; + pWLoop->rStarDelta = rDelta; + } + } + } + } + return pWInfo->nOutStarDelta>0 ? 18 : 12; +} + +/* ** Given the list of WhereLoop objects at pWInfo->pLoops, this routine ** attempts to find the lowest cost path that visits each WhereLoop ** once. This path is then loaded into the pWInfo->a[].pWLoop fields. @@ -5153,13 +5516,25 @@ static int wherePathSolver(WhereInfo *pWInfo, LogEst nRowEst){ pParse = pWInfo->pParse; nLoop = pWInfo->nLevel; - /* TUNING: For simple queries, only the best path is tracked. - ** For 2-way joins, the 5 best paths are followed. - ** For joins of 3 or more tables, track the 10 best paths */ - mxChoice = (nLoop<=1) ? 1 : (nLoop==2 ? 5 : 10); - assert( nLoop<=pWInfo->pTabList->nSrc ); WHERETRACE(0x002, ("---- begin solver. (nRowEst=%d, nQueryLoop=%d)\n", nRowEst, pParse->nQueryLoop)); + /* TUNING: mxChoice is the maximum number of possible paths to preserve + ** at each step. Based on the number of loops in the FROM clause: + ** + ** nLoop mxChoice + ** ----- -------- + ** 1 1 // the most common case + ** 2 5 + ** 3+ 12 or 18 // see computeMxChoice() + */ + if( nLoop<=1 ){ + mxChoice = 1; + }else if( nLoop==2 ){ + mxChoice = 5; + }else{ + mxChoice = computeMxChoice(pWInfo, nRowEst); + } + assert( nLoop<=pWInfo->pTabList->nSrc ); /* If nRowEst is zero and there is an ORDER BY clause, ignore it. In this ** case the purpose of this call is to estimate the number of rows returned @@ -5242,7 +5617,10 @@ static int wherePathSolver(WhereInfo *pWInfo, LogEst nRowEst){ /* At this point, pWLoop is a candidate to be the next loop. ** Compute its cost */ - rUnsorted = sqlite3LogEstAdd(pWLoop->rSetup,pWLoop->rRun + pFrom->nRow); + rUnsorted = pWLoop->rRun + pFrom->nRow; + if( pWLoop->rSetup ){ + rUnsorted = sqlite3LogEstAdd(pWLoop->rSetup, rUnsorted); + } rUnsorted = sqlite3LogEstAdd(rUnsorted, pFrom->rUnsorted); nOut = pFrom->nRow + pWLoop->nOut; maskNew = pFrom->maskLoop | pWLoop->maskSelf; @@ -5287,6 +5665,7 @@ static int wherePathSolver(WhereInfo *pWInfo, LogEst nRowEst){ ** to (pTo->isOrdered==(-1))==(isOrdered==(-1))" for the range ** of legal values for isOrdered, -1..64. */ + testcase( nTo==0 ); for(jj=0, pTo=aTo; jj<nTo; jj++, pTo++){ if( pTo->maskLoop==maskNew && ((pTo->isOrdered^isOrdered)&0x80)==0 @@ -5403,16 +5782,28 @@ static int wherePathSolver(WhereInfo *pWInfo, LogEst nRowEst){ #ifdef WHERETRACE_ENABLED /* >=2 */ if( sqlite3WhereTrace & 0x02 ){ + LogEst rMin, rFloor = 0; + int nDone = 0; sqlite3DebugPrintf("---- after round %d ----\n", iLoop); - for(ii=0, pTo=aTo; ii<nTo; ii++, pTo++){ - sqlite3DebugPrintf(" %s cost=%-3d nrow=%-3d order=%c", - wherePathName(pTo, iLoop+1, 0), pTo->rCost, pTo->nRow, - pTo->isOrdered>=0 ? (pTo->isOrdered+'0') : '?'); - if( pTo->isOrdered>0 ){ - sqlite3DebugPrintf(" rev=0x%llx\n", pTo->revLoop); - }else{ - sqlite3DebugPrintf("\n"); + while( nDone<nTo ){ + rMin = 0x7fff; + for(ii=0, pTo=aTo; ii<nTo; ii++, pTo++){ + if( pTo->rCost>rFloor && pTo->rCost<rMin ) rMin = pTo->rCost; } + for(ii=0, pTo=aTo; ii<nTo; ii++, pTo++){ + if( pTo->rCost==rMin ){ + sqlite3DebugPrintf(" %s cost=%-3d nrow=%-3d order=%c", + wherePathName(pTo, iLoop+1, 0), pTo->rCost, pTo->nRow, + pTo->isOrdered>=0 ? (pTo->isOrdered+'0') : '?'); + if( pTo->isOrdered>0 ){ + sqlite3DebugPrintf(" rev=0x%llx\n", pTo->revLoop); + }else{ + sqlite3DebugPrintf("\n"); + } + nDone++; + } + } + rFloor = rMin; } } #endif @@ -5462,10 +5853,9 @@ static int wherePathSolver(WhereInfo *pWInfo, LogEst nRowEst){ if( pFrom->isOrdered==pWInfo->pOrderBy->nExpr ){ pWInfo->eDistinct = WHERE_DISTINCT_ORDERED; } - if( pWInfo->pSelect->pOrderBy - && pWInfo->nOBSat > pWInfo->pSelect->pOrderBy->nExpr ){ - pWInfo->nOBSat = pWInfo->pSelect->pOrderBy->nExpr; - } + /* vvv--- See check-in [12ad822d9b827777] on 2023-03-16 ---vvv */ + assert( pWInfo->pSelect->pOrderBy==0 + || pWInfo->nOBSat <= pWInfo->pSelect->pOrderBy->nExpr ); }else{ pWInfo->revMask = pFrom->revLoop; if( pWInfo->nOBSat<=0 ){ @@ -5508,8 +5898,7 @@ static int wherePathSolver(WhereInfo *pWInfo, LogEst nRowEst){ } } - - pWInfo->nRowOut = pFrom->nRow; + pWInfo->nRowOut = pFrom->nRow + pWInfo->nOutStarDelta; /* Free temporary memory and return success */ sqlite3StackFreeNN(pParse->db, pSpace); @@ -5517,6 +5906,83 @@ static int wherePathSolver(WhereInfo *pWInfo, LogEst nRowEst){ } /* +** This routine implements a heuristic designed to improve query planning. +** This routine is called in between the first and second call to +** wherePathSolver(). Hence the name "Interstage" "Heuristic". +** +** The first call to wherePathSolver() (hereafter just "solver()") computes +** the best path without regard to the order of the outputs. The second call +** to the solver() builds upon the first call to try to find an alternative +** path that satisfies the ORDER BY clause. +** +** This routine looks at the results of the first solver() run, and for +** every FROM clause term in the resulting query plan that uses an equality +** constraint against an index, disable other WhereLoops for that same +** FROM clause term that would try to do a full-table scan. This prevents +** an index search from being converted into a full-table scan in order to +** satisfy an ORDER BY clause, since even though we might get slightly better +** performance using the full-scan without sorting if the output size +** estimates are very precise, we might also get severe performance +** degradation using the full-scan if the output size estimate is too large. +** It is better to err on the side of caution. +** +** Except, if the first solver() call generated a full-table scan in an outer +** loop then stop this analysis at the first full-scan, since the second +** solver() run might try to swap that full-scan for another in order to +** get the output into the correct order. In other words, we allow a +** rewrite like this: +** +** First Solver() Second Solver() +** |-- SCAN t1 |-- SCAN t2 +** |-- SEARCH t2 `-- SEARCH t1 +** `-- SORT USING B-TREE +** +** The purpose of this routine is to disallow rewrites such as: +** +** First Solver() Second Solver() +** |-- SEARCH t1 |-- SCAN t2 <--- bad! +** |-- SEARCH t2 `-- SEARCH t1 +** `-- SORT USING B-TREE +** +** See test cases in test/whereN.test for the real-world query that +** originally provoked this heuristic. +*/ +static SQLITE_NOINLINE void whereInterstageHeuristic(WhereInfo *pWInfo){ + int i; +#ifdef WHERETRACE_ENABLED + int once = 0; +#endif + for(i=0; i<pWInfo->nLevel; i++){ + WhereLoop *p = pWInfo->a[i].pWLoop; + if( p==0 ) break; + if( (p->wsFlags & WHERE_VIRTUALTABLE)!=0 ) continue; + if( (p->wsFlags & (WHERE_COLUMN_EQ|WHERE_COLUMN_NULL|WHERE_COLUMN_IN))!=0 ){ + u8 iTab = p->iTab; + WhereLoop *pLoop; + for(pLoop=pWInfo->pLoops; pLoop; pLoop=pLoop->pNextLoop){ + if( pLoop->iTab!=iTab ) continue; + if( (pLoop->wsFlags & (WHERE_CONSTRAINT|WHERE_AUTO_INDEX))!=0 ){ + /* Auto-index and index-constrained loops allowed to remain */ + continue; + } +#ifdef WHERETRACE_ENABLED + if( sqlite3WhereTrace & 0x80 ){ + if( once==0 ){ + sqlite3DebugPrintf("Loops disabled by interstage heuristic:\n"); + once = 1; + } + sqlite3WhereLoopPrint(pLoop, &pWInfo->sWC); + } +#endif /* WHERETRACE_ENABLED */ + pLoop->prereq = ALLBITS; /* Prevent 2nd solver() from using this one */ + } + }else{ + break; + } + } +} + +/* ** Most queries use only a single table (they are not joins) and have ** simple == constraints against indexed fields. This routine attempts ** to plan those simple cases using much less ceremony than the @@ -5543,7 +6009,7 @@ static int whereShortCut(WhereLoopBuilder *pBuilder){ if( pWInfo->wctrlFlags & WHERE_OR_SUBCLAUSE ) return 0; assert( pWInfo->pTabList->nSrc>=1 ); pItem = pWInfo->pTabList->a; - pTab = pItem->pTab; + pTab = pItem->pSTab; if( IsVirtual(pTab) ) return 0; if( pItem->fg.isIndexedBy || pItem->fg.notIndexed ){ testcase( pItem->fg.isIndexedBy ); @@ -5684,6 +6150,10 @@ static void showAllWhereLoops(WhereInfo *pWInfo, WhereClause *pWC){ ** the right-most table of a subquery that was flattened into the ** main query and that subquery was the right-hand operand of an ** inner join that held an ON or USING clause. +** 6) The ORDER BY clause has 63 or fewer terms +** 7) The omit-noop-join optimization is enabled. +** +** Items (1), (6), and (7) are checked by the caller. ** ** For example, given: ** @@ -5802,9 +6272,9 @@ static SQLITE_NOINLINE void whereCheckIfBloomFilterIsUseful( WhereLoop *pLoop = pWInfo->a[i].pWLoop; const unsigned int reqFlags = (WHERE_SELFCULL|WHERE_COLUMN_EQ); SrcItem *pItem = &pWInfo->pTabList->a[pLoop->iTab]; - Table *pTab = pItem->pTab; + Table *pTab = pItem->pSTab; if( (pTab->tabFlags & TF_HasStat1)==0 ) break; - pTab->tabFlags |= TF_StatsUsed; + pTab->tabFlags |= TF_MaybeReanalyze; if( i>=1 && (pLoop->wsFlags & reqFlags)==reqFlags /* vvvvvv--- Always the case if WHERE_COLUMN_EQ is defined */ @@ -5822,10 +6292,63 @@ static SQLITE_NOINLINE void whereCheckIfBloomFilterIsUseful( } } nSearch += pLoop->nOut; + if( pWInfo->nOutStarDelta ) nSearch += pLoop->rStarDelta; } } /* +** Expression Node callback for sqlite3ExprCanReturnSubtype(). +** +** Only a function call is able to return a subtype. So if the node +** is not a function call, return WRC_Prune immediately. +** +** A function call is able to return a subtype if it has the +** SQLITE_RESULT_SUBTYPE property. +** +** Assume that every function is able to pass-through a subtype from +** one of its argument (using sqlite3_result_value()). Most functions +** are not this way, but we don't have a mechanism to distinguish those +** that are from those that are not, so assume they all work this way. +** That means that if one of its arguments is another function and that +** other function is able to return a subtype, then this function is +** able to return a subtype. +*/ +static int exprNodeCanReturnSubtype(Walker *pWalker, Expr *pExpr){ + int n; + FuncDef *pDef; + sqlite3 *db; + if( pExpr->op!=TK_FUNCTION ){ + return WRC_Prune; + } + assert( ExprUseXList(pExpr) ); + db = pWalker->pParse->db; + n = pExpr->x.pList ? pExpr->x.pList->nExpr : 0; + pDef = sqlite3FindFunction(db, pExpr->u.zToken, n, ENC(db), 0); + if( pDef==0 || (pDef->funcFlags & SQLITE_RESULT_SUBTYPE)!=0 ){ + pWalker->eCode = 1; + return WRC_Prune; + } + return WRC_Continue; +} + +/* +** Return TRUE if expression pExpr is able to return a subtype. +** +** A TRUE return does not guarantee that a subtype will be returned. +** It only indicates that a subtype return is possible. False positives +** are acceptable as they only disable an optimization. False negatives, +** on the other hand, can lead to incorrect answers. +*/ +static int sqlite3ExprCanReturnSubtype(Parse *pParse, Expr *pExpr){ + Walker w; + memset(&w, 0, sizeof(w)); + w.pParse = pParse; + w.xExprCallback = exprNodeCanReturnSubtype; + sqlite3WalkExpr(&w, pExpr); + return w.eCode; +} + +/* ** The index pIdx is used by a query and contains one or more expressions. ** In other words pIdx is an index on an expression. iIdxCur is the cursor ** number for the index and iDataCur is the cursor number for the corresponding @@ -5850,33 +6373,19 @@ static SQLITE_NOINLINE void whereAddIndexedExpr( for(i=0; i<pIdx->nColumn; i++){ Expr *pExpr; int j = pIdx->aiColumn[i]; - int bMaybeNullRow; if( j==XN_EXPR ){ pExpr = pIdx->aColExpr->a[i].pExpr; - testcase( pTabItem->fg.jointype & JT_LEFT ); - testcase( pTabItem->fg.jointype & JT_RIGHT ); - testcase( pTabItem->fg.jointype & JT_LTORJ ); - bMaybeNullRow = (pTabItem->fg.jointype & (JT_LEFT|JT_LTORJ|JT_RIGHT))!=0; }else if( j>=0 && (pTab->aCol[j].colFlags & COLFLAG_VIRTUAL)!=0 ){ pExpr = sqlite3ColumnExpr(pTab, &pTab->aCol[j]); - bMaybeNullRow = 0; }else{ continue; } - if( sqlite3ExprIsConstant(pExpr) ) continue; - if( pExpr->op==TK_FUNCTION ){ + if( sqlite3ExprIsConstant(0,pExpr) ) continue; + if( pExpr->op==TK_FUNCTION && sqlite3ExprCanReturnSubtype(pParse,pExpr) ){ /* Functions that might set a subtype should not be replaced by the ** value taken from an expression index since the index omits the ** subtype. https://sqlite.org/forum/forumpost/68d284c86b082c3e */ - int n; - FuncDef *pDef; - sqlite3 *db = pParse->db; - assert( ExprUseXList(pExpr) ); - n = pExpr->x.pList ? pExpr->x.pList->nExpr : 0; - pDef = sqlite3FindFunction(db, pExpr->u.zToken, n, ENC(db), 0); - if( pDef==0 || (pDef->funcFlags & SQLITE_RESULT_SUBTYPE)!=0 ){ - continue; - } + continue; } p = sqlite3DbMallocRaw(pParse->db, sizeof(IndexedExpr)); if( p==0 ) break; @@ -5891,7 +6400,7 @@ static SQLITE_NOINLINE void whereAddIndexedExpr( p->iDataCur = pTabItem->iCursor; p->iIdxCur = iIdxCur; p->iIdxCol = i; - p->bMaybeNullRow = bMaybeNullRow; + p->bMaybeNullRow = (pTabItem->fg.jointype & (JT_LEFT|JT_LTORJ|JT_RIGHT))!=0; if( sqlite3IndexAffinityStr(pParse->db, pIdx) ){ p->aff = pIdx->zColAff[i]; } @@ -5920,8 +6429,8 @@ static SQLITE_NOINLINE void whereReverseScanOrder(WhereInfo *pWInfo){ SrcItem *pItem = &pWInfo->pTabList->a[ii]; if( !pItem->fg.isCte || pItem->u2.pCteUse->eM10d!=M10d_Yes - || NEVER(pItem->pSelect==0) - || pItem->pSelect->pOrderBy==0 + || NEVER(pItem->fg.isSubquery==0) + || pItem->u4.pSubq->pSelect->pOrderBy==0 ){ pWInfo->revMask |= MASKBIT(ii); } @@ -6056,7 +6565,11 @@ WhereInfo *sqlite3WhereBegin( /* An ORDER/GROUP BY clause of more than 63 terms cannot be optimized */ testcase( pOrderBy && pOrderBy->nExpr==BMS-1 ); - if( pOrderBy && pOrderBy->nExpr>=BMS ) pOrderBy = 0; + if( pOrderBy && pOrderBy->nExpr>=BMS ){ + pOrderBy = 0; + wctrlFlags &= ~WHERE_WANT_DISTINCT; + wctrlFlags |= WHERE_KEEP_ALL_JOINS; /* Disable omit-noop-join opt */ + } /* The number of tables in the FROM clause is limited by the number of ** bits in a Bitmask @@ -6138,7 +6651,11 @@ WhereInfo *sqlite3WhereBegin( ){ pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE; } - ExplainQueryPlan((pParse, 0, "SCAN CONSTANT ROW")); + if( ALWAYS(pWInfo->pSelect) + && (pWInfo->pSelect->selFlags & SF_MultiValue)==0 + ){ + ExplainQueryPlan((pParse, 0, "SCAN CONSTANT ROW")); + } }else{ /* Assign a bit from the bitmask to every term in the FROM clause. ** @@ -6291,7 +6808,8 @@ WhereInfo *sqlite3WhereBegin( wherePathSolver(pWInfo, 0); if( db->mallocFailed ) goto whereBeginError; if( pWInfo->pOrderBy ){ - wherePathSolver(pWInfo, pWInfo->nRowOut+1); + whereInterstageHeuristic(pWInfo); + wherePathSolver(pWInfo, pWInfo->nRowOut<0 ? 1 : pWInfo->nRowOut+1); if( db->mallocFailed ) goto whereBeginError; } @@ -6351,10 +6869,10 @@ WhereInfo *sqlite3WhereBegin( ** in-line sqlite3WhereCodeOneLoopStart() for performance reasons. */ notReady = ~(Bitmask)0; - if( pWInfo->nLevel>=2 - && pResultSet!=0 /* these two combine to guarantee */ - && 0==(wctrlFlags & WHERE_AGG_DISTINCT) /* condition (1) above */ - && OptimizationEnabled(db, SQLITE_OmitNoopJoin) + if( pWInfo->nLevel>=2 /* Must be a join, or this opt8n is pointless */ + && pResultSet!=0 /* Condition (1) */ + && 0==(wctrlFlags & (WHERE_AGG_DISTINCT|WHERE_KEEP_ALL_JOINS)) /* (1),(6) */ + && OptimizationEnabled(db, SQLITE_OmitNoopJoin) /* (7) */ ){ notReady = whereOmitNoopJoin(pWInfo, notReady); nTabList = pWInfo->nLevel; @@ -6402,15 +6920,15 @@ WhereInfo *sqlite3WhereBegin( if( (wctrlFlags & WHERE_ONEPASS_DESIRED)!=0 ){ int wsFlags = pWInfo->a[0].pWLoop->wsFlags; int bOnerow = (wsFlags & WHERE_ONEROW)!=0; - assert( !(wsFlags & WHERE_VIRTUALTABLE) || IsVirtual(pTabList->a[0].pTab) ); + assert( !(wsFlags&WHERE_VIRTUALTABLE) || IsVirtual(pTabList->a[0].pSTab) ); if( bOnerow || ( 0!=(wctrlFlags & WHERE_ONEPASS_MULTIROW) - && !IsVirtual(pTabList->a[0].pTab) + && !IsVirtual(pTabList->a[0].pSTab) && (0==(wsFlags & WHERE_MULTI_OR) || (wctrlFlags & WHERE_DUPLICATES_OK)) && OptimizationEnabled(db, SQLITE_OnePass) )){ pWInfo->eOnePass = bOnerow ? ONEPASS_SINGLE : ONEPASS_MULTI; - if( HasRowid(pTabList->a[0].pTab) && (wsFlags & WHERE_IDX_ONLY) ){ + if( HasRowid(pTabList->a[0].pSTab) && (wsFlags & WHERE_IDX_ONLY) ){ if( wctrlFlags & WHERE_ONEPASS_MULTIROW ){ bFordelete = OPFLAG_FORDELETE; } @@ -6428,7 +6946,7 @@ WhereInfo *sqlite3WhereBegin( SrcItem *pTabItem; pTabItem = &pTabList->a[pLevel->iFrom]; - pTab = pTabItem->pTab; + pTab = pTabItem->pSTab; iDb = sqlite3SchemaToIndex(db, pTab->pSchema); pLoop = pLevel->pWLoop; if( (pTab->tabFlags & TF_Ephemeral)!=0 || IsView(pTab) ){ @@ -6499,7 +7017,7 @@ WhereInfo *sqlite3WhereBegin( iIndexCur = pLevel->iTabCur; op = 0; }else if( pWInfo->eOnePass!=ONEPASS_OFF ){ - Index *pJ = pTabItem->pTab->pIndex; + Index *pJ = pTabItem->pSTab->pIndex; iIndexCur = iAuxArg; assert( wctrlFlags & WHERE_ONEPASS_DESIRED ); while( ALWAYS(pJ) && pJ!=pIx ){ @@ -6566,7 +7084,7 @@ WhereInfo *sqlite3WhereBegin( sqlite3VdbeAddOp2(v, OP_Blob, 65536, pRJ->regBloom); pRJ->regReturn = ++pParse->nMem; sqlite3VdbeAddOp2(v, OP_Null, 0, pRJ->regReturn); - assert( pTab==pTabItem->pTab ); + assert( pTab==pTabItem->pSTab ); if( HasRowid(pTab) ){ KeyInfo *pInfo; sqlite3VdbeAddOp2(v, OP_OpenEphemeral, pRJ->iMatch, 1); @@ -6605,13 +7123,18 @@ WhereInfo *sqlite3WhereBegin( wsFlags = pLevel->pWLoop->wsFlags; pSrc = &pTabList->a[pLevel->iFrom]; if( pSrc->fg.isMaterialized ){ - if( pSrc->fg.isCorrelated ){ - sqlite3VdbeAddOp2(v, OP_Gosub, pSrc->regReturn, pSrc->addrFillSub); + Subquery *pSubq; + int iOnce = 0; + assert( pSrc->fg.isSubquery ); + pSubq = pSrc->u4.pSubq; + if( pSrc->fg.isCorrelated==0 ){ + iOnce = sqlite3VdbeAddOp0(v, OP_Once); VdbeCoverage(v); }else{ - int iOnce = sqlite3VdbeAddOp0(v, OP_Once); VdbeCoverage(v); - sqlite3VdbeAddOp2(v, OP_Gosub, pSrc->regReturn, pSrc->addrFillSub); - sqlite3VdbeJumpHere(v, iOnce); + iOnce = 0; } + sqlite3VdbeAddOp2(v, OP_Gosub, pSubq->regReturn, pSubq->addrFillSub); + VdbeComment((v, "materialize %!S", pSrc)); + if( iOnce ) sqlite3VdbeJumpHere(v, iOnce); } assert( pTabList == pWInfo->pTabList ); if( (wsFlags & (WHERE_AUTO_INDEX|WHERE_BLOOMFILTER))!=0 ){ @@ -6674,26 +7197,6 @@ whereBeginError: } #endif -#ifdef SQLITE_DEBUG -/* -** Return true if cursor iCur is opened by instruction k of the -** bytecode. Used inside of assert() only. -*/ -static int cursorIsOpen(Vdbe *v, int iCur, int k){ - while( k>=0 ){ - VdbeOp *pOp = sqlite3VdbeGetOp(v,k--); - if( pOp->p1!=iCur ) continue; - if( pOp->opcode==OP_Close ) return 0; - if( pOp->opcode==OP_OpenRead ) return 1; - if( pOp->opcode==OP_OpenWrite ) return 1; - if( pOp->opcode==OP_OpenDup ) return 1; - if( pOp->opcode==OP_OpenAutoindex ) return 1; - if( pOp->opcode==OP_OpenEphemeral ) return 1; - } - return 0; -} -#endif /* SQLITE_DEBUG */ - /* ** Generate the end of the WHERE loop. See comments on ** sqlite3WhereBegin() for additional information. @@ -6840,7 +7343,16 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){ addr = sqlite3VdbeAddOp1(v, OP_IfPos, pLevel->iLeftJoin); VdbeCoverage(v); assert( (ws & WHERE_IDX_ONLY)==0 || (ws & WHERE_INDEXED)!=0 ); if( (ws & WHERE_IDX_ONLY)==0 ){ - assert( pLevel->iTabCur==pTabList->a[pLevel->iFrom].iCursor ); + SrcItem *pSrc = &pTabList->a[pLevel->iFrom]; + assert( pLevel->iTabCur==pSrc->iCursor ); + if( pSrc->fg.viaCoroutine ){ + int m, n; + assert( pSrc->fg.isSubquery ); + n = pSrc->u4.pSubq->regResult; + assert( pSrc->pSTab!=0 ); + m = pSrc->pSTab->nCol; + sqlite3VdbeAddOp3(v, OP_Null, 0, n, n+m-1); + } sqlite3VdbeAddOp1(v, OP_NullRow, pLevel->iTabCur); } if( (ws & WHERE_INDEXED) @@ -6862,7 +7374,7 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){ sqlite3VdbeJumpHere(v, addr); } VdbeModuleComment((v, "End WHERE-loop%d: %s", i, - pWInfo->pTabList->a[pLevel->iFrom].pTab->zName)); + pWInfo->pTabList->a[pLevel->iFrom].pSTab->zName)); } assert( pWInfo->nLevel<=pTabList->nSrc ); @@ -6871,7 +7383,7 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){ VdbeOp *pOp, *pLastOp; Index *pIdx = 0; SrcItem *pTabItem = &pTabList->a[pLevel->iFrom]; - Table *pTab = pTabItem->pTab; + Table *pTab = pTabItem->pSTab; assert( pTab!=0 ); pLoop = pLevel->pWLoop; @@ -6890,8 +7402,10 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){ */ if( pTabItem->fg.viaCoroutine ){ testcase( pParse->db->mallocFailed ); + assert( pTabItem->fg.isSubquery ); + assert( pTabItem->u4.pSubq->regResult>=0 ); translateColumnToCopy(pParse, pLevel->addrBody, pLevel->iTabCur, - pTabItem->regResult, 0); + pTabItem->u4.pSubq->regResult, 0); continue; } @@ -6984,16 +7498,10 @@ void sqlite3WhereEnd(WhereInfo *pWInfo){ ** reference. Verify that this is harmless - that the ** table being referenced really is open. */ -#ifdef SQLITE_ENABLE_OFFSET_SQL_FUNC - assert( (pLoop->wsFlags & WHERE_IDX_ONLY)==0 - || cursorIsOpen(v,pOp->p1,k) - || pOp->opcode==OP_Offset - ); -#else - assert( (pLoop->wsFlags & WHERE_IDX_ONLY)==0 - || cursorIsOpen(v,pOp->p1,k) - ); -#endif + if( pLoop->wsFlags & WHERE_IDX_ONLY ){ + sqlite3ErrorMsg(pParse, "internal query planner error"); + pParse->rc = SQLITE_INTERNAL; + } } }else if( pOp->opcode==OP_Rowid ){ pOp->p1 = pLevel->iIdxCur; |