SQLite

Check-in [6c56b3a047]
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 | trunk
Files: files | file ages | folders
SHA1: 6c56b3a04778bc62ca50307ad838dd301cd91ac2
User & Date: drh 2016-04-09 18:04:28.474
Context
2016-04-18
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)
2016-04-11
01:06
Factor out the common operation of setting the Expr.x.pSelect field of an Expr object into a subroutine. (check-in: 6a5cceee48 user: drh tags: trunk)
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)
17:04
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: ca2ef8a86c 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