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: |
a599e76d952a7840b67131823515be93 |
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
Changes to pages/optoverview.in.
︙ | ︙ | |||
1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 | } 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"> | > > > > > > > > > | > | | | | 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 | <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"> | | | | | | > > | | | 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 | each use of a view is translated into a subquery. } HEADING 1 {Subquery Co-routines} coroutines hd_keywords {subquery co-routines} {co-routines} PARAGRAPH { | > | | 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 | } 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 | | | > | 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 | 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 | > > > | | 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 |
︙ | ︙ |