Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix EXPLAIN QUERY PLAN output for indexed-expressions. Fix another obscure fault in the WHERE term scanner. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | index-expr |
| Files: | files | file ages | folders |
| SHA1: |
73d361ce9e4d72c943def8b0b3caa227 |
| User & Date: | drh 2015-08-27 20:33:38 |
Context
|
2015-08-27
| ||
| 23:42 | Fix the OR-optimization so that it always ignores subplans that do not use an index. check-in: cf452028 user: drh tags: index-expr | |
| 20:33 | Fix EXPLAIN QUERY PLAN output for indexed-expressions. Fix another obscure fault in the WHERE term scanner. check-in: 73d361ce user: drh tags: index-expr | |
| 19:56 | Fix problems in the indexed-expression handling in the optimizer. check-in: 03375017 user: drh tags: index-expr | |
Changes
Changes to src/where.c.
187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 |
&& pTerm->u.leftColumn==iColumn
&& (iColumn!=(-2)
|| sqlite3ExprCompare(pTerm->pExpr->pLeft,pScan->pIdxExpr,iCur)==0)
&& (pScan->iEquiv<=1 || !ExprHasProperty(pTerm->pExpr, EP_FromJoin))
){
if( (pTerm->eOperator & WO_EQUIV)!=0
&& pScan->nEquiv<ArraySize(pScan->aiCur)
){
int j;
pX = sqlite3ExprSkipCollate(pTerm->pExpr->pRight);
assert( pX->op==TK_COLUMN );
for(j=0; j<pScan->nEquiv; j++){
if( pScan->aiCur[j]==pX->iTable
&& pScan->aiColumn[j]==pX->iColumn ){
break;
}
}
if( j==pScan->nEquiv ){
|
> < < |
187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 |
&& pTerm->u.leftColumn==iColumn
&& (iColumn!=(-2)
|| sqlite3ExprCompare(pTerm->pExpr->pLeft,pScan->pIdxExpr,iCur)==0)
&& (pScan->iEquiv<=1 || !ExprHasProperty(pTerm->pExpr, EP_FromJoin))
){
if( (pTerm->eOperator & WO_EQUIV)!=0
&& pScan->nEquiv<ArraySize(pScan->aiCur)
&& (pX = sqlite3ExprSkipCollate(pTerm->pExpr->pRight))->op==TK_COLUMN
){
int j;
for(j=0; j<pScan->nEquiv; j++){
if( pScan->aiCur[j]==pX->iTable
&& pScan->aiColumn[j]==pX->iColumn ){
break;
}
}
if( j==pScan->nEquiv ){
|
Changes to src/wherecode.c.
36
37
38
39
40
41
42
43
44
45
46
47
48
49
..
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
|
const char *zOp /* Name of the operator */
){
if( iTerm ) sqlite3StrAccumAppend(pStr, " AND ", 5);
sqlite3StrAccumAppendAll(pStr, zColumn);
sqlite3StrAccumAppend(pStr, zOp, 1);
sqlite3StrAccumAppend(pStr, "?", 1);
}
/*
** Argument pLevel describes a strategy for scanning table pTab. This
** function appends text to pStr that describes the subset of table
** rows scanned by the strategy in the form of an SQL expression.
**
** For example, if the query:
................................................................................
** "a=? AND b>?"
*/
static void explainIndexRange(StrAccum *pStr, WhereLoop *pLoop, Table *pTab){
Index *pIndex = pLoop->u.btree.pIndex;
u16 nEq = pLoop->u.btree.nEq;
u16 nSkip = pLoop->nSkip;
int i, j;
Column *aCol = pTab->aCol;
i16 *aiColumn = pIndex->aiColumn;
if( nEq==0 && (pLoop->wsFlags&(WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))==0 ) return;
sqlite3StrAccumAppend(pStr, " (", 2);
for(i=0; i<nEq; i++){
char *z = aiColumn[i] < 0 ? "rowid" : aCol[aiColumn[i]].zName;
if( i>=nSkip ){
explainAppendTerm(pStr, i, z, "=");
}else{
if( i ) sqlite3StrAccumAppend(pStr, " AND ", 5);
sqlite3XPrintf(pStr, 0, "ANY(%s)", z);
}
}
j = i;
if( pLoop->wsFlags&WHERE_BTM_LIMIT ){
char *z = aiColumn[j] < 0 ? "rowid" : aCol[aiColumn[j]].zName;
explainAppendTerm(pStr, i++, z, ">");
}
if( pLoop->wsFlags&WHERE_TOP_LIMIT ){
char *z = aiColumn[j] < 0 ? "rowid" : aCol[aiColumn[j]].zName;
explainAppendTerm(pStr, i, z, "<");
}
sqlite3StrAccumAppend(pStr, ")", 1);
}
/*
** This function is a no-op unless currently processing an EXPLAIN QUERY PLAN
|
>
>
>
>
>
>
>
>
>
>
<
<
|
|
|
|
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
..
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
|
const char *zOp /* Name of the operator */
){
if( iTerm ) sqlite3StrAccumAppend(pStr, " AND ", 5);
sqlite3StrAccumAppendAll(pStr, zColumn);
sqlite3StrAccumAppend(pStr, zOp, 1);
sqlite3StrAccumAppend(pStr, "?", 1);
}
/*
** Return the name of the i-th column of the pIdx index.
*/
static const char *explainIndexColumnName(Index *pIdx, int i){
i = pIdx->aiColumn[i];
if( i==(-2) ) return "<expr>";
if( i==(-1) ) return "rowid";
return pIdx->pTable->aCol[i].zName;
}
/*
** Argument pLevel describes a strategy for scanning table pTab. This
** function appends text to pStr that describes the subset of table
** rows scanned by the strategy in the form of an SQL expression.
**
** For example, if the query:
................................................................................
** "a=? AND b>?"
*/
static void explainIndexRange(StrAccum *pStr, WhereLoop *pLoop, Table *pTab){
Index *pIndex = pLoop->u.btree.pIndex;
u16 nEq = pLoop->u.btree.nEq;
u16 nSkip = pLoop->nSkip;
int i, j;
if( nEq==0 && (pLoop->wsFlags&(WHERE_BTM_LIMIT|WHERE_TOP_LIMIT))==0 ) return;
sqlite3StrAccumAppend(pStr, " (", 2);
for(i=0; i<nEq; i++){
const char *z = explainIndexColumnName(pIndex, i);
if( i>=nSkip ){
explainAppendTerm(pStr, i, z, "=");
}else{
if( i ) sqlite3StrAccumAppend(pStr, " AND ", 5);
sqlite3XPrintf(pStr, 0, "ANY(%s)", z);
}
}
j = i;
if( pLoop->wsFlags&WHERE_BTM_LIMIT ){
const char *z = explainIndexColumnName(pIndex, i);
explainAppendTerm(pStr, i++, z, ">");
}
if( pLoop->wsFlags&WHERE_TOP_LIMIT ){
const char *z = explainIndexColumnName(pIndex, j);
explainAppendTerm(pStr, i, z, "<");
}
sqlite3StrAccumAppend(pStr, ")", 1);
}
/*
** This function is a no-op unless currently processing an EXPLAIN QUERY PLAN
|