Small. Fast. Reliable.
Choose any three.
About Documentation Download License Support Purchase

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-52638-40483-64904-57206-41972-21673-15082-17271 tcl slt th3 src

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

/* IMP: R-52638-40483 */
# EVIDENCE-OF: R-52638-40483 All terms of the WHERE clause are analyzed
# to see if they can be satisfied using indices.

R-07782-53619-37361-40296-27943-36501-08866-63723 tcl slt th3 src

To be usable by an index a term must 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


/* IMP: R-07782-53619 */
# EVIDENCE-OF: R-07782-53619 To be usable by an index a term must 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

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-06696-14078-40229-44066-29642-50805-54097-35981 tcl slt th3 src

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

/* IMP: R-06696-14078 */
# EVIDENCE-OF: R-06696-14078 But there cannot be gaps in the columns of
# the index that are used.

R-25698-53233-37700-31268-29379-58660-07035-56459 tcl slt th3 src

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

/* IMP: R-25698-53233 */
# EVIDENCE-OF: R-25698-53233 Thus for the example index above, if there
# is no WHERE clause term that constraints column c, then terms that
# constrain columns a and b can be used with the index but not terms
# that constraint 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-00206-08174-12423-08291-28017-31720-33784-40037 tcl slt th3 src

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

/* IMP: R-00206-08174 */
# EVIDENCE-OF: R-00206-08174 Assuming there are no other indices, 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-32076-12237-46595-29546-24176-12469-16826-41322 tcl slt th3 src

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

/* IMP: R-32076-12237 */
# EVIDENCE-OF: R-32076-12237 However, if three additional indices 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-21248-34301-14178-52249-38888-44158-13510-13358 tcl slt th3 src

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

/* IMP: R-21248-34301 */
# EVIDENCE-OF: R-21248-34301 Virtual terms are used for analysis only
# and do not cause any VDBE 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-24596-57909-32096-12351-64612-37660-33667-09663 tcl slt th3 src

If there are many OR terms in the WHERE clause or if some of the indices 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-24596-57909 */
# EVIDENCE-OF: R-24596-57909 If there are many OR terms in the WHERE
# clause or if some of the indices 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-02065-49465-15222-26040-51715-24674-62882-64380 tcl slt th3 src

The left-hand side of the LIKE or GLOB operator must be the name of an indexed column with TEXT affinity.

src/whereexpr.c:220   th3/req1/like01.test:13 

/* IMP: R-02065-49465 */
# EVIDENCE-OF: R-02065-49465 The left-hand side of the LIKE or GLOB
# operator must be the name of an indexed column with TEXT affinity.

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-32792-60226-60844-50115-44778-21229-22761-46890 tcl slt th3 src

The ESCAPE clause cannot appear on the LIKE operator.

th3/cov1/where03.test:87   th3/cov1/where03b.test:88 

/* IMP: R-32792-60226 */
# EVIDENCE-OF: R-32792-60226 The ESCAPE clause cannot appear on the LIKE
# operator.

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-59212-00515-24356-00769-54260-21124-60986-12573 tcl slt th3 src

But 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-59212-00515 */
# EVIDENCE-OF: R-59212-00515 But 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-22958-57964-21518-29993-48758-46150-56113-34417 tcl slt th3 src

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

/* IMP: R-22958-57964 */
# EVIDENCE-OF: R-22958-57964 But 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-60516-64103-60731-31538-24132-02538-08166-64982 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 indices.

/* IMP: R-60516-64103 */
# EVIDENCE-OF: R-60516-64103 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 indices.

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-39231-33076-05727-00102-39639-63072-44132-27548 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 attempted.

/* IMP: R-39231-33076 */
# EVIDENCE-OF: R-39231-33076 The string literal must not begin with a
# wildcard; if the right-hand side begins with a wildcard character then
# this optimization is 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-47793-04145-17927-56615-10449-16911-07772-02040 tcl slt th3 src

