*** DRAFT ***

SQLite Requirement Matrix Details
optoverview.html

Index Summary Markup Original

R-15842-53296-08003-60856-35696-17823-54223-09480 tcl slt th3 src

The WHERE clause on a query is broken up into "terms" where each term is separated from the others by an AND operator.

/* IMP: R-15842-53296 */

# EVIDENCE-OF: R-15842-53296 The WHERE clause on a query is broken up
# into "terms" where each term is separated from the others by an AND
# operator.

R-61939-33480-39091-09989-20555-53712-51566-46990 tcl slt th3 src

All terms of the WHERE clause are analyzed to see if they can be satisfied using indexes.

/* IMP: R-61939-33480 */

# EVIDENCE-OF: R-61939-33480 All terms of the WHERE clause are analyzed
# to see if they can be satisfied using indexes.

R-51285-50643-24260-37588-20138-27920-05712-56972 tcl slt th3 src

To be usable by an index a term must usually be of one of the following forms:


  column = expression
  column IS expression
  column > expression
  column >= expression
  column < expression
  column <= expression
  expression = column
  expression > column
  expression >= column
  expression < column
  expression <= column
  column IN (expression-list)
  column IN (subquery)
  column IS NULL
  column LIKE pattern
  column GLOB pattern

/* IMP: R-51285-50643 */

# EVIDENCE-OF: R-51285-50643 To be usable by an index a term must
# usually be of one of the following forms: column = expression column
# IS expression column > expression column >= expression column
# < expression column <= expression expression = column expression
# > column expression >= column expression < column expression
# <= column column IN (expression-list) column IN (subquery) column
# IS NULL column LIKE pattern column GLOB pattern

R-55989-34690-53025-39227-28195-61827-56491-03494 tcl slt th3 src

If an index is created using a statement like this:

CREATE INDEX idx_ex1 ON ex1(a,b,c,d,e,...,y,z);

Then the index might be used if the initial columns of the index (columns a, b, and so forth) appear in WHERE clause terms.

/* IMP: R-55989-34690 */

# EVIDENCE-OF: R-55989-34690 If an index is created using a statement
# like this: CREATE INDEX idx_ex1 ON ex1(a,b,c,d,e,...,y,z); Then the
# index might be used if the initial columns of the index (columns a, b,
# and so forth) appear in WHERE clause terms.

R-48969-57179-22710-19682-41342-60747-10784-57811 tcl slt th3 src

The initial columns of the index must be used with the = or IN or IS operators.

/* IMP: R-48969-57179 */

# EVIDENCE-OF: R-48969-57179 The initial columns of the index must be
# used with the = or IN or IS operators.

R-47794-27716-03685-35899-02913-62361-61931-61907 tcl slt th3 src

The right-most column that is used can employ inequalities.

/* IMP: R-47794-27716 */

# EVIDENCE-OF: R-47794-27716 The right-most column that is used can
# employ inequalities.

R-06192-43187-09900-12561-05322-03139-09562-46958 tcl slt th3 src

For the right-most column of an index that is used, there can be up to two inequalities that must sandwich the allowed values of the column between two extremes.

/* IMP: R-06192-43187 */

# EVIDENCE-OF: R-06192-43187 For the right-most column of an index that
# is used, there can be up to two inequalities that must sandwich the
# allowed values of the column between two extremes.

R-25343-19498-17466-42652-61214-37840-16481-55260 tcl slt th3 src

It is not necessary for every column of an index to appear in a WHERE clause term in order for that index to be used.

/* IMP: R-25343-19498 */

# EVIDENCE-OF: R-25343-19498 It is not necessary for every column of an
# index to appear in a WHERE clause term in order for that index to be
# used.

R-60371-22228-63292-43045-55610-34824-26426-18643 tcl slt th3 src

However, there cannot be gaps in the columns of the index that are used.

/* IMP: R-60371-22228 */

# EVIDENCE-OF: R-60371-22228 However, there cannot be gaps in the
# columns of the index that are used.

R-02320-27410-64858-02729-11929-10797-55740-08223 tcl slt th3 src

Thus for the example index above, if there is no WHERE clause term that constrains column c, then terms that constrain columns a and b can be used with the index but not terms that constrain columns d through z.

/* IMP: R-02320-27410 */

# EVIDENCE-OF: R-02320-27410 Thus for the example index above, if there
# is no WHERE clause term that constrains column c, then terms that
# constrain columns a and b can be used with the index but not terms
# that constrain columns d through z.

R-43492-30972-07405-10322-20447-24571-17673-28262 tcl slt th3 src

Similarly, index columns will not normally be used (for indexing purposes) if they are to the right of a column that is constrained only by inequalities.

/* IMP: R-43492-30972 */

# EVIDENCE-OF: R-43492-30972 Similarly, index columns will not normally
# be used (for indexing purposes) if they are to the right of a column
# that is constrained only by inequalities.

R-03642-28175-07234-22849-20831-22837-60337-13680 tcl slt th3 src

For the index above and WHERE clause like this:

... WHERE a=5 AND b IN (1,2,3) AND c IS NULL AND d='hello'

The first four columns a, b, c, and d of the index would be usable since those four columns form a prefix of the index and are all bound by equality constraints.

/* IMP: R-03642-28175 */

# EVIDENCE-OF: R-03642-28175 For the index above and WHERE clause like
# this: ... WHERE a=5 AND b IN (1,2,3) AND c IS NULL AND d='hello' The
# first four columns a, b, c, and d of the index would be usable since
# those four columns form a prefix of the index and are all bound by
# equality constraints.

R-53334-49168-61696-52419-22788-21864-59883-48546 tcl slt th3 src

For the index above and WHERE clause like this:

... WHERE a=5 AND b IN (1,2,3) AND c>12 AND d='hello'

Only columns a, b, and c of the index would be usable. The d column would not be usable because it occurs to the right of c and c is constrained only by inequalities.

/* IMP: R-53334-49168 */

# EVIDENCE-OF: R-53334-49168 For the index above and WHERE clause like
# this: ... WHERE a=5 AND b IN (1,2,3) AND c>12 AND d='hello' Only
# columns a, b, and c of the index would be usable. The d column would
# not be usable because it occurs to the right of c and c is constrained
# only by inequalities.

R-27800-48033-09479-04735-22303-21367-59255-07249 tcl slt th3 src

For the index above and WHERE clause like this:

... WHERE a=5 AND b IN (1,2,3) AND d='hello'

