Documentation Source Text

Check-in [a599e76d95]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Bring the query flattening section of the optoverview.html document up to date with version 3.22.0.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: a599e76d952a7840b67131823515be93a9b40105ee47f92f160922c2ac6661fe
User & Date: drh 2018-01-26 15:44:20.459
Context
2018-01-26
15:50
Turn on "fancy-format" for the optoverview.html document. (check-in: 98e66068fe user: drh tags: trunk)
15:44
Bring the query flattening section of the optoverview.html document up to date with version 3.22.0. (check-in: a599e76d95 user: drh tags: trunk)
2018-01-25
16:06
Fix a typo on the privatebranch.html page. (check-in: a41da69db7 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/optoverview.in.
1054
1055
1056
1057
1058
1059
1060









1061
1062
1063
1064
1065
1066
1067
1068
1069
1070

1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
}
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>







>
>
>
>
>
>
>
>
>









|
>
|
|
|







1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
}
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>
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134


1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
  <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.

  <li value="18">
  ^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.

  <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>
}







|



|



|



|

















|
>
>


|


|







1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
  <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.

  <li value="18">
  ^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.

  <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>
}
1155
1156
1157
1158
1159
1160
1161

1162
1163
1164
1165
1166
1167
1168
1169
  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.







>
|







1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
  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.
1200
1201
1202
1203
1204
1205
1206
1207
1208

1209
1210
1211
1212
1213
1214
1215
}

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;
}







|
|
>







1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
}

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;
}
1233
1234
1235
1236
1237
1238
1239



1240
1241
1242
1243
1244
1245
1246
1247
  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







>
>
>
|







1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
1261
1262
1263
1264
  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