Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Fix a problem in the code generator for joins on virtual tables where the outer loop of the join uses the IN operator. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | branch-3.12.0 |
Files: | files | file ages | folders |
SHA1: |
a2cf49689642c2157288829e96c21c0d |
User & Date: | drh 2016-04-18 16:12:04.978 |
Context
2016-04-18
| ||
16:18 | Correctly interpret negative "PRAGMA cache_size" values when determining the cache-size used for sorting large amounts of data (i.e. the functionality in vdbesort.c). (check-in: 39dd67afa5 user: drh tags: branch-3.12.0) | |
16:12 | Fix a problem in the code generator for joins on virtual tables where the outer loop of the join uses the IN operator. (check-in: a2cf496896 user: drh tags: branch-3.12.0) | |
16:06 | Fix the sqlite3BtreeDelete() routine so that it preserves the correct key even when the row being deleted is not on a leaf page. Fix for ticket [a306e56ff68b8fa56] (check-in: 368e86c760 user: drh tags: branch-3.12.0) | |
2016-04-09
| ||
18:04 | Fix a problem in the code generator for joins on virtual tables where the outer loop of the join uses the IN operator. (check-in: 6c56b3a047 user: drh tags: trunk) | |
Changes
Changes to src/wherecode.c.
︙ | ︙ | |||
937 938 939 940 941 942 943 | sqlite3ExprIfFalse(pParse, pCompare, pLevel->addrCont, 0); } pCompare->pLeft = 0; sqlite3ExprDelete(db, pCompare); } } } | > > > > > | > | 937 938 939 940 941 942 943 944 945 946 947 948 949 950 951 952 953 954 955 956 957 | sqlite3ExprIfFalse(pParse, pCompare, pLevel->addrCont, 0); } pCompare->pLeft = 0; sqlite3ExprDelete(db, pCompare); } } } /* These registers need to be preserved in case there is an IN operator ** loop. So we could deallocate the registers here (and potentially ** reuse them later) if (pLoop->wsFlags & WHERE_IN_ABLE)==0. But it seems ** simpler and safer to simply not reuse the registers. ** ** sqlite3ReleaseTempRange(pParse, iReg, nConstraint+2); */ sqlite3ExprCachePop(pParse); }else #endif /* SQLITE_OMIT_VIRTUALTABLE */ if( (pLoop->wsFlags & WHERE_IPK)!=0 && (pLoop->wsFlags & (WHERE_COLUMN_IN|WHERE_COLUMN_EQ))!=0 ){ |
︙ | ︙ |
Changes to test/bestindex1.test.
︙ | ︙ | |||
160 161 162 163 164 165 166 167 168 | 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } do_eqp_test 2.2.$mode.6 { SELECT rowid FROM t1 WHERE a IN ('one', 'four') ORDER BY +rowid } $plan($mode) } finish_test | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 | 0 0 0 {USE TEMP B-TREE FOR ORDER BY} } do_eqp_test 2.2.$mode.6 { SELECT rowid FROM t1 WHERE a IN ('one', 'four') ORDER BY +rowid } $plan($mode) } # 2016-04-09. # Demonstrate a register overwrite problem when using two virtual # tables where the outer loop uses the IN operator. # set G(collist) [list PrimaryKey flagA columnA] set G(cols) [join $G(collist) ,] set G(nulls) "NULL" proc vtab_command {method args} { global G switch -- $method { xConnect { return "CREATE TABLE t1($G(cols))" } xBestIndex { set clist [lindex $args 0] #puts $clist set W [list] set U [list] set i 0 for {set idx 0} {$idx < [llength $clist]} {incr idx} { array set c [lindex $clist $idx] if {$c(op)=="eq" && $c(usable)} { lappend W "[lindex $G(collist) $c(column)] = %$i%" lappend U use $idx incr i } } if {$W==""} { set sql "SELECT rowid, * FROM t1" } else { set sql "SELECT rowid, * FROM t1 WHERE [join $W { AND }]" } return [concat [list idxstr $sql] $U] } xFilter { foreach {idxnum idxstr vals} $args {} set map [list] for {set i 0} {$i < [llength $vals]} {incr i} { lappend map "%$i%" set v [lindex $vals $i] if {[string is integer $v]} { lappend map $v } else { lappend map "'$v'" } } set sql [string map $map $idxstr] #puts "SQL: $sql" return [list sql $sql] } } return {} } db close forcedelete test.db sqlite3 db test.db register_tcl_module db do_execsql_test 3.1 " CREATE TABLE t1($G(cols)); INSERT INTO t1 VALUES(1, 0, 'ValueA'); INSERT INTO t1 VALUES(2, 0, 'ValueA'); INSERT INTO t1 VALUES(3, 0, 'ValueB'); INSERT INTO t1 VALUES(4, 0, 'ValueB'); " do_execsql_test 3.2 { CREATE VIRTUAL TABLE VirtualTableA USING tcl(vtab_command); CREATE VIRTUAL TABLE VirtualTableB USING tcl(vtab_command); } do_execsql_test 3.3 { SELECT primarykey FROM VirtualTableA } {1 2 3 4} do_execsql_test 3.4 { SELECT * FROM VirtualTableA a CROSS JOIN VirtualTableB b ON b.PrimaryKey=a.PrimaryKey WHERE a.ColumnA IN ('ValueA', 'ValueB') AND a.FlagA=0 } { 1 0 ValueA 1 0 ValueA 2 0 ValueA 2 0 ValueA 3 0 ValueB 3 0 ValueB 4 0 ValueB 4 0 ValueB } do_execsql_test 3.5 { SELECT * FROM VirtualTableA a CROSS JOIN VirtualTableB b ON b.PrimaryKey=a.PrimaryKey WHERE a.FlagA=0 AND a.ColumnA IN ('ValueA', 'ValueB') } { 1 0 ValueA 1 0 ValueA 2 0 ValueA 2 0 ValueA 3 0 ValueB 3 0 ValueB 4 0 ValueB 4 0 ValueB } finish_test |