/ Check-in [dab4c137]
Login

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

Overview
Comment:Update comments describing the flattening optimization in select.c.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: dab4c137a852222f11179fa2ade52d17a4206dd2
User & Date: drh 2011-12-11 21:51:04
Context
2011-12-11
22:44
In the multiplexor shim, when using 8+3 filenames, begin numbering journal overflow files with 101 instead of 001 to avoid name collisions with the main database file. check-in: 05bf8a0b user: drh tags: trunk
21:51
Update comments describing the flattening optimization in select.c. check-in: dab4c137 user: drh tags: trunk
02:30
Fix STAT3 so that it works with the new uninitialized register logic of the VDBE. Ticket [7bbfb7d4422ff] check-in: d11a5798 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

  2584   2584       }
  2585   2585     }
  2586   2586   }
  2587   2587   #endif /* !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW) */
  2588   2588   
  2589   2589   #if !defined(SQLITE_OMIT_SUBQUERY) || !defined(SQLITE_OMIT_VIEW)
  2590   2590   /*
  2591         -** This routine attempts to flatten subqueries in order to speed
  2592         -** execution.  It returns 1 if it makes changes and 0 if no flattening
  2593         -** occurs.
         2591  +** This routine attempts to flatten subqueries as a performance optimization.
         2592  +** This routine returns 1 if it makes changes and 0 if no flattening occurs.
  2594   2593   **
  2595   2594   ** To understand the concept of flattening, consider the following
  2596   2595   ** query:
  2597   2596   **
  2598   2597   **     SELECT a FROM (SELECT x+y AS a FROM t1 WHERE z<100) WHERE a>5
  2599   2598   **
  2600   2599   ** The default way of implementing this query is to execute the
................................................................................
  2628   2627   **  (**)  At one point restrictions (4) and (5) defined a subset of DISTINCT
  2629   2628   **        sub-queries that were excluded from this optimization. Restriction 
  2630   2629   **        (4) has since been expanded to exclude all DISTINCT subqueries.
  2631   2630   **
  2632   2631   **   (6)  The subquery does not use aggregates or the outer query is not
  2633   2632   **        DISTINCT.
  2634   2633   **
  2635         -**   (7)  The subquery has a FROM clause.
         2634  +**   (7)  The subquery has a FROM clause.  TODO:  For subqueries without
         2635  +**        A FROM clause, consider adding a FROM close with the special
         2636  +**        table sqlite_once that consists of a single row containing a
         2637  +**        single NULL.
  2636   2638   **
  2637   2639   **   (8)  The subquery does not use LIMIT or the outer query is not a join.
  2638   2640   **
  2639   2641   **   (9)  The subquery does not use LIMIT or the outer query does not use
  2640   2642   **        aggregates.
  2641   2643   **
  2642   2644   **  (10)  The subquery does not use aggregates or the outer query does not
................................................................................
  2661   2663   **
  2662   2664   **  (17)  The sub-query is not a compound select, or it is a UNION ALL 
  2663   2665   **        compound clause made up entirely of non-aggregate queries, and 
  2664   2666   **        the parent query:
  2665   2667   **
  2666   2668   **          * is not itself part of a compound select,
  2667   2669   **          * is not an aggregate or DISTINCT query, and
  2668         -**          * has no other tables or sub-selects in the FROM clause.
         2670  +**          * is not a join
  2669   2671   **
  2670   2672   **        The parent and sub-query may contain WHERE clauses. Subject to
  2671   2673   **        rules (11), (13) and (14), they may also contain ORDER BY,
  2672         -**        LIMIT and OFFSET clauses.
         2674  +**        LIMIT and OFFSET clauses.  The subquery cannot use any compound
         2675  +**        operator other than UNION ALL because all the other compound
         2676  +**        operators have an implied DISTINCT which is disallowed by
         2677  +**        restriction (4).
  2673   2678   **
  2674   2679   **  (18)  If the sub-query is a compound select, then all terms of the
  2675   2680   **        ORDER by clause of the parent must be simple references to 
  2676   2681   **        columns of the sub-query.
  2677   2682   **
  2678   2683   **  (19)  The subquery does not use LIMIT or the outer query does not
  2679   2684   **        have a WHERE clause.
  2680   2685   **
  2681   2686   **  (20)  If the sub-query is a compound select, then it must not use
  2682   2687   **        an ORDER BY clause.  Ticket #3773.  We could relax this constraint
  2683   2688   **        somewhat by saying that the terms of the ORDER BY clause must
  2684         -**        appear as unmodified result columns in the outer query.  But
         2689  +**        appear as unmodified result columns in the outer query.  But we
  2685   2690   **        have other optimizations in mind to deal with that case.
  2686   2691   **
  2687   2692   **  (21)  The subquery does not use LIMIT or the outer query is not
  2688   2693   **        DISTINCT.  (See ticket [752e1646fc]).
  2689   2694   **
  2690   2695   ** In this routine, the "p" parameter is a pointer to the outer query.
  2691   2696   ** The subquery is p->pSrc->a[iFrom].  isAgg is true if the outer query