From bb52308f3d53949407c13e7cc624b1983b0d4034 Mon Sep 17 00:00:00 2001 From: drh Date: Thu, 27 Aug 2015 15:58:51 +0000 Subject: [PATCH] Adjustments to the WHERE term scanning, to better handle scanning terms of an index. FossilOrigin-Name: 5611130a595e7f0b6d5f21d76f2755e9c09c7810 --- manifest | 12 ++++++------ manifest.uuid | 2 +- src/where.c | 53 ++++++++++++++++++++++++++++++++++----------------- 3 files changed, 43 insertions(+), 24 deletions(-) diff --git a/manifest b/manifest index cc1cce44df..8a7ec7eaf1 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Merge\senhancements\sfrom\strunk. -D 2015-08-26T18:04:23.442 +C Adjustments\sto\sthe\sWHERE\sterm\sscanning,\sto\sbetter\shandle\sscanning\sterms\sof\nan\sindex. +D 2015-08-27T15:58:51.073 F Makefile.arm-wince-mingw32ce-gcc d6df77f1f48d690bd73162294bbba7f59507c72f F Makefile.in e2218eb228374422969de7b1680eda6864affcef F Makefile.linux-gcc 91d710bdc4998cb015f39edf3cb314ec4f4d7e23 @@ -414,7 +414,7 @@ F src/vxworks.h c18586c8edc1bddbc15c004fa16aeb1e1342b4fb F src/wal.c 6fb6b68969e4692593c2552c4e7bff5882de2cb8 F src/wal.h df01efe09c5cb8c8e391ff1715cca294f89668a4 F src/walker.c 2e14d17f592d176b6dc879c33fbdec4fbccaa2ba -F src/where.c fb8546b8053433bfb39f6c39fdc99c111a6f97c5 +F src/where.c 0709bbc33117a21db57a031b1452a35de14bf86b F src/whereInt.h 880a8599226ac1c00203490d934f3ed79b292572 F src/wherecode.c 3d9113cc307ffeed58db41fe9f2d807c94787ab5 F src/whereexpr.c 1a308d1ee5144890d21ea9cf70d49bc96a83432b @@ -1380,7 +1380,7 @@ F tool/vdbe_profile.tcl 67746953071a9f8f2f668b73fe899074e2c6d8c1 F tool/warnings-clang.sh f6aa929dc20ef1f856af04a730772f59283631d4 F tool/warnings.sh 48bd54594752d5be3337f12c72f28d2080cb630b F tool/win/sqlite.vsix deb315d026cc8400325c5863eef847784a219a2f -P e8b02902c48f3668dcff7b1767347ac7ef3c971e c2f3bbad778504681b39ab9399a1eb3c1a35ab3f -R 81693495b2084fce0fa08431d5ef28b1 +P ec6ddb3d481d005c304a26c948c9c808586750e9 +R e5ffdfd8fde6c8b7686cb0a9ca3923f7 U drh -Z bec0cbafd7b79403eeb50b2f9a3289c8 +Z 4ee413196597973385c2eb895ecb052b diff --git a/manifest.uuid b/manifest.uuid index c6150c8ad4..5b29dbd27d 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -ec6ddb3d481d005c304a26c948c9c808586750e9 \ No newline at end of file +5611130a595e7f0b6d5f21d76f2755e9c09c7810 \ No newline at end of file diff --git a/src/where.c b/src/where.c index 9789b0848d..f3454c0fd0 100644 --- a/src/where.c +++ b/src/where.c @@ -273,11 +273,12 @@ static WhereTerm *whereScanInit( /* memset(pScan, 0, sizeof(*pScan)); */ pScan->pOrigWC = pWC; pScan->pWC = pWC; + if( pIdx ){ + j = iColumn; + iColumn = pIdx->aiColumn[j]; + } if( pIdx && iColumn>=0 ){ pScan->idxaff = pIdx->pTable->aCol[iColumn].affinity; - for(j=0; pIdx->aiColumn[j]!=iColumn; j++){ - if( NEVER(j>pIdx->nColumn) ) return 0; - } pScan->zCollName = pIdx->azColl[j]; }else{ pScan->idxaff = 0; @@ -298,6 +299,9 @@ static WhereTerm *whereScanInit( ** the WO_xx operator codes specified by the op parameter. ** Return a pointer to the term. Return 0 if not found. ** +** If pIdx!=0 then search for terms matching the iColumn-th column of pIdx +** rather than the iColumn-th column of table iCur. +** ** The term returned might by Y= if there is another constraint in ** the WHERE clause that specifies that X=Y. Any such constraints will be ** identified by the WO_EQUIV bit in the pTerm->eOperator field. The @@ -375,6 +379,24 @@ static int findIndexCol( return -1; } +/* +** Return TRUE if the iCol-th column of index pIdx is NOT NULL +*/ +static int indexColumnNotNull(Index *pIdx, int iCol){ + int j; + assert( pIdx!=0 ); + assert( iCol>=0 && iColnColumn ); + j = pIdx->aiColumn[iCol]; + if( j>=0 ){ + return pIdx->pTable->aCol[j].notNull; + }else if( j==(-1) ){ + return 1; + }else{ + assert( j==(-2) ); + return !sqlite3ExprCanBeNull(pIdx->aColExpr->a[iCol].pExpr); + } +} + /* ** Return true if the DISTINCT expression-list passed as the third argument ** is redundant. @@ -425,12 +447,9 @@ static int isDistinctRedundant( for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ if( !IsUniqueIndex(pIdx) ) continue; for(i=0; inKeyCol; i++){ - i16 iCol = pIdx->aiColumn[i]; - if( 0==sqlite3WhereFindTerm(pWC, iBase, iCol, ~(Bitmask)0, WO_EQ, pIdx) ){ - int iIdxCol = findIndexCol(pParse, pDistinct, iBase, pIdx, i); - if( iIdxCol<0 || pTab->aCol[iCol].notNull==0 ){ - break; - } + if( 0==sqlite3WhereFindTerm(pWC, iBase, i, ~(Bitmask)0, WO_EQ, pIdx) ){ + if( findIndexCol(pParse, pDistinct, iBase, pIdx, i)<0 ) break; + if( indexColumnNotNull(pIdx, i)==0 ) break; } } if( i==pIdx->nKeyCol ){ @@ -2126,7 +2145,6 @@ static int whereLoopAddBtreeIndex( u16 saved_nSkip; /* Original value of pNew->nSkip */ u32 saved_wsFlags; /* Original value of pNew->wsFlags */ LogEst saved_nOut; /* Original value of pNew->nOut */ - int iCol; /* Index of the column in the table */ int rc = SQLITE_OK; /* Return code */ LogEst rSize; /* Number of rows in the table */ LogEst rLogSize; /* Logarithm of table size */ @@ -2147,16 +2165,15 @@ static int whereLoopAddBtreeIndex( if( pProbe->bUnordered ) opMask &= ~(WO_GT|WO_GE|WO_LT|WO_LE); assert( pNew->u.btree.nEqnColumn ); - iCol = pProbe->aiColumn[pNew->u.btree.nEq]; - pTerm = whereScanInit(&scan, pBuilder->pWC, pSrc->iCursor, iCol, - opMask, pProbe); saved_nEq = pNew->u.btree.nEq; saved_nSkip = pNew->nSkip; saved_nLTerm = pNew->nLTerm; saved_wsFlags = pNew->wsFlags; saved_prereq = pNew->prereq; saved_nOut = pNew->nOut; + pTerm = whereScanInit(&scan, pBuilder->pWC, pSrc->iCursor, saved_nEq, + opMask, pProbe); pNew->rSetup = 0; rSize = pProbe->aiRowLogEst[0]; rLogSize = estLog(rSize); @@ -2169,7 +2186,7 @@ static int whereLoopAddBtreeIndex( int nRecValid = pBuilder->nRecValid; #endif if( (eOp==WO_ISNULL || (pTerm->wtFlags&TERM_VNULL)!=0) - && (iCol<0 || pSrc->pTab->aCol[iCol].notNull) + && indexColumnNotNull(pProbe, saved_nEq) ){ continue; /* ignore IS [NOT] NULL constraints on NOT NULL columns */ } @@ -2206,8 +2223,10 @@ static int whereLoopAddBtreeIndex( ** changes "x IN (?)" into "x=?". */ }else if( eOp & (WO_EQ|WO_IS) ){ + int iCol = pProbe->aiColumn[saved_nEq]; pNew->wsFlags |= WHERE_COLUMN_EQ; - if( iCol<0 || (nInMul==0 && pNew->u.btree.nEq==pProbe->nKeyCol-1) ){ + assert( saved_nEq==pNew->u.btree.nEq ); + if( iCol==(-1) || (nInMul==0 && saved_nEq==pProbe->nKeyCol-1) ){ if( iCol>=0 && pProbe->uniqNotNull==0 ){ pNew->wsFlags |= WHERE_UNQ_WANTED; }else{ @@ -2258,7 +2277,7 @@ static int whereLoopAddBtreeIndex( assert( eOp & (WO_ISNULL|WO_EQ|WO_IN|WO_IS) ); assert( pNew->nOut==saved_nOut ); - if( pTerm->truthProb<=0 && iCol>=0 ){ + if( pTerm->truthProb<=0 && pProbe->aiColumn[saved_nEq]>=0 ){ assert( (eOp & WO_IN) || nIn==0 ); testcase( eOp & WO_IN ); pNew->nOut += pTerm->truthProb; @@ -3785,7 +3804,7 @@ static int whereShortCut(WhereLoopBuilder *pBuilder){ ) continue; opMask = pIdx->uniqNotNull ? (WO_EQ|WO_IS) : WO_EQ; for(j=0; jnKeyCol; j++){ - pTerm = sqlite3WhereFindTerm(pWC, iCur, pIdx->aiColumn[j], 0, opMask, pIdx); + pTerm = sqlite3WhereFindTerm(pWC, iCur, j, 0, opMask, pIdx); if( pTerm==0 ) break; testcase( pTerm->eOperator & WO_IS ); pLoop->aLTerm[j] = pTerm; -- 2.47.2