/ Check-in [3996f92a]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Change fts4aux to (additionally) report on term frequency in individual columns of an fts table.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 3996f92a9aca9ac2628be003eca83c8f954c71de
User & Date: dan 2011-02-04 18:56:25
Context
2011-02-05
14:37
Add test cases for fts4aux. Fix a bug affecting fts3 tables with multiple columns. check-in: dc511e60 user: dan tags: trunk
2011-02-04
18:56
Change fts4aux to (additionally) report on term frequency in individual columns of an fts table. check-in: 3996f92a user: dan tags: trunk
14:28
Significant changes have occurred since the 3.7.5 release so go ahead and increase the version number to 3.7.6. check-in: 999c915a user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to ext/fts3/fts3_aux.c.

27
28
29
30
31
32
33
34
35
36




37
38

39
40
41
42
43
44
45
46
47
48
49
50
51
...
195
196
197
198
199
200
201

202
203
204
















205
206
207
208
209
210
211
212







213
214
215
216
217
218
219



220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235

236
237

238

239



240












241
242
243
244
245

246


247








248













249
250
251
252
253
254
255
256
257
...
276
277
278
279
280
281
282

283
284
285
286
287
288
289
...
328
329
330
331
332
333
334
335






336
337
338
339
340
341
342
343
344
345
};

struct Fts3auxCursor {
  sqlite3_vtab_cursor base;       /* Base class used by SQLite core */
  Fts3SegReaderCursor csr;        /* Must be right after "base" */
  Fts3SegFilter filter;
  char *zStop;
  int nStop;
  int isEof;
  sqlite3_int64 iRowid;




  sqlite3_int64 nDoc;
  sqlite3_int64 nOcc;

};

/*
** Schema of the terms table.
*/
#define FTS3_TERMS_SCHEMA "CREATE TABLE x(term, documents, occurrences)"

/*
** This function does all the work for both the xConnect and xCreate methods.
** These tables have no persistent representation of their own, so xConnect
** and xCreate are identical operations.
*/
static int fts3auxConnectMethod(
................................................................................
  Fts3Table *pFts3 = ((Fts3auxTable *)pCursor->pVtab)->pFts3Tab;
  Fts3auxCursor *pCsr = (Fts3auxCursor *)pCursor;

  sqlite3Fts3SegmentsClose(pFts3);
  sqlite3Fts3SegReaderFinish(&pCsr->csr);
  sqlite3_free((void *)pCsr->filter.zTerm);
  sqlite3_free(pCsr->zStop);

  sqlite3_free(pCsr);
  return SQLITE_OK;
}

















/*
** xNext - Advance the cursor to the next row, if any.
*/
static int fts3auxNextMethod(sqlite3_vtab_cursor *pCursor){
  Fts3auxCursor *pCsr = (Fts3auxCursor *)pCursor;
  Fts3Table *pFts3 = ((Fts3auxTable *)pCursor->pVtab)->pFts3Tab;
  int rc;








  rc = sqlite3Fts3SegReaderStep(pFts3, &pCsr->csr);
  if( rc==SQLITE_ROW ){
    int i;
    int isIgnore = 1;
    int nDoclist = pCsr->csr.nDoclist;
    char *aDoclist = pCsr->csr.aDoclist;




    if( pCsr->zStop ){
      int n = (pCsr->nStop<pCsr->csr.nTerm) ? pCsr->nStop : pCsr->csr.nTerm;
      int mc = memcmp(pCsr->zStop, pCsr->csr.zTerm, n);
      if( mc<0 || (mc==0 && pCsr->csr.nTerm>pCsr->nStop) ){
        pCsr->isEof = 1;
        return SQLITE_OK;
      }
    }

    /* Now count the number of documents and positions in the doclist
    ** in pCsr->csr.aDoclist[]. Store the number of documents in pCsr->nDoc
    ** and the number of occurrences in pCsr->nOcc.  */
    pCsr->nDoc = 0;
    pCsr->nOcc = 0;
    i = 0;

    while( i<nDoclist ){
      sqlite3_int64 v = 0;

      i += sqlite3Fts3GetVarint(&aDoclist[i], &v);

      if( isIgnore ){



        isIgnore = 0;












      }else if( v>1 ){
        pCsr->nOcc++;
      }else{
        if( v==0 ) pCsr->nDoc++;
        isIgnore = 1;

      }


    }






















    rc = SQLITE_OK;
    pCsr->iRowid++;
  }else{
    pCsr->isEof = 1;
  }
  return rc;
}

/*
................................................................................
  );
  isScan = (idxNum!=FTS4AUX_EQ_CONSTRAINT);

  /* In case this cursor is being reused, close and zero it. */
  testcase(pCsr->filter.zTerm);
  sqlite3Fts3SegReaderFinish(&pCsr->csr);
  sqlite3_free((void *)pCsr->filter.zTerm);

  memset(&pCsr->csr, 0, ((u8*)&pCsr[1]) - (u8*)&pCsr->csr);

  pCsr->filter.flags = FTS3_SEGMENT_REQUIRE_POS|FTS3_SEGMENT_IGNORE_EMPTY;
  if( isScan ) pCsr->filter.flags |= FTS3_SEGMENT_SCAN;

  if( idxNum&(FTS4AUX_EQ_CONSTRAINT|FTS4AUX_GE_CONSTRAINT) ){
    const unsigned char *zStr = sqlite3_value_text(apVal[0]);
................................................................................
  int iCol                        /* Index of column to read value from */
){
  Fts3auxCursor *p = (Fts3auxCursor *)pCursor;

  assert( p->isEof==0 );
  if( iCol==0 ){        /* Column "term" */
    sqlite3_result_text(pContext, p->csr.zTerm, p->csr.nTerm, SQLITE_TRANSIENT);
  }else if( iCol==1 ){  /* Column "documents" */






    sqlite3_result_int64(pContext, p->nDoc);
  }else{                /* Column "occurrences" */
    sqlite3_result_int64(pContext, p->nOcc);
  }

  return SQLITE_OK;
}

