xref: /sqlite-3.40.0/test/bestindex3.test (revision dfe4e6bb) 
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
12set testdir [file dirname $argv0]
13source $testdir/tester.tcl
14set testprefix bestindex3
15
16ifcapable !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#
30proc 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
74register_tcl_module db
75
76do_execsql_test 1.0 {
77  CREATE VIRTUAL TABLE t1 USING tcl("vtab_cmd 0");
78}
79
80do_eqp_test 1.1 {
81  SELECT * FROM t1 WHERE a LIKE 'abc';
82} {
83  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a LIKE ?}
84}
85
86do_eqp_test 1.2 {
87  SELECT * FROM t1 WHERE a = 'abc';
88} {
89  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a EQ ?}
90}
91
92do_eqp_test 1.3 {
93  SELECT * FROM t1 WHERE a = 'abc' OR b = 'def';
94} {
95  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a EQ ?}
96  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:b EQ ?}
97}
98
99do_eqp_test 1.4 {
100  SELECT * FROM t1 WHERE a LIKE 'abc%' OR b = 'def';
101} {
102  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:a LIKE ?}
103  0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:b EQ ?}
104}
105
106do_execsql_test 1.5 {
107  CREATE TABLE ttt(a, b, c);
108
109  INSERT INTO ttt VALUES(1, 'two',   'three');
110  INSERT INTO ttt VALUES(2, 'one',   'two');
111  INSERT INTO ttt VALUES(3, 'three', 'one');
112  INSERT INTO ttt VALUES(4, 'y',     'one');
113  INSERT INTO ttt VALUES(5, 'x',     'two');
114  INSERT INTO ttt VALUES(6, 'y',     'three');
115}
116
117foreach omit {0 1} {
118  do_execsql_test 1.6.$omit.0 "
119    DROP TABLE t1;
120    CREATE VIRTUAL TABLE t1 USING tcl('vtab_cmd $omit');
121  "
122  do_execsql_test 1.6.$omit.1 {
123    SELECT rowid FROM t1 WHERE c LIKE 'o%'
124  } {3 4}
125
126  do_execsql_test 1.6.$omit.2 {
127    SELECT rowid FROM t1 WHERE c LIKE 'o%' OR b='y'
128  } {3 4 6}
129
130  do_execsql_test 1.6.$omit.3 {
131    SELECT rowid FROM t1 WHERE c = 'three' OR c LIKE 'o%'
132  } {1 6 3 4}
133}
134
135#-------------------------------------------------------------------------
136# Test the same pattern works with ordinary tables.
137#
138# This test does not work if the ICU extension is enabled. ICU overrides
139# LIKE - and this optimization only works with the built-in LIKE function.
140#
141ifcapable !icu {
142  do_execsql_test 2.1 {
143    CREATE TABLE t2(x TEXT COLLATE nocase, y TEXT);
144    CREATE INDEX t2x ON t2(x COLLATE nocase);
145    CREATE INDEX t2y ON t2(y);
146  }
147
148  do_eqp_test 2.2 {
149    SELECT * FROM t2 WHERE x LIKE 'abc%' OR y = 'def'
150  } {
151    0 0 0 {SEARCH TABLE t2 USING INDEX t2x (x>? AND x<?)}
152    0 0 0 {SEARCH TABLE t2 USING INDEX t2y (y=?)}
153  }
154}
155
156#-------------------------------------------------------------------------
157# Test that any PRIMARY KEY within a sqlite3_decl_vtab() CREATE TABLE
158# statement is currently ignored.
159#
160proc vvv_command {method args} {
161  switch -- $method {
162    xConnect { return "CREATE TABLE t1(a PRIMARY KEY, b, c)" }
163  }
164}
165proc yyy_command {method args} {
166  switch -- $method {
167    xConnect { return "CREATE TABLE t1(a, b, c, PRIMARY KEY(a, b))" }
168  }
169}
170
171do_execsql_test 3.1 { CREATE VIRTUAL TABLE t3 USING tcl('vvv_command') }
172do_execsql_test 3.2 { CREATE VIRTUAL TABLE t4 USING tcl('yyy_command') }
173
174finish_test
175
176