Only columns a and b of the index would be usable. The d column would not be usable because column c is not constrained and there can be no gaps in the set of columns that usable by the index.

/* IMP: R-27800-48033 */

# EVIDENCE-OF: R-27800-48033 For the index above and WHERE clause like
# this: ... WHERE a=5 AND b IN (1,2,3) AND d='hello' Only columns a and
# b of the index would be usable. The d column would not be usable
# because column c is not constrained and there can be no gaps in the
# set of columns that usable by the index.

R-35880-12325-57817-26418-36971-18732-46777-12474 tcl slt th3 src

For the index above and WHERE clause like this:

... WHERE b IN (1,2,3) AND c NOT NULL AND d='hello'

The index is not usable at all because the left-most column of the index (column "a") is not constrained.

/* IMP: R-35880-12325 */

# EVIDENCE-OF: R-35880-12325 For the index above and WHERE clause like
# this: ... WHERE b IN (1,2,3) AND c NOT NULL AND d='hello' The index is
# not usable at all because the left-most column of the index (column
# "a") is not constrained.

R-00796-21362-00586-19798-41446-05404-46596-57988 tcl slt th3 src

Assuming there are no other indexes, the query above would result in a full table scan.

/* IMP: R-00796-21362 */

# EVIDENCE-OF: R-00796-21362 Assuming there are no other indexes, the
# query above would result in a full table scan.

R-05693-48487-42509-32993-20525-28591-05760-18227 tcl slt th3 src

For the index above and WHERE clause like this:

... WHERE a=5 OR b IN (1,2,3) OR c NOT NULL OR d='hello'

The index is not usable because the WHERE clause terms are connected by OR instead of AND.

/* IMP: R-05693-48487 */

# EVIDENCE-OF: R-05693-48487 For the index above and WHERE clause like
# this: ... WHERE a=5 OR b IN (1,2,3) OR c NOT NULL OR d='hello' The
# index is not usable because the WHERE clause terms are connected by OR
# instead of AND.

R-44155-30417-12865-55438-14349-49695-29873-16334 tcl slt th3 src

This query would result in a full table scan.

/* IMP: R-44155-30417 */

# EVIDENCE-OF: R-44155-30417 This query would result in a full table
# scan.

R-64097-24821-08867-43816-57174-45858-42283-08645 tcl slt th3 src

However, if three additional indexes where added that contained columns b, c, and d as their left-most columns, then the OR-clause optimization might apply.

/* IMP: R-64097-24821 */

# EVIDENCE-OF: R-64097-24821 However, if three additional indexes where
# added that contained columns b, c, and d as their left-most columns,
# then the OR-clause optimization might apply.

R-57148-27043-24197-50864-17789-27068-60020-34476 tcl slt th3 src

If a term of the WHERE clause is of the following form:


  expr1 BETWEEN expr2 AND expr3

Then two "virtual" terms are added as follows:


  expr1 >= expr2 AND expr1 <= expr3

/* IMP: R-57148-27043 */

# EVIDENCE-OF: R-57148-27043 If a term of the WHERE clause is of the
# following form: expr1 BETWEEN expr2 AND expr3 Then two "virtual" terms
# are added as follows: expr1 >= expr2 AND expr1 <= expr3

R-53273-52304-57192-35830-05219-48039-41857-59674 tcl slt th3 src

Virtual terms are used for analysis only and do not cause any byte-code to be generated.

/* IMP: R-53273-52304 */

# EVIDENCE-OF: R-53273-52304 Virtual terms are used for analysis only
# and do not cause any byte-code to be generated.

R-19827-62289-58446-52993-02249-28252-12913-58361 tcl slt th3 src

If both virtual terms end up being used as constraints on an index, then the original BETWEEN term is omitted and the corresponding test is not performed on input rows.

/* IMP: R-19827-62289 */

# EVIDENCE-OF: R-19827-62289 If both virtual terms end up being used as
# constraints on an index, then the original BETWEEN term is omitted and
# the corresponding test is not performed on input rows.

R-50295-28419-15389-14310-31711-65097-63451-02333 tcl slt th3 src

Thus if the BETWEEN term ends up being used as an index constraint no tests are ever performed on that term.

/* IMP: R-50295-28419 */

# EVIDENCE-OF: R-50295-28419 Thus if the BETWEEN term ends up being used
# as an index constraint no tests are ever performed on that term.

R-36834-48950-59089-31267-28729-41659-26952-44127 tcl slt th3 src

On the other hand, the virtual terms themselves never causes tests to be performed on input rows.

/* IMP: R-36834-48950 */

# EVIDENCE-OF: R-36834-48950 On the other hand, the virtual terms
# themselves never causes tests to be performed on input rows.

R-64894-16593-62452-43919-42251-52509-45292-14622 tcl slt th3 src

Thus if the BETWEEN term is not used as an index constraint and instead must be used to test input rows, the expr1 expression is only evaluated once.

/* IMP: R-64894-16593 */

# EVIDENCE-OF: R-64894-16593 Thus if the BETWEEN term is not used as an
# index constraint and instead must be used to test input rows, the
# expr1 expression is only evaluated once.

R-22070-59472-34995-42069-63414-37359-02285-11048 tcl slt th3 src

If a term consists of multiple subterms containing a common column name and separated by OR, like this:


  column = expr1 OR column = expr2 OR column = expr3 OR ...

Then that term is rewritten as follows:


  column IN (expr1,expr2,expr3,...)

/* IMP: R-22070-59472 */

# EVIDENCE-OF: R-22070-59472 If a term consists of multiple subterms
# containing a common column name and separated by OR, like this: column
# = expr1 OR column = expr2 OR column = expr3 OR ... Then that term is
# rewritten as follows: column IN (expr1,expr2,expr3,...)

R-64770-29120-59115-58521-20051-37065-45923-19746 tcl slt th3 src

The rewritten term then might go on to constrain an index using the normal rules for IN operators.

/* IMP: R-64770-29120 */

# EVIDENCE-OF: R-64770-29120 The rewritten term then might go on to
# constrain an index using the normal rules for IN operators.

R-00605-14249-27716-38234-38428-32294-04406-05104 tcl slt th3 src

Note that column must be the same column in every OR-connected subterm, although the column can occur on either the left or the right side of the = operator.

/* IMP: R-00605-14249 */

# EVIDENCE-OF: R-00605-14249 Note that column must be the same column in
# every OR-connected subterm, although the column can occur on either
# the left or the right side of the = operator.

R-44209-40260-17459-44675-20302-51345-49866-43242 tcl slt th3 src

