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