Debian Codegraph @

test/bestindexF.test

raw
1 # 2025-12-15
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 set testdir [file dirname $argv0]
14 source $testdir/tester.tcl
15 set testprefix bestindexF
16
17 ifcapable !vtab {
18 finish_test
19 return
20 }
21
22
23 proc vtab_command {method args} {
24 switch -- $method {
25 xConnect {
26 return "CREATE TABLE t1(a, b, c)"
27 }
28
29 xBestIndex {
30 set hdl [lindex $args 0]
31 set ::vtab_orderby [$hdl orderby]
32 set ::vtab_distinct [$hdl distinct]
33
34 if {$::vtab_orderby == "{column 0 desc 0} {column 1 desc 0}"
35 || $::vtab_orderby == "{column 0 desc 0}"
36 } {
37 return [list orderby 1]
38 }
39
40 return ""
41 }
42
43 xFilter {
44 set sql {
45 SELECT 1, 1, 'a', 555
46 UNION ALL
47 SELECT 2, 1, 'a', NULL
48 UNION ALL
49 SELECT 3, 1, 'b', 'text'
50 UNION ALL
51 SELECT 4, 2, 'a', 3.14
52 UNION ALL
53 SELECT 5, 2, 'b', 0
54 }
55 return [list sql $sql]
56 }
57 }
58
59 return {}
60 }
61
62 register_tcl_module db
63
64 do_execsql_test 1.0 {
65 CREATE VIRTUAL TABLE t1 USING tcl(vtab_command)
66 }
67
68 proc uses_idxinsert {sql} {
69 return [expr [lsearch [db eval "explain $sql"] IdxInsert]>=0]
70 }
71 proc do_idxinsert_test {tn sql res} {
72 set uses [uses_idxinsert $sql]
73 uplevel [list do_execsql_test $tn "SELECT $uses ; $sql" $res]
74 }
75
76 do_idxinsert_test 1.1.1 {
77 SELECT DISTINCT a, b FROM t1
78 } {0 1 a 1 b 2 a 2 b}
79
80 do_test 1.1.2 {
81 list $::vtab_distinct $::vtab_orderby
82 } {2 {{column 0 desc 0} {column 1 desc 0}}}
83
84 do_execsql_test 1.3 {
85 CREATE TABLE t0(c0);
86 INSERT INTO t0 VALUES(0);
87 INSERT INTO t0 VALUES(1);
88 }
89
90 do_idxinsert_test 1.4.1 {
91 SELECT DISTINCT t0.c0 FROM t1, t0 ORDER BY t1.a;
92 } {1 0 1}
93
94 do_test 1.4.2 {
95 list $::vtab_distinct $::vtab_orderby
96 } {3 {{column 0 desc 0}}}
97
98 #-------------------------------------------------------------------------
99 #
100 reset_db
101 proc vtab_command {method args} {
102 switch -- $method {
103 xConnect {
104 return "CREATE TABLE t1(a, b, c)"
105 }
106
107 xBestIndex {
108 set hdl [lindex $args 0]
109 set ::vtab_orderby [$hdl orderby]
110 set ::vtab_distinct [$hdl distinct]
111
112 # Set idxNum to 1 if DISTINCT is to be used in xFilter.
113 #
114 set idxStr [list ""]
115 if {$::vtab_distinct==2 || $::vtab_distinct==3} {
116 set idxStr [list DISTINCT]
117 }
118
119 set orderby 0
120 if {$::vtab_orderby == "{column 0 desc 1}"
121 || $::vtab_orderby == "{column 0 desc 0}"
122 } {
123 set orderby 1
124 if {$::vtab_distinct==1 || $::vtab_distinct==2} {
125 lappend idxStr "ORDER BY ((a+2)%5)"
126 } else {
127 set sort "ORDER BY a"
128 if {$::vtab_orderby == "{column 0 desc 1}"} {
129 append sort " DESC"
130 }
131 lappend idxStr $sort
132 }
133 } else {
134 lappend idxStr ""
135 }
136
137 return [list orderby $orderby idxstr $idxStr]
138 return ""
139 }
140
141 xFilter {
142 set idxstr [lindex $args 1]
143
144 set distinct [lindex $idxstr 0]
145 set orderby [lindex $idxstr 1]
146 set sql "
147 SELECT $distinct 0, a, b FROM real_t1 $orderby
148 "
149 return [list sql $sql]
150 }
151 }
152
153 return {}
154 }
155
156 do_execsql_test 2.0 {
157 CREATE TABLE real_t1(a, b);
158
159 INSERT INTO real_t1 VALUES (1, 'a');
160 INSERT INTO real_t1 VALUES (2, 'a');
161 INSERT INTO real_t1 VALUES (1, 'a');
162
163 INSERT INTO real_t1 VALUES (2, 'b');
164 INSERT INTO real_t1 VALUES (1, 'b');
165 INSERT INTO real_t1 VALUES (2, 'b');
166
167 INSERT INTO real_t1 VALUES (3, 'a');
168 INSERT INTO real_t1 VALUES (4, 'b');
169 INSERT INTO real_t1 VALUES (3, 'a');
170
171 INSERT INTO real_t1 VALUES (4, 'b');
172 INSERT INTO real_t1 VALUES (3, 'a');
173 INSERT INTO real_t1 VALUES (4, 'b');
174 }
175
176 register_tcl_module db
177 do_execsql_test 2.0 {
178 CREATE VIRTUAL TABLE t1 USING tcl(vtab_command)
179 }
180
181 do_execsql_test 2.1 {
182 SELECT a, b FROM t1
183 } {
184 1 a 2 a 1 a
185 2 b 1 b 2 b
186 3 a 4 b 3 a
187 4 b 3 a 4 b
188 }
189
190 # This is like do_execsql_test, except one value is prepended to the
191 # expected result - the P4 (idxStr) of the VFilter opcode. It is an error
192 # if $sql generates two or more VFilter instructions.
193 #
194 proc do_vtabsorter_test {tn sql expect} {
195 set vm [db eval "EXPLAIN $sql"]
196
197 set ii [lsearch $vm VFilter]
198 set ::res [lindex $vm [expr $ii+4]]
199
200 set ::idx [expr [lsearch $vm IdxInsert]>=0]
201
202 set iSort [lsearch $vm SorterSort]
203 if {$iSort>=0} {
204 error "query is using sorter"
205 }
206
207 uplevel [list do_test $tn.0 { set ::idx } [lindex $expect 0]]
208 uplevel [list do_test $tn.1 { set ::res } [lindex $expect 1]]
209 uplevel [list do_execsql_test $tn.2 $sql [lrange $expect 2 end]]
210 }
211
212 do_vtabsorter_test 2.2 {
213 SELECT a, b FROM t1
214 } { 0 "{} {}"
215 1 a 2 a 1 a
216 2 b 1 b 2 b
217 3 a 4 b 3 a
218 4 b 3 a 4 b
219 }
220
221 do_vtabsorter_test 2.3 {
222 SELECT DISTINCT a FROM t1
223 } { 0 "DISTINCT {ORDER BY ((a+2)%5)}"
224 3 4 1 2
225 }
226
227 do_vtabsorter_test 2.4 {
228 SELECT DISTINCT a FROM t1 ORDER BY a
229 } { 0 "DISTINCT {ORDER BY a}"
230 1 2 3 4
231 }
232
233 do_vtabsorter_test 2.5 {
234 SELECT DISTINCT a FROM t1 ORDER BY a DESC
235 } { 0 "DISTINCT {ORDER BY a DESC}"
236 4 3 2 1
237 }
238
239 do_vtabsorter_test 2.6 {
240 SELECT a FROM t1 ORDER BY a
241 } { 0 "{} {ORDER BY a}"
242 1 1 1
243 2 2 2
244 3 3 3
245 4 4 4
246 }
247
248 do_vtabsorter_test 2.7 {
249 SELECT a FROM t1 ORDER BY a DESC
250 } { 0 "{} {ORDER BY a DESC}"
251 4 4 4
252 3 3 3
253 2 2 2
254 1 1 1
255 }
256
257 do_vtabsorter_test 2.8 {
258 SELECT a, count(*) FROM t1 GROUP BY a ORDER BY a
259 } { 0 "{} {ORDER BY a}"
260 1 3
261 2 3
262 3 3
263 4 3
264 }
265
266 do_vtabsorter_test 2.9 {
267 SELECT a, count(*) FROM t1 GROUP BY a ORDER BY a DESC
268 } { 0 "{} {ORDER BY a DESC}"
269 4 3
270 3 3
271 2 3
272 1 3
273 }
274
275 do_vtabsorter_test 2.10 {
276 SELECT a, count(*) FROM t1 GROUP BY a
277 } { 0 "{} {ORDER BY ((a+2)%5)}"
278 3 3
279 4 3
280 1 3
281 2 3
282 }
283
284 do_vtabsorter_test 2.11 {
285 SELECT DISTINCT a, count(*) FROM t1 GROUP BY a
286 } { 1 "{} {ORDER BY ((a+2)%5)}"
287 3 3
288 4 3
289 1 3
290 2 3
291 }
292
293 finish_test
294