sqlite/test/vt02.c

/*
** This file implements an eponymous, read-only table-valued function
** (a virtual table) designed to be used for testing.  We are not aware
** of any practical real-world use case for the virtual table.
**
** This virtual table originated in the TH3 test suite.  It is still used
** there, but has now been copied into the public SQLite source tree and
** reused for a variety of testing purpose.  The name "vt02" comes from the
** fact that there are many different testing virtual tables in TH3, of which
** this one is the second.
**
** ## SUBJECT TO CHANGE
**
** Because this virtual table is intended for testing, its interface is not
** guaranteed to be stable across releases.  Future releases may contain
** changes in the vt02 design and interface.
**
** ## OVERVIEW
**
** The vt02 table-valued function has 10000 rows with 5 data columns.
** Column X contains all integer values between 0 and 9999 inclusive.
** Columns A, B, C, and D contain the individual base-10 digits associated
** with each X value:
**
**      X     A  B  C  D
**      ----  -  -  -  -
**      0     0  0  0  0
**      1     0  0  0  1
**      2     0  0  0  2
**              ...
**      4998  4  9  9  8
**      4999  4  9  9  9
**      5000  5  0  0  0
**              ...
**      9995  9  9  9  5
**      9996  9  9  9  6
**      9997  9  9  9  7
**
** The xBestIndex method recognizes a variety of equality constraints
** and attempts to optimize its output accordingly.
**
**      x=...
**      a=...
**      a=... AND b=...
**      a=... AND b=... AND c=...
**      a=... AND b=... AND c=... AND d=...
**
** Various ORDER BY constraints are also recognized and consumed.  The
** OFFSET constraint is recognized and consumed.
**
** ## TABLE-VALUED FUNCTION
**
** The vt02 virtual table is eponymous and has two hidden columns, meaning
** that it can functions a table-valued function.  The two hidden columns
** are "flags" and "logtab", in that order.  The "flags" column can be set
** to an integer where various bits enable or disable behaviors of the
** virtual table.  The "logtab" can set to the name of an ordinary SQLite
** table into which is written information about each call to xBestIndex.
**
** The bits of "flags" are as follows:
**
**       0x01           Ignore the aConstraint[].usable flag.  This might
**                      result in the xBestIndex method incorrectly using
**                      unusable entries in the aConstraint[] array, which
**                      should result in the SQLite core detecting and
**                      reporting that the virtual table is not behaving
**                      to spec.
**
**       0x02           Do not set the orderByConsumed flag, even if it
**                      could be set.
**
**       0x04           Do not consume the OFFSET constraint, if there is
**                      one.  Instead, let the generated byte-code visit
**                      and ignore the first few columns of output.
**
**       0x08           Use sqlite3_mprintf() to allocate an idxStr string.
**                      The string is never used, but allocating it does
**                      test the idxStr deallocation logic inside of the
**                      SQLite core.
**
**       0x10           Cause the xBestIndex method to generate an idxNum
**                      that xFilter does not understand, thus causing
**                      the OP_VFilter opcode to raise an error.
**
**       0x20           Set the omit flag for all equality constraints on
**                      columns X, A, B, C, and D that are used to limit
**                      the search.
**
**       0x40           Add all constraints against X,A,B,C,D to the
**                      vector of results sent to xFilter.  Only the first
**                      few are used, as required by idxNum.
**
** Because these flags take effect during xBestIndex, the RHS of the
** flag= constraint must be accessible.  In other words, the RHS of flag=
** needs to be an integer literal, not another column of a join or a
** bound parameter.
**
** ## LOGGING OUTPUT
**
** If the "logtab" columns is set, then each call to the xBestIndex method
** inserts multiple rows into the table identified by "logtab".  These
** rows collectively show the content of the sqlite3_index_info object and
** other context associated with the xBestIndex call.
**
** If the table named by "logtab" does not previously exist, it is created
** automatically.  The schema for the logtab table is like this:
**
**   CREATE TEMP TABLE vt02_log(
**     bi INT,         -- BestIndex call counter
**     vn TEXT,        -- Variable Name
**     ix INT,         -- Index or value
**     cn TEXT,        -- Column Name
**     op INT,         -- Opcode or "DESC" value
**     ux INT,         -- "Usable" flag
**     ra BOOLEAN,     -- Right-hand side Available.
**     rhs ANY,        -- Right-Hand Side value
**     cs TEXT         -- Collating Sequence for this constraint
**  );
**
** Because logging happens during xBestIindex, the RHS value of "logtab" must
** be known to xBestIndex, which means it must be a string literal, not a
** column in a join, or a bound parameter.
** 
** ## VIRTUAL TABLE SCHEMA
**
**    CREATE TABLE vt02(
**      x INT,              -- integer between 0 and 9999 inclusive
**      a INT,              -- The 1000s digit
**      b INT,              -- The 100s digit
**      c INT,              -- The 10s digit
**      d INT,              -- The 1s digit
**      flags INT HIDDEN,   -- Option flags
**      logtab TEXT HIDDEN, -- Name of table into which to log xBestIndex
**    );
**
** ## COMPILING AND RUNNING
**
** This file can also be compiled separately as a loadable extension
** for SQLite (as long as the -DTH3_VERSION is not defined).  To compile as a
** loadable extension do his:
**
**    gcc -Wall -g -shared -fPIC -I. -DSQLITE_DEBUG vt02.c -o vt02.so
**
** Or on Windows:
**
**    cl vt02.c -link -dll -out:vt02.dll
**
** Then load into the CLI using:
**
**    .load ./vt02 sqlite3_vt02_init
**
** ## IDXNUM SUMMARY
**
** The xBestIndex method communicates the query plan to xFilter using
** the idxNum value, as follows:
**
**     0           unconstrained
**     1           X=argv[0]
**     2           A=argv[0]
**     3           A=argv[0], B=argv[1]
**     4           A=argv[0], B=argv[1], C=argv[2]
**     5           A=argv[0], B=argv[1], C=argv[2], D=argv[3]
**     6           A=argv[0], D IN argv[2]
**     7           A=argv[0], B=argv[2], D IN argv[3]
**     8           A=argv[0], B=argv[2], C=argv[3], D IN argv[4]
**    1x           increment by 10
**    2x           increment by 100
**    3x           increment by 1000
**   1xx           Use offset provided by argv[N]
*/
#ifndef TH3_VERSION
  /* These bits for separate compilation as a loadable extension, only */
  #include "sqlite3ext.h"
  SQLITE_EXTENSION_INIT1
  #include <stdlib.h>
  #include <string.h>
  #include <assert.h>
