Documentation Source Text

}
PARAGRAPH {)^
  There is a long list of conditions that must all be met in order for
  query flattening to occur.  Some of the constraints are marked as 
  obsolete by italic text.  These extra constraints are retained in the
  documentation to preserve the numbering of the other constraints.
}









PARAGRAPH {
  <ol>
  <li value="1">  <i>(Obsolete.  Query flattening is no longer
                      attempted for aggregate subqueries.)</i>

  <li value="2">  <i>(Obsolete.  Query flattening is no longer
                      attempted for aggregate subqueries.)</i>

  <li value="3">
  ^The subquery is not the right operand of a LEFT JOIN then the

   subquery may not be a join, the FROM clause of the subquery may
   not contain a virtual table, and the outer query may not be
   an aggregate.

  <li value="4">  ^The subquery is not DISTINCT.

  <li value="5"> <i>(Subsumed into constraint 4)</i>

  <li value="6"> <i>(Obsolete.  Query flattening is no longer
                      attempted for aggregate subqueries.)</i>
................................................................................
  <li value="12"> <i>(Subsumed into constraint 3)</i>

  <li value="13">  ^The subquery and outer query do not both use LIMIT.

  <li value="14">  ^The subquery does not use OFFSET.

  <li value="15">
  ^The outer query is part of a compound select, then the
  subquery may not have a LIMIT clause.

  <li value="16">
  ^The outer query is an aggregate, then the subquery may
  not contain ORDER BY. 

  <li value="17">
  ^(The sub-query is a compound SELECT, then
  <ol type='a'>
  <li> all compound operators must be UNION ALL, and
  <li> no terms with the subquery compound may be aggregate
       or distinct, and
  <li> every term within the subquery must have a FROM clause, and
  <li> the outer query may not be an aggregate, DISTINCT query, or join.
  </ol>)^

  ^The parent and sub-query may contain WHERE clauses. ^Subject to
  rules (11), (12) and (13), they may also contain ORDER BY,
  LIMIT and OFFSET clauses.
................................................................................
  ORDER by clause of the parent must be simple references to 
  columns of the sub-query.

  <li value="19">
  ^If the subquery uses LIMIT then the outer query may not
  have a WHERE clause.

  <li value="20"> <i>(Subsumed into constraint 17d.)</i>



  <li value="21">
  ^The subquery uses LIMIT, then the outer query may not be
  DISTINCT.

  <li value="22"> ^The subquery may not a recursive CTE.

  <li value="23"> <i>(Subsumed into constraint 17d.)</i>

  <li value="24"> <i>(Obsolete. Query flattening is no longer
                      attempted for aggregate subqueries.)</i>
  </ol>
}
................................................................................
  each use of a view is translated into a subquery.
}

HEADING 1 {Subquery Co-routines} coroutines
hd_keywords {subquery co-routines} {co-routines}

PARAGRAPH {

  In older versions of SQLite, a subquery in the FROM clause would be
  either flattened into the outer query, or else the subquery would be run
  to completion
  before the outer query started, the result set from the subquery
  would be stored in a transient table,
  and then the transient table would be used in the outer query.  Newer
  versions of SQLite have a third option, which is to implement the subquery
  using a co-routine.
................................................................................
}

HEADING 2 {Using Co-routines To Defer Work Until After The Sorting} \
  deferred_work

PARAGRAPH {
  As of SQLite version 3.21.0 ([dateof:3.21.0]), the query planner will
  always use a co-routine to implement FROM-clause subqueries that contain
  an ORDER BY clause and that are not part of a join.  This feature allows

  applications to shift expensive computations from before the
  sorter until after the sorter, which can result in faster operation.
  For example, consider this query:
}
CODE {
  SELECT expensive_function(a) FROM tab ORDER BY date DESC LIMIT 5;
}
................................................................................
  invoked on only the specific rows that the application cares about.
}
PARAGRAPH {
  The query planner in future versions of SQLite might grow smart enough
  to make transformations such as the above automatically, in both directions.
  That is to say, future versions of SQLite might transform queries of the
  first form into the second, or queries written the second way into the



  first.  But for now, SQLite implements each of the above queries as
  written.
}

HEADING 1 {The MIN/MAX optimization} minmax

