| OLD | NEW |
|---|
| (Empty) |
| 1 # 2010 November 6 | |
| 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 | |
| 16 ifcapable !compound { | |
| 17 finish_test | |
| 18 return | |
| 19 } | |
| 20 | |
| 21 set testprefix eqp | |
| 22 | |
| 23 #------------------------------------------------------------------------- | |
| 24 # | |
| 25 # eqp-1.*: Assorted tests. | |
| 26 # eqp-2.*: Tests for single select statements. | |
| 27 # eqp-3.*: Select statements that execute sub-selects. | |
| 28 # eqp-4.*: Compound select statements. | |
| 29 # ... | |
| 30 # eqp-7.*: "SELECT count(*) FROM tbl" statements (VDBE code OP_Count). | |
| 31 # | |
| 32 | |
| 33 proc det {args} { uplevel do_eqp_test $args } | |
| 34 | |
| 35 do_execsql_test 1.1 { | |
| 36 CREATE TABLE t1(a INT, b INT, ex TEXT); | |
| 37 CREATE INDEX i1 ON t1(a); | |
| 38 CREATE INDEX i2 ON t1(b); | |
| 39 CREATE TABLE t2(a INT, b INT, ex TEXT); | |
| 40 CREATE TABLE t3(a INT, b INT, ex TEXT); | |
| 41 } | |
| 42 | |
| 43 do_eqp_test 1.2 { | |
| 44 SELECT * FROM t2, t1 WHERE t1.a=1 OR t1.b=2; | |
| 45 } { | |
| 46 0 0 1 {SEARCH TABLE t1 USING INDEX i1 (a=?)} | |
| 47 0 0 1 {SEARCH TABLE t1 USING INDEX i2 (b=?)} | |
| 48 0 1 0 {SCAN TABLE t2} | |
| 49 } | |
| 50 do_eqp_test 1.3 { | |
| 51 SELECT * FROM t2 CROSS JOIN t1 WHERE t1.a=1 OR t1.b=2; | |
| 52 } { | |
| 53 0 0 0 {SCAN TABLE t2} | |
| 54 0 1 1 {SEARCH TABLE t1 USING INDEX i1 (a=?)} | |
| 55 0 1 1 {SEARCH TABLE t1 USING INDEX i2 (b=?)} | |
| 56 } | |
| 57 do_eqp_test 1.3 { | |
| 58 SELECT a FROM t1 ORDER BY a | |
| 59 } { | |
| 60 0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1} | |
| 61 } | |
| 62 do_eqp_test 1.4 { | |
| 63 SELECT a FROM t1 ORDER BY +a | |
| 64 } { | |
| 65 0 0 0 {SCAN TABLE t1 USING COVERING INDEX i1} | |
| 66 0 0 0 {USE TEMP B-TREE FOR ORDER BY} | |
| 67 } | |
| 68 do_eqp_test 1.5 { | |
| 69 SELECT a FROM t1 WHERE a=4 | |
| 70 } { | |
| 71 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i1 (a=?)} | |
| 72 } | |
| 73 do_eqp_test 1.6 { | |
| 74 SELECT DISTINCT count(*) FROM t3 GROUP BY a; | |
| 75 } { | |
| 76 0 0 0 {SCAN TABLE t3} | |
| 77 0 0 0 {USE TEMP B-TREE FOR GROUP BY} | |
| 78 0 0 0 {USE TEMP B-TREE FOR DISTINCT} | |
| 79 } | |
| 80 | |
| 81 do_eqp_test 1.7 { | |
| 82 SELECT * FROM t3 JOIN (SELECT 1) | |
| 83 } { | |
| 84 0 0 1 {SCAN SUBQUERY 1} | |
| 85 0 1 0 {SCAN TABLE t3} | |
| 86 } | |
| 87 do_eqp_test 1.8 { | |
| 88 SELECT * FROM t3 JOIN (SELECT 1 UNION SELECT 2) | |
| 89 } { | |
| 90 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)} | |
| 91 0 0 1 {SCAN SUBQUERY 1} | |
| 92 0 1 0 {SCAN TABLE t3} | |
| 93 } | |
| 94 do_eqp_test 1.9 { | |
| 95 SELECT * FROM t3 JOIN (SELECT 1 EXCEPT SELECT a FROM t3 LIMIT 17) | |
| 96 } { | |
| 97 3 0 0 {SCAN TABLE t3} | |
| 98 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (EXCEPT)} | |
| 99 0 0 1 {SCAN SUBQUERY 1} | |
| 100 0 1 0 {SCAN TABLE t3} | |
| 101 } | |
| 102 do_eqp_test 1.10 { | |
| 103 SELECT * FROM t3 JOIN (SELECT 1 INTERSECT SELECT a FROM t3 LIMIT 17) | |
| 104 } { | |
| 105 3 0 0 {SCAN TABLE t3} | |
| 106 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (INTERSECT)} | |
| 107 0 0 1 {SCAN SUBQUERY 1} | |
| 108 0 1 0 {SCAN TABLE t3} | |
| 109 } | |
| 110 | |
| 111 do_eqp_test 1.11 { | |
| 112 SELECT * FROM t3 JOIN (SELECT 1 UNION ALL SELECT a FROM t3 LIMIT 17) | |
| 113 } { | |
| 114 3 0 0 {SCAN TABLE t3} | |
| 115 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 (UNION ALL)} | |
| 116 0 0 1 {SCAN SUBQUERY 1} | |
| 117 0 1 0 {SCAN TABLE t3} | |
| 118 } | |
| 119 | |
| 120 #------------------------------------------------------------------------- | |
| 121 # Test cases eqp-2.* - tests for single select statements. | |
| 122 # | |
| 123 drop_all_tables | |
| 124 do_execsql_test 2.1 { | |
| 125 CREATE TABLE t1(x INT, y INT, ex TEXT); | |
| 126 | |
| 127 CREATE TABLE t2(x INT, y INT, ex TEXT); | |
| 128 CREATE INDEX t2i1 ON t2(x); | |
| 129 } | |
| 130 | |
| 131 det 2.2.1 "SELECT DISTINCT min(x), max(x) FROM t1 GROUP BY x ORDER BY 1" { | |
| 132 0 0 0 {SCAN TABLE t1} | |
| 133 0 0 0 {USE TEMP B-TREE FOR GROUP BY} | |
| 134 0 0 0 {USE TEMP B-TREE FOR DISTINCT} | |
| 135 0 0 0 {USE TEMP B-TREE FOR ORDER BY} | |
| 136 } | |
| 137 det 2.2.2 "SELECT DISTINCT min(x), max(x) FROM t2 GROUP BY x ORDER BY 1" { | |
| 138 0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1} | |
| 139 0 0 0 {USE TEMP B-TREE FOR DISTINCT} | |
| 140 0 0 0 {USE TEMP B-TREE FOR ORDER BY} | |
| 141 } | |
| 142 det 2.2.3 "SELECT DISTINCT * FROM t1" { | |
| 143 0 0 0 {SCAN TABLE t1} | |
| 144 0 0 0 {USE TEMP B-TREE FOR DISTINCT} | |
| 145 } | |
| 146 det 2.2.4 "SELECT DISTINCT * FROM t1, t2" { | |
| 147 0 0 0 {SCAN TABLE t1} | |
| 148 0 1 1 {SCAN TABLE t2} | |
| 149 0 0 0 {USE TEMP B-TREE FOR DISTINCT} | |
| 150 } | |
| 151 det 2.2.5 "SELECT DISTINCT * FROM t1, t2 ORDER BY t1.x" { | |
| 152 0 0 0 {SCAN TABLE t1} | |
| 153 0 1 1 {SCAN TABLE t2} | |
| 154 0 0 0 {USE TEMP B-TREE FOR DISTINCT} | |
| 155 0 0 0 {USE TEMP B-TREE FOR ORDER BY} | |
| 156 } | |
| 157 det 2.2.6 "SELECT DISTINCT t2.x FROM t1, t2 ORDER BY t2.x" { | |
| 158 0 0 1 {SCAN TABLE t2 USING COVERING INDEX t2i1} | |
| 159 0 1 0 {SCAN TABLE t1} | |
| 160 } | |
| 161 | |
| 162 det 2.3.1 "SELECT max(x) FROM t2" { | |
| 163 0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2i1} | |
| 164 } | |
| 165 det 2.3.2 "SELECT min(x) FROM t2" { | |
| 166 0 0 0 {SEARCH TABLE t2 USING COVERING INDEX t2i1} | |
| 167 } | |
| 168 det 2.3.3 "SELECT min(x), max(x) FROM t2" { | |
| 169 0 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1} | |
| 170 } | |
| 171 | |
| 172 det 2.4.1 "SELECT * FROM t1 WHERE rowid=?" { | |
| 173 0 0 0 {SEARCH TABLE t1 USING INTEGER PRIMARY KEY (rowid=?)} | |
| 174 } | |
| 175 | |
| 176 | |
| 177 | |
| 178 #------------------------------------------------------------------------- | |
| 179 # Test cases eqp-3.* - tests for select statements that use sub-selects. | |
| 180 # | |
| 181 do_eqp_test 3.1.1 { | |
| 182 SELECT (SELECT x FROM t1 AS sub) FROM t1; | |
| 183 } { | |
| 184 0 0 0 {SCAN TABLE t1} | |
| 185 0 0 0 {EXECUTE SCALAR SUBQUERY 1} | |
| 186 1 0 0 {SCAN TABLE t1 AS sub} | |
| 187 } | |
| 188 do_eqp_test 3.1.2 { | |
| 189 SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub); | |
| 190 } { | |
| 191 0 0 0 {SCAN TABLE t1} | |
| 192 0 0 0 {EXECUTE SCALAR SUBQUERY 1} | |
| 193 1 0 0 {SCAN TABLE t1 AS sub} | |
| 194 } | |
| 195 do_eqp_test 3.1.3 { | |
| 196 SELECT * FROM t1 WHERE (SELECT x FROM t1 AS sub ORDER BY y); | |
| 197 } { | |
| 198 0 0 0 {SCAN TABLE t1} | |
| 199 0 0 0 {EXECUTE SCALAR SUBQUERY 1} | |
| 200 1 0 0 {SCAN TABLE t1 AS sub} | |
| 201 1 0 0 {USE TEMP B-TREE FOR ORDER BY} | |
| 202 } | |
| 203 do_eqp_test 3.1.4 { | |
| 204 SELECT * FROM t1 WHERE (SELECT x FROM t2 ORDER BY x); | |
| 205 } { | |
| 206 0 0 0 {SCAN TABLE t1} | |
| 207 0 0 0 {EXECUTE SCALAR SUBQUERY 1} | |
| 208 1 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1} | |
| 209 } | |
| 210 | |
| 211 det 3.2.1 { | |
| 212 SELECT * FROM (SELECT * FROM t1 ORDER BY x LIMIT 10) ORDER BY y LIMIT 5 | |
| 213 } { | |
| 214 1 0 0 {SCAN TABLE t1} | |
| 215 1 0 0 {USE TEMP B-TREE FOR ORDER BY} | |
| 216 0 0 0 {SCAN SUBQUERY 1} | |
| 217 0 0 0 {USE TEMP B-TREE FOR ORDER BY} | |
| 218 } | |
| 219 det 3.2.2 { | |
| 220 SELECT * FROM | |
| 221 (SELECT * FROM t1 ORDER BY x LIMIT 10) AS x1, | |
| 222 (SELECT * FROM t2 ORDER BY x LIMIT 10) AS x2 | |
| 223 ORDER BY x2.y LIMIT 5 | |
| 224 } { | |
| 225 1 0 0 {SCAN TABLE t1} | |
| 226 1 0 0 {USE TEMP B-TREE FOR ORDER BY} | |
| 227 2 0 0 {SCAN TABLE t2 USING INDEX t2i1} | |
| 228 0 0 0 {SCAN SUBQUERY 1 AS x1} | |
| 229 0 1 1 {SCAN SUBQUERY 2 AS x2} | |
| 230 0 0 0 {USE TEMP B-TREE FOR ORDER BY} | |
| 231 } | |
| 232 | |
| 233 det 3.3.1 { | |
| 234 SELECT * FROM t1 WHERE y IN (SELECT y FROM t2) | |
| 235 } { | |
| 236 0 0 0 {SCAN TABLE t1} | |
| 237 0 0 0 {EXECUTE LIST SUBQUERY 1} | |
| 238 1 0 0 {SCAN TABLE t2} | |
| 239 } | |
| 240 det 3.3.2 { | |
| 241 SELECT * FROM t1 WHERE y IN (SELECT y FROM t2 WHERE t1.x!=t2.x) | |
| 242 } { | |
| 243 0 0 0 {SCAN TABLE t1} | |
| 244 0 0 0 {EXECUTE CORRELATED LIST SUBQUERY 1} | |
| 245 1 0 0 {SCAN TABLE t2} | |
| 246 } | |
| 247 det 3.3.3 { | |
| 248 SELECT * FROM t1 WHERE EXISTS (SELECT y FROM t2 WHERE t1.x!=t2.x) | |
| 249 } { | |
| 250 0 0 0 {SCAN TABLE t1} | |
| 251 0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 1} | |
| 252 1 0 0 {SCAN TABLE t2} | |
| 253 } | |
| 254 | |
| 255 #------------------------------------------------------------------------- | |
| 256 # Test cases eqp-4.* - tests for composite select statements. | |
| 257 # | |
| 258 do_eqp_test 4.1.1 { | |
| 259 SELECT * FROM t1 UNION ALL SELECT * FROM t2 | |
| 260 } { | |
| 261 1 0 0 {SCAN TABLE t1} | |
| 262 2 0 0 {SCAN TABLE t2} | |
| 263 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)} | |
| 264 } | |
| 265 do_eqp_test 4.1.2 { | |
| 266 SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 2 | |
| 267 } { | |
| 268 1 0 0 {SCAN TABLE t1} | |
| 269 1 0 0 {USE TEMP B-TREE FOR ORDER BY} | |
| 270 2 0 0 {SCAN TABLE t2} | |
| 271 2 0 0 {USE TEMP B-TREE FOR ORDER BY} | |
| 272 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)} | |
| 273 } | |
| 274 do_eqp_test 4.1.3 { | |
| 275 SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 2 | |
| 276 } { | |
| 277 1 0 0 {SCAN TABLE t1} | |
| 278 1 0 0 {USE TEMP B-TREE FOR ORDER BY} | |
| 279 2 0 0 {SCAN TABLE t2} | |
| 280 2 0 0 {USE TEMP B-TREE FOR ORDER BY} | |
| 281 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION)} | |
| 282 } | |
| 283 do_eqp_test 4.1.4 { | |
| 284 SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 2 | |
| 285 } { | |
| 286 1 0 0 {SCAN TABLE t1} | |
| 287 1 0 0 {USE TEMP B-TREE FOR ORDER BY} | |
| 288 2 0 0 {SCAN TABLE t2} | |
| 289 2 0 0 {USE TEMP B-TREE FOR ORDER BY} | |
| 290 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (INTERSECT)} | |
| 291 } | |
| 292 do_eqp_test 4.1.5 { | |
| 293 SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 2 | |
| 294 } { | |
| 295 1 0 0 {SCAN TABLE t1} | |
| 296 1 0 0 {USE TEMP B-TREE FOR ORDER BY} | |
| 297 2 0 0 {SCAN TABLE t2} | |
| 298 2 0 0 {USE TEMP B-TREE FOR ORDER BY} | |
| 299 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)} | |
| 300 } | |
| 301 | |
| 302 do_eqp_test 4.2.2 { | |
| 303 SELECT * FROM t1 UNION ALL SELECT * FROM t2 ORDER BY 1 | |
| 304 } { | |
| 305 1 0 0 {SCAN TABLE t1} | |
| 306 1 0 0 {USE TEMP B-TREE FOR ORDER BY} | |
| 307 2 0 0 {SCAN TABLE t2 USING INDEX t2i1} | |
| 308 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION ALL)} | |
| 309 } | |
| 310 do_eqp_test 4.2.3 { | |
| 311 SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY 1 | |
| 312 } { | |
| 313 1 0 0 {SCAN TABLE t1} | |
| 314 1 0 0 {USE TEMP B-TREE FOR ORDER BY} | |
| 315 2 0 0 {SCAN TABLE t2 USING INDEX t2i1} | |
| 316 2 0 0 {USE TEMP B-TREE FOR RIGHT PART OF ORDER BY} | |
| 317 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (UNION)} | |
| 318 } | |
| 319 do_eqp_test 4.2.4 { | |
| 320 SELECT * FROM t1 INTERSECT SELECT * FROM t2 ORDER BY 1 | |
| 321 } { | |
| 322 1 0 0 {SCAN TABLE t1} | |
| 323 1 0 0 {USE TEMP B-TREE FOR ORDER BY} | |
| 324 2 0 0 {SCAN TABLE t2 USING INDEX t2i1} | |
| 325 2 0 0 {USE TEMP B-TREE FOR RIGHT PART OF ORDER BY} | |
| 326 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (INTERSECT)} | |
| 327 } | |
| 328 do_eqp_test 4.2.5 { | |
| 329 SELECT * FROM t1 EXCEPT SELECT * FROM t2 ORDER BY 1 | |
| 330 } { | |
| 331 1 0 0 {SCAN TABLE t1} | |
| 332 1 0 0 {USE TEMP B-TREE FOR ORDER BY} | |
| 333 2 0 0 {SCAN TABLE t2 USING INDEX t2i1} | |
| 334 2 0 0 {USE TEMP B-TREE FOR RIGHT PART OF ORDER BY} | |
| 335 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)} | |
| 336 } | |
| 337 | |
| 338 do_eqp_test 4.3.1 { | |
| 339 SELECT x FROM t1 UNION SELECT x FROM t2 | |
| 340 } { | |
| 341 1 0 0 {SCAN TABLE t1} | |
| 342 2 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1} | |
| 343 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)} | |
| 344 } | |
| 345 | |
| 346 do_eqp_test 4.3.2 { | |
| 347 SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1 | |
| 348 } { | |
| 349 2 0 0 {SCAN TABLE t1} | |
| 350 3 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1} | |
| 351 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)} | |
| 352 4 0 0 {SCAN TABLE t1} | |
| 353 0 0 0 {COMPOUND SUBQUERIES 1 AND 4 USING TEMP B-TREE (UNION)} | |
| 354 } | |
| 355 do_eqp_test 4.3.3 { | |
| 356 SELECT x FROM t1 UNION SELECT x FROM t2 UNION SELECT x FROM t1 ORDER BY 1 | |
| 357 } { | |
| 358 2 0 0 {SCAN TABLE t1} | |
| 359 2 0 0 {USE TEMP B-TREE FOR ORDER BY} | |
| 360 3 0 0 {SCAN TABLE t2 USING COVERING INDEX t2i1} | |
| 361 1 0 0 {COMPOUND SUBQUERIES 2 AND 3 (UNION)} | |
| 362 4 0 0 {SCAN TABLE t1} | |
| 363 4 0 0 {USE TEMP B-TREE FOR ORDER BY} | |
| 364 0 0 0 {COMPOUND SUBQUERIES 1 AND 4 (UNION)} | |
| 365 } | |
| 366 | |
| 367 #------------------------------------------------------------------------- | |
| 368 # This next block of tests verifies that the examples on the | |
| 369 # lang_explain.html page are correct. | |
| 370 # | |
| 371 drop_all_tables | |
| 372 | |
| 373 # EVIDENCE-OF: R-47779-47605 sqlite> EXPLAIN QUERY PLAN SELECT a, b | |
| 374 # FROM t1 WHERE a=1; | |
| 375 # 0|0|0|SCAN TABLE t1 | |
| 376 # | |
| 377 do_execsql_test 5.1.0 { CREATE TABLE t1(a INT, b INT, ex TEXT) } | |
| 378 det 5.1.1 "SELECT a, b FROM t1 WHERE a=1" { | |
| 379 0 0 0 {SCAN TABLE t1} | |
| 380 } | |
| 381 | |
| 382 # EVIDENCE-OF: R-55852-17599 sqlite> CREATE INDEX i1 ON t1(a); | |
| 383 # sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; | |
| 384 # 0|0|0|SEARCH TABLE t1 USING INDEX i1 | |
| 385 # | |
| 386 do_execsql_test 5.2.0 { CREATE INDEX i1 ON t1(a) } | |
| 387 det 5.2.1 "SELECT a, b FROM t1 WHERE a=1" { | |
| 388 0 0 0 {SEARCH TABLE t1 USING INDEX i1 (a=?)} | |
| 389 } | |
| 390 | |
| 391 # EVIDENCE-OF: R-21179-11011 sqlite> CREATE INDEX i2 ON t1(a, b); | |
| 392 # sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; | |
| 393 # 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) | |
| 394 # | |
| 395 do_execsql_test 5.3.0 { CREATE INDEX i2 ON t1(a, b) } | |
| 396 det 5.3.1 "SELECT a, b FROM t1 WHERE a=1" { | |
| 397 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)} | |
| 398 } | |
| 399 | |
| 400 # EVIDENCE-OF: R-09991-48941 sqlite> EXPLAIN QUERY PLAN | |
| 401 # SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2; | |
| 402 # 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) | |
| 403 # 0|1|1|SCAN TABLE t2 | |
| 404 # | |
| 405 do_execsql_test 5.4.0 {CREATE TABLE t2(c INT, d INT, ex TEXT)} | |
| 406 det 5.4.1 "SELECT t1.a, t2.c FROM t1, t2 WHERE t1.a=1 AND t1.b>2" { | |
| 407 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)} | |
| 408 0 1 1 {SCAN TABLE t2} | |
| 409 } | |
| 410 | |
| 411 # EVIDENCE-OF: R-33626-61085 sqlite> EXPLAIN QUERY PLAN | |
| 412 # SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2; | |
| 413 # 0|0|1|SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?) | |
| 414 # 0|1|0|SCAN TABLE t2 | |
| 415 # | |
| 416 det 5.5 "SELECT t1.a, t2.c FROM t2, t1 WHERE t1.a=1 AND t1.b>2" { | |
| 417 0 0 1 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)} | |
| 418 0 1 0 {SCAN TABLE t2} | |
| 419 } | |
| 420 | |
| 421 # EVIDENCE-OF: R-04002-25654 sqlite> CREATE INDEX i3 ON t1(b); | |
| 422 # sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2; | |
| 423 # 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) | |
| 424 # 0|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?) | |
| 425 # | |
| 426 do_execsql_test 5.5.0 {CREATE INDEX i3 ON t1(b)} | |
| 427 det 5.6.1 "SELECT a, b FROM t1 WHERE a=1 OR b=2" { | |
| 428 0 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)} | |
| 429 0 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?)} | |
| 430 } | |
| 431 | |
| 432 # EVIDENCE-OF: R-24577-38891 sqlite> EXPLAIN QUERY PLAN | |
| 433 # SELECT c, d FROM t2 ORDER BY c; | |
| 434 # 0|0|0|SCAN TABLE t2 | |
| 435 # 0|0|0|USE TEMP B-TREE FOR ORDER BY | |
| 436 # | |
| 437 det 5.7 "SELECT c, d FROM t2 ORDER BY c" { | |
| 438 0 0 0 {SCAN TABLE t2} | |
| 439 0 0 0 {USE TEMP B-TREE FOR ORDER BY} | |
| 440 } | |
| 441 | |
| 442 # EVIDENCE-OF: R-58157-12355 sqlite> CREATE INDEX i4 ON t2(c); | |
| 443 # sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c; | |
| 444 # 0|0|0|SCAN TABLE t2 USING INDEX i4 | |
| 445 # | |
| 446 do_execsql_test 5.8.0 {CREATE INDEX i4 ON t2(c)} | |
| 447 det 5.8.1 "SELECT c, d FROM t2 ORDER BY c" { | |
| 448 0 0 0 {SCAN TABLE t2 USING INDEX i4} | |
| 449 } | |
| 450 | |
| 451 # EVIDENCE-OF: R-13931-10421 sqlite> EXPLAIN QUERY PLAN SELECT | |
| 452 # (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2; | |
| 453 # 0|0|0|SCAN TABLE t2 | |
| 454 # 0|0|0|EXECUTE SCALAR SUBQUERY 1 | |
| 455 # 1|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) | |
| 456 # 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2 | |
| 457 # 2|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?) | |
| 458 # | |
| 459 det 5.9 { | |
| 460 SELECT (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2 | |
| 461 } { | |
| 462 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i4} | |
| 463 0 0 0 {EXECUTE SCALAR SUBQUERY 1} | |
| 464 1 0 0 {SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)} | |
| 465 0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2} | |
| 466 2 0 0 {SEARCH TABLE t1 USING INDEX i3 (b=?)} | |
| 467 } | |
| 468 | |
| 469 # EVIDENCE-OF: R-50892-45943 sqlite> EXPLAIN QUERY PLAN | |
| 470 # SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x; | |
| 471 # 1|0|0|SCAN TABLE t1 USING COVERING INDEX i2 | |
| 472 # 0|0|0|SCAN SUBQUERY 1 | |
| 473 # 0|0|0|USE TEMP B-TREE FOR GROUP BY | |
| 474 # | |
| 475 det 5.10 { | |
| 476 SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x | |
| 477 } { | |
| 478 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2} | |
| 479 0 0 0 {SCAN SUBQUERY 1} | |
| 480 0 0 0 {USE TEMP B-TREE FOR GROUP BY} | |
| 481 } | |
| 482 | |
| 483 # EVIDENCE-OF: R-46219-33846 sqlite> EXPLAIN QUERY PLAN | |
| 484 # SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1; | |
| 485 # 0|0|0|SEARCH TABLE t2 USING INDEX i4 (c=?) | |
| 486 # 0|1|1|SCAN TABLE t1 | |
| 487 # | |
| 488 det 5.11 "SELECT a, b FROM (SELECT * FROM t2 WHERE c=1), t1" { | |
| 489 0 0 0 {SEARCH TABLE t2 USING INDEX i4 (c=?)} | |
| 490 0 1 1 {SCAN TABLE t1 USING COVERING INDEX i2} | |
| 491 } | |
| 492 | |
| 493 # EVIDENCE-OF: R-37879-39987 sqlite> EXPLAIN QUERY PLAN | |
| 494 # SELECT a FROM t1 UNION SELECT c FROM t2; | |
| 495 # 1|0|0|SCAN TABLE t1 | |
| 496 # 2|0|0|SCAN TABLE t2 | |
| 497 # 0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION) | |
| 498 # | |
| 499 det 5.12 "SELECT a,b FROM t1 UNION SELECT c, 99 FROM t2" { | |
| 500 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i2} | |
| 501 2 0 0 {SCAN TABLE t2 USING COVERING INDEX i4} | |
| 502 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)} | |
| 503 } | |
| 504 | |
| 505 # EVIDENCE-OF: R-44864-63011 sqlite> EXPLAIN QUERY PLAN | |
| 506 # SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1; | |
| 507 # 1|0|0|SCAN TABLE t1 USING COVERING INDEX i2 | |
| 508 # 2|0|0|SCAN TABLE t2 2|0|0|USE TEMP B-TREE FOR ORDER BY | |
| 509 # 0|0|0|COMPOUND SUBQUERIES 1 AND 2 (EXCEPT) | |
| 510 # | |
| 511 det 5.13 "SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1" { | |
| 512 1 0 0 {SCAN TABLE t1 USING COVERING INDEX i1} | |
| 513 2 0 0 {SCAN TABLE t2} | |
| 514 2 0 0 {USE TEMP B-TREE FOR ORDER BY} | |
| 515 0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)} | |
| 516 } | |
| 517 | |
| 518 | |
| 519 #------------------------------------------------------------------------- | |
| 520 # The following tests - eqp-6.* - test that the example C code on | |
| 521 # documentation page eqp.html works. The C code is duplicated in test1.c | |
| 522 # and wrapped in Tcl command [print_explain_query_plan] | |
| 523 # | |
| 524 set boilerplate { | |
| 525 proc explain_query_plan {db sql} { | |
| 526 set stmt [sqlite3_prepare_v2 db $sql -1 DUMMY] | |
| 527 print_explain_query_plan $stmt | |
| 528 sqlite3_finalize $stmt | |
| 529 } | |
| 530 sqlite3 db test.db | |
| 531 explain_query_plan db {%SQL%} | |
| 532 db close | |
| 533 exit | |
| 534 } | |
| 535 | |
| 536 # Do a "Print Explain Query Plan" test. | |
| 537 proc do_peqp_test {tn sql res} { | |
| 538 set fd [open script.tcl w] | |
| 539 puts $fd [string map [list %SQL% $sql] $::boilerplate] | |
| 540 close $fd | |
| 541 | |
| 542 uplevel do_test $tn [list { | |
| 543 set fd [open "|[info nameofexec] script.tcl"] | |
| 544 set data [read $fd] | |
| 545 close $fd | |
| 546 set data | |
| 547 }] [list $res] | |
| 548 } | |
| 549 | |
| 550 do_peqp_test 6.1 { | |
| 551 SELECT a, b FROM t1 EXCEPT SELECT d, 99 FROM t2 ORDER BY 1 | |
| 552 } [string trimleft { | |
| 553 1 0 0 SCAN TABLE t1 USING COVERING INDEX i2 | |
| 554 2 0 0 SCAN TABLE t2 | |
| 555 2 0 0 USE TEMP B-TREE FOR ORDER BY | |
| 556 0 0 0 COMPOUND SUBQUERIES 1 AND 2 (EXCEPT) | |
| 557 }] | |
| 558 | |
| 559 #------------------------------------------------------------------------- | |
| 560 # The following tests - eqp-7.* - test that queries that use the OP_Count | |
| 561 # optimization return something sensible with EQP. | |
| 562 # | |
| 563 drop_all_tables | |
| 564 | |
| 565 do_execsql_test 7.0 { | |
| 566 CREATE TABLE t1(a INT, b INT, ex CHAR(100)); | |
| 567 CREATE TABLE t2(a INT, b INT, ex CHAR(100)); | |
| 568 CREATE INDEX i1 ON t2(a); | |
| 569 } | |
| 570 | |
| 571 det 7.1 "SELECT count(*) FROM t1" { | |
| 572 0 0 0 {SCAN TABLE t1} | |
| 573 } | |
| 574 | |
| 575 det 7.2 "SELECT count(*) FROM t2" { | |
| 576 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i1} | |
| 577 } | |
| 578 | |
| 579 do_execsql_test 7.3 { | |
| 580 INSERT INTO t1(a,b) VALUES(1, 2); | |
| 581 INSERT INTO t1(a,b) VALUES(3, 4); | |
| 582 | |
| 583 INSERT INTO t2(a,b) VALUES(1, 2); | |
| 584 INSERT INTO t2(a,b) VALUES(3, 4); | |
| 585 INSERT INTO t2(a,b) VALUES(5, 6); | |
| 586 | |
| 587 ANALYZE; | |
| 588 } | |
| 589 | |
| 590 db close | |
| 591 sqlite3 db test.db | |
| 592 | |
| 593 det 7.4 "SELECT count(*) FROM t1" { | |
| 594 0 0 0 {SCAN TABLE t1} | |
| 595 } | |
| 596 | |
| 597 det 7.5 "SELECT count(*) FROM t2" { | |
| 598 0 0 0 {SCAN TABLE t2 USING COVERING INDEX i1} | |
| 599 } | |
| 600 | |
| 601 #------------------------------------------------------------------------- | |
| 602 # The following tests - eqp-8.* - test that queries that use the OP_Count | |
| 603 # optimization return something sensible with EQP. | |
| 604 # | |
| 605 drop_all_tables | |
| 606 | |
| 607 do_execsql_test 8.0 { | |
| 608 CREATE TABLE t1(a, b, c, PRIMARY KEY(b, c)) WITHOUT ROWID; | |
| 609 CREATE TABLE t2(a, b, c); | |
| 610 } | |
| 611 | |
| 612 det 8.1.1 "SELECT * FROM t2" { | |
| 613 0 0 0 {SCAN TABLE t2} | |
| 614 } | |
| 615 | |
| 616 det 8.1.2 "SELECT * FROM t2 WHERE rowid=?" { | |
| 617 0 0 0 {SEARCH TABLE t2 USING INTEGER PRIMARY KEY (rowid=?)} | |
| 618 } | |
| 619 | |
| 620 det 8.1.3 "SELECT count(*) FROM t2" { | |
| 621 0 0 0 {SCAN TABLE t2} | |
| 622 } | |
| 623 | |
| 624 det 8.2.1 "SELECT * FROM t1" { | |
| 625 0 0 0 {SCAN TABLE t1} | |
| 626 } | |
| 627 | |
| 628 det 8.2.2 "SELECT * FROM t1 WHERE b=?" { | |
| 629 0 0 0 {SEARCH TABLE t1 USING PRIMARY KEY (b=?)} | |
| 630 } | |
| 631 | |
| 632 det 8.2.3 "SELECT * FROM t1 WHERE b=? AND c=?" { | |
| 633 0 0 0 {SEARCH TABLE t1 USING PRIMARY KEY (b=? AND c=?)} | |
| 634 } | |
| 635 | |
| 636 det 8.2.4 "SELECT count(*) FROM t1" { | |
| 637 0 0 0 {SCAN TABLE t1} | |
| 638 } | |
| 639 | |
| 640 | |
| 641 | |
| 642 | |
| 643 | |
| 644 | |
| 645 | |
| 646 finish_test | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2846743003:
[sql] Remove SQLite 3.10.2 reference directory. (Closed)