The LIKE optimization is not attempted if there is an ESCAPE phrase on the LIKE operator.

th3/cov1/where03.test:88   th3/cov1/where03b.test:89 

/* IMP: R-47793-04145 */
# EVIDENCE-OF: R-47793-04145 The LIKE optimization is not attempted if
# there is an ESCAPE phrase on the LIKE operator.

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-55842-56137-49604-51466-59132-51451-25325-13692 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 1.

/* IMP: R-55842-56137 */
# EVIDENCE-OF: R-55842-56137 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 1.

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-33707-40199-39350-00147-14640-22092-62266-02343 tcl slt th3 src

But 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-33707-40199 */
# EVIDENCE-OF: R-33707-40199 But 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-17805-37710-17139-40895-40838-31681-06316-29590 tcl slt th3 src

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

/* IMP: R-17805-37710 */
# EVIDENCE-OF: R-17805-37710 However, SQLite will nest the loops in a
# different order if doing so will help it to select better indices.

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-23782-22078-58175-11068-16823-45650-46319-45186 tcl slt th3 src

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

/* IMP: R-23782-22078 */
# EVIDENCE-OF: R-23782-22078 Because of this, SQLite is able to plan
# queries with 50- or 60-way joins in a matter of microseconds.

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-24831-43693-55364-04496-15994-32297-02839-53654 tcl slt th3 src

But 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-24831-43693 */
# EVIDENCE-OF: R-24831-43693 But 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-17957-41878-23952-00530-42808-37166-56745-23900 tcl slt th3 src

In the following query, the optimizer is free to reorder the tables of FROM clause anyway 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-17957-41878 */
# EVIDENCE-OF: R-17957-41878 In the following query, the optimizer is
# free to reorder the tables of FROM clause anyway 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-44141-33413-24990-15185-17174-27405-30480-46344 tcl slt th3 src

But 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-44141-33413 */
# EVIDENCE-OF: R-44141-33413 But 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-27542-21820-48287-27325-53525-05675-63695-54951 tcl slt th3 src

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

/* IMP: R-27542-21820 */
# EVIDENCE-OF: R-27542-21820 The ANALYZE command scans all indices of
# database where there might be a choice between two or more indices and
# gathers statistics on the selectiveness of those indices.

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-07057-45839-58945-17922-08934-43257-63274-32805 tcl slt th3 src

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

/* IMP: R-07057-45839 */
# EVIDENCE-OF: R-07057-45839 Terms of the WHERE clause can be manually
# disqualified for use with indices 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-49962-37036-40067-13077-38484-28797-22165-05685 tcl slt th3 src

In the example above, if column x has TEXT affinity then the comparison "x=5" will be done as text. But 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-49962-37036 */
# EVIDENCE-OF: R-49962-37036 In the example above, if column x has TEXT
# affinity then the comparison "x=5" will be done as text. But 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-01335-41434-49669-14652-18813-14877-14788-28819 tcl slt th3 src

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

/* IMP: R-01335-41434 */
# EVIDENCE-OF: R-01335-41434 Hence, the histogram data which is
# collected no left-most column of indices 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-34720-58307-10931-58766-48844-01756-24190-06684 tcl slt th3 src

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

/* IMP: R-34720-58307 */
# EVIDENCE-OF: R-34720-58307 SQLite will also attempt to use indices 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-50143-27238-56524-33998-35600-39903-63308-38872 tcl slt th3 src

For example: 

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

Would be rewritten using query flattening as: 

  SELECT x+y AS a FROM t1 WHERE z<100 AND a>5


/* IMP: R-50143-27238 */
# EVIDENCE-OF: R-50143-27238 For example: SELECT a FROM (SELECT x+y AS a
# FROM t1 WHERE z5 Would be rewritten using query flattening as: SELECT
# x+y AS a FROM t1 WHERE z5

