/ Check-in [bf1607ac]
Login

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

Overview
Comment:Optimizations for fts5 expressions that filter on column. More still to come.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: bf1607ac155018573ca40fb58aca62c5fea7e60b
User & Date: dan 2015-10-06 20:53:26
Context
2015-10-06
21:07
Simplifications to the VDBE bytecode that handles LIMIT and OFFSET. check-in: 041df7c2 user: drh tags: trunk
20:53
Optimizations for fts5 expressions that filter on column. More still to come. check-in: bf1607ac user: dan tags: trunk
17:27
Fix the LIMIT and OFFSET handling for UNION ALL queries that contain a subquery with ORDER BY on the right-hand side. Fix for ticket [b65cb2c8d91f668584]. check-in: 4b631364 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to ext/fts5/fts5Int.h.

    77     77   extern int sqlite3_fts5_may_be_corrupt;
    78     78   # define assert_nc(x) assert(sqlite3_fts5_may_be_corrupt || (x))
    79     79   #else
    80     80   # define assert_nc(x) assert(x)
    81     81   #endif
    82     82   
    83     83   typedef struct Fts5Global Fts5Global;
           84  +typedef struct Fts5ExprColset Fts5ExprColset;
           85  +
           86  +/* If a NEAR() clump or phrase may only match a specific set of columns, 
           87  +** then an object of the following type is used to record the set of columns.
           88  +** Each entry in the aiCol[] array is a column that may be matched.
           89  +**
           90  +** This object is used by fts5_expr.c and fts5_index.c.
           91  +*/
           92  +struct Fts5ExprColset {
           93  +  int nCol;
           94  +  int aiCol[1];
           95  +};
           96  +
           97  +
    84     98   
    85     99   /**************************************************************************
    86    100   ** Interface to code in fts5_config.c. fts5_config.c contains contains code
    87    101   ** to parse the arguments passed to the CREATE VIRTUAL TABLE statement.
    88    102   */
    89    103   
    90    104   typedef struct Fts5Config Fts5Config;
................................................................................
   301    315   ** Create/destroy an Fts5Index object.
   302    316   */
   303    317   int sqlite3Fts5IndexOpen(Fts5Config *pConfig, int bCreate, Fts5Index**, char**);
   304    318   int sqlite3Fts5IndexClose(Fts5Index *p);
   305    319   
   306    320   /*
   307    321   ** for(
   308         -**   pIter = sqlite3Fts5IndexQuery(p, "token", 5, 0);
          322  +**   sqlite3Fts5IndexQuery(p, "token", 5, 0, 0, &pIter);
   309    323   **   0==sqlite3Fts5IterEof(pIter);
   310    324   **   sqlite3Fts5IterNext(pIter)
   311    325   ** ){
   312    326   **   i64 iRowid = sqlite3Fts5IterRowid(pIter);
   313    327   ** }
   314    328   */
   315    329   
................................................................................
   317    331   ** Open a new iterator to iterate though all rowids that match the 
   318    332   ** specified token or token prefix.
   319    333   */
   320    334   int sqlite3Fts5IndexQuery(
   321    335     Fts5Index *p,                   /* FTS index to query */
   322    336     const char *pToken, int nToken, /* Token (or prefix) to query for */
   323    337     int flags,                      /* Mask of FTS5INDEX_QUERY_X flags */
   324         -  Fts5IndexIter **ppIter
          338  +  Fts5ExprColset *pColset,        /* Match these columns only */
          339  +  Fts5IndexIter **ppIter          /* OUT: New iterator object */
   325    340   );
   326    341   
   327    342   /*
   328    343   ** The various operations on open token or token prefix iterators opened
   329    344   ** using sqlite3Fts5IndexQuery().
   330    345   */
   331    346   int sqlite3Fts5IterEof(Fts5IndexIter*);
................................................................................
   563    578   */
   564    579   typedef struct Fts5Expr Fts5Expr;
   565    580   typedef struct Fts5ExprNode Fts5ExprNode;
   566    581   typedef struct Fts5Parse Fts5Parse;
   567    582   typedef struct Fts5Token Fts5Token;
   568    583   typedef struct Fts5ExprPhrase Fts5ExprPhrase;
   569    584   typedef struct Fts5ExprNearset Fts5ExprNearset;
   570         -typedef struct Fts5ExprColset Fts5ExprColset;
   571    585   
   572    586   struct Fts5Token {
   573    587     const char *p;                  /* Token text (not NULL terminated) */
   574    588     int n;                          /* Size of buffer p in bytes */
   575    589   };
   576    590   
   577    591   /* Parse a MATCH expression. */

