Merge Chromium + Blink git repositories
[chromium-blink-merge.git] / third_party / sqlite / sqlite-src-3080704 / test / analyze9.test
blob8572cbea0044a58f3bcb131c58c49f61f4b38562
1 # 2013 August 3
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 #***********************************************************************
12 # This file contains automated tests used to verify that the sqlite_stat4
13 # functionality is working.
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
18 set testprefix analyze9
20 ifcapable !stat4 {
21   finish_test
22   return
25 proc s {blob} {
26   set ret ""
27   binary scan $blob c* bytes
28   foreach b $bytes {
29     set t [binary format c $b]
30     if {[string is print $t]} {
31       append ret $t
32     } else {
33       append ret .
34     }
35   }
36   return $ret
38 db function s s
40 do_execsql_test 1.0 {
41   CREATE TABLE t1(a TEXT, b TEXT); 
42   INSERT INTO t1 VALUES('(0)', '(0)');
43   INSERT INTO t1 VALUES('(1)', '(1)');
44   INSERT INTO t1 VALUES('(2)', '(2)');
45   INSERT INTO t1 VALUES('(3)', '(3)');
46   INSERT INTO t1 VALUES('(4)', '(4)');
47   CREATE INDEX i1 ON t1(a, b);
48 } {}
51 do_execsql_test 1.1 {
52   ANALYZE;
53 } {}
55 do_execsql_test 1.2 {
56   SELECT tbl,idx,nEq,nLt,nDLt,test_decode(sample) FROM sqlite_stat4;
57 } {
58   t1 i1 {1 1 1} {0 0 0} {0 0 0} {(0) (0) 1}
59   t1 i1 {1 1 1} {1 1 1} {1 1 1} {(1) (1) 2}
60   t1 i1 {1 1 1} {2 2 2} {2 2 2} {(2) (2) 3}
61   t1 i1 {1 1 1} {3 3 3} {3 3 3} {(3) (3) 4}
62   t1 i1 {1 1 1} {4 4 4} {4 4 4} {(4) (4) 5}
65 if {[permutation] != "utf16"} {
66   do_execsql_test 1.3 {
67     SELECT tbl,idx,nEq,nLt,nDLt,s(sample) FROM sqlite_stat4;
68   } {
69     t1 i1 {1 1 1} {0 0 0} {0 0 0} ....(0)(0)
70     t1 i1 {1 1 1} {1 1 1} {1 1 1} ....(1)(1).
71     t1 i1 {1 1 1} {2 2 2} {2 2 2} ....(2)(2).
72     t1 i1 {1 1 1} {3 3 3} {3 3 3} ....(3)(3).
73     t1 i1 {1 1 1} {4 4 4} {4 4 4} ....(4)(4).
74   }
78 #-------------------------------------------------------------------------
79 # This is really just to test SQL user function "test_decode".
81 reset_db
82 do_execsql_test 2.1 {
83   CREATE TABLE t1(a, b, c);
84   INSERT INTO t1 VALUES('some text', 14, NULL);
85   INSERT INTO t1 VALUES(22.0, NULL, x'656667');
86   CREATE INDEX i1 ON t1(a, b, c);
87   ANALYZE;
88   SELECT test_decode(sample) FROM sqlite_stat4;
89 } {
90   {22.0 NULL x'656667' 2} 
91   {{some text} 14 NULL 1}
94 #-------------------------------------------------------------------------
95
96 reset_db
97 do_execsql_test 3.1 {
98   CREATE TABLE t2(a, b);
99   CREATE INDEX i2 ON t2(a, b);
100   BEGIN;
103 do_test 3.2 {
104   for {set i 0} {$i < 1000} {incr i} {
105     set a [expr $i / 10]
106     set b [expr int(rand() * 15.0)]
107     execsql { INSERT INTO t2 VALUES($a, $b) }
108   }
109   execsql COMMIT
110 } {}
112 db func lindex lindex
114 # Each value of "a" occurs exactly 10 times in the table.
116 do_execsql_test 3.3.1 {
117   SELECT count(*) FROM t2 GROUP BY a;
118 } [lrange [string repeat "10 " 100] 0 99]
120 # The first element in the "nEq" list of all samples should therefore be 10.
122 do_execsql_test 3.3.2 {
123   ANALYZE;
124   SELECT lindex(nEq, 0) FROM sqlite_stat4;
125 } [lrange [string repeat "10 " 100] 0 23]
127 #-------------------------------------------------------------------------
129 do_execsql_test 3.4 {
130   DROP TABLE IF EXISTS t1;
131   CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
132   INSERT INTO t1 VALUES(1, 1, 'one-a');
133   INSERT INTO t1 VALUES(11, 1, 'one-b');
134   INSERT INTO t1 VALUES(21, 1, 'one-c');
135   INSERT INTO t1 VALUES(31, 1, 'one-d');
136   INSERT INTO t1 VALUES(41, 1, 'one-e');
137   INSERT INTO t1 VALUES(51, 1, 'one-f');
138   INSERT INTO t1 VALUES(61, 1, 'one-g');
139   INSERT INTO t1 VALUES(71, 1, 'one-h');
140   INSERT INTO t1 VALUES(81, 1, 'one-i');
141   INSERT INTO t1 VALUES(91, 1, 'one-j');
142   INSERT INTO t1 SELECT a+1,2,'two' || substr(c,4) FROM t1;
143   INSERT INTO t1 SELECT a+2,3,'three'||substr(c,4) FROM t1 WHERE c GLOB 'one-*';
144   INSERT INTO t1 SELECT a+3,4,'four'||substr(c,4) FROM t1 WHERE c GLOB 'one-*';
145   INSERT INTO t1 SELECT a+4,5,'five'||substr(c,4) FROM t1 WHERE c GLOB 'one-*';
146   INSERT INTO t1 SELECT a+5,6,'six'||substr(c,4) FROM t1 WHERE c GLOB 'one-*';  
147   CREATE INDEX t1b ON t1(b);
148   ANALYZE;
149   SELECT c FROM t1 WHERE b=3 AND a BETWEEN 30 AND 60;
150 } {three-d three-e three-f}
153 #-------------------------------------------------------------------------
154 # These tests verify that the sample selection for stat4 appears to be 
155 # working as designed.
158 reset_db
159 db func lindex lindex
160 db func lrange lrange
162 do_execsql_test 4.0 {
163   DROP TABLE IF EXISTS t1;
164   CREATE TABLE t1(a, b, c);
165   CREATE INDEX i1 ON t1(c, b, a);
169 proc insert_filler_rows_n {iStart args} {
170   set A(-ncopy) 1
171   set A(-nval) 1
173   foreach {k v} $args {
174     if {[info exists A($k)]==0} { error "no such option: $k" }
175     set A($k) $v
176   }
177   if {[llength $args] % 2} {
178     error "option requires an argument: [lindex $args end]"
179   }
181   for {set i 0} {$i < $A(-nval)} {incr i} {
182     set iVal [expr $iStart+$i]
183     for {set j 0} {$j < $A(-ncopy)} {incr j} {
184       execsql { INSERT INTO t1 VALUES($iVal, $iVal, $iVal) }
185     }
186   }
189 do_test 4.1 {
190   execsql { BEGIN }
191   insert_filler_rows_n  0  -ncopy 10 -nval 19
192   insert_filler_rows_n 20  -ncopy  1 -nval 100
194   execsql {
195     INSERT INTO t1(c, b, a) VALUES(200, 1, 'a');
196     INSERT INTO t1(c, b, a) VALUES(200, 1, 'b');
197     INSERT INTO t1(c, b, a) VALUES(200, 1, 'c');
199     INSERT INTO t1(c, b, a) VALUES(200, 2, 'e');
200     INSERT INTO t1(c, b, a) VALUES(200, 2, 'f');
202     INSERT INTO t1(c, b, a) VALUES(201, 3, 'g');
203     INSERT INTO t1(c, b, a) VALUES(201, 4, 'h');
205     ANALYZE;
206     SELECT count(*) FROM sqlite_stat4;
207     SELECT count(*) FROM t1;
208   }
209 } {24 297}
211 do_execsql_test 4.2 {
212   SELECT 
213     neq,
214     lrange(nlt, 0, 2),
215     lrange(ndlt, 0, 2),
216     lrange(test_decode(sample), 0, 2)
217     FROM sqlite_stat4
218   ORDER BY rowid LIMIT 16;
219 } {
220   {10 10 10 1} {0 0 0} {0 0 0} {0 0 0}
221   {10 10 10 1} {10 10 10} {1 1 1} {1 1 1}
222   {10 10 10 1} {20 20 20} {2 2 2} {2 2 2}
223   {10 10 10 1} {30 30 30} {3 3 3} {3 3 3}
224   {10 10 10 1} {40 40 40} {4 4 4} {4 4 4}
225   {10 10 10 1} {50 50 50} {5 5 5} {5 5 5}
226   {10 10 10 1} {60 60 60} {6 6 6} {6 6 6}
227   {10 10 10 1} {70 70 70} {7 7 7} {7 7 7}
228   {10 10 10 1} {80 80 80} {8 8 8} {8 8 8}
229   {10 10 10 1} {90 90 90} {9 9 9} {9 9 9}
230   {10 10 10 1} {100 100 100} {10 10 10} {10 10 10}
231   {10 10 10 1} {110 110 110} {11 11 11} {11 11 11}
232   {10 10 10 1} {120 120 120} {12 12 12} {12 12 12}
233   {10 10 10 1} {130 130 130} {13 13 13} {13 13 13}
234   {10 10 10 1} {140 140 140} {14 14 14} {14 14 14}
235   {10 10 10 1} {150 150 150} {15 15 15} {15 15 15}
238 do_execsql_test 4.3 {
239   SELECT 
240     neq,
241     lrange(nlt, 0, 2),
242     lrange(ndlt, 0, 2),
243     lrange(test_decode(sample), 0, 1)
244     FROM sqlite_stat4
245   ORDER BY rowid DESC LIMIT 2;
246 } {
247   {2 1 1 1} {295 296 296} {120 122 125} {201 4} 
248   {5 3 1 1} {290 290 290} {119 119 119} {200 1}
251 do_execsql_test 4.4 { SELECT count(DISTINCT c) FROM t1 WHERE c<201 } 120
252 do_execsql_test 4.5 { SELECT count(DISTINCT c) FROM t1 WHERE c<200 } 119
254 # Check that the perioidic samples are present.
255 do_execsql_test 4.6 {
256   SELECT count(*) FROM sqlite_stat4
257   WHERE lindex(test_decode(sample), 3) IN 
258     ('34', '68', '102', '136', '170', '204', '238', '272')
259 } {8}
261 reset_db
262 do_test 4.7 {
263   execsql { 
264     BEGIN;
265     CREATE TABLE t1(o,t INTEGER PRIMARY KEY);
266     CREATE INDEX i1 ON t1(o);
267   }
268   for {set i 0} {$i<10000} {incr i [expr (($i<1000)?1:10)]} {
269     execsql { INSERT INTO t1 VALUES('x', $i) }
270   }
271   execsql {
272     COMMIT;
273     ANALYZE;
274     SELECT count(*) FROM sqlite_stat4;
275   }
276 } {8}
277 do_execsql_test 4.8 {
278   SELECT test_decode(sample) FROM sqlite_stat4;
279 } {
280   {x 211} {x 423} {x 635} {x 847} 
281   {x 1590} {x 3710} {x 5830} {x 7950}
285 #-------------------------------------------------------------------------
286 # The following would cause a crash at one point.
288 reset_db
289 do_execsql_test 5.1 {
290   PRAGMA encoding = 'utf-16';
291   CREATE TABLE t0(v);
292   ANALYZE;
295 #-------------------------------------------------------------------------
296 # This was also crashing (corrupt sqlite_stat4 table).
298 reset_db
299 do_execsql_test 6.1 {
300   CREATE TABLE t1(a, b);
301   CREATE INDEX i1 ON t1(a);
302   CREATE INDEX i2 ON t1(b);
303   INSERT INTO t1 VALUES(1, 1);
304   INSERT INTO t1 VALUES(2, 2);
305   INSERT INTO t1 VALUES(3, 3);
306   INSERT INTO t1 VALUES(4, 4);
307   INSERT INTO t1 VALUES(5, 5);
308   ANALYZE;
309   PRAGMA writable_schema = 1;
310   CREATE TEMP TABLE x1 AS
311     SELECT tbl,idx,neq,nlt,ndlt,sample FROM sqlite_stat4
312     ORDER BY (rowid%5), rowid;
313   DELETE FROM sqlite_stat4;
314   INSERT INTO sqlite_stat4 SELECT * FROM x1;
315   PRAGMA writable_schema = 0;
316   ANALYZE sqlite_master;
318 do_execsql_test 6.2 {
319   SELECT * FROM t1 WHERE a = 'abc';
322 #-------------------------------------------------------------------------
323 # The following tests experiment with adding corrupted records to the
324 # 'sample' column of the sqlite_stat4 table.
326 reset_db
327 sqlite3_db_config_lookaside db 0 0 0
329 database_may_be_corrupt
330 do_execsql_test 7.1 {
331   CREATE TABLE t1(a, b);
332   CREATE INDEX i1 ON t1(a, b);
333   INSERT INTO t1 VALUES(1, 1);
334   INSERT INTO t1 VALUES(2, 2);
335   INSERT INTO t1 VALUES(3, 3);
336   INSERT INTO t1 VALUES(4, 4);
337   INSERT INTO t1 VALUES(5, 5);
338   ANALYZE;
339   UPDATE sqlite_stat4 SET sample = X'' WHERE rowid = 1;
340   ANALYZE sqlite_master;
343 do_execsql_test 7.2 {
344   UPDATE sqlite_stat4 SET sample = X'FFFF';
345   ANALYZE sqlite_master;
346   SELECT * FROM t1 WHERE a = 1;
347 } {1 1}
349 do_execsql_test 7.3 {
350   ANALYZE;
351   UPDATE sqlite_stat4 SET neq = '0 0 0';
352   ANALYZE sqlite_master;
353   SELECT * FROM t1 WHERE a = 1;
354 } {1 1}
356 do_execsql_test 7.4 {
357   ANALYZE;
358   UPDATE sqlite_stat4 SET ndlt = '0 0 0';
359   ANALYZE sqlite_master;
360   SELECT * FROM t1 WHERE a = 3;
361 } {3 3}
363 do_execsql_test 7.5 {
364   ANALYZE;
365   UPDATE sqlite_stat4 SET nlt = '0 0 0';
366   ANALYZE sqlite_master;
367   SELECT * FROM t1 WHERE a = 5;
368 } {5 5}
370 database_never_corrupt
372 #-------------------------------------------------------------------------
374 reset_db
375 do_execsql_test 8.1 {
376   CREATE TABLE t1(x TEXT);
377   CREATE INDEX i1 ON t1(x);
378   INSERT INTO t1 VALUES('1');
379   INSERT INTO t1 VALUES('2');
380   INSERT INTO t1 VALUES('3');
381   INSERT INTO t1 VALUES('4');
382   ANALYZE;
384 do_execsql_test 8.2 {
385   SELECT * FROM t1 WHERE x = 3;
386 } {3}
388 #-------------------------------------------------------------------------
389 # Check that the bug fixed by [91733bc485] really is fixed.
391 reset_db
392 do_execsql_test 9.1 {
393   CREATE TABLE t1(a, b, c, d, e);
394   CREATE INDEX i1 ON t1(a, b, c, d);
395   CREATE INDEX i2 ON t1(e);
397 do_test 9.2 {
398   execsql BEGIN;
399   for {set i 0} {$i < 100} {incr i} {
400     execsql "INSERT INTO t1 VALUES('x', 'y', 'z', $i, [expr $i/2])"
401   }
402   for {set i 0} {$i < 20} {incr i} {
403     execsql "INSERT INTO t1 VALUES('x', 'y', 'z', 101, $i)"
404   }
405   for {set i 102} {$i < 200} {incr i} {
406     execsql "INSERT INTO t1 VALUES('x', 'y', 'z', $i, [expr $i/2])"
407   }
408   execsql COMMIT
409   execsql ANALYZE
410 } {}
412 do_eqp_test 9.3.1 {
413   SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=101 AND e=5;
414 } {/t1 USING INDEX i2/}
415 do_eqp_test 9.3.2 {
416   SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=99 AND e=5;
417 } {/t1 USING INDEX i1/}
419 set value_d [expr 101]
420 do_eqp_test 9.4.1 {
421   SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=$value_d AND e=5
422 } {/t1 USING INDEX i2/}
423 set value_d [expr 99]
424 do_eqp_test 9.4.2 {
425   SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=$value_d AND e=5
426 } {/t1 USING INDEX i1/}
428 #-------------------------------------------------------------------------
429 # Check that the planner takes stat4 data into account when considering
430 # "IS NULL" and "IS NOT NULL" constraints.
432 do_execsql_test 10.1.1 {
433   DROP TABLE IF EXISTS t3;
434   CREATE TABLE t3(a, b);
435   CREATE INDEX t3a ON t3(a);
436   CREATE INDEX t3b ON t3(b);
438 do_test 10.1.2 {
439   for {set i 1} {$i < 100} {incr i} {
440     if {$i>90} { set a $i } else { set a NULL }
441     set b [expr $i % 5]
442     execsql "INSERT INTO t3 VALUES($a, $b)"
443   }
444   execsql ANALYZE
445 } {}
446 do_eqp_test 10.1.3 {
447   SELECT * FROM t3 WHERE a IS NULL AND b = 2
448 } {/t3 USING INDEX t3b/}
449 do_eqp_test 10.1.4 {
450   SELECT * FROM t3 WHERE a IS NOT NULL AND b = 2
451 } {/t3 USING INDEX t3a/}
453 do_execsql_test 10.2.1 {
454   DROP TABLE IF EXISTS t3;
455   CREATE TABLE t3(x, a, b);
456   CREATE INDEX t3a ON t3(x, a);
457   CREATE INDEX t3b ON t3(x, b);
459 do_test 10.2.2 {
460   for {set i 1} {$i < 100} {incr i} {
461     if {$i>90} { set a $i } else { set a NULL }
462     set b [expr $i % 5]
463     execsql "INSERT INTO t3 VALUES('xyz', $a, $b)"
464   }
465   execsql ANALYZE
466 } {}
467 do_eqp_test 10.2.3 {
468   SELECT * FROM t3 WHERE x = 'xyz' AND a IS NULL AND b = 2
469 } {/t3 USING INDEX t3b/}
470 do_eqp_test 10.2.4 {
471   SELECT * FROM t3 WHERE x = 'xyz' AND a IS NOT NULL AND b = 2
472 } {/t3 USING INDEX t3a/}
474 #-------------------------------------------------------------------------
475 # Check that stat4 data is used correctly with non-default collation
476 # sequences.
478 foreach {tn schema} {
479   1 {
480     CREATE TABLE t4(a COLLATE nocase, b);
481     CREATE INDEX t4a ON t4(a);
482     CREATE INDEX t4b ON t4(b);
483   }
484   2 {
485     CREATE TABLE t4(a, b);
486     CREATE INDEX t4a ON t4(a COLLATE nocase);
487     CREATE INDEX t4b ON t4(b);
488   }
489 } {
490   drop_all_tables
491   do_test 11.$tn.1 { execsql $schema } {}
493   do_test 11.$tn.2 {
494     for {set i 0} {$i < 100} {incr i} {
495       if { ($i % 10)==0 } { set a ABC } else { set a DEF }
496       set b [expr $i % 5]
497         execsql { INSERT INTO t4 VALUES($a, $b) }
498     }
499     execsql ANALYZE
500   } {}
502   do_eqp_test 11.$tn.3 {
503     SELECT * FROM t4 WHERE a = 'def' AND b = 3;
504   } {/t4 USING INDEX t4b/}
506   if {$tn==1} {
507     set sql "SELECT * FROM t4 WHERE a = 'abc' AND b = 3;"
508     do_eqp_test 11.$tn.4 $sql {/t4 USING INDEX t4a/}
509   } else {
511     set sql "SELECT * FROM t4 WHERE a = 'abc' COLLATE nocase AND b = 3;"
512     do_eqp_test 11.$tn.5 $sql {/t4 USING INDEX t4a/}
514     set sql "SELECT * FROM t4 WHERE a COLLATE nocase = 'abc' AND b = 3;"
515     do_eqp_test 11.$tn.6 $sql {/t4 USING INDEX t4a/}
516   }
519 foreach {tn schema} {
520   1 {
521     CREATE TABLE t4(x, a COLLATE nocase, b);
522     CREATE INDEX t4a ON t4(x, a);
523     CREATE INDEX t4b ON t4(x, b);
524   }
525   2 {
526     CREATE TABLE t4(x, a, b);
527     CREATE INDEX t4a ON t4(x, a COLLATE nocase);
528     CREATE INDEX t4b ON t4(x, b);
529   }
530 } {
531   drop_all_tables
532   do_test 12.$tn.1 { execsql $schema } {}
534   do_test 12.$tn.2 {
535     for {set i 0} {$i < 100} {incr i} {
536       if { ($i % 10)==0 } { set a ABC } else { set a DEF }
537       set b [expr $i % 5]
538         execsql { INSERT INTO t4 VALUES(X'abcdef', $a, $b) }
539     }
540     execsql ANALYZE
541   } {}
543   do_eqp_test 12.$tn.3 {
544     SELECT * FROM t4 WHERE x=X'abcdef' AND a = 'def' AND b = 3;
545   } {/t4 USING INDEX t4b/}
547   if {$tn==1} {
548     set sql "SELECT * FROM t4 WHERE x=X'abcdef' AND a = 'abc' AND b = 3;"
549     do_eqp_test 12.$tn.4 $sql {/t4 USING INDEX t4a/}
550   } else {
551     set sql {
552       SELECT * FROM t4 WHERE x=X'abcdef' AND a = 'abc' COLLATE nocase AND b = 3
553     }
554     do_eqp_test 12.$tn.5 $sql {/t4 USING INDEX t4a/}
555     set sql {
556       SELECT * FROM t4 WHERE x=X'abcdef' AND a COLLATE nocase = 'abc' AND b = 3
557     }
558     do_eqp_test 12.$tn.6 $sql {/t4 USING INDEX t4a/}
559   }
562 #-------------------------------------------------------------------------
563 # Check that affinities are taken into account when using stat4 data to
564 # estimate the number of rows scanned by a rowid constraint.
566 drop_all_tables
567 do_test 13.1 {
568   execsql {
569     CREATE TABLE t1(a, b, c, d);
570     CREATE INDEX i1 ON t1(a);
571     CREATE INDEX i2 ON t1(b, c);
572   }
573   for {set i 0} {$i<100} {incr i} {
574     if {$i %2} {set a abc} else {set a def}
575     execsql { INSERT INTO t1(rowid, a, b, c) VALUES($i, $a, $i, $i) }
576   }
577   execsql ANALYZE
578 } {}
579 do_eqp_test 13.2.1 {
580   SELECT * FROM t1 WHERE a='abc' AND rowid<15 AND b<12
581 } {/SEARCH TABLE t1 USING INDEX i1/}
582 do_eqp_test 13.2.2 {
583   SELECT * FROM t1 WHERE a='abc' AND rowid<'15' AND b<12
584 } {/SEARCH TABLE t1 USING INDEX i1/}
585 do_eqp_test 13.3.1 {
586   SELECT * FROM t1 WHERE a='abc' AND rowid<100 AND b<12
587 } {/SEARCH TABLE t1 USING INDEX i2/}
588 do_eqp_test 13.3.2 {
589   SELECT * FROM t1 WHERE a='abc' AND rowid<'100' AND b<12
590 } {/SEARCH TABLE t1 USING INDEX i2/}
592 #-------------------------------------------------------------------------
593 # Check also that affinities are taken into account when using stat4 data 
594 # to estimate the number of rows scanned by any other constraint on a 
595 # column other than the leftmost.
597 drop_all_tables
598 do_test 14.1 {
599   execsql { CREATE TABLE t1(a, b INTEGER, c) }
600   for {set i 0} {$i<100} {incr i} {
601     set c [expr $i % 3]
602     execsql { INSERT INTO t1 VALUES('ott', $i, $c) }
603   }
604   execsql {
605     CREATE INDEX i1 ON t1(a, b);
606     CREATE INDEX i2 ON t1(c);
607     ANALYZE;
608   }
609 } {}
610 do_eqp_test 13.2.1 {
611   SELECT * FROM t1 WHERE a='ott' AND b<10 AND c=1
612 } {/SEARCH TABLE t1 USING INDEX i1/}
613 do_eqp_test 13.2.2 {
614   SELECT * FROM t1 WHERE a='ott' AND b<'10' AND c=1
615 } {/SEARCH TABLE t1 USING INDEX i1/}
617 #-------------------------------------------------------------------------
618 # By default, 16 non-periodic samples are collected for the stat4 table.
619 # The following tests attempt to verify that the most common keys are
620 # being collected.
622 proc check_stat4 {tn} {
623   db eval ANALYZE
624   db eval {SELECT a, b, c, d FROM t1} {
625     incr k($a)
626     incr k([list $a $b])
627     incr k([list $a $b $c])
628     if { [info exists k([list $a $b $c $d])]==0 } { incr nRow }
629     incr k([list $a $b $c $d])
630   }
632   set L [list]
633   foreach key [array names k] {
634     lappend L [list $k($key) $key]
635   }
637   set nSample $nRow
638   if {$nSample>16} {set nSample 16}
640   set nThreshold [lindex [lsort -decr -integer -index 0 $L] [expr $nSample-1] 0]
641   foreach key [array names k] {
642     if {$k($key)>$nThreshold} {
643       set expect($key) 1
644     }
645     if {$k($key)==$nThreshold} {
646       set possible($key) 1
647     }
648   }
651   set nPossible [expr $nSample - [llength [array names expect]]]
653   #puts "EXPECT: [array names expect]"
654   #puts "POSSIBLE($nPossible/[array size possible]): [array names possible]"
655   #puts "HAVE: [db eval {SELECT test_decode(sample) FROM sqlite_stat4 WHERE idx='i1'}]"
657   db eval {SELECT test_decode(sample) AS s FROM sqlite_stat4 WHERE idx='i1'} {
658     set seen 0
659     for {set i 0} {$i<4} {incr i} {
660       unset -nocomplain expect([lrange $s 0 $i])
661       if {[info exists possible([lrange $s 0 $i])]} {
662         set seen 1
663         unset -nocomplain possible([lrange $s 0 $i])
664       }
665     }
666     if {$seen} {incr nPossible -1}
667   }
668   if {$nPossible<0} {set nPossible 0}
670   set res [list [llength [array names expect]] $nPossible]
671   uplevel [list do_test $tn [list set {} $res] {0 0}]
674 drop_all_tables
675 do_test 14.1.1 {
676   execsql {
677     CREATE TABLE t1(a,b,c,d);
678     CREATE INDEX i1 ON t1(a,b,c,d);
679   }
680   for {set i 0} {$i < 160} {incr i} {
681     execsql { INSERT INTO t1 VALUES($i,$i,$i,$i) }
682     if {($i % 10)==0} { execsql { INSERT INTO t1 VALUES($i,$i,$i,$i) } }
683   }
684 } {}
685 check_stat4 14.1.2
687 do_test 14.2.1 {
688   execsql { DELETE FROM t1 }
689   for {set i 0} {$i < 1600} {incr i} {
690     execsql { INSERT INTO t1 VALUES($i/10,$i/17,$i/27,$i/37) }
691   }
692 } {}
693 check_stat4 14.2.2
695 do_test 14.3.1 {
696   for {set i 0} {$i < 10} {incr i} {
697     execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
698     execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
699     execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
700     execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
701     execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
702     execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
703     execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
704     execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
705     execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
706     execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) }
707   }
708 } {}
709 check_stat4 14.3.2
711 do_test 14.4.1 {
712   execsql {DELETE FROM t1}
713   for {set i 1} {$i < 160} {incr i} {
714     set b [expr $i % 10]
715     if {$b==0 || $b==2} {set b 1}
716     execsql { INSERT INTO t1 VALUES($i/10,$b,$i,$i) }
717   }
718 } {}
719 check_stat4 14.4.2
720 db func lrange lrange
721 db func lindex lindex
722 do_execsql_test 14.4.3 {
723   SELECT lrange(test_decode(sample), 0, 1) AS s FROM sqlite_stat4
724   WHERE lindex(s, 1)=='1' ORDER BY rowid
725 } {
726   {0 1} {1 1} {2 1} {3 1} 
727   {4 1} {5 1} {6 1} {7 1} 
728   {8 1} {9 1} {10 1} {11 1} 
729   {12 1} {13 1} {14 1} {15 1}
732 #-------------------------------------------------------------------------
733 # Test that nothing untoward happens if the stat4 table contains entries
734 # for indexes that do not exist. Or NULL values in the idx column.
735 # Or NULL values in any of the other columns.
737 drop_all_tables
738 do_execsql_test 15.1 {
739   CREATE TABLE x1(a, b, UNIQUE(a, b));
740   INSERT INTO x1 VALUES(1, 2);
741   INSERT INTO x1 VALUES(3, 4);
742   INSERT INTO x1 VALUES(5, 6);
743   ANALYZE;
744   INSERT INTO sqlite_stat4 VALUES(NULL, NULL, NULL, NULL, NULL, NULL);
746 db close
747 sqlite3 db test.db
748 do_execsql_test 15.2 { SELECT * FROM x1 } {1 2 3 4 5 6}
750 do_execsql_test 15.3 {
751   INSERT INTO sqlite_stat4 VALUES(42, 42, 42, 42, 42, 42);
753 db close
754 sqlite3 db test.db
755 do_execsql_test 15.4 { SELECT * FROM x1 } {1 2 3 4 5 6}
757 do_execsql_test 15.5 {
758   UPDATE sqlite_stat1 SET stat = NULL;
760 db close
761 sqlite3 db test.db
762 do_execsql_test 15.6 { SELECT * FROM x1 } {1 2 3 4 5 6}
764 do_execsql_test 15.7 {
765   ANALYZE;
766   UPDATE sqlite_stat1 SET tbl = 'no such tbl';
768 db close
769 sqlite3 db test.db
770 do_execsql_test 15.8 { SELECT * FROM x1 } {1 2 3 4 5 6}
772 do_execsql_test 15.9 {
773   ANALYZE;
774   UPDATE sqlite_stat4 SET neq = NULL, nlt=NULL, ndlt=NULL;
776 db close
777 sqlite3 db test.db
778 do_execsql_test 15.10 { SELECT * FROM x1 } {1 2 3 4 5 6}
780 # This is just for coverage....
781 do_execsql_test 15.11 {
782   ANALYZE;
783   UPDATE sqlite_stat1 SET stat = stat || ' unordered';
785 db close
786 sqlite3 db test.db
787 do_execsql_test 15.12 { SELECT * FROM x1 } {1 2 3 4 5 6}
789 #-------------------------------------------------------------------------
790 # Test that allocations used for sqlite_stat4 samples are included in
791 # the quantity returned by SQLITE_DBSTATUS_SCHEMA_USED.
793 set one [string repeat x 1000]
794 set two [string repeat x 2000]
795 do_test 16.1 {
796   reset_db
797   execsql {
798     CREATE TABLE t1(a, UNIQUE(a));
799     INSERT INTO t1 VALUES($one);
800     ANALYZE;
801   }
802   set nByte [lindex [sqlite3_db_status db SCHEMA_USED 0] 1]
804   reset_db
805   execsql {
806     CREATE TABLE t1(a, UNIQUE(a));
807     INSERT INTO t1 VALUES($two);
808     ANALYZE;
809   }
810   set nByte2 [lindex [sqlite3_db_status db SCHEMA_USED 0] 1]
811   puts -nonewline " (nByte=$nByte nByte2=$nByte2)"
813   expr {$nByte2 > $nByte+900 && $nByte2 < $nByte+1100}
814 } {1}
816 #-------------------------------------------------------------------------
817 # Test that stat4 data may be used with partial indexes.
819 do_test 17.1 {
820   reset_db
821   execsql {
822     CREATE TABLE t1(a, b, c, d);
823     CREATE INDEX i1 ON t1(a, b) WHERE d IS NOT NULL;
824     INSERT INTO t1 VALUES(-1, -1, -1, NULL);
825     INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1;
826     INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1;
827     INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1;
828     INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1;
829     INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1;
830     INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1;
831   }
833   for {set i 0} {$i < 32} {incr i} {
834     if {$i<8} {set b 0} else { set b $i }
835     execsql { INSERT INTO t1 VALUES($i%2, $b, $i/2, 'abc') }
836   }
837   execsql {ANALYZE main.t1}
838 } {}
840 do_catchsql_test 17.1.2 {
841   ANALYZE temp.t1;
842 } {1 {no such table: temp.t1}}
844 do_eqp_test 17.2 {
845   SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=10 AND c=10;
846 } {/USING INDEX i1/}
847 do_eqp_test 17.3 {
848   SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=0 AND c=10;
849 } {/USING INDEX i1/}
851 do_execsql_test 17.4 {
852   CREATE INDEX i2 ON t1(c, d);
853   ANALYZE main.i2;
855 do_eqp_test 17.5 {
856   SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=10 AND c=10;
857 } {/USING INDEX i1/}
858 do_eqp_test 17.6 {
859   SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=0 AND c=10;
860 } {/USING INDEX i2/}
862 #-------------------------------------------------------------------------
864 do_test 18.1 {
865   reset_db
866   execsql {
867     CREATE TABLE t1(a, b);
868     CREATE INDEX i1 ON t1(a, b);
869   }
870   for {set i 0} {$i < 9} {incr i} {
871     execsql {
872       INSERT INTO t1 VALUES($i, 0);
873       INSERT INTO t1 VALUES($i, 0);
874       INSERT INTO t1 VALUES($i, 0);
875       INSERT INTO t1 VALUES($i, 0);
876       INSERT INTO t1 VALUES($i, 0);
877       INSERT INTO t1 VALUES($i, 0);
878       INSERT INTO t1 VALUES($i, 0);
879       INSERT INTO t1 VALUES($i, 0);
880       INSERT INTO t1 VALUES($i, 0);
881       INSERT INTO t1 VALUES($i, 0);
882       INSERT INTO t1 VALUES($i, 0);
883       INSERT INTO t1 VALUES($i, 0);
884       INSERT INTO t1 VALUES($i, 0);
885       INSERT INTO t1 VALUES($i, 0);
886       INSERT INTO t1 VALUES($i, 0);
887     }
888   }
889   execsql ANALYZE
890   execsql { SELECT count(*) FROM sqlite_stat4 }
891 } {9}
893 #-------------------------------------------------------------------------
894 # For coverage.
896 ifcapable view {
897   do_test 19.1 {
898     reset_db 
899     execsql {
900       CREATE TABLE t1(x, y);
901       CREATE INDEX i1 ON t1(x, y);
902       CREATE VIEW v1 AS SELECT * FROM t1;
903       ANALYZE;
904     }
905   } {}
907 ifcapable auth {
908   proc authproc {op args} {
909     if {$op == "SQLITE_ANALYZE"} { return "SQLITE_DENY" }
910     return "SQLITE_OK"
911   }
912   do_test 19.2 {
913     reset_db 
914     db auth authproc
915     execsql {
916       CREATE TABLE t1(x, y);
917       CREATE VIEW v1 AS SELECT * FROM t1;
918     }
919     catchsql ANALYZE
920   } {1 {not authorized}}
923 #-------------------------------------------------------------------------
925 reset_db
926 proc r {args} { expr rand() }
927 db func r r
928 db func lrange lrange
929 do_test 20.1 {
930   execsql {
931     CREATE TABLE t1(a,b,c,d);
932     CREATE INDEX i1 ON t1(a,b,c,d);
933   }
934   for {set i 0} {$i < 16} {incr i} {
935     execsql {
936       INSERT INTO t1 VALUES($i, r(), r(), r());
937       INSERT INTO t1 VALUES($i, $i,  r(), r());
938       INSERT INTO t1 VALUES($i, $i,  $i,  r());
939       INSERT INTO t1 VALUES($i, $i,  $i,  $i);
940       INSERT INTO t1 VALUES($i, $i,  $i,  $i);
941       INSERT INTO t1 VALUES($i, $i,  $i,  r());
942       INSERT INTO t1 VALUES($i, $i,  r(), r());
943       INSERT INTO t1 VALUES($i, r(), r(), r());
944     }
945   }
946 } {}
947 do_execsql_test 20.2 { ANALYZE }
948 for {set i 0} {$i<16} {incr i} {
949     set val "$i $i $i $i"
950     do_execsql_test 20.3.$i {
951       SELECT count(*) FROM sqlite_stat4 
952       WHERE lrange(test_decode(sample), 0, 3)=$val
953     } {1}
956 #-------------------------------------------------------------------------
958 reset_db
960 do_execsql_test 21.0 {
961   CREATE TABLE t2(a, b);
962   CREATE INDEX i2 ON t2(a);
965 do_test 21.1 {
966   for {set i 1} {$i < 100} {incr i} {
967     execsql { 
968       INSERT INTO t2 VALUES(CASE WHEN $i < 80 THEN 'one' ELSE 'two' END, $i) 
969     }
970   }
971   execsql ANALYZE
972 } {}
974 # Condition (a='one') matches 80% of the table. (rowid<10) reduces this to
975 # 10%, but (rowid<50) only reduces it to 50%. So in the first case below
976 # the index is used. In the second, it is not. 
978 do_eqp_test 21.2 {
979   SELECT * FROM t2 WHERE a='one' AND rowid < 10
980 } {/*USING INDEX i2 (a=? AND rowid<?)*/}
981 do_eqp_test 21.3 {
982   SELECT * FROM t2 WHERE a='one' AND rowid < 50
983 } {/*USING INTEGER PRIMARY KEY*/}
985 #-------------------------------------------------------------------------
987 reset_db
988 do_execsql_test 22.0 {
989   CREATE TABLE t3(a, b, c, d, PRIMARY KEY(a, b)) WITHOUT ROWID;
991 do_execsql_test 22.1 {
992   WITH r(x) AS (
993     SELECT 1
994     UNION ALL
995     SELECT x+1 FROM r WHERE x<=100
996   )
998   INSERT INTO t3 SELECT
999     CASE WHEN (x>45 AND x<96) THEN 'B' ELSE 'A' END,  /* Column "a" */
1000     x,                                                /* Column "b" */
1001     CASE WHEN (x<51) THEN 'one' ELSE 'two' END,       /* Column "c" */
1002     x                                                 /* Column "d" */
1003   FROM r;
1005   CREATE INDEX i3 ON t3(c);
1006   CREATE INDEX i4 ON t3(d);
1007   ANALYZE;
1010 # Expression (c='one' AND a='B') matches 5 table rows. But (c='one' AND a=A')
1011 # matches 45. Expression (d<?) matches 20. Neither index is a covering index.
1013 # Therefore, with stat4 data, SQLite prefers (c='one' AND a='B') over (d<20),
1014 # and (d<20) over (c='one' AND a='A').
1015 foreach {tn where res} {
1016   1 "c='one' AND a='B' AND d < 20"   {/*INDEX i3 (c=? AND a=?)*/}
1017   2 "c='one' AND a='A' AND d < 20"   {/*INDEX i4 (d<?)*/}
1018 } {
1019   do_eqp_test 22.2.$tn "SELECT * FROM t3 WHERE $where" $res
1022 proc int_to_char {i} {
1023   set ret ""
1024   set char [list a b c d e f g h i j]
1025   foreach {div} {1000 100 10 1} {
1026     append ret [lindex $char [expr ($i / $div) % 10]]
1027   }
1028   set ret
1030 db func int_to_char int_to_char
1032 do_execsql_test 23.0 {
1033   CREATE TABLE t4(
1034     a COLLATE nocase, b, c, 
1035     d, e, f, 
1036     PRIMARY KEY(c, b, a)
1037   ) WITHOUT ROWID;
1038   CREATE INDEX i41 ON t4(e);
1039   CREATE INDEX i42 ON t4(f);
1041   WITH data(a, b, c, d, e, f) AS (
1042     SELECT int_to_char(0), 'xyz', 'zyx', '*', 0, 0
1043     UNION ALL
1044     SELECT 
1045       int_to_char(f+1), b, c, d, (e+1) % 2, f+1
1046     FROM data WHERE f<1024
1047   )
1048   INSERT INTO t4 SELECT a, b, c, d, e, f FROM data;
1049   ANALYZE;
1050 } {}
1052 do_eqp_test 23.1 {
1053   SELECT * FROM t4 WHERE 
1054     (e=1 AND b='xyz' AND c='zyx' AND a<'AEA') AND f<300
1055 } {
1056   0 0 0 {SEARCH TABLE t4 USING INDEX i41 (e=? AND c=? AND b=? AND a<?)}
1058 do_eqp_test 23.2 {
1059   SELECT * FROM t4 WHERE 
1060     (e=1 AND b='xyz' AND c='zyx' AND a<'JJJ') AND f<300
1061 } {
1062   0 0 0 {SEARCH TABLE t4 USING INDEX i42 (f<?)}
1065 do_execsql_test 24.0 {
1066   CREATE TABLE t5(c, d, b, e, a, PRIMARY KEY(a, b, c)) WITHOUT ROWID;
1067   WITH data(a, b, c, d, e) AS (
1068     SELECT 'z', 'y', 0, 0, 0
1069     UNION ALL
1070     SELECT 
1071       a, CASE WHEN b='y' THEN 'n' ELSE 'y' END, c+1, e/250, e+1 
1072     FROM data
1073     WHERE e<1000
1074   )
1075   INSERT INTO t5(a, b, c, d, e) SELECT * FROM data;
1076   CREATE INDEX t5d ON t5(d);
1077   CREATE INDEX t5e ON t5(e);
1078   ANALYZE;
1081 foreach {tn where eqp} {
1082   1 "d=0 AND a='z' AND b='n' AND e<200" {/*t5d (d=? AND a=? AND b=?)*/}
1083   2 "d=0 AND a='z' AND b='n' AND e<100" {/*t5e (e<?)*/}
1085   3 "d=0 AND e<300"                     {/*t5d (d=?)*/}
1086   4 "d=0 AND e<200"                     {/*t5e (e<?)*/}
1087 } {
1088   do_eqp_test 24.$tn "SeLeCt * FROM t5 WHERE $where" $eqp
1091 #-------------------------------------------------------------------------
1092 # Test that if stat4 data is available but cannot be used because the
1093 # rhs of a range constraint is a complex expression, the default estimates
1094 # are used instead.
1095 ifcapable stat4&&cte {
1096   do_execsql_test 25.1 {
1097     CREATE TABLE t6(a, b);
1098     WITH ints(i,j) AS (
1099       SELECT 1,1 UNION ALL SELECT i+1,j+1 FROM ints WHERE i<100
1100     ) INSERT INTO t6 SELECT * FROM ints;
1101     CREATE INDEX aa ON t6(a);
1102     CREATE INDEX bb ON t6(b);
1103     ANALYZE;
1104   }
1106   # Term (b<?) is estimated at 25%. Better than (a<30) but not as
1107   # good as (a<20).
1108   do_eqp_test 25.2.1 { SELECT * FROM t6 WHERE a<30 AND b<? } {
1109     0 0 0 {SEARCH TABLE t6 USING INDEX bb (b<?)}
1110   }
1111   do_eqp_test 25.2.2 { SELECT * FROM t6 WHERE a<20 AND b<? } {
1112     0 0 0 {SEARCH TABLE t6 USING INDEX aa (a<?)}
1113   }
1115   # Term (b BETWEEN ? AND ?) is estimated at 1/64.
1116   do_eqp_test 25.3.1 { 
1117     SELECT * FROM t6 WHERE a BETWEEN 5 AND 10 AND b BETWEEN ? AND ? 
1118   } {
1119     0 0 0 {SEARCH TABLE t6 USING INDEX bb (b>? AND b<?)}
1120   }
1121   
1122   # Term (b BETWEEN ? AND 60) is estimated to return roughly 15 rows -
1123   # 60 from (b<=60) multiplied by 0.25 for the b>=? term. Better than
1124   # (a<20) but not as good as (a<10).
1125   do_eqp_test 25.4.1 { 
1126     SELECT * FROM t6 WHERE a < 10 AND (b BETWEEN ? AND 60)
1127   } {
1128     0 0 0 {SEARCH TABLE t6 USING INDEX aa (a<?)}
1129   }
1130   do_eqp_test 25.4.2 { 
1131     SELECT * FROM t6 WHERE a < 20 AND (b BETWEEN ? AND 60)
1132   } {
1133     0 0 0 {SEARCH TABLE t6 USING INDEX bb (b>? AND b<?)}
1134   }
1137 finish_test