Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -3793,16 +3793,22 @@ Expr *pWhere, /* The WHERE clause of the outer query */ int iCursor /* Cursor number of the subquery */ ){ Expr *pNew; int nChng = 0; + Select *pX; /* For looping over compound SELECTs in pSubq */ if( pWhere==0 ) return 0; - if( (pSubq->selFlags & (SF_Aggregate|SF_Recursive))!=0 ){ - return 0; /* restrictions (1) and (2) */ + for(pX=pSubq; pX; pX=pX->pPrior){ + if( (pX->selFlags & (SF_Aggregate|SF_Recursive))!=0 ){ + testcase( pX->selFlags & SF_Aggregate ); + testcase( pX->selFlags & SF_Recursive ); + testcase( pX!=pSubq ); + return 0; /* restrictions (1) and (2) */ + } } if( pSubq->pLimit!=0 ){ - return 0; /* restriction (3) */ + return 0; /* restriction (3) */ } while( pWhere->op==TK_AND ){ nChng += pushDownWhereTerms(db, pSubq, pWhere->pRight, iCursor); pWhere = pWhere->pLeft; } Index: test/select4.test ================================================================== --- test/select4.test +++ test/select4.test @@ -861,7 +861,44 @@ SELECT * FROM t14 EXCEPT VALUES('a','b','c') EXCEPT VALUES(4,5,6) } {1 2 3} do_execsql_test select4-14.9 { SELECT * FROM t14 UNION ALL VALUES(3,2,1),(2,3,1),(1,2,3),(2,1,3); } {1 2 3 4 5 6 3 2 1 2 3 1 1 2 3 2 1 3} + +# Ticket https://www.sqlite.org/src/tktview/f7f8c97e975978d45 on 2016-04-25 +# +# The where push-down optimization from 2015-06-02 is suppose to disable +# on aggregate subqueries. But if the subquery is a compound where the +# last SELECT is non-aggregate but some other SELECT is an aggregate, the +# test is incomplete and the optimization is not properly disabled. +# +# The following test cases verify that the fix works. +# +do_execsql_test select4-17.1 { + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(a int, b int); + INSERT INTO t1 VALUES(1,2),(1,18),(2,19); + SELECT x, y FROM ( + SELECT 98 AS x, 99 AS y + UNION + SELECT a AS x, sum(b) AS y FROM t1 GROUP BY a + ) AS w WHERE y>=20 + ORDER BY +x; +} {1 20 98 99} +do_execsql_test select4-17.2 { + SELECT x, y FROM ( + SELECT a AS x, sum(b) AS y FROM t1 GROUP BY a + UNION + SELECT 98 AS x, 99 AS y + ) AS w WHERE y>=20 + ORDER BY +x; +} {1 20 98 99} +do_catchsql_test select4-17.3 { + SELECT x, y FROM ( + SELECT a AS x, sum(b) AS y FROM t1 GROUP BY a LIMIT 3 + UNION + SELECT 98 AS x, 99 AS y + ) AS w WHERE y>=20 + ORDER BY +x; +} {1 {LIMIT clause should come after UNION not before}} finish_test