/ Check-in [6020d96a]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Handle equality and range constraints on the "term" column of fts5vocab tables. Also have the same module report columns using names instead of indexes.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 6020d96ab47a0bebe41f691a3f44fd55d8b2d2b7
User & Date: dan 2015-10-17 19:49:01
Context
2015-10-17
20:34
Add tests to cover new branches added to the fts5 code by the previous commit. check-in: cf618334 user: dan tags: trunk
19:49
Handle equality and range constraints on the "term" column of fts5vocab tables. Also have the same module report columns using names instead of indexes. check-in: 6020d96a user: dan tags: trunk
2015-10-16
23:55
Fix a comment in expr.c and add a CORRUPT_DB to an assert() in btree.c. check-in: 0df371d1 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/fts5/fts5Int.h.

   471    471     const char **azArg,
   472    472     int nArg,
   473    473     Fts5Tokenizer**,
   474    474     fts5_tokenizer**,
   475    475     char **pzErr
   476    476   );
   477    477   
   478         -Fts5Index *sqlite3Fts5IndexFromCsrid(Fts5Global*, i64, int*);
          478  +Fts5Index *sqlite3Fts5IndexFromCsrid(Fts5Global*, i64, Fts5Config **);
   479    479   
   480    480   /*
   481    481   ** End of interface to code in fts5.c.
   482    482   **************************************************************************/
   483    483   
   484    484   /**************************************************************************
   485    485   ** Interface to code in fts5_hash.c. 

Changes to ext/fts5/fts5_index.c.

  4593   4593   ){
  4594   4594     Fts5Config *pConfig = p->pConfig;
  4595   4595     Fts5IndexIter *pRet = 0;
  4596   4596     int iIdx = 0;
  4597   4597     Fts5Buffer buf = {0, 0, 0};
  4598   4598   
  4599   4599     /* If the QUERY_SCAN flag is set, all other flags must be clear. */
  4600         -  assert( (flags & FTS5INDEX_QUERY_SCAN)==0
  4601         -       || (flags & FTS5INDEX_QUERY_SCAN)==FTS5INDEX_QUERY_SCAN
  4602         -  );
         4600  +  assert( (flags & FTS5INDEX_QUERY_SCAN)==0 || flags==FTS5INDEX_QUERY_SCAN );
  4603   4601   
  4604   4602     if( sqlite3Fts5BufferGrow(&p->rc, &buf, nToken+1)==0 ){
  4605   4603       memcpy(&buf.p[1], pToken, nToken);
  4606   4604   
  4607   4605   #ifdef SQLITE_DEBUG
  4608   4606       /* If the QUERY_TEST_NOIDX flag was specified, then this must be a
  4609   4607       ** prefix-query. Instead of using a prefix-index (if one exists), 

Changes to ext/fts5/fts5_main.c.

  2017   2017   }
  2018   2018   
  2019   2019   
  2020   2020   /*
  2021   2021   ** Given cursor id iId, return a pointer to the corresponding Fts5Index 
  2022   2022   ** object. Or NULL If the cursor id does not exist.
  2023   2023   **
  2024         -** If successful, set *pnCol to the number of indexed columns in the
  2025         -** table before returning.
         2024  +** If successful, set *ppConfig to point to the associated config object 
         2025  +** before returning.
  2026   2026   */
  2027   2027   Fts5Index *sqlite3Fts5IndexFromCsrid(
  2028         -  Fts5Global *pGlobal, 
  2029         -  i64 iCsrId, 
  2030         -  int *pnCol
         2028  +  Fts5Global *pGlobal,            /* FTS5 global context for db handle */
         2029  +  i64 iCsrId,                     /* Id of cursor to find */
         2030  +  Fts5Config **ppConfig           /* OUT: Configuration object */
  2031   2031   ){
  2032   2032     Fts5Cursor *pCsr;
  2033   2033     Fts5Table *pTab;
  2034   2034   
  2035   2035     pCsr = fts5CursorFromCsrid(pGlobal, iCsrId);
  2036   2036     pTab = (Fts5Table*)pCsr->base.pVtab;
  2037         -  *pnCol = pTab->pConfig->nCol;
         2037  +  *ppConfig = pTab->pConfig;
  2038   2038   
  2039   2039     return pTab->pIndex;
  2040   2040   }
  2041   2041   
  2042   2042   /*
  2043   2043   ** Return a "position-list blob" corresponding to the current position of
  2044   2044   ** cursor pCsr via sqlite3_result_blob(). A position-list blob contains

Changes to ext/fts5/fts5_vocab.c.

    51     51     sqlite3_vtab_cursor base;
    52     52     sqlite3_stmt *pStmt;            /* Statement holding lock on pIndex */
    53     53     Fts5Index *pIndex;              /* Associated FTS5 index */
    54     54   
    55     55     int bEof;                       /* True if this cursor is at EOF */
    56     56     Fts5IndexIter *pIter;           /* Term/rowid iterator object */
    57     57   
           58  +  int nLeTerm;                    /* Size of zLeTerm in bytes */
           59  +  char *zLeTerm;                  /* (term <= $zLeTerm) paramater, or NULL */
           60  +
    58     61     /* These are used by 'col' tables only */
    59         -  int nCol;
           62  +  Fts5Config *pConfig;            /* Fts5 table configuration */
    60     63     int iCol;
    61     64     i64 *aCnt;
    62     65     i64 *aDoc;
    63     66   
    64         -  /* Output values */
           67  +  /* Output values used by 'row' and 'col' tables */
    65     68     i64 rowid;                      /* This table's current rowid value */
    66     69     Fts5Buffer term;                /* Current value of 'term' column */
    67         -  i64 aVal[3];                    /* Up to three columns left of 'term' */
    68     70   };
    69     71   
    70     72   #define FTS5_VOCAB_COL    0
    71     73   #define FTS5_VOCAB_ROW    1
    72     74   
    73     75   #define FTS5_VOCAB_COL_SCHEMA  "term, col, doc, cnt"
    74     76   #define FTS5_VOCAB_ROW_SCHEMA  "term, doc, cnt"
           77  +
           78  +/*
           79  +** Bits for the mask used as the idxNum value by xBestIndex/xFilter.
           80  +*/
           81  +#define FTS5_VOCAB_TERM_EQ 0x01
           82  +#define FTS5_VOCAB_TERM_GE 0x02
           83  +#define FTS5_VOCAB_TERM_LE 0x04
           84  +
    75     85   
    76     86   /*
    77     87   ** Translate a string containing an fts5vocab table type to an 
    78     88   ** FTS5_VOCAB_XXX constant. If successful, set *peType to the output
    79     89   ** value and return SQLITE_OK. Otherwise, set *pzErr to an error message
    80     90   ** and return SQLITE_ERROR.
    81     91   */
