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
){
297 while( ALWAYS(pTerm
!=0)
298 && (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
];
310 if( pTerm
->nChild
!=0 ) break;
316 ** Code an OP_Affinity opcode to apply the column affinity string zAff
317 ** to the n registers starting at base.
319 ** As an optimization, SQLITE_AFF_BLOB entries (which are no-ops) at the
320 ** beginning and end of zAff are ignored. If all entries in zAff are
321 ** SQLITE_AFF_BLOB, then no code gets generated.
323 ** This routine makes its own copy of zAff so that the caller is free
324 ** to modify zAff after this routine returns.
326 static void codeApplyAffinity(Parse
*pParse
, int base
, int n
, char *zAff
){
327 Vdbe
*v
= pParse
->pVdbe
;
329 assert( pParse
->db
->mallocFailed
);
334 /* Adjust base and n to skip over SQLITE_AFF_BLOB entries at the beginning
335 ** and end of the affinity string.
337 while( n
>0 && zAff
[0]==SQLITE_AFF_BLOB
){
342 while( n
>1 && zAff
[n
-1]==SQLITE_AFF_BLOB
){
346 /* Code the OP_Affinity opcode if there is anything left to do. */
348 sqlite3VdbeAddOp4(v
, OP_Affinity
, base
, n
, 0, zAff
, n
);
349 sqlite3ExprCacheAffinityChange(pParse
, base
, n
);
354 ** Expression pRight, which is the RHS of a comparison operation, is
355 ** either a vector of n elements or, if n==1, a scalar expression.
356 ** Before the comparison operation, affinity zAff is to be applied
357 ** to the pRight values. This function modifies characters within the
358 ** affinity string to SQLITE_AFF_BLOB if either:
360 ** * the comparison will be performed with no affinity, or
361 ** * the affinity change in zAff is guaranteed not to change the value.
363 static void updateRangeAffinityStr(
364 Expr
*pRight
, /* RHS of comparison */
365 int n
, /* Number of vector elements in comparison */
366 char *zAff
/* Affinity string to modify */
370 Expr
*p
= sqlite3VectorFieldSubexpr(pRight
, i
);
371 if( sqlite3CompareAffinity(p
, zAff
[i
])==SQLITE_AFF_BLOB
372 || sqlite3ExprNeedsNoAffinityChange(p
, zAff
[i
])
374 zAff
[i
] = SQLITE_AFF_BLOB
;
380 ** Generate code for a single equality term of the WHERE clause. An equality
381 ** term can be either X=expr or X IN (...). pTerm is the term to be
384 ** The current value for the constraint is left in a register, the index
385 ** of which is returned. An attempt is made store the result in iTarget but
386 ** this is only guaranteed for TK_ISNULL and TK_IN constraints. If the
387 ** constraint is a TK_EQ or TK_IS, then the current value might be left in
388 ** some other register and it is the caller's responsibility to compensate.
390 ** For a constraint of the form X=expr, the expression is evaluated in
391 ** straight-line code. For constraints of the form X IN (...)
392 ** this routine sets up a loop that will iterate over all values of X.
394 static int codeEqualityTerm(
395 Parse
*pParse
, /* The parsing context */
396 WhereTerm
*pTerm
, /* The term of the WHERE clause to be coded */
397 WhereLevel
*pLevel
, /* The level of the FROM clause we are working on */
398 int iEq
, /* Index of the equality term within this level */
399 int bRev
, /* True for reverse-order IN operations */
400 int iTarget
/* Attempt to leave results in this register */
402 Expr
*pX
= pTerm
->pExpr
;
403 Vdbe
*v
= pParse
->pVdbe
;
404 int iReg
; /* Register holding results */
406 assert( pLevel
->pWLoop
->aLTerm
[iEq
]==pTerm
);
408 if( pX
->op
==TK_EQ
|| pX
->op
==TK_IS
){
409 iReg
= sqlite3ExprCodeTarget(pParse
, pX
->pRight
, iTarget
);
410 }else if( pX
->op
==TK_ISNULL
){
412 sqlite3VdbeAddOp2(v
, OP_Null
, 0, iReg
);
413 #ifndef SQLITE_OMIT_SUBQUERY
415 int eType
= IN_INDEX_NOOP
;
418 WhereLoop
*pLoop
= pLevel
->pWLoop
;
423 if( (pLoop
->wsFlags
& WHERE_VIRTUALTABLE
)==0
424 && pLoop
->u
.btree
.pIndex
!=0
425 && pLoop
->u
.btree
.pIndex
->aSortOrder
[iEq
]
431 assert( pX
->op
==TK_IN
);
434 for(i
=0; i
<iEq
; i
++){
435 if( pLoop
->aLTerm
[i
] && pLoop
->aLTerm
[i
]->pExpr
==pX
){
436 disableTerm(pLevel
, pTerm
);
440 for(i
=iEq
;i
<pLoop
->nLTerm
; i
++){
441 if( ALWAYS(pLoop
->aLTerm
[i
]) && pLoop
->aLTerm
[i
]->pExpr
==pX
) nEq
++;
444 if( (pX
->flags
& EP_xIsSelect
)==0 || pX
->x
.pSelect
->pEList
->nExpr
==1 ){
445 eType
= sqlite3FindInIndex(pParse
, pX
, IN_INDEX_LOOP
, 0, 0);
447 Select
*pSelect
= pX
->x
.pSelect
;
448 sqlite3
*db
= pParse
->db
;
449 u16 savedDbOptFlags
= db
->dbOptFlags
;
450 ExprList
*pOrigRhs
= pSelect
->pEList
;
451 ExprList
*pOrigLhs
= pX
->pLeft
->x
.pList
;
452 ExprList
*pRhs
= 0; /* New Select.pEList for RHS */
453 ExprList
*pLhs
= 0; /* New pX->pLeft vector */
455 for(i
=iEq
;i
<pLoop
->nLTerm
; i
++){
456 if( pLoop
->aLTerm
[i
]->pExpr
==pX
){
457 int iField
= pLoop
->aLTerm
[i
]->iField
- 1;
458 Expr
*pNewRhs
= sqlite3ExprDup(db
, pOrigRhs
->a
[iField
].pExpr
, 0);
459 Expr
*pNewLhs
= sqlite3ExprDup(db
, pOrigLhs
->a
[iField
].pExpr
, 0);
461 pRhs
= sqlite3ExprListAppend(pParse
, pRhs
, pNewRhs
);
462 pLhs
= sqlite3ExprListAppend(pParse
, pLhs
, pNewLhs
);
465 if( !db
->mallocFailed
){
466 Expr
*pLeft
= pX
->pLeft
;
468 if( pSelect
->pOrderBy
){
469 /* If the SELECT statement has an ORDER BY clause, zero the
470 ** iOrderByCol variables. These are set to non-zero when an
471 ** ORDER BY term exactly matches one of the terms of the
472 ** result-set. Since the result-set of the SELECT statement may
473 ** have been modified or reordered, these variables are no longer
474 ** set correctly. Since setting them is just an optimization,
475 ** it's easiest just to zero them here. */
476 ExprList
*pOrderBy
= pSelect
->pOrderBy
;
477 for(i
=0; i
<pOrderBy
->nExpr
; i
++){
478 pOrderBy
->a
[i
].u
.x
.iOrderByCol
= 0;
482 /* Take care here not to generate a TK_VECTOR containing only a
483 ** single value. Since the parser never creates such a vector, some
484 ** of the subroutines do not handle this case. */
485 if( pLhs
->nExpr
==1 ){
486 pX
->pLeft
= pLhs
->a
[0].pExpr
;
488 pLeft
->x
.pList
= pLhs
;
489 aiMap
= (int*)sqlite3DbMallocZero(pParse
->db
, sizeof(int) * nEq
);
490 testcase( aiMap
==0 );
492 pSelect
->pEList
= pRhs
;
493 db
->dbOptFlags
|= SQLITE_QueryFlattener
;
494 eType
= sqlite3FindInIndex(pParse
, pX
, IN_INDEX_LOOP
, 0, aiMap
);
495 db
->dbOptFlags
= savedDbOptFlags
;
496 testcase( aiMap
!=0 && aiMap
[0]!=0 );
497 pSelect
->pEList
= pOrigRhs
;
498 pLeft
->x
.pList
= pOrigLhs
;
501 sqlite3ExprListDelete(pParse
->db
, pLhs
);
502 sqlite3ExprListDelete(pParse
->db
, pRhs
);
505 if( eType
==IN_INDEX_INDEX_DESC
){
510 sqlite3VdbeAddOp2(v
, bRev
? OP_Last
: OP_Rewind
, iTab
, 0);
511 VdbeCoverageIf(v
, bRev
);
512 VdbeCoverageIf(v
, !bRev
);
513 assert( (pLoop
->wsFlags
& WHERE_MULTI_OR
)==0 );
515 pLoop
->wsFlags
|= WHERE_IN_ABLE
;
516 if( pLevel
->u
.in
.nIn
==0 ){
517 pLevel
->addrNxt
= sqlite3VdbeMakeLabel(v
);
520 i
= pLevel
->u
.in
.nIn
;
521 pLevel
->u
.in
.nIn
+= nEq
;
522 pLevel
->u
.in
.aInLoop
=
523 sqlite3DbReallocOrFree(pParse
->db
, pLevel
->u
.in
.aInLoop
,
524 sizeof(pLevel
->u
.in
.aInLoop
[0])*pLevel
->u
.in
.nIn
);
525 pIn
= pLevel
->u
.in
.aInLoop
;
527 int iMap
= 0; /* Index in aiMap[] */
529 for(i
=iEq
;i
<pLoop
->nLTerm
; i
++){
530 if( pLoop
->aLTerm
[i
]->pExpr
==pX
){
531 int iOut
= iReg
+ i
- iEq
;
532 if( eType
==IN_INDEX_ROWID
){
533 testcase( nEq
>1 ); /* Happens with a UNIQUE index on ROWID */
534 pIn
->addrInTop
= sqlite3VdbeAddOp2(v
, OP_Rowid
, iTab
, iOut
);
536 int iCol
= aiMap
? aiMap
[iMap
++] : 0;
537 pIn
->addrInTop
= sqlite3VdbeAddOp3(v
,OP_Column
,iTab
, iCol
, iOut
);
539 sqlite3VdbeAddOp1(v
, OP_IsNull
, iOut
); VdbeCoverage(v
);
542 pIn
->eEndLoopOp
= bRev
? OP_PrevIfOpen
: OP_NextIfOpen
;
544 pIn
->eEndLoopOp
= OP_Noop
;
550 pLevel
->u
.in
.nIn
= 0;
552 sqlite3DbFree(pParse
->db
, aiMap
);
555 disableTerm(pLevel
, pTerm
);
560 ** Generate code that will evaluate all == and IN constraints for an
563 ** For example, consider table t1(a,b,c,d,e,f) with index i1(a,b,c).
564 ** Suppose the WHERE clause is this: a==5 AND b IN (1,2,3) AND c>5 AND c<10
565 ** The index has as many as three equality constraints, but in this
566 ** example, the third "c" value is an inequality. So only two
567 ** constraints are coded. This routine will generate code to evaluate
568 ** a==5 and b IN (1,2,3). The current values for a and b will be stored
569 ** in consecutive registers and the index of the first register is returned.
571 ** In the example above nEq==2. But this subroutine works for any value
572 ** of nEq including 0. If nEq==0, this routine is nearly a no-op.
573 ** The only thing it does is allocate the pLevel->iMem memory cell and
574 ** compute the affinity string.
576 ** The nExtraReg parameter is 0 or 1. It is 0 if all WHERE clause constraints
577 ** are == or IN and are covered by the nEq. nExtraReg is 1 if there is
578 ** an inequality constraint (such as the "c>=5 AND c<10" in the example) that
579 ** occurs after the nEq quality constraints.
581 ** This routine allocates a range of nEq+nExtraReg memory cells and returns
582 ** the index of the first memory cell in that range. The code that
583 ** calls this routine will use that memory range to store keys for
584 ** start and termination conditions of the loop.
585 ** key value of the loop. If one or more IN operators appear, then
586 ** this routine allocates an additional nEq memory cells for internal
589 ** Before returning, *pzAff is set to point to a buffer containing a
590 ** copy of the column affinity string of the index allocated using
591 ** sqlite3DbMalloc(). Except, entries in the copy of the string associated
592 ** with equality constraints that use BLOB or NONE affinity are set to
593 ** SQLITE_AFF_BLOB. This is to deal with SQL such as the following:
595 ** CREATE TABLE t1(a TEXT PRIMARY KEY, b);
596 ** SELECT ... FROM t1 AS t2, t1 WHERE t1.a = t2.b;
598 ** In the example above, the index on t1(a) has TEXT affinity. But since
599 ** the right hand side of the equality constraint (t2.b) has BLOB/NONE affinity,
600 ** no conversion should be attempted before using a t2.b value as part of
601 ** a key to search the index. Hence the first byte in the returned affinity
602 ** string in this example would be set to SQLITE_AFF_BLOB.
604 static int codeAllEqualityTerms(
605 Parse
*pParse
, /* Parsing context */
606 WhereLevel
*pLevel
, /* Which nested loop of the FROM we are coding */
607 int bRev
, /* Reverse the order of IN operators */
608 int nExtraReg
, /* Number of extra registers to allocate */
609 char **pzAff
/* OUT: Set to point to affinity string */
611 u16 nEq
; /* The number of == or IN constraints to code */
612 u16 nSkip
; /* Number of left-most columns to skip */
613 Vdbe
*v
= pParse
->pVdbe
; /* The vm under construction */
614 Index
*pIdx
; /* The index being used for this loop */
615 WhereTerm
*pTerm
; /* A single constraint term */
616 WhereLoop
*pLoop
; /* The WhereLoop object */
617 int j
; /* Loop counter */
618 int regBase
; /* Base register */
619 int nReg
; /* Number of registers to allocate */
620 char *zAff
; /* Affinity string to return */
622 /* This module is only called on query plans that use an index. */
623 pLoop
= pLevel
->pWLoop
;
624 assert( (pLoop
->wsFlags
& WHERE_VIRTUALTABLE
)==0 );
625 nEq
= pLoop
->u
.btree
.nEq
;
626 nSkip
= pLoop
->nSkip
;
627 pIdx
= pLoop
->u
.btree
.pIndex
;
630 /* Figure out how many memory cells we will need then allocate them.
632 regBase
= pParse
->nMem
+ 1;
633 nReg
= pLoop
->u
.btree
.nEq
+ nExtraReg
;
634 pParse
->nMem
+= nReg
;
636 zAff
= sqlite3DbStrDup(pParse
->db
,sqlite3IndexAffinityStr(pParse
->db
,pIdx
));
637 assert( zAff
!=0 || pParse
->db
->mallocFailed
);
640 int iIdxCur
= pLevel
->iIdxCur
;
641 sqlite3VdbeAddOp1(v
, (bRev
?OP_Last
:OP_Rewind
), iIdxCur
);
642 VdbeCoverageIf(v
, bRev
==0);
643 VdbeCoverageIf(v
, bRev
!=0);
644 VdbeComment((v
, "begin skip-scan on %s", pIdx
->zName
));
645 j
= sqlite3VdbeAddOp0(v
, OP_Goto
);
646 pLevel
->addrSkip
= sqlite3VdbeAddOp4Int(v
, (bRev
?OP_SeekLT
:OP_SeekGT
),
647 iIdxCur
, 0, regBase
, nSkip
);
648 VdbeCoverageIf(v
, bRev
==0);
649 VdbeCoverageIf(v
, bRev
!=0);
650 sqlite3VdbeJumpHere(v
, j
);
651 for(j
=0; j
<nSkip
; j
++){
652 sqlite3VdbeAddOp3(v
, OP_Column
, iIdxCur
, j
, regBase
+j
);
653 testcase( pIdx
->aiColumn
[j
]==XN_EXPR
);
654 VdbeComment((v
, "%s", explainIndexColumnName(pIdx
, j
)));
658 /* Evaluate the equality constraints
660 assert( zAff
==0 || (int)strlen(zAff
)>=nEq
);
661 for(j
=nSkip
; j
<nEq
; j
++){
663 pTerm
= pLoop
->aLTerm
[j
];
665 /* The following testcase is true for indices with redundant columns.
666 ** Ex: CREATE INDEX i1 ON t1(a,b,a); SELECT * FROM t1 WHERE a=0 AND b=0; */
667 testcase( (pTerm
->wtFlags
& TERM_CODED
)!=0 );
668 testcase( pTerm
->wtFlags
& TERM_VIRTUAL
);
669 r1
= codeEqualityTerm(pParse
, pTerm
, pLevel
, j
, bRev
, regBase
+j
);
672 sqlite3ReleaseTempReg(pParse
, regBase
);
675 sqlite3VdbeAddOp2(v
, OP_SCopy
, r1
, regBase
+j
);
678 if( pTerm
->eOperator
& WO_IN
){
679 if( pTerm
->pExpr
->flags
& EP_xIsSelect
){
680 /* No affinity ever needs to be (or should be) applied to a value
681 ** from the RHS of an "? IN (SELECT ...)" expression. The
682 ** sqlite3FindInIndex() routine has already ensured that the
683 ** affinity of the comparison has been applied to the value. */
684 if( zAff
) zAff
[j
] = SQLITE_AFF_BLOB
;
686 }else if( (pTerm
->eOperator
& WO_ISNULL
)==0 ){
687 Expr
*pRight
= pTerm
->pExpr
->pRight
;
688 if( (pTerm
->wtFlags
& TERM_IS
)==0 && sqlite3ExprCanBeNull(pRight
) ){
689 sqlite3VdbeAddOp2(v
, OP_IsNull
, regBase
+j
, pLevel
->addrBrk
);
693 if( sqlite3CompareAffinity(pRight
, zAff
[j
])==SQLITE_AFF_BLOB
){
694 zAff
[j
] = SQLITE_AFF_BLOB
;
696 if( sqlite3ExprNeedsNoAffinityChange(pRight
, zAff
[j
]) ){
697 zAff
[j
] = SQLITE_AFF_BLOB
;
706 #ifndef SQLITE_LIKE_DOESNT_MATCH_BLOBS
708 ** If the most recently coded instruction is a constant range constraint
709 ** (a string literal) that originated from the LIKE optimization, then
710 ** set P3 and P5 on the OP_String opcode so that the string will be cast
711 ** to a BLOB at appropriate times.
713 ** The LIKE optimization trys to evaluate "x LIKE 'abc%'" as a range
714 ** expression: "x>='ABC' AND x<'abd'". But this requires that the range
715 ** scan loop run twice, once for strings and a second time for BLOBs.
716 ** The OP_String opcodes on the second pass convert the upper and lower
717 ** bound string constants to blobs. This routine makes the necessary changes
718 ** to the OP_String opcodes for that to happen.
720 ** Except, of course, if SQLITE_LIKE_DOESNT_MATCH_BLOBS is defined, then
721 ** only the one pass through the string space is required, so this routine
724 static void whereLikeOptimizationStringFixup(
725 Vdbe
*v
, /* prepared statement under construction */
726 WhereLevel
*pLevel
, /* The loop that contains the LIKE operator */
727 WhereTerm
*pTerm
/* The upper or lower bound just coded */
729 if( pTerm
->wtFlags
& TERM_LIKEOPT
){
731 assert( pLevel
->iLikeRepCntr
>0 );
732 pOp
= sqlite3VdbeGetOp(v
, -1);
734 assert( pOp
->opcode
==OP_String8
735 || pTerm
->pWC
->pWInfo
->pParse
->db
->mallocFailed
);
736 pOp
->p3
= (int)(pLevel
->iLikeRepCntr
>>1); /* Register holding counter */
737 pOp
->p5
= (u8
)(pLevel
->iLikeRepCntr
&1); /* ASC or DESC */
741 # define whereLikeOptimizationStringFixup(A,B,C)
744 #ifdef SQLITE_ENABLE_CURSOR_HINTS
746 ** Information is passed from codeCursorHint() down to individual nodes of
747 ** the expression tree (by sqlite3WalkExpr()) using an instance of this
751 int iTabCur
; /* Cursor for the main table */
752 int iIdxCur
; /* Cursor for the index, if pIdx!=0. Unused otherwise */
753 Index
*pIdx
; /* The index used to access the table */
757 ** This function is called for every node of an expression that is a candidate
758 ** for a cursor hint on an index cursor. For TK_COLUMN nodes that reference
759 ** the table CCurHint.iTabCur, verify that the same column can be
760 ** accessed through the index. If it cannot, then set pWalker->eCode to 1.
762 static int codeCursorHintCheckExpr(Walker
*pWalker
, Expr
*pExpr
){
763 struct CCurHint
*pHint
= pWalker
->u
.pCCurHint
;
764 assert( pHint
->pIdx
!=0 );
765 if( pExpr
->op
==TK_COLUMN
766 && pExpr
->iTable
==pHint
->iTabCur
767 && sqlite3ColumnOfIndex(pHint
->pIdx
, pExpr
->iColumn
)<0
775 ** Test whether or not expression pExpr, which was part of a WHERE clause,
776 ** should be included in the cursor-hint for a table that is on the rhs
777 ** of a LEFT JOIN. Set Walker.eCode to non-zero before returning if the
778 ** expression is not suitable.
780 ** An expression is unsuitable if it might evaluate to non NULL even if
781 ** a TK_COLUMN node that does affect the value of the expression is set
782 ** to NULL. For example:
787 ** CASE WHEN col THEN 0 ELSE 1 END
789 static int codeCursorHintIsOrFunction(Walker
*pWalker
, Expr
*pExpr
){
791 || pExpr
->op
==TK_ISNULL
|| pExpr
->op
==TK_ISNOT
792 || pExpr
->op
==TK_NOTNULL
|| pExpr
->op
==TK_CASE
795 }else if( pExpr
->op
==TK_FUNCTION
){
798 if( 0==sqlite3IsLikeFunction(pWalker
->pParse
->db
, pExpr
, &d1
, d2
) ){
808 ** This function is called on every node of an expression tree used as an
809 ** argument to the OP_CursorHint instruction. If the node is a TK_COLUMN
810 ** that accesses any table other than the one identified by
811 ** CCurHint.iTabCur, then do the following:
813 ** 1) allocate a register and code an OP_Column instruction to read
814 ** the specified column into the new register, and
816 ** 2) transform the expression node to a TK_REGISTER node that reads
817 ** from the newly populated register.
819 ** Also, if the node is a TK_COLUMN that does access the table idenified
820 ** by pCCurHint.iTabCur, and an index is being used (which we will
821 ** know because CCurHint.pIdx!=0) then transform the TK_COLUMN into
822 ** an access of the index rather than the original table.
824 static int codeCursorHintFixExpr(Walker
*pWalker
, Expr
*pExpr
){
825 int rc
= WRC_Continue
;
826 struct CCurHint
*pHint
= pWalker
->u
.pCCurHint
;
827 if( pExpr
->op
==TK_COLUMN
){
828 if( pExpr
->iTable
!=pHint
->iTabCur
){
829 Vdbe
*v
= pWalker
->pParse
->pVdbe
;
830 int reg
= ++pWalker
->pParse
->nMem
; /* Register for column value */
831 sqlite3ExprCodeGetColumnOfTable(
832 v
, pExpr
->pTab
, pExpr
->iTable
, pExpr
->iColumn
, reg
834 pExpr
->op
= TK_REGISTER
;
836 }else if( pHint
->pIdx
!=0 ){
837 pExpr
->iTable
= pHint
->iIdxCur
;
838 pExpr
->iColumn
= sqlite3ColumnOfIndex(pHint
->pIdx
, pExpr
->iColumn
);
839 assert( pExpr
->iColumn
>=0 );
841 }else if( pExpr
->op
==TK_AGG_FUNCTION
){
842 /* An aggregate function in the WHERE clause of a query means this must
843 ** be a correlated sub-query, and expression pExpr is an aggregate from
844 ** the parent context. Do not walk the function arguments in this case.
846 ** todo: It should be possible to replace this node with a TK_REGISTER
847 ** expression, as the result of the expression must be stored in a
848 ** register at this point. The same holds for TK_AGG_COLUMN nodes. */
855 ** Insert an OP_CursorHint instruction if it is appropriate to do so.
857 static void codeCursorHint(
858 struct SrcList_item
*pTabItem
, /* FROM clause item */
859 WhereInfo
*pWInfo
, /* The where clause */
860 WhereLevel
*pLevel
, /* Which loop to provide hints for */
861 WhereTerm
*pEndRange
/* Hint this end-of-scan boundary term if not NULL */
863 Parse
*pParse
= pWInfo
->pParse
;
864 sqlite3
*db
= pParse
->db
;
865 Vdbe
*v
= pParse
->pVdbe
;
867 WhereLoop
*pLoop
= pLevel
->pWLoop
;
872 struct CCurHint sHint
;
875 if( OptimizationDisabled(db
, SQLITE_CursorHints
) ) return;
876 iCur
= pLevel
->iTabCur
;
877 assert( iCur
==pWInfo
->pTabList
->a
[pLevel
->iFrom
].iCursor
);
878 sHint
.iTabCur
= iCur
;
879 sHint
.iIdxCur
= pLevel
->iIdxCur
;
880 sHint
.pIdx
= pLoop
->u
.btree
.pIndex
;
881 memset(&sWalker
, 0, sizeof(sWalker
));
882 sWalker
.pParse
= pParse
;
883 sWalker
.u
.pCCurHint
= &sHint
;
885 for(i
=0; i
<pWC
->nTerm
; i
++){
887 if( pTerm
->wtFlags
& (TERM_VIRTUAL
|TERM_CODED
) ) continue;
888 if( pTerm
->prereqAll
& pLevel
->notReady
) continue;
890 /* Any terms specified as part of the ON(...) clause for any LEFT
891 ** JOIN for which the current table is not the rhs are omitted
892 ** from the cursor-hint.
894 ** If this table is the rhs of a LEFT JOIN, "IS" or "IS NULL" terms
895 ** that were specified as part of the WHERE clause must be excluded.
896 ** This is to address the following:
898 ** SELECT ... t1 LEFT JOIN t2 ON (t1.a=t2.b) WHERE t2.c IS NULL;
900 ** Say there is a single row in t2 that matches (t1.a=t2.b), but its
901 ** t2.c values is not NULL. If the (t2.c IS NULL) constraint is
902 ** pushed down to the cursor, this row is filtered out, causing
903 ** SQLite to synthesize a row of NULL values. Which does match the
904 ** WHERE clause, and so the query returns a row. Which is incorrect.
906 ** For the same reason, WHERE terms such as:
908 ** WHERE 1 = (t2.c IS NULL)
910 ** are also excluded. See codeCursorHintIsOrFunction() for details.
912 if( pTabItem
->fg
.jointype
& JT_LEFT
){
913 Expr
*pExpr
= pTerm
->pExpr
;
914 if( !ExprHasProperty(pExpr
, EP_FromJoin
)
915 || pExpr
->iRightJoinTable
!=pTabItem
->iCursor
918 sWalker
.xExprCallback
= codeCursorHintIsOrFunction
;
919 sqlite3WalkExpr(&sWalker
, pTerm
->pExpr
);
920 if( sWalker
.eCode
) continue;
923 if( ExprHasProperty(pTerm
->pExpr
, EP_FromJoin
) ) continue;
926 /* All terms in pWLoop->aLTerm[] except pEndRange are used to initialize
927 ** the cursor. These terms are not needed as hints for a pure range
928 ** scan (that has no == terms) so omit them. */
929 if( pLoop
->u
.btree
.nEq
==0 && pTerm
!=pEndRange
){
930 for(j
=0; j
<pLoop
->nLTerm
&& pLoop
->aLTerm
[j
]!=pTerm
; j
++){}
931 if( j
<pLoop
->nLTerm
) continue;
934 /* No subqueries or non-deterministic functions allowed */
935 if( sqlite3ExprContainsSubquery(pTerm
->pExpr
) ) continue;
937 /* For an index scan, make sure referenced columns are actually in
941 sWalker
.xExprCallback
= codeCursorHintCheckExpr
;
942 sqlite3WalkExpr(&sWalker
, pTerm
->pExpr
);
943 if( sWalker
.eCode
) continue;
946 /* If we survive all prior tests, that means this term is worth hinting */
947 pExpr
= sqlite3ExprAnd(db
, pExpr
, sqlite3ExprDup(db
, pTerm
->pExpr
, 0));
950 sWalker
.xExprCallback
= codeCursorHintFixExpr
;
951 sqlite3WalkExpr(&sWalker
, pExpr
);
952 sqlite3VdbeAddOp4(v
, OP_CursorHint
,
953 (sHint
.pIdx
? sHint
.iIdxCur
: sHint
.iTabCur
), 0, 0,
954 (const char*)pExpr
, P4_EXPR
);
958 # define codeCursorHint(A,B,C,D) /* No-op */
959 #endif /* SQLITE_ENABLE_CURSOR_HINTS */
962 ** Cursor iCur is open on an intkey b-tree (a table). Register iRowid contains
963 ** a rowid value just read from cursor iIdxCur, open on index pIdx. This
964 ** function generates code to do a deferred seek of cursor iCur to the
965 ** rowid stored in register iRowid.
967 ** Normally, this is just:
969 ** OP_DeferredSeek $iCur $iRowid
971 ** However, if the scan currently being coded is a branch of an OR-loop and
972 ** the statement currently being coded is a SELECT, then P3 of OP_DeferredSeek
973 ** is set to iIdxCur and P4 is set to point to an array of integers
974 ** containing one entry for each column of the table cursor iCur is open
975 ** on. For each table column, if the column is the i'th column of the
976 ** index, then the corresponding array entry is set to (i+1). If the column
977 ** does not appear in the index at all, the array entry is set to 0.
979 static void codeDeferredSeek(
980 WhereInfo
*pWInfo
, /* Where clause context */
981 Index
*pIdx
, /* Index scan is using */
982 int iCur
, /* Cursor for IPK b-tree */
983 int iIdxCur
/* Index cursor */
985 Parse
*pParse
= pWInfo
->pParse
; /* Parse context */
986 Vdbe
*v
= pParse
->pVdbe
; /* Vdbe to generate code within */
989 assert( pIdx
->aiColumn
[pIdx
->nColumn
-1]==-1 );
991 sqlite3VdbeAddOp3(v
, OP_DeferredSeek
, iIdxCur
, 0, iCur
);
992 if( (pWInfo
->wctrlFlags
& WHERE_OR_SUBCLAUSE
)
993 && DbMaskAllZero(sqlite3ParseToplevel(pParse
)->writeMask
)
996 Table
*pTab
= pIdx
->pTable
;
997 int *ai
= (int*)sqlite3DbMallocZero(pParse
->db
, sizeof(int)*(pTab
->nCol
+1));
1000 for(i
=0; i
<pIdx
->nColumn
-1; i
++){
1001 assert( pIdx
->aiColumn
[i
]<pTab
->nCol
);
1002 if( pIdx
->aiColumn
[i
]>=0 ) ai
[pIdx
->aiColumn
[i
]+1] = i
+1;
1004 sqlite3VdbeChangeP4(v
, -1, (char*)ai
, P4_INTARRAY
);
1010 ** If the expression passed as the second argument is a vector, generate
1011 ** code to write the first nReg elements of the vector into an array
1012 ** of registers starting with iReg.
1014 ** If the expression is not a vector, then nReg must be passed 1. In
1015 ** this case, generate code to evaluate the expression and leave the
1016 ** result in register iReg.
1018 static void codeExprOrVector(Parse
*pParse
, Expr
*p
, int iReg
, int nReg
){
1020 if( sqlite3ExprIsVector(p
) ){
1021 #ifndef SQLITE_OMIT_SUBQUERY
1022 if( (p
->flags
& EP_xIsSelect
) ){
1023 Vdbe
*v
= pParse
->pVdbe
;
1024 int iSelect
= sqlite3CodeSubselect(pParse
, p
, 0, 0);
1025 sqlite3VdbeAddOp3(v
, OP_Copy
, iSelect
, iReg
, nReg
-1);
1030 ExprList
*pList
= p
->x
.pList
;
1031 assert( nReg
<=pList
->nExpr
);
1032 for(i
=0; i
<nReg
; i
++){
1033 sqlite3ExprCode(pParse
, pList
->a
[i
].pExpr
, iReg
+i
);
1038 sqlite3ExprCode(pParse
, p
, iReg
);
1042 /* An instance of the IdxExprTrans object carries information about a
1043 ** mapping from an expression on table columns into a column in an index
1044 ** down through the Walker.
1046 typedef struct IdxExprTrans
{
1047 Expr
*pIdxExpr
; /* The index expression */
1048 int iTabCur
; /* The cursor of the corresponding table */
1049 int iIdxCur
; /* The cursor for the index */
1050 int iIdxCol
; /* The column for the index */
1053 /* The walker node callback used to transform matching expressions into
1054 ** a reference to an index column for an index on an expression.
1056 ** If pExpr matches, then transform it into a reference to the index column
1057 ** that contains the value of pExpr.
1059 static int whereIndexExprTransNode(Walker
*p
, Expr
*pExpr
){
1060 IdxExprTrans
*pX
= p
->u
.pIdxTrans
;
1061 if( sqlite3ExprCompare(0, pExpr
, pX
->pIdxExpr
, pX
->iTabCur
)==0 ){
1062 pExpr
->op
= TK_COLUMN
;
1063 pExpr
->iTable
= pX
->iIdxCur
;
1064 pExpr
->iColumn
= pX
->iIdxCol
;
1068 return WRC_Continue
;
1073 ** For an indexes on expression X, locate every instance of expression X in pExpr
1074 ** and change that subexpression into a reference to the appropriate column of
1077 static void whereIndexExprTrans(
1078 Index
*pIdx
, /* The Index */
1079 int iTabCur
, /* Cursor of the table that is being indexed */
1080 int iIdxCur
, /* Cursor of the index itself */
1081 WhereInfo
*pWInfo
/* Transform expressions in this WHERE clause */
1083 int iIdxCol
; /* Column number of the index */
1084 ExprList
*aColExpr
; /* Expressions that are indexed */
1087 aColExpr
= pIdx
->aColExpr
;
1088 if( aColExpr
==0 ) return; /* Not an index on expressions */
1089 memset(&w
, 0, sizeof(w
));
1090 w
.xExprCallback
= whereIndexExprTransNode
;
1092 x
.iTabCur
= iTabCur
;
1093 x
.iIdxCur
= iIdxCur
;
1094 for(iIdxCol
=0; iIdxCol
<aColExpr
->nExpr
; iIdxCol
++){
1095 if( pIdx
->aiColumn
[iIdxCol
]!=XN_EXPR
) continue;
1096 assert( aColExpr
->a
[iIdxCol
].pExpr
!=0 );
1097 x
.iIdxCol
= iIdxCol
;
1098 x
.pIdxExpr
= aColExpr
->a
[iIdxCol
].pExpr
;
1099 sqlite3WalkExpr(&w
, pWInfo
->pWhere
);
1100 sqlite3WalkExprList(&w
, pWInfo
->pOrderBy
);
1101 sqlite3WalkExprList(&w
, pWInfo
->pResultSet
);
1106 ** Generate code for the start of the iLevel-th loop in the WHERE clause
1107 ** implementation described by pWInfo.
1109 Bitmask
sqlite3WhereCodeOneLoopStart(
1110 WhereInfo
*pWInfo
, /* Complete information about the WHERE clause */
1111 int iLevel
, /* Which level of pWInfo->a[] should be coded */
1112 Bitmask notReady
/* Which tables are currently available */
1114 int j
, k
; /* Loop counters */
1115 int iCur
; /* The VDBE cursor for the table */
1116 int addrNxt
; /* Where to jump to continue with the next IN case */
1117 int omitTable
; /* True if we use the index only */
1118 int bRev
; /* True if we need to scan in reverse order */
1119 WhereLevel
*pLevel
; /* The where level to be coded */
1120 WhereLoop
*pLoop
; /* The WhereLoop object being coded */
1121 WhereClause
*pWC
; /* Decomposition of the entire WHERE clause */
1122 WhereTerm
*pTerm
; /* A WHERE clause term */
1123 Parse
*pParse
; /* Parsing context */
1124 sqlite3
*db
; /* Database connection */
1125 Vdbe
*v
; /* The prepared stmt under constructions */
1126 struct SrcList_item
*pTabItem
; /* FROM clause term being coded */
1127 int addrBrk
; /* Jump here to break out of the loop */
1128 int addrHalt
; /* addrBrk for the outermost loop */
1129 int addrCont
; /* Jump here to continue with next cycle */
1130 int iRowidReg
= 0; /* Rowid is stored in this register, if not zero */
1131 int iReleaseReg
= 0; /* Temp register to free before returning */
1132 Index
*pIdx
= 0; /* Index used by loop (if any) */
1133 int iLoop
; /* Iteration of constraint generator loop */
1135 pParse
= pWInfo
->pParse
;
1139 pLevel
= &pWInfo
->a
[iLevel
];
1140 pLoop
= pLevel
->pWLoop
;
1141 pTabItem
= &pWInfo
->pTabList
->a
[pLevel
->iFrom
];
1142 iCur
= pTabItem
->iCursor
;
1143 pLevel
->notReady
= notReady
& ~sqlite3WhereGetMask(&pWInfo
->sMaskSet
, iCur
);
1144 bRev
= (pWInfo
->revMask
>>iLevel
)&1;
1145 omitTable
= (pLoop
->wsFlags
& WHERE_IDX_ONLY
)!=0
1146 && (pWInfo
->wctrlFlags
& WHERE_OR_SUBCLAUSE
)==0;
1147 VdbeModuleComment((v
, "Begin WHERE-loop%d: %s",iLevel
,pTabItem
->pTab
->zName
));
1149 /* Create labels for the "break" and "continue" instructions
1150 ** for the current loop. Jump to addrBrk to break out of a loop.
1151 ** Jump to cont to go immediately to the next iteration of the
1154 ** When there is an IN operator, we also have a "addrNxt" label that
1155 ** means to continue with the next IN value combination. When
1156 ** there are no IN operators in the constraints, the "addrNxt" label
1157 ** is the same as "addrBrk".
1159 addrBrk
= pLevel
->addrBrk
= pLevel
->addrNxt
= sqlite3VdbeMakeLabel(v
);
1160 addrCont
= pLevel
->addrCont
= sqlite3VdbeMakeLabel(v
);
1162 /* If this is the right table of a LEFT OUTER JOIN, allocate and
1163 ** initialize a memory cell that records if this table matches any
1164 ** row of the left table of the join.
1166 if( pLevel
->iFrom
>0 && (pTabItem
[0].fg
.jointype
& JT_LEFT
)!=0 ){
1167 pLevel
->iLeftJoin
= ++pParse
->nMem
;
1168 sqlite3VdbeAddOp2(v
, OP_Integer
, 0, pLevel
->iLeftJoin
);
1169 VdbeComment((v
, "init LEFT JOIN no-match flag"));
1172 /* Compute a safe address to jump to if we discover that the table for
1173 ** this loop is empty and can never contribute content. */
1174 for(j
=iLevel
; j
>0 && pWInfo
->a
[j
].iLeftJoin
==0; j
--){}
1175 addrHalt
= pWInfo
->a
[j
].addrBrk
;
1177 /* Special case of a FROM clause subquery implemented as a co-routine */
1178 if( pTabItem
->fg
.viaCoroutine
){
1179 int regYield
= pTabItem
->regReturn
;
1180 sqlite3VdbeAddOp3(v
, OP_InitCoroutine
, regYield
, 0, pTabItem
->addrFillSub
);
1181 pLevel
->p2
= sqlite3VdbeAddOp2(v
, OP_Yield
, regYield
, addrBrk
);
1183 VdbeComment((v
, "next row of \"%s\"", pTabItem
->pTab
->zName
));
1184 pLevel
->op
= OP_Goto
;
1187 #ifndef SQLITE_OMIT_VIRTUALTABLE
1188 if( (pLoop
->wsFlags
& WHERE_VIRTUALTABLE
)!=0 ){
1189 /* Case 1: The table is a virtual-table. Use the VFilter and VNext
1190 ** to access the data.
1192 int iReg
; /* P3 Value for OP_VFilter */
1194 int nConstraint
= pLoop
->nLTerm
;
1195 int iIn
; /* Counter for IN constraints */
1197 sqlite3ExprCachePush(pParse
);
1198 iReg
= sqlite3GetTempRange(pParse
, nConstraint
+2);
1199 addrNotFound
= pLevel
->addrBrk
;
1200 for(j
=0; j
<nConstraint
; j
++){
1201 int iTarget
= iReg
+j
+2;
1202 pTerm
= pLoop
->aLTerm
[j
];
1203 if( NEVER(pTerm
==0) ) continue;
1204 if( pTerm
->eOperator
& WO_IN
){
1205 codeEqualityTerm(pParse
, pTerm
, pLevel
, j
, bRev
, iTarget
);
1206 addrNotFound
= pLevel
->addrNxt
;
1208 Expr
*pRight
= pTerm
->pExpr
->pRight
;
1209 codeExprOrVector(pParse
, pRight
, iTarget
, 1);
1212 sqlite3VdbeAddOp2(v
, OP_Integer
, pLoop
->u
.vtab
.idxNum
, iReg
);
1213 sqlite3VdbeAddOp2(v
, OP_Integer
, nConstraint
, iReg
+1);
1214 sqlite3VdbeAddOp4(v
, OP_VFilter
, iCur
, addrNotFound
, iReg
,
1215 pLoop
->u
.vtab
.idxStr
,
1216 pLoop
->u
.vtab
.needFree
? P4_DYNAMIC
: P4_STATIC
);
1218 pLoop
->u
.vtab
.needFree
= 0;
1220 pLevel
->op
= pWInfo
->eOnePass
? OP_Noop
: OP_VNext
;
1221 pLevel
->p2
= sqlite3VdbeCurrentAddr(v
);
1222 iIn
= pLevel
->u
.in
.nIn
;
1223 for(j
=nConstraint
-1; j
>=0; j
--){
1224 pTerm
= pLoop
->aLTerm
[j
];
1225 if( j
<16 && (pLoop
->u
.vtab
.omitMask
>>j
)&1 ){
1226 disableTerm(pLevel
, pTerm
);
1227 }else if( (pTerm
->eOperator
& WO_IN
)!=0 ){
1228 Expr
*pCompare
; /* The comparison operator */
1229 Expr
*pRight
; /* RHS of the comparison */
1230 VdbeOp
*pOp
; /* Opcode to access the value of the IN constraint */
1232 /* Reload the constraint value into reg[iReg+j+2]. The same value
1233 ** was loaded into the same register prior to the OP_VFilter, but
1234 ** the xFilter implementation might have changed the datatype or
1235 ** encoding of the value in the register, so it *must* be reloaded. */
1236 assert( pLevel
->u
.in
.aInLoop
!=0 || db
->mallocFailed
);
1237 if( !db
->mallocFailed
){
1239 pOp
= sqlite3VdbeGetOp(v
, pLevel
->u
.in
.aInLoop
[--iIn
].addrInTop
);
1240 assert( pOp
->opcode
==OP_Column
|| pOp
->opcode
==OP_Rowid
);
1241 assert( pOp
->opcode
!=OP_Column
|| pOp
->p3
==iReg
+j
+2 );
1242 assert( pOp
->opcode
!=OP_Rowid
|| pOp
->p2
==iReg
+j
+2 );
1243 testcase( pOp
->opcode
==OP_Rowid
);
1244 sqlite3VdbeAddOp3(v
, pOp
->opcode
, pOp
->p1
, pOp
->p2
, pOp
->p3
);
1247 /* Generate code that will continue to the next row if
1248 ** the IN constraint is not satisfied */
1249 pCompare
= sqlite3PExpr(pParse
, TK_EQ
, 0, 0);
1250 assert( pCompare
!=0 || db
->mallocFailed
);
1252 pCompare
->pLeft
= pTerm
->pExpr
->pLeft
;
1253 pCompare
->pRight
= pRight
= sqlite3Expr(db
, TK_REGISTER
, 0);
1255 pRight
->iTable
= iReg
+j
+2;
1256 sqlite3ExprIfFalse(pParse
, pCompare
, pLevel
->addrCont
, 0);
1258 pCompare
->pLeft
= 0;
1259 sqlite3ExprDelete(db
, pCompare
);
1263 /* These registers need to be preserved in case there is an IN operator
1264 ** loop. So we could deallocate the registers here (and potentially
1265 ** reuse them later) if (pLoop->wsFlags & WHERE_IN_ABLE)==0. But it seems
1266 ** simpler and safer to simply not reuse the registers.
1268 ** sqlite3ReleaseTempRange(pParse, iReg, nConstraint+2);
1270 sqlite3ExprCachePop(pParse
);
1272 #endif /* SQLITE_OMIT_VIRTUALTABLE */
1274 if( (pLoop
->wsFlags
& WHERE_IPK
)!=0
1275 && (pLoop
->wsFlags
& (WHERE_COLUMN_IN
|WHERE_COLUMN_EQ
))!=0
1277 /* Case 2: We can directly reference a single row using an
1278 ** equality comparison against the ROWID field. Or
1279 ** we reference multiple rows using a "rowid IN (...)"
1282 assert( pLoop
->u
.btree
.nEq
==1 );
1283 pTerm
= pLoop
->aLTerm
[0];
1285 assert( pTerm
->pExpr
!=0 );
1286 assert( omitTable
==0 );
1287 testcase( pTerm
->wtFlags
& TERM_VIRTUAL
);
1288 iReleaseReg
= ++pParse
->nMem
;
1289 iRowidReg
= codeEqualityTerm(pParse
, pTerm
, pLevel
, 0, bRev
, iReleaseReg
);
1290 if( iRowidReg
!=iReleaseReg
) sqlite3ReleaseTempReg(pParse
, iReleaseReg
);
1291 addrNxt
= pLevel
->addrNxt
;
1292 sqlite3VdbeAddOp3(v
, OP_SeekRowid
, iCur
, addrNxt
, iRowidReg
);
1294 sqlite3ExprCacheAffinityChange(pParse
, iRowidReg
, 1);
1295 sqlite3ExprCacheStore(pParse
, iCur
, -1, iRowidReg
);
1296 VdbeComment((v
, "pk"));
1297 pLevel
->op
= OP_Noop
;
1298 }else if( (pLoop
->wsFlags
& WHERE_IPK
)!=0
1299 && (pLoop
->wsFlags
& WHERE_COLUMN_RANGE
)!=0
1301 /* Case 3: We have an inequality comparison against the ROWID field.
1303 int testOp
= OP_Noop
;
1305 int memEndValue
= 0;
1306 WhereTerm
*pStart
, *pEnd
;
1308 assert( omitTable
==0 );
1311 if( pLoop
->wsFlags
& WHERE_BTM_LIMIT
) pStart
= pLoop
->aLTerm
[j
++];
1312 if( pLoop
->wsFlags
& WHERE_TOP_LIMIT
) pEnd
= pLoop
->aLTerm
[j
++];
1313 assert( pStart
!=0 || pEnd
!=0 );
1319 codeCursorHint(pTabItem
, pWInfo
, pLevel
, pEnd
);
1321 Expr
*pX
; /* The expression that defines the start bound */
1322 int r1
, rTemp
; /* Registers for holding the start boundary */
1323 int op
; /* Cursor seek operation */
1325 /* The following constant maps TK_xx codes into corresponding
1326 ** seek opcodes. It depends on a particular ordering of TK_xx
1328 const u8 aMoveOp
[] = {
1329 /* TK_GT */ OP_SeekGT
,
1330 /* TK_LE */ OP_SeekLE
,
1331 /* TK_LT */ OP_SeekLT
,
1332 /* TK_GE */ OP_SeekGE
1334 assert( TK_LE
==TK_GT
+1 ); /* Make sure the ordering.. */
1335 assert( TK_LT
==TK_GT
+2 ); /* ... of the TK_xx values... */
1336 assert( TK_GE
==TK_GT
+3 ); /* ... is correcct. */
1338 assert( (pStart
->wtFlags
& TERM_VNULL
)==0 );
1339 testcase( pStart
->wtFlags
& TERM_VIRTUAL
);
1342 testcase( pStart
->leftCursor
!=iCur
); /* transitive constraints */
1343 if( sqlite3ExprIsVector(pX
->pRight
) ){
1344 r1
= rTemp
= sqlite3GetTempReg(pParse
);
1345 codeExprOrVector(pParse
, pX
->pRight
, r1
, 1);
1346 op
= aMoveOp
[(pX
->op
- TK_GT
) | 0x0001];
1348 r1
= sqlite3ExprCodeTemp(pParse
, pX
->pRight
, &rTemp
);
1349 disableTerm(pLevel
, pStart
);
1350 op
= aMoveOp
[(pX
->op
- TK_GT
)];
1352 sqlite3VdbeAddOp3(v
, op
, iCur
, addrBrk
, r1
);
1353 VdbeComment((v
, "pk"));
1354 VdbeCoverageIf(v
, pX
->op
==TK_GT
);
1355 VdbeCoverageIf(v
, pX
->op
==TK_LE
);
1356 VdbeCoverageIf(v
, pX
->op
==TK_LT
);
1357 VdbeCoverageIf(v
, pX
->op
==TK_GE
);
1358 sqlite3ExprCacheAffinityChange(pParse
, r1
, 1);
1359 sqlite3ReleaseTempReg(pParse
, rTemp
);
1361 sqlite3VdbeAddOp2(v
, bRev
? OP_Last
: OP_Rewind
, iCur
, addrHalt
);
1362 VdbeCoverageIf(v
, bRev
==0);
1363 VdbeCoverageIf(v
, bRev
!=0);
1369 assert( (pEnd
->wtFlags
& TERM_VNULL
)==0 );
1370 testcase( pEnd
->leftCursor
!=iCur
); /* Transitive constraints */
1371 testcase( pEnd
->wtFlags
& TERM_VIRTUAL
);
1372 memEndValue
= ++pParse
->nMem
;
1373 codeExprOrVector(pParse
, pX
->pRight
, memEndValue
, 1);
1374 if( 0==sqlite3ExprIsVector(pX
->pRight
)
1375 && (pX
->op
==TK_LT
|| pX
->op
==TK_GT
)
1377 testOp
= bRev
? OP_Le
: OP_Ge
;
1379 testOp
= bRev
? OP_Lt
: OP_Gt
;
1381 if( 0==sqlite3ExprIsVector(pX
->pRight
) ){
1382 disableTerm(pLevel
, pEnd
);
1385 start
= sqlite3VdbeCurrentAddr(v
);
1386 pLevel
->op
= bRev
? OP_Prev
: OP_Next
;
1389 assert( pLevel
->p5
==0 );
1390 if( testOp
!=OP_Noop
){
1391 iRowidReg
= ++pParse
->nMem
;
1392 sqlite3VdbeAddOp2(v
, OP_Rowid
, iCur
, iRowidReg
);
1393 sqlite3ExprCacheStore(pParse
, iCur
, -1, iRowidReg
);
1394 sqlite3VdbeAddOp3(v
, testOp
, memEndValue
, addrBrk
, iRowidReg
);
1395 VdbeCoverageIf(v
, testOp
==OP_Le
);
1396 VdbeCoverageIf(v
, testOp
==OP_Lt
);
1397 VdbeCoverageIf(v
, testOp
==OP_Ge
);
1398 VdbeCoverageIf(v
, testOp
==OP_Gt
);
1399 sqlite3VdbeChangeP5(v
, SQLITE_AFF_NUMERIC
| SQLITE_JUMPIFNULL
);
1401 }else if( pLoop
->wsFlags
& WHERE_INDEXED
){
1402 /* Case 4: A scan using an index.
1404 ** The WHERE clause may contain zero or more equality
1405 ** terms ("==" or "IN" operators) that refer to the N
1406 ** left-most columns of the index. It may also contain
1407 ** inequality constraints (>, <, >= or <=) on the indexed
1408 ** column that immediately follows the N equalities. Only
1409 ** the right-most column can be an inequality - the rest must
1410 ** use the "==" and "IN" operators. For example, if the
1411 ** index is on (x,y,z), then the following clauses are all
1417 ** x=5 AND y>5 AND y<10
1418 ** x=5 AND y=5 AND z<=10
1420 ** The z<10 term of the following cannot be used, only
1425 ** N may be zero if there are inequality constraints.
1426 ** If there are no inequality constraints, then N is at
1429 ** This case is also used when there are no WHERE clause
1430 ** constraints but an index is selected anyway, in order
1431 ** to force the output order to conform to an ORDER BY.
1433 static const u8 aStartOp
[] = {
1436 OP_Rewind
, /* 2: (!start_constraints && startEq && !bRev) */
1437 OP_Last
, /* 3: (!start_constraints && startEq && bRev) */
1438 OP_SeekGT
, /* 4: (start_constraints && !startEq && !bRev) */
1439 OP_SeekLT
, /* 5: (start_constraints && !startEq && bRev) */
1440 OP_SeekGE
, /* 6: (start_constraints && startEq && !bRev) */
1441 OP_SeekLE
/* 7: (start_constraints && startEq && bRev) */
1443 static const u8 aEndOp
[] = {
1444 OP_IdxGE
, /* 0: (end_constraints && !bRev && !endEq) */
1445 OP_IdxGT
, /* 1: (end_constraints && !bRev && endEq) */
1446 OP_IdxLE
, /* 2: (end_constraints && bRev && !endEq) */
1447 OP_IdxLT
, /* 3: (end_constraints && bRev && endEq) */
1449 u16 nEq
= pLoop
->u
.btree
.nEq
; /* Number of == or IN terms */
1450 u16 nBtm
= pLoop
->u
.btree
.nBtm
; /* Length of BTM vector */
1451 u16 nTop
= pLoop
->u
.btree
.nTop
; /* Length of TOP vector */
1452 int regBase
; /* Base register holding constraint values */
1453 WhereTerm
*pRangeStart
= 0; /* Inequality constraint at range start */
1454 WhereTerm
*pRangeEnd
= 0; /* Inequality constraint at range end */
1455 int startEq
; /* True if range start uses ==, >= or <= */
1456 int endEq
; /* True if range end uses ==, >= or <= */
1457 int start_constraints
; /* Start of range is constrained */
1458 int nConstraint
; /* Number of constraint terms */
1459 int iIdxCur
; /* The VDBE cursor for the index */
1460 int nExtraReg
= 0; /* Number of extra registers needed */
1461 int op
; /* Instruction opcode */
1462 char *zStartAff
; /* Affinity for start of range constraint */
1463 char *zEndAff
= 0; /* Affinity for end of range constraint */
1464 u8 bSeekPastNull
= 0; /* True to seek past initial nulls */
1465 u8 bStopAtNull
= 0; /* Add condition to terminate at NULLs */
1467 pIdx
= pLoop
->u
.btree
.pIndex
;
1468 iIdxCur
= pLevel
->iIdxCur
;
1469 assert( nEq
>=pLoop
->nSkip
);
1471 /* If this loop satisfies a sort order (pOrderBy) request that
1472 ** was passed to this function to implement a "SELECT min(x) ..."
1473 ** query, then the caller will only allow the loop to run for
1474 ** a single iteration. This means that the first row returned
1475 ** should not have a NULL value stored in 'x'. If column 'x' is
1476 ** the first one after the nEq equality constraints in the index,
1477 ** this requires some special handling.
1479 assert( pWInfo
->pOrderBy
==0
1480 || pWInfo
->pOrderBy
->nExpr
==1
1481 || (pWInfo
->wctrlFlags
&WHERE_ORDERBY_MIN
)==0 );
1482 if( (pWInfo
->wctrlFlags
&WHERE_ORDERBY_MIN
)!=0
1484 && (pIdx
->nKeyCol
>nEq
)
1486 assert( pLoop
->nSkip
==0 );
1491 /* Find any inequality constraint terms for the start and end
1495 if( pLoop
->wsFlags
& WHERE_BTM_LIMIT
){
1496 pRangeStart
= pLoop
->aLTerm
[j
++];
1497 nExtraReg
= MAX(nExtraReg
, pLoop
->u
.btree
.nBtm
);
1498 /* Like optimization range constraints always occur in pairs */
1499 assert( (pRangeStart
->wtFlags
& TERM_LIKEOPT
)==0 ||
1500 (pLoop
->wsFlags
& WHERE_TOP_LIMIT
)!=0 );
1502 if( pLoop
->wsFlags
& WHERE_TOP_LIMIT
){
1503 pRangeEnd
= pLoop
->aLTerm
[j
++];
1504 nExtraReg
= MAX(nExtraReg
, pLoop
->u
.btree
.nTop
);
1505 #ifndef SQLITE_LIKE_DOESNT_MATCH_BLOBS
1506 if( (pRangeEnd
->wtFlags
& TERM_LIKEOPT
)!=0 ){
1507 assert( pRangeStart
!=0 ); /* LIKE opt constraints */
1508 assert( pRangeStart
->wtFlags
& TERM_LIKEOPT
); /* occur in pairs */
1509 pLevel
->iLikeRepCntr
= (u32
)++pParse
->nMem
;
1510 sqlite3VdbeAddOp2(v
, OP_Integer
, 1, (int)pLevel
->iLikeRepCntr
);
1511 VdbeComment((v
, "LIKE loop counter"));
1512 pLevel
->addrLikeRep
= sqlite3VdbeCurrentAddr(v
);
1513 /* iLikeRepCntr actually stores 2x the counter register number. The
1514 ** bottom bit indicates whether the search order is ASC or DESC. */
1516 testcase( pIdx
->aSortOrder
[nEq
]==SQLITE_SO_DESC
);
1517 assert( (bRev
& ~1)==0 );
1518 pLevel
->iLikeRepCntr
<<=1;
1519 pLevel
->iLikeRepCntr
|= bRev
^ (pIdx
->aSortOrder
[nEq
]==SQLITE_SO_DESC
);
1522 if( pRangeStart
==0 ){
1523 j
= pIdx
->aiColumn
[nEq
];
1524 if( (j
>=0 && pIdx
->pTable
->aCol
[j
].notNull
==0) || j
==XN_EXPR
){
1529 assert( pRangeEnd
==0 || (pRangeEnd
->wtFlags
& TERM_VNULL
)==0 );
1531 /* If we are doing a reverse order scan on an ascending index, or
1532 ** a forward order scan on a descending index, interchange the
1533 ** start and end terms (pRangeStart and pRangeEnd).
1535 if( (nEq
<pIdx
->nKeyCol
&& bRev
==(pIdx
->aSortOrder
[nEq
]==SQLITE_SO_ASC
))
1536 || (bRev
&& pIdx
->nKeyCol
==nEq
)
1538 SWAP(WhereTerm
*, pRangeEnd
, pRangeStart
);
1539 SWAP(u8
, bSeekPastNull
, bStopAtNull
);
1540 SWAP(u8
, nBtm
, nTop
);
1543 /* Generate code to evaluate all constraint terms using == or IN
1544 ** and store the values of those terms in an array of registers
1545 ** starting at regBase.
1547 codeCursorHint(pTabItem
, pWInfo
, pLevel
, pRangeEnd
);
1548 regBase
= codeAllEqualityTerms(pParse
,pLevel
,bRev
,nExtraReg
,&zStartAff
);
1549 assert( zStartAff
==0 || sqlite3Strlen30(zStartAff
)>=nEq
);
1550 if( zStartAff
&& nTop
){
1551 zEndAff
= sqlite3DbStrDup(db
, &zStartAff
[nEq
]);
1553 addrNxt
= pLevel
->addrNxt
;
1555 testcase( pRangeStart
&& (pRangeStart
->eOperator
& WO_LE
)!=0 );
1556 testcase( pRangeStart
&& (pRangeStart
->eOperator
& WO_GE
)!=0 );
1557 testcase( pRangeEnd
&& (pRangeEnd
->eOperator
& WO_LE
)!=0 );
1558 testcase( pRangeEnd
&& (pRangeEnd
->eOperator
& WO_GE
)!=0 );
1559 startEq
= !pRangeStart
|| pRangeStart
->eOperator
& (WO_LE
|WO_GE
);
1560 endEq
= !pRangeEnd
|| pRangeEnd
->eOperator
& (WO_LE
|WO_GE
);
1561 start_constraints
= pRangeStart
|| nEq
>0;
1563 /* Seek the index cursor to the start of the range. */
1566 Expr
*pRight
= pRangeStart
->pExpr
->pRight
;
1567 codeExprOrVector(pParse
, pRight
, regBase
+nEq
, nBtm
);
1568 whereLikeOptimizationStringFixup(v
, pLevel
, pRangeStart
);
1569 if( (pRangeStart
->wtFlags
& TERM_VNULL
)==0
1570 && sqlite3ExprCanBeNull(pRight
)
1572 sqlite3VdbeAddOp2(v
, OP_IsNull
, regBase
+nEq
, addrNxt
);
1576 updateRangeAffinityStr(pRight
, nBtm
, &zStartAff
[nEq
]);
1578 nConstraint
+= nBtm
;
1579 testcase( pRangeStart
->wtFlags
& TERM_VIRTUAL
);
1580 if( sqlite3ExprIsVector(pRight
)==0 ){
1581 disableTerm(pLevel
, pRangeStart
);
1586 }else if( bSeekPastNull
){
1587 sqlite3VdbeAddOp2(v
, OP_Null
, 0, regBase
+nEq
);
1590 start_constraints
= 1;
1592 codeApplyAffinity(pParse
, regBase
, nConstraint
- bSeekPastNull
, zStartAff
);
1593 if( pLoop
->nSkip
>0 && nConstraint
==pLoop
->nSkip
){
1594 /* The skip-scan logic inside the call to codeAllEqualityConstraints()
1595 ** above has already left the cursor sitting on the correct row,
1596 ** so no further seeking is needed */
1598 op
= aStartOp
[(start_constraints
<<2) + (startEq
<<1) + bRev
];
1600 sqlite3VdbeAddOp4Int(v
, op
, iIdxCur
, addrNxt
, regBase
, nConstraint
);
1602 VdbeCoverageIf(v
, op
==OP_Rewind
); testcase( op
==OP_Rewind
);
1603 VdbeCoverageIf(v
, op
==OP_Last
); testcase( op
==OP_Last
);
1604 VdbeCoverageIf(v
, op
==OP_SeekGT
); testcase( op
==OP_SeekGT
);
1605 VdbeCoverageIf(v
, op
==OP_SeekGE
); testcase( op
==OP_SeekGE
);
1606 VdbeCoverageIf(v
, op
==OP_SeekLE
); testcase( op
==OP_SeekLE
);
1607 VdbeCoverageIf(v
, op
==OP_SeekLT
); testcase( op
==OP_SeekLT
);
1610 /* Load the value for the inequality constraint at the end of the
1615 Expr
*pRight
= pRangeEnd
->pExpr
->pRight
;
1616 sqlite3ExprCacheRemove(pParse
, regBase
+nEq
, 1);
1617 codeExprOrVector(pParse
, pRight
, regBase
+nEq
, nTop
);
1618 whereLikeOptimizationStringFixup(v
, pLevel
, pRangeEnd
);
1619 if( (pRangeEnd
->wtFlags
& TERM_VNULL
)==0
1620 && sqlite3ExprCanBeNull(pRight
)
1622 sqlite3VdbeAddOp2(v
, OP_IsNull
, regBase
+nEq
, addrNxt
);
1626 updateRangeAffinityStr(pRight
, nTop
, zEndAff
);
1627 codeApplyAffinity(pParse
, regBase
+nEq
, nTop
, zEndAff
);
1629 assert( pParse
->db
->mallocFailed
);
1631 nConstraint
+= nTop
;
1632 testcase( pRangeEnd
->wtFlags
& TERM_VIRTUAL
);
1634 if( sqlite3ExprIsVector(pRight
)==0 ){
1635 disableTerm(pLevel
, pRangeEnd
);
1639 }else if( bStopAtNull
){
1640 sqlite3VdbeAddOp2(v
, OP_Null
, 0, regBase
+nEq
);
1644 sqlite3DbFree(db
, zStartAff
);
1645 sqlite3DbFree(db
, zEndAff
);
1647 /* Top of the loop body */
1648 pLevel
->p2
= sqlite3VdbeCurrentAddr(v
);
1650 /* Check if the index cursor is past the end of the range. */
1652 op
= aEndOp
[bRev
*2 + endEq
];
1653 sqlite3VdbeAddOp4Int(v
, op
, iIdxCur
, addrNxt
, regBase
, nConstraint
);
1654 testcase( op
==OP_IdxGT
); VdbeCoverageIf(v
, op
==OP_IdxGT
);
1655 testcase( op
==OP_IdxGE
); VdbeCoverageIf(v
, op
==OP_IdxGE
);
1656 testcase( op
==OP_IdxLT
); VdbeCoverageIf(v
, op
==OP_IdxLT
);
1657 testcase( op
==OP_IdxLE
); VdbeCoverageIf(v
, op
==OP_IdxLE
);
1660 /* Seek the table cursor, if required */
1662 /* pIdx is a covering index. No need to access the main table. */
1663 }else if( HasRowid(pIdx
->pTable
) ){
1664 if( (pWInfo
->wctrlFlags
& WHERE_SEEK_TABLE
) || (
1665 (pWInfo
->wctrlFlags
& WHERE_SEEK_UNIQ_TABLE
)
1666 && (pWInfo
->eOnePass
==ONEPASS_SINGLE
)
1668 iRowidReg
= ++pParse
->nMem
;
1669 sqlite3VdbeAddOp2(v
, OP_IdxRowid
, iIdxCur
, iRowidReg
);
1670 sqlite3ExprCacheStore(pParse
, iCur
, -1, iRowidReg
);
1671 sqlite3VdbeAddOp3(v
, OP_NotExists
, iCur
, 0, iRowidReg
);
1674 codeDeferredSeek(pWInfo
, pIdx
, iCur
, iIdxCur
);
1676 }else if( iCur
!=iIdxCur
){
1677 Index
*pPk
= sqlite3PrimaryKeyIndex(pIdx
->pTable
);
1678 iRowidReg
= sqlite3GetTempRange(pParse
, pPk
->nKeyCol
);
1679 for(j
=0; j
<pPk
->nKeyCol
; j
++){
1680 k
= sqlite3ColumnOfIndex(pIdx
, pPk
->aiColumn
[j
]);
1681 sqlite3VdbeAddOp3(v
, OP_Column
, iIdxCur
, k
, iRowidReg
+j
);
1683 sqlite3VdbeAddOp4Int(v
, OP_NotFound
, iCur
, addrCont
,
1684 iRowidReg
, pPk
->nKeyCol
); VdbeCoverage(v
);
1687 /* If pIdx is an index on one or more expressions, then look through
1688 ** all the expressions in pWInfo and try to transform matching expressions
1689 ** into reference to index columns.
1691 whereIndexExprTrans(pIdx
, iCur
, iIdxCur
, pWInfo
);
1694 /* Record the instruction used to terminate the loop. */
1695 if( pLoop
->wsFlags
& WHERE_ONEROW
){
1696 pLevel
->op
= OP_Noop
;
1698 pLevel
->op
= OP_Prev
;
1700 pLevel
->op
= OP_Next
;
1702 pLevel
->p1
= iIdxCur
;
1703 pLevel
->p3
= (pLoop
->wsFlags
&WHERE_UNQ_WANTED
)!=0 ? 1:0;
1704 if( (pLoop
->wsFlags
& WHERE_CONSTRAINT
)==0 ){
1705 pLevel
->p5
= SQLITE_STMTSTATUS_FULLSCAN_STEP
;
1707 assert( pLevel
->p5
==0 );
1709 if( omitTable
) pIdx
= 0;
1712 #ifndef SQLITE_OMIT_OR_OPTIMIZATION
1713 if( pLoop
->wsFlags
& WHERE_MULTI_OR
){
1714 /* Case 5: Two or more separately indexed terms connected by OR
1718 ** CREATE TABLE t1(a,b,c,d);
1719 ** CREATE INDEX i1 ON t1(a);
1720 ** CREATE INDEX i2 ON t1(b);
1721 ** CREATE INDEX i3 ON t1(c);
1723 ** SELECT * FROM t1 WHERE a=5 OR b=7 OR (c=11 AND d=13)
1725 ** In the example, there are three indexed terms connected by OR.
1726 ** The top of the loop looks like this:
1728 ** Null 1 # Zero the rowset in reg 1
1730 ** Then, for each indexed term, the following. The arguments to
1731 ** RowSetTest are such that the rowid of the current row is inserted
1732 ** into the RowSet. If it is already present, control skips the
1733 ** Gosub opcode and jumps straight to the code generated by WhereEnd().
1735 ** sqlite3WhereBegin(<term>)
1736 ** RowSetTest # Insert rowid into rowset
1738 ** sqlite3WhereEnd()
1740 ** Following the above, code to terminate the loop. Label A, the target
1741 ** of the Gosub above, jumps to the instruction right after the Goto.
1743 ** Null 1 # Zero the rowset in reg 1
1744 ** Goto B # The loop is finished.
1746 ** A: <loop body> # Return data, whatever.
1748 ** Return 2 # Jump back to the Gosub
1750 ** B: <after the loop>
1752 ** Added 2014-05-26: If the table is a WITHOUT ROWID table, then
1753 ** use an ephemeral index instead of a RowSet to record the primary
1754 ** keys of the rows we have already seen.
1757 WhereClause
*pOrWc
; /* The OR-clause broken out into subterms */
1758 SrcList
*pOrTab
; /* Shortened table list or OR-clause generation */
1759 Index
*pCov
= 0; /* Potential covering index (or NULL) */
1760 int iCovCur
= pParse
->nTab
++; /* Cursor used for index scans (if any) */
1762 int regReturn
= ++pParse
->nMem
; /* Register used with OP_Gosub */
1763 int regRowset
= 0; /* Register for RowSet object */
1764 int regRowid
= 0; /* Register holding rowid */
1765 int iLoopBody
= sqlite3VdbeMakeLabel(v
); /* Start of loop body */
1766 int iRetInit
; /* Address of regReturn init */
1767 int untestedTerms
= 0; /* Some terms not completely tested */
1768 int ii
; /* Loop counter */
1769 u16 wctrlFlags
; /* Flags for sub-WHERE clause */
1770 Expr
*pAndExpr
= 0; /* An ".. AND (...)" expression */
1771 Table
*pTab
= pTabItem
->pTab
;
1773 pTerm
= pLoop
->aLTerm
[0];
1775 assert( pTerm
->eOperator
& WO_OR
);
1776 assert( (pTerm
->wtFlags
& TERM_ORINFO
)!=0 );
1777 pOrWc
= &pTerm
->u
.pOrInfo
->wc
;
1778 pLevel
->op
= OP_Return
;
1779 pLevel
->p1
= regReturn
;
1781 /* Set up a new SrcList in pOrTab containing the table being scanned
1782 ** by this loop in the a[0] slot and all notReady tables in a[1..] slots.
1783 ** This becomes the SrcList in the recursive call to sqlite3WhereBegin().
1785 if( pWInfo
->nLevel
>1 ){
1786 int nNotReady
; /* The number of notReady tables */
1787 struct SrcList_item
*origSrc
; /* Original list of tables */
1788 nNotReady
= pWInfo
->nLevel
- iLevel
- 1;
1789 pOrTab
= sqlite3StackAllocRaw(db
,
1790 sizeof(*pOrTab
)+ nNotReady
*sizeof(pOrTab
->a
[0]));
1791 if( pOrTab
==0 ) return notReady
;
1792 pOrTab
->nAlloc
= (u8
)(nNotReady
+ 1);
1793 pOrTab
->nSrc
= pOrTab
->nAlloc
;
1794 memcpy(pOrTab
->a
, pTabItem
, sizeof(*pTabItem
));
1795 origSrc
= pWInfo
->pTabList
->a
;
1796 for(k
=1; k
<=nNotReady
; k
++){
1797 memcpy(&pOrTab
->a
[k
], &origSrc
[pLevel
[k
].iFrom
], sizeof(pOrTab
->a
[k
]));
1800 pOrTab
= pWInfo
->pTabList
;
1803 /* Initialize the rowset register to contain NULL. An SQL NULL is
1804 ** equivalent to an empty rowset. Or, create an ephemeral index
1805 ** capable of holding primary keys in the case of a WITHOUT ROWID.
1807 ** Also initialize regReturn to contain the address of the instruction
1808 ** immediately following the OP_Return at the bottom of the loop. This
1809 ** is required in a few obscure LEFT JOIN cases where control jumps
1810 ** over the top of the loop into the body of it. In this case the
1811 ** correct response for the end-of-loop code (the OP_Return) is to
1812 ** fall through to the next instruction, just as an OP_Next does if
1813 ** called on an uninitialized cursor.
1815 if( (pWInfo
->wctrlFlags
& WHERE_DUPLICATES_OK
)==0 ){
1816 if( HasRowid(pTab
) ){
1817 regRowset
= ++pParse
->nMem
;
1818 sqlite3VdbeAddOp2(v
, OP_Null
, 0, regRowset
);
1820 Index
*pPk
= sqlite3PrimaryKeyIndex(pTab
);
1821 regRowset
= pParse
->nTab
++;
1822 sqlite3VdbeAddOp2(v
, OP_OpenEphemeral
, regRowset
, pPk
->nKeyCol
);
1823 sqlite3VdbeSetP4KeyInfo(pParse
, pPk
);
1825 regRowid
= ++pParse
->nMem
;
1827 iRetInit
= sqlite3VdbeAddOp2(v
, OP_Integer
, 0, regReturn
);
1829 /* If the original WHERE clause is z of the form: (x1 OR x2 OR ...) AND y
1830 ** Then for every term xN, evaluate as the subexpression: xN AND z
1831 ** That way, terms in y that are factored into the disjunction will
1832 ** be picked up by the recursive calls to sqlite3WhereBegin() below.
1834 ** Actually, each subexpression is converted to "xN AND w" where w is
1835 ** the "interesting" terms of z - terms that did not originate in the
1836 ** ON or USING clause of a LEFT JOIN, and terms that are usable as
1839 ** This optimization also only applies if the (x1 OR x2 OR ...) term
1840 ** is not contained in the ON clause of a LEFT JOIN.
1841 ** See ticket http://www.sqlite.org/src/info/f2369304e4
1845 for(iTerm
=0; iTerm
<pWC
->nTerm
; iTerm
++){
1846 Expr
*pExpr
= pWC
->a
[iTerm
].pExpr
;
1847 if( &pWC
->a
[iTerm
] == pTerm
) continue;
1848 if( ExprHasProperty(pExpr
, EP_FromJoin
) ) continue;
1849 testcase( pWC
->a
[iTerm
].wtFlags
& TERM_VIRTUAL
);
1850 testcase( pWC
->a
[iTerm
].wtFlags
& TERM_CODED
);
1851 if( (pWC
->a
[iTerm
].wtFlags
& (TERM_VIRTUAL
|TERM_CODED
))!=0 ) continue;
1852 if( (pWC
->a
[iTerm
].eOperator
& WO_ALL
)==0 ) continue;
1853 testcase( pWC
->a
[iTerm
].wtFlags
& TERM_ORINFO
);
1854 pExpr
= sqlite3ExprDup(db
, pExpr
, 0);
1855 pAndExpr
= sqlite3ExprAnd(db
, pAndExpr
, pExpr
);
1858 pAndExpr
= sqlite3PExpr(pParse
, TK_AND
|TKFLG_DONTFOLD
, 0, pAndExpr
);
1862 /* Run a separate WHERE clause for each term of the OR clause. After
1863 ** eliminating duplicates from other WHERE clauses, the action for each
1864 ** sub-WHERE clause is to to invoke the main loop body as a subroutine.
1866 wctrlFlags
= WHERE_OR_SUBCLAUSE
| (pWInfo
->wctrlFlags
& WHERE_SEEK_TABLE
);
1867 for(ii
=0; ii
<pOrWc
->nTerm
; ii
++){
1868 WhereTerm
*pOrTerm
= &pOrWc
->a
[ii
];
1869 if( pOrTerm
->leftCursor
==iCur
|| (pOrTerm
->eOperator
& WO_AND
)!=0 ){
1870 WhereInfo
*pSubWInfo
; /* Info for single OR-term scan */
1871 Expr
*pOrExpr
= pOrTerm
->pExpr
; /* Current OR clause term */
1872 int jmp1
= 0; /* Address of jump operation */
1873 if( pAndExpr
&& !ExprHasProperty(pOrExpr
, EP_FromJoin
) ){
1874 pAndExpr
->pLeft
= pOrExpr
;
1877 /* Loop through table entries that match term pOrTerm. */
1878 WHERETRACE(0xffff, ("Subplan for OR-clause:\n"));
1879 pSubWInfo
= sqlite3WhereBegin(pParse
, pOrTab
, pOrExpr
, 0, 0,
1880 wctrlFlags
, iCovCur
);
1881 assert( pSubWInfo
|| pParse
->nErr
|| db
->mallocFailed
);
1883 WhereLoop
*pSubLoop
;
1884 int addrExplain
= sqlite3WhereExplainOneScan(
1885 pParse
, pOrTab
, &pSubWInfo
->a
[0], iLevel
, pLevel
->iFrom
, 0
1887 sqlite3WhereAddScanStatus(v
, pOrTab
, &pSubWInfo
->a
[0], addrExplain
);
1889 /* This is the sub-WHERE clause body. First skip over
1890 ** duplicate rows from prior sub-WHERE clauses, and record the
1891 ** rowid (or PRIMARY KEY) for the current row so that the same
1892 ** row will be skipped in subsequent sub-WHERE clauses.
1894 if( (pWInfo
->wctrlFlags
& WHERE_DUPLICATES_OK
)==0 ){
1896 int iSet
= ((ii
==pOrWc
->nTerm
-1)?-1:ii
);
1897 if( HasRowid(pTab
) ){
1898 r
= sqlite3ExprCodeGetColumn(pParse
, pTab
, -1, iCur
, regRowid
, 0);
1899 jmp1
= sqlite3VdbeAddOp4Int(v
, OP_RowSetTest
, regRowset
, 0,
1903 Index
*pPk
= sqlite3PrimaryKeyIndex(pTab
);
1904 int nPk
= pPk
->nKeyCol
;
1907 /* Read the PK into an array of temp registers. */
1908 r
= sqlite3GetTempRange(pParse
, nPk
);
1909 for(iPk
=0; iPk
<nPk
; iPk
++){
1910 int iCol
= pPk
->aiColumn
[iPk
];
1911 sqlite3ExprCodeGetColumnToReg(pParse
, pTab
, iCol
, iCur
, r
+iPk
);
1914 /* Check if the temp table already contains this key. If so,
1915 ** the row has already been included in the result set and
1916 ** can be ignored (by jumping past the Gosub below). Otherwise,
1917 ** insert the key into the temp table and proceed with processing
1920 ** Use some of the same optimizations as OP_RowSetTest: If iSet
1921 ** is zero, assume that the key cannot already be present in
1922 ** the temp table. And if iSet is -1, assume that there is no
1923 ** need to insert the key into the temp table, as it will never
1924 ** be tested for. */
1926 jmp1
= sqlite3VdbeAddOp4Int(v
, OP_Found
, regRowset
, 0, r
, nPk
);
1930 sqlite3VdbeAddOp3(v
, OP_MakeRecord
, r
, nPk
, regRowid
);
1931 sqlite3VdbeAddOp4Int(v
, OP_IdxInsert
, regRowset
, regRowid
,
1933 if( iSet
) sqlite3VdbeChangeP5(v
, OPFLAG_USESEEKRESULT
);
1936 /* Release the array of temp registers */
1937 sqlite3ReleaseTempRange(pParse
, r
, nPk
);
1941 /* Invoke the main loop body as a subroutine */
1942 sqlite3VdbeAddOp2(v
, OP_Gosub
, regReturn
, iLoopBody
);
1944 /* Jump here (skipping the main loop body subroutine) if the
1945 ** current sub-WHERE row is a duplicate from prior sub-WHEREs. */
1946 if( jmp1
) sqlite3VdbeJumpHere(v
, jmp1
);
1948 /* The pSubWInfo->untestedTerms flag means that this OR term
1949 ** contained one or more AND term from a notReady table. The
1950 ** terms from the notReady table could not be tested and will
1951 ** need to be tested later.
1953 if( pSubWInfo
->untestedTerms
) untestedTerms
= 1;
1955 /* If all of the OR-connected terms are optimized using the same
1956 ** index, and the index is opened using the same cursor number
1957 ** by each call to sqlite3WhereBegin() made by this loop, it may
1958 ** be possible to use that index as a covering index.
1960 ** If the call to sqlite3WhereBegin() above resulted in a scan that
1961 ** uses an index, and this is either the first OR-connected term
1962 ** processed or the index is the same as that used by all previous
1963 ** terms, set pCov to the candidate covering index. Otherwise, set
1964 ** pCov to NULL to indicate that no candidate covering index will
1967 pSubLoop
= pSubWInfo
->a
[0].pWLoop
;
1968 assert( (pSubLoop
->wsFlags
& WHERE_AUTO_INDEX
)==0 );
1969 if( (pSubLoop
->wsFlags
& WHERE_INDEXED
)!=0
1970 && (ii
==0 || pSubLoop
->u
.btree
.pIndex
==pCov
)
1971 && (HasRowid(pTab
) || !IsPrimaryKeyIndex(pSubLoop
->u
.btree
.pIndex
))
1973 assert( pSubWInfo
->a
[0].iIdxCur
==iCovCur
);
1974 pCov
= pSubLoop
->u
.btree
.pIndex
;
1979 /* Finish the loop through table entries that match term pOrTerm. */
1980 sqlite3WhereEnd(pSubWInfo
);
1984 pLevel
->u
.pCovidx
= pCov
;
1985 if( pCov
) pLevel
->iIdxCur
= iCovCur
;
1987 pAndExpr
->pLeft
= 0;
1988 sqlite3ExprDelete(db
, pAndExpr
);
1990 sqlite3VdbeChangeP1(v
, iRetInit
, sqlite3VdbeCurrentAddr(v
));
1991 sqlite3VdbeGoto(v
, pLevel
->addrBrk
);
1992 sqlite3VdbeResolveLabel(v
, iLoopBody
);
1994 if( pWInfo
->nLevel
>1 ) sqlite3StackFree(db
, pOrTab
);
1995 if( !untestedTerms
) disableTerm(pLevel
, pTerm
);
1997 #endif /* SQLITE_OMIT_OR_OPTIMIZATION */
2000 /* Case 6: There is no usable index. We must do a complete
2001 ** scan of the entire table.
2003 static const u8 aStep
[] = { OP_Next
, OP_Prev
};
2004 static const u8 aStart
[] = { OP_Rewind
, OP_Last
};
2005 assert( bRev
==0 || bRev
==1 );
2006 if( pTabItem
->fg
.isRecursive
){
2007 /* Tables marked isRecursive have only a single row that is stored in
2008 ** a pseudo-cursor. No need to Rewind or Next such cursors. */
2009 pLevel
->op
= OP_Noop
;
2011 codeCursorHint(pTabItem
, pWInfo
, pLevel
, 0);
2012 pLevel
->op
= aStep
[bRev
];
2014 pLevel
->p2
= 1 + sqlite3VdbeAddOp2(v
, aStart
[bRev
], iCur
, addrHalt
);
2015 VdbeCoverageIf(v
, bRev
==0);
2016 VdbeCoverageIf(v
, bRev
!=0);
2017 pLevel
->p5
= SQLITE_STMTSTATUS_FULLSCAN_STEP
;
2021 #ifdef SQLITE_ENABLE_STMT_SCANSTATUS
2022 pLevel
->addrVisit
= sqlite3VdbeCurrentAddr(v
);
2025 /* Insert code to test every subexpression that can be completely
2026 ** computed using the current set of tables.
2028 ** This loop may run between one and three times, depending on the
2029 ** constraints to be generated. The value of stack variable iLoop
2030 ** determines the constraints coded by each iteration, as follows:
2032 ** iLoop==1: Code only expressions that are entirely covered by pIdx.
2033 ** iLoop==2: Code remaining expressions that do not contain correlated
2035 ** iLoop==3: Code all remaining expressions.
2037 ** An effort is made to skip unnecessary iterations of the loop.
2039 iLoop
= (pIdx
? 1 : 2);
2041 int iNext
= 0; /* Next value for iLoop */
2042 for(pTerm
=pWC
->a
, j
=pWC
->nTerm
; j
>0; j
--, pTerm
++){
2044 int skipLikeAddr
= 0;
2045 testcase( pTerm
->wtFlags
& TERM_VIRTUAL
);
2046 testcase( pTerm
->wtFlags
& TERM_CODED
);
2047 if( pTerm
->wtFlags
& (TERM_VIRTUAL
|TERM_CODED
) ) continue;
2048 if( (pTerm
->prereqAll
& pLevel
->notReady
)!=0 ){
2049 testcase( pWInfo
->untestedTerms
==0
2050 && (pWInfo
->wctrlFlags
& WHERE_OR_SUBCLAUSE
)!=0 );
2051 pWInfo
->untestedTerms
= 1;
2056 if( pLevel
->iLeftJoin
&& !ExprHasProperty(pE
, EP_FromJoin
) ){
2060 if( iLoop
==1 && !sqlite3ExprCoveredByIndex(pE
, pLevel
->iTabCur
, pIdx
) ){
2064 if( iLoop
<3 && (pTerm
->wtFlags
& TERM_VARSELECT
) ){
2065 if( iNext
==0 ) iNext
= 3;
2069 if( pTerm
->wtFlags
& TERM_LIKECOND
){
2070 /* If the TERM_LIKECOND flag is set, that means that the range search
2071 ** is sufficient to guarantee that the LIKE operator is true, so we
2072 ** can skip the call to the like(A,B) function. But this only works
2073 ** for strings. So do not skip the call to the function on the pass
2074 ** that compares BLOBs. */
2075 #ifdef SQLITE_LIKE_DOESNT_MATCH_BLOBS
2078 u32 x
= pLevel
->iLikeRepCntr
;
2080 skipLikeAddr
= sqlite3VdbeAddOp1(v
, (x
&1)?OP_IfNot
:OP_If
, (int)(x
>>1));
2084 #ifdef WHERETRACE_ENABLED /* 0xffff */
2085 if( sqlite3WhereTrace
){
2086 VdbeNoopComment((v
, "WhereTerm[%d] (%p) priority=%d",
2087 pWC
->nTerm
-j
, pTerm
, iLoop
));
2090 sqlite3ExprIfFalse(pParse
, pE
, addrCont
, SQLITE_JUMPIFNULL
);
2091 if( skipLikeAddr
) sqlite3VdbeJumpHere(v
, skipLikeAddr
);
2092 pTerm
->wtFlags
|= TERM_CODED
;
2097 /* Insert code to test for implied constraints based on transitivity
2098 ** of the "==" operator.
2100 ** Example: If the WHERE clause contains "t1.a=t2.b" and "t2.b=123"
2101 ** and we are coding the t1 loop and the t2 loop has not yet coded,
2102 ** then we cannot use the "t1.a=t2.b" constraint, but we can code
2103 ** the implied "t1.a=123" constraint.
2105 for(pTerm
=pWC
->a
, j
=pWC
->nTerm
; j
>0; j
--, pTerm
++){
2108 if( pTerm
->wtFlags
& (TERM_VIRTUAL
|TERM_CODED
) ) continue;
2109 if( (pTerm
->eOperator
& (WO_EQ
|WO_IS
))==0 ) continue;
2110 if( (pTerm
->eOperator
& WO_EQUIV
)==0 ) continue;
2111 if( pTerm
->leftCursor
!=iCur
) continue;
2112 if( pLevel
->iLeftJoin
) continue;
2114 assert( !ExprHasProperty(pE
, EP_FromJoin
) );
2115 assert( (pTerm
->prereqRight
& pLevel
->notReady
)!=0 );
2116 pAlt
= sqlite3WhereFindTerm(pWC
, iCur
, pTerm
->u
.leftColumn
, notReady
,
2117 WO_EQ
|WO_IN
|WO_IS
, 0);
2118 if( pAlt
==0 ) continue;
2119 if( pAlt
->wtFlags
& (TERM_CODED
) ) continue;
2120 testcase( pAlt
->eOperator
& WO_EQ
);
2121 testcase( pAlt
->eOperator
& WO_IS
);
2122 testcase( pAlt
->eOperator
& WO_IN
);
2123 VdbeModuleComment((v
, "begin transitive constraint"));
2124 sEAlt
= *pAlt
->pExpr
;
2125 sEAlt
.pLeft
= pE
->pLeft
;
2126 sqlite3ExprIfFalse(pParse
, &sEAlt
, addrCont
, SQLITE_JUMPIFNULL
);
2129 /* For a LEFT OUTER JOIN, generate code that will record the fact that
2130 ** at least one row of the right table has matched the left table.
2132 if( pLevel
->iLeftJoin
){
2133 pLevel
->addrFirst
= sqlite3VdbeCurrentAddr(v
);
2134 sqlite3VdbeAddOp2(v
, OP_Integer
, 1, pLevel
->iLeftJoin
);
2135 VdbeComment((v
, "record LEFT JOIN hit"));
2136 sqlite3ExprCacheClear(pParse
);
2137 for(pTerm
=pWC
->a
, j
=0; j
<pWC
->nTerm
; j
++, pTerm
++){
2138 testcase( pTerm
->wtFlags
& TERM_VIRTUAL
);
2139 testcase( pTerm
->wtFlags
& TERM_CODED
);
2140 if( pTerm
->wtFlags
& (TERM_VIRTUAL
|TERM_CODED
) ) continue;
2141 if( (pTerm
->prereqAll
& pLevel
->notReady
)!=0 ){
2142 assert( pWInfo
->untestedTerms
);
2145 assert( pTerm
->pExpr
);
2146 sqlite3ExprIfFalse(pParse
, pTerm
->pExpr
, addrCont
, SQLITE_JUMPIFNULL
);
2147 pTerm
->wtFlags
|= TERM_CODED
;
2151 return pLevel
->notReady
;