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 hdl [lindex $args 0]
  33       set clist [$hdl constraints]
  34       set orderby [$hdl orderby]
  35
  36       if {[llength $clist]!=1} { error "unexpected constraint list" }
  37       catch { array unset C }
  38       array set C [lindex $clist 0]
  39       if {$C(usable)} {
  40         return "omit 0 cost 0 rows 1 idxnum 555 idxstr eq!"
  41       } else {
  42         return "cost 1000000 rows 0 idxnum 0 idxstr scan..."
  43       }
  44     }
  45
  46   }
  47
  48   return {}
  49 }
  50
  51 do_execsql_test 1.0 {
  52   CREATE VIRTUAL TABLE x1 USING tcl(vtab_command);
  53 } {}
  54
  55 do_eqp_test 1.1 {
  56   SELECT * FROM x1 WHERE a = 'abc'
  57 } {SCAN x1 VIRTUAL TABLE INDEX 555:eq!}
  58
  59 do_eqp_test 1.2 {
  60   SELECT * FROM x1 WHERE a IN ('abc', 'def');
  61 } {SCAN x1 VIRTUAL TABLE INDEX 555:eq!}
  62
  63 #-------------------------------------------------------------------------
  64 #
  65 reset_db
  66 register_tcl_module db
  67
  68 # Parameter $mode may be one of:
  69 #
  70 #   "omit" - Implement filtering. Set the omit flag.
  71 #   "use"  - Implement filtering. Use the constraint, but do not set omit.
  72 #   "use2" - Do not implement filtering. Use the constraint anyway.
  73 #
  74 #
  75 proc t1_vtab {mode method args} {
  76   switch -- $method {
  77     xConnect {
  78       return "CREATE TABLE t1(a, b)"
  79     }
  80
  81     xBestIndex {
  82       set hdl [lindex $args 0]
  83       set clist [$hdl constraints]
  84       set orderby [$hdl orderby]
  85
  86       set SQL_FILTER {SELECT * FROM t1x WHERE a='%1%'}
  87       set SQL_SCAN   {SELECT * FROM t1x}
  88
  89       set idx 0
  90       for {set idx 0} {$idx < [llength $clist]} {incr idx} {
  91         array unset C
  92         array set C [lindex $clist $idx]
  93         if {$C(column)==0 && $C(op)=="eq" && $C(usable)} {
  94           switch -- $mode {
  95             "omit" {
  96               return [list omit $idx rows 10 cost 10 idxstr $SQL_FILTER]
  97             }
  98             "use" {
  99               return [list use $idx rows 10 cost 10 idxstr $SQL_FILTER]
 100             }
 101             "use2" {
 102               return [list use $idx rows 10 cost 10 idxstr $SQL_SCAN]
 103             }
 104             default {
 105               error "Bad mode - $mode"
 106             }
 107           }
 108         }
 109       }
 110
 111       return [list idxstr {SELECT * FROM t1x}]
 112     }
 113
 114     xFilter {
 115       set map [list %1% [lindex $args 2 0]]
 116       set sql [string map $map [lindex $args 1]]
 117       return [list sql $sql]
 118     }
 119   }
 120
 121   return {}
 122 }
 123
 124 do_execsql_test 2.1 {
 125   CREATE TABLE t1x(i INTEGER PRIMARY KEY, a, b);
 126   INSERT INTO t1x VALUES(1, 'one', 1);
 127   INSERT INTO t1x VALUES(2, 'two', 2);
 128   INSERT INTO t1x VALUES(3, 'three', 3);
 129   INSERT INTO t1x VALUES(4, 'four', 4);
 130 }
 131
 132 foreach {tn mode} {
 133   1 use 2 omit 3 use2
 134 } {
 135   do_execsql_test 2.2.$mode.1 "
 136     DROP TABLE IF EXISTS t1;
 137     CREATE VIRTUAL TABLE t1 USING tcl(t1_vtab $mode);
 138   "
 139
 140   do_execsql_test 2.2.$mode.2 {SELECT * FROM t1} {one 1 two 2 three 3 four 4}
 141   do_execsql_test 2.2.$mode.3 {SELECT rowid FROM t1} {1 2 3 4}
 142   do_execsql_test 2.2.$mode.4 {SELECT rowid FROM t1 WHERE a='two'} {2}
 143
 144   do_execsql_test 2.2.$mode.5 {
 145     SELECT rowid FROM t1 WHERE a IN ('one', 'four') ORDER BY +rowid
 146   } {1 4}
 147
 148   set plan(use) {
 149     QUERY PLAN
 150     |--SCAN t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x WHERE a='%1%'
 151     `--USE TEMP B-TREE FOR ORDER BY
 152   }
 153   set plan(omit) {
 154     QUERY PLAN
 155     |--SCAN t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x WHERE a='%1%'
 156     `--USE TEMP B-TREE FOR ORDER BY
 157   }
 158   set plan(use2) {
 159     QUERY PLAN
 160     |--SCAN t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x
 161     `--USE TEMP B-TREE FOR ORDER BY
 162   }
 163
 164   do_eqp_test 2.2.$mode.6 {
 165     SELECT rowid FROM t1 WHERE a IN ('one', 'four') ORDER BY +rowid
 166   } [string map {"\n  " "\n"} $plan($mode)]
 167 }
 168
 169 # 2016-04-09.
 170 # Demonstrate a register overwrite problem when using two virtual
 171 # tables where the outer loop uses the IN operator.
 172 #
 173 set G(collist) [list PrimaryKey flagA columnA]
 174 set G(cols) [join $G(collist) ,]
 175 set G(nulls) "NULL"
 176
 177 proc vtab_command {method args} {
 178   global G
 179
 180   switch -- $method {
 181     xConnect {
 182       return "CREATE TABLE t1($G(cols))"
 183     }
 184
 185     xBestIndex {
 186       set hdl [lindex $args 0]
 187       set clist [$hdl constraints]
 188       set orderby [$hdl orderby]
 189
 190       #puts $clist
 191       set W [list]
 192       set U [list]
 193
 194       set i 0
 195       for {set idx 0} {$idx < [llength $clist]} {incr idx} {
 196         array set c [lindex $clist $idx]
 197         if {$c(op)=="eq" && $c(usable)} {
 198           lappend W "[lindex $G(collist) $c(column)] = %$i%"
 199           lappend U use $idx
 200           incr i
 201         }
 202       }
 203
 204       if {$W==""} {
 205         set sql "SELECT rowid, * FROM t1"
 206       } else {
 207         set sql "SELECT rowid, * FROM t1 WHERE [join $W { AND }]"
 208       }
 209
 210       return [concat [list idxstr $sql] $U]
 211     }
 212
 213     xFilter {
 214       foreach {idxnum idxstr vals} $args {}
 215
 216       set map [list]
 217       for {set i 0} {$i < [llength $vals]} {incr i} {
 218         lappend map "%$i%"
 219         set v [lindex $vals $i]
 220         if {[string is integer $v]} {
 221           lappend map $v
 222         } else {
 223           lappend map "'$v'"
 224         }
 225       }
 226       set sql [string map $map $idxstr]
 227
 228       #puts "SQL: $sql"
 229       return [list sql $sql]
 230     }
 231   }
 232
 233   return {}
 234 }
 235
 236 db close
 237 forcedelete test.db
 238 sqlite3 db test.db
 239 register_tcl_module db
 240
 241 do_execsql_test 3.1 "
 242   CREATE TABLE t1($G(cols));
 243   INSERT INTO t1 VALUES(1, 0, 'ValueA');
 244   INSERT INTO t1 VALUES(2, 0, 'ValueA');
 245   INSERT INTO t1 VALUES(3, 0, 'ValueB');
 246   INSERT INTO t1 VALUES(4, 0, 'ValueB');
 247 "
 248
 249 do_execsql_test 3.2 {
 250   CREATE VIRTUAL TABLE VirtualTableA USING tcl(vtab_command);
 251   CREATE VIRTUAL TABLE VirtualTableB USING tcl(vtab_command);
 252 }
 253
 254 do_execsql_test 3.3 { SELECT primarykey FROM VirtualTableA } {1 2 3 4}
 255
 256 do_execsql_test 3.4 {
 257   SELECT * FROM
 258   VirtualTableA a CROSS JOIN VirtualTableB b ON b.PrimaryKey=a.PrimaryKey
 259   WHERE a.ColumnA IN ('ValueA', 'ValueB') AND a.FlagA=0
 260 } {
 261   1 0 ValueA 1 0 ValueA
 262   2 0 ValueA 2 0 ValueA
 263   3 0 ValueB 3 0 ValueB
 264   4 0 ValueB 4 0 ValueB
 265 }
 266
 267 do_execsql_test 3.5 {
 268   SELECT * FROM
 269   VirtualTableA a CROSS JOIN VirtualTableB b ON b.PrimaryKey=a.PrimaryKey
 270   WHERE a.FlagA=0 AND a.ColumnA IN ('ValueA', 'ValueB')
 271 } {
 272   1 0 ValueA 1 0 ValueA
 273   2 0 ValueA 2 0 ValueA
 274   3 0 ValueB 3 0 ValueB
 275   4 0 ValueB 4 0 ValueB
 276 }
 277
 278 #-------------------------------------------------------------------------
 279 # If there is an IN(..) condition in the WHERE clause of a query on a
 280 # virtual table, the xBestIndex method is first invoked with the IN(...)
 281 # represented by a "usable" SQLITE_INDEX_CONSTRAINT_EQ constraint. If
 282 # the virtual table elects to use the IN(...) constraint, then the
 283 # xBestIndex method is invoked again, this time with the IN(...) marked
 284 # as "not usable". Depending on the relative costs of the two plans as
 285 # defined by the virtual table implementation, and the cardinality of the
 286 # IN(...) operator, SQLite chooses the most efficient plan.
 287 #
 288 # At one point the second invocation of xBestIndex() was only being made
 289 # for join queries. The following tests check that this problem has been
 290 # fixed.
 291 #
 292 proc vtab_command {method args} {
 293   switch -- $method {
 294     xConnect {
 295       return "CREATE TABLE t1(a, b, c, d)"
 296     }
 297
 298     xBestIndex {
 299       set hdl [lindex $args 0]
 300       set clist [$hdl constraints]
 301       set orderby [$hdl orderby]
 302
 303       lappend ::bestindex_calls $clist
 304       set ret "cost 1000000 idxnum 555"
 305       for {set i 0} {$i < [llength $clist]} {incr i} {
 306         array set C [lindex $clist $i]
 307         if {$C(usable)} {
 308           lappend ret use $i
 309         }
 310       }
 311       return $ret
 312     }
 313   }
 314   return {}
 315 }
 316
 317 do_execsql_test 4.0 {
 318   CREATE VIRTUAL TABLE x1 USING tcl(vtab_command);
 319 } {}
 320
 321 do_test 4.1 {
 322   set ::bestindex_calls [list]
 323   execsql {
 324     SELECT * FROM x1 WHERE a=? AND b BETWEEN ? AND ? AND c IN (1, 2, 3, 4);
 325   }
 326   set ::bestindex_calls
 327 } [list \
 328     [list {op eq column 0 usable 1} \
 329           {op eq column 2 usable 1} \
 330           {op ge column 1 usable 1} \
 331           {op le column 1 usable 1} \
 332     ] \
 333     [list {op eq column 0 usable 1} \
 334           {op eq column 2 usable 0} \
 335           {op ge column 1 usable 1} \
 336           {op le column 1 usable 1}
 337     ]
 338 ]
 339
 340 do_catchsql_test 5.0 {
 341   SELECT * FROM tcl('abc');
 342 } {1 {wrong number of arguments}}
 343
 344 finish_test