In partial index scans, if the WHERE clause implies a constant value for a table column, replace occurences of that table column with the constant. This increases the likelihood of the partial index being a covering index.

FossilOrigin-Name: 66ed7abdfa228abde2052e3988589371f0e49b11582b1b4a83255d2df3a0aefa

1 files changed, 39 insertions, 0 deletions

diff --git a/src/expr.c b/src/expr.c
index 63c5a8faa..f0575d629 100644
--- a/src/expr.c
+++ b/src/expr.c
@@ -4253,6 +4253,40 @@ static SQLITE_NOINLINE int sqlite3IndexedExprLookup(
 
 
 /*
+** Expresion pExpr is guaranteed to be a TK_COLUMN or equivalent. This
+** function checks the Parse.pIdxPartExpr list to see if this column
+** can be replaced with a constant value. If so, it generates code to
+** put the constant value in a register (ideally, but not necessarily, 
+** register iTarget) and returns the register number.
+**
+** Or, if the TK_COLUMN cannot be replaced by a constant, zero is 
+** returned.
+*/
+static int exprPartidxExprLookup(Parse *pParse, Expr *pExpr, int iTarget){
+  IndexedExpr *p;
+  for(p=pParse->pIdxPartExpr; p; p=p->pIENext){
+    if( pExpr->iColumn==p->iIdxCol && pExpr->iTable==p->iDataCur ){
+      Vdbe *v = pParse->pVdbe;
+      int addr = 0;
+      int ret;
+
+      if( p->bMaybeNullRow ){
+        addr = sqlite3VdbeAddOp1(v, OP_IfNullRow, p->iIdxCur);
+      }
+      ret = sqlite3ExprCodeTarget(pParse, p->pExpr, iTarget);
+      sqlite3VdbeAddOp4(pParse->pVdbe, OP_Affinity, ret, 1, 0, &p->aff, 1);
+      if( addr ){
+        sqlite3VdbeJumpHere(v, addr);
+        sqlite3VdbeChangeP3(v, addr, ret);
+      }
+      return ret;
+    }
+  }
+  return 0;
+}
+
+
+/*
 ** Generate code into the current Vdbe to evaluate the given
 ** expression.  Attempt to store the results in register "target".
 ** Return the register where results are stored.
@@ -4358,6 +4392,11 @@ expr_code_doover:
         }
         return iReg;
       }
+      if( pParse->pIdxPartExpr 
+       && 0!=(r1 = exprPartidxExprLookup(pParse, pExpr, target))
+      ){
+        return r1;
+      }
       if( iTab<0 ){
         if( pParse->iSelfTab<0 ){
           /* Other columns in the same row for CHECK constraints or