/*
** xRowid - Return the current rowid for the cursor.







|
|
|
>
>
>
>
|
|
>





|







 







>



>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>








>
>
>
>
>
>
>



|
<


>
>
>










|
|
<
<
<
|
>


>

>
|
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
|
<
<
<
<
>
|
>
>
|
>
>
>
>
>
>
>
>
|
>
>
>
>
>
>
>
>
>
>
>
>
>

<







 







>







 







|
>
>
>
>
>
>
|

|







27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
...
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245

246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262



263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287




288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315

316
317
318
319
320
321
322
...
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
...
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
};

struct Fts3auxCursor {
  sqlite3_vtab_cursor base;       /* Base class used by SQLite core */
  Fts3SegReaderCursor csr;        /* Must be right after "base" */
  Fts3SegFilter filter;
  char *zStop;
  int nStop;                      /* Byte-length of string zStop */
  int isEof;                      /* True if cursor is at EOF */
  sqlite3_int64 iRowid;           /* Current rowid */

  int iCol;                       /* Current value of 'col' column */
  int nStat;                      /* Size of aStat[] array */
  struct Fts3auxColstats {
    sqlite3_int64 nDoc;           /* 'documents' values for current csr row */
    sqlite3_int64 nOcc;           /* 'occurrences' values for current csr row */
  } *aStat;
};

/*
** Schema of the terms table.
*/
#define FTS3_TERMS_SCHEMA "CREATE TABLE x(term, col, documents, occurrences)"

/*
** This function does all the work for both the xConnect and xCreate methods.
** These tables have no persistent representation of their own, so xConnect
** and xCreate are identical operations.
*/
static int fts3auxConnectMethod(
................................................................................
  Fts3Table *pFts3 = ((Fts3auxTable *)pCursor->pVtab)->pFts3Tab;
  Fts3auxCursor *pCsr = (Fts3auxCursor *)pCursor;

  sqlite3Fts3SegmentsClose(pFts3);
  sqlite3Fts3SegReaderFinish(&pCsr->csr);
  sqlite3_free((void *)pCsr->filter.zTerm);
  sqlite3_free(pCsr->zStop);
  sqlite3_free(pCsr->aStat);
  sqlite3_free(pCsr);
  return SQLITE_OK;
}

static int fts3auxGrowStatArray(Fts3auxCursor *pCsr, int nSize){
  if( nSize>pCsr->nStat ){
    struct Fts3auxColstats *aNew;
    aNew = (struct Fts3auxColstats *)sqlite3_realloc(pCsr->aStat, 
        sizeof(struct Fts3auxColstats) * nSize
    );
    if( aNew==0 ) return SQLITE_NOMEM;
    memset(&aNew[pCsr->nStat], 0, 
        sizeof(struct Fts3auxColstats) * (nSize - pCsr->nStat)
    );
    pCsr->aStat = aNew;
    pCsr->nStat = nSize;
  }
  return SQLITE_OK;
}

/*
** xNext - Advance the cursor to the next row, if any.
*/
static int fts3auxNextMethod(sqlite3_vtab_cursor *pCursor){
  Fts3auxCursor *pCsr = (Fts3auxCursor *)pCursor;
  Fts3Table *pFts3 = ((Fts3auxTable *)pCursor->pVtab)->pFts3Tab;
  int rc;

  /* Increment our pretend rowid value. */
  pCsr->iRowid++;

  for(pCsr->iCol++; pCsr->iCol<pCsr->nStat; pCsr->iCol++){
    if( pCsr->aStat[pCsr->iCol].nDoc>0 ) return SQLITE_OK;
  }

  rc = sqlite3Fts3SegReaderStep(pFts3, &pCsr->csr);
  if( rc==SQLITE_ROW ){
    int i = 0;

    int nDoclist = pCsr->csr.nDoclist;
    char *aDoclist = pCsr->csr.aDoclist;
    int iCol;

    int eState = 0;

    if( pCsr->zStop ){
      int n = (pCsr->nStop<pCsr->csr.nTerm) ? pCsr->nStop : pCsr->csr.nTerm;
      int mc = memcmp(pCsr->zStop, pCsr->csr.zTerm, n);
      if( mc<0 || (mc==0 && pCsr->csr.nTerm>pCsr->nStop) ){
        pCsr->isEof = 1;
        return SQLITE_OK;
      }
    }

    if( fts3auxGrowStatArray(pCsr, 2) ) return SQLITE_NOMEM;
    memset(pCsr->aStat, 0, sizeof(struct Fts3auxColstats) * pCsr->nStat);



    iCol = 0;

    while( i<nDoclist ){
      sqlite3_int64 v = 0;

      i += sqlite3Fts3GetVarint(&aDoclist[i], &v);
      switch( eState ){
        /* State 0. In this state the integer just read was a docid. */
        case 0:
          pCsr->aStat[0].nDoc++;
          eState = 1;
          iCol = 0;
          break;

        /* State 1. In this state we are expecting either a 1, indicating
        ** that the following integer will be a column number, or the
        ** start of a position list for column 0.  
        ** 
        ** The only difference between state 1 and state 2 is that if the
        ** integer encountered in state 1 is not 0 or 1, then we need to
        ** increment the column 0 "nDoc" count for this term.
        */
        case 1:
          assert( iCol==0 );
          if( v>1 ){




            pCsr->aStat[1].nDoc++;
          }
          eState = 2;
          /* fall through */

        case 2:
          if( v==0 ){       /* 0x00. Next integer will be a docid. */
            eState = 0;
          }else if( v==1 ){ /* 0x01. Next integer will be a column number. */
            eState = 3;
          }else{            /* 2 or greater. A position. */
            pCsr->aStat[iCol+1].nOcc++;
            pCsr->aStat[0].nOcc++;
          }
          break;

        /* State 3. The integer just read is a column number. */
        case 3:
          iCol = (int)v;
          if( fts3auxGrowStatArray(pCsr, iCol+2) ) return SQLITE_NOMEM;
          pCsr->aStat[iCol+1].nDoc++;
          eState = 2;
          break;
      }
    }

    pCsr->iCol = 0;
    rc = SQLITE_OK;

  }else{
    pCsr->isEof = 1;
  }
  return rc;
}

