Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Add testcase() macros and comments and a few test-cases. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | index-is-operator |
| Files: | files | file ages | folders |
| SHA1: |
24263d08b11c88416d270013bdaf5ff4 |
| User & Date: | drh 2015-05-13 17:54:08 |
Context
|
2015-05-13
| ||
| 19:33 | Simplified implementation of indexing with the IS operator. check-in: 95b1f9bf14 user: drh tags: index-is-operator | |
| 17:54 | Add testcase() macros and comments and a few test-cases. check-in: 24263d08b1 user: drh tags: index-is-operator | |
| 15:24 | An early attempt to get indexes to work with the IS operator. This code passes tests, but much more testing is needed to verify that it works on all corner cases. check-in: 6f7f1673d0 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 .... 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 .... 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 .... 1679 1680 1681 1682 1683 1684 1685 1686 1687 1688 1689 1690 1691 1692 .... 2976 2977 2978 2979 2980 2981 2982 2983 2984 2985 2986 2987 2988 2989 .... 3627 3628 3629 3630 3631 3632 3633 3634 3635 3636 3637 3638 3639 3640 .... 3673 3674 3675 3676 3677 3678 3679 3680 3681 3682 3683 3684 3685 3686 .... 4109 4110 4111 4112 4113 4114 4115 4116 4117 4118 4119 4120 4121 4122 4123 .... 4654 4655 4656 4657 4658 4659 4660 4661 4662 4663 4664 4665 4666 4667 .... 4855 4856 4857 4858 4859 4860 4861 4862 4863 4864 4865 4866 4867 4868 4869 .... 5703 5704 5705 5706 5707 5708 5709 5710 5711 5712 5713 5714 5715 5716 .... 6325 6326 6327 6328 6329 6330 6331 6332 6333 6334 6335 6336 6337 6338 |
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;
}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 );
................................................................................
testcase( j==1 );
/* We have found a candidate table and column. Check to see if that
** table and column is common to every term in the OR clause */
okToChngToIN = 1;
for(; i>=0 && okToChngToIN; i--, pOrTerm++){
assert( pOrTerm->eOperator & WO_EQ );
if( pOrTerm->leftCursor!=iCursor ){
pOrTerm->wtFlags &= ~TERM_OR_OK;
}else if( pOrTerm->u.leftColumn!=iColumn ){
okToChngToIN = 0;
}else{
int affLeft, affRight;
/* If the right-hand side is also a column, then the affinities
................................................................................
Expr *pNew; /* The complete IN operator */
for(i=pOrWc->nTerm-1, pOrTerm=pOrWc->a; i>=0; i--, pOrTerm++){
if( (pOrTerm->wtFlags & TERM_OR_OK)==0 ) continue;
assert( pOrTerm->eOperator & WO_EQ );
assert( pOrTerm->leftCursor==iCursor );
assert( pOrTerm->u.leftColumn==iColumn );
pDup = sqlite3ExprDup(db, pOrTerm->pExpr->pRight, 0);
pList = sqlite3ExprListAppend(pWInfo->pParse, pList, pDup);
pLeft = pOrTerm->pExpr->pLeft;
}
assert( pLeft!=0 );
pDup = sqlite3ExprDup(db, pLeft, 0);
pNew = sqlite3PExpr(pParse, TK_IN, pDup, 0, 0);
................................................................................
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;
return 1;
}
#endif
#ifndef SQLITE_OMIT_AUTOMATIC_INDEX
/*
................................................................................
sqlite3VdbeAddOp2(v, OP_SCopy, r1, regBase+j);
}
}
testcase( pTerm->eOperator & WO_ISNULL );
testcase( pTerm->eOperator & WO_IN );
if( (pTerm->eOperator & (WO_ISNULL|WO_IN))==0 ){
Expr *pRight = pTerm->pExpr->pRight;
if( (pTerm->wtFlags & TERM_NULLOK)==0
&& sqlite3ExprCanBeNull(pRight)
){
sqlite3VdbeAddOp2(v, OP_IsNull, regBase+j, pLevel->addrBrk);
VdbeCoverage(v);
}
if( zAff ){
................................................................................
/* Seek the index cursor to the start of the range. */
nConstraint = nEq;
if( pRangeStart ){
Expr *pRight = pRangeStart->pExpr->pRight;
sqlite3ExprCode(pParse, pRight, regBase+nEq);
whereLikeOptimizationStringFixup(v, pLevel, pRangeStart);
if( (pRangeStart->wtFlags & TERM_NULLOK)==0
&& sqlite3ExprCanBeNull(pRight)
){
sqlite3VdbeAddOp2(v, OP_IsNull, regBase+nEq, addrNxt);
VdbeCoverage(v);
}
if( zStartAff ){
................................................................................
*/
nConstraint = nEq;
if( pRangeEnd ){
Expr *pRight = pRangeEnd->pExpr->pRight;
sqlite3ExprCacheRemove(pParse, regBase+nEq, 1);
sqlite3ExprCode(pParse, pRight, regBase+nEq);
whereLikeOptimizationStringFixup(v, pLevel, pRangeEnd);
if( (pRangeEnd->wtFlags & TERM_NULLOK)==0
&& sqlite3ExprCanBeNull(pRight)
){
sqlite3VdbeAddOp2(v, OP_IsNull, regBase+nEq, addrNxt);
VdbeCoverage(v);
}
if( sqlite3CompareAffinity(pRight, cEndAff)!=SQLITE_AFF_NONE
................................................................................
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 );
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);
................................................................................
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;
if( sqlite3ExprIsInteger(pRight, &k) && k>=(-1) && k<=1 ){
k = 10;
}else{
k = 20;
}
if( iReduce<k ) iReduce = k;
}
................................................................................
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 );
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 */
................................................................................
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;
if( sqlite3StrICmp(z1, z2)!=0 ) continue;
}
obSat |= MASKBIT(i);
}
if( (pLoop->wsFlags & WHERE_ONEROW)==0 ){
if( pLoop->wsFlags & WHERE_IPK ){
pIndex = 0;
................................................................................
|| 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;
}
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 .... 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 .... 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 .... 1681 1682 1683 1684 1685 1686 1687 1688 1689 1690 1691 1692 1693 1694 1695 .... 2979 2980 2981 2982 2983 2984 2985 2986 2987 2988 2989 2990 2991 2992 2993 .... 3631 3632 3633 3634 3635 3636 3637 3638 3639 3640 3641 3642 3643 3644 3645 .... 3678 3679 3680 3681 3682 3683 3684 3685 3686 3687 3688 3689 3690 3691 3692 .... 4115 4116 4117 4118 4119 4120 4121 4122 4123 4124 4125 4126 4127 4128 4129 .... 4660 4661 4662 4663 4664 4665 4666 4667 4668 4669 4670 4671 4672 4673 4674 .... 4862 4863 4864 4865 4866 4867 4868 4869 4870 4871 4872 4873 4874 4875 4876 .... 5710 5711 5712 5713 5714 5715 5716 5717 5718 5719 5720 5721 5722 5723 5724 .... 6333 6334 6335 6336 6337 6338 6339 6340 6341 6342 6343 6344 6345 6346 6347 |
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 );
................................................................................
testcase( j==1 );
/* We have found a candidate table and column. Check to see if that
** table and column is common to every term in the OR clause */
okToChngToIN = 1;
for(; i>=0 && okToChngToIN; i--, pOrTerm++){
assert( pOrTerm->eOperator & WO_EQ );
testcase( pOrTerm->pExpr->op==TK_IS );
if( pOrTerm->leftCursor!=iCursor ){
pOrTerm->wtFlags &= ~TERM_OR_OK;
}else if( pOrTerm->u.leftColumn!=iColumn ){
okToChngToIN = 0;
}else{
int affLeft, affRight;
/* If the right-hand side is also a column, then the affinities
................................................................................
Expr *pNew; /* The complete IN operator */
for(i=pOrWc->nTerm-1, pOrTerm=pOrWc->a; i>=0; i--, pOrTerm++){
if( (pOrTerm->wtFlags & TERM_OR_OK)==0 ) continue;
assert( pOrTerm->eOperator & WO_EQ );
assert( pOrTerm->leftCursor==iCursor );
assert( pOrTerm->u.leftColumn==iColumn );
testcase( pOrTerm->pExpr->op==TK_IS );
pDup = sqlite3ExprDup(db, pOrTerm->pExpr->pRight, 0);
pList = sqlite3ExprListAppend(pWInfo->pParse, pList, pDup);
pLeft = pOrTerm->pExpr->pLeft;
}
assert( pLeft!=0 );
pDup = sqlite3ExprDup(db, pLeft, 0);
pNew = sqlite3PExpr(pParse, TK_IN, pDup, 0, 0);
................................................................................
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;
}
#endif
#ifndef SQLITE_OMIT_AUTOMATIC_INDEX
/*
................................................................................
sqlite3VdbeAddOp2(v, OP_SCopy, r1, regBase+j);
}
}
testcase( pTerm->eOperator & WO_ISNULL );
testcase( pTerm->eOperator & WO_IN );
if( (pTerm->eOperator & (WO_ISNULL|WO_IN))==0 ){
Expr *pRight = pTerm->pExpr->pRight;
testcase( pTerm->pExpr->op==TK_IS );
if( (pTerm->wtFlags & TERM_NULLOK)==0
&& sqlite3ExprCanBeNull(pRight)
){
sqlite3VdbeAddOp2(v, OP_IsNull, regBase+j, pLevel->addrBrk);
VdbeCoverage(v);
}
if( zAff ){
................................................................................
/* Seek the index cursor to the start of the range. */
nConstraint = nEq;
if( pRangeStart ){
Expr *pRight = pRangeStart->pExpr->pRight;
sqlite3ExprCode(pParse, pRight, regBase+nEq);
whereLikeOptimizationStringFixup(v, pLevel, pRangeStart);
testcase( pRangeStart->pExpr->op==TK_IS );
if( (pRangeStart->wtFlags & TERM_NULLOK)==0
&& sqlite3ExprCanBeNull(pRight)
){
sqlite3VdbeAddOp2(v, OP_IsNull, regBase+nEq, addrNxt);
VdbeCoverage(v);
}
if( zStartAff ){
................................................................................
*/
nConstraint = nEq;
if( pRangeEnd ){
Expr *pRight = pRangeEnd->pExpr->pRight;
sqlite3ExprCacheRemove(pParse, regBase+nEq, 1);
sqlite3ExprCode(pParse, pRight, regBase+nEq);
whereLikeOptimizationStringFixup(v, pLevel, pRangeEnd);
testcase( pRangeEnd->pExpr->op==TK_IS );
if( (pRangeEnd->wtFlags & TERM_NULLOK)==0
&& sqlite3ExprCanBeNull(pRight)
){
sqlite3VdbeAddOp2(v, OP_IsNull, regBase+nEq, addrNxt);
VdbeCoverage(v);
}
if( sqlite3CompareAffinity(pRight, cEndAff)!=SQLITE_AFF_NONE
................................................................................
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);
................................................................................
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;
}
if( iReduce<k ) iReduce = k;
}
................................................................................
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 */
................................................................................
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;
if( sqlite3StrICmp(z1, z2)!=0 ) continue;
testcase( pTerm->pExpr->op==TK_IS );
}
obSat |= MASKBIT(i);
}
if( (pLoop->wsFlags & WHERE_ONEROW)==0 ){
if( pLoop->wsFlags & WHERE_IPK ){
pIndex = 0;
................................................................................
|| 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;
|
Changes to test/where.test.
61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 .. 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 ... 139 140 141 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 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 |
# "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 where-1.1.1 {
count {SELECT x, y, w FROM t1 WHERE w=10}
} {3 121 10 3}
do_eqp_test where-1.1.2 {
SELECT x, y, w FROM t1 WHERE w=10
} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
do_test where-1.1.3 {
db status step
} {0}
do_test where-1.1.4 {
db eval {SELECT x, y, w FROM t1 WHERE +w=10}
} {3 121 10}
do_test where-1.1.5 {
................................................................................
} {3 144 11 3}
do_test where-1.3.1 {
count {SELECT x, y, w AS abc FROM t1 WHERE 11=w}
} {3 144 11 3}
do_test where-1.3.2 {
count {SELECT x, y, w AS abc FROM t1 WHERE 11=abc}
} {3 144 11 3}
do_test where-1.4.1 {
count {SELECT w, x, y FROM t1 WHERE 11=w AND x>2}
} {11 3 144 3}
do_eqp_test where-1.4.2 {
SELECT w, x, y FROM t1 WHERE 11=w AND x>2
} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
do_test where-1.4.3 {
count {SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2}
} {11 3 144 3}
do_eqp_test where-1.4.4 {
SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2
} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
do_test where-1.5 {
................................................................................
} {3 144 3}
do_test where-1.10 {
count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121}
} {3 121 3}
do_test where-1.11 {
count {SELECT x, y FROM t1 WHERE x=3 AND y=100 AND w<10}
} {3 100 3}
# New for SQLite version 2.1: Verify that that inequality constraints
# are used correctly.
#
do_test where-1.12 {
count {SELECT w FROM t1 WHERE x=3 AND y<100}
} {8 3}
do_test where-1.13 {
count {SELECT w FROM t1 WHERE x=3 AND 100>y}
} {8 3}
do_test where-1.14 {
count {SELECT w FROM t1 WHERE 3=x AND y<100}
} {8 3}
do_test where-1.15 {
count {SELECT w FROM t1 WHERE 3=x AND 100>y}
} {8 3}
do_test where-1.16 {
count {SELECT w FROM t1 WHERE x=3 AND y<=100}
} {8 9 5}
do_test where-1.17 {
count {SELECT w FROM t1 WHERE x=3 AND 100>=y}
} {8 9 5}
do_test where-1.18 {
count {SELECT w FROM t1 WHERE x=3 AND y>225}
} {15 3}
do_test where-1.19 {
count {SELECT w FROM t1 WHERE x=3 AND 225<y}
} {15 3}
do_test where-1.20 {
count {SELECT w FROM t1 WHERE x=3 AND y>=225}
} {14 15 5}
do_test where-1.21 {
count {SELECT w FROM t1 WHERE x=3 AND 225<=y}
} {14 15 5}
do_test where-1.22 {
count {SELECT w FROM t1 WHERE x=3 AND y>121 AND y<196}
} {11 12 5}
do_test where-1.23 {
count {SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196}
} {10 11 12 13 9}
do_test where-1.24 {
count {SELECT w FROM t1 WHERE x=3 AND 121<y AND 196>y}
} {11 12 5}
do_test where-1.25 {
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 ... 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 ... 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 |
# "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 where-1.1.1 {
count {SELECT x, y, w FROM t1 WHERE w=10}
} {3 121 10 3}
do_test where-1.1.1b {
count {SELECT x, y, w FROM t1 WHERE w IS 10}
} {3 121 10 3}
do_eqp_test where-1.1.2 {
SELECT x, y, w FROM t1 WHERE w=10
} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
do_eqp_test where-1.1.2b {
SELECT x, y, w FROM t1 WHERE w IS 10
} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
do_test where-1.1.3 {
db status step
} {0}
do_test where-1.1.4 {
db eval {SELECT x, y, w FROM t1 WHERE +w=10}
} {3 121 10}
do_test where-1.1.5 {
................................................................................
} {3 144 11 3}
do_test where-1.3.1 {
count {SELECT x, y, w AS abc FROM t1 WHERE 11=w}
} {3 144 11 3}
do_test where-1.3.2 {
count {SELECT x, y, w AS abc FROM t1 WHERE 11=abc}
} {3 144 11 3}
do_test where-1.3.3 {
count {SELECT x, y, w AS abc FROM t1 WHERE 11 IS abc}
} {3 144 11 3}
do_test where-1.4.1 {
count {SELECT w, x, y FROM t1 WHERE 11=w AND x>2}
} {11 3 144 3}
do_test where-1.4.1b {
count {SELECT w, x, y FROM t1 WHERE 11 IS w AND x>2}
} {11 3 144 3}
do_eqp_test where-1.4.2 {
SELECT w, x, y FROM t1 WHERE 11=w AND x>2
} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
do_eqp_test where-1.4.2b {
SELECT w, x, y FROM t1 WHERE 11 IS w AND x>2
} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
do_test where-1.4.3 {
count {SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2}
} {11 3 144 3}
do_eqp_test where-1.4.4 {
SELECT w AS a, x AS b, y FROM t1 WHERE 11=a AND b>2
} {*SEARCH TABLE t1 USING INDEX i1w (w=?)*}
do_test where-1.5 {
................................................................................
} {3 144 3}
do_test where-1.10 {
count {SELECT x, y FROM t1 WHERE x=3 AND w>=10 AND y=121}
} {3 121 3}
do_test where-1.11 {
count {SELECT x, y FROM t1 WHERE x=3 AND y=100 AND w<10}
} {3 100 3}
do_test where-1.11b {
count {SELECT x, y FROM t1 WHERE x IS 3 AND y IS 100 AND w<10}
} {3 100 3}
# New for SQLite version 2.1: Verify that that inequality constraints
# are used correctly.
#
do_test where-1.12 {
count {SELECT w FROM t1 WHERE x=3 AND y<100}
} {8 3}
do_test where-1.12b {
count {SELECT w FROM t1 WHERE x IS 3 AND y<100}
} {8 3}
do_test where-1.13 {
count {SELECT w FROM t1 WHERE x=3 AND 100>y}
} {8 3}
do_test where-1.14 {
count {SELECT w FROM t1 WHERE 3=x AND y<100}
} {8 3}
do_test where-1.14b {
count {SELECT w FROM t1 WHERE 3 IS x AND y<100}
} {8 3}
do_test where-1.15 {
count {SELECT w FROM t1 WHERE 3=x AND 100>y}
} {8 3}
do_test where-1.16 {
count {SELECT w FROM t1 WHERE x=3 AND y<=100}
} {8 9 5}
do_test where-1.17 {
count {SELECT w FROM t1 WHERE x=3 AND 100>=y}
} {8 9 5}
do_test where-1.18 {
count {SELECT w FROM t1 WHERE x=3 AND y>225}
} {15 3}
do_test where-1.18b {
count {SELECT w FROM t1 WHERE x IS 3 AND y>225}
} {15 3}
do_test where-1.19 {
count {SELECT w FROM t1 WHERE x=3 AND 225<y}
} {15 3}
do_test where-1.20 {
count {SELECT w FROM t1 WHERE x=3 AND y>=225}
} {14 15 5}
do_test where-1.21 {
count {SELECT w FROM t1 WHERE x=3 AND 225<=y}
} {14 15 5}
do_test where-1.22 {
count {SELECT w FROM t1 WHERE x=3 AND y>121 AND y<196}
} {11 12 5}
do_test where-1.22b {
count {SELECT w FROM t1 WHERE x IS 3 AND y>121 AND y<196}
} {11 12 5}
do_test where-1.23 {
count {SELECT w FROM t1 WHERE x=3 AND y>=121 AND y<=196}
} {10 11 12 13 9}
do_test where-1.24 {
count {SELECT w FROM t1 WHERE x=3 AND 121<y AND 196>y}
} {11 12 5}
do_test where-1.25 {
|