1 /*
2 ** 2001 September 15
3 **
4 ** The author disclaims copyright to this source code. In place of
5 ** a legal notice, here is a blessing:
6 **
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.
10 **
11 *************************************************************************
12 ** This module contains C code that generates VDBE code used to process
13 ** the WHERE clause of SQL statements. This module is responsible for
14 ** generating the code that loops through a table looking for applicable
15 ** rows. Indices are selected and used to speed the search when doing
16 ** so is applicable. Because this module is responsible for selecting
17 ** indices, you might also think of this module as the "query optimizer".
18 */
19 #include "sqliteInt.h"
20 #include "whereInt.h"
21
22 /*
23 ** Extra information appended to the end of sqlite3_index_info but not
24 ** visible to the xBestIndex function, at least not directly. The
25 ** sqlite3_vtab_collation() interface knows how to reach it, however.
26 **
27 ** This object is not an API and can be changed from one release to the
28 ** next. As long as allocateIndexInfo() and sqlite3_vtab_collation()
29 ** agree on the structure, all will be well.
30 */
31 typedef struct HiddenIndexInfo HiddenIndexInfo;
32 struct HiddenIndexInfo {
33 WhereClause *pWC; /* The Where clause being analyzed */
34 Parse *pParse; /* The parsing context */
35 };
36
37 /* Forward declaration of methods */
38 static int whereLoopResize(sqlite3*, WhereLoop*, int);
39
40 /* Test variable that can be set to enable WHERE tracing */
41 #if defined(SQLITE_TEST) || defined(SQLITE_DEBUG)
42 /***/ int sqlite3WhereTrace = 0;
43 #endif
44
45
46 /*
47 ** Return the estimated number of output rows from a WHERE clause
48 */
sqlite3WhereOutputRowCount(WhereInfo * pWInfo)49 LogEst sqlite3WhereOutputRowCount(WhereInfo *pWInfo){
50 return pWInfo->nRowOut;
51 }
52
53 /*
54 ** Return one of the WHERE_DISTINCT_xxxxx values to indicate how this
55 ** WHERE clause returns outputs for DISTINCT processing.
56 */
sqlite3WhereIsDistinct(WhereInfo * pWInfo)57 int sqlite3WhereIsDistinct(WhereInfo *pWInfo){
58 return pWInfo->eDistinct;
59 }
60
61 /*
62 ** Return TRUE if the WHERE clause returns rows in ORDER BY order.
63 ** Return FALSE if the output needs to be sorted.
64 */
sqlite3WhereIsOrdered(WhereInfo * pWInfo)65 int sqlite3WhereIsOrdered(WhereInfo *pWInfo){
66 return pWInfo->nOBSat;
67 }
68
69 /*
70 ** In the ORDER BY LIMIT optimization, if the inner-most loop is known
71 ** to emit rows in increasing order, and if the last row emitted by the
72 ** inner-most loop did not fit within the sorter, then we can skip all
73 ** subsequent rows for the current iteration of the inner loop (because they
74 ** will not fit in the sorter either) and continue with the second inner
75 ** loop - the loop immediately outside the inner-most.
76 **
77 ** When a row does not fit in the sorter (because the sorter already
78 ** holds LIMIT+OFFSET rows that are smaller), then a jump is made to the
79 ** label returned by this function.
80 **
81 ** If the ORDER BY LIMIT optimization applies, the jump destination should
82 ** be the continuation for the second-inner-most loop. If the ORDER BY
83 ** LIMIT optimization does not apply, then the jump destination should
84 ** be the continuation for the inner-most loop.
85 **
86 ** It is always safe for this routine to return the continuation of the
87 ** inner-most loop, in the sense that a correct answer will result.
88 ** Returning the continuation the second inner loop is an optimization
89 ** that might make the code run a little faster, but should not change
90 ** the final answer.
91 */
sqlite3WhereOrderByLimitOptLabel(WhereInfo * pWInfo)92 int sqlite3WhereOrderByLimitOptLabel(WhereInfo *pWInfo){
93 WhereLevel *pInner;
94 if (!pWInfo->bOrderedInnerLoop) {
95 /* The ORDER BY LIMIT optimization does not apply. Jump to the
96 ** continuation of the inner-most loop. */
97 return pWInfo->iContinue;
98 }
99 pInner = &pWInfo->a[pWInfo->nLevel-1];
100 assert( pInner->addrNxt!=0 );
101 return pInner->addrNxt;
102 }
103
104 /*
105 ** Return the VDBE address or label to jump to in order to continue
106 ** immediately with the next row of a WHERE clause.
107 */
sqlite3WhereContinueLabel(WhereInfo * pWInfo)108 int sqlite3WhereContinueLabel(WhereInfo *pWInfo){
109 assert( pWInfo->iContinue!=0 );
110 return pWInfo->iContinue;
111 }
112
113 /*
114 ** Return the VDBE address or label to jump to in order to break
115 ** out of a WHERE loop.
116 */
sqlite3WhereBreakLabel(WhereInfo * pWInfo)117 int sqlite3WhereBreakLabel(WhereInfo *pWInfo){
118 return pWInfo->iBreak;
119 }
120
121 /*
122 ** Return ONEPASS_OFF (0) if an UPDATE or DELETE statement is unable to
123 ** operate directly on the rowis returned by a WHERE clause. Return
124 ** ONEPASS_SINGLE (1) if the statement can operation directly because only
125 ** a single row is to be changed. Return ONEPASS_MULTI (2) if the one-pass
126 ** optimization can be used on multiple
127 **
128 ** If the ONEPASS optimization is used (if this routine returns true)
129 ** then also write the indices of open cursors used by ONEPASS
130 ** into aiCur[0] and aiCur[1]. iaCur[0] gets the cursor of the data
131 ** table and iaCur[1] gets the cursor used by an auxiliary index.
132 ** Either value may be -1, indicating that cursor is not used.
133 ** Any cursors returned will have been opened for writing.
134 **
135 ** aiCur[0] and aiCur[1] both get -1 if the where-clause logic is
136 ** unable to use the ONEPASS optimization.
137 */
sqlite3WhereOkOnePass(WhereInfo * pWInfo,int * aiCur)138 int sqlite3WhereOkOnePass(WhereInfo *pWInfo, int *aiCur){
139 memcpy(aiCur, pWInfo->aiCurOnePass, sizeof(int)*2);
140 #ifdef WHERETRACE_ENABLED
141 if (sqlite3WhereTrace && pWInfo->eOnePass!=ONEPASS_OFF) {
142 sqlite3DebugPrintf("%s cursors: %d %d\n",
143 pWInfo->eOnePass==ONEPASS_SINGLE ? "ONEPASS_SINGLE" : "ONEPASS_MULTI",
144 aiCur[0], aiCur[1]);
145 }
146 #endif
147 return pWInfo->eOnePass;
148 }
149
150 /*
151 ** Move the content of pSrc into pDest
152 */
whereOrMove(WhereOrSet * pDest,WhereOrSet * pSrc)153 static void whereOrMove(WhereOrSet *pDest, WhereOrSet *pSrc){
154 pDest->n = pSrc->n;
155 memcpy(pDest->a, pSrc->a, pDest->n*sizeof(pDest->a[0]));
156 }
157
158 /*
159 ** Try to insert a new prerequisite/cost entry into the WhereOrSet pSet.
160 **
161 ** The new entry might overwrite an existing entry, or it might be
162 ** appended, or it might be discarded. Do whatever is the right thing
163 ** so that pSet keeps the N_OR_COST best entries seen so far.
164 */
whereOrInsert(WhereOrSet * pSet,Bitmask prereq,LogEst rRun,LogEst nOut)165 static int whereOrInsert(
166 WhereOrSet *pSet, /* The WhereOrSet to be updated */
167 Bitmask prereq, /* Prerequisites of the new entry */
168 LogEst rRun, /* Run-cost of the new entry */
169 LogEst nOut /* Number of outputs for the new entry */
170 ){
171 u16 i;
172 WhereOrCost *p;
173 for (i=pSet->n, p=pSet->a; i>0; i--, p++) {
174 if (rRun<=p->rRun && (prereq & p->prereq)==prereq) {
175 goto whereOrInsert_done;
176 }
177 if (p->rRun<=rRun && (p->prereq & prereq)==p->prereq) {
178 return 0;
179 }
180 }
181 if (pSet->n<N_OR_COST) {
182 p = &pSet->a[pSet->n++];
183 p->nOut = nOut;
184 } else {
185 p = pSet->a;
186 for (i=1; i<pSet->n; i++) {
187 if (p->rRun>pSet->a[i].rRun) p = pSet->a + i;
188 }
189 if (p->rRun<=rRun) return 0;
190 }
191 whereOrInsert_done:
192 p->prereq = prereq;
193 p->rRun = rRun;
194 if (p->nOut>nOut) p->nOut = nOut;
195 return 1;
196 }
197
198 /*
199 ** Return the bitmask for the given cursor number. Return 0 if
200 ** iCursor is not in the set.
201 */
sqlite3WhereGetMask(WhereMaskSet * pMaskSet,int iCursor)202 Bitmask sqlite3WhereGetMask(WhereMaskSet *pMaskSet, int iCursor){
203 int i;
204 assert( pMaskSet->n<=(int)sizeof(Bitmask)*8 );
205 for (i=0; i<pMaskSet->n; i++) {
206 if (pMaskSet->ix[i]==iCursor) {
207 return MASKBIT(i);
208 }
209 }
210 return 0;
211 }
212
213 /*
214 ** Create a new mask for cursor iCursor.
215 **
216 ** There is one cursor per table in the FROM clause. The number of
217 ** tables in the FROM clause is limited by a test early in the
218 ** sqlite3WhereBegin() routine. So we know that the pMaskSet->ix[]
219 ** array will never overflow.
220 */
createMask(WhereMaskSet * pMaskSet,int iCursor)221 static void createMask(WhereMaskSet *pMaskSet, int iCursor){
222 assert( pMaskSet->n < ArraySize(pMaskSet->ix));
223 pMaskSet->ix[pMaskSet->n++] = iCursor;
224 }
225
226 /*
227 ** Advance to the next WhereTerm that matches according to the criteria
228 ** established when the pScan object was initialized by whereScanInit().
229 ** Return NULL if there are no more matching WhereTerms.
230 */
whereScanNext(WhereScan * pScan)231 static WhereTerm *whereScanNext(WhereScan *pScan){
232 int iCur; /* The cursor on the LHS of the term */
233 i16 iColumn; /* The column on the LHS of the term. -1 for IPK */
234 Expr *pX; /* An expression being tested */
235 WhereClause *pWC; /* Shorthand for pScan->pWC */
236 WhereTerm *pTerm; /* The term being tested */
237 int k = pScan->k; /* Where to start scanning */
238
239 assert( pScan->iEquiv<=pScan->nEquiv );
240 pWC = pScan->pWC;
241 while (1) {
242 iColumn = pScan->aiColumn[pScan->iEquiv-1];
243 iCur = pScan->aiCur[pScan->iEquiv-1];
244 assert( pWC!=0 );
245 do{
246 for (pTerm=pWC->a+k; k<pWC->nTerm; k++, pTerm++) {
247 if (pTerm->leftCursor==iCur
248 && pTerm->u.leftColumn==iColumn
249 && (iColumn!=XN_EXPR
250 || sqlite3ExprCompareSkip(pTerm->pExpr->pLeft,
251 pScan->pIdxExpr,iCur)==0)
252 && (pScan->iEquiv<=1 || !ExprHasProperty(pTerm->pExpr, EP_FromJoin))
253 ) {
254 if ((pTerm->eOperator & WO_EQUIV)!=0
255 && pScan->nEquiv<ArraySize(pScan->aiCur)
256 && (pX = sqlite3ExprSkipCollate(pTerm->pExpr->pRight))->op==TK_COLUMN
257 ) {
258 int j;
259 for (j=0; j<pScan->nEquiv; j++) {
260 if (pScan->aiCur[j]==pX->iTable
261 && pScan->aiColumn[j]==pX->iColumn) {
262 break;
263 }
264 }
265 if (j==pScan->nEquiv) {
266 pScan->aiCur[j] = pX->iTable;
267 pScan->aiColumn[j] = pX->iColumn;
268 pScan->nEquiv++;
269 }
270 }
271 if ((pTerm->eOperator & pScan->opMask)!=0) {
272 /* Verify the affinity and collating sequence match */
273 if (pScan->zCollName && (pTerm->eOperator & WO_ISNULL)==0) {
274 CollSeq *pColl;
275 Parse *pParse = pWC->pWInfo->pParse;
276 pX = pTerm->pExpr;
277 if (!sqlite3IndexAffinityOk(pX, pScan->idxaff)) {
278 continue;
279 }
280 assert(pX->pLeft);
281 pColl = sqlite3BinaryCompareCollSeq(pParse,
282 pX->pLeft, pX->pRight);
283 if (pColl==0) pColl = pParse->db->pDfltColl;
284 if (sqlite3StrICmp(pColl->zName, pScan->zCollName)) {
285 continue;
286 }
287 }
288 if ((pTerm->eOperator & (WO_EQ|WO_IS))!=0
289 && (pX = pTerm->pExpr->pRight)->op==TK_COLUMN
290 && pX->iTable==pScan->aiCur[0]
291 && pX->iColumn==pScan->aiColumn[0]
292 ) {
293 testcase( pTerm->eOperator & WO_IS );
294 continue;
295 }
296 pScan->pWC = pWC;
297 pScan->k = k+1;
298 return pTerm;
299 }
300 }
301 }
302 pWC = pWC->pOuter;
303 k = 0;
304 }while (pWC!=0);
305 if (pScan->iEquiv>=pScan->nEquiv) break;
306 pWC = pScan->pOrigWC;
307 k = 0;
308 pScan->iEquiv++;
309 }
310 return 0;
311 }
312
313 /*
314 ** This is whereScanInit() for the case of an index on an expression.
315 ** It is factored out into a separate tail-recursion subroutine so that
316 ** the normal whereScanInit() routine, which is a high-runner, does not
317 ** need to push registers onto the stack as part of its prologue.
318 */
whereScanInitIndexExpr(WhereScan * pScan)319 static SQLITE_NOINLINE WhereTerm *whereScanInitIndexExpr(WhereScan *pScan){
320 pScan->idxaff = sqlite3ExprAffinity(pScan->pIdxExpr);
321 return whereScanNext(pScan);
322 }
323
324 /*
325 ** Initialize a WHERE clause scanner object. Return a pointer to the
326 ** first match. Return NULL if there are no matches.
327 **
328 ** The scanner will be searching the WHERE clause pWC. It will look
329 ** for terms of the form "X <op> <expr>" where X is column iColumn of table
330 ** iCur. Or if pIdx!=0 then X is column iColumn of index pIdx. pIdx
331 ** must be one of the indexes of table iCur.
332 **
333 ** The <op> must be one of the operators described by opMask.
334 **
335 ** If the search is for X and the WHERE clause contains terms of the
336 ** form X=Y then this routine might also return terms of the form
337 ** "Y <op> <expr>". The number of levels of transitivity is limited,
338 ** but is enough to handle most commonly occurring SQL statements.
339 **
340 ** If X is not the INTEGER PRIMARY KEY then X must be compatible with
341 ** index pIdx.
342 */
whereScanInit(WhereScan * pScan,WhereClause * pWC,int iCur,int iColumn,u32 opMask,Index * pIdx)343 static WhereTerm *whereScanInit(
344 WhereScan *pScan, /* The WhereScan object being initialized */
345 WhereClause *pWC, /* The WHERE clause to be scanned */
346 int iCur, /* Cursor to scan for */
347 int iColumn, /* Column to scan for */
348 u32 opMask, /* Operator(s) to scan for */
349 Index *pIdx /* Must be compatible with this index */
350 ){
351 pScan->pOrigWC = pWC;
352 pScan->pWC = pWC;
353 pScan->pIdxExpr = 0;
354 pScan->idxaff = 0;
355 pScan->zCollName = 0;
356 pScan->opMask = opMask;
357 pScan->k = 0;
358 pScan->aiCur[0] = iCur;
359 pScan->nEquiv = 1;
360 pScan->iEquiv = 1;
361 if (pIdx) {
362 int j = iColumn;
363 iColumn = pIdx->aiColumn[j];
364 if (iColumn==XN_EXPR) {
365 pScan->pIdxExpr = pIdx->aColExpr->a[j].pExpr;
366 pScan->zCollName = pIdx->azColl[j];
367 pScan->aiColumn[0] = XN_EXPR;
368 return whereScanInitIndexExpr(pScan);
369 } else if (iColumn==pIdx->pTable->iPKey) {
370 iColumn = XN_ROWID;
371 } else if (iColumn>=0) {
372 pScan->idxaff = pIdx->pTable->aCol[iColumn].affinity;
373 pScan->zCollName = pIdx->azColl[j];
374 }
375 } else if (iColumn==XN_EXPR) {
376 return 0;
377 }
378 pScan->aiColumn[0] = iColumn;
379 return whereScanNext(pScan);
380 }
381
382 /*
383 ** Search for a term in the WHERE clause that is of the form "X <op> <expr>"
384 ** where X is a reference to the iColumn of table iCur or of index pIdx
385 ** if pIdx!=0 and <op> is one of the WO_xx operator codes specified by
386 ** the op parameter. Return a pointer to the term. Return 0 if not found.
387 **
388 ** If pIdx!=0 then it must be one of the indexes of table iCur.
389 ** Search for terms matching the iColumn-th column of pIdx
390 ** rather than the iColumn-th column of table iCur.
391 **
392 ** The term returned might by Y=<expr> if there is another constraint in
393 ** the WHERE clause that specifies that X=Y. Any such constraints will be
394 ** identified by the WO_EQUIV bit in the pTerm->eOperator field. The
395 ** aiCur[]/iaColumn[] arrays hold X and all its equivalents. There are 11
396 ** slots in aiCur[]/aiColumn[] so that means we can look for X plus up to 10
397 ** other equivalent values. Hence a search for X will return <expr> if X=A1
398 ** and A1=A2 and A2=A3 and ... and A9=A10 and A10=<expr>.
399 **
400 ** If there are multiple terms in the WHERE clause of the form "X <op> <expr>"
401 ** then try for the one with no dependencies on <expr> - in other words where
402 ** <expr> is a constant expression of some kind. Only return entries of
403 ** the form "X <op> Y" where Y is a column in another table if no terms of
404 ** the form "X <op> <const-expr>" exist. If no terms with a constant RHS
405 ** exist, try to return a term that does not use WO_EQUIV.
406 */
sqlite3WhereFindTerm(WhereClause * pWC,int iCur,int iColumn,Bitmask notReady,u32 op,Index * pIdx)407 WhereTerm *sqlite3WhereFindTerm(
408 WhereClause *pWC, /* The WHERE clause to be searched */
409 int iCur, /* Cursor number of LHS */
410 int iColumn, /* Column number of LHS */
411 Bitmask notReady, /* RHS must not overlap with this mask */
412 u32 op, /* Mask of WO_xx values describing operator */
413 Index *pIdx /* Must be compatible with this index, if not NULL */
414 ){
415 WhereTerm *pResult = 0;
416 WhereTerm *p;
417 WhereScan scan;
418
419 p = whereScanInit(&scan, pWC, iCur, iColumn, op, pIdx);
420 op &= WO_EQ|WO_IS;
421 while (p) {
422 if ((p->prereqRight & notReady)==0) {
423 if (p->prereqRight==0 && (p->eOperator&op)!=0) {
424 testcase( p->eOperator & WO_IS );
425 return p;
426 }
427 if (pResult==0) pResult = p;
428 }
429 p = whereScanNext(&scan);
430 }
431 return pResult;
432 }
433
434 /*
435 ** This function searches pList for an entry that matches the iCol-th column
436 ** of index pIdx.
437 **
438 ** If such an expression is found, its index in pList->a[] is returned. If
439 ** no expression is found, -1 is returned.
440 */
findIndexCol(Parse * pParse,ExprList * pList,int iBase,Index * pIdx,int iCol)441 static int findIndexCol(
442 Parse *pParse, /* Parse context */
443 ExprList *pList, /* Expression list to search */
444 int iBase, /* Cursor for table associated with pIdx */
445 Index *pIdx, /* Index to match column of */
446 int iCol /* Column of index to match */
447 ){
448 int i;
449 const char *zColl = pIdx->azColl[iCol];
450
451 for (i=0; i<pList->nExpr; i++) {
452 Expr *p = sqlite3ExprSkipCollate(pList->a[i].pExpr);
453 if (p->op==TK_COLUMN
454 && p->iColumn==pIdx->aiColumn[iCol]
455 && p->iTable==iBase
456 ) {
457 CollSeq *pColl = sqlite3ExprNNCollSeq(pParse, pList->a[i].pExpr);
458 if (0==sqlite3StrICmp(pColl->zName, zColl)) {
459 return i;
460 }
461 }
462 }
463
464 return -1;
465 }
466
467 /*
468 ** Return TRUE if the iCol-th column of index pIdx is NOT NULL
469 */
indexColumnNotNull(Index * pIdx,int iCol)470 static int indexColumnNotNull(Index *pIdx, int iCol){
471 int j;
472 assert( pIdx!=0 );
473 assert( iCol>=0 && iCol<pIdx->nColumn );
474 j = pIdx->aiColumn[iCol];
475 if (j>=0) {
476 return pIdx->pTable->aCol[j].notNull;
477 } else if (j==(-1)) {
478 return 1;
479 } else {
480 assert( j==(-2));
481 return 0; /* Assume an indexed expression can always yield a NULL */
482
483 }
484 }
485
486 /*
487 ** Return true if the DISTINCT expression-list passed as the third argument
488 ** is redundant.
489 **
490 ** A DISTINCT list is redundant if any subset of the columns in the
491 ** DISTINCT list are collectively unique and individually non-null.
492 */
isDistinctRedundant(Parse * pParse,SrcList * pTabList,WhereClause * pWC,ExprList * pDistinct)493 static int isDistinctRedundant(
494 Parse *pParse, /* Parsing context */
495 SrcList *pTabList, /* The FROM clause */
496 WhereClause *pWC, /* The WHERE clause */
497 ExprList *pDistinct /* The result set that needs to be DISTINCT */
498 ){
499 Table *pTab;
500 Index *pIdx;
501 int i;
502 int iBase;
503
504 /* If there is more than one table or sub-select in the FROM clause of
505 ** this query, then it will not be possible to show that the DISTINCT
506 ** clause is redundant. */
507 if (pTabList->nSrc!=1) return 0;
508 iBase = pTabList->a[0].iCursor;
509 pTab = pTabList->a[0].pTab;
510
511 /* If any of the expressions is an IPK column on table iBase, then return
512 ** true. Note: The (p->iTable==iBase) part of this test may be false if the
513 ** current SELECT is a correlated sub-query.
514 */
515 for (i=0; i<pDistinct->nExpr; i++) {
516 Expr *p = sqlite3ExprSkipCollate(pDistinct->a[i].pExpr);
517 if (p->op==TK_COLUMN && p->iTable==iBase && p->iColumn<0) return 1;
518 }
519
520 /* Loop through all indices on the table, checking each to see if it makes
521 ** the DISTINCT qualifier redundant. It does so if:
522 **
523 ** 1. The index is itself UNIQUE, and
524 **
525 ** 2. All of the columns in the index are either part of the pDistinct
526 ** list, or else the WHERE clause contains a term of the form "col=X",
527 ** where X is a constant value. The collation sequences of the
528 ** comparison and select-list expressions must match those of the index.
529 **
530 ** 3. All of those index columns for which the WHERE clause does not
531 ** contain a "col=X" term are subject to a NOT NULL constraint.
532 */
533 for (pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext) {
534 if (!IsUniqueIndex(pIdx)) continue;
535 for (i=0; i<pIdx->nKeyCol; i++) {
536 if (0==sqlite3WhereFindTerm(pWC, iBase, i, ~(Bitmask)0, WO_EQ, pIdx)) {
537 if (findIndexCol(pParse, pDistinct, iBase, pIdx, i)<0) break;
538 if (indexColumnNotNull(pIdx, i)==0) break;
539 }
540 }
541 if (i==pIdx->nKeyCol) {
542 /* This index implies that the DISTINCT qualifier is redundant. */
543 return 1;
544 }
545 }
546
547 return 0;
548 }
549
550
551 /*
552 ** Estimate the logarithm of the input value to base 2.
553 */
estLog(LogEst N)554 static LogEst estLog(LogEst N){
555 return N<=10 ? 0 : sqlite3LogEst(N) - 33;
556 }
557
558 /*
559 ** Convert OP_Column opcodes to OP_Copy in previously generated code.
560 **
561 ** This routine runs over generated VDBE code and translates OP_Column
562 ** opcodes into OP_Copy when the table is being accessed via co-routine
563 ** instead of via table lookup.
564 **
565 ** If the iAutoidxCur is not zero, then any OP_Rowid instructions on
566 ** cursor iTabCur are transformed into OP_Sequence opcode for the
567 ** iAutoidxCur cursor, in order to generate unique rowids for the
568 ** automatic index being generated.
569 */
translateColumnToCopy(Parse * pParse,int iStart,int iTabCur,int iRegister,int iAutoidxCur)570 static void translateColumnToCopy(
571 Parse *pParse, /* Parsing context */
572 int iStart, /* Translate from this opcode to the end */
573 int iTabCur, /* OP_Column/OP_Rowid references to this table */
574 int iRegister, /* The first column is in this register */
575 int iAutoidxCur /* If non-zero, cursor of autoindex being generated */
576 ){
577 Vdbe *v = pParse->pVdbe;
578 VdbeOp *pOp = sqlite3VdbeGetOp(v, iStart);
579 int iEnd = sqlite3VdbeCurrentAddr(v);
580 if (pParse->db->mallocFailed) return;
581 for (; iStart<iEnd; iStart++, pOp++) {
582 if (pOp->p1!=iTabCur) continue;
583 if (pOp->opcode==OP_Column) {
584 pOp->opcode = OP_Copy;
585 pOp->p1 = pOp->p2 + iRegister;
586 pOp->p2 = pOp->p3;
587 pOp->p3 = 0;
588 } else if (pOp->opcode==OP_Rowid) {
589 if (iAutoidxCur) {
590 pOp->opcode = OP_Sequence;
591 pOp->p1 = iAutoidxCur;
592 } else {
593 pOp->opcode = OP_Null;
594 pOp->p1 = 0;
595 pOp->p3 = 0;
596 }
597 }
598 }
599 }
600
601 /*
602 ** Two routines for printing the content of an sqlite3_index_info
603 ** structure. Used for testing and debugging only. If neither
604 ** SQLITE_TEST or SQLITE_DEBUG are defined, then these routines
605 ** are no-ops.
606 */
607 #if !defined(SQLITE_OMIT_VIRTUALTABLE) && defined(WHERETRACE_ENABLED)
TRACE_IDX_INPUTS(sqlite3_index_info * p)608 static void TRACE_IDX_INPUTS(sqlite3_index_info *p){
609 int i;
610 if (!sqlite3WhereTrace) return;
611 for (i=0; i<p->nConstraint; i++) {
612 sqlite3DebugPrintf(" constraint[%d]: col=%d termid=%d op=%d usabled=%d\n",
613 i,
614 p->aConstraint[i].iColumn,
615 p->aConstraint[i].iTermOffset,
616 p->aConstraint[i].op,
617 p->aConstraint[i].usable);
618 }
619 for (i=0; i<p->nOrderBy; i++) {
620 sqlite3DebugPrintf(" orderby[%d]: col=%d desc=%d\n",
621 i,
622 p->aOrderBy[i].iColumn,
623 p->aOrderBy[i].desc);
624 }
625 }
TRACE_IDX_OUTPUTS(sqlite3_index_info * p)626 static void TRACE_IDX_OUTPUTS(sqlite3_index_info *p){
627 int i;
628 if (!sqlite3WhereTrace) return;
629 for (i=0; i<p->nConstraint; i++) {
630 sqlite3DebugPrintf(" usage[%d]: argvIdx=%d omit=%d\n",
631 i,
632 p->aConstraintUsage[i].argvIndex,
633 p->aConstraintUsage[i].omit);
634 }
635 sqlite3DebugPrintf(" idxNum=%d\n", p->idxNum);
636 sqlite3DebugPrintf(" idxStr=%s\n", p->idxStr);
637 sqlite3DebugPrintf(" orderByConsumed=%d\n", p->orderByConsumed);
638 sqlite3DebugPrintf(" estimatedCost=%g\n", p->estimatedCost);
639 sqlite3DebugPrintf(" estimatedRows=%lld\n", p->estimatedRows);
640 }
641 #else
642 #define TRACE_IDX_INPUTS(A)
643 #define TRACE_IDX_OUTPUTS(A)
644 #endif
645
646 #ifndef SQLITE_OMIT_AUTOMATIC_INDEX
647 /*
648 ** Return TRUE if the WHERE clause term pTerm is of a form where it
649 ** could be used with an index to access pSrc, assuming an appropriate
650 ** index existed.
651 */
termCanDriveIndex(WhereTerm * pTerm,struct SrcList_item * pSrc,Bitmask notReady)652 static int termCanDriveIndex(
653 WhereTerm *pTerm, /* WHERE clause term to check */
654 struct SrcList_item *pSrc, /* Table we are trying to access */
655 Bitmask notReady /* Tables in outer loops of the join */
656 ){
657 char aff;
658 if (pTerm->leftCursor!=pSrc->iCursor) return 0;
659 if ((pTerm->eOperator & (WO_EQ|WO_IS))==0) return 0;
660 if ((pSrc->fg.jointype & JT_LEFT)
661 && !ExprHasProperty(pTerm->pExpr, EP_FromJoin)
662 && (pTerm->eOperator & WO_IS)
663 ) {
664 /* Cannot use an IS term from the WHERE clause as an index driver for
665 ** the RHS of a LEFT JOIN. Such a term can only be used if it is from
666 ** the ON clause. */
667 return 0;
668 }
669 if ((pTerm->prereqRight & notReady)!=0) return 0;
670 if (pTerm->u.leftColumn<0) return 0;
671 aff = pSrc->pTab->aCol[pTerm->u.leftColumn].affinity;
672 if (!sqlite3IndexAffinityOk(pTerm->pExpr, aff)) return 0;
673 testcase( pTerm->pExpr->op==TK_IS );
674 return 1;
675 }
676 #endif
677
678
679 #ifndef SQLITE_OMIT_AUTOMATIC_INDEX
680 /*
681 ** Generate code to construct the Index object for an automatic index
682 ** and to set up the WhereLevel object pLevel so that the code generator
683 ** makes use of the automatic index.
684 */
constructAutomaticIndex(Parse * pParse,WhereClause * pWC,struct SrcList_item * pSrc,Bitmask notReady,WhereLevel * pLevel)685 static void constructAutomaticIndex(
686 Parse *pParse, /* The parsing context */
687 WhereClause *pWC, /* The WHERE clause */
688 struct SrcList_item *pSrc, /* The FROM clause term to get the next index */
689 Bitmask notReady, /* Mask of cursors that are not available */
690 WhereLevel *pLevel /* Write new index here */
691 ){
692 int nKeyCol; /* Number of columns in the constructed index */
693 WhereTerm *pTerm; /* A single term of the WHERE clause */
694 WhereTerm *pWCEnd; /* End of pWC->a[] */
695 Index *pIdx; /* Object describing the transient index */
696 Vdbe *v; /* Prepared statement under construction */
697 int addrInit; /* Address of the initialization bypass jump */
698 Table *pTable; /* The table being indexed */
699 int addrTop; /* Top of the index fill loop */
700 int regRecord; /* Register holding an index record */
701 int n; /* Column counter */
702 int i; /* Loop counter */
703 int mxBitCol; /* Maximum column in pSrc->colUsed */
704 CollSeq *pColl; /* Collating sequence to on a column */
705 WhereLoop *pLoop; /* The Loop object */
706 char *zNotUsed; /* Extra space on the end of pIdx */
707 Bitmask idxCols; /* Bitmap of columns used for indexing */
708 Bitmask extraCols; /* Bitmap of additional columns */
709 u8 sentWarning = 0; /* True if a warnning has been issued */
710 Expr *pPartial = 0; /* Partial Index Expression */
711 int iContinue = 0; /* Jump here to skip excluded rows */
712 struct SrcList_item *pTabItem; /* FROM clause term being indexed */
713 int addrCounter = 0; /* Address where integer counter is initialized */
714 int regBase; /* Array of registers where record is assembled */
715
716 /* Generate code to skip over the creation and initialization of the
717 ** transient index on 2nd and subsequent iterations of the loop. */
718 v = pParse->pVdbe;
719 assert( v!=0 );
720 addrInit = sqlite3VdbeAddOp0(v, OP_Once); VdbeCoverage(v);
721
722 /* Count the number of columns that will be added to the index
723 ** and used to match WHERE clause constraints */
724 nKeyCol = 0;
725 pTable = pSrc->pTab;
726 pWCEnd = &pWC->a[pWC->nTerm];
727 pLoop = pLevel->pWLoop;
728 idxCols = 0;
729 for (pTerm=pWC->a; pTerm<pWCEnd; pTerm++) {
730 Expr *pExpr = pTerm->pExpr;
731 assert( !ExprHasProperty(pExpr, EP_FromJoin) /* prereq always non-zero */
732 || pExpr->iRightJoinTable!=pSrc->iCursor /* for the right-hand */
733 || pLoop->prereq!=0 ); /* table of a LEFT JOIN */
734 if (pLoop->prereq==0
735 && (pTerm->wtFlags & TERM_VIRTUAL)==0
736 && !ExprHasProperty(pExpr, EP_FromJoin)
737 && sqlite3ExprIsTableConstant(pExpr, pSrc->iCursor)) {
738 pPartial = sqlite3ExprAnd(pParse, pPartial,
739 sqlite3ExprDup(pParse->db, pExpr, 0));
740 }
741 if (termCanDriveIndex(pTerm, pSrc, notReady)) {
742 int iCol = pTerm->u.leftColumn;
743 Bitmask cMask = iCol>=BMS ? MASKBIT(BMS-1) : MASKBIT(iCol);
744 testcase( iCol==BMS );
745 testcase( iCol==BMS-1 );
746 if (!sentWarning) {
747 sqlite3_log(SQLITE_WARNING_AUTOINDEX,
748 "automatic index on %s(%s)", pTable->zName,
749 pTable->aCol[iCol].zName);
750 sentWarning = 1;
751 }
752 if ((idxCols & cMask)==0) {
753 if (whereLoopResize(pParse->db, pLoop, nKeyCol+1)) {
754 goto end_auto_index_create;
755 }
756 pLoop->aLTerm[nKeyCol++] = pTerm;
757 idxCols |= cMask;
758 }
759 }
760 }
761 assert( nKeyCol>0 );
762 pLoop->u.btree.nEq = pLoop->nLTerm = nKeyCol;
763 pLoop->wsFlags = WHERE_COLUMN_EQ | WHERE_IDX_ONLY | WHERE_INDEXED
764 | WHERE_AUTO_INDEX;
765
766 /* Count the number of additional columns needed to create a
767 ** covering index. A "covering index" is an index that contains all
768 ** columns that are needed by the query. With a covering index, the
769 ** original table never needs to be accessed. Automatic indices must
770 ** be a covering index because the index will not be updated if the
771 ** original table changes and the index and table cannot both be used
772 ** if they go out of sync.
773 */
774 extraCols = pSrc->colUsed & (~idxCols | MASKBIT(BMS-1));
775 mxBitCol = MIN(BMS-1,pTable->nCol);
776 testcase( pTable->nCol==BMS-1 );
777 testcase( pTable->nCol==BMS-2 );
778 for (i=0; i<mxBitCol; i++) {
779 if (extraCols & MASKBIT(i)) nKeyCol++;
780 }
781 if (pSrc->colUsed & MASKBIT(BMS-1)) {
782 nKeyCol += pTable->nCol - BMS + 1;
783 }
784
785 /* Construct the Index object to describe this index */
786 pIdx = sqlite3AllocateIndexObject(pParse->db, nKeyCol+1, 0, &zNotUsed);
787 if (pIdx==0) goto end_auto_index_create;
788 pLoop->u.btree.pIndex = pIdx;
789 pIdx->zName = "auto-index";
790 pIdx->pTable = pTable;
791 n = 0;
792 idxCols = 0;
793 for (pTerm=pWC->a; pTerm<pWCEnd; pTerm++) {
794 if (termCanDriveIndex(pTerm, pSrc, notReady)) {
795 int iCol = pTerm->u.leftColumn;
796 Bitmask cMask = iCol>=BMS ? MASKBIT(BMS-1) : MASKBIT(iCol);
797 testcase( iCol==BMS-1 );
798 testcase( iCol==BMS );
799 if ((idxCols & cMask)==0) {
800 Expr *pX = pTerm->pExpr;
801 idxCols |= cMask;
802 pIdx->aiColumn[n] = pTerm->u.leftColumn;
803 pColl = sqlite3BinaryCompareCollSeq(pParse, pX->pLeft, pX->pRight);
804 pIdx->azColl[n] = pColl ? pColl->zName : sqlite3StrBINARY;
805 n++;
806 }
807 }
808 }
809 assert((u32)n==pLoop->u.btree.nEq );
810
811 /* Add additional columns needed to make the automatic index into
812 ** a covering index */
813 for (i=0; i<mxBitCol; i++) {
814 if (extraCols & MASKBIT(i)) {
815 pIdx->aiColumn[n] = i;
816 pIdx->azColl[n] = sqlite3StrBINARY;
817 n++;
818 }
819 }
820 if (pSrc->colUsed & MASKBIT(BMS-1)) {
821 for (i=BMS-1; i<pTable->nCol; i++) {
822 pIdx->aiColumn[n] = i;
823 pIdx->azColl[n] = sqlite3StrBINARY;
824 n++;
825 }
826 }
827 assert( n==nKeyCol );
828 pIdx->aiColumn[n] = XN_ROWID;
829 pIdx->azColl[n] = sqlite3StrBINARY;
830
831 /* Create the automatic index */
832 assert( pLevel->iIdxCur>=0 );
833 pLevel->iIdxCur = pParse->nTab++;
834 sqlite3VdbeAddOp2(v, OP_OpenAutoindex, pLevel->iIdxCur, nKeyCol+1);
835 sqlite3VdbeSetP4KeyInfo(pParse, pIdx);
836 VdbeComment((v, "for %s", pTable->zName));
837
838 /* Fill the automatic index with content */
839 pTabItem = &pWC->pWInfo->pTabList->a[pLevel->iFrom];
840 if (pTabItem->fg.viaCoroutine) {
841 int regYield = pTabItem->regReturn;
842 addrCounter = sqlite3VdbeAddOp2(v, OP_Integer, 0, 0);
843 sqlite3VdbeAddOp3(v, OP_InitCoroutine, regYield, 0, pTabItem->addrFillSub);
844 addrTop = sqlite3VdbeAddOp1(v, OP_Yield, regYield);
845 VdbeCoverage(v);
846 VdbeComment((v, "next row of %s", pTabItem->pTab->zName));
847 } else {
848 addrTop = sqlite3VdbeAddOp1(v, OP_Rewind, pLevel->iTabCur); VdbeCoverage(v);
849 }
850 if (pPartial) {
851 iContinue = sqlite3VdbeMakeLabel(pParse);
852 sqlite3ExprIfFalse(pParse, pPartial, iContinue, SQLITE_JUMPIFNULL);
853 pLoop->wsFlags |= WHERE_PARTIALIDX;
854 }
855 regRecord = sqlite3GetTempReg(pParse);
856 regBase = sqlite3GenerateIndexKey(
857 pParse, pIdx, pLevel->iTabCur, regRecord, 0, 0, 0, 0
858 );
859 sqlite3VdbeAddOp2(v, OP_IdxInsert, pLevel->iIdxCur, regRecord);
860 sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT);
861 if (pPartial) sqlite3VdbeResolveLabel(v, iContinue);
862 if (pTabItem->fg.viaCoroutine) {
863 sqlite3VdbeChangeP2(v, addrCounter, regBase+n);
864 testcase( pParse->db->mallocFailed );
865 assert( pLevel->iIdxCur>0 );
866 translateColumnToCopy(pParse, addrTop, pLevel->iTabCur,
867 pTabItem->regResult, pLevel->iIdxCur);
868 sqlite3VdbeGoto(v, addrTop);
869 pTabItem->fg.viaCoroutine = 0;
870 } else {
871 sqlite3VdbeAddOp2(v, OP_Next, pLevel->iTabCur, addrTop+1); VdbeCoverage(v);
872 }
873 sqlite3VdbeChangeP5(v, SQLITE_STMTSTATUS_AUTOINDEX);
874 sqlite3VdbeJumpHere(v, addrTop);
875 sqlite3ReleaseTempReg(pParse, regRecord);
876
877 /* Jump here when skipping the initialization */
878 sqlite3VdbeJumpHere(v, addrInit);
879
880 end_auto_index_create:
881 sqlite3ExprDelete(pParse->db, pPartial);
882 }
883 #endif /* SQLITE_OMIT_AUTOMATIC_INDEX */
884
885 #ifndef SQLITE_OMIT_VIRTUALTABLE
886 /*
887 ** Allocate and populate an sqlite3_index_info structure. It is the
888 ** responsibility of the caller to eventually release the structure
889 ** by passing the pointer returned by this function to sqlite3_free().
890 */
allocateIndexInfo(Parse * pParse,WhereClause * pWC,Bitmask mUnusable,struct SrcList_item * pSrc,ExprList * pOrderBy,u16 * pmNoOmit)891 static sqlite3_index_info *allocateIndexInfo(
892 Parse *pParse, /* The parsing context */
893 WhereClause *pWC, /* The WHERE clause being analyzed */
894 Bitmask mUnusable, /* Ignore terms with these prereqs */
895 struct SrcList_item *pSrc, /* The FROM clause term that is the vtab */
896 ExprList *pOrderBy, /* The ORDER BY clause */
897 u16 *pmNoOmit /* Mask of terms not to omit */
898 ){
899 int i, j;
900 int nTerm;
901 struct sqlite3_index_constraint *pIdxCons;
902 struct sqlite3_index_orderby *pIdxOrderBy;
903 struct sqlite3_index_constraint_usage *pUsage;
904 struct HiddenIndexInfo *pHidden;
905 WhereTerm *pTerm;
906 int nOrderBy;
907 sqlite3_index_info *pIdxInfo;
908 u16 mNoOmit = 0;
909
910 /* Count the number of possible WHERE clause constraints referring
911 ** to this virtual table */
912 for (i=nTerm=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++) {
913 if (pTerm->leftCursor != pSrc->iCursor) continue;
914 if (pTerm->prereqRight & mUnusable) continue;
915 assert( IsPowerOfTwo(pTerm->eOperator & ~WO_EQUIV));
916 testcase( pTerm->eOperator & WO_IN );
917 testcase( pTerm->eOperator & WO_ISNULL );
918 testcase( pTerm->eOperator & WO_IS );
919 testcase( pTerm->eOperator & WO_ALL );
920 if ((pTerm->eOperator & ~(WO_EQUIV))==0) continue;
921 if (pTerm->wtFlags & TERM_VNULL) continue;
922 assert( pTerm->u.leftColumn>=(-1));
923 nTerm++;
924 }
925
926 /* If the ORDER BY clause contains only columns in the current
927 ** virtual table then allocate space for the aOrderBy part of
928 ** the sqlite3_index_info structure.
929 */
930 nOrderBy = 0;
931 if (pOrderBy) {
932 int n = pOrderBy->nExpr;
933 for (i=0; i<n; i++) {
934 Expr *pExpr = pOrderBy->a[i].pExpr;
935 if (pExpr->op!=TK_COLUMN || pExpr->iTable!=pSrc->iCursor) break;
936 }
937 if (i==n) {
938 nOrderBy = n;
939 }
940 }
941
942 /* Allocate the sqlite3_index_info structure
943 */
944 pIdxInfo = sqlite3DbMallocZero(pParse->db, sizeof(*pIdxInfo)
945 + (sizeof(*pIdxCons) + sizeof(*pUsage))*nTerm
946 + sizeof(*pIdxOrderBy)*nOrderBy + sizeof(*pHidden));
947 if (pIdxInfo==0) {
948 sqlite3ErrorMsg(pParse, "out of memory");
949 return 0;
950 }
951
952 /* Initialize the structure. The sqlite3_index_info structure contains
953 ** many fields that are declared "const" to prevent xBestIndex from
954 ** changing them. We have to do some funky casting in order to
955 ** initialize those fields.
956 */
957 pHidden = (struct HiddenIndexInfo*)&pIdxInfo[1];
958 pIdxCons = (struct sqlite3_index_constraint*)&pHidden[1];
959 pIdxOrderBy = (struct sqlite3_index_orderby*)&pIdxCons[nTerm];
960 pUsage = (struct sqlite3_index_constraint_usage*)&pIdxOrderBy[nOrderBy];
961 *(int*)&pIdxInfo->nConstraint = nTerm;
962 *(int*)&pIdxInfo->nOrderBy = nOrderBy;
963 *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint = pIdxCons;
964 *(struct sqlite3_index_orderby**)&pIdxInfo->aOrderBy = pIdxOrderBy;
965 *(struct sqlite3_index_constraint_usage**)&pIdxInfo->aConstraintUsage =
966 pUsage;
967
968 pHidden->pWC = pWC;
969 pHidden->pParse = pParse;
970 for (i=j=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++) {
971 u16 op;
972 if (pTerm->leftCursor != pSrc->iCursor) continue;
973 if (pTerm->prereqRight & mUnusable) continue;
974 assert( IsPowerOfTwo(pTerm->eOperator & ~WO_EQUIV));
975 testcase( pTerm->eOperator & WO_IN );
976 testcase( pTerm->eOperator & WO_IS );
977 testcase( pTerm->eOperator & WO_ISNULL );
978 testcase( pTerm->eOperator & WO_ALL );
979 if ((pTerm->eOperator & ~(WO_EQUIV))==0) continue;
980 if (pTerm->wtFlags & TERM_VNULL) continue;
981 if ((pSrc->fg.jointype & JT_LEFT)!=0
982 && !ExprHasProperty(pTerm->pExpr, EP_FromJoin)
983 && (pTerm->eOperator & (WO_IS|WO_ISNULL))
984 ) {
985 /* An "IS" term in the WHERE clause where the virtual table is the rhs
986 ** of a LEFT JOIN. Do not pass this term to the virtual table
987 ** implementation, as this can lead to incorrect results from SQL such
988 ** as:
989 **
990 ** "LEFT JOIN vtab WHERE vtab.col IS NULL" */
991 testcase( pTerm->eOperator & WO_ISNULL );
992 testcase( pTerm->eOperator & WO_IS );
993 continue;
994 }
995 assert( pTerm->u.leftColumn>=(-1));
996 pIdxCons[j].iColumn = pTerm->u.leftColumn;
997 pIdxCons[j].iTermOffset = i;
998 op = pTerm->eOperator & WO_ALL;
999 if (op==WO_IN) op = WO_EQ;
1000 if (op==WO_AUX) {
1001 pIdxCons[j].op = pTerm->eMatchOp;
1002 } else if (op & (WO_ISNULL|WO_IS)) {
1003 if (op==WO_ISNULL) {
1004 pIdxCons[j].op = SQLITE_INDEX_CONSTRAINT_ISNULL;
1005 } else {
1006 pIdxCons[j].op = SQLITE_INDEX_CONSTRAINT_IS;
1007 }
1008 } else {
1009 pIdxCons[j].op = (u8)op;
1010 /* The direct assignment in the previous line is possible only because
1011 ** the WO_ and SQLITE_INDEX_CONSTRAINT_ codes are identical. The
1012 ** following asserts verify this fact. */
1013 assert( WO_EQ==SQLITE_INDEX_CONSTRAINT_EQ );
1014 assert( WO_LT==SQLITE_INDEX_CONSTRAINT_LT );
1015 assert( WO_LE==SQLITE_INDEX_CONSTRAINT_LE );
1016 assert( WO_GT==SQLITE_INDEX_CONSTRAINT_GT );
1017 assert( WO_GE==SQLITE_INDEX_CONSTRAINT_GE );
1018 assert( pTerm->eOperator&(WO_IN|WO_EQ|WO_LT|WO_LE|WO_GT|WO_GE|WO_AUX));
1019
1020 if (op & (WO_LT|WO_LE|WO_GT|WO_GE)
1021 && sqlite3ExprIsVector(pTerm->pExpr->pRight)
1022 ) {
1023 if (i<16) mNoOmit |= (1 << i);
1024 if (op==WO_LT) pIdxCons[j].op = WO_LE;
1025 if (op==WO_GT) pIdxCons[j].op = WO_GE;
1026 }
1027 }
1028
1029 j++;
1030 }
1031 for (i=0; i<nOrderBy; i++) {
1032 Expr *pExpr = pOrderBy->a[i].pExpr;
1033 pIdxOrderBy[i].iColumn = pExpr->iColumn;
1034 pIdxOrderBy[i].desc = pOrderBy->a[i].sortOrder;
1035 }
1036
1037 *pmNoOmit = mNoOmit;
1038 return pIdxInfo;
1039 }
1040
1041 /*
1042 ** The table object reference passed as the second argument to this function
1043 ** must represent a virtual table. This function invokes the xBestIndex()
1044 ** method of the virtual table with the sqlite3_index_info object that
1045 ** comes in as the 3rd argument to this function.
1046 **
1047 ** If an error occurs, pParse is populated with an error message and an
1048 ** appropriate error code is returned. A return of SQLITE_CONSTRAINT from
1049 ** xBestIndex is not considered an error. SQLITE_CONSTRAINT indicates that
1050 ** the current configuration of "unusable" flags in sqlite3_index_info can
1051 ** not result in a valid plan.
1052 **
1053 ** Whether or not an error is returned, it is the responsibility of the
1054 ** caller to eventually free p->idxStr if p->needToFreeIdxStr indicates
1055 ** that this is required.
1056 */
vtabBestIndex(Parse * pParse,Table * pTab,sqlite3_index_info * p)1057 static int vtabBestIndex(Parse *pParse, Table *pTab, sqlite3_index_info *p){
1058 sqlite3_vtab *pVtab = sqlite3GetVTable(pParse->db, pTab)->pVtab;
1059 int rc;
1060
1061 TRACE_IDX_INPUTS(p);
1062 rc = pVtab->pModule->xBestIndex(pVtab, p);
1063 TRACE_IDX_OUTPUTS(p);
1064
1065 if (rc!=SQLITE_OK && rc!=SQLITE_CONSTRAINT) {
1066 if (rc==SQLITE_NOMEM) {
1067 sqlite3OomFault(pParse->db);
1068 } else if (!pVtab->zErrMsg) {
1069 sqlite3ErrorMsg(pParse, "%s", sqlite3ErrStr(rc));
1070 } else {
1071 sqlite3ErrorMsg(pParse, "%s", pVtab->zErrMsg);
1072 }
1073 }
1074 sqlite3_free(pVtab->zErrMsg);
1075 pVtab->zErrMsg = 0;
1076 return rc;
1077 }
1078 #endif /* !defined(SQLITE_OMIT_VIRTUALTABLE) */
1079
1080 #ifdef SQLITE_ENABLE_STAT4
1081 /*
1082 ** Estimate the location of a particular key among all keys in an
1083 ** index. Store the results in aStat as follows:
1084 **
1085 ** aStat[0] Est. number of rows less than pRec
1086 ** aStat[1] Est. number of rows equal to pRec
1087 **
1088 ** Return the index of the sample that is the smallest sample that
1089 ** is greater than or equal to pRec. Note that this index is not an index
1090 ** into the aSample[] array - it is an index into a virtual set of samples
1091 ** based on the contents of aSample[] and the number of fields in record
1092 ** pRec.
1093 */
whereKeyStats(Parse * pParse,Index * pIdx,UnpackedRecord * pRec,int roundUp,tRowcnt * aStat)1094 static int whereKeyStats(
1095 Parse *pParse, /* Database connection */
1096 Index *pIdx, /* Index to consider domain of */
1097 UnpackedRecord *pRec, /* Vector of values to consider */
1098 int roundUp, /* Round up if true. Round down if false */
1099 tRowcnt *aStat /* OUT: stats written here */
1100 ){
1101 IndexSample *aSample = pIdx->aSample;
1102 int iCol; /* Index of required stats in anEq[] etc. */
1103 int i; /* Index of first sample >= pRec */
1104 int iSample; /* Smallest sample larger than or equal to pRec */
1105 int iMin = 0; /* Smallest sample not yet tested */
1106 int iTest; /* Next sample to test */
1107 int res; /* Result of comparison operation */
1108 int nField; /* Number of fields in pRec */
1109 tRowcnt iLower = 0; /* anLt[] + anEq[] of largest sample pRec is > */
1110
1111 #ifndef SQLITE_DEBUG
1112 UNUSED_PARAMETER( pParse );
1113 #endif
1114 assert( pRec!=0 );
1115 assert( pIdx->nSample>0 );
1116 assert( pRec->nField>0 && pRec->nField<=pIdx->nSampleCol );
1117
1118 /* Do a binary search to find the first sample greater than or equal
1119 ** to pRec. If pRec contains a single field, the set of samples to search
1120 ** is simply the aSample[] array. If the samples in aSample[] contain more
1121 ** than one fields, all fields following the first are ignored.
1122 **
1123 ** If pRec contains N fields, where N is more than one, then as well as the
1124 ** samples in aSample[] (truncated to N fields), the search also has to
1125 ** consider prefixes of those samples. For example, if the set of samples
1126 ** in aSample is:
1127 **
1128 ** aSample[0] = (a, 5)
1129 ** aSample[1] = (a, 10)
1130 ** aSample[2] = (b, 5)
1131 ** aSample[3] = (c, 100)
1132 ** aSample[4] = (c, 105)
1133 **
1134 ** Then the search space should ideally be the samples above and the
1135 ** unique prefixes [a], [b] and [c]. But since that is hard to organize,
1136 ** the code actually searches this set:
1137 **
1138 ** 0: (a)
1139 ** 1: (a, 5)
1140 ** 2: (a, 10)
1141 ** 3: (a, 10)
1142 ** 4: (b)
1143 ** 5: (b, 5)
1144 ** 6: (c)
1145 ** 7: (c, 100)
1146 ** 8: (c, 105)
1147 ** 9: (c, 105)
1148 **
1149 ** For each sample in the aSample[] array, N samples are present in the
1150 ** effective sample array. In the above, samples 0 and 1 are based on
1151 ** sample aSample[0]. Samples 2 and 3 on aSample[1] etc.
1152 **
1153 ** Often, sample i of each block of N effective samples has (i+1) fields.
1154 ** Except, each sample may be extended to ensure that it is greater than or
1155 ** equal to the previous sample in the array. For example, in the above,
1156 ** sample 2 is the first sample of a block of N samples, so at first it
1157 ** appears that it should be 1 field in size. However, that would make it
1158 ** smaller than sample 1, so the binary search would not work. As a result,
1159 ** it is extended to two fields. The duplicates that this creates do not
1160 ** cause any problems.
1161 */
1162 nField = pRec->nField;
1163 iCol = 0;
1164 iSample = pIdx->nSample * nField;
1165 do{
1166 int iSamp; /* Index in aSample[] of test sample */
1167 int n; /* Number of fields in test sample */
1168
1169 iTest = (iMin+iSample)/2;
1170 iSamp = iTest / nField;
1171 if (iSamp>0) {
1172 /* The proposed effective sample is a prefix of sample aSample[iSamp].
1173 ** Specifically, the shortest prefix of at least (1 + iTest%nField)
1174 ** fields that is greater than the previous effective sample. */
1175 for (n=(iTest % nField) + 1; n<nField; n++) {
1176 if (aSample[iSamp-1].anLt[n-1]!=aSample[iSamp].anLt[n-1]) break;
1177 }
1178 } else {
1179 n = iTest + 1;
1180 }
1181
1182 pRec->nField = n;
1183 res = sqlite3VdbeRecordCompare(aSample[iSamp].n, aSample[iSamp].p, pRec);
1184 if (res<0) {
1185 iLower = aSample[iSamp].anLt[n-1] + aSample[iSamp].anEq[n-1];
1186 iMin = iTest+1;
1187 } else if (res==0 && n<nField) {
1188 iLower = aSample[iSamp].anLt[n-1];
1189 iMin = iTest+1;
1190 res = -1;
1191 } else {
1192 iSample = iTest;
1193 iCol = n-1;
1194 }
1195 }while (res && iMin<iSample);
1196 i = iSample / nField;
1197
1198 #ifdef SQLITE_DEBUG
1199 /* The following assert statements check that the binary search code
1200 ** above found the right answer. This block serves no purpose other
1201 ** than to invoke the asserts. */
1202 if (pParse->db->mallocFailed==0) {
1203 if (res==0) {
1204 /* If (res==0) is true, then pRec must be equal to sample i. */
1205 assert( i<pIdx->nSample );
1206 assert( iCol==nField-1 );
1207 pRec->nField = nField;
1208 assert( 0==sqlite3VdbeRecordCompare(aSample[i].n, aSample[i].p, pRec)
1209 || pParse->db->mallocFailed
1210 );
1211 } else {
1212 /* Unless i==pIdx->nSample, indicating that pRec is larger than
1213 ** all samples in the aSample[] array, pRec must be smaller than the
1214 ** (iCol+1) field prefix of sample i. */
1215 assert( i<=pIdx->nSample && i>=0 );
1216 pRec->nField = iCol+1;
1217 assert( i==pIdx->nSample
1218 || sqlite3VdbeRecordCompare(aSample[i].n, aSample[i].p, pRec)>0
1219 || pParse->db->mallocFailed );
1220
1221 /* if i==0 and iCol==0, then record pRec is smaller than all samples
1222 ** in the aSample[] array. Otherwise, if (iCol>0) then pRec must
1223 ** be greater than or equal to the (iCol) field prefix of sample i.
1224 ** If (i>0), then pRec must also be greater than sample (i-1). */
1225 if (iCol>0) {
1226 pRec->nField = iCol;
1227 assert( sqlite3VdbeRecordCompare(aSample[i].n, aSample[i].p, pRec)<=0
1228 || pParse->db->mallocFailed );
1229 }
1230 if (i>0) {
1231 pRec->nField = nField;
1232 assert( sqlite3VdbeRecordCompare(aSample[i-1].n, aSample[i-1].p, pRec)<0
1233 || pParse->db->mallocFailed );
1234 }
1235 }
1236 }
1237 #endif /* ifdef SQLITE_DEBUG */
1238
1239 if (res==0) {
1240 /* Record pRec is equal to sample i */
1241 assert( iCol==nField-1 );
1242 aStat[0] = aSample[i].anLt[iCol];
1243 aStat[1] = aSample[i].anEq[iCol];
1244 } else {
1245 /* At this point, the (iCol+1) field prefix of aSample[i] is the first
1246 ** sample that is greater than pRec. Or, if i==pIdx->nSample then pRec
1247 ** is larger than all samples in the array. */
1248 tRowcnt iUpper, iGap;
1249 if (i>=pIdx->nSample) {
1250 iUpper = sqlite3LogEstToInt(pIdx->aiRowLogEst[0]);
1251 } else {
1252 iUpper = aSample[i].anLt[iCol];
1253 }
1254
1255 if (iLower>=iUpper) {
1256 iGap = 0;
1257 } else {
1258 iGap = iUpper - iLower;
1259 }
1260 if (roundUp) {
1261 iGap = (iGap*2)/3;
1262 } else {
1263 iGap = iGap/3;
1264 }
1265 aStat[0] = iLower + iGap;
1266 aStat[1] = pIdx->aAvgEq[nField-1];
1267 }
1268
1269 /* Restore the pRec->nField value before returning. */
1270 pRec->nField = nField;
1271 return i;
1272 }
1273 #endif /* SQLITE_ENABLE_STAT4 */
1274
1275 /*
1276 ** If it is not NULL, pTerm is a term that provides an upper or lower
1277 ** bound on a range scan. Without considering pTerm, it is estimated
1278 ** that the scan will visit nNew rows. This function returns the number
1279 ** estimated to be visited after taking pTerm into account.
1280 **
1281 ** If the user explicitly specified a likelihood() value for this term,
1282 ** then the return value is the likelihood multiplied by the number of
1283 ** input rows. Otherwise, this function assumes that an "IS NOT NULL" term
1284 ** has a likelihood of 0.50, and any other term a likelihood of 0.25.
1285 */
whereRangeAdjust(WhereTerm * pTerm,LogEst nNew)1286 static LogEst whereRangeAdjust(WhereTerm *pTerm, LogEst nNew){
1287 LogEst nRet = nNew;
1288 if (pTerm) {
1289 if (pTerm->truthProb<=0) {
1290 nRet += pTerm->truthProb;
1291 } else if ((pTerm->wtFlags & TERM_VNULL)==0) {
1292 nRet -= 20; assert( 20==sqlite3LogEst(4));
1293 }
1294 }
1295 return nRet;
1296 }
1297
1298
1299 #ifdef SQLITE_ENABLE_STAT4
1300 /*
1301 ** Return the affinity for a single column of an index.
1302 */
sqlite3IndexColumnAffinity(sqlite3 * db,Index * pIdx,int iCol)1303 char sqlite3IndexColumnAffinity(sqlite3 *db, Index *pIdx, int iCol){
1304 assert( iCol>=0 && iCol<pIdx->nColumn );
1305 if (!pIdx->zColAff) {
1306 if (sqlite3IndexAffinityStr(db, pIdx)==0) return SQLITE_AFF_BLOB;
1307 }
1308 assert( pIdx->zColAff[iCol]!=0 );
1309 return pIdx->zColAff[iCol];
1310 }
1311 #endif
1312
1313
1314 #ifdef SQLITE_ENABLE_STAT4
1315 /*
1316 ** This function is called to estimate the number of rows visited by a
1317 ** range-scan on a skip-scan index. For example:
1318 **
1319 ** CREATE INDEX i1 ON t1(a, b, c);
1320 ** SELECT * FROM t1 WHERE a=? AND c BETWEEN ? AND ?;
1321 **
1322 ** Value pLoop->nOut is currently set to the estimated number of rows
1323 ** visited for scanning (a=? AND b=?). This function reduces that estimate
1324 ** by some factor to account for the (c BETWEEN ? AND ?) expression based
1325 ** on the stat4 data for the index. this scan will be peformed multiple
1326 ** times (once for each (a,b) combination that matches a=?) is dealt with
1327 ** by the caller.
1328 **
1329 ** It does this by scanning through all stat4 samples, comparing values
1330 ** extracted from pLower and pUpper with the corresponding column in each
1331 ** sample. If L and U are the number of samples found to be less than or
1332 ** equal to the values extracted from pLower and pUpper respectively, and
1333 ** N is the total number of samples, the pLoop->nOut value is adjusted
1334 ** as follows:
1335 **
1336 ** nOut = nOut * ( min(U - L, 1) / N )
1337 **
1338 ** If pLower is NULL, or a value cannot be extracted from the term, L is
1339 ** set to zero. If pUpper is NULL, or a value cannot be extracted from it,
1340 ** U is set to N.
1341 **
1342 ** Normally, this function sets *pbDone to 1 before returning. However,
1343 ** if no value can be extracted from either pLower or pUpper (and so the
1344 ** estimate of the number of rows delivered remains unchanged), *pbDone
1345 ** is left as is.
1346 **
1347 ** If an error occurs, an SQLite error code is returned. Otherwise,
1348 ** SQLITE_OK.
1349 */
whereRangeSkipScanEst(Parse * pParse,WhereTerm * pLower,WhereTerm * pUpper,WhereLoop * pLoop,int * pbDone)1350 static int whereRangeSkipScanEst(
1351 Parse *pParse, /* Parsing & code generating context */
1352 WhereTerm *pLower, /* Lower bound on the range. ex: "x>123" Might be NULL */
1353 WhereTerm *pUpper, /* Upper bound on the range. ex: "x<455" Might be NULL */
1354 WhereLoop *pLoop, /* Update the .nOut value of this loop */
1355 int *pbDone /* Set to true if at least one expr. value extracted */
1356 ){
1357 Index *p = pLoop->u.btree.pIndex;
1358 int nEq = pLoop->u.btree.nEq;
1359 sqlite3 *db = pParse->db;
1360 int nLower = -1;
1361 int nUpper = p->nSample+1;
1362 int rc = SQLITE_OK;
1363 u8 aff = sqlite3IndexColumnAffinity(db, p, nEq);
1364 CollSeq *pColl;
1365
1366 sqlite3_value *p1 = 0; /* Value extracted from pLower */
1367 sqlite3_value *p2 = 0; /* Value extracted from pUpper */
1368 sqlite3_value *pVal = 0; /* Value extracted from record */
1369
1370 pColl = sqlite3LocateCollSeq(pParse, p->azColl[nEq]);
1371 if (pLower) {
1372 rc = sqlite3Stat4ValueFromExpr(pParse, pLower->pExpr->pRight, aff, &p1);
1373 nLower = 0;
1374 }
1375 if (pUpper && rc==SQLITE_OK) {
1376 rc = sqlite3Stat4ValueFromExpr(pParse, pUpper->pExpr->pRight, aff, &p2);
1377 nUpper = p2 ? 0 : p->nSample;
1378 }
1379
1380 if (p1 || p2) {
1381 int i;
1382 int nDiff;
1383 for (i=0; rc==SQLITE_OK && i<p->nSample; i++) {
1384 rc = sqlite3Stat4Column(db, p->aSample[i].p, p->aSample[i].n, nEq, &pVal);
1385 if (rc==SQLITE_OK && p1) {
1386 int res = sqlite3MemCompare(p1, pVal, pColl);
1387 if (res>=0) nLower++;
1388 }
1389 if (rc==SQLITE_OK && p2) {
1390 int res = sqlite3MemCompare(p2, pVal, pColl);
1391 if (res>=0) nUpper++;
1392 }
1393 }
1394 nDiff = (nUpper - nLower);
1395 if (nDiff<=0) nDiff = 1;
1396
1397 /* If there is both an upper and lower bound specified, and the
1398 ** comparisons indicate that they are close together, use the fallback
1399 ** method (assume that the scan visits 1/64 of the rows) for estimating
1400 ** the number of rows visited. Otherwise, estimate the number of rows
1401 ** using the method described in the header comment for this function. */
1402 if (nDiff!=1 || pUpper==0 || pLower==0) {
1403 int nAdjust = (sqlite3LogEst(p->nSample) - sqlite3LogEst(nDiff));
1404 pLoop->nOut -= nAdjust;
1405 *pbDone = 1;
1406 WHERETRACE(0x10, ("range skip-scan regions: %u..%u adjust=%d est=%d\n",
1407 nLower, nUpper, nAdjust* -1, pLoop->nOut));
1408 }
1409
1410 } else {
1411 assert( *pbDone==0 );
1412 }
1413
1414 sqlite3ValueFree(p1);
1415 sqlite3ValueFree(p2);
1416 sqlite3ValueFree(pVal);
1417
1418 return rc;
1419 }
1420 #endif /* SQLITE_ENABLE_STAT4 */
1421
1422 /*
1423 ** This function is used to estimate the number of rows that will be visited
1424 ** by scanning an index for a range of values. The range may have an upper
1425 ** bound, a lower bound, or both. The WHERE clause terms that set the upper
1426 ** and lower bounds are represented by pLower and pUpper respectively. For
1427 ** example, assuming that index p is on t1(a):
1428 **
1429 ** ... FROM t1 WHERE a > ? AND a < ? ...
1430 ** |_____| |_____|
1431 ** | |
1432 ** pLower pUpper
1433 **
1434 ** If either of the upper or lower bound is not present, then NULL is passed in
1435 ** place of the corresponding WhereTerm.
1436 **
1437 ** The value in (pBuilder->pNew->u.btree.nEq) is the number of the index
1438 ** column subject to the range constraint. Or, equivalently, the number of
1439 ** equality constraints optimized by the proposed index scan. For example,
1440 ** assuming index p is on t1(a, b), and the SQL query is:
1441 **
1442 ** ... FROM t1 WHERE a = ? AND b > ? AND b < ? ...
1443 **
1444 ** then nEq is set to 1 (as the range restricted column, b, is the second
1445 ** left-most column of the index). Or, if the query is:
1446 **
1447 ** ... FROM t1 WHERE a > ? AND a < ? ...
1448 **
1449 ** then nEq is set to 0.
1450 **
1451 ** When this function is called, *pnOut is set to the sqlite3LogEst() of the
1452 ** number of rows that the index scan is expected to visit without
1453 ** considering the range constraints. If nEq is 0, then *pnOut is the number of
1454 ** rows in the index. Assuming no error occurs, *pnOut is adjusted (reduced)
1455 ** to account for the range constraints pLower and pUpper.
1456 **
1457 ** In the absence of sqlite_stat4 ANALYZE data, or if such data cannot be
1458 ** used, a single range inequality reduces the search space by a factor of 4.
1459 ** and a pair of constraints (x>? AND x<?) reduces the expected number of
1460 ** rows visited by a factor of 64.
1461 */
whereRangeScanEst(Parse * pParse,WhereLoopBuilder * pBuilder,WhereTerm * pLower,WhereTerm * pUpper,WhereLoop * pLoop)1462 static int whereRangeScanEst(
1463 Parse *pParse, /* Parsing & code generating context */
1464 WhereLoopBuilder *pBuilder,
1465 WhereTerm *pLower, /* Lower bound on the range. ex: "x>123" Might be NULL */
1466 WhereTerm *pUpper, /* Upper bound on the range. ex: "x<455" Might be NULL */
1467 WhereLoop *pLoop /* Modify the .nOut and maybe .rRun fields */
1468 ){
1469 int rc = SQLITE_OK;
1470 int nOut = pLoop->nOut;
1471 LogEst nNew;
1472
1473 #ifdef SQLITE_ENABLE_STAT4
1474 Index *p = pLoop->u.btree.pIndex;
1475 int nEq = pLoop->u.btree.nEq;
1476
1477 if (p->nSample>0 && ALWAYS(nEq<p->nSampleCol)
1478 && OptimizationEnabled(pParse->db, SQLITE_Stat4)
1479 ) {
1480 if (nEq==pBuilder->nRecValid) {
1481 UnpackedRecord *pRec = pBuilder->pRec;
1482 tRowcnt a[2];
1483 int nBtm = pLoop->u.btree.nBtm;
1484 int nTop = pLoop->u.btree.nTop;
1485
1486 /* Variable iLower will be set to the estimate of the number of rows in
1487 ** the index that are less than the lower bound of the range query. The
1488 ** lower bound being the concatenation of $P and $L, where $P is the
1489 ** key-prefix formed by the nEq values matched against the nEq left-most
1490 ** columns of the index, and $L is the value in pLower.
1491 **
1492 ** Or, if pLower is NULL or $L cannot be extracted from it (because it
1493 ** is not a simple variable or literal value), the lower bound of the
1494 ** range is $P. Due to a quirk in the way whereKeyStats() works, even
1495 ** if $L is available, whereKeyStats() is called for both ($P) and
1496 ** ($P:$L) and the larger of the two returned values is used.
1497 **
1498 ** Similarly, iUpper is to be set to the estimate of the number of rows
1499 ** less than the upper bound of the range query. Where the upper bound
1500 ** is either ($P) or ($P:$U). Again, even if $U is available, both values
1501 ** of iUpper are requested of whereKeyStats() and the smaller used.
1502 **
1503 ** The number of rows between the two bounds is then just iUpper-iLower.
1504 */
1505 tRowcnt iLower; /* Rows less than the lower bound */
1506 tRowcnt iUpper; /* Rows less than the upper bound */
1507 int iLwrIdx = -2; /* aSample[] for the lower bound */
1508 int iUprIdx = -1; /* aSample[] for the upper bound */
1509
1510 if (pRec) {
1511 testcase( pRec->nField!=pBuilder->nRecValid );
1512 pRec->nField = pBuilder->nRecValid;
1513 }
1514 /* Determine iLower and iUpper using ($P) only. */
1515 if (nEq==0) {
1516 iLower = 0;
1517 iUpper = p->nRowEst0;
1518 } else {
1519 /* Note: this call could be optimized away - since the same values must
1520 ** have been requested when testing key $P in whereEqualScanEst(). */
1521 whereKeyStats(pParse, p, pRec, 0, a);
1522 iLower = a[0];
1523 iUpper = a[0] + a[1];
1524 }
1525
1526 assert( pLower==0 || (pLower->eOperator & (WO_GT|WO_GE))!=0 );
1527 assert( pUpper==0 || (pUpper->eOperator & (WO_LT|WO_LE))!=0 );
1528 assert( p->aSortOrder!=0 );
1529 if (p->aSortOrder[nEq]) {
1530 /* The roles of pLower and pUpper are swapped for a DESC index */
1531 SWAP(WhereTerm*, pLower, pUpper);
1532 SWAP(int, nBtm, nTop);
1533 }
1534
1535 /* If possible, improve on the iLower estimate using ($P:$L). */
1536 if (pLower) {
1537 int n; /* Values extracted from pExpr */
1538 Expr *pExpr = pLower->pExpr->pRight;
1539 rc = sqlite3Stat4ProbeSetValue(pParse, p, &pRec, pExpr, nBtm, nEq, &n);
1540 if (rc==SQLITE_OK && n) {
1541 tRowcnt iNew;
1542 u16 mask = WO_GT|WO_LE;
1543 if (sqlite3ExprVectorSize(pExpr)>n) mask = (WO_LE|WO_LT);
1544 iLwrIdx = whereKeyStats(pParse, p, pRec, 0, a);
1545 iNew = a[0] + ((pLower->eOperator & mask) ? a[1] : 0);
1546 if (iNew>iLower) iLower = iNew;
1547 nOut--;
1548 pLower = 0;
1549 }
1550 }
1551
1552 /* If possible, improve on the iUpper estimate using ($P:$U). */
1553 if (pUpper) {
1554 int n; /* Values extracted from pExpr */
1555 Expr *pExpr = pUpper->pExpr->pRight;
1556 rc = sqlite3Stat4ProbeSetValue(pParse, p, &pRec, pExpr, nTop, nEq, &n);
1557 if (rc==SQLITE_OK && n) {
1558 tRowcnt iNew;
1559 u16 mask = WO_GT|WO_LE;
1560 if (sqlite3ExprVectorSize(pExpr)>n) mask = (WO_LE|WO_LT);
1561 iUprIdx = whereKeyStats(pParse, p, pRec, 1, a);
1562 iNew = a[0] + ((pUpper->eOperator & mask) ? a[1] : 0);
1563 if (iNew<iUpper) iUpper = iNew;
1564 nOut--;
1565 pUpper = 0;
1566 }
1567 }
1568
1569 pBuilder->pRec = pRec;
1570 if (rc==SQLITE_OK) {
1571 if (iUpper>iLower) {
1572 nNew = sqlite3LogEst(iUpper - iLower);
1573 /* TUNING: If both iUpper and iLower are derived from the same
1574 ** sample, then assume they are 4x more selective. This brings
1575 ** the estimated selectivity more in line with what it would be
1576 ** if estimated without the use of STAT4 tables. */
1577 if (iLwrIdx==iUprIdx) nNew -= 20; assert( 20==sqlite3LogEst(4));
1578 } else {
1579 nNew = 10; assert( 10==sqlite3LogEst(2));
1580 }
1581 if (nNew<nOut) {
1582 nOut = nNew;
1583 }
1584 WHERETRACE(0x10, ("STAT4 range scan: %u..%u est=%d\n",
1585 (u32)iLower, (u32)iUpper, nOut));
1586 }
1587 } else {
1588 int bDone = 0;
1589 rc = whereRangeSkipScanEst(pParse, pLower, pUpper, pLoop, &bDone);
1590 if (bDone) return rc;
1591 }
1592 }
1593 #else
1594 UNUSED_PARAMETER(pParse);
1595 UNUSED_PARAMETER(pBuilder);
1596 assert( pLower || pUpper );
1597 #endif
1598 assert( pUpper==0 || (pUpper->wtFlags & TERM_VNULL)==0 );
1599 nNew = whereRangeAdjust(pLower, nOut);
1600 nNew = whereRangeAdjust(pUpper, nNew);
1601
1602 /* TUNING: If there is both an upper and lower limit and neither limit
1603 ** has an application-defined likelihood(), assume the range is
1604 ** reduced by an additional 75%. This means that, by default, an open-ended
1605 ** range query (e.g. col > ?) is assumed to match 1/4 of the rows in the
1606 ** index. While a closed range (e.g. col BETWEEN ? AND ?) is estimated to
1607 ** match 1/64 of the index. */
1608 if (pLower && pLower->truthProb>0 && pUpper && pUpper->truthProb>0) {
1609 nNew -= 20;
1610 }
1611
1612 nOut -= (pLower!=0) + (pUpper!=0);
1613 if (nNew<10) nNew = 10;
1614 if (nNew<nOut) nOut = nNew;
1615 #if defined(WHERETRACE_ENABLED)
1616 if (pLoop->nOut>nOut) {
1617 WHERETRACE(0x10,("Range scan lowers nOut from %d to %d\n",
1618 pLoop->nOut, nOut));
1619 }
1620 #endif
1621 pLoop->nOut = (LogEst)nOut;
1622 return rc;
1623 }
1624
1625 #ifdef SQLITE_ENABLE_STAT4
1626 /*
1627 ** Estimate the number of rows that will be returned based on
1628 ** an equality constraint x=VALUE and where that VALUE occurs in
1629 ** the histogram data. This only works when x is the left-most
1630 ** column of an index and sqlite_stat4 histogram data is available
1631 ** for that index. When pExpr==NULL that means the constraint is
1632 ** "x IS NULL" instead of "x=VALUE".
1633 **
1634 ** Write the estimated row count into *pnRow and return SQLITE_OK.
1635 ** If unable to make an estimate, leave *pnRow unchanged and return
1636 ** non-zero.
1637 **
1638 ** This routine can fail if it is unable to load a collating sequence
1639 ** required for string comparison, or if unable to allocate memory
1640 ** for a UTF conversion required for comparison. The error is stored
1641 ** in the pParse structure.
1642 */
whereEqualScanEst(Parse * pParse,WhereLoopBuilder * pBuilder,Expr * pExpr,tRowcnt * pnRow)1643 static int whereEqualScanEst(
1644 Parse *pParse, /* Parsing & code generating context */
1645 WhereLoopBuilder *pBuilder,
1646 Expr *pExpr, /* Expression for VALUE in the x=VALUE constraint */
1647 tRowcnt *pnRow /* Write the revised row estimate here */
1648 ){
1649 Index *p = pBuilder->pNew->u.btree.pIndex;
1650 int nEq = pBuilder->pNew->u.btree.nEq;
1651 UnpackedRecord *pRec = pBuilder->pRec;
1652 int rc; /* Subfunction return code */
1653 tRowcnt a[2]; /* Statistics */
1654 int bOk;
1655
1656 assert( nEq>=1 );
1657 assert( nEq<=p->nColumn );
1658 assert( p->aSample!=0 );
1659 assert( p->nSample>0 );
1660 assert( pBuilder->nRecValid<nEq );
1661
1662 /* If values are not available for all fields of the index to the left
1663 ** of this one, no estimate can be made. Return SQLITE_NOTFOUND. */
1664 if (pBuilder->nRecValid<(nEq-1)) {
1665 return SQLITE_NOTFOUND;
1666 }
1667
1668 /* This is an optimization only. The call to sqlite3Stat4ProbeSetValue()
1669 ** below would return the same value. */
1670 if (nEq>=p->nColumn) {
1671 *pnRow = 1;
1672 return SQLITE_OK;
1673 }
1674
1675 rc = sqlite3Stat4ProbeSetValue(pParse, p, &pRec, pExpr, 1, nEq-1, &bOk);
1676 pBuilder->pRec = pRec;
1677 if (rc!=SQLITE_OK) return rc;
1678 if (bOk==0) return SQLITE_NOTFOUND;
1679 pBuilder->nRecValid = nEq;
1680
1681 whereKeyStats(pParse, p, pRec, 0, a);
1682 WHERETRACE(0x10,("equality scan regions %s(%d): %d\n",
1683 p->zName, nEq-1, (int)a[1]));
1684 *pnRow = a[1];
1685
1686 return rc;
1687 }
1688 #endif /* SQLITE_ENABLE_STAT4 */
1689
1690 #ifdef SQLITE_ENABLE_STAT4
1691 /*
1692 ** Estimate the number of rows that will be returned based on
1693 ** an IN constraint where the right-hand side of the IN operator
1694 ** is a list of values. Example:
1695 **
1696 ** WHERE x IN (1,2,3,4)
1697 **
1698 ** Write the estimated row count into *pnRow and return SQLITE_OK.
1699 ** If unable to make an estimate, leave *pnRow unchanged and return
1700 ** non-zero.
1701 **
1702 ** This routine can fail if it is unable to load a collating sequence
1703 ** required for string comparison, or if unable to allocate memory
1704 ** for a UTF conversion required for comparison. The error is stored
1705 ** in the pParse structure.
1706 */
whereInScanEst(Parse * pParse,WhereLoopBuilder * pBuilder,ExprList * pList,tRowcnt * pnRow)1707 static int whereInScanEst(
1708 Parse *pParse, /* Parsing & code generating context */
1709 WhereLoopBuilder *pBuilder,
1710 ExprList *pList, /* The value list on the RHS of "x IN (v1,v2,v3,...)" */
1711 tRowcnt *pnRow /* Write the revised row estimate here */
1712 ){
1713 Index *p = pBuilder->pNew->u.btree.pIndex;
1714 i64 nRow0 = sqlite3LogEstToInt(p->aiRowLogEst[0]);
1715 int nRecValid = pBuilder->nRecValid;
1716 int rc = SQLITE_OK; /* Subfunction return code */
1717 tRowcnt nEst; /* Number of rows for a single term */
1718 tRowcnt nRowEst = 0; /* New estimate of the number of rows */
1719 int i; /* Loop counter */
1720
1721 assert( p->aSample!=0 );
1722 for (i=0; rc==SQLITE_OK && i<pList->nExpr; i++) {
1723 nEst = nRow0;
1724 rc = whereEqualScanEst(pParse, pBuilder, pList->a[i].pExpr, &nEst);
1725 nRowEst += nEst;
1726 pBuilder->nRecValid = nRecValid;
1727 }
1728
1729 if (rc==SQLITE_OK) {
1730 if (nRowEst > nRow0) nRowEst = nRow0;
1731 *pnRow = nRowEst;
1732 WHERETRACE(0x10,("IN row estimate: est=%d\n", nRowEst));
1733 }
1734 assert( pBuilder->nRecValid==nRecValid );
1735 return rc;
1736 }
1737 #endif /* SQLITE_ENABLE_STAT4 */
1738
1739
1740 #ifdef WHERETRACE_ENABLED
1741 /*
1742 ** Print the content of a WhereTerm object
1743 */
whereTermPrint(WhereTerm * pTerm,int iTerm)1744 static void whereTermPrint(WhereTerm *pTerm, int iTerm){
1745 if (pTerm==0) {
1746 sqlite3DebugPrintf("TERM-%-3d NULL\n", iTerm);
1747 } else {
1748 char zType[4];
1749 char zLeft[50];
1750 memcpy(zType, "...", 4);
1751 if (pTerm->wtFlags & TERM_VIRTUAL) zType[0] = 'V';
1752 if (pTerm->eOperator & WO_EQUIV) zType[1] = 'E';
1753 if (ExprHasProperty(pTerm->pExpr, EP_FromJoin)) zType[2] = 'L';
1754 if (pTerm->eOperator & WO_SINGLE) {
1755 sqlite3_snprintf(sizeof(zLeft),zLeft,"left={%d:%d}",
1756 pTerm->leftCursor, pTerm->u.leftColumn);
1757 } else if ((pTerm->eOperator & WO_OR)!=0 && pTerm->u.pOrInfo!=0) {
1758 sqlite3_snprintf(sizeof(zLeft),zLeft,"indexable=0x%lld",
1759 pTerm->u.pOrInfo->indexable);
1760 } else {
1761 sqlite3_snprintf(sizeof(zLeft),zLeft,"left=%d", pTerm->leftCursor);
1762 }
1763 sqlite3DebugPrintf(
1764 "TERM-%-3d %p %s %-12s prob=%-3d op=0x%03x wtFlags=0x%04x",
1765 iTerm, pTerm, zType, zLeft, pTerm->truthProb,
1766 pTerm->eOperator, pTerm->wtFlags);
1767 if (pTerm->iField) {
1768 sqlite3DebugPrintf(" iField=%d\n", pTerm->iField);
1769 } else {
1770 sqlite3DebugPrintf("\n");
1771 }
1772 sqlite3TreeViewExpr(0, pTerm->pExpr, 0);
1773 }
1774 }
1775 #endif
1776
1777 #ifdef WHERETRACE_ENABLED
1778 /*
1779 ** Show the complete content of a WhereClause
1780 */
sqlite3WhereClausePrint(WhereClause * pWC)1781 void sqlite3WhereClausePrint(WhereClause *pWC){
1782 int i;
1783 for (i=0; i<pWC->nTerm; i++) {
1784 whereTermPrint(&pWC->a[i], i);
1785 }
1786 }
1787 #endif
1788
1789 #ifdef WHERETRACE_ENABLED
1790 /*
1791 ** Print a WhereLoop object for debugging purposes
1792 */
whereLoopPrint(WhereLoop * p,WhereClause * pWC)1793 static void whereLoopPrint(WhereLoop *p, WhereClause *pWC){
1794 WhereInfo *pWInfo = pWC->pWInfo;
1795 int nb = 1+(pWInfo->pTabList->nSrc+3)/4;
1796 struct SrcList_item *pItem = pWInfo->pTabList->a + p->iTab;
1797 Table *pTab = pItem->pTab;
1798 Bitmask mAll = (((Bitmask)1)<<(nb*4)) - 1;
1799 sqlite3DebugPrintf("%c%2d.%0*llx.%0*llx", p->cId,
1800 p->iTab, nb, p->maskSelf, nb, p->prereq & mAll);
1801 sqlite3DebugPrintf(" %12s",
1802 pItem->zAlias ? pItem->zAlias : pTab->zName);
1803 if ((p->wsFlags & WHERE_VIRTUALTABLE)==0) {
1804 const char *zName;
1805 if (p->u.btree.pIndex && (zName = p->u.btree.pIndex->zName)!=0) {
1806 if (strncmp(zName, "sqlite_autoindex_", 17)==0) {
1807 int i = sqlite3Strlen30(zName) - 1;
1808 while (zName[i]!='_') i--;
1809 zName += i;
1810 }
1811 sqlite3DebugPrintf(".%-16s %2d", zName, p->u.btree.nEq);
1812 } else {
1813 sqlite3DebugPrintf("%20s","");
1814 }
1815 } else {
1816 char *z;
1817 if (p->u.vtab.idxStr) {
1818 z = sqlite3_mprintf("(%d,\"%s\",%x)",
1819 p->u.vtab.idxNum, p->u.vtab.idxStr, p->u.vtab.omitMask);
1820 } else {
1821 z = sqlite3_mprintf("(%d,%x)", p->u.vtab.idxNum, p->u.vtab.omitMask);
1822 }
1823 sqlite3DebugPrintf(" %-19s", z);
1824 sqlite3_free(z);
1825 }
1826 if (p->wsFlags & WHERE_SKIPSCAN) {
1827 sqlite3DebugPrintf(" f %05x %d-%d", p->wsFlags, p->nLTerm,p->nSkip);
1828 } else {
1829 sqlite3DebugPrintf(" f %05x N %d", p->wsFlags, p->nLTerm);
1830 }
1831 sqlite3DebugPrintf(" cost %d,%d,%d\n", p->rSetup, p->rRun, p->nOut);
1832 if (p->nLTerm && (sqlite3WhereTrace & 0x100)!=0) {
1833 int i;
1834 for (i=0; i<p->nLTerm; i++) {
1835 whereTermPrint(p->aLTerm[i], i);
1836 }
1837 }
1838 }
1839 #endif
1840
1841 /*
1842 ** Convert bulk memory into a valid WhereLoop that can be passed
1843 ** to whereLoopClear harmlessly.
1844 */
whereLoopInit(WhereLoop * p)1845 static void whereLoopInit(WhereLoop *p){
1846 p->aLTerm = p->aLTermSpace;
1847 p->nLTerm = 0;
1848 p->nLSlot = ArraySize(p->aLTermSpace);
1849 p->wsFlags = 0;
1850 }
1851
1852 /*
1853 ** Clear the WhereLoop.u union. Leave WhereLoop.pLTerm intact.
1854 */
whereLoopClearUnion(sqlite3 * db,WhereLoop * p)1855 static void whereLoopClearUnion(sqlite3 *db, WhereLoop *p){
1856 if (p->wsFlags & (WHERE_VIRTUALTABLE|WHERE_AUTO_INDEX)) {
1857 if ((p->wsFlags & WHERE_VIRTUALTABLE)!=0 && p->u.vtab.needFree) {
1858 sqlite3_free(p->u.vtab.idxStr);
1859 p->u.vtab.needFree = 0;
1860 p->u.vtab.idxStr = 0;
1861 } else if ((p->wsFlags & WHERE_AUTO_INDEX)!=0 && p->u.btree.pIndex!=0) {
1862 sqlite3DbFree(db, p->u.btree.pIndex->zColAff);
1863 sqlite3DbFreeNN(db, p->u.btree.pIndex);
1864 p->u.btree.pIndex = 0;
1865 }
1866 }
1867 }
1868
1869 /*
1870 ** Deallocate internal memory used by a WhereLoop object
1871 */
whereLoopClear(sqlite3 * db,WhereLoop * p)1872 static void whereLoopClear(sqlite3 *db, WhereLoop *p){
1873 if (p->aLTerm!=p->aLTermSpace) sqlite3DbFreeNN(db, p->aLTerm);
1874 whereLoopClearUnion(db, p);
1875 whereLoopInit(p);
1876 }
1877
1878 /*
1879 ** Increase the memory allocation for pLoop->aLTerm[] to be at least n.
1880 */
whereLoopResize(sqlite3 * db,WhereLoop * p,int n)1881 static int whereLoopResize(sqlite3 *db, WhereLoop *p, int n){
1882 WhereTerm **paNew;
1883 if (p->nLSlot>=n) return SQLITE_OK;
1884 n = (n+7)&~7;
1885 paNew = sqlite3DbMallocRawNN(db, sizeof(p->aLTerm[0])*n);
1886 if (paNew==0) return SQLITE_NOMEM_BKPT;
1887 memcpy(paNew, p->aLTerm, sizeof(p->aLTerm[0])*p->nLSlot);
1888 if (p->aLTerm!=p->aLTermSpace) sqlite3DbFreeNN(db, p->aLTerm);
1889 p->aLTerm = paNew;
1890 p->nLSlot = n;
1891 return SQLITE_OK;
1892 }
1893
1894 /*
1895 ** Transfer content from the second pLoop into the first.
1896 */
whereLoopXfer(sqlite3 * db,WhereLoop * pTo,WhereLoop * pFrom)1897 static int whereLoopXfer(sqlite3 *db, WhereLoop *pTo, WhereLoop *pFrom){
1898 whereLoopClearUnion(db, pTo);
1899 if (whereLoopResize(db, pTo, pFrom->nLTerm)) {
1900 memset(&pTo->u, 0, sizeof(pTo->u));
1901 return SQLITE_NOMEM_BKPT;
1902 }
1903 memcpy(pTo, pFrom, WHERE_LOOP_XFER_SZ);
1904 memcpy(pTo->aLTerm, pFrom->aLTerm, pTo->nLTerm*sizeof(pTo->aLTerm[0]));
1905 if (pFrom->wsFlags & WHERE_VIRTUALTABLE) {
1906 pFrom->u.vtab.needFree = 0;
1907 } else if ((pFrom->wsFlags & WHERE_AUTO_INDEX)!=0) {
1908 pFrom->u.btree.pIndex = 0;
1909 }
1910 return SQLITE_OK;
1911 }
1912
1913 /*
1914 ** Delete a WhereLoop object
1915 */
whereLoopDelete(sqlite3 * db,WhereLoop * p)1916 static void whereLoopDelete(sqlite3 *db, WhereLoop *p){
1917 whereLoopClear(db, p);
1918 sqlite3DbFreeNN(db, p);
1919 }
1920
1921 /*
1922 ** Free a WhereInfo structure
1923 */
whereInfoFree(sqlite3 * db,WhereInfo * pWInfo)1924 static void whereInfoFree(sqlite3 *db, WhereInfo *pWInfo){
1925 int i;
1926 assert( pWInfo!=0 );
1927 for (i=0; i<pWInfo->nLevel; i++) {
1928 WhereLevel *pLevel = &pWInfo->a[i];
1929 if (pLevel->pWLoop && (pLevel->pWLoop->wsFlags & WHERE_IN_ABLE)) {
1930 sqlite3DbFree(db, pLevel->u.in.aInLoop);
1931 }
1932 }
1933 sqlite3WhereClauseClear(&pWInfo->sWC);
1934 while (pWInfo->pLoops) {
1935 WhereLoop *p = pWInfo->pLoops;
1936 pWInfo->pLoops = p->pNextLoop;
1937 whereLoopDelete(db, p);
1938 }
1939 sqlite3DbFreeNN(db, pWInfo);
1940 }
1941
1942 /*
1943 ** Return TRUE if all of the following are true:
1944 **
1945 ** (1) X has the same or lower cost that Y
1946 ** (2) X uses fewer WHERE clause terms than Y
1947 ** (3) Every WHERE clause term used by X is also used by Y
1948 ** (4) X skips at least as many columns as Y
1949 ** (5) If X is a covering index, than Y is too
1950 **
1951 ** Conditions (2) and (3) mean that X is a "proper subset" of Y.
1952 ** If X is a proper subset of Y then Y is a better choice and ought
1953 ** to have a lower cost. This routine returns TRUE when that cost
1954 ** relationship is inverted and needs to be adjusted. Constraint (4)
1955 ** was added because if X uses skip-scan less than Y it still might
1956 ** deserve a lower cost even if it is a proper subset of Y. Constraint (5)
1957 ** was added because a covering index probably deserves to have a lower cost
1958 ** than a non-covering index even if it is a proper subset.
1959 */
whereLoopCheaperProperSubset(const WhereLoop * pX,const WhereLoop * pY)1960 static int whereLoopCheaperProperSubset(
1961 const WhereLoop *pX, /* First WhereLoop to compare */
1962 const WhereLoop *pY /* Compare against this WhereLoop */
1963 ){
1964 int i, j;
1965 if (pX->nLTerm-pX->nSkip >= pY->nLTerm-pY->nSkip) {
1966 return 0; /* X is not a subset of Y */
1967 }
1968 if (pY->nSkip > pX->nSkip) return 0;
1969 if (pX->rRun >= pY->rRun) {
1970 if (pX->rRun > pY->rRun) return 0; /* X costs more than Y */
1971 if (pX->nOut > pY->nOut) return 0; /* X costs more than Y */
1972 }
1973 for (i=pX->nLTerm-1; i>=0; i--) {
1974 if (pX->aLTerm[i]==0) continue;
1975 for (j=pY->nLTerm-1; j>=0; j--) {
1976 if (pY->aLTerm[j]==pX->aLTerm[i]) break;
1977 }
1978 if (j<0) return 0; /* X not a subset of Y since term X[i] not used by Y */
1979 }
1980 if ((pX->wsFlags&WHERE_IDX_ONLY)!=0
1981 && (pY->wsFlags&WHERE_IDX_ONLY)==0) {
1982 return 0; /* Constraint (5) */
1983 }
1984 return 1; /* All conditions meet */
1985 }
1986
1987 /*
1988 ** Try to adjust the cost of WhereLoop pTemplate upwards or downwards so
1989 ** that:
1990 **
1991 ** (1) pTemplate costs less than any other WhereLoops that are a proper
1992 ** subset of pTemplate
1993 **
1994 ** (2) pTemplate costs more than any other WhereLoops for which pTemplate
1995 ** is a proper subset.
1996 **
1997 ** To say "WhereLoop X is a proper subset of Y" means that X uses fewer
1998 ** WHERE clause terms than Y and that every WHERE clause term used by X is
1999 ** also used by Y.
2000 */
whereLoopAdjustCost(const WhereLoop * p,WhereLoop * pTemplate)2001 static void whereLoopAdjustCost(const WhereLoop *p, WhereLoop *pTemplate){
2002 if ((pTemplate->wsFlags & WHERE_INDEXED)==0) return;
2003 for (; p; p=p->pNextLoop) {
2004 if (p->iTab!=pTemplate->iTab) continue;
2005 if ((p->wsFlags & WHERE_INDEXED)==0) continue;
2006 if (whereLoopCheaperProperSubset(p, pTemplate)) {
2007 /* Adjust pTemplate cost downward so that it is cheaper than its
2008 ** subset p. */
2009 WHERETRACE(0x80,("subset cost adjustment %d,%d to %d,%d\n",
2010 pTemplate->rRun, pTemplate->nOut, p->rRun, p->nOut-1));
2011 pTemplate->rRun = p->rRun;
2012 pTemplate->nOut = p->nOut - 1;
2013 } else if (whereLoopCheaperProperSubset(pTemplate, p)) {
2014 /* Adjust pTemplate cost upward so that it is costlier than p since
2015 ** pTemplate is a proper subset of p */
2016 WHERETRACE(0x80,("subset cost adjustment %d,%d to %d,%d\n",
2017 pTemplate->rRun, pTemplate->nOut, p->rRun, p->nOut+1));
2018 pTemplate->rRun = p->rRun;
2019 pTemplate->nOut = p->nOut + 1;
2020 }
2021 }
2022 }
2023
2024 /*
2025 ** Search the list of WhereLoops in *ppPrev looking for one that can be
2026 ** replaced by pTemplate.
2027 **
2028 ** Return NULL if pTemplate does not belong on the WhereLoop list.
2029 ** In other words if pTemplate ought to be dropped from further consideration.
2030 **
2031 ** If pX is a WhereLoop that pTemplate can replace, then return the
2032 ** link that points to pX.
2033 **
2034 ** If pTemplate cannot replace any existing element of the list but needs
2035 ** to be added to the list as a new entry, then return a pointer to the
2036 ** tail of the list.
2037 */
whereLoopFindLesser(WhereLoop ** ppPrev,const WhereLoop * pTemplate)2038 static WhereLoop **whereLoopFindLesser(
2039 WhereLoop **ppPrev,
2040 const WhereLoop *pTemplate
2041 ){
2042 WhereLoop *p;
2043 for (p=(*ppPrev); p; ppPrev=&p->pNextLoop, p=*ppPrev) {
2044 if (p->iTab!=pTemplate->iTab || p->iSortIdx!=pTemplate->iSortIdx) {
2045 /* If either the iTab or iSortIdx values for two WhereLoop are different
2046 ** then those WhereLoops need to be considered separately. Neither is
2047 ** a candidate to replace the other. */
2048 continue;
2049 }
2050 /* In the current implementation, the rSetup value is either zero
2051 ** or the cost of building an automatic index (NlogN) and the NlogN
2052 ** is the same for compatible WhereLoops. */
2053 assert( p->rSetup==0 || pTemplate->rSetup==0
2054 || p->rSetup==pTemplate->rSetup );
2055
2056 /* whereLoopAddBtree() always generates and inserts the automatic index
2057 ** case first. Hence compatible candidate WhereLoops never have a larger
2058 ** rSetup. Call this SETUP-INVARIANT */
2059 assert( p->rSetup>=pTemplate->rSetup );
2060
2061 /* Any loop using an appliation-defined index (or PRIMARY KEY or
2062 ** UNIQUE constraint) with one or more == constraints is better
2063 ** than an automatic index. Unless it is a skip-scan. */
2064 if ((p->wsFlags & WHERE_AUTO_INDEX)!=0
2065 && (pTemplate->nSkip)==0
2066 && (pTemplate->wsFlags & WHERE_INDEXED)!=0
2067 && (pTemplate->wsFlags & WHERE_COLUMN_EQ)!=0
2068 && (p->prereq & pTemplate->prereq)==pTemplate->prereq
2069 ) {
2070 break;
2071 }
2072
2073 /* If existing WhereLoop p is better than pTemplate, pTemplate can be
2074 ** discarded. WhereLoop p is better if:
2075 ** (1) p has no more dependencies than pTemplate, and
2076 ** (2) p has an equal or lower cost than pTemplate
2077 */
2078 if ((p->prereq & pTemplate->prereq)==p->prereq /* (1) */
2079 && p->rSetup<=pTemplate->rSetup /* (2a) */
2080 && p->rRun<=pTemplate->rRun /* (2b) */
2081 && p->nOut<=pTemplate->nOut /* (2c) */
2082 ) {
2083 return 0; /* Discard pTemplate */
2084 }
2085
2086 /* If pTemplate is always better than p, then cause p to be overwritten
2087 ** with pTemplate. pTemplate is better than p if:
2088 ** (1) pTemplate has no more dependences than p, and
2089 ** (2) pTemplate has an equal or lower cost than p.
2090 */
2091 if ((p->prereq & pTemplate->prereq)==pTemplate->prereq /* (1) */
2092 && p->rRun>=pTemplate->rRun /* (2a) */
2093 && p->nOut>=pTemplate->nOut /* (2b) */
2094 ) {
2095 assert( p->rSetup>=pTemplate->rSetup ); /* SETUP-INVARIANT above */
2096 break; /* Cause p to be overwritten by pTemplate */
2097 }
2098 }
2099 return ppPrev;
2100 }
2101
2102 /*
2103 ** Insert or replace a WhereLoop entry using the template supplied.
2104 **
2105 ** An existing WhereLoop entry might be overwritten if the new template
2106 ** is better and has fewer dependencies. Or the template will be ignored
2107 ** and no insert will occur if an existing WhereLoop is faster and has
2108 ** fewer dependencies than the template. Otherwise a new WhereLoop is
2109 ** added based on the template.
2110 **
2111 ** If pBuilder->pOrSet is not NULL then we care about only the
2112 ** prerequisites and rRun and nOut costs of the N best loops. That
2113 ** information is gathered in the pBuilder->pOrSet object. This special
2114 ** processing mode is used only for OR clause processing.
2115 **
2116 ** When accumulating multiple loops (when pBuilder->pOrSet is NULL) we
2117 ** still might overwrite similar loops with the new template if the
2118 ** new template is better. Loops may be overwritten if the following
2119 ** conditions are met:
2120 **
2121 ** (1) They have the same iTab.
2122 ** (2) They have the same iSortIdx.
2123 ** (3) The template has same or fewer dependencies than the current loop
2124 ** (4) The template has the same or lower cost than the current loop
2125 */
whereLoopInsert(WhereLoopBuilder * pBuilder,WhereLoop * pTemplate)2126 static int whereLoopInsert(WhereLoopBuilder *pBuilder, WhereLoop *pTemplate){
2127 WhereLoop **ppPrev, *p;
2128 WhereInfo *pWInfo = pBuilder->pWInfo;
2129 sqlite3 *db = pWInfo->pParse->db;
2130 int rc;
2131
2132 /* Stop the search once we hit the query planner search limit */
2133 if (pBuilder->iPlanLimit==0) {
2134 WHERETRACE(0xffffffff,("=== query planner search limit reached ===\n"));
2135 if (pBuilder->pOrSet) pBuilder->pOrSet->n = 0;
2136 return SQLITE_DONE;
2137 }
2138 pBuilder->iPlanLimit--;
2139
2140 /* If pBuilder->pOrSet is defined, then only keep track of the costs
2141 ** and prereqs.
2142 */
2143 if (pBuilder->pOrSet!=0) {
2144 if (pTemplate->nLTerm) {
2145 #if WHERETRACE_ENABLED
2146 u16 n = pBuilder->pOrSet->n;
2147 int x =
2148 #endif
2149 whereOrInsert(pBuilder->pOrSet, pTemplate->prereq, pTemplate->rRun,
2150 pTemplate->nOut);
2151 #if WHERETRACE_ENABLED /* 0x8 */
2152 if (sqlite3WhereTrace & 0x8) {
2153 sqlite3DebugPrintf(x?" or-%d: ":" or-X: ", n);
2154 whereLoopPrint(pTemplate, pBuilder->pWC);
2155 }
2156 #endif
2157 }
2158 return SQLITE_OK;
2159 }
2160
2161 /* Look for an existing WhereLoop to replace with pTemplate
2162 */
2163 whereLoopAdjustCost(pWInfo->pLoops, pTemplate);
2164 ppPrev = whereLoopFindLesser(&pWInfo->pLoops, pTemplate);
2165
2166 if (ppPrev==0) {
2167 /* There already exists a WhereLoop on the list that is better
2168 ** than pTemplate, so just ignore pTemplate */
2169 #if WHERETRACE_ENABLED /* 0x8 */
2170 if (sqlite3WhereTrace & 0x8) {
2171 sqlite3DebugPrintf(" skip: ");
2172 whereLoopPrint(pTemplate, pBuilder->pWC);
2173 }
2174 #endif
2175 return SQLITE_OK;
2176 } else {
2177 p = *ppPrev;
2178 }
2179
2180 /* If we reach this point it means that either p[] should be overwritten
2181 ** with pTemplate[] if p[] exists, or if p==NULL then allocate a new
2182 ** WhereLoop and insert it.
2183 */
2184 #if WHERETRACE_ENABLED /* 0x8 */
2185 if (sqlite3WhereTrace & 0x8) {
2186 if (p!=0) {
2187 sqlite3DebugPrintf("replace: ");
2188 whereLoopPrint(p, pBuilder->pWC);
2189 sqlite3DebugPrintf(" with: ");
2190 } else {
2191 sqlite3DebugPrintf(" add: ");
2192 }
2193 whereLoopPrint(pTemplate, pBuilder->pWC);
2194 }
2195 #endif
2196 if (p==0) {
2197 /* Allocate a new WhereLoop to add to the end of the list */
2198 *ppPrev = p = sqlite3DbMallocRawNN(db, sizeof(WhereLoop));
2199 if (p==0) return SQLITE_NOMEM_BKPT;
2200 whereLoopInit(p);
2201 p->pNextLoop = 0;
2202 } else {
2203 /* We will be overwriting WhereLoop p[]. But before we do, first
2204 ** go through the rest of the list and delete any other entries besides
2205 ** p[] that are also supplated by pTemplate */
2206 WhereLoop **ppTail = &p->pNextLoop;
2207 WhereLoop *pToDel;
2208 while (*ppTail) {
2209 ppTail = whereLoopFindLesser(ppTail, pTemplate);
2210 if (ppTail==0) break;
2211 pToDel = *ppTail;
2212 if (pToDel==0) break;
2213 *ppTail = pToDel->pNextLoop;
2214 #if WHERETRACE_ENABLED /* 0x8 */
2215 if (sqlite3WhereTrace & 0x8) {
2216 sqlite3DebugPrintf(" delete: ");
2217 whereLoopPrint(pToDel, pBuilder->pWC);
2218 }
2219 #endif
2220 whereLoopDelete(db, pToDel);
2221 }
2222 }
2223 rc = whereLoopXfer(db, p, pTemplate);
2224 if ((p->wsFlags & WHERE_VIRTUALTABLE)==0) {
2225 Index *pIndex = p->u.btree.pIndex;
2226 if (pIndex && pIndex->idxType==SQLITE_IDXTYPE_IPK) {
2227 p->u.btree.pIndex = 0;
2228 }
2229 }
2230 return rc;
2231 }
2232
2233 /*
2234 ** Adjust the WhereLoop.nOut value downward to account for terms of the
2235 ** WHERE clause that reference the loop but which are not used by an
2236 ** index.
2237 *
2238 ** For every WHERE clause term that is not used by the index
2239 ** and which has a truth probability assigned by one of the likelihood(),
2240 ** likely(), or unlikely() SQL functions, reduce the estimated number
2241 ** of output rows by the probability specified.
2242 **
2243 ** TUNING: For every WHERE clause term that is not used by the index
2244 ** and which does not have an assigned truth probability, heuristics
2245 ** described below are used to try to estimate the truth probability.
2246 ** TODO --> Perhaps this is something that could be improved by better
2247 ** table statistics.
2248 **
2249 ** Heuristic 1: Estimate the truth probability as 93.75%. The 93.75%
2250 ** value corresponds to -1 in LogEst notation, so this means decrement
2251 ** the WhereLoop.nOut field for every such WHERE clause term.
2252 **
2253 ** Heuristic 2: If there exists one or more WHERE clause terms of the
2254 ** form "x==EXPR" and EXPR is not a constant 0 or 1, then make sure the
2255 ** final output row estimate is no greater than 1/4 of the total number
2256 ** of rows in the table. In other words, assume that x==EXPR will filter
2257 ** out at least 3 out of 4 rows. If EXPR is -1 or 0 or 1, then maybe the
2258 ** "x" column is boolean or else -1 or 0 or 1 is a common default value
2259 ** on the "x" column and so in that case only cap the output row estimate
2260 ** at 1/2 instead of 1/4.
2261 */
whereLoopOutputAdjust(WhereClause * pWC,WhereLoop * pLoop,LogEst nRow)2262 static void whereLoopOutputAdjust(
2263 WhereClause *pWC, /* The WHERE clause */
2264 WhereLoop *pLoop, /* The loop to adjust downward */
2265 LogEst nRow /* Number of rows in the entire table */
2266 ){
2267 WhereTerm *pTerm, *pX;
2268 Bitmask notAllowed = ~(pLoop->prereq|pLoop->maskSelf);
2269 int i, j, k;
2270 LogEst iReduce = 0; /* pLoop->nOut should not exceed nRow-iReduce */
2271
2272 assert((pLoop->wsFlags & WHERE_AUTO_INDEX)==0 );
2273 for (i=pWC->nTerm, pTerm=pWC->a; i>0; i--, pTerm++) {
2274 assert( pTerm!=0 );
2275 if ((pTerm->wtFlags & TERM_VIRTUAL)!=0) break;
2276 if ((pTerm->prereqAll & pLoop->maskSelf)==0) continue;
2277 if ((pTerm->prereqAll & notAllowed)!=0) continue;
2278 for (j=pLoop->nLTerm-1; j>=0; j--) {
2279 pX = pLoop->aLTerm[j];
2280 if (pX==0) continue;
2281 if (pX==pTerm) break;
2282 if (pX->iParent>=0 && (&pWC->a[pX->iParent])==pTerm) break;
2283 }
2284 if (j<0) {
2285 if (pTerm->truthProb<=0) {
2286 /* If a truth probability is specified using the likelihood() hints,
2287 ** then use the probability provided by the application. */
2288 pLoop->nOut += pTerm->truthProb;
2289 } else {
2290 /* In the absence of explicit truth probabilities, use heuristics to
2291 ** guess a reasonable truth probability. */
2292 pLoop->nOut--;
2293 if (pTerm->eOperator&(WO_EQ|WO_IS)) {
2294 Expr *pRight = pTerm->pExpr->pRight;
2295 testcase( pTerm->pExpr->op==TK_IS );
2296 if (sqlite3ExprIsInteger(pRight, &k) && k>=(-1) && k<=1) {
2297 k = 10;
2298 } else {
2299 k = 20;
2300 }
2301 if (iReduce<k) iReduce = k;
2302 }
2303 }
2304 }
2305 }
2306 if (pLoop->nOut > nRow-iReduce) pLoop->nOut = nRow - iReduce;
2307 }
2308
2309 /*
2310 ** Term pTerm is a vector range comparison operation. The first comparison
2311 ** in the vector can be optimized using column nEq of the index. This
2312 ** function returns the total number of vector elements that can be used
2313 ** as part of the range comparison.
2314 **
2315 ** For example, if the query is:
2316 **
2317 ** WHERE a = ? AND (b, c, d) > (?, ?, ?)
2318 **
2319 ** and the index:
2320 **
2321 ** CREATE INDEX ... ON (a, b, c, d, e)
2322 **
2323 ** then this function would be invoked with nEq=1. The value returned in
2324 ** this case is 3.
2325 */
whereRangeVectorLen(Parse * pParse,int iCur,Index * pIdx,int nEq,WhereTerm * pTerm)2326 static int whereRangeVectorLen(
2327 Parse *pParse, /* Parsing context */
2328 int iCur, /* Cursor open on pIdx */
2329 Index *pIdx, /* The index to be used for a inequality constraint */
2330 int nEq, /* Number of prior equality constraints on same index */
2331 WhereTerm *pTerm /* The vector inequality constraint */
2332 ){
2333 int nCmp = sqlite3ExprVectorSize(pTerm->pExpr->pLeft);
2334 int i;
2335
2336 nCmp = MIN(nCmp, (pIdx->nColumn - nEq));
2337 for (i=1; i<nCmp; i++) {
2338 /* Test if comparison i of pTerm is compatible with column (i+nEq)
2339 ** of the index. If not, exit the loop. */
2340 char aff; /* Comparison affinity */
2341 char idxaff = 0; /* Indexed columns affinity */
2342 CollSeq *pColl; /* Comparison collation sequence */
2343 Expr *pLhs = pTerm->pExpr->pLeft->x.pList->a[i].pExpr;
2344 Expr *pRhs = pTerm->pExpr->pRight;
2345 if (pRhs->flags & EP_xIsSelect) {
2346 pRhs = pRhs->x.pSelect->pEList->a[i].pExpr;
2347 } else {
2348 pRhs = pRhs->x.pList->a[i].pExpr;
2349 }
2350
2351 /* Check that the LHS of the comparison is a column reference to
2352 ** the right column of the right source table. And that the sort
2353 ** order of the index column is the same as the sort order of the
2354 ** leftmost index column. */
2355 if (pLhs->op!=TK_COLUMN
2356 || pLhs->iTable!=iCur
2357 || pLhs->iColumn!=pIdx->aiColumn[i+nEq]
2358 || pIdx->aSortOrder[i+nEq]!=pIdx->aSortOrder[nEq]
2359 ) {
2360 break;
2361 }
2362
2363 testcase( pLhs->iColumn==XN_ROWID );
2364 aff = sqlite3CompareAffinity(pRhs, sqlite3ExprAffinity(pLhs));
2365 idxaff = sqlite3TableColumnAffinity(pIdx->pTable, pLhs->iColumn);
2366 if (aff!=idxaff) break;
2367
2368 pColl = sqlite3BinaryCompareCollSeq(pParse, pLhs, pRhs);
2369 if (pColl==0) break;
2370 if (sqlite3StrICmp(pColl->zName, pIdx->azColl[i+nEq])) break;
2371 }
2372 return i;
2373 }
2374
2375 /*
2376 ** Adjust the cost C by the costMult facter T. This only occurs if
2377 ** compiled with -DSQLITE_ENABLE_COSTMULT
2378 */
2379 #ifdef SQLITE_ENABLE_COSTMULT
2380 # define ApplyCostMultiplier(C,T) C += T
2381 #else
2382 # define ApplyCostMultiplier(C,T)
2383 #endif
2384
2385 /*
2386 ** We have so far matched pBuilder->pNew->u.btree.nEq terms of the
2387 ** index pIndex. Try to match one more.
2388 **
2389 ** When this function is called, pBuilder->pNew->nOut contains the
2390 ** number of rows expected to be visited by filtering using the nEq
2391 ** terms only. If it is modified, this value is restored before this
2392 ** function returns.
2393 **
2394 ** If pProbe->idxType==SQLITE_IDXTYPE_IPK, that means pIndex is
2395 ** a fake index used for the INTEGER PRIMARY KEY.
2396 */
whereLoopAddBtreeIndex(WhereLoopBuilder * pBuilder,struct SrcList_item * pSrc,Index * pProbe,LogEst nInMul)2397 static int whereLoopAddBtreeIndex(
2398 WhereLoopBuilder *pBuilder, /* The WhereLoop factory */
2399 struct SrcList_item *pSrc, /* FROM clause term being analyzed */
2400 Index *pProbe, /* An index on pSrc */
2401 LogEst nInMul /* log(Number of iterations due to IN) */
2402 ){
2403 WhereInfo *pWInfo = pBuilder->pWInfo; /* WHERE analyse context */
2404 Parse *pParse = pWInfo->pParse; /* Parsing context */
2405 sqlite3 *db = pParse->db; /* Database connection malloc context */
2406 WhereLoop *pNew; /* Template WhereLoop under construction */
2407 WhereTerm *pTerm; /* A WhereTerm under consideration */
2408 int opMask; /* Valid operators for constraints */
2409 WhereScan scan; /* Iterator for WHERE terms */
2410 Bitmask saved_prereq; /* Original value of pNew->prereq */
2411 u16 saved_nLTerm; /* Original value of pNew->nLTerm */
2412 u16 saved_nEq; /* Original value of pNew->u.btree.nEq */
2413 u16 saved_nBtm; /* Original value of pNew->u.btree.nBtm */
2414 u16 saved_nTop; /* Original value of pNew->u.btree.nTop */
2415 u16 saved_nSkip; /* Original value of pNew->nSkip */
2416 u32 saved_wsFlags; /* Original value of pNew->wsFlags */
2417 LogEst saved_nOut; /* Original value of pNew->nOut */
2418 int rc = SQLITE_OK; /* Return code */
2419 LogEst rSize; /* Number of rows in the table */
2420 LogEst rLogSize; /* Logarithm of table size */
2421 WhereTerm *pTop = 0, *pBtm = 0; /* Top and bottom range constraints */
2422
2423 pNew = pBuilder->pNew;
2424 if (db->mallocFailed) return SQLITE_NOMEM_BKPT;
2425 WHERETRACE(0x800, ("BEGIN %s.addBtreeIdx(%s), nEq=%d\n",
2426 pProbe->pTable->zName,pProbe->zName, pNew->u.btree.nEq));
2427
2428 assert((pNew->wsFlags & WHERE_VIRTUALTABLE)==0 );
2429 assert((pNew->wsFlags & WHERE_TOP_LIMIT)==0 );
2430 if (pNew->wsFlags & WHERE_BTM_LIMIT) {
2431 opMask = WO_LT|WO_LE;
2432 } else {
2433 assert( pNew->u.btree.nBtm==0 );
2434 opMask = WO_EQ|WO_IN|WO_GT|WO_GE|WO_LT|WO_LE|WO_ISNULL|WO_IS;
2435 }
2436 if (pProbe->bUnordered) opMask &= ~(WO_GT|WO_GE|WO_LT|WO_LE);
2437
2438 assert( pNew->u.btree.nEq<pProbe->nColumn );
2439
2440 saved_nEq = pNew->u.btree.nEq;
2441 saved_nBtm = pNew->u.btree.nBtm;
2442 saved_nTop = pNew->u.btree.nTop;
2443 saved_nSkip = pNew->nSkip;
2444 saved_nLTerm = pNew->nLTerm;
2445 saved_wsFlags = pNew->wsFlags;
2446 saved_prereq = pNew->prereq;
2447 saved_nOut = pNew->nOut;
2448 pTerm = whereScanInit(&scan, pBuilder->pWC, pSrc->iCursor, saved_nEq,
2449 opMask, pProbe);
2450 pNew->rSetup = 0;
2451 rSize = pProbe->aiRowLogEst[0];
2452 rLogSize = estLog(rSize);
2453 for (; rc==SQLITE_OK && pTerm!=0; pTerm = whereScanNext(&scan)) {
2454 u16 eOp = pTerm->eOperator; /* Shorthand for pTerm->eOperator */
2455 LogEst rCostIdx;
2456 LogEst nOutUnadjusted; /* nOut before IN() and WHERE adjustments */
2457 int nIn = 0;
2458 #ifdef SQLITE_ENABLE_STAT4
2459 int nRecValid = pBuilder->nRecValid;
2460 #endif
2461 if ((eOp==WO_ISNULL || (pTerm->wtFlags&TERM_VNULL)!=0)
2462 && indexColumnNotNull(pProbe, saved_nEq)
2463 ) {
2464 continue; /* ignore IS [NOT] NULL constraints on NOT NULL columns */
2465 }
2466 if (pTerm->prereqRight & pNew->maskSelf) continue;
2467
2468 /* Do not allow the upper bound of a LIKE optimization range constraint
2469 ** to mix with a lower range bound from some other source */
2470 if (pTerm->wtFlags & TERM_LIKEOPT && pTerm->eOperator==WO_LT) continue;
2471
2472 /* Do not allow constraints from the WHERE clause to be used by the
2473 ** right table of a LEFT JOIN. Only constraints in the ON clause are
2474 ** allowed */
2475 if ((pSrc->fg.jointype & JT_LEFT)!=0
2476 && !ExprHasProperty(pTerm->pExpr, EP_FromJoin)
2477 ) {
2478 continue;
2479 }
2480
2481 if (IsUniqueIndex(pProbe) && saved_nEq==pProbe->nKeyCol-1) {
2482 pBuilder->bldFlags |= SQLITE_BLDF_UNIQUE;
2483 } else {
2484 pBuilder->bldFlags |= SQLITE_BLDF_INDEXED;
2485 }
2486 pNew->wsFlags = saved_wsFlags;
2487 pNew->u.btree.nEq = saved_nEq;
2488 pNew->u.btree.nBtm = saved_nBtm;
2489 pNew->u.btree.nTop = saved_nTop;
2490 pNew->nLTerm = saved_nLTerm;
2491 if (whereLoopResize(db, pNew, pNew->nLTerm+1)) break; /* OOM */
2492 pNew->aLTerm[pNew->nLTerm++] = pTerm;
2493 pNew->prereq = (saved_prereq | pTerm->prereqRight) & ~pNew->maskSelf;
2494
2495 assert( nInMul==0
2496 || (pNew->wsFlags & WHERE_COLUMN_NULL)!=0
2497 || (pNew->wsFlags & WHERE_COLUMN_IN)!=0
2498 || (pNew->wsFlags & WHERE_SKIPSCAN)!=0
2499 );
2500
2501 if (eOp & WO_IN) {
2502 Expr *pExpr = pTerm->pExpr;
2503 if (ExprHasProperty(pExpr, EP_xIsSelect)) {
2504 /* "x IN (SELECT ...)": TUNING: the SELECT returns 25 rows */
2505 int i;
2506 nIn = 46; assert( 46==sqlite3LogEst(25));
2507
2508 /* The expression may actually be of the form (x, y) IN (SELECT...).
2509 ** In this case there is a separate term for each of (x) and (y).
2510 ** However, the nIn multiplier should only be applied once, not once
2511 ** for each such term. The following loop checks that pTerm is the
2512 ** first such term in use, and sets nIn back to 0 if it is not. */
2513 for (i=0; i<pNew->nLTerm-1; i++) {
2514 if (pNew->aLTerm[i] && pNew->aLTerm[i]->pExpr==pExpr) nIn = 0;
2515 }
2516 } else if (ALWAYS(pExpr->x.pList && pExpr->x.pList->nExpr)) {
2517 /* "x IN (value, value, ...)" */
2518 nIn = sqlite3LogEst(pExpr->x.pList->nExpr);
2519 assert( nIn>0 ); /* RHS always has 2 or more terms... The parser
2520 ** changes "x IN (?)" into "x=?". */
2521 }
2522 if (pProbe->hasStat1) {
2523 LogEst M, logK, safetyMargin;
2524 /* Let:
2525 ** N = the total number of rows in the table
2526 ** K = the number of entries on the RHS of the IN operator
2527 ** M = the number of rows in the table that match terms to the
2528 ** to the left in the same index. If the IN operator is on
2529 ** the left-most index column, M==N.
2530 **
2531 ** Given the definitions above, it is better to omit the IN operator
2532 ** from the index lookup and instead do a scan of the M elements,
2533 ** testing each scanned row against the IN operator separately, if:
2534 **
2535 ** M*log(K) < K*log(N)
2536 **
2537 ** Our estimates for M, K, and N might be inaccurate, so we build in
2538 ** a safety margin of 2 (LogEst: 10) that favors using the IN operator
2539 ** with the index, as using an index has better worst-case behavior.
2540 ** If we do not have real sqlite_stat1 data, always prefer to use
2541 ** the index.
2542 */
2543 M = pProbe->aiRowLogEst[saved_nEq];
2544 logK = estLog(nIn);
2545 safetyMargin = 10; /* TUNING: extra weight for indexed IN */
2546 if (M + logK + safetyMargin < nIn + rLogSize) {
2547 WHERETRACE(0x40,
2548 ("Scan preferred over IN operator on column %d of \"%s\" (%d<%d)\n",
2549 saved_nEq, pProbe->zName, M+logK+10, nIn+rLogSize));
2550 continue;
2551 } else {
2552 WHERETRACE(0x40,
2553 ("IN operator preferred on column %d of \"%s\" (%d>=%d)\n",
2554 saved_nEq, pProbe->zName, M+logK+10, nIn+rLogSize));
2555 }
2556 }
2557 pNew->wsFlags |= WHERE_COLUMN_IN;
2558 } else if (eOp & (WO_EQ|WO_IS)) {
2559 int iCol = pProbe->aiColumn[saved_nEq];
2560 pNew->wsFlags |= WHERE_COLUMN_EQ;
2561 assert( saved_nEq==pNew->u.btree.nEq );
2562 if (iCol==XN_ROWID
2563 || (iCol>=0 && nInMul==0 && saved_nEq==pProbe->nKeyCol-1)
2564 ) {
2565 if (iCol==XN_ROWID || pProbe->uniqNotNull
2566 || (pProbe->nKeyCol==1 && pProbe->onError && eOp==WO_EQ)
2567 ) {
2568 pNew->wsFlags |= WHERE_ONEROW;
2569 } else {
2570 pNew->wsFlags |= WHERE_UNQ_WANTED;
2571 }
2572 }
2573 } else if (eOp & WO_ISNULL) {
2574 pNew->wsFlags |= WHERE_COLUMN_NULL;
2575 } else if (eOp & (WO_GT|WO_GE)) {
2576 testcase( eOp & WO_GT );
2577 testcase( eOp & WO_GE );
2578 pNew->wsFlags |= WHERE_COLUMN_RANGE|WHERE_BTM_LIMIT;
2579 pNew->u.btree.nBtm = whereRangeVectorLen(
2580 pParse, pSrc->iCursor, pProbe, saved_nEq, pTerm
2581 );
2582 pBtm = pTerm;
2583 pTop = 0;
2584 if (pTerm->wtFlags & TERM_LIKEOPT) {
2585 /* Range contraints that come from the LIKE optimization are
2586 ** always used in pairs. */
2587 pTop = &pTerm[1];
2588 assert((pTop-(pTerm->pWC->a))<pTerm->pWC->nTerm );
2589 assert( pTop->wtFlags & TERM_LIKEOPT );
2590 assert( pTop->eOperator==WO_LT );
2591 if (whereLoopResize(db, pNew, pNew->nLTerm+1)) break; /* OOM */
2592 pNew->aLTerm[pNew->nLTerm++] = pTop;
2593 pNew->wsFlags |= WHERE_TOP_LIMIT;
2594 pNew->u.btree.nTop = 1;
2595 }
2596 } else {
2597 assert( eOp & (WO_LT|WO_LE));
2598 testcase( eOp & WO_LT );
2599 testcase( eOp & WO_LE );
2600 pNew->wsFlags |= WHERE_COLUMN_RANGE|WHERE_TOP_LIMIT;
2601 pNew->u.btree.nTop = whereRangeVectorLen(
2602 pParse, pSrc->iCursor, pProbe, saved_nEq, pTerm
2603 );
2604 pTop = pTerm;
2605 pBtm = (pNew->wsFlags & WHERE_BTM_LIMIT)!=0 ?
2606 pNew->aLTerm[pNew->nLTerm-2] : 0;
2607 }
2608
2609 /* At this point pNew->nOut is set to the number of rows expected to
2610 ** be visited by the index scan before considering term pTerm, or the
2611 ** values of nIn and nInMul. In other words, assuming that all
2612 ** "x IN(...)" terms are replaced with "x = ?". This block updates
2613 ** the value of pNew->nOut to account for pTerm (but not nIn/nInMul). */
2614 assert( pNew->nOut==saved_nOut );
2615 if (pNew->wsFlags & WHERE_COLUMN_RANGE) {
2616 /* Adjust nOut using stat4 data. Or, if there is no stat4
2617 ** data, using some other estimate. */
2618 whereRangeScanEst(pParse, pBuilder, pBtm, pTop, pNew);
2619 } else {
2620 int nEq = ++pNew->u.btree.nEq;
2621 assert( eOp & (WO_ISNULL|WO_EQ|WO_IN|WO_IS));
2622
2623 assert( pNew->nOut==saved_nOut );
2624 if (pTerm->truthProb<=0 && pProbe->aiColumn[saved_nEq]>=0) {
2625 assert((eOp & WO_IN) || nIn==0 );
2626 testcase( eOp & WO_IN );
2627 pNew->nOut += pTerm->truthProb;
2628 pNew->nOut -= nIn;
2629 } else {
2630 #ifdef SQLITE_ENABLE_STAT4
2631 tRowcnt nOut = 0;
2632 if (nInMul==0
2633 && pProbe->nSample
2634 && pNew->u.btree.nEq<=pProbe->nSampleCol
2635 && ((eOp & WO_IN)==0 || !ExprHasProperty(pTerm->pExpr, EP_xIsSelect))
2636 && OptimizationEnabled(db, SQLITE_Stat4)
2637 ) {
2638 Expr *pExpr = pTerm->pExpr;
2639 if ((eOp & (WO_EQ|WO_ISNULL|WO_IS))!=0) {
2640 testcase( eOp & WO_EQ );
2641 testcase( eOp & WO_IS );
2642 testcase( eOp & WO_ISNULL );
2643 rc = whereEqualScanEst(pParse, pBuilder, pExpr->pRight, &nOut);
2644 } else {
2645 rc = whereInScanEst(pParse, pBuilder, pExpr->x.pList, &nOut);
2646 }
2647 if (rc==SQLITE_NOTFOUND) rc = SQLITE_OK;
2648 if (rc!=SQLITE_OK) break; /* Jump out of the pTerm loop */
2649 if (nOut) {
2650 pNew->nOut = sqlite3LogEst(nOut);
2651 if (pNew->nOut>saved_nOut) pNew->nOut = saved_nOut;
2652 pNew->nOut -= nIn;
2653 }
2654 }
2655 if (nOut==0)
2656 #endif
2657 {
2658 pNew->nOut += (pProbe->aiRowLogEst[nEq] - pProbe->aiRowLogEst[nEq-1]);
2659 if (eOp & WO_ISNULL) {
2660 /* TUNING: If there is no likelihood() value, assume that a
2661 ** "col IS NULL" expression matches twice as many rows
2662 ** as (col=?). */
2663 pNew->nOut += 10;
2664 }
2665 }
2666 }
2667 }
2668
2669 /* Set rCostIdx to the cost of visiting selected rows in index. Add
2670 ** it to pNew->rRun, which is currently set to the cost of the index
2671 ** seek only. Then, if this is a non-covering index, add the cost of
2672 ** visiting the rows in the main table. */
2673 rCostIdx = pNew->nOut + 1 + (15*pProbe->szIdxRow)/pSrc->pTab->szTabRow;
2674 pNew->rRun = sqlite3LogEstAdd(rLogSize, rCostIdx);
2675 if ((pNew->wsFlags & (WHERE_IDX_ONLY|WHERE_IPK))==0) {
2676 pNew->rRun = sqlite3LogEstAdd(pNew->rRun, pNew->nOut + 16);
2677 }
2678 ApplyCostMultiplier(pNew->rRun, pProbe->pTable->costMult);
2679
2680 nOutUnadjusted = pNew->nOut;
2681 pNew->rRun += nInMul + nIn;
2682 pNew->nOut += nInMul + nIn;
2683 whereLoopOutputAdjust(pBuilder->pWC, pNew, rSize);
2684 rc = whereLoopInsert(pBuilder, pNew);
2685
2686 if (pNew->wsFlags & WHERE_COLUMN_RANGE) {
2687 pNew->nOut = saved_nOut;
2688 } else {
2689 pNew->nOut = nOutUnadjusted;
2690 }
2691
2692 if ((pNew->wsFlags & WHERE_TOP_LIMIT)==0
2693 && pNew->u.btree.nEq<pProbe->nColumn
2694 ) {
2695 whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, nInMul+nIn);
2696 }
2697 pNew->nOut = saved_nOut;
2698 #ifdef SQLITE_ENABLE_STAT4
2699 pBuilder->nRecValid = nRecValid;
2700 #endif
2701 }
2702 pNew->prereq = saved_prereq;
2703 pNew->u.btree.nEq = saved_nEq;
2704 pNew->u.btree.nBtm = saved_nBtm;
2705 pNew->u.btree.nTop = saved_nTop;
2706 pNew->nSkip = saved_nSkip;
2707 pNew->wsFlags = saved_wsFlags;
2708 pNew->nOut = saved_nOut;
2709 pNew->nLTerm = saved_nLTerm;
2710
2711 /* Consider using a skip-scan if there are no WHERE clause constraints
2712 ** available for the left-most terms of the index, and if the average
2713 ** number of repeats in the left-most terms is at least 18.
2714 **
2715 ** The magic number 18 is selected on the basis that scanning 17 rows
2716 ** is almost always quicker than an index seek (even though if the index
2717 ** contains fewer than 2^17 rows we assume otherwise in other parts of
2718 ** the code). And, even if it is not, it should not be too much slower.
2719 ** On the other hand, the extra seeks could end up being significantly
2720 ** more expensive. */
2721 assert( 42==sqlite3LogEst(18));
2722 if (saved_nEq==saved_nSkip
2723 && saved_nEq+1<pProbe->nKeyCol
2724 && pProbe->noSkipScan==0
2725 && OptimizationEnabled(db, SQLITE_SkipScan)
2726 && pProbe->aiRowLogEst[saved_nEq+1]>=42 /* TUNING: Minimum for skip-scan */
2727 && (rc = whereLoopResize(db, pNew, pNew->nLTerm+1))==SQLITE_OK
2728 ) {
2729 LogEst nIter;
2730 pNew->u.btree.nEq++;
2731 pNew->nSkip++;
2732 pNew->aLTerm[pNew->nLTerm++] = 0;
2733 pNew->wsFlags |= WHERE_SKIPSCAN;
2734 nIter = pProbe->aiRowLogEst[saved_nEq] - pProbe->aiRowLogEst[saved_nEq+1];
2735 pNew->nOut -= nIter;
2736 /* TUNING: Because uncertainties in the estimates for skip-scan queries,
2737 ** add a 1.375 fudge factor to make skip-scan slightly less likely. */
2738 nIter += 5;
2739 whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, nIter + nInMul);
2740 pNew->nOut = saved_nOut;
2741 pNew->u.btree.nEq = saved_nEq;
2742 pNew->nSkip = saved_nSkip;
2743 pNew->wsFlags = saved_wsFlags;
2744 }
2745
2746 WHERETRACE(0x800, ("END %s.addBtreeIdx(%s), nEq=%d, rc=%d\n",
2747 pProbe->pTable->zName, pProbe->zName, saved_nEq, rc));
2748 return rc;
2749 }
2750
2751 /*
2752 ** Return True if it is possible that pIndex might be useful in
2753 ** implementing the ORDER BY clause in pBuilder.
2754 **
2755 ** Return False if pBuilder does not contain an ORDER BY clause or
2756 ** if there is no way for pIndex to be useful in implementing that
2757 ** ORDER BY clause.
2758 */
indexMightHelpWithOrderBy(WhereLoopBuilder * pBuilder,Index * pIndex,int iCursor)2759 static int indexMightHelpWithOrderBy(
2760 WhereLoopBuilder *pBuilder,
2761 Index *pIndex,
2762 int iCursor
2763 ){
2764 ExprList *pOB;
2765 ExprList *aColExpr;
2766 int ii, jj;
2767
2768 if (pIndex->bUnordered) return 0;
2769 if ((pOB = pBuilder->pWInfo->pOrderBy)==0) return 0;
2770 for (ii=0; ii<pOB->nExpr; ii++) {
2771 Expr *pExpr = sqlite3ExprSkipCollate(pOB->a[ii].pExpr);
2772 if (pExpr->op==TK_COLUMN && pExpr->iTable==iCursor) {
2773 if (pExpr->iColumn<0) return 1;
2774 for (jj=0; jj<pIndex->nKeyCol; jj++) {
2775 if (pExpr->iColumn==pIndex->aiColumn[jj]) return 1;
2776 }
2777 } else if ((aColExpr = pIndex->aColExpr)!=0) {
2778 for (jj=0; jj<pIndex->nKeyCol; jj++) {
2779 if (pIndex->aiColumn[jj]!=XN_EXPR) continue;
2780 if (sqlite3ExprCompareSkip(pExpr,aColExpr->a[jj].pExpr,iCursor)==0) {
2781 return 1;
2782 }
2783 }
2784 }
2785 }
2786 return 0;
2787 }
2788
2789 /* Check to see if a partial index with pPartIndexWhere can be used
2790 ** in the current query. Return true if it can be and false if not.
2791 */
whereUsablePartialIndex(int iTab,WhereClause * pWC,Expr * pWhere)2792 static int whereUsablePartialIndex(int iTab, WhereClause *pWC, Expr *pWhere){
2793 int i;
2794 WhereTerm *pTerm;
2795 Parse *pParse = pWC->pWInfo->pParse;
2796 while (pWhere->op==TK_AND) {
2797 if (!whereUsablePartialIndex(iTab,pWC,pWhere->pLeft)) return 0;
2798 pWhere = pWhere->pRight;
2799 }
2800 if (pParse->db->flags & SQLITE_EnableQPSG) pParse = 0;
2801 for (i=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++) {
2802 Expr *pExpr = pTerm->pExpr;
2803 if ((!ExprHasProperty(pExpr, EP_FromJoin) || pExpr->iRightJoinTable==iTab)
2804 && sqlite3ExprImpliesExpr(pParse, pExpr, pWhere, iTab)
2805 ) {
2806 return 1;
2807 }
2808 }
2809 return 0;
2810 }
2811
2812 /*
2813 ** Add all WhereLoop objects for a single table of the join where the table
2814 ** is identified by pBuilder->pNew->iTab. That table is guaranteed to be
2815 ** a b-tree table, not a virtual table.
2816 **
2817 ** The costs (WhereLoop.rRun) of the b-tree loops added by this function
2818 ** are calculated as follows:
2819 **
2820 ** For a full scan, assuming the table (or index) contains nRow rows:
2821 **
2822 ** cost = nRow * 3.0 // full-table scan
2823 ** cost = nRow * K // scan of covering index
2824 ** cost = nRow * (K+3.0) // scan of non-covering index
2825 **
2826 ** where K is a value between 1.1 and 3.0 set based on the relative
2827 ** estimated average size of the index and table records.
2828 **
2829 ** For an index scan, where nVisit is the number of index rows visited
2830 ** by the scan, and nSeek is the number of seek operations required on
2831 ** the index b-tree:
2832 **
2833 ** cost = nSeek * (log(nRow) + K * nVisit) // covering index
2834 ** cost = nSeek * (log(nRow) + (K+3.0) * nVisit) // non-covering index
2835 **
2836 ** Normally, nSeek is 1. nSeek values greater than 1 come about if the
2837 ** WHERE clause includes "x IN (....)" terms used in place of "x=?". Or when
2838 ** implicit "x IN (SELECT x FROM tbl)" terms are added for skip-scans.
2839 **
2840 ** The estimated values (nRow, nVisit, nSeek) often contain a large amount
2841 ** of uncertainty. For this reason, scoring is designed to pick plans that
2842 ** "do the least harm" if the estimates are inaccurate. For example, a
2843 ** log(nRow) factor is omitted from a non-covering index scan in order to
2844 ** bias the scoring in favor of using an index, since the worst-case
2845 ** performance of using an index is far better than the worst-case performance
2846 ** of a full table scan.
2847 */
whereLoopAddBtree(WhereLoopBuilder * pBuilder,Bitmask mPrereq)2848 static int whereLoopAddBtree(
2849 WhereLoopBuilder *pBuilder, /* WHERE clause information */
2850 Bitmask mPrereq /* Extra prerequesites for using this table */
2851 ){
2852 WhereInfo *pWInfo; /* WHERE analysis context */
2853 Index *pProbe; /* An index we are evaluating */
2854 Index sPk; /* A fake index object for the primary key */
2855 LogEst aiRowEstPk[2]; /* The aiRowLogEst[] value for the sPk index */
2856 i16 aiColumnPk = -1; /* The aColumn[] value for the sPk index */
2857 SrcList *pTabList; /* The FROM clause */
2858 struct SrcList_item *pSrc; /* The FROM clause btree term to add */
2859 WhereLoop *pNew; /* Template WhereLoop object */
2860 int rc = SQLITE_OK; /* Return code */
2861 int iSortIdx = 1; /* Index number */
2862 int b; /* A boolean value */
2863 LogEst rSize; /* number of rows in the table */
2864 LogEst rLogSize; /* Logarithm of the number of rows in the table */
2865 WhereClause *pWC; /* The parsed WHERE clause */
2866 Table *pTab; /* Table being queried */
2867
2868 pNew = pBuilder->pNew;
2869 pWInfo = pBuilder->pWInfo;
2870 pTabList = pWInfo->pTabList;
2871 pSrc = pTabList->a + pNew->iTab;
2872 pTab = pSrc->pTab;
2873 pWC = pBuilder->pWC;
2874 assert( !IsVirtual(pSrc->pTab));
2875
2876 if (pSrc->pIBIndex) {
2877 /* An INDEXED BY clause specifies a particular index to use */
2878 pProbe = pSrc->pIBIndex;
2879 } else if (!HasRowid(pTab)) {
2880 pProbe = pTab->pIndex;
2881 } else {
2882 /* There is no INDEXED BY clause. Create a fake Index object in local
2883 ** variable sPk to represent the rowid primary key index. Make this
2884 ** fake index the first in a chain of Index objects with all of the real
2885 ** indices to follow */
2886 Index *pFirst; /* First of real indices on the table */
2887 memset(&sPk, 0, sizeof(Index));
2888 sPk.nKeyCol = 1;
2889 sPk.nColumn = 1;
2890 sPk.aiColumn = &aiColumnPk;
2891 sPk.aiRowLogEst = aiRowEstPk;
2892 sPk.onError = OE_Replace;
2893 sPk.pTable = pTab;
2894 sPk.szIdxRow = pTab->szTabRow;
2895 sPk.idxType = SQLITE_IDXTYPE_IPK;
2896 aiRowEstPk[0] = pTab->nRowLogEst;
2897 aiRowEstPk[1] = 0;
2898 pFirst = pSrc->pTab->pIndex;
2899 if (pSrc->fg.notIndexed==0) {
2900 /* The real indices of the table are only considered if the
2901 ** NOT INDEXED qualifier is omitted from the FROM clause */
2902 sPk.pNext = pFirst;
2903 }
2904 pProbe = &sPk;
2905 }
2906 rSize = pTab->nRowLogEst;
2907 rLogSize = estLog(rSize);
2908
2909 #ifndef SQLITE_OMIT_AUTOMATIC_INDEX
2910 /* Automatic indexes */
2911 if (!pBuilder->pOrSet /* Not part of an OR optimization */
2912 && (pWInfo->wctrlFlags & WHERE_OR_SUBCLAUSE)==0
2913 && (pWInfo->pParse->db->flags & SQLITE_AutoIndex)!=0
2914 && pSrc->pIBIndex==0 /* Has no INDEXED BY clause */
2915 && !pSrc->fg.notIndexed /* Has no NOT INDEXED clause */
2916 && HasRowid(pTab) /* Not WITHOUT ROWID table. (FIXME: Why not?) */
2917 && !pSrc->fg.isCorrelated /* Not a correlated subquery */
2918 && !pSrc->fg.isRecursive /* Not a recursive common table expression. */
2919 ) {
2920 /* Generate auto-index WhereLoops */
2921 WhereTerm *pTerm;
2922 WhereTerm *pWCEnd = pWC->a + pWC->nTerm;
2923 for (pTerm=pWC->a; rc==SQLITE_OK && pTerm<pWCEnd; pTerm++) {
2924 if (pTerm->prereqRight & pNew->maskSelf) continue;
2925 if (termCanDriveIndex(pTerm, pSrc, 0)) {
2926 pNew->u.btree.nEq = 1;
2927 pNew->nSkip = 0;
2928 pNew->u.btree.pIndex = 0;
2929 pNew->nLTerm = 1;
2930 pNew->aLTerm[0] = pTerm;
2931 /* TUNING: One-time cost for computing the automatic index is
2932 ** estimated to be X*N*log2(N) where N is the number of rows in
2933 ** the table being indexed and where X is 7 (LogEst=28) for normal
2934 ** tables or 0.5 (LogEst=-10) for views and subqueries. The value
2935 ** of X is smaller for views and subqueries so that the query planner
2936 ** will be more aggressive about generating automatic indexes for
2937 ** those objects, since there is no opportunity to add schema
2938 ** indexes on subqueries and views. */
2939 pNew->rSetup = rLogSize + rSize;
2940 if (pTab->pSelect==0 && (pTab->tabFlags & TF_Ephemeral)==0) {
2941 pNew->rSetup += 28;
2942 } else {
2943 pNew->rSetup -= 10;
2944 }
2945 ApplyCostMultiplier(pNew->rSetup, pTab->costMult);
2946 if (pNew->rSetup<0) pNew->rSetup = 0;
2947 /* TUNING: Each index lookup yields 20 rows in the table. This
2948 ** is more than the usual guess of 10 rows, since we have no way
2949 ** of knowing how selective the index will ultimately be. It would
2950 ** not be unreasonable to make this value much larger. */
2951 pNew->nOut = 43; assert( 43==sqlite3LogEst(20));
2952 pNew->rRun = sqlite3LogEstAdd(rLogSize,pNew->nOut);
2953 pNew->wsFlags = WHERE_AUTO_INDEX;
2954 pNew->prereq = mPrereq | pTerm->prereqRight;
2955 rc = whereLoopInsert(pBuilder, pNew);
2956 }
2957 }
2958 }
2959 #endif /* SQLITE_OMIT_AUTOMATIC_INDEX */
2960
2961 /* Loop over all indices. If there was an INDEXED BY clause, then only
2962 ** consider index pProbe. */
2963 for (; rc==SQLITE_OK && pProbe;
2964 pProbe=(pSrc->pIBIndex ? 0 : pProbe->pNext), iSortIdx++
2965 ) {
2966 if (pProbe->pPartIdxWhere!=0
2967 && !whereUsablePartialIndex(pSrc->iCursor, pWC, pProbe->pPartIdxWhere)) {
2968 testcase( pNew->iTab!=pSrc->iCursor ); /* See ticket [98d973b8f5] */
2969 continue; /* Partial index inappropriate for this query */
2970 }
2971 if (pProbe->bNoQuery) continue;
2972 rSize = pProbe->aiRowLogEst[0];
2973 pNew->u.btree.nEq = 0;
2974 pNew->u.btree.nBtm = 0;
2975 pNew->u.btree.nTop = 0;
2976 pNew->nSkip = 0;
2977 pNew->nLTerm = 0;
2978 pNew->iSortIdx = 0;
2979 pNew->rSetup = 0;
2980 pNew->prereq = mPrereq;
2981 pNew->nOut = rSize;
2982 pNew->u.btree.pIndex = pProbe;
2983 b = indexMightHelpWithOrderBy(pBuilder, pProbe, pSrc->iCursor);
2984 /* The ONEPASS_DESIRED flags never occurs together with ORDER BY */
2985 assert((pWInfo->wctrlFlags & WHERE_ONEPASS_DESIRED)==0 || b==0 );
2986 if (pProbe->idxType==SQLITE_IDXTYPE_IPK) {
2987 /* Integer primary key index */
2988 pNew->wsFlags = WHERE_IPK;
2989
2990 /* Full table scan */
2991 pNew->iSortIdx = b ? iSortIdx : 0;
2992 /* TUNING: Cost of full table scan is (N*3.0). */
2993 pNew->rRun = rSize + 16;
2994 ApplyCostMultiplier(pNew->rRun, pTab->costMult);
2995 whereLoopOutputAdjust(pWC, pNew, rSize);
2996 rc = whereLoopInsert(pBuilder, pNew);
2997 pNew->nOut = rSize;
2998 if (rc) break;
2999 } else {
3000 Bitmask m;
3001 if (pProbe->isCovering) {
3002 pNew->wsFlags = WHERE_IDX_ONLY | WHERE_INDEXED;
3003 m = 0;
3004 } else {
3005 m = pSrc->colUsed & pProbe->colNotIdxed;
3006 pNew->wsFlags = (m==0) ? (WHERE_IDX_ONLY|WHERE_INDEXED) : WHERE_INDEXED;
3007 }
3008
3009 /* Full scan via index */
3010 if (b
3011 || !HasRowid(pTab)
3012 || pProbe->pPartIdxWhere!=0
3013 || (m==0
3014 && pProbe->bUnordered==0
3015 && (pProbe->szIdxRow<pTab->szTabRow)
3016 && (pWInfo->wctrlFlags & WHERE_ONEPASS_DESIRED)==0
3017 && sqlite3GlobalConfig.bUseCis
3018 && OptimizationEnabled(pWInfo->pParse->db, SQLITE_CoverIdxScan)
3019 )
3020 ) {
3021 pNew->iSortIdx = b ? iSortIdx : 0;
3022
3023 /* The cost of visiting the index rows is N*K, where K is
3024 ** between 1.1 and 3.0, depending on the relative sizes of the
3025 ** index and table rows. */
3026 pNew->rRun = rSize + 1 + (15*pProbe->szIdxRow)/pTab->szTabRow;
3027 if (m!=0) {
3028 /* If this is a non-covering index scan, add in the cost of
3029 ** doing table lookups. The cost will be 3x the number of
3030 ** lookups. Take into account WHERE clause terms that can be
3031 ** satisfied using just the index, and that do not require a
3032 ** table lookup. */
3033 LogEst nLookup = rSize + 16; /* Base cost: N*3 */
3034 int ii;
3035 int iCur = pSrc->iCursor;
3036 WhereClause *pWC2 = &pWInfo->sWC;
3037 for (ii=0; ii<pWC2->nTerm; ii++) {
3038 WhereTerm *pTerm = &pWC2->a[ii];
3039 if (!sqlite3ExprCoveredByIndex(pTerm->pExpr, iCur, pProbe)) {
3040 break;
3041 }
3042 /* pTerm can be evaluated using just the index. So reduce
3043 ** the expected number of table lookups accordingly */
3044 if (pTerm->truthProb<=0) {
3045 nLookup += pTerm->truthProb;
3046 } else {
3047 nLookup--;
3048 if (pTerm->eOperator & (WO_EQ|WO_IS)) nLookup -= 19;
3049 }
3050 }
3051
3052 pNew->rRun = sqlite3LogEstAdd(pNew->rRun, nLookup);
3053 }
3054 ApplyCostMultiplier(pNew->rRun, pTab->costMult);
3055 whereLoopOutputAdjust(pWC, pNew, rSize);
3056 rc = whereLoopInsert(pBuilder, pNew);
3057 pNew->nOut = rSize;
3058 if (rc) break;
3059 }
3060 }
3061
3062 pBuilder->bldFlags = 0;
3063 rc = whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, 0);
3064 if (pBuilder->bldFlags==SQLITE_BLDF_INDEXED) {
3065 /* If a non-unique index is used, or if a prefix of the key for
3066 ** unique index is used (making the index functionally non-unique)
3067 ** then the sqlite_stat1 data becomes important for scoring the
3068 ** plan */
3069 pTab->tabFlags |= TF_StatsUsed;
3070 }
3071 #ifdef SQLITE_ENABLE_STAT4
3072 sqlite3Stat4ProbeFree(pBuilder->pRec);
3073 pBuilder->nRecValid = 0;
3074 pBuilder->pRec = 0;
3075 #endif
3076 }
3077 return rc;
3078 }
3079
3080 #ifndef SQLITE_OMIT_VIRTUALTABLE
3081
3082 /*
3083 ** Argument pIdxInfo is already populated with all constraints that may
3084 ** be used by the virtual table identified by pBuilder->pNew->iTab. This
3085 ** function marks a subset of those constraints usable, invokes the
3086 ** xBestIndex method and adds the returned plan to pBuilder.
3087 **
3088 ** A constraint is marked usable if:
3089 **
3090 ** * Argument mUsable indicates that its prerequisites are available, and
3091 **
3092 ** * It is not one of the operators specified in the mExclude mask passed
3093 ** as the fourth argument (which in practice is either WO_IN or 0).
3094 **
3095 ** Argument mPrereq is a mask of tables that must be scanned before the
3096 ** virtual table in question. These are added to the plans prerequisites
3097 ** before it is added to pBuilder.
3098 **
3099 ** Output parameter *pbIn is set to true if the plan added to pBuilder
3100 ** uses one or more WO_IN terms, or false otherwise.
3101 */
whereLoopAddVirtualOne(WhereLoopBuilder * pBuilder,Bitmask mPrereq,Bitmask mUsable,u16 mExclude,sqlite3_index_info * pIdxInfo,u16 mNoOmit,int * pbIn)3102 static int whereLoopAddVirtualOne(
3103 WhereLoopBuilder *pBuilder,
3104 Bitmask mPrereq, /* Mask of tables that must be used. */
3105 Bitmask mUsable, /* Mask of usable tables */
3106 u16 mExclude, /* Exclude terms using these operators */
3107 sqlite3_index_info *pIdxInfo, /* Populated object for xBestIndex */
3108 u16 mNoOmit, /* Do not omit these constraints */
3109 int *pbIn /* OUT: True if plan uses an IN(...) op */
3110 ){
3111 WhereClause *pWC = pBuilder->pWC;
3112 struct sqlite3_index_constraint *pIdxCons;
3113 struct sqlite3_index_constraint_usage *pUsage = pIdxInfo->aConstraintUsage;
3114 int i;
3115 int mxTerm;
3116 int rc = SQLITE_OK;
3117 WhereLoop *pNew = pBuilder->pNew;
3118 Parse *pParse = pBuilder->pWInfo->pParse;
3119 struct SrcList_item *pSrc = &pBuilder->pWInfo->pTabList->a[pNew->iTab];
3120 int nConstraint = pIdxInfo->nConstraint;
3121
3122 assert((mUsable & mPrereq)==mPrereq );
3123 *pbIn = 0;
3124 pNew->prereq = mPrereq;
3125
3126 /* Set the usable flag on the subset of constraints identified by
3127 ** arguments mUsable and mExclude. */
3128 pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint;
3129 for (i=0; i<nConstraint; i++, pIdxCons++) {
3130 WhereTerm *pTerm = &pWC->a[pIdxCons->iTermOffset];
3131 pIdxCons->usable = 0;
3132 if ((pTerm->prereqRight & mUsable)==pTerm->prereqRight
3133 && (pTerm->eOperator & mExclude)==0
3134 ) {
3135 pIdxCons->usable = 1;
3136 }
3137 }
3138
3139 /* Initialize the output fields of the sqlite3_index_info structure */
3140 memset(pUsage, 0, sizeof(pUsage[0])*nConstraint);
3141 assert( pIdxInfo->needToFreeIdxStr==0 );
3142 pIdxInfo->idxStr = 0;
3143 pIdxInfo->idxNum = 0;
3144 pIdxInfo->orderByConsumed = 0;
3145 pIdxInfo->estimatedCost = SQLITE_BIG_DBL / (double)2;
3146 pIdxInfo->estimatedRows = 25;
3147 pIdxInfo->idxFlags = 0;
3148 pIdxInfo->colUsed = (sqlite3_int64)pSrc->colUsed;
3149
3150 /* Invoke the virtual table xBestIndex() method */
3151 rc = vtabBestIndex(pParse, pSrc->pTab, pIdxInfo);
3152 if (rc) {
3153 if (rc==SQLITE_CONSTRAINT) {
3154 /* If the xBestIndex method returns SQLITE_CONSTRAINT, that means
3155 ** that the particular combination of parameters provided is unusable.
3156 ** Make no entries in the loop table.
3157 */
3158 WHERETRACE(0xffff, (" ^^^^--- non-viable plan rejected!\n"));
3159 return SQLITE_OK;
3160 }
3161 return rc;
3162 }
3163
3164 mxTerm = -1;
3165 assert( pNew->nLSlot>=nConstraint );
3166 for (i=0; i<nConstraint; i++) pNew->aLTerm[i] = 0;
3167 pNew->u.vtab.omitMask = 0;
3168 pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint;
3169 for (i=0; i<nConstraint; i++, pIdxCons++) {
3170 int iTerm;
3171 if ((iTerm = pUsage[i].argvIndex - 1)>=0) {
3172 WhereTerm *pTerm;
3173 int j = pIdxCons->iTermOffset;
3174 if (iTerm>=nConstraint
3175 || j<0
3176 || j>=pWC->nTerm
3177 || pNew->aLTerm[iTerm]!=0
3178 || pIdxCons->usable==0
3179 ) {
3180 sqlite3ErrorMsg(pParse,"%s.xBestIndex malfunction",pSrc->pTab->zName);
3181 testcase( pIdxInfo->needToFreeIdxStr );
3182 return SQLITE_ERROR;
3183 }
3184 testcase( iTerm==nConstraint-1 );
3185 testcase( j==0 );
3186 testcase( j==pWC->nTerm-1 );
3187 pTerm = &pWC->a[j];
3188 pNew->prereq |= pTerm->prereqRight;
3189 assert( iTerm<pNew->nLSlot );
3190 pNew->aLTerm[iTerm] = pTerm;
3191 if (iTerm>mxTerm) mxTerm = iTerm;
3192 testcase( iTerm==15 );
3193 testcase( iTerm==16 );
3194 if (iTerm<16 && pUsage[i].omit) pNew->u.vtab.omitMask |= 1<<iTerm;
3195 if ((pTerm->eOperator & WO_IN)!=0) {
3196 /* A virtual table that is constrained by an IN clause may not
3197 ** consume the ORDER BY clause because (1) the order of IN terms
3198 ** is not necessarily related to the order of output terms and
3199 ** (2) Multiple outputs from a single IN value will not merge
3200 ** together. */
3201 pIdxInfo->orderByConsumed = 0;
3202 pIdxInfo->idxFlags &= ~SQLITE_INDEX_SCAN_UNIQUE;
3203 *pbIn = 1; assert((mExclude & WO_IN)==0 );
3204 }
3205 }
3206 }
3207 pNew->u.vtab.omitMask &= ~mNoOmit;
3208
3209 pNew->nLTerm = mxTerm+1;
3210 for (i=0; i<=mxTerm; i++) {
3211 if (pNew->aLTerm[i]==0) {
3212 /* The non-zero argvIdx values must be contiguous. Raise an
3213 ** error if they are not */
3214 sqlite3ErrorMsg(pParse,"%s.xBestIndex malfunction",pSrc->pTab->zName);
3215 testcase( pIdxInfo->needToFreeIdxStr );
3216 return SQLITE_ERROR;
3217 }
3218 }
3219 assert( pNew->nLTerm<=pNew->nLSlot );
3220 pNew->u.vtab.idxNum = pIdxInfo->idxNum;
3221 pNew->u.vtab.needFree = pIdxInfo->needToFreeIdxStr;
3222 pIdxInfo->needToFreeIdxStr = 0;
3223 pNew->u.vtab.idxStr = pIdxInfo->idxStr;
3224 pNew->u.vtab.isOrdered = (i8)(pIdxInfo->orderByConsumed ?
3225 pIdxInfo->nOrderBy : 0);
3226 pNew->rSetup = 0;
3227 pNew->rRun = sqlite3LogEstFromDouble(pIdxInfo->estimatedCost);
3228 pNew->nOut = sqlite3LogEst(pIdxInfo->estimatedRows);
3229
3230 /* Set the WHERE_ONEROW flag if the xBestIndex() method indicated
3231 ** that the scan will visit at most one row. Clear it otherwise. */
3232 if (pIdxInfo->idxFlags & SQLITE_INDEX_SCAN_UNIQUE) {
3233 pNew->wsFlags |= WHERE_ONEROW;
3234 } else {
3235 pNew->wsFlags &= ~WHERE_ONEROW;
3236 }
3237 rc = whereLoopInsert(pBuilder, pNew);
3238 if (pNew->u.vtab.needFree) {
3239 sqlite3_free(pNew->u.vtab.idxStr);
3240 pNew->u.vtab.needFree = 0;
3241 }
3242 WHERETRACE(0xffff, (" bIn=%d prereqIn=%04llx prereqOut=%04llx\n",
3243 *pbIn, (sqlite3_uint64)mPrereq,
3244 (sqlite3_uint64)(pNew->prereq & ~mPrereq)));
3245
3246 return rc;
3247 }
3248
3249 /*
3250 ** If this function is invoked from within an xBestIndex() callback, it
3251 ** returns a pointer to a buffer containing the name of the collation
3252 ** sequence associated with element iCons of the sqlite3_index_info.aConstraint
3253 ** array. Or, if iCons is out of range or there is no active xBestIndex
3254 ** call, return NULL.
3255 */
sqlite3_vtab_collation(sqlite3_index_info * pIdxInfo,int iCons)3256 const char *sqlite3_vtab_collation(sqlite3_index_info *pIdxInfo, int iCons){
3257 HiddenIndexInfo *pHidden = (HiddenIndexInfo*)&pIdxInfo[1];
3258 const char *zRet = 0;
3259 if (iCons>=0 && iCons<pIdxInfo->nConstraint) {
3260 CollSeq *pC = 0;
3261 int iTerm = pIdxInfo->aConstraint[iCons].iTermOffset;
3262 Expr *pX = pHidden->pWC->a[iTerm].pExpr;
3263 if (pX->pLeft) {
3264 pC = sqlite3BinaryCompareCollSeq(pHidden->pParse, pX->pLeft, pX->pRight);
3265 }
3266 zRet = (pC ? pC->zName : sqlite3StrBINARY);
3267 }
3268 return zRet;
3269 }
3270
3271 /*
3272 ** Add all WhereLoop objects for a table of the join identified by
3273 ** pBuilder->pNew->iTab. That table is guaranteed to be a virtual table.
3274 **
3275 ** If there are no LEFT or CROSS JOIN joins in the query, both mPrereq and
3276 ** mUnusable are set to 0. Otherwise, mPrereq is a mask of all FROM clause
3277 ** entries that occur before the virtual table in the FROM clause and are
3278 ** separated from it by at least one LEFT or CROSS JOIN. Similarly, the
3279 ** mUnusable mask contains all FROM clause entries that occur after the
3280 ** virtual table and are separated from it by at least one LEFT or
3281 ** CROSS JOIN.
3282 **
3283 ** For example, if the query were:
3284 **
3285 ** ... FROM t1, t2 LEFT JOIN t3, t4, vt CROSS JOIN t5, t6;
3286 **
3287 ** then mPrereq corresponds to (t1, t2) and mUnusable to (t5, t6).
3288 **
3289 ** All the tables in mPrereq must be scanned before the current virtual
3290 ** table. So any terms for which all prerequisites are satisfied by
3291 ** mPrereq may be specified as "usable" in all calls to xBestIndex.
3292 ** Conversely, all tables in mUnusable must be scanned after the current
3293 ** virtual table, so any terms for which the prerequisites overlap with
3294 ** mUnusable should always be configured as "not-usable" for xBestIndex.
3295 */
whereLoopAddVirtual(WhereLoopBuilder * pBuilder,Bitmask mPrereq,Bitmask mUnusable)3296 static int whereLoopAddVirtual(
3297 WhereLoopBuilder *pBuilder, /* WHERE clause information */
3298 Bitmask mPrereq, /* Tables that must be scanned before this one */
3299 Bitmask mUnusable /* Tables that must be scanned after this one */
3300 ){
3301 int rc = SQLITE_OK; /* Return code */
3302 WhereInfo *pWInfo; /* WHERE analysis context */
3303 Parse *pParse; /* The parsing context */
3304 WhereClause *pWC; /* The WHERE clause */
3305 struct SrcList_item *pSrc; /* The FROM clause term to search */
3306 sqlite3_index_info *p; /* Object to pass to xBestIndex() */
3307 int nConstraint; /* Number of constraints in p */
3308 int bIn; /* True if plan uses IN(...) operator */
3309 WhereLoop *pNew;
3310 Bitmask mBest; /* Tables used by best possible plan */
3311 u16 mNoOmit;
3312
3313 assert((mPrereq & mUnusable)==0 );
3314 pWInfo = pBuilder->pWInfo;
3315 pParse = pWInfo->pParse;
3316 pWC = pBuilder->pWC;
3317 pNew = pBuilder->pNew;
3318 pSrc = &pWInfo->pTabList->a[pNew->iTab];
3319 assert( IsVirtual(pSrc->pTab));
3320 p = allocateIndexInfo(pParse, pWC, mUnusable, pSrc, pBuilder->pOrderBy,
3321 &mNoOmit);
3322 if (p==0) return SQLITE_NOMEM_BKPT;
3323 pNew->rSetup = 0;
3324 pNew->wsFlags = WHERE_VIRTUALTABLE;
3325 pNew->nLTerm = 0;
3326 pNew->u.vtab.needFree = 0;
3327 nConstraint = p->nConstraint;
3328 if (whereLoopResize(pParse->db, pNew, nConstraint)) {
3329 sqlite3DbFree(pParse->db, p);
3330 return SQLITE_NOMEM_BKPT;
3331 }
3332
3333 /* First call xBestIndex() with all constraints usable. */
3334 WHERETRACE(0x800, ("BEGIN %s.addVirtual()\n", pSrc->pTab->zName));
3335 WHERETRACE(0x40, (" VirtualOne: all usable\n"));
3336 rc = whereLoopAddVirtualOne(pBuilder, mPrereq, ALLBITS, 0, p, mNoOmit, &bIn);
3337
3338 /* If the call to xBestIndex() with all terms enabled produced a plan
3339 ** that does not require any source tables (IOW: a plan with mBest==0)
3340 ** and does not use an IN(...) operator, then there is no point in making
3341 ** any further calls to xBestIndex() since they will all return the same
3342 ** result (if the xBestIndex() implementation is sane). */
3343 if (rc==SQLITE_OK && ((mBest = (pNew->prereq & ~mPrereq))!=0 || bIn)) {
3344 int seenZero = 0; /* True if a plan with no prereqs seen */
3345 int seenZeroNoIN = 0; /* Plan with no prereqs and no IN(...) seen */
3346 Bitmask mPrev = 0;
3347 Bitmask mBestNoIn = 0;
3348
3349 /* If the plan produced by the earlier call uses an IN(...) term, call
3350 ** xBestIndex again, this time with IN(...) terms disabled. */
3351 if (bIn) {
3352 WHERETRACE(0x40, (" VirtualOne: all usable w/o IN\n"));
3353 rc = whereLoopAddVirtualOne(
3354 pBuilder, mPrereq, ALLBITS, WO_IN, p, mNoOmit, &bIn);
3355 assert( bIn==0 );
3356 mBestNoIn = pNew->prereq & ~mPrereq;
3357 if (mBestNoIn==0) {
3358 seenZero = 1;
3359 seenZeroNoIN = 1;
3360 }
3361 }
3362
3363 /* Call xBestIndex once for each distinct value of (prereqRight & ~mPrereq)
3364 ** in the set of terms that apply to the current virtual table. */
3365 while (rc==SQLITE_OK) {
3366 int i;
3367 Bitmask mNext = ALLBITS;
3368 assert( mNext>0 );
3369 for (i=0; i<nConstraint; i++) {
3370 Bitmask mThis = (
3371 pWC->a[p->aConstraint[i].iTermOffset].prereqRight & ~mPrereq
3372 );
3373 if (mThis>mPrev && mThis<mNext) mNext = mThis;
3374 }
3375 mPrev = mNext;
3376 if (mNext==ALLBITS) break;
3377 if (mNext==mBest || mNext==mBestNoIn) continue;
3378 WHERETRACE(0x40, (" VirtualOne: mPrev=%04llx mNext=%04llx\n",
3379 (sqlite3_uint64)mPrev, (sqlite3_uint64)mNext));
3380 rc = whereLoopAddVirtualOne(
3381 pBuilder, mPrereq, mNext|mPrereq, 0, p, mNoOmit, &bIn);
3382 if (pNew->prereq==mPrereq) {
3383 seenZero = 1;
3384 if (bIn==0) seenZeroNoIN = 1;
3385 }
3386 }
3387
3388 /* If the calls to xBestIndex() in the above loop did not find a plan
3389 ** that requires no source tables at all (i.e. one guaranteed to be
3390 ** usable), make a call here with all source tables disabled */
3391 if (rc==SQLITE_OK && seenZero==0) {
3392 WHERETRACE(0x40, (" VirtualOne: all disabled\n"));
3393 rc = whereLoopAddVirtualOne(
3394 pBuilder, mPrereq, mPrereq, 0, p, mNoOmit, &bIn);
3395 if (bIn==0) seenZeroNoIN = 1;
3396 }
3397
3398 /* If the calls to xBestIndex() have so far failed to find a plan
3399 ** that requires no source tables at all and does not use an IN(...)
3400 ** operator, make a final call to obtain one here. */
3401 if (rc==SQLITE_OK && seenZeroNoIN==0) {
3402 WHERETRACE(0x40, (" VirtualOne: all disabled and w/o IN\n"));
3403 rc = whereLoopAddVirtualOne(
3404 pBuilder, mPrereq, mPrereq, WO_IN, p, mNoOmit, &bIn);
3405 }
3406 }
3407
3408 if (p->needToFreeIdxStr) sqlite3_free(p->idxStr);
3409 sqlite3DbFreeNN(pParse->db, p);
3410 WHERETRACE(0x800, ("END %s.addVirtual(), rc=%d\n", pSrc->pTab->zName, rc));
3411 return rc;
3412 }
3413 #endif /* SQLITE_OMIT_VIRTUALTABLE */
3414
3415 /*
3416 ** Add WhereLoop entries to handle OR terms. This works for either
3417 ** btrees or virtual tables.
3418 */
whereLoopAddOr(WhereLoopBuilder * pBuilder,Bitmask mPrereq,Bitmask mUnusable)3419 static int whereLoopAddOr(
3420 WhereLoopBuilder *pBuilder,
3421 Bitmask mPrereq,
3422 Bitmask mUnusable
3423 ){
3424 WhereInfo *pWInfo = pBuilder->pWInfo;
3425 WhereClause *pWC;
3426 WhereLoop *pNew;
3427 WhereTerm *pTerm, *pWCEnd;
3428 int rc = SQLITE_OK;
3429 int iCur;
3430 WhereClause tempWC;
3431 WhereLoopBuilder sSubBuild;
3432 WhereOrSet sSum, sCur;
3433 struct SrcList_item *pItem;
3434
3435 pWC = pBuilder->pWC;
3436 pWCEnd = pWC->a + pWC->nTerm;
3437 pNew = pBuilder->pNew;
3438 memset(&sSum, 0, sizeof(sSum));
3439 pItem = pWInfo->pTabList->a + pNew->iTab;
3440 iCur = pItem->iCursor;
3441
3442 for (pTerm=pWC->a; pTerm<pWCEnd && rc==SQLITE_OK; pTerm++) {
3443 if ((pTerm->eOperator & WO_OR)!=0
3444 && (pTerm->u.pOrInfo->indexable & pNew->maskSelf)!=0
3445 ) {
3446 WhereClause * const pOrWC = &pTerm->u.pOrInfo->wc;
3447 WhereTerm * const pOrWCEnd = &pOrWC->a[pOrWC->nTerm];
3448 WhereTerm *pOrTerm;
3449 int once = 1;
3450 int i, j;
3451
3452 sSubBuild = *pBuilder;
3453 sSubBuild.pOrderBy = 0;
3454 sSubBuild.pOrSet = &sCur;
3455
3456 WHERETRACE(0x200, ("Begin processing OR-clause %p\n", pTerm));
3457 for (pOrTerm=pOrWC->a; pOrTerm<pOrWCEnd; pOrTerm++) {
3458 if ((pOrTerm->eOperator & WO_AND)!=0) {
3459 sSubBuild.pWC = &pOrTerm->u.pAndInfo->wc;
3460 } else if (pOrTerm->leftCursor==iCur) {
3461 tempWC.pWInfo = pWC->pWInfo;
3462 tempWC.pOuter = pWC;
3463 tempWC.op = TK_AND;
3464 tempWC.nTerm = 1;
3465 tempWC.a = pOrTerm;
3466 sSubBuild.pWC = &tempWC;
3467 } else {
3468 continue;
3469 }
3470 sCur.n = 0;
3471 #ifdef WHERETRACE_ENABLED
3472 WHERETRACE(0x200, ("OR-term %d of %p has %d subterms:\n",
3473 (int)(pOrTerm-pOrWC->a), pTerm, sSubBuild.pWC->nTerm));
3474 if (sqlite3WhereTrace & 0x400) {
3475 sqlite3WhereClausePrint(sSubBuild.pWC);
3476 }
3477 #endif
3478 #ifndef SQLITE_OMIT_VIRTUALTABLE
3479 if (IsVirtual(pItem->pTab)) {
3480 rc = whereLoopAddVirtual(&sSubBuild, mPrereq, mUnusable);
3481 } else
3482 #endif
3483 {
3484 rc = whereLoopAddBtree(&sSubBuild, mPrereq);
3485 }
3486 if (rc==SQLITE_OK) {
3487 rc = whereLoopAddOr(&sSubBuild, mPrereq, mUnusable);
3488 }
3489 assert( rc==SQLITE_OK || sCur.n==0 );
3490 if (sCur.n==0) {
3491 sSum.n = 0;
3492 break;
3493 } else if (once) {
3494 whereOrMove(&sSum, &sCur);
3495 once = 0;
3496 } else {
3497 WhereOrSet sPrev;
3498 whereOrMove(&sPrev, &sSum);
3499 sSum.n = 0;
3500 for (i=0; i<sPrev.n; i++) {
3501 for (j=0; j<sCur.n; j++) {
3502 whereOrInsert(&sSum, sPrev.a[i].prereq | sCur.a[j].prereq,
3503 sqlite3LogEstAdd(sPrev.a[i].rRun, sCur.a[j].rRun),
3504 sqlite3LogEstAdd(sPrev.a[i].nOut, sCur.a[j].nOut));
3505 }
3506 }
3507 }
3508 }
3509 pNew->nLTerm = 1;
3510 pNew->aLTerm[0] = pTerm;
3511 pNew->wsFlags = WHERE_MULTI_OR;
3512 pNew->rSetup = 0;
3513 pNew->iSortIdx = 0;
3514 memset(&pNew->u, 0, sizeof(pNew->u));
3515 for (i=0; rc==SQLITE_OK && i<sSum.n; i++) {
3516 /* TUNING: Currently sSum.a[i].rRun is set to the sum of the costs
3517 ** of all sub-scans required by the OR-scan. However, due to rounding
3518 ** errors, it may be that the cost of the OR-scan is equal to its
3519 ** most expensive sub-scan. Add the smallest possible penalty
3520 ** (equivalent to multiplying the cost by 1.07) to ensure that
3521 ** this does not happen. Otherwise, for WHERE clauses such as the
3522 ** following where there is an index on "y":
3523 **
3524 ** WHERE likelihood(x=?, 0.99) OR y=?
3525 **
3526 ** the planner may elect to "OR" together a full-table scan and an
3527 ** index lookup. And other similarly odd results. */
3528 pNew->rRun = sSum.a[i].rRun + 1;
3529 pNew->nOut = sSum.a[i].nOut;
3530 pNew->prereq = sSum.a[i].prereq;
3531 rc = whereLoopInsert(pBuilder, pNew);
3532 }
3533 WHERETRACE(0x200, ("End processing OR-clause %p\n", pTerm));
3534 }
3535 }
3536 return rc;
3537 }
3538
3539 /*
3540 ** Add all WhereLoop objects for all tables
3541 */
whereLoopAddAll(WhereLoopBuilder * pBuilder)3542 static int whereLoopAddAll(WhereLoopBuilder *pBuilder){
3543 WhereInfo *pWInfo = pBuilder->pWInfo;
3544 Bitmask mPrereq = 0;
3545 Bitmask mPrior = 0;
3546 int iTab;
3547 SrcList *pTabList = pWInfo->pTabList;
3548 struct SrcList_item *pItem;
3549 struct SrcList_item *pEnd = &pTabList->a[pWInfo->nLevel];
3550 sqlite3 *db = pWInfo->pParse->db;
3551 int rc = SQLITE_OK;
3552 WhereLoop *pNew;
3553 u8 priorJointype = 0;
3554
3555 /* Loop over the tables in the join, from left to right */
3556 pNew = pBuilder->pNew;
3557 whereLoopInit(pNew);
3558 pBuilder->iPlanLimit = SQLITE_QUERY_PLANNER_LIMIT;
3559 for (iTab=0, pItem=pTabList->a; pItem<pEnd; iTab++, pItem++) {
3560 Bitmask mUnusable = 0;
3561 pNew->iTab = iTab;
3562 pBuilder->iPlanLimit += SQLITE_QUERY_PLANNER_LIMIT_INCR;
3563 pNew->maskSelf = sqlite3WhereGetMask(&pWInfo->sMaskSet, pItem->iCursor);
3564 if (((pItem->fg.jointype|priorJointype) & (JT_LEFT|JT_CROSS))!=0) {
3565 /* This condition is true when pItem is the FROM clause term on the
3566 ** right-hand-side of a LEFT or CROSS JOIN. */
3567 mPrereq = mPrior;
3568 }
3569 priorJointype = pItem->fg.jointype;
3570 #ifndef SQLITE_OMIT_VIRTUALTABLE
3571 if (IsVirtual(pItem->pTab)) {
3572 struct SrcList_item *p;
3573 for (p=&pItem[1]; p<pEnd; p++) {
3574 if (mUnusable || (p->fg.jointype & (JT_LEFT|JT_CROSS))) {
3575 mUnusable |= sqlite3WhereGetMask(&pWInfo->sMaskSet, p->iCursor);
3576 }
3577 }
3578 rc = whereLoopAddVirtual(pBuilder, mPrereq, mUnusable);
3579 } else
3580 #endif /* SQLITE_OMIT_VIRTUALTABLE */
3581 {
3582 rc = whereLoopAddBtree(pBuilder, mPrereq);
3583 }
3584 if (rc==SQLITE_OK && pBuilder->pWC->hasOr) {
3585 rc = whereLoopAddOr(pBuilder, mPrereq, mUnusable);
3586 }
3587 mPrior |= pNew->maskSelf;
3588 if (rc || db->mallocFailed) {
3589 if (rc==SQLITE_DONE) {
3590 /* We hit the query planner search limit set by iPlanLimit */
3591 sqlite3_log(SQLITE_WARNING, "abbreviated query algorithm search");
3592 rc = SQLITE_OK;
3593 } else {
3594 break;
3595 }
3596 }
3597 }
3598
3599 whereLoopClear(db, pNew);
3600 return rc;
3601 }
3602
3603 /*
3604 ** Examine a WherePath (with the addition of the extra WhereLoop of the 6th
3605 ** parameters) to see if it outputs rows in the requested ORDER BY
3606 ** (or GROUP BY) without requiring a separate sort operation. Return N:
3607 **
3608 ** N>0: N terms of the ORDER BY clause are satisfied
3609 ** N==0: No terms of the ORDER BY clause are satisfied
3610 ** N<0: Unknown yet how many terms of ORDER BY might be satisfied.
3611 **
3612 ** Note that processing for WHERE_GROUPBY and WHERE_DISTINCTBY is not as
3613 ** strict. With GROUP BY and DISTINCT the only requirement is that
3614 ** equivalent rows appear immediately adjacent to one another. GROUP BY
3615 ** and DISTINCT do not require rows to appear in any particular order as long
3616 ** as equivalent rows are grouped together. Thus for GROUP BY and DISTINCT
3617 ** the pOrderBy terms can be matched in any order. With ORDER BY, the
3618 ** pOrderBy terms must be matched in strict left-to-right order.
3619 */
wherePathSatisfiesOrderBy(WhereInfo * pWInfo,ExprList * pOrderBy,WherePath * pPath,u16 wctrlFlags,u16 nLoop,WhereLoop * pLast,Bitmask * pRevMask)3620 static i8 wherePathSatisfiesOrderBy(
3621 WhereInfo *pWInfo, /* The WHERE clause */
3622 ExprList *pOrderBy, /* ORDER BY or GROUP BY or DISTINCT clause to check */
3623 WherePath *pPath, /* The WherePath to check */
3624 u16 wctrlFlags, /* WHERE_GROUPBY or _DISTINCTBY or _ORDERBY_LIMIT */
3625 u16 nLoop, /* Number of entries in pPath->aLoop[] */
3626 WhereLoop *pLast, /* Add this WhereLoop to the end of pPath->aLoop[] */
3627 Bitmask *pRevMask /* OUT: Mask of WhereLoops to run in reverse order */
3628 ){
3629 u8 revSet; /* True if rev is known */
3630 u8 rev; /* Composite sort order */
3631 u8 revIdx; /* Index sort order */
3632 u8 isOrderDistinct; /* All prior WhereLoops are order-distinct */
3633 u8 distinctColumns; /* True if the loop has UNIQUE NOT NULL columns */
3634 u8 isMatch; /* iColumn matches a term of the ORDER BY clause */
3635 u16 eqOpMask; /* Allowed equality operators */
3636 u16 nKeyCol; /* Number of key columns in pIndex */
3637 u16 nColumn; /* Total number of ordered columns in the index */
3638 u16 nOrderBy; /* Number terms in the ORDER BY clause */
3639 int iLoop; /* Index of WhereLoop in pPath being processed */
3640 int i, j; /* Loop counters */
3641 int iCur; /* Cursor number for current WhereLoop */
3642 int iColumn; /* A column number within table iCur */
3643 WhereLoop *pLoop = 0; /* Current WhereLoop being processed. */
3644 WhereTerm *pTerm; /* A single term of the WHERE clause */
3645 Expr *pOBExpr; /* An expression from the ORDER BY clause */
3646 CollSeq *pColl; /* COLLATE function from an ORDER BY clause term */
3647 Index *pIndex; /* The index associated with pLoop */
3648 sqlite3 *db = pWInfo->pParse->db; /* Database connection */
3649 Bitmask obSat = 0; /* Mask of ORDER BY terms satisfied so far */
3650 Bitmask obDone; /* Mask of all ORDER BY terms */
3651 Bitmask orderDistinctMask; /* Mask of all well-ordered loops */
3652 Bitmask ready; /* Mask of inner loops */
3653
3654 /*
3655 ** We say the WhereLoop is "one-row" if it generates no more than one
3656 ** row of output. A WhereLoop is one-row if all of the following are true:
3657 ** (a) All index columns match with WHERE_COLUMN_EQ.
3658 ** (b) The index is unique
3659 ** Any WhereLoop with an WHERE_COLUMN_EQ constraint on the rowid is one-row.
3660 ** Every one-row WhereLoop will have the WHERE_ONEROW bit set in wsFlags.
3661 **
3662 ** We say the WhereLoop is "order-distinct" if the set of columns from
3663 ** that WhereLoop that are in the ORDER BY clause are different for every
3664 ** row of the WhereLoop. Every one-row WhereLoop is automatically
3665 ** order-distinct. A WhereLoop that has no columns in the ORDER BY clause
3666 ** is not order-distinct. To be order-distinct is not quite the same as being
3667 ** UNIQUE since a UNIQUE column or index can have multiple rows that
3668 ** are NULL and NULL values are equivalent for the purpose of order-distinct.
3669 ** To be order-distinct, the columns must be UNIQUE and NOT NULL.
3670 **
3671 ** The rowid for a table is always UNIQUE and NOT NULL so whenever the
3672 ** rowid appears in the ORDER BY clause, the corresponding WhereLoop is
3673 ** automatically order-distinct.
3674 */
3675
3676 assert( pOrderBy!=0 );
3677 if (nLoop && OptimizationDisabled(db, SQLITE_OrderByIdxJoin)) return 0;
3678
3679 nOrderBy = pOrderBy->nExpr;
3680 testcase( nOrderBy==BMS-1 );
3681 if (nOrderBy>BMS-1) return 0; /* Cannot optimize overly large ORDER BYs */
3682 isOrderDistinct = 1;
3683 obDone = MASKBIT(nOrderBy)-1;
3684 orderDistinctMask = 0;
3685 ready = 0;
3686 eqOpMask = WO_EQ | WO_IS | WO_ISNULL;
3687 if (wctrlFlags & WHERE_ORDERBY_LIMIT) eqOpMask |= WO_IN;
3688 for (iLoop=0; isOrderDistinct && obSat<obDone && iLoop<=nLoop; iLoop++) {
3689 if (iLoop>0) ready |= pLoop->maskSelf;
3690 if (iLoop<nLoop) {
3691 pLoop = pPath->aLoop[iLoop];
3692 if (wctrlFlags & WHERE_ORDERBY_LIMIT) continue;
3693 } else {
3694 pLoop = pLast;
3695 }
3696 if (pLoop->wsFlags & WHERE_VIRTUALTABLE) {
3697 if (pLoop->u.vtab.isOrdered) obSat = obDone;
3698 break;
3699 } else if (wctrlFlags & WHERE_DISTINCTBY) {
3700 pLoop->u.btree.nDistinctCol = 0;
3701 }
3702 iCur = pWInfo->pTabList->a[pLoop->iTab].iCursor;
3703
3704 /* Mark off any ORDER BY term X that is a column in the table of
3705 ** the current loop for which there is term in the WHERE
3706 ** clause of the form X IS NULL or X=? that reference only outer
3707 ** loops.
3708 */
3709 for (i=0; i<nOrderBy; i++) {
3710 if (MASKBIT(i) & obSat) continue;
3711 pOBExpr = sqlite3ExprSkipCollate(pOrderBy->a[i].pExpr);
3712 if (pOBExpr->op!=TK_COLUMN) continue;
3713 if (pOBExpr->iTable!=iCur) continue;
3714 pTerm = sqlite3WhereFindTerm(&pWInfo->sWC, iCur, pOBExpr->iColumn,
3715 ~ready, eqOpMask, 0);
3716 if (pTerm==0) continue;
3717 if (pTerm->eOperator==WO_IN) {
3718 /* IN terms are only valid for sorting in the ORDER BY LIMIT
3719 ** optimization, and then only if they are actually used
3720 ** by the query plan */
3721 assert( wctrlFlags & WHERE_ORDERBY_LIMIT );
3722 for (j=0; j<pLoop->nLTerm && pTerm!=pLoop->aLTerm[j]; j++) {}
3723 if (j>=pLoop->nLTerm) continue;
3724 }
3725 if ((pTerm->eOperator&(WO_EQ|WO_IS))!=0 && pOBExpr->iColumn>=0) {
3726 if (sqlite3ExprCollSeqMatch(pWInfo->pParse,
3727 pOrderBy->a[i].pExpr, pTerm->pExpr)==0) {
3728 continue;
3729 }
3730 testcase( pTerm->pExpr->op==TK_IS );
3731 }
3732 obSat |= MASKBIT(i);
3733 }
3734
3735 if ((pLoop->wsFlags & WHERE_ONEROW)==0) {
3736 if (pLoop->wsFlags & WHERE_IPK) {
3737 pIndex = 0;
3738 nKeyCol = 0;
3739 nColumn = 1;
3740 } else if ((pIndex = pLoop->u.btree.pIndex)==0 || pIndex->bUnordered) {
3741 return 0;
3742 } else {
3743 nKeyCol = pIndex->nKeyCol;
3744 nColumn = pIndex->nColumn;
3745 assert( nColumn==nKeyCol+1 || !HasRowid(pIndex->pTable));
3746 assert( pIndex->aiColumn[nColumn-1]==XN_ROWID
3747 || !HasRowid(pIndex->pTable));
3748 isOrderDistinct = IsUniqueIndex(pIndex)
3749 && (pLoop->wsFlags & WHERE_SKIPSCAN)==0;
3750 }
3751
3752 /* Loop through all columns of the index and deal with the ones
3753 ** that are not constrained by == or IN.
3754 */
3755 rev = revSet = 0;
3756 distinctColumns = 0;
3757 for (j=0; j<nColumn; j++) {
3758 u8 bOnce = 1; /* True to run the ORDER BY search loop */
3759
3760 assert( j>=pLoop->u.btree.nEq
3761 || (pLoop->aLTerm[j]==0)==(j<pLoop->nSkip)
3762 );
3763 if (j<pLoop->u.btree.nEq && j>=pLoop->nSkip) {
3764 u16 eOp = pLoop->aLTerm[j]->eOperator;
3765
3766 /* Skip over == and IS and ISNULL terms. (Also skip IN terms when
3767 ** doing WHERE_ORDERBY_LIMIT processing).
3768 **
3769 ** If the current term is a column of an ((?,?) IN (SELECT...))
3770 ** expression for which the SELECT returns more than one column,
3771 ** check that it is the only column used by this loop. Otherwise,
3772 ** if it is one of two or more, none of the columns can be
3773 ** considered to match an ORDER BY term. */
3774 if ((eOp & eqOpMask)!=0) {
3775 if (eOp & WO_ISNULL) {
3776 testcase( isOrderDistinct );
3777 isOrderDistinct = 0;
3778 }
3779 continue;
3780 } else if (ALWAYS(eOp & WO_IN)) {
3781 /* ALWAYS() justification: eOp is an equality operator due to the
3782 ** j<pLoop->u.btree.nEq constraint above. Any equality other
3783 ** than WO_IN is captured by the previous "if". So this one
3784 ** always has to be WO_IN. */
3785 Expr *pX = pLoop->aLTerm[j]->pExpr;
3786 for (i=j+1; i<pLoop->u.btree.nEq; i++) {
3787 if (pLoop->aLTerm[i]->pExpr==pX) {
3788 assert((pLoop->aLTerm[i]->eOperator & WO_IN));
3789 bOnce = 0;
3790 break;
3791 }
3792 }
3793 }
3794 }
3795
3796 /* Get the column number in the table (iColumn) and sort order
3797 ** (revIdx) for the j-th column of the index.
3798 */
3799 if (pIndex) {
3800 iColumn = pIndex->aiColumn[j];
3801 revIdx = pIndex->aSortOrder[j];
3802 if (iColumn==pIndex->pTable->iPKey) iColumn = XN_ROWID;
3803 } else {
3804 iColumn = XN_ROWID;
3805 revIdx = 0;
3806 }
3807
3808 /* An unconstrained column that might be NULL means that this
3809 ** WhereLoop is not well-ordered
3810 */
3811 if (isOrderDistinct
3812 && iColumn>=0
3813 && j>=pLoop->u.btree.nEq
3814 && pIndex->pTable->aCol[iColumn].notNull==0
3815 ) {
3816 isOrderDistinct = 0;
3817 }
3818
3819 /* Find the ORDER BY term that corresponds to the j-th column
3820 ** of the index and mark that ORDER BY term off
3821 */
3822 isMatch = 0;
3823 for (i=0; bOnce && i<nOrderBy; i++) {
3824 if (MASKBIT(i) & obSat) continue;
3825 pOBExpr = sqlite3ExprSkipCollate(pOrderBy->a[i].pExpr);
3826 testcase( wctrlFlags & WHERE_GROUPBY );
3827 testcase( wctrlFlags & WHERE_DISTINCTBY );
3828 if ((wctrlFlags & (WHERE_GROUPBY|WHERE_DISTINCTBY))==0) bOnce = 0;
3829 if (iColumn>=XN_ROWID) {
3830 if (pOBExpr->op!=TK_COLUMN) continue;
3831 if (pOBExpr->iTable!=iCur) continue;
3832 if (pOBExpr->iColumn!=iColumn) continue;
3833 } else {
3834 Expr *pIdxExpr = pIndex->aColExpr->a[j].pExpr;
3835 if (sqlite3ExprCompareSkip(pOBExpr, pIdxExpr, iCur)) {
3836 continue;
3837 }
3838 }
3839 if (iColumn!=XN_ROWID) {
3840 pColl = sqlite3ExprNNCollSeq(pWInfo->pParse, pOrderBy->a[i].pExpr);
3841 if (sqlite3StrICmp(pColl->zName, pIndex->azColl[j])!=0) continue;
3842 }
3843 if (wctrlFlags & WHERE_DISTINCTBY) {
3844 pLoop->u.btree.nDistinctCol = j+1;
3845 }
3846 isMatch = 1;
3847 break;
3848 }
3849 if (isMatch && (wctrlFlags & WHERE_GROUPBY)==0) {
3850 /* Make sure the sort order is compatible in an ORDER BY clause.
3851 ** Sort order is irrelevant for a GROUP BY clause. */
3852 if (revSet) {
3853 if ((rev ^ revIdx)!=pOrderBy->a[i].sortOrder) isMatch = 0;
3854 } else {
3855 rev = revIdx ^ pOrderBy->a[i].sortOrder;
3856 if (rev) *pRevMask |= MASKBIT(iLoop);
3857 revSet = 1;
3858 }
3859 }
3860 if (isMatch) {
3861 if (iColumn==XN_ROWID) {
3862 testcase( distinctColumns==0 );
3863 distinctColumns = 1;
3864 }
3865 obSat |= MASKBIT(i);
3866 if ((wctrlFlags & WHERE_ORDERBY_MIN) && j==pLoop->u.btree.nEq) {
3867 pLoop->wsFlags |= WHERE_MIN_ORDERED;
3868 }
3869 } else {
3870 /* No match found */
3871 if (j==0 || j<nKeyCol) {
3872 testcase( isOrderDistinct!=0 );
3873 isOrderDistinct = 0;
3874 }
3875 break;
3876 }
3877 } /* end Loop over all index columns */
3878 if (distinctColumns) {
3879 testcase( isOrderDistinct==0 );
3880 isOrderDistinct = 1;
3881 }
3882 } /* end-if not one-row */
3883
3884 /* Mark off any other ORDER BY terms that reference pLoop */
3885 if (isOrderDistinct) {
3886 orderDistinctMask |= pLoop->maskSelf;
3887 for (i=0; i<nOrderBy; i++) {
3888 Expr *p;
3889 Bitmask mTerm;
3890 if (MASKBIT(i) & obSat) continue;
3891 p = pOrderBy->a[i].pExpr;
3892 mTerm = sqlite3WhereExprUsage(&pWInfo->sMaskSet,p);
3893 if (mTerm==0 && !sqlite3ExprIsConstant(p)) continue;
3894 if ((mTerm&~orderDistinctMask)==0) {
3895 obSat |= MASKBIT(i);
3896 }
3897 }
3898 }
3899 } /* End the loop over all WhereLoops from outer-most down to inner-most */
3900 if (obSat==obDone) return (i8)nOrderBy;
3901 if (!isOrderDistinct) {
3902 for (i=nOrderBy-1; i>0; i--) {
3903 Bitmask m = MASKBIT(i) - 1;
3904 if ((obSat&m)==m) return i;
3905 }
3906 return 0;
3907 }
3908 return -1;
3909 }
3910
3911
3912 /*
3913 ** If the WHERE_GROUPBY flag is set in the mask passed to sqlite3WhereBegin(),
3914 ** the planner assumes that the specified pOrderBy list is actually a GROUP
3915 ** BY clause - and so any order that groups rows as required satisfies the
3916 ** request.
3917 **
3918 ** Normally, in this case it is not possible for the caller to determine
3919 ** whether or not the rows are really being delivered in sorted order, or
3920 ** just in some other order that provides the required grouping. However,
3921 ** if the WHERE_SORTBYGROUP flag is also passed to sqlite3WhereBegin(), then
3922 ** this function may be called on the returned WhereInfo object. It returns
3923 ** true if the rows really will be sorted in the specified order, or false
3924 ** otherwise.
3925 **
3926 ** For example, assuming:
3927 **
3928 ** CREATE INDEX i1 ON t1(x, Y);
3929 **
3930 ** then
3931 **
3932 ** SELECT * FROM t1 GROUP BY x,y ORDER BY x,y; -- IsSorted()==1
3933 ** SELECT * FROM t1 GROUP BY y,x ORDER BY y,x; -- IsSorted()==0
3934 */
sqlite3WhereIsSorted(WhereInfo * pWInfo)3935 int sqlite3WhereIsSorted(WhereInfo *pWInfo){
3936 assert( pWInfo->wctrlFlags & WHERE_GROUPBY );
3937 assert( pWInfo->wctrlFlags & WHERE_SORTBYGROUP );
3938 return pWInfo->sorted;
3939 }
3940
3941 #ifdef WHERETRACE_ENABLED
3942 /* For debugging use only: */
wherePathName(WherePath * pPath,int nLoop,WhereLoop * pLast)3943 static const char *wherePathName(WherePath *pPath, int nLoop, WhereLoop *pLast){
3944 static char zName[65];
3945 int i;
3946 for (i=0; i<nLoop; i++) { zName[i] = pPath->aLoop[i]->cId; }
3947 if (pLast) zName[i++] = pLast->cId;
3948 zName[i] = 0;
3949 return zName;
3950 }
3951 #endif
3952
3953 /*
3954 ** Return the cost of sorting nRow rows, assuming that the keys have
3955 ** nOrderby columns and that the first nSorted columns are already in
3956 ** order.
3957 */
whereSortingCost(WhereInfo * pWInfo,LogEst nRow,int nOrderBy,int nSorted)3958 static LogEst whereSortingCost(
3959 WhereInfo *pWInfo,
3960 LogEst nRow,
3961 int nOrderBy,
3962 int nSorted
3963 ){
3964 /* TUNING: Estimated cost of a full external sort, where N is
3965 ** the number of rows to sort is:
3966 **
3967 ** cost = (3.0 * N * log(N)).
3968 **
3969 ** Or, if the order-by clause has X terms but only the last Y
3970 ** terms are out of order, then block-sorting will reduce the
3971 ** sorting cost to:
3972 **
3973 ** cost = (3.0 * N * log(N)) * (Y/X)
3974 **
3975 ** The (Y/X) term is implemented using stack variable rScale
3976 ** below. */
3977 LogEst rScale, rSortCost;
3978 assert( nOrderBy>0 && 66==sqlite3LogEst(100));
3979 rScale = sqlite3LogEst((nOrderBy-nSorted)*100/nOrderBy) - 66;
3980 rSortCost = nRow + rScale + 16;
3981
3982 /* Multiple by log(M) where M is the number of output rows.
3983 ** Use the LIMIT for M if it is smaller */
3984 if ((pWInfo->wctrlFlags & WHERE_USE_LIMIT)!=0 && pWInfo->iLimit<nRow) {
3985 nRow = pWInfo->iLimit;
3986 }
3987 rSortCost += estLog(nRow);
3988 return rSortCost;
3989 }
3990
3991 /*
3992 ** Given the list of WhereLoop objects at pWInfo->pLoops, this routine
3993 ** attempts to find the lowest cost path that visits each WhereLoop
3994 ** once. This path is then loaded into the pWInfo->a[].pWLoop fields.
3995 **
3996 ** Assume that the total number of output rows that will need to be sorted
3997 ** will be nRowEst (in the 10*log2 representation). Or, ignore sorting
3998 ** costs if nRowEst==0.
3999 **
4000 ** Return SQLITE_OK on success or SQLITE_NOMEM of a memory allocation
4001 ** error occurs.
4002 */
wherePathSolver(WhereInfo * pWInfo,LogEst nRowEst)4003 static int wherePathSolver(WhereInfo *pWInfo, LogEst nRowEst){
4004 int mxChoice; /* Maximum number of simultaneous paths tracked */
4005 int nLoop; /* Number of terms in the join */
4006 Parse *pParse; /* Parsing context */
4007 sqlite3 *db; /* The database connection */
4008 int iLoop; /* Loop counter over the terms of the join */
4009 int ii, jj; /* Loop counters */
4010 int mxI = 0; /* Index of next entry to replace */
4011 int nOrderBy; /* Number of ORDER BY clause terms */
4012 LogEst mxCost = 0; /* Maximum cost of a set of paths */
4013 LogEst mxUnsorted = 0; /* Maximum unsorted cost of a set of path */
4014 int nTo, nFrom; /* Number of valid entries in aTo[] and aFrom[] */
4015 WherePath *aFrom; /* All nFrom paths at the previous level */
4016 WherePath *aTo; /* The nTo best paths at the current level */
4017 WherePath *pFrom; /* An element of aFrom[] that we are working on */
4018 WherePath *pTo; /* An element of aTo[] that we are working on */
4019 WhereLoop *pWLoop; /* One of the WhereLoop objects */
4020 WhereLoop **pX; /* Used to divy up the pSpace memory */
4021 LogEst *aSortCost = 0; /* Sorting and partial sorting costs */
4022 char *pSpace; /* Temporary memory used by this routine */
4023 int nSpace; /* Bytes of space allocated at pSpace */
4024
4025 pParse = pWInfo->pParse;
4026 db = pParse->db;
4027 nLoop = pWInfo->nLevel;
4028 /* TUNING: For simple queries, only the best path is tracked.
4029 ** For 2-way joins, the 5 best paths are followed.
4030 ** For joins of 3 or more tables, track the 10 best paths */
4031 mxChoice = (nLoop<=1) ? 1 : (nLoop==2 ? 5 : 10);
4032 assert( nLoop<=pWInfo->pTabList->nSrc );
4033 WHERETRACE(0x002, ("---- begin solver. (nRowEst=%d)\n", nRowEst));
4034
4035 /* If nRowEst is zero and there is an ORDER BY clause, ignore it. In this
4036 ** case the purpose of this call is to estimate the number of rows returned
4037 ** by the overall query. Once this estimate has been obtained, the caller
4038 ** will invoke this function a second time, passing the estimate as the
4039 ** nRowEst parameter. */
4040 if (pWInfo->pOrderBy==0 || nRowEst==0) {
4041 nOrderBy = 0;
4042 } else {
4043 nOrderBy = pWInfo->pOrderBy->nExpr;
4044 }
4045
4046 /* Allocate and initialize space for aTo, aFrom and aSortCost[] */
4047 nSpace = (sizeof(WherePath)+sizeof(WhereLoop*)*nLoop)*mxChoice*2;
4048 nSpace += sizeof(LogEst) * nOrderBy;
4049 pSpace = sqlite3DbMallocRawNN(db, nSpace);
4050 if (pSpace==0) return SQLITE_NOMEM_BKPT;
4051 aTo = (WherePath*)pSpace;
4052 aFrom = aTo+mxChoice;
4053 memset(aFrom, 0, sizeof(aFrom[0]));
4054 pX = (WhereLoop**)(aFrom+mxChoice);
4055 for (ii=mxChoice*2, pFrom=aTo; ii>0; ii--, pFrom++, pX += nLoop) {
4056 pFrom->aLoop = pX;
4057 }
4058 if (nOrderBy) {
4059 /* If there is an ORDER BY clause and it is not being ignored, set up
4060 ** space for the aSortCost[] array. Each element of the aSortCost array
4061 ** is either zero - meaning it has not yet been initialized - or the
4062 ** cost of sorting nRowEst rows of data where the first X terms of
4063 ** the ORDER BY clause are already in order, where X is the array
4064 ** index. */
4065 aSortCost = (LogEst*)pX;
4066 memset(aSortCost, 0, sizeof(LogEst) * nOrderBy);
4067 }
4068 assert( aSortCost==0 || &pSpace[nSpace]==(char*)&aSortCost[nOrderBy] );
4069 assert( aSortCost!=0 || &pSpace[nSpace]==(char*)pX );
4070
4071 /* Seed the search with a single WherePath containing zero WhereLoops.
4072 **
4073 ** TUNING: Do not let the number of iterations go above 28. If the cost
4074 ** of computing an automatic index is not paid back within the first 28
4075 ** rows, then do not use the automatic index. */
4076 aFrom[0].nRow = MIN(pParse->nQueryLoop, 48); assert( 48==sqlite3LogEst(28));
4077 nFrom = 1;
4078 assert( aFrom[0].isOrdered==0 );
4079 if (nOrderBy) {
4080 /* If nLoop is zero, then there are no FROM terms in the query. Since
4081 ** in this case the query may return a maximum of one row, the results
4082 ** are already in the requested order. Set isOrdered to nOrderBy to
4083 ** indicate this. Or, if nLoop is greater than zero, set isOrdered to
4084 ** -1, indicating that the result set may or may not be ordered,
4085 ** depending on the loops added to the current plan. */
4086 aFrom[0].isOrdered = nLoop>0 ? -1 : nOrderBy;
4087 }
4088
4089 /* Compute successively longer WherePaths using the previous generation
4090 ** of WherePaths as the basis for the next. Keep track of the mxChoice
4091 ** best paths at each generation */
4092 for (iLoop=0; iLoop<nLoop; iLoop++) {
4093 nTo = 0;
4094 for (ii=0, pFrom=aFrom; ii<nFrom; ii++, pFrom++) {
4095 for (pWLoop=pWInfo->pLoops; pWLoop; pWLoop=pWLoop->pNextLoop) {
4096 LogEst nOut; /* Rows visited by (pFrom+pWLoop) */
4097 LogEst rCost; /* Cost of path (pFrom+pWLoop) */
4098 LogEst rUnsorted; /* Unsorted cost of (pFrom+pWLoop) */
4099 i8 isOrdered = pFrom->isOrdered; /* isOrdered for (pFrom+pWLoop) */
4100 Bitmask maskNew; /* Mask of src visited by (..) */
4101 Bitmask revMask = 0; /* Mask of rev-order loops for (..) */
4102
4103 if ((pWLoop->prereq & ~pFrom->maskLoop)!=0) continue;
4104 if ((pWLoop->maskSelf & pFrom->maskLoop)!=0) continue;
4105 if ((pWLoop->wsFlags & WHERE_AUTO_INDEX)!=0 && pFrom->nRow<3) {
4106 /* Do not use an automatic index if the this loop is expected
4107 ** to run less than 1.25 times. It is tempting to also exclude
4108 ** automatic index usage on an outer loop, but sometimes an automatic
4109 ** index is useful in the outer loop of a correlated subquery. */
4110 assert( 10==sqlite3LogEst(2));
4111 continue;
4112 }
4113
4114 /* At this point, pWLoop is a candidate to be the next loop.
4115 ** Compute its cost */
4116 rUnsorted = sqlite3LogEstAdd(pWLoop->rSetup,pWLoop->rRun + pFrom->nRow);
4117 rUnsorted = sqlite3LogEstAdd(rUnsorted, pFrom->rUnsorted);
4118 nOut = pFrom->nRow + pWLoop->nOut;
4119 maskNew = pFrom->maskLoop | pWLoop->maskSelf;
4120 if (isOrdered<0) {
4121 isOrdered = wherePathSatisfiesOrderBy(pWInfo,
4122 pWInfo->pOrderBy, pFrom, pWInfo->wctrlFlags,
4123 iLoop, pWLoop, &revMask);
4124 } else {
4125 revMask = pFrom->revLoop;
4126 }
4127 if (isOrdered>=0 && isOrdered<nOrderBy) {
4128 if (aSortCost[isOrdered]==0) {
4129 aSortCost[isOrdered] = whereSortingCost(
4130 pWInfo, nRowEst, nOrderBy, isOrdered
4131 );
4132 }
4133 /* TUNING: Add a small extra penalty (5) to sorting as an
4134 ** extra encouragment to the query planner to select a plan
4135 ** where the rows emerge in the correct order without any sorting
4136 ** required. */
4137 rCost = sqlite3LogEstAdd(rUnsorted, aSortCost[isOrdered]) + 5;
4138
4139 WHERETRACE(0x002,
4140 ("---- sort cost=%-3d (%d/%d) increases cost %3d to %-3d\n",
4141 aSortCost[isOrdered], (nOrderBy-isOrdered), nOrderBy,
4142 rUnsorted, rCost));
4143 } else {
4144 rCost = rUnsorted;
4145 rUnsorted -= 2; /* TUNING: Slight bias in favor of no-sort plans */
4146 }
4147
4148 /* Check to see if pWLoop should be added to the set of
4149 ** mxChoice best-so-far paths.
4150 **
4151 ** First look for an existing path among best-so-far paths
4152 ** that covers the same set of loops and has the same isOrdered
4153 ** setting as the current path candidate.
4154 **
4155 ** The term "((pTo->isOrdered^isOrdered)&0x80)==0" is equivalent
4156 ** to (pTo->isOrdered==(-1))==(isOrdered==(-1))" for the range
4157 ** of legal values for isOrdered, -1..64.
4158 */
4159 for (jj=0, pTo=aTo; jj<nTo; jj++, pTo++) {
4160 if (pTo->maskLoop==maskNew
4161 && ((pTo->isOrdered^isOrdered)&0x80)==0
4162 ) {
4163 testcase( jj==nTo-1 );
4164 break;
4165 }
4166 }
4167 if (jj>=nTo) {
4168 /* None of the existing best-so-far paths match the candidate. */
4169 if (nTo>=mxChoice
4170 && (rCost>mxCost || (rCost==mxCost && rUnsorted>=mxUnsorted))
4171 ) {
4172 /* The current candidate is no better than any of the mxChoice
4173 ** paths currently in the best-so-far buffer. So discard
4174 ** this candidate as not viable. */
4175 #ifdef WHERETRACE_ENABLED /* 0x4 */
4176 if (sqlite3WhereTrace&0x4) {
4177 sqlite3DebugPrintf("Skip %s cost=%-3d,%3d,%3d order=%c\n",
4178 wherePathName(pFrom, iLoop, pWLoop), rCost, nOut, rUnsorted,
4179 isOrdered>=0 ? isOrdered+'0' : '?');
4180 }
4181 #endif
4182 continue;
4183 }
4184 /* If we reach this points it means that the new candidate path
4185 ** needs to be added to the set of best-so-far paths. */
4186 if (nTo<mxChoice) {
4187 /* Increase the size of the aTo set by one */
4188 jj = nTo++;
4189 } else {
4190 /* New path replaces the prior worst to keep count below mxChoice */
4191 jj = mxI;
4192 }
4193 pTo = &aTo[jj];
4194 #ifdef WHERETRACE_ENABLED /* 0x4 */
4195 if (sqlite3WhereTrace&0x4) {
4196 sqlite3DebugPrintf("New %s cost=%-3d,%3d,%3d order=%c\n",
4197 wherePathName(pFrom, iLoop, pWLoop), rCost, nOut, rUnsorted,
4198 isOrdered>=0 ? isOrdered+'0' : '?');
4199 }
4200 #endif
4201 } else {
4202 /* Control reaches here if best-so-far path pTo=aTo[jj] covers the
4203 ** same set of loops and has the same isOrdered setting as the
4204 ** candidate path. Check to see if the candidate should replace
4205 ** pTo or if the candidate should be skipped.
4206 **
4207 ** The conditional is an expanded vector comparison equivalent to:
4208 ** (pTo->rCost,pTo->nRow,pTo->rUnsorted) <= (rCost,nOut,rUnsorted)
4209 */
4210 if (pTo->rCost<rCost
4211 || (pTo->rCost==rCost
4212 && (pTo->nRow<nOut
4213 || (pTo->nRow==nOut && pTo->rUnsorted<=rUnsorted)
4214 )
4215 )
4216 ) {
4217 #ifdef WHERETRACE_ENABLED /* 0x4 */
4218 if (sqlite3WhereTrace&0x4) {
4219 sqlite3DebugPrintf(
4220 "Skip %s cost=%-3d,%3d,%3d order=%c",
4221 wherePathName(pFrom, iLoop, pWLoop), rCost, nOut, rUnsorted,
4222 isOrdered>=0 ? isOrdered+'0' : '?');
4223 sqlite3DebugPrintf(" vs %s cost=%-3d,%3d,%3d order=%c\n",
4224 wherePathName(pTo, iLoop+1, 0), pTo->rCost, pTo->nRow,
4225 pTo->rUnsorted, pTo->isOrdered>=0 ? pTo->isOrdered+'0' : '?');
4226 }
4227 #endif
4228 /* Discard the candidate path from further consideration */
4229 testcase( pTo->rCost==rCost );
4230 continue;
4231 }
4232 testcase( pTo->rCost==rCost+1 );
4233 /* Control reaches here if the candidate path is better than the
4234 ** pTo path. Replace pTo with the candidate. */
4235 #ifdef WHERETRACE_ENABLED /* 0x4 */
4236 if (sqlite3WhereTrace&0x4) {
4237 sqlite3DebugPrintf(
4238 "Update %s cost=%-3d,%3d,%3d order=%c",
4239 wherePathName(pFrom, iLoop, pWLoop), rCost, nOut, rUnsorted,
4240 isOrdered>=0 ? isOrdered+'0' : '?');
4241 sqlite3DebugPrintf(" was %s cost=%-3d,%3d,%3d order=%c\n",
4242 wherePathName(pTo, iLoop+1, 0), pTo->rCost, pTo->nRow,
4243 pTo->rUnsorted, pTo->isOrdered>=0 ? pTo->isOrdered+'0' : '?');
4244 }
4245 #endif
4246 }
4247 /* pWLoop is a winner. Add it to the set of best so far */
4248 pTo->maskLoop = pFrom->maskLoop | pWLoop->maskSelf;
4249 pTo->revLoop = revMask;
4250 pTo->nRow = nOut;
4251 pTo->rCost = rCost;
4252 pTo->rUnsorted = rUnsorted;
4253 pTo->isOrdered = isOrdered;
4254 memcpy(pTo->aLoop, pFrom->aLoop, sizeof(WhereLoop*)*iLoop);
4255 pTo->aLoop[iLoop] = pWLoop;
4256 if (nTo>=mxChoice) {
4257 mxI = 0;
4258 mxCost = aTo[0].rCost;
4259 mxUnsorted = aTo[0].nRow;
4260 for (jj=1, pTo=&aTo[1]; jj<mxChoice; jj++, pTo++) {
4261 if (pTo->rCost>mxCost
4262 || (pTo->rCost==mxCost && pTo->rUnsorted>mxUnsorted)
4263 ) {
4264 mxCost = pTo->rCost;
4265 mxUnsorted = pTo->rUnsorted;
4266 mxI = jj;
4267 }
4268 }
4269 }
4270 }
4271 }
4272
4273 #ifdef WHERETRACE_ENABLED /* >=2 */
4274 if (sqlite3WhereTrace & 0x02) {
4275 sqlite3DebugPrintf("---- after round %d ----\n", iLoop);
4276 for (ii=0, pTo=aTo; ii<nTo; ii++, pTo++) {
4277 sqlite3DebugPrintf(" %s cost=%-3d nrow=%-3d order=%c",
4278 wherePathName(pTo, iLoop+1, 0), pTo->rCost, pTo->nRow,
4279 pTo->isOrdered>=0 ? (pTo->isOrdered+'0') : '?');
4280 if (pTo->isOrdered>0) {
4281 sqlite3DebugPrintf(" rev=0x%llx\n", pTo->revLoop);
4282 } else {
4283 sqlite3DebugPrintf("\n");
4284 }
4285 }
4286 }
4287 #endif
4288
4289 /* Swap the roles of aFrom and aTo for the next generation */
4290 pFrom = aTo;
4291 aTo = aFrom;
4292 aFrom = pFrom;
4293 nFrom = nTo;
4294 }
4295
4296 if (nFrom==0) {
4297 sqlite3ErrorMsg(pParse, "no query solution");
4298 sqlite3DbFreeNN(db, pSpace);
4299 return SQLITE_ERROR;
4300 }
4301
4302 /* Find the lowest cost path. pFrom will be left pointing to that path */
4303 pFrom = aFrom;
4304 for (ii=1; ii<nFrom; ii++) {
4305 if (pFrom->rCost>aFrom[ii].rCost) pFrom = &aFrom[ii];
4306 }
4307 assert( pWInfo->nLevel==nLoop );
4308 /* Load the lowest cost path into pWInfo */
4309 for (iLoop=0; iLoop<nLoop; iLoop++) {
4310 WhereLevel *pLevel = pWInfo->a + iLoop;
4311 pLevel->pWLoop = pWLoop = pFrom->aLoop[iLoop];
4312 pLevel->iFrom = pWLoop->iTab;
4313 pLevel->iTabCur = pWInfo->pTabList->a[pLevel->iFrom].iCursor;
4314 }
4315 if ((pWInfo->wctrlFlags & WHERE_WANT_DISTINCT)!=0
4316 && (pWInfo->wctrlFlags & WHERE_DISTINCTBY)==0
4317 && pWInfo->eDistinct==WHERE_DISTINCT_NOOP
4318 && nRowEst
4319 ) {
4320 Bitmask notUsed;
4321 int rc = wherePathSatisfiesOrderBy(pWInfo, pWInfo->pResultSet, pFrom,
4322 WHERE_DISTINCTBY, nLoop-1, pFrom->aLoop[nLoop-1], ¬Used);
4323 if (rc==pWInfo->pResultSet->nExpr) {
4324 pWInfo->eDistinct = WHERE_DISTINCT_ORDERED;
4325 }
4326 }
4327 pWInfo->bOrderedInnerLoop = 0;
4328 if (pWInfo->pOrderBy) {
4329 if (pWInfo->wctrlFlags & WHERE_DISTINCTBY) {
4330 if (pFrom->isOrdered==pWInfo->pOrderBy->nExpr) {
4331 pWInfo->eDistinct = WHERE_DISTINCT_ORDERED;
4332 }
4333 } else {
4334 pWInfo->nOBSat = pFrom->isOrdered;
4335 pWInfo->revMask = pFrom->revLoop;
4336 if (pWInfo->nOBSat<=0) {
4337 pWInfo->nOBSat = 0;
4338 if (nLoop>0) {
4339 u32 wsFlags = pFrom->aLoop[nLoop-1]->wsFlags;
4340 if ((wsFlags & WHERE_ONEROW)==0
4341 && (wsFlags&(WHERE_IPK|WHERE_COLUMN_IN))!=(WHERE_IPK|WHERE_COLUMN_IN)
4342 ) {
4343 Bitmask m = 0;
4344 int rc = wherePathSatisfiesOrderBy(pWInfo, pWInfo->pOrderBy, pFrom,
4345 WHERE_ORDERBY_LIMIT, nLoop-1, pFrom->aLoop[nLoop-1], &m);
4346 testcase( wsFlags & WHERE_IPK );
4347 testcase( wsFlags & WHERE_COLUMN_IN );
4348 if (rc==pWInfo->pOrderBy->nExpr) {
4349 pWInfo->bOrderedInnerLoop = 1;
4350 pWInfo->revMask = m;
4351 }
4352 }
4353 }
4354 }
4355 }
4356 if ((pWInfo->wctrlFlags & WHERE_SORTBYGROUP)
4357 && pWInfo->nOBSat==pWInfo->pOrderBy->nExpr && nLoop>0
4358 ) {
4359 Bitmask revMask = 0;
4360 int nOrder = wherePathSatisfiesOrderBy(pWInfo, pWInfo->pOrderBy,
4361 pFrom, 0, nLoop-1, pFrom->aLoop[nLoop-1], &revMask
4362 );
4363 assert( pWInfo->sorted==0 );
4364 if (nOrder==pWInfo->pOrderBy->nExpr) {
4365 pWInfo->sorted = 1;
4366 pWInfo->revMask = revMask;
4367 }
4368 }
4369 }
4370
4371
4372 pWInfo->nRowOut = pFrom->nRow;
4373
4374 /* Free temporary memory and return success */
4375 sqlite3DbFreeNN(db, pSpace);
4376 return SQLITE_OK;
4377 }
4378
4379 /*
4380 ** Most queries use only a single table (they are not joins) and have
4381 ** simple == constraints against indexed fields. This routine attempts
4382 ** to plan those simple cases using much less ceremony than the
4383 ** general-purpose query planner, and thereby yield faster sqlite3_prepare()
4384 ** times for the common case.
4385 **
4386 ** Return non-zero on success, if this query can be handled by this
4387 ** no-frills query planner. Return zero if this query needs the
4388 ** general-purpose query planner.
4389 */
whereShortCut(WhereLoopBuilder * pBuilder)4390 static int whereShortCut(WhereLoopBuilder *pBuilder){
4391 WhereInfo *pWInfo;
4392 struct SrcList_item *pItem;
4393 WhereClause *pWC;
4394 WhereTerm *pTerm;
4395 WhereLoop *pLoop;
4396 int iCur;
4397 int j;
4398 Table *pTab;
4399 Index *pIdx;
4400
4401 pWInfo = pBuilder->pWInfo;
4402 if (pWInfo->wctrlFlags & WHERE_OR_SUBCLAUSE) return 0;
4403 assert( pWInfo->pTabList->nSrc>=1 );
4404 pItem = pWInfo->pTabList->a;
4405 pTab = pItem->pTab;
4406 if (IsVirtual(pTab)) return 0;
4407 if (pItem->fg.isIndexedBy) return 0;
4408 iCur = pItem->iCursor;
4409 pWC = &pWInfo->sWC;
4410 pLoop = pBuilder->pNew;
4411 pLoop->wsFlags = 0;
4412 pLoop->nSkip = 0;
4413 pTerm = sqlite3WhereFindTerm(pWC, iCur, -1, 0, WO_EQ|WO_IS, 0);
4414 if (pTerm) {
4415 testcase( pTerm->eOperator & WO_IS );
4416 pLoop->wsFlags = WHERE_COLUMN_EQ|WHERE_IPK|WHERE_ONEROW;
4417 pLoop->aLTerm[0] = pTerm;
4418 pLoop->nLTerm = 1;
4419 pLoop->u.btree.nEq = 1;
4420 /* TUNING: Cost of a rowid lookup is 10 */
4421 pLoop->rRun = 33; /* 33==sqlite3LogEst(10) */
4422 } else {
4423 for (pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext) {
4424 int opMask;
4425 assert( pLoop->aLTermSpace==pLoop->aLTerm );
4426 if (!IsUniqueIndex(pIdx)
4427 || pIdx->pPartIdxWhere!=0
4428 || pIdx->nKeyCol>ArraySize(pLoop->aLTermSpace)
4429 ) continue;
4430 opMask = pIdx->uniqNotNull ? (WO_EQ|WO_IS) : WO_EQ;
4431 for (j=0; j<pIdx->nKeyCol; j++) {
4432 pTerm = sqlite3WhereFindTerm(pWC, iCur, j, 0, opMask, pIdx);
4433 if (pTerm==0) break;
4434 testcase( pTerm->eOperator & WO_IS );
4435 pLoop->aLTerm[j] = pTerm;
4436 }
4437 if (j!=pIdx->nKeyCol) continue;
4438 pLoop->wsFlags = WHERE_COLUMN_EQ|WHERE_ONEROW|WHERE_INDEXED;
4439 if (pIdx->isCovering || (pItem->colUsed & pIdx->colNotIdxed)==0) {
4440 pLoop->wsFlags |= WHERE_IDX_ONLY;
4441 }
4442 pLoop->nLTerm = j;
4443 pLoop->u.btree.nEq = j;
4444 pLoop->u.btree.pIndex = pIdx;
4445 /* TUNING: Cost of a unique index lookup is 15 */
4446 pLoop->rRun = 39; /* 39==sqlite3LogEst(15) */
4447 break;
4448 }
4449 }
4450 if (pLoop->wsFlags) {
4451 pLoop->nOut = (LogEst)1;
4452 pWInfo->a[0].pWLoop = pLoop;
4453 assert( pWInfo->sMaskSet.n==1 && iCur==pWInfo->sMaskSet.ix[0] );
4454 pLoop->maskSelf = 1; /* sqlite3WhereGetMask(&pWInfo->sMaskSet, iCur); */
4455 pWInfo->a[0].iTabCur = iCur;
4456 pWInfo->nRowOut = 1;
4457 if (pWInfo->pOrderBy) pWInfo->nOBSat = pWInfo->pOrderBy->nExpr;
4458 if (pWInfo->wctrlFlags & WHERE_WANT_DISTINCT) {
4459 pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE;
4460 }
4461 #ifdef SQLITE_DEBUG
4462 pLoop->cId = '0';
4463 #endif
4464 return 1;
4465 }
4466 return 0;
4467 }
4468
4469 /*
4470 ** Helper function for exprIsDeterministic().
4471 */
exprNodeIsDeterministic(Walker * pWalker,Expr * pExpr)4472 static int exprNodeIsDeterministic(Walker *pWalker, Expr *pExpr){
4473 if (pExpr->op==TK_FUNCTION && ExprHasProperty(pExpr, EP_ConstFunc)==0) {
4474 pWalker->eCode = 0;
4475 return WRC_Abort;
4476 }
4477 return WRC_Continue;
4478 }
4479
4480 /*
4481 ** Return true if the expression contains no non-deterministic SQL
4482 ** functions. Do not consider non-deterministic SQL functions that are
4483 ** part of sub-select statements.
4484 */
exprIsDeterministic(Expr * p)4485 static int exprIsDeterministic(Expr *p){
4486 Walker w;
4487 memset(&w, 0, sizeof(w));
4488 w.eCode = 1;
4489 w.xExprCallback = exprNodeIsDeterministic;
4490 w.xSelectCallback = sqlite3SelectWalkFail;
4491 sqlite3WalkExpr(&w, p);
4492 return w.eCode;
4493 }
4494
4495 /*
4496 ** Generate the beginning of the loop used for WHERE clause processing.
4497 ** The return value is a pointer to an opaque structure that contains
4498 ** information needed to terminate the loop. Later, the calling routine
4499 ** should invoke sqlite3WhereEnd() with the return value of this function
4500 ** in order to complete the WHERE clause processing.
4501 **
4502 ** If an error occurs, this routine returns NULL.
4503 **
4504 ** The basic idea is to do a nested loop, one loop for each table in
4505 ** the FROM clause of a select. (INSERT and UPDATE statements are the
4506 ** same as a SELECT with only a single table in the FROM clause.) For
4507 ** example, if the SQL is this:
4508 **
4509 ** SELECT * FROM t1, t2, t3 WHERE ...;
4510 **
4511 ** Then the code generated is conceptually like the following:
4512 **
4513 ** foreach row1 in t1 do \ Code generated
4514 ** foreach row2 in t2 do |-- by sqlite3WhereBegin()
4515 ** foreach row3 in t3 do /
4516 ** ...
4517 ** end \ Code generated
4518 ** end |-- by sqlite3WhereEnd()
4519 ** end /
4520 **
4521 ** Note that the loops might not be nested in the order in which they
4522 ** appear in the FROM clause if a different order is better able to make
4523 ** use of indices. Note also that when the IN operator appears in
4524 ** the WHERE clause, it might result in additional nested loops for
4525 ** scanning through all values on the right-hand side of the IN.
4526 **
4527 ** There are Btree cursors associated with each table. t1 uses cursor
4528 ** number pTabList->a[0].iCursor. t2 uses the cursor pTabList->a[1].iCursor.
4529 ** And so forth. This routine generates code to open those VDBE cursors
4530 ** and sqlite3WhereEnd() generates the code to close them.
4531 **
4532 ** The code that sqlite3WhereBegin() generates leaves the cursors named
4533 ** in pTabList pointing at their appropriate entries. The [...] code
4534 ** can use OP_Column and OP_Rowid opcodes on these cursors to extract
4535 ** data from the various tables of the loop.
4536 **
4537 ** If the WHERE clause is empty, the foreach loops must each scan their
4538 ** entire tables. Thus a three-way join is an O(N^3) operation. But if
4539 ** the tables have indices and there are terms in the WHERE clause that
4540 ** refer to those indices, a complete table scan can be avoided and the
4541 ** code will run much faster. Most of the work of this routine is checking
4542 ** to see if there are indices that can be used to speed up the loop.
4543 **
4544 ** Terms of the WHERE clause are also used to limit which rows actually
4545 ** make it to the "..." in the middle of the loop. After each "foreach",
4546 ** terms of the WHERE clause that use only terms in that loop and outer
4547 ** loops are evaluated and if false a jump is made around all subsequent
4548 ** inner loops (or around the "..." if the test occurs within the inner-
4549 ** most loop)
4550 **
4551 ** OUTER JOINS
4552 **
4553 ** An outer join of tables t1 and t2 is conceptally coded as follows:
4554 **
4555 ** foreach row1 in t1 do
4556 ** flag = 0
4557 ** foreach row2 in t2 do
4558 ** start:
4559 ** ...
4560 ** flag = 1
4561 ** end
4562 ** if flag==0 then
4563 ** move the row2 cursor to a null row
4564 ** goto start
4565 ** fi
4566 ** end
4567 **
4568 ** ORDER BY CLAUSE PROCESSING
4569 **
4570 ** pOrderBy is a pointer to the ORDER BY clause (or the GROUP BY clause
4571 ** if the WHERE_GROUPBY flag is set in wctrlFlags) of a SELECT statement
4572 ** if there is one. If there is no ORDER BY clause or if this routine
4573 ** is called from an UPDATE or DELETE statement, then pOrderBy is NULL.
4574 **
4575 ** The iIdxCur parameter is the cursor number of an index. If
4576 ** WHERE_OR_SUBCLAUSE is set, iIdxCur is the cursor number of an index
4577 ** to use for OR clause processing. The WHERE clause should use this
4578 ** specific cursor. If WHERE_ONEPASS_DESIRED is set, then iIdxCur is
4579 ** the first cursor in an array of cursors for all indices. iIdxCur should
4580 ** be used to compute the appropriate cursor depending on which index is
4581 ** used.
4582 */
sqlite3WhereBegin(Parse * pParse,SrcList * pTabList,Expr * pWhere,ExprList * pOrderBy,ExprList * pResultSet,u16 wctrlFlags,int iAuxArg)4583 WhereInfo *sqlite3WhereBegin(
4584 Parse *pParse, /* The parser context */
4585 SrcList *pTabList, /* FROM clause: A list of all tables to be scanned */
4586 Expr *pWhere, /* The WHERE clause */
4587 ExprList *pOrderBy, /* An ORDER BY (or GROUP BY) clause, or NULL */
4588 ExprList *pResultSet, /* Query result set. Req'd for DISTINCT */
4589 u16 wctrlFlags, /* The WHERE_* flags defined in sqliteInt.h */
4590 int iAuxArg /* If WHERE_OR_SUBCLAUSE is set, index cursor number
4591 ** If WHERE_USE_LIMIT, then the limit amount */
4592 ){
4593 int nByteWInfo; /* Num. bytes allocated for WhereInfo struct */
4594 int nTabList; /* Number of elements in pTabList */
4595 WhereInfo *pWInfo; /* Will become the return value of this function */
4596 Vdbe *v = pParse->pVdbe; /* The virtual database engine */
4597 Bitmask notReady; /* Cursors that are not yet positioned */
4598 WhereLoopBuilder sWLB; /* The WhereLoop builder */
4599 WhereMaskSet *pMaskSet; /* The expression mask set */
4600 WhereLevel *pLevel; /* A single level in pWInfo->a[] */
4601 WhereLoop *pLoop; /* Pointer to a single WhereLoop object */
4602 int ii; /* Loop counter */
4603 sqlite3 *db; /* Database connection */
4604 int rc; /* Return code */
4605 u8 bFordelete = 0; /* OPFLAG_FORDELETE or zero, as appropriate */
4606
4607 assert((wctrlFlags & WHERE_ONEPASS_MULTIROW)==0 || (
4608 (wctrlFlags & WHERE_ONEPASS_DESIRED)!=0
4609 && (wctrlFlags & WHERE_OR_SUBCLAUSE)==0
4610 ));
4611
4612 /* Only one of WHERE_OR_SUBCLAUSE or WHERE_USE_LIMIT */
4613 assert((wctrlFlags & WHERE_OR_SUBCLAUSE)==0
4614 || (wctrlFlags & WHERE_USE_LIMIT)==0 );
4615
4616 /* Variable initialization */
4617 db = pParse->db;
4618 memset(&sWLB, 0, sizeof(sWLB));
4619
4620 /* An ORDER/GROUP BY clause of more than 63 terms cannot be optimized */
4621 testcase( pOrderBy && pOrderBy->nExpr==BMS-1 );
4622 if (pOrderBy && pOrderBy->nExpr>=BMS) pOrderBy = 0;
4623 sWLB.pOrderBy = pOrderBy;
4624
4625 /* Disable the DISTINCT optimization if SQLITE_DistinctOpt is set via
4626 ** sqlite3_test_ctrl(SQLITE_TESTCTRL_OPTIMIZATIONS,...) */
4627 if (OptimizationDisabled(db, SQLITE_DistinctOpt)) {
4628 wctrlFlags &= ~WHERE_WANT_DISTINCT;
4629 }
4630
4631 /* The number of tables in the FROM clause is limited by the number of
4632 ** bits in a Bitmask
4633 */
4634 testcase( pTabList->nSrc==BMS );
4635 if (pTabList->nSrc>BMS) {
4636 sqlite3ErrorMsg(pParse, "at most %d tables in a join", BMS);
4637 return 0;
4638 }
4639
4640 /* This function normally generates a nested loop for all tables in
4641 ** pTabList. But if the WHERE_OR_SUBCLAUSE flag is set, then we should
4642 ** only generate code for the first table in pTabList and assume that
4643 ** any cursors associated with subsequent tables are uninitialized.
4644 */
4645 nTabList = (wctrlFlags & WHERE_OR_SUBCLAUSE) ? 1 : pTabList->nSrc;
4646
4647 /* Allocate and initialize the WhereInfo structure that will become the
4648 ** return value. A single allocation is used to store the WhereInfo
4649 ** struct, the contents of WhereInfo.a[], the WhereClause structure
4650 ** and the WhereMaskSet structure. Since WhereClause contains an 8-byte
4651 ** field (type Bitmask) it must be aligned on an 8-byte boundary on
4652 ** some architectures. Hence the ROUND8() below.
4653 */
4654 nByteWInfo = ROUND8(sizeof(WhereInfo)+(nTabList-1)*sizeof(WhereLevel));
4655 pWInfo = sqlite3DbMallocRawNN(db, nByteWInfo + sizeof(WhereLoop));
4656 if (db->mallocFailed) {
4657 sqlite3DbFree(db, pWInfo);
4658 pWInfo = 0;
4659 goto whereBeginError;
4660 }
4661 pWInfo->pParse = pParse;
4662 pWInfo->pTabList = pTabList;
4663 pWInfo->pOrderBy = pOrderBy;
4664 pWInfo->pWhere = pWhere;
4665 pWInfo->pResultSet = pResultSet;
4666 pWInfo->aiCurOnePass[0] = pWInfo->aiCurOnePass[1] = -1;
4667 pWInfo->nLevel = nTabList;
4668 pWInfo->iBreak = pWInfo->iContinue = sqlite3VdbeMakeLabel(pParse);
4669 pWInfo->wctrlFlags = wctrlFlags;
4670 pWInfo->iLimit = iAuxArg;
4671 pWInfo->savedNQueryLoop = pParse->nQueryLoop;
4672 memset(&pWInfo->nOBSat, 0,
4673 offsetof(WhereInfo,sWC) - offsetof(WhereInfo,nOBSat));
4674 memset(&pWInfo->a[0], 0, sizeof(WhereLoop)+nTabList*sizeof(WhereLevel));
4675 assert( pWInfo->eOnePass==ONEPASS_OFF ); /* ONEPASS defaults to OFF */
4676 pMaskSet = &pWInfo->sMaskSet;
4677 sWLB.pWInfo = pWInfo;
4678 sWLB.pWC = &pWInfo->sWC;
4679 sWLB.pNew = (WhereLoop*)(((char*)pWInfo)+nByteWInfo);
4680 assert( EIGHT_BYTE_ALIGNMENT(sWLB.pNew));
4681 whereLoopInit(sWLB.pNew);
4682 #ifdef SQLITE_DEBUG
4683 sWLB.pNew->cId = '*';
4684 #endif
4685
4686 /* Split the WHERE clause into separate subexpressions where each
4687 ** subexpression is separated by an AND operator.
4688 */
4689 initMaskSet(pMaskSet);
4690 sqlite3WhereClauseInit(&pWInfo->sWC, pWInfo);
4691 sqlite3WhereSplit(&pWInfo->sWC, pWhere, TK_AND);
4692
4693 /* Special case: No FROM clause
4694 */
4695 if (nTabList==0) {
4696 if (pOrderBy) pWInfo->nOBSat = pOrderBy->nExpr;
4697 if (wctrlFlags & WHERE_WANT_DISTINCT) {
4698 pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE;
4699 }
4700 ExplainQueryPlan((pParse, 0, "SCAN CONSTANT ROW"));
4701 } else {
4702 /* Assign a bit from the bitmask to every term in the FROM clause.
4703 **
4704 ** The N-th term of the FROM clause is assigned a bitmask of 1<<N.
4705 **
4706 ** The rule of the previous sentence ensures thta if X is the bitmask for
4707 ** a table T, then X-1 is the bitmask for all other tables to the left of T.
4708 ** Knowing the bitmask for all tables to the left of a left join is
4709 ** important. Ticket #3015.
4710 **
4711 ** Note that bitmasks are created for all pTabList->nSrc tables in
4712 ** pTabList, not just the first nTabList tables. nTabList is normally
4713 ** equal to pTabList->nSrc but might be shortened to 1 if the
4714 ** WHERE_OR_SUBCLAUSE flag is set.
4715 */
4716 ii = 0;
4717 do{
4718 createMask(pMaskSet, pTabList->a[ii].iCursor);
4719 sqlite3WhereTabFuncArgs(pParse, &pTabList->a[ii], &pWInfo->sWC);
4720 }while ((++ii)<pTabList->nSrc);
4721 #ifdef SQLITE_DEBUG
4722 {
4723 Bitmask mx = 0;
4724 for (ii=0; ii<pTabList->nSrc; ii++) {
4725 Bitmask m = sqlite3WhereGetMask(pMaskSet, pTabList->a[ii].iCursor);
4726 assert( m>=mx );
4727 mx = m;
4728 }
4729 }
4730 #endif
4731 }
4732
4733 /* Analyze all of the subexpressions. */
4734 sqlite3WhereExprAnalyze(pTabList, &pWInfo->sWC);
4735 if (db->mallocFailed) goto whereBeginError;
4736
4737 /* Special case: WHERE terms that do not refer to any tables in the join
4738 ** (constant expressions). Evaluate each such term, and jump over all the
4739 ** generated code if the result is not true.
4740 **
4741 ** Do not do this if the expression contains non-deterministic functions
4742 ** that are not within a sub-select. This is not strictly required, but
4743 ** preserves SQLite's legacy behaviour in the following two cases:
4744 **
4745 ** FROM ... WHERE random()>0; -- eval random() once per row
4746 ** FROM ... WHERE (SELECT random())>0; -- eval random() once overall
4747 */
4748 for (ii=0; ii<sWLB.pWC->nTerm; ii++) {
4749 WhereTerm *pT = &sWLB.pWC->a[ii];
4750 if (pT->wtFlags & TERM_VIRTUAL) continue;
4751 if (pT->prereqAll==0 && (nTabList==0 || exprIsDeterministic(pT->pExpr))) {
4752 sqlite3ExprIfFalse(pParse, pT->pExpr, pWInfo->iBreak, SQLITE_JUMPIFNULL);
4753 pT->wtFlags |= TERM_CODED;
4754 }
4755 }
4756
4757 if (wctrlFlags & WHERE_WANT_DISTINCT) {
4758 if (isDistinctRedundant(pParse, pTabList, &pWInfo->sWC, pResultSet)) {
4759 /* The DISTINCT marking is pointless. Ignore it. */
4760 pWInfo->eDistinct = WHERE_DISTINCT_UNIQUE;
4761 } else if (pOrderBy==0) {
4762 /* Try to ORDER BY the result set to make distinct processing easier */
4763 pWInfo->wctrlFlags |= WHERE_DISTINCTBY;
4764 pWInfo->pOrderBy = pResultSet;
4765 }
4766 }
4767
4768 /* Construct the WhereLoop objects */
4769 #if defined(WHERETRACE_ENABLED)
4770 if (sqlite3WhereTrace & 0xffff) {
4771 sqlite3DebugPrintf("*** Optimizer Start *** (wctrlFlags: 0x%x",wctrlFlags);
4772 if (wctrlFlags & WHERE_USE_LIMIT) {
4773 sqlite3DebugPrintf(", limit: %d", iAuxArg);
4774 }
4775 sqlite3DebugPrintf(")\n");
4776 if (sqlite3WhereTrace & 0x100) {
4777 Select sSelect;
4778 memset(&sSelect, 0, sizeof(sSelect));
4779 sSelect.selFlags = SF_WhereBegin;
4780 sSelect.pSrc = pTabList;
4781 sSelect.pWhere = pWhere;
4782 sSelect.pOrderBy = pOrderBy;
4783 sSelect.pEList = pResultSet;
4784 sqlite3TreeViewSelect(0, &sSelect, 0);
4785 }
4786 }
4787 if (sqlite3WhereTrace & 0x100) { /* Display all terms of the WHERE clause */
4788 sqlite3WhereClausePrint(sWLB.pWC);
4789 }
4790 #endif
4791
4792 if (nTabList!=1 || whereShortCut(&sWLB)==0) {
4793 rc = whereLoopAddAll(&sWLB);
4794 if (rc) goto whereBeginError;
4795
4796 #ifdef WHERETRACE_ENABLED
4797 if (sqlite3WhereTrace) { /* Display all of the WhereLoop objects */
4798 WhereLoop *p;
4799 int i;
4800 static const char zLabel[] = "0123456789abcdefghijklmnopqrstuvwyxz"
4801 "ABCDEFGHIJKLMNOPQRSTUVWYXZ";
4802 for (p=pWInfo->pLoops, i=0; p; p=p->pNextLoop, i++) {
4803 p->cId = zLabel[i%(sizeof(zLabel)-1)];
4804 whereLoopPrint(p, sWLB.pWC);
4805 }
4806 }
4807 #endif
4808
4809 wherePathSolver(pWInfo, 0);
4810 if (db->mallocFailed) goto whereBeginError;
4811 if (pWInfo->pOrderBy) {
4812 wherePathSolver(pWInfo, pWInfo->nRowOut+1);
4813 if (db->mallocFailed) goto whereBeginError;
4814 }
4815 }
4816 if (pWInfo->pOrderBy==0 && (db->flags & SQLITE_ReverseOrder)!=0) {
4817 pWInfo->revMask = ALLBITS;
4818 }
4819 if (pParse->nErr || NEVER(db->mallocFailed)) {
4820 goto whereBeginError;
4821 }
4822 #ifdef WHERETRACE_ENABLED
4823 if (sqlite3WhereTrace) {
4824 sqlite3DebugPrintf("---- Solution nRow=%d", pWInfo->nRowOut);
4825 if (pWInfo->nOBSat>0) {
4826 sqlite3DebugPrintf(" ORDERBY=%d,0x%llx", pWInfo->nOBSat, pWInfo->revMask);
4827 }
4828 switch (pWInfo->eDistinct) {
4829 case WHERE_DISTINCT_UNIQUE: {
4830 sqlite3DebugPrintf(" DISTINCT=unique");
4831 break;
4832 }
4833 case WHERE_DISTINCT_ORDERED: {
4834 sqlite3DebugPrintf(" DISTINCT=ordered");
4835 break;
4836 }
4837 case WHERE_DISTINCT_UNORDERED: {
4838 sqlite3DebugPrintf(" DISTINCT=unordered");
4839 break;
4840 }
4841 }
4842 sqlite3DebugPrintf("\n");
4843 for (ii=0; ii<pWInfo->nLevel; ii++) {
4844 whereLoopPrint(pWInfo->a[ii].pWLoop, sWLB.pWC);
4845 }
4846 }
4847 #endif
4848
4849 /* Attempt to omit tables from the join that do not affect the result.
4850 ** For a table to not affect the result, the following must be true:
4851 **
4852 ** 1) The query must not be an aggregate.
4853 ** 2) The table must be the RHS of a LEFT JOIN.
4854 ** 3) Either the query must be DISTINCT, or else the ON or USING clause
4855 ** must contain a constraint that limits the scan of the table to
4856 ** at most a single row.
4857 ** 4) The table must not be referenced by any part of the query apart
4858 ** from its own USING or ON clause.
4859 **
4860 ** For example, given:
4861 **
4862 ** CREATE TABLE t1(ipk INTEGER PRIMARY KEY, v1);
4863 ** CREATE TABLE t2(ipk INTEGER PRIMARY KEY, v2);
4864 ** CREATE TABLE t3(ipk INTEGER PRIMARY KEY, v3);
4865 **
4866 ** then table t2 can be omitted from the following:
4867 **
4868 ** SELECT v1, v3 FROM t1
4869 ** LEFT JOIN t2 USING (t1.ipk=t2.ipk)
4870 ** LEFT JOIN t3 USING (t1.ipk=t3.ipk)
4871 **
4872 ** or from:
4873 **
4874 ** SELECT DISTINCT v1, v3 FROM t1
4875 ** LEFT JOIN t2
4876 ** LEFT JOIN t3 USING (t1.ipk=t3.ipk)
4877 */
4878 notReady = ~(Bitmask)0;
4879 if (pWInfo->nLevel>=2
4880 && pResultSet!=0 /* guarantees condition (1) above */
4881 && OptimizationEnabled(db, SQLITE_OmitNoopJoin)
4882 ) {
4883 int i;
4884 Bitmask tabUsed = sqlite3WhereExprListUsage(pMaskSet, pResultSet);
4885 if (sWLB.pOrderBy) {
4886 tabUsed |= sqlite3WhereExprListUsage(pMaskSet, sWLB.pOrderBy);
4887 }
4888 for (i=pWInfo->nLevel-1; i>=1; i--) {
4889 WhereTerm *pTerm, *pEnd;
4890 struct SrcList_item *pItem;
4891 pLoop = pWInfo->a[i].pWLoop;
4892 pItem = &pWInfo->pTabList->a[pLoop->iTab];
4893 if ((pItem->fg.jointype & JT_LEFT)==0) continue;
4894 if ((wctrlFlags & WHERE_WANT_DISTINCT)==0
4895 && (pLoop->wsFlags & WHERE_ONEROW)==0
4896 ) {
4897 continue;
4898 }
4899 if ((tabUsed & pLoop->maskSelf)!=0) continue;
4900 pEnd = sWLB.pWC->a + sWLB.pWC->nTerm;
4901 for (pTerm=sWLB.pWC->a; pTerm<pEnd; pTerm++) {
4902 if ((pTerm->prereqAll & pLoop->maskSelf)!=0) {
4903 if (!ExprHasProperty(pTerm->pExpr, EP_FromJoin)
4904 || pTerm->pExpr->iRightJoinTable!=pItem->iCursor
4905 ) {
4906 break;
4907 }
4908 }
4909 }
4910 if (pTerm<pEnd) continue;
4911 WHERETRACE(0xffff, ("-> drop loop %c not used\n", pLoop->cId));
4912 notReady &= ~pLoop->maskSelf;
4913 for (pTerm=sWLB.pWC->a; pTerm<pEnd; pTerm++) {
4914 if ((pTerm->prereqAll & pLoop->maskSelf)!=0) {
4915 pTerm->wtFlags |= TERM_CODED;
4916 }
4917 }
4918 if (i!=pWInfo->nLevel-1) {
4919 int nByte = (pWInfo->nLevel-1-i) * sizeof(WhereLevel);
4920 memmove(&pWInfo->a[i], &pWInfo->a[i+1], nByte);
4921 }
4922 pWInfo->nLevel--;
4923 nTabList--;
4924 }
4925 }
4926 WHERETRACE(0xffff,("*** Optimizer Finished ***\n"));
4927 pWInfo->pParse->nQueryLoop += pWInfo->nRowOut;
4928
4929 /* If the caller is an UPDATE or DELETE statement that is requesting
4930 ** to use a one-pass algorithm, determine if this is appropriate.
4931 **
4932 ** A one-pass approach can be used if the caller has requested one
4933 ** and either (a) the scan visits at most one row or (b) each
4934 ** of the following are true:
4935 **
4936 ** * the caller has indicated that a one-pass approach can be used
4937 ** with multiple rows (by setting WHERE_ONEPASS_MULTIROW), and
4938 ** * the table is not a virtual table, and
4939 ** * either the scan does not use the OR optimization or the caller
4940 ** is a DELETE operation (WHERE_DUPLICATES_OK is only specified
4941 ** for DELETE).
4942 **
4943 ** The last qualification is because an UPDATE statement uses
4944 ** WhereInfo.aiCurOnePass[1] to determine whether or not it really can
4945 ** use a one-pass approach, and this is not set accurately for scans
4946 ** that use the OR optimization.
4947 */
4948 assert((wctrlFlags & WHERE_ONEPASS_DESIRED)==0 || pWInfo->nLevel==1 );
4949 if ((wctrlFlags & WHERE_ONEPASS_DESIRED)!=0) {
4950 int wsFlags = pWInfo->a[0].pWLoop->wsFlags;
4951 int bOnerow = (wsFlags & WHERE_ONEROW)!=0;
4952 assert( !(wsFlags & WHERE_VIRTUALTABLE) || IsVirtual(pTabList->a[0].pTab));
4953 if (bOnerow || (
4954 0!=(wctrlFlags & WHERE_ONEPASS_MULTIROW)
4955 && !IsVirtual(pTabList->a[0].pTab)
4956 && (0==(wsFlags & WHERE_MULTI_OR) || (wctrlFlags & WHERE_DUPLICATES_OK))
4957 )) {
4958 pWInfo->eOnePass = bOnerow ? ONEPASS_SINGLE : ONEPASS_MULTI;
4959 if (HasRowid(pTabList->a[0].pTab) && (wsFlags & WHERE_IDX_ONLY)) {
4960 if (wctrlFlags & WHERE_ONEPASS_MULTIROW) {
4961 bFordelete = OPFLAG_FORDELETE;
4962 }
4963 pWInfo->a[0].pWLoop->wsFlags = (wsFlags & ~WHERE_IDX_ONLY);
4964 }
4965 }
4966 }
4967
4968 /* Open all tables in the pTabList and any indices selected for
4969 ** searching those tables.
4970 */
4971 for (ii=0, pLevel=pWInfo->a; ii<nTabList; ii++, pLevel++) {
4972 Table *pTab; /* Table to open */
4973 int iDb; /* Index of database containing table/index */
4974 struct SrcList_item *pTabItem;
4975
4976 pTabItem = &pTabList->a[pLevel->iFrom];
4977 pTab = pTabItem->pTab;
4978 iDb = sqlite3SchemaToIndex(db, pTab->pSchema);
4979 pLoop = pLevel->pWLoop;
4980 if ((pTab->tabFlags & TF_Ephemeral)!=0 || pTab->pSelect) {
4981 /* Do nothing */
4982 } else
4983 #ifndef SQLITE_OMIT_VIRTUALTABLE
4984 if ((pLoop->wsFlags & WHERE_VIRTUALTABLE)!=0) {
4985 const char *pVTab = (const char *)sqlite3GetVTable(db, pTab);
4986 int iCur = pTabItem->iCursor;
4987 sqlite3VdbeAddOp4(v, OP_VOpen, iCur, 0, 0, pVTab, P4_VTAB);
4988 } else if (IsVirtual(pTab)) {
4989 /* noop */
4990 } else
4991 #endif
4992 if ((pLoop->wsFlags & WHERE_IDX_ONLY)==0
4993 && (wctrlFlags & WHERE_OR_SUBCLAUSE)==0) {
4994 int op = OP_OpenRead;
4995 if (pWInfo->eOnePass!=ONEPASS_OFF) {
4996 op = OP_OpenWrite;
4997 pWInfo->aiCurOnePass[0] = pTabItem->iCursor;
4998 };
4999 sqlite3OpenTable(pParse, pTabItem->iCursor, iDb, pTab, op);
5000 assert( pTabItem->iCursor==pLevel->iTabCur );
5001 testcase( pWInfo->eOnePass==ONEPASS_OFF && pTab->nCol==BMS-1 );
5002 testcase( pWInfo->eOnePass==ONEPASS_OFF && pTab->nCol==BMS );
5003 if (pWInfo->eOnePass==ONEPASS_OFF && pTab->nCol<BMS && HasRowid(pTab)) {
5004 Bitmask b = pTabItem->colUsed;
5005 int n = 0;
5006 for (; b; b=b>>1, n++) {}
5007 sqlite3VdbeChangeP4(v, -1, SQLITE_INT_TO_PTR(n), P4_INT32);
5008 assert( n<=pTab->nCol );
5009 }
5010 #ifdef SQLITE_ENABLE_CURSOR_HINTS
5011 if (pLoop->u.btree.pIndex!=0) {
5012 sqlite3VdbeChangeP5(v, OPFLAG_SEEKEQ|bFordelete);
5013 } else
5014 #endif
5015 {
5016 sqlite3VdbeChangeP5(v, bFordelete);
5017 }
5018 #ifdef SQLITE_ENABLE_COLUMN_USED_MASK
5019 sqlite3VdbeAddOp4Dup8(v, OP_ColumnsUsed, pTabItem->iCursor, 0, 0,
5020 (const u8*)&pTabItem->colUsed, P4_INT64);
5021 #endif
5022 } else {
5023 sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);
5024 }
5025 if (pLoop->wsFlags & WHERE_INDEXED) {
5026 Index *pIx = pLoop->u.btree.pIndex;
5027 int iIndexCur;
5028 int op = OP_OpenRead;
5029 /* iAuxArg is always set to a positive value if ONEPASS is possible */
5030 assert( iAuxArg!=0 || (pWInfo->wctrlFlags & WHERE_ONEPASS_DESIRED)==0 );
5031 if (!HasRowid(pTab) && IsPrimaryKeyIndex(pIx)
5032 && (wctrlFlags & WHERE_OR_SUBCLAUSE)!=0
5033 ) {
5034 /* This is one term of an OR-optimization using the PRIMARY KEY of a
5035 ** WITHOUT ROWID table. No need for a separate index */
5036 iIndexCur = pLevel->iTabCur;
5037 op = 0;
5038 } else if (pWInfo->eOnePass!=ONEPASS_OFF) {
5039 Index *pJ = pTabItem->pTab->pIndex;
5040 iIndexCur = iAuxArg;
5041 assert( wctrlFlags & WHERE_ONEPASS_DESIRED );
5042 while (ALWAYS(pJ) && pJ!=pIx) {
5043 iIndexCur++;
5044 pJ = pJ->pNext;
5045 }
5046 op = OP_OpenWrite;
5047 pWInfo->aiCurOnePass[1] = iIndexCur;
5048 } else if (iAuxArg && (wctrlFlags & WHERE_OR_SUBCLAUSE)!=0) {
5049 iIndexCur = iAuxArg;
5050 op = OP_ReopenIdx;
5051 } else {
5052 iIndexCur = pParse->nTab++;
5053 }
5054 pLevel->iIdxCur = iIndexCur;
5055 assert( pIx->pSchema==pTab->pSchema );
5056 assert( iIndexCur>=0 );
5057 if (op) {
5058 sqlite3VdbeAddOp3(v, op, iIndexCur, pIx->tnum, iDb);
5059 sqlite3VdbeSetP4KeyInfo(pParse, pIx);
5060 if ((pLoop->wsFlags & WHERE_CONSTRAINT)!=0
5061 && (pLoop->wsFlags & (WHERE_COLUMN_RANGE|WHERE_SKIPSCAN))==0
5062 && (pWInfo->wctrlFlags&WHERE_ORDERBY_MIN)==0
5063 && pWInfo->eDistinct!=WHERE_DISTINCT_ORDERED
5064 ) {
5065 sqlite3VdbeChangeP5(v, OPFLAG_SEEKEQ); /* Hint to COMDB2 */
5066 }
5067 VdbeComment((v, "%s", pIx->zName));
5068 #ifdef SQLITE_ENABLE_COLUMN_USED_MASK
5069 {
5070 u64 colUsed = 0;
5071 int ii, jj;
5072 for (ii=0; ii<pIx->nColumn; ii++) {
5073 jj = pIx->aiColumn[ii];
5074 if (jj<0) continue;
5075 if (jj>63) jj = 63;
5076 if ((pTabItem->colUsed & MASKBIT(jj))==0) continue;
5077 colUsed |= ((u64)1)<<(ii<63 ? ii : 63);
5078 }
5079 sqlite3VdbeAddOp4Dup8(v, OP_ColumnsUsed, iIndexCur, 0, 0,
5080 (u8*)&colUsed, P4_INT64);
5081 }
5082 #endif /* SQLITE_ENABLE_COLUMN_USED_MASK */
5083 }
5084 }
5085 if (iDb>=0) sqlite3CodeVerifySchema(pParse, iDb);
5086 }
5087 pWInfo->iTop = sqlite3VdbeCurrentAddr(v);
5088 if (db->mallocFailed) goto whereBeginError;
5089
5090 /* Generate the code to do the search. Each iteration of the for
5091 ** loop below generates code for a single nested loop of the VM
5092 ** program.
5093 */
5094 for (ii=0; ii<nTabList; ii++) {
5095 int addrExplain;
5096 int wsFlags;
5097 pLevel = &pWInfo->a[ii];
5098 wsFlags = pLevel->pWLoop->wsFlags;
5099 #ifndef SQLITE_OMIT_AUTOMATIC_INDEX
5100 if ((pLevel->pWLoop->wsFlags & WHERE_AUTO_INDEX)!=0) {
5101 constructAutomaticIndex(pParse, &pWInfo->sWC,
5102 &pTabList->a[pLevel->iFrom], notReady, pLevel);
5103 if (db->mallocFailed) goto whereBeginError;
5104 }
5105 #endif
5106 addrExplain = sqlite3WhereExplainOneScan(
5107 pParse, pTabList, pLevel, wctrlFlags
5108 );
5109 pLevel->addrBody = sqlite3VdbeCurrentAddr(v);
5110 notReady = sqlite3WhereCodeOneLoopStart(pParse,v,pWInfo,ii,pLevel,notReady);
5111 pWInfo->iContinue = pLevel->addrCont;
5112 if ((wsFlags&WHERE_MULTI_OR)==0 && (wctrlFlags&WHERE_OR_SUBCLAUSE)==0) {
5113 sqlite3WhereAddScanStatus(v, pTabList, pLevel, addrExplain);
5114 }
5115 }
5116
5117 /* Done. */
5118 VdbeModuleComment((v, "Begin WHERE-core"));
5119 return pWInfo;
5120
5121 /* Jump here if malloc fails */
5122 whereBeginError:
5123 if (pWInfo) {
5124 pParse->nQueryLoop = pWInfo->savedNQueryLoop;
5125 whereInfoFree(db, pWInfo);
5126 }
5127 return 0;
5128 }
5129
5130 /*
5131 ** Part of sqlite3WhereEnd() will rewrite opcodes to reference the
5132 ** index rather than the main table. In SQLITE_DEBUG mode, we want
5133 ** to trace those changes if PRAGMA vdbe_addoptrace=on. This routine
5134 ** does that.
5135 */
5136 #ifndef SQLITE_DEBUG
5137 # define OpcodeRewriteTrace(D,K,P) /* no-op */
5138 #else
5139 # define OpcodeRewriteTrace(D,K,P) sqlite3WhereOpcodeRewriteTrace(D,K,P)
sqlite3WhereOpcodeRewriteTrace(sqlite3 * db,int pc,VdbeOp * pOp)5140 static void sqlite3WhereOpcodeRewriteTrace(
5141 sqlite3 *db,
5142 int pc,
5143 VdbeOp *pOp
5144 ){
5145 if ((db->flags & SQLITE_VdbeAddopTrace)==0) return;
5146 sqlite3VdbePrintOp(0, pc, pOp);
5147 }
5148 #endif
5149
5150 /*
5151 ** Generate the end of the WHERE loop. See comments on
5152 ** sqlite3WhereBegin() for additional information.
5153 */
sqlite3WhereEnd(WhereInfo * pWInfo)5154 void sqlite3WhereEnd(WhereInfo *pWInfo){
5155 Parse *pParse = pWInfo->pParse;
5156 Vdbe *v = pParse->pVdbe;
5157 int i;
5158 WhereLevel *pLevel;
5159 WhereLoop *pLoop;
5160 SrcList *pTabList = pWInfo->pTabList;
5161 sqlite3 *db = pParse->db;
5162
5163 /* Generate loop termination code.
5164 */
5165 VdbeModuleComment((v, "End WHERE-core"));
5166 for (i=pWInfo->nLevel-1; i>=0; i--) {
5167 int addr;
5168 pLevel = &pWInfo->a[i];
5169 pLoop = pLevel->pWLoop;
5170 if (pLevel->op!=OP_Noop) {
5171 #ifndef SQLITE_DISABLE_SKIPAHEAD_DISTINCT
5172 int addrSeek = 0;
5173 Index *pIdx;
5174 int n;
5175 if (pWInfo->eDistinct==WHERE_DISTINCT_ORDERED
5176 && i==pWInfo->nLevel-1 /* Ticket [ef9318757b152e3] 2017-10-21 */
5177 && (pLoop->wsFlags & WHERE_INDEXED)!=0
5178 && (pIdx = pLoop->u.btree.pIndex)->hasStat1
5179 && (n = pLoop->u.btree.nDistinctCol)>0
5180 && pIdx->aiRowLogEst[n]>=36
5181 ) {
5182 int r1 = pParse->nMem+1;
5183 int j, op;
5184 for (j=0; j<n; j++) {
5185 sqlite3VdbeAddOp3(v, OP_Column, pLevel->iIdxCur, j, r1+j);
5186 }
5187 pParse->nMem += n+1;
5188 op = pLevel->op==OP_Prev ? OP_SeekLT : OP_SeekGT;
5189 addrSeek = sqlite3VdbeAddOp4Int(v, op, pLevel->iIdxCur, 0, r1, n);
5190 VdbeCoverageIf(v, op==OP_SeekLT);
5191 VdbeCoverageIf(v, op==OP_SeekGT);
5192 sqlite3VdbeAddOp2(v, OP_Goto, 1, pLevel->p2);
5193 }
5194 #endif /* SQLITE_DISABLE_SKIPAHEAD_DISTINCT */
5195 /* The common case: Advance to the next row */
5196 sqlite3VdbeResolveLabel(v, pLevel->addrCont);
5197 sqlite3VdbeAddOp3(v, pLevel->op, pLevel->p1, pLevel->p2, pLevel->p3);
5198 sqlite3VdbeChangeP5(v, pLevel->p5);
5199 VdbeCoverage(v);
5200 VdbeCoverageIf(v, pLevel->op==OP_Next);
5201 VdbeCoverageIf(v, pLevel->op==OP_Prev);
5202 VdbeCoverageIf(v, pLevel->op==OP_VNext);
5203 #ifndef SQLITE_DISABLE_SKIPAHEAD_DISTINCT
5204 if (addrSeek) sqlite3VdbeJumpHere(v, addrSeek);
5205 #endif
5206 } else {
5207 sqlite3VdbeResolveLabel(v, pLevel->addrCont);
5208 }
5209 if (pLoop->wsFlags & WHERE_IN_ABLE && pLevel->u.in.nIn>0) {
5210 struct InLoop *pIn;
5211 int j;
5212 sqlite3VdbeResolveLabel(v, pLevel->addrNxt);
5213 for (j=pLevel->u.in.nIn, pIn=&pLevel->u.in.aInLoop[j-1]; j>0; j--, pIn--) {
5214 sqlite3VdbeJumpHere(v, pIn->addrInTop+1);
5215 if (pIn->eEndLoopOp!=OP_Noop) {
5216 if (pIn->nPrefix) {
5217 assert( pLoop->wsFlags & WHERE_IN_EARLYOUT );
5218 sqlite3VdbeAddOp4Int(v, OP_IfNoHope, pLevel->iIdxCur,
5219 sqlite3VdbeCurrentAddr(v)+2,
5220 pIn->iBase, pIn->nPrefix);
5221 VdbeCoverage(v);
5222 }
5223 sqlite3VdbeAddOp2(v, pIn->eEndLoopOp, pIn->iCur, pIn->addrInTop);
5224 VdbeCoverage(v);
5225 VdbeCoverageIf(v, pIn->eEndLoopOp==OP_Prev);
5226 VdbeCoverageIf(v, pIn->eEndLoopOp==OP_Next);
5227 }
5228 sqlite3VdbeJumpHere(v, pIn->addrInTop-1);
5229 }
5230 }
5231 sqlite3VdbeResolveLabel(v, pLevel->addrBrk);
5232 if (pLevel->addrSkip) {
5233 sqlite3VdbeGoto(v, pLevel->addrSkip);
5234 VdbeComment((v, "next skip-scan on %s", pLoop->u.btree.pIndex->zName));
5235 sqlite3VdbeJumpHere(v, pLevel->addrSkip);
5236 sqlite3VdbeJumpHere(v, pLevel->addrSkip-2);
5237 }
5238 #ifndef SQLITE_LIKE_DOESNT_MATCH_BLOBS
5239 if (pLevel->addrLikeRep) {
5240 sqlite3VdbeAddOp2(v, OP_DecrJumpZero, (int)(pLevel->iLikeRepCntr>>1),
5241 pLevel->addrLikeRep);
5242 VdbeCoverage(v);
5243 }
5244 #endif
5245 if (pLevel->iLeftJoin) {
5246 int ws = pLoop->wsFlags;
5247 addr = sqlite3VdbeAddOp1(v, OP_IfPos, pLevel->iLeftJoin); VdbeCoverage(v);
5248 assert((ws & WHERE_IDX_ONLY)==0 || (ws & WHERE_INDEXED)!=0 );
5249 if ((ws & WHERE_IDX_ONLY)==0) {
5250 assert( pLevel->iTabCur==pTabList->a[pLevel->iFrom].iCursor );
5251 sqlite3VdbeAddOp1(v, OP_NullRow, pLevel->iTabCur);
5252 }
5253 if ((ws & WHERE_INDEXED)
5254 || ((ws & WHERE_MULTI_OR) && pLevel->u.pCovidx)
5255 ) {
5256 sqlite3VdbeAddOp1(v, OP_NullRow, pLevel->iIdxCur);
5257 }
5258 if (pLevel->op==OP_Return) {
5259 sqlite3VdbeAddOp2(v, OP_Gosub, pLevel->p1, pLevel->addrFirst);
5260 } else {
5261 sqlite3VdbeGoto(v, pLevel->addrFirst);
5262 }
5263 sqlite3VdbeJumpHere(v, addr);
5264 }
5265 VdbeModuleComment((v, "End WHERE-loop%d: %s", i,
5266 pWInfo->pTabList->a[pLevel->iFrom].pTab->zName));
5267 }
5268
5269 /* The "break" point is here, just past the end of the outer loop.
5270 ** Set it.
5271 */
5272 sqlite3VdbeResolveLabel(v, pWInfo->iBreak);
5273
5274 assert( pWInfo->nLevel<=pTabList->nSrc );
5275 for (i=0, pLevel=pWInfo->a; i<pWInfo->nLevel; i++, pLevel++) {
5276 int k, last;
5277 VdbeOp *pOp;
5278 Index *pIdx = 0;
5279 struct SrcList_item *pTabItem = &pTabList->a[pLevel->iFrom];
5280 Table *pTab = pTabItem->pTab;
5281 assert( pTab!=0 );
5282 pLoop = pLevel->pWLoop;
5283
5284 /* For a co-routine, change all OP_Column references to the table of
5285 ** the co-routine into OP_Copy of result contained in a register.
5286 ** OP_Rowid becomes OP_Null.
5287 */
5288 if (pTabItem->fg.viaCoroutine) {
5289 testcase( pParse->db->mallocFailed );
5290 translateColumnToCopy(pParse, pLevel->addrBody, pLevel->iTabCur,
5291 pTabItem->regResult, 0);
5292 continue;
5293 }
5294
5295 #ifdef SQLITE_ENABLE_EARLY_CURSOR_CLOSE
5296 /* Close all of the cursors that were opened by sqlite3WhereBegin.
5297 ** Except, do not close cursors that will be reused by the OR optimization
5298 ** (WHERE_OR_SUBCLAUSE). And do not close the OP_OpenWrite cursors
5299 ** created for the ONEPASS optimization.
5300 */
5301 if ((pTab->tabFlags & TF_Ephemeral)==0
5302 && pTab->pSelect==0
5303 && (pWInfo->wctrlFlags & WHERE_OR_SUBCLAUSE)==0
5304 ) {
5305 int ws = pLoop->wsFlags;
5306 if (pWInfo->eOnePass==ONEPASS_OFF && (ws & WHERE_IDX_ONLY)==0) {
5307 sqlite3VdbeAddOp1(v, OP_Close, pTabItem->iCursor);
5308 }
5309 if ((ws & WHERE_INDEXED)!=0
5310 && (ws & (WHERE_IPK|WHERE_AUTO_INDEX))==0
5311 && pLevel->iIdxCur!=pWInfo->aiCurOnePass[1]
5312 ) {
5313 sqlite3VdbeAddOp1(v, OP_Close, pLevel->iIdxCur);
5314 }
5315 }
5316 #endif
5317
5318 /* If this scan uses an index, make VDBE code substitutions to read data
5319 ** from the index instead of from the table where possible. In some cases
5320 ** this optimization prevents the table from ever being read, which can
5321 ** yield a significant performance boost.
5322 **
5323 ** Calls to the code generator in between sqlite3WhereBegin and
5324 ** sqlite3WhereEnd will have created code that references the table
5325 ** directly. This loop scans all that code looking for opcodes
5326 ** that reference the table and converts them into opcodes that
5327 ** reference the index.
5328 */
5329 if (pLoop->wsFlags & (WHERE_INDEXED|WHERE_IDX_ONLY)) {
5330 pIdx = pLoop->u.btree.pIndex;
5331 } else if (pLoop->wsFlags & WHERE_MULTI_OR) {
5332 pIdx = pLevel->u.pCovidx;
5333 }
5334 if (pIdx
5335 && (pWInfo->eOnePass==ONEPASS_OFF || !HasRowid(pIdx->pTable))
5336 && !db->mallocFailed
5337 ) {
5338 last = sqlite3VdbeCurrentAddr(v);
5339 k = pLevel->addrBody;
5340 #ifdef SQLITE_DEBUG
5341 if (db->flags & SQLITE_VdbeAddopTrace) {
5342 printf("TRANSLATE opcodes in range %d..%d\n", k, last-1);
5343 }
5344 #endif
5345 pOp = sqlite3VdbeGetOp(v, k);
5346 for (; k<last; k++, pOp++) {
5347 if (pOp->p1!=pLevel->iTabCur) continue;
5348 if (pOp->opcode==OP_Column
5349 #ifdef SQLITE_ENABLE_OFFSET_SQL_FUNC
5350 || pOp->opcode==OP_Offset
5351 #endif
5352 ) {
5353 int x = pOp->p2;
5354 assert( pIdx->pTable==pTab );
5355 if (!HasRowid(pTab)) {
5356 Index *pPk = sqlite3PrimaryKeyIndex(pTab);
5357 x = pPk->aiColumn[x];
5358 assert( x>=0 );
5359 }
5360 x = sqlite3ColumnOfIndex(pIdx, x);
5361 if (x>=0) {
5362 pOp->p2 = x;
5363 pOp->p1 = pLevel->iIdxCur;
5364 OpcodeRewriteTrace(db, k, pOp);
5365 }
5366 assert((pLoop->wsFlags & WHERE_IDX_ONLY)==0 || x>=0
5367 || pWInfo->eOnePass );
5368 } else if (pOp->opcode==OP_Rowid) {
5369 pOp->p1 = pLevel->iIdxCur;
5370 pOp->opcode = OP_IdxRowid;
5371 OpcodeRewriteTrace(db, k, pOp);
5372 } else if (pOp->opcode==OP_IfNullRow) {
5373 pOp->p1 = pLevel->iIdxCur;
5374 OpcodeRewriteTrace(db, k, pOp);
5375 }
5376 }
5377 #ifdef SQLITE_DEBUG
5378 if (db->flags & SQLITE_VdbeAddopTrace) printf("TRANSLATE complete\n");
5379 #endif
5380 }
5381 }
5382
5383 /* Final cleanup
5384 */
5385 pParse->nQueryLoop = pWInfo->savedNQueryLoop;
5386 whereInfoFree(db, pWInfo);
5387 return;
5388 }
5389