/ Check-in [042d655d]
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 | SQL 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
Context
2017-09-29
14:31
Merge changes from trunk. check-in: 06f432fb 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: 042d655d 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: ff2f5a31 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

  5200   5200       /* Catch mismatch in the declared columns of a view and the number of
  5201   5201       ** columns in the SELECT on the RHS */
  5202   5202       if( pTab->nCol!=pSub->pEList->nExpr ){
  5203   5203         sqlite3ErrorMsg(pParse, "expected %d columns for '%s' but got %d",
  5204   5204                         pTab->nCol, pTab->zName, pSub->pEList->nExpr);
  5205   5205         goto select_end;
  5206   5206       }
         5207  +
         5208  +    /* If the subquery contains an ORDER BY or GROUP BY clause and if
         5209  +    ** it will be implemented as a co-routine, then do not flatten.  This
         5210  +    ** restriction allows SQL constructs like this:
         5211  +    **
         5212  +    **  SELECT expensive_function(x)
         5213  +    **    FROM (SELECT x FROM tab ORDER BY y LIMIT 10);
         5214  +    **
         5215  +    ** The expensive_function() is only computed on the 10 rows that
         5216  +    ** are output, rather than every row of the table.
         5217  +    */
         5218  +    if( (pSub->pOrderBy!=0 || pSub->pGroupBy!=0)
         5219  +     && i==0
         5220  +     && (pTabList->nSrc==1
         5221  +         || (pTabList->a[1].fg.jointype&(JT_LEFT|JT_CROSS))!=0)
         5222  +     && OptimizationEnabled(db, SQLITE_SubqCoroutine)
         5223  +    ){
         5224  +      continue;
         5225  +    }
  5207   5226   
  5208   5227       isAggSub = (pSub->selFlags & SF_Aggregate)!=0;
  5209   5228       if( flattenSubquery(pParse, p, i, isAgg, isAggSub) ){
  5210   5229         /* This subquery can be absorbed into its parent. */
  5211   5230         if( isAggSub ){
  5212   5231           isAgg = 1;
  5213   5232           p->selFlags |= SF_Aggregate;