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 |
Timelines: | family | ancestors | descendants | both | tkt-2326c258 |
Files: | files | file ages | folders |
SHA1: |
1a1516e4da26dcee35e6fbb6604ce252 |
User & Date: | drh 2015-02-24 16:48:59.496 |
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: c6399958a1 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: 1a1516e4da 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: c0f4e308a5 user: drh tags: tkt-2326c258) | |
Changes
Changes to src/where.c.
︙ | ︙ | |||
4691 4692 4693 4694 4695 4696 4697 | /* 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++){ | | > > > > | 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 | 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} | > > > > > > > > > > | 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 | 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} |
︙ | ︙ |