1 # 2005 July 22

     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 # This file implements regression tests for SQLite library.

    12 # This file implements tests for the ANALYZE command.

    13 #

    14 # $Id: analyze.test,v 1.9 2008/08/11 18:44:58 drh Exp $

    15 

    16 set testdir [file dirname $argv0]

    17 source $testdir/tester.tcl

    18 

    19 # There is nothing to test if ANALYZE is disable for this build.

    20 #

    21 ifcapable {!analyze} {

    22   finish_test

    23   return

    24 }

    25 

    26 # Basic sanity checks.

    27 #

    28 do_test analyze-1.1 {

    29   catchsql {

    30     ANALYZE no_such_table

    31   }

    32 } {1 {no such table: no_such_table}}

    33 do_test analyze-1.2 {

    34   execsql {

    35     SELECT count(*) FROM sqlite_master WHERE name='sqlite_stat1'

    36   }

    37 } {0}

    38 do_test analyze-1.3 {

    39   catchsql {

    40     ANALYZE no_such_db.no_such_table

    41   }

    42 } {1 {unknown database no_such_db}}

    43 do_test analyze-1.4 {

    44   execsql {

    45     SELECT count(*) FROM sqlite_master WHERE name='sqlite_stat1'

    46   }

    47 } {0}

    48 do_test analyze-1.5.1 {

    49   catchsql {

    50     ANALYZE

    51   }

    52 } {0 {}}

    53 do_test analyze-1.5.2 {

    54   catchsql {

    55     PRAGMA empty_result_callbacks=1;

    56     ANALYZE

    57   }

    58 } {0 {}}

    59 do_test analyze-1.6 {

    60   execsql {

    61     SELECT count(*) FROM sqlite_master WHERE name='sqlite_stat1'

    62   }

    63 } {1}

    64 do_test analyze-1.6.2 {

    65   catchsql {

    66     CREATE INDEX stat1idx ON sqlite_stat1(idx);

    67   }

    68 } {1 {table sqlite_stat1 may not be indexed}}

    69 do_test analyze-1.6.3 {

    70   catchsql {

    71     CREATE INDEX main.stat1idx ON SQLite_stat1(idx);

    72   }

    73 } {1 {table sqlite_stat1 may not be indexed}}

    74 do_test analyze-1.7 {

    75   execsql {

    76     SELECT * FROM sqlite_stat1

    77   }

    78 } {}

    79 do_test analyze-1.8 {

    80   catchsql {

    81     ANALYZE main

    82   }

    83 } {0 {}}

    84 do_test analyze-1.9 {

    85   execsql {

    86     SELECT * FROM sqlite_stat1

    87   }

    88 } {}

    89 do_test analyze-1.10 {

    90   catchsql {

    91     CREATE TABLE t1(a,b);

    92     ANALYZE main.t1;

    93   }

    94 } {0 {}}

    95 do_test analyze-1.11 {

    96   execsql {

    97     SELECT * FROM sqlite_stat1

    98   }

    99 } {}

   100 do_test analyze-1.12 {

   101   catchsql {

   102     ANALYZE t1;

   103   }

   104 } {0 {}}

   105 do_test analyze-1.13 {

   106   execsql {

   107     SELECT * FROM sqlite_stat1

   108   }

   109 } {}

   110 

   111 # Create some indices that can be analyzed.  But do not yet add

   112 # data.  Without data in the tables, no analysis is done.

   113 #

   114 do_test analyze-2.1 {

   115   execsql {

   116     CREATE INDEX t1i1 ON t1(a);

   117     ANALYZE main.t1;

   118     SELECT * FROM sqlite_stat1 ORDER BY idx;

   119   }

   120 } {}

   121 do_test analyze-2.2 {

   122   execsql {

   123     CREATE INDEX t1i2 ON t1(b);

   124     ANALYZE t1;

   125     SELECT * FROM sqlite_stat1 ORDER BY idx;

   126   }

   127 } {}

   128 do_test analyze-2.3 {

   129   execsql {

   130     CREATE INDEX t1i3 ON t1(a,b);

   131     ANALYZE main;

   132     SELECT * FROM sqlite_stat1 ORDER BY idx;

   133   }

   134 } {}

   135 

   136 # Start adding data to the table.  Verify that the analysis

   137 # is done correctly.

   138 #

   139 do_test analyze-3.1 {

   140   execsql {

   141     INSERT INTO t1 VALUES(1,2);

   142     INSERT INTO t1 VALUES(1,3);

   143     ANALYZE main.t1;

   144     SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;

   145   }

   146 } {t1i1 {2 2} t1i2 {2 1} t1i3 {2 2 1}}

   147 do_test analyze-3.2 {

   148   execsql {

   149     INSERT INTO t1 VALUES(1,4);

   150     INSERT INTO t1 VALUES(1,5);

   151     ANALYZE t1;

   152     SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;

   153   }

   154 } {t1i1 {4 4} t1i2 {4 1} t1i3 {4 4 1}}

   155 do_test analyze-3.3 {

   156   execsql {

   157     INSERT INTO t1 VALUES(2,5);

   158     ANALYZE main;

   159     SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;

   160   }

   161 } {t1i1 {5 3} t1i2 {5 2} t1i3 {5 3 1}}

   162 do_test analyze-3.4 {

   163   execsql {

   164     CREATE TABLE t2 AS SELECT * FROM t1;

   165     CREATE INDEX t2i1 ON t2(a);

   166     CREATE INDEX t2i2 ON t2(b);

   167     CREATE INDEX t2i3 ON t2(a,b);

   168     ANALYZE;

   169     SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;

   170   }

   171 } {t1i1 {5 3} t1i2 {5 2} t1i3 {5 3 1} t2i1 {5 3} t2i2 {5 2} t2i3 {5 3 1}}

   172 do_test analyze-3.5 {

   173   execsql {

   174     DROP INDEX t2i3;

   175     ANALYZE t1;

   176     SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;

   177   }

   178 } {t1i1 {5 3} t1i2 {5 2} t1i3 {5 3 1} t2i1 {5 3} t2i2 {5 2}}

   179 do_test analyze-3.6 {

   180   execsql {

   181     ANALYZE t2;

   182     SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;

   183   }

   184 } {t1i1 {5 3} t1i2 {5 2} t1i3 {5 3 1} t2i1 {5 3} t2i2 {5 2}}

   185 do_test analyze-3.7 {

   186   execsql {

   187     DROP INDEX t2i2;

   188     ANALYZE t2;

   189     SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;

   190   }

   191 } {t1i1 {5 3} t1i2 {5 2} t1i3 {5 3 1} t2i1 {5 3}}

   192 do_test analyze-3.8 {

   193   execsql {

   194     CREATE TABLE t3 AS SELECT a, b, rowid AS c, 'hi' AS d FROM t1;

   195     CREATE INDEX t3i1 ON t3(a);

   196     CREATE INDEX t3i2 ON t3(a,b,c,d);

   197     CREATE INDEX t3i3 ON t3(d,b,c,a);

   198     DROP TABLE t1;

   199     DROP TABLE t2;

   200     SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;

   201   }

   202 } {}

   203 do_test analyze-3.9 {

   204   execsql {

   205     ANALYZE;

   206     SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;

   207   }

   208 } {t3i1 {5 3} t3i2 {5 3 1 1 1} t3i3 {5 5 2 1 1}}

   209 

   210 do_test analyze-3.10 {

   211   execsql {

   212     CREATE TABLE [silly " name](a, b, c);

   213     CREATE INDEX 'foolish '' name' ON [silly " name](a, b);

   214     CREATE INDEX 'another foolish '' name' ON [silly " name](c);

   215     INSERT INTO [silly " name] VALUES(1, 2, 3);

   216     INSERT INTO [silly " name] VALUES(4, 5, 6);

   217     ANALYZE;

   218     SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;

   219   }

   220 } {{another foolish ' name} {2 1} {foolish ' name} {2 1 1} t3i1 {5 3} t3i2 {5 3 1 1 1} t3i3 {5 5 2 1 1}}

   221 do_test analyze-3.11 {

   222   execsql {

   223     DROP INDEX "foolish ' name";

   224     SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;

   225   }

   226 } {{another foolish ' name} {2 1} t3i1 {5 3} t3i2 {5 3 1 1 1} t3i3 {5 5 2 1 1}}

   227 do_test analyze-3.11 {

   228   execsql {

   229     DROP TABLE "silly "" name";

   230     SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;

   231   }

   232 } {t3i1 {5 3} t3i2 {5 3 1 1 1} t3i3 {5 5 2 1 1}}

   233 

   234 # Try corrupting the sqlite_stat1 table and make sure the

   235 # database is still able to function.

   236 #

   237 do_test analyze-4.0 {

   238   sqlite3 db2 test.db

   239   db2 eval {

   240     CREATE TABLE t4(x,y,z);

   241     CREATE INDEX t4i1 ON t4(x);

   242     CREATE INDEX t4i2 ON t4(y);

   243     INSERT INTO t4 SELECT a,b,c FROM t3;

   244   }

   245   db2 close

   246   db close

   247   sqlite3 db test.db

   248   execsql {

   249     ANALYZE;

   250     SELECT idx, stat FROM sqlite_stat1 ORDER BY idx;

   251   }

   252 } {t3i1 {5 3} t3i2 {5 3 1 1 1} t3i3 {5 5 2 1 1} t4i1 {5 3} t4i2 {5 2}}

   253 do_test analyze-4.1 {

   254   execsql {

   255     PRAGMA writable_schema=on;

   256     INSERT INTO sqlite_stat1 VALUES(null,null,null);

   257     PRAGMA writable_schema=off;

   258   }

   259   db close

   260   sqlite3 db test.db

   261   execsql {

   262     SELECT * FROM t4 WHERE x=1234;

   263   }

   264 } {}

   265 do_test analyze-4.2 {

   266   execsql {

   267     PRAGMA writable_schema=on;

   268     DELETE FROM sqlite_stat1;

   269     INSERT INTO sqlite_stat1 VALUES('t4','t4i1','nonsense');

   270     INSERT INTO sqlite_stat1 VALUES('t4','t4i2','120897349817238741092873198273409187234918720394817209384710928374109827172901827349871928741910');

   271     PRAGMA writable_schema=off;

   272   }

   273   db close

   274   sqlite3 db test.db

   275   execsql {

   276     SELECT * FROM t4 WHERE x=1234;

   277   }

   278 } {}

   279 do_test analyze-4.3 {

   280   execsql {

   281     INSERT INTO sqlite_stat1 VALUES('t4','xyzzy','0 1 2 3');

   282   }

   283   db close

   284   sqlite3 db test.db

   285   execsql {

   286     SELECT * FROM t4 WHERE x=1234;

   287   }

   288 } {}

   289 

   290 # This test corrupts the database file so it must be the last test

   291 # in the series.

   292 #

   293 do_test analyze-99.1 {

   294   execsql {

   295     PRAGMA writable_schema=on;

   296     UPDATE sqlite_master SET sql='nonsense' WHERE name='sqlite_stat1';

   297   }

   298   db close

   299   sqlite3 db test.db

   300   catchsql {

   301     ANALYZE

   302   }

   303 } {1 {malformed database schema (sqlite_stat1) - near "nonsense": syntax error}}

   304 

   305 

   306 finish_test