Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | A new implementation of indexing with the IS operator that works correctly when the IS operator is in the WHERE clause and the operands are from opposite sides of a LEFT JOIN. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | index-is-operator |
| Files: | files | file ages | folders |
| SHA1: |
4541688b3f56f5cd3d5b299594b58c57 |
| User & Date: | drh 2015-05-14 01:05:25 |
Context
|
2015-05-14
| ||
| 13:18 | Add testcase() macros. Get transitive WHERE clause constraints on IS operators working again. check-in: d195d4a6 user: drh tags: index-is-operator | |
| 09:53 | Merge changes from the index-is-operator branch into this one. Drop the partial support added for IS on this branch by [52e73eec]. check-in: 16ab9caf user: dan tags: ota-update | |
| 01:05 | A new implementation of indexing with the IS operator that works correctly when the IS operator is in the WHERE clause and the operands are from opposite sides of a LEFT JOIN. check-in: 4541688b user: drh tags: index-is-operator | |
|
2015-05-13
| ||
| 19:33 | Simplified implementation of indexing with the IS operator. check-in: 95b1f9bf user: drh tags: index-is-operator | |
Changes
Changes to src/where.c.
413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 ... 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 ... 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 .... 1675 1676 1677 1678 1679 1680 1681 1682 1683 1684 1685 1686 1687 1688 1689 .... 1951 1952 1953 1954 1955 1956 1957 1958 1959 1960 1961 1962 1963 1964 1965 .... 4100 4101 4102 4103 4104 4105 4106 4107 4108 4109 4110 4111 4112 4113 4114 4115 4116 4117 4118 4119 4120 4121 4122 4123 .... 4652 4653 4654 4655 4656 4657 4658 4659 4660 4661 4662 4663 4664 4665 4666 .... 4725 4726 4727 4728 4729 4730 4731 4732 4733 4734 4735 4736 4737 4738 4739 .... 4788 4789 4790 4791 4792 4793 4794 4795 4796 4797 4798 4799 4800 4801 4802 .... 4838 4839 4840 4841 4842 4843 4844 4845 4846 4847 4848 4849 4850 4851 4852 .... 4855 4856 4857 4858 4859 4860 4861 4862 4863 4864 4865 4866 4867 4868 4869 4870 .... 5693 5694 5695 5696 5697 5698 5699 5700 5701 5702 5703 5704 5705 5706 5707 5708 5709 .... 5735 5736 5737 5738 5739 5740 5741 5742 5743 5744 5745 5746 5747 5748 5749 .... 6308 6309 6310 6311 6312 6313 6314 6315 6316 6317 6318 6319 6320 6321 6322 6323 .... 6324 6325 6326 6327 6328 6329 6330 6331 6332 6333 6334 6335 6336 6337 6338 6339 6340 6341 |
u16 c;
assert( allowedOp(op) );
if( op==TK_IN ){
c = WO_IN;
}else if( op==TK_ISNULL ){
c = WO_ISNULL;
}else if( op==TK_IS ){
c = WO_EQ; /* IS works like ==, just without the IsNull tests */
}else{
assert( (WO_EQ<<(op-TK_EQ)) < 0x7fff );
c = (u16)(WO_EQ<<(op-TK_EQ));
}
assert( op!=TK_ISNULL || c==WO_ISNULL );
assert( op!=TK_IN || c==WO_IN );
assert( op!=TK_EQ || c==WO_EQ );
assert( op!=TK_LT || c==WO_LT );
assert( op!=TK_LE || c==WO_LE );
assert( op!=TK_GT || c==WO_GT );
assert( op!=TK_GE || c==WO_GE );
assert( op!=TK_IS || c==WO_EQ );
return c;
}
/*
** 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.
................................................................................
pColl = sqlite3BinaryCompareCollSeq(pParse,
pX->pLeft, pX->pRight);
if( pColl==0 ) pColl = pParse->db->pDfltColl;
if( sqlite3StrICmp(pColl->zName, pScan->zCollName) ){
continue;
}
}
if( (pTerm->eOperator & WO_EQ)!=0
&& (pX = pTerm->pExpr->pRight)->op==TK_COLUMN
&& pX->iTable==pScan->aEquiv[0]
&& pX->iColumn==pScan->aEquiv[1]
){
continue;
}
pScan->k = k+1;
return pTerm;
}
}
}
................................................................................
Index *pIdx /* Must be compatible with this index, if not NULL */
){
WhereTerm *pResult = 0;
WhereTerm *p;
WhereScan scan;
p = whereScanInit(&scan, pWC, iCur, iColumn, op, pIdx);
while( p ){
if( (p->prereqRight & notReady)==0 ){
if( p->prereqRight==0 && (p->eOperator&WO_EQ)!=0 ){
return p;
}
if( pResult==0 ) pResult = p;
}
p = whereScanNext(&scan);
}
return pResult;
................................................................................
static int termCanDriveIndex(
WhereTerm *pTerm, /* WHERE clause term to check */
struct SrcList_item *pSrc, /* Table we are trying to access */
Bitmask notReady /* Tables in outer loops of the join */
){
char aff;
if( pTerm->leftCursor!=pSrc->iCursor ) return 0;
if( (pTerm->eOperator & WO_EQ)==0 ) return 0;
if( (pTerm->prereqRight & notReady)!=0 ) return 0;
if( pTerm->u.leftColumn<0 ) return 0;
aff = pSrc->pTab->aCol[pTerm->u.leftColumn].affinity;
if( !sqlite3IndexAffinityOk(pTerm->pExpr, aff) ) return 0;
testcase( pTerm->pExpr->op==TK_IS );
return 1;
}
................................................................................
for(i=j=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
u8 op;
if( pTerm->leftCursor != pSrc->iCursor ) continue;
assert( IsPowerOfTwo(pTerm->eOperator & ~WO_EQUIV) );
testcase( pTerm->eOperator & WO_IN );
testcase( pTerm->eOperator & WO_ISNULL );
testcase( pTerm->eOperator & WO_ALL );
if( (pTerm->eOperator & ~(WO_ISNULL|WO_EQUIV))==0 ) continue;
if( pTerm->wtFlags & TERM_VNULL ) continue;
pIdxCons[j].iColumn = pTerm->u.leftColumn;
pIdxCons[j].iTermOffset = i;
op = (u8)pTerm->eOperator & WO_ALL;
if( op==WO_IN ) op = WO_EQ;
pIdxCons[j].op = op;
/* The direct assignment in the previous line is possible only because
................................................................................
** then we cannot use the "t1.a=t2.b" constraint, but we can code
** the implied "t1.a=123" constraint.
*/
for(pTerm=pWC->a, j=pWC->nTerm; j>0; j--, pTerm++){
Expr *pE, *pEAlt;
WhereTerm *pAlt;
if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue;
if( pTerm->eOperator!=(WO_EQUIV|WO_EQ) ) continue;
if( pTerm->leftCursor!=iCur ) continue;
if( pLevel->iLeftJoin ) continue;
pE = pTerm->pExpr;
assert( !ExprHasProperty(pE, EP_FromJoin) );
assert( (pTerm->prereqRight & pLevel->notReady)!=0 );
pAlt = findTerm(pWC, iCur, pTerm->u.leftColumn, notReady, WO_EQ|WO_IN, 0);
if( pAlt==0 ) continue;
if( pAlt->wtFlags & (TERM_CODED) ) continue;
testcase( (pAlt->eOperator & WO_EQ)!=0 && pAlt->pExpr->op==TK_IS );
testcase( pAlt->eOperator & WO_IN );
VdbeModuleComment((v, "begin transitive constraint"));
pEAlt = sqlite3StackAllocRaw(db, sizeof(*pEAlt));
if( pEAlt ){
*pEAlt = *pAlt->pExpr;
pEAlt->pLeft = pE->pLeft;
sqlite3ExprIfFalse(pParse, pEAlt, addrCont, SQLITE_JUMPIFNULL);
................................................................................
/* If a truth probability is specified using the likelihood() hints,
** then use the probability provided by the application. */
pLoop->nOut += pTerm->truthProb;
}else{
/* In the absence of explicit truth probabilities, use heuristics to
** guess a reasonable truth probability. */
pLoop->nOut--;
if( pTerm->eOperator&WO_EQ ){
Expr *pRight = pTerm->pExpr->pRight;
testcase( pTerm->pExpr->op==TK_IS );
if( sqlite3ExprIsInteger(pRight, &k) && k>=(-1) && k<=1 ){
k = 10;
}else{
k = 20;
}
................................................................................
assert( (pNew->wsFlags & WHERE_VIRTUALTABLE)==0 );
assert( (pNew->wsFlags & WHERE_TOP_LIMIT)==0 );
if( pNew->wsFlags & WHERE_BTM_LIMIT ){
opMask = WO_LT|WO_LE;
}else if( pProbe->tnum<=0 || (pSrc->jointype & JT_LEFT)!=0 ){
opMask = WO_EQ|WO_IN|WO_GT|WO_GE|WO_LT|WO_LE;
}else{
opMask = WO_EQ|WO_IN|WO_ISNULL|WO_GT|WO_GE|WO_LT|WO_LE;
}
if( pProbe->bUnordered ) opMask &= ~(WO_GT|WO_GE|WO_LT|WO_LE);
assert( pNew->u.btree.nEq<pProbe->nColumn );
iCol = pProbe->aiColumn[pNew->u.btree.nEq];
pTerm = whereScanInit(&scan, pBuilder->pWC, pSrc->iCursor, iCol,
................................................................................
}else if( ALWAYS(pExpr->x.pList && pExpr->x.pList->nExpr) ){
/* "x IN (value, value, ...)" */
nIn = sqlite3LogEst(pExpr->x.pList->nExpr);
}
assert( nIn>0 ); /* RHS always has 2 or more terms... The parser
** changes "x IN (?)" into "x=?". */
}else if( eOp & (WO_EQ) ){
pNew->wsFlags |= WHERE_COLUMN_EQ;
if( iCol<0 || (nInMul==0 && pNew->u.btree.nEq==pProbe->nKeyCol-1) ){
if( iCol>=0 && pProbe->uniqNotNull==0 ){
pNew->wsFlags |= WHERE_UNQ_WANTED;
}else{
pNew->wsFlags |= WHERE_ONEROW;
}
................................................................................
assert( pNew->nOut==saved_nOut );
if( pNew->wsFlags & WHERE_COLUMN_RANGE ){
/* Adjust nOut using stat3/stat4 data. Or, if there is no stat3/stat4
** data, using some other estimate. */
whereRangeScanEst(pParse, pBuilder, pBtm, pTop, pNew);
}else{
int nEq = ++pNew->u.btree.nEq;
assert( eOp & (WO_ISNULL|WO_EQ|WO_IN) );
assert( pNew->nOut==saved_nOut );
if( pTerm->truthProb<=0 && iCol>=0 ){
assert( (eOp & WO_IN) || nIn==0 );
testcase( eOp & WO_IN );
pNew->nOut += pTerm->truthProb;
pNew->nOut -= nIn;
................................................................................
tRowcnt nOut = 0;
if( nInMul==0
&& pProbe->nSample
&& pNew->u.btree.nEq<=pProbe->nSampleCol
&& ((eOp & WO_IN)==0 || !ExprHasProperty(pTerm->pExpr, EP_xIsSelect))
){
Expr *pExpr = pTerm->pExpr;
if( (eOp & (WO_EQ|WO_ISNULL))!=0 ){
testcase( (eOp & WO_EQ)!=0 && pExpr->op==TK_IS );
testcase( eOp & WO_ISNULL );
rc = whereEqualScanEst(pParse, pBuilder, pExpr->pRight, &nOut);
}else{
rc = whereInScanEst(pParse, pBuilder, pExpr->x.pList, &nOut);
}
if( rc==SQLITE_NOTFOUND ) rc = SQLITE_OK;
if( rc!=SQLITE_OK ) break; /* Jump out of the pTerm loop */
................................................................................
*/
for(i=0; i<nOrderBy; i++){
if( MASKBIT(i) & obSat ) continue;
pOBExpr = sqlite3ExprSkipCollate(pOrderBy->a[i].pExpr);
if( pOBExpr->op!=TK_COLUMN ) continue;
if( pOBExpr->iTable!=iCur ) continue;
pTerm = findTerm(&pWInfo->sWC, iCur, pOBExpr->iColumn,
~ready, WO_EQ|WO_ISNULL, 0);
if( pTerm==0 ) continue;
if( (pTerm->eOperator&WO_EQ)!=0 && pOBExpr->iColumn>=0 ){
const char *z1, *z2;
pColl = sqlite3ExprCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr);
if( !pColl ) pColl = db->pDfltColl;
z1 = pColl->zName;
pColl = sqlite3ExprCollSeq(pWInfo->pParse, pTerm->pExpr);
if( !pColl ) pColl = db->pDfltColl;
z2 = pColl->zName;
................................................................................
distinctColumns = 0;
for(j=0; j<nColumn; j++){
u8 bOnce; /* True to run the ORDER BY search loop */
/* Skip over == and IS NULL terms */
if( j<pLoop->u.btree.nEq
&& pLoop->nSkip==0
&& ((i = pLoop->aLTerm[j]->eOperator) & (WO_EQ|WO_ISNULL))!=0
){
if( i & WO_ISNULL ){
testcase( isOrderDistinct );
isOrderDistinct = 0;
}
continue;
}
................................................................................
if( IsVirtual(pTab) ) return 0;
if( pItem->zIndex ) return 0;
iCur = pItem->iCursor;
pWC = &pWInfo->sWC;
pLoop = pBuilder->pNew;
pLoop->wsFlags = 0;
pLoop->nSkip = 0;
pTerm = findTerm(pWC, iCur, -1, 0, WO_EQ, 0);
if( pTerm ){
pLoop->wsFlags = WHERE_COLUMN_EQ|WHERE_IPK|WHERE_ONEROW;
pLoop->aLTerm[0] = pTerm;
pLoop->nLTerm = 1;
pLoop->u.btree.nEq = 1;
/* TUNING: Cost of a rowid lookup is 10 */
pLoop->rRun = 33; /* 33==sqlite3LogEst(10) */
}else{
................................................................................
for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
assert( pLoop->aLTermSpace==pLoop->aLTerm );
if( !IsUniqueIndex(pIdx)
|| pIdx->pPartIdxWhere!=0
|| pIdx->nKeyCol>ArraySize(pLoop->aLTermSpace)
) continue;
for(j=0; j<pIdx->nKeyCol; j++){
pTerm = findTerm(pWC, iCur, pIdx->aiColumn[j], 0, WO_EQ, pIdx);
if( pTerm==0 ) break;
pLoop->aLTerm[j] = pTerm;
testcase( pTerm->pExpr->op==TK_IS );
}
if( j!=pIdx->nKeyCol ) continue;
pLoop->wsFlags = WHERE_COLUMN_EQ|WHERE_ONEROW|WHERE_INDEXED;
if( pIdx->isCovering || (pItem->colUsed & ~columnsInIndex(pIdx))==0 ){
pLoop->wsFlags |= WHERE_IDX_ONLY;
}
pLoop->nLTerm = j;
|
| | | > > | > | | | | > | > | | | | | | > | | | | > | > < |
413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 ... 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 ... 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 .... 1678 1679 1680 1681 1682 1683 1684 1685 1686 1687 1688 1689 1690 1691 1692 .... 1954 1955 1956 1957 1958 1959 1960 1961 1962 1963 1964 1965 1966 1967 1968 .... 4103 4104 4105 4106 4107 4108 4109 4110 4111 4112 4113 4114 4115 4116 4117 4118 4119 4120 4121 4122 4123 4124 4125 4126 4127 4128 .... 4657 4658 4659 4660 4661 4662 4663 4664 4665 4666 4667 4668 4669 4670 4671 .... 4730 4731 4732 4733 4734 4735 4736 4737 4738 4739 4740 4741 4742 4743 4744 .... 4793 4794 4795 4796 4797 4798 4799 4800 4801 4802 4803 4804 4805 4806 4807 .... 4843 4844 4845 4846 4847 4848 4849 4850 4851 4852 4853 4854 4855 4856 4857 .... 4860 4861 4862 4863 4864 4865 4866 4867 4868 4869 4870 4871 4872 4873 4874 4875 4876 .... 5699 5700 5701 5702 5703 5704 5705 5706 5707 5708 5709 5710 5711 5712 5713 5714 5715 .... 5741 5742 5743 5744 5745 5746 5747 5748 5749 5750 5751 5752 5753 5754 5755 .... 6314 6315 6316 6317 6318 6319 6320 6321 6322 6323 6324 6325 6326 6327 6328 6329 6330 .... 6331 6332 6333 6334 6335 6336 6337 6338 6339 6340 6341 6342 6343 6344 6345 6346 6347 6348 |
u16 c;
assert( allowedOp(op) );
if( op==TK_IN ){
c = WO_IN;
}else if( op==TK_ISNULL ){
c = WO_ISNULL;
}else if( op==TK_IS ){
c = WO_IS;
}else{
assert( (WO_EQ<<(op-TK_EQ)) < 0x7fff );
c = (u16)(WO_EQ<<(op-TK_EQ));
}
assert( op!=TK_ISNULL || c==WO_ISNULL );
assert( op!=TK_IN || c==WO_IN );
assert( op!=TK_EQ || c==WO_EQ );
assert( op!=TK_LT || c==WO_LT );
assert( op!=TK_LE || c==WO_LE );
assert( op!=TK_GT || c==WO_GT );
assert( op!=TK_GE || c==WO_GE );
assert( op!=TK_IS || c==WO_IS );
return c;
}
/*
** 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.
................................................................................
pColl = sqlite3BinaryCompareCollSeq(pParse,
pX->pLeft, pX->pRight);
if( pColl==0 ) pColl = pParse->db->pDfltColl;
if( sqlite3StrICmp(pColl->zName, pScan->zCollName) ){
continue;
}
}
if( (pTerm->eOperator & (WO_EQ|WO_IS))!=0
&& (pX = pTerm->pExpr->pRight)->op==TK_COLUMN
&& pX->iTable==pScan->aEquiv[0]
&& pX->iColumn==pScan->aEquiv[1]
){
testcase( pTerm->eOperator & WO_IS );
continue;
}
pScan->k = k+1;
return pTerm;
}
}
}
................................................................................
Index *pIdx /* Must be compatible with this index, if not NULL */
){
WhereTerm *pResult = 0;
WhereTerm *p;
WhereScan scan;
p = whereScanInit(&scan, pWC, iCur, iColumn, op, pIdx);
op &= WO_EQ|WO_IS;
while( p ){
if( (p->prereqRight & notReady)==0 ){
if( p->prereqRight==0 && (p->eOperator&op)!=0 ){
testcase( p->eOperator & WO_IS );
return p;
}
if( pResult==0 ) pResult = p;
}
p = whereScanNext(&scan);
}
return pResult;
................................................................................
static int termCanDriveIndex(
WhereTerm *pTerm, /* WHERE clause term to check */
struct SrcList_item *pSrc, /* Table we are trying to access */
Bitmask notReady /* Tables in outer loops of the join */
){
char aff;
if( pTerm->leftCursor!=pSrc->iCursor ) return 0;
if( (pTerm->eOperator & (WO_EQ|WO_IS))==0 ) return 0;
if( (pTerm->prereqRight & notReady)!=0 ) return 0;
if( pTerm->u.leftColumn<0 ) return 0;
aff = pSrc->pTab->aCol[pTerm->u.leftColumn].affinity;
if( !sqlite3IndexAffinityOk(pTerm->pExpr, aff) ) return 0;
testcase( pTerm->pExpr->op==TK_IS );
return 1;
}
................................................................................
for(i=j=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
u8 op;
if( pTerm->leftCursor != pSrc->iCursor ) continue;
assert( IsPowerOfTwo(pTerm->eOperator & ~WO_EQUIV) );
testcase( pTerm->eOperator & WO_IN );
testcase( pTerm->eOperator & WO_ISNULL );
testcase( pTerm->eOperator & WO_ALL );
if( (pTerm->eOperator & ~(WO_ISNULL|WO_EQUIV|WO_IS))==0 ) continue;
if( pTerm->wtFlags & TERM_VNULL ) continue;
pIdxCons[j].iColumn = pTerm->u.leftColumn;
pIdxCons[j].iTermOffset = i;
op = (u8)pTerm->eOperator & WO_ALL;
if( op==WO_IN ) op = WO_EQ;
pIdxCons[j].op = op;
/* The direct assignment in the previous line is possible only because
................................................................................
** then we cannot use the "t1.a=t2.b" constraint, but we can code
** the implied "t1.a=123" constraint.
*/
for(pTerm=pWC->a, j=pWC->nTerm; j>0; j--, pTerm++){
Expr *pE, *pEAlt;
WhereTerm *pAlt;
if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue;
if( (pTerm->eOperator&(WO_EQUIV|WO_EQ|WO_IS))<=WO_EQUIV ) continue;
if( pTerm->leftCursor!=iCur ) continue;
if( pLevel->iLeftJoin ) continue;
pE = pTerm->pExpr;
assert( !ExprHasProperty(pE, EP_FromJoin) );
assert( (pTerm->prereqRight & pLevel->notReady)!=0 );
pAlt = findTerm(pWC, iCur, pTerm->u.leftColumn, notReady,
WO_EQ|WO_IN|WO_IS, 0);
if( pAlt==0 ) continue;
if( pAlt->wtFlags & (TERM_CODED) ) continue;
testcase( pAlt->eOperator & WO_EQ );
testcase( pAlt->eOperator & WO_IS );
testcase( pAlt->eOperator & WO_IN );
VdbeModuleComment((v, "begin transitive constraint"));
pEAlt = sqlite3StackAllocRaw(db, sizeof(*pEAlt));
if( pEAlt ){
*pEAlt = *pAlt->pExpr;
pEAlt->pLeft = pE->pLeft;
sqlite3ExprIfFalse(pParse, pEAlt, addrCont, SQLITE_JUMPIFNULL);
................................................................................
/* If a truth probability is specified using the likelihood() hints,
** then use the probability provided by the application. */
pLoop->nOut += pTerm->truthProb;
}else{
/* In the absence of explicit truth probabilities, use heuristics to
** guess a reasonable truth probability. */
pLoop->nOut--;
if( pTerm->eOperator&(WO_EQ|WO_IS) ){
Expr *pRight = pTerm->pExpr->pRight;
testcase( pTerm->pExpr->op==TK_IS );
if( sqlite3ExprIsInteger(pRight, &k) && k>=(-1) && k<=1 ){
k = 10;
}else{
k = 20;
}
................................................................................
assert( (pNew->wsFlags & WHERE_VIRTUALTABLE)==0 );
assert( (pNew->wsFlags & WHERE_TOP_LIMIT)==0 );
if( pNew->wsFlags & WHERE_BTM_LIMIT ){
opMask = WO_LT|WO_LE;
}else if( pProbe->tnum<=0 || (pSrc->jointype & JT_LEFT)!=0 ){
opMask = WO_EQ|WO_IN|WO_GT|WO_GE|WO_LT|WO_LE;
}else{
opMask = WO_EQ|WO_IN|WO_GT|WO_GE|WO_LT|WO_LE|WO_ISNULL|WO_IS;
}
if( pProbe->bUnordered ) opMask &= ~(WO_GT|WO_GE|WO_LT|WO_LE);
assert( pNew->u.btree.nEq<pProbe->nColumn );
iCol = pProbe->aiColumn[pNew->u.btree.nEq];
pTerm = whereScanInit(&scan, pBuilder->pWC, pSrc->iCursor, iCol,
................................................................................
}else if( ALWAYS(pExpr->x.pList && pExpr->x.pList->nExpr) ){
/* "x IN (value, value, ...)" */
nIn = sqlite3LogEst(pExpr->x.pList->nExpr);
}
assert( nIn>0 ); /* RHS always has 2 or more terms... The parser
** changes "x IN (?)" into "x=?". */
}else if( eOp & (WO_EQ|WO_IS) ){
pNew->wsFlags |= WHERE_COLUMN_EQ;
if( iCol<0 || (nInMul==0 && pNew->u.btree.nEq==pProbe->nKeyCol-1) ){
if( iCol>=0 && pProbe->uniqNotNull==0 ){
pNew->wsFlags |= WHERE_UNQ_WANTED;
}else{
pNew->wsFlags |= WHERE_ONEROW;
}
................................................................................
assert( pNew->nOut==saved_nOut );
if( pNew->wsFlags & WHERE_COLUMN_RANGE ){
/* Adjust nOut using stat3/stat4 data. Or, if there is no stat3/stat4
** data, using some other estimate. */
whereRangeScanEst(pParse, pBuilder, pBtm, pTop, pNew);
}else{
int nEq = ++pNew->u.btree.nEq;
assert( eOp & (WO_ISNULL|WO_EQ|WO_IN|WO_IS) );
assert( pNew->nOut==saved_nOut );
if( pTerm->truthProb<=0 && iCol>=0 ){
assert( (eOp & WO_IN) || nIn==0 );
testcase( eOp & WO_IN );
pNew->nOut += pTerm->truthProb;
pNew->nOut -= nIn;
................................................................................
tRowcnt nOut = 0;
if( nInMul==0
&& pProbe->nSample
&& pNew->u.btree.nEq<=pProbe->nSampleCol
&& ((eOp & WO_IN)==0 || !ExprHasProperty(pTerm->pExpr, EP_xIsSelect))
){
Expr *pExpr = pTerm->pExpr;
if( (eOp & (WO_EQ|WO_ISNULL|WO_IS))!=0 ){
testcase( eOp & WO_EQ );
testcase( eOp & WO_IS );
testcase( eOp & WO_ISNULL );
rc = whereEqualScanEst(pParse, pBuilder, pExpr->pRight, &nOut);
}else{
rc = whereInScanEst(pParse, pBuilder, pExpr->x.pList, &nOut);
}
if( rc==SQLITE_NOTFOUND ) rc = SQLITE_OK;
if( rc!=SQLITE_OK ) break; /* Jump out of the pTerm loop */
................................................................................
*/
for(i=0; i<nOrderBy; i++){
if( MASKBIT(i) & obSat ) continue;
pOBExpr = sqlite3ExprSkipCollate(pOrderBy->a[i].pExpr);
if( pOBExpr->op!=TK_COLUMN ) continue;
if( pOBExpr->iTable!=iCur ) continue;
pTerm = findTerm(&pWInfo->sWC, iCur, pOBExpr->iColumn,
~ready, WO_EQ|WO_ISNULL|WO_IS, 0);
if( pTerm==0 ) continue;
if( (pTerm->eOperator&(WO_EQ|WO_IS))!=0 && pOBExpr->iColumn>=0 ){
const char *z1, *z2;
pColl = sqlite3ExprCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr);
if( !pColl ) pColl = db->pDfltColl;
z1 = pColl->zName;
pColl = sqlite3ExprCollSeq(pWInfo->pParse, pTerm->pExpr);
if( !pColl ) pColl = db->pDfltColl;
z2 = pColl->zName;
................................................................................
distinctColumns = 0;
for(j=0; j<nColumn; j++){
u8 bOnce; /* True to run the ORDER BY search loop */
/* Skip over == and IS NULL terms */
if( j<pLoop->u.btree.nEq
&& pLoop->nSkip==0
&& ((i = pLoop->aLTerm[j]->eOperator) & (WO_EQ|WO_ISNULL|WO_IS))!=0
){
if( i & WO_ISNULL ){
testcase( isOrderDistinct );
isOrderDistinct = 0;
}
continue;
}
................................................................................
if( IsVirtual(pTab) ) return 0;
if( pItem->zIndex ) return 0;
iCur = pItem->iCursor;
pWC = &pWInfo->sWC;
pLoop = pBuilder->pNew;
pLoop->wsFlags = 0;
pLoop->nSkip = 0;
pTerm = findTerm(pWC, iCur, -1, 0, WO_EQ|WO_IS, 0);
if( pTerm ){
testcase( pTerm->eOperator & WO_IS );
pLoop->wsFlags = WHERE_COLUMN_EQ|WHERE_IPK|WHERE_ONEROW;
pLoop->aLTerm[0] = pTerm;
pLoop->nLTerm = 1;
pLoop->u.btree.nEq = 1;
/* TUNING: Cost of a rowid lookup is 10 */
pLoop->rRun = 33; /* 33==sqlite3LogEst(10) */
}else{
................................................................................
for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
assert( pLoop->aLTermSpace==pLoop->aLTerm );
if( !IsUniqueIndex(pIdx)
|| pIdx->pPartIdxWhere!=0
|| pIdx->nKeyCol>ArraySize(pLoop->aLTermSpace)
) continue;
for(j=0; j<pIdx->nKeyCol; j++){
pTerm = findTerm(pWC, iCur, pIdx->aiColumn[j], 0, WO_EQ|WO_IS, pIdx);
if( pTerm==0 ) break;
testcase( pTerm->eOperator & WO_IS );
pLoop->aLTerm[j] = pTerm;
}
if( j!=pIdx->nKeyCol ) continue;
pLoop->wsFlags = WHERE_COLUMN_EQ|WHERE_ONEROW|WHERE_INDEXED;
if( pIdx->isCovering || (pItem->colUsed & ~columnsInIndex(pIdx))==0 ){
pLoop->wsFlags |= WHERE_IDX_ONLY;
}
pLoop->nLTerm = j;
|
Changes to src/whereInt.h.
425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 |
/* ** Bitmasks for the operators on WhereTerm objects. These are all ** operators that are of interest to the query planner. An ** OR-ed combination of these values can be used when searching for ** particular WhereTerms within a WhereClause. */ #define WO_IN 0x001 #define WO_EQ 0x002 /* Used for both == and IS */ #define WO_LT (WO_EQ<<(TK_LT-TK_EQ)) #define WO_LE (WO_EQ<<(TK_LE-TK_EQ)) #define WO_GT (WO_EQ<<(TK_GT-TK_EQ)) #define WO_GE (WO_EQ<<(TK_GE-TK_EQ)) #define WO_MATCH 0x040 #define WO_ISNULL 0x080 #define WO_OR 0x100 /* Two or more OR-connected terms */ #define WO_AND 0x200 /* Two or more AND-connected terms */ #define WO_EQUIV 0x400 /* Of the form A==B, both columns */ #define WO_NOOP 0x800 /* This term does not restrict search space */ #define WO_ALL 0xfff /* Mask of all possible WO_* values */ #define WO_SINGLE 0x0ff /* Mask of all non-compound WO_* values */ /* ** These are definitions of bits in the WhereLoop.wsFlags field. ** The particular combination of bits in each WhereLoop help to ** determine the algorithm that WhereLoop represents. */ #define WHERE_COLUMN_EQ 0x00000001 /* x=EXPR */ |
| | | > | | | | | | | |
425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 |
/* ** Bitmasks for the operators on WhereTerm objects. These are all ** operators that are of interest to the query planner. An ** OR-ed combination of these values can be used when searching for ** particular WhereTerms within a WhereClause. */ #define WO_IN 0x0001 #define WO_EQ 0x0002 #define WO_LT (WO_EQ<<(TK_LT-TK_EQ)) #define WO_LE (WO_EQ<<(TK_LE-TK_EQ)) #define WO_GT (WO_EQ<<(TK_GT-TK_EQ)) #define WO_GE (WO_EQ<<(TK_GE-TK_EQ)) #define WO_MATCH 0x0040 #define WO_IS 0x0080 #define WO_ISNULL 0x0100 #define WO_OR 0x0200 /* Two or more OR-connected terms */ #define WO_AND 0x0400 /* Two or more AND-connected terms */ #define WO_EQUIV 0x0800 /* Of the form A==B, both columns */ #define WO_NOOP 0x1000 /* This term does not restrict search space */ #define WO_ALL 0x1fff /* Mask of all possible WO_* values */ #define WO_SINGLE 0x01ff /* Mask of all non-compound WO_* values */ /* ** These are definitions of bits in the WhereLoop.wsFlags field. ** The particular combination of bits in each WhereLoop help to ** determine the algorithm that WhereLoop represents. */ #define WHERE_COLUMN_EQ 0x00000001 /* x=EXPR */ |
Changes to test/where4.test.
53
54
55
56
57
58
59
60
61
62
63
64
65
66
...
138
139
140
141
142
143
144
145
146
147
148
149
150
151
|
# "sqlite_search_count" which tallys the number of executions of MoveTo
# and Next operators in the VDBE. By verifing that the search count is
# small we can be assured that indices are being used properly.
#
do_test where4-1.1 {
count {SELECT rowid FROM t1 WHERE w IS NULL}
} {7 2}
do_test where4-1.2 {
count {SELECT rowid FROM t1 WHERE +w IS NULL}
} {7 6}
do_test where4-1.3 {
count {SELECT rowid FROM t1 WHERE w=1 AND x IS NULL}
} {2 2}
do_test where4-1.4 {
................................................................................
SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE +y IS NULL;
}
} {2 2 {} 3 {} {}}
do_test where4-3.2 {
execsql {
SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE y IS NULL;
}
} {2 2 {} 3 {} {}}
# Ticket #2189. Probably the same bug as #2177.
#
do_test where4-4.1 {
execsql {
CREATE TABLE test(col1 TEXT PRIMARY KEY);
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
...
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
|
# "sqlite_search_count" which tallys the number of executions of MoveTo
# and Next operators in the VDBE. By verifing that the search count is
# small we can be assured that indices are being used properly.
#
do_test where4-1.1 {
count {SELECT rowid FROM t1 WHERE w IS NULL}
} {7 2}
do_test where4-1.1b {
unset -nocomplain null
count {SELECT rowid FROM t1 WHERE w IS $null}
} {7 2}
do_test where4-1.2 {
count {SELECT rowid FROM t1 WHERE +w IS NULL}
} {7 6}
do_test where4-1.3 {
count {SELECT rowid FROM t1 WHERE w=1 AND x IS NULL}
} {2 2}
do_test where4-1.4 {
................................................................................
SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE +y IS NULL;
}
} {2 2 {} 3 {} {}}
do_test where4-3.2 {
execsql {
SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE y IS NULL;
}
} {2 2 {} 3 {} {}}
do_test where4-3.3 {
execsql {
SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE NULL is y;
}
} {2 2 {} 3 {} {}}
do_test where4-3.4 {
unset -nocomplain null
execsql {
SELECT * FROM t2 LEFT JOIN t3 ON a=x WHERE y IS $null;
}
} {2 2 {} 3 {} {}}
# Ticket #2189. Probably the same bug as #2177.
#
do_test where4-4.1 {
execsql {
CREATE TABLE test(col1 TEXT PRIMARY KEY);
|