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: |
bf1607ac155018573ca40fb58aca62c5 |
| 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
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