test/bestindex1.test

   1 # 2016-03-01
   2 #
   3 # The author disclaims copyright to this source code.  In place of
   4 # a legal notice, here is a blessing:
   5 #
   6 #    May you do good and not evil.
   7 #    May you find forgiveness for yourself and forgive others.
   8 #    May you share freely, never taking more than you give.
   9 #
  10 #***********************************************************************
  11 #
  12 #
  13
  14 set testdir [file dirname $argv0]
  15 source $testdir/tester.tcl
  16 set testprefix bestindex1
  17
  18 ifcapable !vtab {
  19   finish_test
  20   return
  21 }
  22
  23 register_tcl_module db
  24
  25 proc vtab_command {method args} {
  26   switch -- $method {
  27     xConnect {
  28       return "CREATE TABLE t1(a, b, c)"
  29     }
  30
  31     xBestIndex {
  32       set clist [lindex $args 0]
  33       if {[llength $clist]!=1} { error "unexpected constraint list" }
  34       catch { array unset C }
  35       array set C [lindex $clist 0]
  36       if {$C(usable)} {
  37         return "omit 0 cost 0 rows 1 idxnum 555 idxstr eq!"
  38       } else {
  39         return "cost 1000000 rows 0 idxnum 0 idxstr scan..."
  40       }
  41     }
  42
  43   }
  44
  45   return {}
  46 }
  47
  48 do_execsql_test 1.0 {
  49   CREATE VIRTUAL TABLE x1 USING tcl(vtab_command);
  50 } {}
  51
  52 do_eqp_test 1.1 {
  53   SELECT * FROM x1 WHERE a = 'abc'
  54 } {
  55   0 0 0 {SCAN TABLE x1 VIRTUAL TABLE INDEX 555:eq!}
  56 }
  57
  58 do_eqp_test 1.2 {
  59   SELECT * FROM x1 WHERE a IN ('abc', 'def');
  60 } {
  61   0 0 0 {SCAN TABLE x1 VIRTUAL TABLE INDEX 555:eq!}
  62   0 0 0 {EXECUTE LIST SUBQUERY 1}
  63 }
  64
  65 #-------------------------------------------------------------------------
  66 #
  67 reset_db
  68 register_tcl_module db
  69
  70 # Parameter $mode may be one of:
  71 #
  72 #   "omit" - Implement filtering. Set the omit flag.
  73 #   "use"  - Implement filtering. Use the constraint, but do not set omit.
  74 #   "use2" - Do not implement filtering. Use the constraint anyway.
  75 #
  76 #
  77 proc t1_vtab {mode method args} {
  78   switch -- $method {
  79     xConnect {
  80       return "CREATE TABLE t1(a, b)"
  81     }
  82
  83     xBestIndex {
  84       set SQL_FILTER {SELECT * FROM t1x WHERE a='%1%'}
  85       set SQL_SCAN   {SELECT * FROM t1x}
  86
  87       set clist [lindex $args 0]
  88       set idx 0
  89       for {set idx 0} {$idx < [llength $clist]} {incr idx} {
  90         array unset C
  91         array set C [lindex $clist $idx]
  92         if {$C(column)==0 && $C(op)=="eq" && $C(usable)} {
  93           switch -- $mode {
  94             "omit" {
  95               return [list omit $idx rows 10 cost 10 idxstr $SQL_FILTER]
  96             }
  97             "use" {
  98               return [list use $idx rows 10 cost 10 idxstr $SQL_FILTER]
  99             }
 100             "use2" {
 101               return [list use $idx rows 10 cost 10 idxstr $SQL_SCAN]
 102             }
 103             default {
 104               error "Bad mode - $mode"
 105             }
 106           }
 107         }
 108       }
 109
 110       return [list idxstr {SELECT * FROM t1x}]
 111     }
 112
 113     xFilter {
 114       set map [list %1% [lindex $args 2 0]]
 115       set sql [string map $map [lindex $args 1]]
 116       return [list sql $sql]
 117     }
 118   }
 119
 120   return {}
 121 }
 122
 123 do_execsql_test 2.1 {
 124   CREATE TABLE t1x(i INTEGER PRIMARY KEY, a, b);
 125   INSERT INTO t1x VALUES(1, 'one', 1);
 126   INSERT INTO t1x VALUES(2, 'two', 2);
 127   INSERT INTO t1x VALUES(3, 'three', 3);
 128   INSERT INTO t1x VALUES(4, 'four', 4);
 129 }
 130
 131 foreach {tn mode} {
 132   1 use 2 omit 3 use2
 133 } {
 134   do_execsql_test 2.2.$mode.1 "
 135     DROP TABLE IF EXISTS t1;
 136     CREATE VIRTUAL TABLE t1 USING tcl(t1_vtab $mode);
 137   "
 138
 139   do_execsql_test 2.2.$mode.2 {SELECT * FROM t1} {one 1 two 2 three 3 four 4}
 140   do_execsql_test 2.2.$mode.3 {SELECT rowid FROM t1} {1 2 3 4}
 141   do_execsql_test 2.2.$mode.4 {SELECT rowid FROM t1 WHERE a='two'} {2}
 142
 143   do_execsql_test 2.2.$mode.5 {
 144     SELECT rowid FROM t1 WHERE a IN ('one', 'four') ORDER BY +rowid
 145   } {1 4}
 146
 147   set plan(use) {
 148     0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x WHERE a='%1%'}
 149     0 0 0 {EXECUTE LIST SUBQUERY 1}
 150     0 0 0 {USE TEMP B-TREE FOR ORDER BY}
 151   }
 152   set plan(omit) {
 153     0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x WHERE a='%1%'}
 154     0 0 0 {EXECUTE LIST SUBQUERY 1}
 155     0 0 0 {USE TEMP B-TREE FOR ORDER BY}
 156   }
 157   set plan(use2) {
 158     0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x}
 159     0 0 0 {EXECUTE LIST SUBQUERY 1}
 160     0 0 0 {USE TEMP B-TREE FOR ORDER BY}
 161   }
 162
 163   do_eqp_test 2.2.$mode.6 {
 164     SELECT rowid FROM t1 WHERE a IN ('one', 'four') ORDER BY +rowid
 165   } $plan($mode)
 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
 274
 275 finish_test