#endif

/* Forward declarations */
typedef struct vt02_vtab vt02_vtab;
typedef struct vt02_cur vt02_cur;

/*
** The complete virtual table
*/
struct vt02_vtab {
  sqlite3_vtab parent;        /* Base clase.  Must be first. */
  sqlite3 *db;                /* Database connection */
  int busy;                   /* Currently running xBestIndex */
};

#define VT02_IGNORE_USABLE  0x0001  /* Ignore usable flags */
#define VT02_NO_SORT_OPT    0x0002  /* Do not do any sorting optimizations */
#define VT02_NO_OFFSET      0x0004  /* Omit the offset optimization */
#define VT02_ALLOC_IDXSTR   0x0008  /* Alloate an idxStr */
#define VT02_BAD_IDXNUM     0x0010  /* Generate an invalid idxNum */

/*
** A cursor
*/
struct vt02_cur {
  sqlite3_vtab_cursor parent; /* Base class.  Must be first */
  sqlite3_int64 i;            /* Current entry */
  sqlite3_int64 iEof;         /* Indicate EOF when reaching this value */
  int iIncr;                  /* Amount by which to increment */
  unsigned int mD;            /* Mask of allowed D-column values */
};

/* The xConnect method */
int vt02Connect(
  sqlite3 *db,                /* The database connection */
  void *pAux,                 /* Pointer to an alternative schema */
  int argc,                   /* Number of arguments */
  const char *const*argv,     /* Text of the arguments */
  sqlite3_vtab **ppVTab,      /* Write the new vtab here */
  char **pzErr                /* Error message written here */
){
  vt02_vtab *pVtab;
  int rc;
  const char *zSchema = (const char*)pAux;
  static const char zDefaultSchema[] = 
    "CREATE TABLE x(x INT, a INT, b INT, c INT, d INT,"
    " flags INT HIDDEN, logtab TEXT HIDDEN);";
#define VT02_COL_X       0
#define VT02_COL_A       1
#define VT02_COL_B       2
#define VT02_COL_C       3
#define VT02_COL_D       4
#define VT02_COL_FLAGS   5
#define VT02_COL_LOGTAB  6
#define VT02_COL_NONE    7

  pVtab = sqlite3_malloc( sizeof(*pVtab) );
  if( pVtab==0 ){
    *pzErr = sqlite3_mprintf("out of memory");
    return SQLITE_NOMEM;
  }
  memset(pVtab, 0, sizeof(*pVtab));
  pVtab->db = db;
  rc = sqlite3_declare_vtab(db, zSchema ? zSchema : zDefaultSchema);
  if( rc ){
    sqlite3_free(pVtab);
  }else{
    *ppVTab = &pVtab->parent;
  }
  return rc;
}

/* the xDisconnect method
*/
int vt02Disconnect(sqlite3_vtab *pVTab){
  sqlite3_free(pVTab);
  return SQLITE_OK;
}

/* Put an error message into the zErrMsg string of the virtual table.
*/
static void vt02ErrMsg(sqlite3_vtab *pVtab, const char *zFormat, ...){
  va_list ap;
  sqlite3_free(pVtab->zErrMsg);
  va_start(ap, zFormat);
  pVtab->zErrMsg = sqlite3_vmprintf(zFormat, ap);
  va_end(ap);
}


/* Open a cursor for scanning
*/
static int vt02Open(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor){
  vt02_cur *pCur;
  pCur = sqlite3_malloc( sizeof(*pCur) );
  if( pCur==0 ){
    vt02ErrMsg(pVTab, "out of memory");
    return SQLITE_NOMEM;
  }
  *ppCursor = &pCur->parent;
  pCur->i = -1;
  return SQLITE_OK;
}

