Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Wrap all automatic index changes inside SQLITE_OMIT_AUTOMATIC_INDEX. Add the automatic_index PRAGMA to turn it on and off. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | experimental |
| Files: | files | file ages | folders |
| SHA1: |
a811a47fbe4f757a7ab575ae5a0f6517 |
| User & Date: | drh 2010-04-07 16:54:58 |
Context
|
2010-04-07
| ||
| 19:32 | Add an interface to the SQLITE_STMTSTATUS_AUTOINDEX status information to the TCL bindings. Add some simple automatic index test cases. (check-in: 1f404412 user: drh tags: experimental) | |
| 16:54 | Wrap all automatic index changes inside SQLITE_OMIT_AUTOMATIC_INDEX. Add the automatic_index PRAGMA to turn it on and off. (check-in: a811a47f user: drh tags: experimental) | |
| 14:59 | Make sure that all automatic indices are covering indices. Otherwise, the table and index might be used together in a query but the table could get out of sync with the automatic index through out-of-band changes. (check-in: 23643131 user: drh tags: experimental) | |
Changes
Changes to src/ctime.c.
166 167 168 169 170 171 172 173 174 175 176 177 178 179 |
"OMIT_AUTHORIZATION", #endif #ifdef SQLITE_OMIT_AUTOINCREMENT "OMIT_AUTOINCREMENT", #endif #ifdef SQLITE_OMIT_AUTOINIT "OMIT_AUTOINIT", #endif #ifdef SQLITE_OMIT_AUTOVACUUM "OMIT_AUTOVACUUM", #endif #ifdef SQLITE_OMIT_BETWEEN_OPTIMIZATION "OMIT_BETWEEN_OPTIMIZATION", #endif |
> > > |
166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 |
"OMIT_AUTHORIZATION", #endif #ifdef SQLITE_OMIT_AUTOINCREMENT "OMIT_AUTOINCREMENT", #endif #ifdef SQLITE_OMIT_AUTOINIT "OMIT_AUTOINIT", #endif #ifdef SQLITE_OMIT_AUTOMATIC_INDEX "OMIT_AUTOMATIC_INDEX", #endif #ifdef SQLITE_OMIT_AUTOVACUUM "OMIT_AUTOVACUUM", #endif #ifdef SQLITE_OMIT_BETWEEN_OPTIMIZATION "OMIT_BETWEEN_OPTIMIZATION", #endif |
Changes to src/main.c.
1604 1605 1606 1607 1608 1609 1610 1611 1612 1613 1614 1615 1616 1617 1618 |
db->aDb = db->aDbStatic;
assert( sizeof(db->aLimit)==sizeof(aHardLimit) );
memcpy(db->aLimit, aHardLimit, sizeof(db->aLimit));
db->autoCommit = 1;
db->nextAutovac = -1;
db->nextPagesize = 0;
db->flags |= SQLITE_ShortColNames
#if SQLITE_DEFAULT_FILE_FORMAT<4
| SQLITE_LegacyFileFmt
#endif
#ifdef SQLITE_ENABLE_LOAD_EXTENSION
| SQLITE_LoadExtension
#endif
#if SQLITE_DEFAULT_RECURSIVE_TRIGGERS
|
| |
1604 1605 1606 1607 1608 1609 1610 1611 1612 1613 1614 1615 1616 1617 1618 |
db->aDb = db->aDbStatic;
assert( sizeof(db->aLimit)==sizeof(aHardLimit) );
memcpy(db->aLimit, aHardLimit, sizeof(db->aLimit));
db->autoCommit = 1;
db->nextAutovac = -1;
db->nextPagesize = 0;
db->flags |= SQLITE_ShortColNames | SQLITE_AutoIndex
#if SQLITE_DEFAULT_FILE_FORMAT<4
| SQLITE_LegacyFileFmt
#endif
#ifdef SQLITE_ENABLE_LOAD_EXTENSION
| SQLITE_LoadExtension
#endif
#if SQLITE_DEFAULT_RECURSIVE_TRIGGERS
|
Changes to src/pragma.c.
169 170 171 172 173 174 175 176 177 178 179 180 181 182 |
{ "full_column_names", SQLITE_FullColNames },
{ "short_column_names", SQLITE_ShortColNames },
{ "count_changes", SQLITE_CountRows },
{ "empty_result_callbacks", SQLITE_NullCallback },
{ "legacy_file_format", SQLITE_LegacyFileFmt },
{ "fullfsync", SQLITE_FullFSync },
{ "reverse_unordered_selects", SQLITE_ReverseOrder },
#ifdef SQLITE_DEBUG
{ "sql_trace", SQLITE_SqlTrace },
{ "vdbe_listing", SQLITE_VdbeListing },
{ "vdbe_trace", SQLITE_VdbeTrace },
#endif
#ifndef SQLITE_OMIT_CHECK
{ "ignore_check_constraints", SQLITE_IgnoreChecks },
|
> > > |
169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 |
{ "full_column_names", SQLITE_FullColNames },
{ "short_column_names", SQLITE_ShortColNames },
{ "count_changes", SQLITE_CountRows },
{ "empty_result_callbacks", SQLITE_NullCallback },
{ "legacy_file_format", SQLITE_LegacyFileFmt },
{ "fullfsync", SQLITE_FullFSync },
{ "reverse_unordered_selects", SQLITE_ReverseOrder },
#ifndef SQLITE_OMIT_AUTOMATIC_INDEX
{ "automatic_index", SQLITE_AutoIndex },
#endif
#ifdef SQLITE_DEBUG
{ "sql_trace", SQLITE_SqlTrace },
{ "vdbe_listing", SQLITE_VdbeListing },
{ "vdbe_trace", SQLITE_VdbeTrace },
#endif
#ifndef SQLITE_OMIT_CHECK
{ "ignore_check_constraints", SQLITE_IgnoreChecks },
|
Changes to src/sqliteInt.h.
908 909 910 911 912 913 914 915 916 917 918 919 920 921 |
#define SQLITE_LegacyFileFmt 0x00100000 /* Create new databases in format 1 */ #define SQLITE_FullFSync 0x00200000 /* Use full fsync on the backend */ #define SQLITE_LoadExtension 0x00400000 /* Enable load_extension */ #define SQLITE_RecoveryMode 0x00800000 /* Ignore schema errors */ #define SQLITE_ReverseOrder 0x01000000 /* Reverse unordered SELECTs */ #define SQLITE_RecTriggers 0x02000000 /* Enable recursive triggers */ #define SQLITE_ForeignKeys 0x04000000 /* Enforce foreign key constraints */ /* ** Bits of the sqlite3.flags field that are used by the ** sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS,...) interface. ** These must be the low-order bits of the flags field. */ #define SQLITE_QueryFlattener 0x01 /* Disable query flattening */ |
> |
908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 |
#define SQLITE_LegacyFileFmt 0x00100000 /* Create new databases in format 1 */
#define SQLITE_FullFSync 0x00200000 /* Use full fsync on the backend */
#define SQLITE_LoadExtension 0x00400000 /* Enable load_extension */
#define SQLITE_RecoveryMode 0x00800000 /* Ignore schema errors */
#define SQLITE_ReverseOrder 0x01000000 /* Reverse unordered SELECTs */
#define SQLITE_RecTriggers 0x02000000 /* Enable recursive triggers */
#define SQLITE_ForeignKeys 0x04000000 /* Enforce foreign key constraints */
#define SQLITE_AutoIndex 0x08000000 /* Enable automatic indexes */
/*
** Bits of the sqlite3.flags field that are used by the
** sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS,...) interface.
** These must be the low-order bits of the flags field.
*/
#define SQLITE_QueryFlattener 0x01 /* Disable query flattening */
|
Changes to src/test_config.c.
122 123 124 125 126 127 128 129 130 131 132 133 134 135 |
#endif #ifdef SQLITE_OMIT_AUTOINCREMENT Tcl_SetVar2(interp, "sqlite_options", "autoinc", "0", TCL_GLOBAL_ONLY); #else Tcl_SetVar2(interp, "sqlite_options", "autoinc", "1", TCL_GLOBAL_ONLY); #endif #ifdef SQLITE_OMIT_AUTOVACUUM Tcl_SetVar2(interp, "sqlite_options", "autovacuum", "0", TCL_GLOBAL_ONLY); #else Tcl_SetVar2(interp, "sqlite_options", "autovacuum", "1", TCL_GLOBAL_ONLY); #endif /* SQLITE_OMIT_AUTOVACUUM */ #if !defined(SQLITE_DEFAULT_AUTOVACUUM) |
> > > > > > |
122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 |
#endif #ifdef SQLITE_OMIT_AUTOINCREMENT Tcl_SetVar2(interp, "sqlite_options", "autoinc", "0", TCL_GLOBAL_ONLY); #else Tcl_SetVar2(interp, "sqlite_options", "autoinc", "1", TCL_GLOBAL_ONLY); #endif #ifdef SQLITE_OMIT_AUTOMATIC_INDEX Tcl_SetVar2(interp, "sqlite_options", "autoindex", "0", TCL_GLOBAL_ONLY); #else Tcl_SetVar2(interp, "sqlite_options", "autoindex", "1", TCL_GLOBAL_ONLY); #endif #ifdef SQLITE_OMIT_AUTOVACUUM Tcl_SetVar2(interp, "sqlite_options", "autovacuum", "0", TCL_GLOBAL_ONLY); #else Tcl_SetVar2(interp, "sqlite_options", "autovacuum", "1", TCL_GLOBAL_ONLY); #endif /* SQLITE_OMIT_AUTOVACUUM */ #if !defined(SQLITE_DEFAULT_AUTOVACUUM) |
Changes to src/where.c.
1633 1634 1635 1636 1637 1638 1639 1640 1641 1642 1643 1644 1645 1646 .... 1651 1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 1672 1673 1674 1675 1676 1677 1678 1679 1680 1681 1682 1683 1684 1685 1686 1687 .... 1708 1709 1710 1711 1712 1713 1714 1715 1716 1717 1718 1719 1720 1721 1722 1723 1724 1725 1726 1727 .... 1807 1808 1809 1810 1811 1812 1813 1814 1815 1816 1817 1818 1819 1820 1821 .... 1824 1825 1826 1827 1828 1829 1830 1831 1832 1833 1834 1835 1836 1837 1838 1839 1840 1841 1842 1843 1844 1845 1846 1847 1848 1849 1850 1851 1852 1853 1854 1855 1856 1857 1858 .... 2740 2741 2742 2743 2744 2745 2746 2747 2748 2749 2750 2751 2752 2753 2754 .... 4147 4148 4149 4150 4151 4152 4153 4154 4155 4156 4157 4158 4159 4160 4161 4162 4163 |
pCost->plan.u.pTerm = pTerm;
}
}
}
#endif /* SQLITE_OMIT_OR_OPTIMIZATION */
}
/*
** Return TRUE if the WHERE clause term pTerm is of a form where it
** could be used with an index to access pSrc, assuming an appropriate
** index existed.
*/
static int termCanDriveIndex(
WhereTerm *pTerm, /* WHERE clause term to check */
................................................................................
if( pTerm->leftCursor!=pSrc->iCursor ) return 0;
if( pTerm->eOperator!=WO_EQ ) return 0;
if( (pTerm->prereqRight & notReady)!=0 ) return 0;
aff = pSrc->pTab->aCol[pTerm->u.leftColumn].affinity;
if( !sqlite3IndexAffinityOk(pTerm->pExpr, aff) ) return 0;
return 1;
}
/*
** If the query plan for pSrc specified in pCost is a full table scan
** and indexing is allows (if there is no NOT INDEXED clause) and it
** possible to construct a transient index that would perform better
** than a full table scan even when the cost of constructing the index
** is taken into account, then alter the query plan to use the
** transient index.
*/
static void bestTransientIndex(
Parse *pParse, /* The parsing context */
WhereClause *pWC, /* The WHERE clause */
struct SrcList_item *pSrc, /* The FROM clause term to search */
Bitmask notReady, /* Mask of cursors that are not available */
WhereCost *pCost /* Lowest cost query plan */
){
double nTableRow; /* Rows in the input table */
double logN; /* log(nTableRow) */
double costTempIdx; /* per-query cost of the transient index */
WhereTerm *pTerm; /* A single term of the WHERE clause */
WhereTerm *pWCEnd; /* End of pWC->a[] */
Table *pTable; /* Table tht might be indexed */
if( (pCost->plan.wsFlags & WHERE_NOT_FULLSCAN)!=0 ){
/* We already have some kind of index in use for this query. */
return;
}
if( pSrc->notIndexed ){
/* The NOT INDEXED clause appears in the SQL. */
return;
................................................................................
pCost->nRow = logN + 1;
pCost->plan.wsFlags = WHERE_TEMP_INDEX;
pCost->used = pTerm->prereqRight;
break;
}
}
}
/*
** Generate code to construct a transient index. Also create the
** corresponding Index structure and put it in pLevel->plan.u.pIdx.
*/
static void constructTransientIndex(
Parse *pParse, /* The parsing context */
WhereClause *pWC, /* The WHERE clause */
struct SrcList_item *pSrc, /* The FROM clause term to get the next index */
Bitmask notReady, /* Mask of cursors that are not available */
WhereLevel *pLevel /* Write new index here */
){
int nColumn; /* Number of columns in the constructed index */
................................................................................
pColl = sqlite3BinaryCompareCollSeq(pParse, pX->pLeft, pX->pRight);
pIdx->azColl[n] = pColl->zName;
n++;
}
}
assert( n==pLevel->plan.nEq );
/* Add additional columns needed to make the index into a covering index */
for(i=0; i<mxBitCol; i++){
if( extraCols & (1<<i) ){
pIdx->aiColumn[n] = i;
pIdx->azColl[n] = "BINARY";
n++;
}
}
................................................................................
pIdx->aiColumn[n] = i;
pIdx->azColl[n] = "BINARY";
n++;
}
}
assert( n==nColumn );
/* Create the transient index */
pKeyinfo = sqlite3IndexKeyinfo(pParse, pIdx);
assert( pLevel->iIdxCur>=0 );
sqlite3VdbeAddOp4(v, OP_OpenAutoindex, pLevel->iIdxCur, nColumn+1, 0,
(char*)pKeyinfo, P4_KEYINFO_HANDOFF);
VdbeComment((v, "for %s", pTable->zName));
/* Fill the transient index with content */
addrTop = sqlite3VdbeAddOp1(v, OP_Rewind, pLevel->iTabCur);
regRecord = sqlite3GetTempReg(pParse);
sqlite3GenerateIndexKey(pParse, pIdx, pLevel->iTabCur, regRecord, 1);
sqlite3VdbeAddOp2(v, OP_IdxInsert, pLevel->iIdxCur, regRecord);
sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT);
sqlite3VdbeAddOp2(v, OP_Next, pLevel->iTabCur, addrTop+1);
sqlite3VdbeChangeP5(v, SQLITE_STMTSTATUS_AUTOINDEX);
sqlite3VdbeJumpHere(v, addrTop);
sqlite3ReleaseTempReg(pParse, regRecord);
/* Jump here when skipping the initialization */
sqlite3VdbeJumpHere(v, addrInit);
}
#ifndef SQLITE_OMIT_VIRTUALTABLE
/*
** Allocate and populate an sqlite3_index_info structure. It is the
** responsibility of the caller to eventually release the structure
** by passing the pointer returned by this function to sqlite3_free().
*/
................................................................................
);
WHERETRACE(("best index is: %s\n",
(pCost->plan.u.pIdx ? pCost->plan.u.pIdx->zName : "ipk")
));
bestOrClauseIndex(pParse, pWC, pSrc, notReady, pOrderBy, pCost);
bestTransientIndex(pParse, pWC, pSrc, notReady, pCost);
pCost->plan.wsFlags |= eqTermMask;
}
/*
** Find the query plan for accessing table pSrc->pTab. Write the
** best query plan and its cost into the WhereCost object supplied
** as the last parameter. This function may calculate the cost of
................................................................................
sqlite3VdbeChangeP4(v, sqlite3VdbeCurrentAddr(v)-1,
SQLITE_INT_TO_PTR(n), P4_INT32);
assert( n<=pTab->nCol );
}
}else{
sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);
}
if( (pLevel->plan.wsFlags & WHERE_TEMP_INDEX)!=0 ){
constructTransientIndex(pParse, pWC, pTabItem, notReady, pLevel);
}else if( (pLevel->plan.wsFlags & WHERE_INDEXED)!=0 ){
Index *pIx = pLevel->plan.u.pIdx;
KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIx);
int iIdxCur = pLevel->iIdxCur;
assert( pIx->pSchema==pTab->pSchema );
assert( iIdxCur>=0 );
sqlite3VdbeAddOp4(v, OP_OpenRead, iIdxCur, pIx->tnum, iDb,
(char*)pKey, P4_KEYINFO_HANDOFF);
|
> > > | > > > > > > > > > | < > > | | > | | > | > | > > | |
1633 1634 1635 1636 1637 1638 1639 1640 1641 1642 1643 1644 1645 1646 1647 .... 1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 1672 1673 1674 1675 1676 1677 1678 1679 1680 1681 1682 1683 1684 1685 1686 1687 1688 1689 1690 1691 1692 1693 1694 .... 1715 1716 1717 1718 1719 1720 1721 1722 1723 1724 1725 1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736 1737 1738 1739 1740 .... 1820 1821 1822 1823 1824 1825 1826 1827 1828 1829 1830 1831 1832 1833 1834 1835 .... 1838 1839 1840 1841 1842 1843 1844 1845 1846 1847 1848 1849 1850 1851 1852 1853 1854 1855 1856 1857 1858 1859 1860 1861 1862 1863 1864 1865 1866 1867 1868 1869 1870 1871 1872 1873 .... 2755 2756 2757 2758 2759 2760 2761 2762 2763 2764 2765 2766 2767 2768 2769 .... 4162 4163 4164 4165 4166 4167 4168 4169 4170 4171 4172 4173 4174 4175 4176 4177 4178 4179 4180 4181 |
pCost->plan.u.pTerm = pTerm;
}
}
}
#endif /* SQLITE_OMIT_OR_OPTIMIZATION */
}
#ifndef SQLITE_OMIT_AUTOMATIC_INDEX
/*
** Return TRUE if the WHERE clause term pTerm is of a form where it
** could be used with an index to access pSrc, assuming an appropriate
** index existed.
*/
static int termCanDriveIndex(
WhereTerm *pTerm, /* WHERE clause term to check */
................................................................................
if( pTerm->leftCursor!=pSrc->iCursor ) return 0;
if( pTerm->eOperator!=WO_EQ ) return 0;
if( (pTerm->prereqRight & notReady)!=0 ) return 0;
aff = pSrc->pTab->aCol[pTerm->u.leftColumn].affinity;
if( !sqlite3IndexAffinityOk(pTerm->pExpr, aff) ) return 0;
return 1;
}
#endif
#ifndef SQLITE_OMIT_AUTOMATIC_INDEX
/*
** If the query plan for pSrc specified in pCost is a full table scan
** and indexing is allows (if there is no NOT INDEXED clause) and it
** possible to construct a transient index that would perform better
** than a full table scan even when the cost of constructing the index
** is taken into account, then alter the query plan to use the
** transient index.
*/
static void bestAutomaticIndex(
Parse *pParse, /* The parsing context */
WhereClause *pWC, /* The WHERE clause */
struct SrcList_item *pSrc, /* The FROM clause term to search */
Bitmask notReady, /* Mask of cursors that are not available */
WhereCost *pCost /* Lowest cost query plan */
){
double nTableRow; /* Rows in the input table */
double logN; /* log(nTableRow) */
double costTempIdx; /* per-query cost of the transient index */
WhereTerm *pTerm; /* A single term of the WHERE clause */
WhereTerm *pWCEnd; /* End of pWC->a[] */
Table *pTable; /* Table tht might be indexed */
if( (pParse->db->flags & SQLITE_AutoIndex)==0 ){
/* Automatic indices are disabled at run-time */
return;
}
if( (pCost->plan.wsFlags & WHERE_NOT_FULLSCAN)!=0 ){
/* We already have some kind of index in use for this query. */
return;
}
if( pSrc->notIndexed ){
/* The NOT INDEXED clause appears in the SQL. */
return;
................................................................................
pCost->nRow = logN + 1;
pCost->plan.wsFlags = WHERE_TEMP_INDEX;
pCost->used = pTerm->prereqRight;
break;
}
}
}
#else
# define bestAutomaticIndex(A,B,C,D,E) /* no-op */
#endif /* SQLITE_OMIT_AUTOMATIC_INDEX */
#ifndef SQLITE_OMIT_AUTOMATIC_INDEX
/*
** Generate code to construct the Index object for an automatic index
** and to set up the WhereLevel object pLevel so that the code generator
** makes use of the automatic index.
*/
static void constructAutomaticIndex(
Parse *pParse, /* The parsing context */
WhereClause *pWC, /* The WHERE clause */
struct SrcList_item *pSrc, /* The FROM clause term to get the next index */
Bitmask notReady, /* Mask of cursors that are not available */
WhereLevel *pLevel /* Write new index here */
){
int nColumn; /* Number of columns in the constructed index */
................................................................................
pColl = sqlite3BinaryCompareCollSeq(pParse, pX->pLeft, pX->pRight);
pIdx->azColl[n] = pColl->zName;
n++;
}
}
assert( n==pLevel->plan.nEq );
/* Add additional columns needed to make the automatic index into
** a covering index */
for(i=0; i<mxBitCol; i++){
if( extraCols & (1<<i) ){
pIdx->aiColumn[n] = i;
pIdx->azColl[n] = "BINARY";
n++;
}
}
................................................................................
pIdx->aiColumn[n] = i;
pIdx->azColl[n] = "BINARY";
n++;
}
}
assert( n==nColumn );
/* Create the automatic index */
pKeyinfo = sqlite3IndexKeyinfo(pParse, pIdx);
assert( pLevel->iIdxCur>=0 );
sqlite3VdbeAddOp4(v, OP_OpenAutoindex, pLevel->iIdxCur, nColumn+1, 0,
(char*)pKeyinfo, P4_KEYINFO_HANDOFF);
VdbeComment((v, "for %s", pTable->zName));
/* Fill the automatic index with content */
addrTop = sqlite3VdbeAddOp1(v, OP_Rewind, pLevel->iTabCur);
regRecord = sqlite3GetTempReg(pParse);
sqlite3GenerateIndexKey(pParse, pIdx, pLevel->iTabCur, regRecord, 1);
sqlite3VdbeAddOp2(v, OP_IdxInsert, pLevel->iIdxCur, regRecord);
sqlite3VdbeChangeP5(v, OPFLAG_USESEEKRESULT);
sqlite3VdbeAddOp2(v, OP_Next, pLevel->iTabCur, addrTop+1);
sqlite3VdbeChangeP5(v, SQLITE_STMTSTATUS_AUTOINDEX);
sqlite3VdbeJumpHere(v, addrTop);
sqlite3ReleaseTempReg(pParse, regRecord);
/* Jump here when skipping the initialization */
sqlite3VdbeJumpHere(v, addrInit);
}
#endif /* SQLITE_OMIT_AUTOMATIC_INDEX */
#ifndef SQLITE_OMIT_VIRTUALTABLE
/*
** Allocate and populate an sqlite3_index_info structure. It is the
** responsibility of the caller to eventually release the structure
** by passing the pointer returned by this function to sqlite3_free().
*/
................................................................................
);
WHERETRACE(("best index is: %s\n",
(pCost->plan.u.pIdx ? pCost->plan.u.pIdx->zName : "ipk")
));
bestOrClauseIndex(pParse, pWC, pSrc, notReady, pOrderBy, pCost);
bestAutomaticIndex(pParse, pWC, pSrc, notReady, pCost);
pCost->plan.wsFlags |= eqTermMask;
}
/*
** Find the query plan for accessing table pSrc->pTab. Write the
** best query plan and its cost into the WhereCost object supplied
** as the last parameter. This function may calculate the cost of
................................................................................
sqlite3VdbeChangeP4(v, sqlite3VdbeCurrentAddr(v)-1,
SQLITE_INT_TO_PTR(n), P4_INT32);
assert( n<=pTab->nCol );
}
}else{
sqlite3TableLock(pParse, iDb, pTab->tnum, 0, pTab->zName);
}
#ifndef SQLITE_OMIT_AUTOMATIC_INDEX
if( (pLevel->plan.wsFlags & WHERE_TEMP_INDEX)!=0 ){
constructAutomaticIndex(pParse, pWC, pTabItem, notReady, pLevel);
}else
#endif
if( (pLevel->plan.wsFlags & WHERE_INDEXED)!=0 ){
Index *pIx = pLevel->plan.u.pIdx;
KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIx);
int iIdxCur = pLevel->iIdxCur;
assert( pIx->pSchema==pTab->pSchema );
assert( iIdxCur>=0 );
sqlite3VdbeAddOp4(v, OP_OpenRead, iIdxCur, pIx->tnum, iDb,
(char*)pKey, P4_KEYINFO_HANDOFF);
|
Changes to test/collate4.test.
317 318 319 320 321 322 323 324 325 326 327 328 329 330 ... 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 ... 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 |
# defined collation sequences are involved.
#
# Indices may optimise WHERE clauses using <, >, <=, >=, = or IN
# operators.
#
do_test collate4-2.1.0 {
execsql {
CREATE TABLE collate4t1(a COLLATE NOCASE);
CREATE TABLE collate4t2(b COLLATE TEXT);
INSERT INTO collate4t1 VALUES('a');
INSERT INTO collate4t1 VALUES('A');
INSERT INTO collate4t1 VALUES('b');
INSERT INTO collate4t1 VALUES('B');
................................................................................
INSERT INTO collate4t2 VALUES('A');
INSERT INTO collate4t2 VALUES('Z');
}
} {}
do_test collate4-2.1.1 {
count {
SELECT * FROM collate4t2 NOT INDEXED, collate4t1 NOT INDEXED WHERE a = b;
}
} {A a A A 19}
do_test collate4-2.1.2 {
execsql {
CREATE INDEX collate4i1 ON collate4t1(a);
}
count {
................................................................................
SELECT * FROM collate4t2, collate4t1 WHERE a = b;
}
} {A a A A 5}
do_test collate4-2.1.3 {
count {
SELECT * FROM collate4t2, collate4t1 WHERE b = a;
}
} {A A 13}
do_test collate4-2.1.4 {
execsql {
DROP INDEX collate4i1;
CREATE INDEX collate4i1 ON collate4t1(a COLLATE TEXT);
}
count {
SELECT * FROM collate4t2, collate4t1 WHERE a = b;
}
} {A a A A 14}
do_test collate4-2.1.5 {
count {
SELECT * FROM collate4t2, collate4t1 WHERE b = a;
}
} {A A 4}
ifcapable subquery {
do_test collate4-2.1.6 {
|
> | | | |
317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 ... 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 ... 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 |
# defined collation sequences are involved.
#
# Indices may optimise WHERE clauses using <, >, <=, >=, = or IN
# operators.
#
do_test collate4-2.1.0 {
execsql {
PRAGMA automatic_index=OFF;
CREATE TABLE collate4t1(a COLLATE NOCASE);
CREATE TABLE collate4t2(b COLLATE TEXT);
INSERT INTO collate4t1 VALUES('a');
INSERT INTO collate4t1 VALUES('A');
INSERT INTO collate4t1 VALUES('b');
INSERT INTO collate4t1 VALUES('B');
................................................................................
INSERT INTO collate4t2 VALUES('A');
INSERT INTO collate4t2 VALUES('Z');
}
} {}
do_test collate4-2.1.1 {
count {
SELECT * FROM collate4t2, collate4t1 WHERE a = b;
}
} {A a A A 19}
do_test collate4-2.1.2 {
execsql {
CREATE INDEX collate4i1 ON collate4t1(a);
}
count {
................................................................................
SELECT * FROM collate4t2, collate4t1 WHERE a = b;
}
} {A a A A 5}
do_test collate4-2.1.3 {
count {
SELECT * FROM collate4t2, collate4t1 WHERE b = a;
}
} {A A 19}
do_test collate4-2.1.4 {
execsql {
DROP INDEX collate4i1;
CREATE INDEX collate4i1 ON collate4t1(a COLLATE TEXT);
}
count {
SELECT * FROM collate4t2, collate4t1 WHERE a = b;
}
} {A a A A 19}
do_test collate4-2.1.5 {
count {
SELECT * FROM collate4t2, collate4t1 WHERE b = a;
}
} {A A 4}
ifcapable subquery {
do_test collate4-2.1.6 {
|