Overview
| Comment: | Enhance the query planner so that IS and IS NULL operators are able to drive an index on a LEFT OUTER JOIN. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: | c648539b52ca28c0b2cb61208e2c32b1d29626a1 |
| User & Date: | drh 2016-03-31 20:40:28 |
Context
|
2016-03-31
| ||
| 22:53 | Update the clean target in the MSVC makefile. check-in: 69f4d832 user: mistachkin tags: trunk | |
| 20:40 | Enhance the query planner so that IS and IS NULL operators are able to drive an index on a LEFT OUTER JOIN. check-in: c648539b user: drh tags: trunk | |
|
2016-03-30
| ||
| 16:23 | Updates for the MSVC makefiles. check-in: 7cf0cab7 user: mistachkin tags: trunk | |
Changes
Changes to src/where.c.
2197
2198
2199
2200
2201
2202
2203
2204
2205
2206
2207
2208
2209
2210
2211
2212
....
2235
2236
2237
2238
2239
2240
2241
2242
2243
2244
2245
2246
2247
2248
|
pNew = pBuilder->pNew;
if( db->mallocFailed ) return SQLITE_NOMEM_BKPT;
assert( (pNew->wsFlags & WHERE_VIRTUALTABLE)==0 );
assert( (pNew->wsFlags & WHERE_TOP_LIMIT)==0 );
if( pNew->wsFlags & WHERE_BTM_LIMIT ){
opMask = WO_LT|WO_LE;
}else if( /*pProbe->tnum<=0 ||*/ (pSrc->fg.jointype & JT_LEFT)!=0 ){
opMask = WO_EQ|WO_IN|WO_GT|WO_GE|WO_LT|WO_LE;
}else{
opMask = WO_EQ|WO_IN|WO_GT|WO_GE|WO_LT|WO_LE|WO_ISNULL|WO_IS;
}
if( pProbe->bUnordered ) opMask &= ~(WO_GT|WO_GE|WO_LT|WO_LE);
assert( pNew->u.btree.nEq<pProbe->nColumn );
................................................................................
continue; /* ignore IS [NOT] NULL constraints on NOT NULL columns */
}
if( pTerm->prereqRight & pNew->maskSelf ) continue;
/* Do not allow the upper bound of a LIKE optimization range constraint
** to mix with a lower range bound from some other source */
if( pTerm->wtFlags & TERM_LIKEOPT && pTerm->eOperator==WO_LT ) continue;
pNew->wsFlags = saved_wsFlags;
pNew->u.btree.nEq = saved_nEq;
pNew->nLTerm = saved_nLTerm;
if( whereLoopResize(db, pNew, pNew->nLTerm+1) ) break; /* OOM */
pNew->aLTerm[pNew->nLTerm++] = pTerm;
pNew->prereq = (saved_prereq | pTerm->prereqRight) & ~pNew->maskSelf;
|
<
<
>
>
>
>
>
>
>
>
>
>
>
>
|
2197
2198
2199
2200
2201
2202
2203
2204
2205
2206
2207
2208
2209
2210
....
2233
2234
2235
2236
2237
2238
2239
2240
2241
2242
2243
2244
2245
2246
2247
2248
2249
2250
2251
2252
2253
2254
2255
2256
2257
2258
|
pNew = pBuilder->pNew;
if( db->mallocFailed ) return SQLITE_NOMEM_BKPT;
assert( (pNew->wsFlags & WHERE_VIRTUALTABLE)==0 );
assert( (pNew->wsFlags & WHERE_TOP_LIMIT)==0 );
if( pNew->wsFlags & WHERE_BTM_LIMIT ){
opMask = WO_LT|WO_LE;
}else{
opMask = WO_EQ|WO_IN|WO_GT|WO_GE|WO_LT|WO_LE|WO_ISNULL|WO_IS;
}
if( pProbe->bUnordered ) opMask &= ~(WO_GT|WO_GE|WO_LT|WO_LE);
assert( pNew->u.btree.nEq<pProbe->nColumn );
................................................................................
continue; /* ignore IS [NOT] NULL constraints on NOT NULL columns */
}
if( pTerm->prereqRight & pNew->maskSelf ) continue;
/* Do not allow the upper bound of a LIKE optimization range constraint
** to mix with a lower range bound from some other source */
if( pTerm->wtFlags & TERM_LIKEOPT && pTerm->eOperator==WO_LT ) continue;
/* Do not allow IS constraints from the WHERE clause to be used by the
** right table of a LEFT JOIN. Only constraints in the ON clause are
** allowed */
if( (pSrc->fg.jointype & JT_LEFT)!=0
&& !ExprHasProperty(pTerm->pExpr, EP_FromJoin)
&& (eOp & (WO_IS|WO_ISNULL))!=0
){
testcase( eOp & WO_IS );
testcase( eOp & WO_ISNULL );
continue;
}
pNew->wsFlags = saved_wsFlags;
pNew->u.btree.nEq = saved_nEq;
pNew->nLTerm = saved_nLTerm;
if( whereLoopResize(db, pNew, pNew->nLTerm+1) ) break; /* OOM */
pNew->aLTerm[pNew->nLTerm++] = pTerm;
pNew->prereq = (saved_prereq | pTerm->prereqRight) & ~pNew->maskSelf;
|