/*
................................................................................
  );
  isScan = (idxNum!=FTS4AUX_EQ_CONSTRAINT);

  /* In case this cursor is being reused, close and zero it. */
  testcase(pCsr->filter.zTerm);
  sqlite3Fts3SegReaderFinish(&pCsr->csr);
  sqlite3_free((void *)pCsr->filter.zTerm);
  sqlite3_free(pCsr->aStat);
  memset(&pCsr->csr, 0, ((u8*)&pCsr[1]) - (u8*)&pCsr->csr);

  pCsr->filter.flags = FTS3_SEGMENT_REQUIRE_POS|FTS3_SEGMENT_IGNORE_EMPTY;
  if( isScan ) pCsr->filter.flags |= FTS3_SEGMENT_SCAN;

  if( idxNum&(FTS4AUX_EQ_CONSTRAINT|FTS4AUX_GE_CONSTRAINT) ){
    const unsigned char *zStr = sqlite3_value_text(apVal[0]);
................................................................................
  int iCol                        /* Index of column to read value from */
){
  Fts3auxCursor *p = (Fts3auxCursor *)pCursor;

  assert( p->isEof==0 );
  if( iCol==0 ){        /* Column "term" */
    sqlite3_result_text(pContext, p->csr.zTerm, p->csr.nTerm, SQLITE_TRANSIENT);
  }else if( iCol==1 ){  /* Column "col" */
    if( p->iCol ){
      sqlite3_result_int(pContext, p->iCol-1);
    }else{
      sqlite3_result_text(pContext, "*", -1, SQLITE_STATIC);
    }
  }else if( iCol==2 ){  /* Column "documents" */
    sqlite3_result_int64(pContext, p->aStat[p->iCol].nDoc);
  }else{                /* Column "occurrences" */
    sqlite3_result_int64(pContext, p->aStat[p->iCol].nOcc);
  }

  return SQLITE_OK;
}