If and only if the previously described conversion of OR to an IN operator does not work, the second OR-clause optimization is attempted.

/* IMP: R-44209-40260 */

# EVIDENCE-OF: R-44209-40260 If and only if the previously described
# conversion of OR to an IN operator does not work, the second OR-clause
# optimization is attempted.

R-45099-02601-54724-45407-56685-24152-16852-27215 tcl slt th3 src

Each subterm is analyzed as if it were itself the entire WHERE clause in order to see if the subterm is indexable by itself.

/* IMP: R-45099-02601 */

# EVIDENCE-OF: R-45099-02601 Each subterm is analyzed as if it were
# itself the entire WHERE clause in order to see if the subterm is
# indexable by itself.

R-64812-56077-47875-43117-23188-46780-09721-28568 tcl slt th3 src

If every subterm of an OR clause is separately indexable then the OR clause might be coded such that a separate index is used to evaluate each term of the OR clause.

/* IMP: R-64812-56077 */

# EVIDENCE-OF: R-64812-56077 If every subterm of an OR clause is
# separately indexable then the OR clause might be coded such that a
# separate index is used to evaluate each term of the OR clause.

R-62380-26708-45604-24232-06189-26790-29124-54464 tcl slt th3 src

For any given query, the fact that the OR-clause optimization described here can be used does not guarantee that it will be used.

/* IMP: R-62380-26708 */

# EVIDENCE-OF: R-62380-26708 For any given query, the fact that the
# OR-clause optimization described here can be used does not guarantee
# that it will be used.

R-16185-00071-09955-21762-46377-47789-41533-03861 tcl slt th3 src

SQLite uses a cost-based query planner that estimates the CPU and disk I/O costs of various competing query plans and chooses the plan that it thinks will be the fastest.

/* IMP: R-16185-00071 */

# EVIDENCE-OF: R-16185-00071 SQLite uses a cost-based query planner that
# estimates the CPU and disk I/O costs of various competing query plans
# and chooses the plan that it thinks will be the fastest.

R-20707-34919-04305-11410-25646-43791-21158-43878 tcl slt th3 src

If there are many OR terms in the WHERE clause or if some of the indexes on individual OR-clause subterms are not very selective, then SQLite might decide that it is faster to use a different query algorithm, or even a full-table scan.

/* IMP: R-20707-34919 */

# EVIDENCE-OF: R-20707-34919 If there are many OR terms in the WHERE
# clause or if some of the indexes on individual OR-clause subterms are
# not very selective, then SQLite might decide that it is faster to use
# a different query algorithm, or even a full-table scan.

R-29704-26647-58862-58935-31533-13851-11315-20283 tcl slt th3 src

Application developers can use the EXPLAIN QUERY PLAN prefix on a statement to get a high-level overview of the chosen query strategy.

/* IMP: R-29704-26647 */

# EVIDENCE-OF: R-29704-26647 Application developers can use the EXPLAIN
# QUERY PLAN prefix on a statement to get a high-level overview of the
# chosen query strategy.

R-50194-36439-38643-58258-08094-53618-19496-51516 tcl slt th3 src

The right-hand side of the LIKE or GLOB must be either a string literal or a parameter bound to a string literal that does not begin with a wildcard character.

/* IMP: R-50194-36439 */

# EVIDENCE-OF: R-50194-36439 The right-hand side of the LIKE or GLOB
# must be either a string literal or a parameter bound to a string
# literal that does not begin with a wildcard character.

R-02559-29055-33962-47817-36688-50556-57969-25403 tcl slt th3 src

The built-in functions used to implement LIKE and GLOB must not have been overloaded using the sqlite3_create_function() API.

/* IMP: R-02559-29055 */

# EVIDENCE-OF: R-02559-29055 The built-in functions used to implement
# LIKE and GLOB must not have been overloaded using the
# sqlite3_create_function() API.

R-09310-54000-20676-45974-44442-40354-59533-62372 tcl slt th3 src

For the GLOB operator, the column must be indexed using the built-in BINARY collating sequence.

/* IMP: R-09310-54000 */

# EVIDENCE-OF: R-09310-54000 For the GLOB operator, the column must be
# indexed using the built-in BINARY collating sequence.

R-33140-21501-29804-63127-48240-59602-40241-17867 tcl slt th3 src

For the LIKE operator, if case_sensitive_like mode is enabled then the column must indexed using BINARY collating sequence, or if case_sensitive_like mode is disabled then the column must indexed using built-in NOCASE collating sequence.

th3/cov1/where03.test:29 th3/cov1/where03b.test:30

/* IMP: R-33140-21501 */

# EVIDENCE-OF: R-33140-21501 For the LIKE operator, if
# case_sensitive_like mode is enabled then the column must indexed using
# BINARY collating sequence, or if case_sensitive_like mode is disabled
# then the column must indexed using built-in NOCASE collating sequence.

R-34234-52126-13891-36855-21265-08475-46071-60317 tcl slt th3 src

The default mode is for LIKE comparisons to be insensitive to differences of case for latin1 characters.

th3/cov1/pragma02.test:79 th3/cov1/where03.test:34 th3/cov1/where03b.test:35

/* IMP: R-34234-52126 */

# EVIDENCE-OF: R-34234-52126 The default mode is for LIKE comparisons to
# be insensitive to differences of case for latin1 characters.

R-58344-62489-38049-32085-64337-15515-50489-64181 tcl slt th3 src

Thus, by default, the following expression is true:

'a' LIKE 'A'

/* IMP: R-58344-62489 */

# EVIDENCE-OF: R-58344-62489 Thus, by default, the following expression
# is true: 'a' LIKE 'A'

R-06289-57229-11447-28178-35750-27104-36074-23089 tcl slt th3 src

If the case_sensitive_like pragma is enabled as follows:

PRAGMA case_sensitive_like=ON;

Then the LIKE operator pays attention to case and the example above would evaluate to false.

/* IMP: R-06289-57229 */

# EVIDENCE-OF: R-06289-57229 If the case_sensitive_like pragma is
# enabled as follows: PRAGMA case_sensitive_like=ON; Then the LIKE
# operator pays attention to case and the example above would evaluate
# to false.

R-12452-54941-39354-45567-09766-62540-46508-43420 tcl slt th3 src

Note that case insensitivity only applies to latin1 characters - basically the upper and lower case letters of English in the lower 127 byte codes of ASCII.

th3/cov1/pragma02.test:142

/* IMP: R-12452-54941 */

