*** DRAFT ***

SQLite Requirement Matrix Details
eqp.html

Index Summary Markup Original


R-46794-19848-23128-00719-30471-17820-52179-33084 tcl slt th3 src

An EXPLAIN QUERY PLAN command returns zero or more rows of four columns each.

th3/req1/eqp01.test:10

/* IMP: R-46794-19848 */
# EVIDENCE-OF: R-46794-19848 An EXPLAIN QUERY PLAN command returns zero
# or more rows of four columns each.

R-50054-41546-44227-45565-32365-53294-59640-58817 tcl slt th3 src

The column names are "selectid", "order", "from", "detail".

th3/req1/eqp01.test:13

/* IMP: R-50054-41546 */
# EVIDENCE-OF: R-50054-41546 The column names are "selectid", "order",
# "from", "detail".

R-30248-02140-43435-09276-30517-50982-49513-41085 tcl slt th3 src

The first three columns contain an integer value.

th3/req1/eqp01.test:16

/* IMP: R-30248-02140 */
# EVIDENCE-OF: R-30248-02140 The first three columns contain an integer
# value.

R-31327-09584-58286-37804-26894-23464-47071-52660 tcl slt th3 src

The final column, "detail", contains a text value which carries most of the useful information.

th3/req1/eqp01.test:19

/* IMP: R-31327-09584 */
# EVIDENCE-OF: R-31327-09584 The final column, "detail", contains a text
# value which carries most of the useful information.

R-48001-03885-46533-22918-25450-29209-50529-34547 tcl slt th3 src

EXPLAIN QUERY PLAN is most useful on a SELECT statement, but may also be appear with other statements that read data from database tables (e.g. UPDATE, DELETE, INSERT INTO ... SELECT).

th3/req1/eqp01.test:53

/* IMP: R-48001-03885 */
# EVIDENCE-OF: R-48001-03885 EXPLAIN QUERY PLAN is most useful on a
# SELECT statement, but may also be appear with other statements that
# read data from database tables (e.g. UPDATE, DELETE, INSERT INTO ...
# SELECT).

R-43064-28843-60545-11344-25729-47633-22876-03755 tcl slt th3 src

For each table read by the query, the output of EXPLAIN QUERY PLAN includes a record for which the value in the "detail" column begins with either "SCAN" or "SEARCH".

th3/req1/eqp01.test:76

/* IMP: R-43064-28843 */
# EVIDENCE-OF: R-43064-28843 For each table read by the query, the
# output of EXPLAIN QUERY PLAN includes a record for which the value in
# the "detail" column begins with either "SCAN" or "SEARCH".

R-54961-28617-11438-13073-24028-16766-12231-36247 tcl slt th3 src

"SCAN" is used for a full-table scan, including cases where SQLite iterates through all records in a table in an order defined by an index.

th3/req1/eqp01.test:90

/* IMP: R-54961-28617 */
# EVIDENCE-OF: R-54961-28617 "SCAN" is used for a full-table scan,
# including cases where SQLite iterates through all records in a table
# in an order defined by an index.

R-38242-53217-63508-42050-45799-27349-00700-51305 tcl slt th3 src

"SEARCH" indicates that only a subset of the table rows are visited.

th3/req1/eqp01.test:104

/* IMP: R-38242-53217 */
# EVIDENCE-OF: R-38242-53217 "SEARCH" indicates that only a subset of
# the table rows are visited.

R-12693-31159-47760-14933-14712-45041-12329-60921 tcl slt th3 src

Each SCAN or SEARCH record includes the following information:

th3/req1/eqp01.test:26

/* IMP: R-12693-31159 */
# EVIDENCE-OF: R-12693-31159 Each SCAN or SEARCH record includes the
# following information: The name of the table data is read from.
# Whether or not an index or automatic index is used. Whether or not the
# covering index optimization applies. Which terms of the WHERE clause
# are used for indexing.

R-47779-47605-17033-59564-54641-41930-11276-19483 tcl slt th3 src

sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; 
0|0|0|SCAN TABLE t1

tcl/eqp.test:373

/* IMP: R-47779-47605 */
# EVIDENCE-OF: R-47779-47605 sqlite> EXPLAIN QUERY PLAN SELECT a, b
# FROM t1 WHERE a=1; 0|0|0|SCAN TABLE t1

R-55852-17599-19078-62551-62802-26618-54100-32972 tcl slt th3 src

sqlite> CREATE INDEX i1 ON t1(a);
sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
0|0|0|SEARCH TABLE t1 USING INDEX i1

tcl/eqp.test:382

/* IMP: R-55852-17599 */
# EVIDENCE-OF: R-55852-17599 sqlite> CREATE INDEX i1 ON t1(a);
# sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
# 0|0|0|SEARCH TABLE t1 USING INDEX i1

R-21179-11011-35531-44364-04825-01555-54528-36481 tcl slt th3 src

sqlite> CREATE INDEX i2 ON t1(a, b);
sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1; 
0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)

tcl/eqp.test:391

