Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Enable optimization of IN operators on constraints to virtual tables. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | vtab-IN-opt |
| Files: | files | file ages | folders |
| SHA1: | aa650746b19e4a6a373f7e47effff3ab |
| User & Date: | drh 2012-10-16 23:17:14 |
Context
|
2012-12-14
| ||
| 15:36 | Fix the virtual table IN optimizer so that it work even if the virtual table implementation leaves the sqlite3_index_info.aConstraintUsage[].omit flag clear for an equality constraint that it intends to use. check-in: d6e045f8 user: drh tags: vtab-IN-opt | |
|
2012-10-16
| ||
| 23:17 | Enable optimization of IN operators on constraints to virtual tables. check-in: aa650746 user: drh tags: vtab-IN-opt | |
|
2012-10-15
| ||
| 20:28 | Correct comments and enhance readability of the mkvsix tool. check-in: 2c3af657 user: mistachkin tags: trunk | |
Changes
Changes to src/where.c.
249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 .... 2034 2035 2036 2037 2038 2039 2040 2041 2042 2043 2044 2045 2046 2047 2048 .... 2082 2083 2084 2085 2086 2087 2088 2089 2090 2091 2092 2093 2094 2095 2096 2097 2098 2099 2100 2101 2102 2103 2104 2105 2106 2107 2108 2109 2110 2111 2112 2113 2114 .... 4026 4027 4028 4029 4030 4031 4032 4033 4034 4035 4036 4037 4038 4039 4040 4041 4042 4043 4044 4045 4046 4047 4048 4049 4050 4051 4052 4053 4054 4055 4056 4057 4058 4059 4060 4061 |
#define WHERE_ROWID_RANGE 0x00002000 /* rowid<EXPR and/or rowid>EXPR */ #define WHERE_COLUMN_EQ 0x00010000 /* x=EXPR or x IN (...) or x IS NULL */ #define WHERE_COLUMN_RANGE 0x00020000 /* x<EXPR and/or x>EXPR */ #define WHERE_COLUMN_IN 0x00040000 /* x IN (...) */ #define WHERE_COLUMN_NULL 0x00080000 /* x IS NULL */ #define WHERE_INDEXED 0x000f0000 /* Anything that uses an index */ #define WHERE_NOT_FULLSCAN 0x100f3000 /* Does not do a full table scan */ #define WHERE_IN_ABLE 0x000f1000 /* Able to support an IN operator */ #define WHERE_TOP_LIMIT 0x00100000 /* x<EXPR or x<=EXPR constraint */ #define WHERE_BTM_LIMIT 0x00200000 /* x>EXPR or x>=EXPR constraint */ #define WHERE_BOTH_LIMIT 0x00300000 /* Both x>EXPR and x<EXPR */ #define WHERE_IDX_ONLY 0x00400000 /* Use index only - omit table */ #define WHERE_ORDERED 0x00800000 /* Output will appear in correct order */ #define WHERE_REVERSE 0x01000000 /* Scan in reverse order */ #define WHERE_UNIQUE 0x02000000 /* Selects no more than one row */ ................................................................................ /* Count the number of possible WHERE clause constraints referring ** to this virtual table */ for(i=nTerm=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){ if( pTerm->leftCursor != pSrc->iCursor ) continue; assert( (pTerm->eOperator&(pTerm->eOperator-1))==0 ); testcase( pTerm->eOperator==WO_IN ); testcase( pTerm->eOperator==WO_ISNULL ); if( pTerm->eOperator & (WO_IN|WO_ISNULL) ) continue; if( pTerm->wtFlags & TERM_VNULL ) continue; nTerm++; } /* If the ORDER BY clause contains only columns in the current ** virtual table then allocate space for the aOrderBy part of ** the sqlite3_index_info structure. ................................................................................ *(int*)&pIdxInfo->nOrderBy = nOrderBy; *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint = pIdxCons; *(struct sqlite3_index_orderby**)&pIdxInfo->aOrderBy = pIdxOrderBy; *(struct sqlite3_index_constraint_usage**)&pIdxInfo->aConstraintUsage = pUsage; for(i=j=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){ if( pTerm->leftCursor != pSrc->iCursor ) continue; assert( (pTerm->eOperator&(pTerm->eOperator-1))==0 ); testcase( pTerm->eOperator==WO_IN ); testcase( pTerm->eOperator==WO_ISNULL ); if( pTerm->eOperator & (WO_IN|WO_ISNULL) ) continue; if( pTerm->wtFlags & TERM_VNULL ) continue; pIdxCons[j].iColumn = pTerm->u.leftColumn; pIdxCons[j].iTermOffset = i; pIdxCons[j].op = (u8)pTerm->eOperator; /* The direct assignment in the previous line is possible only because ** the WO_ and SQLITE_INDEX_CONSTRAINT_ codes are identical. The ** following asserts verify this fact. */ assert( WO_EQ==SQLITE_INDEX_CONSTRAINT_EQ ); assert( WO_LT==SQLITE_INDEX_CONSTRAINT_LT ); assert( WO_LE==SQLITE_INDEX_CONSTRAINT_LE ); assert( WO_GT==SQLITE_INDEX_CONSTRAINT_GT ); assert( WO_GE==SQLITE_INDEX_CONSTRAINT_GE ); assert( WO_MATCH==SQLITE_INDEX_CONSTRAINT_MATCH ); assert( pTerm->eOperator & (WO_EQ|WO_LT|WO_LE|WO_GT|WO_GE|WO_MATCH) ); j++; } for(i=0; i<nOrderBy; i++){ Expr *pExpr = pOrderBy->a[i].pExpr; pIdxOrderBy[i].iColumn = pExpr->iColumn; pIdxOrderBy[i].desc = pOrderBy->a[i].sortOrder; } ................................................................................ #ifndef SQLITE_OMIT_VIRTUALTABLE if( (pLevel->plan.wsFlags & WHERE_VIRTUALTABLE)!=0 ){ /* Case 0: The table is a virtual-table. Use the VFilter and VNext ** to access the data. */ int iReg; /* P3 Value for OP_VFilter */ sqlite3_index_info *pVtabIdx = pLevel->plan.u.pVtabIdx; int nConstraint = pVtabIdx->nConstraint; struct sqlite3_index_constraint_usage *aUsage = pVtabIdx->aConstraintUsage; const struct sqlite3_index_constraint *aConstraint = pVtabIdx->aConstraint; sqlite3ExprCachePush(pParse); iReg = sqlite3GetTempRange(pParse, nConstraint+2); for(j=1; j<=nConstraint; j++){ for(k=0; k<nConstraint; k++){ if( aUsage[k].argvIndex==j ){ int iTerm = aConstraint[k].iTermOffset; sqlite3ExprCode(pParse, pWC->a[iTerm].pExpr->pRight, iReg+j+1); break; } } if( k==nConstraint ) break; } sqlite3VdbeAddOp2(v, OP_Integer, pVtabIdx->idxNum, iReg); sqlite3VdbeAddOp2(v, OP_Integer, j-1, iReg+1); sqlite3VdbeAddOp4(v, OP_VFilter, iCur, addrBrk, iReg, pVtabIdx->idxStr, pVtabIdx->needToFreeIdxStr ? P4_MPRINTF : P4_STATIC); pVtabIdx->needToFreeIdxStr = 0; for(j=0; j<nConstraint; j++){ if( aUsage[j].omit ){ int iTerm = aConstraint[j].iTermOffset; disableTerm(pLevel, &pWC->a[iTerm]); } |
| | > | | > > | > > | > > > > > | > | |
249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 .... 2034 2035 2036 2037 2038 2039 2040 2041 2042 2043 2044 2045 2046 2047 2048 .... 2082 2083 2084 2085 2086 2087 2088 2089 2090 2091 2092 2093 2094 2095 2096 2097 2098 2099 2100 2101 2102 2103 2104 2105 2106 2107 2108 2109 2110 2111 2112 2113 2114 2115 2116 2117 .... 4029 4030 4031 4032 4033 4034 4035 4036 4037 4038 4039 4040 4041 4042 4043 4044 4045 4046 4047 4048 4049 4050 4051 4052 4053 4054 4055 4056 4057 4058 4059 4060 4061 4062 4063 4064 4065 4066 4067 4068 4069 4070 4071 4072 |
#define WHERE_ROWID_RANGE 0x00002000 /* rowid<EXPR and/or rowid>EXPR */ #define WHERE_COLUMN_EQ 0x00010000 /* x=EXPR or x IN (...) or x IS NULL */ #define WHERE_COLUMN_RANGE 0x00020000 /* x<EXPR and/or x>EXPR */ #define WHERE_COLUMN_IN 0x00040000 /* x IN (...) */ #define WHERE_COLUMN_NULL 0x00080000 /* x IS NULL */ #define WHERE_INDEXED 0x000f0000 /* Anything that uses an index */ #define WHERE_NOT_FULLSCAN 0x100f3000 /* Does not do a full table scan */ #define WHERE_IN_ABLE 0x080f1000 /* Able to support an IN operator */ #define WHERE_TOP_LIMIT 0x00100000 /* x<EXPR or x<=EXPR constraint */ #define WHERE_BTM_LIMIT 0x00200000 /* x>EXPR or x>=EXPR constraint */ #define WHERE_BOTH_LIMIT 0x00300000 /* Both x>EXPR and x<EXPR */ #define WHERE_IDX_ONLY 0x00400000 /* Use index only - omit table */ #define WHERE_ORDERED 0x00800000 /* Output will appear in correct order */ #define WHERE_REVERSE 0x01000000 /* Scan in reverse order */ #define WHERE_UNIQUE 0x02000000 /* Selects no more than one row */ ................................................................................ /* Count the number of possible WHERE clause constraints referring ** to this virtual table */ for(i=nTerm=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){ if( pTerm->leftCursor != pSrc->iCursor ) continue; assert( (pTerm->eOperator&(pTerm->eOperator-1))==0 ); testcase( pTerm->eOperator==WO_IN ); testcase( pTerm->eOperator==WO_ISNULL ); if( pTerm->eOperator & (WO_ISNULL) ) continue; if( pTerm->wtFlags & TERM_VNULL ) continue; nTerm++; } /* If the ORDER BY clause contains only columns in the current ** virtual table then allocate space for the aOrderBy part of ** the sqlite3_index_info structure. ................................................................................ *(int*)&pIdxInfo->nOrderBy = nOrderBy; *(struct sqlite3_index_constraint**)&pIdxInfo->aConstraint = pIdxCons; *(struct sqlite3_index_orderby**)&pIdxInfo->aOrderBy = pIdxOrderBy; *(struct sqlite3_index_constraint_usage**)&pIdxInfo->aConstraintUsage = pUsage; for(i=j=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){ u8 op; if( pTerm->leftCursor != pSrc->iCursor ) continue; assert( (pTerm->eOperator&(pTerm->eOperator-1))==0 ); testcase( pTerm->eOperator==WO_IN ); testcase( pTerm->eOperator==WO_ISNULL ); if( pTerm->eOperator & (WO_ISNULL) ) continue; if( pTerm->wtFlags & TERM_VNULL ) continue; pIdxCons[j].iColumn = pTerm->u.leftColumn; pIdxCons[j].iTermOffset = i; op = (u8)pTerm->eOperator; if( op==WO_IN ) op = WO_EQ; pIdxCons[j].op = op; /* The direct assignment in the previous line is possible only because ** the WO_ and SQLITE_INDEX_CONSTRAINT_ codes are identical. The ** following asserts verify this fact. */ assert( WO_EQ==SQLITE_INDEX_CONSTRAINT_EQ ); assert( WO_LT==SQLITE_INDEX_CONSTRAINT_LT ); assert( WO_LE==SQLITE_INDEX_CONSTRAINT_LE ); assert( WO_GT==SQLITE_INDEX_CONSTRAINT_GT ); assert( WO_GE==SQLITE_INDEX_CONSTRAINT_GE ); assert( WO_MATCH==SQLITE_INDEX_CONSTRAINT_MATCH ); assert( pTerm->eOperator & (WO_IN|WO_EQ|WO_LT|WO_LE|WO_GT|WO_GE|WO_MATCH) ); j++; } for(i=0; i<nOrderBy; i++){ Expr *pExpr = pOrderBy->a[i].pExpr; pIdxOrderBy[i].iColumn = pExpr->iColumn; pIdxOrderBy[i].desc = pOrderBy->a[i].sortOrder; } ................................................................................ #ifndef SQLITE_OMIT_VIRTUALTABLE if( (pLevel->plan.wsFlags & WHERE_VIRTUALTABLE)!=0 ){ /* Case 0: The table is a virtual-table. Use the VFilter and VNext ** to access the data. */ int iReg; /* P3 Value for OP_VFilter */ int addrNotFound; sqlite3_index_info *pVtabIdx = pLevel->plan.u.pVtabIdx; int nConstraint = pVtabIdx->nConstraint; struct sqlite3_index_constraint_usage *aUsage = pVtabIdx->aConstraintUsage; const struct sqlite3_index_constraint *aConstraint = pVtabIdx->aConstraint; sqlite3ExprCachePush(pParse); iReg = sqlite3GetTempRange(pParse, nConstraint+2); addrNotFound = pLevel->addrBrk; for(j=1; j<=nConstraint; j++){ for(k=0; k<nConstraint; k++){ if( aUsage[k].argvIndex==j ){ WhereTerm *pTerm = &pWC->a[aConstraint[k].iTermOffset]; int iTarget = iReg+j+1; if( pTerm->eOperator & WO_IN ){ codeEqualityTerm(pParse, pTerm, pLevel, iTarget); addrNotFound = pLevel->addrNxt; }else{ sqlite3ExprCode(pParse, pTerm->pExpr->pRight, iTarget); } break; } } if( k==nConstraint ) break; } sqlite3VdbeAddOp2(v, OP_Integer, pVtabIdx->idxNum, iReg); sqlite3VdbeAddOp2(v, OP_Integer, j-1, iReg+1); sqlite3VdbeAddOp4(v, OP_VFilter, iCur, addrNotFound, iReg, pVtabIdx->idxStr, pVtabIdx->needToFreeIdxStr ? P4_MPRINTF : P4_STATIC); pVtabIdx->needToFreeIdxStr = 0; for(j=0; j<nConstraint; j++){ if( aUsage[j].omit ){ int iTerm = aConstraint[j].iTermOffset; disableTerm(pLevel, &pWC->a[iTerm]); } |
Changes to test/vtab1.test.
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
....
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
|
} {15 {} 16}
do_test vtab1.13-3 {
execsql {
SELECT * FROM echo_c WHERE b IS NULL AND a = 15;
}
} {15 {} 16}
do_test vtab1-14.1 {
execsql { DELETE FROM c }
set echo_module ""
execsql { SELECT * FROM echo_c WHERE rowid IN (1, 2, 3) }
set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'c'} xFilter {SELECT rowid, * FROM 'c'}]
do_test vtab1-14.2 {
set echo_module ""
execsql { SELECT * FROM echo_c WHERE rowid = 1 }
set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'c' WHERE rowid = ?} xFilter {SELECT rowid, * FROM 'c' WHERE rowid = ?} 1]
................................................................................
set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'c' WHERE a = ?} xFilter {SELECT rowid, * FROM 'c' WHERE a = ?} 1]
do_test vtab1-14.4 {
set echo_module ""
execsql { SELECT * FROM echo_c WHERE a IN (1, 2) }
set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'c'} xFilter {SELECT rowid, * FROM 'c'}]
do_test vtab1-15.1 {
execsql {
CREATE TABLE t1(a, b, c);
CREATE VIRTUAL TABLE echo_t1 USING echo(t1);
}
} {}
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
|
|
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
....
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
|
} {15 {} 16}
do_test vtab1.13-3 {
execsql {
SELECT * FROM echo_c WHERE b IS NULL AND a = 15;
}
} {15 {} 16}
do_test vtab1-14.001 {
execsql {SELECT rowid, * FROM echo_c WHERE +rowid IN (1,2,3)}
} {1 3 G H 2 {} 15 16 3 15 {} 16}
do_test vtab1-14.002 {
execsql {SELECT rowid, * FROM echo_c WHERE rowid IN (1,2,3)}
} {1 3 G H 2 {} 15 16 3 15 {} 16}
do_test vtab1-14.003 {
execsql {SELECT rowid, * FROM echo_c WHERE +rowid IN (0,1,5,2,'a',3,NULL)}
} {1 3 G H 2 {} 15 16 3 15 {} 16}
do_test vtab1-14.004 {
execsql {SELECT rowid, * FROM echo_c WHERE rowid IN (0,1,5,'a',2,3,NULL)}
} {1 3 G H 2 {} 15 16 3 15 {} 16}
do_test vtab1-14.005 {
execsql {SELECT rowid, * FROM echo_c WHERE rowid NOT IN (0,1,5,'a',2,3)}
} {}
do_test vtab1-14.006 {
execsql {SELECT rowid, * FROM echo_c WHERE rowid NOT IN (0,5,'a',2,3)}
} {1 3 G H}
do_test vtab1-14.007 {
execsql {SELECT rowid, * FROM echo_c WHERE +rowid NOT IN (0,5,'a',2,3,NULL)}
} {}
do_test vtab1-14.008 {
execsql {SELECT rowid, * FROM echo_c WHERE rowid NOT IN (0,5,'a',2,3,NULL)}
} {}
do_test vtab1-14.011 {
execsql {SELECT * FROM echo_c WHERE +a IN (1,3,8,'x',NULL,15,24)}
} {3 G H 15 {} 16}
do_test vtab1-14.012 {
execsql {SELECT * FROM echo_c WHERE a IN (1,3,8,'x',NULL,15,24)}
} {3 G H 15 {} 16}
do_test vtab1-14.013 {
execsql {SELECT * FROM echo_c WHERE a NOT IN (1,8,'x',15,24)}
} {3 G H}
do_test vtab1-14.014 {
execsql {SELECT * FROM echo_c WHERE a NOT IN (1,8,'x',NULL,15,24)}
} {}
do_test vtab1-14.015 {
execsql {SELECT * FROM echo_c WHERE +a NOT IN (1,8,'x',NULL,15,24)}
} {}
do_test vtab1-14.1 {
execsql { DELETE FROM c }
set echo_module ""
execsql { SELECT * FROM echo_c WHERE rowid IN (1, 2, 3) }
set echo_module
} {/xBestIndex {SELECT rowid, . FROM 'c' WHERE rowid = .} xFilter {SELECT rowid, . FROM 'c' WHERE rowid = .} 1/}
do_test vtab1-14.2 {
set echo_module ""
execsql { SELECT * FROM echo_c WHERE rowid = 1 }
set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'c' WHERE rowid = ?} xFilter {SELECT rowid, * FROM 'c' WHERE rowid = ?} 1]
................................................................................
set echo_module
} [list xBestIndex {SELECT rowid, * FROM 'c' WHERE a = ?} xFilter {SELECT rowid, * FROM 'c' WHERE a = ?} 1]
do_test vtab1-14.4 {
set echo_module ""
execsql { SELECT * FROM echo_c WHERE a IN (1, 2) }
set echo_module
} {/xBestIndex {SELECT rowid, . FROM 'c' WHERE a = .} xFilter {SELECT rowid, . FROM 'c' WHERE a = .} 1/}
do_test vtab1-15.1 {
execsql {
CREATE TABLE t1(a, b, c);
CREATE VIRTUAL TABLE echo_t1 USING echo(t1);
}
} {}
|