AryaWu/sqlite
0
1/*2** 2015-06-063**4** The author disclaims copyright to this source code. In place of5** 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 process13** the WHERE clause of SQL statements.14**15** This file was split off from where.c on 2015-06-06 in order to reduce the16** size of where.c and make it easier to edit. This file contains the routines17** that actually generate the bulk of the WHERE loop code. The original where.c18** file retains the code that does query planning and analysis.19*/20#include "sqliteInt.h"21#include "whereInt.h"22 23#ifndef SQLITE_OMIT_EXPLAIN24 25/*26** Return the name of the i-th column of the pIdx index.27*/28static const char *explainIndexColumnName(Index *pIdx, int i){29 i = pIdx->aiColumn[i];30 if( i==XN_EXPR ) return "<expr>";31 if( i==XN_ROWID ) return "rowid";32 return pIdx->pTable->aCol[i].zCnName;33}34 35/*36** This routine is a helper for explainIndexRange() below37**38** pStr holds the text of an expression that we are building up one term39** at a time. This routine adds a new term to the end of the expression.40** Terms are separated by AND so add the "AND" text for second and subsequent41** terms only.42*/43static void explainAppendTerm(44 StrAccum *pStr, /* The text expression being built */45 Index *pIdx, /* Index to read column names from */46 int nTerm, /* Number of terms */47 int iTerm, /* Zero-based index of first term. */48 int bAnd, /* Non-zero to append " AND " */49 const char *zOp /* Name of the operator */50){51 int i;52 53 assert( nTerm>=1 );54 if( bAnd ) sqlite3_str_append(pStr, " AND ", 5);55 56 if( nTerm>1 ) sqlite3_str_append(pStr, "(", 1);57 for(i=0; i<nTerm; i++){58 if( i ) sqlite3_str_append(pStr, ",", 1);59 sqlite3_str_appendall(pStr, explainIndexColumnName(pIdx, iTerm+i));60 }61 if( nTerm>1 ) sqlite3_str_append(pStr, ")", 1);62 63 sqlite3_str_append(pStr, zOp, 1);64 65 if( nTerm>1 ) sqlite3_str_append(pStr, "(", 1);66 for(i=0; i<nTerm; i++){67 if( i ) sqlite3_str_append(pStr, ",", 1);68 sqlite3_str_append(pStr, "?", 1);69 }70 if( nTerm>1 ) sqlite3_str_append(pStr, ")", 1);71}72 73/*74** Argument pLevel describes a strategy for scanning table pTab. This75** function appends text to pStr that describes the subset of table76** rows scanned by the strategy in the form of an SQL expression.77**78** For example, if the query:79**80** SELECT * FROM t1 WHERE a=1 AND b>2;81**82** is run and there is an index on (a, b), then this function returns a83** string similar to:84**85** "a=? AND b>?"86*/87static void explainIndexRange(StrAccum *pStr, WhereLoop *pLoop){88 Index *pIndex = pLoop->u.btree.pIndex;89 u16 nEq = pLoop->u.btree.nEq;90 u16 nSkip = pLoop->nSkip;91 int i, j;92 93 if( nEq==0 && (pLoop->wsFlags&(WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))==0 ) return;94 sqlite3_str_append(pStr, " (", 2);95 for(i=0; i<nEq; i++){96 const char *z = explainIndexColumnName(pIndex, i);97 if( i ) sqlite3_str_append(pStr, " AND ", 5);98 sqlite3_str_appendf(pStr, i>=nSkip ? "%s=?" : "ANY(%s)", z);99 }100 101 j = i;102 if( pLoop->wsFlags&WHERE_BTM_LIMIT ){103 explainAppendTerm(pStr, pIndex, pLoop->u.btree.nBtm, j, i, ">");104 i = 1;105 }106 if( pLoop->wsFlags&WHERE_TOP_LIMIT ){107 explainAppendTerm(pStr, pIndex, pLoop->u.btree.nTop, j, i, "<");108 }109 sqlite3_str_append(pStr, ")", 1);110}111 112/*113** This function sets the P4 value of an existing OP_Explain opcode to114** text describing the loop in pLevel. If the OP_Explain opcode already has115** a P4 value, it is freed before it is overwritten.116*/117void sqlite3WhereAddExplainText(118 Parse *pParse, /* Parse context */119 int addr, /* Address of OP_Explain opcode */120 SrcList *pTabList, /* Table list this loop refers to */121 WhereLevel *pLevel, /* Scan to write OP_Explain opcode for */122 u16 wctrlFlags /* Flags passed to sqlite3WhereBegin() */123){124#if !defined(SQLITE_DEBUG)125 if( sqlite3ParseToplevel(pParse)->explain==2 || IS_STMT_SCANSTATUS(pParse->db) )126#endif127 {128 VdbeOp *pOp = sqlite3VdbeGetOp(pParse->pVdbe, addr);129 SrcItem *pItem = &pTabList->a[pLevel->iFrom];130 sqlite3 *db = pParse->db; /* Database handle */131 int isSearch; /* True for a SEARCH. False for SCAN. */132 WhereLoop *pLoop; /* The controlling WhereLoop object */133 u32 flags; /* Flags that describe this loop */134#if defined(SQLITE_DEBUG) && !defined(SQLITE_OMIT_EXPLAIN)135 char *zMsg; /* Text to add to EQP output */136#endif137 StrAccum str; /* EQP output string */138 char zBuf[100]; /* Initial space for EQP output string */139 140 if( db->mallocFailed ) return;141 142 pLoop = pLevel->pWLoop;143 flags = pLoop->wsFlags;144 145 isSearch = (flags&(WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))!=0146 || ((flags&WHERE_VIRTUALTABLE)==0 && (pLoop->u.btree.nEq>0))147 || (wctrlFlags&(WHERE_ORDERBY_MIN|WHERE_ORDERBY_MAX));148 149 sqlite3StrAccumInit(&str, db, zBuf, sizeof(zBuf), SQLITE_MAX_LENGTH);150 str.printfFlags = SQLITE_PRINTF_INTERNAL;151 sqlite3_str_appendf(&str, "%s %S%s",152 isSearch ? "SEARCH" : "SCAN",153 pItem,154 pItem->fg.fromExists ? " EXISTS" : "");155 if( (flags & (WHERE_IPK|WHERE_VIRTUALTABLE))==0 ){156 const char *zFmt = 0;157 Index *pIdx;158 159 assert( pLoop->u.btree.pIndex!=0 );160 pIdx = pLoop->u.btree.pIndex;161 assert( !(flags&WHERE_AUTO_INDEX) || (flags&WHERE_IDX_ONLY) );162 if( !HasRowid(pItem->pSTab) && IsPrimaryKeyIndex(pIdx) ){163 if( isSearch ){164 zFmt = "PRIMARY KEY";165 }166 }else if( flags & WHERE_PARTIALIDX ){167 zFmt = "AUTOMATIC PARTIAL COVERING INDEX";168 }else if( flags & WHERE_AUTO_INDEX ){169 zFmt = "AUTOMATIC COVERING INDEX";170 }else if( flags & (WHERE_IDX_ONLY|WHERE_EXPRIDX) ){171 zFmt = "COVERING INDEX %s";172 }else{173 zFmt = "INDEX %s";174 }175 if( zFmt ){176 sqlite3_str_append(&str, " USING ", 7);177 sqlite3_str_appendf(&str, zFmt, pIdx->zName);178 explainIndexRange(&str, pLoop);179 }180 }else if( (flags & WHERE_IPK)!=0 && (flags & WHERE_CONSTRAINT)!=0 ){181 char cRangeOp;182#if 0 /* Better output, but breaks many tests */183 const Table *pTab = pItem->pTab;184 const char *zRowid = pTab->iPKey>=0 ? pTab->aCol[pTab->iPKey].zCnName:185 "rowid";186#else187 const char *zRowid = "rowid";188#endif189 sqlite3_str_appendf(&str, " USING INTEGER PRIMARY KEY (%s", zRowid);190 if( flags&(WHERE_COLUMN_EQ|WHERE_COLUMN_IN) ){191 cRangeOp = '=';192 }else if( (flags&WHERE_BOTH_LIMIT)==WHERE_BOTH_LIMIT ){193 sqlite3_str_appendf(&str, ">? AND %s", zRowid);194 cRangeOp = '<';195 }else if( flags&WHERE_BTM_LIMIT ){196 cRangeOp = '>';197 }else{198 assert( flags&WHERE_TOP_LIMIT);199 cRangeOp = '<';200 }201 sqlite3_str_appendf(&str, "%c?)", cRangeOp);202 }203#ifndef SQLITE_OMIT_VIRTUALTABLE204 else if( (flags & WHERE_VIRTUALTABLE)!=0 ){205 sqlite3_str_appendall(&str, " VIRTUAL TABLE INDEX ");206 sqlite3_str_appendf(&str,207 pLoop->u.vtab.bIdxNumHex ? "0x%x:%s" : "%d:%s",208 pLoop->u.vtab.idxNum, pLoop->u.vtab.idxStr);209 }210#endif211 if( pItem->fg.jointype & JT_LEFT ){212 sqlite3_str_appendf(&str, " LEFT-JOIN");213 }214#ifdef SQLITE_EXPLAIN_ESTIMATED_ROWS215 if( pLoop->nOut>=10 ){216 sqlite3_str_appendf(&str, " (~%llu rows)",217 sqlite3LogEstToInt(pLoop->nOut));218 }else{219 sqlite3_str_append(&str, " (~1 row)", 9);220 }221#endif222#if defined(SQLITE_DEBUG) && !defined(SQLITE_OMIT_EXPLAIN)223 zMsg = sqlite3StrAccumFinish(&str);224 sqlite3ExplainBreakpoint("",zMsg);225#endif226 227 assert( pOp->opcode==OP_Explain );228 assert( pOp->p4type==P4_DYNAMIC || pOp->p4.z==0 );229 sqlite3DbFree(db, pOp->p4.z);230 pOp->p4type = P4_DYNAMIC;231 pOp->p4.z = sqlite3StrAccumFinish(&str);232 }233}234 235 236/*237** This function is a no-op unless currently processing an EXPLAIN QUERY PLAN238** command, or if stmt_scanstatus_v2() stats are enabled, or if SQLITE_DEBUG239** was defined at compile-time. If it is not a no-op, a single OP_Explain240** opcode is added to the output to describe the table scan strategy in pLevel.241**242** If an OP_Explain opcode is added to the VM, its address is returned.243** Otherwise, if no OP_Explain is coded, zero is returned.244*/245int sqlite3WhereExplainOneScan(246 Parse *pParse, /* Parse context */247 SrcList *pTabList, /* Table list this loop refers to */248 WhereLevel *pLevel, /* Scan to write OP_Explain opcode for */249 u16 wctrlFlags /* Flags passed to sqlite3WhereBegin() */250){251 int ret = 0;252#if !defined(SQLITE_DEBUG)253 if( sqlite3ParseToplevel(pParse)->explain==2 || IS_STMT_SCANSTATUS(pParse->db) )254#endif255 {256 if( (pLevel->pWLoop->wsFlags & WHERE_MULTI_OR)==0257 && (wctrlFlags & WHERE_OR_SUBCLAUSE)==0258 ){259 Vdbe *v = pParse->pVdbe;260 int addr = sqlite3VdbeCurrentAddr(v);261 ret = sqlite3VdbeAddOp3(262 v, OP_Explain, addr, pParse->addrExplain, pLevel->pWLoop->rRun263 );264 sqlite3WhereAddExplainText(pParse, addr, pTabList, pLevel, wctrlFlags);265 }266 }267 return ret;268}269 270/*271** Add a single OP_Explain opcode that describes a Bloom filter.272**273** Or if not processing EXPLAIN QUERY PLAN and not in a SQLITE_DEBUG and/or274** SQLITE_ENABLE_STMT_SCANSTATUS build, then OP_Explain opcodes are not275** required and this routine is a no-op.276**277** If an OP_Explain opcode is added to the VM, its address is returned.278** Otherwise, if no OP_Explain is coded, zero is returned.279*/280int sqlite3WhereExplainBloomFilter(281 const Parse *pParse, /* Parse context */282 const WhereInfo *pWInfo, /* WHERE clause */283 const WhereLevel *pLevel /* Bloom filter on this level */284){285 int ret = 0;286 SrcItem *pItem = &pWInfo->pTabList->a[pLevel->iFrom];287 Vdbe *v = pParse->pVdbe; /* VM being constructed */288 sqlite3 *db = pParse->db; /* Database handle */289 char *zMsg; /* Text to add to EQP output */290 int i; /* Loop counter */291 WhereLoop *pLoop; /* The where loop */292 StrAccum str; /* EQP output string */293 char zBuf[100]; /* Initial space for EQP output string */294 295 sqlite3StrAccumInit(&str, db, zBuf, sizeof(zBuf), SQLITE_MAX_LENGTH);296 str.printfFlags = SQLITE_PRINTF_INTERNAL;297 sqlite3_str_appendf(&str, "BLOOM FILTER ON %S (", pItem);298 pLoop = pLevel->pWLoop;299 if( pLoop->wsFlags & WHERE_IPK ){300 const Table *pTab = pItem->pSTab;301 if( pTab->iPKey>=0 ){302 sqlite3_str_appendf(&str, "%s=?", pTab->aCol[pTab->iPKey].zCnName);303 }else{304 sqlite3_str_appendf(&str, "rowid=?");305 }306 }else{307 for(i=pLoop->nSkip; i<pLoop->u.btree.nEq; i++){308 const char *z = explainIndexColumnName(pLoop->u.btree.pIndex, i);309 if( i>pLoop->nSkip ) sqlite3_str_append(&str, " AND ", 5);310 sqlite3_str_appendf(&str, "%s=?", z);311 }312 }313 sqlite3_str_append(&str, ")", 1);314 zMsg = sqlite3StrAccumFinish(&str);315 ret = sqlite3VdbeAddOp4(v, OP_Explain, sqlite3VdbeCurrentAddr(v),316 pParse->addrExplain, 0, zMsg,P4_DYNAMIC);317 318 sqlite3VdbeScanStatus(v, sqlite3VdbeCurrentAddr(v)-1, 0, 0, 0, 0);319 return ret;320}321#endif /* SQLITE_OMIT_EXPLAIN */322 323#ifdef SQLITE_ENABLE_STMT_SCANSTATUS324/*325** Configure the VM passed as the first argument with an326** sqlite3_stmt_scanstatus() entry corresponding to the scan used to327** implement level pLvl. Argument pSrclist is a pointer to the FROM328** clause that the scan reads data from.329**330** If argument addrExplain is not 0, it must be the address of an331** OP_Explain instruction that describes the same loop.332*/333void sqlite3WhereAddScanStatus(334 Vdbe *v, /* Vdbe to add scanstatus entry to */335 SrcList *pSrclist, /* FROM clause pLvl reads data from */336 WhereLevel *pLvl, /* Level to add scanstatus() entry for */337 int addrExplain /* Address of OP_Explain (or 0) */338){339 if( IS_STMT_SCANSTATUS( sqlite3VdbeDb(v) ) ){340 const char *zObj = 0;341 WhereLoop *pLoop = pLvl->pWLoop;342 int wsFlags = pLoop->wsFlags;343 int viaCoroutine = 0;344 345 if( (wsFlags & WHERE_VIRTUALTABLE)==0 && pLoop->u.btree.pIndex!=0 ){346 zObj = pLoop->u.btree.pIndex->zName;347 }else{348 zObj = pSrclist->a[pLvl->iFrom].zName;349 viaCoroutine = pSrclist->a[pLvl->iFrom].fg.viaCoroutine;350 }351 sqlite3VdbeScanStatus(352 v, addrExplain, pLvl->addrBody, pLvl->addrVisit, pLoop->nOut, zObj353 );354 355 if( viaCoroutine==0 ){356 if( (wsFlags & (WHERE_MULTI_OR|WHERE_AUTO_INDEX))==0 ){357 sqlite3VdbeScanStatusRange(v, addrExplain, -1, pLvl->iTabCur);358 }359 if( wsFlags & WHERE_INDEXED ){360 sqlite3VdbeScanStatusRange(v, addrExplain, -1, pLvl->iIdxCur);361 }362 }else{363 int addr;364 VdbeOp *pOp;365 assert( pSrclist->a[pLvl->iFrom].fg.isSubquery );366 addr = pSrclist->a[pLvl->iFrom].u4.pSubq->addrFillSub;367 pOp = sqlite3VdbeGetOp(v, addr-1);368 assert( sqlite3VdbeDb(v)->mallocFailed || pOp->opcode==OP_InitCoroutine );369 assert( sqlite3VdbeDb(v)->mallocFailed || pOp->p2>addr );370 sqlite3VdbeScanStatusRange(v, addrExplain, addr, pOp->p2-1);371 }372 }373}374#endif375 376 377/*378** Disable a term in the WHERE clause. Except, do not disable the term379** if it controls a LEFT OUTER JOIN and it did not originate in the ON380** or USING clause of that join.381**382** Consider the term t2.z='ok' in the following queries:383**384** (1) SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.x WHERE t2.z='ok'385** (2) SELECT * FROM t1 LEFT JOIN t2 ON t1.a=t2.x AND t2.z='ok'386** (3) SELECT * FROM t1, t2 WHERE t1.a=t2.x AND t2.z='ok'387**388** The t2.z='ok' is disabled in the in (2) because it originates389** in the ON clause. The term is disabled in (3) because it is not part390** of a LEFT OUTER JOIN. In (1), the term is not disabled.391**392** Disabling a term causes that term to not be tested in the inner loop393** of the join. Disabling is an optimization. When terms are satisfied394** by indices, we disable them to prevent redundant tests in the inner395** loop. We would get the correct results if nothing were ever disabled,396** but joins might run a little slower. The trick is to disable as much397** as we can without disabling too much. If we disabled in (1), we'd get398** the wrong answer. See ticket #813.399**400** If all the children of a term are disabled, then that term is also401** automatically disabled. In this way, terms get disabled if derived402** virtual terms are tested first. For example:403**404** x GLOB 'abc*' AND x>='abc' AND x<'acd'405** \___________/ \______/ \_____/406** parent child1 child2407**408** Only the parent term was in the original WHERE clause. The child1409** and child2 terms were added by the LIKE optimization. If both of410** the virtual child terms are valid, then testing of the parent can be411** skipped.412**413** Usually the parent term is marked as TERM_CODED. But if the parent414** term was originally TERM_LIKE, then the parent gets TERM_LIKECOND instead.415** The TERM_LIKECOND marking indicates that the term should be coded inside416** a conditional such that is only evaluated on the second pass of a417** LIKE-optimization loop, when scanning BLOBs instead of strings.418*/419static void disableTerm(WhereLevel *pLevel, WhereTerm *pTerm){420 int nLoop = 0;421 assert( pTerm!=0 );422 while( (pTerm->wtFlags & TERM_CODED)==0423 && (pLevel->iLeftJoin==0 || ExprHasProperty(pTerm->pExpr, EP_OuterON))424 && (pLevel->notReady & pTerm->prereqAll)==0425 ){426 if( nLoop && (pTerm->wtFlags & TERM_LIKE)!=0 ){427 pTerm->wtFlags |= TERM_LIKECOND;428 }else{429 pTerm->wtFlags |= TERM_CODED;430 }431#ifdef WHERETRACE_ENABLED432 if( (sqlite3WhereTrace & 0x4001)==0x4001 ){433 sqlite3DebugPrintf("DISABLE-");434 sqlite3WhereTermPrint(pTerm, (int)(pTerm - (pTerm->pWC->a)));435 }436#endif437 if( pTerm->iParent<0 ) break;438 pTerm = &pTerm->pWC->a[pTerm->iParent];439 assert( pTerm!=0 );440 pTerm->nChild--;441 if( pTerm->nChild!=0 ) break;442 nLoop++;443 }444}445 446/*447** Code an OP_Affinity opcode to apply the column affinity string zAff448** to the n registers starting at base.449**450** As an optimization, SQLITE_AFF_BLOB and SQLITE_AFF_NONE entries (which451** are no-ops) at the beginning and end of zAff are ignored. If all entries452** in zAff are SQLITE_AFF_BLOB or SQLITE_AFF_NONE, then no code gets generated.453**454** This routine makes its own copy of zAff so that the caller is free455** to modify zAff after this routine returns.456*/457static void codeApplyAffinity(Parse *pParse, int base, int n, char *zAff){458 Vdbe *v = pParse->pVdbe;459 if( zAff==0 ){460 assert( pParse->db->mallocFailed );461 return;462 }463 assert( v!=0 );464 465 /* Adjust base and n to skip over SQLITE_AFF_BLOB and SQLITE_AFF_NONE466 ** entries at the beginning and end of the affinity string.467 */468 assert( SQLITE_AFF_NONE<SQLITE_AFF_BLOB );469 while( n>0 && zAff[0]<=SQLITE_AFF_BLOB ){470 n--;471 base++;472 zAff++;473 }474 while( n>1 && zAff[n-1]<=SQLITE_AFF_BLOB ){475 n--;476 }477 478 /* Code the OP_Affinity opcode if there is anything left to do. */479 if( n>0 ){480 sqlite3VdbeAddOp4(v, OP_Affinity, base, n, 0, zAff, n);481 }482}483 484/*485** Expression pRight, which is the RHS of a comparison operation, is486** either a vector of n elements or, if n==1, a scalar expression.487** Before the comparison operation, affinity zAff is to be applied488** to the pRight values. This function modifies characters within the489** affinity string to SQLITE_AFF_BLOB if either:490**491** * the comparison will be performed with no affinity, or492** * the affinity change in zAff is guaranteed not to change the value.493*/494static void updateRangeAffinityStr(495 Expr *pRight, /* RHS of comparison */496 int n, /* Number of vector elements in comparison */497 char *zAff /* Affinity string to modify */498){499 int i;500 for(i=0; i<n; i++){501 Expr *p = sqlite3VectorFieldSubexpr(pRight, i);502 if( sqlite3CompareAffinity(p, zAff[i])==SQLITE_AFF_BLOB503 || sqlite3ExprNeedsNoAffinityChange(p, zAff[i])504 ){505 zAff[i] = SQLITE_AFF_BLOB;506 }507 }508}509 510/*511** The pOrderBy->a[].u.x.iOrderByCol values might be incorrect because512** columns might have been rearranged in the result set. This routine513** fixes them up.514**515** pEList is the new result set. The pEList->a[].u.x.iOrderByCol values516** contain the *old* locations of each expression. This is a temporary517** use of u.x.iOrderByCol, not its intended use. The caller must reset518** u.x.iOrderByCol back to zero for all entries in pEList before the519** caller returns.520**521** This routine changes pOrderBy->a[].u.x.iOrderByCol values from522** pEList->a[N].u.x.iOrderByCol into N+1. (The "+1" is because of the 1-based523** indexing used by iOrderByCol.) Or if no match, iOrderByCol is set to zero.524*/525static void adjustOrderByCol(ExprList *pOrderBy, ExprList *pEList){526 int i, j;527 if( pOrderBy==0 ) return;528 for(i=0; i<pOrderBy->nExpr; i++){529 int t = pOrderBy->a[i].u.x.iOrderByCol;530 if( t==0 ) continue;531 for(j=0; j<pEList->nExpr; j++){532 if( pEList->a[j].u.x.iOrderByCol==t ){533 pOrderBy->a[i].u.x.iOrderByCol = j+1;534 break;535 }536 }537 if( j>=pEList->nExpr ){538 pOrderBy->a[i].u.x.iOrderByCol = 0;539 }540 }541}542 543 544/*545** pX is an expression of the form: (vector) IN (SELECT ...)546** In other words, it is a vector IN operator with a SELECT clause on the547** RHS. But not all terms in the vector are indexable and the terms might548** not be in the correct order for indexing.549**550** This routine makes a copy of the input pX expression and then adjusts551** the vector on the LHS with corresponding changes to the SELECT so that552** the vector contains only index terms and those terms are in the correct553** order. The modified IN expression is returned. The caller is responsible554** for deleting the returned expression.555**556** Example:557**558** CREATE TABLE t1(a,b,c,d,e,f);559** CREATE INDEX t1x1 ON t1(e,c);560** SELECT * FROM t1 WHERE (a,b,c,d,e) IN (SELECT v,w,x,y,z FROM t2)561** \_______________________________________/562** The pX expression563**564** Since only columns e and c can be used with the index, in that order,565** the modified IN expression that is returned will be:566**567** (e,c) IN (SELECT z,x FROM t2)568**569** The reduced pX is different from the original (obviously) and thus is570** only used for indexing, to improve performance. The original unaltered571** IN expression must also be run on each output row for correctness.572*/573static Expr *removeUnindexableInClauseTerms(574 Parse *pParse, /* The parsing context */575 int iEq, /* Look at loop terms starting here */576 WhereLoop *pLoop, /* The current loop */577 Expr *pX /* The IN expression to be reduced */578){579 sqlite3 *db = pParse->db;580 Select *pSelect; /* Pointer to the SELECT on the RHS */581 Expr *pNew;582 pNew = sqlite3ExprDup(db, pX, 0);583 if( db->mallocFailed==0 ){584 for(pSelect=pNew->x.pSelect; pSelect; pSelect=pSelect->pPrior){585 ExprList *pOrigRhs; /* Original unmodified RHS */586 ExprList *pOrigLhs = 0; /* Original unmodified LHS */587 ExprList *pRhs = 0; /* New RHS after modifications */588 ExprList *pLhs = 0; /* New LHS after mods */589 int i; /* Loop counter */590 591 assert( ExprUseXSelect(pNew) );592 pOrigRhs = pSelect->pEList;593 assert( pNew->pLeft!=0 );594 assert( ExprUseXList(pNew->pLeft) );595 if( pSelect==pNew->x.pSelect ){596 pOrigLhs = pNew->pLeft->x.pList;597 }598 for(i=iEq; i<pLoop->nLTerm; i++){599 if( pLoop->aLTerm[i]->pExpr==pX ){600 int iField;601 assert( (pLoop->aLTerm[i]->eOperator & (WO_OR|WO_AND))==0 );602 iField = pLoop->aLTerm[i]->u.x.iField - 1;603 if( NEVER(pOrigRhs->a[iField].pExpr==0) ){604 continue; /* Duplicate PK column */605 }606 pRhs = sqlite3ExprListAppend(pParse, pRhs, pOrigRhs->a[iField].pExpr);607 pOrigRhs->a[iField].pExpr = 0;608 if( pRhs ) pRhs->a[pRhs->nExpr-1].u.x.iOrderByCol = iField+1;609 if( pOrigLhs ){610 assert( pOrigLhs->a[iField].pExpr!=0 );611 pLhs = sqlite3ExprListAppend(pParse,pLhs,pOrigLhs->a[iField].pExpr);612 pOrigLhs->a[iField].pExpr = 0;613 }614 }615 }616 sqlite3ExprListDelete(db, pOrigRhs);617 if( pOrigLhs ){618 sqlite3ExprListDelete(db, pOrigLhs);619 pNew->pLeft->x.pList = pLhs;620 }621 pSelect->pEList = pRhs;622 pSelect->selId = ++pParse->nSelect; /* Req'd for SubrtnSig validity */623 if( pLhs && pLhs->nExpr==1 ){624 /* Take care here not to generate a TK_VECTOR containing only a625 ** single value. Since the parser never creates such a vector, some626 ** of the subroutines do not handle this case. */627 Expr *p = pLhs->a[0].pExpr;628 pLhs->a[0].pExpr = 0;629 sqlite3ExprDelete(db, pNew->pLeft);630 pNew->pLeft = p;631 }632 633 /* If either the ORDER BY clause or the GROUP BY clause contains634 ** references to result-set columns, those references might now be635 ** obsolete. So fix them up.636 */637 assert( pRhs!=0 || db->mallocFailed );638 if( pRhs ){639 adjustOrderByCol(pSelect->pOrderBy, pRhs);640 adjustOrderByCol(pSelect->pGroupBy, pRhs);641 for(i=0; i<pRhs->nExpr; i++) pRhs->a[i].u.x.iOrderByCol = 0;642 }643 644#if 0645 printf("For indexing, change the IN expr:\n");646 sqlite3TreeViewExpr(0, pX, 0);647 printf("Into:\n");648 sqlite3TreeViewExpr(0, pNew, 0);649#endif650 }651 }652 return pNew;653}654 655 656#ifndef SQLITE_OMIT_SUBQUERY657/*658** Generate code for a single X IN (....) term of the WHERE clause.659**660** This is a special-case of codeEqualityTerm() that works for IN operators661** only. It is broken out into a subroutine because this case is662** uncommon and by splitting it off into a subroutine, the common case663** runs faster.664**665** The current value for the constraint is left in register iTarget.666** This routine sets up a loop that will iterate over all values of X.667*/668static SQLITE_NOINLINE void codeINTerm(669 Parse *pParse, /* The parsing context */670 WhereTerm *pTerm, /* The term of the WHERE clause to be coded */671 WhereLevel *pLevel, /* The level of the FROM clause we are working on */672 int iEq, /* Index of the equality term within this level */673 int bRev, /* True for reverse-order IN operations */674 int iTarget /* Attempt to leave results in this register */675){676 Expr *pX = pTerm->pExpr;677 int eType = IN_INDEX_NOOP;678 int iTab;679 struct InLoop *pIn;680 WhereLoop *pLoop = pLevel->pWLoop;681 Vdbe *v = pParse->pVdbe;682 int i;683 int nEq = 0;684 int *aiMap = 0;685 686 if( (pLoop->wsFlags & WHERE_VIRTUALTABLE)==0687 && pLoop->u.btree.pIndex!=0688 && pLoop->u.btree.pIndex->aSortOrder[iEq]689 ){690 testcase( iEq==0 );691 testcase( bRev );692 bRev = !bRev;693 }694 assert( pX->op==TK_IN );695 696 for(i=0; i<iEq; i++){697 if( pLoop->aLTerm[i] && pLoop->aLTerm[i]->pExpr==pX ){698 disableTerm(pLevel, pTerm);699 return;700 }701 }702 for(i=iEq; i<pLoop->nLTerm; i++){703 assert( pLoop->aLTerm[i]!=0 );704 if( pLoop->aLTerm[i]->pExpr==pX ) nEq++;705 }706 707 iTab = 0;708 if( !ExprUseXSelect(pX) || pX->x.pSelect->pEList->nExpr==1 ){709 eType = sqlite3FindInIndex(pParse, pX, IN_INDEX_LOOP, 0, 0, &iTab);710 }else{711 sqlite3 *db = pParse->db;712 Expr *pXMod = removeUnindexableInClauseTerms(pParse, iEq, pLoop, pX);713 if( !db->mallocFailed ){714 aiMap = (int*)sqlite3DbMallocZero(db, sizeof(int)*nEq);715 eType = sqlite3FindInIndex(pParse, pXMod, IN_INDEX_LOOP, 0, aiMap, &iTab);716 }717 sqlite3ExprDelete(db, pXMod);718 }719 720 if( eType==IN_INDEX_INDEX_DESC ){721 testcase( bRev );722 bRev = !bRev;723 }724 sqlite3VdbeAddOp2(v, bRev ? OP_Last : OP_Rewind, iTab, 0);725 VdbeCoverageIf(v, bRev);726 VdbeCoverageIf(v, !bRev);727 728 assert( (pLoop->wsFlags & WHERE_MULTI_OR)==0 );729 pLoop->wsFlags |= WHERE_IN_ABLE;730 if( pLevel->u.in.nIn==0 ){731 pLevel->addrNxt = sqlite3VdbeMakeLabel(pParse);732 }733 if( iEq>0 && (pLoop->wsFlags & WHERE_IN_SEEKSCAN)==0 ){734 pLoop->wsFlags |= WHERE_IN_EARLYOUT;735 }736 737 i = pLevel->u.in.nIn;738 pLevel->u.in.nIn += nEq;739 pLevel->u.in.aInLoop =740 sqlite3WhereRealloc(pTerm->pWC->pWInfo,741 pLevel->u.in.aInLoop,742 sizeof(pLevel->u.in.aInLoop[0])*pLevel->u.in.nIn);743 pIn = pLevel->u.in.aInLoop;744 if( pIn ){745 int iMap = 0; /* Index in aiMap[] */746 pIn += i;747 for(i=iEq; i<pLoop->nLTerm; i++){748 if( pLoop->aLTerm[i]->pExpr==pX ){749 int iOut = iTarget + i - iEq;750 if( eType==IN_INDEX_ROWID ){751 pIn->addrInTop = sqlite3VdbeAddOp2(v, OP_Rowid, iTab, iOut);752 }else{753 int iCol = aiMap ? aiMap[iMap++] : 0;754 pIn->addrInTop = sqlite3VdbeAddOp3(v,OP_Column,iTab, iCol, iOut);755 }756 sqlite3VdbeAddOp1(v, OP_IsNull, iOut); VdbeCoverage(v);757 if( i==iEq ){758 pIn->iCur = iTab;759 pIn->eEndLoopOp = bRev ? OP_Prev : OP_Next;760 if( iEq>0 ){761 pIn->iBase = iTarget - i;762 pIn->nPrefix = i;763 }else{764 pIn->nPrefix = 0;765 }766 }else{767 pIn->eEndLoopOp = OP_Noop;768 }769 pIn++;770 }771 }772 testcase( iEq>0773 && (pLoop->wsFlags & WHERE_IN_SEEKSCAN)==0774 && (pLoop->wsFlags & WHERE_VIRTUALTABLE)!=0 );775 if( iEq>0776 && (pLoop->wsFlags & (WHERE_IN_SEEKSCAN|WHERE_VIRTUALTABLE))==0777 ){778 sqlite3VdbeAddOp3(v, OP_SeekHit, pLevel->iIdxCur, 0, iEq);779 }780 }else{781 pLevel->u.in.nIn = 0;782 }783 sqlite3DbFree(pParse->db, aiMap);784}785#endif786 787 788/*789** Generate code for a single equality term of the WHERE clause. An equality790** term can be either X=expr or X IN (...). pTerm is the term to be791** coded.792**793** The current value for the constraint is left in a register, the index794** of which is returned. An attempt is made store the result in iTarget but795** this is only guaranteed for TK_ISNULL and TK_IN constraints. If the796** constraint is a TK_EQ or TK_IS, then the current value might be left in797** some other register and it is the caller's responsibility to compensate.798**799** For a constraint of the form X=expr, the expression is evaluated in800** straight-line code. For constraints of the form X IN (...)801** this routine sets up a loop that will iterate over all values of X.802*/803static int codeEqualityTerm(804 Parse *pParse, /* The parsing context */805 WhereTerm *pTerm, /* The term of the WHERE clause to be coded */806 WhereLevel *pLevel, /* The level of the FROM clause we are working on */807 int iEq, /* Index of the equality term within this level */808 int bRev, /* True for reverse-order IN operations */809 int iTarget /* Attempt to leave results in this register */810){811 Expr *pX = pTerm->pExpr;812 int iReg; /* Register holding results */813 814 assert( pLevel->pWLoop->aLTerm[iEq]==pTerm );815 assert( iTarget>0 );816 if( pX->op==TK_EQ || pX->op==TK_IS ){817 iReg = sqlite3ExprCodeTarget(pParse, pX->pRight, iTarget);818 }else if( pX->op==TK_ISNULL ){819 iReg = iTarget;820 sqlite3VdbeAddOp2(pParse->pVdbe, OP_Null, 0, iReg);821#ifndef SQLITE_OMIT_SUBQUERY822 }else{823 assert( pX->op==TK_IN );824 iReg = iTarget;825 codeINTerm(pParse, pTerm, pLevel, iEq, bRev, iTarget);826#endif827 }828 829 /* As an optimization, try to disable the WHERE clause term that is830 ** driving the index as it will always be true. The correct answer is831 ** obtained regardless, but we might get the answer with fewer CPU cycles832 ** by omitting the term.833 **834 ** But do not disable the term unless we are certain that the term is835 ** not a transitive constraint. For an example of where that does not836 ** work, see https://sqlite.org/forum/forumpost/eb8613976a (2021-05-04)837 */838 if( (pLevel->pWLoop->wsFlags & WHERE_TRANSCONS)==0839 || (pTerm->eOperator & WO_EQUIV)==0840 ){841 disableTerm(pLevel, pTerm);842 }843 844 return iReg;845}846 847/*848** Generate code that will evaluate all == and IN constraints for an849** index scan.850**851** For example, consider table t1(a,b,c,d,e,f) with index i1(a,b,c).852** Suppose the WHERE clause is this: a==5 AND b IN (1,2,3) AND c>5 AND c<10853** The index has as many as three equality constraints, but in this854** example, the third "c" value is an inequality. So only two855** constraints are coded. This routine will generate code to evaluate856** a==5 and b IN (1,2,3). The current values for a and b will be stored857** in consecutive registers and the index of the first register is returned.858**859** In the example above nEq==2. But this subroutine works for any value860** of nEq including 0. If nEq==0, this routine is nearly a no-op.861** The only thing it does is allocate the pLevel->iMem memory cell and862** compute the affinity string.863**864** The nExtraReg parameter is 0 or 1. It is 0 if all WHERE clause constraints865** are == or IN and are covered by the nEq. nExtraReg is 1 if there is866** an inequality constraint (such as the "c>=5 AND c<10" in the example) that867** occurs after the nEq quality constraints.868**869** This routine allocates a range of nEq+nExtraReg memory cells and returns870** the index of the first memory cell in that range. The code that871** calls this routine will use that memory range to store keys for872** start and termination conditions of the loop.873** key value of the loop. If one or more IN operators appear, then874** this routine allocates an additional nEq memory cells for internal875** use.876**877** Before returning, *pzAff is set to point to a buffer containing a878** copy of the column affinity string of the index allocated using879** sqlite3DbMalloc(). Except, entries in the copy of the string associated880** with equality constraints that use BLOB or NONE affinity are set to881** SQLITE_AFF_BLOB. This is to deal with SQL such as the following:882**883** CREATE TABLE t1(a TEXT PRIMARY KEY, b);884** SELECT ... FROM t1 AS t2, t1 WHERE t1.a = t2.b;885**886** In the example above, the index on t1(a) has TEXT affinity. But since887** the right hand side of the equality constraint (t2.b) has BLOB/NONE affinity,888** no conversion should be attempted before using a t2.b value as part of889** a key to search the index. Hence the first byte in the returned affinity890** string in this example would be set to SQLITE_AFF_BLOB.891*/892static int codeAllEqualityTerms(893 Parse *pParse, /* Parsing context */894 WhereLevel *pLevel, /* Which nested loop of the FROM we are coding */895 int bRev, /* Reverse the order of IN operators */896 int nExtraReg, /* Number of extra registers to allocate */897 char **pzAff /* OUT: Set to point to affinity string */898){899 u16 nEq; /* The number of == or IN constraints to code */900 u16 nSkip; /* Number of left-most columns to skip */901 Vdbe *v = pParse->pVdbe; /* The vm under construction */902 Index *pIdx; /* The index being used for this loop */903 WhereTerm *pTerm; /* A single constraint term */904 WhereLoop *pLoop; /* The WhereLoop object */905 int j; /* Loop counter */906 int regBase; /* Base register */907 int nReg; /* Number of registers to allocate */908 char *zAff; /* Affinity string to return */909 910 /* This module is only called on query plans that use an index. */911 pLoop = pLevel->pWLoop;912 assert( (pLoop->wsFlags & WHERE_VIRTUALTABLE)==0 );913 nEq = pLoop->u.btree.nEq;914 nSkip = pLoop->nSkip;915 pIdx = pLoop->u.btree.pIndex;916 assert( pIdx!=0 );917 918 /* Figure out how many memory cells we will need then allocate them.919 */920 regBase = pParse->nMem + 1;921 nReg = nEq + nExtraReg;922 pParse->nMem += nReg;923 924 zAff = sqlite3DbStrDup(pParse->db,sqlite3IndexAffinityStr(pParse->db,pIdx));925 assert( zAff!=0 || pParse->db->mallocFailed );926 927 if( nSkip ){928 int iIdxCur = pLevel->iIdxCur;929 sqlite3VdbeAddOp3(v, OP_Null, 0, regBase, regBase+nSkip-1);930 sqlite3VdbeAddOp1(v, (bRev?OP_Last:OP_Rewind), iIdxCur);931 VdbeCoverageIf(v, bRev==0);932 VdbeCoverageIf(v, bRev!=0);933 VdbeComment((v, "begin skip-scan on %s", pIdx->zName));934 j = sqlite3VdbeAddOp0(v, OP_Goto);935 assert( pLevel->addrSkip==0 );936 pLevel->addrSkip = sqlite3VdbeAddOp4Int(v, (bRev?OP_SeekLT:OP_SeekGT),937 iIdxCur, 0, regBase, nSkip);938 VdbeCoverageIf(v, bRev==0);939 VdbeCoverageIf(v, bRev!=0);940 sqlite3VdbeJumpHere(v, j);941 for(j=0; j<nSkip; j++){942 sqlite3VdbeAddOp3(v, OP_Column, iIdxCur, j, regBase+j);943 testcase( pIdx->aiColumn[j]==XN_EXPR );944 VdbeComment((v, "%s", explainIndexColumnName(pIdx, j)));945 }946 }947 948 /* Evaluate the equality constraints949 */950 assert( zAff==0 || (int)strlen(zAff)>=nEq );951 for(j=nSkip; j<nEq; j++){952 int r1;953 pTerm = pLoop->aLTerm[j];954 assert( pTerm!=0 );955 /* The following testcase is true for indices with redundant columns.956 ** Ex: CREATE INDEX i1 ON t1(a,b,a); SELECT * FROM t1 WHERE a=0 AND b=0; */957 testcase( (pTerm->wtFlags & TERM_CODED)!=0 );958 testcase( pTerm->wtFlags & TERM_VIRTUAL );959 r1 = codeEqualityTerm(pParse, pTerm, pLevel, j, bRev, regBase+j);960 if( r1!=regBase+j ){961 if( nReg==1 ){962 sqlite3ReleaseTempReg(pParse, regBase);963 regBase = r1;964 }else{965 sqlite3VdbeAddOp2(v, OP_Copy, r1, regBase+j);966 }967 }968 if( pTerm->eOperator & WO_IN ){969 if( pTerm->pExpr->flags & EP_xIsSelect ){970 /* No affinity ever needs to be (or should be) applied to a value971 ** from the RHS of an "? IN (SELECT ...)" expression. The972 ** sqlite3FindInIndex() routine has already ensured that the973 ** affinity of the comparison has been applied to the value. */974 if( zAff ) zAff[j] = SQLITE_AFF_BLOB;975 }976 }else if( (pTerm->eOperator & WO_ISNULL)==0 ){977 Expr *pRight = pTerm->pExpr->pRight;978 if( (pTerm->wtFlags & TERM_IS)==0 && sqlite3ExprCanBeNull(pRight) ){979 sqlite3VdbeAddOp2(v, OP_IsNull, regBase+j, pLevel->addrBrk);980 VdbeCoverage(v);981 }982 if( pParse->nErr==0 ){983 assert( pParse->db->mallocFailed==0 );984 if( sqlite3CompareAffinity(pRight, zAff[j])==SQLITE_AFF_BLOB ){985 zAff[j] = SQLITE_AFF_BLOB;986 }987 if( sqlite3ExprNeedsNoAffinityChange(pRight, zAff[j]) ){988 zAff[j] = SQLITE_AFF_BLOB;989 }990 }991 }992 }993 *pzAff = zAff;994 return regBase;995}996 997#ifndef SQLITE_LIKE_DOESNT_MATCH_BLOBS998/*999** If the most recently coded instruction is a constant range constraint1000** (a string literal) that originated from the LIKE optimization, then1001** set P3 and P5 on the OP_String opcode so that the string will be cast1002** to a BLOB at appropriate times.1003**1004** The LIKE optimization trys to evaluate "x LIKE 'abc%'" as a range1005** expression: "x>='ABC' AND x<'abd'". But this requires that the range1006** scan loop run twice, once for strings and a second time for BLOBs.1007** The OP_String opcodes on the second pass convert the upper and lower1008** bound string constants to blobs. This routine makes the necessary changes1009** to the OP_String opcodes for that to happen.1010**1011** Except, of course, if SQLITE_LIKE_DOESNT_MATCH_BLOBS is defined, then1012** only the one pass through the string space is required, so this routine1013** becomes a no-op.1014*/1015static void whereLikeOptimizationStringFixup(1016 Vdbe *v, /* prepared statement under construction */1017 WhereLevel *pLevel, /* The loop that contains the LIKE operator */1018 WhereTerm *pTerm /* The upper or lower bound just coded */1019){1020 if( pTerm->wtFlags & TERM_LIKEOPT ){1021 VdbeOp *pOp;1022 assert( pLevel->iLikeRepCntr>0 );1023 pOp = sqlite3VdbeGetLastOp(v);1024 assert( pOp!=0 );1025 assert( pOp->opcode==OP_String81026 || pTerm->pWC->pWInfo->pParse->db->mallocFailed );1027 pOp->p3 = (int)(pLevel->iLikeRepCntr>>1); /* Register holding counter */1028 pOp->p5 = (u8)(pLevel->iLikeRepCntr&1); /* ASC or DESC */1029 }1030}1031#else1032# define whereLikeOptimizationStringFixup(A,B,C)1033#endif1034 1035#ifdef SQLITE_ENABLE_CURSOR_HINTS1036/*1037** Information is passed from codeCursorHint() down to individual nodes of1038** the expression tree (by sqlite3WalkExpr()) using an instance of this1039** structure.1040*/1041struct CCurHint {1042 int iTabCur; /* Cursor for the main table */1043 int iIdxCur; /* Cursor for the index, if pIdx!=0. Unused otherwise */1044 Index *pIdx; /* The index used to access the table */1045};1046 1047/*1048** This function is called for every node of an expression that is a candidate1049** for a cursor hint on an index cursor. For TK_COLUMN nodes that reference1050** the table CCurHint.iTabCur, verify that the same column can be1051** accessed through the index. If it cannot, then set pWalker->eCode to 1.1052*/1053static int codeCursorHintCheckExpr(Walker *pWalker, Expr *pExpr){1054 struct CCurHint *pHint = pWalker->u.pCCurHint;1055 assert( pHint->pIdx!=0 );1056 if( pExpr->op==TK_COLUMN1057 && pExpr->iTable==pHint->iTabCur1058 && sqlite3TableColumnToIndex(pHint->pIdx, pExpr->iColumn)<01059 ){1060 pWalker->eCode = 1;1061 }1062 return WRC_Continue;1063}1064 1065/*1066** Test whether or not expression pExpr, which was part of a WHERE clause,1067** should be included in the cursor-hint for a table that is on the rhs1068** of a LEFT JOIN. Set Walker.eCode to non-zero before returning if the1069** expression is not suitable.1070**1071** An expression is unsuitable if it might evaluate to non NULL even if1072** a TK_COLUMN node that does affect the value of the expression is set1073** to NULL. For example:1074**1075** col IS NULL1076** col IS NOT NULL1077** coalesce(col, 1)1078** CASE WHEN col THEN 0 ELSE 1 END1079*/1080static int codeCursorHintIsOrFunction(Walker *pWalker, Expr *pExpr){1081 if( pExpr->op==TK_IS1082 || pExpr->op==TK_ISNULL || pExpr->op==TK_ISNOT1083 || pExpr->op==TK_NOTNULL || pExpr->op==TK_CASE1084 ){1085 pWalker->eCode = 1;1086 }else if( pExpr->op==TK_FUNCTION ){1087 int d1;1088 char d2[4];1089 if( 0==sqlite3IsLikeFunction(pWalker->pParse->db, pExpr, &d1, d2) ){1090 pWalker->eCode = 1;1091 }1092 }1093 1094 return WRC_Continue;1095}1096 1097 1098/*1099** This function is called on every node of an expression tree used as an1100** argument to the OP_CursorHint instruction. If the node is a TK_COLUMN1101** that accesses any table other than the one identified by1102** CCurHint.iTabCur, then do the following:1103**1104** 1) allocate a register and code an OP_Column instruction to read1105** the specified column into the new register, and1106**1107** 2) transform the expression node to a TK_REGISTER node that reads1108** from the newly populated register.1109**1110** Also, if the node is a TK_COLUMN that does access the table identified1111** by pCCurHint.iTabCur, and an index is being used (which we will1112** know because CCurHint.pIdx!=0) then transform the TK_COLUMN into1113** an access of the index rather than the original table.1114*/1115static int codeCursorHintFixExpr(Walker *pWalker, Expr *pExpr){1116 int rc = WRC_Continue;1117 int reg;1118 struct CCurHint *pHint = pWalker->u.pCCurHint;1119 if( pExpr->op==TK_COLUMN ){1120 if( pExpr->iTable!=pHint->iTabCur ){1121 reg = ++pWalker->pParse->nMem; /* Register for column value */1122 reg = sqlite3ExprCodeTarget(pWalker->pParse, pExpr, reg);1123 pExpr->op = TK_REGISTER;1124 pExpr->iTable = reg;1125 }else if( pHint->pIdx!=0 ){1126 pExpr->iTable = pHint->iIdxCur;1127 pExpr->iColumn = sqlite3TableColumnToIndex(pHint->pIdx, pExpr->iColumn);1128 assert( pExpr->iColumn>=0 );1129 }1130 }else if( pExpr->pAggInfo ){1131 rc = WRC_Prune;1132 reg = ++pWalker->pParse->nMem; /* Register for column value */1133 reg = sqlite3ExprCodeTarget(pWalker->pParse, pExpr, reg);1134 pExpr->op = TK_REGISTER;1135 pExpr->iTable = reg;1136 }else if( pExpr->op==TK_TRUEFALSE ){1137 /* Do not walk disabled expressions. tag-20230504-1 */1138 return WRC_Prune;1139 }1140 return rc;1141}1142 1143/*1144** Insert an OP_CursorHint instruction if it is appropriate to do so.1145*/1146static void codeCursorHint(1147 SrcItem *pTabItem, /* FROM clause item */1148 WhereInfo *pWInfo, /* The where clause */1149 WhereLevel *pLevel, /* Which loop to provide hints for */1150 WhereTerm *pEndRange /* Hint this end-of-scan boundary term if not NULL */1151){1152 Parse *pParse = pWInfo->pParse;1153 sqlite3 *db = pParse->db;1154 Vdbe *v = pParse->pVdbe;1155 Expr *pExpr = 0;1156 WhereLoop *pLoop = pLevel->pWLoop;1157 int iCur;1158 WhereClause *pWC;1159 WhereTerm *pTerm;1160 int i, j;1161 struct CCurHint sHint;1162 Walker sWalker;1163 1164 if( OptimizationDisabled(db, SQLITE_CursorHints) ) return;1165 iCur = pLevel->iTabCur;1166 assert( iCur==pWInfo->pTabList->a[pLevel->iFrom].iCursor );1167 sHint.iTabCur = iCur;1168 sHint.iIdxCur = pLevel->iIdxCur;1169 sHint.pIdx = pLoop->u.btree.pIndex;1170 memset(&sWalker, 0, sizeof(sWalker));1171 sWalker.pParse = pParse;1172 sWalker.u.pCCurHint = &sHint;1173 pWC = &pWInfo->sWC;1174 for(i=0; i<pWC->nBase; i++){1175 pTerm = &pWC->a[i];1176 if( pTerm->wtFlags & (TERM_VIRTUAL|TERM_CODED) ) continue;1177 if( pTerm->prereqAll & pLevel->notReady ) continue;1178 1179 /* Any terms specified as part of the ON(...) clause for any LEFT1180 ** JOIN for which the current table is not the rhs are omitted1181 ** from the cursor-hint.1182 **1183 ** If this table is the rhs of a LEFT JOIN, "IS" or "IS NULL" terms1184 ** that were specified as part of the WHERE clause must be excluded.1185 ** This is to address the following:1186 **1187 ** SELECT ... t1 LEFT JOIN t2 ON (t1.a=t2.b) WHERE t2.c IS NULL;1188 **1189 ** Say there is a single row in t2 that matches (t1.a=t2.b), but its1190 ** t2.c values is not NULL. If the (t2.c IS NULL) constraint is1191 ** pushed down to the cursor, this row is filtered out, causing1192 ** SQLite to synthesize a row of NULL values. Which does match the1193 ** WHERE clause, and so the query returns a row. Which is incorrect.1194 **1195 ** For the same reason, WHERE terms such as:1196 **1197 ** WHERE 1 = (t2.c IS NULL)1198 **1199 ** are also excluded. See codeCursorHintIsOrFunction() for details.1200 */