/* IMP: R-21179-11011 */
# EVIDENCE-OF: R-21179-11011 sqlite> CREATE INDEX i2 ON t1(a, b);
# sqlite> EXPLAIN QUERY PLAN SELECT a, b FROM t1 WHERE a=1;
# 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)

R-09991-48941-34645-31832-04466-19849-36549-45775 tcl slt th3 src

sqlite> EXPLAIN QUERY PLAN SELECT t1.*, t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2;
0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)
0|1|1|SCAN TABLE t2

tcl/eqp.test:400

/* IMP: R-09991-48941 */
# EVIDENCE-OF: R-09991-48941 sqlite> EXPLAIN QUERY PLAN SELECT t1.*,
# t2.* FROM t1, t2 WHERE t1.a=1 AND t1.b>2; 0|0|0|SEARCH TABLE t1
# USING COVERING INDEX i2 (a=? AND b>?) 0|1|1|SCAN TABLE t2

R-55443-46660-27592-17981-12219-05081-62305-56714 tcl slt th3 src

The second column of output (column "order") indicates the nesting order.

th3/req1/eqp01.test:184

/* IMP: R-55443-46660 */
# EVIDENCE-OF: R-55443-46660 The second column of output (column
# "order") indicates the nesting order.

R-33626-61085-00249-11901-41489-60441-37285-33795 tcl slt th3 src

sqlite> EXPLAIN QUERY PLAN SELECT t1.*, t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2;
0|0|1|SEARCH TABLE t1 USING COVERING INDEX i2 (a=? AND b>?)
0|1|0|SCAN TABLE t2

tcl/eqp.test:411

/* IMP: R-33626-61085 */
# EVIDENCE-OF: R-33626-61085 sqlite> EXPLAIN QUERY PLAN SELECT t1.*,
# t2.* FROM t2, t1 WHERE t1.a=1 AND t1.b>2; 0|0|1|SEARCH TABLE t1
# USING COVERING INDEX i2 (a=? AND b>?) 0|1|0|SCAN TABLE t2

R-04002-25654-12640-07246-34304-59945-33194-22689 tcl slt th3 src

sqlite> CREATE INDEX i3 ON t1(b);
sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2;
0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)
0|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?)

tcl/eqp.test:421

/* IMP: R-04002-25654 */
# EVIDENCE-OF: R-04002-25654 sqlite> CREATE INDEX i3 ON t1(b);
# sqlite> EXPLAIN QUERY PLAN SELECT * FROM t1 WHERE a=1 OR b=2;
# 0|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?) 0|0|0|SEARCH TABLE
# t1 USING INDEX i3 (b=?)

R-24577-38891-65340-59973-62459-58446-28963-28131 tcl slt th3 src

sqlite> EXPLAIN QUERY PLAN SELECT c, d FROM t2 ORDER BY c; 
0|0|0|SCAN TABLE t2
0|0|0|USE TEMP B-TREE FOR ORDER BY

tcl/eqp.test:432

/* IMP: R-24577-38891 */
# EVIDENCE-OF: R-24577-38891 sqlite> EXPLAIN QUERY PLAN SELECT c, d
# FROM t2 ORDER BY c; 0|0|0|SCAN TABLE t2 0|0|0|USE TEMP B-TREE FOR
# ORDER BY

R-58157-12355-40365-38560-33320-54735-39734-50208 tcl slt th3 src

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 USING INDEX i4

tcl/eqp.test:442

/* IMP: R-58157-12355 */
# EVIDENCE-OF: R-58157-12355 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 USING INDEX i4

R-50329-17145-27119-54152-42841-36375-05497-15742 tcl slt th3 src

If a query contains sub-selects, either as part of the FROM clause or as part of SQL expressions, then the output of EXPLAIN QUERY PLAN also includes a report for each sub-select.

th3/req1/eqp01.test:197

/* IMP: R-50329-17145 */
# EVIDENCE-OF: R-50329-17145 If a query contains sub-selects, either as
# part of the FROM clause or as part of SQL expressions, then the output
# of EXPLAIN QUERY PLAN also includes a report for each sub-select.

R-24958-23411-18929-15714-30148-06066-52554-42233 tcl slt th3 src

Each sub-select is assigned a distinct, non-zero "selectid" value.

th3/req1/eqp01.test:201

/* IMP: R-24958-23411 */
# EVIDENCE-OF: R-24958-23411 Each sub-select is assigned a distinct,
# non-zero "selectid" value.

R-11290-60355-43377-26029-27960-65452-54790-61013 tcl slt th3 src

The top-level SELECT statement is always assigned the selectid value 0.

th3/req1/eqp01.test:204

/* IMP: R-11290-60355 */
# EVIDENCE-OF: R-11290-60355 The top-level SELECT statement is always
# assigned the selectid value 0.

R-13931-10421-35283-26355-06468-08264-20065-34151 tcl slt th3 src

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
0|0|0|EXECUTE SCALAR SUBQUERY 1
1|0|0|SEARCH TABLE t1 USING COVERING INDEX i2 (a=?)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
2|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?)