# EVIDENCE-OF: R-12452-54941 Note that case insensitivity only applies
# to latin1 characters - basically the upper and lower case letters of
# English in the lower 127 byte codes of ASCII.

R-46221-33909-22471-18212-13524-55885-62727-63389 tcl slt th3 src

International character sets are case sensitive in SQLite unless an application-defined collating sequence and like() SQL function are provided that take non-ASCII characters into account.

th3/cov1/pragma02.test:146

/* IMP: R-46221-33909 */

# EVIDENCE-OF: R-46221-33909 International character sets are case
# sensitive in SQLite unless an application-defined collating sequence
# and like() SQL function are provided that take non-ASCII characters
# into account.

R-18268-44140-34945-14293-33912-32138-63537-31986 tcl slt th3 src

If an application-defined collating sequence and/or like() SQL function are provided, the LIKE optimization described here will never be taken.

/* IMP: R-18268-44140 */

# EVIDENCE-OF: R-18268-44140 If an application-defined collating
# sequence and/or like() SQL function are provided, the LIKE
# optimization described here will never be taken.

R-40298-31327-08313-07924-25737-24102-08430-39905 tcl slt th3 src

The LIKE optimization might occur if the column named on the left of the operator is indexed using the built-in BINARY collating sequence and case_sensitive_like is turned on. Or the optimization might occur if the column is indexed using the built-in NOCASE collating sequence and the case_sensitive_like mode is off. These are the only two combinations under which LIKE operators will be optimized.

th3/cov1/where03.test:37 th3/cov1/where03b.test:38

/* IMP: R-40298-31327 */

# EVIDENCE-OF: R-40298-31327 The LIKE optimization might occur if the
# column named on the left of the operator is indexed using the built-in
# BINARY collating sequence and case_sensitive_like is turned on. Or the
# optimization might occur if the column is indexed using the built-in
# NOCASE collating sequence and the case_sensitive_like mode is off.
# These are the only two combinations under which LIKE operators will be
# optimized.

R-24911-36108-02843-41555-24793-65003-42358-61703 tcl slt th3 src

The GLOB operator is always case sensitive.

th3/cov1/pragma02.test:136

/* IMP: R-24911-36108 */

# EVIDENCE-OF: R-24911-36108 The GLOB operator is always case sensitive.

R-27525-15031-55320-08912-51889-58390-12207-18026 tcl slt th3 src

The column on the left side of the GLOB operator must always use the built-in BINARY collating sequence or no attempt will be made to optimize that operator with indexes.

/* IMP: R-27525-15031 */

# EVIDENCE-OF: R-27525-15031 The column on the left side of the GLOB
# operator must always use the built-in BINARY collating sequence or no
# attempt will be made to optimize that operator with indexes.

R-15707-53749-16663-50259-17040-65136-59107-02793 tcl slt th3 src

The LIKE optimization will only be attempted if the right-hand side of the GLOB or LIKE operator is either literal string or a parameter that has been bound to a string literal.

/* IMP: R-15707-53749 */

# EVIDENCE-OF: R-15707-53749 The LIKE optimization will only be
# attempted if the right-hand side of the GLOB or LIKE operator is
# either literal string or a parameter that has been bound to a string
# literal.

R-28329-37702-19692-05275-47566-55090-16021-12790 tcl slt th3 src

The string literal must not begin with a wildcard; if the right-hand side begins with a wildcard character then this optimization is not attempted.

/* IMP: R-28329-37702 */

# EVIDENCE-OF: R-28329-37702 The string literal must not begin with a
# wildcard; if the right-hand side begins with a wildcard character then
# this optimization is not attempted.

R-43521-55927-08457-46227-28920-39349-41728-53227 tcl slt th3 src

If the right-hand side is a parameter that is bound to a string, then this optimization is only attempted if the prepared statement containing the expression was compiled with sqlite3_prepare_v2() or sqlite3_prepare16_v2().

/* IMP: R-43521-55927 */

# EVIDENCE-OF: R-43521-55927 If the right-hand side is a parameter that
# is bound to a string, then this optimization is only attempted if the
# prepared statement containing the expression was compiled with
# sqlite3_prepare_v2() or sqlite3_prepare16_v2().

R-63742-65009-14401-02244-32779-40518-14763-45993 tcl slt th3 src

The LIKE optimization is not attempted if the right-hand side is a parameter and the statement was prepared using sqlite3_prepare() or sqlite3_prepare16().

/* IMP: R-63742-65009 */

# EVIDENCE-OF: R-63742-65009 The LIKE optimization is not attempted if
# the right-hand side is a parameter and the statement was prepared
# using sqlite3_prepare() or sqlite3_prepare16().

R-16230-40037-23249-35634-11042-62086-60136-18681 tcl slt th3 src

The LIKE and GLOB optimizations consist of adding two virtual terms like this:


  column >= x AND column < y

/* IMP: R-16230-40037 */

# EVIDENCE-OF: R-16230-40037 The LIKE and GLOB optimizations consist of
# adding two virtual terms like this: column >= x AND column < y

R-22724-45533-32532-12289-36170-43581-26380-53780 tcl slt th3 src

if there is only a single global wildcard to the right of x, then the original LIKE or GLOB test is disabled.

/* IMP: R-22724-45533 */

# EVIDENCE-OF: R-22724-45533 if there is only a single global wildcard
# to the right of x, then the original LIKE or GLOB test is disabled.

R-42207-04347-65478-63733-46803-43649-38770-36227 tcl slt th3 src

In other words, if the pattern is like this:


  column LIKE x%
  column GLOB x*

then the original LIKE or GLOB tests are disabled when the virtual terms constrain an index because in that case we know that all of the rows selected by the index will pass the LIKE or GLOB test.

/* IMP: R-42207-04347 */

# EVIDENCE-OF: R-42207-04347 In other words, if the pattern is like
# this: column LIKE x% column GLOB x* then the original LIKE or GLOB
# tests are disabled when the virtual terms constrain an index because
# in that case we know that all of the rows selected by the index will
# pass the LIKE or GLOB test.

R-33124-58846-63098-57444-60113-38939-22033-09777 tcl slt th3 src

Note that when the right-hand side of a LIKE or GLOB operator is a parameter and the statement is prepared using sqlite3_prepare_v2() or sqlite3_prepare16_v2() then the statement is automatically reparsed and recompiled on the first sqlite3_step() call of each run if the binding to the right-hand side parameter has changed since the previous run.

/* IMP: R-33124-58846 */

