4 ** The author disclaims copyright to this source code. In place of
5 ** a legal notice, here is a blessing:
7 ** May you do good and not evil.
8 ** May you find forgiveness for yourself and forgive others.
9 ** May you share freely, never taking more than you give.
11 *************************************************************************
12 ** This module contains C code that generates VDBE code used to process
13 ** the WHERE clause of SQL statements.
15 ** This file was split off from where.c on 2015-06-06 in order to reduce the
16 ** size of where.c and make it easier to edit. This file contains the routines
17 ** that actually generate the bulk of the WHERE loop code. The original where.c
18 ** file retains the code that does query planning and analysis.
20 #include "sqliteInt.h"
23 #ifndef SQLITE_OMIT_EXPLAIN
26 ** Return the name of the i-th column of the pIdx index.
28 static const char *explainIndexColumnName(Index
*pIdx
, int i
){
29 i
= pIdx
->aiColumn
[i
];
30 if( i
==XN_EXPR
) return "<expr>";
31 if( i
==XN_ROWID
) return "rowid";
32 return pIdx
->pTable
->aCol
[i
].zName
;
36 ** This routine is a helper for explainIndexRange() below
38 ** pStr holds the text of an expression that we are building up one term
39 ** at a time. This routine adds a new term to the end of the expression.
40 ** Terms are separated by AND so add the "AND" text for second and subsequent
43 static void explainAppendTerm(
44 StrAccum
*pStr
, /* The text expression being built */
45 Index
*pIdx
, /* Index to read column names from */
46 int nTerm
, /* Number of terms */
47 int iTerm
, /* Zero-based index of first term. */
48 int bAnd
, /* Non-zero to append " AND " */
49 const char *zOp
/* Name of the operator */
54 if( bAnd
) sqlite3StrAccumAppend(pStr
, " AND ", 5);
56 if( nTerm
>1 ) sqlite3StrAccumAppend(pStr
, "(", 1);
57 for(i
=0; i
<nTerm
; i
++){
58 if( i
) sqlite3StrAccumAppend(pStr
, ",", 1);
59 sqlite3StrAccumAppendAll(pStr
, explainIndexColumnName(pIdx
, iTerm
+i
));
61 if( nTerm
>1 ) sqlite3StrAccumAppend(pStr
, ")", 1);
63 sqlite3StrAccumAppend(pStr
, zOp
, 1);
65 if( nTerm
>1 ) sqlite3StrAccumAppend(pStr
, "(", 1);
66 for(i
=0; i
<nTerm
; i
++){
67 if( i
) sqlite3StrAccumAppend(pStr
, ",", 1);
68 sqlite3StrAccumAppend(pStr
, "?", 1);
70 if( nTerm
>1 ) sqlite3StrAccumAppend(pStr
, ")", 1);
74 ** Argument pLevel describes a strategy for scanning table pTab. This
75 ** function appends text to pStr that describes the subset of table
76 ** rows scanned by the strategy in the form of an SQL expression.
78 ** For example, if the query:
80 ** SELECT * FROM t1 WHERE a=1 AND b>2;
82 ** is run and there is an index on (a, b), then this function returns a
87 static void explainIndexRange(StrAccum
*pStr
, WhereLoop
*pLoop
){
88 Index
*pIndex
= pLoop
->u
.btree
.pIndex
;
89 u16 nEq
= pLoop
->u
.btree
.nEq
;
90 u16 nSkip
= pLoop
->nSkip
;
93 if( nEq
==0 && (pLoop
->wsFlags
&(WHERE_BTM_LIMIT
|WHERE_TOP_LIMIT
))==0 ) return;
94 sqlite3StrAccumAppend(pStr
, " (", 2);
96 const char *z
= explainIndexColumnName(pIndex
, i
);
97 if( i
) sqlite3StrAccumAppend(pStr
, " AND ", 5);
98 sqlite3XPrintf(pStr
, i
>=nSkip
? "%s=?" : "ANY(%s)", z
);
102 if( pLoop
->wsFlags
&WHERE_BTM_LIMIT
){
103 explainAppendTerm(pStr
, pIndex
, pLoop
->u
.btree
.nBtm
, j
, i
, ">");
106 if( pLoop
->wsFlags
&WHERE_TOP_LIMIT
){
107 explainAppendTerm(pStr
, pIndex
, pLoop
->u
.btree
.nTop
, j
, i
, "<");
109 sqlite3StrAccumAppend(pStr
, ")", 1);
113 ** This function is a no-op unless currently processing an EXPLAIN QUERY PLAN
114 ** command, or if either SQLITE_DEBUG or SQLITE_ENABLE_STMT_SCANSTATUS was
115 ** defined at compile-time. If it is not a no-op, a single OP_Explain opcode
116 ** is added to the output to describe the table scan strategy in pLevel.
118 ** If an OP_Explain opcode is added to the VM, its address is returned.
119 ** Otherwise, if no OP_Explain is coded, zero is returned.
121 int sqlite3WhereExplainOneScan(
122 Parse
*pParse
, /* Parse context */
123 SrcList
*pTabList
, /* Table list this loop refers to */
124 WhereLevel
*pLevel
, /* Scan to write OP_Explain opcode for */
125 int iLevel
, /* Value for "level" column of output */
126 int iFrom
, /* Value for "from" column of output */
127 u16 wctrlFlags
/* Flags passed to sqlite3WhereBegin() */
130 #if !defined(SQLITE_DEBUG) && !defined(SQLITE_ENABLE_STMT_SCANSTATUS)
131 if( pParse
->explain
==2 )
134 struct SrcList_item
*pItem
= &pTabList
->a
[pLevel
->iFrom
];
135 Vdbe
*v
= pParse
->pVdbe
; /* VM being constructed */
136 sqlite3
*db
= pParse
->db
; /* Database handle */
137 int iId
= pParse
->iSelectId
; /* Select id (left-most output column) */
138 int isSearch
; /* True for a SEARCH. False for SCAN. */
139 WhereLoop
*pLoop
; /* The controlling WhereLoop object */
140 u32 flags
; /* Flags that describe this loop */
141 char *zMsg
; /* Text to add to EQP output */
142 StrAccum str
; /* EQP output string */
143 char zBuf
[100]; /* Initial space for EQP output string */
145 pLoop
= pLevel
->pWLoop
;
146 flags
= pLoop
->wsFlags
;
147 if( (flags
&WHERE_MULTI_OR
) || (wctrlFlags
&WHERE_OR_SUBCLAUSE
) ) return 0;
149 isSearch
= (flags
&(WHERE_BTM_LIMIT
|WHERE_TOP_LIMIT
))!=0
150 || ((flags
&WHERE_VIRTUALTABLE
)==0 && (pLoop
->u
.btree
.nEq
>0))
151 || (wctrlFlags
&(WHERE_ORDERBY_MIN
|WHERE_ORDERBY_MAX
));
153 sqlite3StrAccumInit(&str
, db
, zBuf
, sizeof(zBuf
), SQLITE_MAX_LENGTH
);
154 sqlite3StrAccumAppendAll(&str
, isSearch
? "SEARCH" : "SCAN");
155 if( pItem
->pSelect
){
156 sqlite3XPrintf(&str
, " SUBQUERY %d", pItem
->iSelectId
);
158 sqlite3XPrintf(&str
, " TABLE %s", pItem
->zName
);
162 sqlite3XPrintf(&str
, " AS %s", pItem
->zAlias
);
164 if( (flags
& (WHERE_IPK
|WHERE_VIRTUALTABLE
))==0 ){
165 const char *zFmt
= 0;
168 assert( pLoop
->u
.btree
.pIndex
!=0 );
169 pIdx
= pLoop
->u
.btree
.pIndex
;
170 assert( !(flags
&WHERE_AUTO_INDEX
) || (flags
&WHERE_IDX_ONLY
) );
171 if( !HasRowid(pItem
->pTab
) && IsPrimaryKeyIndex(pIdx
) ){
173 zFmt
= "PRIMARY KEY";
175 }else if( flags
& WHERE_PARTIALIDX
){
176 zFmt
= "AUTOMATIC PARTIAL COVERING INDEX";
177 }else if( flags
& WHERE_AUTO_INDEX
){
178 zFmt
= "AUTOMATIC COVERING INDEX";
179 }else if( flags
& WHERE_IDX_ONLY
){
180 zFmt
= "COVERING INDEX %s";
185 sqlite3StrAccumAppend(&str
, " USING ", 7);
186 sqlite3XPrintf(&str
, zFmt
, pIdx
->zName
);
187 explainIndexRange(&str
, pLoop
);
189 }else if( (flags
& WHERE_IPK
)!=0 && (flags
& WHERE_CONSTRAINT
)!=0 ){
190 const char *zRangeOp
;
191 if( flags
&(WHERE_COLUMN_EQ
|WHERE_COLUMN_IN
) ){
193 }else if( (flags
&WHERE_BOTH_LIMIT
)==WHERE_BOTH_LIMIT
){
194 zRangeOp
= ">? AND rowid<";
195 }else if( flags
&WHERE_BTM_LIMIT
){
198 assert( flags
&WHERE_TOP_LIMIT
);
201 sqlite3XPrintf(&str
, " USING INTEGER PRIMARY KEY (rowid%s?)",zRangeOp
);
203 #ifndef SQLITE_OMIT_VIRTUALTABLE
204 else if( (flags
& WHERE_VIRTUALTABLE
)!=0 ){
205 sqlite3XPrintf(&str
, " VIRTUAL TABLE INDEX %d:%s",
206 pLoop
->u
.vtab
.idxNum
, pLoop
->u
.vtab
.idxStr
);
209 #ifdef SQLITE_EXPLAIN_ESTIMATED_ROWS
210 if( pLoop
->nOut
>=10 ){
211 sqlite3XPrintf(&str
, " (~%llu rows)", sqlite3LogEstToInt(pLoop
->nOut
));
213 sqlite3StrAccumAppend(&str
, " (~1 row)", 9);
216 zMsg
= sqlite3StrAccumFinish(&str
);
217 ret
= sqlite3VdbeAddOp4(v
, OP_Explain
, iId
, iLevel
, iFrom
, zMsg
,P4_DYNAMIC
);
221 #endif /* SQLITE_OMIT_EXPLAIN */
223 #ifdef SQLITE_ENABLE_STMT_SCANSTATUS
225 ** Configure the VM passed as the first argument with an
226 ** sqlite3_stmt_scanstatus() entry corresponding to the scan used to
227 ** implement level pLvl. Argument pSrclist is a pointer to the FROM
228 ** clause that the scan reads data from.
230 ** If argument addrExplain is not 0, it must be the address of an
231 ** OP_Explain instruction that describes the same loop.
233 void sqlite3WhereAddScanStatus(
234 Vdbe
*v
, /* Vdbe to add scanstatus entry to */
235 SrcList
*pSrclist
, /* FROM clause pLvl reads data from */
236 WhereLevel
*pLvl
, /* Level to add scanstatus() entry for */
237 int addrExplain
/* Address of OP_Explain (or 0) */
239 const char *zObj
= 0;
240 WhereLoop
*pLoop
= pLvl
->pWLoop
;
241 if( (pLoop
->wsFlags
& WHERE_VIRTUALTABLE
)==0 && pLoop
->u
.btree
.pIndex
!=0 ){
242 zObj
= pLoop
->u
.btree
.pIndex
->zName
;
244 zObj
= pSrclist
->a
[pLvl
->iFrom
].zName
;
246 sqlite3VdbeScanStatus(
247 v
, addrExplain
, pLvl
->addrBody
, pLvl
->addrVisit
, pLoop
->nOut
, zObj
254 ** Disable a term in the WHERE clause. Except, do not disable the term
255 ** if it controls a LEFT OUTER JOIN and it did not originate in the ON
256 ** or USING clause of that join.
258 ** Consider the term t2.z='ok' in the following queries:
260 ** (1) SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.x WHERE t2.z='ok'
261 ** (2) SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.x AND t2.z='ok'
262 ** (3) SELECT * FROM t1, t2 WHERE t1.a=t2.x AND t2.z='ok'
264 ** The t2.z='ok' is disabled in the in (2) because it originates
265 ** in the ON clause. The term is disabled in (3) because it is not part
266 ** of a LEFT OUTER JOIN. In (1), the term is not disabled.
268 ** Disabling a term causes that term to not be tested in the inner loop
269 ** of the join. Disabling is an optimization. When terms are satisfied
270 ** by indices, we disable them to prevent redundant tests in the inner
271 ** loop. We would get the correct results if nothing were ever disabled,
272 ** but joins might run a little slower. The trick is to disable as much
273 ** as we can without disabling too much. If we disabled in (1), we'd get
274 ** the wrong answer. See ticket #813.
276 ** If all the children of a term are disabled, then that term is also
277 ** automatically disabled. In this way, terms get disabled if derived
278 ** virtual terms are tested first. For example:
280 ** x GLOB 'abc*' AND x>='abc' AND x<'acd'
281 ** \___________/ \______/ \_____/
282 ** parent child1 child2
284 ** Only the parent term was in the original WHERE clause. The child1
285 ** and child2 terms were added by the LIKE optimization. If both of
286 ** the virtual child terms are valid, then testing of the parent can be
289 ** Usually the parent term is marked as TERM_CODED. But if the parent
290 ** term was originally TERM_LIKE, then the parent gets TERM_LIKECOND instead.
291 ** The TERM_LIKECOND marking indicates that the term should be coded inside
292 ** a conditional such that is only evaluated on the second pass of a
293 ** LIKE-optimization loop, when scanning BLOBs instead of strings.
295 static void disableTerm(WhereLevel
*pLevel
, WhereTerm
*pTerm
){
298 while( (pTerm
->wtFlags
& TERM_CODED
)==0
299 && (pLevel
->iLeftJoin
==0 || ExprHasProperty(pTerm
->pExpr
, EP_FromJoin
))
300 && (pLevel
->notReady
& pTerm
->prereqAll
)==0
302 if( nLoop
&& (pTerm
->wtFlags
& TERM_LIKE
)!=0 ){
303 pTerm
->wtFlags
|= TERM_LIKECOND
;
305 pTerm
->wtFlags
|= TERM_CODED
;
307 if( pTerm
->iParent
<0 ) break;
308 pTerm
= &pTerm
->pWC
->a
[pTerm
->iParent
];
311 if( pTerm
->nChild
!=0 ) break;
317 ** Code an OP_Affinity opcode to apply the column affinity string zAff
318 ** to the n registers starting at base.
320 ** As an optimization, SQLITE_AFF_BLOB entries (which are no-ops) at the
321 ** beginning and end of zAff are ignored. If all entries in zAff are
322 ** SQLITE_AFF_BLOB, then no code gets generated.
324 ** This routine makes its own copy of zAff so that the caller is free
325 ** to modify zAff after this routine returns.
327 static void codeApplyAffinity(Parse
*pParse
, int base
, int n
, char *zAff
){
328 Vdbe
*v
= pParse
->pVdbe
;
330 assert( pParse
->db
->mallocFailed
);
335 /* Adjust base and n to skip over SQLITE_AFF_BLOB entries at the beginning
336 ** and end of the affinity string.
338 while( n
>0 && zAff
[0]==SQLITE_AFF_BLOB
){
343 while( n
>1 && zAff
[n
-1]==SQLITE_AFF_BLOB
){
347 /* Code the OP_Affinity opcode if there is anything left to do. */
349 sqlite3VdbeAddOp4(v
, OP_Affinity
, base
, n
, 0, zAff
, n
);
350 sqlite3ExprCacheAffinityChange(pParse
, base
, n
);
355 ** Expression pRight, which is the RHS of a comparison operation, is
356 ** either a vector of n elements or, if n==1, a scalar expression.
357 ** Before the comparison operation, affinity zAff is to be applied
358 ** to the pRight values. This function modifies characters within the
359 ** affinity string to SQLITE_AFF_BLOB if either:
361 ** * the comparison will be performed with no affinity, or
362 ** * the affinity change in zAff is guaranteed not to change the value.
364 static void updateRangeAffinityStr(
365 Expr
*pRight
, /* RHS of comparison */
366 int n
, /* Number of vector elements in comparison */
367 char *zAff
/* Affinity string to modify */
371 Expr
*p
= sqlite3VectorFieldSubexpr(pRight
, i
);
372 if( sqlite3CompareAffinity(p
, zAff
[i
])==SQLITE_AFF_BLOB
373 || sqlite3ExprNeedsNoAffinityChange(p
, zAff
[i
])
375 zAff
[i
] = SQLITE_AFF_BLOB
;
381 /* Return true if the pSub ExprList is a subset of pMain. The terms
382 ** of pSub can be in a different order from pMain. The only requirement
383 ** is that every term in pSub must exist somewhere in pMain.
385 ** Return false if pSub contains any term that is not found in pMain.
387 static int exprListSubset(ExprList
*pSub
, ExprList
*pMain
){
389 for(i
=0; i
<pSub
->nExpr
; i
++){
390 Expr
*p
= pSub
->a
[i
].pExpr
;
391 for(j
=0; j
<pMain
->nExpr
; j
++){
392 if( sqlite3ExprCompare(0, p
, pMain
->a
[j
].pExpr
, 0)==0 ) break;
394 if( j
>=pMain
->nExpr
) return 0;
398 #endif /* SQLITE_DEBUG */
402 ** Generate code for a single equality term of the WHERE clause. An equality
403 ** term can be either X=expr or X IN (...). pTerm is the term to be
406 ** The current value for the constraint is left in a register, the index
407 ** of which is returned. An attempt is made store the result in iTarget but
408 ** this is only guaranteed for TK_ISNULL and TK_IN constraints. If the
409 ** constraint is a TK_EQ or TK_IS, then the current value might be left in
410 ** some other register and it is the caller's responsibility to compensate.
412 ** For a constraint of the form X=expr, the expression is evaluated in
413 ** straight-line code. For constraints of the form X IN (...)
414 ** this routine sets up a loop that will iterate over all values of X.
416 static int codeEqualityTerm(
417 Parse
*pParse
, /* The parsing context */
418 WhereTerm
*pTerm
, /* The term of the WHERE clause to be coded */
419 WhereLevel
*pLevel
, /* The level of the FROM clause we are working on */
420 int iEq
, /* Index of the equality term within this level */
421 int bRev
, /* True for reverse-order IN operations */
422 int iTarget
/* Attempt to leave results in this register */
424 Expr
*pX
= pTerm
->pExpr
;
425 Vdbe
*v
= pParse
->pVdbe
;
426 int iReg
; /* Register holding results */
428 assert( pLevel
->pWLoop
->aLTerm
[iEq
]==pTerm
);
430 if( pX
->op
==TK_EQ
|| pX
->op
==TK_IS
){
431 iReg
= sqlite3ExprCodeTarget(pParse
, pX
->pRight
, iTarget
);
432 }else if( pX
->op
==TK_ISNULL
){
434 sqlite3VdbeAddOp2(v
, OP_Null
, 0, iReg
);
435 #ifndef SQLITE_OMIT_SUBQUERY
437 int eType
= IN_INDEX_NOOP
;
440 WhereLoop
*pLoop
= pLevel
->pWLoop
;
445 if( (pLoop
->wsFlags
& WHERE_VIRTUALTABLE
)==0
446 && pLoop
->u
.btree
.pIndex
!=0
447 && pLoop
->u
.btree
.pIndex
->aSortOrder
[iEq
]
453 assert( pX
->op
==TK_IN
);
456 for(i
=0; i
<iEq
; i
++){
457 if( pLoop
->aLTerm
[i
] && pLoop
->aLTerm
[i
]->pExpr
==pX
){
458 disableTerm(pLevel
, pTerm
);
462 for(i
=iEq
;i
<pLoop
->nLTerm
; i
++){
463 if( ALWAYS(pLoop
->aLTerm
[i
]) && pLoop
->aLTerm
[i
]->pExpr
==pX
) nEq
++;
466 if( (pX
->flags
& EP_xIsSelect
)==0 || pX
->x
.pSelect
->pEList
->nExpr
==1 ){
467 eType
= sqlite3FindInIndex(pParse
, pX
, IN_INDEX_LOOP
, 0, 0);
469 Select
*pSelect
= pX
->x
.pSelect
;
470 sqlite3
*db
= pParse
->db
;
471 u16 savedDbOptFlags
= db
->dbOptFlags
;
472 ExprList
*pOrigRhs
= pSelect
->pEList
;
473 ExprList
*pOrigLhs
= pX
->pLeft
->x
.pList
;
474 ExprList
*pRhs
= 0; /* New Select.pEList for RHS */
475 ExprList
*pLhs
= 0; /* New pX->pLeft vector */
477 for(i
=iEq
;i
<pLoop
->nLTerm
; i
++){
478 if( pLoop
->aLTerm
[i
]->pExpr
==pX
){
479 int iField
= pLoop
->aLTerm
[i
]->iField
- 1;
480 Expr
*pNewRhs
= sqlite3ExprDup(db
, pOrigRhs
->a
[iField
].pExpr
, 0);
481 Expr
*pNewLhs
= sqlite3ExprDup(db
, pOrigLhs
->a
[iField
].pExpr
, 0);
483 pRhs
= sqlite3ExprListAppend(pParse
, pRhs
, pNewRhs
);
484 pLhs
= sqlite3ExprListAppend(pParse
, pLhs
, pNewLhs
);
488 /* pRhs should be a subset of pOrigRhs (though possibly in a different
489 ** order). And pLhs should be a subset of pOrigLhs. To put it
490 ** another way: Every term of pRhs should exist in pOrigRhs and
491 ** every term of pLhs should exist in pOrigLhs. */
492 assert( db
->mallocFailed
|| exprListSubset(pRhs
, pOrigRhs
) );
493 assert( db
->mallocFailed
|| exprListSubset(pLhs
, pOrigLhs
) );
495 if( !db
->mallocFailed
){
496 Expr
*pLeft
= pX
->pLeft
;
498 if( pSelect
->pOrderBy
){
499 /* If the SELECT statement has an ORDER BY clause, zero the
500 ** iOrderByCol variables. These are set to non-zero when an
501 ** ORDER BY term exactly matches one of the terms of the
502 ** result-set. Since the result-set of the SELECT statement may
503 ** have been modified or reordered, these variables are no longer
504 ** set correctly. Since setting them is just an optimization,
505 ** it's easiest just to zero them here. */
506 ExprList
*pOrderBy
= pSelect
->pOrderBy
;
507 for(i
=0; i
<pOrderBy
->nExpr
; i
++){
508 pOrderBy
->a
[i
].u
.x
.iOrderByCol
= 0;
512 /* Take care here not to generate a TK_VECTOR containing only a
513 ** single value. Since the parser never creates such a vector, some
514 ** of the subroutines do not handle this case. */
515 if( pLhs
->nExpr
==1 ){
516 pX
->pLeft
= pLhs
->a
[0].pExpr
;
518 pLeft
->x
.pList
= pLhs
;
519 aiMap
= (int*)sqlite3DbMallocZero(pParse
->db
, sizeof(int) * nEq
);
520 testcase( aiMap
==0 );
522 pSelect
->pEList
= pRhs
;
523 db
->dbOptFlags
|= SQLITE_QueryFlattener
;
524 eType
= sqlite3FindInIndex(pParse
, pX
, IN_INDEX_LOOP
, 0, aiMap
);
525 db
->dbOptFlags
= savedDbOptFlags
;
526 testcase( aiMap
!=0 && aiMap
[0]!=0 );
527 pSelect
->pEList
= pOrigRhs
;
528 pLeft
->x
.pList
= pOrigLhs
;
531 sqlite3ExprListDelete(pParse
->db
, pLhs
);
532 sqlite3ExprListDelete(pParse
->db
, pRhs
);
535 if( eType
==IN_INDEX_INDEX_DESC
){
540 sqlite3VdbeAddOp2(v
, bRev
? OP_Last
: OP_Rewind
, iTab
, 0);
541 VdbeCoverageIf(v
, bRev
);
542 VdbeCoverageIf(v
, !bRev
);
543 assert( (pLoop
->wsFlags
& WHERE_MULTI_OR
)==0 );
545 pLoop
->wsFlags
|= WHERE_IN_ABLE
;
546 if( pLevel
->u
.in
.nIn
==0 ){
547 pLevel
->addrNxt
= sqlite3VdbeMakeLabel(v
);
550 i
= pLevel
->u
.in
.nIn
;
551 pLevel
->u
.in
.nIn
+= nEq
;
552 pLevel
->u
.in
.aInLoop
=
553 sqlite3DbReallocOrFree(pParse
->db
, pLevel
->u
.in
.aInLoop
,
554 sizeof(pLevel
->u
.in
.aInLoop
[0])*pLevel
->u
.in
.nIn
);
555 pIn
= pLevel
->u
.in
.aInLoop
;
557 int iMap
= 0; /* Index in aiMap[] */
559 for(i
=iEq
;i
<pLoop
->nLTerm
; i
++){
560 if( pLoop
->aLTerm
[i
]->pExpr
==pX
){
561 int iOut
= iReg
+ i
- iEq
;
562 if( eType
==IN_INDEX_ROWID
){
563 testcase( nEq
>1 ); /* Happens with a UNIQUE index on ROWID */
564 pIn
->addrInTop
= sqlite3VdbeAddOp2(v
, OP_Rowid
, iTab
, iOut
);
566 int iCol
= aiMap
? aiMap
[iMap
++] : 0;
567 pIn
->addrInTop
= sqlite3VdbeAddOp3(v
,OP_Column
,iTab
, iCol
, iOut
);
569 sqlite3VdbeAddOp1(v
, OP_IsNull
, iOut
); VdbeCoverage(v
);
572 pIn
->eEndLoopOp
= bRev
? OP_PrevIfOpen
: OP_NextIfOpen
;
574 pIn
->eEndLoopOp
= OP_Noop
;
580 pLevel
->u
.in
.nIn
= 0;
582 sqlite3DbFree(pParse
->db
, aiMap
);
585 disableTerm(pLevel
, pTerm
);
590 ** Generate code that will evaluate all == and IN constraints for an
593 ** For example, consider table t1(a,b,c,d,e,f) with index i1(a,b,c).
594 ** Suppose the WHERE clause is this: a==5 AND b IN (1,2,3) AND c>5 AND c<10
595 ** The index has as many as three equality constraints, but in this
596 ** example, the third "c" value is an inequality. So only two
597 ** constraints are coded. This routine will generate code to evaluate
598 ** a==5 and b IN (1,2,3). The current values for a and b will be stored
599 ** in consecutive registers and the index of the first register is returned.
601 ** In the example above nEq==2. But this subroutine works for any value
602 ** of nEq including 0. If nEq==0, this routine is nearly a no-op.
603 ** The only thing it does is allocate the pLevel->iMem memory cell and
604 ** compute the affinity string.
606 ** The nExtraReg parameter is 0 or 1. It is 0 if all WHERE clause constraints
607 ** are == or IN and are covered by the nEq. nExtraReg is 1 if there is
608 ** an inequality constraint (such as the "c>=5 AND c<10" in the example) that
609 ** occurs after the nEq quality constraints.
611 ** This routine allocates a range of nEq+nExtraReg memory cells and returns
612 ** the index of the first memory cell in that range. The code that
613 ** calls this routine will use that memory range to store keys for
614 ** start and termination conditions of the loop.
615 ** key value of the loop. If one or more IN operators appear, then
616 ** this routine allocates an additional nEq memory cells for internal
619 ** Before returning, *pzAff is set to point to a buffer containing a
620 ** copy of the column affinity string of the index allocated using
621 ** sqlite3DbMalloc(). Except, entries in the copy of the string associated
622 ** with equality constraints that use BLOB or NONE affinity are set to
623 ** SQLITE_AFF_BLOB. This is to deal with SQL such as the following:
625 ** CREATE TABLE t1(a TEXT PRIMARY KEY, b);
626 ** SELECT ... FROM t1 AS t2, t1 WHERE t1.a = t2.b;
628 ** In the example above, the index on t1(a) has TEXT affinity. But since
629 ** the right hand side of the equality constraint (t2.b) has BLOB/NONE affinity,
630 ** no conversion should be attempted before using a t2.b value as part of
631 ** a key to search the index. Hence the first byte in the returned affinity
632 ** string in this example would be set to SQLITE_AFF_BLOB.
634 static int codeAllEqualityTerms(
635 Parse
*pParse
, /* Parsing context */
636 WhereLevel
*pLevel
, /* Which nested loop of the FROM we are coding */
637 int bRev
, /* Reverse the order of IN operators */
638 int nExtraReg
, /* Number of extra registers to allocate */
639 char **pzAff
/* OUT: Set to point to affinity string */
641 u16 nEq
; /* The number of == or IN constraints to code */
642 u16 nSkip
; /* Number of left-most columns to skip */
643 Vdbe
*v
= pParse
->pVdbe
; /* The vm under construction */
644 Index
*pIdx
; /* The index being used for this loop */
645 WhereTerm
*pTerm
; /* A single constraint term */
646 WhereLoop
*pLoop
; /* The WhereLoop object */
647 int j
; /* Loop counter */
648 int regBase
; /* Base register */
649 int nReg
; /* Number of registers to allocate */
650 char *zAff
; /* Affinity string to return */
652 /* This module is only called on query plans that use an index. */
653 pLoop
= pLevel
->pWLoop
;
654 assert( (pLoop
->wsFlags
& WHERE_VIRTUALTABLE
)==0 );
655 nEq
= pLoop
->u
.btree
.nEq
;
656 nSkip
= pLoop
->nSkip
;
657 pIdx
= pLoop
->u
.btree
.pIndex
;
660 /* Figure out how many memory cells we will need then allocate them.
662 regBase
= pParse
->nMem
+ 1;
663 nReg
= pLoop
->u
.btree
.nEq
+ nExtraReg
;
664 pParse
->nMem
+= nReg
;
666 zAff
= sqlite3DbStrDup(pParse
->db
,sqlite3IndexAffinityStr(pParse
->db
,pIdx
));
667 assert( zAff
!=0 || pParse
->db
->mallocFailed
);
670 int iIdxCur
= pLevel
->iIdxCur
;
671 sqlite3VdbeAddOp1(v
, (bRev
?OP_Last
:OP_Rewind
), iIdxCur
);
672 VdbeCoverageIf(v
, bRev
==0);
673 VdbeCoverageIf(v
, bRev
!=0);
674 VdbeComment((v
, "begin skip-scan on %s", pIdx
->zName
));
675 j
= sqlite3VdbeAddOp0(v
, OP_Goto
);
676 pLevel
->addrSkip
= sqlite3VdbeAddOp4Int(v
, (bRev
?OP_SeekLT
:OP_SeekGT
),
677 iIdxCur
, 0, regBase
, nSkip
);
678 VdbeCoverageIf(v
, bRev
==0);
679 VdbeCoverageIf(v
, bRev
!=0);
680 sqlite3VdbeJumpHere(v
, j
);
681 for(j
=0; j
<nSkip
; j
++){
682 sqlite3VdbeAddOp3(v
, OP_Column
, iIdxCur
, j
, regBase
+j
);
683 testcase( pIdx
->aiColumn
[j
]==XN_EXPR
);
684 VdbeComment((v
, "%s", explainIndexColumnName(pIdx
, j
)));
688 /* Evaluate the equality constraints
690 assert( zAff
==0 || (int)strlen(zAff
)>=nEq
);
691 for(j
=nSkip
; j
<nEq
; j
++){
693 pTerm
= pLoop
->aLTerm
[j
];
695 /* The following testcase is true for indices with redundant columns.
696 ** Ex: CREATE INDEX i1 ON t1(a,b,a); SELECT * FROM t1 WHERE a=0 AND b=0; */
697 testcase( (pTerm
->wtFlags
& TERM_CODED
)!=0 );
698 testcase( pTerm
->wtFlags
& TERM_VIRTUAL
);
699 r1
= codeEqualityTerm(pParse
, pTerm
, pLevel
, j
, bRev
, regBase
+j
);
702 sqlite3ReleaseTempReg(pParse
, regBase
);
705 sqlite3VdbeAddOp2(v
, OP_SCopy
, r1
, regBase
+j
);
708 if( pTerm
->eOperator
& WO_IN
){
709 if( pTerm
->pExpr
->flags
& EP_xIsSelect
){
710 /* No affinity ever needs to be (or should be) applied to a value
711 ** from the RHS of an "? IN (SELECT ...)" expression. The
712 ** sqlite3FindInIndex() routine has already ensured that the
713 ** affinity of the comparison has been applied to the value. */
714 if( zAff
) zAff
[j
] = SQLITE_AFF_BLOB
;
716 }else if( (pTerm
->eOperator
& WO_ISNULL
)==0 ){
717 Expr
*pRight
= pTerm
->pExpr
->pRight
;
718 if( (pTerm
->wtFlags
& TERM_IS
)==0 && sqlite3ExprCanBeNull(pRight
) ){
719 sqlite3VdbeAddOp2(v
, OP_IsNull
, regBase
+j
, pLevel
->addrBrk
);
723 if( sqlite3CompareAffinity(pRight
, zAff
[j
])==SQLITE_AFF_BLOB
){
724 zAff
[j
] = SQLITE_AFF_BLOB
;
726 if( sqlite3ExprNeedsNoAffinityChange(pRight
, zAff
[j
]) ){
727 zAff
[j
] = SQLITE_AFF_BLOB
;
736 #ifndef SQLITE_LIKE_DOESNT_MATCH_BLOBS
738 ** If the most recently coded instruction is a constant range constraint
739 ** (a string literal) that originated from the LIKE optimization, then
740 ** set P3 and P5 on the OP_String opcode so that the string will be cast
741 ** to a BLOB at appropriate times.
743 ** The LIKE optimization trys to evaluate "x LIKE 'abc%'" as a range
744 ** expression: "x>='ABC' AND x<'abd'". But this requires that the range
745 ** scan loop run twice, once for strings and a second time for BLOBs.
746 ** The OP_String opcodes on the second pass convert the upper and lower
747 ** bound string constants to blobs. This routine makes the necessary changes
748 ** to the OP_String opcodes for that to happen.
750 ** Except, of course, if SQLITE_LIKE_DOESNT_MATCH_BLOBS is defined, then
751 ** only the one pass through the string space is required, so this routine
754 static void whereLikeOptimizationStringFixup(
755 Vdbe
*v
, /* prepared statement under construction */
756 WhereLevel
*pLevel
, /* The loop that contains the LIKE operator */
757 WhereTerm
*pTerm
/* The upper or lower bound just coded */
759 if( pTerm
->wtFlags
& TERM_LIKEOPT
){
761 assert( pLevel
->iLikeRepCntr
>0 );
762 pOp
= sqlite3VdbeGetOp(v
, -1);
764 assert( pOp
->opcode
==OP_String8
765 || pTerm
->pWC
->pWInfo
->pParse
->db
->mallocFailed
);
766 pOp
->p3
= (int)(pLevel
->iLikeRepCntr
>>1); /* Register holding counter */
767 pOp
->p5
= (u8
)(pLevel
->iLikeRepCntr
&1); /* ASC or DESC */
771 # define whereLikeOptimizationStringFixup(A,B,C)
774 #ifdef SQLITE_ENABLE_CURSOR_HINTS
776 ** Information is passed from codeCursorHint() down to individual nodes of
777 ** the expression tree (by sqlite3WalkExpr()) using an instance of this
781 int iTabCur
; /* Cursor for the main table */
782 int iIdxCur
; /* Cursor for the index, if pIdx!=0. Unused otherwise */
783 Index
*pIdx
; /* The index used to access the table */
787 ** This function is called for every node of an expression that is a candidate
788 ** for a cursor hint on an index cursor. For TK_COLUMN nodes that reference
789 ** the table CCurHint.iTabCur, verify that the same column can be
790 ** accessed through the index. If it cannot, then set pWalker->eCode to 1.
792 static int codeCursorHintCheckExpr(Walker
*pWalker
, Expr
*pExpr
){
793 struct CCurHint
*pHint
= pWalker
->u
.pCCurHint
;
794 assert( pHint
->pIdx
!=0 );
795 if( pExpr
->op
==TK_COLUMN
796 && pExpr
->iTable
==pHint
->iTabCur
797 && sqlite3ColumnOfIndex(pHint
->pIdx
, pExpr
->iColumn
)<0
805 ** Test whether or not expression pExpr, which was part of a WHERE clause,
806 ** should be included in the cursor-hint for a table that is on the rhs
807 ** of a LEFT JOIN. Set Walker.eCode to non-zero before returning if the
808 ** expression is not suitable.
810 ** An expression is unsuitable if it might evaluate to non NULL even if
811 ** a TK_COLUMN node that does affect the value of the expression is set
812 ** to NULL. For example:
817 ** CASE WHEN col THEN 0 ELSE 1 END
819 static int codeCursorHintIsOrFunction(Walker
*pWalker
, Expr
*pExpr
){
821 || pExpr
->op
==TK_ISNULL
|| pExpr
->op
==TK_ISNOT
822 || pExpr
->op
==TK_NOTNULL
|| pExpr
->op
==TK_CASE
825 }else if( pExpr
->op
==TK_FUNCTION
){
828 if( 0==sqlite3IsLikeFunction(pWalker
->pParse
->db
, pExpr
, &d1
, d2
) ){
838 ** This function is called on every node of an expression tree used as an
839 ** argument to the OP_CursorHint instruction. If the node is a TK_COLUMN
840 ** that accesses any table other than the one identified by
841 ** CCurHint.iTabCur, then do the following:
843 ** 1) allocate a register and code an OP_Column instruction to read
844 ** the specified column into the new register, and
846 ** 2) transform the expression node to a TK_REGISTER node that reads
847 ** from the newly populated register.
849 ** Also, if the node is a TK_COLUMN that does access the table idenified
850 ** by pCCurHint.iTabCur, and an index is being used (which we will
851 ** know because CCurHint.pIdx!=0) then transform the TK_COLUMN into
852 ** an access of the index rather than the original table.
854 static int codeCursorHintFixExpr(Walker
*pWalker
, Expr
*pExpr
){
855 int rc
= WRC_Continue
;
856 struct CCurHint
*pHint
= pWalker
->u
.pCCurHint
;
857 if( pExpr
->op
==TK_COLUMN
){
858 if( pExpr
->iTable
!=pHint
->iTabCur
){
859 Vdbe
*v
= pWalker
->pParse
->pVdbe
;
860 int reg
= ++pWalker
->pParse
->nMem
; /* Register for column value */
861 sqlite3ExprCodeGetColumnOfTable(
862 v
, pExpr
->pTab
, pExpr
->iTable
, pExpr
->iColumn
, reg
864 pExpr
->op
= TK_REGISTER
;
866 }else if( pHint
->pIdx
!=0 ){
867 pExpr
->iTable
= pHint
->iIdxCur
;
868 pExpr
->iColumn
= sqlite3ColumnOfIndex(pHint
->pIdx
, pExpr
->iColumn
);
869 assert( pExpr
->iColumn
>=0 );
871 }else if( pExpr
->op
==TK_AGG_FUNCTION
){
872 /* An aggregate function in the WHERE clause of a query means this must
873 ** be a correlated sub-query, and expression pExpr is an aggregate from
874 ** the parent context. Do not walk the function arguments in this case.
876 ** todo: It should be possible to replace this node with a TK_REGISTER
877 ** expression, as the result of the expression must be stored in a
878 ** register at this point. The same holds for TK_AGG_COLUMN nodes. */
885 ** Insert an OP_CursorHint instruction if it is appropriate to do so.
887 static void codeCursorHint(
888 struct SrcList_item
*pTabItem
, /* FROM clause item */
889 WhereInfo
*pWInfo
, /* The where clause */
890 WhereLevel
*pLevel
, /* Which loop to provide hints for */
891 WhereTerm
*pEndRange
/* Hint this end-of-scan boundary term if not NULL */
893 Parse
*pParse
= pWInfo
->pParse
;
894 sqlite3
*db
= pParse
->db
;
895 Vdbe
*v
= pParse
->pVdbe
;
897 WhereLoop
*pLoop
= pLevel
->pWLoop
;
902 struct CCurHint sHint
;
905 if( OptimizationDisabled(db
, SQLITE_CursorHints
) ) return;
906 iCur
= pLevel
->iTabCur
;
907 assert( iCur
==pWInfo
->pTabList
->a
[pLevel
->iFrom
].iCursor
);
908 sHint
.iTabCur
= iCur
;
909 sHint
.iIdxCur
= pLevel
->iIdxCur
;
910 sHint
.pIdx
= pLoop
->u
.btree
.pIndex
;
911 memset(&sWalker
, 0, sizeof(sWalker
));
912 sWalker
.pParse
= pParse
;
913 sWalker
.u
.pCCurHint
= &sHint
;
915 for(i
=0; i
<pWC
->nTerm
; i
++){
917 if( pTerm
->wtFlags
& (TERM_VIRTUAL
|TERM_CODED
) ) continue;
918 if( pTerm
->prereqAll
& pLevel
->notReady
) continue;
920 /* Any terms specified as part of the ON(...) clause for any LEFT
921 ** JOIN for which the current table is not the rhs are omitted
922 ** from the cursor-hint.
924 ** If this table is the rhs of a LEFT JOIN, "IS" or "IS NULL" terms
925 ** that were specified as part of the WHERE clause must be excluded.
926 ** This is to address the following:
928 ** SELECT ... t1 LEFT JOIN t2 ON (t1.a=t2.b) WHERE t2.c IS NULL;
930 ** Say there is a single row in t2 that matches (t1.a=t2.b), but its
931 ** t2.c values is not NULL. If the (t2.c IS NULL) constraint is
932 ** pushed down to the cursor, this row is filtered out, causing
933 ** SQLite to synthesize a row of NULL values. Which does match the
934 ** WHERE clause, and so the query returns a row. Which is incorrect.
936 ** For the same reason, WHERE terms such as:
938 ** WHERE 1 = (t2.c IS NULL)
940 ** are also excluded. See codeCursorHintIsOrFunction() for details.
942 if( pTabItem
->fg
.jointype
& JT_LEFT
){
943 Expr
*pExpr
= pTerm
->pExpr
;
944 if( !ExprHasProperty(pExpr
, EP_FromJoin
)
945 || pExpr
->iRightJoinTable
!=pTabItem
->iCursor
948 sWalker
.xExprCallback
= codeCursorHintIsOrFunction
;
949 sqlite3WalkExpr(&sWalker
, pTerm
->pExpr
);
950 if( sWalker
.eCode
) continue;
953 if( ExprHasProperty(pTerm
->pExpr
, EP_FromJoin
) ) continue;
956 /* All terms in pWLoop->aLTerm[] except pEndRange are used to initialize
957 ** the cursor. These terms are not needed as hints for a pure range
958 ** scan (that has no == terms) so omit them. */
959 if( pLoop
->u
.btree
.nEq
==0 && pTerm
!=pEndRange
){
960 for(j
=0; j
<pLoop
->nLTerm
&& pLoop
->aLTerm
[j
]!=pTerm
; j
++){}
961 if( j
<pLoop
->nLTerm
) continue;
964 /* No subqueries or non-deterministic functions allowed */
965 if( sqlite3ExprContainsSubquery(pTerm
->pExpr
) ) continue;
967 /* For an index scan, make sure referenced columns are actually in
971 sWalker
.xExprCallback
= codeCursorHintCheckExpr
;
972 sqlite3WalkExpr(&sWalker
, pTerm
->pExpr
);
973 if( sWalker
.eCode
) continue;
976 /* If we survive all prior tests, that means this term is worth hinting */
977 pExpr
= sqlite3ExprAnd(db
, pExpr
, sqlite3ExprDup(db
, pTerm
->pExpr
, 0));
980 sWalker
.xExprCallback
= codeCursorHintFixExpr
;
981 sqlite3WalkExpr(&sWalker
, pExpr
);
982 sqlite3VdbeAddOp4(v
, OP_CursorHint
,
983 (sHint
.pIdx
? sHint
.iIdxCur
: sHint
.iTabCur
), 0, 0,
984 (const char*)pExpr
, P4_EXPR
);
988 # define codeCursorHint(A,B,C,D) /* No-op */
989 #endif /* SQLITE_ENABLE_CURSOR_HINTS */
992 ** Cursor iCur is open on an intkey b-tree (a table). Register iRowid contains
993 ** a rowid value just read from cursor iIdxCur, open on index pIdx. This
994 ** function generates code to do a deferred seek of cursor iCur to the
995 ** rowid stored in register iRowid.
997 ** Normally, this is just:
999 ** OP_DeferredSeek $iCur $iRowid
1001 ** However, if the scan currently being coded is a branch of an OR-loop and
1002 ** the statement currently being coded is a SELECT, then P3 of OP_DeferredSeek
1003 ** is set to iIdxCur and P4 is set to point to an array of integers
1004 ** containing one entry for each column of the table cursor iCur is open
1005 ** on. For each table column, if the column is the i'th column of the
1006 ** index, then the corresponding array entry is set to (i+1). If the column
1007 ** does not appear in the index at all, the array entry is set to 0.
1009 static void codeDeferredSeek(
1010 WhereInfo
*pWInfo
, /* Where clause context */
1011 Index
*pIdx
, /* Index scan is using */
1012 int iCur
, /* Cursor for IPK b-tree */
1013 int iIdxCur
/* Index cursor */
1015 Parse
*pParse
= pWInfo
->pParse
; /* Parse context */
1016 Vdbe
*v
= pParse
->pVdbe
; /* Vdbe to generate code within */
1018 assert( iIdxCur
>0 );
1019 assert( pIdx
->aiColumn
[pIdx
->nColumn
-1]==-1 );
1021 sqlite3VdbeAddOp3(v
, OP_DeferredSeek
, iIdxCur
, 0, iCur
);
1022 if( (pWInfo
->wctrlFlags
& WHERE_OR_SUBCLAUSE
)
1023 && DbMaskAllZero(sqlite3ParseToplevel(pParse
)->writeMask
)
1026 Table
*pTab
= pIdx
->pTable
;
1027 int *ai
= (int*)sqlite3DbMallocZero(pParse
->db
, sizeof(int)*(pTab
->nCol
+1));
1030 for(i
=0; i
<pIdx
->nColumn
-1; i
++){
1031 assert( pIdx
->aiColumn
[i
]<pTab
->nCol
);
1032 if( pIdx
->aiColumn
[i
]>=0 ) ai
[pIdx
->aiColumn
[i
]+1] = i
+1;
1034 sqlite3VdbeChangeP4(v
, -1, (char*)ai
, P4_INTARRAY
);
1040 ** If the expression passed as the second argument is a vector, generate
1041 ** code to write the first nReg elements of the vector into an array
1042 ** of registers starting with iReg.
1044 ** If the expression is not a vector, then nReg must be passed 1. In
1045 ** this case, generate code to evaluate the expression and leave the
1046 ** result in register iReg.
1048 static void codeExprOrVector(Parse
*pParse
, Expr
*p
, int iReg
, int nReg
){
1050 if( p
&& sqlite3ExprIsVector(p
) ){
1051 #ifndef SQLITE_OMIT_SUBQUERY
1052 if( (p
->flags
& EP_xIsSelect
) ){
1053 Vdbe
*v
= pParse
->pVdbe
;
1054 int iSelect
= sqlite3CodeSubselect(pParse
, p
, 0, 0);
1055 sqlite3VdbeAddOp3(v
, OP_Copy
, iSelect
, iReg
, nReg
-1);
1060 ExprList
*pList
= p
->x
.pList
;
1061 assert( nReg
<=pList
->nExpr
);
1062 for(i
=0; i
<nReg
; i
++){
1063 sqlite3ExprCode(pParse
, pList
->a
[i
].pExpr
, iReg
+i
);
1068 sqlite3ExprCode(pParse
, p
, iReg
);
1072 /* An instance of the IdxExprTrans object carries information about a
1073 ** mapping from an expression on table columns into a column in an index
1074 ** down through the Walker.
1076 typedef struct IdxExprTrans
{
1077 Expr
*pIdxExpr
; /* The index expression */
1078 int iTabCur
; /* The cursor of the corresponding table */
1079 int iIdxCur
; /* The cursor for the index */
1080 int iIdxCol
; /* The column for the index */
1083 /* The walker node callback used to transform matching expressions into
1084 ** a reference to an index column for an index on an expression.
1086 ** If pExpr matches, then transform it into a reference to the index column
1087 ** that contains the value of pExpr.
1089 static int whereIndexExprTransNode(Walker
*p
, Expr
*pExpr
){
1090 IdxExprTrans
*pX
= p
->u
.pIdxTrans
;
1091 if( sqlite3ExprCompare(0, pExpr
, pX
->pIdxExpr
, pX
->iTabCur
)==0 ){
1092 pExpr
->op
= TK_COLUMN
;
1093 pExpr
->iTable
= pX
->iIdxCur
;
1094 pExpr
->iColumn
= pX
->iIdxCol
;
1098 return WRC_Continue
;
1103 ** For an indexes on expression X, locate every instance of expression X
1104 ** in pExpr and change that subexpression into a reference to the appropriate
1105 ** column of the index.
1107 static void whereIndexExprTrans(
1108 Index
*pIdx
, /* The Index */
1109 int iTabCur
, /* Cursor of the table that is being indexed */
1110 int iIdxCur
, /* Cursor of the index itself */
1111 WhereInfo
*pWInfo
/* Transform expressions in this WHERE clause */
1113 int iIdxCol
; /* Column number of the index */
1114 ExprList
*aColExpr
; /* Expressions that are indexed */
1117 aColExpr
= pIdx
->aColExpr
;
1118 if( aColExpr
==0 ) return; /* Not an index on expressions */
1119 memset(&w
, 0, sizeof(w
));
1120 w
.xExprCallback
= whereIndexExprTransNode
;
1122 x
.iTabCur
= iTabCur
;
1123 x
.iIdxCur
= iIdxCur
;
1124 for(iIdxCol
=0; iIdxCol
<aColExpr
->nExpr
; iIdxCol
++){
1125 if( pIdx
->aiColumn
[iIdxCol
]!=XN_EXPR
) continue;
1126 assert( aColExpr
->a
[iIdxCol
].pExpr
!=0 );
1127 x
.iIdxCol
= iIdxCol
;
1128 x
.pIdxExpr
= aColExpr
->a
[iIdxCol
].pExpr
;
1129 sqlite3WalkExpr(&w
, pWInfo
->pWhere
);
1130 sqlite3WalkExprList(&w
, pWInfo
->pOrderBy
);
1131 sqlite3WalkExprList(&w
, pWInfo
->pResultSet
);
1136 ** Generate code for the start of the iLevel-th loop in the WHERE clause
1137 ** implementation described by pWInfo.
1139 Bitmask
sqlite3WhereCodeOneLoopStart(
1140 WhereInfo
*pWInfo
, /* Complete information about the WHERE clause */
1141 int iLevel
, /* Which level of pWInfo->a[] should be coded */
1142 Bitmask notReady
/* Which tables are currently available */
1144 int j
, k
; /* Loop counters */
1145 int iCur
; /* The VDBE cursor for the table */
1146 int addrNxt
; /* Where to jump to continue with the next IN case */
1147 int omitTable
; /* True if we use the index only */
1148 int bRev
; /* True if we need to scan in reverse order */
1149 WhereLevel
*pLevel
; /* The where level to be coded */
1150 WhereLoop
*pLoop
; /* The WhereLoop object being coded */
1151 WhereClause
*pWC
; /* Decomposition of the entire WHERE clause */
1152 WhereTerm
*pTerm
; /* A WHERE clause term */
1153 Parse
*pParse
; /* Parsing context */
1154 sqlite3
*db
; /* Database connection */
1155 Vdbe
*v
; /* The prepared stmt under constructions */
1156 struct SrcList_item
*pTabItem
; /* FROM clause term being coded */
1157 int addrBrk
; /* Jump here to break out of the loop */
1158 int addrHalt
; /* addrBrk for the outermost loop */
1159 int addrCont
; /* Jump here to continue with next cycle */
1160 int iRowidReg
= 0; /* Rowid is stored in this register, if not zero */
1161 int iReleaseReg
= 0; /* Temp register to free before returning */
1162 Index
*pIdx
= 0; /* Index used by loop (if any) */
1163 int iLoop
; /* Iteration of constraint generator loop */
1165 pParse
= pWInfo
->pParse
;
1169 pLevel
= &pWInfo
->a
[iLevel
];
1170 pLoop
= pLevel
->pWLoop
;
1171 pTabItem
= &pWInfo
->pTabList
->a
[pLevel
->iFrom
];
1172 iCur
= pTabItem
->iCursor
;
1173 pLevel
->notReady
= notReady
& ~sqlite3WhereGetMask(&pWInfo
->sMaskSet
, iCur
);
1174 bRev
= (pWInfo
->revMask
>>iLevel
)&1;
1175 omitTable
= (pLoop
->wsFlags
& WHERE_IDX_ONLY
)!=0
1176 && (pWInfo
->wctrlFlags
& WHERE_OR_SUBCLAUSE
)==0;
1177 VdbeModuleComment((v
, "Begin WHERE-loop%d: %s",iLevel
,pTabItem
->pTab
->zName
));
1179 /* Create labels for the "break" and "continue" instructions
1180 ** for the current loop. Jump to addrBrk to break out of a loop.
1181 ** Jump to cont to go immediately to the next iteration of the
1184 ** When there is an IN operator, we also have a "addrNxt" label that
1185 ** means to continue with the next IN value combination. When
1186 ** there are no IN operators in the constraints, the "addrNxt" label
1187 ** is the same as "addrBrk".
1189 addrBrk
= pLevel
->addrBrk
= pLevel
->addrNxt
= sqlite3VdbeMakeLabel(v
);
1190 addrCont
= pLevel
->addrCont
= sqlite3VdbeMakeLabel(v
);
1192 /* If this is the right table of a LEFT OUTER JOIN, allocate and
1193 ** initialize a memory cell that records if this table matches any
1194 ** row of the left table of the join.
1196 if( pLevel
->iFrom
>0 && (pTabItem
[0].fg
.jointype
& JT_LEFT
)!=0 ){
1197 pLevel
->iLeftJoin
= ++pParse
->nMem
;
1198 sqlite3VdbeAddOp2(v
, OP_Integer
, 0, pLevel
->iLeftJoin
);
1199 VdbeComment((v
, "init LEFT JOIN no-match flag"));
1202 /* Compute a safe address to jump to if we discover that the table for
1203 ** this loop is empty and can never contribute content. */
1204 for(j
=iLevel
; j
>0 && pWInfo
->a
[j
].iLeftJoin
==0; j
--){}
1205 addrHalt
= pWInfo
->a
[j
].addrBrk
;
1207 /* Special case of a FROM clause subquery implemented as a co-routine */
1208 if( pTabItem
->fg
.viaCoroutine
){
1209 int regYield
= pTabItem
->regReturn
;
1210 sqlite3VdbeAddOp3(v
, OP_InitCoroutine
, regYield
, 0, pTabItem
->addrFillSub
);
1211 pLevel
->p2
= sqlite3VdbeAddOp2(v
, OP_Yield
, regYield
, addrBrk
);
1213 VdbeComment((v
, "next row of \"%s\"", pTabItem
->pTab
->zName
));
1214 pLevel
->op
= OP_Goto
;
1217 #ifndef SQLITE_OMIT_VIRTUALTABLE
1218 if( (pLoop
->wsFlags
& WHERE_VIRTUALTABLE
)!=0 ){
1219 /* Case 1: The table is a virtual-table. Use the VFilter and VNext
1220 ** to access the data.
1222 int iReg
; /* P3 Value for OP_VFilter */
1224 int nConstraint
= pLoop
->nLTerm
;
1225 int iIn
; /* Counter for IN constraints */
1227 sqlite3ExprCachePush(pParse
);
1228 iReg
= sqlite3GetTempRange(pParse
, nConstraint
+2);
1229 addrNotFound
= pLevel
->addrBrk
;
1230 for(j
=0; j
<nConstraint
; j
++){
1231 int iTarget
= iReg
+j
+2;
1232 pTerm
= pLoop
->aLTerm
[j
];
1233 if( NEVER(pTerm
==0) ) continue;
1234 if( pTerm
->eOperator
& WO_IN
){
1235 codeEqualityTerm(pParse
, pTerm
, pLevel
, j
, bRev
, iTarget
);
1236 addrNotFound
= pLevel
->addrNxt
;
1238 Expr
*pRight
= pTerm
->pExpr
->pRight
;
1239 codeExprOrVector(pParse
, pRight
, iTarget
, 1);
1242 sqlite3VdbeAddOp2(v
, OP_Integer
, pLoop
->u
.vtab
.idxNum
, iReg
);
1243 sqlite3VdbeAddOp2(v
, OP_Integer
, nConstraint
, iReg
+1);
1244 sqlite3VdbeAddOp4(v
, OP_VFilter
, iCur
, addrNotFound
, iReg
,
1245 pLoop
->u
.vtab
.idxStr
,
1246 pLoop
->u
.vtab
.needFree
? P4_DYNAMIC
: P4_STATIC
);
1248 pLoop
->u
.vtab
.needFree
= 0;
1250 pLevel
->op
= pWInfo
->eOnePass
? OP_Noop
: OP_VNext
;
1251 pLevel
->p2
= sqlite3VdbeCurrentAddr(v
);
1252 iIn
= pLevel
->u
.in
.nIn
;
1253 for(j
=nConstraint
-1; j
>=0; j
--){
1254 pTerm
= pLoop
->aLTerm
[j
];
1255 if( j
<16 && (pLoop
->u
.vtab
.omitMask
>>j
)&1 ){
1256 disableTerm(pLevel
, pTerm
);
1257 }else if( (pTerm
->eOperator
& WO_IN
)!=0 ){
1258 Expr
*pCompare
; /* The comparison operator */
1259 Expr
*pRight
; /* RHS of the comparison */
1260 VdbeOp
*pOp
; /* Opcode to access the value of the IN constraint */
1262 /* Reload the constraint value into reg[iReg+j+2]. The same value
1263 ** was loaded into the same register prior to the OP_VFilter, but
1264 ** the xFilter implementation might have changed the datatype or
1265 ** encoding of the value in the register, so it *must* be reloaded. */
1266 assert( pLevel
->u
.in
.aInLoop
!=0 || db
->mallocFailed
);
1267 if( !db
->mallocFailed
){
1269 pOp
= sqlite3VdbeGetOp(v
, pLevel
->u
.in
.aInLoop
[--iIn
].addrInTop
);
1270 assert( pOp
->opcode
==OP_Column
|| pOp
->opcode
==OP_Rowid
);
1271 assert( pOp
->opcode
!=OP_Column
|| pOp
->p3
==iReg
+j
+2 );
1272 assert( pOp
->opcode
!=OP_Rowid
|| pOp
->p2
==iReg
+j
+2 );
1273 testcase( pOp
->opcode
==OP_Rowid
);
1274 sqlite3VdbeAddOp3(v
, pOp
->opcode
, pOp
->p1
, pOp
->p2
, pOp
->p3
);
1277 /* Generate code that will continue to the next row if
1278 ** the IN constraint is not satisfied */
1279 pCompare
= sqlite3PExpr(pParse
, TK_EQ
, 0, 0);
1280 assert( pCompare
!=0 || db
->mallocFailed
);
1282 pCompare
->pLeft
= pTerm
->pExpr
->pLeft
;
1283 pCompare
->pRight
= pRight
= sqlite3Expr(db
, TK_REGISTER
, 0);
1285 pRight
->iTable
= iReg
+j
+2;
1286 sqlite3ExprIfFalse(pParse
, pCompare
, pLevel
->addrCont
, 0);
1288 pCompare
->pLeft
= 0;
1289 sqlite3ExprDelete(db
, pCompare
);
1293 /* These registers need to be preserved in case there is an IN operator
1294 ** loop. So we could deallocate the registers here (and potentially
1295 ** reuse them later) if (pLoop->wsFlags & WHERE_IN_ABLE)==0. But it seems
1296 ** simpler and safer to simply not reuse the registers.
1298 ** sqlite3ReleaseTempRange(pParse, iReg, nConstraint+2);
1300 sqlite3ExprCachePop(pParse
);
1302 #endif /* SQLITE_OMIT_VIRTUALTABLE */
1304 if( (pLoop
->wsFlags
& WHERE_IPK
)!=0
1305 && (pLoop
->wsFlags
& (WHERE_COLUMN_IN
|WHERE_COLUMN_EQ
))!=0
1307 /* Case 2: We can directly reference a single row using an
1308 ** equality comparison against the ROWID field. Or
1309 ** we reference multiple rows using a "rowid IN (...)"
1312 assert( pLoop
->u
.btree
.nEq
==1 );
1313 pTerm
= pLoop
->aLTerm
[0];
1315 assert( pTerm
->pExpr
!=0 );
1316 assert( omitTable
==0 );
1317 testcase( pTerm
->wtFlags
& TERM_VIRTUAL
);
1318 iReleaseReg
= ++pParse
->nMem
;
1319 iRowidReg
= codeEqualityTerm(pParse
, pTerm
, pLevel
, 0, bRev
, iReleaseReg
);
1320 if( iRowidReg
!=iReleaseReg
) sqlite3ReleaseTempReg(pParse
, iReleaseReg
);
1321 addrNxt
= pLevel
->addrNxt
;
1322 sqlite3VdbeAddOp3(v
, OP_SeekRowid
, iCur
, addrNxt
, iRowidReg
);
1324 sqlite3ExprCacheAffinityChange(pParse
, iRowidReg
, 1);
1325 sqlite3ExprCacheStore(pParse
, iCur
, -1, iRowidReg
);
1326 VdbeComment((v
, "pk"));
1327 pLevel
->op
= OP_Noop
;
1328 }else if( (pLoop
->wsFlags
& WHERE_IPK
)!=0
1329 && (pLoop
->wsFlags
& WHERE_COLUMN_RANGE
)!=0
1331 /* Case 3: We have an inequality comparison against the ROWID field.
1333 int testOp
= OP_Noop
;
1335 int memEndValue
= 0;
1336 WhereTerm
*pStart
, *pEnd
;
1338 assert( omitTable
==0 );
1341 if( pLoop
->wsFlags
& WHERE_BTM_LIMIT
) pStart
= pLoop
->aLTerm
[j
++];
1342 if( pLoop
->wsFlags
& WHERE_TOP_LIMIT
) pEnd
= pLoop
->aLTerm
[j
++];
1343 assert( pStart
!=0 || pEnd
!=0 );
1349 codeCursorHint(pTabItem
, pWInfo
, pLevel
, pEnd
);
1351 Expr
*pX
; /* The expression that defines the start bound */
1352 int r1
, rTemp
; /* Registers for holding the start boundary */
1353 int op
; /* Cursor seek operation */
1355 /* The following constant maps TK_xx codes into corresponding
1356 ** seek opcodes. It depends on a particular ordering of TK_xx
1358 const u8 aMoveOp
[] = {
1359 /* TK_GT */ OP_SeekGT
,
1360 /* TK_LE */ OP_SeekLE
,
1361 /* TK_LT */ OP_SeekLT
,
1362 /* TK_GE */ OP_SeekGE
1364 assert( TK_LE
==TK_GT
+1 ); /* Make sure the ordering.. */
1365 assert( TK_LT
==TK_GT
+2 ); /* ... of the TK_xx values... */
1366 assert( TK_GE
==TK_GT
+3 ); /* ... is correcct. */
1368 assert( (pStart
->wtFlags
& TERM_VNULL
)==0 );
1369 testcase( pStart
->wtFlags
& TERM_VIRTUAL
);
1372 testcase( pStart
->leftCursor
!=iCur
); /* transitive constraints */
1373 if( sqlite3ExprIsVector(pX
->pRight
) ){
1374 r1
= rTemp
= sqlite3GetTempReg(pParse
);
1375 codeExprOrVector(pParse
, pX
->pRight
, r1
, 1);
1376 op
= aMoveOp
[(pX
->op
- TK_GT
) | 0x0001];
1378 r1
= sqlite3ExprCodeTemp(pParse
, pX
->pRight
, &rTemp
);
1379 disableTerm(pLevel
, pStart
);
1380 op
= aMoveOp
[(pX
->op
- TK_GT
)];
1382 sqlite3VdbeAddOp3(v
, op
, iCur
, addrBrk
, r1
);
1383 VdbeComment((v
, "pk"));
1384 VdbeCoverageIf(v
, pX
->op
==TK_GT
);
1385 VdbeCoverageIf(v
, pX
->op
==TK_LE
);
1386 VdbeCoverageIf(v
, pX
->op
==TK_LT
);
1387 VdbeCoverageIf(v
, pX
->op
==TK_GE
);
1388 sqlite3ExprCacheAffinityChange(pParse
, r1
, 1);
1389 sqlite3ReleaseTempReg(pParse
, rTemp
);
1391 sqlite3VdbeAddOp2(v
, bRev
? OP_Last
: OP_Rewind
, iCur
, addrHalt
);
1392 VdbeCoverageIf(v
, bRev
==0);
1393 VdbeCoverageIf(v
, bRev
!=0);
1399 assert( (pEnd
->wtFlags
& TERM_VNULL
)==0 );
1400 testcase( pEnd
->leftCursor
!=iCur
); /* Transitive constraints */
1401 testcase( pEnd
->wtFlags
& TERM_VIRTUAL
);
1402 memEndValue
= ++pParse
->nMem
;
1403 codeExprOrVector(pParse
, pX
->pRight
, memEndValue
, 1);
1404 if( 0==sqlite3ExprIsVector(pX
->pRight
)
1405 && (pX
->op
==TK_LT
|| pX
->op
==TK_GT
)
1407 testOp
= bRev
? OP_Le
: OP_Ge
;
1409 testOp
= bRev
? OP_Lt
: OP_Gt
;
1411 if( 0==sqlite3ExprIsVector(pX
->pRight
) ){
1412 disableTerm(pLevel
, pEnd
);
1415 start
= sqlite3VdbeCurrentAddr(v
);
1416 pLevel
->op
= bRev
? OP_Prev
: OP_Next
;
1419 assert( pLevel
->p5
==0 );
1420 if( testOp
!=OP_Noop
){
1421 iRowidReg
= ++pParse
->nMem
;
1422 sqlite3VdbeAddOp2(v
, OP_Rowid
, iCur
, iRowidReg
);
1423 sqlite3ExprCacheStore(pParse
, iCur
, -1, iRowidReg
);
1424 sqlite3VdbeAddOp3(v
, testOp
, memEndValue
, addrBrk
, iRowidReg
);
1425 VdbeCoverageIf(v
, testOp
==OP_Le
);
1426 VdbeCoverageIf(v
, testOp
==OP_Lt
);
1427 VdbeCoverageIf(v
, testOp
==OP_Ge
);
1428 VdbeCoverageIf(v
, testOp
==OP_Gt
);
1429 sqlite3VdbeChangeP5(v
, SQLITE_AFF_NUMERIC
| SQLITE_JUMPIFNULL
);
1431 }else if( pLoop
->wsFlags
& WHERE_INDEXED
){
1432 /* Case 4: A scan using an index.
1434 ** The WHERE clause may contain zero or more equality
1435 ** terms ("==" or "IN" operators) that refer to the N
1436 ** left-most columns of the index. It may also contain
1437 ** inequality constraints (>, <, >= or <=) on the indexed
1438 ** column that immediately follows the N equalities. Only
1439 ** the right-most column can be an inequality - the rest must
1440 ** use the "==" and "IN" operators. For example, if the
1441 ** index is on (x,y,z), then the following clauses are all
1447 ** x=5 AND y>5 AND y<10
1448 ** x=5 AND y=5 AND z<=10
1450 ** The z<10 term of the following cannot be used, only
1455 ** N may be zero if there are inequality constraints.
1456 ** If there are no inequality constraints, then N is at
1459 ** This case is also used when there are no WHERE clause
1460 ** constraints but an index is selected anyway, in order
1461 ** to force the output order to conform to an ORDER BY.
1463 static const u8 aStartOp
[] = {
1466 OP_Rewind
, /* 2: (!start_constraints && startEq && !bRev) */
1467 OP_Last
, /* 3: (!start_constraints && startEq && bRev) */
1468 OP_SeekGT
, /* 4: (start_constraints && !startEq && !bRev) */
1469 OP_SeekLT
, /* 5: (start_constraints && !startEq && bRev) */
1470 OP_SeekGE
, /* 6: (start_constraints && startEq && !bRev) */
1471 OP_SeekLE
/* 7: (start_constraints && startEq && bRev) */
1473 static const u8 aEndOp
[] = {
1474 OP_IdxGE
, /* 0: (end_constraints && !bRev && !endEq) */
1475 OP_IdxGT
, /* 1: (end_constraints && !bRev && endEq) */
1476 OP_IdxLE
, /* 2: (end_constraints && bRev && !endEq) */
1477 OP_IdxLT
, /* 3: (end_constraints && bRev && endEq) */
1479 u16 nEq
= pLoop
->u
.btree
.nEq
; /* Number of == or IN terms */
1480 u16 nBtm
= pLoop
->u
.btree
.nBtm
; /* Length of BTM vector */
1481 u16 nTop
= pLoop
->u
.btree
.nTop
; /* Length of TOP vector */
1482 int regBase
; /* Base register holding constraint values */
1483 WhereTerm
*pRangeStart
= 0; /* Inequality constraint at range start */
1484 WhereTerm
*pRangeEnd
= 0; /* Inequality constraint at range end */
1485 int startEq
; /* True if range start uses ==, >= or <= */
1486 int endEq
; /* True if range end uses ==, >= or <= */
1487 int start_constraints
; /* Start of range is constrained */
1488 int nConstraint
; /* Number of constraint terms */
1489 int iIdxCur
; /* The VDBE cursor for the index */
1490 int nExtraReg
= 0; /* Number of extra registers needed */
1491 int op
; /* Instruction opcode */
1492 char *zStartAff
; /* Affinity for start of range constraint */
1493 char *zEndAff
= 0; /* Affinity for end of range constraint */
1494 u8 bSeekPastNull
= 0; /* True to seek past initial nulls */
1495 u8 bStopAtNull
= 0; /* Add condition to terminate at NULLs */
1497 pIdx
= pLoop
->u
.btree
.pIndex
;
1498 iIdxCur
= pLevel
->iIdxCur
;
1499 assert( nEq
>=pLoop
->nSkip
);
1501 /* If this loop satisfies a sort order (pOrderBy) request that
1502 ** was passed to this function to implement a "SELECT min(x) ..."
1503 ** query, then the caller will only allow the loop to run for
1504 ** a single iteration. This means that the first row returned
1505 ** should not have a NULL value stored in 'x'. If column 'x' is
1506 ** the first one after the nEq equality constraints in the index,
1507 ** this requires some special handling.
1509 assert( pWInfo
->pOrderBy
==0
1510 || pWInfo
->pOrderBy
->nExpr
==1
1511 || (pWInfo
->wctrlFlags
&WHERE_ORDERBY_MIN
)==0 );
1512 if( (pWInfo
->wctrlFlags
&WHERE_ORDERBY_MIN
)!=0
1514 && (pIdx
->nKeyCol
>nEq
)
1516 assert( pLoop
->nSkip
==0 );
1521 /* Find any inequality constraint terms for the start and end
1525 if( pLoop
->wsFlags
& WHERE_BTM_LIMIT
){
1526 pRangeStart
= pLoop
->aLTerm
[j
++];
1527 nExtraReg
= MAX(nExtraReg
, pLoop
->u
.btree
.nBtm
);
1528 /* Like optimization range constraints always occur in pairs */
1529 assert( (pRangeStart
->wtFlags
& TERM_LIKEOPT
)==0 ||
1530 (pLoop
->wsFlags
& WHERE_TOP_LIMIT
)!=0 );
1532 if( pLoop
->wsFlags
& WHERE_TOP_LIMIT
){
1533 pRangeEnd
= pLoop
->aLTerm
[j
++];
1534 nExtraReg
= MAX(nExtraReg
, pLoop
->u
.btree
.nTop
);
1535 #ifndef SQLITE_LIKE_DOESNT_MATCH_BLOBS
1536 if( (pRangeEnd
->wtFlags
& TERM_LIKEOPT
)!=0 ){
1537 assert( pRangeStart
!=0 ); /* LIKE opt constraints */
1538 assert( pRangeStart
->wtFlags
& TERM_LIKEOPT
); /* occur in pairs */
1539 pLevel
->iLikeRepCntr
= (u32
)++pParse
->nMem
;
1540 sqlite3VdbeAddOp2(v
, OP_Integer
, 1, (int)pLevel
->iLikeRepCntr
);
1541 VdbeComment((v
, "LIKE loop counter"));
1542 pLevel
->addrLikeRep
= sqlite3VdbeCurrentAddr(v
);
1543 /* iLikeRepCntr actually stores 2x the counter register number. The
1544 ** bottom bit indicates whether the search order is ASC or DESC. */
1546 testcase( pIdx
->aSortOrder
[nEq
]==SQLITE_SO_DESC
);
1547 assert( (bRev
& ~1)==0 );
1548 pLevel
->iLikeRepCntr
<<=1;
1549 pLevel
->iLikeRepCntr
|= bRev
^ (pIdx
->aSortOrder
[nEq
]==SQLITE_SO_DESC
);
1552 if( pRangeStart
==0 ){
1553 j
= pIdx
->aiColumn
[nEq
];
1554 if( (j
>=0 && pIdx
->pTable
->aCol
[j
].notNull
==0) || j
==XN_EXPR
){
1559 assert( pRangeEnd
==0 || (pRangeEnd
->wtFlags
& TERM_VNULL
)==0 );
1561 /* If we are doing a reverse order scan on an ascending index, or
1562 ** a forward order scan on a descending index, interchange the
1563 ** start and end terms (pRangeStart and pRangeEnd).
1565 if( (nEq
<pIdx
->nKeyCol
&& bRev
==(pIdx
->aSortOrder
[nEq
]==SQLITE_SO_ASC
))
1566 || (bRev
&& pIdx
->nKeyCol
==nEq
)
1568 SWAP(WhereTerm
*, pRangeEnd
, pRangeStart
);
1569 SWAP(u8
, bSeekPastNull
, bStopAtNull
);
1570 SWAP(u8
, nBtm
, nTop
);
1573 /* Generate code to evaluate all constraint terms using == or IN
1574 ** and store the values of those terms in an array of registers
1575 ** starting at regBase.
1577 codeCursorHint(pTabItem
, pWInfo
, pLevel
, pRangeEnd
);
1578 regBase
= codeAllEqualityTerms(pParse
,pLevel
,bRev
,nExtraReg
,&zStartAff
);
1579 assert( zStartAff
==0 || sqlite3Strlen30(zStartAff
)>=nEq
);
1580 if( zStartAff
&& nTop
){
1581 zEndAff
= sqlite3DbStrDup(db
, &zStartAff
[nEq
]);
1583 addrNxt
= pLevel
->addrNxt
;
1585 testcase( pRangeStart
&& (pRangeStart
->eOperator
& WO_LE
)!=0 );
1586 testcase( pRangeStart
&& (pRangeStart
->eOperator
& WO_GE
)!=0 );
1587 testcase( pRangeEnd
&& (pRangeEnd
->eOperator
& WO_LE
)!=0 );
1588 testcase( pRangeEnd
&& (pRangeEnd
->eOperator
& WO_GE
)!=0 );
1589 startEq
= !pRangeStart
|| pRangeStart
->eOperator
& (WO_LE
|WO_GE
);
1590 endEq
= !pRangeEnd
|| pRangeEnd
->eOperator
& (WO_LE
|WO_GE
);
1591 start_constraints
= pRangeStart
|| nEq
>0;
1593 /* Seek the index cursor to the start of the range. */
1596 Expr
*pRight
= pRangeStart
->pExpr
->pRight
;
1597 codeExprOrVector(pParse
, pRight
, regBase
+nEq
, nBtm
);
1598 whereLikeOptimizationStringFixup(v
, pLevel
, pRangeStart
);
1599 if( (pRangeStart
->wtFlags
& TERM_VNULL
)==0
1600 && sqlite3ExprCanBeNull(pRight
)
1602 sqlite3VdbeAddOp2(v
, OP_IsNull
, regBase
+nEq
, addrNxt
);
1606 updateRangeAffinityStr(pRight
, nBtm
, &zStartAff
[nEq
]);
1608 nConstraint
+= nBtm
;
1609 testcase( pRangeStart
->wtFlags
& TERM_VIRTUAL
);
1610 if( sqlite3ExprIsVector(pRight
)==0 ){
1611 disableTerm(pLevel
, pRangeStart
);
1616 }else if( bSeekPastNull
){
1617 sqlite3VdbeAddOp2(v
, OP_Null
, 0, regBase
+nEq
);
1620 start_constraints
= 1;
1622 codeApplyAffinity(pParse
, regBase
, nConstraint
- bSeekPastNull
, zStartAff
);
1623 if( pLoop
->nSkip
>0 && nConstraint
==pLoop
->nSkip
){
1624 /* The skip-scan logic inside the call to codeAllEqualityConstraints()
1625 ** above has already left the cursor sitting on the correct row,
1626 ** so no further seeking is needed */
1628 op
= aStartOp
[(start_constraints
<<2) + (startEq
<<1) + bRev
];
1630 sqlite3VdbeAddOp4Int(v
, op
, iIdxCur
, addrNxt
, regBase
, nConstraint
);
1632 VdbeCoverageIf(v
, op
==OP_Rewind
); testcase( op
==OP_Rewind
);
1633 VdbeCoverageIf(v
, op
==OP_Last
); testcase( op
==OP_Last
);
1634 VdbeCoverageIf(v
, op
==OP_SeekGT
); testcase( op
==OP_SeekGT
);
1635 VdbeCoverageIf(v
, op
==OP_SeekGE
); testcase( op
==OP_SeekGE
);
1636 VdbeCoverageIf(v
, op
==OP_SeekLE
); testcase( op
==OP_SeekLE
);
1637 VdbeCoverageIf(v
, op
==OP_SeekLT
); testcase( op
==OP_SeekLT
);
1640 /* Load the value for the inequality constraint at the end of the
1645 Expr
*pRight
= pRangeEnd
->pExpr
->pRight
;
1646 sqlite3ExprCacheRemove(pParse
, regBase
+nEq
, 1);
1647 codeExprOrVector(pParse
, pRight
, regBase
+nEq
, nTop
);
1648 whereLikeOptimizationStringFixup(v
, pLevel
, pRangeEnd
);
1649 if( (pRangeEnd
->wtFlags
& TERM_VNULL
)==0
1650 && sqlite3ExprCanBeNull(pRight
)
1652 sqlite3VdbeAddOp2(v
, OP_IsNull
, regBase
+nEq
, addrNxt
);
1656 updateRangeAffinityStr(pRight
, nTop
, zEndAff
);
1657 codeApplyAffinity(pParse
, regBase
+nEq
, nTop
, zEndAff
);
1659 assert( pParse
->db
->mallocFailed
);
1661 nConstraint
+= nTop
;
1662 testcase( pRangeEnd
->wtFlags
& TERM_VIRTUAL
);
1664 if( sqlite3ExprIsVector(pRight
)==0 ){
1665 disableTerm(pLevel
, pRangeEnd
);
1669 }else if( bStopAtNull
){
1670 sqlite3VdbeAddOp2(v
, OP_Null
, 0, regBase
+nEq
);
1674 sqlite3DbFree(db
, zStartAff
);
1675 sqlite3DbFree(db
, zEndAff
);
1677 /* Top of the loop body */
1678 pLevel
->p2
= sqlite3VdbeCurrentAddr(v
);
1680 /* Check if the index cursor is past the end of the range. */
1682 op
= aEndOp
[bRev
*2 + endEq
];
1683 sqlite3VdbeAddOp4Int(v
, op
, iIdxCur
, addrNxt
, regBase
, nConstraint
);
1684 testcase( op
==OP_IdxGT
); VdbeCoverageIf(v
, op
==OP_IdxGT
);
1685 testcase( op
==OP_IdxGE
); VdbeCoverageIf(v
, op
==OP_IdxGE
);
1686 testcase( op
==OP_IdxLT
); VdbeCoverageIf(v
, op
==OP_IdxLT
);
1687 testcase( op
==OP_IdxLE
); VdbeCoverageIf(v
, op
==OP_IdxLE
);
1690 /* Seek the table cursor, if required */
1692 /* pIdx is a covering index. No need to access the main table. */
1693 }else if( HasRowid(pIdx
->pTable
) ){
1694 if( (pWInfo
->wctrlFlags
& WHERE_SEEK_TABLE
) || (
1695 (pWInfo
->wctrlFlags
& WHERE_SEEK_UNIQ_TABLE
)
1696 && (pWInfo
->eOnePass
==ONEPASS_SINGLE
)
1698 iRowidReg
= ++pParse
->nMem
;
1699 sqlite3VdbeAddOp2(v
, OP_IdxRowid
, iIdxCur
, iRowidReg
);
1700 sqlite3ExprCacheStore(pParse
, iCur
, -1, iRowidReg
);
1701 sqlite3VdbeAddOp3(v
, OP_NotExists
, iCur
, 0, iRowidReg
);
1704 codeDeferredSeek(pWInfo
, pIdx
, iCur
, iIdxCur
);
1706 }else if( iCur
!=iIdxCur
){
1707 Index
*pPk
= sqlite3PrimaryKeyIndex(pIdx
->pTable
);
1708 iRowidReg
= sqlite3GetTempRange(pParse
, pPk
->nKeyCol
);
1709 for(j
=0; j
<pPk
->nKeyCol
; j
++){
1710 k
= sqlite3ColumnOfIndex(pIdx
, pPk
->aiColumn
[j
]);
1711 sqlite3VdbeAddOp3(v
, OP_Column
, iIdxCur
, k
, iRowidReg
+j
);
1713 sqlite3VdbeAddOp4Int(v
, OP_NotFound
, iCur
, addrCont
,
1714 iRowidReg
, pPk
->nKeyCol
); VdbeCoverage(v
);
1717 /* If pIdx is an index on one or more expressions, then look through
1718 ** all the expressions in pWInfo and try to transform matching expressions
1719 ** into reference to index columns.
1721 whereIndexExprTrans(pIdx
, iCur
, iIdxCur
, pWInfo
);
1724 /* Record the instruction used to terminate the loop. */
1725 if( pLoop
->wsFlags
& WHERE_ONEROW
){
1726 pLevel
->op
= OP_Noop
;
1728 pLevel
->op
= OP_Prev
;
1730 pLevel
->op
= OP_Next
;
1732 pLevel
->p1
= iIdxCur
;
1733 pLevel
->p3
= (pLoop
->wsFlags
&WHERE_UNQ_WANTED
)!=0 ? 1:0;
1734 if( (pLoop
->wsFlags
& WHERE_CONSTRAINT
)==0 ){
1735 pLevel
->p5
= SQLITE_STMTSTATUS_FULLSCAN_STEP
;
1737 assert( pLevel
->p5
==0 );
1739 if( omitTable
) pIdx
= 0;
1742 #ifndef SQLITE_OMIT_OR_OPTIMIZATION
1743 if( pLoop
->wsFlags
& WHERE_MULTI_OR
){
1744 /* Case 5: Two or more separately indexed terms connected by OR
1748 ** CREATE TABLE t1(a,b,c,d);
1749 ** CREATE INDEX i1 ON t1(a);
1750 ** CREATE INDEX i2 ON t1(b);
1751 ** CREATE INDEX i3 ON t1(c);
1753 ** SELECT * FROM t1 WHERE a=5 OR b=7 OR (c=11 AND d=13)
1755 ** In the example, there are three indexed terms connected by OR.
1756 ** The top of the loop looks like this:
1758 ** Null 1 # Zero the rowset in reg 1
1760 ** Then, for each indexed term, the following. The arguments to
1761 ** RowSetTest are such that the rowid of the current row is inserted
1762 ** into the RowSet. If it is already present, control skips the
1763 ** Gosub opcode and jumps straight to the code generated by WhereEnd().
1765 ** sqlite3WhereBegin(<term>)
1766 ** RowSetTest # Insert rowid into rowset
1768 ** sqlite3WhereEnd()
1770 ** Following the above, code to terminate the loop. Label A, the target
1771 ** of the Gosub above, jumps to the instruction right after the Goto.
1773 ** Null 1 # Zero the rowset in reg 1
1774 ** Goto B # The loop is finished.
1776 ** A: <loop body> # Return data, whatever.
1778 ** Return 2 # Jump back to the Gosub
1780 ** B: <after the loop>
1782 ** Added 2014-05-26: If the table is a WITHOUT ROWID table, then
1783 ** use an ephemeral index instead of a RowSet to record the primary
1784 ** keys of the rows we have already seen.
1787 WhereClause
*pOrWc
; /* The OR-clause broken out into subterms */
1788 SrcList
*pOrTab
; /* Shortened table list or OR-clause generation */
1789 Index
*pCov
= 0; /* Potential covering index (or NULL) */
1790 int iCovCur
= pParse
->nTab
++; /* Cursor used for index scans (if any) */
1792 int regReturn
= ++pParse
->nMem
; /* Register used with OP_Gosub */
1793 int regRowset
= 0; /* Register for RowSet object */
1794 int regRowid
= 0; /* Register holding rowid */
1795 int iLoopBody
= sqlite3VdbeMakeLabel(v
); /* Start of loop body */
1796 int iRetInit
; /* Address of regReturn init */
1797 int untestedTerms
= 0; /* Some terms not completely tested */
1798 int ii
; /* Loop counter */
1799 u16 wctrlFlags
; /* Flags for sub-WHERE clause */
1800 Expr
*pAndExpr
= 0; /* An ".. AND (...)" expression */
1801 Table
*pTab
= pTabItem
->pTab
;
1803 pTerm
= pLoop
->aLTerm
[0];
1805 assert( pTerm
->eOperator
& WO_OR
);
1806 assert( (pTerm
->wtFlags
& TERM_ORINFO
)!=0 );
1807 pOrWc
= &pTerm
->u
.pOrInfo
->wc
;
1808 pLevel
->op
= OP_Return
;
1809 pLevel
->p1
= regReturn
;
1811 /* Set up a new SrcList in pOrTab containing the table being scanned
1812 ** by this loop in the a[0] slot and all notReady tables in a[1..] slots.
1813 ** This becomes the SrcList in the recursive call to sqlite3WhereBegin().
1815 if( pWInfo
->nLevel
>1 ){
1816 int nNotReady
; /* The number of notReady tables */
1817 struct SrcList_item
*origSrc
; /* Original list of tables */
1818 nNotReady
= pWInfo
->nLevel
- iLevel
- 1;
1819 pOrTab
= sqlite3StackAllocRaw(db
,
1820 sizeof(*pOrTab
)+ nNotReady
*sizeof(pOrTab
->a
[0]));
1821 if( pOrTab
==0 ) return notReady
;
1822 pOrTab
->nAlloc
= (u8
)(nNotReady
+ 1);
1823 pOrTab
->nSrc
= pOrTab
->nAlloc
;
1824 memcpy(pOrTab
->a
, pTabItem
, sizeof(*pTabItem
));
1825 origSrc
= pWInfo
->pTabList
->a
;
1826 for(k
=1; k
<=nNotReady
; k
++){
1827 memcpy(&pOrTab
->a
[k
], &origSrc
[pLevel
[k
].iFrom
], sizeof(pOrTab
->a
[k
]));
1830 pOrTab
= pWInfo
->pTabList
;
1833 /* Initialize the rowset register to contain NULL. An SQL NULL is
1834 ** equivalent to an empty rowset. Or, create an ephemeral index
1835 ** capable of holding primary keys in the case of a WITHOUT ROWID.
1837 ** Also initialize regReturn to contain the address of the instruction
1838 ** immediately following the OP_Return at the bottom of the loop. This
1839 ** is required in a few obscure LEFT JOIN cases where control jumps
1840 ** over the top of the loop into the body of it. In this case the
1841 ** correct response for the end-of-loop code (the OP_Return) is to
1842 ** fall through to the next instruction, just as an OP_Next does if
1843 ** called on an uninitialized cursor.
1845 if( (pWInfo
->wctrlFlags
& WHERE_DUPLICATES_OK
)==0 ){
1846 if( HasRowid(pTab
) ){
1847 regRowset
= ++pParse
->nMem
;
1848 sqlite3VdbeAddOp2(v
, OP_Null
, 0, regRowset
);
1850 Index
*pPk
= sqlite3PrimaryKeyIndex(pTab
);
1851 regRowset
= pParse
->nTab
++;
1852 sqlite3VdbeAddOp2(v
, OP_OpenEphemeral
, regRowset
, pPk
->nKeyCol
);
1853 sqlite3VdbeSetP4KeyInfo(pParse
, pPk
);
1855 regRowid
= ++pParse
->nMem
;
1857 iRetInit
= sqlite3VdbeAddOp2(v
, OP_Integer
, 0, regReturn
);
1859 /* If the original WHERE clause is z of the form: (x1 OR x2 OR ...) AND y
1860 ** Then for every term xN, evaluate as the subexpression: xN AND z
1861 ** That way, terms in y that are factored into the disjunction will
1862 ** be picked up by the recursive calls to sqlite3WhereBegin() below.
1864 ** Actually, each subexpression is converted to "xN AND w" where w is
1865 ** the "interesting" terms of z - terms that did not originate in the
1866 ** ON or USING clause of a LEFT JOIN, and terms that are usable as
1869 ** This optimization also only applies if the (x1 OR x2 OR ...) term
1870 ** is not contained in the ON clause of a LEFT JOIN.
1871 ** See ticket http://www.sqlite.org/src/info/f2369304e4
1875 for(iTerm
=0; iTerm
<pWC
->nTerm
; iTerm
++){
1876 Expr
*pExpr
= pWC
->a
[iTerm
].pExpr
;
1877 if( &pWC
->a
[iTerm
] == pTerm
) continue;
1878 if( ExprHasProperty(pExpr
, EP_FromJoin
) ) continue;
1879 testcase( pWC
->a
[iTerm
].wtFlags
& TERM_VIRTUAL
);
1880 testcase( pWC
->a
[iTerm
].wtFlags
& TERM_CODED
);
1881 if( (pWC
->a
[iTerm
].wtFlags
& (TERM_VIRTUAL
|TERM_CODED
))!=0 ) continue;
1882 if( (pWC
->a
[iTerm
].eOperator
& WO_ALL
)==0 ) continue;
1883 testcase( pWC
->a
[iTerm
].wtFlags
& TERM_ORINFO
);
1884 pExpr
= sqlite3ExprDup(db
, pExpr
, 0);
1885 pAndExpr
= sqlite3ExprAnd(db
, pAndExpr
, pExpr
);
1888 pAndExpr
= sqlite3PExpr(pParse
, TK_AND
|TKFLG_DONTFOLD
, 0, pAndExpr
);
1892 /* Run a separate WHERE clause for each term of the OR clause. After
1893 ** eliminating duplicates from other WHERE clauses, the action for each
1894 ** sub-WHERE clause is to to invoke the main loop body as a subroutine.
1896 wctrlFlags
= WHERE_OR_SUBCLAUSE
| (pWInfo
->wctrlFlags
& WHERE_SEEK_TABLE
);
1897 for(ii
=0; ii
<pOrWc
->nTerm
; ii
++){
1898 WhereTerm
*pOrTerm
= &pOrWc
->a
[ii
];
1899 if( pOrTerm
->leftCursor
==iCur
|| (pOrTerm
->eOperator
& WO_AND
)!=0 ){
1900 WhereInfo
*pSubWInfo
; /* Info for single OR-term scan */
1901 Expr
*pOrExpr
= pOrTerm
->pExpr
; /* Current OR clause term */
1902 int jmp1
= 0; /* Address of jump operation */
1903 if( pAndExpr
&& !ExprHasProperty(pOrExpr
, EP_FromJoin
) ){
1904 pAndExpr
->pLeft
= pOrExpr
;
1907 /* Loop through table entries that match term pOrTerm. */
1908 WHERETRACE(0xffff, ("Subplan for OR-clause:\n"));
1909 pSubWInfo
= sqlite3WhereBegin(pParse
, pOrTab
, pOrExpr
, 0, 0,
1910 wctrlFlags
, iCovCur
);
1911 assert( pSubWInfo
|| pParse
->nErr
|| db
->mallocFailed
);
1913 WhereLoop
*pSubLoop
;
1914 int addrExplain
= sqlite3WhereExplainOneScan(
1915 pParse
, pOrTab
, &pSubWInfo
->a
[0], iLevel
, pLevel
->iFrom
, 0
1917 sqlite3WhereAddScanStatus(v
, pOrTab
, &pSubWInfo
->a
[0], addrExplain
);
1919 /* This is the sub-WHERE clause body. First skip over
1920 ** duplicate rows from prior sub-WHERE clauses, and record the
1921 ** rowid (or PRIMARY KEY) for the current row so that the same
1922 ** row will be skipped in subsequent sub-WHERE clauses.
1924 if( (pWInfo
->wctrlFlags
& WHERE_DUPLICATES_OK
)==0 ){
1926 int iSet
= ((ii
==pOrWc
->nTerm
-1)?-1:ii
);
1927 if( HasRowid(pTab
) ){
1928 r
= sqlite3ExprCodeGetColumn(pParse
, pTab
, -1, iCur
, regRowid
, 0);
1929 jmp1
= sqlite3VdbeAddOp4Int(v
, OP_RowSetTest
, regRowset
, 0,
1933 Index
*pPk
= sqlite3PrimaryKeyIndex(pTab
);
1934 int nPk
= pPk
->nKeyCol
;
1937 /* Read the PK into an array of temp registers. */
1938 r
= sqlite3GetTempRange(pParse
, nPk
);
1939 for(iPk
=0; iPk
<nPk
; iPk
++){
1940 int iCol
= pPk
->aiColumn
[iPk
];
1941 sqlite3ExprCodeGetColumnToReg(pParse
, pTab
, iCol
, iCur
, r
+iPk
);
1944 /* Check if the temp table already contains this key. If so,
1945 ** the row has already been included in the result set and
1946 ** can be ignored (by jumping past the Gosub below). Otherwise,
1947 ** insert the key into the temp table and proceed with processing
1950 ** Use some of the same optimizations as OP_RowSetTest: If iSet
1951 ** is zero, assume that the key cannot already be present in
1952 ** the temp table. And if iSet is -1, assume that there is no
1953 ** need to insert the key into the temp table, as it will never
1954 ** be tested for. */
1956 jmp1
= sqlite3VdbeAddOp4Int(v
, OP_Found
, regRowset
, 0, r
, nPk
);
1960 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, r
, nPk
, regRowid
);
1961 sqlite3VdbeAddOp4Int(v
, OP_IdxInsert
, regRowset
, regRowid
,
1963 if( iSet
) sqlite3VdbeChangeP5(v
, OPFLAG_USESEEKRESULT
);
1966 /* Release the array of temp registers */
1967 sqlite3ReleaseTempRange(pParse
, r
, nPk
);
1971 /* Invoke the main loop body as a subroutine */
1972 sqlite3VdbeAddOp2(v
, OP_Gosub
, regReturn
, iLoopBody
);
1974 /* Jump here (skipping the main loop body subroutine) if the
1975 ** current sub-WHERE row is a duplicate from prior sub-WHEREs. */
1976 if( jmp1
) sqlite3VdbeJumpHere(v
, jmp1
);
1978 /* The pSubWInfo->untestedTerms flag means that this OR term
1979 ** contained one or more AND term from a notReady table. The
1980 ** terms from the notReady table could not be tested and will
1981 ** need to be tested later.
1983 if( pSubWInfo
->untestedTerms
) untestedTerms
= 1;
1985 /* If all of the OR-connected terms are optimized using the same
1986 ** index, and the index is opened using the same cursor number
1987 ** by each call to sqlite3WhereBegin() made by this loop, it may
1988 ** be possible to use that index as a covering index.
1990 ** If the call to sqlite3WhereBegin() above resulted in a scan that
1991 ** uses an index, and this is either the first OR-connected term
1992 ** processed or the index is the same as that used by all previous
1993 ** terms, set pCov to the candidate covering index. Otherwise, set
1994 ** pCov to NULL to indicate that no candidate covering index will
1997 pSubLoop
= pSubWInfo
->a
[0].pWLoop
;
1998 assert( (pSubLoop
->wsFlags
& WHERE_AUTO_INDEX
)==0 );
1999 if( (pSubLoop
->wsFlags
& WHERE_INDEXED
)!=0
2000 && (ii
==0 || pSubLoop
->u
.btree
.pIndex
==pCov
)
2001 && (HasRowid(pTab
) || !IsPrimaryKeyIndex(pSubLoop
->u
.btree
.pIndex
))
2003 assert( pSubWInfo
->a
[0].iIdxCur
==iCovCur
);
2004 pCov
= pSubLoop
->u
.btree
.pIndex
;
2009 /* Finish the loop through table entries that match term pOrTerm. */
2010 sqlite3WhereEnd(pSubWInfo
);
2014 pLevel
->u
.pCovidx
= pCov
;
2015 if( pCov
) pLevel
->iIdxCur
= iCovCur
;
2017 pAndExpr
->pLeft
= 0;
2018 sqlite3ExprDelete(db
, pAndExpr
);
2020 sqlite3VdbeChangeP1(v
, iRetInit
, sqlite3VdbeCurrentAddr(v
));
2021 sqlite3VdbeGoto(v
, pLevel
->addrBrk
);
2022 sqlite3VdbeResolveLabel(v
, iLoopBody
);
2024 if( pWInfo
->nLevel
>1 ) sqlite3StackFree(db
, pOrTab
);
2025 if( !untestedTerms
) disableTerm(pLevel
, pTerm
);
2027 #endif /* SQLITE_OMIT_OR_OPTIMIZATION */
2030 /* Case 6: There is no usable index. We must do a complete
2031 ** scan of the entire table.
2033 static const u8 aStep
[] = { OP_Next
, OP_Prev
};
2034 static const u8 aStart
[] = { OP_Rewind
, OP_Last
};
2035 assert( bRev
==0 || bRev
==1 );
2036 if( pTabItem
->fg
.isRecursive
){
2037 /* Tables marked isRecursive have only a single row that is stored in
2038 ** a pseudo-cursor. No need to Rewind or Next such cursors. */
2039 pLevel
->op
= OP_Noop
;
2041 codeCursorHint(pTabItem
, pWInfo
, pLevel
, 0);
2042 pLevel
->op
= aStep
[bRev
];
2044 pLevel
->p2
= 1 + sqlite3VdbeAddOp2(v
, aStart
[bRev
], iCur
, addrHalt
);
2045 VdbeCoverageIf(v
, bRev
==0);
2046 VdbeCoverageIf(v
, bRev
!=0);
2047 pLevel
->p5
= SQLITE_STMTSTATUS_FULLSCAN_STEP
;
2051 #ifdef SQLITE_ENABLE_STMT_SCANSTATUS
2052 pLevel
->addrVisit
= sqlite3VdbeCurrentAddr(v
);
2055 /* Insert code to test every subexpression that can be completely
2056 ** computed using the current set of tables.
2058 ** This loop may run between one and three times, depending on the
2059 ** constraints to be generated. The value of stack variable iLoop
2060 ** determines the constraints coded by each iteration, as follows:
2062 ** iLoop==1: Code only expressions that are entirely covered by pIdx.
2063 ** iLoop==2: Code remaining expressions that do not contain correlated
2065 ** iLoop==3: Code all remaining expressions.
2067 ** An effort is made to skip unnecessary iterations of the loop.
2069 iLoop
= (pIdx
? 1 : 2);
2071 int iNext
= 0; /* Next value for iLoop */
2072 for(pTerm
=pWC
->a
, j
=pWC
->nTerm
; j
>0; j
--, pTerm
++){
2074 int skipLikeAddr
= 0;
2075 testcase( pTerm
->wtFlags
& TERM_VIRTUAL
);
2076 testcase( pTerm
->wtFlags
& TERM_CODED
);
2077 if( pTerm
->wtFlags
& (TERM_VIRTUAL
|TERM_CODED
) ) continue;
2078 if( (pTerm
->prereqAll
& pLevel
->notReady
)!=0 ){
2079 testcase( pWInfo
->untestedTerms
==0
2080 && (pWInfo
->wctrlFlags
& WHERE_OR_SUBCLAUSE
)!=0 );
2081 pWInfo
->untestedTerms
= 1;
2086 if( pLevel
->iLeftJoin
&& !ExprHasProperty(pE
, EP_FromJoin
) ){
2090 if( iLoop
==1 && !sqlite3ExprCoveredByIndex(pE
, pLevel
->iTabCur
, pIdx
) ){
2094 if( iLoop
<3 && (pTerm
->wtFlags
& TERM_VARSELECT
) ){
2095 if( iNext
==0 ) iNext
= 3;
2099 if( pTerm
->wtFlags
& TERM_LIKECOND
){
2100 /* If the TERM_LIKECOND flag is set, that means that the range search
2101 ** is sufficient to guarantee that the LIKE operator is true, so we
2102 ** can skip the call to the like(A,B) function. But this only works
2103 ** for strings. So do not skip the call to the function on the pass
2104 ** that compares BLOBs. */
2105 #ifdef SQLITE_LIKE_DOESNT_MATCH_BLOBS
2108 u32 x
= pLevel
->iLikeRepCntr
;
2110 skipLikeAddr
= sqlite3VdbeAddOp1(v
, (x
&1)?OP_IfNot
:OP_If
, (int)(x
>>1));
2114 #ifdef WHERETRACE_ENABLED /* 0xffff */
2115 if( sqlite3WhereTrace
){
2116 VdbeNoopComment((v
, "WhereTerm[%d] (%p) priority=%d",
2117 pWC
->nTerm
-j
, pTerm
, iLoop
));
2120 sqlite3ExprIfFalse(pParse
, pE
, addrCont
, SQLITE_JUMPIFNULL
);
2121 if( skipLikeAddr
) sqlite3VdbeJumpHere(v
, skipLikeAddr
);
2122 pTerm
->wtFlags
|= TERM_CODED
;
2127 /* Insert code to test for implied constraints based on transitivity
2128 ** of the "==" operator.
2130 ** Example: If the WHERE clause contains "t1.a=t2.b" and "t2.b=123"
2131 ** and we are coding the t1 loop and the t2 loop has not yet coded,
2132 ** then we cannot use the "t1.a=t2.b" constraint, but we can code
2133 ** the implied "t1.a=123" constraint.
2135 for(pTerm
=pWC
->a
, j
=pWC
->nTerm
; j
>0; j
--, pTerm
++){
2138 if( pTerm
->wtFlags
& (TERM_VIRTUAL
|TERM_CODED
) ) continue;
2139 if( (pTerm
->eOperator
& (WO_EQ
|WO_IS
))==0 ) continue;
2140 if( (pTerm
->eOperator
& WO_EQUIV
)==0 ) continue;
2141 if( pTerm
->leftCursor
!=iCur
) continue;
2142 if( pLevel
->iLeftJoin
) continue;
2144 assert( !ExprHasProperty(pE
, EP_FromJoin
) );
2145 assert( (pTerm
->prereqRight
& pLevel
->notReady
)!=0 );
2146 pAlt
= sqlite3WhereFindTerm(pWC
, iCur
, pTerm
->u
.leftColumn
, notReady
,
2147 WO_EQ
|WO_IN
|WO_IS
, 0);
2148 if( pAlt
==0 ) continue;
2149 if( pAlt
->wtFlags
& (TERM_CODED
) ) continue;
2150 testcase( pAlt
->eOperator
& WO_EQ
);
2151 testcase( pAlt
->eOperator
& WO_IS
);
2152 testcase( pAlt
->eOperator
& WO_IN
);
2153 VdbeModuleComment((v
, "begin transitive constraint"));
2154 sEAlt
= *pAlt
->pExpr
;
2155 sEAlt
.pLeft
= pE
->pLeft
;
2156 sqlite3ExprIfFalse(pParse
, &sEAlt
, addrCont
, SQLITE_JUMPIFNULL
);
2159 /* For a LEFT OUTER JOIN, generate code that will record the fact that
2160 ** at least one row of the right table has matched the left table.
2162 if( pLevel
->iLeftJoin
){
2163 pLevel
->addrFirst
= sqlite3VdbeCurrentAddr(v
);
2164 sqlite3VdbeAddOp2(v
, OP_Integer
, 1, pLevel
->iLeftJoin
);
2165 VdbeComment((v
, "record LEFT JOIN hit"));
2166 sqlite3ExprCacheClear(pParse
);
2167 for(pTerm
=pWC
->a
, j
=0; j
<pWC
->nTerm
; j
++, pTerm
++){
2168 testcase( pTerm
->wtFlags
& TERM_VIRTUAL
);
2169 testcase( pTerm
->wtFlags
& TERM_CODED
);
2170 if( pTerm
->wtFlags
& (TERM_VIRTUAL
|TERM_CODED
) ) continue;
2171 if( (pTerm
->prereqAll
& pLevel
->notReady
)!=0 ){
2172 assert( pWInfo
->untestedTerms
);
2175 assert( pTerm
->pExpr
);
2176 sqlite3ExprIfFalse(pParse
, pTerm
->pExpr
, addrCont
, SQLITE_JUMPIFNULL
);
2177 pTerm
->wtFlags
|= TERM_CODED
;
2181 return pLevel
->notReady
;