/* Close a cursor
*/
static int vt02Close(sqlite3_vtab_cursor *pCursor){
  vt02_cur *pCur = (vt02_cur*)pCursor;
  sqlite3_free(pCur);
  return SQLITE_OK;
}

/* Return TRUE if we are at the end of the BVS and there are
** no more entries.
*/
static int vt02Eof(sqlite3_vtab_cursor *pCursor){
  vt02_cur *pCur = (vt02_cur*)pCursor;
  return pCur->i<0 || pCur->i>=pCur->iEof;
}

/* Advance the cursor to the next row in the table
*/
static int vt02Next(sqlite3_vtab_cursor *pCursor){
  vt02_cur *pCur = (vt02_cur*)pCursor;
  do{
    pCur->i += pCur->iIncr;
    if( pCur->i<0 ) pCur->i = pCur->iEof;
  }while( (pCur->mD & (1<<(pCur->i%10)))==0 && pCur->i<pCur->iEof );
  return SQLITE_OK;
}

/* Rewind a cursor back to the beginning of its scan.
**
** Scanning is always increasing.
**
**   idxNum
**     0           unconstrained
**     1           X=argv[0]
**     2           A=argv[0]
**     3           A=argv[0], B=argv[1]
**     4           A=argv[0], B=argv[1], C=argv[2]
**     5           A=argv[0], B=argv[1], C=argv[2], D=argv[3]
**     6           A=argv[0], D IN argv[2]
**     7           A=argv[0], B=argv[2], D IN argv[3]
**     8           A=argv[0], B=argv[2], C=argv[3], D IN argv[4]
**    1x           increment by 10
**    2x           increment by 100
**    3x           increment by 1000
**   1xx           Use offset provided by argv[N]
*/
static int vt02Filter(
  sqlite3_vtab_cursor *pCursor, /* The cursor to rewind */
  int idxNum,                   /* Search strategy */
  const char *idxStr,           /* Not used */
  int argc,                     /* Not used */
  sqlite3_value **argv          /* Not used */
){
  vt02_cur *pCur = (vt02_cur*)pCursor; /* The vt02 cursor */
  int bUseOffset = 0;                  /* True to use OFFSET value */
  int iArg = 0;                        /* argv[] values used so far */
  int iOrigIdxNum = idxNum;            /* Original value for idxNum */

  pCur->iIncr = 1;
  pCur->mD = 0x3ff;
  if( idxNum>=100 ){
    bUseOffset = 1;
    idxNum -= 100;
  }
  if( idxNum<0 || idxNum>38 ) goto vt02_bad_idxnum;
  while( idxNum>=10 ){
    pCur->iIncr *= 10;
    idxNum -= 10;
  }
  if( idxNum==0 ){
    pCur->i = 0;
    pCur->iEof = 10000;
  }else if( idxNum==1 ){
    pCur->i = sqlite3_value_int64(argv[0]);
    if( pCur->i<0 ) pCur->i = -1;
    if( pCur->i>9999 ) pCur->i = 10000;
    pCur->iEof = pCur->i+1;
    if( pCur->i<0 || pCur->i>9999 ) pCur->i = pCur->iEof;
  }else if( idxNum>=2 && idxNum<=5 ){
    int i, e, m;
    e = idxNum - 2;
    assert( e<=argc-1 );
    pCur->i = 0;
    for(m=1000, i=0; i<=e; i++, m /= 10){
      sqlite3_int64 v = sqlite3_value_int64(argv[iArg++]);
      if( v<0 ) v = 0;
      if( v>9 ) v = 9;
      pCur->i += m*v;
      pCur->iEof = pCur->i+m;
    }
  }else if( idxNum>=6 && idxNum<=8 ){
    int i, e, m, rc;
    sqlite3_value *pIn, *pVal;
    e = idxNum - 6;
    assert( e<=argc-2 );
    pCur->i = 0;
    for(m=1000, i=0; i<=e; i++, m /= 10){
      sqlite3_int64 v;
      pVal = 0;
      if( sqlite3_vtab_in_first(0, &pVal)!=SQLITE_MISUSE
       || sqlite3_vtab_in_first(argv[iArg], &pVal)!=SQLITE_ERROR
      ){
        vt02ErrMsg(pCursor->pVtab, 
                "unexpected success from sqlite3_vtab_in_first()");
        return SQLITE_ERROR;
      }
      v = sqlite3_value_int64(argv[iArg++]);
      if( v<0 ) v = 0;
      if( v>9 ) v = 9;
      pCur->i += m*v;
      pCur->iEof = pCur->i+m;
    }
    pCur->mD = 0;
    pIn = argv[iArg++];
    assert( sqlite3_value_type(pIn)==SQLITE_NULL );
    for( rc = sqlite3_vtab_in_first(pIn, &pVal);
         rc==SQLITE_OK && pVal!=0;
         rc = sqlite3_vtab_in_next(pIn, &pVal)
    ){
      int eType = sqlite3_value_numeric_type(pVal);
      if( eType==SQLITE_FLOAT ){
        double r = sqlite3_value_double(pVal);
        if( r<0.0 || r>9.0 || r!=(int)r ) continue;
      }else if( eType!=SQLITE_INTEGER ){
        continue;
      }
      i = sqlite3_value_int(pVal);
      if( i<0 || i>9 ) continue;
      pCur->mD |= 1<<i;
    }
    if( rc!=SQLITE_OK && rc!=SQLITE_DONE ){
      vt02ErrMsg(pCursor->pVtab, "Error from sqlite3_vtab_in_first/next()");
      return rc;
    }
  }else{
    goto vt02_bad_idxnum;
  }
  if( bUseOffset ){
    int nSkip = sqlite3_value_int(argv[iArg]);
    while( nSkip-- > 0 && pCur->i<pCur->iEof ) vt02Next(pCursor);
  }
  return SQLITE_OK;

vt02_bad_idxnum:
  vt02ErrMsg(pCursor->pVtab, "invalid idxNum for vt02: %d", iOrigIdxNum);
  return SQLITE_ERROR;
}

