SQLite

Check-in [44b21e35c9]
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
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 44b21e35c92137cc519d0cc87c627cc531d0bd442a7b9a7356fa2c6bc5f70fb6
User & Date: drh 2017-05-23 15:21:37.230
Context
2017-05-23
19:35
Do not use strcpy() in the (obsolete) amatch extension. In releasetest.tcl, run Apple tests using -Os instead of -O1 (check-in: 4440e42031 user: drh tags: trunk)
15:33
Disable the LEFT JOIN flattening optimization for aggregate queries, as it does not currently work. Further fix for ticket [cad1ab4cb7b0fc344]. (check-in: 05ada74155 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: 44b21e35c9 user: drh tags: trunk)
12:36
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: 941d8142b7 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/select.c.
︙︙
3297
3298
3299
3300
3301
3302
3303
3304

3305
3306
3307
3308
3309
3310
3311

**
**   (2)  The subquery is not an aggregate or (2a) the outer query is not a join
**        and (2b) the outer query does not use subqueries other than the one
**        FROM-clause subquery that is a candidate for flattening.  (2b is
**        due to ticket [2f7170d73bf9abf80] from 2015-02-09.)
**
**   (3)  The subquery is not the right operand of a LEFT JOIN
**        or the subquery is not itself a join.

**
**   (4)  The subquery is not DISTINCT.
**
**  (**)  At one point restrictions (4) and (5) defined a subset of DISTINCT
**        sub-queries that were excluded from this optimization. Restriction 
**        (4) has since been expanded to exclude all DISTINCT subqueries.
**








|
>








3297
3298
3299
3300
3301
3302
3303
3304
3305
3306
3307
3308
3309
3310
3311
3312

**
**   (2)  The subquery is not an aggregate or (2a) the outer query is not a join
**        and (2b) the outer query does not use subqueries other than the one
**        FROM-clause subquery that is a candidate for flattening.  (2b is
**        due to ticket [2f7170d73bf9abf80] from 2015-02-09.)
**
**   (3)  The subquery is not the right operand of a LEFT JOIN
**        or the subquery is not itself a join and the outer query is not
**        an aggregate.
**
**   (4)  The subquery is not DISTINCT.
**
**  (**)  At one point restrictions (4) and (5) defined a subset of DISTINCT
**        sub-queries that were excluded from this optimization. Restriction 
**        (4) has since been expanded to exclude all DISTINCT subqueries.
**
︙︙
3492
3493
3494
3495
3496
3497
3498





3499
3500
3501
3502
3503
3504
3505
3506
3507
3508
3509
3510
3511

  **         t1 LEFT OUTER JOIN (t2 JOIN t3)
  **
  ** If we flatten the above, we would get
  **
  **         (t1 LEFT OUTER JOIN t2) JOIN t3
  **
  ** which is not at all the same thing.





  **
  ** See also tickets #306, #350, and #3300.
  */
  if( (pSubitem->fg.jointype & JT_OUTER)!=0 ){
    isLeftJoin = 1;
    if( pSubSrc->nSrc>1 ){
      return 0; /* Restriction (3) */
    }
  }

  /* Restriction 17: If the sub-query is a compound SELECT, then it must
  ** use only the UNION ALL operator. And none of the simple select queries
  ** that make up the compound SELECT are allowed to be aggregate or distinct








>
>
>
>
>





|








3493
3494
3495
3496
3497
3498
3499
3500
3501
3502
3503
3504
3505
3506
3507
3508
3509
3510
3511
3512
3513
3514
3515
3516
3517

  **         t1 LEFT OUTER JOIN (t2 JOIN t3)
  **
  ** If we flatten the above, we would get
  **
  **         (t1 LEFT OUTER JOIN t2) JOIN t3
  **
  ** which is not at all the same thing.
  **
  ** If the subquery is the right operand of a LEFT JOIN, then the outer
  ** query cannot be an aggregate.  This is an artifact of the way aggregates
  ** are processed - there is not mechanism to determine if the LEFT JOIN
  ** table should be all-NULL.
  **
  ** See also tickets #306, #350, and #3300.
  */
  if( (pSubitem->fg.jointype & JT_OUTER)!=0 ){
    isLeftJoin = 1;
    if( pSubSrc->nSrc>1 || isAgg ){
      return 0; /* Restriction (3) */
    }
  }

  /* Restriction 17: If the sub-query is a compound SELECT, then it must
  ** use only the UNION ALL operator. And none of the simple select queries
  ** that make up the compound SELECT are allowed to be aggregate or distinct
︙︙
Changes to test/join.test.
︙︙
727
728
729
730
731
732
733











734
735

    JOIN (SELECT * FROM (SELECT 9)) AS z;
} {1 1 1 9}
do_execsql_test join-14.3 {
  SELECT *
  FROM (SELECT 111)
  LEFT JOIN (SELECT cc+222, * FROM (SELECT * FROM (SELECT 333 cc)));
} {111 555 333}












finish_test








>
>
>
>
>
>
>
>
>
>
>



727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746

    JOIN (SELECT * FROM (SELECT 9)) AS z;
} {1 1 1 9}
do_execsql_test join-14.3 {
  SELECT *
  FROM (SELECT 111)
  LEFT JOIN (SELECT cc+222, * FROM (SELECT * FROM (SELECT 333 cc)));
} {111 555 333}

do_execsql_test join-14.4 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(c PRIMARY KEY, a TEXT(10000), b TEXT(10000));
  SELECT * FROM (SELECT 111) LEFT JOIN (SELECT c+222 FROM t1) GROUP BY 1;
} {111 {}}
do_execsql_test join-14.5 {
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(c PRIMARY KEY) WITHOUT ROWID;
  SELECT * FROM (SELECT 111) LEFT JOIN (SELECT c+222 FROM t1) GROUP BY 1;
} {111 {}}

finish_test