SQLite

**
** If pExpr is a TK_COLUMN column reference, then this routine always returns
** true even if that particular column is not indexed, because the column
** might be added to an automatic index later.
*/
static SQLITE_NOINLINE int exprMightBeIndexed2(
  SrcList *pFrom,        /* The FROM clause */

  int *aiCurCol,         /* Write the referenced table cursor and column here */
  Expr *pExpr,           /* An operand of a comparison operator */
  int j                  /* Start looking with the j-th pFrom entry */
){
  Index *pIdx;
  int i;
  int iCur;
  do{
    iCur = pFrom->a[j].iCursor;
    for(pIdx=pFrom->a[j].pTab->pIndex; pIdx; pIdx=pIdx->pNext){
      if( pIdx->aColExpr==0 ) continue;
      for(i=0; i<pIdx->nKeyCol; i++){
        if( pIdx->aiColumn[i]!=XN_EXPR ) continue;
        assert( pIdx->bHasExpr );
        if( sqlite3ExprCompareSkip(pExpr,pIdx->aColExpr->a[i].pExpr,iCur)==0 ){
          aiCurCol[0] = iCur;
          aiCurCol[1] = XN_EXPR;
          return 1;
        }
      }
    }
  }while( ++j < pFrom->nSrc );
  return 0;
}
static int exprMightBeIndexed(
  SrcList *pFrom,        /* The FROM clause */

  int *aiCurCol,         /* Write the referenced table cursor & column here */
  Expr *pExpr,           /* An operand of a comparison operator */
  int op                 /* The specific comparison operator */
){
  int i;

  /* If this expression is a vector to the left or right of a 
  ** inequality constraint (>, <, >= or <=), perform the processing 
  ** on the first element of the vector.  */
  assert( TK_GT+1==TK_LE && TK_GT+2==TK_LT && TK_GT+3==TK_GE );
  assert( TK_IS<TK_GE && TK_ISNULL<TK_GE && TK_IN<TK_GE );
  assert( op<=TK_GE );
  if( pExpr->op==TK_VECTOR && (op>=TK_GT && ALWAYS(op<=TK_GE)) ){
    assert( ExprUseXList(pExpr) );
    pExpr = pExpr->x.pList->a[0].pExpr;

  }

  if( pExpr->op==TK_COLUMN ){
    aiCurCol[0] = pExpr->iTable;
    aiCurCol[1] = pExpr->iColumn;
    return 1;
  }

  for(i=0; i<pFrom->nSrc; i++){
    Index *pIdx;
    for(pIdx=pFrom->a[i].pTab->pIndex; pIdx; pIdx=pIdx->pNext){
      if( pIdx->aColExpr ){
        return exprMightBeIndexed2(pFrom,aiCurCol,pExpr,i);
      }
    }
  }
  return 0;
}


/*
** The input to this routine is an WhereTerm structure with only the
** "pExpr" field filled in.  The job of this routine is to analyze the
** subexpression and populate all the other fields of the WhereTerm

    if( pTerm->u.x.iField>0 ){
      assert( op==TK_IN );
      assert( pLeft->op==TK_VECTOR );
      assert( ExprUseXList(pLeft) );
      pLeft = pLeft->x.pList->a[pTerm->u.x.iField-1].pExpr;
    }

    if( exprMightBeIndexed(pSrc, aiCurCol, pLeft, op) ){
      pTerm->leftCursor = aiCurCol[0];
      assert( (pTerm->eOperator & (WO_OR|WO_AND))==0 );
      pTerm->u.x.leftColumn = aiCurCol[1];
      pTerm->eOperator = operatorMask(op) & opMask;
    }
    if( op==TK_IS ) pTerm->wtFlags |= TERM_IS;
    if( pRight 
     && exprMightBeIndexed(pSrc, aiCurCol, pRight, op)
     && !ExprHasProperty(pRight, EP_FixedCol)
    ){
      WhereTerm *pNew;
      Expr *pDup;
      u16 eExtraOp = 0;        /* Extra bits for pNew->eOperator */
      assert( pTerm->u.x.iField==0 );
      if( pTerm->leftCursor>=0 ){

        FROM tableB
        WHERE RunYearMonth = 202004
    ) AS B
    ON A.ID = B.ID
    AND A.RunYearMonth = B.RunYearMonth;
} {4 202004 4 202004 5 202004 5 202004}

# 2023-02-10 https://sqlite.org/forum/forumpost/0a539c76db3b9e29
# The original constant propagation implementation caused a performance
# regression.  Because "abs(v)" was rewritten into "abs(1)" it no longer
# matches the indexed column and the index is not used.
# 
reset_db
do_execsql_test 700 {
  CREATE TABLE t1(v INTEGER);
  WITH RECURSIVE c(x) AS (VALUES(-10) UNION ALL SELECT x+1 FROM c WHERE x<10)
    INSERT INTO t1(v) SELECT x FROM c;
  CREATE INDEX idx ON t1( abs(v) );
  SELECT v FROM t1 WHERE abs(v)=1 and v=1;
} 1
do_eqp_test 710 {
  SELECT v FROM t1 WHERE abs(v)=1 and v=1;
} {
  QUERY PLAN
  `--SEARCH t1 USING INDEX idx (<expr>=?)
}

finish_test