Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Prevent partial indexes on the table on the left hand side of a LEFT JOIN from being incorrectly qualified by a constraint in the ON clause of the join. This relaxes the rule introduced by the previous commit (as the partial indexes on the table on the rhs of the LEFT JOIN may now be qualified by terms within the ON clause). |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
1d6fb43a576d335d2717e94d28385178 |
| User & Date: | dan 2015-02-24 20:10:49 |
Context
|
2015-02-25
| ||
| 00:24 | Remove an always-false conditional from constructAutomaticIndex(). Put an assert() in its place to prove that the conditional is always false. check-in: 3af300bf user: drh tags: trunk | |
|
2015-02-24
| ||
| 20:10 | Prevent partial indexes on the table on the left hand side of a LEFT JOIN from being incorrectly qualified by a constraint in the ON clause of the join. This relaxes the rule introduced by the previous commit (as the partial indexes on the table on the rhs of the LEFT JOIN may now be qualified by terms within the ON clause). check-in: 1d6fb43a user: dan tags: trunk | |
| 20:04 | Make sure partial indexes are not qualified incorrectly by a constraint that is inside the ON clause of a LEFT JOIN. Fix for ticket [2326c258d02ead33]. check-in: 491cfe9b user: drh tags: trunk | |
Changes
Changes to src/where.c.
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
....
4691
4692
4693
4694
4695
4696
4697
4698
4699
4700
4701
4702
4703
4704
4705
4706
|
pTable = pSrc->pTab;
pWCEnd = &pWC->a[pWC->nTerm];
pLoop = pLevel->pWLoop;
idxCols = 0;
for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
if( pLoop->prereq==0
&& (pTerm->wtFlags & TERM_VIRTUAL)==0
&& !ExprHasProperty(pTerm->pExpr, EP_FromJoin)
&& sqlite3ExprIsTableConstant(pTerm->pExpr, pSrc->iCursor) ){
pPartial = sqlite3ExprAnd(pParse->db, pPartial,
sqlite3ExprDup(pParse->db, pTerm->pExpr, 0));
}
if( termCanDriveIndex(pTerm, pSrc, notReady) ){
int iCol = pTerm->u.leftColumn;
Bitmask cMask = iCol>=BMS ? MASKBIT(BMS-1) : MASKBIT(iCol);
................................................................................
/* Check to see if a partial index with pPartIndexWhere can be used
** in the current query. Return true if it can be and false if not.
*/
static int whereUsablePartialIndex(int iTab, WhereClause *pWC, Expr *pWhere){
int i;
WhereTerm *pTerm;
for(i=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
if( sqlite3ExprImpliesExpr(pTerm->pExpr, pWhere, iTab)
&& !ExprHasProperty(pTerm->pExpr, EP_FromJoin)
){
return 1;
}
}
return 0;
}
|
|
>
|
|
>
|
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
....
4692
4693
4694
4695
4696
4697
4698
4699
4700
4701
4702
4703
4704
4705
4706
4707
4708
|
pTable = pSrc->pTab;
pWCEnd = &pWC->a[pWC->nTerm];
pLoop = pLevel->pWLoop;
idxCols = 0;
for(pTerm=pWC->a; pTerm<pWCEnd; pTerm++){
if( pLoop->prereq==0
&& (pTerm->wtFlags & TERM_VIRTUAL)==0
&& (!ExprHasProperty(pTerm->pExpr, EP_FromJoin)
|| pTerm->pExpr->iRightJoinTable==pSrc->iCursor)
&& sqlite3ExprIsTableConstant(pTerm->pExpr, pSrc->iCursor) ){
pPartial = sqlite3ExprAnd(pParse->db, pPartial,
sqlite3ExprDup(pParse->db, pTerm->pExpr, 0));
}
if( termCanDriveIndex(pTerm, pSrc, notReady) ){
int iCol = pTerm->u.leftColumn;
Bitmask cMask = iCol>=BMS ? MASKBIT(BMS-1) : MASKBIT(iCol);
................................................................................
/* Check to see if a partial index with pPartIndexWhere can be used
** in the current query. Return true if it can be and false if not.
*/
static int whereUsablePartialIndex(int iTab, WhereClause *pWC, Expr *pWhere){
int i;
WhereTerm *pTerm;
for(i=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
Expr *pExpr = pTerm->pExpr;
if( sqlite3ExprImpliesExpr(pExpr, pWhere, iTab)
&& (!ExprHasProperty(pExpr, EP_FromJoin) || pExpr->iRightJoinTable==iTab)
){
return 1;
}
}
return 0;
}
|
Changes to test/index6.test.
292 293 294 295 296 297 298 299 |
SELECT * FROM t7a JOIN t7b ON (x=99) ORDER BY x;
} {99 2}
do_execsql_test index6-7.4 {
EXPLAIN QUERY PLAN
SELECT * FROM t7a JOIN t7b ON (x=99) ORDER BY x;
} {/USING COVERING INDEX t7ax/}
finish_test
|
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 |
SELECT * FROM t7a JOIN t7b ON (x=99) ORDER BY x;
} {99 2}
do_execsql_test index6-7.4 {
EXPLAIN QUERY PLAN
SELECT * FROM t7a JOIN t7b ON (x=99) ORDER BY x;
} {/USING COVERING INDEX t7ax/}
do_execsql_test index6-8.0 {
CREATE TABLE t8a(a,b);
CREATE TABLE t8b(x,y);
CREATE INDEX i8c ON t8b(y) WHERE x = 'value';
INSERT INTO t8a VALUES(1, 'one');
INSERT INTO t8a VALUES(2, 'two');
INSERT INTO t8a VALUES(3, 'three');
INSERT INTO t8b VALUES('value', 1);
INSERT INTO t8b VALUES('dummy', 2);
INSERT INTO t8b VALUES('value', 3);
INSERT INTO t8b VALUES('dummy', 4);
} {}
do_eqp_test index6-8.1 {
SELECT * FROM t8a LEFT JOIN t8b ON (x = 'value' AND y = a)
} {
0 0 0 {SCAN TABLE t8a}
0 1 1 {SEARCH TABLE t8b USING INDEX i8c (y=?)}
}
do_execsql_test index6-8.2 {
SELECT * FROM t8a LEFT JOIN t8b ON (x = 'value' AND y = a)
} {
1 one value 1
2 two {} {}
3 three value 3
}
finish_test
|