Disable the query flattener for aggregate subqueries if the parent query

uses other subqueries in its result set or WHERE clause or ORDER BY clause.
Preliminary fix for ticket [2f7170d73bf9abf8].  However it still contains
a defect similar to the COLLATE problem of [ca0d20b6cddd].

FossilOrigin-Name: 0b7d65e3fda676d193347cb782854c28a48252af

4 files changed, 41 insertions, 9 deletions

diff --git a/src/expr.c b/src/expr.c
index 2f0fe4a58..2def950f2 100644
--- a/src/expr.c
+++ b/src/expr.c
@@ -490,11 +490,11 @@ void sqlite3ExprAttachSubtrees(
   }else{
     if( pRight ){
       pRoot->pRight = pRight;
-      pRoot->flags |= EP_Collate & pRight->flags;
+      pRoot->flags |= EP_Propagate & pRight->flags;
     }
     if( pLeft ){
       pRoot->pLeft = pLeft;
-      pRoot->flags |= EP_Collate & pLeft->flags;
+      pRoot->flags |= EP_Propagate & pLeft->flags;
     }
     exprSetHeight(pRoot);
   }
@@ -1210,6 +1210,19 @@ void sqlite3ExprListDelete(sqlite3 *db, ExprList *pList){
 }
 
 /*
+** Return TRUE if any expression in ExprList has any of the EP_*
+** properties given by "m"
+*/
+int sqlite3AnyExprListHasProperty(const ExprList *pList, u32 m){
+  int i;
+  if( pList==0 ) return 0;
+  for(i=0; i<pList->nExpr; i++){
+    if( ExprHasProperty(pList->a[i].pExpr, m) ) return 1;
+  }
+  return 0;
+}
+
+/*
 ** These routines are Walker callbacks used to check expressions to
 ** see if they are "constant" for some definition of constant.  The
 ** Walker.eCode value determines the type of "constant" we are looking
diff --git a/src/parse.y b/src/parse.y
index 544888a22..6730312d7 100644
--- a/src/parse.y
+++ b/src/parse.y
@@ -1091,7 +1091,7 @@ expr(A) ::= expr(W) between_op(N) expr(X) AND expr(Y). [BETWEEN] {
     A.pExpr = sqlite3PExpr(pParse, TK_SELECT, 0, 0, 0);
     if( A.pExpr ){
       A.pExpr->x.pSelect = X;
-      ExprSetProperty(A.pExpr, EP_xIsSelect);
+      ExprSetProperty(A.pExpr, EP_xIsSelect|EP_Subquery);
       sqlite3ExprSetHeight(pParse, A.pExpr);
     }else{
       sqlite3SelectDelete(pParse->db, X);
@@ -1103,7 +1103,7 @@ expr(A) ::= expr(W) between_op(N) expr(X) AND expr(Y). [BETWEEN] {
     A.pExpr = sqlite3PExpr(pParse, TK_IN, X.pExpr, 0, 0);
     if( A.pExpr ){
       A.pExpr->x.pSelect = Y;
-      ExprSetProperty(A.pExpr, EP_xIsSelect);
+      ExprSetProperty(A.pExpr, EP_xIsSelect|EP_Subquery);
       sqlite3ExprSetHeight(pParse, A.pExpr);
     }else{
       sqlite3SelectDelete(pParse->db, Y);
@@ -1117,7 +1117,7 @@ expr(A) ::= expr(W) between_op(N) expr(X) AND expr(Y). [BETWEEN] {
     A.pExpr = sqlite3PExpr(pParse, TK_IN, X.pExpr, 0, 0);
     if( A.pExpr ){
       A.pExpr->x.pSelect = sqlite3SelectNew(pParse, 0,pSrc,0,0,0,0,0,0,0);
-      ExprSetProperty(A.pExpr, EP_xIsSelect);
+      ExprSetProperty(A.pExpr, EP_xIsSelect|EP_Subquery);
       sqlite3ExprSetHeight(pParse, A.pExpr);
     }else{
       sqlite3SrcListDelete(pParse->db, pSrc);
@@ -1130,7 +1130,7 @@ expr(A) ::= expr(W) between_op(N) expr(X) AND expr(Y). [BETWEEN] {
     Expr *p = A.pExpr = sqlite3PExpr(pParse, TK_EXISTS, 0, 0, 0);
     if( p ){
       p->x.pSelect = Y;
-      ExprSetProperty(p, EP_xIsSelect);
+      ExprSetProperty(p, EP_xIsSelect|EP_Subquery);
       sqlite3ExprSetHeight(pParse, p);
     }else{
       sqlite3SelectDelete(pParse->db, Y);
diff --git a/src/select.c b/src/select.c
index 058bf3d57..2c4c9e41f 100644
--- a/src/select.c
+++ b/src/select.c
@@ -3194,7 +3194,10 @@ static void substSelect(
 **
 **   (1)  The subquery and the outer query do not both use aggregates.
 **
-**   (2)  The subquery is not an aggregate or the outer query is not a join.
+**   (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 outer join
 **        (Originally ticket #306.  Strengthened by ticket #3300)
@@ -3331,8 +3334,17 @@ static int flattenSubquery(
   iParent = pSubitem->iCursor;
   pSub = pSubitem->pSelect;
   assert( pSub!=0 );
-  if( isAgg && subqueryIsAgg ) return 0;                 /* Restriction (1)  */
-  if( subqueryIsAgg && pSrc->nSrc>1 ) return 0;          /* Restriction (2)  */
+  if( subqueryIsAgg ){
+    if( isAgg ) return 0;                                /* Restriction (1)   */
+    if( pSrc->nSrc>1 ) return 0;                         /* Restriction (2a)  */
+    if( (p->pWhere && ExprHasProperty(p->pWhere,EP_Subquery))
+     || sqlite3AnyExprListHasProperty(p->pEList,EP_Subquery)
+     || sqlite3AnyExprListHasProperty(p->pOrderBy,EP_Subquery)
+    ){
+      return 0;                                          /* Restriction (2b)  */
+    }
+  }
+    
   pSubSrc = pSub->pSrc;
   assert( pSubSrc );
   /* Prior to version 3.1.2, when LIMIT and OFFSET had to be simple constants,
diff --git a/src/sqliteInt.h b/src/sqliteInt.h
index f5892db4b..e07ee4a5b 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -2046,6 +2046,12 @@ struct Expr {
 #define EP_Unlikely  0x040000 /* unlikely() or likelihood() function */
 #define EP_ConstFunc 0x080000 /* Node is a SQLITE_FUNC_CONSTANT function */
 #define EP_CanBeNull 0x100000 /* Can be null despite NOT NULL constraint */
+#define EP_Subquery  0x200000 /* Tree contains a TK_SELECT operator */
+
+/*
+** Combinations of two or more EP_* flags
+*/
+#define EP_Propagate (EP_Collate|EP_Subquery) /* Propagate these bits up tree */
 
 /*
 ** These macros can be used to test, set, or clear bits in the 
@@ -3153,6 +3159,7 @@ ExprList *sqlite3ExprListAppend(Parse*,ExprList*,Expr*);
 void sqlite3ExprListSetName(Parse*,ExprList*,Token*,int);
 void sqlite3ExprListSetSpan(Parse*,ExprList*,ExprSpan*);
 void sqlite3ExprListDelete(sqlite3*, ExprList*);
+int sqlite3AnyExprListHasProperty(const ExprList*,u32);
 int sqlite3Init(sqlite3*, char**);
 int sqlite3InitCallback(void*, int, char**, char**);
 void sqlite3Pragma(Parse*,Token*,Token*,Token*,int);