PARAGRAPH {
  ^(Queries that contain a single MIN() or MAX() aggregate function whose

}
PARAGRAPH {)^
  There is a long list of conditions that must all be met in order for
  query flattening to occur.  Some of the constraints are marked as 
  obsolete by italic text.  These extra constraints are retained in the
  documentation to preserve the numbering of the other constraints.
}
PARAGRAPH {
  Casual readers are not expected to understand all of these rules.
  A key take-away from this section is that the rules for determining
  when query flatting is safe and when it is unsafe are subtle and
  complex.  There have been multiple bugs over the years caused by
  over-aggressive query flattening.  On the other hand, performance
  of complex queries and/or queries involving views tends to suffer
  if query flattening is more conservative.
}
PARAGRAPH {
  <ol>
  <li value="1">  <i>(Obsolete.  Query flattening is no longer
                      attempted for aggregate subqueries.)</i>

  <li value="2">  <i>(Obsolete.  Query flattening is no longer
                      attempted for aggregate subqueries.)</i>

  <li value="3">
  ^If the subquery is not the right operand of a LEFT JOIN then
   <ol type="a"><li> the subquery may not be a join, and
   <li> the FROM clause of the subquery may
   not contain a virtual table, and
   <li> the outer query may not be an aggregate.</ol></li>

  <li value="4">  ^The subquery is not DISTINCT.

  <li value="5"> <i>(Subsumed into constraint 4)</i>

  <li value="6"> <i>(Obsolete.  Query flattening is no longer
                      attempted for aggregate subqueries.)</i>
................................................................................
  <li value="12"> <i>(Subsumed into constraint 3)</i>

  <li value="13">  ^The subquery and outer query do not both use LIMIT.

  <li value="14">  ^The subquery does not use OFFSET.

  <li value="15">
  ^If the outer query is part of a compound select, then the
  subquery may not have a LIMIT clause.

  <li value="16">
  ^If the outer query is an aggregate, then the subquery may
  not contain ORDER BY. 

  <li value="17">
  ^(If the sub-query is a compound SELECT, then
  <ol type='a'>
  <li> all compound operators must be UNION ALL, and
  <li> no terms with the subquery compound may be aggregate
       or DISTINCT, and
  <li> every term within the subquery must have a FROM clause, and
  <li> the outer query may not be an aggregate, DISTINCT query, or join.
  </ol>)^

  ^The parent and sub-query may contain WHERE clauses. ^Subject to
  rules (11), (12) and (13), they may also contain ORDER BY,
  LIMIT and OFFSET clauses.
................................................................................
  ORDER by clause of the parent must be simple references to 
  columns of the sub-query.

  <li value="19">
  ^If the subquery uses LIMIT then the outer query may not
  have a WHERE clause.

  <li value="20">
  ^If the sub-query is a compound select, then it must not use
   an ORDER BY clause.

  <li value="21">
  ^If the subquery uses LIMIT, then the outer query may not be
  DISTINCT.

  <li value="22"> ^The subquery may not be a recursive CTE.

  <li value="23"> <i>(Subsumed into constraint 17d.)</i>

  <li value="24"> <i>(Obsolete. Query flattening is no longer
                      attempted for aggregate subqueries.)</i>
  </ol>
}
................................................................................
  each use of a view is translated into a subquery.
}

HEADING 1 {Subquery Co-routines} coroutines
hd_keywords {subquery co-routines} {co-routines}

PARAGRAPH {
  Prior to SQLite 3.7.15 ([dateof:3.7.15]),
  a subquery in the FROM clause would be
  either flattened into the outer query, or else the subquery would be run
  to completion
  before the outer query started, the result set from the subquery
  would be stored in a transient table,
  and then the transient table would be used in the outer query.  Newer
  versions of SQLite have a third option, which is to implement the subquery
  using a co-routine.
................................................................................
}

HEADING 2 {Using Co-routines To Defer Work Until After The Sorting} \
  deferred_work

PARAGRAPH {
  As of SQLite version 3.21.0 ([dateof:3.21.0]), the query planner will
  always prefer to use a co-routine to implement FROM-clause subqueries 
  that contains an ORDER BY clause and that are not part of a join when
  the result set of the outer query is "complex".  This feature allows
  applications to shift expensive computations from before the
  sorter until after the sorter, which can result in faster operation.
  For example, consider this query:
}
CODE {
  SELECT expensive_function(a) FROM tab ORDER BY date DESC LIMIT 5;
}
................................................................................
  invoked on only the specific rows that the application cares about.
}
PARAGRAPH {
  The query planner in future versions of SQLite might grow smart enough
  to make transformations such as the above automatically, in both directions.
  That is to say, future versions of SQLite might transform queries of the
  first form into the second, or queries written the second way into the
  first.  As of SQLite version 3.22.0 ([dateof:3.22.0]), the query planner
  will flatten the subquery if the outer query does not make use of any
  user-defined functions or subqueries in its result set.  For the examples
  shown above, however, SQLite implements each of the queries as
  written.
}

HEADING 1 {The MIN/MAX optimization} minmax

PARAGRAPH {
  ^(Queries that contain a single MIN() or MAX() aggregate function whose