SQLite

** and convert them into references to the index.  Hence we always want
** the actual table at hand in order to recompute the virtual column, if
** necessary.
**
** The colNotIdxed mask is AND-ed with the SrcList.a[].colUsed mask
** to determine if the index is covering index.
*/
static void recomputeColumnsNotIndexed(Parse *pParse, Index *pIdx){
  Bitmask m = 0;
  int j;
  Table *pTab = pIdx->pTable;
  for(j=pIdx->nColumn-1; j>=0; j--){
    int x = pIdx->aiColumn[j];
    if( x>=0 && (pTab->aCol[x].colFlags & COLFLAG_VIRTUAL)==0 ){
      testcase( x==BMS-1 );

      pPk->aiColumn[j] = i;
      pPk->azColl[j] = sqlite3StrBINARY;
      j++;
    }
  }
  assert( pPk->nColumn==j );
  assert( pTab->nNVCol<=j );
  recomputeColumnsNotIndexed(pParse, pPk);
}


#ifndef SQLITE_OMIT_VIRTUALTABLE
/*
** Return true if pTab is a virtual table and zName is a shadow table name
** for that virtual table.

  sqlite3DefaultRowEst(pIndex);
  if( pParse->pNewTable==0 ) estimateIndexWidth(pIndex);

  /* If this index contains every column of its table, then mark
  ** it as a covering index */
  assert( HasRowid(pTab)
      || pTab->iPKey<0 || sqlite3TableColumnToIndex(pIndex, pTab->iPKey)>=0 );
  recomputeColumnsNotIndexed(pParse, pIndex);
  if( pTblName!=0 && pIndex->nColumn>=pTab->nCol ){
    pIndex->isCovering = 1;
    for(j=0; j<pTab->nCol; j++){
      if( j==pTab->iPKey ) continue;
      if( sqlite3TableColumnToIndex(pIndex,j)>=0 ) continue;
      pIndex->isCovering = 0;
      break;

      VdbeComment((v, "%s expr-column %d", p->zIdxName, p->iIdxCol));
    }
    return target;
  }
  return -1;  /* Not found */
}


/*
** 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,
                        (const char*)&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.
**
** With this routine, there is no guarantee that results will

            return iSrc;
          }
        }else{
          /* Coding an expression that is part of an index where column names
          ** in the index refer to the table to which the index belongs */
          iTab = pParse->iSelfTab - 1;
        }
      }
      else if( pParse->pIdxPartExpr 
       && 0!=(r1 = exprPartidxExprLookup(pParse, pExpr, target))
      ){
        return r1;
      }
      assert( ExprUseYTab(pExpr) );
      assert( pExpr->y.pTab!=0 );
      iReg = sqlite3ExprCodeGetColumn(pParse, pExpr->y.pTab,
                               pExpr->iColumn, iTab, target,
                               pExpr->op2);
      return iReg;

    sqlite3ExprListDelete(db, pParse->pConstExpr);
  }
  assert( db->lookaside.bDisable >= pParse->disableLookaside );
  db->lookaside.bDisable -= pParse->disableLookaside;
  db->lookaside.sz = db->lookaside.bDisable ? 0 : db->lookaside.szTrue;
  assert( pParse->db->pParse==pParse );
  db->pParse = pParse->pOuterParse;


}