................................................................................
   226    236   /* 
   227    237   ** Implementation of the xBestIndex method.
   228    238   */
   229    239   static int fts5VocabBestIndexMethod(
   230    240     sqlite3_vtab *pVTab, 
   231    241     sqlite3_index_info *pInfo
   232    242   ){
          243  +  int i;
          244  +  int iTermEq = -1;
          245  +  int iTermGe = -1;
          246  +  int iTermLe = -1;
          247  +  int idxNum = 0;
          248  +  int nArg = 0;
          249  +
          250  +  for(i=0; i<pInfo->nConstraint; i++){
          251  +    struct sqlite3_index_constraint *p = &pInfo->aConstraint[i];
          252  +    if( p->usable==0 ) continue;
          253  +    if( p->iColumn==0 ){          /* term column */
          254  +      if( p->op==SQLITE_INDEX_CONSTRAINT_EQ ) iTermEq = i;
          255  +      if( p->op==SQLITE_INDEX_CONSTRAINT_LE ) iTermLe = i;
          256  +      if( p->op==SQLITE_INDEX_CONSTRAINT_LT ) iTermLe = i;
          257  +      if( p->op==SQLITE_INDEX_CONSTRAINT_GE ) iTermGe = i;
          258  +      if( p->op==SQLITE_INDEX_CONSTRAINT_GT ) iTermGe = i;
          259  +    }
          260  +  }
          261  +
          262  +  if( iTermEq>=0 ){
          263  +    idxNum |= FTS5_VOCAB_TERM_EQ;
          264  +    pInfo->aConstraintUsage[iTermEq].argvIndex = ++nArg;
          265  +    pInfo->aConstraintUsage[iTermEq].omit = 1;
          266  +    pInfo->estimatedCost = 100;
          267  +  }else{
          268  +    pInfo->estimatedCost = 1000000;
          269  +    if( iTermGe>=0 ){
          270  +      idxNum |= FTS5_VOCAB_TERM_GE;
          271  +      pInfo->aConstraintUsage[iTermGe].argvIndex = ++nArg;
          272  +      pInfo->estimatedCost = pInfo->estimatedCost / 2;
          273  +    }
          274  +    if( iTermLe>=0 ){
          275  +      idxNum |= FTS5_VOCAB_TERM_LE;
          276  +      pInfo->aConstraintUsage[iTermLe].argvIndex = ++nArg;
          277  +      pInfo->estimatedCost = pInfo->estimatedCost / 2;
          278  +    }
          279  +  }
          280  +
          281  +  pInfo->idxNum = idxNum;
          282  +
   233    283     return SQLITE_OK;
   234    284   }
   235    285   
   236    286   /*
   237    287   ** Implementation of xOpen method.
   238    288   */
   239    289   static int fts5VocabOpenMethod(
   240    290     sqlite3_vtab *pVTab, 
   241    291     sqlite3_vtab_cursor **ppCsr
   242    292   ){
   243    293     Fts5VocabTable *pTab = (Fts5VocabTable*)pVTab;
   244    294     Fts5Index *pIndex = 0;
   245         -  int nCol = 0;
          295  +  Fts5Config *pConfig = 0;
   246    296     Fts5VocabCursor *pCsr = 0;
   247    297     int rc = SQLITE_OK;
   248    298     sqlite3_stmt *pStmt = 0;
   249    299     char *zSql = 0;
   250         -  int nByte;
   251    300   
   252    301     zSql = sqlite3Fts5Mprintf(&rc,
   253    302         "SELECT t.%Q FROM %Q.%Q AS t WHERE t.%Q MATCH '*id'",
   254    303         pTab->zFts5Tbl, pTab->zFts5Db, pTab->zFts5Tbl, pTab->zFts5Tbl
   255    304     );
   256    305     if( zSql ){
   257    306       rc = sqlite3_prepare_v2(pTab->db, zSql, -1, &pStmt, 0);
................................................................................
   258    307     }
   259    308     sqlite3_free(zSql);
   260    309     assert( rc==SQLITE_OK || pStmt==0 );
   261    310     if( rc==SQLITE_ERROR ) rc = SQLITE_OK;
   262    311   
   263    312     if( pStmt && sqlite3_step(pStmt)==SQLITE_ROW ){
   264    313       i64 iId = sqlite3_column_int64(pStmt, 0);
   265         -    pIndex = sqlite3Fts5IndexFromCsrid(pTab->pGlobal, iId, &nCol);
          314  +    pIndex = sqlite3Fts5IndexFromCsrid(pTab->pGlobal, iId, &pConfig);
   266    315     }
   267    316   
   268    317     if( rc==SQLITE_OK && pIndex==0 ){
   269    318       rc = sqlite3_finalize(pStmt);
   270    319       pStmt = 0;
   271    320       if( rc==SQLITE_OK ){
   272    321         pVTab->zErrMsg = sqlite3_mprintf(
   273    322             "no such fts5 table: %s.%s", pTab->zFts5Db, pTab->zFts5Tbl
   274    323         );
   275    324         rc = SQLITE_ERROR;
   276    325       }
   277    326     }
   278    327   
   279         -  nByte = nCol * sizeof(i64) * 2 + sizeof(Fts5VocabCursor);
   280         -  pCsr = (Fts5VocabCursor*)sqlite3Fts5MallocZero(&rc, nByte);
          328  +  if( rc==SQLITE_OK ){
          329  +    int nByte = pConfig->nCol * sizeof(i64) * 2 + sizeof(Fts5VocabCursor);
          330  +    pCsr = (Fts5VocabCursor*)sqlite3Fts5MallocZero(&rc, nByte);
          331  +  }
          332  +
   281    333     if( pCsr ){
   282    334       pCsr->pIndex = pIndex;
   283    335       pCsr->pStmt = pStmt;
   284         -    pCsr->nCol = nCol;
          336  +    pCsr->pConfig = pConfig;
   285    337       pCsr->aCnt = (i64*)&pCsr[1];
   286         -    pCsr->aDoc = &pCsr->aCnt[nCol];
          338  +    pCsr->aDoc = &pCsr->aCnt[pConfig->nCol];
   287    339     }else{
   288    340       sqlite3_finalize(pStmt);
   289    341     }
   290    342   
   291    343     *ppCsr = (sqlite3_vtab_cursor*)pCsr;
   292    344     return rc;
   293    345   }
   294    346   
   295    347   static void fts5VocabResetCursor(Fts5VocabCursor *pCsr){
   296    348     pCsr->rowid = 0;
   297    349     sqlite3Fts5IterClose(pCsr->pIter);
   298    350     pCsr->pIter = 0;
          351  +  sqlite3_free(pCsr->zLeTerm);
          352  +  pCsr->nLeTerm = -1;
          353  +  pCsr->zLeTerm = 0;
   299    354   }
   300    355   
   301    356   /*
   302    357   ** Close the cursor.  For additional information see the documentation
   303    358   ** on the xClose method of the virtual table interface.
   304    359   */
   305    360   static int fts5VocabCloseMethod(sqlite3_vtab_cursor *pCursor){
................................................................................
   315    370   /*
   316    371   ** Advance the cursor to the next row in the table.
   317    372   */
   318    373   static int fts5VocabNextMethod(sqlite3_vtab_cursor *pCursor){
   319    374     Fts5VocabCursor *pCsr = (Fts5VocabCursor*)pCursor;
   320    375     Fts5VocabTable *pTab = (Fts5VocabTable*)pCursor->pVtab;
   321    376     int rc = SQLITE_OK;
          377  +  int nCol = pCsr->pConfig->nCol;
   322    378   
   323    379     pCsr->rowid++;
   324    380   
   325    381     if( pTab->eType==FTS5_VOCAB_COL ){
   326         -    for(pCsr->iCol++; pCsr->iCol<pCsr->nCol; pCsr->iCol++){
          382  +    for(pCsr->iCol++; pCsr->iCol<nCol; pCsr->iCol++){
   327    383         if( pCsr->aCnt[pCsr->iCol] ) break;
   328    384       }
   329    385     }
   330    386   
   331         -  if( pTab->eType==FTS5_VOCAB_ROW || pCsr->iCol>=pCsr->nCol ){
          387  +  if( pTab->eType==FTS5_VOCAB_ROW || pCsr->iCol>=nCol ){
   332    388       if( sqlite3Fts5IterEof(pCsr->pIter) ){
   333    389         pCsr->bEof = 1;
   334    390       }else{
   335    391         const char *zTerm;
   336    392         int nTerm;
   337    393   
   338    394         zTerm = sqlite3Fts5IterTerm(pCsr->pIter, &nTerm);
          395  +      if( pCsr->nLeTerm>=0 ){
          396  +        int nCmp = MIN(nTerm, pCsr->nLeTerm);
          397  +        int bCmp = memcmp(pCsr->zLeTerm, zTerm, nCmp);
          398  +        if( bCmp<0 || (bCmp==0 && pCsr->nLeTerm<nTerm) ){
          399  +          pCsr->bEof = 1;
          400  +          return SQLITE_OK;
          401  +        }
          402  +      }
          403  +
   339    404         sqlite3Fts5BufferSet(&rc, &pCsr->term, nTerm, (const u8*)zTerm);
   340         -      memset(pCsr->aVal, 0, sizeof(pCsr->aVal));
   341         -      memset(pCsr->aCnt, 0, pCsr->nCol * sizeof(i64));
   342         -      memset(pCsr->aDoc, 0, pCsr->nCol * sizeof(i64));
          405  +      memset(pCsr->aCnt, 0, nCol * sizeof(i64));
          406  +      memset(pCsr->aDoc, 0, nCol * sizeof(i64));
   343    407         pCsr->iCol = 0;
   344    408   
   345    409         assert( pTab->eType==FTS5_VOCAB_COL || pTab->eType==FTS5_VOCAB_ROW );
   346    410         while( rc==SQLITE_OK ){
   347    411           i64 dummy;
   348    412           const u8 *pPos; int nPos;   /* Position list */
   349    413           i64 iPos = 0;               /* 64-bit position read from poslist */
   350    414           int iOff = 0;               /* Current offset within position list */
   351    415   
   352    416           rc = sqlite3Fts5IterPoslist(pCsr->pIter, 0, &pPos, &nPos, &dummy);
   353    417           if( rc==SQLITE_OK ){
   354    418             if( pTab->eType==FTS5_VOCAB_ROW ){
   355    419               while( 0==sqlite3Fts5PoslistNext64(pPos, nPos, &iOff, &iPos) ){
   356         -              pCsr->aVal[1]++;
          420  +              pCsr->aCnt[0]++;
   357    421               }
   358         -            pCsr->aVal[0]++;
          422  +            pCsr->aDoc[0]++;
   359    423             }else{
   360    424               int iCol = -1;
   361    425               while( 0==sqlite3Fts5PoslistNext64(pPos, nPos, &iOff, &iPos) ){
   362    426                 int ii = FTS5_POS2COLUMN(iPos);
   363    427                 pCsr->aCnt[ii]++;
   364    428                 if( iCol!=ii ){
   365    429                   pCsr->aDoc[ii]++;
   366    430                   iCol = ii;
   367    431                 }
   368    432               }
   369    433             }
   370    434             rc = sqlite3Fts5IterNextScan(pCsr->pIter);
   371    435           }
          436  +
   372    437           if( rc==SQLITE_OK ){
   373    438             zTerm = sqlite3Fts5IterTerm(pCsr->pIter, &nTerm);
   374         -          if( nTerm!=pCsr->term.n || memcmp(zTerm, pCsr->term.p, nTerm) ) break;
          439  +          if( nTerm!=pCsr->term.n || memcmp(zTerm, pCsr->term.p, nTerm) ){
          440  +            break;
          441  +          }
   375    442             if( sqlite3Fts5IterEof(pCsr->pIter) ) break;
   376    443           }
   377    444         }
   378    445       }
   379    446     }
   380    447   
   381    448     if( pCsr->bEof==0 && pTab->eType==FTS5_VOCAB_COL ){
   382    449       while( pCsr->aCnt[pCsr->iCol]==0 ) pCsr->iCol++;
   383         -    pCsr->aVal[0] = pCsr->iCol;
   384         -    pCsr->aVal[1] = pCsr->aDoc[pCsr->iCol];
   385         -    pCsr->aVal[2] = pCsr->aCnt[pCsr->iCol];
          450  +    assert( pCsr->iCol<pCsr->pConfig->nCol );
   386    451     }
   387    452     return rc;
   388    453   }
   389    454   
   390    455   /*
   391    456   ** This is the xFilter implementation for the virtual table.
   392    457   */
................................................................................
   395    460     int idxNum,                     /* Strategy index */
   396    461     const char *idxStr,             /* Unused */
   397    462     int nVal,                       /* Number of elements in apVal */
   398    463     sqlite3_value **apVal           /* Arguments for the indexing scheme */
   399    464   ){
   400    465     Fts5VocabCursor *pCsr = (Fts5VocabCursor*)pCursor;
   401    466     int rc;
   402         -  const int flags = FTS5INDEX_QUERY_SCAN;
          467  +
          468  +  int iVal = 0;
          469  +  int f = FTS5INDEX_QUERY_SCAN;
          470  +  const char *zTerm = 0;
          471  +  int nTerm = 0;
          472  +
          473  +  sqlite3_value *pEq = 0;
          474  +  sqlite3_value *pGe = 0;
          475  +  sqlite3_value *pLe = 0;
   403    476   
   404    477     fts5VocabResetCursor(pCsr);
   405         -  rc = sqlite3Fts5IndexQuery(pCsr->pIndex, 0, 0, flags, 0, &pCsr->pIter);
          478  +  if( idxNum & FTS5_VOCAB_TERM_EQ ) pEq = apVal[iVal++];
          479  +  if( idxNum & FTS5_VOCAB_TERM_GE ) pGe = apVal[iVal++];
          480  +  if( idxNum & FTS5_VOCAB_TERM_LE ) pLe = apVal[iVal++];
          481  +
          482  +  if( pEq ){
          483  +    zTerm = sqlite3_value_text(pEq);
          484  +    nTerm = sqlite3_value_bytes(pEq);
          485  +    f = 0;
          486  +  }else{
          487  +    if( pGe ){
          488  +      zTerm = sqlite3_value_text(pGe);
          489  +      nTerm = sqlite3_value_bytes(pGe);
          490  +    }
          491  +    if( pLe ){
          492  +      const char *zCopy = sqlite3_value_text(pLe);
          493  +      pCsr->nLeTerm = sqlite3_value_bytes(pLe);
          494  +      pCsr->zLeTerm = sqlite3_malloc(pCsr->nLeTerm+1);
          495  +      if( pCsr->zLeTerm==0 ){
          496  +        rc = SQLITE_NOMEM;
          497  +      }else{
          498  +        memcpy(pCsr->zLeTerm, zCopy, pCsr->nLeTerm+1);
          499  +      }
          500  +    }
          501  +  }
          502  +
          503  +
          504  +  if( rc==SQLITE_OK ){
          505  +    rc = sqlite3Fts5IndexQuery(pCsr->pIndex, zTerm, nTerm, f, 0, &pCsr->pIter);
          506  +  }
   406    507     if( rc==SQLITE_OK ){
   407    508       rc = fts5VocabNextMethod(pCursor);
   408    509     }
   409    510   
   410    511     return rc;
   411    512   }
   412    513   
................................................................................
   421    522   
   422    523   static int fts5VocabColumnMethod(
   423    524     sqlite3_vtab_cursor *pCursor,   /* Cursor to retrieve value from */
   424    525     sqlite3_context *pCtx,          /* Context for sqlite3_result_xxx() calls */
   425    526     int iCol                        /* Index of column to read value from */
   426    527   ){
   427    528     Fts5VocabCursor *pCsr = (Fts5VocabCursor*)pCursor;
   428         -  switch( iCol ){
   429         -    case 0: /* term */
   430         -      sqlite3_result_text(
   431         -          pCtx, (const char*)pCsr->term.p, pCsr->term.n, SQLITE_TRANSIENT
   432         -      );
   433         -      break;
   434    529   
   435         -    default:
   436         -      assert( iCol<4 && iCol>0 );
   437         -      sqlite3_result_int64(pCtx, pCsr->aVal[iCol-1]);
   438         -      break;
          530  +  if( iCol==0 ){
          531  +    sqlite3_result_text(
          532  +        pCtx, (const char*)pCsr->term.p, pCsr->term.n, SQLITE_TRANSIENT
          533  +    );
          534  +  }
          535  +  else if( ((Fts5VocabTable*)(pCursor->pVtab))->eType==FTS5_VOCAB_COL ){
          536  +    assert( iCol==1 || iCol==2 || iCol==3 );
          537  +    if( iCol==1 ){
          538  +      const char *z = pCsr->pConfig->azCol[pCsr->iCol];
          539  +      sqlite3_result_text(pCtx, z, -1, SQLITE_STATIC);
          540  +    }else if( iCol==2 ){
          541  +      sqlite3_result_int64(pCtx, pCsr->aDoc[pCsr->iCol]);
          542  +    }else{
          543  +      sqlite3_result_int64(pCtx, pCsr->aCnt[pCsr->iCol]);
          544  +    }
          545  +  }else{
          546  +    assert( iCol==1 || iCol==2 );
          547  +    if( iCol==1 ){
          548  +      sqlite3_result_int64(pCtx, pCsr->aDoc[0]);
          549  +    }else{
          550  +      sqlite3_result_int64(pCtx, pCsr->aCnt[0]);
          551  +    }
   439    552     }
   440    553     return SQLITE_OK;
   441    554   }
   442    555   
   443    556   /* 
   444    557   ** This is the xRowid method. The SQLite core calls this routine to
   445    558   ** retrieve the rowid for the current row of the result set. The

Changes to ext/fts5/test/fts5vocab.test.

    52     52   
    53     53   do_execsql_test 1.4.1 {
    54     54     SELECT * FROM v1;
    55     55   } {x 2 4  y 1 1  z 1 1}
    56     56   
    57     57   do_execsql_test 1.4.2 {
    58     58     SELECT * FROM v2;
    59         -} {x 0 2 4  y 0 1 1  z 0 1 1}
           59  +} {x one 2 4  y one 1 1  z one 1 1}
    60     60   
    61     61   do_execsql_test 1.5.1 {
    62     62     BEGIN;
    63     63       INSERT INTO t1 VALUES('a b c');
    64     64       SELECT * FROM v1 WHERE term<'d';
    65     65   } {a 1 1   b 1 1   c 1 1}
    66     66   
    67     67   do_execsql_test 1.5.2 {
    68     68       SELECT * FROM v2 WHERE term<'d';
    69     69     COMMIT;
    70         -} {a 0 1 1  b 0 1 1  c 0 1 1}
           70  +} {a one 1 1  b one 1 1  c one 1 1}
    71     71   
    72     72   do_execsql_test 1.6 {
    73     73     DELETE FROM t1 WHERE one = 'a b c';
    74     74     SELECT * FROM v1;
    75     75   } {x 2 4  y 1 1  z 1 1}
    76     76   
    77     77   #-------------------------------------------------------------------------
................................................................................
    87     87     INSERT INTO tt VALUES('c e c f g b', 'f e d b g a');
    88     88     INSERT INTO tt VALUES('g d e f d e', 'a c d b a g');
    89     89     INSERT INTO tt VALUES('e f a c c b', 'b f e a f d y');
    90     90     INSERT INTO tt VALUES('c c a a c f', 'd g a e b g');
    91     91   }
    92     92   
    93     93   set res_col {
    94         -  a 0 6 11    a 1 7 9
    95         -  b 0 6 7     b 1 7 7 
    96         -  c 0 6 12    c 1 5 8 
    97         -  d 0 4 6     d 1 9 13 
    98         -  e 0 6 7     e 1 6 6 
    99         -  f 0 9 10    f 1 7 10 
   100         -  g 0 5 7     g 1 5 7
   101         -  x 0 1 1     y 1 1 1
           94  +  a a 6 11    a b 7 9
           95  +  b a 6 7     b b 7 7 
           96  +  c a 6 12    c b 5 8 
           97  +  d a 4 6     d b 9 13 
           98  +  e a 6 7     e b 6 6 
           99  +  f a 9 10    f b 7 10 
          100  +  g a 5 7     g b 5 7
          101  +  x a 1 1     y b 1 1
   102    102   }
   103    103   set res_row {
   104    104     a 10 20   b 9 14   c 9 20   d 9 19   
   105    105     e 8 13   f 10 20   g 7 14   x 1 1   
   106    106     y 1 1
   107    107   }
   108    108   
................................................................................
   208    208     SELECT * FROM vocab2;
   209    209   } {1 {no such fts5 table: main.jjj}}
   210    210   
   211    211   do_catchsql_test 6.2 {
   212    212     CREATE VIRTUAL TABLE vocab3 USING fts5vocab(lll, row);
   213    213     SELECT * FROM vocab3;
   214    214   } {1 {no such fts5 table: main.lll}}
          215  +
          216  +#-------------------------------------------------------------------------
          217  +# Test single term queries on fts5vocab tables (i.e. those with term=?
          218  +# constraints in the WHERE clause).
          219  +#
          220  +do_execsql_test 7.0 {
          221  +  CREATE VIRTUAL TABLE tx USING fts5(one, two);
          222  +  INSERT INTO tx VALUES('g a ggg g a b eee',      'cc d aa ff g ee');
          223  +  INSERT INTO tx VALUES('dd fff i a i jjj',       'f fff hh jj e f');
          224  +  INSERT INTO tx VALUES('ggg a f f fff dd aa',    'd ggg f f j gg ddd');
          225  +  INSERT INTO tx VALUES('e bb h jjj ii gg',       'e aa e f c fff');
          226  +  INSERT INTO tx VALUES('j ff aa a h',            'h a j bbb bb');
          227  +  INSERT INTO tx VALUES('cc i ff c d f',          'dd ii fff f c cc d');
          228  +  INSERT INTO tx VALUES('jjj g i bb cc eee',      'hhh iii aaa b bbb aaa');
          229  +  INSERT INTO tx VALUES('hhh hhh hhh bb fff f',   'fff gg aa ii h a');
          230  +  INSERT INTO tx VALUES('b c cc aaa iii ggg f',   'iii ff ee a ff c cc');
          231  +  INSERT INTO tx VALUES('hhh b hhh aaa j i i',    'dd ee ee aa bbb iii');
          232  +  INSERT INTO tx VALUES('hh dd h b g ff i',       'ccc bb cc ccc f a d');
          233  +  INSERT INTO tx VALUES('g d b ggg jj',           'fff jj ff jj g gg ee');
          234  +  INSERT INTO tx VALUES('g ee ggg ggg cc bb eee', 'aa j jjj bbb dd eee ff');
          235  +  INSERT INTO tx VALUES('c jjj hh ddd dd h',      'e aaa h jjj gg');
          236  +
          237  +  CREATE VIRTUAL TABLE txr USING fts5vocab(tx, row);
          238  +  CREATE VIRTUAL TABLE txc USING fts5vocab(tx, col);
          239  +}
          240  +
          241  +proc cont {L elem} {
          242  +  set n 0
          243  +  foreach e $L { if {$elem==$e} {incr n} }
          244  +  set n
          245  +}
          246  +db func cont cont
          247  +
          248  +foreach {term} {
          249  +  a aa aaa
          250  +  b bb bbb
          251  +  c cc ccc
          252  +  d dd ddd
          253  +  e ee eee
          254  +  f ff fff
          255  +  g gg ggg
          256  +  h hh hhh
          257  +  i ii iii
          258  +  j jj jjj
          259  +} {
          260  +  set resr [db eval {
          261  +    SELECT $term, 
          262  +      sum(cont(one || ' ' || two, $term) > 0),
          263  +      sum(cont(one || ' ' || two, $term))
          264  +    FROM tx
          265  +  }]
          266  +  if {[lindex $resr 1]==0} {set resr [list]}
          267  +
          268  +  set r1 [db eval {
          269  +    SELECT $term, 'one', sum(cont(one, $term)>0), sum(cont(one, $term)) FROM tx
          270  +  }]
          271  +  if {[lindex $r1 2]==0} {set r1 [list]}
          272  +
          273  +  set r2 [db eval {
          274  +    SELECT $term, 'two', sum(cont(two, $term)>0), sum(cont(two, $term)) FROM tx
          275  +  }]
          276  +  if {[lindex $r2 2]==0} {set r2 [list]}
          277  +
          278  +  set resc [concat $r1 $r2]
          279  +  do_execsql_test 7.$term.1 {SELECT * FROM txc WHERE term=$term} $resc
          280  +  do_execsql_test 7.$term.2 {SELECT * FROM txr WHERE term=$term} $resr
          281  +}
          282  +
          283  +do_execsql_test 7.1 {
          284  +  CREATE TABLE txr_c AS SELECT * FROM txr;
          285  +  CREATE TABLE txc_c AS SELECT * FROM txc;
          286  +}
          287  +
          288  +# Test range queries on the fts5vocab tables created above.
          289  +#
          290  +foreach {tn a b} {
          291  +  1   a   jjj
          292  +  2   bb  j
          293  +  3   ccc ddd
          294  +  4   dd  xyz
          295  +  5   xzy dd
          296  +  6   h   hh
          297  +} {
          298  +  do_execsql_test 7.2.$tn.1 {
          299  +    SELECT * FROM txr WHERE term>=$a
          300  +  } [db eval {SELECT * FROM txr_c WHERE term>=$a}]
          301  +  do_execsql_test 7.2.$tn.2 {
          302  +    SELECT * FROM txr WHERE term<=$b
          303  +  } [db eval {SELECT * FROM txr_c WHERE term <=$b}]
          304  +  do_execsql_test 7.2.$tn.3 {
          305  +    SELECT * FROM txr WHERE term>=$a AND term<=$b
          306  +  } [db eval {SELECT * FROM txr_c WHERE term>=$a AND term <=$b}]
          307  +
          308  +  do_execsql_test 7.2.$tn.4 {
          309  +    SELECT * FROM txc WHERE term>=$a
          310  +  } [db eval {SELECT * FROM txc_c WHERE term>=$a}]
          311  +  do_execsql_test 7.2.$tn.5 {
          312  +    SELECT * FROM txc WHERE term<=$b
          313  +  } [db eval {SELECT * FROM txc_c WHERE term <=$b}]
          314  +  do_execsql_test 7.2.$tn.6 {
          315  +    SELECT * FROM txc WHERE term>=$a AND term<=$b
          316  +  } [db eval {SELECT * FROM txc_c WHERE term>=$a AND term <=$b}]
          317  +
          318  +  do_execsql_test 7.2.$tn.7 {
          319  +    SELECT * FROM txr WHERE term>$a
          320  +  } [db eval {SELECT * FROM txr_c WHERE term>$a}]
          321  +  do_execsql_test 7.2.$tn.8 {
          322  +    SELECT * FROM txr WHERE term<$b
          323  +  } [db eval {SELECT * FROM txr_c WHERE term<$b}]
          324  +  do_execsql_test 7.2.$tn.9 {
          325  +    SELECT * FROM txr WHERE term>$a AND term<$b
          326  +  } [db eval {SELECT * FROM txr_c WHERE term>$a AND term <$b}]
          327  +
          328  +  do_execsql_test 7.2.$tn.10 {
          329  +    SELECT * FROM txc WHERE term>$a
          330  +  } [db eval {SELECT * FROM txc_c WHERE term>$a}]
          331  +  do_execsql_test 7.2.$tn.11 {
          332  +    SELECT * FROM txc WHERE term<$b
          333  +  } [db eval {SELECT * FROM txc_c WHERE term<$b}]
          334  +  do_execsql_test 7.2.$tn.12 {
          335  +    SELECT * FROM txc WHERE term>$a AND term<$b
          336  +  } [db eval {SELECT * FROM txc_c WHERE term>$a AND term <$b}]
          337  +}
   215    338   
   216    339   finish_test
   217    340