/*
** xRowid - Return the current rowid for the cursor.

Changes to ext/fts3/fts3_write.c.

2219
2220
2221
2222
2223
2224
2225
2226
2227
2228
2229
2230
2231
2232
2233
....
2238
2239
2240
2241
2242
2243
2244
2245
2246
2247
2248
2249
2250
2251
2252
** If this function is called with iLevel<0, but there is only one
** segment in the database, SQLITE_DONE is returned immediately. 
** Otherwise, if successful, SQLITE_OK is returned. If an error occurs, 
** an SQLite error code is returned.
*/
static int fts3SegmentMerge(Fts3Table *p, int iLevel){
  int rc;                         /* Return code */
  int iIdx;                       /* Index of new segment */
  int iNewLevel = 0;              /* Level to create new segment at */
  SegmentWriter *pWriter = 0;     /* Used to write the new, merged, segment */
  Fts3SegFilter filter;           /* Segment term filter condition */
  Fts3SegReaderCursor csr;        /* Cursor to iterate through level(s) */

  rc = sqlite3Fts3SegReaderCursor(p, iLevel, 0, 0, 1, 0, &csr);
  if( rc!=SQLITE_OK || csr.nSegment==0 ) goto finished;
................................................................................
    ** greatest segment level currently present in the database. The index
    ** of the new segment is always 0.  */
    int nDummy; /* TODO: Remove this */
    if( csr.nSegment==1 ){
      rc = SQLITE_DONE;
      goto finished;
    }
    iIdx = 0;
    rc = fts3SegmentCountMax(p, &nDummy, &iNewLevel);
  }else{
    /* This call is to merge all segments at level iLevel. Find the next
    ** available segment index at level iLevel+1. The call to
    ** fts3AllocateSegdirIdx() will merge the segments at level iLevel+1 to 
    ** a single iLevel+2 segment if necessary.  */
    iNewLevel = iLevel+1;







|







 







<







2219
2220
2221
2222
2223
2224
2225
2226
2227
2228
2229
2230
2231
2232
2233
....
2238
2239
2240
2241
2242
2243
2244

2245
2246
2247
2248
2249
2250
2251
** If this function is called with iLevel<0, but there is only one
** segment in the database, SQLITE_DONE is returned immediately. 
** Otherwise, if successful, SQLITE_OK is returned. If an error occurs, 
** an SQLite error code is returned.
*/
static int fts3SegmentMerge(Fts3Table *p, int iLevel){
  int rc;                         /* Return code */
  int iIdx = 0;                   /* Index of new segment */
  int iNewLevel = 0;              /* Level to create new segment at */
  SegmentWriter *pWriter = 0;     /* Used to write the new, merged, segment */
  Fts3SegFilter filter;           /* Segment term filter condition */
  Fts3SegReaderCursor csr;        /* Cursor to iterate through level(s) */

  rc = sqlite3Fts3SegReaderCursor(p, iLevel, 0, 0, 1, 0, &csr);
  if( rc!=SQLITE_OK || csr.nSegment==0 ) goto finished;
................................................................................
    ** greatest segment level currently present in the database. The index
    ** of the new segment is always 0.  */
    int nDummy; /* TODO: Remove this */
    if( csr.nSegment==1 ){
      rc = SQLITE_DONE;
      goto finished;
    }

    rc = fts3SegmentCountMax(p, &nDummy, &iNewLevel);
  }else{
    /* This call is to merge all segments at level iLevel. Find the next
    ** available segment index at level iLevel+1. The call to
    ** fts3AllocateSegdirIdx() will merge the segments at level iLevel+1 to 
    ** a single iLevel+2 segment if necessary.  */
    iNewLevel = iLevel+1;

Changes to test/fts3aux1.test.

20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
..
80
81
82
83
84
85
86



87
88
89
90
91
92
93
...
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
...
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
...
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
do_execsql_test 1.1 {
  CREATE VIRTUAL TABLE t1 USING fts4;
  INSERT INTO t1 VALUES('one two three four');
  INSERT INTO t1 VALUES('three four five six');
  INSERT INTO t1 VALUES('one three five seven');

  CREATE VIRTUAL TABLE terms USING fts4aux(t1);
  SELECT * FROM terms;
} {
  five  2 2     four  2 2     one   2 2     seven 1 1 
  six   1 1     three 3 3     two   1 1
}

do_execsql_test 1.2 {
  INSERT INTO t1 VALUES('one one one three three three');
  SELECT * FROM terms;
} { 
  five  2 2     four  2 2     one   3 5     seven 1 1 
  six   1 1     three 4 6     two   1 1
}

do_execsql_test 1.3 {
  DELETE FROM t1;
  SELECT * FROM terms;
} {}

do_execsql_test 1.4 {
  INSERT INTO t1 VALUES('a b a b a b a');
  INSERT INTO t1 SELECT * FROM t1;
  INSERT INTO t1 SELECT * FROM t1;
  INSERT INTO t1 SELECT * FROM t1;
  INSERT INTO t1 SELECT * FROM t1;
  INSERT INTO t1 SELECT * FROM t1;
  INSERT INTO t1 SELECT * FROM t1;
  INSERT INTO t1 SELECT * FROM t1;
  INSERT INTO t1 SELECT * FROM t1;
  SELECT * FROM terms;
} {a 256 1024    b 256 768}

#-------------------------------------------------------------------------
# The following tests verify that the fts4aux module uses the full-text
# index to reduce the number of rows scanned in the following circumstances:
#
#   * when there is equality comparison against the term column using the 
................................................................................
  DROP TABLE t1;
  DROP TABLE terms;

  CREATE VIRTUAL TABLE x1 USING fts4(x);
  INSERT INTO x1(x1) VALUES('nodesize=24');
  CREATE VIRTUAL TABLE terms USING fts4aux(x1);




  INSERT INTO x1 VALUES('braes brag bragged bragger bragging');
  INSERT INTO x1 VALUES('brags braid braided braiding braids');
  INSERT INTO x1 VALUES('brain brainchild brained braining brains');
  INSERT INTO x1 VALUES('brainstem brainstems brainstorm brainstorms'); 
}

proc rec {varname x} {
................................................................................
} {0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)}}

# Now show that using "term='braid'" means the virtual table returns
# only 1 row to SQLite, but "+term='braid'" means all 19 are returned.
#
do_test 2.1.2.1 {
  set cnt 0
  execsql { SELECT * FROM terms WHERE rec('cnt', term) AND term='braid' }
  set cnt
} {1}
do_test 2.1.2.2 {
  set cnt 0
  execsql { SELECT * FROM terms WHERE rec('cnt', term) AND +term='braid' }
  set cnt
} {19}

# Similar to the test immediately above, but using a term ("breakfast") that 
# is not featured in the dataset.
#
do_test 2.1.3.1 {
  set cnt 0
  execsql { SELECT * FROM terms WHERE rec('cnt', term) AND term='breakfast' }
  set cnt
} {0}
do_test 2.1.3.2 {
  set cnt 0
  execsql { SELECT * FROM terms WHERE rec('cnt', term) AND +term='breakfast' }
  set cnt
} {19}

do_execsql_test 2.1.4.1 { SELECT * FROM terms WHERE term='braid'  } {braid 1 1}
do_execsql_test 2.1.4.2 { SELECT * FROM terms WHERE +term='braid' } {braid 1 1}
do_execsql_test 2.1.4.3 { SELECT * FROM terms WHERE term='breakfast'  } {}
do_execsql_test 2.1.4.4 { SELECT * FROM terms WHERE +term='breakfast' } {}

do_execsql_test 2.1.4.5 { SELECT * FROM terms WHERE term='cba'  } {}
do_execsql_test 2.1.4.6 { SELECT * FROM terms WHERE +term='cba' } {}
do_execsql_test 2.1.4.7 { SELECT * FROM terms WHERE term='abc'  } {}
do_execsql_test 2.1.4.8 { SELECT * FROM terms WHERE +term='abc' } {}

# Special case: term=NULL
#
do_execsql_test 2.1.5 { SELECT * FROM terms WHERE term=NULL } {}

do_execsql_test 2.2.1.1 {
  EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term>'brain'
................................................................................
  EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term BETWEEN 'brags' AND 'brain'
} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} }

