test/bestindex3.test

   1 # 2016 May 29
   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 set testdir [file dirname $argv0]
  13 source $testdir/tester.tcl
  14 set testprefix bestindex3
  15
  16 ifcapable !vtab {
  17   finish_test
  18   return
  19 }
  20
  21 #-------------------------------------------------------------------------
  22 # Virtual table callback for a virtual table named $tbl.
  23 #
  24 # The table created is:
  25 #
  26 #      "CREATE TABLE t1 (a, b, c)"
  27 #
  28 # This virtual table supports both LIKE and = operators on all columns.
  29 #
  30 proc vtab_cmd {bOmit method args} {
  31   switch -- $method {
  32     xConnect {
  33       return "CREATE TABLE t1(a, b, c)"
  34     }
  35
  36     xBestIndex {
  37       set hdl [lindex $args 0]
  38       set clist [$hdl constraints]
  39       set orderby [$hdl orderby]
  40       set mask [$hdl mask]
  41
  42       set ret [list]
  43       set use use
  44       if {$bOmit} {set use omit}
  45
  46       for {set i 0} {$i < [llength $clist]} {incr i} {
  47         array unset C
  48         array set C [lindex $clist $i]
  49         if {$C(usable) && ($C(op)=="like" || $C(op)=="eq")} {
  50           lappend ret $use $i
  51           lappend ret idxstr
  52           lappend ret "[lindex {a b c} $C(column)] [string toupper $C(op)] ?"
  53           break
  54         }
  55       }
  56
  57       if {$ret==""} {
  58         lappend ret cost 1000000 rows 1000000
  59       } else {
  60         lappend ret cost 100 rows 10
  61       }
  62       return $ret
  63     }
  64
  65     xFilter {
  66       foreach {idxnum idxstr param} $args {}
  67       set where ""
  68       if {$bOmit && $idxstr != ""} {
  69         set where " WHERE [string map [list ? '$param' EQ =] $idxstr]"
  70       }
  71       return [list sql "SELECT rowid, * FROM ttt$where"]
  72     }
  73   }
  74   return ""
  75 }
  76
  77 register_tcl_module db
  78
  79 do_execsql_test 1.0 {
  80   CREATE VIRTUAL TABLE t1 USING tcl("vtab_cmd 0");
  81 }
  82
  83 do_eqp_test 1.1 {
  84   SELECT * FROM t1 WHERE a LIKE 'abc';
  85 } {SCAN t1 VIRTUAL TABLE INDEX 0:a LIKE ?}
  86
  87 do_eqp_test 1.2 {
  88   SELECT * FROM t1 WHERE a = 'abc';
  89 } {SCAN t1 VIRTUAL TABLE INDEX 0:a EQ ?}
  90
  91 do_eqp_test 1.3 {
  92   SELECT * FROM t1 WHERE a = 'abc' OR b = 'def';
  93 } {
  94   QUERY PLAN
  95   `--MULTI-INDEX OR
  96      |--INDEX 1
  97      |  `--SCAN t1 VIRTUAL TABLE INDEX 0:a EQ ?
  98      `--INDEX 2
  99         `--SCAN t1 VIRTUAL TABLE INDEX 0:b EQ ?
 100 }
 101
 102 do_eqp_test 1.4 {
 103   SELECT * FROM t1 WHERE a LIKE 'abc%' OR b = 'def';
 104 } {
 105   QUERY PLAN
 106   `--MULTI-INDEX OR
 107      |--INDEX 1
 108      |  `--SCAN t1 VIRTUAL TABLE INDEX 0:a LIKE ?
 109      `--INDEX 2
 110         `--SCAN t1 VIRTUAL TABLE INDEX 0:b EQ ?
 111 }
 112
 113 do_execsql_test 1.5 {
 114   CREATE TABLE ttt(a, b, c);
 115
 116   INSERT INTO ttt VALUES(1, 'two',   'three');
 117   INSERT INTO ttt VALUES(2, 'one',   'two');
 118   INSERT INTO ttt VALUES(3, 'three', 'one');
 119   INSERT INTO ttt VALUES(4, 'y',     'one');
 120   INSERT INTO ttt VALUES(5, 'x',     'two');
 121   INSERT INTO ttt VALUES(6, 'y',     'three');
 122 }
 123
 124 foreach omit {0 1} {
 125   do_execsql_test 1.6.$omit.0 "
 126     DROP TABLE t1;
 127     CREATE VIRTUAL TABLE t1 USING tcl('vtab_cmd $omit');
 128   "
 129   do_execsql_test 1.6.$omit.1 {
 130     SELECT rowid FROM t1 WHERE c LIKE 'o%'
 131   } {3 4}
 132
 133   do_execsql_test 1.6.$omit.2 {
 134     SELECT rowid FROM t1 WHERE c LIKE 'o%' OR b='y'
 135   } {3 4 6}
 136
 137   do_execsql_test 1.6.$omit.3 {
 138     SELECT rowid FROM t1 WHERE c = 'three' OR c LIKE 'o%'
 139   } {1 6 3 4}
 140 }
 141
 142 #-------------------------------------------------------------------------
 143 # Test the same pattern works with ordinary tables.
 144 #
 145 # This test does not work if the ICU extension is enabled. ICU overrides
 146 # LIKE - and this optimization only works with the built-in LIKE function.
 147 #
 148 ifcapable !icu {
 149   do_execsql_test 2.1 {
 150     CREATE TABLE t2(x TEXT COLLATE nocase, y TEXT);
 151     CREATE INDEX t2x ON t2(x COLLATE nocase);
 152     CREATE INDEX t2y ON t2(y);
 153   }
 154
 155   do_eqp_test 2.2 {
 156     SELECT * FROM t2 WHERE x LIKE 'abc%' OR y = 'def'
 157   } [string map {"\n  " \n} {
 158     QUERY PLAN
 159     `--MULTI-INDEX OR
 160        |--INDEX 1
 161        |  `--SEARCH t2 USING INDEX t2x (x>? AND x<?)
 162        `--INDEX 2
 163           `--SEARCH t2 USING INDEX t2y (y=?)
 164   }]
 165 }
 166
 167 #-------------------------------------------------------------------------
 168 # Test that any PRIMARY KEY within a sqlite3_decl_vtab() CREATE TABLE
 169 # statement is currently ignored.
 170 #
 171 proc vvv_command {method args} {
 172   switch -- $method {
 173     xConnect { return "CREATE TABLE t1(a PRIMARY KEY, b, c)" }
 174   }
 175 }
 176 proc yyy_command {method args} {
 177   switch -- $method {
 178     xConnect { return "CREATE TABLE t1(a, b, c, PRIMARY KEY(a, b))" }
 179   }
 180 }
 181
 182 do_execsql_test 3.1 { CREATE VIRTUAL TABLE t3 USING tcl('vvv_command') }
 183 do_execsql_test 3.2 { CREATE VIRTUAL TABLE t4 USING tcl('yyy_command') }
 184
 185 finish_test