/* Return the Nth column of the current row.
*/
static int vt02Column(
  sqlite3_vtab_cursor *pCursor,
  sqlite3_context *context,
  int N
){
  vt02_cur *pCur = (vt02_cur*)pCursor;
  int v = pCur->i;
  if( N==VT02_COL_X ){
    sqlite3_result_int(context, v);
  }else if( N>=VT02_COL_A && N<=VT02_COL_D ){
    static const int iDivisor[] = { 1, 1000, 100, 10, 1 };
    v = (v/iDivisor[N])%10;
    sqlite3_result_int(context, v);
  }
  return SQLITE_OK;
}

/* Return the rowid of the current row
*/
static int vt02Rowid(sqlite3_vtab_cursor *pCursor, sqlite3_int64 *pRowid){
  vt02_cur *pCur = (vt02_cur*)pCursor;
  *pRowid = pCur->i+1;
  return SQLITE_OK;
}

/*************************************************************************
** Logging Subsystem
**
** The sqlite3BestIndexLog() routine implements a logging system for
** xBestIndex calls.  This code is portable to any virtual table.
**
** sqlite3BestIndexLog() is the main routine,  sqlite3RunSql() is a
** helper routine used for running various SQL statements as part of
** creating the log.
**
** These two routines should be portable to other virtual tables.  Simply
** extract this code and call sqlite3BestIndexLog() near the end of the
** xBestIndex method in cases where logging is desired.
*/
/*
** Run SQL on behalf of sqlite3BestIndexLog.
**
** Construct the SQL using the zFormat string and subsequent arguments.
** Or if zFormat is NULL, take the SQL as the first argument after the
** zFormat.  In either case, the dynamically allocated SQL string is
** freed after it has been run.  If something goes wrong with the SQL,
** then an error is left in pVTab->zErrMsg.
*/
static void sqlite3RunSql(
  sqlite3 *db,               /* Run the SQL on this database connection */
  sqlite3_vtab *pVTab,       /* Report errors to this virtual table */
  const char *zFormat,       /* Format string for SQL, or NULL */
  ...                        /* Arguments, according to the format string */
){
  char *zSql;

  va_list ap;
  va_start(ap, zFormat);
  if( zFormat==0 ){
    zSql = va_arg(ap, char*);
  }else{
    zSql = sqlite3_vmprintf(zFormat, ap);
  }
  va_end(ap);
  if( zSql ){
    char *zErrMsg = 0;
    (void)sqlite3_exec(db, zSql, 0, 0, &zErrMsg);
    if( zErrMsg ){
      if( pVTab->zErrMsg==0 ){
        pVTab->zErrMsg = sqlite3_mprintf("%s in [%s]", zErrMsg, zSql);
      }
      sqlite3_free(zErrMsg);
    }
    sqlite3_free(zSql);
  }
}