# EVIDENCE-OF: R-33124-58846 Note that when the right-hand side of a
# LIKE or GLOB operator is a parameter and the statement is prepared
# using sqlite3_prepare_v2() or sqlite3_prepare16_v2() then the
# statement is automatically reparsed and recompiled on the first
# sqlite3_step() call of each run if the binding to the right-hand side
# parameter has changed since the previous run.

R-64672-26627-33717-56839-41110-28928-46204-47104 tcl slt th3 src

The ON and USING clauses of an inner join are converted into additional terms of the WHERE clause prior to WHERE clause analysis described above in paragraph 2.

/* IMP: R-64672-26627 */

# EVIDENCE-OF: R-64672-26627 The ON and USING clauses of an inner join
# are converted into additional terms of the WHERE clause prior to WHERE
# clause analysis described above in paragraph 2.

R-49770-47130-58542-44165-02097-09691-62108-60186 tcl slt th3 src

Thus with SQLite, there is no computational advantage to use the newer SQL92 join syntax over the older SQL89 comma-join syntax. They both end up accomplishing exactly the same thing on inner joins.

/* IMP: R-49770-47130 */

# EVIDENCE-OF: R-49770-47130 Thus with SQLite, there is no computational
# advantage to use the newer SQL92 join syntax over the older SQL89
# comma-join syntax. They both end up accomplishing exactly the same
# thing on inner joins.

R-22528-31592-23814-33593-53187-04400-51621-15631 tcl slt th3 src

The following two queries are not equivalent:

SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.x=tab2.y;
SELECT * FROM tab1 LEFT JOIN tab2 WHERE tab1.x=tab2.y;

/* IMP: R-22528-31592 */

# EVIDENCE-OF: R-22528-31592 The following two queries are not
# equivalent: SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.x=tab2.y; SELECT
# * FROM tab1 LEFT JOIN tab2 WHERE tab1.x=tab2.y;

R-49789-16524-33222-15346-50158-11030-19428-38711 tcl slt th3 src

For an inner join, the two queries above would be identical.

/* IMP: R-49789-16524 */

# EVIDENCE-OF: R-49789-16524 For an inner join, the two queries above
# would be identical.

R-52275-20321-01403-45171-16511-11521-16103-23832 tcl slt th3 src

However, special processing applies to the ON and USING clauses of an OUTER join: specifically, the constraints in an ON or USING clause do not apply if the right table of the join is on a null row, but the constraints do apply in the WHERE clause.

/* IMP: R-52275-20321 */

# EVIDENCE-OF: R-52275-20321 However, special processing applies to the
# ON and USING clauses of an OUTER join: specifically, the constraints
# in an ON or USING clause do not apply if the right table of the join
# is on a null row, but the constraints do apply in the WHERE clause.

R-02204-63196-34665-25585-57735-42246-07132-57008 tcl slt th3 src

The net effect is that putting the ON or USING clause expressions for a LEFT JOIN in the WHERE clause effectively converts the query to an ordinary INNER JOIN - albeit an inner join that runs more slowly.

/* IMP: R-02204-63196 */

# EVIDENCE-OF: R-02204-63196 The net effect is that putting the ON or
# USING clause expressions for a LEFT JOIN in the WHERE clause
# effectively converts the query to an ordinary INNER JOIN - albeit an
# inner join that runs more slowly.

R-44272-02265-45662-49730-44670-47137-63900-27537 tcl slt th3 src

However, SQLite will nest the loops in a different order if doing so will help it to select better indexes.

/* IMP: R-44272-02265 */

# EVIDENCE-OF: R-44272-02265 However, SQLite will nest the loops in a
# different order if doing so will help it to select better indexes.

R-31559-52189-63274-09210-03777-15240-18014-01548 tcl slt th3 src

Inner joins can be freely reordered.

/* IMP: R-31559-52189 */

# EVIDENCE-OF: R-31559-52189 Inner joins can be freely reordered.

R-27649-21314-13870-57957-53735-15830-08405-07126 tcl slt th3 src

However a left outer join is neither commutative nor associative and hence will not be reordered.

/* IMP: R-27649-21314 */

# EVIDENCE-OF: R-27649-21314 However a left outer join is neither
# commutative nor associative and hence will not be reordered.

R-12895-64156-28673-05493-20672-24033-21713-05965 tcl slt th3 src

Inner joins to the left and right of the outer join might be reordered if the optimizer thinks that is advantageous but the outer joins are always evaluated in the order in which they occur.

/* IMP: R-12895-64156 */

# EVIDENCE-OF: R-12895-64156 Inner joins to the left and right of the
# outer join might be reordered if the optimizer thinks that is
# advantageous but the outer joins are always evaluated in the order in
# which they occur.

R-61057-06282-34752-62385-16178-37477-62681-46915 tcl slt th3 src

When selecting the order of tables in a join, SQLite uses an efficient polynomial-time algorithm.

/* IMP: R-61057-06282 */

# EVIDENCE-OF: R-61057-06282 When selecting the order of tables in a
# join, SQLite uses an efficient polynomial-time algorithm.

R-09594-37460-38113-48088-05045-45379-64772-49989 tcl slt th3 src

Because of this, SQLite is able to plan queries with 50- or 60-way joins in a matter of microseconds

Join reordering is automatic and usually works well enough that programmers do not have to think about it, especially if ANALYZE has been used to gather statistics about the available indexes, though occasionally some hints from the programmer are needed.

/* IMP: R-09594-37460 */

# EVIDENCE-OF: R-09594-37460 Because of this, SQLite is able to plan
# queries with 50- or 60-way joins in a matter of microseconds Join
# reordering is automatic and usually works well enough that programmers
# do not have to think about it, especially if ANALYZE has been used to
# gather statistics about the available indexes, though occasionally
# some hints from the programmer are needed.

R-26959-28969-15823-07414-35392-55670-52959-52462 tcl slt th3 src

As of version 3.6.18, without running ANALYZE, SQLite will choose option 2.

/* IMP: R-26959-28969 */

# EVIDENCE-OF: R-26959-28969 As of version 3.6.18, without running
# ANALYZE, SQLite will choose option 2.

R-05681-60460-45963-09749-31482-57327-02393-17906 tcl slt th3 src

If the ANALYZE command is run in order to gather statistics, a different choice might be made if the statistics indicate that the alternative is likely to run faster.

/* IMP: R-05681-60460 */

# EVIDENCE-OF: R-05681-60460 If the ANALYZE command is run in order to
# gather statistics, a different choice might be made if the statistics
# indicate that the alternative is likely to run faster.

