Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Consider the values bound to SQL variables when determining whether or not a partial index may be used. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | partial-index-variables |
Files: | files | file ages | folders |
SHA3-256: |
7b59c353b805c64689b4ae9df347705a |
User & Date: | dan 2017-06-24 18:10:29.777 |
Context
2017-06-28
| ||
18:07 | Merge the in the latest enhancements from trunk. (check-in: 8f63c58632 user: drh tags: partial-index-variables) | |
2017-06-24
| ||
18:10 | Consider the values bound to SQL variables when determining whether or not a partial index may be used. (check-in: 7b59c353b8 user: dan tags: partial-index-variables) | |
16:35 | Make sure the config.h header is included by ctime.c, if that header exists. (check-in: c2ea62937e user: drh tags: trunk) | |
Changes
Changes to src/expr.c.
︙ | ︙ | |||
1825 1826 1827 1828 1829 1830 1831 | ExprList *pGroupBy = pWalker->u.pGroupBy; int i; /* Check if pExpr is identical to any GROUP BY term. If so, consider ** it constant. */ for(i=0; i<pGroupBy->nExpr; i++){ Expr *p = pGroupBy->a[i].pExpr; | | | 1825 1826 1827 1828 1829 1830 1831 1832 1833 1834 1835 1836 1837 1838 1839 | ExprList *pGroupBy = pWalker->u.pGroupBy; int i; /* Check if pExpr is identical to any GROUP BY term. If so, consider ** it constant. */ for(i=0; i<pGroupBy->nExpr; i++){ Expr *p = pGroupBy->a[i].pExpr; if( sqlite3ExprCompare(0, pExpr, p, -1)<2 ){ CollSeq *pColl = sqlite3ExprCollSeq(pWalker->pParse, p); if( pColl==0 || sqlite3_stricmp("BINARY", pColl->zName)==0 ){ return WRC_Prune; } } } |
︙ | ︙ | |||
4101 4102 4103 4104 4105 4106 4107 | ExprList *p; assert( ConstFactorOk(pParse) ); p = pParse->pConstExpr; if( regDest<0 && p ){ struct ExprList_item *pItem; int i; for(pItem=p->a, i=p->nExpr; i>0; pItem++, i--){ | | | 4101 4102 4103 4104 4105 4106 4107 4108 4109 4110 4111 4112 4113 4114 4115 | ExprList *p; assert( ConstFactorOk(pParse) ); p = pParse->pConstExpr; if( regDest<0 && p ){ struct ExprList_item *pItem; int i; for(pItem=p->a, i=p->nExpr; i>0; pItem++, i--){ if( pItem->reusable && sqlite3ExprCompare(0,pItem->pExpr,pExpr,-1)==0 ){ return pItem->u.iConstExprReg; } } } pExpr = sqlite3ExprDup(pParse->db, pExpr, 0); p = sqlite3ExprListAppend(pParse, p, pExpr); if( p ){ |
︙ | ︙ | |||
4656 4657 4658 4659 4660 4661 4662 4663 4664 4665 4666 4667 4668 4669 | Expr *pCopy = sqlite3ExprDup(db, pExpr, 0); if( db->mallocFailed==0 ){ sqlite3ExprIfFalse(pParse, pCopy, dest, jumpIfNull); } sqlite3ExprDelete(db, pCopy); } /* ** Do a deep comparison of two expression trees. Return 0 if the two ** expressions are completely identical. Return 1 if they differ only ** by a COLLATE operator at the top level. Return 2 if there are differences ** other than the top-level COLLATE operator. ** | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 4656 4657 4658 4659 4660 4661 4662 4663 4664 4665 4666 4667 4668 4669 4670 4671 4672 4673 4674 4675 4676 4677 4678 4679 4680 4681 4682 4683 4684 4685 4686 4687 4688 4689 4690 4691 4692 4693 4694 4695 4696 4697 4698 4699 4700 4701 4702 4703 4704 4705 4706 4707 4708 4709 4710 4711 | Expr *pCopy = sqlite3ExprDup(db, pExpr, 0); if( db->mallocFailed==0 ){ sqlite3ExprIfFalse(pParse, pCopy, dest, jumpIfNull); } sqlite3ExprDelete(db, pCopy); } /* ** Expression pVar is guaranteed to be an SQL variable. pExpr may be any ** type of expression. ** ** If pExpr is a simple SQL value - an integer, real, string, blob ** or NULL value - then the VDBE currently being prepared is configured ** to re-prepare each time a new value is bound to variable pVar. ** ** Additionally, if pExpr is a simple SQL value and the value is the ** same as that currently bound to variable pVar, non-zero is returned. ** Otherwise, if the values are not the same or if pExpr is not a simple ** SQL value, zero is returned. */ static int exprCompareVariable(Parse *pParse, Expr *pVar, Expr *pExpr){ int res = 0; int iVar = pVar->iColumn; Expr *p = pExpr; while( p->op==TK_UMINUS ) p = p->pLeft; if( p->op==TK_NULL || p->op==TK_INTEGER || p->op==TK_FLOAT || p->op==TK_STRING || p->op==TK_BLOB ){ sqlite3VdbeSetVarmask(pParse->pVdbe, iVar); sqlite3_value *pL; pL = sqlite3VdbeGetBoundValue(pParse->pReprepare, iVar, SQLITE_AFF_BLOB); if( pL ){ sqlite3_value *pR = 0; sqlite3ValueFromExpr(pParse->db, pExpr, SQLITE_UTF8, SQLITE_AFF_BLOB,&pR); assert( pR || pParse->db->mallocFailed ); if( pR && 0==sqlite3MemCompare(pL, pR, 0) ){ res = 1; } sqlite3ValueFree(pR); sqlite3ValueFree(pL); }else if( p->op==TK_NULL ){ res = 1; } } return res; } /* ** Do a deep comparison of two expression trees. Return 0 if the two ** expressions are completely identical. Return 1 if they differ only ** by a COLLATE operator at the top level. Return 2 if there are differences ** other than the top-level COLLATE operator. ** |
︙ | ︙ | |||
4678 4679 4680 4681 4682 4683 4684 4685 | ** identical, we return 2 just to be safe. So if this routine ** returns 2, then you do not really know for certain if the two ** expressions are the same. But if you get a 0 or 1 return, then you ** can be sure the expressions are the same. In the places where ** this routine is used, it does not hurt to get an extra 2 - that ** just might result in some slightly slower code. But returning ** an incorrect 0 or 1 could lead to a malfunction. */ | > > > > > > | > > > | | | | | 4720 4721 4722 4723 4724 4725 4726 4727 4728 4729 4730 4731 4732 4733 4734 4735 4736 4737 4738 4739 4740 4741 4742 4743 4744 4745 4746 4747 4748 4749 4750 4751 4752 4753 4754 4755 4756 4757 4758 4759 4760 4761 4762 4763 4764 4765 4766 4767 4768 4769 4770 4771 4772 4773 4774 4775 4776 | ** identical, we return 2 just to be safe. So if this routine ** returns 2, then you do not really know for certain if the two ** expressions are the same. But if you get a 0 or 1 return, then you ** can be sure the expressions are the same. In the places where ** this routine is used, it does not hurt to get an extra 2 - that ** just might result in some slightly slower code. But returning ** an incorrect 0 or 1 could lead to a malfunction. ** ** Argument pParse should normally be NULL. If it is not NULL and ** expression pA contains SQL variable references, then the values ** currently bound to those variable references may be compared to ** simple SQL values in pB. See comments above function exprCompareVariable() ** for details. */ int sqlite3ExprCompare(Parse *pParse, Expr *pA, Expr *pB, int iTab){ u32 combinedFlags; if( pA==0 || pB==0 ){ return pB==pA ? 0 : 2; } if( pParse && pA->op==TK_VARIABLE && exprCompareVariable(pParse, pA, pB) ){ return 0; } combinedFlags = pA->flags | pB->flags; if( combinedFlags & EP_IntValue ){ if( (pA->flags&pB->flags&EP_IntValue)!=0 && pA->u.iValue==pB->u.iValue ){ return 0; } return 2; } if( pA->op!=pB->op ){ if( pA->op==TK_COLLATE && sqlite3ExprCompare(pParse, pA->pLeft,pB,iTab)<2 ){ return 1; } if( pB->op==TK_COLLATE && sqlite3ExprCompare(pParse, pA,pB->pLeft,iTab)<2 ){ return 1; } return 2; } if( pA->op!=TK_COLUMN && pA->op!=TK_AGG_COLUMN && pA->u.zToken ){ if( pA->op==TK_FUNCTION ){ if( sqlite3StrICmp(pA->u.zToken,pB->u.zToken)!=0 ) return 2; }else if( strcmp(pA->u.zToken,pB->u.zToken)!=0 ){ return pA->op==TK_COLLATE ? 1 : 2; } } if( (pA->flags & EP_Distinct)!=(pB->flags & EP_Distinct) ) return 2; if( ALWAYS((combinedFlags & EP_TokenOnly)==0) ){ if( combinedFlags & EP_xIsSelect ) return 2; if( sqlite3ExprCompare(pParse, pA->pLeft, pB->pLeft, iTab) ) return 2; if( sqlite3ExprCompare(pParse, pA->pRight, pB->pRight, iTab) ) return 2; if( sqlite3ExprListCompare(pA->x.pList, pB->x.pList, iTab) ) return 2; if( ALWAYS((combinedFlags & EP_Reduced)==0) && pA->op!=TK_STRING ){ if( pA->iColumn!=pB->iColumn ) return 2; if( pA->iTable!=pB->iTable && (pA->iTable!=iTab || NEVER(pB->iTable>=0)) ) return 2; } } |
︙ | ︙ | |||
4746 4747 4748 4749 4750 4751 4752 | if( pA==0 && pB==0 ) return 0; if( pA==0 || pB==0 ) return 1; if( pA->nExpr!=pB->nExpr ) return 1; for(i=0; i<pA->nExpr; i++){ Expr *pExprA = pA->a[i].pExpr; Expr *pExprB = pB->a[i].pExpr; if( pA->a[i].sortOrder!=pB->a[i].sortOrder ) return 1; | | | | 4797 4798 4799 4800 4801 4802 4803 4804 4805 4806 4807 4808 4809 4810 4811 4812 4813 4814 4815 4816 4817 4818 4819 4820 4821 | if( pA==0 && pB==0 ) return 0; if( pA==0 || pB==0 ) return 1; if( pA->nExpr!=pB->nExpr ) return 1; for(i=0; i<pA->nExpr; i++){ Expr *pExprA = pA->a[i].pExpr; Expr *pExprB = pB->a[i].pExpr; if( pA->a[i].sortOrder!=pB->a[i].sortOrder ) return 1; if( sqlite3ExprCompare(0, pExprA, pExprB, iTab) ) return 1; } return 0; } /* ** Like sqlite3ExprCompare() except COLLATE operators at the top-level ** are ignored. */ int sqlite3ExprCompareSkip(Expr *pA, Expr *pB, int iTab){ return sqlite3ExprCompare(0, sqlite3ExprSkipCollate(pA), sqlite3ExprSkipCollate(pB), iTab); } /* ** Return true if we can prove the pE2 will always be true if pE1 is |
︙ | ︙ | |||
4782 4783 4784 4785 4786 4787 4788 | ** When comparing TK_COLUMN nodes between pE1 and pE2, if pE2 has ** Expr.iTable<0 then assume a table number given by iTab. ** ** When in doubt, return false. Returning true might give a performance ** improvement. Returning false might cause a performance reduction, but ** it will always give the correct answer and is hence always safe. */ | | | | | | | 4833 4834 4835 4836 4837 4838 4839 4840 4841 4842 4843 4844 4845 4846 4847 4848 4849 4850 4851 4852 4853 4854 4855 4856 4857 4858 4859 4860 | ** When comparing TK_COLUMN nodes between pE1 and pE2, if pE2 has ** Expr.iTable<0 then assume a table number given by iTab. ** ** When in doubt, return false. Returning true might give a performance ** improvement. Returning false might cause a performance reduction, but ** it will always give the correct answer and is hence always safe. */ int sqlite3ExprImpliesExpr(Parse *pParse, Expr *pE1, Expr *pE2, int iTab){ if( sqlite3ExprCompare(pParse, pE1, pE2, iTab)==0 ){ return 1; } if( pE2->op==TK_OR && (sqlite3ExprImpliesExpr(pParse, pE1, pE2->pLeft, iTab) || sqlite3ExprImpliesExpr(pParse, pE1, pE2->pRight, iTab) ) ){ return 1; } if( pE2->op==TK_NOTNULL && pE1->op!=TK_ISNULL && pE1->op!=TK_IS ){ Expr *pX = sqlite3ExprSkipCollate(pE1->pLeft); testcase( pX!=pE1->pLeft ); if( sqlite3ExprCompare(pParse, pX, pE2->pLeft, iTab)==0 ) return 1; } return 0; } /* ** An instance of the following structure is used by the tree walker ** to determine if an expression can be evaluated by reference to the |
︙ | ︙ | |||
5036 5037 5038 5039 5040 5041 5042 | && pWalker->walkerDepth==pExpr->op2 ){ /* Check to see if pExpr is a duplicate of another aggregate ** function that is already in the pAggInfo structure */ struct AggInfo_func *pItem = pAggInfo->aFunc; for(i=0; i<pAggInfo->nFunc; i++, pItem++){ | | | 5087 5088 5089 5090 5091 5092 5093 5094 5095 5096 5097 5098 5099 5100 5101 | && pWalker->walkerDepth==pExpr->op2 ){ /* Check to see if pExpr is a duplicate of another aggregate ** function that is already in the pAggInfo structure */ struct AggInfo_func *pItem = pAggInfo->aFunc; for(i=0; i<pAggInfo->nFunc; i++, pItem++){ if( sqlite3ExprCompare(0, pItem->pExpr, pExpr, -1)==0 ){ break; } } if( i>=pAggInfo->nFunc ){ /* pExpr is original. Make a new entry in pAggInfo->aFunc[] */ u8 enc = ENC(pParse->db); |
︙ | ︙ |
Changes to src/insert.c.
︙ | ︙ | |||
1894 1895 1896 1897 1898 1899 1900 | } for(i=0; i<pSrc->nKeyCol; i++){ if( pSrc->aiColumn[i]!=pDest->aiColumn[i] ){ return 0; /* Different columns indexed */ } if( pSrc->aiColumn[i]==XN_EXPR ){ assert( pSrc->aColExpr!=0 && pDest->aColExpr!=0 ); | | | | 1894 1895 1896 1897 1898 1899 1900 1901 1902 1903 1904 1905 1906 1907 1908 1909 1910 1911 1912 1913 1914 1915 1916 1917 1918 1919 1920 | } for(i=0; i<pSrc->nKeyCol; i++){ if( pSrc->aiColumn[i]!=pDest->aiColumn[i] ){ return 0; /* Different columns indexed */ } if( pSrc->aiColumn[i]==XN_EXPR ){ assert( pSrc->aColExpr!=0 && pDest->aColExpr!=0 ); if( sqlite3ExprCompare(0, pSrc->aColExpr->a[i].pExpr, pDest->aColExpr->a[i].pExpr, -1)!=0 ){ return 0; /* Different expressions in the index */ } } if( pSrc->aSortOrder[i]!=pDest->aSortOrder[i] ){ return 0; /* Different sort orders */ } if( sqlite3_stricmp(pSrc->azColl[i],pDest->azColl[i])!=0 ){ return 0; /* Different collating sequences */ } } if( sqlite3ExprCompare(0, pSrc->pPartIdxWhere, pDest->pPartIdxWhere, -1) ){ return 0; /* Different WHERE clauses */ } /* If no test above fails then the indices must be compatible */ return 1; } |
︙ | ︙ |
Changes to src/resolve.c.
︙ | ︙ | |||
906 907 908 909 910 911 912 | if( rc ) return 0; /* Try to match the ORDER BY expression against an expression ** in the result set. Return an 1-based index of the matching ** result-set entry. */ for(i=0; i<pEList->nExpr; i++){ | | | 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 | if( rc ) return 0; /* Try to match the ORDER BY expression against an expression ** in the result set. Return an 1-based index of the matching ** result-set entry. */ for(i=0; i<pEList->nExpr; i++){ if( sqlite3ExprCompare(0, pEList->a[i].pExpr, pE, -1)<2 ){ return i+1; } } /* If no match, return 0. */ return 0; } |
︙ | ︙ | |||
1140 1141 1142 1143 1144 1145 1146 | /* Otherwise, treat the ORDER BY term as an ordinary expression */ pItem->u.x.iOrderByCol = 0; if( sqlite3ResolveExprNames(pNC, pE) ){ return 1; } for(j=0; j<pSelect->pEList->nExpr; j++){ | | | 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 | /* Otherwise, treat the ORDER BY term as an ordinary expression */ pItem->u.x.iOrderByCol = 0; if( sqlite3ResolveExprNames(pNC, pE) ){ return 1; } for(j=0; j<pSelect->pEList->nExpr; j++){ if( sqlite3ExprCompare(0, pE, pSelect->pEList->a[j].pExpr, -1)==0 ){ pItem->u.x.iOrderByCol = j+1; } } } return sqlite3ResolveOrderGroupBy(pParse, pSelect, pOrderBy, zType); } |
︙ | ︙ |
Changes to src/select.c.
︙ | ︙ | |||
5001 5002 5003 5004 5005 5006 5007 | struct SrcList_item *pItem; for(pItem = pTabList->a; pItem<pThis; pItem++){ if( pItem->pSelect==0 ) continue; if( pItem->fg.viaCoroutine ) continue; if( pItem->zName==0 ) continue; if( sqlite3_stricmp(pItem->zDatabase, pThis->zDatabase)!=0 ) continue; if( sqlite3_stricmp(pItem->zName, pThis->zName)!=0 ) continue; | > | > | 5001 5002 5003 5004 5005 5006 5007 5008 5009 5010 5011 5012 5013 5014 5015 5016 5017 | struct SrcList_item *pItem; for(pItem = pTabList->a; pItem<pThis; pItem++){ if( pItem->pSelect==0 ) continue; if( pItem->fg.viaCoroutine ) continue; if( pItem->zName==0 ) continue; if( sqlite3_stricmp(pItem->zDatabase, pThis->zDatabase)!=0 ) continue; if( sqlite3_stricmp(pItem->zName, pThis->zName)!=0 ) continue; if( sqlite3ExprCompare(0, pThis->pSelect->pWhere, pItem->pSelect->pWhere, -1) ){ /* The view was modified by some other optimization such as ** pushDownWhereTerms() */ continue; } return pItem; } return 0; |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
3777 3778 3779 3780 3781 3782 3783 | Table *sqlite3LocateTableItem(Parse*,u32 flags,struct SrcList_item *); Index *sqlite3FindIndex(sqlite3*,const char*, const char*); void sqlite3UnlinkAndDeleteTable(sqlite3*,int,const char*); void sqlite3UnlinkAndDeleteIndex(sqlite3*,int,const char*); void sqlite3Vacuum(Parse*,Token*); int sqlite3RunVacuum(char**, sqlite3*, int); char *sqlite3NameFromToken(sqlite3*, Token*); | | | | 3777 3778 3779 3780 3781 3782 3783 3784 3785 3786 3787 3788 3789 3790 3791 3792 3793 3794 | Table *sqlite3LocateTableItem(Parse*,u32 flags,struct SrcList_item *); Index *sqlite3FindIndex(sqlite3*,const char*, const char*); void sqlite3UnlinkAndDeleteTable(sqlite3*,int,const char*); void sqlite3UnlinkAndDeleteIndex(sqlite3*,int,const char*); void sqlite3Vacuum(Parse*,Token*); int sqlite3RunVacuum(char**, sqlite3*, int); char *sqlite3NameFromToken(sqlite3*, Token*); int sqlite3ExprCompare(Parse*,Expr*, Expr*, int); int sqlite3ExprCompareSkip(Expr*, Expr*, int); int sqlite3ExprListCompare(ExprList*, ExprList*, int); int sqlite3ExprImpliesExpr(Parse*,Expr*, Expr*, int); void sqlite3ExprAnalyzeAggregates(NameContext*, Expr*); void sqlite3ExprAnalyzeAggList(NameContext*,ExprList*); int sqlite3ExprCoveredByIndex(Expr*, int iCur, Index *pIdx); int sqlite3FunctionUsesThisSrc(Expr*, SrcList*); Vdbe *sqlite3GetVdbe(Parse*); #ifndef SQLITE_UNTESTABLE void sqlite3PrngSaveState(void); |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
2650 2651 2652 2653 2654 2655 2656 | if( pExpr->iColumn<0 ) return 1; for(jj=0; jj<pIndex->nKeyCol; jj++){ if( pExpr->iColumn==pIndex->aiColumn[jj] ) return 1; } }else if( (aColExpr = pIndex->aColExpr)!=0 ){ for(jj=0; jj<pIndex->nKeyCol; jj++){ if( pIndex->aiColumn[jj]!=XN_EXPR ) continue; | | | 2650 2651 2652 2653 2654 2655 2656 2657 2658 2659 2660 2661 2662 2663 2664 | if( pExpr->iColumn<0 ) return 1; for(jj=0; jj<pIndex->nKeyCol; jj++){ if( pExpr->iColumn==pIndex->aiColumn[jj] ) return 1; } }else if( (aColExpr = pIndex->aColExpr)!=0 ){ for(jj=0; jj<pIndex->nKeyCol; jj++){ if( pIndex->aiColumn[jj]!=XN_EXPR ) continue; if( sqlite3ExprCompare(0, pExpr,aColExpr->a[jj].pExpr,iCursor)==0 ){ return 1; } } } } return 0; } |
︙ | ︙ | |||
2689 2690 2691 2692 2693 2694 2695 | WhereTerm *pTerm; while( pWhere->op==TK_AND ){ if( !whereUsablePartialIndex(iTab,pWC,pWhere->pLeft) ) return 0; pWhere = pWhere->pRight; } for(i=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){ Expr *pExpr = pTerm->pExpr; | | | 2689 2690 2691 2692 2693 2694 2695 2696 2697 2698 2699 2700 2701 2702 2703 | WhereTerm *pTerm; while( pWhere->op==TK_AND ){ if( !whereUsablePartialIndex(iTab,pWC,pWhere->pLeft) ) return 0; pWhere = pWhere->pRight; } for(i=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){ Expr *pExpr = pTerm->pExpr; if( sqlite3ExprImpliesExpr(pWC->pWInfo->pParse, pExpr, pWhere, iTab) && (!ExprHasProperty(pExpr, EP_FromJoin) || pExpr->iRightJoinTable==iTab) ){ return 1; } } return 0; } |
︙ | ︙ | |||
3669 3670 3671 3672 3673 3674 3675 | testcase( wctrlFlags & WHERE_DISTINCTBY ); if( (wctrlFlags & (WHERE_GROUPBY|WHERE_DISTINCTBY))==0 ) bOnce = 0; if( iColumn>=(-1) ){ if( pOBExpr->op!=TK_COLUMN ) continue; if( pOBExpr->iTable!=iCur ) continue; if( pOBExpr->iColumn!=iColumn ) continue; }else{ | > | | 3669 3670 3671 3672 3673 3674 3675 3676 3677 3678 3679 3680 3681 3682 3683 3684 | testcase( wctrlFlags & WHERE_DISTINCTBY ); if( (wctrlFlags & (WHERE_GROUPBY|WHERE_DISTINCTBY))==0 ) bOnce = 0; if( iColumn>=(-1) ){ if( pOBExpr->op!=TK_COLUMN ) continue; if( pOBExpr->iTable!=iCur ) continue; if( pOBExpr->iColumn!=iColumn ) continue; }else{ if( sqlite3ExprCompare(0, pOBExpr,pIndex->aColExpr->a[j].pExpr,iCur) ){ continue; } } if( iColumn>=0 ){ pColl = sqlite3ExprCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr); if( !pColl ) pColl = db->pDfltColl; if( sqlite3StrICmp(pColl->zName, pIndex->azColl[j])!=0 ) continue; |
︙ | ︙ |
Changes to src/wherecode.c.
︙ | ︙ | |||
1054 1055 1056 1057 1058 1059 1060 | ** a reference to an index column for an index on an expression. ** ** If pExpr matches, then transform it into a reference to the index column ** that contains the value of pExpr. */ static int whereIndexExprTransNode(Walker *p, Expr *pExpr){ IdxExprTrans *pX = p->u.pIdxTrans; | | | 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 | ** a reference to an index column for an index on an expression. ** ** If pExpr matches, then transform it into a reference to the index column ** that contains the value of pExpr. */ static int whereIndexExprTransNode(Walker *p, Expr *pExpr){ IdxExprTrans *pX = p->u.pIdxTrans; if( sqlite3ExprCompare(0, pExpr, pX->pIdxExpr, pX->iTabCur)==0 ){ pExpr->op = TK_COLUMN; pExpr->iTable = pX->iIdxCur; pExpr->iColumn = pX->iIdxCol; pExpr->pTab = 0; return WRC_Prune; }else{ return WRC_Continue; |
︙ | ︙ |
Changes to src/whereexpr.c.
︙ | ︙ | |||
402 403 404 405 406 407 408 | if( (pOne->eOperator & (WO_EQ|WO_LT|WO_LE|WO_GT|WO_GE))==0 ) return; if( (pTwo->eOperator & (WO_EQ|WO_LT|WO_LE|WO_GT|WO_GE))==0 ) return; if( (eOp & (WO_EQ|WO_LT|WO_LE))!=eOp && (eOp & (WO_EQ|WO_GT|WO_GE))!=eOp ) return; assert( pOne->pExpr->pLeft!=0 && pOne->pExpr->pRight!=0 ); assert( pTwo->pExpr->pLeft!=0 && pTwo->pExpr->pRight!=0 ); | | | | 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 | if( (pOne->eOperator & (WO_EQ|WO_LT|WO_LE|WO_GT|WO_GE))==0 ) return; if( (pTwo->eOperator & (WO_EQ|WO_LT|WO_LE|WO_GT|WO_GE))==0 ) return; if( (eOp & (WO_EQ|WO_LT|WO_LE))!=eOp && (eOp & (WO_EQ|WO_GT|WO_GE))!=eOp ) return; assert( pOne->pExpr->pLeft!=0 && pOne->pExpr->pRight!=0 ); assert( pTwo->pExpr->pLeft!=0 && pTwo->pExpr->pRight!=0 ); if( sqlite3ExprCompare(0,pOne->pExpr->pLeft, pTwo->pExpr->pLeft, -1) ) return; if( sqlite3ExprCompare(0,pOne->pExpr->pRight, pTwo->pExpr->pRight,-1) )return; /* If we reach this point, it means the two subterms can be combined */ if( (eOp & (eOp-1))!=0 ){ if( eOp & (WO_LT|WO_LE) ){ eOp = WO_LE; }else{ assert( eOp & (WO_GT|WO_GE) ); eOp = WO_GE; |
︙ | ︙ |
Added test/index9.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 | # 2017 Jun 24 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # # Test that partial indexes work with bound variables. # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix index9 proc sqluses {sql} { array unset ::T uplevel [list db eval "EXPLAIN $sql" a { if {$a(opcode)=="OpenRead"} { set ::T($a(p2)) 1 } }] set in [join [array names ::T] ,] db eval "SELECT name FROM sqlite_master WHERE rootpage IN ($in) ORDER BY 1" } proc do_sqluses_test {tn sql objects} { uplevel [list do_test $tn [list sqluses $sql] $objects] } do_execsql_test 1.0 { CREATE TABLE t1(x, y); CREATE INDEX t1x ON t1(x) WHERE y=45; } set y [expr 45] do_sqluses_test 1.1 { SELECT * FROM t1 WHERE x=? AND y=$y } {t1 t1x} set y [expr 45.1] do_sqluses_test 1.2 { SELECT * FROM t1 WHERE x=? AND y=$y } {t1} set y [expr 44] do_sqluses_test 1.3 { SELECT * FROM t1 WHERE x=? AND y=$y } {t1} unset -nocomplain y do_sqluses_test 1.4 { SELECT * FROM t1 WHERE x=? AND y=$y } {t1} set y [string range "45" 0 end] do_sqluses_test 1.5 { SELECT * FROM t1 WHERE x=? AND y=$y } {t1} do_execsql_test 2.0 { CREATE INDEX t1x2 ON t1(x) WHERE y=-20111000111 } do_sqluses_test 2.1 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1} set y [expr -20111000111] do_sqluses_test 2.2 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1 t1x2} set y [expr -20111000110] do_sqluses_test 2.3 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1} set y [expr -20111000112] do_sqluses_test 2.4 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1} do_execsql_test 3.0 { CREATE INDEX t1x3 ON t1(x) WHERE y=9223372036854775807 } set y [expr 9223372036854775807] do_sqluses_test 3.1 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1 t1x3} set y [expr 9223372036854775808] do_sqluses_test 3.2 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1} set y [expr 9223372036854775806] do_sqluses_test 3.3 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1} do_execsql_test 3.0 { CREATE INDEX t1x4 ON t1(x) WHERE y=-9223372036854775808 } set y [expr -9223372036854775808] do_sqluses_test 3.1 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1 t1x4} set y [expr -9223372036854775807] do_sqluses_test 3.2 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1} set y [expr -9223372036854775809] do_sqluses_test 3.3 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1} set y [expr -9223372036854775808] do_sqluses_test 3.4 { SELECT * FROM t1 WHERE $y=y ORDER BY x } {t1 t1x4} finish_test |