diff options
| author | drh <> | 2021-07-15 19:29:43 +0000 |
|---|---|---|
| committer | drh <> | 2021-07-15 19:29:43 +0000 |
| commit | bb301231788a7ca2292005ecf8d924dbf122f2d2 (patch) | |
| tree | 14293aa7205fd4473513a252dab25e0e2be0639b /src/select.c | |
| parent | 480f5e3e6e70e803b35ddeab947988a89b0163a0 (diff) | |
| download | sqlite-bb301231788a7ca2292005ecf8d924dbf122f2d2.tar.gz sqlite-bb301231788a7ca2292005ecf8d924dbf122f2d2.zip | |
Attempt to omit ORDER BY clauses from FROM-clause subqueries if those ORDER BY
clauses do not affect the output. See
[forum:/forumpost/2d76f2bcf65d256a|forum thread 2d76f2bcf65d256a] for
discussion. This can help the query flattener in
some cases, resulting in faster query plans. The current implemention does
not always work.
FossilOrigin-Name: ef97c3e7c3ea2cf1a4db6591328fe7ce3f1d189afc2d578159135824ec89e620
Diffstat (limited to 'src/select.c')
| -rw-r--r-- | src/select.c | 25 |
1 files changed, 25 insertions, 0 deletions
diff --git a/src/select.c b/src/select.c index d36839264..a6513afca 100644 --- a/src/select.c +++ b/src/select.c @@ -6384,6 +6384,31 @@ int sqlite3Select( if( (pSub->selFlags & SF_Aggregate)!=0 ) continue; assert( pSub->pGroupBy==0 ); + /* If a FROM-clause subquery has an ORDER BY clause that is not + ** really doing anything, then delete it now so that it does not + ** interfere with query flattening. + ** + ** Beware of these cases where the ORDER BY clause may not be safely + ** omitted: + ** + ** (1) There is also a LIMIT clause + ** (2) The subquery was added to help with window-function + ** processing + ** (3) The outer query uses an aggregate function other than + ** the built-in count(), min(), or max(). + */ + if( pSub->pOrderBy!=0 + && pSub->pLimit==0 /* Condition (1) */ + && (pSub->selFlags & SF_OrderByReqd)==0 /* Condition (2) */ + && (p->selFlags & SF_OrderByReqd)==0 /* Condition (3) */ + && OptimizationEnabled(db, SQLITE_OmitOrderBy) + ){ + SELECTTRACE(0x100,pParse,p, + ("omit superfluous ORDER BY on %r FROM-clause subquery\n",i+1)); + sqlite3ExprListDelete(db, pSub->pOrderBy); + pSub->pOrderBy = 0; + } + /* If the outer query contains a "complex" result set (that is, ** if the result set of the outer query uses functions or subqueries) ** and if the subquery contains an ORDER BY clause and if |