SQLite

Check-in [042d655dd9]
Login

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

Overview
Comment:Do not flatten subqueries that contain an ORDER BY or GROUP BY clause and can be implemented using a co-routine.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | prefer-coroutine-sort-subquery
Files: files | file ages | folders
SHA3-256: 042d655dd9002e8b89a798ad955b0285891aecf79f6978c5312e70ffe0609a46
User & Date: drh 2017-09-28 20:06:53.014
Context
2017-09-29
14:31
Merge changes from trunk. (check-in: 06f432fb7c user: drh tags: prefer-coroutine-sort-subquery)
2017-09-28
20:06
Do not flatten subqueries that contain an ORDER BY or GROUP BY clause and can be implemented using a co-routine. (check-in: 042d655dd9 user: drh tags: prefer-coroutine-sort-subquery)
17:29
Remove the (undocumented) query-planner control that prevents a "SELECT ALL" subquery in FROM clause from being implemented as a co-routine. This control was added by [a29e117d7ec], where it was called a "stop-gap". (check-in: ff2f5a31a2 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/select.c.
5200
5201
5202
5203
5204
5205
5206



















5207
5208
5209
5210
5211
5212
5213
    /* Catch mismatch in the declared columns of a view and the number of
    ** columns in the SELECT on the RHS */
    if( pTab->nCol!=pSub->pEList->nExpr ){
      sqlite3ErrorMsg(pParse, "expected %d columns for '%s' but got %d",
                      pTab->nCol, pTab->zName, pSub->pEList->nExpr);
      goto select_end;
    }




















    isAggSub = (pSub->selFlags & SF_Aggregate)!=0;
    if( flattenSubquery(pParse, p, i, isAgg, isAggSub) ){
      /* This subquery can be absorbed into its parent. */
      if( isAggSub ){
        isAgg = 1;
        p->selFlags |= SF_Aggregate;







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







5200
5201
5202
5203
5204
5205
5206
5207
5208
5209
5210
5211
5212
5213
5214
5215
5216
5217
5218
5219
5220
5221
5222
5223
5224
5225
5226
5227
5228
5229
5230
5231
5232
    /* Catch mismatch in the declared columns of a view and the number of
    ** columns in the SELECT on the RHS */
    if( pTab->nCol!=pSub->pEList->nExpr ){
      sqlite3ErrorMsg(pParse, "expected %d columns for '%s' but got %d",
                      pTab->nCol, pTab->zName, pSub->pEList->nExpr);
      goto select_end;
    }

    /* If the subquery contains an ORDER BY or GROUP BY clause and if
    ** it will be implemented as a co-routine, then do not flatten.  This
    ** restriction allows SQL constructs like this:
    **
    **  SELECT expensive_function(x)
    **    FROM (SELECT x FROM tab ORDER BY y LIMIT 10);
    **
    ** The expensive_function() is only computed on the 10 rows that
    ** are output, rather than every row of the table.
    */
    if( (pSub->pOrderBy!=0 || pSub->pGroupBy!=0)
     && i==0
     && (pTabList->nSrc==1
         || (pTabList->a[1].fg.jointype&(JT_LEFT|JT_CROSS))!=0)
     && OptimizationEnabled(db, SQLITE_SubqCoroutine)
    ){
      continue;
    }

    isAggSub = (pSub->selFlags & SF_Aggregate)!=0;
    if( flattenSubquery(pParse, p, i, isAgg, isAggSub) ){
      /* This subquery can be absorbed into its parent. */
      if( isAggSub ){
        isAgg = 1;
        p->selFlags |= SF_Aggregate;