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_stat4 13# functionality is working. 14# 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18set testprefix analyze9 19 20ifcapable !stat4 { 21 finish_test 22 return 23} 24 25proc s {blob} { 26 set ret "" 27 binary scan $blob c* bytes 28 foreach b $bytes { 29 set t [binary format c $b] 30 if {[string is print $t]} { 31 append ret $t 32 } else { 33 append ret . 34 } 35 } 36 return $ret 37} 38db function s s 39 40do_execsql_test 1.0 { 41 CREATE TABLE t1(a TEXT, b TEXT); 42 INSERT INTO t1 VALUES('(0)', '(0)'); 43 INSERT INTO t1 VALUES('(1)', '(1)'); 44 INSERT INTO t1 VALUES('(2)', '(2)'); 45 INSERT INTO t1 VALUES('(3)', '(3)'); 46 INSERT INTO t1 VALUES('(4)', '(4)'); 47 CREATE INDEX i1 ON t1(a, b); 48} {} 49 50 51do_execsql_test 1.1 { 52 ANALYZE; 53} {} 54 55do_execsql_test 1.2 { 56 SELECT tbl,idx,nEq,nLt,nDLt,test_decode(sample) FROM sqlite_stat4; 57} { 58 t1 i1 {1 1 1} {0 0 0} {0 0 0} {(0) (0) 1} 59 t1 i1 {1 1 1} {1 1 1} {1 1 1} {(1) (1) 2} 60 t1 i1 {1 1 1} {2 2 2} {2 2 2} {(2) (2) 3} 61 t1 i1 {1 1 1} {3 3 3} {3 3 3} {(3) (3) 4} 62 t1 i1 {1 1 1} {4 4 4} {4 4 4} {(4) (4) 5} 63} 64 65if {[permutation] != "utf16"} { 66 do_execsql_test 1.3 { 67 SELECT tbl,idx,nEq,nLt,nDLt,s(sample) FROM sqlite_stat4; 68 } { 69 t1 i1 {1 1 1} {0 0 0} {0 0 0} ....(0)(0) 70 t1 i1 {1 1 1} {1 1 1} {1 1 1} ....(1)(1). 71 t1 i1 {1 1 1} {2 2 2} {2 2 2} ....(2)(2). 72 t1 i1 {1 1 1} {3 3 3} {3 3 3} ....(3)(3). 73 t1 i1 {1 1 1} {4 4 4} {4 4 4} ....(4)(4). 74 } 75} 76 77 78#------------------------------------------------------------------------- 79# This is really just to test SQL user function "test_decode". 80# 81reset_db 82do_execsql_test 2.1 { 83 CREATE TABLE t1(a, b, c); 84 INSERT INTO t1 VALUES('some text', 14, NULL); 85 INSERT INTO t1 VALUES(22.0, NULL, x'656667'); 86 CREATE INDEX i1 ON t1(a, b, c); 87 ANALYZE; 88 SELECT test_decode(sample) FROM sqlite_stat4; 89} { 90 {22.0 NULL x'656667' 2} 91 {{some text} 14 NULL 1} 92} 93 94#------------------------------------------------------------------------- 95# 96reset_db 97do_execsql_test 3.1 { 98 CREATE TABLE t2(a, b); 99 CREATE INDEX i2 ON t2(a, b); 100 BEGIN; 101} 102 103do_test 3.2 { 104 for {set i 0} {$i < 1000} {incr i} { 105 set a [expr $i / 10] 106 set b [expr int(rand() * 15.0)] 107 execsql { INSERT INTO t2 VALUES($a, $b) } 108 } 109 execsql COMMIT 110} {} 111 112db func lindex lindex 113 114# Each value of "a" occurs exactly 10 times in the table. 115# 116do_execsql_test 3.3.1 { 117 SELECT count(*) FROM t2 GROUP BY a; 118} [lrange [string repeat "10 " 100] 0 99] 119 120# The first element in the "nEq" list of all samples should therefore be 10. 121# 122do_execsql_test 3.3.2 { 123 ANALYZE; 124 SELECT lindex(nEq, 0) FROM sqlite_stat4; 125} [lrange [string repeat "10 " 100] 0 23] 126 127#------------------------------------------------------------------------- 128# 129do_execsql_test 3.4 { 130 DROP TABLE IF EXISTS t1; 131 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); 132 INSERT INTO t1 VALUES(1, 1, 'one-a'); 133 INSERT INTO t1 VALUES(11, 1, 'one-b'); 134 INSERT INTO t1 VALUES(21, 1, 'one-c'); 135 INSERT INTO t1 VALUES(31, 1, 'one-d'); 136 INSERT INTO t1 VALUES(41, 1, 'one-e'); 137 INSERT INTO t1 VALUES(51, 1, 'one-f'); 138 INSERT INTO t1 VALUES(61, 1, 'one-g'); 139 INSERT INTO t1 VALUES(71, 1, 'one-h'); 140 INSERT INTO t1 VALUES(81, 1, 'one-i'); 141 INSERT INTO t1 VALUES(91, 1, 'one-j'); 142 INSERT INTO t1 SELECT a+1,2,'two' || substr(c,4) FROM t1; 143 INSERT INTO t1 SELECT a+2,3,'three'||substr(c,4) FROM t1 WHERE c GLOB 'one-*'; 144 INSERT INTO t1 SELECT a+3,4,'four'||substr(c,4) FROM t1 WHERE c GLOB 'one-*'; 145 INSERT INTO t1 SELECT a+4,5,'five'||substr(c,4) FROM t1 WHERE c GLOB 'one-*'; 146 INSERT INTO t1 SELECT a+5,6,'six'||substr(c,4) FROM t1 WHERE c GLOB 'one-*'; 147 CREATE INDEX t1b ON t1(b); 148 ANALYZE; 149 SELECT c FROM t1 WHERE b=3 AND a BETWEEN 30 AND 60; 150} {three-d three-e three-f} 151 152 153#------------------------------------------------------------------------- 154# These tests verify that the sample selection for stat4 appears to be 155# working as designed. 156# 157 158reset_db 159db func lindex lindex 160db func lrange lrange 161 162do_execsql_test 4.0 { 163 DROP TABLE IF EXISTS t1; 164 CREATE TABLE t1(a, b, c); 165 CREATE INDEX i1 ON t1(c, b, a); 166} 167 168 169proc insert_filler_rows_n {iStart args} { 170 set A(-ncopy) 1 171 set A(-nval) 1 172 173 foreach {k v} $args { 174 if {[info exists A($k)]==0} { error "no such option: $k" } 175 set A($k) $v 176 } 177 if {[llength $args] % 2} { 178 error "option requires an argument: [lindex $args end]" 179 } 180 181 for {set i 0} {$i < $A(-nval)} {incr i} { 182 set iVal [expr $iStart+$i] 183 for {set j 0} {$j < $A(-ncopy)} {incr j} { 184 execsql { INSERT INTO t1 VALUES($iVal, $iVal, $iVal) } 185 } 186 } 187} 188 189do_test 4.1 { 190 execsql { BEGIN } 191 insert_filler_rows_n 0 -ncopy 10 -nval 19 192 insert_filler_rows_n 20 -ncopy 1 -nval 100 193 194 execsql { 195 INSERT INTO t1(c, b, a) VALUES(200, 1, 'a'); 196 INSERT INTO t1(c, b, a) VALUES(200, 1, 'b'); 197 INSERT INTO t1(c, b, a) VALUES(200, 1, 'c'); 198 199 INSERT INTO t1(c, b, a) VALUES(200, 2, 'e'); 200 INSERT INTO t1(c, b, a) VALUES(200, 2, 'f'); 201 202 INSERT INTO t1(c, b, a) VALUES(201, 3, 'g'); 203 INSERT INTO t1(c, b, a) VALUES(201, 4, 'h'); 204 205 ANALYZE; 206 SELECT count(*) FROM sqlite_stat4; 207 SELECT count(*) FROM t1; 208 } 209} {24 297} 210 211do_execsql_test 4.2 { 212 SELECT 213 neq, 214 lrange(nlt, 0, 2), 215 lrange(ndlt, 0, 2), 216 lrange(test_decode(sample), 0, 2) 217 FROM sqlite_stat4 218 ORDER BY rowid LIMIT 16; 219} { 220 {10 10 10 1} {0 0 0} {0 0 0} {0 0 0} 221 {10 10 10 1} {10 10 10} {1 1 1} {1 1 1} 222 {10 10 10 1} {20 20 20} {2 2 2} {2 2 2} 223 {10 10 10 1} {30 30 30} {3 3 3} {3 3 3} 224 {10 10 10 1} {40 40 40} {4 4 4} {4 4 4} 225 {10 10 10 1} {50 50 50} {5 5 5} {5 5 5} 226 {10 10 10 1} {60 60 60} {6 6 6} {6 6 6} 227 {10 10 10 1} {70 70 70} {7 7 7} {7 7 7} 228 {10 10 10 1} {80 80 80} {8 8 8} {8 8 8} 229 {10 10 10 1} {90 90 90} {9 9 9} {9 9 9} 230 {10 10 10 1} {100 100 100} {10 10 10} {10 10 10} 231 {10 10 10 1} {110 110 110} {11 11 11} {11 11 11} 232 {10 10 10 1} {120 120 120} {12 12 12} {12 12 12} 233 {10 10 10 1} {130 130 130} {13 13 13} {13 13 13} 234 {10 10 10 1} {140 140 140} {14 14 14} {14 14 14} 235 {10 10 10 1} {150 150 150} {15 15 15} {15 15 15} 236} 237 238do_execsql_test 4.3 { 239 SELECT 240 neq, 241 lrange(nlt, 0, 2), 242 lrange(ndlt, 0, 2), 243 lrange(test_decode(sample), 0, 1) 244 FROM sqlite_stat4 245 ORDER BY rowid DESC LIMIT 2; 246} { 247 {2 1 1 1} {295 296 296} {120 122 125} {201 4} 248 {5 3 1 1} {290 290 290} {119 119 119} {200 1} 249} 250 251do_execsql_test 4.4 { SELECT count(DISTINCT c) FROM t1 WHERE c<201 } 120 252do_execsql_test 4.5 { SELECT count(DISTINCT c) FROM t1 WHERE c<200 } 119 253 254# Check that the perioidic samples are present. 255do_execsql_test 4.6 { 256 SELECT count(*) FROM sqlite_stat4 257 WHERE lindex(test_decode(sample), 3) IN 258 ('34', '68', '102', '136', '170', '204', '238', '272') 259} {8} 260 261reset_db 262do_test 4.7 { 263 execsql { 264 BEGIN; 265 CREATE TABLE t1(o,t INTEGER PRIMARY KEY); 266 CREATE INDEX i1 ON t1(o); 267 } 268 for {set i 0} {$i<10000} {incr i [expr (($i<1000)?1:10)]} { 269 execsql { INSERT INTO t1 VALUES('x', $i) } 270 } 271 execsql { 272 COMMIT; 273 ANALYZE; 274 SELECT count(*) FROM sqlite_stat4; 275 } 276} {8} 277do_execsql_test 4.8 { 278 SELECT test_decode(sample) FROM sqlite_stat4; 279} { 280 {x 211} {x 423} {x 635} {x 847} 281 {x 1590} {x 3710} {x 5830} {x 7950} 282} 283 284 285#------------------------------------------------------------------------- 286# The following would cause a crash at one point. 287# 288reset_db 289do_execsql_test 5.1 { 290 PRAGMA encoding = 'utf-16'; 291 CREATE TABLE t0(v); 292 ANALYZE; 293} 294 295#------------------------------------------------------------------------- 296# This was also crashing (corrupt sqlite_stat4 table). 297# 298reset_db 299do_execsql_test 6.1 { 300 CREATE TABLE t1(a, b); 301 CREATE INDEX i1 ON t1(a); 302 CREATE INDEX i2 ON t1(b); 303 INSERT INTO t1 VALUES(1, 1); 304 INSERT INTO t1 VALUES(2, 2); 305 INSERT INTO t1 VALUES(3, 3); 306 INSERT INTO t1 VALUES(4, 4); 307 INSERT INTO t1 VALUES(5, 5); 308 ANALYZE; 309 PRAGMA writable_schema = 1; 310 CREATE TEMP TABLE x1 AS 311 SELECT tbl,idx,neq,nlt,ndlt,sample FROM sqlite_stat4 312 ORDER BY (rowid%5), rowid; 313 DELETE FROM sqlite_stat4; 314 INSERT INTO sqlite_stat4 SELECT * FROM x1; 315 PRAGMA writable_schema = 0; 316 ANALYZE sqlite_master; 317} 318do_execsql_test 6.2 { 319 SELECT * FROM t1 WHERE a = 'abc'; 320} 321 322#------------------------------------------------------------------------- 323# The following tests experiment with adding corrupted records to the 324# 'sample' column of the sqlite_stat4 table. 325# 326reset_db 327sqlite3_db_config_lookaside db 0 0 0 328 329do_execsql_test 7.1 { 330 CREATE TABLE t1(a, b); 331 CREATE INDEX i1 ON t1(a, b); 332 INSERT INTO t1 VALUES(1, 1); 333 INSERT INTO t1 VALUES(2, 2); 334 INSERT INTO t1 VALUES(3, 3); 335 INSERT INTO t1 VALUES(4, 4); 336 INSERT INTO t1 VALUES(5, 5); 337 ANALYZE; 338 UPDATE sqlite_stat4 SET sample = X'' WHERE rowid = 1; 339 ANALYZE sqlite_master; 340} 341 342do_execsql_test 7.2 { 343 UPDATE sqlite_stat4 SET sample = X'FFFF'; 344 ANALYZE sqlite_master; 345 SELECT * FROM t1 WHERE a = 1; 346} {1 1} 347 348do_execsql_test 7.3 { 349 ANALYZE; 350 UPDATE sqlite_stat4 SET neq = '0 0 0'; 351 ANALYZE sqlite_master; 352 SELECT * FROM t1 WHERE a = 1; 353} {1 1} 354 355do_execsql_test 7.4 { 356 ANALYZE; 357 UPDATE sqlite_stat4 SET ndlt = '0 0 0'; 358 ANALYZE sqlite_master; 359 SELECT * FROM t1 WHERE a = 3; 360} {3 3} 361 362do_execsql_test 7.5 { 363 ANALYZE; 364 UPDATE sqlite_stat4 SET nlt = '0 0 0'; 365 ANALYZE sqlite_master; 366 SELECT * FROM t1 WHERE a = 5; 367} {5 5} 368 369#------------------------------------------------------------------------- 370# 371reset_db 372do_execsql_test 8.1 { 373 CREATE TABLE t1(x TEXT); 374 CREATE INDEX i1 ON t1(x); 375 INSERT INTO t1 VALUES('1'); 376 INSERT INTO t1 VALUES('2'); 377 INSERT INTO t1 VALUES('3'); 378 INSERT INTO t1 VALUES('4'); 379 ANALYZE; 380} 381do_execsql_test 8.2 { 382 SELECT * FROM t1 WHERE x = 3; 383} {3} 384 385#------------------------------------------------------------------------- 386# Check that the bug fixed by [91733bc485] really is fixed. 387# 388reset_db 389do_execsql_test 9.1 { 390 CREATE TABLE t1(a, b, c, d, e); 391 CREATE INDEX i1 ON t1(a, b, c, d); 392 CREATE INDEX i2 ON t1(e); 393} 394do_test 9.2 { 395 execsql BEGIN; 396 for {set i 0} {$i < 100} {incr i} { 397 execsql "INSERT INTO t1 VALUES('x', 'y', 'z', $i, [expr $i/2])" 398 } 399 for {set i 0} {$i < 20} {incr i} { 400 execsql "INSERT INTO t1 VALUES('x', 'y', 'z', 101, $i)" 401 } 402 for {set i 102} {$i < 200} {incr i} { 403 execsql "INSERT INTO t1 VALUES('x', 'y', 'z', $i, [expr $i/2])" 404 } 405 execsql COMMIT 406 execsql ANALYZE 407} {} 408 409do_eqp_test 9.3.1 { 410 SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=101 AND e=5; 411} {/t1 USING INDEX i2/} 412do_eqp_test 9.3.2 { 413 SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=99 AND e=5; 414} {/t1 USING INDEX i1/} 415 416set value_d [expr 101] 417do_eqp_test 9.4.1 { 418 SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=$value_d AND e=5 419} {/t1 USING INDEX i2/} 420set value_d [expr 99] 421do_eqp_test 9.4.2 { 422 SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=$value_d AND e=5 423} {/t1 USING INDEX i1/} 424 425#------------------------------------------------------------------------- 426# Check that the planner takes stat4 data into account when considering 427# "IS NULL" and "IS NOT NULL" constraints. 428# 429do_execsql_test 10.1.1 { 430 DROP TABLE IF EXISTS t3; 431 CREATE TABLE t3(a, b); 432 CREATE INDEX t3a ON t3(a); 433 CREATE INDEX t3b ON t3(b); 434} 435do_test 10.1.2 { 436 for {set i 1} {$i < 100} {incr i} { 437 if {$i>90} { set a $i } else { set a NULL } 438 set b [expr $i % 5] 439 execsql "INSERT INTO t3 VALUES($a, $b)" 440 } 441 execsql ANALYZE 442} {} 443do_eqp_test 10.1.3 { 444 SELECT * FROM t3 WHERE a IS NULL AND b = 2 445} {/t3 USING INDEX t3b/} 446do_eqp_test 10.1.4 { 447 SELECT * FROM t3 WHERE a IS NOT NULL AND b = 2 448} {/t3 USING INDEX t3a/} 449 450do_execsql_test 10.2.1 { 451 DROP TABLE IF EXISTS t3; 452 CREATE TABLE t3(x, a, b); 453 CREATE INDEX t3a ON t3(x, a); 454 CREATE INDEX t3b ON t3(x, b); 455} 456do_test 10.2.2 { 457 for {set i 1} {$i < 100} {incr i} { 458 if {$i>90} { set a $i } else { set a NULL } 459 set b [expr $i % 5] 460 execsql "INSERT INTO t3 VALUES('xyz', $a, $b)" 461 } 462 execsql ANALYZE 463} {} 464do_eqp_test 10.2.3 { 465 SELECT * FROM t3 WHERE x = 'xyz' AND a IS NULL AND b = 2 466} {/t3 USING INDEX t3b/} 467do_eqp_test 10.2.4 { 468 SELECT * FROM t3 WHERE x = 'xyz' AND a IS NOT NULL AND b = 2 469} {/t3 USING INDEX t3a/} 470 471#------------------------------------------------------------------------- 472# Check that stat4 data is used correctly with non-default collation 473# sequences. 474# 475foreach {tn schema} { 476 1 { 477 CREATE TABLE t4(a COLLATE nocase, b); 478 CREATE INDEX t4a ON t4(a); 479 CREATE INDEX t4b ON t4(b); 480 } 481 2 { 482 CREATE TABLE t4(a, b); 483 CREATE INDEX t4a ON t4(a COLLATE nocase); 484 CREATE INDEX t4b ON t4(b); 485 } 486} { 487 drop_all_tables 488 do_test 11.$tn.1 { execsql $schema } {} 489 490 do_test 11.$tn.2 { 491 for {set i 0} {$i < 100} {incr i} { 492 if { ($i % 10)==0 } { set a ABC } else { set a DEF } 493 set b [expr $i % 5] 494 execsql { INSERT INTO t4 VALUES($a, $b) } 495 } 496 execsql ANALYZE 497 } {} 498 499 do_eqp_test 11.$tn.3 { 500 SELECT * FROM t4 WHERE a = 'def' AND b = 3; 501 } {/t4 USING INDEX t4b/} 502 503 if {$tn==1} { 504 set sql "SELECT * FROM t4 WHERE a = 'abc' AND b = 3;" 505 do_eqp_test 11.$tn.4 $sql {/t4 USING INDEX t4a/} 506 } else { 507 508 set sql "SELECT * FROM t4 WHERE a = 'abc' COLLATE nocase AND b = 3;" 509 do_eqp_test 11.$tn.5 $sql {/t4 USING INDEX t4a/} 510 511 set sql "SELECT * FROM t4 WHERE a COLLATE nocase = 'abc' AND b = 3;" 512 do_eqp_test 11.$tn.6 $sql {/t4 USING INDEX t4a/} 513 } 514} 515 516foreach {tn schema} { 517 1 { 518 CREATE TABLE t4(x, a COLLATE nocase, b); 519 CREATE INDEX t4a ON t4(x, a); 520 CREATE INDEX t4b ON t4(x, b); 521 } 522 2 { 523 CREATE TABLE t4(x, a, b); 524 CREATE INDEX t4a ON t4(x, a COLLATE nocase); 525 CREATE INDEX t4b ON t4(x, b); 526 } 527} { 528 drop_all_tables 529 do_test 12.$tn.1 { execsql $schema } {} 530 531 do_test 12.$tn.2 { 532 for {set i 0} {$i < 100} {incr i} { 533 if { ($i % 10)==0 } { set a ABC } else { set a DEF } 534 set b [expr $i % 5] 535 execsql { INSERT INTO t4 VALUES(X'abcdef', $a, $b) } 536 } 537 execsql ANALYZE 538 } {} 539 540 do_eqp_test 12.$tn.3 { 541 SELECT * FROM t4 WHERE x=X'abcdef' AND a = 'def' AND b = 3; 542 } {/t4 USING INDEX t4b/} 543 544 if {$tn==1} { 545 set sql "SELECT * FROM t4 WHERE x=X'abcdef' AND a = 'abc' AND b = 3;" 546 do_eqp_test 12.$tn.4 $sql {/t4 USING INDEX t4a/} 547 } else { 548 set sql { 549 SELECT * FROM t4 WHERE x=X'abcdef' AND a = 'abc' COLLATE nocase AND b = 3 550 } 551 do_eqp_test 12.$tn.5 $sql {/t4 USING INDEX t4a/} 552 set sql { 553 SELECT * FROM t4 WHERE x=X'abcdef' AND a COLLATE nocase = 'abc' AND b = 3 554 } 555 do_eqp_test 12.$tn.6 $sql {/t4 USING INDEX t4a/} 556 } 557} 558 559#------------------------------------------------------------------------- 560# Check that affinities are taken into account when using stat4 data to 561# estimate the number of rows scanned by a rowid constraint. 562# 563drop_all_tables 564do_test 13.1 { 565 execsql { 566 CREATE TABLE t1(a, b, c); 567 CREATE INDEX i1 ON t1(a); 568 CREATE INDEX i2 ON t1(b, c); 569 } 570 for {set i 0} {$i<100} {incr i} { 571 if {$i %2} {set a abc} else {set a def} 572 execsql { INSERT INTO t1(rowid, a, b, c) VALUES($i, $a, $i, $i) } 573 } 574 execsql ANALYZE 575} {} 576do_eqp_test 13.2.1 { 577 SELECT * FROM t1 WHERE a='abc' AND rowid<15 AND b<20 578} {/SEARCH TABLE t1 USING INDEX i1/} 579do_eqp_test 13.2.2 { 580 SELECT * FROM t1 WHERE a='abc' AND rowid<'15' AND b<20 581} {/SEARCH TABLE t1 USING INDEX i1/} 582do_eqp_test 13.3.1 { 583 SELECT * FROM t1 WHERE a='abc' AND rowid<100 AND b<20 584} {/SEARCH TABLE t1 USING INDEX i2/} 585do_eqp_test 13.3.2 { 586 SELECT * FROM t1 WHERE a='abc' AND rowid<'100' AND b<20 587} {/SEARCH TABLE t1 USING INDEX i2/} 588 589#------------------------------------------------------------------------- 590# Check also that affinities are taken into account when using stat4 data 591# to estimate the number of rows scanned by any other constraint on a 592# column other than the leftmost. 593# 594drop_all_tables 595do_test 14.1 { 596 execsql { CREATE TABLE t1(a, b INTEGER, c) } 597 for {set i 0} {$i<100} {incr i} { 598 set c [expr $i % 3] 599 execsql { INSERT INTO t1 VALUES('ott', $i, $c) } 600 } 601 execsql { 602 CREATE INDEX i1 ON t1(a, b); 603 CREATE INDEX i2 ON t1(c); 604 ANALYZE; 605 } 606} {} 607do_eqp_test 13.2.1 { 608 SELECT * FROM t1 WHERE a='ott' AND b<10 AND c=1 609} {/SEARCH TABLE t1 USING INDEX i1/} 610do_eqp_test 13.2.2 { 611 SELECT * FROM t1 WHERE a='ott' AND b<'10' AND c=1 612} {/SEARCH TABLE t1 USING INDEX i1/} 613 614#------------------------------------------------------------------------- 615# By default, 16 non-periodic samples are collected for the stat4 table. 616# The following tests attempt to verify that the most common keys are 617# being collected. 618# 619proc check_stat4 {tn} { 620 db eval ANALYZE 621 db eval {SELECT a, b, c, d FROM t1} { 622 incr k($a) 623 incr k([list $a $b]) 624 incr k([list $a $b $c]) 625 if { [info exists k([list $a $b $c $d])]==0 } { incr nRow } 626 incr k([list $a $b $c $d]) 627 } 628 629 set L [list] 630 foreach key [array names k] { 631 lappend L [list $k($key) $key] 632 } 633 634 set nSample $nRow 635 if {$nSample>16} {set nSample 16} 636 637 set nThreshold [lindex [lsort -decr -integer -index 0 $L] [expr $nSample-1] 0] 638 foreach key [array names k] { 639 if {$k($key)>$nThreshold} { 640 set expect($key) 1 641 } 642 if {$k($key)==$nThreshold} { 643 set possible($key) 1 644 } 645 } 646 647 648 set nPossible [expr $nSample - [llength [array names expect]]] 649 650 #puts "EXPECT: [array names expect]" 651 #puts "POSSIBLE($nPossible/[array size possible]): [array names possible]" 652 #puts "HAVE: [db eval {SELECT test_decode(sample) FROM sqlite_stat4 WHERE idx='i1'}]" 653 654 db eval {SELECT test_decode(sample) AS s FROM sqlite_stat4 WHERE idx='i1'} { 655 set seen 0 656 for {set i 0} {$i<4} {incr i} { 657 unset -nocomplain expect([lrange $s 0 $i]) 658 if {[info exists possible([lrange $s 0 $i])]} { 659 set seen 1 660 unset -nocomplain possible([lrange $s 0 $i]) 661 } 662 } 663 if {$seen} {incr nPossible -1} 664 } 665 if {$nPossible<0} {set nPossible 0} 666 667 set res [list [llength [array names expect]] $nPossible] 668 uplevel [list do_test $tn [list set {} $res] {0 0}] 669} 670 671drop_all_tables 672do_test 14.1.1 { 673 execsql { 674 CREATE TABLE t1(a,b,c,d); 675 CREATE INDEX i1 ON t1(a,b,c,d); 676 } 677 for {set i 0} {$i < 160} {incr i} { 678 execsql { INSERT INTO t1 VALUES($i,$i,$i,$i) } 679 if {($i % 10)==0} { execsql { INSERT INTO t1 VALUES($i,$i,$i,$i) } } 680 } 681} {} 682check_stat4 14.1.2 683 684do_test 14.2.1 { 685 execsql { DELETE FROM t1 } 686 for {set i 0} {$i < 1600} {incr i} { 687 execsql { INSERT INTO t1 VALUES($i/10,$i/17,$i/27,$i/37) } 688 } 689} {} 690check_stat4 14.2.2 691 692do_test 14.3.1 { 693 for {set i 0} {$i < 10} {incr i} { 694 execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } 695 execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } 696 execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } 697 execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } 698 execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } 699 execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } 700 execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } 701 execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } 702 execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } 703 execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } 704 } 705} {} 706check_stat4 14.3.2 707 708do_test 14.4.1 { 709 execsql {DELETE FROM t1} 710 for {set i 1} {$i < 160} {incr i} { 711 set b [expr $i % 10] 712 if {$b==0 || $b==2} {set b 1} 713 execsql { INSERT INTO t1 VALUES($i/10,$b,$i,$i) } 714 } 715} {} 716check_stat4 14.4.2 717db func lrange lrange 718db func lindex lindex 719do_execsql_test 14.4.3 { 720 SELECT lrange(test_decode(sample), 0, 1) AS s FROM sqlite_stat4 721 WHERE lindex(s, 1)=='1' ORDER BY rowid 722} { 723 {0 1} {1 1} {2 1} {3 1} 724 {4 1} {5 1} {6 1} {7 1} 725 {8 1} {9 1} {10 1} {11 1} 726 {12 1} {13 1} {14 1} {15 1} 727} 728 729#------------------------------------------------------------------------- 730# Test that nothing untoward happens if the stat4 table contains entries 731# for indexes that do not exist. Or NULL values in the idx column. 732# Or NULL values in any of the other columns. 733# 734drop_all_tables 735do_execsql_test 15.1 { 736 CREATE TABLE x1(a, b, UNIQUE(a, b)); 737 INSERT INTO x1 VALUES(1, 2); 738 INSERT INTO x1 VALUES(3, 4); 739 INSERT INTO x1 VALUES(5, 6); 740 ANALYZE; 741 INSERT INTO sqlite_stat4 VALUES(NULL, NULL, NULL, NULL, NULL, NULL); 742} 743db close 744sqlite3 db test.db 745do_execsql_test 15.2 { SELECT * FROM x1 } {1 2 3 4 5 6} 746 747do_execsql_test 15.3 { 748 INSERT INTO sqlite_stat4 VALUES(42, 42, 42, 42, 42, 42); 749} 750db close 751sqlite3 db test.db 752do_execsql_test 15.4 { SELECT * FROM x1 } {1 2 3 4 5 6} 753 754do_execsql_test 15.5 { 755 UPDATE sqlite_stat1 SET stat = NULL; 756} 757db close 758sqlite3 db test.db 759do_execsql_test 15.6 { SELECT * FROM x1 } {1 2 3 4 5 6} 760 761do_execsql_test 15.7 { 762 ANALYZE; 763 UPDATE sqlite_stat1 SET tbl = 'no such tbl'; 764} 765db close 766sqlite3 db test.db 767do_execsql_test 15.8 { SELECT * FROM x1 } {1 2 3 4 5 6} 768 769do_execsql_test 15.9 { 770 ANALYZE; 771 UPDATE sqlite_stat4 SET neq = NULL, nlt=NULL, ndlt=NULL; 772} 773db close 774sqlite3 db test.db 775do_execsql_test 15.10 { SELECT * FROM x1 } {1 2 3 4 5 6} 776 777# This is just for coverage.... 778do_execsql_test 15.11 { 779 ANALYZE; 780 UPDATE sqlite_stat1 SET stat = stat || ' unordered'; 781} 782db close 783sqlite3 db test.db 784do_execsql_test 15.12 { SELECT * FROM x1 } {1 2 3 4 5 6} 785 786#------------------------------------------------------------------------- 787# Test that allocations used for sqlite_stat4 samples are included in 788# the quantity returned by SQLITE_DBSTATUS_SCHEMA_USED. 789# 790set one [string repeat x 1000] 791set two [string repeat x 2000] 792do_test 16.1 { 793 reset_db 794 execsql { 795 CREATE TABLE t1(a, UNIQUE(a)); 796 INSERT INTO t1 VALUES($one); 797 ANALYZE; 798 } 799 set nByte [lindex [sqlite3_db_status db SCHEMA_USED 0] 1] 800 801 reset_db 802 execsql { 803 CREATE TABLE t1(a, UNIQUE(a)); 804 INSERT INTO t1 VALUES($two); 805 ANALYZE; 806 } 807 set nByte2 [lindex [sqlite3_db_status db SCHEMA_USED 0] 1] 808 puts -nonewline " (nByte=$nByte nByte2=$nByte2)" 809 810 expr {$nByte2 > $nByte+900 && $nByte2 < $nByte+1100} 811} {1} 812 813#------------------------------------------------------------------------- 814# Test that stat4 data may be used with partial indexes. 815# 816do_test 17.1 { 817 reset_db 818 execsql { 819 CREATE TABLE t1(a, b, c, d); 820 CREATE INDEX i1 ON t1(a, b) WHERE d IS NOT NULL; 821 INSERT INTO t1 VALUES(-1, -1, -1, NULL); 822 INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; 823 INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; 824 INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; 825 INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; 826 INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; 827 INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; 828 } 829 830 for {set i 0} {$i < 32} {incr i} { 831 if {$i<8} {set b 0} else { set b $i } 832 execsql { INSERT INTO t1 VALUES($i%2, $b, $i/2, 'abc') } 833 } 834 execsql {ANALYZE main.t1} 835} {} 836 837do_catchsql_test 17.1.2 { 838 ANALYZE temp.t1; 839} {1 {no such table: temp.t1}} 840 841do_eqp_test 17.2 { 842 SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=10 AND c=10; 843} {/USING INDEX i1/} 844do_eqp_test 17.3 { 845 SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=0 AND c=10; 846} {/USING INDEX i1/} 847 848do_execsql_test 17.4 { 849 CREATE INDEX i2 ON t1(c, d); 850 ANALYZE main.i2; 851} 852do_eqp_test 17.5 { 853 SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=10 AND c=10; 854} {/USING INDEX i1/} 855do_eqp_test 17.6 { 856 SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=0 AND c=10; 857} {/USING INDEX i2/} 858 859#------------------------------------------------------------------------- 860# 861do_test 18.1 { 862 reset_db 863 execsql { 864 CREATE TABLE t1(a, b); 865 CREATE INDEX i1 ON t1(a, b); 866 } 867 for {set i 0} {$i < 9} {incr i} { 868 execsql { 869 INSERT INTO t1 VALUES($i, 0); 870 INSERT INTO t1 VALUES($i, 0); 871 INSERT INTO t1 VALUES($i, 0); 872 INSERT INTO t1 VALUES($i, 0); 873 INSERT INTO t1 VALUES($i, 0); 874 INSERT INTO t1 VALUES($i, 0); 875 INSERT INTO t1 VALUES($i, 0); 876 INSERT INTO t1 VALUES($i, 0); 877 INSERT INTO t1 VALUES($i, 0); 878 INSERT INTO t1 VALUES($i, 0); 879 INSERT INTO t1 VALUES($i, 0); 880 INSERT INTO t1 VALUES($i, 0); 881 INSERT INTO t1 VALUES($i, 0); 882 INSERT INTO t1 VALUES($i, 0); 883 INSERT INTO t1 VALUES($i, 0); 884 } 885 } 886 execsql ANALYZE 887 execsql { SELECT count(*) FROM sqlite_stat4 } 888} {9} 889 890#------------------------------------------------------------------------- 891# For coverage. 892# 893ifcapable view { 894 do_test 19.1 { 895 reset_db 896 execsql { 897 CREATE TABLE t1(x, y); 898 CREATE INDEX i1 ON t1(x, y); 899 CREATE VIEW v1 AS SELECT * FROM t1; 900 ANALYZE; 901 } 902 } {} 903} 904ifcapable auth { 905 proc authproc {op args} { 906 if {$op == "SQLITE_ANALYZE"} { return "SQLITE_DENY" } 907 return "SQLITE_OK" 908 } 909 do_test 19.2 { 910 reset_db 911 db auth authproc 912 execsql { 913 CREATE TABLE t1(x, y); 914 CREATE VIEW v1 AS SELECT * FROM t1; 915 } 916 catchsql ANALYZE 917 } {1 {not authorized}} 918} 919 920#------------------------------------------------------------------------- 921# 922reset_db 923proc r {args} { expr rand() } 924db func r r 925db func lrange lrange 926do_test 20.1 { 927 execsql { 928 CREATE TABLE t1(a,b,c,d); 929 CREATE INDEX i1 ON t1(a,b,c,d); 930 } 931 for {set i 0} {$i < 16} {incr i} { 932 execsql { 933 INSERT INTO t1 VALUES($i, r(), r(), r()); 934 INSERT INTO t1 VALUES($i, $i, r(), r()); 935 INSERT INTO t1 VALUES($i, $i, $i, r()); 936 INSERT INTO t1 VALUES($i, $i, $i, $i); 937 INSERT INTO t1 VALUES($i, $i, $i, $i); 938 INSERT INTO t1 VALUES($i, $i, $i, r()); 939 INSERT INTO t1 VALUES($i, $i, r(), r()); 940 INSERT INTO t1 VALUES($i, r(), r(), r()); 941 } 942 } 943} {} 944do_execsql_test 20.2 { ANALYZE } 945for {set i 0} {$i<16} {incr i} { 946 set val "$i $i $i $i" 947 do_execsql_test 20.3.$i { 948 SELECT count(*) FROM sqlite_stat4 949 WHERE lrange(test_decode(sample), 0, 3)=$val 950 } {1} 951} 952 953finish_test 954