Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -3299,11 +3299,12 @@ ** 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. +** 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 @@ -3494,16 +3495,21 @@ ** 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 ){ + if( pSubSrc->nSrc>1 || isAgg ){ return 0; /* Restriction (3) */ } } /* Restriction 17: If the sub-query is a compound SELECT, then it must Index: test/join.test ================================================================== --- test/join.test +++ test/join.test @@ -729,7 +729,18 @@ 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