/*
** Record information about each xBestIndex method call in a separate
** table:
**
**   CREATE TEMP TABLE [log-table-name] (
**     bi INT,      -- BestIndex call number
**     vn TEXT,     -- Variable Name
**     ix INT,      -- Index or value
**     cn TEXT,     -- Column Name
**     op INT,      -- Opcode or argvIndex
**     ux INT,      -- "usable" or "omit" flag
**     rx BOOLEAN,  -- True if has a RHS value
**     rhs ANY,     -- The RHS value
**     cs TEXT,     -- Collating Sequence
**     inop BOOLEAN -- True if this is a batchable IN operator
**  );
**
** If an error occurs, leave an error message in pVTab->zErrMsg.
*/
static void sqlite3BestIndexLog(
  sqlite3_index_info *pInfo,  /* The sqlite3_index_info object */
  const char *zLogTab,        /* Log into this table */
  sqlite3 *db,                /* Database connection containing zLogTab */
  const char **azColname,     /* Names of columns in the virtual table */
  sqlite3_vtab *pVTab         /* Record errors into this object */
){
  int i, rc;
  sqlite3_str *pStr;
  int iBI;

  if( sqlite3_table_column_metadata(db,0,zLogTab,0,0,0,0,0,0) ){
    /* The log table does not previously exist.  Create it. */
    sqlite3RunSql(db,pVTab, 
      "CREATE TABLE IF NOT EXISTS temp.\"%w\"(\n"
      " bi INT,          -- BestIndex call number\n"
      " vn TEXT,         -- Variable Name\n"
      " ix INT,          -- Index or value\n"
      " cn TEXT,         -- Column Name\n"
      " op INT,          -- Opcode or argvIndex\n"
      " ux INT,          -- usable for omit flag\n"
      " rx BOOLEAN,      -- Right-hand side value is available\n"
      " rhs ANY,         -- RHS value\n"
      " cs TEXT,         -- Collating Sequence\n"
      " inop BOOLEAN     -- IN operator capable of batch reads\n"
      ");", zLogTab
    );
    iBI = 1;
  }else{
    /* The log table does already exist.  We assume that it has the
    ** correct schema and proceed to find the largest prior "bi" value.
    ** If the schema is wrong, errors might result.  The code is able
    ** to deal with this. */
    sqlite3_stmt *pStmt;
    char *zSql;
    zSql = sqlite3_mprintf("SELECT max(bi) FROM temp.\"%w\"",zLogTab);
    if( zSql==0 ){
      sqlite3_free(pVTab->zErrMsg);
      pVTab->zErrMsg = sqlite3_mprintf("out of memory");
      return;
    }
    rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
    sqlite3_free(zSql);
    if( rc ){
      sqlite3_free(pVTab->zErrMsg);
      pVTab->zErrMsg = sqlite3_mprintf("%s", sqlite3_errmsg(db));
      iBI = 0;
    }else if( sqlite3_step(pStmt)==SQLITE_ROW ){
      iBI = sqlite3_column_int(pStmt, 0)+1;
    }else{
      iBI = 1;
    }
    sqlite3_finalize(pStmt);
  }
  sqlite3RunSql(db,pVTab,
    "INSERT INTO temp.\"%w\"(bi,vn,ix) VALUES(%d,'nConstraint',%d)"
      "RETURNING iif(bi=%d,'ok',RAISE(ABORT,'wrong trigger'))",
    /* The RETURNING clause checks to see that the returning trigger fired
    ** for the correct INSERT in the case of nested INSERT RETURNINGs. */
    zLogTab, iBI, pInfo->nConstraint, iBI
  );
  for(i=0; i<pInfo->nConstraint; i++){
    sqlite3_value *pVal;
    char *zSql;
    int iCol = pInfo->aConstraint[i].iColumn;
    int op = pInfo->aConstraint[i].op;
    const char *zCol;
    if( op==SQLITE_INDEX_CONSTRAINT_LIMIT
     || op==SQLITE_INDEX_CONSTRAINT_OFFSET
    ){
      zCol = "";
    }else if( iCol<0 ){
      zCol = "rowid";
    }else{
      zCol = azColname[iCol];
    }
    pStr = sqlite3_str_new(0);
    sqlite3_str_appendf(pStr,
       "INSERT INTO temp.\"%w\"(bi,vn,ix,cn,op,ux,rx,rhs,cs,inop)"
       "VALUES(%d,'aConstraint',%d,%Q,%d,%d",
       zLogTab, iBI,
       i,
       zCol,
       op,
       pInfo->aConstraint[i].usable);
    pVal = 0;
    rc = sqlite3_vtab_rhs_value(pInfo, i, &pVal);
    assert( pVal!=0 || rc!=SQLITE_OK );
    if( rc==SQLITE_OK ){
      sqlite3_str_appendf(pStr,",1,?1");
    }else{
      sqlite3_str_appendf(pStr,",0,NULL");
    }
    sqlite3_str_appendf(pStr,",%Q,%d)",
         sqlite3_vtab_collation(pInfo,i),
         sqlite3_vtab_in(pInfo,i,-1));
    zSql = sqlite3_str_finish(pStr);
    if( zSql==0 ){
      if( pVTab->zErrMsg==0 ) pVTab->zErrMsg = sqlite3_mprintf("out of memory");
    }else{
      sqlite3_stmt *pStmt = 0;
      rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
      if( rc ){
        if( pVTab->zErrMsg==0 ){
          pVTab->zErrMsg = sqlite3_mprintf("%s", sqlite3_errmsg(db));
        }
      }else{
        if( pVal ) sqlite3_bind_value(pStmt, 1, pVal);
        sqlite3_step(pStmt);
        rc = sqlite3_reset(pStmt);
        if( rc && pVTab->zErrMsg==0 ){
          pVTab->zErrMsg = sqlite3_mprintf("%s", sqlite3_errmsg(db));
        }
      }
      sqlite3_finalize(pStmt);
      sqlite3_free(zSql);
    }
  }
  sqlite3RunSql(db,pVTab,
    "INSERT INTO temp.\"%w\"(bi,vn,ix) VALUES(%d,'nOrderBy',%d)",
    zLogTab, iBI, pInfo->nOrderBy
  );
  for(i=0; i<pInfo->nOrderBy; i++){
    int iCol = pInfo->aOrderBy[i].iColumn;
    sqlite3RunSql(db,pVTab,
      "INSERT INTO temp.\"%w\"(bi,vn,ix,cn,op)VALUES(%d,'aOrderBy',%d,%Q,%d)",
      zLogTab, iBI,
      i,
      iCol>=0 ? azColname[iCol] : "rowid",
      pInfo->aOrderBy[i].desc
    );
  }
  sqlite3RunSql(db,pVTab,
    "INSERT INTO temp.\"%w\"(bi,vn,ix) VALUES(%d,'sqlite3_vtab_distinct',%d)",
    zLogTab, iBI, sqlite3_vtab_distinct(pInfo)
  );
  sqlite3RunSql(db,pVTab,
    "INSERT INTO temp.\"%w\"(bi,vn,ix) VALUES(%d,'colUsed',%lld)",
    zLogTab, iBI, pInfo->colUsed
  );
  for(i=0; i<pInfo->nConstraint; i++){
    int iCol = pInfo->aConstraint[i].iColumn;
    int op = pInfo->aConstraint[i].op;
    const char *zCol;
    if( op==SQLITE_INDEX_CONSTRAINT_LIMIT
     || op==SQLITE_INDEX_CONSTRAINT_OFFSET
    ){
      zCol = "";
    }else if( iCol<0 ){
      zCol = "rowid";
    }else{
      zCol = azColname[iCol];
    }
    sqlite3RunSql(db,pVTab,
       "INSERT INTO temp.\"%w\"(bi,vn,ix,cn,op,ux)"
       "VALUES(%d,'aConstraintUsage',%d,%Q,%d,%d)",
       zLogTab, iBI,
       i,
       zCol,
       pInfo->aConstraintUsage[i].argvIndex,
       pInfo->aConstraintUsage[i].omit
    );
  }
  sqlite3RunSql(db,pVTab,
    "INSERT INTO temp.\"%w\"(bi,vn,ix)VALUES(%d,'idxNum',%d)",
    zLogTab, iBI, pInfo->idxNum
  );
  sqlite3RunSql(db,pVTab,
    "INSERT INTO temp.\"%w\"(bi,vn,ix)VALUES(%d,'estimatedCost',%f)",
    zLogTab, iBI, pInfo->estimatedCost
  );
  sqlite3RunSql(db,pVTab,
    "INSERT INTO temp.\"%w\"(bi,vn,ix)VALUES(%d,'estimatedRows',%lld)",
    zLogTab, iBI, pInfo->estimatedRows
  );
  if( pInfo->idxStr ){
    sqlite3RunSql(db,pVTab,
      "INSERT INTO temp.\"%w\"(bi,vn,ix)VALUES(%d,'idxStr',%Q)",
      zLogTab, iBI, pInfo->idxStr
    );
    sqlite3RunSql(db,pVTab,
      "INSERT INTO temp.\"%w\"(bi,vn,ix)VALUES(%d,'needToFreeIdxStr',%d)",
      zLogTab, iBI, pInfo->needToFreeIdxStr
    );
  }
  if( pInfo->nOrderBy ){
    sqlite3RunSql(db,pVTab,
      "INSERT INTO temp.\"%w\"(bi,vn,ix)VALUES(%d,'orderByConsumed',%d)",
      zLogTab, iBI, pInfo->orderByConsumed
    );
  }
}
/*
** End of Logging Subsystem
*****************************************************************************/


