AryaWu/sqlite
0
1# 2005 July 222#3# The author disclaims copyright to this source code. In place of4# 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 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18 19# There is nothing to test if ANALYZE is disable for this build.20#21ifcapable {!analyze} {22 finish_test23 return24}25 26# Basic sanity checks.27#28do_test analyze-1.1 {29 catchsql {30 ANALYZE no_such_table31 }32} {1 {no such table: no_such_table}}33do_test analyze-1.2 {34 execsql {35 SELECT count(*) FROM sqlite_master WHERE name='sqlite_stat1'36 }37} {0}38do_test analyze-1.3 {39 catchsql {40 ANALYZE no_such_db.no_such_table41 }42} {1 {unknown database no_such_db}}43do_test analyze-1.4 {44 execsql {45 SELECT count(*) FROM sqlite_master WHERE name='sqlite_stat1'46 }47} {0}48do_test analyze-1.5.1 {49 catchsql {50 ANALYZE51 }52} {0 {}}53do_test analyze-1.5.2 {54 catchsql {55 PRAGMA empty_result_callbacks=1;56 ANALYZE57 }58} {0 {}}59do_test analyze-1.6 {60 execsql {61 SELECT count(*) FROM sqlite_master WHERE name='sqlite_stat1'62 }63} {1}64do_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}}69do_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}}74do_test analyze-1.7 {75 execsql {76 SELECT * FROM sqlite_stat1 WHERE idx NOT NULL77 }78} {}79do_test analyze-1.8 {80 catchsql {81 ANALYZE main82 }83} {0 {}}84do_test analyze-1.9 {85 execsql {86 SELECT * FROM sqlite_stat1 WHERE idx NOT NULL87 }88} {}89do_test analyze-1.10 {90 catchsql {91 CREATE TABLE t1(a,b);92 ANALYZE main.t1;93 }94} {0 {}}95do_test analyze-1.11 {96 execsql {97 SELECT * FROM sqlite_stat198 }99} {}100do_test analyze-1.12 {101 catchsql {102 ANALYZE t1;103 }104} {0 {}}105do_test analyze-1.13 {106 execsql {107 SELECT * FROM sqlite_stat1108 }109} {}110 111# Create some indices that can be analyzed. But do not yet add112# data. Without data in the tables, no analysis is done.113#114do_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} {}121do_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} {}128do_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 analysis137# is done correctly.138#139do_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}}147do_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}}155do_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}}162do_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}}172do_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}}179do_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}}185do_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}}192do_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} {}203do_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 210do_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}}221do_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}}227do_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 the235# database is still able to function.236#237do_test analyze-4.0 {238 sqlite3 db2 test.db239 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 close246 db close247 sqlite3 db test.db248 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}}253do_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 close260 sqlite3 db test.db261 execsql {262 SELECT * FROM t4 WHERE x=1234;263 }264} {}265do_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 close274 sqlite3 db test.db275 execsql {276 SELECT * FROM t4 WHERE x=1234;277 }278} {}279do_test analyze-4.3 {280 execsql {281 INSERT INTO sqlite_stat1 VALUES('t4','xyzzy','0 1 2 3');282 }283 db close284 sqlite3 db test.db285 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 and sqlite_stat4 tables.292#293do_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}309ifcapable stat4 {310 do_test analyze-5.1 {311 execsql {312 SELECT DISTINCT idx FROM sqlite_stat4 ORDER BY 1;313 SELECT DISTINCT tbl FROM sqlite_stat4 ORDER BY 1;314 }315 } {t3i1 t3i2 t3i3 t4i1 t4i2 t3 t4}316}317do_test analyze-5.2 {318 execsql {319 DROP INDEX t3i2;320 SELECT DISTINCT idx FROM sqlite_stat1 ORDER BY 1;321 SELECT DISTINCT tbl FROM sqlite_stat1 ORDER BY 1;322 }323} {t3i1 t3i3 t4i1 t4i2 t3 t4}324ifcapable stat4 {325 do_test analyze-5.3 {326 execsql {327 SELECT DISTINCT idx FROM sqlite_stat4 ORDER BY 1;328 SELECT DISTINCT tbl FROM sqlite_stat4 ORDER BY 1;329 }330 } {t3i1 t3i3 t4i1 t4i2 t3 t4}331}332do_test analyze-5.4 {333 execsql {334 DROP TABLE t3;335 SELECT DISTINCT idx FROM sqlite_stat1 ORDER BY 1;336 SELECT DISTINCT tbl FROM sqlite_stat1 ORDER BY 1;337 }338} {t4i1 t4i2 t4}339ifcapable stat4 {340 do_test analyze-5.5 {341 execsql {342 SELECT DISTINCT idx FROM sqlite_stat4 ORDER BY 1;343 SELECT DISTINCT tbl FROM sqlite_stat4 ORDER BY 1;344 }345 } {t4i1 t4i2 t4}346}347 348# This test corrupts the database file so it must be the last test349# in the series.350#351do_test analyze-5.99 {352 sqlite3_db_config db DEFENSIVE 0353 execsql {354 PRAGMA writable_schema=on;355 UPDATE sqlite_master SET sql='nonsense' WHERE name='sqlite_stat1';356 }357 db close358 catch { sqlite3 db test.db }359 catchsql {360 ANALYZE361 }362} {1 {malformed database schema (sqlite_stat1)}}363 364# Verify that tables whose names begin with "sqlite" but not365# "sqlite_" are analyzed.366#367db close368sqlite3 db :memory:369do_execsql_test analyze-6.1 {370 CREATE TABLE sqliteDemo(a);371 INSERT INTO sqliteDemo(a) VALUES(1),(2),(3),(4),(5);372 CREATE TABLE SQLiteDemo2(a INTEGER PRIMARY KEY AUTOINCREMENT);373 INSERT INTO SQLiteDemo2 SELECT * FROM sqliteDemo;374 CREATE TABLE t1(b);375 INSERT INTO t1(b) SELECT a FROM sqliteDemo;376 ANALYZE;377 SELECT tbl FROM sqlite_stat1 WHERE idx IS NULL ORDER BY tbl;378} {SQLiteDemo2 sqliteDemo t1}379 380# The following caused a small buffer overread in STAT4 processing prior381# to check-in [b99135288b157044].382#383ifcapable stat4 {384 reset_db385 database_may_be_corrupt386 do_execsql_test analyze-7.1 {387 CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER);388 INSERT INTO t1 VALUES(1, 7223372036854775);389 INSERT INTO t1 VALUES(2, 7223372036854776);390 INSERT INTO t1 VALUES(3, 7223372036854777);391 CREATE INDEX i1 ON t1(b);392 ANALYZE;393 UPDATE sqlite_stat4 SET sample = substr(sample, 0, 4);394 ANALYZE sqlite_schema;395 SELECT * FROM t1 WHERE b>7223372036854775396 } {2 7223372036854776 3 7223372036854777}397}398 399finish_test400