Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Make use of range constraints on the rowid field of an fts5 table in full-text queries. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | fts5 |
| Files: | files | file ages | folders |
| SHA1: |
32cbc0ed3699cc21302f0b6a15949311 |
| User & Date: | dan 2015-06-05 19:05:57 |
Context
|
2015-06-06
| ||
| 16:28 | Fix handling of fts5 rowid constraints in the absence of a MATCH clause. Add tests to cover recently added branches. check-in: 3a9cb648 user: dan tags: fts5 | |
|
2015-06-05
| ||
| 19:05 | Make use of range constraints on the rowid field of an fts5 table in full-text queries. check-in: 32cbc0ed user: dan tags: fts5 | |
|
2015-06-03
| ||
| 11:23 | Fix an fts5 problem in extracting columns from position lists containing large varints. check-in: 4ea015ab user: dan tags: fts5 | |
Changes
Changes to ext/fts5/fts5.c.
152 152 153 153 /* 154 154 ** Virtual-table cursor object. 155 155 ** 156 156 ** iSpecial: 157 157 ** If this is a 'special' query (refer to function fts5SpecialMatch()), 158 158 ** then this variable contains the result of the query. 159 +** 160 +** iFirstRowid, iLastRowid: 161 +** These variables are only used for FTS5_PLAN_MATCH cursors. Assuming the 162 +** cursor iterates in ascending order of rowids, iFirstRowid is the lower 163 +** limit of rowids to return, and iLastRowid the upper. In other words, the 164 +** WHERE clause in the user's query might have been: 165 +** 166 +** <tbl> MATCH <expr> AND rowid BETWEEN $iFirstRowid AND $iLastRowid 167 +** 168 +** If the cursor iterates in descending order of rowid, iFirstRowid 169 +** is the upper limit (i.e. the "first" rowid visited) and iLastRowid 170 +** the lower. 159 171 */ 160 172 struct Fts5Cursor { 161 173 sqlite3_vtab_cursor base; /* Base class used by SQLite core */ 162 - int idxNum; /* idxNum passed to xFilter() */ 174 + int ePlan; /* FTS5_PLAN_XXX value */ 175 + int bDesc; /* True for "ORDER BY rowid DESC" queries */ 176 + i64 iFirstRowid; /* Return no rowids earlier than this */ 177 + i64 iLastRowid; /* Return no rowids later than this */ 163 178 sqlite3_stmt *pStmt; /* Statement used to read %_content */ 164 179 Fts5Expr *pExpr; /* Expression for MATCH queries */ 165 180 Fts5Sorter *pSorter; /* Sorter for "ORDER BY rank" queries */ 166 181 int csrflags; /* Mask of cursor flags (see below) */ 167 182 Fts5Cursor *pNext; /* Next cursor in Fts5Cursor.pCsr list */ 168 183 i64 iSpecial; /* Result of special query */ 169 184 ................................................................................ 177 192 178 193 /* Variables used by auxiliary functions */ 179 194 i64 iCsrId; /* Cursor id */ 180 195 Fts5Auxiliary *pAux; /* Currently executing extension function */ 181 196 Fts5Auxdata *pAuxdata; /* First in linked list of saved aux-data */ 182 197 int *aColumnSize; /* Values for xColumnSize() */ 183 198 199 + /* Cache used by auxiliary functions xInst() and xInstCount() */ 184 200 int nInstCount; /* Number of phrase instances */ 185 201 int *aInst; /* 3 integers per phrase instance */ 186 202 }; 187 203 204 +/* 205 +** Bits that make up the "idxNum" parameter passed indirectly by 206 +** xBestIndex() to xFilter(). 207 +*/ 208 +#define FTS5_BI_MATCH 0x0001 /* <tbl> MATCH ? */ 209 +#define FTS5_BI_RANK 0x0002 /* rank MATCH ? */ 210 +#define FTS5_BI_ROWID_EQ 0x0004 /* rowid == ? */ 211 +#define FTS5_BI_ROWID_LE 0x0008 /* rowid <= ? */ 212 +#define FTS5_BI_ROWID_GE 0x0010 /* rowid >= ? */ 213 + 214 +#define FTS5_BI_ORDER_RANK 0x0020 215 +#define FTS5_BI_ORDER_ROWID 0x0040 216 +#define FTS5_BI_ORDER_DESC 0x0080 217 + 188 218 /* 189 219 ** Values for Fts5Cursor.csrflags 190 220 */ 191 221 #define FTS5CSR_REQUIRE_CONTENT 0x01 192 222 #define FTS5CSR_REQUIRE_DOCSIZE 0x02 193 223 #define FTS5CSR_EOF 0x04 194 224 #define FTS5CSR_FREE_ZRANK 0x08 195 225 #define FTS5CSR_REQUIRE_RESEEK 0x10 226 + 227 +#define BitFlagAllTest(x,y) (((x) & (y))==(y)) 228 +#define BitFlagTest(x,y) (((x) & (y))!=0) 229 + 230 +/* 231 +** Constants for the largest and smallest possible 64-bit signed integers. 232 +** These are copied from sqliteInt.h. 233 +*/ 234 +#ifndef SQLITE_AMALGAMATION 235 +# define LARGEST_INT64 (0xffffffff|(((i64)0x7fffffff)<<32)) 236 +# define SMALLEST_INT64 (((i64)-1) - LARGEST_INT64) 237 +#endif 196 238 197 239 /* 198 240 ** Macros to Set(), Clear() and Test() cursor flags. 199 241 */ 200 242 #define CsrFlagSet(pCsr, flag) ((pCsr)->csrflags |= (flag)) 201 243 #define CsrFlagClear(pCsr, flag) ((pCsr)->csrflags &= ~(flag)) 202 244 #define CsrFlagTest(pCsr, flag) ((pCsr)->csrflags & (flag)) ................................................................................ 390 432 sqlite3_vtab **ppVtab, /* OUT: New sqlite3_vtab object */ 391 433 char **pzErr /* OUT: sqlite3_malloc'd error message */ 392 434 ){ 393 435 return fts5InitVtab(1, db, pAux, argc, argv, ppVtab, pzErr); 394 436 } 395 437 396 438 /* 397 -** The three query plans xBestIndex may choose between. 439 +** The different query plans. 398 440 */ 399 441 #define FTS5_PLAN_SCAN 1 /* No usable constraint */ 400 442 #define FTS5_PLAN_MATCH 2 /* (<tbl> MATCH ?) */ 401 443 #define FTS5_PLAN_SORTED_MATCH 3 /* (<tbl> MATCH ? ORDER BY rank) */ 402 444 #define FTS5_PLAN_ROWID 4 /* (rowid = ?) */ 403 445 #define FTS5_PLAN_SOURCE 5 /* A source cursor for SORTED_MATCH */ 404 446 #define FTS5_PLAN_SPECIAL 6 /* An internal query */ 405 447 406 -#define FTS5_PLAN(idxNum) ((idxNum) & 0x7) 407 - 408 -#define FTS5_ORDER_DESC 8 /* ORDER BY rowid DESC */ 409 -#define FTS5_ORDER_ASC 16 /* ORDER BY rowid ASC */ 410 - 411 -/* 412 -** Search the object passed as the first argument for a usable constraint 413 -** on column iCol using operator eOp. If one is found, return its index in 414 -** the pInfo->aConstraint[] array. If no such constraint is found, return 415 -** a negative value. 416 -*/ 417 -static int fts5FindConstraint(sqlite3_index_info *pInfo, int eOp, int iCol){ 418 - int i; 419 - for(i=0; i<pInfo->nConstraint; i++){ 420 - struct sqlite3_index_constraint *p = &pInfo->aConstraint[i]; 421 - if( p->usable && p->iColumn==iCol && p->op==eOp ) return i; 422 - } 423 - return -1; 424 -} 425 - 426 -/* 427 -** Implementation of the xBestIndex method for FTS5 tables. There 428 -** are three possible strategies, in order of preference: 429 -** 430 -** 1. Full-text search using a MATCH operator. 431 -** 2. A by-rowid lookup. 432 -** 3. A full-table scan. 448 +/* 449 +** Implementation of the xBestIndex method for FTS5 tables. Within the 450 +** WHERE constraint, it searches for the following: 451 +** 452 +** 1. A MATCH constraint against the special column. 453 +** 2. A MATCH constraint against the "rank" column. 454 +** 3. An == constraint against the rowid column. 455 +** 4. A < or <= constraint against the rowid column. 456 +** 5. A > or >= constraint against the rowid column. 457 +** 458 +** Within the ORDER BY, either: 459 +** 460 +** 5. ORDER BY rank [ASC|DESC] 461 +** 6. ORDER BY rowid [ASC|DESC] 462 +** 463 +** Costs are assigned as follows: 464 +** 465 +** a) If an unusable MATCH operator is present in the WHERE clause, the 466 +** cost is unconditionally set to 1e50 (a really big number). 467 +** 468 +** a) If a MATCH operator is present, the cost depends on the other 469 +** constraints also present. As follows: 470 +** 471 +** * No other constraints: cost=1000.0 472 +** * One rowid range constraint: cost=750.0 473 +** * Both rowid range constraints: cost=500.0 474 +** * An == rowid constraint: cost=100.0 475 +** 476 +** b) Otherwise, if there is no MATCH: 477 +** 478 +** * No other constraints: cost=1000000.0 479 +** * One rowid range constraint: cost=750000.0 480 +** * Both rowid range constraints: cost=250000.0 481 +** * An == rowid constraint: cost=10.0 482 +** 483 +** Costs are not modified by the ORDER BY clause. 433 484 */ 434 485 static int fts5BestIndexMethod(sqlite3_vtab *pVTab, sqlite3_index_info *pInfo){ 435 486 Fts5Table *pTab = (Fts5Table*)pVTab; 436 487 Fts5Config *pConfig = pTab->pConfig; 437 - int iCons; 438 - int ePlan = FTS5_PLAN_SCAN; 439 - int iRankMatch; 440 - 441 - iCons = fts5FindConstraint(pInfo,SQLITE_INDEX_CONSTRAINT_MATCH,pConfig->nCol); 442 - if( iCons>=0 ){ 443 - ePlan = FTS5_PLAN_MATCH; 444 - pInfo->estimatedCost = 1.0; 445 - }else{ 446 - iCons = fts5FindConstraint(pInfo, SQLITE_INDEX_CONSTRAINT_EQ, -1); 447 - if( iCons>=0 ){ 448 - ePlan = FTS5_PLAN_ROWID; 449 - pInfo->estimatedCost = 2.0; 450 - } 451 - } 452 - 453 - if( iCons>=0 ){ 454 - pInfo->aConstraintUsage[iCons].argvIndex = 1; 455 - pInfo->aConstraintUsage[iCons].omit = 1; 456 - }else{ 457 - pInfo->estimatedCost = 10000000.0; 458 - } 459 - 488 + int idxFlags = 0; /* Parameter passed through to xFilter() */ 489 + int bHasMatch; 490 + int iNext; 491 + int i; 492 + 493 + struct Constraint { 494 + int op; /* Mask against sqlite3_index_constraint.op */ 495 + int fts5op; /* FTS5 mask for idxFlags */ 496 + int iCol; /* 0==rowid, 1==tbl, 2==rank */ 497 + int omit; /* True to omit this if found */ 498 + int iConsIndex; /* Index in pInfo->aConstraint[] */ 499 + } aConstraint[] = { 500 + {SQLITE_INDEX_CONSTRAINT_MATCH, FTS5_BI_MATCH, 1, 1, -1}, 501 + {SQLITE_INDEX_CONSTRAINT_MATCH, FTS5_BI_RANK, 2, 1, -1}, 502 + {SQLITE_INDEX_CONSTRAINT_EQ, FTS5_BI_ROWID_EQ, 0, 0, -1}, 503 + {SQLITE_INDEX_CONSTRAINT_LT|SQLITE_INDEX_CONSTRAINT_LE, 504 + FTS5_BI_ROWID_LE, 0, 0, -1}, 505 + {SQLITE_INDEX_CONSTRAINT_GT|SQLITE_INDEX_CONSTRAINT_GE, 506 + FTS5_BI_ROWID_GE, 0, 0, -1}, 507 + }; 508 + 509 + int aColMap[3]; 510 + aColMap[0] = -1; 511 + aColMap[1] = pConfig->nCol; 512 + aColMap[2] = pConfig->nCol+1; 513 + 514 + /* Set idxFlags flags for all WHERE clause terms that will be used. */ 515 + for(i=0; i<pInfo->nConstraint; i++){ 516 + struct sqlite3_index_constraint *p = &pInfo->aConstraint[i]; 517 + int j; 518 + for(j=0; j<sizeof(aConstraint)/sizeof(aConstraint[0]); j++){ 519 + struct Constraint *pC = &aConstraint[j]; 520 + if( p->iColumn==aColMap[pC->iCol] && p->op & pC->op ){ 521 + if( p->usable ){ 522 + pC->iConsIndex = i; 523 + idxFlags |= pC->fts5op; 524 + }else if( j==0 ){ 525 + /* As there exists an unusable MATCH constraint this is an 526 + ** unusable plan. Set a prohibitively high cost. */ 527 + pInfo->estimatedCost = 1e50; 528 + return SQLITE_OK; 529 + } 530 + } 531 + } 532 + } 533 + 534 + /* Set idxFlags flags for the ORDER BY clause */ 460 535 if( pInfo->nOrderBy==1 ){ 461 536 int iSort = pInfo->aOrderBy[0].iColumn; 462 - if( iSort<0 ){ 463 - /* ORDER BY rowid [ASC|DESC] */ 464 - pInfo->orderByConsumed = 1; 465 - }else if( iSort==(pConfig->nCol+1) && ePlan==FTS5_PLAN_MATCH ){ 466 - /* ORDER BY rank [ASC|DESC] */ 537 + if( iSort==(pConfig->nCol+1) && BitFlagTest(idxFlags, FTS5_BI_MATCH) ){ 538 + idxFlags |= FTS5_BI_ORDER_RANK; 539 + }else if( iSort==-1 ){ 540 + idxFlags |= FTS5_BI_ORDER_ROWID; 541 + } 542 + if( BitFlagTest(idxFlags, FTS5_BI_ORDER_RANK|FTS5_BI_ORDER_ROWID) ){ 467 543 pInfo->orderByConsumed = 1; 468 - ePlan = FTS5_PLAN_SORTED_MATCH; 469 - } 470 - 471 - if( pInfo->orderByConsumed ){ 472 - ePlan |= pInfo->aOrderBy[0].desc ? FTS5_ORDER_DESC : FTS5_ORDER_ASC; 473 - } 474 - } 475 - 476 - iRankMatch = fts5FindConstraint( 477 - pInfo, SQLITE_INDEX_CONSTRAINT_MATCH, pConfig->nCol+1 478 - ); 479 - if( iRankMatch>=0 ){ 480 - pInfo->aConstraintUsage[iRankMatch].argvIndex = 1 + (iCons>=0); 481 - pInfo->aConstraintUsage[iRankMatch].omit = 1; 482 - } 483 - 484 - pInfo->idxNum = ePlan; 544 + if( pInfo->aOrderBy[0].desc ){ 545 + idxFlags |= FTS5_BI_ORDER_DESC; 546 + } 547 + } 548 + } 549 + 550 + /* Calculate the estimated cost based on the flags set in idxFlags. */ 551 + bHasMatch = BitFlagTest(idxFlags, FTS5_BI_MATCH); 552 + if( BitFlagTest(idxFlags, FTS5_BI_ROWID_EQ) ){ 553 + pInfo->estimatedCost = bHasMatch ? 100.0 : 10.0; 554 + }else if( BitFlagAllTest(idxFlags, FTS5_BI_ROWID_LE|FTS5_BI_ROWID_GE) ){ 555 + pInfo->estimatedCost = bHasMatch ? 500.0 : 250000.0; 556 + }else if( BitFlagTest(idxFlags, FTS5_BI_ROWID_LE|FTS5_BI_ROWID_GE) ){ 557 + pInfo->estimatedCost = bHasMatch ? 750.0 : 750000.0; 558 + }else{ 559 + pInfo->estimatedCost = bHasMatch ? 1000.0 : 1000000.0; 560 + } 561 + 562 + /* Assign argvIndex values to each constraint in use. */ 563 + iNext = 1; 564 + for(i=0; i<sizeof(aConstraint)/sizeof(aConstraint[0]); i++){ 565 + struct Constraint *pC = &aConstraint[i]; 566 + if( pC->iConsIndex>=0 ){ 567 + pInfo->aConstraintUsage[pC->iConsIndex].argvIndex = iNext++; 568 + pInfo->aConstraintUsage[pC->iConsIndex].omit = pC->omit; 569 + } 570 + } 571 + 572 + pInfo->idxNum = idxFlags; 485 573 return SQLITE_OK; 486 574 } 487 575 488 576 /* 489 577 ** Implementation of xOpen method. 490 578 */ 491 579 static int fts5OpenMethod(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCsr){ ................................................................................ 507 595 }else{ 508 596 rc = SQLITE_NOMEM; 509 597 } 510 598 *ppCsr = (sqlite3_vtab_cursor*)pCsr; 511 599 return rc; 512 600 } 513 601 514 -static int fts5StmtType(int idxNum){ 515 - if( FTS5_PLAN(idxNum)==FTS5_PLAN_SCAN ){ 516 - return (idxNum&FTS5_ORDER_DESC) ? FTS5_STMT_SCAN_DESC : FTS5_STMT_SCAN_ASC; 602 +static int fts5StmtType(Fts5Cursor *pCsr){ 603 + if( pCsr->ePlan==FTS5_PLAN_SCAN ){ 604 + return (pCsr->bDesc) ? FTS5_STMT_SCAN_DESC : FTS5_STMT_SCAN_ASC; 517 605 } 518 606 return FTS5_STMT_LOOKUP; 519 607 } 520 608 521 609 /* 522 610 ** This function is called after the cursor passed as the only argument 523 611 ** is moved to point at a different row. It clears all cached data ................................................................................ 540 628 Fts5Cursor *pCsr = (Fts5Cursor*)pCursor; 541 629 Fts5Cursor **pp; 542 630 Fts5Auxdata *pData; 543 631 Fts5Auxdata *pNext; 544 632 545 633 fts5CsrNewrow(pCsr); 546 634 if( pCsr->pStmt ){ 547 - int eStmt = fts5StmtType(pCsr->idxNum); 635 + int eStmt = fts5StmtType(pCsr); 548 636 sqlite3Fts5StorageStmtRelease(pTab->pStorage, eStmt, pCsr->pStmt); 549 637 } 550 638 if( pCsr->pSorter ){ 551 639 Fts5Sorter *pSorter = pCsr->pSorter; 552 640 sqlite3_finalize(pSorter->pStmt); 553 641 sqlite3_free(pSorter); 554 642 } 555 643 556 - if( pCsr->idxNum!=FTS5_PLAN_SOURCE ){ 644 + if( pCsr->ePlan!=FTS5_PLAN_SOURCE ){ 557 645 sqlite3Fts5ExprFree(pCsr->pExpr); 558 646 } 559 647 560 648 for(pData=pCsr->pAuxdata; pData; pData=pNext){ 561 649 pNext = pData->pNext; 562 650 if( pData->xDelete ) pData->xDelete(pData->pPtr); 563 651 sqlite3_free(pData); ................................................................................ 618 706 /* 619 707 ** Set the FTS5CSR_REQUIRE_RESEEK flag on all FTS5_PLAN_MATCH cursors 620 708 ** open on table pTab. 621 709 */ 622 710 static void fts5TripCursors(Fts5Table *pTab){ 623 711 Fts5Cursor *pCsr; 624 712 for(pCsr=pTab->pGlobal->pCsr; pCsr; pCsr=pCsr->pNext){ 625 - if( FTS5_PLAN(pCsr->idxNum)==FTS5_PLAN_MATCH 713 + if( pCsr->ePlan==FTS5_PLAN_MATCH 626 714 && pCsr->base.pVtab==(sqlite3_vtab*)pTab 627 715 ){ 628 716 CsrFlagSet(pCsr, FTS5CSR_REQUIRE_RESEEK); 629 717 } 630 718 } 631 719 } 632 720 ................................................................................ 643 731 ** error code if an error occurred. 644 732 */ 645 733 static int fts5CursorReseek(Fts5Cursor *pCsr, int *pbSkip){ 646 734 int rc = SQLITE_OK; 647 735 assert( *pbSkip==0 ); 648 736 if( CsrFlagTest(pCsr, FTS5CSR_REQUIRE_RESEEK) ){ 649 737 Fts5Table *pTab = (Fts5Table*)(pCsr->base.pVtab); 650 - int bDesc = ((pCsr->idxNum & FTS5_ORDER_DESC) ? 1 : 0); 738 + int bDesc = pCsr->bDesc; 651 739 i64 iRowid = sqlite3Fts5ExprRowid(pCsr->pExpr); 652 740 653 - rc = sqlite3Fts5ExprFirst(pCsr->pExpr, pTab->pIndex, bDesc); 654 - while( rc==SQLITE_OK && sqlite3Fts5ExprEof(pCsr->pExpr)==0 ){ 655 - i64 ii = sqlite3Fts5ExprRowid(pCsr->pExpr); 656 - if( ii==iRowid ) break; 657 - if( (bDesc && ii<iRowid) || (bDesc==0 && ii>iRowid) ){ 658 - *pbSkip = 1; 659 - break; 660 - } 661 - rc = sqlite3Fts5ExprNext(pCsr->pExpr); 741 + rc = sqlite3Fts5ExprFirst(pCsr->pExpr, pTab->pIndex, iRowid, bDesc); 742 + if( rc==SQLITE_OK && iRowid!=sqlite3Fts5ExprRowid(pCsr->pExpr) ){ 743 + *pbSkip = 1; 662 744 } 663 745 664 746 CsrFlagClear(pCsr, FTS5CSR_REQUIRE_RESEEK); 665 747 fts5CsrNewrow(pCsr); 666 748 if( sqlite3Fts5ExprEof(pCsr->pExpr) ){ 667 749 CsrFlagSet(pCsr, FTS5CSR_EOF); 668 750 } ................................................................................ 677 759 ** 678 760 ** Return SQLITE_OK if nothing goes wrong. SQLITE_OK is returned 679 761 ** even if we reach end-of-file. The fts5EofMethod() will be called 680 762 ** subsequently to determine whether or not an EOF was hit. 681 763 */ 682 764 static int fts5NextMethod(sqlite3_vtab_cursor *pCursor){ 683 765 Fts5Cursor *pCsr = (Fts5Cursor*)pCursor; 684 - int ePlan = FTS5_PLAN(pCsr->idxNum); 766 + int ePlan = pCsr->ePlan; 685 767 int bSkip = 0; 686 768 int rc; 687 769 688 770 if( (rc = fts5CursorReseek(pCsr, &bSkip)) || bSkip ) return rc; 689 771 690 772 switch( ePlan ){ 691 773 case FTS5_PLAN_MATCH: 692 774 case FTS5_PLAN_SOURCE: 693 - rc = sqlite3Fts5ExprNext(pCsr->pExpr); 775 + rc = sqlite3Fts5ExprNext(pCsr->pExpr, pCsr->iLastRowid); 694 776 if( sqlite3Fts5ExprEof(pCsr->pExpr) ){ 695 777 CsrFlagSet(pCsr, FTS5CSR_EOF); 696 778 } 697 779 fts5CsrNewrow(pCsr); 698 780 break; 699 781 700 782 case FTS5_PLAN_SPECIAL: { ................................................................................ 773 855 } 774 856 775 857 return rc; 776 858 } 777 859 778 860 static int fts5CursorFirst(Fts5Table *pTab, Fts5Cursor *pCsr, int bDesc){ 779 861 int rc; 780 - rc = sqlite3Fts5ExprFirst(pCsr->pExpr, pTab->pIndex, bDesc); 781 - if( sqlite3Fts5ExprEof(pCsr->pExpr) ){ 862 + Fts5Expr *pExpr = pCsr->pExpr; 863 + rc = sqlite3Fts5ExprFirst(pExpr, pTab->pIndex, pCsr->iFirstRowid, bDesc); 864 + if( sqlite3Fts5ExprEof(pExpr) ){ 782 865 CsrFlagSet(pCsr, FTS5CSR_EOF); 783 866 } 784 867 fts5CsrNewrow(pCsr); 785 868 return rc; 786 869 } 787 870 788 871 /* ................................................................................ 800 883 const char *z = zQuery; /* Special query text */ 801 884 int n; /* Number of bytes in text at z */ 802 885 803 886 while( z[0]==' ' ) z++; 804 887 for(n=0; z[n] && z[n]!=' '; n++); 805 888 806 889 assert( pTab->base.zErrMsg==0 ); 807 - pCsr->idxNum = FTS5_PLAN_SPECIAL; 890 + pCsr->ePlan = FTS5_PLAN_SPECIAL; 808 891 809 892 if( 0==sqlite3_strnicmp("reads", z, n) ){ 810 893 pCsr->iSpecial = sqlite3Fts5IndexReads(pTab->pIndex); 811 894 } 812 895 else if( 0==sqlite3_strnicmp("id", z, n) ){ 813 896 pCsr->iSpecial = pCsr->iCsrId; 814 897 } ................................................................................ 922 1005 }else{ 923 1006 pCsr->zRank = (char*)FTS5_DEFAULT_RANK; 924 1007 pCsr->zRankArgs = 0; 925 1008 } 926 1009 } 927 1010 return rc; 928 1011 } 1012 + 1013 +static i64 fts5GetRowidLimit(sqlite3_value *pVal, i64 iDefault){ 1014 + if( pVal ){ 1015 + int eType = sqlite3_value_numeric_type(pVal); 1016 + if( eType==SQLITE_INTEGER ){ 1017 + return sqlite3_value_int64(pVal); 1018 + } 1019 + } 1020 + return iDefault; 1021 +} 929 1022 930 1023 /* 931 1024 ** This is the xFilter interface for the virtual table. See 932 1025 ** the virtual table xFilter method documentation for additional 933 1026 ** information. 934 1027 ** 935 1028 ** There are three possible query strategies: ................................................................................ 943 1036 int idxNum, /* Strategy index */ 944 1037 const char *idxStr, /* Unused */ 945 1038 int nVal, /* Number of elements in apVal */ 946 1039 sqlite3_value **apVal /* Arguments for the indexing scheme */ 947 1040 ){ 948 1041 Fts5Table *pTab = (Fts5Table*)(pCursor->pVtab); 949 1042 Fts5Cursor *pCsr = (Fts5Cursor*)pCursor; 950 - int bDesc = ((idxNum & FTS5_ORDER_DESC) ? 1 : 0); 951 - int rc = SQLITE_OK; 1043 + int rc = SQLITE_OK; /* Error code */ 1044 + int iVal = 0; /* Counter for apVal[] */ 1045 + int bDesc; /* True if ORDER BY [rank|rowid] DESC */ 1046 + int bOrderByRank; /* True if ORDER BY rank */ 1047 + sqlite3_value *pMatch = 0; /* <tbl> MATCH ? expression (or NULL) */ 1048 + sqlite3_value *pRank = 0; /* rank MATCH ? expression (or NULL) */ 1049 + sqlite3_value *pRowidEq = 0; /* rowid = ? expression (or NULL) */ 1050 + sqlite3_value *pRowidLe = 0; /* rowid <= ? expression (or NULL) */ 1051 + sqlite3_value *pRowidGe = 0; /* rowid >= ? expression (or NULL) */ 952 1052 char **pzErrmsg = pTab->pConfig->pzErrmsg; 953 1053 954 - assert( pzErrmsg==0 || pzErrmsg==&pTab->base.zErrMsg ); 955 - pTab->pConfig->pzErrmsg = &pTab->base.zErrMsg; 956 - 957 - assert( nVal<=2 ); 958 1054 assert( pCsr->pStmt==0 ); 959 1055 assert( pCsr->pExpr==0 ); 960 1056 assert( pCsr->csrflags==0 ); 961 1057 assert( pCsr->pRank==0 ); 962 1058 assert( pCsr->zRank==0 ); 963 1059 assert( pCsr->zRankArgs==0 ); 1060 + 1061 + assert( pzErrmsg==0 || pzErrmsg==&pTab->base.zErrMsg ); 1062 + pTab->pConfig->pzErrmsg = &pTab->base.zErrMsg; 1063 + 1064 + /* Decode the arguments passed through to this function. 1065 + ** 1066 + ** Note: The following set of if(...) statements must be in the same 1067 + ** order as the corresponding entries in the struct at the top of 1068 + ** fts5BestIndexMethod(). */ 1069 + if( BitFlagTest(idxNum, FTS5_BI_MATCH) ) pMatch = apVal[iVal++]; 1070 + if( BitFlagTest(idxNum, FTS5_BI_RANK) ) pRank = apVal[iVal++]; 1071 + if( BitFlagTest(idxNum, FTS5_BI_ROWID_EQ) ) pRowidEq = apVal[iVal++]; 1072 + if( BitFlagTest(idxNum, FTS5_BI_ROWID_LE) ) pRowidLe = apVal[iVal++]; 1073 + if( BitFlagTest(idxNum, FTS5_BI_ROWID_GE) ) pRowidGe = apVal[iVal++]; 1074 + assert( iVal==nVal ); 1075 + bOrderByRank = ((idxNum & FTS5_BI_ORDER_RANK) ? 1 : 0); 1076 + pCsr->bDesc = bDesc = ((idxNum & FTS5_BI_ORDER_DESC) ? 1 : 0); 1077 + 1078 + /* Set the cursor upper and lower rowid limits. Only some strategies 1079 + ** actually use them. This is ok, as the xBestIndex() method leaves the 1080 + ** sqlite3_index_constraint.omit flag clear for range constraints 1081 + ** on the rowid field. */ 1082 + if( pRowidEq ){ 1083 + pRowidLe = pRowidGe = pRowidEq; 1084 + } 1085 + if( bDesc ){ 1086 + pCsr->iFirstRowid = fts5GetRowidLimit(pRowidLe, LARGEST_INT64); 1087 + pCsr->iLastRowid = fts5GetRowidLimit(pRowidGe, SMALLEST_INT64); 1088 + }else{ 1089 + pCsr->iLastRowid = fts5GetRowidLimit(pRowidLe, LARGEST_INT64); 1090 + pCsr->iFirstRowid = fts5GetRowidLimit(pRowidGe, SMALLEST_INT64); 1091 + } 964 1092 965 1093 if( pTab->pSortCsr ){ 966 1094 /* If pSortCsr is non-NULL, then this call is being made as part of 967 1095 ** processing for a "... MATCH <expr> ORDER BY rank" query (ePlan is 968 1096 ** set to FTS5_PLAN_SORTED_MATCH). pSortCsr is the cursor that will 969 1097 ** return results to the user for this query. The current cursor 970 1098 ** (pCursor) is used to execute the query issued by function 971 1099 ** fts5CursorFirstSorted() above. */ 972 - assert( FTS5_PLAN(idxNum)==FTS5_PLAN_SCAN ); 973 - pCsr->idxNum = FTS5_PLAN_SOURCE; 1100 + assert( pRowidEq==0 && pRowidLe==0 && pRowidGe==0 && pRank==0 ); 1101 + assert( nVal==0 && pMatch==0 && bOrderByRank==0 && bDesc==0 ); 1102 + assert( pCsr->iLastRowid==LARGEST_INT64 ); 1103 + assert( pCsr->iFirstRowid==SMALLEST_INT64 ); 1104 + pCsr->ePlan = FTS5_PLAN_SOURCE; 974 1105 pCsr->pExpr = pTab->pSortCsr->pExpr; 975 1106 rc = fts5CursorFirst(pTab, pCsr, bDesc); 976 - }else{ 977 - int ePlan = FTS5_PLAN(idxNum); 978 - pCsr->idxNum = idxNum; 979 - if( ePlan==FTS5_PLAN_MATCH || ePlan==FTS5_PLAN_SORTED_MATCH ){ 980 - const char *zExpr = (const char*)sqlite3_value_text(apVal[0]); 981 - 982 - rc = fts5CursorParseRank(pTab->pConfig, pCsr, (nVal==2 ? apVal[1] : 0)); 983 - if( rc==SQLITE_OK ){ 984 - if( zExpr[0]=='*' ){ 985 - /* The user has issued a query of the form "MATCH '*...'". This 986 - ** indicates that the MATCH expression is not a full text query, 987 - ** but a request for an internal parameter. */ 988 - rc = fts5SpecialMatch(pTab, pCsr, &zExpr[1]); 989 - }else{ 990 - char **pzErr = &pTab->base.zErrMsg; 991 - rc = sqlite3Fts5ExprNew(pTab->pConfig, zExpr, &pCsr->pExpr, pzErr); 992 - if( rc==SQLITE_OK ){ 993 - if( ePlan==FTS5_PLAN_MATCH ){ 994 - rc = fts5CursorFirst(pTab, pCsr, bDesc); 995 - }else{ 996 - rc = fts5CursorFirstSorted(pTab, pCsr, bDesc); 997 - } 1107 + }else if( pMatch ){ 1108 + const char *zExpr = (const char*)sqlite3_value_text(apVal[0]); 1109 + 1110 + rc = fts5CursorParseRank(pTab->pConfig, pCsr, pRank); 1111 + if( rc==SQLITE_OK ){ 1112 + if( zExpr[0]=='*' ){ 1113 + /* The user has issued a query of the form "MATCH '*...'". This 1114 + ** indicates that the MATCH expression is not a full text query, 1115 + ** but a request for an internal parameter. */ 1116 + rc = fts5SpecialMatch(pTab, pCsr, &zExpr[1]); 1117 + }else{ 1118 + char **pzErr = &pTab->base.zErrMsg; 1119 + rc = sqlite3Fts5ExprNew(pTab->pConfig, zExpr, &pCsr->pExpr, pzErr); 1120 + if( rc==SQLITE_OK ){ 1121 + if( bOrderByRank ){ 1122 + pCsr->ePlan = FTS5_PLAN_SORTED_MATCH; 1123 + rc = fts5CursorFirstSorted(pTab, pCsr, bDesc); 1124 + }else{ 1125 + pCsr->ePlan = FTS5_PLAN_MATCH; 1126 + rc = fts5CursorFirst(pTab, pCsr, bDesc); 998 1127 } 999 1128 } 1000 1129 } 1001 - }else{ 1002 - /* This is either a full-table scan (ePlan==FTS5_PLAN_SCAN) or a lookup 1003 - ** by rowid (ePlan==FTS5_PLAN_ROWID). */ 1004 - int eStmt = fts5StmtType(idxNum); 1005 - rc = sqlite3Fts5StorageStmt( 1006 - pTab->pStorage, eStmt, &pCsr->pStmt, &pTab->base.zErrMsg 1007 - ); 1008 - if( rc==SQLITE_OK ){ 1009 - if( ePlan==FTS5_PLAN_ROWID ){ 1010 - sqlite3_bind_value(pCsr->pStmt, 1, apVal[0]); 1011 - } 1012 - rc = fts5NextMethod(pCursor); 1130 + } 1131 + }else{ 1132 + /* This is either a full-table scan (ePlan==FTS5_PLAN_SCAN) or a lookup 1133 + ** by rowid (ePlan==FTS5_PLAN_ROWID). */ 1134 + pCsr->ePlan = (pRowidEq ? FTS5_PLAN_ROWID : FTS5_PLAN_SCAN); 1135 + rc = sqlite3Fts5StorageStmt( 1136 + pTab->pStorage, fts5StmtType(pCsr), &pCsr->pStmt, &pTab->base.zErrMsg 1137 + ); 1138 + if( rc==SQLITE_OK ){ 1139 + if( pCsr->ePlan==FTS5_PLAN_ROWID ){ 1140 + sqlite3_bind_value(pCsr->pStmt, 1, apVal[0]); 1013 1141 } 1142 + rc = fts5NextMethod(pCursor); 1014 1143 } 1015 1144 } 1016 1145 1017 1146 pTab->pConfig->pzErrmsg = pzErrmsg; 1018 1147 return rc; 1019 1148 } 1020 1149 ................................................................................ 1027 1156 return (CsrFlagTest(pCsr, FTS5CSR_EOF) ? 1 : 0); 1028 1157 } 1029 1158 1030 1159 /* 1031 1160 ** Return the rowid that the cursor currently points to. 1032 1161 */ 1033 1162 static i64 fts5CursorRowid(Fts5Cursor *pCsr){ 1034 - assert( FTS5_PLAN(pCsr->idxNum)==FTS5_PLAN_MATCH 1035 - || FTS5_PLAN(pCsr->idxNum)==FTS5_PLAN_SORTED_MATCH 1036 - || FTS5_PLAN(pCsr->idxNum)==FTS5_PLAN_SOURCE 1163 + assert( pCsr->ePlan==FTS5_PLAN_MATCH 1164 + || pCsr->ePlan==FTS5_PLAN_SORTED_MATCH 1165 + || pCsr->ePlan==FTS5_PLAN_SOURCE 1037 1166 ); 1038 1167 if( pCsr->pSorter ){ 1039 1168 return pCsr->pSorter->iRowid; 1040 1169 }else{ 1041 1170 return sqlite3Fts5ExprRowid(pCsr->pExpr); 1042 1171 } 1043 1172 } ................................................................................ 1046 1175 ** This is the xRowid method. The SQLite core calls this routine to 1047 1176 ** retrieve the rowid for the current row of the result set. fts5 1048 1177 ** exposes %_content.docid as the rowid for the virtual table. The 1049 1178 ** rowid should be written to *pRowid. 1050 1179 */ 1051 1180 static int fts5RowidMethod(sqlite3_vtab_cursor *pCursor, sqlite_int64 *pRowid){ 1052 1181 Fts5Cursor *pCsr = (Fts5Cursor*)pCursor; 1053 - int ePlan = FTS5_PLAN(pCsr->idxNum); 1182 + int ePlan = pCsr->ePlan; 1054 1183 1055 1184 assert( CsrFlagTest(pCsr, FTS5CSR_EOF)==0 ); 1056 1185 switch( ePlan ){ 1057 1186 case FTS5_PLAN_SPECIAL: 1058 1187 *pRowid = 0; 1059 1188 break; 1060 1189 ................................................................................ 1078 1207 */ 1079 1208 static int fts5SeekCursor(Fts5Cursor *pCsr){ 1080 1209 int rc = SQLITE_OK; 1081 1210 1082 1211 /* If the cursor does not yet have a statement handle, obtain one now. */ 1083 1212 if( pCsr->pStmt==0 ){ 1084 1213 Fts5Table *pTab = (Fts5Table*)(pCsr->base.pVtab); 1085 - int eStmt = fts5StmtType(pCsr->idxNum); 1214 + int eStmt = fts5StmtType(pCsr); 1086 1215 rc = sqlite3Fts5StorageStmt( 1087 1216 pTab->pStorage, eStmt, &pCsr->pStmt, &pTab->base.zErrMsg 1088 1217 ); 1089 1218 assert( CsrFlagTest(pCsr, FTS5CSR_REQUIRE_CONTENT) ); 1090 1219 } 1091 1220 1092 1221 if( rc==SQLITE_OK && CsrFlagTest(pCsr, FTS5CSR_REQUIRE_CONTENT) ){ ................................................................................ 1609 1738 Fts5Table *pTab = (Fts5Table*)(pCsr->base.pVtab); 1610 1739 int rc; 1611 1740 Fts5Cursor *pNew = 0; 1612 1741 1613 1742 rc = fts5OpenMethod(pCsr->base.pVtab, (sqlite3_vtab_cursor**)&pNew); 1614 1743 if( rc==SQLITE_OK ){ 1615 1744 Fts5Config *pConf = pTab->pConfig; 1616 - pNew->idxNum = FTS5_PLAN_MATCH; 1745 + pNew->ePlan = FTS5_PLAN_MATCH; 1746 + pNew->iFirstRowid = SMALLEST_INT64; 1747 + pNew->iLastRowid = LARGEST_INT64; 1617 1748 pNew->base.pVtab = (sqlite3_vtab*)pTab; 1618 1749 rc = sqlite3Fts5ExprPhraseExpr(pConf, pCsr->pExpr, iPhrase, &pNew->pExpr); 1619 1750 } 1620 1751 1621 1752 if( rc==SQLITE_OK ){ 1622 1753 for(rc = fts5CursorFirst(pTab, pNew, 0); 1623 1754 rc==SQLITE_OK && CsrFlagTest(pNew, FTS5CSR_EOF)==0; ................................................................................ 1757 1888 Fts5Table *pTab = (Fts5Table*)(pCursor->pVtab); 1758 1889 Fts5Config *pConfig = pTab->pConfig; 1759 1890 Fts5Cursor *pCsr = (Fts5Cursor*)pCursor; 1760 1891 int rc = SQLITE_OK; 1761 1892 1762 1893 assert( CsrFlagTest(pCsr, FTS5CSR_EOF)==0 ); 1763 1894 1764 - if( pCsr->idxNum==FTS5_PLAN_SPECIAL ){ 1895 + if( pCsr->ePlan==FTS5_PLAN_SPECIAL ){ 1765 1896 if( iCol==pConfig->nCol ){ 1766 1897 sqlite3_result_int64(pCtx, pCsr->iSpecial); 1767 1898 } 1768 1899 }else 1769 1900 1770 1901 if( iCol==pConfig->nCol ){ 1771 1902 /* User is requesting the value of the special column with the same name ................................................................................ 1772 1903 ** as the table. Return the cursor integer id number. This value is only 1773 1904 ** useful in that it may be passed as the first argument to an FTS5 1774 1905 ** auxiliary function. */ 1775 1906 sqlite3_result_int64(pCtx, pCsr->iCsrId); 1776 1907 }else if( iCol==pConfig->nCol+1 ){ 1777 1908 1778 1909 /* The value of the "rank" column. */ 1779 - if( FTS5_PLAN(pCsr->idxNum)==FTS5_PLAN_SOURCE ){ 1910 + if( pCsr->ePlan==FTS5_PLAN_SOURCE ){ 1780 1911 fts5PoslistBlob(pCtx, pCsr); 1781 1912 }else if( 1782 - FTS5_PLAN(pCsr->idxNum)==FTS5_PLAN_MATCH 1783 - || FTS5_PLAN(pCsr->idxNum)==FTS5_PLAN_SORTED_MATCH 1913 + pCsr->ePlan==FTS5_PLAN_MATCH 1914 + || pCsr->ePlan==FTS5_PLAN_SORTED_MATCH 1784 1915 ){ 1785 1916 if( pCsr->pRank || SQLITE_OK==(rc = fts5FindRankFunction(pCsr)) ){ 1786 1917 fts5ApiInvoke(pCsr->pRank, pCsr, pCtx, pCsr->nRankArg, pCsr->apRankArg); 1787 1918 } 1788 1919 } 1789 1920 }else if( !fts5IsContentless(pTab) ){ 1790 1921 rc = fts5SeekCursor(pCsr);
Changes to ext/fts5/fts5Int.h.
562 562 ** rc==SQLITE_OK && 0==sqlite3Fts5ExprEof(pExpr); 563 563 ** rc = sqlite3Fts5ExprNext(pExpr) 564 564 ** ){ 565 565 ** // The document with rowid iRowid matches the expression! 566 566 ** i64 iRowid = sqlite3Fts5ExprRowid(pExpr); 567 567 ** } 568 568 */ 569 -int sqlite3Fts5ExprFirst(Fts5Expr*, Fts5Index *pIdx, int bDesc); 570 -int sqlite3Fts5ExprNext(Fts5Expr*); 569 +int sqlite3Fts5ExprFirst(Fts5Expr*, Fts5Index *pIdx, i64 iMin, int bDesc); 570 +int sqlite3Fts5ExprNext(Fts5Expr*, i64 iMax); 571 571 int sqlite3Fts5ExprEof(Fts5Expr*); 572 572 i64 sqlite3Fts5ExprRowid(Fts5Expr*); 573 573 574 574 void sqlite3Fts5ExprFree(Fts5Expr*); 575 575 576 576 /* Called during startup to register a UDF with SQLite */ 577 577 int sqlite3Fts5ExprInit(Fts5Global*, sqlite3*);
Changes to ext/fts5/fts5_expr.c.
1189 1189 } 1190 1190 return rc; 1191 1191 } 1192 1192 1193 1193 1194 1194 /* 1195 1195 ** Begin iterating through the set of documents in index pIdx matched by 1196 -** the MATCH expression passed as the first argument. If the "bDesc" parameter 1197 -** is passed a non-zero value, iteration is in descending rowid order. Or, 1198 -** if it is zero, in ascending order. 1196 +** the MATCH expression passed as the first argument. If the "bDesc" 1197 +** parameter is passed a non-zero value, iteration is in descending rowid 1198 +** order. Or, if it is zero, in ascending order. 1199 +** 1200 +** If iterating in ascending rowid order (bDesc==0), the first document 1201 +** visited is that with the smallest rowid that is larger than or equal 1202 +** to parameter iFirst. Or, if iterating in ascending order (bDesc==1), 1203 +** then the first document visited must have a rowid smaller than or 1204 +** equal to iFirst. 1199 1205 ** 1200 1206 ** Return SQLITE_OK if successful, or an SQLite error code otherwise. It 1201 1207 ** is not considered an error if the query does not match any documents. 1202 1208 */ 1203 -int sqlite3Fts5ExprFirst(Fts5Expr *p, Fts5Index *pIdx, int bDesc){ 1209 +int sqlite3Fts5ExprFirst(Fts5Expr *p, Fts5Index *pIdx, i64 iFirst, int bDesc){ 1204 1210 Fts5ExprNode *pRoot = p->pRoot; 1205 1211 int rc = SQLITE_OK; 1206 1212 if( pRoot ){ 1207 1213 p->pIndex = pIdx; 1208 1214 p->bDesc = bDesc; 1209 1215 rc = fts5ExprNodeFirst(p, pRoot); 1210 1216 1217 + /* If not at EOF but the current rowid occurs earlier than iFirst in 1218 + ** the iteration order, move to document iFirst or later. */ 1219 + if( pRoot->bEof==0 && fts5RowidCmp(p, pRoot->iRowid, iFirst)<0 ){ 1220 + rc = fts5ExprNodeNext(p, pRoot, 1, iFirst); 1221 + } 1222 + 1223 + /* If the iterator is not at a real match, skip forward until it is. */ 1211 1224 while( pRoot->bNomatch && rc==SQLITE_OK && pRoot->bEof==0 ){ 1212 1225 rc = fts5ExprNodeNext(p, pRoot, 0, 0); 1213 1226 } 1214 1227 } 1215 1228 return rc; 1216 1229 } 1217 1230 1218 1231 /* 1219 1232 ** Move to the next document 1220 1233 ** 1221 1234 ** Return SQLITE_OK if successful, or an SQLite error code otherwise. It 1222 1235 ** is not considered an error if the query does not match any documents. 1223 1236 */ 1224 -int sqlite3Fts5ExprNext(Fts5Expr *p){ 1237 +int sqlite3Fts5ExprNext(Fts5Expr *p, i64 iLast){ 1225 1238 int rc; 1226 1239 Fts5ExprNode *pRoot = p->pRoot; 1227 1240 do { 1228 1241 rc = fts5ExprNodeNext(p, pRoot, 0, 0); 1229 1242 }while( pRoot->bNomatch && pRoot->bEof==0 && rc==SQLITE_OK ); 1243 + if( fts5RowidCmp(p, pRoot->iRowid, iLast)>0 ){ 1244 + pRoot->bEof = 1; 1245 + } 1230 1246 return rc; 1231 1247 } 1232 1248 1233 1249 int sqlite3Fts5ExprEof(Fts5Expr *p){ 1234 1250 return (p->pRoot==0 || p->pRoot->bEof); 1235 1251 } 1236 1252
Changes to ext/fts5/test/fts5ah.test.
99 99 puts -nonewline "(n=$n nReadX=$nReadX)" 100 100 expr {$n < ($nReadX / 8)} 101 101 } {1} 102 102 103 103 do_execsql_test 1.6.$tn.3 $q [lsort -int -incr $res] 104 104 do_execsql_test 1.6.$tn.4 "$q ORDER BY rowid DESC" [lsort -int -decr $res] 105 105 } 106 + 107 +#------------------------------------------------------------------------- 108 +# Now test that adding range constraints on the rowid field reduces the 109 +# number of pages loaded from disk. 110 +# 111 +foreach {tn fraction tail cnt} { 112 + 1 0.6 {rowid > 5000} 5000 113 + 2 0.2 {rowid > 9000} 1000 114 + 3 0.2 {rowid < 1000} 999 115 + 4 0.2 {rowid BETWEEN 4000 AND 5000} 1001 116 + 5 0.6 {rowid >= 5000} 5001 117 + 6 0.2 {rowid >= 9000} 1001 118 + 7 0.2 {rowid <= 1000} 1000 119 + 8 0.6 {rowid > '5000'} 5000 120 + 9 0.2 {rowid > '9000'} 1000 121 + 10 0.1 {rowid = 444} 1 122 +} { 123 + set q "SELECT rowid FROM t1 WHERE t1 MATCH 'x' AND $tail" 124 + set n [execsql_reads $q] 125 + set ret [llength [execsql $q]] 126 + 127 + do_test "1.7.$tn.asc.(n=$n ret=$ret)" { 128 + expr {$n < ($fraction*$nReadX) && $ret==$cnt} 129 + } {1} 130 + 131 + set q "SELECT rowid FROM t1 WHERE t1 MATCH 'x' AND $tail ORDER BY rowid DESC" 132 + set n [execsql_reads $q] 133 + set ret [llength [execsql $q]] 134 + do_test "1.7.$tn.desc.(n=$n ret=$ret)" { 135 + expr {$n < 2*$fraction*$nReadX && $ret==$cnt} 136 + } {1} 137 +} 138 + 139 +do_execsql_test 1.8.1 { 140 + SELECT count(*) FROM t1 WHERE t1 MATCH 'x' AND +rowid < 'text'; 141 +} {10000} 142 +do_execsql_test 1.8.2 { 143 + SELECT count(*) FROM t1 WHERE t1 MATCH 'x' AND rowid < 'text'; 144 +} {10000} 145 + 106 146 107 147 #db eval {SELECT rowid, fts5_decode(rowid, block) aS r FROM t1_data} {puts $r} 108 148 109 149 finish_test 110 150
Changes to ext/fts5/test/fts5plan.test.
20 20 CREATE VIRTUAL TABLE f1 USING fts5(ff); 21 21 } 22 22 23 23 do_eqp_test 1.1 { 24 24 SELECT * FROM t1, f1 WHERE f1 MATCH t1.x 25 25 } { 26 26 0 0 0 {SCAN TABLE t1} 27 - 0 1 1 {SCAN TABLE f1 VIRTUAL TABLE INDEX 2:} 27 + 0 1 1 {SCAN TABLE f1 VIRTUAL TABLE INDEX 1:} 28 28 } 29 29 30 30 do_eqp_test 1.2 { 31 31 SELECT * FROM t1, f1 WHERE f1 > t1.x 32 32 } { 33 - 0 0 1 {SCAN TABLE f1 VIRTUAL TABLE INDEX 1:} 33 + 0 0 1 {SCAN TABLE f1 VIRTUAL TABLE INDEX 0:} 34 34 0 1 0 {SCAN TABLE t1} 35 35 } 36 36 37 37 do_eqp_test 1.3 { 38 38 SELECT * FROM f1 WHERE f1 MATCH ? ORDER BY ff 39 39 } { 40 - 0 0 0 {SCAN TABLE f1 VIRTUAL TABLE INDEX 2:} 40 + 0 0 0 {SCAN TABLE f1 VIRTUAL TABLE INDEX 1:} 41 41 0 0 0 {USE TEMP B-TREE FOR ORDER BY} 42 42 } 43 43 44 44 do_eqp_test 1.4 { 45 45 SELECT * FROM f1 ORDER BY rank 46 46 } { 47 - 0 0 0 {SCAN TABLE f1 VIRTUAL TABLE INDEX 1:} 47 + 0 0 0 {SCAN TABLE f1 VIRTUAL TABLE INDEX 0:} 48 48 0 0 0 {USE TEMP B-TREE FOR ORDER BY} 49 49 } 50 50 51 51 do_eqp_test 1.5 { 52 52 SELECT * FROM f1 WHERE rank MATCH ? 53 53 } { 54 - 0 0 0 {SCAN TABLE f1 VIRTUAL TABLE INDEX 1:} 54 + 0 0 0 {SCAN TABLE f1 VIRTUAL TABLE INDEX 2:} 55 55 } 56 56 57 57 58 58 59 59 60 60 finish_test 61 61