gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/scanstatus.test (about) 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 version -last-stmt-ptr] 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 t1} 49 nLoop 2 nVisit 6 nEst 1048576.0 zName t2 zExplain {SCAN 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 t1} 58 nLoop 2 nVisit 6 nEst 3.0 zName t2 zExplain {SCAN 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 t2 USING INTEGER PRIMARY KEY (rowid>?)} 68 nLoop 2 nVisit 4 nEst 2.0 zName t1 zExplain {SCAN 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 t2 USING INTEGER PRIMARY KEY (rowid>?)} 78 nLoop 4 nVisit 8 nEst 2.0 zName t1 zExplain {SCAN t1} 79 } 80 81 do_test 1.9 { 82 sqlite3_stmt_scanstatus_reset [db version -last-stmt-ptr] 83 } {} 84 85 do_scanstatus_test 1.10 { 86 nLoop 0 nVisit 0 nEst 2.0 zName t2 zExplain 87 {SEARCH t2 USING INTEGER PRIMARY KEY (rowid>?)} 88 nLoop 0 nVisit 0 nEst 2.0 zName t1 zExplain {SCAN 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 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 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 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 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 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 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 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 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 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 a1 USING INDEX a1a (a=?)} 198 nLoop 1 nVisit 1 nEst 10.0 zName a1bc 199 zExplain {SEARCH 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 a1 USING INDEX a1a (a>? AND a<?)} 208 nLoop 1 nVisit 21 nEst 16384.0 zName a1bc 209 zExplain {SEARCH 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 y USING COVERING INDEX a1bc (b>? AND b<?)} 219 nLoop 10 nVisit 90 nEst 16384.0 zName a1a 220 zExplain {SEARCH 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 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 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 p1 USING INDEX sqlite_autoindex_p1_1 (x=?)} 270 271 nLoop 1 nVisit 3 nEst 262144.0 zName c1 zExplain {SCAN 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 ii USING COVERING INDEX t1bc} 317 nLoop 1 nVisit 2 nEst 8.0 zName sqlite_autoindex_t1_1 318 zExplain {SEARCH 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 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 } {SEARCH 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 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 QUERY PLAN 344 |--SCAN t1 USING COVERING INDEX t1bc 345 `--SEARCH t2 USING COVERING INDEX t2xy (ANY(x) AND y=?) 346 } 347 do_execsql_test 5.4.2 { 348 SELECT count(*) FROM t1, t2 WHERE y = c; 349 } {200} 350 do_scanstatus_test 5.4.3 { 351 nLoop 1 nVisit 10 nEst 10.0 zName t1bc 352 zExplain {SCAN t1 USING COVERING INDEX t1bc} 353 nLoop 10 nVisit 200 nEst 56.0 zName t2xy 354 zExplain {SEARCH t2 USING COVERING INDEX t2xy (ANY(x) AND y=?)} 355 } 356 357 do_eqp_test 5.5.1 { 358 SELECT count(*) FROM t1, t3 WHERE y = c; 359 } { 360 QUERY PLAN 361 |--SCAN t3 362 `--SEARCH t1 USING AUTOMATIC COVERING INDEX (c=?) 363 } 364 do_execsql_test 5.5.2 { 365 SELECT count(*) FROM t1, t3 WHERE y = c; 366 } {200} 367 do_scanstatus_test 5.5.3 { 368 nLoop 1 nVisit 501 nEst 480.0 zName t3 zExplain {SCAN t3} 369 nLoop 501 nVisit 200 nEst 20.0 zName auto-index zExplain 370 {SEARCH t1 USING AUTOMATIC COVERING INDEX (c=?)} 371 } 372 373 #------------------------------------------------------------------------- 374 # Virtual table scans 375 # 376 ifcapable fts3 { 377 do_execsql_test 6.0 { 378 CREATE VIRTUAL TABLE ft1 USING fts4; 379 INSERT INTO ft1 VALUES('a d c f g h e i f c'); 380 INSERT INTO ft1 VALUES('g c h b g b f f f g'); 381 INSERT INTO ft1 VALUES('h h c c h f a e d d'); 382 INSERT INTO ft1 VALUES('e j i j i e b c f g'); 383 INSERT INTO ft1 VALUES('g f b g j c h a d f'); 384 INSERT INTO ft1 VALUES('j i a e g f a i a c'); 385 INSERT INTO ft1 VALUES('f d g g j j c a h g'); 386 INSERT INTO ft1 VALUES('b d h a d j j j b i'); 387 INSERT INTO ft1 VALUES('j e a b j e c b c i'); 388 INSERT INTO ft1 VALUES('a d e f b j j c g d'); 389 } 390 do_execsql_test 6.1.1 { 391 SELECT count(*) FROM ft1 WHERE ft1 MATCH 'd' 392 } {6} 393 do_scanstatus_test 6.1.2 { 394 nLoop 1 nVisit 6 nEst 24.0 zName ft1 zExplain 395 {SCAN ft1 VIRTUAL TABLE INDEX 3:} 396 } 397 } 398 399 400 finish_test