test/analyze.test

   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 WHERE idx NOT NULL
  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 WHERE idx NOT NULL
  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 # Verify that DROP TABLE and DROP INDEX remove entries from the
 291 # sqlite_stat1, sqlite_stat3 and sqlite_stat4 tables.
 292 #
 293 do_test analyze-5.0 {
 294   execsql {
 295     DELETE FROM t3;
 296     DELETE FROM t4;
 297     INSERT INTO t3 VALUES(1,2,3,4);
 298     INSERT INTO t3 VALUES(5,6,7,8);
 299     INSERT INTO t3 SELECT a+8, b+8, c+8, d+8 FROM t3;
 300     INSERT INTO t3 SELECT a+16, b+16, c+16, d+16 FROM t3;
 301     INSERT INTO t3 SELECT a+32, b+32, c+32, d+32 FROM t3;
 302     INSERT INTO t3 SELECT a+64, b+64, c+64, d+64 FROM t3;
 303     INSERT INTO t4 SELECT a, b, c FROM t3;
 304     ANALYZE;
 305     SELECT DISTINCT idx FROM sqlite_stat1 ORDER BY 1;
 306     SELECT DISTINCT tbl FROM sqlite_stat1 ORDER BY 1;
 307   }
 308 } {t3i1 t3i2 t3i3 t4i1 t4i2 t3 t4}
 309 ifcapable stat4||stat3 {
 310   ifcapable stat4 {set stat sqlite_stat4} else {set stat sqlite_stat3}
 311   do_test analyze-5.1 {
 312     execsql "
 313       SELECT DISTINCT idx FROM $stat ORDER BY 1;
 314       SELECT DISTINCT tbl FROM $stat ORDER BY 1;
 315     "
 316   } {t3i1 t3i2 t3i3 t4i1 t4i2 t3 t4}
 317 }
 318 do_test analyze-5.2 {
 319   execsql {
 320     DROP INDEX t3i2;
 321     SELECT DISTINCT idx FROM sqlite_stat1 ORDER BY 1;
 322     SELECT DISTINCT tbl FROM sqlite_stat1 ORDER BY 1;
 323   }
 324 } {t3i1 t3i3 t4i1 t4i2 t3 t4}
 325 ifcapable stat4||stat3 {
 326   do_test analyze-5.3 {
 327     execsql "
 328       SELECT DISTINCT idx FROM $stat ORDER BY 1;
 329       SELECT DISTINCT tbl FROM $stat ORDER BY 1;
 330     "
 331   } {t3i1 t3i3 t4i1 t4i2 t3 t4}
 332 }
 333 do_test analyze-5.4 {
 334   execsql {
 335     DROP TABLE t3;
 336     SELECT DISTINCT idx FROM sqlite_stat1 ORDER BY 1;
 337     SELECT DISTINCT tbl FROM sqlite_stat1 ORDER BY 1;
 338   }
 339 } {t4i1 t4i2 t4}
 340 ifcapable stat4||stat3 {
 341   do_test analyze-5.5 {
 342     execsql "
 343       SELECT DISTINCT idx FROM $stat ORDER BY 1;
 344       SELECT DISTINCT tbl FROM $stat ORDER BY 1;
 345     "
 346   } {t4i1 t4i2 t4}
 347 }
 348
 349 # This test corrupts the database file so it must be the last test
 350 # in the series.
 351 #
 352 do_test analyze-99.1 {
 353   execsql {
 354     PRAGMA writable_schema=on;
 355     UPDATE sqlite_master SET sql='nonsense' WHERE name='sqlite_stat1';
 356   }
 357   db close
 358   catch { sqlite3 db test.db }
 359   catchsql {
 360     ANALYZE
 361   }
 362 } {1 {malformed database schema (sqlite_stat1)}}
 363
 364 finish_test