/* Find an estimated cost of running a query against vt02.
*/
static int vt02BestIndex(sqlite3_vtab *pVTab, sqlite3_index_info *pInfo){
  int i;                      /* Loop counter */
  int isEq[5];                /* Equality constraints on X, A, B, C, and D */
  int isUsed[5];              /* Other non-== cosntraints X, A, B, C, and D */
  int argvIndex = 0;          /* Next available argv[] slot */
  int iOffset = -1;           /* Constraint for OFFSET */
  void *pX = 0;               /* idxStr value */
  int flags = 0;              /* RHS value for flags= */
  const char *zLogTab = 0;    /* RHS value for logtab= */
  int iFlagTerm = -1;         /* Constraint term for flags= */
  int iLogTerm = -1;          /* Constraint term for logtab= */
  int iIn = -1;               /* Index of the IN constraint */
  vt02_vtab *pSelf;           /* This virtual table */

  pSelf = (vt02_vtab*)pVTab;
  if( pSelf->busy ){
    vt02ErrMsg(pVTab, "recursive use  of vt02 prohibited");
    return SQLITE_CONSTRAINT;
  }
  pSelf->busy++;
 

  /* Do an initial scan for flags=N and logtab=TAB constraints with
  ** usable RHS values */
  for(i=0; i<pInfo->nConstraint; i++){
    sqlite3_value *pVal;
    if( !pInfo->aConstraint[i].usable ) continue;
    if( pInfo->aConstraint[i].op!=SQLITE_INDEX_CONSTRAINT_EQ ) continue;
    switch( pInfo->aConstraint[i].iColumn ){
      case VT02_COL_FLAGS:
        if( sqlite3_vtab_rhs_value(pInfo, i, &pVal)==SQLITE_OK
         && sqlite3_value_type(pVal)==SQLITE_INTEGER
        ){
          flags = sqlite3_value_int(pVal);
        }
        iFlagTerm = i;
        break;
      case VT02_COL_LOGTAB:
        if( sqlite3_vtab_rhs_value(pInfo, i, &pVal)==SQLITE_OK
         && sqlite3_value_type(pVal)==SQLITE_TEXT
        ){
          zLogTab = (const char*)sqlite3_value_text(pVal);
        }
        iLogTerm = i;
        break;
    }
  }

  /* Do a second scan to actually analyze the index information */
  memset(isEq, 0xff, sizeof(isEq));
  memset(isUsed, 0xff, sizeof(isUsed));
  for(i=0; i<pInfo->nConstraint; i++){
    int j = pInfo->aConstraint[i].iColumn;
    if( j>=VT02_COL_FLAGS ) continue;
    if( pInfo->aConstraint[i].usable==0
     && (flags & VT02_IGNORE_USABLE)==0 ) continue;
    if( j<0 ) j = VT02_COL_X;
    switch( pInfo->aConstraint[i].op ){
      case SQLITE_INDEX_CONSTRAINT_FUNCTION:
      case SQLITE_INDEX_CONSTRAINT_EQ:
        isEq[j] = i;
        break;
      case SQLITE_INDEX_CONSTRAINT_LT:
      case SQLITE_INDEX_CONSTRAINT_LE:
      case SQLITE_INDEX_CONSTRAINT_GT:
      case SQLITE_INDEX_CONSTRAINT_GE:
        isUsed[j] = i;
        break;
      case SQLITE_INDEX_CONSTRAINT_OFFSET:
        iOffset = i;
        break;
    }
  }

  /* Use the analysis to find an appropriate query plan */
  if( isEq[0]>=0 ){
    /* A constraint of X= takes priority */
    pInfo->estimatedCost = 1;
    pInfo->aConstraintUsage[isEq[0]].argvIndex = ++argvIndex;
    if( flags & 0x20 ) pInfo->aConstraintUsage[isEq[0]].omit = 1;
    pInfo->idxNum = 1;
  }else if( isEq[1]<0 ){
    /* If there is no X= nor A= then we have to do a full scan */
    pInfo->idxNum = 0;
    pInfo->estimatedCost = 10000;
  }else{
    int v = 1000;
    pInfo->aConstraintUsage[isEq[1]].argvIndex = ++argvIndex;
    if( flags & 0x20 ) pInfo->aConstraintUsage[isEq[1]].omit = 1;
    for(i=2; i<=4 && isEq[i]>=0; i++){
      if( i==4 && sqlite3_vtab_in(pInfo, isEq[4], 0) ) break;
      pInfo->aConstraintUsage[isEq[i]].argvIndex = ++argvIndex;
      if( flags & 0x20 ) pInfo->aConstraintUsage[isEq[i]].omit = 1;
      v /= 10;
    }
    pInfo->idxNum = i;
    if( isEq[4]>=0 && sqlite3_vtab_in(pInfo,isEq[4],1) ){
      iIn = isEq[4];
      pInfo->aConstraintUsage[iIn].argvIndex = ++argvIndex;
      if( flags & 0x20 ) pInfo->aConstraintUsage[iIn].omit = 1;
      v /= 5;
      i++;
      pInfo->idxNum += 4;
    }
    pInfo->estimatedCost = v;
  }
  pInfo->estimatedRows = (sqlite3_int64)pInfo->estimatedCost;

  /* Attempt to consume the ORDER BY clause.  Except, always leave
  ** orderByConsumed set to 0 for vt02_no_sort_opt.  In this way,
  ** we can compare vt02 and vt02_no_sort_opt to ensure they get
  ** the same answer.
  */
  if( pInfo->nOrderBy>0 && (flags & VT02_NO_SORT_OPT)==0 ){
    if( pInfo->idxNum==1 ){
      /* There will only be one row of output.  So it is always sorted. */
      pInfo->orderByConsumed = 1;
    }else
    if( pInfo->aOrderBy[0].iColumn<=0 
     && pInfo->aOrderBy[0].desc==0
    ){
      /* First column of order by is X ascending */
      pInfo->orderByConsumed = 1;
    }else
    if( sqlite3_vtab_distinct(pInfo)>=1 ){
      unsigned int x = 0;
      for(i=0; i<pInfo->nOrderBy; i++){
        int iCol = pInfo->aOrderBy[i].iColumn;
        if( iCol<0 ) iCol = 0;
        x |= 1<<iCol;
      }
      if( sqlite3_vtab_distinct(pInfo)==2 ){
        if( x==0x02 ){
          /* DISTINCT A */
          pInfo->idxNum += 30;
          pInfo->orderByConsumed = 1;
        }else if( x==0x06 ){
          /* DISTINCT A,B */
          pInfo->idxNum += 20;
          pInfo->orderByConsumed = 1;
        }else if( x==0x0e ){
          /* DISTINCT A,B,C */
          pInfo->idxNum += 10;
          pInfo->orderByConsumed = 1;
        }else if( x & 0x01 ){
          /* DISTINCT X */
          pInfo->orderByConsumed = 1;
        }else if( x==0x1e ){
          /* DISTINCT A,B,C,D */
          pInfo->orderByConsumed = 1;
        }
      }else{
        if( x==0x02 ){
          /* GROUP BY A */
          pInfo->orderByConsumed = 1;
        }else if( x==0x06 ){
          /* GROUP BY A,B */
          pInfo->orderByConsumed = 1;
        }else if( x==0x0e ){
          /* GROUP BY A,B,C */
          pInfo->orderByConsumed = 1;
        }else if( x & 0x01 ){
          /* GROUP BY X */
          pInfo->orderByConsumed = 1;
        }else if( x==0x1e ){
          /* GROUP BY A,B,C,D */
          pInfo->orderByConsumed = 1;
        }
      }
    }
  }

  if( flags & VT02_ALLOC_IDXSTR ){
    pInfo->idxStr = sqlite3_mprintf("test");
    pInfo->needToFreeIdxStr = 1;
  }
  if( flags & VT02_BAD_IDXNUM ){
    pInfo->idxNum += 1000;
  }

  if( iOffset>=0 ){
    pInfo->aConstraintUsage[iOffset].argvIndex = ++argvIndex;
    if( (flags & VT02_NO_OFFSET)==0
     && (pInfo->nOrderBy==0 || pInfo->orderByConsumed)
    ){
      pInfo->aConstraintUsage[iOffset].omit = 1;
      pInfo->idxNum += 100;
    }
  }


  /* Always omit flags= and logtab= constraints to prevent them from
  ** interfering with the bytecode.  Put them at the end of the argv[]
  ** array to keep them out of the way.
  */
  if( iFlagTerm>=0 ){
    pInfo->aConstraintUsage[iFlagTerm].omit = 1;
    pInfo->aConstraintUsage[iFlagTerm].argvIndex = ++argvIndex;
  }
  if( iLogTerm>=0 ){
    pInfo->aConstraintUsage[iLogTerm].omit = 1;
    pInfo->aConstraintUsage[iLogTerm].argvIndex = ++argvIndex;
  }

  /* The 0x40 flag means add all usable constraints to the output set */
  if( flags & 0x40 ){
    for(i=0; i<pInfo->nConstraint; i++){
      if( pInfo->aConstraint[i].usable
       && pInfo->aConstraintUsage[i].argvIndex==0
      ){
        pInfo->aConstraintUsage[i].argvIndex = ++argvIndex;
        if( flags & 0x20 )  pInfo->aConstraintUsage[i].omit = 1;
      }
    }
  }


  /* Generate the log if requested */
  if( zLogTab ){
    static const char *azColname[] = {
       "x", "a", "b", "c", "d", "flags", "logtab"
    };
    sqlite3 *db = ((vt02_vtab*)pVTab)->db;
    sqlite3BestIndexLog(pInfo, zLogTab, db, azColname, pVTab);
  }
  pSelf->busy--;

  /* Try to do a memory allocation solely for the purpose of causing
  ** an error under OOM testing loops */
  pX = sqlite3_malloc(800);
  if( pX==0 ) return SQLITE_NOMEM;
  sqlite3_free(pX);

  return pVTab->zErrMsg!=0 ? SQLITE_ERROR : SQLITE_OK;
}