R-41321-43211-53918-03188-33264-03683-46680-13592 tcl slt th3 src

In the following query, the optimizer is free to reorder the tables of FROM clause any way it sees fit:

SELECT *
  FROM node AS n1,
       edge AS e,
       node AS n2
 WHERE n1.name = 'alice'
   AND n2.name = 'bob'
   AND e.orig = n1.id
   AND e.dest = n2.id;

/* IMP: R-41321-43211 */

# EVIDENCE-OF: R-41321-43211 In the following query, the optimizer is
# free to reorder the tables of FROM clause any way it sees fit: SELECT
# * FROM node AS n1, edge AS e, node AS n2 WHERE n1.name = 'alice' AND
# n2.name = 'bob' AND e.orig = n1.id AND e.dest = n2.id;

R-40935-37384-47520-54323-57387-46753-55838-11679 tcl slt th3 src

In the following logically equivalent formulation of the same query, the substitution of "CROSS JOIN" for the "," means that the order of tables must be N1, E, N2.

SELECT *
  FROM node AS n1 CROSS JOIN
       edge AS e CROSS JOIN
       node AS n2
 WHERE n1.name = 'alice'
   AND n2.name = 'bob'
   AND e.orig = n1.id
   AND e.dest = n2.id;

/* IMP: R-40935-37384 */

# EVIDENCE-OF: R-40935-37384 In the following logically equivalent
# formulation of the same query, the substitution of "CROSS JOIN" for
# the "," means that the order of tables must be N1, E, N2. SELECT *
# FROM node AS n1 CROSS JOIN edge AS e CROSS JOIN node AS n2 WHERE
# n1.name = 'alice' AND n2.name = 'bob' AND e.orig = n1.id AND e.dest =
# n2.id;

R-49703-40061-17137-59156-45965-30575-53076-21589 tcl slt th3 src

Note that you must use the keyword "CROSS" in order to disable the table reordering optimization; INNER JOIN, NATURAL JOIN, JOIN, and other similar combinations work just like a comma join in that the optimizer is free to reorder tables as it sees fit.

/* IMP: R-49703-40061 */

# EVIDENCE-OF: R-49703-40061 Note that you must use the keyword "CROSS"
# in order to disable the table reordering optimization; INNER JOIN,
# NATURAL JOIN, JOIN, and other similar combinations work just like a
# comma join in that the optimizer is free to reorder tables as it sees
# fit.

R-09101-51783-59948-59887-59676-41336-15465-57227 tcl slt th3 src

The ANALYZE command scans all indexes of database where there might be a choice between two or more indexes and gathers statistics on the selectiveness of those indexes.

/* IMP: R-09101-51783 */

# EVIDENCE-OF: R-09101-51783 The ANALYZE command scans all indexes of
# database where there might be a choice between two or more indexes and
# gathers statistics on the selectiveness of those indexes.

R-17926-09424-52325-52888-31926-01436-23382-42237 tcl slt th3 src

The statistics gathered by this scan are stored in special database tables names shows names all begin with "sqlite_stat".

/* IMP: R-17926-09424 */

# EVIDENCE-OF: R-17926-09424 The statistics gathered by this scan are
# stored in special database tables names shows names all begin with
# "sqlite_stat".

R-26078-30979-29806-34785-43846-23819-01793-57552 tcl slt th3 src

The content of these tables is not updated as the database changes so after making significant changes it might be prudent to rerun ANALYZE.

/* IMP: R-26078-30979 */

# EVIDENCE-OF: R-26078-30979 The content of these tables is not updated
# as the database changes so after making significant changes it might
# be prudent to rerun ANALYZE.

R-51011-20077-64132-12410-55765-38017-02998-57581 tcl slt th3 src

The results of an ANALYZE command are only available to database connections that are opened after the ANALYZE command completes.

/* IMP: R-51011-20077 */

# EVIDENCE-OF: R-51011-20077 The results of an ANALYZE command are only
# available to database connections that are opened after the ANALYZE
# command completes.

R-29045-37224-33225-01178-62278-53898-40877-61168 tcl slt th3 src

For example, the sqlite_stat1 table might indicate that an equality constraint on column x reduces the search space to 10 rows on average, whereas an equality constraint on column y reduces the search space to 3 rows on average. In that case, SQLite would prefer to use index ex2i2 since that index is more selective.

/* IMP: R-29045-37224 */

# EVIDENCE-OF: R-29045-37224 For example, the sqlite_stat1 table might
# indicate that an equality constraint on column x reduces the search
# space to 10 rows on average, whereas an equality constraint on column
# y reduces the search space to 3 rows on average. In that case, SQLite
# would prefer to use index ex2i2 since that index is more selective.

R-22624-19349-43907-49115-55409-14540-64613-59655 tcl slt th3 src

Terms of the WHERE clause can be manually disqualified for use with indexes by prepending a unary + operator to the column name.

/* IMP: R-22624-19349 */

# EVIDENCE-OF: R-22624-19349 Terms of the WHERE clause can be manually
# disqualified for use with indexes by prepending a unary + operator to
# the column name.

R-63967-10740-31078-31148-41041-55599-05562-25747 tcl slt th3 src

The unary + is a no-op and will not generate any byte code in the prepared statement.

/* IMP: R-63967-10740 */

# EVIDENCE-OF: R-63967-10740 The unary + is a no-op and will not
# generate any byte code in the prepared statement.

R-34751-04246-20772-53163-23654-34620-17677-07511 tcl slt th3 src

So, in the example above, if the query were rewritten as:

SELECT z FROM ex2 WHERE +x=5 AND y=6;

The + operator on the x column will prevent that term from constraining an index. This would force the use of the ex2i2 index.

/* IMP: R-34751-04246 */

# EVIDENCE-OF: R-34751-04246 So, in the example above, if the query were
# rewritten as: SELECT z FROM ex2 WHERE +x=5 AND y=6; The + operator on
# the x column will prevent that term from constraining an index. This
# would force the use of the ex2i2 index.

R-06730-09692-37737-40278-41150-46947-53359-48437 tcl slt th3 src

Note that the unary + operator also removes type affinity from an expression, and in some cases this can cause subtle changes in the meaning of an expression.

/* IMP: R-06730-09692 */

# EVIDENCE-OF: R-06730-09692 Note that the unary + operator also removes
# type affinity from an expression, and in some cases this can cause
# subtle changes in the meaning of an expression.

R-45428-55355-50147-62692-64807-19630-14135-59274 tcl slt th3 src

