SQLite

Check-in [a934dd14ac]
Login

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
Timelines: family | ancestors | descendants | both | partial-index-variables
Files: files | file ages | folders
SHA3-256: a934dd14ac55177ac541423f4a077484bb3b461b60c9c2e88d067cca922fa2bc
User & Date: drh 2017-06-28 18:25:03.706
Context
2017-06-28
21:47
Alternative implementation of exprCompareVariable(). (check-in: b959c6297c 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: a934dd14ac user: drh tags: partial-index-variables)
18:07
Merge the in the latest enhancements from trunk. (check-in: 8f63c58632 user: drh tags: partial-index-variables)
Changes
Unified Diff Ignore Whitespace Patch
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