Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | This additional fix prevents a partial index from being qualified for use if the constraint that qualifies the partial index is part of the ON clause of a LEFT JOIN. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | tkt-2326c258 |
| Files: | files | file ages | folders |
| SHA1: | 1a1516e4da26dcee35e6fbb6604ce252faf3d116 |
| User & Date: | drh 2015-02-24 16:48:59 |
Context
|
2015-02-24
| ||
| 18:39 | More test cases to help ensure that partial indexes do not get used if their qualifing constraint is inside the ON clause of a LEFT JOIN. Closed-Leaf check-in: c6399958 user: drh tags: tkt-2326c258 | |
| 16:48 | This additional fix prevents a partial index from being qualified for use if the constraint that qualifies the partial index is part of the ON clause of a LEFT JOIN. check-in: 1a1516e4 user: drh tags: tkt-2326c258 | |
| 16:05 | Make sure partial automatic indexes are not based on terms in the ON clause of a LEFT JOIN. Fix for ticket [2326c258d02ead3]. UPDATE: This fix does not work where the partial index is a named index. check-in: c0f4e308 user: drh tags: tkt-2326c258 | |
Changes
Changes to src/where.c.
4691 4692 4693 4694 4695 4696 4697 4698 4699 4700 4701 4702 4703 4704 4705 |
/* 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) ) return 1;
}
return 0;
}
/*
** Add all WhereLoop objects for a single table of the join where the table
** is idenfied by pBuilder->pNew->iTab. That table is guaranteed to be
|
| > > > > |
4691 4692 4693 4694 4695 4696 4697 4698 4699 4700 4701 4702 4703 4704 4705 4706 4707 4708 4709 |
/* 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;
}
/*
** Add all WhereLoop objects for a single table of the join where the table
** is idenfied by pBuilder->pNew->iTab. That table is guaranteed to be
|
Changes to test/autoindex4.test.
57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 |
do_execsql_test autoindex4-3.0 {
CREATE TABLE A(Name text);
CREATE TABLE Items(ItemName text , Name text);
INSERT INTO Items VALUES('Item1','Parent');
INSERT INTO Items VALUES('Item2','Parent');
CREATE TABLE B(Name text);
SELECT Items.ItemName
FROM Items
LEFT JOIN A ON (A.Name = Items.ItemName and Items.ItemName = 'dummy')
LEFT JOIN B ON (B.Name = Items.ItemName)
WHERE Items.Name = 'Parent'
ORDER BY Items.ItemName;
} {Item1 Item2}
finish_test
|
> > > > > > > > > > |
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 |
do_execsql_test autoindex4-3.0 {
CREATE TABLE A(Name text);
CREATE TABLE Items(ItemName text , Name text);
INSERT INTO Items VALUES('Item1','Parent');
INSERT INTO Items VALUES('Item2','Parent');
CREATE TABLE B(Name text);
SELECT Items.ItemName
FROM Items
LEFT JOIN A ON (A.Name = Items.ItemName and Items.ItemName = 'dummy')
LEFT JOIN B ON (B.Name = Items.ItemName)
WHERE Items.Name = 'Parent'
ORDER BY Items.ItemName;
} {Item1 Item2}
do_execsql_test autoindex4-3.1 {
CREATE INDEX Items_x1 ON Items(ItemName,Name) WHERE ItemName = 'dummy';
SELECT Items.ItemName
FROM Items
LEFT JOIN A ON (A.Name = Items.ItemName and Items.ItemName = 'dummy')
LEFT JOIN B ON (B.Name = Items.ItemName)
WHERE Items.Name = 'Parent'
ORDER BY Items.ItemName;
} {Item1 Item2}
finish_test
|