/*
** Add a new cleanup operation to a Parser.  The cleanup should happen when
** the parser object is destroyed.  But, beware: the cleanup might happen
** immediately.
**

  int iSelfTab;        /* Table associated with an index on expr, or negative
                       ** of the base register during check-constraint eval */
  int nLabel;          /* The *negative* of the number of labels used */
  int nLabelAlloc;     /* Number of slots in aLabel */
  int *aLabel;         /* Space to hold the labels */
  ExprList *pConstExpr;/* Constant expressions */
  IndexedExpr *pIdxEpr;/* List of expressions used by active indexes */
  IndexedExpr *pIdxPartExpr; /* Exprs constrained by index WHERE clauses */
  Token constraintName;/* Name of the constraint currently being parsed */
  yDbMask writeMask;   /* Start a write transaction on these databases */
  yDbMask cookieMask;  /* Bitmask of schema verified databases */
  int regRowid;        /* Register holding rowid of CREATE TABLE entry */
  int regRoot;         /* Register holding root page number for new objects */
  int nMaxArg;         /* Max args passed to user function by sub-program */
  int nSelect;         /* Number of SELECT stmts. Counter for Select.selId */

  const WhereTerm *pTerm;              /* For looping over WHERE clause terms */
  const WhereTerm *pWCEnd;             /* Last WHERE clause term */
  Parse *pParse = pWInfo->pParse;      /* Parsing context */
  Vdbe *v = pParse->pVdbe;             /* VDBE under construction */
  WhereLoop *pLoop = pLevel->pWLoop;   /* The loop being coded */
  int iCur;                            /* Cursor for table getting the filter */
  IndexedExpr *saved_pIdxEpr;          /* saved copy of Parse.pIdxEpr */
  IndexedExpr *saved_pIdxPartExpr;     /* saved copy of Parse.pIdxPartExpr */

  saved_pIdxEpr = pParse->pIdxEpr;
  saved_pIdxPartExpr = pParse->pIdxPartExpr;
  pParse->pIdxEpr = 0;
  pParse->pIdxPartExpr = 0;

  assert( pLoop!=0 );
  assert( v!=0 );
  assert( pLoop->wsFlags & WHERE_BLOOMFILTER );
  assert( (pLoop->wsFlags & WHERE_IDX_ONLY)==0 );

  addrOnce = sqlite3VdbeAddOp0(v, OP_Once); VdbeCoverage(v);
  do{
    const SrcList *pTabList;
    const SrcItem *pItem;
    const Table *pTab;
    u64 sz;

        ** the IN operator */
        break;
      }
    }
  }while( iLevel < pWInfo->nLevel );
  sqlite3VdbeJumpHere(v, addrOnce);
  pParse->pIdxEpr = saved_pIdxEpr;
  pParse->pIdxPartExpr = saved_pIdxPartExpr;
}


#ifndef SQLITE_OMIT_VIRTUALTABLE
/*
** Allocate and populate an sqlite3_index_info structure. It is the
** responsibility of the caller to eventually release the structure

  }else if( ck.bExpr ){
    rc = WHERE_EXPRIDX;
  }else{
    rc = WHERE_IDX_ONLY;
  }
  return rc;
}

/*
** This is an sqlite3ParserAddCleanup() callback that is invoked to
** free the Parse->pIdxEpr list when the Parse object is destroyed.
*/
static void whereIndexedExprCleanup(sqlite3 *db, void *pObject){
  IndexedExpr **pp = (IndexedExpr**)pObject;
  while( *pp!=0 ){
    IndexedExpr *p = *pp;
    *pp = p->pIENext;
    sqlite3ExprDelete(db, p->pExpr);
    sqlite3DbFreeNN(db, p);
  }
}

