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: 3ce804e99b 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: 4066a34da7 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: 2c4f714892 user: drh tags: trunk | |
Changes
Changes to src/where.c.
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
....
2961
2962
2963
2964
2965
2966
2967
2968
2969
2970
2971
2972
2973
2974
2975
2976
|
} 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, WhereClause *pWC, Expr *pWhere){ int i; WhereTerm *pTerm; Parse *pParse = pWC->pWInfo->pParse; while( pWhere->op==TK_AND ){ if( !whereUsablePartialIndex(iTab,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) && sqlite3ExprImpliesExpr(pParse, pExpr, pWhere, iTab) ){ return 1; } } return 0; } ................................................................................ #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 && !whereUsablePartialIndex(pSrc->iCursor, 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; |
|
>
>
>
>
>
|
>
>
|
>
>
|
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
....
2967
2968
2969
2970
2971
2972
2973
2974
2975
2976
2977
2978
2979
2980
2981
2982
2983
2984
2985
|
} 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; } ................................................................................ #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 |