R-46056-27065-32020-27937-15966-47144-54941-20671 tcl slt th3 src

The subquery and the outer query do not both use aggregates.

/* IMP: R-46056-27065 */
# EVIDENCE-OF: R-46056-27065 The subquery and the outer query do not
# both use aggregates.

R-56387-19459-30565-00993-44183-40692-62235-22092 tcl slt th3 src

The subquery is not an aggregate or the outer query is not a join.

/* IMP: R-56387-19459 */
# EVIDENCE-OF: R-56387-19459 The subquery is not an aggregate or the
# outer query is not a join.

R-02313-46896-62483-09816-33283-33292-64343-33302 tcl slt th3 src

The subquery is not the right operand of a left outer join.

/* IMP: R-02313-46896 */
# EVIDENCE-OF: R-02313-46896 The subquery is not the right operand of a
# left outer join.

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-07224-37960-07038-64465-45368-63797-43287-02157 tcl slt th3 src

The subquery does not use aggregates or the outer query is not DISTINCT.

/* IMP: R-07224-37960 */
# EVIDENCE-OF: R-07224-37960 The subquery does not use aggregates or the
# outer query 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-38341-46511-60647-36550-00452-63449-13223-34323 tcl slt th3 src

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

/* IMP: R-38341-46511 */
# EVIDENCE-OF: R-38341-46511 The subquery does not use aggregates or the
# outer query does not use LIMIT.

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-43931-64995-43515-53673-12278-45382-32660-63545 tcl slt th3 src

The outer query is not part of a compound select or the subquery does not have a LIMIT clause.

/* IMP: R-43931-64995 */
# EVIDENCE-OF: R-43931-64995 The outer query is not part of a compound
# select or the subquery does not have a LIMIT clause.

R-00202-17548-59772-16282-16027-29431-32424-58722 tcl slt th3 src

The outer query is not an aggregate or the subquery does not contain ORDER BY.

/* IMP: R-00202-17548 */
# EVIDENCE-OF: R-00202-17548 The outer query is not an aggregate or the
# subquery does not contain ORDER BY.

R-33987-21303-29717-40761-22942-02669-49814-53507 tcl slt th3 src

The sub-query is not a compound select, or it is a UNION ALL compound clause made up entirely of non-aggregate queries, and the parent query: 

/* IMP: R-33987-21303 */
# EVIDENCE-OF: R-33987-21303 The sub-query is not a compound select, or
# it is a UNION ALL compound clause made up entirely of non-aggregate
# queries, and the parent query: is not itself part of a compound
# select, is not an aggregate or DISTINCT query, and is not a 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-41108-63135-59113-45543-59580-27259-39094-46915 tcl slt th3 src

The subquery does not use LIMIT or the outer query does not have a WHERE clause.

/* IMP: R-41108-63135 */
# EVIDENCE-OF: R-41108-63135 The subquery does not use LIMIT or the
# outer query does 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-38341-53143-20377-01008-01065-23997-09704-35751 tcl slt th3 src

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

/* IMP: R-38341-53143 */
# EVIDENCE-OF: R-38341-53143 The subquery does not use LIMIT or the
# outer query is not DISTINCT.

R-41122-46973-40742-21767-11414-32017-24227-01412 tcl slt th3 src

The subquery is not a recursive CTE.

/* IMP: R-41122-46973 */
# EVIDENCE-OF: R-41122-46973 The subquery is not a recursive CTE.

R-47693-25331-52984-11068-59161-61137-57411-63213 tcl slt th3 src

The parent is not a recursive CTE, or the sub-query is not a compound query.

/* IMP: R-47693-25331 */
# EVIDENCE-OF: R-47693-25331 The parent is not a recursive CTE, or the
# sub-query is not a compound query.

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-32977-38363-55737-64786-46399-31149-31160-28820 tcl slt th3 src

When no indices 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-32977-38363 */
# EVIDENCE-OF: R-32977-38363 When no indices 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.