Changes to ext/fts5/fts5_expr.c.

    85     85   struct Fts5ExprPhrase {
    86     86     Fts5ExprNode *pNode;            /* FTS5_STRING node this phrase is part of */
    87     87     Fts5Buffer poslist;             /* Current position list */
    88     88     int nTerm;                      /* Number of entries in aTerm[] */
    89     89     Fts5ExprTerm aTerm[1];          /* Terms that make up this phrase */
    90     90   };
    91     91   
    92         -/*
    93         -** If a NEAR() clump may only match a specific set of columns, then
    94         -** Fts5ExprNearset.pColset points to an object of the following type.
    95         -** Each entry in the aiCol[] array
    96         -*/
    97         -struct Fts5ExprColset {
    98         -  int nCol;
    99         -  int aiCol[1];
   100         -};
   101         -
   102     92   /*
   103     93   ** One or more phrases that must appear within a certain token distance of
   104     94   ** each other within each matching document.
   105     95   */
   106     96   struct Fts5ExprNearset {
   107     97     int nNear;                      /* NEAR parameter */
   108     98     Fts5ExprColset *pColset;        /* Columns to search (NULL -> all columns) */
................................................................................
   998    988             sqlite3Fts5IterClose(p->pIter);
   999    989             p->pIter = 0;
  1000    990           }
  1001    991           rc = sqlite3Fts5IndexQuery(
  1002    992               pExpr->pIndex, p->zTerm, strlen(p->zTerm),
  1003    993               (pTerm->bPrefix ? FTS5INDEX_QUERY_PREFIX : 0) |
  1004    994               (pExpr->bDesc ? FTS5INDEX_QUERY_DESC : 0),
          995  +            pNear->pColset,
  1005    996               &p->pIter
  1006    997           );
  1007    998           assert( rc==SQLITE_OK || p->pIter==0 );
  1008    999           if( p->pIter && 0==sqlite3Fts5IterEof(p->pIter) ){
  1009   1000             bEof = 0;
  1010   1001           }
  1011   1002         }

