Test case for the assert() problem fixed by [9ba3cff9d48fe8fc].
[sqlite.git] / ext / expert / expert1.test
blobc456c30c524784b8c259c18796a704da1c3be2f6
1 # 2009 Nov 11
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 #***********************************************************************
11 # TESTRUNNER: shell
13 # The focus of this file is testing the CLI shell tool. Specifically,
14 # the ".recommend" command.
18 # Test plan:
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]==""} {
28   finish_test
29   return
33 set CLI [test_binary_name sqlite3]
34 set CMD [test_binary_name sqlite3_expert]
36 proc squish {txt} {
37   regsub -all {[[:space:]]+} $txt { }
40 proc do_setup_rec_test {tn setup sql res} {
41   reset_db
42   if {[info exists ::set_main_db_name]} {
43     dbconfig_maindbname_icecube db
44   }
45   db eval $setup
46   uplevel [list do_rec_test $tn $sql $res]
49 foreach {tn setup} {
50   1 {
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]]
57       }]
58       uplevel [list do_test $tn $tst $res]
59     }
60   }
61   2 {
62     if {[info commands sqlite3_expert_new]==""} { continue }
64     proc do_rec_test {tn sql res} {
65       set expert [sqlite3_expert_new db]
66       $expert sql $sql
67       $expert analyze
69       set result [list]
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)"}
73         lappend result $idx
74         lappend result [string trim [$expert report $i plan]]
75       }
77       $expert destroy
79       set tst [subst -nocommands {set {} [squish [join {$result}]]}]
80       uplevel [list do_test $tn $tst [string trim [squish $res]]]
81     }
82   }
83   3 {
84     if {[info commands sqlite3_expert_new]==""} { continue }
85     set ::set_main_db_name 1
86   }
87   4 {
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;}]]
94       }]
95       uplevel [list do_test $tn $tst $res]
96     }
97   }
98 } {
100   eval $setup
103 do_setup_rec_test $tn.1 { CREATE TABLE t1(a, b, c) } {
104   SELECT * FROM t1
105 } {
106   (no new indexes)
107   SCAN t1
110 do_setup_rec_test $tn.2 {
111   CREATE TABLE t1(a, b, c);
112 } {
113   SELECT * FROM t1 WHERE b>?;
114 } {
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);
121 } {
122   SELECT * FROM t1 WHERE b COLLATE nocase BETWEEN ? AND ?
123 } {
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);
130 } {
131   SELECT a FROM t1 ORDER BY b;
132 } {
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);
139 } {
140   SELECT a FROM t1 WHERE a=? ORDER BY b;
141 } {
142   CREATE INDEX t1_idx_000123a7 ON t1(a, b);
143   SEARCH t1 USING COVERING INDEX t1_idx_000123a7 (a=?)
146 if 0 {
147 do_setup_rec_test $tn.6 {
148   CREATE TABLE t1(a, b, c);
149 } {
150   SELECT min(a) FROM t1
151 } {
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);
159 } {
160   SELECT * FROM t1 ORDER BY a, b, c;
161 } {
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);
168 #} {
169 #  SELECT * FROM t1 ORDER BY a ASC, b COLLATE nocase DESC, c ASC;
170 #} {
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);
177 } {
178   SELECT * FROM t1 WHERE a=?
179 } {
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);
185 } {
186   SELECT * FROM t1 ORDER BY a ASC, b DESC, c ASC;
187 } {
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);
197 } {
198   SELECT * FROM "t t" WHERE a=?
199 } {
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);
206 } {
207   SELECT * FROM "t t" WHERE b BETWEEN ? AND ?
208 } {
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);
217 } {
218   SELECT * FROM t3 WHERE "b b" = ?
219 } {
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);
226 } {
227   SELECT * FROM t3 ORDER BY "b b"
228 } {
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);
238 } {
239   SELECT * FROM t5, t6 WHERE a=? AND b=c AND c=?
240 } {
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=?)
247 # OR terms.
249 do_setup_rec_test $tn.12.1 {
250   CREATE TABLE t7(a, b);
251 } {
252   SELECT * FROM t7 WHERE a=? OR b=?
253 } {
254   CREATE INDEX t7_idx_00000062 ON t7(b);
255   CREATE INDEX t7_idx_00000061 ON t7(a);
256   MULTI-INDEX OR
257     INDEX 1
258       SEARCH t7 USING INDEX t7_idx_00000061 (a=?) 
259     INDEX 2
260       SEARCH t7 USING INDEX t7_idx_00000062 (b=?)
263 # rowid terms.
265 do_setup_rec_test $tn.13.1 {
266   CREATE TABLE t8(a, b);
267 } {
268   SELECT * FROM t8 WHERE rowid=?
269 } {
270   (no new indexes)
271   SEARCH t8 USING INTEGER PRIMARY KEY (rowid=?)
273 do_setup_rec_test $tn.13.2 {
274   CREATE TABLE t8(a, b);
275 } {
276   SELECT * FROM t8 ORDER BY rowid
277 } {
278   (no new indexes)
279   SCAN t8
281 do_setup_rec_test $tn.13.3 {
282   CREATE TABLE t8(a, b);
283 } {
284   SELECT * FROM t8 WHERE a=? ORDER BY rowid
285 } {
286   CREATE INDEX t8_idx_00000061 ON t8(a); 
287   SEARCH t8 USING INDEX t8_idx_00000061 (a=?)
290 # Triggers
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;
297   END;
298 } {
299   INSERT INTO t9 VALUES(?, ?, ?);
300 } {
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;
314 } {
315   SELECT * FROM t2, t1 WHERE b=? AND d=? AND t2.rowid=t1.rowid
316 } {
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);
324 } {
325   SELECT * FROM t1 WHERE b IS NOT NULL;
326 } {
327   (no new indexes)
328   SCAN t1
331 do_setup_rec_test $tn.17.1 {
332   CREATE TABLE example (A INTEGER, B INTEGER, C INTEGER, PRIMARY KEY (A,B));
333 } {
334   SELECT * FROM example WHERE a=?
335 } {
336   (no new indexes)
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));
341 } {
342   SELECT * FROM example WHERE b=?
343 } {
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));
349 } {
350   SELECT * FROM example WHERE a=? AND b=?
351 } {
352   (no new indexes)
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));
357 } {
358   SELECT * FROM example WHERE a=? AND b>?
359 } {
360   (no new indexes)
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));
365 } {
366   SELECT * FROM example WHERE a>? AND b=?
367 } {
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
376   );
377 } {
378   SELECT x FROM SomeObject;
379 } {
380   (no new indexes)
381   SCAN 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
387   );
388 } {
389   SELECT * FROM SomeObject WHERE x=?;
390 } {
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");
398 } {
399   SELECT * FROM t1 ORDER BY "index";
400 } {
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]
411   $expert sql $sql
412   $expert analyze
414   set candidates [squish [string trim [$expert report 0 candidates]]]
415   $expert destroy
417   uplevel [list do_test $tn [list set {} $candidates] $res]
421 reset_db
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=?)
434 } {
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=?
443 } {
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
457   ANALYZE;
458   SELECT * FROM sqlite_stat1 ORDER BY 1, 2;
459 } {
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}
468 if 0 {
469 do_test expert1-6.0 {
470   catchcmd :memory: {
471 .expert
472 select base64('');
473 .expert
474 select name from pragma_collation_list order by name collate uint;
476 } {0 {(no new indexes)
478 SCAN CONSTANT ROW
480 (no new indexes)
482 SCAN pragma_collation_list VIRTUAL TABLE INDEX 0:
483 USE TEMP B-TREE FOR ORDER BY
487 finish_test