Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Make the query planners use of partial indexes based on bound variables responsive to the SQLITE_DBCONFIG_ENABLE_QPSG setting. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | partial-index-variables |
| Files: | files | file ages | folders |
| SHA3-256: | a934dd14ac55177ac541423f4a077484 |
| User & Date: | drh 2017-06-28 18:25:03 |
Context
|
2017-06-28
| ||
| 21:47 | Alternative implementation of exprCompareVariable(). check-in: b959c629 user: drh tags: partial-index-variables | |
| 18:25 | Make the query planners use of partial indexes based on bound variables responsive to the SQLITE_DBCONFIG_ENABLE_QPSG setting. check-in: a934dd14 user: drh tags: partial-index-variables | |
| 18:07 | Merge the in the latest enhancements from trunk. check-in: 8f63c586 user: drh tags: partial-index-variables | |
Changes
Changes to src/where.c.
2683 2684 2685 2686 2687 2688 2689 2690 2691 2692 2693 2694 2695 2696 2697 2698 2699 2700 2701 2702 2703 2704 |
/* 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;
while( pWhere->op==TK_AND ){
if( !whereUsablePartialIndex(iTab,pWC,pWhere->pLeft) ) return 0;
pWhere = pWhere->pRight;
}
for(i=0, pTerm=pWC->a; i<pWC->nTerm; i++, pTerm++){
Expr *pExpr = pTerm->pExpr;
if( sqlite3ExprImpliesExpr(pWC->pWInfo->pParse, pExpr, pWhere, iTab)
&& (!ExprHasProperty(pExpr, EP_FromJoin) || pExpr->iRightJoinTable==iTab)
){
return 1;
}
}
return 0;
}
|
> > < | > |
2683 2684 2685 2686 2687 2688 2689 2690 2691 2692 2693 2694 2695 2696 2697 2698 2699 2700 2701 2702 2703 2704 2705 2706 |
/* 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 = pTerm->pExpr;
if( (!ExprHasProperty(pExpr, EP_FromJoin) || pExpr->iRightJoinTable==iTab)
&& sqlite3ExprImpliesExpr(pParse, pExpr, pWhere, iTab)
){
return 1;
}
}
return 0;
}
|
Changes to test/index9.test.
61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 |
}
set y [expr 9223372036854775807]
do_sqluses_test 3.1 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1 t1x3}
set y [expr 9223372036854775808]
do_sqluses_test 3.2 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1}
set y [expr 9223372036854775806]
do_sqluses_test 3.3 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1}
do_execsql_test 3.0 {
CREATE INDEX t1x4 ON t1(x) WHERE y=-9223372036854775808
}
set y [expr -9223372036854775808]
do_sqluses_test 3.1 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1 t1x4}
set y [expr -9223372036854775807]
do_sqluses_test 3.2 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1}
set y [expr -9223372036854775809]
do_sqluses_test 3.3 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1}
set y [expr -9223372036854775808]
do_sqluses_test 3.4 { SELECT * FROM t1 WHERE $y=y ORDER BY x } {t1 t1x4}
finish_test
|
> > > > > > > > > | | | | | > > > > > < |
61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 |
}
set y [expr 9223372036854775807]
do_sqluses_test 3.1 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1 t1x3}
set y [expr 9223372036854775808]
do_sqluses_test 3.2 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1}
set y [expr 9223372036854775806]
do_sqluses_test 3.3 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1}
db cache flush
sqlite3_db_config db QPSG 1
set y [expr 9223372036854775807]
do_sqluses_test 3.4 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1}
set y [expr 9223372036854775808]
do_sqluses_test 3.5 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1}
sqlite3_db_config db QPSG 0
db cache flush
do_execsql_test 4.0 {
CREATE INDEX t1x4 ON t1(x) WHERE y=-9223372036854775808
}
set y [expr -9223372036854775808]
do_sqluses_test 4.1 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1 t1x4}
set y [expr -9223372036854775807]
do_sqluses_test 4.2 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1}
set y [expr -9223372036854775809]
do_sqluses_test 4.3 { SELECT * FROM t1 WHERE y=$y ORDER BY x } {t1}
set y [expr -9223372036854775808]
do_sqluses_test 4.4 { SELECT * FROM t1 WHERE $y=y ORDER BY x } {t1 t1x4}
db cache flush
sqlite3_db_config db QPSG 1
do_sqluses_test 4.5 { SELECT * FROM t1 WHERE $y=y ORDER BY x } {t1}
sqlite3_db_config db QPSG 0
db cache flush
finish_test
|