In the example above, if column x has TEXT affinity then the comparison "x=5" will be done as text. The + operator removes the affinity. So the comparison "+x=5" will compare the text in column x with the numeric value 5 and will always be false.

/* IMP: R-45428-55355 */

# EVIDENCE-OF: R-45428-55355 In the example above, if column x has TEXT
# affinity then the comparison "x=5" will be done as text. The +
# operator removes the affinity. So the comparison "+x=5" will compare
# the text in column x with the numeric value 5 and will always be
# false.

R-22973-56658-43836-32419-61759-38607-43348-03721 tcl slt th3 src

SQLite will make this determination, but only if it has been compiled with SQLITE_ENABLE_STAT3 or SQLITE_ENABLE_STAT4.

/* IMP: R-22973-56658 */

# EVIDENCE-OF: R-22973-56658 SQLite will make this determination, but
# only if it has been compiled with SQLITE_ENABLE_STAT3 or
# SQLITE_ENABLE_STAT4.

R-17129-39366-12879-37953-16940-52651-36509-01278 tcl slt th3 src

The SQLITE_ENABLE_STAT3 and SQLITE_ENABLE_STAT4 options causes the ANALYZE command to collect a histogram of column content in the sqlite_stat3 or sqlite_stat4 tables and to use this histogram to make a better guess at the best query to use for range constraints such as the above.

/* IMP: R-17129-39366 */

# EVIDENCE-OF: R-17129-39366 The SQLITE_ENABLE_STAT3 and
# SQLITE_ENABLE_STAT4 options causes the ANALYZE command to collect a
# histogram of column content in the sqlite_stat3 or sqlite_stat4 tables
# and to use this histogram to make a better guess at the best query to
# use for range constraints such as the above.

R-10099-10064-58361-57180-39744-05216-32972-43593 tcl slt th3 src

The histogram data is only useful if the right-hand side of the constraint is a simple compile-time constant or parameter and not an expression.

/* IMP: R-10099-10064 */

# EVIDENCE-OF: R-10099-10064 The histogram data is only useful if the
# right-hand side of the constraint is a simple compile-time constant or
# parameter and not an expression.

R-40921-47946-20610-64910-18137-09746-01061-06361 tcl slt th3 src

Another limitation of the histogram data is that it only applies to the left-most column on an index.

/* IMP: R-40921-47946 */

# EVIDENCE-OF: R-40921-47946 Another limitation of the histogram data is
# that it only applies to the left-most column on an index.

R-59967-13104-62273-50017-33648-63119-25172-21301 tcl slt th3 src

Hence, the histogram data which is collected no left-most column of indexes is useless in helping to choose between the range constraints on columns x and y.

/* IMP: R-59967-13104 */

# EVIDENCE-OF: R-59967-13104 Hence, the histogram data which is
# collected no left-most column of indexes is useless in helping to
# choose between the range constraints on columns x and y.

R-21527-28967-38710-01216-03543-44346-41120-54609 tcl slt th3 src

If, however, all columns that were to be fetched from the table are already available in the index itself, SQLite will use the values contained in the index and will never look up the original table row.

/* IMP: R-21527-28967 */

# EVIDENCE-OF: R-21527-28967 If, however, all columns that were to be
# fetched from the table are already available in the index itself,
# SQLite will use the values contained in the index and will never look
# up the original table row.

R-38308-24406-60432-36844-44723-02085-25022-07693 tcl slt th3 src

SQLite attempts to use an index to satisfy the ORDER BY clause of a query when possible.

/* IMP: R-38308-24406 */

# EVIDENCE-OF: R-38308-24406 SQLite attempts to use an index to satisfy
# the ORDER BY clause of a query when possible.

R-10815-21922-06260-57655-10656-49970-23058-15520 tcl slt th3 src

When faced with the choice of using an index to satisfy WHERE clause constraints or satisfying an ORDER BY clause, SQLite does the same cost analysis described above and chooses the index that it believes will result in the fastest answer.

/* IMP: R-10815-21922 */

# EVIDENCE-OF: R-10815-21922 When faced with the choice of using an
# index to satisfy WHERE clause constraints or satisfying an ORDER BY
# clause, SQLite does the same cost analysis described above and chooses
# the index that it believes will result in the fastest answer.

R-42413-48699-18180-28011-52485-52164-16827-39292 tcl slt th3 src

SQLite will also attempt to use indexes to help satisfy GROUP BY clauses and the DISTINCT keyword.

/* IMP: R-42413-48699 */

# EVIDENCE-OF: R-42413-48699 SQLite will also attempt to use indexes to
# help satisfy GROUP BY clauses and the DISTINCT keyword.

R-07061-54920-02690-34802-26523-14617-41412-12725 tcl slt th3 src

To overcome this problem, SQLite attempts to flatten subqueries in the FROM clause of a SELECT.

/* IMP: R-07061-54920 */

# EVIDENCE-OF: R-07061-54920 To overcome this problem, SQLite attempts
# to flatten subqueries in the FROM clause of a SELECT.

R-44364-00581-20291-58587-51533-44159-46989-51099 tcl slt th3 src

For example:

SELECT t1.a, t2.b FROM t2, (SELECT x+y AS a FROM t1 WHERE z<100) WHERE a>5

Would be rewritten using query flattening as:

SELECT t1.x+t1.y AS a, t2.b FROM t2, t1 WHERE z<100 AND a>5

/* IMP: R-44364-00581 */

# EVIDENCE-OF: R-44364-00581 For example: SELECT t1.a, t2.b FROM t2,
# (SELECT x+y AS a FROM t1 WHERE z<100) WHERE a>5 Would be
# rewritten using query flattening as: SELECT t1.x+t1.y AS a, t2.b FROM
# t2, t1 WHERE z<100 AND a>5

R-04460-38575-52121-32050-22893-58240-13310-33499 tcl slt th3 src

If the subquery is the right operand of a LEFT JOIN then

the subquery may not be a join, and
the FROM clause of the subquery may not contain a virtual table, and

the outer query may not be an aggregate.

/* IMP: R-04460-38575 */

# EVIDENCE-OF: R-04460-38575 If the subquery is the right operand of a
# LEFT JOIN then the subquery may not be a join, and the FROM clause of
# the subquery may not contain a virtual table, and the outer query may
# not be an aggregate.

R-51247-30732-42040-45560-24413-23058-07649-15585 tcl slt th3 src

The subquery is not DISTINCT.

/* IMP: R-51247-30732 */

# EVIDENCE-OF: R-51247-30732 The subquery is not DISTINCT.