/*
** This function is called for a partial index - one with a WHERE clause - in 
** two scenarios. In both cases, it determines whether or not the WHERE 
** clause on the index implies that a column of the table may be safely
** replaced by a constant expression. For example, in the following 
** SELECT:
**
**   CREATE INDEX i1 ON t1(b, c) WHERE a=<expr>;
**   SELECT a, b, c FROM t1 WHERE a=<expr> AND b=?;
**
** The "a" in the select-list may be replaced by <expr>, iff:
**
**    (a) <expr> is a constant expression, and
**    (b) The (a=<expr>) comparison uses the BINARY collation sequence, and
**    (c) Column "a" has an affinity other than NONE or BLOB.
**
** If argument pItem is NULL, then pMask must not be NULL. In this case this 
** function is being called as part of determining whether or not pIdx
** is a covering index. This function clears any bits in (*pMask) 
** corresponding to columns that may be replaced by constants as described
** above.
**
** Otherwise, if pItem is not NULL, then this function is being called
** as part of coding a loop that uses index pIdx. In this case, add entries
** to the Parse.pIdxPartExpr list for each column that can be replaced
** by a constant.
*/
static void wherePartIdxExpr(
  Parse *pParse,                  /* Parse context */
  Index *pIdx,                    /* Partial index being processed */
  Expr *pPart,                    /* WHERE clause being processed */
  Bitmask *pMask,                 /* Mask to clear bits in */
  int iIdxCur,                    /* Cursor number for index */
  SrcItem *pItem                  /* The FROM clause entry for the table */
){
  assert( pItem==0 || (pItem->fg.jointype & JT_RIGHT)==0 );
  assert( (pItem==0 || pMask==0) && (pMask!=0 || pItem!=0) );

  if( pPart->op==TK_AND ){
    wherePartIdxExpr(pParse, pIdx, pPart->pRight, pMask, iIdxCur, pItem);
    pPart = pPart->pLeft;
  }

  if( (pPart->op==TK_EQ || pPart->op==TK_IS) ){
    Expr *pLeft = pPart->pLeft;
    Expr *pRight = pPart->pRight;
    u8 aff;

    if( pRight->op==TK_COLUMN ){
      SWAP(Expr*, pLeft, pRight);
    }

    if( pLeft->op!=TK_COLUMN ) return;
    if( !sqlite3ExprIsConstant(pRight) ) return;
    if( !sqlite3IsBinary(sqlite3ExprCompareCollSeq(pParse, pPart)) ) return;
    if( pLeft->iColumn<0 ) return;
    aff = pIdx->pTable->aCol[pLeft->iColumn].affinity;
    if( aff>=SQLITE_AFF_TEXT ){
      if( pItem ){
        sqlite3 *db = pParse->db;
        IndexedExpr *p = (IndexedExpr*)sqlite3DbMallocRaw(db, sizeof(*p));
        if( p ){
          int bNullRow = (pItem->fg.jointype&(JT_LEFT|JT_LTORJ))!=0;
          p->pExpr = sqlite3ExprDup(db, pRight, 0);
          p->iDataCur = pItem->iCursor;
          p->iIdxCur = iIdxCur;
          p->iIdxCol = pLeft->iColumn;
          p->bMaybeNullRow = bNullRow;
          p->pIENext = pParse->pIdxPartExpr;
          p->aff = aff;
          pParse->pIdxPartExpr = p;
          if( p->pIENext==0 ){
            void *pArg = (void*)&pParse->pIdxPartExpr;
            sqlite3ParserAddCleanup(pParse, whereIndexedExprCleanup, pArg);
          }
        }
      }else if( pLeft->iColumn<(BMS-1) ){
        *pMask &= ~((Bitmask)1 << pLeft->iColumn);
      }
    }
  }
}


