gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/analyze.test (about) 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 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 { 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 } 317 do_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} 324 ifcapable 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 } 332 do_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} 339 ifcapable 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 test 349 # in the series. 350 # 351 do_test analyze-5.99 { 352 sqlite3_db_config db DEFENSIVE 0 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 # Verify that tables whose names begin with "sqlite" but not 365 # "sqlite_" are analyzed. 366 # 367 db close 368 sqlite3 db :memory: 369 do_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 finish_test