Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch avoid-unusable-match Excluding Merge-Ins
This is equivalent to a diff from 429018b19c to 24f84b3813
2014-01-04
| ||
14:46 | Have FTS assign extremely high costs to plans that feature unusable MATCH constraints. This discourages the planner from choosing such plans, which lead to "unable to use function MATCH in the requested context" errors. (check-in: fa8be488a3 user: dan tags: trunk) | |
14:42 | Instead of having the planner ignore plans with unusable MATCH constraints, have FTS assign extremely high costs to such plans in order to discourage the planner from using them. (Closed-Leaf check-in: 24f84b3813 user: dan tags: avoid-unusable-match) | |
14:16 | Have the planner detect cases where a MATCH constraint is applied to a virtual table, and ignore any plans that do not allow the virtual table implementation to implement the MATCH filtering. (check-in: 19f3208b26 user: dan tags: avoid-unusable-match) | |
2014-01-03
| ||
16:03 | Avoid some unnecessary OP_SCopy operations when inserting into a table with multiple indices. (check-in: 429018b19c user: drh tags: trunk) | |
2014-01-02
| ||
21:05 | Try to factor constant subcomponents of the WHERE clause out of the loop. (check-in: 9d05777fe2 user: drh tags: trunk) | |
Changes to ext/fts3/fts3.c.
︙ | ︙ | |||
1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 | int argc, /* Number of elements in argv array */ const char * const *argv, /* xCreate/xConnect argument array */ sqlite3_vtab **ppVtab, /* OUT: New sqlite3_vtab object */ char **pzErr /* OUT: sqlite3_malloc'd error message */ ){ return fts3InitVtab(1, db, pAux, argc, argv, ppVtab, pzErr); } /* ** Implementation of the xBestIndex method for FTS3 tables. There ** are three possible strategies, in order of preference: ** ** 1. Direct lookup by rowid or docid. ** 2. Full-text search using a MATCH operator on a non-docid column. | > > > > > > > > > > > > > | 1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 | int argc, /* Number of elements in argv array */ const char * const *argv, /* xCreate/xConnect argument array */ sqlite3_vtab **ppVtab, /* OUT: New sqlite3_vtab object */ char **pzErr /* OUT: sqlite3_malloc'd error message */ ){ return fts3InitVtab(1, db, pAux, argc, argv, ppVtab, pzErr); } /* ** Set the pIdxInfo->estimatedRows variable to nRow. Unless this ** extension is currently being used by a version of SQLite too old to ** support estimatedRows. In that case this function is a no-op. */ static void setEstimatedRows(sqlite3_index_info *pIdxInfo, i64 nRow){ #if SQLITE_VERSION_NUMBER>=3008002 if( sqlite3_libversion_number()>=3008002 ){ pIdxInfo->estimatedRows = nRow; } #endif } /* ** Implementation of the xBestIndex method for FTS3 tables. There ** are three possible strategies, in order of preference: ** ** 1. Direct lookup by rowid or docid. ** 2. Full-text search using a MATCH operator on a non-docid column. |
︙ | ︙ | |||
1495 1496 1497 1498 1499 1500 1501 | ** strategy is possible. */ pInfo->idxNum = FTS3_FULLSCAN_SEARCH; pInfo->estimatedCost = 5000000; for(i=0; i<pInfo->nConstraint; i++){ int bDocid; /* True if this constraint is on docid */ struct sqlite3_index_constraint *pCons = &pInfo->aConstraint[i]; | | > > > > > > > > > > > > > | 1508 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523 1524 1525 1526 1527 1528 1529 1530 1531 1532 1533 1534 1535 | ** strategy is possible. */ pInfo->idxNum = FTS3_FULLSCAN_SEARCH; pInfo->estimatedCost = 5000000; for(i=0; i<pInfo->nConstraint; i++){ int bDocid; /* True if this constraint is on docid */ struct sqlite3_index_constraint *pCons = &pInfo->aConstraint[i]; if( pCons->usable==0 ){ if( pCons->op==SQLITE_INDEX_CONSTRAINT_MATCH ){ /* There exists an unusable MATCH constraint. This means that if ** the planner does elect to use the results of this call as part ** of the overall query plan the user will see an "unable to use ** function MATCH in the requested context" error. To discourage ** this, return a very high cost here. */ pInfo->idxNum = FTS3_FULLSCAN_SEARCH; pInfo->estimatedCost = 1e50; setEstimatedRows(pInfo, ((sqlite3_int64)1) << 50); return SQLITE_OK; } continue; } bDocid = (pCons->iColumn<0 || pCons->iColumn==p->nColumn+1); /* A direct lookup on the rowid or docid column. Assign a cost of 1.0. */ if( iCons<0 && pCons->op==SQLITE_INDEX_CONSTRAINT_EQ && bDocid ){ pInfo->idxNum = FTS3_DOCID_SEARCH; pInfo->estimatedCost = 1.0; |
︙ | ︙ |
Added test/fts3join.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 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 64 65 66 | # 2014 January 4 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #************************************************************************* # This file implements regression tests for SQLite library. The # focus of this script is testing the FTS3 module. # set testdir [file dirname $argv0] source $testdir/tester.tcl set ::testprefix fts3join # If SQLITE_ENABLE_FTS3 is defined, omit this file. ifcapable !fts3 { finish_test return } do_execsql_test 1.0 { CREATE VIRTUAL TABLE ft1 USING fts4(x); INSERT INTO ft1 VALUES('aaa aaa'); INSERT INTO ft1 VALUES('aaa bbb'); INSERT INTO ft1 VALUES('bbb aaa'); INSERT INTO ft1 VALUES('bbb bbb'); CREATE TABLE t1(id, y); INSERT INTO t1 VALUES(1, 'aaa'); INSERT INTO t1 VALUES(2, 'bbb'); } do_execsql_test 1.1 { SELECT docid FROM ft1, t1 WHERE ft1 MATCH y AND id=1; } {1 2 3} do_execsql_test 1.2 { SELECT docid FROM ft1, t1 WHERE ft1 MATCH y AND id=1 ORDER BY docid; } {1 2 3} do_execsql_test 2.0 { CREATE VIRTUAL TABLE ft2 USING fts4(x); CREATE VIRTUAL TABLE ft3 USING fts4(y); INSERT INTO ft2 VALUES('abc'); INSERT INTO ft2 VALUES('def'); INSERT INTO ft3 VALUES('ghi'); INSERT INTO ft3 VALUES('abc'); } do_execsql_test 2.1 { SELECT * FROM ft2, ft3 WHERE x MATCH y; } {abc abc} do_execsql_test 2.2 { SELECT * FROM ft2, ft3 WHERE y MATCH x; } {abc abc} do_execsql_test 2.3 { SELECT * FROM ft3, ft2 WHERE x MATCH y; } {abc abc} do_execsql_test 2.4 { SELECT * FROM ft3, ft2 WHERE y MATCH x; } {abc abc} do_catchsql_test 2.5 { SELECT * FROM ft3, ft2 WHERE y MATCH x AND x MATCH y; } {1 {unable to use function MATCH in the requested context}} finish_test |