Changes to ext/fts5/fts5_index.c.

  3938   3938     fts5StructureRelease(pStruct);
  3939   3939   
  3940   3940     return fts5IndexReturn(p);
  3941   3941   }
  3942   3942   
  3943   3943   static void fts5PoslistCallback(
  3944   3944     Fts5Index *p, 
  3945         -  void *pCtx, 
         3945  +  void *pContext, 
         3946  +  const u8 *pChunk, int nChunk
         3947  +){
         3948  +  assert_nc( nChunk>=0 );
         3949  +  if( nChunk>0 ){
         3950  +    fts5BufferAppendBlob(&p->rc, (Fts5Buffer*)pContext, nChunk, pChunk);
         3951  +  }
         3952  +}
         3953  +
         3954  +typedef struct PoslistCallbackCtx PoslistCallbackCtx;
         3955  +struct PoslistCallbackCtx {
         3956  +  Fts5Buffer *pBuf;               /* Append to this buffer */
         3957  +  Fts5ExprColset *pColset;        /* Restrict matches to this column */
         3958  +  int eState;                     /* See above */
         3959  +};
         3960  +
         3961  +/*
         3962  +** TODO: Make this more efficient!
         3963  +*/
         3964  +static int fts5IndexColsetTest(Fts5ExprColset *pColset, int iCol){
         3965  +  int i;
         3966  +  for(i=0; i<pColset->nCol; i++){
         3967  +    if( pColset->aiCol[i]==iCol ) return 1;
         3968  +  }
         3969  +  return 0;
         3970  +}
         3971  +
         3972  +static void fts5PoslistFilterCallback(
         3973  +  Fts5Index *p, 
         3974  +  void *pContext, 
  3946   3975     const u8 *pChunk, int nChunk
  3947   3976   ){
         3977  +  PoslistCallbackCtx *pCtx = (PoslistCallbackCtx*)pContext;
  3948   3978     assert_nc( nChunk>=0 );
  3949   3979     if( nChunk>0 ){
  3950         -    fts5BufferAppendBlob(&p->rc, (Fts5Buffer*)pCtx, nChunk, pChunk);
         3980  +    /* Search through to find the first varint with value 1. This is the
         3981  +    ** start of the next columns hits. */
         3982  +    int i = 0;
         3983  +    int iStart = 0;
         3984  +
         3985  +    if( pCtx->eState==2 ){
         3986  +      int iCol;
         3987  +      fts5IndexGetVarint32(pChunk, i, iCol);
         3988  +      if( fts5IndexColsetTest(pCtx->pColset, iCol) ){
         3989  +        pCtx->eState = 1;
         3990  +        fts5BufferAppendVarint(&p->rc, pCtx->pBuf, 1);
         3991  +      }else{
         3992  +        pCtx->eState = 0;
         3993  +      }
         3994  +    }
         3995  +
         3996  +    do {
         3997  +      while( i<nChunk && pChunk[i]!=0x01 ){
         3998  +        while( pChunk[i] & 0x80 ) i++;
         3999  +        i++;
         4000  +      }
         4001  +      if( pCtx->eState ){
         4002  +        fts5BufferAppendBlob(&p->rc, pCtx->pBuf, i-iStart, &pChunk[iStart]);
         4003  +      }
         4004  +      if( i<nChunk ){
         4005  +        int iCol;
         4006  +        iStart = i;
         4007  +        i++;
         4008  +        if( i>=nChunk ){
         4009  +          pCtx->eState = 2;
         4010  +        }else{
         4011  +          fts5IndexGetVarint32(pChunk, i, iCol);
         4012  +          pCtx->eState = fts5IndexColsetTest(pCtx->pColset, iCol);
         4013  +          if( pCtx->eState ){
         4014  +            fts5BufferAppendBlob(&p->rc, pCtx->pBuf, i-iStart, &pChunk[iStart]);
         4015  +            iStart = i;
         4016  +          }
         4017  +        }
         4018  +      }
         4019  +    }while( i<nChunk );
  3951   4020     }
  3952   4021   }
  3953   4022   
  3954   4023   /*
  3955   4024   ** Iterator pIter currently points to a valid entry (not EOF). This
  3956   4025   ** function appends the position list data for the current entry to
  3957   4026   ** buffer pBuf. It does not make a copy of the position-list size
  3958   4027   ** field.
  3959   4028   */
  3960   4029   static void fts5SegiterPoslist(
  3961   4030     Fts5Index *p,
  3962   4031     Fts5SegIter *pSeg,
         4032  +  Fts5ExprColset *pColset,
  3963   4033     Fts5Buffer *pBuf
  3964   4034   ){
         4035  +  if( pColset==0 ){
  3965   4036     fts5ChunkIterate(p, pSeg, (void*)pBuf, fts5PoslistCallback);
         4037  +  }else{
         4038  +    PoslistCallbackCtx sCtx;
         4039  +    sCtx.pBuf = pBuf;
         4040  +    sCtx.pColset = pColset;
         4041  +    sCtx.eState = pColset ? fts5IndexColsetTest(pColset, 0) : 1;
         4042  +    assert( sCtx.eState==0 || sCtx.eState==1 );
         4043  +    fts5ChunkIterate(p, pSeg, (void*)&sCtx, fts5PoslistFilterCallback);
         4044  +  }
  3966   4045   }
  3967   4046   
  3968   4047   /*
  3969   4048   ** Iterator pMulti currently points to a valid entry (not EOF). This
  3970   4049   ** function appends a copy of the position-list of the entry pMulti 
  3971   4050   ** currently points to to buffer pBuf.
  3972   4051   **
  3973   4052   ** If an error occurs, an error code is left in p->rc. It is assumed
  3974   4053   ** no error has already occurred when this function is called.
  3975   4054   */
  3976         -static void fts5MultiIterPoslist(
         4055  +static int fts5MultiIterPoslist(
  3977   4056     Fts5Index *p,
  3978   4057     Fts5IndexIter *pMulti,
         4058  +  Fts5ExprColset *pColset,
  3979   4059     int bSz,                        /* Append a size field before the data */
  3980   4060     Fts5Buffer *pBuf
  3981   4061   ){
  3982   4062     if( p->rc==SQLITE_OK ){
         4063  +    int iSz;
         4064  +    int iData;
         4065  +
  3983   4066       Fts5SegIter *pSeg = &pMulti->aSeg[ pMulti->aFirst[1].iFirst ];
  3984   4067       assert( fts5MultiIterEof(p, pMulti)==0 );
  3985   4068   
  3986   4069       if( bSz ){
  3987   4070         /* WRITEPOSLISTSIZE */
         4071  +      iSz = pBuf->n;
  3988   4072         fts5BufferAppendVarint(&p->rc, pBuf, pSeg->nPos*2);
         4073  +      iData = pBuf->n;
  3989   4074       }
  3990         -    fts5SegiterPoslist(p, pSeg, pBuf);
         4075  +
         4076  +    fts5SegiterPoslist(p, pSeg, pColset, pBuf);
         4077  +
         4078  +    if( bSz && pColset ){
         4079  +      int nActual = pBuf->n - iData;
         4080  +      if( nActual!=pSeg->nPos ){
         4081  +        /* WRITEPOSLISTSIZE */
         4082  +        if( nActual==0 ){
         4083  +          return 1;
         4084  +        }else{
         4085  +          int nReq = sqlite3Fts5GetVarintLen((u32)(nActual*2));
         4086  +          while( iSz<(iData-nReq) ){ pBuf->p[iSz++] = 0x80; }
         4087  +          sqlite3Fts5PutVarint(&pBuf->p[iSz], nActual*2);
  3991   4088     }
  3992   4089   }
         4090  +    }
         4091  +  }
         4092  +
         4093  +  return 0;
         4094  +}
  3993   4095   
  3994   4096   static void fts5DoclistIterNext(Fts5DoclistIter *pIter){
  3995   4097     u8 *p = pIter->aPoslist + pIter->nPoslist;
  3996   4098   
  3997   4099     assert( pIter->aPoslist );
  3998   4100     if( p>=pIter->aEof ){
  3999   4101       pIter->aPoslist = 0;
................................................................................
  4145   4247   }
  4146   4248   
  4147   4249   static void fts5SetupPrefixIter(
  4148   4250     Fts5Index *p,                   /* Index to read from */
  4149   4251     int bDesc,                      /* True for "ORDER BY rowid DESC" */
  4150   4252     const u8 *pToken,               /* Buffer containing prefix to match */
  4151   4253     int nToken,                     /* Size of buffer pToken in bytes */
         4254  +  Fts5ExprColset *pColset,        /* Restrict matches to these columns */
  4152   4255     Fts5IndexIter **ppIter       /* OUT: New iterator */
  4153   4256   ){
  4154   4257     Fts5Structure *pStruct;
  4155   4258     Fts5Buffer *aBuf;
  4156   4259     const int nBuf = 32;
  4157   4260   
  4158   4261     aBuf = (Fts5Buffer*)fts5IdxMalloc(p, sizeof(Fts5Buffer)*nBuf);
................................................................................
  4188   4291               fts5BufferZero(&aBuf[i]);
  4189   4292             }
  4190   4293           }
  4191   4294           iLastRowid = 0;
  4192   4295         }
  4193   4296   
  4194   4297         if( 0==sqlite3Fts5BufferGrow(&p->rc, &doclist, 9) ){
  4195         -        fts5MergeAppendDocid(&doclist, iLastRowid, iRowid);
  4196         -        fts5MultiIterPoslist(p, p1, 1, &doclist);
         4298  +        int iSave = doclist.n;
         4299  +        assert( doclist.n!=0 || iLastRowid==0 );
         4300  +        fts5BufferSafeAppendVarint(&doclist, iRowid - iLastRowid);
         4301  +        if( fts5MultiIterPoslist(p, p1, pColset, 1, &doclist) ){
         4302  +          doclist.n = iSave;
         4303  +        }else{
         4304  +          iLastRowid = iRowid;
         4305  +        }
  4197   4306         }
  4198   4307       }
  4199   4308   
  4200   4309       for(i=0; i<nBuf; i++){
  4201   4310         if( p->rc==SQLITE_OK ){
  4202   4311           fts5MergePrefixLists(p, &doclist, &aBuf[i]);
  4203   4312         }
................................................................................
  4423   4532   ** Open a new iterator to iterate though all rowid that match the 
  4424   4533   ** specified token or token prefix.
  4425   4534   */
  4426   4535   int sqlite3Fts5IndexQuery(
  4427   4536     Fts5Index *p,                   /* FTS index to query */
  4428   4537     const char *pToken, int nToken, /* Token (or prefix) to query for */
  4429   4538     int flags,                      /* Mask of FTS5INDEX_QUERY_X flags */
         4539  +  Fts5ExprColset *pColset,        /* Match these columns only */
  4430   4540     Fts5IndexIter **ppIter          /* OUT: New iterator object */
  4431   4541   ){
  4432   4542     Fts5Config *pConfig = p->pConfig;
  4433   4543     Fts5IndexIter *pRet = 0;
  4434   4544     int iIdx = 0;
  4435   4545     Fts5Buffer buf = {0, 0, 0};
  4436   4546   
................................................................................
  4466   4576         if( pStruct ){
  4467   4577           fts5MultiIterNew(p, pStruct, 1, flags, buf.p, nToken+1, -1, 0, &pRet);
  4468   4578           fts5StructureRelease(pStruct);
  4469   4579         }
  4470   4580       }else{
  4471   4581         int bDesc = (flags & FTS5INDEX_QUERY_DESC)!=0;
  4472   4582         buf.p[0] = FTS5_MAIN_PREFIX;
  4473         -      fts5SetupPrefixIter(p, bDesc, buf.p, nToken+1, &pRet);
         4583  +      fts5SetupPrefixIter(p, bDesc, buf.p, nToken+1, pColset, &pRet);
  4474   4584       }
  4475   4585   
  4476   4586       if( p->rc ){
  4477   4587         sqlite3Fts5IterClose(pRet);
  4478   4588         pRet = 0;
  4479   4589         fts5CloseReader(p);
  4480   4590       }
................................................................................
  4568   4678     assert( pIter->pIndex->rc==SQLITE_OK );
  4569   4679     *piRowid = pSeg->iRowid;
  4570   4680     *pn = pSeg->nPos;
  4571   4681     if( pSeg->iLeafOffset+pSeg->nPos <= pSeg->pLeaf->szLeaf ){
  4572   4682       *pp = &pSeg->pLeaf->p[pSeg->iLeafOffset];
  4573   4683     }else{
  4574   4684       fts5BufferZero(&pIter->poslist);
  4575         -    fts5SegiterPoslist(pIter->pIndex, pSeg, &pIter->poslist);
         4685  +    fts5SegiterPoslist(pIter->pIndex, pSeg, 0, &pIter->poslist);
  4576   4686       *pp = pIter->poslist.p;
  4577   4687     }
  4578   4688     return fts5IndexReturn(pIter->pIndex);
  4579   4689   }
  4580   4690   
  4581   4691   /*
  4582   4692   ** This function is similar to sqlite3Fts5IterPoslist(), except that it
................................................................................
  4584   4694   ** argument.
  4585   4695   */
  4586   4696   int sqlite3Fts5IterPoslistBuffer(Fts5IndexIter *pIter, Fts5Buffer *pBuf){
  4587   4697     Fts5Index *p = pIter->pIndex;
  4588   4698   
  4589   4699     assert( p->rc==SQLITE_OK );
  4590   4700     fts5BufferZero(pBuf);
  4591         -  fts5MultiIterPoslist(p, pIter, 0, pBuf);
         4701  +  fts5MultiIterPoslist(p, pIter, 0, 0, pBuf);
  4592   4702     return fts5IndexReturn(p);
  4593   4703   }
  4594   4704   
  4595   4705   /*
  4596   4706   ** Close an iterator opened by an earlier call to sqlite3Fts5IndexQuery().
  4597   4707   */
  4598   4708   void sqlite3Fts5IterClose(Fts5IndexIter *pIter){
................................................................................
  4759   4869     const char *z,                  /* Index key to query for */
  4760   4870     int n,                          /* Size of index key in bytes */
  4761   4871     int flags,                      /* Flags for Fts5IndexQuery */
  4762   4872     u64 *pCksum                     /* IN/OUT: Checksum value */
  4763   4873   ){
  4764   4874     u64 cksum = *pCksum;
  4765   4875     Fts5IndexIter *pIdxIter = 0;
  4766         -  int rc = sqlite3Fts5IndexQuery(p, z, n, flags, &pIdxIter);
         4876  +  int rc = sqlite3Fts5IndexQuery(p, z, n, flags, 0, &pIdxIter);
  4767   4877   
  4768   4878     while( rc==SQLITE_OK && 0==sqlite3Fts5IterEof(pIdxIter) ){
  4769   4879       i64 dummy;
  4770   4880       const u8 *pPos;
  4771   4881       int nPos;
  4772   4882       i64 rowid = sqlite3Fts5IterRowid(pIdxIter);
  4773   4883       rc = sqlite3Fts5IterPoslist(pIdxIter, &pPos, &nPos, &dummy);
................................................................................
  5133   5243       i64 iRowid = fts5MultiIterRowid(pIter);
  5134   5244       char *z = (char*)fts5MultiIterTerm(pIter, &n);
  5135   5245   
  5136   5246       /* If this is a new term, query for it. Update cksum3 with the results. */
  5137   5247       fts5TestTerm(p, &term, z, n, cksum2, &cksum3);
  5138   5248   
  5139   5249       poslist.n = 0;
  5140         -    fts5MultiIterPoslist(p, pIter, 0, &poslist);
         5250  +    fts5MultiIterPoslist(p, pIter, 0, 0, &poslist);
  5141   5251       while( 0==sqlite3Fts5PoslistNext64(poslist.p, poslist.n, &iOff, &iPos) ){
  5142   5252         int iCol = FTS5_POS2COLUMN(iPos);
  5143   5253         int iTokOff = FTS5_POS2OFFSET(iPos);
  5144   5254         cksum2 ^= fts5IndexEntryCksum(iRowid, iCol, iTokOff, -1, z, n);
  5145   5255       }
  5146   5256     }
  5147   5257     fts5TestTerm(p, &term, 0, 0, cksum2, &cksum3);

Changes to ext/fts5/fts5_vocab.c.

   398    398     sqlite3_value **apVal           /* Arguments for the indexing scheme */
   399    399   ){
   400    400     Fts5VocabCursor *pCsr = (Fts5VocabCursor*)pCursor;
   401    401     int rc;
   402    402     const int flags = FTS5INDEX_QUERY_SCAN;
   403    403   
   404    404     fts5VocabResetCursor(pCsr);
   405         -  rc = sqlite3Fts5IndexQuery(pCsr->pIndex, 0, 0, flags, &pCsr->pIter);
          405  +  rc = sqlite3Fts5IndexQuery(pCsr->pIndex, 0, 0, flags, 0, &pCsr->pIter);
   406    406     if( rc==SQLITE_OK ){
   407    407       rc = fts5VocabNextMethod(pCursor);
   408    408     }
   409    409   
   410    410     return rc;
   411    411   }
   412    412   

