| OLD | NEW |
|---|
| (Empty) |
| 1 # 2014 November 1 | |
| 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 | |
| 13 set testdir [file dirname $argv0] | |
| 14 source $testdir/tester.tcl | |
| 15 set testprefix scanstatus | |
| 16 | |
| 17 ifcapable !scanstatus { | |
| 18 finish_test | |
| 19 return | |
| 20 } | |
| 21 | |
| 22 do_execsql_test 1.0 { | |
| 23 CREATE TABLE t1(a, b); | |
| 24 CREATE TABLE t2(x, y); | |
| 25 INSERT INTO t1 VALUES(1, 2); | |
| 26 INSERT INTO t1 VALUES(3, 4); | |
| 27 INSERT INTO t2 VALUES('a', 'b'); | |
| 28 INSERT INTO t2 VALUES('c', 'd'); | |
| 29 INSERT INTO t2 VALUES('e', 'f'); | |
| 30 } | |
| 31 | |
| 32 proc do_scanstatus_test {tn res} { | |
| 33 set stmt [db_last_stmt_ptr db] | |
| 34 set idx 0 | |
| 35 set ret [list] | |
| 36 while {1} { | |
| 37 set r [sqlite3_stmt_scanstatus $stmt $idx] | |
| 38 if {[llength $r]==0} break | |
| 39 lappend ret {*}$r | |
| 40 incr idx | |
| 41 } | |
| 42 | |
| 43 uplevel [list do_test $tn [list set {} $ret] [list {*}$res]] | |
| 44 } | |
| 45 | |
| 46 do_execsql_test 1.1 { SELECT count(*) FROM t1, t2; } 6 | |
| 47 do_scanstatus_test 1.2 { | |
| 48 nLoop 1 nVisit 2 nEst 1048576.0 zName t1 zExplain {SCAN TABLE t1} | |
| 49 nLoop 2 nVisit 6 nEst 1048576.0 zName t2 zExplain {SCAN TABLE t2} | |
| 50 } | |
| 51 | |
| 52 do_execsql_test 1.3 { | |
| 53 ANALYZE; | |
| 54 SELECT count(*) FROM t1, t2; | |
| 55 } 6 | |
| 56 do_scanstatus_test 1.4 { | |
| 57 nLoop 1 nVisit 2 nEst 2.0 zName t1 zExplain {SCAN TABLE t1} | |
| 58 nLoop 2 nVisit 6 nEst 3.0 zName t2 zExplain {SCAN TABLE t2} | |
| 59 } | |
| 60 | |
| 61 do_execsql_test 1.5 { ANALYZE } | |
| 62 do_execsql_test 1.6 { | |
| 63 SELECT count(*) FROM t1, t2 WHERE t2.rowid>1; | |
| 64 } 4 | |
| 65 do_scanstatus_test 1.7 { | |
| 66 nLoop 1 nVisit 2 nEst 2.0 zName t2 zExplain | |
| 67 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid>?)} | |
| 68 nLoop 2 nVisit 4 nEst 2.0 zName t1 zExplain {SCAN TABLE t1} | |
| 69 } | |
| 70 | |
| 71 do_execsql_test 1.8 { | |
| 72 SELECT count(*) FROM t1, t2 WHERE t2.rowid>1; | |
| 73 } 4 | |
| 74 | |
| 75 do_scanstatus_test 1.9 { | |
| 76 nLoop 2 nVisit 4 nEst 2.0 zName t2 zExplain | |
| 77 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid>?)} | |
| 78 nLoop 4 nVisit 8 nEst 2.0 zName t1 zExplain {SCAN TABLE t1} | |
| 79 } | |
| 80 | |
| 81 do_test 1.9 { | |
| 82 sqlite3_stmt_scanstatus_reset [db_last_stmt_ptr db] | |
| 83 } {} | |
| 84 | |
| 85 do_scanstatus_test 1.10 { | |
| 86 nLoop 0 nVisit 0 nEst 2.0 zName t2 zExplain | |
| 87 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid>?)} | |
| 88 nLoop 0 nVisit 0 nEst 2.0 zName t1 zExplain {SCAN TABLE t1} | |
| 89 } | |
| 90 | |
| 91 #------------------------------------------------------------------------- | |
| 92 # Try a few different types of scans. | |
| 93 # | |
| 94 reset_db | |
| 95 do_execsql_test 2.1 { | |
| 96 CREATE TABLE x1(i INTEGER PRIMARY KEY, j); | |
| 97 INSERT INTO x1 VALUES(1, 'one'); | |
| 98 INSERT INTO x1 VALUES(2, 'two'); | |
| 99 INSERT INTO x1 VALUES(3, 'three'); | |
| 100 INSERT INTO x1 VALUES(4, 'four'); | |
| 101 CREATE INDEX x1j ON x1(j); | |
| 102 | |
| 103 SELECT * FROM x1 WHERE i=2; | |
| 104 } {2 two} | |
| 105 | |
| 106 do_scanstatus_test 2.2 { | |
| 107 nLoop 1 nVisit 1 nEst 1.0 zName x1 | |
| 108 zExplain {SEARCH TABLE x1 USING INTEGER PRIMARY KEY (rowid=?)} | |
| 109 } | |
| 110 | |
| 111 do_execsql_test 2.3.1 { | |
| 112 SELECT * FROM x1 WHERE j='two' | |
| 113 } {2 two} | |
| 114 do_scanstatus_test 2.3.2 { | |
| 115 nLoop 1 nVisit 1 nEst 10.0 zName x1j | |
| 116 zExplain {SEARCH TABLE x1 USING COVERING INDEX x1j (j=?)} | |
| 117 } | |
| 118 | |
| 119 do_execsql_test 2.4.1 { | |
| 120 SELECT * FROM x1 WHERE j<'two' | |
| 121 } {4 four 1 one 3 three} | |
| 122 do_scanstatus_test 2.4.2 { | |
| 123 nLoop 1 nVisit 3 nEst 262144.0 zName x1j | |
| 124 zExplain {SEARCH TABLE x1 USING COVERING INDEX x1j (j<?)} | |
| 125 } | |
| 126 | |
| 127 do_execsql_test 2.5.1 { | |
| 128 SELECT * FROM x1 WHERE j>='two' | |
| 129 } {2 two} | |
| 130 do_scanstatus_test 2.5.2 { | |
| 131 nLoop 1 nVisit 1 nEst 262144.0 zName x1j | |
| 132 zExplain {SEARCH TABLE x1 USING COVERING INDEX x1j (j>?)} | |
| 133 } | |
| 134 | |
| 135 do_execsql_test 2.6.1 { | |
| 136 SELECT * FROM x1 WHERE j BETWEEN 'three' AND 'two' | |
| 137 } {3 three 2 two} | |
| 138 do_scanstatus_test 2.6.2 { | |
| 139 nLoop 1 nVisit 2 nEst 16384.0 zName x1j | |
| 140 zExplain {SEARCH TABLE x1 USING COVERING INDEX x1j (j>? AND j<?)} | |
| 141 } | |
| 142 | |
| 143 do_execsql_test 2.7.1 { | |
| 144 CREATE TABLE x2(i INTEGER, j, k); | |
| 145 INSERT INTO x2 SELECT i, j, i || ' ' || j FROM x1; | |
| 146 CREATE INDEX x2j ON x2(j); | |
| 147 CREATE INDEX x2ij ON x2(i, j); | |
| 148 SELECT * FROM x2 WHERE j BETWEEN 'three' AND 'two' | |
| 149 } {3 three {3 three} 2 two {2 two}} | |
| 150 | |
| 151 do_scanstatus_test 2.7.2 { | |
| 152 nLoop 1 nVisit 2 nEst 16384.0 zName x2j | |
| 153 zExplain {SEARCH TABLE x2 USING INDEX x2j (j>? AND j<?)} | |
| 154 } | |
| 155 | |
| 156 do_execsql_test 2.8.1 { | |
| 157 SELECT * FROM x2 WHERE i=1 AND j='two' | |
| 158 } | |
| 159 do_scanstatus_test 2.8.2 { | |
| 160 nLoop 1 nVisit 0 nEst 8.0 zName x2ij | |
| 161 zExplain {SEARCH TABLE x2 USING INDEX x2ij (i=? AND j=?)} | |
| 162 } | |
| 163 | |
| 164 do_execsql_test 2.9.1 { | |
| 165 SELECT * FROM x2 WHERE i=5 AND j='two' | |
| 166 } | |
| 167 do_scanstatus_test 2.9.2 { | |
| 168 nLoop 1 nVisit 0 nEst 8.0 zName x2ij | |
| 169 zExplain {SEARCH TABLE x2 USING INDEX x2ij (i=? AND j=?)} | |
| 170 } | |
| 171 | |
| 172 do_execsql_test 2.10.1 { | |
| 173 SELECT * FROM x2 WHERE i=3 AND j='three' | |
| 174 } {3 three {3 three}} | |
| 175 do_scanstatus_test 2.10.2 { | |
| 176 nLoop 1 nVisit 1 nEst 8.0 zName x2ij | |
| 177 zExplain {SEARCH TABLE x2 USING INDEX x2ij (i=? AND j=?)} | |
| 178 } | |
| 179 | |
| 180 #------------------------------------------------------------------------- | |
| 181 # Try with queries that use the OR optimization. | |
| 182 # | |
| 183 do_execsql_test 3.1 { | |
| 184 CREATE TABLE a1(a, b, c, d); | |
| 185 CREATE INDEX a1a ON a1(a); | |
| 186 CREATE INDEX a1bc ON a1(b, c); | |
| 187 | |
| 188 WITH d(x) AS (SELECT 1 UNION ALL SELECT x+1 AS n FROM d WHERE n<=100) | |
| 189 INSERT INTO a1 SELECT x, x, x, x FROM d; | |
| 190 } | |
| 191 | |
| 192 do_execsql_test 3.2.1 { | |
| 193 SELECT d FROM a1 WHERE (a=4 OR b=13) | |
| 194 } {4 13} | |
| 195 do_scanstatus_test 3.2.2 { | |
| 196 nLoop 1 nVisit 1 nEst 10.0 zName a1a | |
| 197 zExplain {SEARCH TABLE a1 USING INDEX a1a (a=?)} | |
| 198 nLoop 1 nVisit 1 nEst 10.0 zName a1bc | |
| 199 zExplain {SEARCH TABLE a1 USING INDEX a1bc (b=?)} | |
| 200 } | |
| 201 | |
| 202 do_execsql_test 3.2.1 { | |
| 203 SELECT count(*) FROM a1 WHERE (a BETWEEN 4 AND 12) OR (b BETWEEN 40 AND 60) | |
| 204 } {30} | |
| 205 do_scanstatus_test 3.2.2 { | |
| 206 nLoop 1 nVisit 9 nEst 16384.0 zName a1a | |
| 207 zExplain {SEARCH TABLE a1 USING INDEX a1a (a>? AND a<?)} | |
| 208 nLoop 1 nVisit 21 nEst 16384.0 zName a1bc | |
| 209 zExplain {SEARCH TABLE a1 USING INDEX a1bc (b>? AND b<?)} | |
| 210 } | |
| 211 | |
| 212 do_execsql_test 3.3.1 { | |
| 213 SELECT count(*) FROM a1 AS x, a1 AS y | |
| 214 WHERE (x.a BETWEEN 4 AND 12) AND (y.b BETWEEN 1 AND 10) | |
| 215 } {90} | |
| 216 do_scanstatus_test 3.2.2 { | |
| 217 nLoop 1 nVisit 10 nEst 16384.0 zName a1bc | |
| 218 zExplain {SEARCH TABLE a1 AS y USING COVERING INDEX a1bc (b>? AND b<?)} | |
| 219 nLoop 10 nVisit 90 nEst 16384.0 zName a1a | |
| 220 zExplain {SEARCH TABLE a1 AS x USING COVERING INDEX a1a (a>? AND a<?)} | |
| 221 } | |
| 222 | |
| 223 do_execsql_test 3.4.1 { | |
| 224 SELECT count(*) FROM a1 WHERE a IN (1, 5, 10, 15); | |
| 225 } {4} | |
| 226 do_scanstatus_test 3.4.2 { | |
| 227 nLoop 1 nVisit 4 nEst 40.0 zName a1a | |
| 228 zExplain {SEARCH TABLE a1 USING COVERING INDEX a1a (a=?)} | |
| 229 } | |
| 230 | |
| 231 do_execsql_test 3.4.1 { | |
| 232 SELECT count(*) FROM a1 WHERE rowid IN (1, 5, 10, 15); | |
| 233 } {4} | |
| 234 do_scanstatus_test 3.4.2 { | |
| 235 nLoop 1 nVisit 4 nEst 4.0 zName a1 | |
| 236 zExplain {SEARCH TABLE a1 USING INTEGER PRIMARY KEY (rowid=?)} | |
| 237 } | |
| 238 | |
| 239 #------------------------------------------------------------------------- | |
| 240 # Test that scanstatus() data is not available for searches performed | |
| 241 # by triggers. | |
| 242 # | |
| 243 # It is available for searches performed as part of FK processing, but | |
| 244 # not FK action processing. | |
| 245 # | |
| 246 do_execsql_test 4.0 { | |
| 247 CREATE TABLE t1(a, b, c); | |
| 248 CREATE TABLE t2(x PRIMARY KEY, y, z); | |
| 249 CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN | |
| 250 SELECT * FROM t2 WHERE x BETWEEN 20 AND 40; | |
| 251 END; | |
| 252 WITH d(x) AS (SELECT 1 UNION ALL SELECT x+1 AS n FROM d WHERE n<=100) | |
| 253 INSERT INTO t2 SELECT x, x*2, x*3 FROM d; | |
| 254 } | |
| 255 | |
| 256 do_execsql_test 4.1.1 { INSERT INTO t1 VALUES(1, 2, 3); } | |
| 257 do_scanstatus_test 4.1.2 { } | |
| 258 | |
| 259 do_execsql_test 4.2 { | |
| 260 CREATE TABLE p1(x PRIMARY KEY); | |
| 261 INSERT INTO p1 VALUES(1), (2), (3), (4); | |
| 262 CREATE TABLE c1(y REFERENCES p1); | |
| 263 INSERT INTO c1 VALUES(1), (2), (3); | |
| 264 PRAGMA foreign_keys=on; | |
| 265 } | |
| 266 do_execsql_test 4.2.1 { DELETE FROM p1 WHERE x=4 } | |
| 267 do_scanstatus_test 4.2.2 { | |
| 268 nLoop 1 nVisit 1 nEst 1.0 zName sqlite_autoindex_p1_1 | |
| 269 zExplain {SEARCH TABLE p1 USING INDEX sqlite_autoindex_p1_1 (x=?)} | |
| 270 | |
| 271 nLoop 1 nVisit 3 nEst 262144.0 zName c1 zExplain {SCAN TABLE c1} | |
| 272 } | |
| 273 | |
| 274 #------------------------------------------------------------------------- | |
| 275 # Further tests of different scan types. | |
| 276 # | |
| 277 reset_db | |
| 278 proc tochar {i} { | |
| 279 set alphabet {a b c d e f g h i j k l m n o p q r s t u v w x y z} | |
| 280 return [lindex $alphabet [expr $i % [llength $alphabet]]] | |
| 281 } | |
| 282 db func tochar tochar | |
| 283 do_execsql_test 5.0 { | |
| 284 CREATE TABLE t1(a PRIMARY KEY, b, c); | |
| 285 INSERT INTO t1 VALUES(0, 1, 'a'); | |
| 286 INSERT INTO t1 VALUES(1, 0, 'b'); | |
| 287 INSERT INTO t1 VALUES(2, 1, 'c'); | |
| 288 INSERT INTO t1 VALUES(3, 0, 'd'); | |
| 289 INSERT INTO t1 VALUES(4, 1, 'e'); | |
| 290 INSERT INTO t1 VALUES(5, 0, 'a'); | |
| 291 INSERT INTO t1 VALUES(6, 1, 'b'); | |
| 292 INSERT INTO t1 VALUES(7, 0, 'c'); | |
| 293 INSERT INTO t1 VALUES(8, 1, 'd'); | |
| 294 INSERT INTO t1 VALUES(9, 0, 'e'); | |
| 295 CREATE INDEX t1bc ON t1(b, c); | |
| 296 | |
| 297 CREATE TABLE t2(x, y); | |
| 298 CREATE INDEX t2xy ON t2(x, y); | |
| 299 WITH data(i, x, y) AS ( | |
| 300 SELECT 0, 0, tochar(0) | |
| 301 UNION ALL | |
| 302 SELECT i+1, (i+1)%2, tochar(i+1) FROM data WHERE i<500 | |
| 303 ) INSERT INTO t2 SELECT x, y FROM data; | |
| 304 | |
| 305 CREATE TABLE t3(x, y); | |
| 306 INSERT INTO t3 SELECT * FROM t2; | |
| 307 | |
| 308 ANALYZE; | |
| 309 } | |
| 310 | |
| 311 do_execsql_test 5.1.1 { | |
| 312 SELECT count(*) FROM t1 WHERE a IN (SELECT b FROM t1 AS ii) | |
| 313 } {2} | |
| 314 do_scanstatus_test 5.1.2 { | |
| 315 nLoop 1 nVisit 10 nEst 10.0 zName t1bc | |
| 316 zExplain {SCAN TABLE t1 AS ii USING COVERING INDEX t1bc} | |
| 317 nLoop 1 nVisit 2 nEst 8.0 zName sqlite_autoindex_t1_1 | |
| 318 zExplain {SEARCH TABLE t1 USING COVERING INDEX sqlite_autoindex_t1_1 (a=?)} | |
| 319 } | |
| 320 | |
| 321 do_execsql_test 5.2.1 { | |
| 322 SELECT count(*) FROM t1 WHERE a IN (0, 1) | |
| 323 } {2} | |
| 324 do_scanstatus_test 5.2.2 { | |
| 325 nLoop 1 nVisit 2 nEst 2.0 zName sqlite_autoindex_t1_1 | |
| 326 zExplain {SEARCH TABLE t1 USING COVERING INDEX sqlite_autoindex_t1_1 (a=?)} | |
| 327 } | |
| 328 | |
| 329 do_eqp_test 5.3.1 { | |
| 330 SELECT count(*) FROM t2 WHERE y = 'j'; | |
| 331 } {0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)}} | |
| 332 do_execsql_test 5.3.2 { | |
| 333 SELECT count(*) FROM t2 WHERE y = 'j'; | |
| 334 } {19} | |
| 335 do_scanstatus_test 5.3.3 { | |
| 336 nLoop 1 nVisit 19 nEst 56.0 zName t2xy zExplain | |
| 337 {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)} | |
| 338 } | |
| 339 | |
| 340 do_eqp_test 5.4.1 { | |
| 341 SELECT count(*) FROM t1, t2 WHERE y = c; | |
| 342 } { | |
| 343 0 0 0 {SCAN TABLE t1 USING COVERING INDEX t1bc} | |
| 344 0 1 1 {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)} | |
| 345 } | |
| 346 do_execsql_test 5.4.2 { | |
| 347 SELECT count(*) FROM t1, t2 WHERE y = c; | |
| 348 } {200} | |
| 349 do_scanstatus_test 5.4.3 { | |
| 350 nLoop 1 nVisit 10 nEst 10.0 zName t1bc | |
| 351 zExplain {SCAN TABLE t1 USING COVERING INDEX t1bc} | |
| 352 nLoop 10 nVisit 200 nEst 56.0 zName t2xy | |
| 353 zExplain {SEARCH TABLE t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)} | |
| 354 } | |
| 355 | |
| 356 do_eqp_test 5.5.1 { | |
| 357 SELECT count(*) FROM t1, t3 WHERE y = c; | |
| 358 } { | |
| 359 0 0 1 {SCAN TABLE t3} | |
| 360 0 1 0 {SEARCH TABLE t1 USING AUTOMATIC COVERING INDEX (c=?)} | |
| 361 } | |
| 362 do_execsql_test 5.5.2 { | |
| 363 SELECT count(*) FROM t1, t3 WHERE y = c; | |
| 364 } {200} | |
| 365 do_scanstatus_test 5.5.3 { | |
| 366 nLoop 1 nVisit 501 nEst 480.0 zName t3 zExplain {SCAN TABLE t3} | |
| 367 nLoop 501 nVisit 200 nEst 20.0 zName auto-index zExplain | |
| 368 {SEARCH TABLE t1 USING AUTOMATIC COVERING INDEX (c=?)} | |
| 369 } | |
| 370 | |
| 371 #------------------------------------------------------------------------- | |
| 372 # Virtual table scans | |
| 373 # | |
| 374 ifcapable fts3 { | |
| 375 do_execsql_test 6.0 { | |
| 376 CREATE VIRTUAL TABLE ft1 USING fts4; | |
| 377 INSERT INTO ft1 VALUES('a d c f g h e i f c'); | |
| 378 INSERT INTO ft1 VALUES('g c h b g b f f f g'); | |
| 379 INSERT INTO ft1 VALUES('h h c c h f a e d d'); | |
| 380 INSERT INTO ft1 VALUES('e j i j i e b c f g'); | |
| 381 INSERT INTO ft1 VALUES('g f b g j c h a d f'); | |
| 382 INSERT INTO ft1 VALUES('j i a e g f a i a c'); | |
| 383 INSERT INTO ft1 VALUES('f d g g j j c a h g'); | |
| 384 INSERT INTO ft1 VALUES('b d h a d j j j b i'); | |
| 385 INSERT INTO ft1 VALUES('j e a b j e c b c i'); | |
| 386 INSERT INTO ft1 VALUES('a d e f b j j c g d'); | |
| 387 } | |
| 388 do_execsql_test 6.1.1 { | |
| 389 SELECT count(*) FROM ft1 WHERE ft1 MATCH 'd' | |
| 390 } {6} | |
| 391 do_scanstatus_test 6.1.2 { | |
| 392 nLoop 1 nVisit 6 nEst 24.0 zName ft1 zExplain | |
| 393 {SCAN TABLE ft1 VIRTUAL TABLE INDEX 3:} | |
| 394 } | |
| 395 } | |
| 396 | |
| 397 | |
| 398 finish_test | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2846743003:
[sql] Remove SQLite 3.10.2 reference directory. (Closed)