do_test 2.2.2.1 {
  set cnt 0
  execsql { SELECT * FROM terms WHERE rec('cnt', term) AND term>'brain' }
  set cnt
} {9}
do_test 2.2.2.2 {
  set cnt 0
  execsql { SELECT * FROM terms WHERE rec('cnt', term) AND +term>'brain' }
  set cnt
} {19}
do_execsql_test 2.2.2.3 {
  SELECT * FROM terms WHERE rec('cnt', term) AND term>'brain'
} {
  brainchild 1 1 brained 1 1 braining 1 1 brains 1 1 
  brainstem 1 1 brainstems 1 1 brainstorm 1 1 brainstorms 1 1
}
do_execsql_test 2.2.2.4 {
  SELECT * FROM terms WHERE rec('cnt', term) AND +term>'brain'
} {
  brainchild 1 1 brained 1 1 braining 1 1 brains 1 1 
  brainstem 1 1 brainstems 1 1 brainstorm 1 1 brainstorms 1 1
}
do_execsql_test 2.2.2.5 {
  SELECT * FROM terms WHERE rec('cnt', term) AND term>='brain'
} {
  brain 1 1
  brainchild 1 1 brained 1 1 braining 1 1 brains 1 1 
  brainstem 1 1 brainstems 1 1 brainstorm 1 1 brainstorms 1 1
}
do_execsql_test 2.2.2.6 {
  SELECT * FROM terms WHERE rec('cnt', term) AND +term>='brain'
} {
  brain 1 1
  brainchild 1 1 brained 1 1 braining 1 1 brains 1 1 
  brainstem 1 1 brainstems 1 1 brainstorm 1 1 brainstorms 1 1
}

do_execsql_test 2.2.2.7 {
  SELECT * FROM terms WHERE term>='abc'
} {
  braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 
  bragging 1 1 brags 1 1 braid 1 1 braided 1 1 
  braiding 1 1 braids 1 1 brain 1 1 brainchild 1 1 
  brained 1 1 braining 1 1 brains 1 1 brainstem 1 1 
  brainstems 1 1 brainstorm 1 1 brainstorms 1 1
}
do_execsql_test 2.2.2.8 {
  SELECT * FROM terms WHERE +term>='abc'
} {
  braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 
  bragging 1 1 brags 1 1 braid 1 1 braided 1 1 
  braiding 1 1 braids 1 1 brain 1 1 brainchild 1 1 
  brained 1 1 braining 1 1 brains 1 1 brainstem 1 1 
  brainstems 1 1 brainstorm 1 1 brainstorms 1 1
}

do_execsql_test 2.2.2.9 {
  SELECT * FROM terms WHERE term>='brainstorms'
} {brainstorms 1 1}
do_execsql_test 2.2.2.10 {
  SELECT * FROM terms WHERE term>='brainstorms'
} {brainstorms 1 1}
do_execsql_test 2.2.2.11 { SELECT * FROM terms WHERE term>'brainstorms' } {}
do_execsql_test 2.2.2.12 { SELECT * FROM terms WHERE term>'brainstorms' } {}

do_execsql_test 2.2.2.13 { SELECT * FROM terms WHERE term>'cba' } {}
do_execsql_test 2.2.2.14 { SELECT * FROM terms WHERE term>'cba' } {}

do_test 2.2.3.1 {
  set cnt 0
  execsql { SELECT * FROM terms WHERE rec('cnt', term) AND term<'brain' }
  set cnt
} {11}
do_test 2.2.3.2 {
  set cnt 0
  execsql { SELECT * FROM terms WHERE rec('cnt', term) AND +term<'brain' }
  set cnt
} {19}
do_execsql_test 2.2.3.3 {
  SELECT * FROM terms WHERE rec('cnt', term) AND term<'brain'
} {
  braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 bragging 1 1 
  brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1
}
do_execsql_test 2.2.3.4 {
  SELECT * FROM terms WHERE rec('cnt', term) AND +term<'brain'
} {
  braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 bragging 1 1 
  brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1
}
do_execsql_test 2.2.3.5 {
  SELECT * FROM terms WHERE rec('cnt', term) AND term<='brain'
} {
  braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 bragging 1 1 
  brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1
  brain 1 1
}
do_execsql_test 2.2.3.6 {
  SELECT * FROM terms WHERE rec('cnt', term) AND +term<='brain'
} {
  braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 bragging 1 1 
  brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1
  brain 1 1
}

do_test 2.2.4.1 {
  set cnt 0
  execsql { 
    SELECT * FROM terms 
    WHERE rec('cnt', term) AND term BETWEEN 'brags' AND 'brain' 
  }
  set cnt
} {6}
do_test 2.2.4.2 {
  set cnt 0
  execsql { 
    SELECT * FROM terms 
    WHERE rec('cnt', term) AND +term BETWEEN 'brags' AND 'brain' 
  }
  set cnt
} {19}
do_execsql_test 2.2.4.3 {
  SELECT * FROM terms 
  WHERE rec('cnt', term) AND term BETWEEN 'brags' AND 'brain' 
} {
  brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1 brain 1 1 
}
do_execsql_test 2.2.4.4 {
  SELECT * FROM terms 
  WHERE rec('cnt', term) AND +term BETWEEN 'brags' AND 'brain' 
} {
  brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1 brain 1 1 
}
do_execsql_test 2.2.4.5 {
  SELECT * FROM terms 
  WHERE rec('cnt', term) AND term > 'brags' AND term < 'brain' 
} {
  braid 1 1 braided 1 1 braiding 1 1 braids 1 1
}
do_execsql_test 2.2.4.6 {
  SELECT * FROM terms 
  WHERE rec('cnt', term) AND +term > 'brags' AND +term < 'brain' 
} {
  braid 1 1 braided 1 1 braiding 1 1 braids 1 1
}