/* This is the sqlite3_module definition for the the virtual table defined
** by this include file.
*/
const sqlite3_module vt02Module = {
  /* iVersion      */  2,
  /* xCreate       */  0,   /* This is an eponymous table */
  /* xConnect      */  vt02Connect,
  /* xBestIndex    */  vt02BestIndex,
  /* xDisconnect   */  vt02Disconnect,
  /* xDestroy      */  vt02Disconnect,
  /* xOpen         */  vt02Open,
  /* xClose        */  vt02Close,
  /* xFilter       */  vt02Filter,
  /* xNext         */  vt02Next,
  /* xEof          */  vt02Eof,
  /* xColumn       */  vt02Column,
  /* xRowid        */  vt02Rowid,
  /* xUpdate       */  0,
  /* xBegin        */  0, 
  /* xSync         */  0,
  /* xCommit       */  0, 
  /* xRollback     */  0,
  /* xFindFunction */  0,
  /* xRename       */  0,
  /* xSavepoint    */  0,
  /* xRelease      */  0,
  /* xRollbackTo   */  0,
  /* xShadowName   */  0,
  /* xIntegrity    */  0
};

static void vt02CoreInit(sqlite3 *db){
  static const char zPkXSchema[] = 
    "CREATE TABLE x(x INT NOT NULL PRIMARY KEY, a INT, b INT, c INT, d INT,"
    " flags INT HIDDEN, logtab TEXT HIDDEN);";
  static const char zPkABCDSchema[] = 
    "CREATE TABLE x(x INT, a INT NOT NULL, b INT NOT NULL, c INT NOT NULL, "
    "d INT NOT NULL, flags INT HIDDEN, logtab TEXT HIDDEN, "
    "PRIMARY KEY(a,b,c,d));";
  sqlite3_create_module(db, "vt02", &vt02Module, 0);
  sqlite3_create_module(db, "vt02pkx", &vt02Module, (void*)zPkXSchema);
  sqlite3_create_module(db, "vt02pkabcd", &vt02Module, (void*)zPkABCDSchema);
}

#ifdef TH3_VERSION
static void vt02_init(th3state *p, int iDb, char *zArg){
  vt02CoreInit(th3dbPointer(p, iDb));
}
#else
#ifdef _WIN32
__declspec(dllexport)
#endif
int sqlite3_vt02_init(
  sqlite3 *db, 
  char **pzErrMsg, 
  const sqlite3_api_routines *pApi
){
  SQLITE_EXTENSION_INIT2(pApi);
  vt02CoreInit(db);
  return SQLITE_OK;
}
#endif /* TH3_VERSION */