Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch vtab-experimental Excluding Merge-Ins
This is equivalent to a diff from 24f258c239 to 242507b4ff
2016-06-03
| ||
18:59 | The OR optimization is usable on virtual tables with LIKE, REGEXP and/or GLOB terms in the WHERE clause. (check-in: fa3a89fc0b user: drh tags: trunk) | |
2016-05-30
| ||
08:28 | Fix an FTS5 problem (segfault or incorrect query results) with "... MATCH 'x OR y' ORDER BY rank" queries when either token 'x' or 'y' is completely absent from the dataset. (check-in: 64ca1a835a user: dan tags: trunk) | |
2016-05-28
| ||
18:53 | Experimental change to allow virtual tables to take advantage of LIKE, REGEXP and GLOB terms that are part of OR expressions within WHERE clauses. (Closed-Leaf check-in: 242507b4ff user: dan tags: vtab-experimental) | |
17:45 | Remove an unnecessary malloc from the vfsstat extension. (check-in: 24f258c239 user: drh tags: trunk) | |
15:22 | Enhance "PRAGMA compile_options" so that it shows the version of the compiler used to generate the executable, for common compilers. (check-in: 6a0f200957 user: drh tags: trunk) | |
Changes to src/whereexpr.c.
︙ | ︙ | |||
564 565 566 567 568 569 570 | sqlite3WhereClauseInit(pAndWC, pWC->pWInfo); sqlite3WhereSplit(pAndWC, pOrTerm->pExpr, TK_AND); sqlite3WhereExprAnalyze(pSrc, pAndWC); pAndWC->pOuter = pWC; if( !db->mallocFailed ){ for(j=0, pAndTerm=pAndWC->a; j<pAndWC->nTerm; j++, pAndTerm++){ assert( pAndTerm->pExpr ); | | > > | 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 | sqlite3WhereClauseInit(pAndWC, pWC->pWInfo); sqlite3WhereSplit(pAndWC, pOrTerm->pExpr, TK_AND); sqlite3WhereExprAnalyze(pSrc, pAndWC); pAndWC->pOuter = pWC; if( !db->mallocFailed ){ for(j=0, pAndTerm=pAndWC->a; j<pAndWC->nTerm; j++, pAndTerm++){ assert( pAndTerm->pExpr ); if( allowedOp(pAndTerm->pExpr->op) || pAndTerm->eOperator==WO_MATCH ){ b |= sqlite3WhereGetMask(&pWInfo->sMaskSet, pAndTerm->leftCursor); } } } indexable &= b; } }else if( pOrTerm->wtFlags & TERM_COPIED ){ |
︙ | ︙ | |||
1118 1119 1120 1121 1122 1123 1124 | #ifndef SQLITE_OMIT_VIRTUALTABLE /* Add a WO_MATCH auxiliary term to the constraint set if the ** current expression is of the form: column MATCH expr. ** This information is used by the xBestIndex methods of ** virtual tables. The native query optimizer does not attempt ** to do anything with MATCH functions. */ | | | 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 | #ifndef SQLITE_OMIT_VIRTUALTABLE /* Add a WO_MATCH auxiliary term to the constraint set if the ** current expression is of the form: column MATCH expr. ** This information is used by the xBestIndex methods of ** virtual tables. The native query optimizer does not attempt ** to do anything with MATCH functions. */ if( pWC->op==TK_AND && isMatchOfColumn(pExpr, &eOp2) ){ int idxNew; Expr *pRight, *pLeft; WhereTerm *pNewTerm; Bitmask prereqColumn, prereqExpr; pRight = pExpr->x.pList->a[0].pExpr; pLeft = pExpr->x.pList->a[1].pExpr; |
︙ | ︙ |
Added test/bestindex3.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 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 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 | # 2016 May 29 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix bestindex3 ifcapable !vtab { finish_test return } #------------------------------------------------------------------------- # Virtual table callback for a virtual table named $tbl. # # The table created is: # # "CREATE TABLE t1 (a, b, c)" # # This virtual table supports both LIKE and = operators on all columns. # proc vtab_cmd {bOmit method args} { switch -- $method { xConnect { return "CREATE TABLE t1(a, b, c)" } xBestIndex { foreach {clist orderby mask} $args {} set ret [list] set use use if {$bOmit} {set use omit} for {set i 0} {$i < [llength $clist]} {incr i} { array unset C array set C [lindex $clist $i] if {$C(usable) && ($C(op)=="like" || $C(op)=="eq")} { lappend ret $use $i lappend ret idxstr lappend ret "[lindex {a b c} $C(column)] [string toupper $C(op)] ?" break } } if {$ret==""} { lappend ret cost 1000000 rows 1000000 } else { lappend ret cost 100 rows 10 } return $ret } xFilter { foreach {idxnum idxstr param} $args {} set where "" if {$bOmit && $idxstr != ""} { set where " WHERE [string map [list ? '$param' EQ =] $idxstr]" } return [list sql "SELECT rowid, * FROM ttt$where"] } } return "" } register_tcl_module db do_execsql_test 1.0 { CREATE VIRTUAL TABLE t1 USING tcl("vtab_cmd 0"); } do_eqp_test 1.1 { SELECT * FROM t1 WHERE a LIKE 'abc'; } { 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a LIKE ?} } do_eqp_test 1.2 { SELECT * FROM t1 WHERE a = 'abc'; } { 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a EQ ?} } do_eqp_test 1.3 { SELECT * FROM t1 WHERE a = 'abc' OR b = 'def'; } { 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a EQ ?} 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:b EQ ?} } do_eqp_test 1.4 { SELECT * FROM t1 WHERE a LIKE 'abc%' OR b = 'def'; } { 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a LIKE ?} 0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:b EQ ?} } do_execsql_test 1.5 { CREATE TABLE ttt(a, b, c); INSERT INTO ttt VALUES(1, 'two', 'three'); INSERT INTO ttt VALUES(2, 'one', 'two'); INSERT INTO ttt VALUES(3, 'three', 'one'); INSERT INTO ttt VALUES(4, 'y', 'one'); INSERT INTO ttt VALUES(5, 'x', 'two'); INSERT INTO ttt VALUES(6, 'y', 'three'); } foreach omit {0 1} { do_execsql_test 1.6.$omit.0 " DROP TABLE t1; CREATE VIRTUAL TABLE t1 USING tcl('vtab_cmd $omit'); " do_execsql_test 1.6.$omit.1 { SELECT rowid FROM t1 WHERE c LIKE 'o%' } {3 4} do_execsql_test 1.6.$omit.2 { SELECT rowid FROM t1 WHERE c LIKE 'o%' OR b='y' } {3 4 6} do_execsql_test 1.6.$omit.3 { SELECT rowid FROM t1 WHERE c = 'three' OR c LIKE 'o%' } {1 6 3 4} } #------------------------------------------------------------------------- # Test the same pattern works with ordinary tables. # do_execsql_test 2.1 { CREATE TABLE t2(x TEXT COLLATE nocase, y TEXT); CREATE INDEX t2x ON t2(x COLLATE nocase); CREATE INDEX t2y ON t2(y); } do_eqp_test 2.2 { SELECT * FROM t2 WHERE x LIKE 'abc%' OR y = 'def' } { 0 0 0 {SEARCH TABLE t2 USING INDEX t2x (x>? AND x<?)} 0 0 0 {SEARCH TABLE t2 USING INDEX t2y (y=?)} } #------------------------------------------------------------------------- # Test that any PRIMARY KEY within a sqlite3_decl_vtab() CREATE TABLE # statement is currently ignored. # proc vvv_command {method args} { switch -- $method { xConnect { return "CREATE TABLE t1(a PRIMARY KEY, b, c)" } } } proc yyy_command {method args} { switch -- $method { xConnect { return "CREATE TABLE t1(a, b, c, PRIMARY KEY(a, b))" } } } do_execsql_test 3.1 { CREATE VIRTUAL TABLE t3 USING tcl('vvv_command') } do_execsql_test 3.2 { CREATE VIRTUAL TABLE t4 USING tcl('yyy_command') } finish_test |