/ Check-in [386701de]
Login

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

Overview
Comment:Optimize handling of equality and range constraints on the "term" column of an fts4aux table.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 386701ded2bcee5309ec9f285d94e6eb1eade193
User & Date: dan 2011-02-02 17:30:43
Context
2011-02-02
19:17
Fix minor problems with the output of "PRAGMA wal_checkpoint". In both code and tests. check-in: aef61036 user: dan tags: trunk
17:30
Optimize handling of equality and range constraints on the "term" column of an fts4aux table. check-in: 386701de user: dan tags: trunk
16:34
Merge in the blocking-checkpoint enhancement, including the new sqlite3_wal_checkpoint_v2() interface and the PRAGMA wal_checkpoint(full) statement. check-in: bac7342c user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/fts3/fts3.c.

  2048   2048   
  2049   2049   int sqlite3Fts3SegReaderCursor(
  2050   2050     Fts3Table *p,                   /* FTS3 table handle */
  2051   2051     int iLevel,                     /* Level of segments to scan */
  2052   2052     const char *zTerm,              /* Term to query for */
  2053   2053     int nTerm,                      /* Size of zTerm in bytes */
  2054   2054     int isPrefix,                   /* True for a prefix search */
         2055  +  int isScan,                     /* True to scan from zTerm to EOF */
  2055   2056     Fts3SegReaderCursor *pCsr       /* Cursor object to populate */
  2056   2057   ){
  2057   2058     int rc = SQLITE_OK;
  2058   2059     int rc2;
  2059   2060     int iAge = 0;
  2060   2061     sqlite3_stmt *pStmt = 0;
  2061   2062     Fts3SegReader *pPending = 0;
................................................................................
  2063   2064     assert( iLevel==FTS3_SEGCURSOR_ALL 
  2064   2065         ||  iLevel==FTS3_SEGCURSOR_PENDING 
  2065   2066         ||  iLevel>=0
  2066   2067     );
  2067   2068     assert( FTS3_SEGCURSOR_PENDING<0 );
  2068   2069     assert( FTS3_SEGCURSOR_ALL<0 );
  2069   2070     assert( iLevel==FTS3_SEGCURSOR_ALL || (zTerm==0 && isPrefix==1) );
         2071  +  assert( isPrefix==0 || isScan==0 );
         2072  +
  2070   2073   
  2071   2074     memset(pCsr, 0, sizeof(Fts3SegReaderCursor));
  2072   2075   
  2073   2076     /* If iLevel is less than 0, include a seg-reader for the pending-terms. */
  2074         -  if( iLevel<0 ){
         2077  +  assert( isScan==0 || fts3HashCount(&p->pendingTerms)==0 );
         2078  +  if( iLevel<0 && isScan==0 ){
  2075   2079       rc = sqlite3Fts3SegReaderPending(p, zTerm, nTerm, isPrefix, &pPending);
  2076   2080       if( rc==SQLITE_OK && pPending ){
  2077   2081         int nByte = (sizeof(Fts3SegReader *) * 16);
  2078   2082         pCsr->apSegment = (Fts3SegReader **)sqlite3_malloc(nByte);
  2079   2083         if( pCsr->apSegment==0 ){
  2080   2084           rc = SQLITE_NOMEM;
  2081   2085         }else{
................................................................................
  2113   2117   
  2114   2118         /* If zTerm is not NULL, and this segment is not stored entirely on its
  2115   2119         ** root node, the range of leaves scanned can be reduced. Do this. */
  2116   2120         if( iStartBlock && zTerm ){
  2117   2121           sqlite3_int64 *pi = (isPrefix ? &iLeavesEndBlock : 0);
  2118   2122           rc = fts3SelectLeaf(p, zTerm, nTerm, zRoot, nRoot, &iStartBlock, pi);
  2119   2123           if( rc!=SQLITE_OK ) goto finished;
  2120         -        if( isPrefix==0 ) iLeavesEndBlock = iStartBlock;
         2124  +        if( isPrefix==0 && isScan==0 ) iLeavesEndBlock = iStartBlock;
  2121   2125         }
  2122         -  
         2126  + 
  2123   2127         rc = sqlite3Fts3SegReaderNew(iAge, iStartBlock, iLeavesEndBlock,
  2124   2128             iEndBlock, zRoot, nRoot, &pCsr->apSegment[pCsr->nSegment]
  2125   2129         );
  2126   2130         if( rc!=SQLITE_OK ) goto finished;
  2127   2131         pCsr->nSegment++;
  2128   2132         iAge++;
  2129   2133       }
................................................................................
  2150   2154   
  2151   2155     pSegcsr = sqlite3_malloc(sizeof(Fts3SegReaderCursor));
  2152   2156     if( pSegcsr ){
  2153   2157       Fts3Table *p = (Fts3Table *)pCsr->base.pVtab;
  2154   2158       int i;
  2155   2159       int nCost = 0;
  2156   2160       rc = sqlite3Fts3SegReaderCursor(
  2157         -        p, FTS3_SEGCURSOR_ALL, zTerm, nTerm, isPrefix, pSegcsr);
         2161  +        p, FTS3_SEGCURSOR_ALL, zTerm, nTerm, isPrefix, 0, pSegcsr);
  2158   2162     
  2159   2163       for(i=0; rc==SQLITE_OK && i<pSegcsr->nSegment; i++){
  2160   2164         rc = sqlite3Fts3SegReaderCost(pCsr, pSegcsr->apSegment[i], &nCost);
  2161   2165       }
  2162   2166       pSegcsr->nCost = nCost;
  2163   2167     }
  2164   2168   

Changes to ext/fts3/fts3Int.h.

   306    306   #define FTS3_SEGCURSOR_PENDING -1
   307    307   #define FTS3_SEGCURSOR_ALL     -2
   308    308   
   309    309   int sqlite3Fts3SegReaderStart(Fts3Table*, Fts3SegReaderCursor*, Fts3SegFilter*);
   310    310   int sqlite3Fts3SegReaderStep(Fts3Table *, Fts3SegReaderCursor *);
   311    311   void sqlite3Fts3SegReaderFinish(Fts3SegReaderCursor *);
   312    312   int sqlite3Fts3SegReaderCursor(
   313         -    Fts3Table *, int, const char *, int, int, Fts3SegReaderCursor *);
          313  +    Fts3Table *, int, const char *, int, int, int, Fts3SegReaderCursor *);
   314    314   
   315    315   /* Flags allowed as part of the 4th argument to SegmentReaderIterate() */
   316    316   #define FTS3_SEGMENT_REQUIRE_POS   0x00000001
   317    317   #define FTS3_SEGMENT_IGNORE_EMPTY  0x00000002
   318    318   #define FTS3_SEGMENT_COLUMN_FILTER 0x00000004
   319    319   #define FTS3_SEGMENT_PREFIX        0x00000008
          320  +#define FTS3_SEGMENT_SCAN          0x00000010
   320    321   
   321    322   /* Type passed as 4th argument to SegmentReaderIterate() */
   322    323   struct Fts3SegFilter {
   323    324     const char *zTerm;
   324    325     int nTerm;
   325    326     int iCol;
   326    327     int flags;

Changes to ext/fts3/fts3_aux.c.

    24     24   struct Fts3auxTable {
    25     25     sqlite3_vtab base;              /* Base class used by SQLite core */
    26     26     Fts3Table *pFts3Tab;
    27     27   };
    28     28   
    29     29   struct Fts3auxCursor {
    30     30     sqlite3_vtab_cursor base;       /* Base class used by SQLite core */
    31         -
           31  +  Fts3SegReaderCursor csr;        /* Must be right after "base" */
    32     32     Fts3SegFilter filter;
    33         -  Fts3SegReaderCursor csr;
           33  +  char *zStop;
           34  +  int nStop;
    34     35     int isEof;
    35         -
    36     36     sqlite3_int64 iRowid;
    37     37     sqlite3_int64 nDoc;
    38     38     sqlite3_int64 nOcc;
    39     39   };
    40     40   
    41     41   /*
    42     42   ** Schema of the terms table.
................................................................................
   110    110       sqlite3_finalize(pFts3->aStmt[i]);
   111    111     }
   112    112     sqlite3_free(pFts3->zSegmentsTbl);
   113    113     sqlite3_free(p);
   114    114     return SQLITE_OK;
   115    115   }
   116    116   
          117  +#define FTS4AUX_EQ_CONSTRAINT 1
          118  +#define FTS4AUX_GE_CONSTRAINT 2
          119  +#define FTS4AUX_LE_CONSTRAINT 4
          120  +
   117    121   /*
   118    122   ** xBestIndex - Analyze a WHERE and ORDER BY clause.
   119    123   */
   120    124   static int fts3auxBestIndexMethod(
   121    125     sqlite3_vtab *pVTab, 
   122    126     sqlite3_index_info *pInfo
   123    127   ){
          128  +  int i;
          129  +  int iEq = -1;
          130  +  int iGe = -1;
          131  +  int iLe = -1;
   124    132   
   125    133     /* This vtab delivers always results in "ORDER BY term ASC" order. */
   126    134     if( pInfo->nOrderBy==1 
   127    135      && pInfo->aOrderBy[0].iColumn==0 
   128    136      && pInfo->aOrderBy[0].desc==0
   129    137     ){
   130    138       pInfo->orderByConsumed = 1;
   131    139     }
          140  +
          141  +  /* Search for equality and range constraints on the "term" column. */
          142  +  for(i=0; i<pInfo->nConstraint; i++){
          143  +    if( pInfo->aConstraint[i].usable && pInfo->aConstraint[i].iColumn==0 ){
          144  +      int op = pInfo->aConstraint[i].op;
          145  +      if( op==SQLITE_INDEX_CONSTRAINT_EQ ) iEq = i;
          146  +      if( op==SQLITE_INDEX_CONSTRAINT_LT ) iLe = i;
          147  +      if( op==SQLITE_INDEX_CONSTRAINT_LE ) iLe = i;
          148  +      if( op==SQLITE_INDEX_CONSTRAINT_GT ) iGe = i;
          149  +      if( op==SQLITE_INDEX_CONSTRAINT_GE ) iGe = i;
          150  +    }
          151  +  }
          152  +
          153  +  if( iEq>=0 ){
          154  +    pInfo->idxNum = FTS4AUX_EQ_CONSTRAINT;
          155  +    pInfo->aConstraintUsage[iEq].argvIndex = 1;
          156  +  }else{
          157  +    pInfo->idxNum = 0;
          158  +    if( iGe>=0 ){
          159  +      pInfo->idxNum += FTS4AUX_GE_CONSTRAINT;
          160  +      pInfo->aConstraintUsage[iGe].argvIndex = 1;
          161  +    }
          162  +    if( iLe>=0 ){
          163  +      pInfo->idxNum += FTS4AUX_LE_CONSTRAINT;
          164  +      pInfo->aConstraintUsage[iLe].argvIndex = 1 + (iGe>=0);
          165  +    }
          166  +  }
   132    167   
   133    168     pInfo->estimatedCost = 20000;
   134    169     return SQLITE_OK;
   135    170   }
   136    171   
   137    172   /*
   138    173   ** xOpen - Open a cursor.
................................................................................
   153    188   */
   154    189   static int fts3auxCloseMethod(sqlite3_vtab_cursor *pCursor){
   155    190     Fts3Table *pFts3 = ((Fts3auxTable *)pCursor->pVtab)->pFts3Tab;
   156    191     Fts3auxCursor *pCsr = (Fts3auxCursor *)pCursor;
   157    192   
   158    193     sqlite3Fts3SegmentsClose(pFts3);
   159    194     sqlite3Fts3SegReaderFinish(&pCsr->csr);
          195  +  sqlite3_free((void *)pCsr->filter.zTerm);
          196  +  sqlite3_free(pCsr->zStop);
   160    197     sqlite3_free(pCsr);
   161    198     return SQLITE_OK;
   162    199   }
   163    200   
   164    201   /*
   165    202   ** xNext - Advance the cursor to the next row, if any.
   166    203   */
................................................................................
   171    208   
   172    209     rc = sqlite3Fts3SegReaderStep(pFts3, &pCsr->csr);
   173    210     if( rc==SQLITE_ROW ){
   174    211       int i;
   175    212       int isIgnore = 1;
   176    213       int nDoclist = pCsr->csr.nDoclist;
   177    214       char *aDoclist = pCsr->csr.aDoclist;
          215  +
          216  +    if( pCsr->zStop ){
          217  +      int n = (pCsr->nStop<pCsr->csr.nTerm) ? pCsr->nStop : pCsr->csr.nTerm;
          218  +      int mc = memcmp(pCsr->zStop, pCsr->csr.zTerm, n);
          219  +      if( mc<0 || (mc==0 && pCsr->csr.nTerm>pCsr->nStop) ){
          220  +        pCsr->isEof = 1;
          221  +        return SQLITE_OK;
          222  +      }
          223  +    }
   178    224   
   179    225       /* Now count the number of documents and positions in the doclist
   180    226       ** in pCsr->csr.aDoclist[]. Store the number of documents in pCsr->nDoc
   181    227       ** and the number of occurrences in pCsr->nOcc.  */
   182    228       pCsr->nDoc = 0;
   183    229       pCsr->nOcc = 0;
   184    230       i = 0;
................................................................................
   212    258     const char *idxStr,             /* Unused */
   213    259     int nVal,                       /* Number of elements in apVal */
   214    260     sqlite3_value **apVal           /* Arguments for the indexing scheme */
   215    261   ){
   216    262     Fts3auxCursor *pCsr = (Fts3auxCursor *)pCursor;
   217    263     Fts3Table *pFts3 = ((Fts3auxTable *)pCursor->pVtab)->pFts3Tab;
   218    264     int rc;
          265  +  int isScan;
   219    266   
          267  +  assert( idxStr==0 );
          268  +  assert( idxNum==FTS4AUX_EQ_CONSTRAINT || idxNum==0
          269  +       || idxNum==FTS4AUX_LE_CONSTRAINT || idxNum==FTS4AUX_GE_CONSTRAINT
          270  +       || idxNum==(FTS4AUX_LE_CONSTRAINT|FTS4AUX_GE_CONSTRAINT)
          271  +  );
          272  +  isScan = (idxNum!=FTS4AUX_EQ_CONSTRAINT);
          273  +
          274  +  /* In case this cursor is being reused, close and zero it. */
          275  +  testcase(pCsr->filter.zTerm);
   220    276     sqlite3Fts3SegReaderFinish(&pCsr->csr);
   221         -  memset(&pCsr->csr, 0, sizeof(Fts3SegReaderCursor));
   222         -  pCsr->isEof = 0;
   223         -  pCsr->iRowid = 0;
          277  +  sqlite3_free((void *)pCsr->filter.zTerm);
          278  +  memset(&pCsr->csr, 0, ((u8*)&pCsr[1]) - (u8*)&pCsr->csr);
          279  +
   224    280     pCsr->filter.flags = FTS3_SEGMENT_REQUIRE_POS|FTS3_SEGMENT_IGNORE_EMPTY;
          281  +  if( isScan ) pCsr->filter.flags |= FTS3_SEGMENT_SCAN;
          282  +
          283  +  if( idxNum&(FTS4AUX_EQ_CONSTRAINT|FTS4AUX_GE_CONSTRAINT) ){
          284  +    const char *zStr = sqlite3_value_text(apVal[0]);
          285  +    if( zStr ){
          286  +      pCsr->filter.zTerm = sqlite3_mprintf("%s", zStr);
          287  +      pCsr->filter.nTerm = sqlite3_value_bytes(apVal[0]);
          288  +      if( pCsr->filter.zTerm==0 ) return SQLITE_NOMEM;
          289  +    }
          290  +  }
          291  +  if( idxNum&FTS4AUX_LE_CONSTRAINT ){
          292  +    int iIdx = (idxNum&FTS4AUX_GE_CONSTRAINT) ? 1 : 0;
          293  +    pCsr->zStop = sqlite3_mprintf("%s", sqlite3_value_text(apVal[iIdx]));
          294  +    pCsr->nStop = sqlite3_value_bytes(apVal[iIdx]);
          295  +    if( pCsr->zStop==0 ) return SQLITE_NOMEM;
          296  +  }
   225    297   
   226         -  rc = sqlite3Fts3SegReaderCursor(pFts3, FTS3_SEGCURSOR_ALL, 0, 0,1,&pCsr->csr);
          298  +  rc = sqlite3Fts3SegReaderCursor(pFts3, FTS3_SEGCURSOR_ALL,
          299  +      pCsr->filter.zTerm, pCsr->filter.nTerm, 0, isScan, &pCsr->csr
          300  +  );
   227    301     if( rc==SQLITE_OK ){
   228    302       rc = sqlite3Fts3SegReaderStart(pFts3, &pCsr->csr, &pCsr->filter);
   229    303     }
   230    304   
   231    305     if( rc==SQLITE_OK ) rc = fts3auxNextMethod(pCursor);
   232    306     return rc;
   233    307   }

Changes to ext/fts3/fts3_write.c.

  2062   2062   ){
  2063   2063     int rc = SQLITE_OK;
  2064   2064   
  2065   2065     int isIgnoreEmpty =  (pCsr->pFilter->flags & FTS3_SEGMENT_IGNORE_EMPTY);
  2066   2066     int isRequirePos =   (pCsr->pFilter->flags & FTS3_SEGMENT_REQUIRE_POS);
  2067   2067     int isColFilter =    (pCsr->pFilter->flags & FTS3_SEGMENT_COLUMN_FILTER);
  2068   2068     int isPrefix =       (pCsr->pFilter->flags & FTS3_SEGMENT_PREFIX);
         2069  +  int isScan =         (pCsr->pFilter->flags & FTS3_SEGMENT_SCAN);
  2069   2070   
  2070   2071     Fts3SegReader **apSegment = pCsr->apSegment;
  2071   2072     int nSegment = pCsr->nSegment;
  2072   2073     Fts3SegFilter *pFilter = pCsr->pFilter;
  2073   2074   
  2074   2075     if( pCsr->nSegment==0 ) return SQLITE_OK;
  2075   2076   
................................................................................
  2097   2098       /* If this is a prefix-search, and if the term that apSegment[0] points
  2098   2099       ** to does not share a suffix with pFilter->zTerm/nTerm, then all 
  2099   2100       ** required callbacks have been made. In this case exit early.
  2100   2101       **
  2101   2102       ** Similarly, if this is a search for an exact match, and the first term
  2102   2103       ** of segment apSegment[0] is not a match, exit early.
  2103   2104       */
  2104         -    if( pFilter->zTerm ){
         2105  +    if( pFilter->zTerm && !isScan ){
  2105   2106         if( pCsr->nTerm<pFilter->nTerm 
  2106   2107          || (!isPrefix && pCsr->nTerm>pFilter->nTerm)
  2107   2108          || memcmp(pCsr->zTerm, pFilter->zTerm, pFilter->nTerm) 
  2108   2109         ){
  2109   2110           break;
  2110   2111         }
  2111   2112       }
................................................................................
  2224   2225     int rc;                         /* Return code */
  2225   2226     int iIdx;                       /* Index of new segment */
  2226   2227     int iNewLevel = 0;              /* Level to create new segment at */
  2227   2228     SegmentWriter *pWriter = 0;     /* Used to write the new, merged, segment */
  2228   2229     Fts3SegFilter filter;           /* Segment term filter condition */
  2229   2230     Fts3SegReaderCursor csr;        /* Cursor to iterate through level(s) */
  2230   2231   
  2231         -  rc = sqlite3Fts3SegReaderCursor(p, iLevel, 0, 0, 1, &csr);
         2232  +  rc = sqlite3Fts3SegReaderCursor(p, iLevel, 0, 0, 1, 0, &csr);
  2232   2233     if( rc!=SQLITE_OK || csr.nSegment==0 ) goto finished;
  2233   2234   
  2234   2235     if( iLevel==FTS3_SEGCURSOR_ALL ){
  2235   2236       /* This call is to merge all segments in the database to a single
  2236   2237       ** segment. The level of the new segment is equal to the the numerically 
  2237   2238       ** greatest segment level currently present in the database. The index
  2238   2239       ** of the new segment is always 0.  */

Changes to test/fts3aux1.test.

    51     51     INSERT INTO t1 SELECT * FROM t1;
    52     52     INSERT INTO t1 SELECT * FROM t1;
    53     53     INSERT INTO t1 SELECT * FROM t1;
    54     54     INSERT INTO t1 SELECT * FROM t1;
    55     55     INSERT INTO t1 SELECT * FROM t1;
    56     56     SELECT * FROM terms;
    57     57   } {a 256 1024    b 256 768}
           58  +
           59  +#-------------------------------------------------------------------------
           60  +# The following tests verify that the fts4aux module uses the full-text
           61  +# index to reduce the number of rows scanned in the following circumstances:
           62  +#
           63  +#   * when there is equality comparison against the term column using the 
           64  +#     BINARY collating sequence. 
           65  +#
           66  +#   * when there is a range constraint on the term column using the BINARY 
           67  +#     collating sequence. 
           68  +#
           69  +# And also uses the full-text index to optimize ORDER BY clauses of the 
           70  +# form "ORDER BY term ASC" or equivalent.
           71  +#
           72  +# Test organization is:
           73  +#
           74  +#   fts3aux1-2.1.*: equality constraints.
           75  +#   fts3aux1-2.2.*: range constraints.
           76  +#   fts3aux1-2.3.*: ORDER BY optimization.
           77  +# 
           78  +
           79  +do_execsql_test 2.0 {
           80  +  DROP TABLE t1;
           81  +  DROP TABLE terms;
           82  +
           83  +  CREATE VIRTUAL TABLE x1 USING fts4(x);
           84  +  INSERT INTO x1(x1) VALUES('nodesize=24');
           85  +  CREATE VIRTUAL TABLE terms USING fts4aux(x1);
           86  +
           87  +  INSERT INTO x1 VALUES('braes brag bragged bragger bragging');
           88  +  INSERT INTO x1 VALUES('brags braid braided braiding braids');
           89  +  INSERT INTO x1 VALUES('brain brainchild brained braining brains');
           90  +  INSERT INTO x1 VALUES('brainstem brainstems brainstorm brainstorms'); 
           91  +}
           92  +
           93  +proc rec {varname x} {
           94  +  global $varname
           95  +  incr $varname
           96  +  return 1
           97  +}
           98  +db func rec rec
           99  +
          100  +# Use EQP to show that the WHERE expression "term='braid'" uses a different
          101  +# index number (1) than "+term='braid'" (0).
          102  +#
          103  +do_execsql_test 2.1.1.1 {
          104  +  EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term='braid'
          105  +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~0 rows)} }
          106  +do_execsql_test 2.1.1.2 {
          107  +  EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term='braid'
          108  +} {0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)}}
          109  +
          110  +# Now show that using "term='braid'" means the virtual table returns
          111  +# only 1 row to SQLite, but "+term='braid'" means all 19 are returned.
          112  +#
          113  +do_test 2.1.2.1 {
          114  +  set cnt 0
          115  +  execsql { SELECT * FROM terms WHERE rec('cnt', term) AND term='braid' }
          116  +  set cnt
          117  +} {1}
          118  +do_test 2.1.2.2 {
          119  +  set cnt 0
          120  +  execsql { SELECT * FROM terms WHERE rec('cnt', term) AND +term='braid' }
          121  +  set cnt
          122  +} {19}
          123  +
          124  +# Similar to the test immediately above, but using a term ("breakfast") that 
          125  +# is not featured in the dataset.
          126  +#
          127  +do_test 2.1.3.1 {
          128  +  set cnt 0
          129  +  execsql { SELECT * FROM terms WHERE rec('cnt', term) AND term='breakfast' }
          130  +  set cnt
          131  +} {0}
          132  +do_test 2.1.3.2 {
          133  +  set cnt 0
          134  +  execsql { SELECT * FROM terms WHERE rec('cnt', term) AND +term='breakfast' }
          135  +  set cnt
          136  +} {19}
          137  +
          138  +do_execsql_test 2.1.4.1 { SELECT * FROM terms WHERE term='braid'  } {braid 1 1}
          139  +do_execsql_test 2.1.4.2 { SELECT * FROM terms WHERE +term='braid' } {braid 1 1}
          140  +do_execsql_test 2.1.4.3 { SELECT * FROM terms WHERE term='breakfast'  } {}
          141  +do_execsql_test 2.1.4.4 { SELECT * FROM terms WHERE +term='breakfast' } {}
          142  +
          143  +do_execsql_test 2.1.4.5 { SELECT * FROM terms WHERE term='cba'  } {}
          144  +do_execsql_test 2.1.4.6 { SELECT * FROM terms WHERE +term='cba' } {}
          145  +do_execsql_test 2.1.4.7 { SELECT * FROM terms WHERE term='abc'  } {}
          146  +do_execsql_test 2.1.4.8 { SELECT * FROM terms WHERE +term='abc' } {}
          147  +
          148  +do_execsql_test 2.2.1.1 {
          149  +  EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term>'brain'
          150  +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 2: (~0 rows)} }
          151  +do_execsql_test 2.2.1.2 {
          152  +  EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term>'brain'
          153  +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} }
          154  +
          155  +do_execsql_test 2.2.1.3 {
          156  +  EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term<'brain'
          157  +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 4: (~0 rows)} }
          158  +do_execsql_test 2.2.1.4 {
          159  +  EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term<'brain'
          160  +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} }
          161  +
          162  +do_execsql_test 2.2.1.5 {
          163  +  EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term BETWEEN 'brags' AND 'brain'
          164  +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 6: (~0 rows)} }
          165  +do_execsql_test 2.2.1.6 {
          166  +  EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term BETWEEN 'brags' AND 'brain'
          167  +} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} }
          168  +
          169  +do_test 2.2.2.1 {
          170  +  set cnt 0
          171  +  execsql { SELECT * FROM terms WHERE rec('cnt', term) AND term>'brain' }
          172  +  set cnt
          173  +} {9}
          174  +do_test 2.2.2.2 {
          175  +  set cnt 0
          176  +  execsql { SELECT * FROM terms WHERE rec('cnt', term) AND +term>'brain' }
          177  +  set cnt
          178  +} {19}
          179  +do_execsql_test 2.2.2.3 {
          180  +  SELECT * FROM terms WHERE rec('cnt', term) AND term>'brain'
          181  +} {
          182  +  brainchild 1 1 brained 1 1 braining 1 1 brains 1 1 
          183  +  brainstem 1 1 brainstems 1 1 brainstorm 1 1 brainstorms 1 1
          184  +}
          185  +do_execsql_test 2.2.2.4 {
          186  +  SELECT * FROM terms WHERE rec('cnt', term) AND +term>'brain'
          187  +} {
          188  +  brainchild 1 1 brained 1 1 braining 1 1 brains 1 1 
          189  +  brainstem 1 1 brainstems 1 1 brainstorm 1 1 brainstorms 1 1
          190  +}
          191  +do_execsql_test 2.2.2.5 {
          192  +  SELECT * FROM terms WHERE rec('cnt', term) AND term>='brain'
          193  +} {
          194  +  brain 1 1
          195  +  brainchild 1 1 brained 1 1 braining 1 1 brains 1 1 
          196  +  brainstem 1 1 brainstems 1 1 brainstorm 1 1 brainstorms 1 1
          197  +}
          198  +do_execsql_test 2.2.2.6 {
          199  +  SELECT * FROM terms WHERE rec('cnt', term) AND +term>='brain'
          200  +} {
          201  +  brain 1 1
          202  +  brainchild 1 1 brained 1 1 braining 1 1 brains 1 1 
          203  +  brainstem 1 1 brainstems 1 1 brainstorm 1 1 brainstorms 1 1
          204  +}
          205  +
          206  +do_execsql_test 2.2.2.7 {
          207  +  SELECT * FROM terms WHERE term>='abc'
          208  +} {
          209  +  braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 
          210  +  bragging 1 1 brags 1 1 braid 1 1 braided 1 1 
          211  +  braiding 1 1 braids 1 1 brain 1 1 brainchild 1 1 
          212  +  brained 1 1 braining 1 1 brains 1 1 brainstem 1 1 
          213  +  brainstems 1 1 brainstorm 1 1 brainstorms 1 1
          214  +}
          215  +do_execsql_test 2.2.2.8 {
          216  +  SELECT * FROM terms WHERE +term>='abc'
          217  +} {
          218  +  braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 
          219  +  bragging 1 1 brags 1 1 braid 1 1 braided 1 1 
          220  +  braiding 1 1 braids 1 1 brain 1 1 brainchild 1 1 
          221  +  brained 1 1 braining 1 1 brains 1 1 brainstem 1 1 
          222  +  brainstems 1 1 brainstorm 1 1 brainstorms 1 1
          223  +}
          224  +
          225  +do_execsql_test 2.2.2.9 {
          226  +  SELECT * FROM terms WHERE term>='brainstorms'
          227  +} {brainstorms 1 1}
          228  +do_execsql_test 2.2.2.10 {
          229  +  SELECT * FROM terms WHERE term>='brainstorms'
          230  +} {brainstorms 1 1}
          231  +do_execsql_test 2.2.2.11 { SELECT * FROM terms WHERE term>'brainstorms' } {}
          232  +do_execsql_test 2.2.2.12 { SELECT * FROM terms WHERE term>'brainstorms' } {}
          233  +
          234  +do_execsql_test 2.2.2.13 { SELECT * FROM terms WHERE term>'cba' } {}
          235  +do_execsql_test 2.2.2.14 { SELECT * FROM terms WHERE term>'cba' } {}
          236  +
          237  +do_test 2.2.3.1 {
          238  +  set cnt 0
          239  +  execsql { SELECT * FROM terms WHERE rec('cnt', term) AND term<'brain' }
          240  +  set cnt
          241  +} {11}
          242  +do_test 2.2.3.2 {
          243  +  set cnt 0
          244  +  execsql { SELECT * FROM terms WHERE rec('cnt', term) AND +term<'brain' }
          245  +  set cnt
          246  +} {19}
          247  +do_execsql_test 2.2.3.3 {
          248  +  SELECT * FROM terms WHERE rec('cnt', term) AND term<'brain'
          249  +} {
          250  +  braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 bragging 1 1 
          251  +  brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1
          252  +}
          253  +do_execsql_test 2.2.3.4 {
          254  +  SELECT * FROM terms WHERE rec('cnt', term) AND +term<'brain'
          255  +} {
          256  +  braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 bragging 1 1 
          257  +  brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1
          258  +}
          259  +do_execsql_test 2.2.3.5 {
          260  +  SELECT * FROM terms WHERE rec('cnt', term) AND term<='brain'
          261  +} {
          262  +  braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 bragging 1 1 
          263  +  brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1
          264  +  brain 1 1
          265  +}
          266  +do_execsql_test 2.2.3.6 {
          267  +  SELECT * FROM terms WHERE rec('cnt', term) AND +term<='brain'
          268  +} {
          269  +  braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 bragging 1 1 
          270  +  brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1
          271  +  brain 1 1
          272  +}
          273  +
          274  +do_test 2.2.4.1 {
          275  +  set cnt 0
          276  +  execsql { 
          277  +    SELECT * FROM terms 
          278  +    WHERE rec('cnt', term) AND term BETWEEN 'brags' AND 'brain' 
          279  +  }
          280  +  set cnt
          281  +} {6}
          282  +do_test 2.2.4.2 {
          283  +  set cnt 0
          284  +  execsql { 
          285  +    SELECT * FROM terms 
          286  +    WHERE rec('cnt', term) AND +term BETWEEN 'brags' AND 'brain' 
          287  +  }
          288  +  set cnt
          289  +} {19}
          290  +do_execsql_test 2.2.4.3 {
          291  +  SELECT * FROM terms 
          292  +  WHERE rec('cnt', term) AND term BETWEEN 'brags' AND 'brain' 
          293  +} {
          294  +  brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1 brain 1 1 
          295  +}
          296  +do_execsql_test 2.2.4.4 {
          297  +  SELECT * FROM terms 
          298  +  WHERE rec('cnt', term) AND +term BETWEEN 'brags' AND 'brain' 
          299  +} {
          300  +  brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1 brain 1 1 
          301  +}
          302  +do_execsql_test 2.2.4.5 {
          303  +  SELECT * FROM terms 
          304  +  WHERE rec('cnt', term) AND term > 'brags' AND term < 'brain' 
          305  +} {
          306  +  braid 1 1 braided 1 1 braiding 1 1 braids 1 1
          307  +}
          308  +do_execsql_test 2.2.4.6 {
          309  +  SELECT * FROM terms 
          310  +  WHERE rec('cnt', term) AND +term > 'brags' AND +term < 'brain' 
          311  +} {
          312  +  braid 1 1 braided 1 1 braiding 1 1 braids 1 1
          313  +}
          314  +
          315  +do_execsql_test 2.3.1.1 {
          316  +  EXPLAIN QUERY PLAN SELECT * FROM terms ORDER BY term ASC;
          317  +} {
          318  +  0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} 
          319  +}
          320  +do_execsql_test 2.3.1.2 {
          321  +  EXPLAIN QUERY PLAN SELECT * FROM terms ORDER BY term DESC;
          322  +} {
          323  +  0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} 
          324  +  0 0 0 {USE TEMP B-TREE FOR ORDER BY}
          325  +}
    58    326   
    59    327   finish_test