/ Check-in [6c56b3a0]
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 | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 6c56b3a04778bc62ca50307ad838dd301cd91ac2
User & Date: drh 2016-04-09 18:04:28
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: a2cf4968 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: 6a5cceee 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: 6c56b3a0 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: ca2ef8a8 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/wherecode.c.

   937    937               sqlite3ExprIfFalse(pParse, pCompare, pLevel->addrCont, 0);
   938    938             }
   939    939             pCompare->pLeft = 0;
   940    940             sqlite3ExprDelete(db, pCompare);
   941    941           }
   942    942         }
   943    943       }
   944         -    sqlite3ReleaseTempRange(pParse, iReg, nConstraint+2);
          944  +    /* These registers need to be preserved in case there is an IN operator
          945  +    ** loop.  So we could deallocate the registers here (and potentially
          946  +    ** reuse them later) if (pLoop->wsFlags & WHERE_IN_ABLE)==0.  But it seems
          947  +    ** simpler and safer to simply not reuse the registers.
          948  +    **
          949  +    **    sqlite3ReleaseTempRange(pParse, iReg, nConstraint+2);
          950  +    */
   945    951       sqlite3ExprCachePop(pParse);
   946    952     }else
   947    953   #endif /* SQLITE_OMIT_VIRTUALTABLE */
   948    954   
   949    955     if( (pLoop->wsFlags & WHERE_IPK)!=0
   950    956      && (pLoop->wsFlags & (WHERE_COLUMN_IN|WHERE_COLUMN_EQ))!=0
   951    957     ){

Changes to test/bestindex1.test.

   160    160       0 0 0 {USE TEMP B-TREE FOR ORDER BY}
   161    161     }
   162    162   
   163    163     do_eqp_test 2.2.$mode.6 { 
   164    164       SELECT rowid FROM t1 WHERE a IN ('one', 'four') ORDER BY +rowid
   165    165     } $plan($mode)
   166    166   }
          167  +
          168  +# 2016-04-09.
          169  +# Demonstrate a register overwrite problem when using two virtual
          170  +# tables where the outer loop uses the IN operator.
          171  +#
          172  +set G(collist) [list PrimaryKey flagA columnA]
          173  +set G(cols) [join $G(collist) ,]
          174  +set G(nulls) "NULL"
          175  +
          176  +proc vtab_command {method args} {
          177  +  global G
          178  +
          179  +  switch -- $method {
          180  +    xConnect {
          181  +      return "CREATE TABLE t1($G(cols))"
          182  +    }
          183  +
          184  +    xBestIndex {
          185  +      set clist [lindex $args 0]
          186  +      #puts $clist
          187  +      set W [list]
          188  +      set U [list]
          189  +
          190  +      set i 0
          191  +      for {set idx 0} {$idx < [llength $clist]} {incr idx} {
          192  +        array set c [lindex $clist $idx]
          193  +        if {$c(op)=="eq" && $c(usable)} {
          194  +          lappend W "[lindex $G(collist) $c(column)] = %$i%"
          195  +          lappend U use $idx
          196  +          incr i
          197  +        }
          198  +      }
          199  +
          200  +      if {$W==""} {
          201  +        set sql "SELECT rowid, * FROM t1"
          202  +      } else {
          203  +        set sql "SELECT rowid, * FROM t1 WHERE [join $W { AND }]"
          204  +      }
          205  +
          206  +      return [concat [list idxstr $sql] $U]
          207  +    }
          208  +
          209  +    xFilter {
          210  +      foreach {idxnum idxstr vals} $args {}
          211  +
          212  +      set map [list]
          213  +      for {set i 0} {$i < [llength $vals]} {incr i} {
          214  +        lappend map "%$i%" 
          215  +        set v [lindex $vals $i]
          216  +        if {[string is integer $v]} { 
          217  +          lappend map $v 
          218  +        } else {
          219  +          lappend map "'$v'"
          220  +        }
          221  +      }
          222  +      set sql [string map $map $idxstr]
          223  +
          224  +      #puts "SQL: $sql"
          225  +      return [list sql $sql]
          226  +    }
          227  +  }
          228  +
          229  +  return {}
          230  +}
          231  +
          232  +db close
          233  +forcedelete test.db
          234  +sqlite3 db test.db
          235  +register_tcl_module db
          236  +
          237  +do_execsql_test 3.1 "
          238  +  CREATE TABLE t1($G(cols));
          239  +  INSERT INTO t1 VALUES(1, 0, 'ValueA');
          240  +  INSERT INTO t1 VALUES(2, 0, 'ValueA');
          241  +  INSERT INTO t1 VALUES(3, 0, 'ValueB');
          242  +  INSERT INTO t1 VALUES(4, 0, 'ValueB');
          243  +"
          244  +
          245  +do_execsql_test 3.2 {
          246  +  CREATE VIRTUAL TABLE VirtualTableA USING tcl(vtab_command);
          247  +  CREATE VIRTUAL TABLE VirtualTableB USING tcl(vtab_command);
          248  +}
          249  +
          250  +do_execsql_test 3.3 { SELECT primarykey FROM VirtualTableA } {1 2 3 4}
          251  +
          252  +do_execsql_test 3.4 {
          253  +  SELECT * FROM 
          254  +  VirtualTableA a CROSS JOIN VirtualTableB b ON b.PrimaryKey=a.PrimaryKey
          255  +  WHERE a.ColumnA IN ('ValueA', 'ValueB') AND a.FlagA=0
          256  +} {
          257  +  1 0 ValueA 1 0 ValueA
          258  +  2 0 ValueA 2 0 ValueA
          259  +  3 0 ValueB 3 0 ValueB
          260  +  4 0 ValueB 4 0 ValueB
          261  +}
          262  +
          263  +do_execsql_test 3.5 {
          264  +  SELECT * FROM 
          265  +  VirtualTableA a CROSS JOIN VirtualTableB b ON b.PrimaryKey=a.PrimaryKey
          266  +  WHERE a.FlagA=0 AND a.ColumnA IN ('ValueA', 'ValueB') 
          267  +} {
          268  +  1 0 ValueA 1 0 ValueA
          269  +  2 0 ValueA 2 0 ValueA
          270  +  3 0 ValueB 3 0 ValueB
          271  +  4 0 ValueB 4 0 ValueB
          272  +}
          273  +
   167    274   
   168    275   finish_test