Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Increase the estimated cost of using a virtual table as the outer loop of a join when there exists an ORDER BY clause that is not satisfied by the virtual table. Fix for [775b39dd3c]. |
|---|---|
| Downloads: | Tarball | ZIP archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
9e075e70f0e2a1ad302d17150cd58f91 |
| User & Date: | dan 2010-03-27 09:44:42.000 |
Context
|
2010-03-29
| ||
| 21:22 | Pull the experimental file-size-in-header changes into the trunk. (check-in: 0092b36c7b user: drh tags: trunk) | |
|
2010-03-27
| ||
| 17:12 | Experimental changes that cause SQLite to use bytes 28..31 of the database header to determine the database size, rather than using the actual database size. This allows database space to be preallocated. (check-in: b844ac6fcb user: drh tags: experimental) | |
| 09:44 | Increase the estimated cost of using a virtual table as the outer loop of a join when there exists an ORDER BY clause that is not satisfied by the virtual table. Fix for [775b39dd3c]. (check-in: 9e075e70f0 user: dan tags: trunk) | |
|
2010-03-26
| ||
| 01:54 | Fix OOM error on ANALYZE with STAT2 enabled with zero-length data. Ticket [cbd054fa6b]. (check-in: c33b38b59f user: shaneh tags: trunk) | |
Changes
Changes to src/where.c.
| ︙ | ︙ | |||
1815 1816 1817 1818 1819 1820 1821 1822 1823 1824 1825 1826 1827 1828 | Table *pTab = pSrc->pTab; sqlite3_index_info *pIdxInfo; struct sqlite3_index_constraint *pIdxCons; struct sqlite3_index_constraint_usage *pUsage; WhereTerm *pTerm; int i, j; int nOrderBy; /* Make sure wsFlags is initialized to some sane value. Otherwise, if the ** malloc in allocateIndexInfo() fails and this function returns leaving ** wsFlags in an uninitialized state, the caller may behave unpredictably. */ memset(pCost, 0, sizeof(*pCost)); pCost->plan.wsFlags = WHERE_VIRTUALTABLE; | > | 1815 1816 1817 1818 1819 1820 1821 1822 1823 1824 1825 1826 1827 1828 1829 | Table *pTab = pSrc->pTab; sqlite3_index_info *pIdxInfo; struct sqlite3_index_constraint *pIdxCons; struct sqlite3_index_constraint_usage *pUsage; WhereTerm *pTerm; int i, j; int nOrderBy; double rCost; /* Make sure wsFlags is initialized to some sane value. Otherwise, if the ** malloc in allocateIndexInfo() fails and this function returns leaving ** wsFlags in an uninitialized state, the caller may behave unpredictably. */ memset(pCost, 0, sizeof(*pCost)); pCost->plan.wsFlags = WHERE_VIRTUALTABLE; |
| ︙ | ︙ | |||
1900 1901 1902 1903 1904 1905 1906 1907 1908 1909 1910 1911 1912 1913 1914 |
pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint;
for(i=0; i<pIdxInfo->nConstraint; i++){
if( pUsage[i].argvIndex>0 ){
pCost->used |= pWC->a[pIdxCons[i].iTermOffset].prereqRight;
}
}
/* The cost is not allowed to be larger than SQLITE_BIG_DBL (the
** inital value of lowestCost in this loop. If it is, then the
** (cost<lowestCost) test below will never be true.
**
** Use "(double)2" instead of "2.0" in case OMIT_FLOATING_POINT
** is defined.
*/
| > > > > > > > > > | | | 1901 1902 1903 1904 1905 1906 1907 1908 1909 1910 1911 1912 1913 1914 1915 1916 1917 1918 1919 1920 1921 1922 1923 1924 1925 1926 1927 1928 1929 1930 1931 1932 1933 1934 1935 |
pIdxCons = *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint;
for(i=0; i<pIdxInfo->nConstraint; i++){
if( pUsage[i].argvIndex>0 ){
pCost->used |= pWC->a[pIdxCons[i].iTermOffset].prereqRight;
}
}
/* If there is an ORDER BY clause, and the selected virtual table index
** does not satisfy it, increase the cost of the scan accordingly. This
** matches the processing for non-virtual tables in bestBtreeIndex().
*/
rCost = pIdxInfo->estimatedCost;
if( pOrderBy && pIdxInfo->orderByConsumed==0 ){
rCost += estLog(rCost)*rCost;
}
/* The cost is not allowed to be larger than SQLITE_BIG_DBL (the
** inital value of lowestCost in this loop. If it is, then the
** (cost<lowestCost) test below will never be true.
**
** Use "(double)2" instead of "2.0" in case OMIT_FLOATING_POINT
** is defined.
*/
if( (SQLITE_BIG_DBL/((double)2))<rCost ){
pCost->rCost = (SQLITE_BIG_DBL/((double)2));
}else{
pCost->rCost = rCost;
}
pCost->plan.u.pVtabIdx = pIdxInfo;
if( pIdxInfo->orderByConsumed ){
pCost->plan.wsFlags |= WHERE_ORDERBY;
}
pCost->plan.nEq = 0;
pIdxInfo->nOrderBy = nOrderBy;
|
| ︙ | ︙ |
Changes to test/fts3query.test.
| ︙ | ︙ | |||
96 97 98 99 100 101 102 103 104 105 |
return $r
}
db func mit mit
do_test fts3query-3.3 {
execsql { SELECT mit(matchinfo(foobar)) FROM foobar WHERE foobar MATCH 'the' }
} {{1 1 3 3 1}}
finish_test
| > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 96 97 98 99 100 101 102 103 104 105 106 107 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 |
return $r
}
db func mit mit
do_test fts3query-3.3 {
execsql { SELECT mit(matchinfo(foobar)) FROM foobar WHERE foobar MATCH 'the' }
} {{1 1 3 3 1}}
# The following tests check that ticket 775b39dd3c has been fixed.
#
proc eqp {sql} {
uplevel [list execsql "EXPLAIN QUERY PLAN $sql"]
}
do_test fts3query-4.1 {
execsql {
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(number INTEGER PRIMARY KEY, date);
CREATE INDEX i1 ON t1(date);
CREATE VIRTUAL TABLE ft USING fts3(title);
CREATE TABLE bt(title);
}
} {}
do_test fts3query-4.2 {
eqp "SELECT t1.number FROM t1, ft WHERE t1.number=ft.rowid ORDER BY t1.date"
} {0 0 {TABLE t1 WITH INDEX i1 ORDER BY} 1 1 {TABLE ft VIRTUAL TABLE INDEX 1:}}
do_test fts3query-4.3 {
eqp "SELECT t1.number FROM ft, t1 WHERE t1.number=ft.rowid ORDER BY t1.date"
} {0 1 {TABLE t1 WITH INDEX i1 ORDER BY} 1 0 {TABLE ft VIRTUAL TABLE INDEX 1:}}
do_test fts3query-4.4 {
eqp "SELECT t1.number FROM t1, bt WHERE t1.number=bt.rowid ORDER BY t1.date"
} {0 0 {TABLE t1 WITH INDEX i1 ORDER BY} 1 1 {TABLE bt USING PRIMARY KEY}}
do_test fts3query-4.5 {
eqp "SELECT t1.number FROM bt, t1 WHERE t1.number=bt.rowid ORDER BY t1.date"
} {0 1 {TABLE t1 WITH INDEX i1 ORDER BY} 1 0 {TABLE bt USING PRIMARY KEY}}
finish_test
|