Changes to ext/fts5/test/fts5prefix.test.

    58     58   foreach {tn q res} {
    59     59     1 "SELECT rowid FROM t1 WHERE t1 MATCH '\xCA\xCB*'" 1
    60     60     2 "SELECT rowid FROM t1 WHERE t1 MATCH '\u1234\u5678*'" 2
    61     61   } {
    62     62     do_execsql_test 2.3.$tn $q $res
    63     63   }
    64     64   
           65  +#-------------------------------------------------------------------------
           66  +# Check that prefix queries with:
           67  +#
           68  +#   * a column filter, and
           69  +#   * no prefix index.
           70  +#
           71  +# work Ok.
           72  +#
           73  +do_execsql_test 3.0 {
           74  +  CREATE VIRTUAL TABLE t3 USING fts5(a, b, c);
           75  +  INSERT INTO t3(t3, rank) VALUES('pgsz', 32);
           76  +  BEGIN;
           77  +    INSERT INTO t3 VALUES('acb ccc bba', 'cca bba bca', 'bbc ccc bca'); -- 1
           78  +    INSERT INTO t3 VALUES('cbb cac cab', 'abb aac bba', 'aab ccc cac'); -- 2
           79  +    INSERT INTO t3 VALUES('aac bcb aac', 'acb bcb caa', 'aca bab bca'); -- 3
           80  +    INSERT INTO t3 VALUES('aab ccb ccc', 'aca cba cca', 'aca aac cbb'); -- 4
           81  +    INSERT INTO t3 VALUES('bac aab bab', 'ccb bac cba', 'acb aba abb'); -- 5
           82  +    INSERT INTO t3 VALUES('bab abc ccb', 'acb cba abb', 'cbb aaa cab'); -- 6
           83  +    INSERT INTO t3 VALUES('cbb bbc baa', 'aab aca baa', 'bcc cca aca'); -- 7
           84  +    INSERT INTO t3 VALUES('abc bba abb', 'cac abc cba', 'acc aac cac'); -- 8
           85  +    INSERT INTO t3 VALUES('bbc bbc cab', 'bcb ccb cba', 'bcc cac acb'); -- 9
           86  +  COMMIT;
           87  +}
           88  +
           89  +foreach {tn match res} {
           90  +  1 "a : c*" {1 2 4 6 7 9}
           91  +  2 "b : c*" {1 3 4 5 6 8 9}
           92  +  3 "c : c*" {1 2 4 6 7 8 9}
           93  +  4 "a : b*" {1 3 5 6 7 8 9}
           94  +  5 "b : b*" {1 2 3 5 7 9}
           95  +  6 "c : b*" {1 3 7 9}
           96  +  7 "a : a*" {1 3 4 5 6 8}
           97  +  8 "b : a*" {2 3 4 6 7 8}
           98  +  9 "c : a*" {2 3 4 5 6 7 8 9}
           99  +} {
          100  +  do_execsql_test 3.1.$tn {
          101  +    SELECT rowid FROM t3($match)
          102  +  } $res
          103  +}
          104  +
          105  +do_test 3.2 {
          106  +  expr srand(0)
          107  +  execsql { DELETE FROM t3 }
          108  +  for {set i 0} {$i < 1000} {incr i} {
          109  +    set a [fts5_rnddoc 3]
          110  +    set b [fts5_rnddoc 8]
          111  +    set c [fts5_rnddoc 20]
          112  +    execsql { INSERT INTO t3 VALUES($a, $b, $c) }
          113  +  }
          114  +  execsql { INSERT INTO t3(t3) VALUES('integrity-check') }
          115  +} {}
          116  +
          117  +proc gmatch {col pattern} {
          118  +  expr {[lsearch -glob $col $pattern]>=0}
          119  +}
          120  +db func gmatch gmatch
          121  +
          122  +for {set x 0} {$x<2} {incr x} {
          123  +  foreach {tn pattern} {
          124  +    1  {xa*}
          125  +    2  {xb*}
          126  +    3  {xc*}
          127  +    4  {xd*}
          128  +    5  {xe*}
          129  +    6  {xf*}
          130  +    7  {xg*}
          131  +    8  {xh*}
          132  +    9  {xi*}
          133  +    10 {xj*}
          134  +  } {
          135  +    foreach col {b} {
          136  +      set res [db eval "SELECT rowid FROM t3 WHERE gmatch($col, '$pattern')"]
          137  +      set query "$col : $pattern"
          138  +      do_execsql_test 3.3.$x.$tn.$col {
          139  +        SELECT rowid FROM t3($query);
          140  +      } $res
          141  +    }
          142  +  }
          143  +  execsql { INSERT INTO t3(t3) VALUES('optimize') }
          144  +  execsql { INSERT INTO t3(t3) VALUES('integrity-check') }
          145  +}
          146  +
    65    147   
    66    148   finish_test
          149  +
    67    150   

Changes to ext/fts5/test/fts5simple.test.

   235    235     SELECT rowid FROM ft2('a');
   236    236   } {1 2}
   237    237   
   238    238   do_execsql_test 9.3 {
   239    239     SELECT rowid FROM ft2('b AND c');
   240    240   } {2}
   241    241   
          242  +#-------------------------------------------------------------------------
          243  +#
          244  +do_execsql_test 10.0 {
          245  +  CREATE VIRTUAL TABLE t3 USING fts5(a, b, c);
          246  +  INSERT INTO t3 VALUES('bac aab bab', 'c bac c', 'acb aba abb'); -- 1
          247  +  INSERT INTO t3 VALUES('bab abc c', 'acb c abb', 'c aaa c');     -- 2
          248  +}
          249  +
          250  +do_execsql_test 10.1 {
          251  +  SELECT rowid FROM t3('c: c*');
          252  +} {2}
   242    253   
   243    254   
   244    255   finish_test
   245    256