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
diff --git a/src/expr.c b/src/expr.c
index 2f0fe4a..2def950 100644
--- a/src/expr.c
+++ b/src/expr.c
@@ -490,11 +490,11 @@
}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 @@
}
/*
+** 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 544888a..6730312 100644
--- a/src/parse.y
+++ b/src/parse.y
@@ -1091,7 +1091,7 @@
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 @@
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 @@
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 *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 058bf3d..2c4c9e4 100644
--- a/src/select.c
+++ b/src/select.c
@@ -3194,7 +3194,10 @@
**
** (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 @@
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 f5892db..e07ee4a 100644
--- a/src/sqliteInt.h
+++ b/src/sqliteInt.h
@@ -2046,6 +2046,12 @@
#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 @@
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);