R-44053-64068-31529-32657-44606-24557-47766-35713 tcl slt th3 src

The subquery has a FROM clause.

/* IMP: R-44053-64068 */

# EVIDENCE-OF: R-44053-64068 The subquery has a FROM clause.

R-44252-06035-10062-38462-00454-12973-27157-29991 tcl slt th3 src

The subquery does not use LIMIT or the outer query is not a join.

/* IMP: R-44252-06035 */

# EVIDENCE-OF: R-44252-06035 The subquery does not use LIMIT or the
# outer query is not a join.

R-39332-27982-27049-48904-10655-02053-00990-04758 tcl slt th3 src

The subquery does not use LIMIT or the outer query does not use aggregates.

/* IMP: R-39332-27982 */

# EVIDENCE-OF: R-39332-27982 The subquery does not use LIMIT or the
# outer query does not use aggregates.

R-13161-58512-18507-02027-33184-52817-40400-65254 tcl slt th3 src

The subquery and the outer query do not both have ORDER BY clauses.

/* IMP: R-13161-58512 */

# EVIDENCE-OF: R-13161-58512 The subquery and the outer query do not
# both have ORDER BY clauses.

R-00353-01851-44275-08807-26330-41077-52452-65296 tcl slt th3 src

The subquery and outer query do not both use LIMIT.

/* IMP: R-00353-01851 */

# EVIDENCE-OF: R-00353-01851 The subquery and outer query do not both
# use LIMIT.

R-33553-19363-35042-00573-64070-53843-36958-05658 tcl slt th3 src

The subquery does not use OFFSET.

/* IMP: R-33553-19363 */

# EVIDENCE-OF: R-33553-19363 The subquery does not use OFFSET.

R-45058-52483-53751-35980-24819-54487-13131-07028 tcl slt th3 src

If the outer query is part of a compound select, then the subquery may not have a LIMIT clause.

/* IMP: R-45058-52483 */

# EVIDENCE-OF: R-45058-52483 If the outer query is part of a compound
# select, then the subquery may not have a LIMIT clause.

R-64618-48771-56077-37095-41787-03212-44585-24877 tcl slt th3 src

If the outer query is an aggregate, then the subquery may not contain ORDER BY.

/* IMP: R-64618-48771 */

# EVIDENCE-OF: R-64618-48771 If the outer query is an aggregate, then
# the subquery may not contain ORDER BY.

R-10606-56585-07684-37439-38314-01521-03990-40264 tcl slt th3 src

If the sub-query is a compound SELECT, then

all compound operators must be UNION ALL, and
no terms with the subquery compound may be aggregate or DISTINCT, and
every term within the subquery must have a FROM clause, and
the outer query may not be an aggregate, DISTINCT query, or join.

/* IMP: R-10606-56585 */

# EVIDENCE-OF: R-10606-56585 If the sub-query is a compound SELECT, then
# all compound operators must be UNION ALL, and no terms with the
# subquery compound may be aggregate or DISTINCT, and every term within
# the subquery must have a FROM clause, and the outer query may not be
# an aggregate, DISTINCT query, or join.

R-47870-49110-06101-43911-08439-20961-63107-08637 tcl slt th3 src

The parent and sub-query may contain WHERE clauses.

/* IMP: R-47870-49110 */

# EVIDENCE-OF: R-47870-49110 The parent and sub-query may contain WHERE
# clauses.

R-26601-01119-55361-00031-26734-63731-65122-53650 tcl slt th3 src

Subject to rules (11), (12) and (13), they may also contain ORDER BY, LIMIT and OFFSET clauses.

/* IMP: R-26601-01119 */

# EVIDENCE-OF: R-26601-01119 Subject to rules (11), (12) and (13), they
# may also contain ORDER BY, LIMIT and OFFSET clauses.

R-09783-65036-25423-48067-42538-45414-00279-46435 tcl slt th3 src

If the sub-query is a compound select, then all terms of the ORDER by clause of the parent must be simple references to columns of the sub-query.

/* IMP: R-09783-65036 */

# EVIDENCE-OF: R-09783-65036 If the sub-query is a compound select, then
# all terms of the ORDER by clause of the parent must be simple
# references to columns of the sub-query.

R-20235-14821-33939-37352-02301-46502-25259-56810 tcl slt th3 src

If the subquery uses LIMIT then the outer query may not have a WHERE clause.

/* IMP: R-20235-14821 */

# EVIDENCE-OF: R-20235-14821 If the subquery uses LIMIT then the outer
# query may not have a WHERE clause.

R-01724-22761-13278-31858-02106-19568-34768-00701 tcl slt th3 src

If the sub-query is a compound select, then it must not use an ORDER BY clause.

/* IMP: R-01724-22761 */

# EVIDENCE-OF: R-01724-22761 If the sub-query is a compound select, then
# it must not use an ORDER BY clause.

R-64951-20071-29590-30713-07634-57943-17719-34762 tcl slt th3 src

If the subquery uses LIMIT, then the outer query may not be DISTINCT.

/* IMP: R-64951-20071 */

# EVIDENCE-OF: R-64951-20071 If the subquery uses LIMIT, then the outer
# query may not be DISTINCT.

R-50253-50589-37477-41446-40449-16495-32904-62089 tcl slt th3 src

The subquery may not be a recursive CTE.

/* IMP: R-50253-50589 */

# EVIDENCE-OF: R-50253-50589 The subquery may not be a recursive CTE.

R-32181-50629-37005-34302-07700-29801-11487-35180 tcl slt th3 src

Queries that contain a single MIN() or MAX() aggregate function whose argument is the left-most column of an index might be satisfied by doing a single index lookup rather than by scanning the entire table.

/* IMP: R-32181-50629 */

# EVIDENCE-OF: R-32181-50629 Queries that contain a single MIN() or
# MAX() aggregate function whose argument is the left-most column of an
# index might be satisfied by doing a single index lookup rather than by
# scanning the entire table.

R-60014-23402-36895-53889-00518-21066-14301-55859 tcl slt th3 src

When no indexes are available to aid the evaluation of a query, SQLite might create an automatic index that lasts only for the duration of a single SQL statement.

/* IMP: R-60014-23402 */

# EVIDENCE-OF: R-60014-23402 When no indexes are available to aid the
# evaluation of a query, SQLite might create an automatic index that
# lasts only for the duration of a single SQL statement.

SQLite Requirement Matrix Details optoverview.html

Index Summary Markup Original

SQLite Requirement Matrix Details
optoverview.html