# Check that "ORDER BY term ASC" and equivalents are sorted by the
# virtual table implementation. Any other ORDER BY clause requires
................................................................................
  CREATE TABLE x3(y);
  CREATE INDEX i1 ON x3(y);

  INSERT INTO x1 VALUES('a b c d e');
  INSERT INTO x1 VALUES('f g h i j');
  INSERT INTO x1 VALUES('k k l l a');

  INSERT INTO x2 SELECT term FROM terms;
  INSERT INTO x3 SELECT term FROM terms;
}

proc do_plansql_test {tn sql r} {
  uplevel do_execsql_test $tn [list "EXPLAIN QUERY PLAN $sql ; $sql"] [list $r]
}


do_plansql_test 4.2 {
  SELECT y FROM x2, terms WHERE y = term
} {
  0 0 0 {SCAN TABLE x2 (~1000000 rows)} 
  0 1 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~0 rows)} 
  a b c d e f g h i j k l
}

do_plansql_test 4.3 {
  SELECT y FROM terms, x2 WHERE y = term
} {
  0 0 1 {SCAN TABLE x2 (~1000000 rows)} 
  0 1 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~0 rows)} 
  a b c d e f g h i j k l
}

do_plansql_test 4.4 {
  SELECT y FROM x3, terms WHERE y = term
} {
  0 0 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} 
  0 1 0 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?) (~10 rows)}
  a b c d e f g h i j k l
}

do_plansql_test 4.5 {
  SELECT y FROM terms, x3 WHERE y = term AND occurrences>1
} {
  0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} 
  0 1 1 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?) (~10 rows)}
  a k l
}


finish_test







|







|







|












|







 







>
>
>







 







|

|


|

|






|




|

|

|
|
|
|

|
|
|
|







 







|




|

|





|





|






|







|








|









|


|

|
|

|
|





|




|

|





|





|






|









|



|



|



|

|





|





|





|







 







|
|








|







|







|







|








20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
..
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
...
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
...
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
...
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
do_execsql_test 1.1 {
  CREATE VIRTUAL TABLE t1 USING fts4;
  INSERT INTO t1 VALUES('one two three four');
  INSERT INTO t1 VALUES('three four five six');
  INSERT INTO t1 VALUES('one three five seven');

  CREATE VIRTUAL TABLE terms USING fts4aux(t1);
  SELECT term, documents, occurrences FROM terms WHERE col = '*';
} {
  five  2 2     four  2 2     one   2 2     seven 1 1 
  six   1 1     three 3 3     two   1 1
}

do_execsql_test 1.2 {
  INSERT INTO t1 VALUES('one one one three three three');
  SELECT term, documents, occurrences FROM terms WHERE col = '*';
} { 
  five  2 2     four  2 2     one   3 5     seven 1 1 
  six   1 1     three 4 6     two   1 1
}

do_execsql_test 1.3 {
  DELETE FROM t1;
  SELECT term, documents, occurrences FROM terms WHERE col = '*';
} {}

do_execsql_test 1.4 {
  INSERT INTO t1 VALUES('a b a b a b a');
  INSERT INTO t1 SELECT * FROM t1;
  INSERT INTO t1 SELECT * FROM t1;
  INSERT INTO t1 SELECT * FROM t1;
  INSERT INTO t1 SELECT * FROM t1;
  INSERT INTO t1 SELECT * FROM t1;
  INSERT INTO t1 SELECT * FROM t1;
  INSERT INTO t1 SELECT * FROM t1;
  INSERT INTO t1 SELECT * FROM t1;
  SELECT term, documents, occurrences FROM terms WHERE col = '*';
} {a 256 1024    b 256 768}

#-------------------------------------------------------------------------
# The following tests verify that the fts4aux module uses the full-text
# index to reduce the number of rows scanned in the following circumstances:
#
#   * when there is equality comparison against the term column using the 
................................................................................
  DROP TABLE t1;
  DROP TABLE terms;

  CREATE VIRTUAL TABLE x1 USING fts4(x);
  INSERT INTO x1(x1) VALUES('nodesize=24');
  CREATE VIRTUAL TABLE terms USING fts4aux(x1);

  CREATE VIEW terms_v AS 
  SELECT term, documents, occurrences FROM terms WHERE col = '*';

  INSERT INTO x1 VALUES('braes brag bragged bragger bragging');
  INSERT INTO x1 VALUES('brags braid braided braiding braids');
  INSERT INTO x1 VALUES('brain brainchild brained braining brains');
  INSERT INTO x1 VALUES('brainstem brainstems brainstorm brainstorms'); 
}

proc rec {varname x} {
................................................................................
} {0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)}}

# Now show that using "term='braid'" means the virtual table returns
# only 1 row to SQLite, but "+term='braid'" means all 19 are returned.
#
do_test 2.1.2.1 {
  set cnt 0
  execsql { SELECT * FROM terms_v WHERE rec('cnt', term) AND term='braid' }
  set cnt
} {2}
do_test 2.1.2.2 {
  set cnt 0
  execsql { SELECT * FROM terms_v WHERE rec('cnt', term) AND +term='braid' }
  set cnt
} {38}

# Similar to the test immediately above, but using a term ("breakfast") that 
# is not featured in the dataset.
#
do_test 2.1.3.1 {
  set cnt 0
  execsql { SELECT * FROM terms_v WHERE rec('cnt', term) AND term='breakfast' }
  set cnt
} {0}
do_test 2.1.3.2 {
  set cnt 0
  execsql { SELECT * FROM terms_v WHERE rec('cnt', term) AND +term='breakfast' }
  set cnt
} {38}

