xref: /sqlite-3.40.0/test/bestindex2.test (revision d96ab995) 
1# 2016 March 3
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 bestindex2
15
16ifcapable !vtab {
17  finish_test
18  return
19}
20
21#-------------------------------------------------------------------------
22# Virtual table callback for table named $tbl, with the columns specified
23# by list argument $cols. e.g. if the function is invoked as:
24#
25#   vtab_cmd t1 {a b c} ...
26#
27# The table created is:
28#
29#      "CREATE TABLE t1 (a, b, c)"
30#
31# The tables xBestIndex method behaves as if all possible combinations of
32# "=" constraints (but no others) may be optimized. The cost of a full table
33# scan is:
34#
35#      "WHERE 1"                "cost 1000000 rows 1000000"
36#
37# If one or more "=" constraints are in use, the cost and estimated number
38# of rows returned are both is (11 - nCons)*1000, where nCons is the number
39# of constraints used. e.g.
40#
41#   "WHERE a=? AND b=?"    ->   "cost  900 rows  900"
42#   "WHERE c=? AND b<?"    ->   "cost 1000 rows 1000"
43#
44proc vtab_cmd {tbl cols method args} {
45  switch -- $method {
46    xConnect {
47      return "CREATE TABLE $tbl ([join $cols ,])"
48    }
49    xBestIndex {
50      set hdl [lindex $args 0]
51      set clist [$hdl constraints]
52      set orderby [$hdl orderby]
53      set mask [$hdl mask]
54
55      set cons [list]
56      set used [list]
57
58      for {set i 0} {$i < [llength $clist]} {incr i} {
59        array unset C
60        array set C [lindex $clist $i]
61        if {$C(op)=="eq" && $C(usable) && [lsearch $cons $C(column)]<0} {
62          lappend used use $i
63          lappend cons $C(column)
64        }
65      }
66
67      set nCons [llength $cons]
68      if {$nCons==0} {
69        return "cost 1000000 rows 1000000"
70      } else {
71        set cost [expr (11-$nCons) * 1000]
72        set ret [concat $used "cost $cost rows $cost"]
73
74        set txt [list]
75        foreach c $cons { lappend txt "[lindex $cols $c]=?" }
76        lappend ret idxstr "indexed([join $txt { AND }])"
77
78        return $ret
79      }
80    }
81  }
82  return ""
83}
84
85register_tcl_module db
86
87do_execsql_test 1.0 {
88  CREATE VIRTUAL TABLE t1 USING tcl("vtab_cmd t1 {a b}");
89  CREATE VIRTUAL TABLE t2 USING tcl("vtab_cmd t2 {c d}");
90  CREATE VIRTUAL TABLE t3 USING tcl("vtab_cmd t3 {e f}");
91}
92
93do_eqp_test 1.1 {
94  SELECT * FROM t1 WHERE a='abc'
95} {SCAN t1 VIRTUAL TABLE INDEX 0:indexed(a=?)}
96
97do_eqp_test 1.2 {
98  SELECT * FROM t1 WHERE a='abc' AND b='def'
99} {SCAN t1 VIRTUAL TABLE INDEX 0:indexed(a=? AND b=?)}
100
101do_eqp_test 1.3 {
102  SELECT * FROM t1 WHERE a='abc' AND a='def'
103} {SCAN t1 VIRTUAL TABLE INDEX 0:indexed(a=?)}
104
105do_eqp_test 1.4 {
106  SELECT * FROM t1,t2 WHERE c=a
107} {
108  QUERY PLAN
109  |--SCAN t1 VIRTUAL TABLE INDEX 0:
110  `--SCAN t2 VIRTUAL TABLE INDEX 0:indexed(c=?)
111}
112
113do_eqp_test 1.5 {
114  SELECT * FROM t1, t2 CROSS JOIN t3 WHERE t2.c = +t1.b AND t3.e=t2.d
115} {
116  QUERY PLAN
117  |--SCAN t1 VIRTUAL TABLE INDEX 0:
118  |--SCAN t2 VIRTUAL TABLE INDEX 0:indexed(c=?)
119  `--SCAN t3 VIRTUAL TABLE INDEX 0:indexed(e=?)
120}
121
122do_eqp_test 1.6 {
123  SELECT * FROM t1, t2, t3 WHERE t2.c = +t1.b AND t3.e = t2.d
124} {
125  QUERY PLAN
126  |--SCAN t1 VIRTUAL TABLE INDEX 0:
127  |--SCAN t2 VIRTUAL TABLE INDEX 0:indexed(c=?)
128  `--SCAN t3 VIRTUAL TABLE INDEX 0:indexed(e=?)
129}
130
131do_execsql_test 1.7.1 {
132  CREATE TABLE x1(a, b);
133}
134do_eqp_test 1.7.2 {
135  SELECT * FROM x1 CROSS JOIN t1, t2, t3
136    WHERE t1.a = t2.c AND t1.b = t3.e
137} {
138  QUERY PLAN
139  |--SCAN x1
140  |--SCAN t1 VIRTUAL TABLE INDEX 0:
141  |--SCAN t2 VIRTUAL TABLE INDEX 0:indexed(c=?)
142  `--SCAN t3 VIRTUAL TABLE INDEX 0:indexed(e=?)
143}
144
145finish_test
146