SQLite

Check-in [a2cf496896]
Login

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: a2cf49689642c2157288829e96c21c0de83e3a1b
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
Unified Diff Ignore Whitespace Patch
Changes to src/wherecode.c.
937
938
939
940
941
942
943





944

945
946
947
948
949
950
951
            sqlite3ExprIfFalse(pParse, pCompare, pLevel->addrCont, 0);
          }
          pCompare->pLeft = 0;
          sqlite3ExprDelete(db, pCompare);
        }
      }
    }





    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
  ){







>
>
>
>
>
|
>







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