| 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_stat4 | |
| 13 # functionality is working. | |
| 14 # | |
| 15 | |
| 16 set testdir [file dirname $argv0] | |
| 17 source $testdir/tester.tcl | |
| 18 set testprefix analyze9 | |
| 19 | |
| 20 ifcapable !stat4 { | |
| 21 finish_test | |
| 22 return | |
| 23 } | |
| 24 | |
| 25 proc 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 } | |
| 38 db function s s | |
| 39 | |
| 40 do_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 | |
| 51 do_execsql_test 1.1 { | |
| 52 ANALYZE; | |
| 53 } {} | |
| 54 | |
| 55 do_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 | |
| 65 if {[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 # | |
| 81 reset_db | |
| 82 do_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 # | |
| 96 reset_db | |
| 97 do_execsql_test 3.1 { | |
| 98 CREATE TABLE t2(a, b); | |
| 99 CREATE INDEX i2 ON t2(a, b); | |
| 100 BEGIN; | |
| 101 } | |
| 102 | |
| 103 do_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 | |
| 112 db func lindex lindex | |
| 113 | |
| 114 # Each value of "a" occurs exactly 10 times in the table. | |
| 115 # | |
| 116 do_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 # | |
| 122 do_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 # | |
| 129 do_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 | |
| 158 reset_db | |
| 159 db func lindex lindex | |
| 160 db func lrange lrange | |
| 161 | |
| 162 do_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 | |
| 169 proc 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 | |
| 189 do_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 | |
| 211 do_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 | |
| 238 do_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 | |
| 251 do_execsql_test 4.4 { SELECT count(DISTINCT c) FROM t1 WHERE c<201 } 120 | |
| 252 do_execsql_test 4.5 { SELECT count(DISTINCT c) FROM t1 WHERE c<200 } 119 | |
| 253 | |
| 254 # Check that the perioidic samples are present. | |
| 255 do_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 | |
| 261 reset_db | |
| 262 do_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} | |
| 277 do_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 # | |
| 288 reset_db | |
| 289 do_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 # | |
| 298 reset_db | |
| 299 do_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 } | |
| 318 do_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 # | |
| 326 reset_db | |
| 327 sqlite3_db_config_lookaside db 0 0 0 | |
| 328 | |
| 329 database_may_be_corrupt | |
| 330 do_execsql_test 7.1 { | |
| 331 CREATE TABLE t1(a, b); | |
| 332 CREATE INDEX i1 ON t1(a, b); | |
| 333 INSERT INTO t1 VALUES(1, 1); | |
| 334 INSERT INTO t1 VALUES(2, 2); | |
| 335 INSERT INTO t1 VALUES(3, 3); | |
| 336 INSERT INTO t1 VALUES(4, 4); | |
| 337 INSERT INTO t1 VALUES(5, 5); | |
| 338 ANALYZE; | |
| 339 UPDATE sqlite_stat4 SET sample = X'' WHERE rowid = 1; | |
| 340 ANALYZE sqlite_master; | |
| 341 } | |
| 342 | |
| 343 do_execsql_test 7.2 { | |
| 344 UPDATE sqlite_stat4 SET sample = X'FFFF'; | |
| 345 ANALYZE sqlite_master; | |
| 346 SELECT * FROM t1 WHERE a = 1; | |
| 347 } {1 1} | |
| 348 | |
| 349 do_execsql_test 7.3 { | |
| 350 ANALYZE; | |
| 351 UPDATE sqlite_stat4 SET neq = '0 0 0'; | |
| 352 ANALYZE sqlite_master; | |
| 353 SELECT * FROM t1 WHERE a = 1; | |
| 354 } {1 1} | |
| 355 | |
| 356 do_execsql_test 7.4 { | |
| 357 ANALYZE; | |
| 358 UPDATE sqlite_stat4 SET ndlt = '0 0 0'; | |
| 359 ANALYZE sqlite_master; | |
| 360 SELECT * FROM t1 WHERE a = 3; | |
| 361 } {3 3} | |
| 362 | |
| 363 do_execsql_test 7.5 { | |
| 364 ANALYZE; | |
| 365 UPDATE sqlite_stat4 SET nlt = '0 0 0'; | |
| 366 ANALYZE sqlite_master; | |
| 367 SELECT * FROM t1 WHERE a = 5; | |
| 368 } {5 5} | |
| 369 | |
| 370 database_never_corrupt | |
| 371 | |
| 372 #------------------------------------------------------------------------- | |
| 373 # | |
| 374 reset_db | |
| 375 do_execsql_test 8.1 { | |
| 376 CREATE TABLE t1(x TEXT); | |
| 377 CREATE INDEX i1 ON t1(x); | |
| 378 INSERT INTO t1 VALUES('1'); | |
| 379 INSERT INTO t1 VALUES('2'); | |
| 380 INSERT INTO t1 VALUES('3'); | |
| 381 INSERT INTO t1 VALUES('4'); | |
| 382 ANALYZE; | |
| 383 } | |
| 384 do_execsql_test 8.2 { | |
| 385 SELECT * FROM t1 WHERE x = 3; | |
| 386 } {3} | |
| 387 | |
| 388 #------------------------------------------------------------------------- | |
| 389 # Check that the bug fixed by [91733bc485] really is fixed. | |
| 390 # | |
| 391 reset_db | |
| 392 do_execsql_test 9.1 { | |
| 393 CREATE TABLE t1(a, b, c, d, e); | |
| 394 CREATE INDEX i1 ON t1(a, b, c, d); | |
| 395 CREATE INDEX i2 ON t1(e); | |
| 396 } | |
| 397 do_test 9.2 { | |
| 398 execsql BEGIN; | |
| 399 for {set i 0} {$i < 100} {incr i} { | |
| 400 execsql "INSERT INTO t1 VALUES('x', 'y', 'z', $i, [expr $i/2])" | |
| 401 } | |
| 402 for {set i 0} {$i < 20} {incr i} { | |
| 403 execsql "INSERT INTO t1 VALUES('x', 'y', 'z', 101, $i)" | |
| 404 } | |
| 405 for {set i 102} {$i < 200} {incr i} { | |
| 406 execsql "INSERT INTO t1 VALUES('x', 'y', 'z', $i, [expr $i/2])" | |
| 407 } | |
| 408 execsql COMMIT | |
| 409 execsql ANALYZE | |
| 410 } {} | |
| 411 | |
| 412 do_eqp_test 9.3.1 { | |
| 413 SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=101 AND e=5; | |
| 414 } {/t1 USING INDEX i2/} | |
| 415 do_eqp_test 9.3.2 { | |
| 416 SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=99 AND e=5; | |
| 417 } {/t1 USING INDEX i1/} | |
| 418 | |
| 419 set value_d [expr 101] | |
| 420 do_eqp_test 9.4.1 { | |
| 421 SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=$value_d AND e=5 | |
| 422 } {/t1 USING INDEX i2/} | |
| 423 set value_d [expr 99] | |
| 424 do_eqp_test 9.4.2 { | |
| 425 SELECT * FROM t1 WHERE a='x' AND b='y' AND c='z' AND d=$value_d AND e=5 | |
| 426 } {/t1 USING INDEX i1/} | |
| 427 | |
| 428 #------------------------------------------------------------------------- | |
| 429 # Check that the planner takes stat4 data into account when considering | |
| 430 # "IS NULL" and "IS NOT NULL" constraints. | |
| 431 # | |
| 432 do_execsql_test 10.1.1 { | |
| 433 DROP TABLE IF EXISTS t3; | |
| 434 CREATE TABLE t3(a, b); | |
| 435 CREATE INDEX t3a ON t3(a); | |
| 436 CREATE INDEX t3b ON t3(b); | |
| 437 } | |
| 438 do_test 10.1.2 { | |
| 439 for {set i 1} {$i < 100} {incr i} { | |
| 440 if {$i>90} { set a $i } else { set a NULL } | |
| 441 set b [expr $i % 5] | |
| 442 execsql "INSERT INTO t3 VALUES($a, $b)" | |
| 443 } | |
| 444 execsql ANALYZE | |
| 445 } {} | |
| 446 do_eqp_test 10.1.3 { | |
| 447 SELECT * FROM t3 WHERE a IS NULL AND b = 2 | |
| 448 } {/t3 USING INDEX t3b/} | |
| 449 do_eqp_test 10.1.4 { | |
| 450 SELECT * FROM t3 WHERE a IS NOT NULL AND b = 2 | |
| 451 } {/t3 USING INDEX t3a/} | |
| 452 | |
| 453 do_execsql_test 10.2.1 { | |
| 454 DROP TABLE IF EXISTS t3; | |
| 455 CREATE TABLE t3(x, a, b); | |
| 456 CREATE INDEX t3a ON t3(x, a); | |
| 457 CREATE INDEX t3b ON t3(x, b); | |
| 458 } | |
| 459 do_test 10.2.2 { | |
| 460 for {set i 1} {$i < 100} {incr i} { | |
| 461 if {$i>90} { set a $i } else { set a NULL } | |
| 462 set b [expr $i % 5] | |
| 463 execsql "INSERT INTO t3 VALUES('xyz', $a, $b)" | |
| 464 } | |
| 465 execsql ANALYZE | |
| 466 } {} | |
| 467 do_eqp_test 10.2.3 { | |
| 468 SELECT * FROM t3 WHERE x = 'xyz' AND a IS NULL AND b = 2 | |
| 469 } {/t3 USING INDEX t3b/} | |
| 470 do_eqp_test 10.2.4 { | |
| 471 SELECT * FROM t3 WHERE x = 'xyz' AND a IS NOT NULL AND b = 2 | |
| 472 } {/t3 USING INDEX t3a/} | |
| 473 | |
| 474 #------------------------------------------------------------------------- | |
| 475 # Check that stat4 data is used correctly with non-default collation | |
| 476 # sequences. | |
| 477 # | |
| 478 foreach {tn schema} { | |
| 479 1 { | |
| 480 CREATE TABLE t4(a COLLATE nocase, b); | |
| 481 CREATE INDEX t4a ON t4(a); | |
| 482 CREATE INDEX t4b ON t4(b); | |
| 483 } | |
| 484 2 { | |
| 485 CREATE TABLE t4(a, b); | |
| 486 CREATE INDEX t4a ON t4(a COLLATE nocase); | |
| 487 CREATE INDEX t4b ON t4(b); | |
| 488 } | |
| 489 } { | |
| 490 drop_all_tables | |
| 491 do_test 11.$tn.1 { execsql $schema } {} | |
| 492 | |
| 493 do_test 11.$tn.2 { | |
| 494 for {set i 0} {$i < 100} {incr i} { | |
| 495 if { ($i % 10)==0 } { set a ABC } else { set a DEF } | |
| 496 set b [expr $i % 5] | |
| 497 execsql { INSERT INTO t4 VALUES($a, $b) } | |
| 498 } | |
| 499 execsql ANALYZE | |
| 500 } {} | |
| 501 | |
| 502 do_eqp_test 11.$tn.3 { | |
| 503 SELECT * FROM t4 WHERE a = 'def' AND b = 3; | |
| 504 } {/t4 USING INDEX t4b/} | |
| 505 | |
| 506 if {$tn==1} { | |
| 507 set sql "SELECT * FROM t4 WHERE a = 'abc' AND b = 3;" | |
| 508 do_eqp_test 11.$tn.4 $sql {/t4 USING INDEX t4a/} | |
| 509 } else { | |
| 510 | |
| 511 set sql "SELECT * FROM t4 WHERE a = 'abc' COLLATE nocase AND b = 3;" | |
| 512 do_eqp_test 11.$tn.5 $sql {/t4 USING INDEX t4a/} | |
| 513 | |
| 514 set sql "SELECT * FROM t4 WHERE a COLLATE nocase = 'abc' AND b = 3;" | |
| 515 do_eqp_test 11.$tn.6 $sql {/t4 USING INDEX t4a/} | |
| 516 } | |
| 517 } | |
| 518 | |
| 519 foreach {tn schema} { | |
| 520 1 { | |
| 521 CREATE TABLE t4(x, a COLLATE nocase, b); | |
| 522 CREATE INDEX t4a ON t4(x, a); | |
| 523 CREATE INDEX t4b ON t4(x, b); | |
| 524 } | |
| 525 2 { | |
| 526 CREATE TABLE t4(x, a, b); | |
| 527 CREATE INDEX t4a ON t4(x, a COLLATE nocase); | |
| 528 CREATE INDEX t4b ON t4(x, b); | |
| 529 } | |
| 530 } { | |
| 531 drop_all_tables | |
| 532 do_test 12.$tn.1 { execsql $schema } {} | |
| 533 | |
| 534 do_test 12.$tn.2 { | |
| 535 for {set i 0} {$i < 100} {incr i} { | |
| 536 if { ($i % 10)==0 } { set a ABC } else { set a DEF } | |
| 537 set b [expr $i % 5] | |
| 538 execsql { INSERT INTO t4 VALUES(X'abcdef', $a, $b) } | |
| 539 } | |
| 540 execsql ANALYZE | |
| 541 } {} | |
| 542 | |
| 543 do_eqp_test 12.$tn.3 { | |
| 544 SELECT * FROM t4 WHERE x=X'abcdef' AND a = 'def' AND b = 3; | |
| 545 } {/t4 USING INDEX t4b/} | |
| 546 | |
| 547 if {$tn==1} { | |
| 548 set sql "SELECT * FROM t4 WHERE x=X'abcdef' AND a = 'abc' AND b = 3;" | |
| 549 do_eqp_test 12.$tn.4 $sql {/t4 USING INDEX t4a/} | |
| 550 } else { | |
| 551 set sql { | |
| 552 SELECT * FROM t4 WHERE x=X'abcdef' AND a = 'abc' COLLATE nocase AND b = 3 | |
| 553 } | |
| 554 do_eqp_test 12.$tn.5 $sql {/t4 USING INDEX t4a/} | |
| 555 set sql { | |
| 556 SELECT * FROM t4 WHERE x=X'abcdef' AND a COLLATE nocase = 'abc' AND b = 3 | |
| 557 } | |
| 558 do_eqp_test 12.$tn.6 $sql {/t4 USING INDEX t4a/} | |
| 559 } | |
| 560 } | |
| 561 | |
| 562 #------------------------------------------------------------------------- | |
| 563 # Check that affinities are taken into account when using stat4 data to | |
| 564 # estimate the number of rows scanned by a rowid constraint. | |
| 565 # | |
| 566 drop_all_tables | |
| 567 do_test 13.1 { | |
| 568 execsql { | |
| 569 CREATE TABLE t1(a, b, c, d); | |
| 570 CREATE INDEX i1 ON t1(a); | |
| 571 CREATE INDEX i2 ON t1(b, c); | |
| 572 } | |
| 573 for {set i 0} {$i<100} {incr i} { | |
| 574 if {$i %2} {set a abc} else {set a def} | |
| 575 execsql { INSERT INTO t1(rowid, a, b, c) VALUES($i, $a, $i, $i) } | |
| 576 } | |
| 577 execsql ANALYZE | |
| 578 } {} | |
| 579 do_eqp_test 13.2.1 { | |
| 580 SELECT * FROM t1 WHERE a='abc' AND rowid<15 AND b<12 | |
| 581 } {/SEARCH TABLE t1 USING INDEX i1/} | |
| 582 do_eqp_test 13.2.2 { | |
| 583 SELECT * FROM t1 WHERE a='abc' AND rowid<'15' AND b<12 | |
| 584 } {/SEARCH TABLE t1 USING INDEX i1/} | |
| 585 do_eqp_test 13.3.1 { | |
| 586 SELECT * FROM t1 WHERE a='abc' AND rowid<100 AND b<12 | |
| 587 } {/SEARCH TABLE t1 USING INDEX i2/} | |
| 588 do_eqp_test 13.3.2 { | |
| 589 SELECT * FROM t1 WHERE a='abc' AND rowid<'100' AND b<12 | |
| 590 } {/SEARCH TABLE t1 USING INDEX i2/} | |
| 591 | |
| 592 #------------------------------------------------------------------------- | |
| 593 # Check also that affinities are taken into account when using stat4 data | |
| 594 # to estimate the number of rows scanned by any other constraint on a | |
| 595 # column other than the leftmost. | |
| 596 # | |
| 597 drop_all_tables | |
| 598 do_test 14.1 { | |
| 599 execsql { CREATE TABLE t1(a, b INTEGER, c) } | |
| 600 for {set i 0} {$i<100} {incr i} { | |
| 601 set c [expr $i % 3] | |
| 602 execsql { INSERT INTO t1 VALUES('ott', $i, $c) } | |
| 603 } | |
| 604 execsql { | |
| 605 CREATE INDEX i1 ON t1(a, b); | |
| 606 CREATE INDEX i2 ON t1(c); | |
| 607 ANALYZE; | |
| 608 } | |
| 609 } {} | |
| 610 do_eqp_test 13.2.1 { | |
| 611 SELECT * FROM t1 WHERE a='ott' AND b<10 AND c=1 | |
| 612 } {/SEARCH TABLE t1 USING INDEX i1/} | |
| 613 do_eqp_test 13.2.2 { | |
| 614 SELECT * FROM t1 WHERE a='ott' AND b<'10' AND c=1 | |
| 615 } {/SEARCH TABLE t1 USING INDEX i1/} | |
| 616 | |
| 617 #------------------------------------------------------------------------- | |
| 618 # By default, 16 non-periodic samples are collected for the stat4 table. | |
| 619 # The following tests attempt to verify that the most common keys are | |
| 620 # being collected. | |
| 621 # | |
| 622 proc check_stat4 {tn} { | |
| 623 db eval ANALYZE | |
| 624 db eval {SELECT a, b, c, d FROM t1} { | |
| 625 incr k($a) | |
| 626 incr k([list $a $b]) | |
| 627 incr k([list $a $b $c]) | |
| 628 if { [info exists k([list $a $b $c $d])]==0 } { incr nRow } | |
| 629 incr k([list $a $b $c $d]) | |
| 630 } | |
| 631 | |
| 632 set L [list] | |
| 633 foreach key [array names k] { | |
| 634 lappend L [list $k($key) $key] | |
| 635 } | |
| 636 | |
| 637 set nSample $nRow | |
| 638 if {$nSample>16} {set nSample 16} | |
| 639 | |
| 640 set nThreshold [lindex [lsort -decr -integer -index 0 $L] [expr $nSample-1] 0] | |
| 641 foreach key [array names k] { | |
| 642 if {$k($key)>$nThreshold} { | |
| 643 set expect($key) 1 | |
| 644 } | |
| 645 if {$k($key)==$nThreshold} { | |
| 646 set possible($key) 1 | |
| 647 } | |
| 648 } | |
| 649 | |
| 650 | |
| 651 set nPossible [expr $nSample - [llength [array names expect]]] | |
| 652 | |
| 653 #puts "EXPECT: [array names expect]" | |
| 654 #puts "POSSIBLE($nPossible/[array size possible]): [array names possible]" | |
| 655 #puts "HAVE: [db eval {SELECT test_decode(sample) FROM sqlite_stat4 WHERE idx=
'i1'}]" | |
| 656 | |
| 657 db eval {SELECT test_decode(sample) AS s FROM sqlite_stat4 WHERE idx='i1'} { | |
| 658 set seen 0 | |
| 659 for {set i 0} {$i<4} {incr i} { | |
| 660 unset -nocomplain expect([lrange $s 0 $i]) | |
| 661 if {[info exists possible([lrange $s 0 $i])]} { | |
| 662 set seen 1 | |
| 663 unset -nocomplain possible([lrange $s 0 $i]) | |
| 664 } | |
| 665 } | |
| 666 if {$seen} {incr nPossible -1} | |
| 667 } | |
| 668 if {$nPossible<0} {set nPossible 0} | |
| 669 | |
| 670 set res [list [llength [array names expect]] $nPossible] | |
| 671 uplevel [list do_test $tn [list set {} $res] {0 0}] | |
| 672 } | |
| 673 | |
| 674 drop_all_tables | |
| 675 do_test 14.1.1 { | |
| 676 execsql { | |
| 677 CREATE TABLE t1(a,b,c,d); | |
| 678 CREATE INDEX i1 ON t1(a,b,c,d); | |
| 679 } | |
| 680 for {set i 0} {$i < 160} {incr i} { | |
| 681 execsql { INSERT INTO t1 VALUES($i,$i,$i,$i) } | |
| 682 if {($i % 10)==0} { execsql { INSERT INTO t1 VALUES($i,$i,$i,$i) } } | |
| 683 } | |
| 684 } {} | |
| 685 check_stat4 14.1.2 | |
| 686 | |
| 687 do_test 14.2.1 { | |
| 688 execsql { DELETE FROM t1 } | |
| 689 for {set i 0} {$i < 1600} {incr i} { | |
| 690 execsql { INSERT INTO t1 VALUES($i/10,$i/17,$i/27,$i/37) } | |
| 691 } | |
| 692 } {} | |
| 693 check_stat4 14.2.2 | |
| 694 | |
| 695 do_test 14.3.1 { | |
| 696 for {set i 0} {$i < 10} {incr i} { | |
| 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 execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } | |
| 705 execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } | |
| 706 execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } | |
| 707 } | |
| 708 } {} | |
| 709 check_stat4 14.3.2 | |
| 710 | |
| 711 do_test 14.4.1 { | |
| 712 execsql {DELETE FROM t1} | |
| 713 for {set i 1} {$i < 160} {incr i} { | |
| 714 set b [expr $i % 10] | |
| 715 if {$b==0 || $b==2} {set b 1} | |
| 716 execsql { INSERT INTO t1 VALUES($i/10,$b,$i,$i) } | |
| 717 } | |
| 718 } {} | |
| 719 check_stat4 14.4.2 | |
| 720 db func lrange lrange | |
| 721 db func lindex lindex | |
| 722 do_execsql_test 14.4.3 { | |
| 723 SELECT lrange(test_decode(sample), 0, 1) AS s FROM sqlite_stat4 | |
| 724 WHERE lindex(s, 1)=='1' ORDER BY rowid | |
| 725 } { | |
| 726 {0 1} {1 1} {2 1} {3 1} | |
| 727 {4 1} {5 1} {6 1} {7 1} | |
| 728 {8 1} {9 1} {10 1} {11 1} | |
| 729 {12 1} {13 1} {14 1} {15 1} | |
| 730 } | |
| 731 | |
| 732 #------------------------------------------------------------------------- | |
| 733 # Test that nothing untoward happens if the stat4 table contains entries | |
| 734 # for indexes that do not exist. Or NULL values in the idx column. | |
| 735 # Or NULL values in any of the other columns. | |
| 736 # | |
| 737 drop_all_tables | |
| 738 do_execsql_test 15.1 { | |
| 739 CREATE TABLE x1(a, b, UNIQUE(a, b)); | |
| 740 INSERT INTO x1 VALUES(1, 2); | |
| 741 INSERT INTO x1 VALUES(3, 4); | |
| 742 INSERT INTO x1 VALUES(5, 6); | |
| 743 ANALYZE; | |
| 744 INSERT INTO sqlite_stat4 VALUES(NULL, NULL, NULL, NULL, NULL, NULL); | |
| 745 } | |
| 746 db close | |
| 747 sqlite3 db test.db | |
| 748 do_execsql_test 15.2 { SELECT * FROM x1 } {1 2 3 4 5 6} | |
| 749 | |
| 750 do_execsql_test 15.3 { | |
| 751 INSERT INTO sqlite_stat4 VALUES(42, 42, 42, 42, 42, 42); | |
| 752 } | |
| 753 db close | |
| 754 sqlite3 db test.db | |
| 755 do_execsql_test 15.4 { SELECT * FROM x1 } {1 2 3 4 5 6} | |
| 756 | |
| 757 do_execsql_test 15.5 { | |
| 758 UPDATE sqlite_stat1 SET stat = NULL; | |
| 759 } | |
| 760 db close | |
| 761 sqlite3 db test.db | |
| 762 do_execsql_test 15.6 { SELECT * FROM x1 } {1 2 3 4 5 6} | |
| 763 | |
| 764 do_execsql_test 15.7 { | |
| 765 ANALYZE; | |
| 766 UPDATE sqlite_stat1 SET tbl = 'no such tbl'; | |
| 767 } | |
| 768 db close | |
| 769 sqlite3 db test.db | |
| 770 do_execsql_test 15.8 { SELECT * FROM x1 } {1 2 3 4 5 6} | |
| 771 | |
| 772 do_execsql_test 15.9 { | |
| 773 ANALYZE; | |
| 774 UPDATE sqlite_stat4 SET neq = NULL, nlt=NULL, ndlt=NULL; | |
| 775 } | |
| 776 db close | |
| 777 sqlite3 db test.db | |
| 778 do_execsql_test 15.10 { SELECT * FROM x1 } {1 2 3 4 5 6} | |
| 779 | |
| 780 # This is just for coverage.... | |
| 781 do_execsql_test 15.11 { | |
| 782 ANALYZE; | |
| 783 UPDATE sqlite_stat1 SET stat = stat || ' unordered'; | |
| 784 } | |
| 785 db close | |
| 786 sqlite3 db test.db | |
| 787 do_execsql_test 15.12 { SELECT * FROM x1 } {1 2 3 4 5 6} | |
| 788 | |
| 789 #------------------------------------------------------------------------- | |
| 790 # Test that allocations used for sqlite_stat4 samples are included in | |
| 791 # the quantity returned by SQLITE_DBSTATUS_SCHEMA_USED. | |
| 792 # | |
| 793 set one [string repeat x 1000] | |
| 794 set two [string repeat x 2000] | |
| 795 do_test 16.1 { | |
| 796 reset_db | |
| 797 execsql { | |
| 798 CREATE TABLE t1(a, UNIQUE(a)); | |
| 799 INSERT INTO t1 VALUES($one); | |
| 800 ANALYZE; | |
| 801 } | |
| 802 set nByte [lindex [sqlite3_db_status db SCHEMA_USED 0] 1] | |
| 803 | |
| 804 reset_db | |
| 805 execsql { | |
| 806 CREATE TABLE t1(a, UNIQUE(a)); | |
| 807 INSERT INTO t1 VALUES($two); | |
| 808 ANALYZE; | |
| 809 } | |
| 810 set nByte2 [lindex [sqlite3_db_status db SCHEMA_USED 0] 1] | |
| 811 puts -nonewline " (nByte=$nByte nByte2=$nByte2)" | |
| 812 | |
| 813 expr {$nByte2 > $nByte+900 && $nByte2 < $nByte+1100} | |
| 814 } {1} | |
| 815 | |
| 816 #------------------------------------------------------------------------- | |
| 817 # Test that stat4 data may be used with partial indexes. | |
| 818 # | |
| 819 do_test 17.1 { | |
| 820 reset_db | |
| 821 execsql { | |
| 822 CREATE TABLE t1(a, b, c, d); | |
| 823 CREATE INDEX i1 ON t1(a, b) WHERE d IS NOT NULL; | |
| 824 INSERT INTO t1 VALUES(-1, -1, -1, NULL); | |
| 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 INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; | |
| 829 INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; | |
| 830 INSERT INTO t1 SELECT 2*a,2*b,2*c,d FROM t1; | |
| 831 } | |
| 832 | |
| 833 for {set i 0} {$i < 32} {incr i} { | |
| 834 if {$i<8} {set b 0} else { set b $i } | |
| 835 execsql { INSERT INTO t1 VALUES($i%2, $b, $i/2, 'abc') } | |
| 836 } | |
| 837 execsql {ANALYZE main.t1} | |
| 838 } {} | |
| 839 | |
| 840 do_catchsql_test 17.1.2 { | |
| 841 ANALYZE temp.t1; | |
| 842 } {1 {no such table: temp.t1}} | |
| 843 | |
| 844 do_eqp_test 17.2 { | |
| 845 SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=10 AND c=10; | |
| 846 } {/USING INDEX i1/} | |
| 847 do_eqp_test 17.3 { | |
| 848 SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=0 AND c=10; | |
| 849 } {/USING INDEX i1/} | |
| 850 | |
| 851 do_execsql_test 17.4 { | |
| 852 CREATE INDEX i2 ON t1(c, d); | |
| 853 ANALYZE main.i2; | |
| 854 } | |
| 855 do_eqp_test 17.5 { | |
| 856 SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=10 AND c=10; | |
| 857 } {/USING INDEX i1/} | |
| 858 do_eqp_test 17.6 { | |
| 859 SELECT * FROM t1 WHERE d IS NOT NULL AND a=0 AND b=0 AND c=10; | |
| 860 } {/USING INDEX i2/} | |
| 861 | |
| 862 #------------------------------------------------------------------------- | |
| 863 # | |
| 864 do_test 18.1 { | |
| 865 reset_db | |
| 866 execsql { | |
| 867 CREATE TABLE t1(a, b); | |
| 868 CREATE INDEX i1 ON t1(a, b); | |
| 869 } | |
| 870 for {set i 0} {$i < 9} {incr i} { | |
| 871 execsql { | |
| 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 INSERT INTO t1 VALUES($i, 0); | |
| 885 INSERT INTO t1 VALUES($i, 0); | |
| 886 INSERT INTO t1 VALUES($i, 0); | |
| 887 } | |
| 888 } | |
| 889 execsql ANALYZE | |
| 890 execsql { SELECT count(*) FROM sqlite_stat4 } | |
| 891 } {9} | |
| 892 | |
| 893 #------------------------------------------------------------------------- | |
| 894 # For coverage. | |
| 895 # | |
| 896 ifcapable view { | |
| 897 do_test 19.1 { | |
| 898 reset_db | |
| 899 execsql { | |
| 900 CREATE TABLE t1(x, y); | |
| 901 CREATE INDEX i1 ON t1(x, y); | |
| 902 CREATE VIEW v1 AS SELECT * FROM t1; | |
| 903 ANALYZE; | |
| 904 } | |
| 905 } {} | |
| 906 } | |
| 907 ifcapable auth { | |
| 908 proc authproc {op args} { | |
| 909 if {$op == "SQLITE_ANALYZE"} { return "SQLITE_DENY" } | |
| 910 return "SQLITE_OK" | |
| 911 } | |
| 912 do_test 19.2 { | |
| 913 reset_db | |
| 914 db auth authproc | |
| 915 execsql { | |
| 916 CREATE TABLE t1(x, y); | |
| 917 CREATE VIEW v1 AS SELECT * FROM t1; | |
| 918 } | |
| 919 catchsql ANALYZE | |
| 920 } {1 {not authorized}} | |
| 921 } | |
| 922 | |
| 923 #------------------------------------------------------------------------- | |
| 924 # | |
| 925 reset_db | |
| 926 proc r {args} { expr rand() } | |
| 927 db func r r | |
| 928 db func lrange lrange | |
| 929 do_test 20.1 { | |
| 930 execsql { | |
| 931 CREATE TABLE t1(a,b,c,d); | |
| 932 CREATE INDEX i1 ON t1(a,b,c,d); | |
| 933 } | |
| 934 for {set i 0} {$i < 16} {incr i} { | |
| 935 execsql { | |
| 936 INSERT INTO t1 VALUES($i, r(), r(), r()); | |
| 937 INSERT INTO t1 VALUES($i, $i, r(), r()); | |
| 938 INSERT INTO t1 VALUES($i, $i, $i, r()); | |
| 939 INSERT INTO t1 VALUES($i, $i, $i, $i); | |
| 940 INSERT INTO t1 VALUES($i, $i, $i, $i); | |
| 941 INSERT INTO t1 VALUES($i, $i, $i, r()); | |
| 942 INSERT INTO t1 VALUES($i, $i, r(), r()); | |
| 943 INSERT INTO t1 VALUES($i, r(), r(), r()); | |
| 944 } | |
| 945 } | |
| 946 } {} | |
| 947 do_execsql_test 20.2 { ANALYZE } | |
| 948 for {set i 0} {$i<16} {incr i} { | |
| 949 set val "$i $i $i $i" | |
| 950 do_execsql_test 20.3.$i { | |
| 951 SELECT count(*) FROM sqlite_stat4 | |
| 952 WHERE lrange(test_decode(sample), 0, 3)=$val | |
| 953 } {1} | |
| 954 } | |
| 955 | |
| 956 #------------------------------------------------------------------------- | |
| 957 # | |
| 958 reset_db | |
| 959 | |
| 960 do_execsql_test 21.0 { | |
| 961 CREATE TABLE t2(a, b); | |
| 962 CREATE INDEX i2 ON t2(a); | |
| 963 } | |
| 964 | |
| 965 do_test 21.1 { | |
| 966 for {set i 1} {$i < 100} {incr i} { | |
| 967 execsql { | |
| 968 INSERT INTO t2 VALUES(CASE WHEN $i < 80 THEN 'one' ELSE 'two' END, $i) | |
| 969 } | |
| 970 } | |
| 971 execsql ANALYZE | |
| 972 } {} | |
| 973 | |
| 974 # Condition (a='one') matches 80% of the table. (rowid<10) reduces this to | |
| 975 # 10%, but (rowid<50) only reduces it to 50%. So in the first case below | |
| 976 # the index is used. In the second, it is not. | |
| 977 # | |
| 978 do_eqp_test 21.2 { | |
| 979 SELECT * FROM t2 WHERE a='one' AND rowid < 10 | |
| 980 } {/*USING INDEX i2 (a=? AND rowid<?)*/} | |
| 981 do_eqp_test 21.3 { | |
| 982 SELECT * FROM t2 WHERE a='one' AND rowid < 50 | |
| 983 } {/*USING INTEGER PRIMARY KEY*/} | |
| 984 | |
| 985 #------------------------------------------------------------------------- | |
| 986 # | |
| 987 reset_db | |
| 988 do_execsql_test 22.0 { | |
| 989 CREATE TABLE t3(a, b, c, d, PRIMARY KEY(a, b)) WITHOUT ROWID; | |
| 990 } | |
| 991 do_execsql_test 22.1 { | |
| 992 WITH r(x) AS ( | |
| 993 SELECT 1 | |
| 994 UNION ALL | |
| 995 SELECT x+1 FROM r WHERE x<=100 | |
| 996 ) | |
| 997 | |
| 998 INSERT INTO t3 SELECT | |
| 999 CASE WHEN (x>45 AND x<96) THEN 'B' ELSE 'A' END, /* Column "a" */ | |
| 1000 x, /* Column "b" */ | |
| 1001 CASE WHEN (x<51) THEN 'one' ELSE 'two' END, /* Column "c" */ | |
| 1002 x /* Column "d" */ | |
| 1003 FROM r; | |
| 1004 | |
| 1005 CREATE INDEX i3 ON t3(c); | |
| 1006 CREATE INDEX i4 ON t3(d); | |
| 1007 ANALYZE; | |
| 1008 } | |
| 1009 | |
| 1010 # Expression (c='one' AND a='B') matches 5 table rows. But (c='one' AND a=A') | |
| 1011 # matches 45. Expression (d<?) matches 20. Neither index is a covering index. | |
| 1012 # | |
| 1013 # Therefore, with stat4 data, SQLite prefers (c='one' AND a='B') over (d<20), | |
| 1014 # and (d<20) over (c='one' AND a='A'). | |
| 1015 foreach {tn where res} { | |
| 1016 1 "c='one' AND a='B' AND d < 20" {/*INDEX i3 (c=? AND a=?)*/} | |
| 1017 2 "c='one' AND a='A' AND d < 20" {/*INDEX i4 (d<?)*/} | |
| 1018 } { | |
| 1019 do_eqp_test 22.2.$tn "SELECT * FROM t3 WHERE $where" $res | |
| 1020 } | |
| 1021 | |
| 1022 proc int_to_char {i} { | |
| 1023 set ret "" | |
| 1024 set char [list a b c d e f g h i j] | |
| 1025 foreach {div} {1000 100 10 1} { | |
| 1026 append ret [lindex $char [expr ($i / $div) % 10]] | |
| 1027 } | |
| 1028 set ret | |
| 1029 } | |
| 1030 db func int_to_char int_to_char | |
| 1031 | |
| 1032 do_execsql_test 23.0 { | |
| 1033 CREATE TABLE t4( | |
| 1034 a COLLATE nocase, b, c, | |
| 1035 d, e, f, | |
| 1036 PRIMARY KEY(c, b, a) | |
| 1037 ) WITHOUT ROWID; | |
| 1038 CREATE INDEX i41 ON t4(e); | |
| 1039 CREATE INDEX i42 ON t4(f); | |
| 1040 | |
| 1041 WITH data(a, b, c, d, e, f) AS ( | |
| 1042 SELECT int_to_char(0), 'xyz', 'zyx', '*', 0, 0 | |
| 1043 UNION ALL | |
| 1044 SELECT | |
| 1045 int_to_char(f+1), b, c, d, (e+1) % 2, f+1 | |
| 1046 FROM data WHERE f<1024 | |
| 1047 ) | |
| 1048 INSERT INTO t4 SELECT a, b, c, d, e, f FROM data; | |
| 1049 ANALYZE; | |
| 1050 } {} | |
| 1051 | |
| 1052 do_eqp_test 23.1 { | |
| 1053 SELECT * FROM t4 WHERE | |
| 1054 (e=1 AND b='xyz' AND c='zyx' AND a<'AEA') AND f<300 | |
| 1055 } { | |
| 1056 0 0 0 {SEARCH TABLE t4 USING INDEX i41 (e=? AND c=? AND b=? AND a<?)} | |
| 1057 } | |
| 1058 do_eqp_test 23.2 { | |
| 1059 SELECT * FROM t4 WHERE | |
| 1060 (e=1 AND b='xyz' AND c='zyx' AND a<'JJJ') AND f<300 | |
| 1061 } { | |
| 1062 0 0 0 {SEARCH TABLE t4 USING INDEX i42 (f<?)} | |
| 1063 } | |
| 1064 | |
| 1065 do_execsql_test 24.0 { | |
| 1066 CREATE TABLE t5(c, d, b, e, a, PRIMARY KEY(a, b, c)) WITHOUT ROWID; | |
| 1067 WITH data(a, b, c, d, e) AS ( | |
| 1068 SELECT 'z', 'y', 0, 0, 0 | |
| 1069 UNION ALL | |
| 1070 SELECT | |
| 1071 a, CASE WHEN b='y' THEN 'n' ELSE 'y' END, c+1, e/250, e+1 | |
| 1072 FROM data | |
| 1073 WHERE e<1000 | |
| 1074 ) | |
| 1075 INSERT INTO t5(a, b, c, d, e) SELECT * FROM data; | |
| 1076 CREATE INDEX t5d ON t5(d); | |
| 1077 CREATE INDEX t5e ON t5(e); | |
| 1078 ANALYZE; | |
| 1079 } | |
| 1080 | |
| 1081 foreach {tn where eqp} { | |
| 1082 1 "d=0 AND a='z' AND b='n' AND e<200" {/*t5d (d=? AND a=? AND b=?)*/} | |
| 1083 2 "d=0 AND a='z' AND b='n' AND e<100" {/*t5e (e<?)*/} | |
| 1084 | |
| 1085 3 "d=0 AND e<300" {/*t5d (d=?)*/} | |
| 1086 4 "d=0 AND e<200" {/*t5e (e<?)*/} | |
| 1087 } { | |
| 1088 do_eqp_test 24.$tn "SeLeCt * FROM t5 WHERE $where" $eqp | |
| 1089 } | |
| 1090 | |
| 1091 #------------------------------------------------------------------------- | |
| 1092 # Test that if stat4 data is available but cannot be used because the | |
| 1093 # rhs of a range constraint is a complex expression, the default estimates | |
| 1094 # are used instead. | |
| 1095 ifcapable stat4&&cte { | |
| 1096 do_execsql_test 25.1 { | |
| 1097 CREATE TABLE t6(a, b); | |
| 1098 WITH ints(i,j) AS ( | |
| 1099 SELECT 1,1 UNION ALL SELECT i+1,j+1 FROM ints WHERE i<100 | |
| 1100 ) INSERT INTO t6 SELECT * FROM ints; | |
| 1101 CREATE INDEX aa ON t6(a); | |
| 1102 CREATE INDEX bb ON t6(b); | |
| 1103 ANALYZE; | |
| 1104 } | |
| 1105 | |
| 1106 # Term (b<?) is estimated at 25%. Better than (a<30) but not as | |
| 1107 # good as (a<20). | |
| 1108 do_eqp_test 25.2.1 { SELECT * FROM t6 WHERE a<30 AND b<? } { | |
| 1109 0 0 0 {SEARCH TABLE t6 USING INDEX bb (b<?)} | |
| 1110 } | |
| 1111 do_eqp_test 25.2.2 { SELECT * FROM t6 WHERE a<20 AND b<? } { | |
| 1112 0 0 0 {SEARCH TABLE t6 USING INDEX aa (a<?)} | |
| 1113 } | |
| 1114 | |
| 1115 # Term (b BETWEEN ? AND ?) is estimated at 1/64. | |
| 1116 do_eqp_test 25.3.1 { | |
| 1117 SELECT * FROM t6 WHERE a BETWEEN 5 AND 10 AND b BETWEEN ? AND ? | |
| 1118 } { | |
| 1119 0 0 0 {SEARCH TABLE t6 USING INDEX bb (b>? AND b<?)} | |
| 1120 } | |
| 1121 | |
| 1122 # Term (b BETWEEN ? AND 60) is estimated to return roughly 15 rows - | |
| 1123 # 60 from (b<=60) multiplied by 0.25 for the b>=? term. Better than | |
| 1124 # (a<20) but not as good as (a<10). | |
| 1125 do_eqp_test 25.4.1 { | |
| 1126 SELECT * FROM t6 WHERE a < 10 AND (b BETWEEN ? AND 60) | |
| 1127 } { | |
| 1128 0 0 0 {SEARCH TABLE t6 USING INDEX aa (a<?)} | |
| 1129 } | |
| 1130 do_eqp_test 25.4.2 { | |
| 1131 SELECT * FROM t6 WHERE a < 20 AND (b BETWEEN ? AND 60) | |
| 1132 } { | |
| 1133 0 0 0 {SEARCH TABLE t6 USING INDEX bb (b>? AND b<?)} | |
| 1134 } | |
| 1135 } | |
| 1136 | |
| 1137 finish_test | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2363173002:
[sqlite] Remove obsolete reference version 3.8.7.4. (Closed)
