diff options
| author | drh <> | 2023-08-02 11:06:27 +0000 |
|---|---|---|
| committer | drh <> | 2023-08-02 11:06:27 +0000 |
| commit | 5e82c06cf89a5d3d57f8cbb206a7142c9c4c4a0c (patch) | |
| tree | fecc19454bb7fe8b66be15609e6dff0da94cc7a3 /src | |
| parent | 4178849736c81ec17bbfb2628ec0ef7bad94962d (diff) | |
| download | sqlite-5e82c06cf89a5d3d57f8cbb206a7142c9c4c4a0c.tar.gz sqlite-5e82c06cf89a5d3d57f8cbb206a7142c9c4c4a0c.zip | |
Never flatten a CTE that is labeled MATERIALIZED. This is really something
of a bug fix because the documentation says that a MATERIALIZED CTE is an
optimization barrier.
FossilOrigin-Name: b7ef9796f548ce43e06673f86bbec68157aae5e4ee8451e1d87ee5f70af1bb27
Diffstat (limited to 'src')
| -rw-r--r-- | src/select.c | 14 |
1 files changed, 13 insertions, 1 deletions
diff --git a/src/select.c b/src/select.c index 7f73b1928..7a3ba4a6f 100644 --- a/src/select.c +++ b/src/select.c @@ -7314,7 +7314,14 @@ int sqlite3Select( ** (a) The outer query has a different ORDER BY clause ** (b) The subquery is part of a join ** See forum post 062d576715d277c8 + ** + ** The above cases might get an incorrect result if ORDER BY is omitted. + ** The following constraints are not required for correct answers, but are + ** included in order to give developers more control over when a sort + ** occurs: + ** ** (6) The subquery is really a MATERIALIZED CTE + ** (7) The OmitOrderBy optimization is disabled */ if( pSub->pOrderBy!=0 && (p->pOrderBy!=0 || pTabList->nSrc>1) /* Condition (5) */ @@ -7322,7 +7329,7 @@ int sqlite3Select( && (pSub->selFlags & SF_OrderByReqd)==0 /* Condition (2) */ && (p->selFlags & SF_OrderByReqd)==0 /* Condition (3) and (4) */ && (pItem->fg.isCte==0 || pItem->u2.pCteUse->eM10d!=M10d_Yes) /* (6) */ - && OptimizationEnabled(db, SQLITE_OmitOrderBy) + && OptimizationEnabled(db, SQLITE_OmitOrderBy) /* (7) */ ){ TREETRACE(0x800,pParse,p, ("omit superfluous ORDER BY on %r FROM-clause subquery\n",i+1)); @@ -7359,6 +7366,11 @@ int sqlite3Select( continue; } + /* Do not flatten a MATERIALIZED CTE */ + if( pItem->fg.isCte && pItem->u2.pCteUse->eM10d==M10d_Yes ){ + continue; + } + if( flattenSubquery(pParse, p, i, isAgg) ){ if( pParse->nErr ) goto select_end; /* This subquery can be absorbed into its parent. */ |