3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
13 # The focus of this file is testing the CLI shell tool. Specifically,
14 # the ".recommend" command.
21 if {![info exists testdir]} {
22 set testdir [file join [file dirname [info script]] .. .. test]
24 source $testdir/tester.tcl
25 set testprefix expert1
27 if {[info commands sqlite3_expert_new]==""} {
33 set CLI [test_binary_name sqlite3]
34 set CMD [test_binary_name sqlite3_expert]
37 regsub -all {[[:space:]]+} $txt { }
40 proc do_setup_rec_test {tn setup sql res} {
42 if {[info exists ::set_main_db_name]} {
43 dbconfig_maindbname_icecube db
46 uplevel [list do_rec_test $tn $sql $res]
51 if {![file executable $CMD]} { continue }
53 proc do_rec_test {tn sql res} {
54 set res [squish [string trim $res]]
55 set tst [subst -nocommands {
56 squish [string trim [exec $::CMD -verbose 0 -sql {$sql;} test.db]]
58 uplevel [list do_test $tn $tst $res]
62 if {[info commands sqlite3_expert_new]==""} { continue }
64 proc do_rec_test {tn sql res} {
65 set expert [sqlite3_expert_new db]
70 for {set i 0} {$i < [$expert count]} {incr i} {
71 set idx [string trim [$expert report $i indexes]]
72 if {$idx==""} {set idx "(no new indexes)"}
74 lappend result [string trim [$expert report $i plan]]
79 set tst [subst -nocommands {set {} [squish [join {$result}]]}]
80 uplevel [list do_test $tn $tst [string trim [squish $res]]]
84 if {[info commands sqlite3_expert_new]==""} { continue }
85 set ::set_main_db_name 1
88 if {![file executable $CLI]} { continue }
90 proc do_rec_test {tn sql res} {
91 set res [squish [string trim $res]]
92 set tst [subst -nocommands {
93 squish [string trim [exec $::CLI test.db ".expert" {$sql;}]]
95 uplevel [list do_test $tn $tst $res]
103 do_setup_rec_test $tn.1 { CREATE TABLE t1(a, b, c) } {
110 do_setup_rec_test $tn.2 {
111 CREATE TABLE t1(a, b, c);
113 SELECT * FROM t1 WHERE b>?;
115 CREATE INDEX t1_idx_00000062 ON t1(b);
116 SEARCH t1 USING INDEX t1_idx_00000062 (b>?)
119 do_setup_rec_test $tn.3 {
120 CREATE TABLE t1(a, b, c);
122 SELECT * FROM t1 WHERE b COLLATE nocase BETWEEN ? AND ?
124 CREATE INDEX t1_idx_3e094c27 ON t1(b COLLATE NOCASE);
125 SEARCH t1 USING INDEX t1_idx_3e094c27 (b>? AND b<?)
128 do_setup_rec_test $tn.4 {
129 CREATE TABLE t1(a, b, c);
131 SELECT a FROM t1 ORDER BY b;
133 CREATE INDEX t1_idx_00000062 ON t1(b);
134 SCAN t1 USING INDEX t1_idx_00000062
137 do_setup_rec_test $tn.5 {
138 CREATE TABLE t1(a, b, c);
140 SELECT a FROM t1 WHERE a=? ORDER BY b;
142 CREATE INDEX t1_idx_000123a7 ON t1(a, b);
143 SEARCH t1 USING COVERING INDEX t1_idx_000123a7 (a=?)
147 do_setup_rec_test $tn.6 {
148 CREATE TABLE t1(a, b, c);
150 SELECT min(a) FROM t1
152 CREATE INDEX t1_idx_00000061 ON t1(a);
153 SEARCH t1 USING COVERING INDEX t1_idx_00000061
157 do_setup_rec_test $tn.7 {
158 CREATE TABLE t1(a, b, c);
160 SELECT * FROM t1 ORDER BY a, b, c;
162 CREATE INDEX t1_idx_033e95fe ON t1(a, b, c);
163 SCAN t1 USING COVERING INDEX t1_idx_033e95fe
166 #do_setup_rec_test $tn.1.8 {
167 # CREATE TABLE t1(a, b, c);
169 # SELECT * FROM t1 ORDER BY a ASC, b COLLATE nocase DESC, c ASC;
171 # CREATE INDEX t1_idx_5be6e222 ON t1(a, b COLLATE NOCASE DESC, c);
172 # 0|0|0|SCAN t1 USING COVERING INDEX t1_idx_5be6e222
175 do_setup_rec_test $tn.8.1 {
176 CREATE TABLE t1(a COLLATE NOCase, b, c);
178 SELECT * FROM t1 WHERE a=?
180 CREATE INDEX t1_idx_00000061 ON t1(a);
181 SEARCH t1 USING INDEX t1_idx_00000061 (a=?)
183 do_setup_rec_test $tn.8.2 {
184 CREATE TABLE t1(a, b COLLATE nocase, c);
186 SELECT * FROM t1 ORDER BY a ASC, b DESC, c ASC;
188 CREATE INDEX t1_idx_5cb97285 ON t1(a, b DESC, c);
189 SCAN t1 USING COVERING INDEX t1_idx_5cb97285
193 # Tables with names that require quotes.
195 do_setup_rec_test $tn.9.1 {
196 CREATE TABLE "t t"(a, b, c);
198 SELECT * FROM "t t" WHERE a=?
200 CREATE INDEX "t t_idx_00000061" ON "t t"(a);
201 SEARCH t t USING INDEX t t_idx_00000061 (a=?)
204 do_setup_rec_test $tn.9.2 {
205 CREATE TABLE "t t"(a, b, c);
207 SELECT * FROM "t t" WHERE b BETWEEN ? AND ?
209 CREATE INDEX "t t_idx_00000062" ON "t t"(b);
210 SEARCH t t USING INDEX t t_idx_00000062 (b>? AND b<?)
213 # Columns with names that require quotes.
215 do_setup_rec_test $tn.10.1 {
216 CREATE TABLE t3(a, "b b", c);
218 SELECT * FROM t3 WHERE "b b" = ?
220 CREATE INDEX t3_idx_00050c52 ON t3('b b');
221 SEARCH t3 USING INDEX t3_idx_00050c52 (b b=?)
224 do_setup_rec_test $tn.10.2 {
225 CREATE TABLE t3(a, "b b", c);
227 SELECT * FROM t3 ORDER BY "b b"
229 CREATE INDEX t3_idx_00050c52 ON t3('b b');
230 SCAN t3 USING INDEX t3_idx_00050c52
233 # Transitive constraints
235 do_setup_rec_test $tn.11.1 {
236 CREATE TABLE t5(a, b);
237 CREATE TABLE t6(c, d);
239 SELECT * FROM t5, t6 WHERE a=? AND b=c AND c=?
241 CREATE INDEX t5_idx_000123a7 ON t5(a, b);
242 CREATE INDEX t6_idx_00000063 ON t6(c);
243 SEARCH t6 USING INDEX t6_idx_00000063 (c=?)
244 SEARCH t5 USING COVERING INDEX t5_idx_000123a7 (a=? AND b=?)
249 do_setup_rec_test $tn.12.1 {
250 CREATE TABLE t7(a, b);
252 SELECT * FROM t7 WHERE a=? OR b=?
254 CREATE INDEX t7_idx_00000062 ON t7(b);
255 CREATE INDEX t7_idx_00000061 ON t7(a);
258 SEARCH t7 USING INDEX t7_idx_00000061 (a=?)
260 SEARCH t7 USING INDEX t7_idx_00000062 (b=?)
265 do_setup_rec_test $tn.13.1 {
266 CREATE TABLE t8(a, b);
268 SELECT * FROM t8 WHERE rowid=?
271 SEARCH t8 USING INTEGER PRIMARY KEY (rowid=?)
273 do_setup_rec_test $tn.13.2 {
274 CREATE TABLE t8(a, b);
276 SELECT * FROM t8 ORDER BY rowid
281 do_setup_rec_test $tn.13.3 {
282 CREATE TABLE t8(a, b);
284 SELECT * FROM t8 WHERE a=? ORDER BY rowid
286 CREATE INDEX t8_idx_00000061 ON t8(a);
287 SEARCH t8 USING INDEX t8_idx_00000061 (a=?)
292 do_setup_rec_test $tn.14 {
293 CREATE TABLE t9(a, b, c);
294 CREATE TABLE t10(a, b, c);
295 CREATE TRIGGER t9t AFTER INSERT ON t9 BEGIN
296 UPDATE t10 SET a=new.a WHERE b = new.b;
299 INSERT INTO t9 VALUES(?, ?, ?);
301 CREATE INDEX t10_idx_00000062 ON t10(b);
302 SEARCH t10 USING INDEX t10_idx_00000062 (b=?)
305 do_setup_rec_test $tn.15 {
306 CREATE TABLE t1(a, b);
307 CREATE TABLE t2(c, d);
309 WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100)
310 INSERT INTO t1 SELECT (i-1)/50, (i-1)/20 FROM s;
312 WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100)
313 INSERT INTO t2 SELECT (i-1)/20, (i-1)/5 FROM s;
315 SELECT * FROM t2, t1 WHERE b=? AND d=? AND t2.rowid=t1.rowid
317 CREATE INDEX t2_idx_00000064 ON t2(d);
318 SEARCH t2 USING INDEX t2_idx_00000064 (d=?)
319 SEARCH t1 USING INTEGER PRIMARY KEY (rowid=?)
322 do_setup_rec_test $tn.16 {
323 CREATE TABLE t1(a, b);
325 SELECT * FROM t1 WHERE b IS NOT NULL;
331 do_setup_rec_test $tn.17.1 {
332 CREATE TABLE example (A INTEGER, B INTEGER, C INTEGER, PRIMARY KEY (A,B));
334 SELECT * FROM example WHERE a=?
337 SEARCH example USING INDEX sqlite_autoindex_example_1 (A=?)
339 do_setup_rec_test $tn.17.2 {
340 CREATE TABLE example (A INTEGER, B INTEGER, C INTEGER, PRIMARY KEY (A,B));
342 SELECT * FROM example WHERE b=?
344 CREATE INDEX example_idx_00000042 ON example(B);
345 SEARCH example USING INDEX example_idx_00000042 (B=?)
347 do_setup_rec_test $tn.17.3 {
348 CREATE TABLE example (A INTEGER, B INTEGER, C INTEGER, PRIMARY KEY (A,B));
350 SELECT * FROM example WHERE a=? AND b=?
353 SEARCH example USING INDEX sqlite_autoindex_example_1 (A=? AND B=?)
355 do_setup_rec_test $tn.17.4 {
356 CREATE TABLE example (A INTEGER, B INTEGER, C INTEGER, PRIMARY KEY (A,B));
358 SELECT * FROM example WHERE a=? AND b>?
361 SEARCH example USING INDEX sqlite_autoindex_example_1 (A=? AND B>?)
363 do_setup_rec_test $tn.17.5 {
364 CREATE TABLE example (A INTEGER, B INTEGER, C INTEGER, PRIMARY KEY (A,B));
366 SELECT * FROM example WHERE a>? AND b=?
368 CREATE INDEX example_idx_0000cb3f ON example(B, A);
369 SEARCH example USING INDEX example_idx_0000cb3f (B=? AND A>?)
372 do_setup_rec_test $tn.18.0 {
373 CREATE TABLE SomeObject (
374 a INTEGER PRIMARY KEY,
375 x TEXT GENERATED ALWAYS AS(HEX(a)) VIRTUAL
378 SELECT x FROM SomeObject;
383 do_setup_rec_test $tn.18.1 {
384 CREATE TABLE SomeObject (
385 a INTEGER PRIMARY KEY,
386 x TEXT GENERATED ALWAYS AS(HEX(a)) VIRTUAL
389 SELECT * FROM SomeObject WHERE x=?;
391 CREATE INDEX SomeObject_idx_00000078 ON SomeObject(x);
392 SEARCH SomeObject USING COVERING INDEX SomeObject_idx_00000078 (x=?)
396 do_setup_rec_test $tn.19.0 {
397 CREATE TABLE t1("index");
399 SELECT * FROM t1 ORDER BY "index";
401 CREATE INDEX t1_idx_01a7214e ON t1('index');
402 SCAN t1 USING COVERING INDEX t1_idx_01a7214e
407 proc do_candidates_test {tn sql res} {
408 set res [squish [string trim $res]]
410 set expert [sqlite3_expert_new db]
414 set candidates [squish [string trim [$expert report 0 candidates]]]
417 uplevel [list do_test $tn [list set {} $candidates] $res]
422 do_execsql_test 5.0 {
423 CREATE TABLE t1(a, b);
424 CREATE TABLE t2(c, d);
426 WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100)
427 INSERT INTO t1 SELECT (i-1)/50, (i-1)/20 FROM s;
429 WITH s(i) AS ( VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100)
430 INSERT INTO t2 SELECT (i-1)/20, (i-1)/5 FROM s;
432 do_candidates_test 5.1 {
433 SELECT * FROM t1,t2 WHERE (b=? OR a=?) AND (c=? OR d=?)
435 CREATE INDEX t1_idx_00000062 ON t1(b); -- stat1: 100 20
436 CREATE INDEX t1_idx_00000061 ON t1(a); -- stat1: 100 50
437 CREATE INDEX t2_idx_00000063 ON t2(c); -- stat1: 100 20
438 CREATE INDEX t2_idx_00000064 ON t2(d); -- stat1: 100 5
441 do_candidates_test 5.2 {
442 SELECT * FROM t1,t2 WHERE a=? AND b=? AND c=? AND d=?
444 CREATE INDEX t1_idx_000123a7 ON t1(a, b); -- stat1: 100 50 17
445 CREATE INDEX t2_idx_0001295b ON t2(c, d); -- stat1: 100 20 5
448 do_execsql_test 5.3 {
449 CREATE INDEX t1_idx_00000061 ON t1(a); -- stat1: 100 50
450 CREATE INDEX t1_idx_00000062 ON t1(b); -- stat1: 100 20
451 CREATE INDEX t1_idx_000123a7 ON t1(a, b); -- stat1: 100 50 16
453 CREATE INDEX t2_idx_00000063 ON t2(c); -- stat1: 100 20
454 CREATE INDEX t2_idx_00000064 ON t2(d); -- stat1: 100 5
455 CREATE INDEX t2_idx_0001295b ON t2(c, d); -- stat1: 100 20 5
458 SELECT * FROM sqlite_stat1 ORDER BY 1, 2;
460 t1 t1_idx_00000061 {100 50}
461 t1 t1_idx_00000062 {100 20}
462 t1 t1_idx_000123a7 {100 50 17}
463 t2 t2_idx_00000063 {100 20}
464 t2 t2_idx_00000064 {100 5}
465 t2 t2_idx_0001295b {100 20 5}
469 do_test expert1-6.0 {
474 select name from pragma_collation_list order by name collate uint;
476 } {0 {(no new indexes)
482 SCAN pragma_collation_list VIRTUAL TABLE INDEX 0:
483 USE TEMP B-TREE FOR ORDER BY