tcl/eqp.test:451

/* IMP: R-13931-10421 */
# EVIDENCE-OF: R-13931-10421 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 0|0|0|EXECUTE SCALAR SUBQUERY 1 1|0|0|SEARCH TABLE
# t1 USING COVERING INDEX i2 (a=?) 0|0|0|EXECUTE CORRELATED SCALAR
# SUBQUERY 2 2|0|0|SEARCH TABLE t1 USING INDEX i3 (b=?)

R-00515-56855-31188-51684-43616-15583-54594-04320 tcl slt th3 src

Unless the flattening optimization is applied, if a subquery appears in the FROM clause of a SELECT statement, SQLite executes the subquery and stores the results in a temporary table. It then uses the contents of the temporary table in place of the subquery to execute the parent query.

th3/req1/eqp01.test:223

/* IMP: R-00515-56855 */
# EVIDENCE-OF: R-00515-56855 Unless the flattening optimization is
# applied, if a subquery appears in the FROM clause of a SELECT
# statement, SQLite executes the subquery and stores the results in a
# temporary table. It then uses the contents of the temporary table in
# place of the subquery to execute the parent query.

R-50892-45943-46066-37296-36419-10149-27380-46800 tcl slt th3 src

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 USING COVERING INDEX i2
0|0|0|SCAN SUBQUERY 1
0|0|0|USE TEMP B-TREE FOR GROUP BY

tcl/eqp.test:469

/* IMP: R-50892-45943 */
# EVIDENCE-OF: R-50892-45943 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 USING COVERING INDEX i2 0|0|0|SCAN SUBQUERY 1
# 0|0|0|USE TEMP B-TREE FOR GROUP BY

R-46219-33846-03341-47811-62259-36002-31480-38609 tcl slt th3 src

sqlite> EXPLAIN QUERY PLAN SELECT * FROM (SELECT * FROM t2 WHERE c=1), t1;
0|0|0|SEARCH TABLE t2 USING INDEX i4 (c=?)
0|1|1|SCAN TABLE t1

tcl/eqp.test:483

/* IMP: R-46219-33846 */
# EVIDENCE-OF: R-46219-33846 sqlite> EXPLAIN QUERY PLAN SELECT * FROM
# (SELECT * FROM t2 WHERE c=1), t1; 0|0|0|SEARCH TABLE t2 USING INDEX i4
# (c=?) 0|1|1|SCAN TABLE t1

R-10717-02538-23214-42555-35648-10956-24290-47354 tcl slt th3 src

Each component query of a compound query (UNION, UNION ALL, EXCEPT or INTERSECT) is assigned its own selectid and reported on separately.

th3/req1/eqp01.test:246

/* IMP: R-10717-02538 */
# EVIDENCE-OF: R-10717-02538 Each component query of a compound query
# (UNION, UNION ALL, EXCEPT or INTERSECT) is assigned its own selectid
# and reported on separately.

R-20533-60939-10698-49029-55085-32598-43205-37289 tcl slt th3 src

A single record is output for the parent (compound query) identifying the operation, and whether or not a temporary b-tree is used to implement it.

th3/req1/eqp01.test:250

/* IMP: R-20533-60939 */
# EVIDENCE-OF: R-20533-60939 A single record is output for the parent
# (compound query) identifying the operation, and whether or not a
# temporary b-tree is used to implement it.

R-37879-39987-64431-01486-31700-04419-20015-25987 tcl slt th3 src

sqlite> EXPLAIN QUERY PLAN SELECT a FROM t1 UNION SELECT c FROM t2;
1|0|0|SCAN TABLE t1
2|0|0|SCAN TABLE t2
0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)

tcl/eqp.test:493

/* IMP: R-37879-39987 */
# EVIDENCE-OF: R-37879-39987 sqlite> EXPLAIN QUERY PLAN SELECT a FROM
# t1 UNION SELECT c FROM t2; 1|0|0|SCAN TABLE t1 2|0|0|SCAN TABLE t2
# 0|0|0|COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION)

R-44864-63011-00700-13629-09474-43315-20375-05910 tcl slt th3 src

sqlite> EXPLAIN QUERY PLAN SELECT a FROM t1 EXCEPT SELECT d FROM t2 ORDER BY 1;
1|0|0|SCAN TABLE t1 USING COVERING INDEX i2
2|0|0|SCAN TABLE t2
2|0|0|USE TEMP B-TREE FOR ORDER BY
0|0|0|COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)

tcl/eqp.test:505

/* IMP: R-44864-63011 */
# EVIDENCE-OF: R-44864-63011 sqlite> EXPLAIN QUERY PLAN SELECT a FROM
# t1 EXCEPT SELECT d FROM t2 ORDER BY 1; 1|0|0|SCAN TABLE t1 USING
# COVERING INDEX i2 2|0|0|SCAN TABLE t2 2|0|0|USE TEMP B-TREE FOR ORDER
# BY 0|0|0|COMPOUND SUBQUERIES 1 AND 2 (EXCEPT)