xref: /sqlite-3.40.0/test/bestindex1.test (revision c5644346) 
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
14set testdir [file dirname $argv0]
15source $testdir/tester.tcl
16set testprefix bestindex1
17
18ifcapable !vtab {
19  finish_test
20  return
21}
22
23register_tcl_module db
24
25proc vtab_command {method args} {
26  switch -- $method {
27    xConnect {
28      return "CREATE TABLE t1(a, b, c)"
29    }
30
31    xBestIndex {
32      set clist [lindex $args 0]
33      if {[llength $clist]!=1} { error "unexpected constraint list" }
34      catch { array unset C }
35      array set C [lindex $clist 0]
36      if {$C(usable)} {
37        return "omit 0 cost 0 rows 1 idxnum 555 idxstr eq!"
38      } else {
39        return "cost 1000000 rows 0 idxnum 0 idxstr scan..."
40      }
41    }
42
43  }
44
45  return {}
46}
47
48do_execsql_test 1.0 {
49  CREATE VIRTUAL TABLE x1 USING tcl(vtab_command);
50} {}
51
52do_eqp_test 1.1 {
53  SELECT * FROM x1 WHERE a = 'abc'
54} {
55  0 0 0 {SCAN TABLE x1 VIRTUAL TABLE INDEX 555:eq!}
56}
57
58do_eqp_test 1.2 {
59  SELECT * FROM x1 WHERE a IN ('abc', 'def');
60} {
61  0 0 0 {SCAN TABLE x1 VIRTUAL TABLE INDEX 555:eq!}
62  0 0 0 {EXECUTE LIST SUBQUERY 1}
63}
64
65#-------------------------------------------------------------------------
66#
67reset_db
68register_tcl_module db
69
70# Parameter $mode may be one of:
71#
72#   "omit" - Implement filtering. Set the omit flag.
73#   "use"  - Implement filtering. Use the constraint, but do not set omit.
74#   "use2" - Do not implement filtering. Use the constraint anyway.
75#
76#
77proc t1_vtab {mode method args} {
78  switch -- $method {
79    xConnect {
80      return "CREATE TABLE t1(a, b)"
81    }
82
83    xBestIndex {
84      set SQL_FILTER {SELECT * FROM t1x WHERE a='%1%'}
85      set SQL_SCAN   {SELECT * FROM t1x}
86
87      set clist [lindex $args 0]
88      set idx 0
89      for {set idx 0} {$idx < [llength $clist]} {incr idx} {
90        array unset C
91        array set C [lindex $clist $idx]
92        if {$C(column)==0 && $C(op)=="eq" && $C(usable)} {
93          switch -- $mode {
94            "omit" {
95              return [list omit $idx rows 10 cost 10 idxstr $SQL_FILTER]
96            }
97            "use" {
98              return [list use $idx rows 10 cost 10 idxstr $SQL_FILTER]
99            }
100            "use2" {
101              return [list use $idx rows 10 cost 10 idxstr $SQL_SCAN]
102            }
103            default {
104              error "Bad mode - $mode"
105            }
106          }
107        }
108      }
109
110      return [list idxstr {SELECT * FROM t1x}]
111    }
112
113    xFilter {
114      set map [list %1% [lindex $args 2 0]]
115      set sql [string map $map [lindex $args 1]]
116      return [list sql $sql]
117    }
118  }
119
120  return {}
121}
122
123do_execsql_test 2.1 {
124  CREATE TABLE t1x(i INTEGER PRIMARY KEY, a, b);
125  INSERT INTO t1x VALUES(1, 'one', 1);
126  INSERT INTO t1x VALUES(2, 'two', 2);
127  INSERT INTO t1x VALUES(3, 'three', 3);
128  INSERT INTO t1x VALUES(4, 'four', 4);
129}
130
131foreach {tn mode} {
132  1 use 2 omit 3 use2
133} {
134  do_execsql_test 2.2.$mode.1 "
135    DROP TABLE IF EXISTS t1;
136    CREATE VIRTUAL TABLE t1 USING tcl(t1_vtab $mode);
137  "
138
139  do_execsql_test 2.2.$mode.2 {SELECT * FROM t1} {one 1 two 2 three 3 four 4}
140  do_execsql_test 2.2.$mode.3 {SELECT rowid FROM t1} {1 2 3 4}
141  do_execsql_test 2.2.$mode.4 {SELECT rowid FROM t1 WHERE a='two'} {2}
142
143  do_execsql_test 2.2.$mode.5 {
144    SELECT rowid FROM t1 WHERE a IN ('one', 'four') ORDER BY +rowid
145  } {1 4}
146
147  set plan(use) {
148    0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x WHERE a='%1%'}
149    0 0 0 {EXECUTE LIST SUBQUERY 1}
150    0 0 0 {USE TEMP B-TREE FOR ORDER BY}
151  }
152  set plan(omit) {
153    0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x WHERE a='%1%'}
154    0 0 0 {EXECUTE LIST SUBQUERY 1}
155    0 0 0 {USE TEMP B-TREE FOR ORDER BY}
156  }
157  set plan(use2) {
158    0 0 0 {SCAN TABLE t1 VIRTUAL TABLE INDEX 0:SELECT * FROM t1x}
159    0 0 0 {EXECUTE LIST SUBQUERY 1}
160    0 0 0 {USE TEMP B-TREE FOR ORDER BY}
161  }
162
163  do_eqp_test 2.2.$mode.6 {
164    SELECT rowid FROM t1 WHERE a IN ('one', 'four') ORDER BY +rowid
165  } $plan($mode)
166}
167
168finish_test
169