/*
** Add all WhereLoop objects for a single table of the join where the table
** is identified by pBuilder->pNew->iTab.  That table is guaranteed to be
** a b-tree table, not a virtual table.
**
** The costs (WhereLoop.rRun) of the b-tree loops added by this function

    }else{
      Bitmask m;
      if( pProbe->isCovering ){
        m = 0;
        pNew->wsFlags = WHERE_IDX_ONLY | WHERE_INDEXED;
      }else{
        m = pSrc->colUsed & pProbe->colNotIdxed;
        if( pProbe->pPartIdxWhere ){
          wherePartIdxExpr(
              pWInfo->pParse, pProbe, pProbe->pPartIdxWhere, &m, 0, 0
          );
        }
        pNew->wsFlags = WHERE_INDEXED;
        if( m==TOPBIT || (pProbe->bHasExpr && !pProbe->bHasVCol && m!=0) ){
          u32 isCov = whereIsCoveringIndex(pWInfo, pProbe, pSrc->iCursor);
          if( isCov==0 ){
            WHERETRACE(0x200,
               ("-> %s is not a covering index"
                " according to whereIsCoveringIndex()\n", pProbe->zName));

           (double)sqlite3LogEstToInt(pTab->nRowLogEst)));
      }
    }
    nSearch += pLoop->nOut;
  }
}















/*
** The index pIdx is used by a query and contains one or more expressions.
** In other words pIdx is an index on an expression.  iIdxCur is the cursor
** number for the index and iDataCur is the cursor number for the corresponding
** table.
**
** This routine adds IndexedExpr entries to the Parse->pIdxEpr field for

      p->aff = pIdx->zColAff[i];
    }
#ifdef SQLITE_ENABLE_EXPLAIN_COMMENTS
    p->zIdxName = pIdx->zName;
#endif
    pParse->pIdxEpr = p;
    if( p->pIENext==0 ){
      void *pArg = (void*)&pParse->pIdxEpr;
      sqlite3ParserAddCleanup(pParse, whereIndexedExprCleanup, pArg);
    }
  }
}

/*
** Set the reverse-scan order mask to one for all tables in the query
** with the exception of MATERIALIZED common table expressions that have

      }else if( iAuxArg && (wctrlFlags & WHERE_OR_SUBCLAUSE)!=0 ){
        iIndexCur = iAuxArg;
        op = OP_ReopenIdx;
      }else{
        iIndexCur = pParse->nTab++;
        if( pIx->bHasExpr && OptimizationEnabled(db, SQLITE_IndexedExpr) ){
          whereAddIndexedExpr(pParse, pIx, iIndexCur, pTabItem);
        }
        if( pIx->pPartIdxWhere && (pTabItem->fg.jointype & JT_RIGHT)==0 ){
          wherePartIdxExpr(
              pParse, pIx, pIx->pPartIdxWhere, 0, iIndexCur, pTabItem
          );
        }
      }
      pLevel->iIdxCur = iIndexCur;
      assert( pIx!=0 );
      assert( pIx->pSchema==pTab->pSchema );
      assert( iIndexCur>=0 );
      if( op ){

# 2023 September 23
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix indexA

do_execsql_test 1.0 {
  CREATE TABLE t1(a TEXT, b, c);
  CREATE INDEX i1 ON t1(b, c) WHERE a='abc';
  INSERT INTO t1 VALUES('abc', 1, 2);
}

do_execsql_test 1.1 {
  SELECT * FROM t1 WHERE a='abc'
} {abc 1 2}

do_eqp_test 1.2 {
  SELECT * FROM t1 WHERE a='abc'
} {USING COVERING INDEX i1}

do_execsql_test 1.3 {
  CREATE INDEX i2 ON t1(b, c) WHERE a=5;
  INSERT INTO t1 VALUES(5, 4, 3);

  SELECT a, typeof(a), b, c FROM t1 WHERE a=5;
} {5 text 4 3}

do_execsql_test 1.4 {
  CREATE TABLE t2(x);
  INSERT INTO t2 VALUES('v');
}

do_execsql_test 1.5 {
  SELECT x, a, b, c FROM t2 LEFT JOIN t1 ON (a=5 AND b=x)
} {v {} {} {}}

do_execsql_test 1.6 {
  SELECT x, a, b, c FROM t2 RIGHT JOIN t1 ON (t1.a=5 AND t1.b=t2.x)
} {{} abc 1 2   {} 5 4 3}

do_eqp_test 1.7 {
  SELECT x, a, b, c FROM t2 RIGHT JOIN t1 ON (t1.a=5 AND t1.b=t2.x)
} {USING INDEX i2}

#-------------------------------------------------------------------------
reset_db

do_execsql_test 2.0 {
  CREATE TABLE x1(a TEXT, b, c);
  INSERT INTO x1 VALUES('2', 'two', 'ii');
  INSERT INTO x1 VALUES('2.0', 'twopointoh', 'ii.0');

  CREATE TABLE x2(a NUMERIC, b, c);
  INSERT INTO x2 VALUES('2', 'two', 'ii');
  INSERT INTO x2 VALUES('2.0', 'twopointoh', 'ii.0');

  CREATE TABLE x3(a REAL, b, c);
  INSERT INTO x3 VALUES('2', 'two', 'ii');
  INSERT INTO x3 VALUES('2.0', 'twopointoh', 'ii.0');
}

foreach {tn idx} {
  0 {
  }
  1 {
    CREATE INDEX i1 ON x1(b, c) WHERE a=2;
    CREATE INDEX i2 ON x2(b, c) WHERE a=2;
    CREATE INDEX i3 ON x3(b, c) WHERE a=2;
  }
  2 {
    CREATE INDEX i1 ON x1(b, c) WHERE a=2.0;
    CREATE INDEX i2 ON x2(b, c) WHERE a=2.0;
    CREATE INDEX i3 ON x3(b, c) WHERE a=2.0;
  }
  3 {
    CREATE INDEX i1 ON x1(b, c) WHERE a='2.0';
    CREATE INDEX i2 ON x2(b, c) WHERE a='2.0';
    CREATE INDEX i3 ON x3(b, c) WHERE a='2.0';
  }
  4 {
    CREATE INDEX i1 ON x1(b, c) WHERE a='2';
    CREATE INDEX i2 ON x2(b, c) WHERE a='2';
    CREATE INDEX i3 ON x3(b, c) WHERE a='2';
  }
} {
  execsql { DROP INDEX IF EXISTS i1 }
  execsql { DROP INDEX IF EXISTS i2 }
  execsql { DROP INDEX IF EXISTS i3 }

  execsql $idx
  do_execsql_test 2.1.$tn.1 {
    SELECT *, typeof(a) FROM x1 WHERE a=2
  } {2 two ii text}
  do_execsql_test 2.1.$tn.2 {
    SELECT *, typeof(a) FROM x1 WHERE a=2.0
  } {2.0 twopointoh ii.0 text}
  do_execsql_test 2.1.$tn.3 {
    SELECT *, typeof(a) FROM x1 WHERE a='2'
  } {2 two ii text}
  do_execsql_test 2.1.$tn.4 {
    SELECT *, typeof(a) FROM x1 WHERE a='2.0'
  } {2.0 twopointoh ii.0 text}

  do_execsql_test 2.1.$tn.5 {
    SELECT *, typeof(a) FROM x2 WHERE a=2
  } {2 two ii integer 2 twopointoh ii.0 integer}
  do_execsql_test 2.1.$tn.6 {
    SELECT *, typeof(a) FROM x2 WHERE a=2.0
  } {2 two ii integer 2 twopointoh ii.0 integer}
  do_execsql_test 2.1.$tn.7 {
    SELECT *, typeof(a) FROM x2 WHERE a='2'
  } {2 two ii integer 2 twopointoh ii.0 integer}
  do_execsql_test 2.1.$tn.8 {
    SELECT *, typeof(a) FROM x2 WHERE a='2.0'
  } {2 two ii integer 2 twopointoh ii.0 integer}

  do_execsql_test 2.1.$tn.9 {
    SELECT *, typeof(a) FROM x3 WHERE a=2
  } {2.0 two ii real 2.0 twopointoh ii.0 real}
  do_execsql_test 2.1.$tn.10 {
    SELECT *, typeof(a) FROM x3 WHERE a=2.0
  } {2.0 two ii real 2.0 twopointoh ii.0 real}
  do_execsql_test 2.1.$tn.11 {
    SELECT *, typeof(a) FROM x3 WHERE a='2'
  } {2.0 two ii real 2.0 twopointoh ii.0 real}
  do_execsql_test 2.1.$tn.12 {
    SELECT *, typeof(a) FROM x3 WHERE a='2.0'
  } {2.0 two ii real 2.0 twopointoh ii.0 real}

}

reset_db
do_execsql_test 3.0 {
  CREATE TABLE x1(a TEXT, d PRIMARY KEY, b, c) WITHOUT ROWID;
  INSERT INTO x1 VALUES('2', 1, 'two', 'ii');
  INSERT INTO x1 VALUES('2.0', 2, 'twopointoh', 'ii.0');

  CREATE TABLE x2(a NUMERIC, b, c, d PRIMARY KEY) WITHOUT ROWID;
  INSERT INTO x2 VALUES('2', 'two', 'ii', 1);
  INSERT INTO x2 VALUES('2.0', 'twopointoh', 'ii.0', 2);

  CREATE TABLE x3(d PRIMARY KEY, a REAL, b, c) WITHOUT ROWID;
  INSERT INTO x3 VALUES(34, '2', 'two', 'ii');
  INSERT INTO x3 VALUES(35, '2.0', 'twopointoh', 'ii.0');
}

foreach {tn idx} {
  0 {
  }
  1 {
    CREATE INDEX i1 ON x1(b, c) WHERE a=2;
    CREATE INDEX i2 ON x2(b, c) WHERE a=2;
    CREATE INDEX i3 ON x3(b, c) WHERE a=2;
  }
  2 {
    CREATE INDEX i1 ON x1(b, c) WHERE a=2.0;
    CREATE INDEX i2 ON x2(b, c) WHERE a=2.0;
    CREATE INDEX i3 ON x3(b, c) WHERE a=2.0;
  }
  3 {
    CREATE INDEX i1 ON x1(b, c) WHERE a='2.0';
    CREATE INDEX i2 ON x2(b, c) WHERE a='2.0';
    CREATE INDEX i3 ON x3(b, c) WHERE a='2.0';
  }
  4 {
    CREATE INDEX i1 ON x1(b, c) WHERE a='2';
    CREATE INDEX i2 ON x2(b, c) WHERE a='2';
    CREATE INDEX i3 ON x3(b, c) WHERE a='2';
  }
} {
  execsql { DROP INDEX IF EXISTS i1 }
  execsql { DROP INDEX IF EXISTS i2 }
  execsql { DROP INDEX IF EXISTS i3 }

  execsql $idx
  do_execsql_test 3.1.$tn.1 {
    SELECT a, b, c, typeof(a) FROM x1 WHERE a=2
  } {2 two ii text}
  do_execsql_test 3.1.$tn.2 {
    SELECT a, b, c, typeof(a) FROM x1 WHERE a=2.0
  } {2.0 twopointoh ii.0 text}
  do_execsql_test 3.1.$tn.3 {
    SELECT a, b, c, typeof(a) FROM x1 WHERE a='2'
  } {2 two ii text}
  do_execsql_test 3.1.$tn.4 {
    SELECT a, b, c, typeof(a) FROM x1 WHERE a='2.0'
  } {2.0 twopointoh ii.0 text}

  do_execsql_test 3.1.$tn.5 {
    SELECT a, b, c, typeof(a) FROM x2 WHERE a=2
  } {2 two ii integer 2 twopointoh ii.0 integer}
  do_execsql_test 3.1.$tn.6 {
    SELECT a, b, c, typeof(a) FROM x2 WHERE a=2.0
  } {2 two ii integer 2 twopointoh ii.0 integer}
  do_execsql_test 3.1.$tn.7 {
    SELECT a, b, c, typeof(a) FROM x2 WHERE a='2'
  } {2 two ii integer 2 twopointoh ii.0 integer}
  do_execsql_test 3.1.$tn.8 {
    SELECT a, b, c, typeof(a) FROM x2 WHERE a='2.0'
  } {2 two ii integer 2 twopointoh ii.0 integer}

  do_execsql_test 3.1.$tn.9 {
    SELECT a, b, c, typeof(a) FROM x3 WHERE a=2
  } {2.0 two ii real 2.0 twopointoh ii.0 real}
  do_execsql_test 3.1.$tn.10 {
    SELECT a, b, c, typeof(a) FROM x3 WHERE a=2.0
  } {2.0 two ii real 2.0 twopointoh ii.0 real}
  do_execsql_test 3.1.$tn.11 {
    SELECT a, b, c, typeof(a) FROM x3 WHERE a='2'
  } {2.0 two ii real 2.0 twopointoh ii.0 real}
  do_execsql_test 3.1.$tn.12 {
    SELECT a, b, c, typeof(a) FROM x3 WHERE a='2.0'
  } {2.0 two ii real 2.0 twopointoh ii.0 real}
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 4.0 {
  CREATE TABLE t2(a INTEGER, b TEXT);
  INSERT INTO t2 VALUES(1, 'two');
  INSERT INTO t2 VALUES(2, 'two');
  INSERT INTO t2 VALUES(3, 'two');
  INSERT INTO t2 VALUES(1, 'three');
  INSERT INTO t2 VALUES(2, 'three');
  INSERT INTO t2 VALUES(3, 'three');

  CREATE INDEX t2a_two ON t2(a) WHERE b='two';
}

# explain_i { SELECT sum(a), b FROM t2 WHERE b='two' }
do_execsql_test 4.1.1 {
  SELECT sum(a), b FROM t2 WHERE b='two'
} {6 two}
do_eqp_test 4.1.2 {
  SELECT sum(a), b FROM t2 WHERE b='two'
} {USING COVERING INDEX t2a_two}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 5.0 {
  CREATE TABLE t1(a INTEGER PRIMQRY KEY, b, c);
}
do_catchsql_test 5.1 {
  CREATE INDEX ex1 ON t1(c) WHERE b IS 'abc' COLLATE g;
} {1 {no such collation sequence: g}}

proc xyz {lhs rhs} {
  return [string compare $lhs $rhs]
}
db collate xyz xyz
do_execsql_test 5.2 {
  CREATE INDEX ex1 ON t1(c) WHERE b IS 'abc' COLLATE xyz;
}
db close
sqlite3 db test.db
do_execsql_test 5.3 {
  SELECT * FROM t1
}

#-------------------------------------------------------------------------
reset_db
do_execsql_test 6.0 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
  CREATE TABLE t2(x INTEGER PRIMARY KEY, y INTEGER, z INTEGER);
  INSERT INTO t1 VALUES(1, 1, 1);
  INSERT INTO t1 VALUES(2, 1, 2);
  INSERT INTO t2 VALUES(1, 5, 1);
  INSERT INTO t2 VALUES(2, 5, 2);

  CREATE INDEX t2z ON t2(z) WHERE y=5;
}

do_execsql_test 6.1 {
  ANALYZE;
  UPDATE sqlite_stat1 SET stat = '50 1' WHERE idx='t2z';
  UPDATE sqlite_stat1 SET stat = '50' WHERE tbl='t2' AND idx IS NULL;
  UPDATE sqlite_stat1 SET stat = '5000' WHERE tbl='t1' AND idx IS NULL;
  ANALYZE sqlite_schema;
}

do_execsql_test 6.2 {
  SELECT * FROM t1, t2 WHERE b=1 AND z=c AND y=5;
} {
  1 1 1  1 5 1
  2 1 2  2 5 2
}

do_eqp_test 6.3 {
  SELECT * FROM t1, t2 WHERE b=1 AND z=c AND y=5;
} {BLOOM FILTER ON t2}

do_execsql_test 6.4 {
  SELECT * FROM t1 LEFT JOIN t2 ON (y=5) WHERE b=1 AND z IS c;
} {
  1 1 1  1 5 1
  2 1 2  2 5 2
}

do_eqp_test 6.5 {
  SELECT * FROM t1 LEFT JOIN t2 ON (y=5) WHERE b=1 AND z IS c;
} {BLOOM FILTER ON t2}

do_execsql_test 6.6 {
  CREATE INDEX t2yz ON t2(y, z) WHERE y=5;
}

do_execsql_test 6.7 {
  SELECT * FROM t1 LEFT JOIN t2 ON (y=5) WHERE b=1 AND z IS c;
} {
  1 1 1  1 5 1
  2 1 2  2 5 2
}

finish_test