do_execsql_test 2.1.4.1 { SELECT * FROM terms_v WHERE term='braid' } {braid 1 1}
do_execsql_test 2.1.4.2 { SELECT * FROM terms_v WHERE +term='braid'} {braid 1 1}
do_execsql_test 2.1.4.3 { SELECT * FROM terms_v WHERE term='breakfast'  } {}
do_execsql_test 2.1.4.4 { SELECT * FROM terms_v WHERE +term='breakfast' } {}

do_execsql_test 2.1.4.5 { SELECT * FROM terms_v WHERE term='cba'  } {}
do_execsql_test 2.1.4.6 { SELECT * FROM terms_v WHERE +term='cba' } {}
do_execsql_test 2.1.4.7 { SELECT * FROM terms_v WHERE term='abc'  } {}
do_execsql_test 2.1.4.8 { SELECT * FROM terms_v WHERE +term='abc' } {}

# Special case: term=NULL
#
do_execsql_test 2.1.5 { SELECT * FROM terms WHERE term=NULL } {}

do_execsql_test 2.2.1.1 {
  EXPLAIN QUERY PLAN SELECT * FROM terms WHERE term>'brain'
................................................................................
  EXPLAIN QUERY PLAN SELECT * FROM terms WHERE +term BETWEEN 'brags' AND 'brain'
} { 0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} }

do_test 2.2.2.1 {
  set cnt 0
  execsql { SELECT * FROM terms WHERE rec('cnt', term) AND term>'brain' }
  set cnt
} {18}
do_test 2.2.2.2 {
  set cnt 0
  execsql { SELECT * FROM terms WHERE rec('cnt', term) AND +term>'brain' }
  set cnt
} {38}
do_execsql_test 2.2.2.3 {
  SELECT term, documents, occurrences FROM terms_v WHERE term>'brain'
} {
  brainchild 1 1 brained 1 1 braining 1 1 brains 1 1 
  brainstem 1 1 brainstems 1 1 brainstorm 1 1 brainstorms 1 1
}
do_execsql_test 2.2.2.4 {
  SELECT term, documents, occurrences FROM terms_v WHERE +term>'brain'
} {
  brainchild 1 1 brained 1 1 braining 1 1 brains 1 1 
  brainstem 1 1 brainstems 1 1 brainstorm 1 1 brainstorms 1 1
}
do_execsql_test 2.2.2.5 {
  SELECT term, documents, occurrences FROM terms_v WHERE term>='brain'
} {
  brain 1 1
  brainchild 1 1 brained 1 1 braining 1 1 brains 1 1 
  brainstem 1 1 brainstems 1 1 brainstorm 1 1 brainstorms 1 1
}
do_execsql_test 2.2.2.6 {
  SELECT term, documents, occurrences FROM terms_v WHERE +term>='brain'
} {
  brain 1 1
  brainchild 1 1 brained 1 1 braining 1 1 brains 1 1 
  brainstem 1 1 brainstems 1 1 brainstorm 1 1 brainstorms 1 1
}

do_execsql_test 2.2.2.7 {
  SELECT term, documents, occurrences FROM terms_v WHERE term>='abc'
} {
  braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 
  bragging 1 1 brags 1 1 braid 1 1 braided 1 1 
  braiding 1 1 braids 1 1 brain 1 1 brainchild 1 1 
  brained 1 1 braining 1 1 brains 1 1 brainstem 1 1 
  brainstems 1 1 brainstorm 1 1 brainstorms 1 1
}
do_execsql_test 2.2.2.8 {
  SELECT term, documents, occurrences FROM terms_v WHERE +term>='abc'
} {
  braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 
  bragging 1 1 brags 1 1 braid 1 1 braided 1 1 
  braiding 1 1 braids 1 1 brain 1 1 brainchild 1 1 
  brained 1 1 braining 1 1 brains 1 1 brainstem 1 1 
  brainstems 1 1 brainstorm 1 1 brainstorms 1 1
}

do_execsql_test 2.2.2.9 {
  SELECT term, documents, occurrences FROM terms_v WHERE term>='brainstorms'
} {brainstorms 1 1}
do_execsql_test 2.2.2.10 {
  SELECT term, documents, occurrences FROM terms_v WHERE term>='brainstorms'
} {brainstorms 1 1}
do_execsql_test 2.2.2.11 { SELECT * FROM terms_v WHERE term>'brainstorms' } {}
do_execsql_test 2.2.2.12 { SELECT * FROM terms_v WHERE term>'brainstorms' } {}

do_execsql_test 2.2.2.13 { SELECT * FROM terms_v WHERE term>'cba' } {}
do_execsql_test 2.2.2.14 { SELECT * FROM terms_v WHERE term>'cba' } {}

do_test 2.2.3.1 {
  set cnt 0
  execsql { SELECT * FROM terms WHERE rec('cnt', term) AND term<'brain' }
  set cnt
} {22}
do_test 2.2.3.2 {
  set cnt 0
  execsql { SELECT * FROM terms WHERE rec('cnt', term) AND +term<'brain' }
  set cnt
} {38}
do_execsql_test 2.2.3.3 {
  SELECT term, documents, occurrences FROM terms_v WHERE term<'brain'
} {
  braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 bragging 1 1 
  brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1
}
do_execsql_test 2.2.3.4 {
  SELECT term, documents, occurrences FROM terms_v WHERE +term<'brain'
} {
  braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 bragging 1 1 
  brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1
}
do_execsql_test 2.2.3.5 {
  SELECT term, documents, occurrences FROM terms_v WHERE term<='brain'
} {
  braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 bragging 1 1 
  brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1
  brain 1 1
}
do_execsql_test 2.2.3.6 {
  SELECT term, documents, occurrences FROM terms_v WHERE +term<='brain'
} {
  braes 1 1 brag 1 1 bragged 1 1 bragger 1 1 bragging 1 1 
  brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1
  brain 1 1
}

