Debian Codegraph @

test/scanstatus2.test

raw
1 # 2022 December 5
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 scanstatus2
16
17 ifcapable !scanstatus {
18 finish_test
19 return
20 }
21
22 sqlite3_db_config db STMT_SCANSTATUS 1
23
24 do_execsql_test 1.0 {
25 CREATE TABLE t1(a, b);
26 CREATE TABLE t2(x, y);
27 INSERT INTO t1 VALUES(1, 2);
28 INSERT INTO t1 VALUES(3, 4);
29 INSERT INTO t2 VALUES('a', 'b');
30 INSERT INTO t2 VALUES('c', 'd');
31 INSERT INTO t2 VALUES('e', 'f');
32 }
33
34 proc do_zexplain_test {v2 tn sql res} {
35 db eval $sql
36 set stmt [db version -last-stmt-ptr]
37 set idx 0
38 set ret [list]
39
40 set cmd sqlite3_stmt_scanstatus
41 set f [list]
42 if {$v2} { lappend f complex }
43
44 while {1} {
45 set r [sqlite3_stmt_scanstatus -flags $f $stmt $idx]
46 if {[llength $r]==0} break
47 lappend ret [dict get $r zExplain]
48 incr idx
49 }
50 uplevel [list do_test $tn [list set {} $ret] [list {*}$res]]
51 }
52
53 proc get_cycles {stmt} {
54 set r [sqlite3_stmt_scanstatus $stmt -1]
55 dict get $r nCycle
56 }
57
58 proc foreach_scan {varname stmt body {debug 0}} {
59 upvar $varname var
60 for {set ii 0} {1} {incr ii} {
61 set f "complex"
62 if {$debug} { set f "complex debug" }
63 set r [sqlite3_stmt_scanstatus -flags $f $stmt $ii]
64 if {[llength $r]==0} break
65 array set var $r
66 uplevel $body
67 }
68 }
69
70 proc get_eqp_graph {stmt iPar nIndent} {
71 set res ""
72 foreach_scan A $stmt {
73 if {$A(iParentId)==$iPar} {
74 set txt $A(zExplain)
75 if {$A(nCycle)>=0} {
76 append txt " (nCycle=$A(nCycle))"
77 }
78 append res "[string repeat - $nIndent]$txt\n"
79 append res [get_eqp_graph $stmt $A(iSelectId) [expr $nIndent+2]]
80 }
81 }
82 set res
83 }
84
85 proc get_graph {stmt} {
86 set nCycle [get_cycles $stmt]
87 set res "QUERY (nCycle=$nCycle)\n"
88 append res [get_eqp_graph $stmt 0 2]
89 }
90
91 proc do_graph_test {tn sql res} {
92 db eval $sql
93 set stmt [db version -last-stmt-ptr]
94 set graph [string trim [get_graph $stmt]]
95
96 set graph [regsub -all {nCycle=[0-9]+} $graph nCycle=nnn]
97 uplevel [list do_test $tn [list set {} $graph] [string trim $res]]
98 }
99
100 proc puts_graph {sql} {
101 db eval $sql
102 set stmt [db version -last-stmt-ptr]
103 puts [string trim [get_graph $stmt]]
104 }
105
106 proc puts_debug_info {sql} {
107 db eval $sql
108 set stmt [db version -last-stmt-ptr]
109 foreach_scan X $stmt {
110 puts -nonewline "$X(debug_explain) $X(zExplain): "
111 puts -nonewline "loop=$X(debug_loop) visit=$X(debug_visit) "
112 puts "csr=$X(debug_csr) range=$X(debug_range)"
113 } 1
114 }
115
116 do_zexplain_test 0 1.1 {
117 SELECT (SELECT a FROM t1 WHERE b=x) FROM t2 WHERE y=2
118 } {
119 {SCAN t2}
120 {SCAN t1}
121 }
122 do_zexplain_test 1 1.2 {
123 SELECT (SELECT a FROM t1 WHERE b=x) FROM t2 WHERE y=2
124 } {
125 {SCAN t2}
126 {CORRELATED SCALAR SUBQUERY 1}
127 {SCAN t1}
128 }
129
130 do_graph_test 1.3 {
131 SELECT (SELECT a FROM t1 WHERE b=x) FROM t2 WHERE y=2
132 } {
133 QUERY (nCycle=nnn)
134 --SCAN t2 (nCycle=nnn)
135 --CORRELATED SCALAR SUBQUERY 1 (nCycle=nnn)
136 ----SCAN t1 (nCycle=nnn)
137 }
138
139 do_graph_test 1.4 {
140 WITH v2(x,y) AS MATERIALIZED (
141 SELECT x,y FROM t2
142 )
143 SELECT * FROM t1, v2 ORDER BY y;
144 } {
145 QUERY (nCycle=nnn)
146 --MATERIALIZE v2 (nCycle=nnn)
147 ----SCAN t2 (nCycle=nnn)
148 --SCAN t1 (nCycle=nnn)
149 --SCAN v2 (nCycle=nnn)
150 --USE TEMP B-TREE FOR ORDER BY (nCycle=nnn)
151 }
152
153 #-------------------------------------------------------------------------
154 ifcapable fts5 {
155 reset_db
156 sqlite3_db_config db STMT_SCANSTATUS 1
157 do_execsql_test 2.0 {
158 CREATE VIRTUAL TABLE ft USING fts5(a);
159 INSERT INTO ft VALUES('abc');
160 INSERT INTO ft VALUES('def');
161 INSERT INTO ft VALUES('ghi');
162 }
163
164 do_graph_test 2.1 {
165 SELECT * FROM ft('def')
166 } {
167 QUERY (nCycle=nnn)
168 --SCAN ft VIRTUAL TABLE INDEX 0:M1 (nCycle=nnn)
169 }
170 }
171
172 #-------------------------------------------------------------------------
173 reset_db
174 sqlite3_db_config db STMT_SCANSTATUS 1
175 do_execsql_test 3.0 {
176 CREATE TABLE x1(a, b);
177 CREATE TABLE x2(c, d);
178
179 WITH s(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<1000)
180 INSERT INTO x1 SELECT i, i FROM s;
181 INSERT INTO x2 SELECT a, b FROM x1;
182 }
183
184 do_graph_test 2.1 {
185 SELECT * FROM x1, x2 WHERE c=+a;
186 } {
187 QUERY (nCycle=nnn)
188 --SCAN x1 (nCycle=nnn)
189 --CREATE AUTOMATIC INDEX ON x2(c, d) (nCycle=nnn)
190 --BLOOM FILTER ON x2 (c=?)
191 --SEARCH x2 USING AUTOMATIC COVERING INDEX (c=?) (nCycle=nnn)
192 }
193
194 #-------------------------------------------------------------------------
195 reset_db
196 sqlite3_db_config db STMT_SCANSTATUS 1
197 do_execsql_test 4.0 {
198 CREATE TABLE rt1 (id INTEGER PRIMARY KEY, x1, x2);
199 CREATE TABLE rt2 (id, x1, x2);
200 }
201
202 do_graph_test 4.1 {
203 SELECT * FROM rt1, rt2 WHERE rt1.id%2 AND rt2.x1=rt1.x1;
204 } {
205 QUERY (nCycle=nnn)
206 --SCAN rt1 (nCycle=nnn)
207 --CREATE AUTOMATIC INDEX ON rt2(x1, id, x2) (nCycle=nnn)
208 --BLOOM FILTER ON rt2 (x1=?)
209 --SEARCH rt2 USING AUTOMATIC COVERING INDEX (x1=?) (nCycle=nnn)
210 }
211
212 do_graph_test 4.2 {
213 SELECT rt2.id FROM rt1, rt2 WHERE rt1.id%2 AND rt2.x1=rt1.x1;
214 } {
215 QUERY (nCycle=nnn)
216 --SCAN rt1 (nCycle=nnn)
217 --CREATE AUTOMATIC INDEX ON rt2(x1, id) (nCycle=nnn)
218 --BLOOM FILTER ON rt2 (x1=?)
219 --SEARCH rt2 USING AUTOMATIC COVERING INDEX (x1=?) (nCycle=nnn)
220 }
221
222 do_graph_test 4.3 {
223 SELECT rt2.id FROM rt1, rt2 WHERE rt1.id%2 AND (rt2.x1+1)=(rt1.x1+1);
224 } {
225 QUERY (nCycle=nnn)
226 --SCAN rt1 (nCycle=nnn)
227 --SCAN rt2 (nCycle=nnn)
228 }
229
230 do_graph_test 4.4 {
231 SELECT rt2.id FROM rt1, rt2 WHERE rt1.id%2 AND rt2.x1=(rt1.x1+1) AND rt2.id>5;
232 } {
233 QUERY (nCycle=nnn)
234 --SCAN rt1 (nCycle=nnn)
235 --CREATE AUTOMATIC INDEX ON rt2(x1, id) WHERE <expr> (nCycle=nnn)
236 --BLOOM FILTER ON rt2 (x1=?)
237 --SEARCH rt2 USING AUTOMATIC PARTIAL COVERING INDEX (x1=?) (nCycle=nnn)
238 }
239
240 do_graph_test 4.5 {
241 SELECT v1.cnt FROM rt1, (
242 SELECT count(*) AS cnt, rt2.x1 AS x1 FROM rt2 GROUP BY x1
243 ) AS v1 WHERE rt1.x1=v1.x1
244 } {
245 QUERY (nCycle=nnn)
246 --CO-ROUTINE v1
247 ----SCAN rt2 (nCycle=nnn)
248 ----USE TEMP B-TREE FOR GROUP BY (nCycle=nnn)
249 --SCAN rt1 (nCycle=nnn)
250 --CREATE AUTOMATIC INDEX ON v1(x1, cnt) (nCycle=nnn)
251 --BLOOM FILTER ON v1 (x1=?)
252 --SEARCH v1 USING AUTOMATIC COVERING INDEX (x1=?) (nCycle=nnn)
253 }
254
255 #-------------------------------------------------------------------------
256 reset_db
257
258 ifcapable trace {
259 do_execsql_test 5.0 {
260 CREATE TABLE t1(x, y);
261 CREATE TRIGGER tr1 AFTER DELETE ON t1 BEGIN
262 SELECT 1;
263 END;
264 INSERT INTO t1 VALUES(1, 2);
265 }
266
267 proc trace {stmt sql} {
268 array set A [sqlite3_stmt_scanstatus -flags complex [format %llx $stmt] 0]
269 lappend ::trace_explain $A(zExplain)
270 }
271 db trace_v2 trace
272
273 set ::trace_explain [list]
274 do_execsql_test 5.1 {
275 DELETE FROM t1 WHERE x=1;
276 }
277
278 do_test 5.2 {
279 set ::trace_explain
280 } {{SCAN t1} {SCAN t1} {SCAN t1}}
281 }
282
283 #-------------------------------------------------------------------------
284 reset_db
285 sqlite3_db_config db STMT_SCANSTATUS 1
286
287 do_execsql_test 6.0 {
288 CREATE TABLE t1(a, b);
289 INSERT INTO t1 VALUES(1, 'one');
290 INSERT INTO t1 VALUES(2, 'two');
291 INSERT INTO t1 VALUES(3, 'three');
292 INSERT INTO t1 VALUES(4, 'four');
293 INSERT INTO t1 VALUES(5, 'five');
294 INSERT INTO t1 VALUES(6, 'six');
295 INSERT INTO t1 VALUES(7, 'seven');
296 INSERT INTO t1 VALUES(8, 'eight');
297 }
298
299 do_graph_test 6.1 {
300 SELECT (a % 2), group_concat(b) FROM t1 GROUP BY 1
301 } {
302 QUERY (nCycle=nnn)
303 --SCAN t1 (nCycle=nnn)
304 --USE TEMP B-TREE FOR GROUP BY (nCycle=nnn)
305 }
306
307 set sql {
308 WITH xy(x, y) AS ( SELECT (a % 2), group_concat(b) FROM t1 GROUP BY 1)
309 SELECT * FROM xy WHERE x=1
310 }
311 do_graph_test 6.2 $sql {
312 QUERY (nCycle=nnn)
313 --CO-ROUTINE xy
314 ----SCAN t1 (nCycle=nnn)
315 ----USE TEMP B-TREE FOR GROUP BY (nCycle=nnn)
316 --SCAN xy (nCycle=nnn)
317 }
318
319 do_graph_test 6.3 {
320 WITH xy(x, y) AS ( SELECT (a % 2), group_concat(b) FROM t1 GROUP BY 1)
321 SELECT * FROM xy, xy AS xy2
322 } {
323 QUERY (nCycle=nnn)
324 --MATERIALIZE xy (nCycle=nnn)
325 ----SCAN t1 (nCycle=nnn)
326 ----USE TEMP B-TREE FOR GROUP BY (nCycle=nnn)
327 --SCAN xy (nCycle=nnn)
328 --SCAN xy2 (nCycle=nnn)
329 }
330
331 #-------------------------------------------------------------------------
332 reset_db
333
334 # Check that an OOB parameter (45) does not cause asan or valgrind errors.
335 #
336 do_test 7.0 {
337 db eval {SELECT * FROM sqlite_schema}
338 set stmt [db version -last-stmt-ptr]
339 sqlite3_stmt_scanstatus -flags complex $stmt 1000000
340 } {}
341
342 #explain_i { SELECT (a % 2), group_concat(b) FROM t1 GROUP BY 1 }
343 #puts_debug_info { SELECT (a % 2), group_concat(b) FROM t1 GROUP BY 1 }
344
345 finish_test