xref: /sqlite-3.40.0/test/bestindex3.test (revision d96ab995) 
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      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
77register_tcl_module db
78
79do_execsql_test 1.0 {
80  CREATE VIRTUAL TABLE t1 USING tcl("vtab_cmd 0");
81}
82
83do_eqp_test 1.1 {
84  SELECT * FROM t1 WHERE a LIKE 'abc';
85} {SCAN t1 VIRTUAL TABLE INDEX 0:a LIKE ?}
86
87do_eqp_test 1.2 {
88  SELECT * FROM t1 WHERE a = 'abc';
89} {SCAN t1 VIRTUAL TABLE INDEX 0:a EQ ?}
90
91do_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
102do_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
113do_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
124foreach 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#
148ifcapable !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#
171proc vvv_command {method args} {
172  switch -- $method {
173    xConnect { return "CREATE TABLE t1(a PRIMARY KEY, b, c)" }
174  }
175}
176proc yyy_command {method args} {
177  switch -- $method {
178    xConnect { return "CREATE TABLE t1(a, b, c, PRIMARY KEY(a, b))" }
179  }
180}
181
182do_execsql_test 3.1 { CREATE VIRTUAL TABLE t3 USING tcl('vvv_command') }
183do_execsql_test 3.2 { CREATE VIRTUAL TABLE t4 USING tcl('yyy_command') }
184
185finish_test
186