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