Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Add EXPLAIN QUERY PLAN test cases to check that the examples in the documentation work. |
|---|---|
| Downloads: | Tarball | ZIP archive |
| Timelines: | family | ancestors | descendants | both | experimental |
| Files: | files | file ages | folders |
| SHA1: |
85fdad850a4da33fd74f5394b921a63e |
| User & Date: | dan 2010-11-12 17:41:38.000 |
Context
|
2010-11-13
| ||
| 16:42 | Change the EXPLAIN QUERY PLAN output to use "USING INDEX" instead of "BY INDEX", and to use "SEARCH" instead of "SCAN" for loops that are not full-table scans. (Closed-Leaf check-in: 6611b76b02 user: dan tags: experimental) | |
|
2010-11-12
| ||
| 17:41 | Add EXPLAIN QUERY PLAN test cases to check that the examples in the documentation work. (check-in: 85fdad850a user: dan tags: experimental) | |
| 15:36 | Reduce the number of branches that need to be tested in the explainIndexRange() function of where.c. (check-in: 6fdae9a635 user: drh tags: experimental) | |
Changes
Changes to test/eqp.test.
| ︙ | ︙ | |||
316 317 318 319 320 321 322 323 324 325 |
2 0 0 {USE TEMP B-TREE FOR ORDER BY}
3 0 0 {SCAN TABLE t2 BY COVERING INDEX t2i1 (~1000000 rows)}
1 0 0 {COMPOUND SUBQUERIES 2 AND 3 (UNION)}
4 0 0 {SCAN TABLE t1 (~1000000 rows)}
4 0 0 {USE TEMP B-TREE FOR ORDER BY}
0 0 0 {COMPOUND SUBQUERIES 1 AND 4 (UNION)}
}
finish_test
| > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 |
2 0 0 {USE TEMP B-TREE FOR ORDER BY}
3 0 0 {SCAN TABLE t2 BY COVERING INDEX t2i1 (~1000000 rows)}
1 0 0 {COMPOUND SUBQUERIES 2 AND 3 (UNION)}
4 0 0 {SCAN TABLE t1 (~1000000 rows)}
4 0 0 {USE TEMP B-TREE FOR ORDER BY}
0 0 0 {COMPOUND SUBQUERIES 1 AND 4 (UNION)}
}
#-------------------------------------------------------------------------
# This next block of tests verifies that the examples on the
# lang_explain.html page are correct.
#
drop_all_tables
# EVIDENCE-OF: R-64208-08323 sqlite> EXPLAIN QUERY PLAN SELECT a, b
# FROM t1 WHERE a=1; 0|0|0|SCAN TABLE t1 (~100000 rows)
#
do_execsql_test 5.1.0 { CREATE TABLE t1(a, b) }
det 5.1.1 "SELECT a, b FROM t1 WHERE a=1" {
0 0 0 {SCAN TABLE t1 (~100000 rows)}
}
# EVIDENCE-OF: R-03114-52867 sqlite> CREATE INDEX i1 ON t1(a);
# sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
# 0|0|0|SCAN TABLE t1 BY INDEX i1 (a=?) (~10 rows)
do_execsql_test 5.2.0 { CREATE INDEX i1 ON t1(a) }
det 5.2.1 "SELECT a, b FROM t1 WHERE a=1" {
0 0 0 {SCAN TABLE t1 BY INDEX i1 (a=?) (~10 rows)}
}
# EVIDENCE-OF: R-20407-61322 sqlite> CREATE INDEX i2 ON t1(a, b);
# sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
# 0|0|0|SCAN TABLE t1 BY COVERING INDEX i2 (a=?) (~10 rows)
do_execsql_test 5.3.0 { CREATE INDEX i2 ON t1(a, b) }
det 5.3.1 "SELECT a, b FROM t1 WHERE a=1" {
0 0 0 {SCAN TABLE t1 BY COVERING INDEX i2 (a=?) (~10 rows)}
}
# EVIDENCE-OF: R-01893-00096 sqlite> EXPLAIN QUERY PLAN SELECT t1.*,
# t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2; 0|0|0|SCAN TABLE t1 BY
# COVERING INDEX i2 (a=? AND b>?) (~3 rows) 0|1|1|SCAN TABLE t2
# (~1000000 rows)
do_execsql_test 5.4.0 {CREATE TABLE t2(c, d)}
det 5.4.1 "SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2" {
0 0 0 {SCAN TABLE t1 BY COVERING INDEX i2 (a=? AND b>?) (~3 rows)}
0 1 1 {SCAN TABLE t2 (~1000000 rows)}
}
# EVIDENCE-OF: R-26531-36629 sqlite> EXPLAIN QUERY PLAN SELECT t1.*,
# t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2; 0|0|1|SCAN TABLE t1 BY
# COVERING INDEX i2 (a=? AND b>?) (~3 rows) 0|1|0|SCAN TABLE t2
# (~1000000 rows)
det 5.5 "SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2" {
0 0 1 {SCAN TABLE t1 BY COVERING INDEX i2 (a=? AND b>?) (~3 rows)}
0 1 0 {SCAN TABLE t2 (~1000000 rows)}
}
# EVIDENCE-OF: R-17671-37431 sqlite> CREATE INDEX i3 ON t1(b);
# sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2;
# 0|0|0|SCAN TABLE t1 BY COVERING INDEX i2 (a=?) (~10 rows) 0|0|0|SCAN
# TABLE t1 BY INDEX i3 (b=?) (~10 rows)
do_execsql_test 5.5.0 {CREATE INDEX i3 ON t1(b)}
det 5.6.1 "SELECT * FROM t1 WHERE a=1 OR b=2" {
0 0 0 {SCAN TABLE t1 BY COVERING INDEX i2 (a=?) (~10 rows)}
0 0 0 {SCAN TABLE t1 BY INDEX i3 (b=?) (~10 rows)}
}
# EVIDENCE-OF: R-33025-54904 sqlite> EXPLAIN QUERY PLAN SELECT c, d
# FROM t2 ORDER BY c; 0|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|USE TEMP
# B-TREE FOR ORDER BY
det 5.7 "SELECT c, d FROM t2 ORDER BY c" {
0 0 0 {SCAN TABLE t2 (~1000000 rows)}
0 0 0 {USE TEMP B-TREE FOR ORDER BY}
}
# EVIDENCE-OF: R-08354-12138 sqlite> CREATE INDEX i4 ON t2(c);
# sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c;
# 0|0|0|SCAN TABLE t2 BY INDEX i4 (~1000000 rows)
do_execsql_test 5.8.0 {CREATE INDEX i4 ON t2(c)}
det 5.8.1 "SELECT c, d FROM t2 ORDER BY c" {
0 0 0 {SCAN TABLE t2 BY INDEX i4 (~1000000 rows)}
}
# EVIDENCE-OF: R-01895-58356 sqlite> EXPLAIN QUERY PLAN SELECT
# (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2;
# 0|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|EXECUTE SCALAR SUBQUERY 1
# 1|0|0|SCAN TABLE t1 BY COVERING INDEX i2 (a=?) (~10 rows)
# 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2 2|0|0|SCAN TABLE t1 BY
# INDEX i3 (b=?) (~10 rows)
det 5.9 {
SELECT (SELECT b FROM t1 WHERE a=0), (SELECT a FROM t1 WHERE b=t2.c) FROM t2
} {
0 0 0 {SCAN TABLE t2 (~1000000 rows)}
0 0 0 {EXECUTE SCALAR SUBQUERY 1}
1 0 0 {SCAN TABLE t1 BY COVERING INDEX i2 (a=?) (~10 rows)}
0 0 0 {EXECUTE CORRELATED SCALAR SUBQUERY 2}
2 0 0 {SCAN TABLE t1 BY INDEX i3 (b=?) (~10 rows)}
}
# EVIDENCE-OF: R-43933-45972 sqlite> EXPLAIN QUERY PLAN SELECT
# count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x;
# 1|0|0|SCAN TABLE t1 BY COVERING INDEX i2 (~1000000 rows) 0|0|0|SCAN
# SUBQUERY 1 (~1000000 rows) 0|0|0|USE TEMP B-TREE FOR GROUP BY
det 5.10 {
SELECT count(*) FROM (SELECT max(b) AS x FROM t1 GROUP BY a) GROUP BY x
} {
1 0 0 {SCAN TABLE t1 BY COVERING INDEX i2 (~1000000 rows)}
0 0 0 {SCAN SUBQUERY 1 (~1000000 rows)}
0 0 0 {USE TEMP B-TREE FOR GROUP BY}
}
# EVIDENCE-OF: R-15989-23611 sqlite> EXPLAIN QUERY PLAN SELECT * FROM
# (SELECT * FROM t2 WHERE c=1), t1; 0|0|0|SCAN TABLE t2 BY INDEX i4
# (c=?) (~10 rows) 0|1|1|SCAN TABLE t1 (~1000000 rows)
det 5.11 "SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1" {
0 0 0 {SCAN TABLE t2 BY INDEX i4 (c=?) (~10 rows)}
0 1 1 {SCAN TABLE t1 (~1000000 rows)}
}
# EVIDENCE-OF: R-40701-42164 sqlite> EXPLAIN QUERY PLAN SELECT a FROM
# t1 UNION SELECT c FROM t2; 1|0|0|SCAN TABLE t1 (~1000000 rows)
# 2|0|0|SCAN TABLE t2 (~1000000 rows) 0|0|0|COMPOUND SUBQUERIES 1 AND 2
# USING TEMP B-TREE (UNION)
det 5.12 "SELECT a FROM t1 UNION SELECT c FROM t2" {
1 0 0 {SCAN TABLE t1 (~1000000 rows)}
2 0 0 {SCAN TABLE t2 (~1000000 rows)}
0 0 0 {COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)}
}
# EVIDENCE-OF: R-34523-61710 sqlite> EXPLAIN QUERY PLAN SELECT a FROM
# t1 EXCEPT SELECT d FROM t2 ORDER BY 1; 1|0|0|SCAN TABLE t1 BY COVERING
# INDEX i2 (~1000000 rows) 2|0|0|SCAN TABLE t2 (~1000000 rows) 2|0|0|USE
# TEMP B-TREE FOR ORDER BY 0|0|0|COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)
det 5.13 "SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1" {
1 0 0 {SCAN TABLE t1 BY COVERING INDEX i2 (~1000000 rows)}
2 0 0 {SCAN TABLE t2 (~1000000 rows)}
2 0 0 {USE TEMP B-TREE FOR ORDER BY}
0 0 0 {COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)}
}
finish_test
|