/ Check-in [05ada741]
Login

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

Overview
Comment:Disable the LEFT JOIN flattening optimization for aggregate queries, as it does not currently work. Further fix for ticket [cad1ab4cb7b0fc344].
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | branch-3.19
Files: files | file ages | folders
SHA3-256: 05ada741554b9fd00befcf7b4083637f53de68699ae5210ac18e0773ad1a9910
User & Date: drh 2017-05-23 15:33:41
Context
2017-05-24
13:08
Version 3.19.1 check-in: f6d7b988 user: drh tags: release, branch-3.19, version-3.19.1
2017-05-23
15:33
Disable the LEFT JOIN flattening optimization for aggregate queries, as it does not currently work. Further fix for ticket [cad1ab4cb7b0fc344]. check-in: 05ada741 user: drh tags: branch-3.19
15:21
Disable the LEFT JOIN flattening optimization for aggregate queries, as it does not currently work. Further fix for ticket [cad1ab4cb7b0fc344]. check-in: 44b21e35 user: drh tags: trunk
12:44
Ensure that the expression rewriter inside the query flattener decends into the substructure of the TK_IF_NULL_ROW operator. This is a continuation of the fix for ticket [cad1ab4cb7b0fc344]. check-in: 28d2902d user: drh tags: branch-3.19
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/select.c.

  3297   3297   **
  3298   3298   **   (2)  The subquery is not an aggregate or (2a) the outer query is not a join
  3299   3299   **        and (2b) the outer query does not use subqueries other than the one
  3300   3300   **        FROM-clause subquery that is a candidate for flattening.  (2b is
  3301   3301   **        due to ticket [2f7170d73bf9abf80] from 2015-02-09.)
  3302   3302   **
  3303   3303   **   (3)  The subquery is not the right operand of a LEFT JOIN
  3304         -**        or the subquery is not itself a join.
         3304  +**        or the subquery is not itself a join and the outer query is not
         3305  +**        an aggregate.
  3305   3306   **
  3306   3307   **   (4)  The subquery is not DISTINCT.
  3307   3308   **
  3308   3309   **  (**)  At one point restrictions (4) and (5) defined a subset of DISTINCT
  3309   3310   **        sub-queries that were excluded from this optimization. Restriction 
  3310   3311   **        (4) has since been expanded to exclude all DISTINCT subqueries.
  3311   3312   **
................................................................................
  3492   3493     **         t1 LEFT OUTER JOIN (t2 JOIN t3)
  3493   3494     **
  3494   3495     ** If we flatten the above, we would get
  3495   3496     **
  3496   3497     **         (t1 LEFT OUTER JOIN t2) JOIN t3
  3497   3498     **
  3498   3499     ** which is not at all the same thing.
         3500  +  **
         3501  +  ** If the subquery is the right operand of a LEFT JOIN, then the outer
         3502  +  ** query cannot be an aggregate.  This is an artifact of the way aggregates
         3503  +  ** are processed - there is not mechanism to determine if the LEFT JOIN
         3504  +  ** table should be all-NULL.
  3499   3505     **
  3500   3506     ** See also tickets #306, #350, and #3300.
  3501   3507     */
  3502   3508     if( (pSubitem->fg.jointype & JT_OUTER)!=0 ){
  3503   3509       isLeftJoin = 1;
  3504         -    if( pSubSrc->nSrc>1 ){
         3510  +    if( pSubSrc->nSrc>1 || isAgg ){
  3505   3511         return 0; /* Restriction (3) */
  3506   3512       }
  3507   3513     }
  3508   3514   
  3509   3515     /* Restriction 17: If the sub-query is a compound SELECT, then it must
  3510   3516     ** use only the UNION ALL operator. And none of the simple select queries
  3511   3517     ** that make up the compound SELECT are allowed to be aggregate or distinct

Changes to test/join.test.

   727    727       JOIN (SELECT * FROM (SELECT 9)) AS z;
   728    728   } {1 1 1 9}
   729    729   do_execsql_test join-14.3 {
   730    730     SELECT *
   731    731     FROM (SELECT 111)
   732    732     LEFT JOIN (SELECT cc+222, * FROM (SELECT * FROM (SELECT 333 cc)));
   733    733   } {111 555 333}
          734  +
          735  +do_execsql_test join-14.4 {
          736  +  DROP TABLE IF EXISTS t1;
          737  +  CREATE TABLE t1(c PRIMARY KEY, a TEXT(10000), b TEXT(10000));
          738  +  SELECT * FROM (SELECT 111) LEFT JOIN (SELECT c+222 FROM t1) GROUP BY 1;
          739  +} {111 {}}
          740  +do_execsql_test join-14.5 {
          741  +  DROP TABLE IF EXISTS t1;
          742  +  CREATE TABLE t1(c PRIMARY KEY) WITHOUT ROWID;
          743  +  SELECT * FROM (SELECT 111) LEFT JOIN (SELECT c+222 FROM t1) GROUP BY 1;
          744  +} {111 {}}
   734    745   
   735    746   finish_test