do_test 2.2.4.1 {
  set cnt 0
  execsql { 
    SELECT term, documents, occurrences FROM terms 
    WHERE rec('cnt', term) AND term BETWEEN 'brags' AND 'brain' 
  }
  set cnt
} {12}
do_test 2.2.4.2 {
  set cnt 0
  execsql { 
    SELECT term, documents, occurrences FROM terms 
    WHERE rec('cnt', term) AND +term BETWEEN 'brags' AND 'brain' 
  }
  set cnt
} {38}
do_execsql_test 2.2.4.3 {
  SELECT term, documents, occurrences FROM terms_v 
  WHERE rec('cnt', term) AND term BETWEEN 'brags' AND 'brain' 
} {
  brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1 brain 1 1 
}
do_execsql_test 2.2.4.4 {
  SELECT term, documents, occurrences FROM terms_v 
  WHERE rec('cnt', term) AND +term BETWEEN 'brags' AND 'brain' 
} {
  brags 1 1 braid 1 1 braided 1 1 braiding 1 1 braids 1 1 brain 1 1 
}
do_execsql_test 2.2.4.5 {
  SELECT term, documents, occurrences FROM terms_v 
  WHERE rec('cnt', term) AND term > 'brags' AND term < 'brain' 
} {
  braid 1 1 braided 1 1 braiding 1 1 braids 1 1
}
do_execsql_test 2.2.4.6 {
  SELECT term, documents, occurrences FROM terms_v 
  WHERE rec('cnt', term) AND +term > 'brags' AND +term < 'brain' 
} {
  braid 1 1 braided 1 1 braiding 1 1 braids 1 1
}

# Check that "ORDER BY term ASC" and equivalents are sorted by the
# virtual table implementation. Any other ORDER BY clause requires
................................................................................
  CREATE TABLE x3(y);
  CREATE INDEX i1 ON x3(y);

  INSERT INTO x1 VALUES('a b c d e');
  INSERT INTO x1 VALUES('f g h i j');
  INSERT INTO x1 VALUES('k k l l a');

  INSERT INTO x2 SELECT term FROM terms WHERE col = '*';
  INSERT INTO x3 SELECT term FROM terms WHERE col = '*';
}

proc do_plansql_test {tn sql r} {
  uplevel do_execsql_test $tn [list "EXPLAIN QUERY PLAN $sql ; $sql"] [list $r]
}


do_plansql_test 4.2 {
  SELECT y FROM x2, terms WHERE y = term AND col = '*'
} {
  0 0 0 {SCAN TABLE x2 (~1000000 rows)} 
  0 1 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~0 rows)} 
  a b c d e f g h i j k l
}

do_plansql_test 4.3 {
  SELECT y FROM terms, x2 WHERE y = term AND col = '*'
} {
  0 0 1 {SCAN TABLE x2 (~1000000 rows)} 
  0 1 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 1: (~0 rows)} 
  a b c d e f g h i j k l
}

do_plansql_test 4.4 {
  SELECT y FROM x3, terms WHERE y = term AND col = '*'
} {
  0 0 1 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} 
  0 1 0 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?) (~10 rows)}
  a b c d e f g h i j k l
}

do_plansql_test 4.5 {
  SELECT y FROM terms, x3 WHERE y = term AND occurrences>1 AND col = '*'
} {
  0 0 0 {SCAN TABLE terms VIRTUAL TABLE INDEX 0: (~0 rows)} 
  0 1 1 {SEARCH TABLE x3 USING COVERING INDEX i1 (y=?) (~10 rows)}
  a k l
}


finish_test

Changes to test/fts3comp1.test.

84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
  } {{one two three} {two four six}}
  
  # Delete a row and check that the full-text index is correctly updated.
  # Inspect the full-text index using an fts4aux table.
  #
  do_execsql_test 1.$tn.7 {
    CREATE VIRTUAL TABLE terms USING fts4aux(t1);
    SELECT * FROM terms;
  } {
    eight 1 1    four 2 2    nine 1 1    one 1 1 
    six 2 2      three 2 2   twelve 1 1  two 1 2
  }
  do_execsql_test 1.$tn.8 {
    DELETE FROM t1 WHERE docid = 1;
    SELECT * FROM terms;
  } {
    eight 1 1   four 1 1    nine 1 1 
    six 1 1     three 1 1   twelve 1 1
  }
  do_execsql_test 1.$tn.9 { SELECT c0a, c1b FROM t1_content } {3 4}
}








|






|







84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
  } {{one two three} {two four six}}
  
  # Delete a row and check that the full-text index is correctly updated.
  # Inspect the full-text index using an fts4aux table.
  #
  do_execsql_test 1.$tn.7 {
    CREATE VIRTUAL TABLE terms USING fts4aux(t1);
    SELECT term, documents, occurrences FROM terms WHERE col = '*';
  } {
    eight 1 1    four 2 2    nine 1 1    one 1 1 
    six 2 2      three 2 2   twelve 1 1  two 1 2
  }
  do_execsql_test 1.$tn.8 {
    DELETE FROM t1 WHERE docid = 1;
    SELECT term, documents, occurrences FROM terms WHERE col = '*';
  } {
    eight 1 1   four 1 1    nine 1 1 
    six 1 1     three 1 1   twelve 1 1
  }
  do_execsql_test 1.$tn.9 { SELECT c0a, c1b FROM t1_content } {3 4}
}