| OLD | NEW |
|---|
| (Empty) |
| 1 # 2013 August 3 | |
| 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 # | |
| 12 # This file contains automated tests used to verify that the sqlite_stat3 | |
| 13 # functionality is working. The tests in this file are based on a subset | |
| 14 # of the sqlite_stat4 tests in analyze9.test. | |
| 15 # | |
| 16 | |
| 17 set testdir [file dirname $argv0] | |
| 18 source $testdir/tester.tcl | |
| 19 set testprefix analyzeB | |
| 20 | |
| 21 ifcapable !stat3 { | |
| 22 finish_test | |
| 23 return | |
| 24 } | |
| 25 | |
| 26 do_execsql_test 1.0 { | |
| 27 CREATE TABLE t1(a TEXT, b TEXT); | |
| 28 INSERT INTO t1 VALUES('(0)', '(0)'); | |
| 29 INSERT INTO t1 VALUES('(1)', '(1)'); | |
| 30 INSERT INTO t1 VALUES('(2)', '(2)'); | |
| 31 INSERT INTO t1 VALUES('(3)', '(3)'); | |
| 32 INSERT INTO t1 VALUES('(4)', '(4)'); | |
| 33 CREATE INDEX i1 ON t1(a, b); | |
| 34 } {} | |
| 35 | |
| 36 | |
| 37 do_execsql_test 1.1 { | |
| 38 ANALYZE; | |
| 39 } {} | |
| 40 | |
| 41 do_execsql_test 1.2 { | |
| 42 SELECT tbl,idx,nEq,nLt,nDLt,quote(sample) FROM sqlite_stat3; | |
| 43 } { | |
| 44 t1 i1 1 0 0 '(0)' | |
| 45 t1 i1 1 1 1 '(1)' | |
| 46 t1 i1 1 2 2 '(2)' | |
| 47 t1 i1 1 3 3 '(3)' | |
| 48 t1 i1 1 4 4 '(4)' | |
| 49 } | |
| 50 | |
| 51 if {[permutation] != "utf16"} { | |
| 52 do_execsql_test 1.3 { | |
| 53 SELECT tbl,idx,nEq,nLt,nDLt,quote(sample) FROM sqlite_stat3; | |
| 54 } { | |
| 55 t1 i1 1 0 0 '(0)' | |
| 56 t1 i1 1 1 1 '(1)' | |
| 57 t1 i1 1 2 2 '(2)' | |
| 58 t1 i1 1 3 3 '(3)' | |
| 59 t1 i1 1 4 4 '(4)' | |
| 60 } | |
| 61 } | |
| 62 | |
| 63 | |
| 64 #------------------------------------------------------------------------- | |
| 65 # This is really just to test SQL user function "test_decode". | |
| 66 # | |
| 67 reset_db | |
| 68 do_execsql_test 2.1 { | |
| 69 CREATE TABLE t1(a, b, c); | |
| 70 INSERT INTO t1(a) VALUES('some text'); | |
| 71 INSERT INTO t1(a) VALUES(14); | |
| 72 INSERT INTO t1(a) VALUES(NULL); | |
| 73 INSERT INTO t1(a) VALUES(22.0); | |
| 74 INSERT INTO t1(a) VALUES(x'656667'); | |
| 75 CREATE INDEX i1 ON t1(a, b, c); | |
| 76 ANALYZE; | |
| 77 SELECT quote(sample) FROM sqlite_stat3; | |
| 78 } { | |
| 79 NULL 14 22.0 {'some text'} X'656667' | |
| 80 } | |
| 81 | |
| 82 #------------------------------------------------------------------------- | |
| 83 # | |
| 84 reset_db | |
| 85 do_execsql_test 3.1 { | |
| 86 CREATE TABLE t2(a, b); | |
| 87 CREATE INDEX i2 ON t2(a, b); | |
| 88 BEGIN; | |
| 89 } | |
| 90 | |
| 91 do_test 3.2 { | |
| 92 for {set i 0} {$i < 1000} {incr i} { | |
| 93 set a [expr $i / 10] | |
| 94 set b [expr int(rand() * 15.0)] | |
| 95 execsql { INSERT INTO t2 VALUES($a, $b) } | |
| 96 } | |
| 97 execsql COMMIT | |
| 98 } {} | |
| 99 | |
| 100 db func lindex lindex | |
| 101 | |
| 102 # Each value of "a" occurs exactly 10 times in the table. | |
| 103 # | |
| 104 do_execsql_test 3.3.1 { | |
| 105 SELECT count(*) FROM t2 GROUP BY a; | |
| 106 } [lrange [string repeat "10 " 100] 0 99] | |
| 107 | |
| 108 # The first element in the "nEq" list of all samples should therefore be 10. | |
| 109 # | |
| 110 do_execsql_test 3.3.2 { | |
| 111 ANALYZE; | |
| 112 SELECT nEq FROM sqlite_stat3; | |
| 113 } [lrange [string repeat "10 " 100] 0 23] | |
| 114 | |
| 115 #------------------------------------------------------------------------- | |
| 116 # | |
| 117 do_execsql_test 3.4 { | |
| 118 DROP TABLE IF EXISTS t1; | |
| 119 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); | |
| 120 INSERT INTO t1 VALUES(1, 1, 'one-a'); | |
| 121 INSERT INTO t1 VALUES(11, 1, 'one-b'); | |
| 122 INSERT INTO t1 VALUES(21, 1, 'one-c'); | |
| 123 INSERT INTO t1 VALUES(31, 1, 'one-d'); | |
| 124 INSERT INTO t1 VALUES(41, 1, 'one-e'); | |
| 125 INSERT INTO t1 VALUES(51, 1, 'one-f'); | |
| 126 INSERT INTO t1 VALUES(61, 1, 'one-g'); | |
| 127 INSERT INTO t1 VALUES(71, 1, 'one-h'); | |
| 128 INSERT INTO t1 VALUES(81, 1, 'one-i'); | |
| 129 INSERT INTO t1 VALUES(91, 1, 'one-j'); | |
| 130 INSERT INTO t1 SELECT a+1,2,'two' || substr(c,4) FROM t1; | |
| 131 INSERT INTO t1 SELECT a+2,3,'three'||substr(c,4) FROM t1 WHERE c GLOB 'one-*'; | |
| 132 INSERT INTO t1 SELECT a+3,4,'four'||substr(c,4) FROM t1 WHERE c GLOB 'one-*'; | |
| 133 INSERT INTO t1 SELECT a+4,5,'five'||substr(c,4) FROM t1 WHERE c GLOB 'one-*'; | |
| 134 INSERT INTO t1 SELECT a+5,6,'six'||substr(c,4) FROM t1 WHERE c GLOB 'one-*'; | |
| 135 CREATE INDEX t1b ON t1(b); | |
| 136 ANALYZE; | |
| 137 SELECT c FROM t1 WHERE b=3 AND a BETWEEN 30 AND 60; | |
| 138 } {three-d three-e three-f} | |
| 139 | |
| 140 | |
| 141 #------------------------------------------------------------------------- | |
| 142 # These tests verify that the sample selection for stat3 appears to be | |
| 143 # working as designed. | |
| 144 # | |
| 145 | |
| 146 reset_db | |
| 147 db func lindex lindex | |
| 148 db func lrange lrange | |
| 149 | |
| 150 do_execsql_test 4.0 { | |
| 151 DROP TABLE IF EXISTS t1; | |
| 152 CREATE TABLE t1(a, b, c); | |
| 153 CREATE INDEX i1 ON t1(c, b, a); | |
| 154 } | |
| 155 | |
| 156 | |
| 157 proc insert_filler_rows_n {iStart args} { | |
| 158 set A(-ncopy) 1 | |
| 159 set A(-nval) 1 | |
| 160 | |
| 161 foreach {k v} $args { | |
| 162 if {[info exists A($k)]==0} { error "no such option: $k" } | |
| 163 set A($k) $v | |
| 164 } | |
| 165 if {[llength $args] % 2} { | |
| 166 error "option requires an argument: [lindex $args end]" | |
| 167 } | |
| 168 | |
| 169 for {set i 0} {$i < $A(-nval)} {incr i} { | |
| 170 set iVal [expr $iStart+$i] | |
| 171 for {set j 0} {$j < $A(-ncopy)} {incr j} { | |
| 172 execsql { INSERT INTO t1 VALUES($iVal, $iVal, $iVal) } | |
| 173 } | |
| 174 } | |
| 175 } | |
| 176 | |
| 177 do_test 4.1 { | |
| 178 execsql { BEGIN } | |
| 179 insert_filler_rows_n 0 -ncopy 10 -nval 19 | |
| 180 insert_filler_rows_n 20 -ncopy 1 -nval 100 | |
| 181 | |
| 182 execsql { | |
| 183 INSERT INTO t1(c, b, a) VALUES(200, 1, 'a'); | |
| 184 INSERT INTO t1(c, b, a) VALUES(200, 1, 'b'); | |
| 185 INSERT INTO t1(c, b, a) VALUES(200, 1, 'c'); | |
| 186 | |
| 187 INSERT INTO t1(c, b, a) VALUES(200, 2, 'e'); | |
| 188 INSERT INTO t1(c, b, a) VALUES(200, 2, 'f'); | |
| 189 | |
| 190 INSERT INTO t1(c, b, a) VALUES(201, 3, 'g'); | |
| 191 INSERT INTO t1(c, b, a) VALUES(201, 4, 'h'); | |
| 192 | |
| 193 ANALYZE; | |
| 194 SELECT count(*) FROM sqlite_stat3; | |
| 195 SELECT count(*) FROM t1; | |
| 196 } | |
| 197 } {24 297} | |
| 198 | |
| 199 do_execsql_test 4.2 { | |
| 200 SELECT neq, nlt, ndlt, sample FROM sqlite_stat3 ORDER BY rowid LIMIT 16; | |
| 201 } { | |
| 202 10 0 0 0 | |
| 203 10 10 1 1 | |
| 204 10 20 2 2 | |
| 205 10 30 3 3 | |
| 206 10 40 4 4 | |
| 207 10 50 5 5 | |
| 208 10 60 6 6 | |
| 209 10 70 7 7 | |
| 210 10 80 8 8 | |
| 211 10 90 9 9 | |
| 212 10 100 10 10 | |
| 213 10 110 11 11 | |
| 214 10 120 12 12 | |
| 215 10 130 13 13 | |
| 216 10 140 14 14 | |
| 217 10 150 15 15 | |
| 218 } | |
| 219 | |
| 220 do_execsql_test 4.3 { | |
| 221 SELECT neq, nlt, ndlt, sample FROM sqlite_stat3 | |
| 222 ORDER BY rowid DESC LIMIT 2; | |
| 223 } { | |
| 224 2 295 120 201 | |
| 225 5 290 119 200 | |
| 226 } | |
| 227 | |
| 228 do_execsql_test 4.4 { SELECT count(DISTINCT c) FROM t1 WHERE c<201 } 120 | |
| 229 do_execsql_test 4.5 { SELECT count(DISTINCT c) FROM t1 WHERE c<200 } 119 | |
| 230 | |
| 231 reset_db | |
| 232 do_test 4.7 { | |
| 233 execsql { | |
| 234 BEGIN; | |
| 235 CREATE TABLE t1(o,t INTEGER PRIMARY KEY); | |
| 236 CREATE INDEX i1 ON t1(o); | |
| 237 } | |
| 238 for {set i 0} {$i<10000} {incr i [expr (($i<1000)?1:10)]} { | |
| 239 execsql { INSERT INTO t1 VALUES('x', $i) } | |
| 240 } | |
| 241 execsql { | |
| 242 COMMIT; | |
| 243 ANALYZE; | |
| 244 SELECT count(*) FROM sqlite_stat3; | |
| 245 } | |
| 246 } {1} | |
| 247 do_execsql_test 4.8 { | |
| 248 SELECT sample FROM sqlite_stat3; | |
| 249 } {x} | |
| 250 | |
| 251 | |
| 252 #------------------------------------------------------------------------- | |
| 253 # The following would cause a crash at one point. | |
| 254 # | |
| 255 reset_db | |
| 256 do_execsql_test 5.1 { | |
| 257 PRAGMA encoding = 'utf-16'; | |
| 258 CREATE TABLE t0(v); | |
| 259 ANALYZE; | |
| 260 } | |
| 261 | |
| 262 #------------------------------------------------------------------------- | |
| 263 # This was also crashing (corrupt sqlite_stat3 table). | |
| 264 # | |
| 265 reset_db | |
| 266 do_execsql_test 6.1 { | |
| 267 CREATE TABLE t1(a, b); | |
| 268 CREATE INDEX i1 ON t1(a); | |
| 269 CREATE INDEX i2 ON t1(b); | |
| 270 INSERT INTO t1 VALUES(1, 1); | |
| 271 INSERT INTO t1 VALUES(2, 2); | |
| 272 INSERT INTO t1 VALUES(3, 3); | |
| 273 INSERT INTO t1 VALUES(4, 4); | |
| 274 INSERT INTO t1 VALUES(5, 5); | |
| 275 ANALYZE; | |
| 276 PRAGMA writable_schema = 1; | |
| 277 CREATE TEMP TABLE x1 AS | |
| 278 SELECT tbl,idx,neq,nlt,ndlt,sample FROM sqlite_stat3 | |
| 279 ORDER BY (rowid%5), rowid; | |
| 280 DELETE FROM sqlite_stat3; | |
| 281 INSERT INTO sqlite_stat3 SELECT * FROM x1; | |
| 282 PRAGMA writable_schema = 0; | |
| 283 ANALYZE sqlite_master; | |
| 284 } | |
| 285 do_execsql_test 6.2 { | |
| 286 SELECT * FROM t1 WHERE a = 'abc'; | |
| 287 } | |
| 288 | |
| 289 #------------------------------------------------------------------------- | |
| 290 # The following tests experiment with adding corrupted records to the | |
| 291 # 'sample' column of the sqlite_stat3 table. | |
| 292 # | |
| 293 reset_db | |
| 294 sqlite3_db_config_lookaside db 0 0 0 | |
| 295 | |
| 296 do_execsql_test 7.1 { | |
| 297 CREATE TABLE t1(a, b); | |
| 298 CREATE INDEX i1 ON t1(a, b); | |
| 299 INSERT INTO t1 VALUES(1, 1); | |
| 300 INSERT INTO t1 VALUES(2, 2); | |
| 301 INSERT INTO t1 VALUES(3, 3); | |
| 302 INSERT INTO t1 VALUES(4, 4); | |
| 303 INSERT INTO t1 VALUES(5, 5); | |
| 304 ANALYZE; | |
| 305 UPDATE sqlite_stat3 SET sample = X'' WHERE rowid = 1; | |
| 306 ANALYZE sqlite_master; | |
| 307 } | |
| 308 | |
| 309 do_execsql_test 7.2 { | |
| 310 UPDATE sqlite_stat3 SET sample = X'FFFF'; | |
| 311 ANALYZE sqlite_master; | |
| 312 SELECT * FROM t1 WHERE a = 1; | |
| 313 } {1 1} | |
| 314 | |
| 315 do_execsql_test 7.3 { | |
| 316 ANALYZE; | |
| 317 UPDATE sqlite_stat3 SET neq = '0 0 0'; | |
| 318 ANALYZE sqlite_master; | |
| 319 SELECT * FROM t1 WHERE a = 1; | |
| 320 } {1 1} | |
| 321 | |
| 322 do_execsql_test 7.4 { | |
| 323 ANALYZE; | |
| 324 UPDATE sqlite_stat3 SET ndlt = '0 0 0'; | |
| 325 ANALYZE sqlite_master; | |
| 326 SELECT * FROM t1 WHERE a = 3; | |
| 327 } {3 3} | |
| 328 | |
| 329 do_execsql_test 7.5 { | |
| 330 ANALYZE; | |
| 331 UPDATE sqlite_stat3 SET nlt = '0 0 0'; | |
| 332 ANALYZE sqlite_master; | |
| 333 SELECT * FROM t1 WHERE a = 5; | |
| 334 } {5 5} | |
| 335 | |
| 336 #------------------------------------------------------------------------- | |
| 337 # | |
| 338 reset_db | |
| 339 do_execsql_test 8.1 { | |
| 340 CREATE TABLE t1(x TEXT); | |
| 341 CREATE INDEX i1 ON t1(x); | |
| 342 INSERT INTO t1 VALUES('1'); | |
| 343 INSERT INTO t1 VALUES('2'); | |
| 344 INSERT INTO t1 VALUES('3'); | |
| 345 INSERT INTO t1 VALUES('4'); | |
| 346 ANALYZE; | |
| 347 } | |
| 348 do_execsql_test 8.2 { | |
| 349 SELECT * FROM t1 WHERE x = 3; | |
| 350 } {3} | |
| 351 | |
| 352 #------------------------------------------------------------------------- | |
| 353 # | |
| 354 reset_db | |
| 355 do_execsql_test 9.1 { | |
| 356 CREATE TABLE t1(a, b, c, d, e); | |
| 357 CREATE INDEX i1 ON t1(a, b, c, d); | |
| 358 CREATE INDEX i2 ON t1(e); | |
| 359 } | |
| 360 do_test 9.2 { | |
| 361 execsql BEGIN; | |
| 362 for {set i 0} {$i < 100} {incr i} { | |
| 363 execsql "INSERT INTO t1 VALUES('x', 'y', 'z', $i, [expr $i/2])" | |
| 364 } | |
| 365 for {set i 0} {$i < 20} {incr i} { | |
| 366 execsql "INSERT INTO t1 VALUES('x', 'y', 'z', 101, $i)" | |
| 367 } | |
| 368 for {set i 102} {$i < 200} {incr i} { | |
| 369 execsql "INSERT INTO t1 VALUES('x', 'y', 'z', $i, [expr $i/2])" | |
| 370 } | |
| 371 execsql COMMIT | |
| 372 execsql ANALYZE | |
| 373 } {} | |
| 374 | |
| 375 do_eqp_test 9.3.1 { | |
| 376 SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=101 AND e=5; | |
| 377 } {/t1 USING INDEX i1/} | |
| 378 do_eqp_test 9.3.2 { | |
| 379 SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=99 AND e=5; | |
| 380 } {/t1 USING INDEX i1/} | |
| 381 | |
| 382 set value_d [expr 101] | |
| 383 do_eqp_test 9.4.1 { | |
| 384 SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=$value_d AND e=5 | |
| 385 } {/t1 USING INDEX i1/} | |
| 386 set value_d [expr 99] | |
| 387 do_eqp_test 9.4.2 { | |
| 388 SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=$value_d AND e=5 | |
| 389 } {/t1 USING INDEX i1/} | |
| 390 | |
| 391 #------------------------------------------------------------------------- | |
| 392 # Check that the planner takes stat3 data into account when considering | |
| 393 # "IS NULL" and "IS NOT NULL" constraints. | |
| 394 # | |
| 395 do_execsql_test 10.1.1 { | |
| 396 DROP TABLE IF EXISTS t3; | |
| 397 CREATE TABLE t3(a, b); | |
| 398 CREATE INDEX t3a ON t3(a); | |
| 399 CREATE INDEX t3b ON t3(b); | |
| 400 } | |
| 401 do_test 10.1.2 { | |
| 402 for {set i 1} {$i < 100} {incr i} { | |
| 403 if {$i>90} { set a $i } else { set a NULL } | |
| 404 set b [expr $i % 5] | |
| 405 execsql "INSERT INTO t3 VALUES($a, $b)" | |
| 406 } | |
| 407 execsql ANALYZE | |
| 408 } {} | |
| 409 do_eqp_test 10.1.3 { | |
| 410 SELECT * FROM t3 WHERE a IS NULL AND b = 2 | |
| 411 } {/t3 USING INDEX t3b/} | |
| 412 do_eqp_test 10.1.4 { | |
| 413 SELECT * FROM t3 WHERE a IS NOT NULL AND b = 2 | |
| 414 } {/t3 USING INDEX t3a/} | |
| 415 | |
| 416 #------------------------------------------------------------------------- | |
| 417 # Check that stat3 data is used correctly with non-default collation | |
| 418 # sequences. | |
| 419 # | |
| 420 foreach {tn schema} { | |
| 421 1 { | |
| 422 CREATE TABLE t4(a COLLATE nocase, b); | |
| 423 CREATE INDEX t4a ON t4(a); | |
| 424 CREATE INDEX t4b ON t4(b); | |
| 425 } | |
| 426 2 { | |
| 427 CREATE TABLE t4(a, b); | |
| 428 CREATE INDEX t4a ON t4(a COLLATE nocase); | |
| 429 CREATE INDEX t4b ON t4(b); | |
| 430 } | |
| 431 } { | |
| 432 drop_all_tables | |
| 433 do_test 11.$tn.1 { execsql $schema } {} | |
| 434 | |
| 435 do_test 11.$tn.2 { | |
| 436 for {set i 0} {$i < 100} {incr i} { | |
| 437 if { ($i % 10)==0 } { set a ABC } else { set a DEF } | |
| 438 set b [expr $i % 5] | |
| 439 execsql { INSERT INTO t4 VALUES($a, $b) } | |
| 440 } | |
| 441 execsql ANALYZE | |
| 442 } {} | |
| 443 | |
| 444 do_eqp_test 11.$tn.3 { | |
| 445 SELECT * FROM t4 WHERE a = 'def' AND b = 3; | |
| 446 } {/t4 USING INDEX t4b/} | |
| 447 | |
| 448 if {$tn==1} { | |
| 449 set sql "SELECT * FROM t4 WHERE a = 'abc' AND b = 3;" | |
| 450 do_eqp_test 11.$tn.4 $sql {/t4 USING INDEX t4a/} | |
| 451 } else { | |
| 452 | |
| 453 set sql "SELECT * FROM t4 WHERE a = 'abc' COLLATE nocase AND b = 3;" | |
| 454 do_eqp_test 11.$tn.5 $sql {/t4 USING INDEX t4a/} | |
| 455 | |
| 456 set sql "SELECT * FROM t4 WHERE a COLLATE nocase = 'abc' AND b = 3;" | |
| 457 do_eqp_test 11.$tn.6 $sql {/t4 USING INDEX t4a/} | |
| 458 } | |
| 459 } | |
| 460 | |
| 461 #------------------------------------------------------------------------- | |
| 462 # Test that nothing untoward happens if the stat3 table contains entries | |
| 463 # for indexes that do not exist. Or NULL values in the idx column. | |
| 464 # Or NULL values in any of the other columns. | |
| 465 # | |
| 466 drop_all_tables | |
| 467 do_execsql_test 15.1 { | |
| 468 CREATE TABLE x1(a, b, UNIQUE(a, b)); | |
| 469 INSERT INTO x1 VALUES(1, 2); | |
| 470 INSERT INTO x1 VALUES(3, 4); | |
| 471 INSERT INTO x1 VALUES(5, 6); | |
| 472 ANALYZE; | |
| 473 INSERT INTO sqlite_stat3 VALUES(NULL, NULL, NULL, NULL, NULL, NULL); | |
| 474 } | |
| 475 db close | |
| 476 sqlite3 db test.db | |
| 477 do_execsql_test 15.2 { SELECT * FROM x1 } {1 2 3 4 5 6} | |
| 478 | |
| 479 do_execsql_test 15.3 { | |
| 480 INSERT INTO sqlite_stat3 VALUES(42, 42, 42, 42, 42, 42); | |
| 481 } | |
| 482 db close | |
| 483 sqlite3 db test.db | |
| 484 do_execsql_test 15.4 { SELECT * FROM x1 } {1 2 3 4 5 6} | |
| 485 | |
| 486 do_execsql_test 15.5 { | |
| 487 UPDATE sqlite_stat1 SET stat = NULL; | |
| 488 } | |
| 489 db close | |
| 490 sqlite3 db test.db | |
| 491 do_execsql_test 15.6 { SELECT * FROM x1 } {1 2 3 4 5 6} | |
| 492 | |
| 493 do_execsql_test 15.7 { | |
| 494 ANALYZE; | |
| 495 UPDATE sqlite_stat1 SET tbl = 'no such tbl'; | |
| 496 } | |
| 497 db close | |
| 498 sqlite3 db test.db | |
| 499 do_execsql_test 15.8 { SELECT * FROM x1 } {1 2 3 4 5 6} | |
| 500 | |
| 501 do_execsql_test 15.9 { | |
| 502 ANALYZE; | |
| 503 UPDATE sqlite_stat3 SET neq = NULL, nlt=NULL, ndlt=NULL; | |
| 504 } | |
| 505 db close | |
| 506 sqlite3 db test.db | |
| 507 do_execsql_test 15.10 { SELECT * FROM x1 } {1 2 3 4 5 6} | |
| 508 | |
| 509 # This is just for coverage.... | |
| 510 do_execsql_test 15.11 { | |
| 511 ANALYZE; | |
| 512 UPDATE sqlite_stat1 SET stat = stat || ' unordered'; | |
| 513 } | |
| 514 db close | |
| 515 sqlite3 db test.db | |
| 516 do_execsql_test 15.12 { SELECT * FROM x1 } {1 2 3 4 5 6} | |
| 517 | |
| 518 #------------------------------------------------------------------------- | |
| 519 # Test that allocations used for sqlite_stat3 samples are included in | |
| 520 # the quantity returned by SQLITE_DBSTATUS_SCHEMA_USED. | |
| 521 # | |
| 522 set one [string repeat x 1000] | |
| 523 set two [string repeat x 2000] | |
| 524 do_test 16.1 { | |
| 525 reset_db | |
| 526 execsql { | |
| 527 CREATE TABLE t1(a, UNIQUE(a)); | |
| 528 INSERT INTO t1 VALUES($one); | |
| 529 ANALYZE; | |
| 530 } | |
| 531 set nByte [lindex [sqlite3_db_status db SCHEMA_USED 0] 1] | |
| 532 | |
| 533 reset_db | |
| 534 execsql { | |
| 535 CREATE TABLE t1(a, UNIQUE(a)); | |
| 536 INSERT INTO t1 VALUES($two); | |
| 537 ANALYZE; | |
| 538 } | |
| 539 set nByte2 [lindex [sqlite3_db_status db SCHEMA_USED 0] 1] | |
| 540 | |
| 541 expr {$nByte2 > $nByte+950 && $nByte2 < $nByte+1050} | |
| 542 } {1} | |
| 543 | |
| 544 #------------------------------------------------------------------------- | |
| 545 # Test that stat3 data may be used with partial indexes. | |
| 546 # | |
| 547 do_test 17.1 { | |
| 548 reset_db | |
| 549 execsql { | |
| 550 CREATE TABLE t1(a, b, c, d); | |
| 551 CREATE INDEX i1 ON t1(a, b) WHERE d IS NOT NULL; | |
| 552 INSERT INTO t1 VALUES(-1, -1, -1, NULL); | |
| 553 INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; | |
| 554 INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; | |
| 555 INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; | |
| 556 INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; | |
| 557 INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; | |
| 558 INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; | |
| 559 } | |
| 560 | |
| 561 for {set i 0} {$i < 32} {incr i} { | |
| 562 execsql { INSERT INTO t1 VALUES($i%2, $b, $i/2, 'abc') } | |
| 563 } | |
| 564 execsql {ANALYZE main.t1} | |
| 565 } {} | |
| 566 | |
| 567 do_catchsql_test 17.1.2 { | |
| 568 ANALYZE temp.t1; | |
| 569 } {1 {no such table: temp.t1}} | |
| 570 | |
| 571 do_eqp_test 17.2 { | |
| 572 SELECT * FROM t1 WHERE d IS NOT NULL AND a=0; | |
| 573 } {/USING INDEX i1/} | |
| 574 do_eqp_test 17.3 { | |
| 575 SELECT * FROM t1 WHERE d IS NOT NULL AND a=0; | |
| 576 } {/USING INDEX i1/} | |
| 577 | |
| 578 do_execsql_test 17.4 { | |
| 579 CREATE INDEX i2 ON t1(c) WHERE d IS NOT NULL; | |
| 580 ANALYZE main.i2; | |
| 581 } | |
| 582 do_eqp_test 17.5 { | |
| 583 SELECT * FROM t1 WHERE d IS NOT NULL AND a=0; | |
| 584 } {/USING INDEX i1/} | |
| 585 do_eqp_test 17.6 { | |
| 586 SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=0 AND c=10; | |
| 587 } {/USING INDEX i2/} | |
| 588 | |
| 589 #------------------------------------------------------------------------- | |
| 590 # | |
| 591 do_test 18.1 { | |
| 592 reset_db | |
| 593 execsql { | |
| 594 CREATE TABLE t1(a, b); | |
| 595 CREATE INDEX i1 ON t1(a, b); | |
| 596 } | |
| 597 for {set i 0} {$i < 9} {incr i} { | |
| 598 execsql { | |
| 599 INSERT INTO t1 VALUES($i, 0); | |
| 600 INSERT INTO t1 VALUES($i, 0); | |
| 601 INSERT INTO t1 VALUES($i, 0); | |
| 602 INSERT INTO t1 VALUES($i, 0); | |
| 603 INSERT INTO t1 VALUES($i, 0); | |
| 604 INSERT INTO t1 VALUES($i, 0); | |
| 605 INSERT INTO t1 VALUES($i, 0); | |
| 606 INSERT INTO t1 VALUES($i, 0); | |
| 607 INSERT INTO t1 VALUES($i, 0); | |
| 608 INSERT INTO t1 VALUES($i, 0); | |
| 609 INSERT INTO t1 VALUES($i, 0); | |
| 610 INSERT INTO t1 VALUES($i, 0); | |
| 611 INSERT INTO t1 VALUES($i, 0); | |
| 612 INSERT INTO t1 VALUES($i, 0); | |
| 613 INSERT INTO t1 VALUES($i, 0); | |
| 614 } | |
| 615 } | |
| 616 execsql ANALYZE | |
| 617 execsql { SELECT count(*) FROM sqlite_stat3 } | |
| 618 } {9} | |
| 619 | |
| 620 #------------------------------------------------------------------------- | |
| 621 # For coverage. | |
| 622 # | |
| 623 ifcapable view { | |
| 624 do_test 19.1 { | |
| 625 reset_db | |
| 626 execsql { | |
| 627 CREATE TABLE t1(x, y); | |
| 628 CREATE INDEX i1 ON t1(x, y); | |
| 629 CREATE VIEW v1 AS SELECT * FROM t1; | |
| 630 ANALYZE; | |
| 631 } | |
| 632 } {} | |
| 633 } | |
| 634 ifcapable auth { | |
| 635 proc authproc {op args} { | |
| 636 if {$op == "SQLITE_ANALYZE"} { return "SQLITE_DENY" } | |
| 637 return "SQLITE_OK" | |
| 638 } | |
| 639 do_test 19.2 { | |
| 640 reset_db | |
| 641 db auth authproc | |
| 642 execsql { | |
| 643 CREATE TABLE t1(x, y); | |
| 644 CREATE VIEW v1 AS SELECT * FROM t1; | |
| 645 } | |
| 646 catchsql ANALYZE | |
| 647 } {1 {not authorized}} | |
| 648 } | |
| 649 | |
| 650 #------------------------------------------------------------------------- | |
| 651 # | |
| 652 reset_db | |
| 653 proc r {args} { expr rand() } | |
| 654 db func r r | |
| 655 db func lrange lrange | |
| 656 do_test 20.1 { | |
| 657 execsql { | |
| 658 CREATE TABLE t1(a,b,c,d); | |
| 659 CREATE INDEX i1 ON t1(a,b,c,d); | |
| 660 } | |
| 661 for {set i 0} {$i < 16} {incr i} { | |
| 662 execsql { | |
| 663 INSERT INTO t1 VALUES($i, r(), r(), r()); | |
| 664 INSERT INTO t1 VALUES($i, $i, r(), r()); | |
| 665 INSERT INTO t1 VALUES($i, $i, $i, r()); | |
| 666 INSERT INTO t1 VALUES($i, $i, $i, $i); | |
| 667 INSERT INTO t1 VALUES($i, $i, $i, $i); | |
| 668 INSERT INTO t1 VALUES($i, $i, $i, r()); | |
| 669 INSERT INTO t1 VALUES($i, $i, r(), r()); | |
| 670 INSERT INTO t1 VALUES($i, r(), r(), r()); | |
| 671 } | |
| 672 } | |
| 673 } {} | |
| 674 do_execsql_test 20.2 { ANALYZE } | |
| 675 for {set i 0} {$i<16} {incr i} { | |
| 676 set val $i | |
| 677 do_execsql_test 20.3.$i { | |
| 678 SELECT count(*) FROM sqlite_stat3 WHERE sample=$val | |
| 679 } {1} | |
| 680 } | |
| 681 | |
| 682 finish_test | |
| 683 | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2846743003:
[sql] Remove SQLite 3.10.2 reference directory. (Closed)
