Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Do not allow a term in the WHERE clause of the query to qualify a partial index on the right table of a LEFT JOIN. Ticket [7f39060a24b47353] |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
4066a34da7bcdcece6c438c27f3a11bc |
| User & Date: | drh 2019-11-30 19:29:19 |
Context
|
2019-12-03
| ||
| 02:51 | Avoid computing a zero offset of a null pointer, which though this is technically harmless, is upsetting to pedantic run-time checkers. (check-in: 3ce804e9 user: drh tags: trunk) | |
|
2019-11-30
| ||
| 19:29 | Do not allow a term in the WHERE clause of the query to qualify a partial index on the right table of a LEFT JOIN. Ticket [7f39060a24b47353] (check-in: 4066a34d user: drh tags: trunk) | |
|
2019-11-29
| ||
| 13:01 | Add comments in the parse.sql file to help demonstrate how rules are encoded. (check-in: 2c4f7148 user: drh tags: trunk) | |
Changes
Changes to src/where.c.
| ︙ | ︙ | |||
2786 2787 2788 2789 2790 2791 2792 | } return 0; } /* 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. */ | | > > > > > | > | 2786 2787 2788 2789 2790 2791 2792 2793 2794 2795 2796 2797 2798 2799 2800 2801 2802 2803 2804 2805 2806 2807 2808 2809 2810 2811 2812 2813 2814 2815 2816 2817 2818 |
}
return 0;
}
/* 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, /* The table for which we want an index */
int isLeft, /* True if iTab is the right table of a LEFT JOIN */
WhereClause *pWC, /* The WHERE clause of the query */
Expr *pWhere /* The WHERE clause from the partial index */
){
int i;
WhereTerm *pTerm;
Parse *pParse = pWC->pWInfo->pParse;
while( pWhere->op==TK_AND ){
if( !whereUsablePartialIndex(iTab,isLeft,pWC,pWhere->pLeft) ) return 0;
pWhere = pWhere->pRight;
}
if( pParse->db->flags & SQLITE_EnableQPSG ) pParse = 0;
for(i=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
Expr *pExpr;
pExpr = pTerm->pExpr;
if( (!ExprHasProperty(pExpr, EP_FromJoin) || pExpr->iRightJoinTable==iTab)
&& (isLeft==0 || ExprHasProperty(pExpr, EP_FromJoin))
&& sqlite3ExprImpliesExpr(pParse, pExpr, pWhere, iTab)
){
return 1;
}
}
return 0;
}
|
| ︙ | ︙ | |||
2961 2962 2963 2964 2965 2966 2967 2968 |
#endif /* SQLITE_OMIT_AUTOMATIC_INDEX */
/* Loop over all indices. If there was an INDEXED BY clause, then only
** consider index pProbe. */
for(; rc==SQLITE_OK && pProbe;
pProbe=(pSrc->pIBIndex ? 0 : pProbe->pNext), iSortIdx++
){
if( pProbe->pPartIdxWhere!=0
| > | > > | 2967 2968 2969 2970 2971 2972 2973 2974 2975 2976 2977 2978 2979 2980 2981 2982 2983 2984 2985 |
#endif /* SQLITE_OMIT_AUTOMATIC_INDEX */
/* Loop over all indices. If there was an INDEXED BY clause, then only
** consider index pProbe. */
for(; rc==SQLITE_OK && pProbe;
pProbe=(pSrc->pIBIndex ? 0 : pProbe->pNext), iSortIdx++
){
int isLeft = (pSrc->fg.jointype & JT_OUTER)!=0;
if( pProbe->pPartIdxWhere!=0
&& !whereUsablePartialIndex(pSrc->iCursor, isLeft, pWC,
pProbe->pPartIdxWhere)
){
testcase( pNew->iTab!=pSrc->iCursor ); /* See ticket [98d973b8f5] */
continue; /* Partial index inappropriate for this query */
}
if( pProbe->bNoQuery ) continue;
rSize = pProbe->aiRowLogEst[0];
pNew->u.btree.nEq = 0;
pNew->u.btree.nBtm = 0;
|
| ︙ | ︙ |
Changes to test/join.test.
| ︙ | ︙ | |||
948 949 950 951 952 953 954 955 956 |
INSERT INTO t0(c0) VALUES (0);
SELECT * FROM t0 LEFT JOIN t1 WHERE NULL IN (c1);
} {}
do_execsql_test join-20.2 {
CREATE INDEX t1x ON t1(0) WHERE NULL IN (c1);
SELECT * FROM t0 LEFT JOIN t1 WHERE NULL IN (c1);
} {}
finish_test
| > > > > > > > > > > > > > > > > > > > > > > | 948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 974 975 976 977 978 |
INSERT INTO t0(c0) VALUES (0);
SELECT * FROM t0 LEFT JOIN t1 WHERE NULL IN (c1);
} {}
do_execsql_test join-20.2 {
CREATE INDEX t1x ON t1(0) WHERE NULL IN (c1);
SELECT * FROM t0 LEFT JOIN t1 WHERE NULL IN (c1);
} {}
# 2019-11-30 ticket 7f39060a24b47353
# Do not allow a WHERE clause term to qualify a partial index on the
# right table of a LEFT JOIN.
#
do_execsql_test join-21.10 {
DROP TABLE t0;
DROP TABLE t1;
CREATE TABLE t0(aa);
CREATE TABLE t1(bb);
INSERT INTO t0(aa) VALUES (1);
INSERT INTO t1(bb) VALUES (1);
SELECT 11, * FROM t1 LEFT JOIN t0 WHERE aa ISNULL;
SELECT 12, * FROM t1 LEFT JOIN t0 WHERE +aa ISNULL;
SELECT 13, * FROM t1 LEFT JOIN t0 ON aa ISNULL;
SELECT 14, * FROM t1 LEFT JOIN t0 ON +aa ISNULL;
CREATE INDEX i0 ON t0(aa) WHERE aa ISNULL;
SELECT 21, * FROM t1 LEFT JOIN t0 WHERE aa ISNULL;
SELECT 22, * FROM t1 LEFT JOIN t0 WHERE +aa ISNULL;
SELECT 23, * FROM t1 LEFT JOIN t0 ON aa ISNULL;
SELECT 24, * FROM t1 LEFT JOIN t0 ON +aa ISNULL;
} {13 1 {} 14 1 {} 23 1 {} 24 1 {}}
finish_test
|