Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fix a problem with processing a "vtab.col IS NULL" expression within the WHERE clause of a query when "vtab" is a virtual table on the rhs of a LEFT JOIN. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
83da4d4104ee1870a2a95bb5fa15ee65 |
User & Date: | dan 2018-09-10 12:17:16.759 |
References
2019-12-11
| ||
16:22 | Strengthen check-in [83da4d4104ee1870] by ignoring *all* WHERE-clause constraints for a virtual table that is the right table of a LEFT JOIN, as such constraints are never useful. This fixes an issue discovered by Manuel Rigger. (check-in: 840de36df1 user: drh tags: trunk) | |
Context
2018-09-10
| ||
12:40 | Fix the LIKE optimization so that it is disabled when the LHS of the expression does not have TEXT affinity and the RHS is the pattern '/%' or the RHS begins with the escape character. Fix for ticket [c94369cae9b561b1f996d0054b]. (check-in: f191431d63 user: drh tags: trunk) | |
12:17 | Fix a problem with processing a "vtab.col IS NULL" expression within the WHERE clause of a query when "vtab" is a virtual table on the rhs of a LEFT JOIN. (check-in: 83da4d4104 user: dan tags: trunk) | |
2018-09-08
| ||
20:29 | Fix an unreachable branch in the new sqlite3WhereOrderByLimitOptLabel() function of the query planner. (check-in: 5a954533ed user: drh tags: trunk) | |
Changes
Changes to src/where.c.
︙ | ︙ | |||
962 963 964 965 966 967 968 969 970 971 972 973 974 975 | assert( IsPowerOfTwo(pTerm->eOperator & ~WO_EQUIV) ); testcase( pTerm->eOperator & WO_IN ); testcase( pTerm->eOperator & WO_IS ); testcase( pTerm->eOperator & WO_ISNULL ); testcase( pTerm->eOperator & WO_ALL ); if( (pTerm->eOperator & ~(WO_EQUIV))==0 ) continue; if( pTerm->wtFlags & TERM_VNULL ) continue; assert( pTerm->u.leftColumn>=(-1) ); pIdxCons[j].iColumn = pTerm->u.leftColumn; pIdxCons[j].iTermOffset = i; op = pTerm->eOperator & WO_ALL; if( op==WO_IN ) op = WO_EQ; if( op==WO_AUX ){ pIdxCons[j].op = pTerm->eMatchOp; | > > > > > > > > > > > > | 962 963 964 965 966 967 968 969 970 971 972 973 974 975 976 977 978 979 980 981 982 983 984 985 986 987 | assert( IsPowerOfTwo(pTerm->eOperator & ~WO_EQUIV) ); testcase( pTerm->eOperator & WO_IN ); testcase( pTerm->eOperator & WO_IS ); testcase( pTerm->eOperator & WO_ISNULL ); testcase( pTerm->eOperator & WO_ALL ); if( (pTerm->eOperator & ~(WO_EQUIV))==0 ) continue; if( pTerm->wtFlags & TERM_VNULL ) continue; if( (pSrc->fg.jointype & JT_LEFT)!=0 && !ExprHasProperty(pTerm->pExpr, EP_FromJoin) && (pTerm->eOperator & (WO_IS|WO_ISNULL)) ){ /* An "IS" term in the WHERE clause where the virtual table is the rhs ** of a LEFT JOIN. Do not pass this term to the virtual table ** implementation, as this can lead to incorrect results from SQL such ** as: ** ** "LEFT JOIN vtab WHERE vtab.col IS NULL" */ continue; } assert( pTerm->u.leftColumn>=(-1) ); pIdxCons[j].iColumn = pTerm->u.leftColumn; pIdxCons[j].iTermOffset = i; op = pTerm->eOperator & WO_ALL; if( op==WO_IN ) op = WO_EQ; if( op==WO_AUX ){ pIdxCons[j].op = pTerm->eMatchOp; |
︙ | ︙ |
Added test/bestindex6.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 | # 2018-09-09 # # 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 bestindex6 ifcapable !vtab { finish_test return } register_tcl_module db proc vtab_command {src method args} { switch -- $method { xConnect { return [db one {SELECT sql FROM sqlite_master where name = $src}] } xBestIndex { set clist [lindex $args 0] set wlist 1 set iCons 0 set ret [list] foreach cons $clist { catch { array unset C } array set C $cons if {$C(usable)} { set col [db one { SELECT name FROM pragma_table_info($src) WHERE cid=$C(column) }] switch $C(op) { isnull { lappend wlist "$col IS NULL" lappend ret omit $iCons } eq { lappend wlist "$col = %$iCons%" lappend ret omit $iCons } } } incr iCons } #puts "xBestIndex: $ret" lappend ret idxStr [join $wlist " AND "] return $ret } xFilter { foreach {idxnum idxstr aa} $args {} set map [list] for {set iCons 0} {$iCons < [llength $aa]} {incr iCons} { lappend map %$iCons% [lindex $aa $iCons] } set ret [list sql \ "SELECT rowid, * FROM $src WHERE [string map $map $idxstr]" ] # puts "xFilter: $ret" return $ret } } return {} } do_execsql_test 1.0 { CREATE TABLE t1(id int, value text); CREATE TABLE t2(ctx int, id int, value text); INSERT INTO t1 VALUES(1,'try'); INSERT INTO t2 VALUES(1,1,'good'); INSERT INTO t2 VALUES(2,2,'evil'); CREATE VIRTUAL TABLE vt1 USING tcl(vtab_command t1); CREATE VIRTUAL TABLE vt2 USING tcl(vtab_command t2); } do_execsql_test 1.1 { select * from t2 left join t1 on t1.id=t2.ctx where t1.value is null; } {2 2 evil {} {}} do_execsql_test 1.2 { select * from vt2 left join vt1 on vt1.id=vt2.ctx where vt1.value is null; } {2 2 evil {} {}} unset -nocomplain xxx do_execsql_test 1.3 { select * from vt2 left join vt1 on vt1.id=vt2.ctx where vt1.value is $xxx; } {2 2 evil {} {}} do_execsql_test 1.4 { select * from t2 left join vt1 on vt1.id=t2.ctx where vt1.value = 3 } {} finish_test |