Index: src/delete.c ================================================================== --- src/delete.c +++ src/delete.c @@ -187,11 +187,11 @@ pInClause = sqlite3PExpr(pParse, TK_IN, pWhereRowid, 0, 0); if( pInClause == 0 ) goto limit_where_cleanup_1; pInClause->x.pSelect = pSelect; pInClause->flags |= EP_xIsSelect; - sqlite3ExprSetHeight(pParse, pInClause); + sqlite3ExprSetHeightAndFlags(pParse, pInClause); return pInClause; /* something went wrong. clean up anything allocated. */ limit_where_cleanup_1: sqlite3SelectDelete(pParse->db, pSelect); Index: src/expr.c ================================================================== --- src/expr.c +++ src/expr.c @@ -144,14 +144,24 @@ pColl = sqlite3FindCollSeq(db, ENC(db), zColl, 0); } break; } if( p->flags & EP_Collate ){ - if( ALWAYS(p->pLeft) && (p->pLeft->flags & EP_Collate)!=0 ){ + if( p->pLeft && (p->pLeft->flags & EP_Collate)!=0 ){ p = p->pLeft; }else{ - p = p->pRight; + Expr *pNext = p->pRight; + if( p->x.pList!=0 && !ExprHasProperty(p, EP_xIsSelect) ){ + int i; + for(i=0; ix.pList->nExpr; i++){ + if( ExprHasProperty(p->x.pList->a[i].pExpr, EP_Collate) ){ + pNext = p->x.pList->a[i].pExpr; + break; + } + } + } + p = pNext; } }else{ break; } } @@ -353,29 +363,36 @@ ** Set the Expr.nHeight variable in the structure passed as an ** argument. An expression with no children, Expr.pList or ** Expr.pSelect member has a height of 1. Any other expression ** has a height equal to the maximum height of any other ** referenced Expr plus one. +** +** Also propagate EP_Propagate flags up from Expr.x.pList to Expr.flags, +** if appropriate. */ static void exprSetHeight(Expr *p){ int nHeight = 0; heightOfExpr(p->pLeft, &nHeight); heightOfExpr(p->pRight, &nHeight); if( ExprHasProperty(p, EP_xIsSelect) ){ heightOfSelect(p->x.pSelect, &nHeight); - }else{ + }else if( p->x.pList ){ heightOfExprList(p->x.pList, &nHeight); + p->flags |= EP_Propagate & sqlite3ExprListFlags(p->x.pList); } p->nHeight = nHeight + 1; } /* ** Set the Expr.nHeight variable using the exprSetHeight() function. If ** the height is greater than the maximum allowed expression depth, ** leave an error in pParse. +** +** Also propagate all EP_Propagate flags from the Expr.x.pList into +** Expr.flags. */ -void sqlite3ExprSetHeight(Parse *pParse, Expr *p){ +void sqlite3ExprSetHeightAndFlags(Parse *pParse, Expr *p){ exprSetHeight(p); sqlite3ExprCheckHeight(pParse, p->nHeight); } /* @@ -385,12 +402,21 @@ int sqlite3SelectExprHeight(Select *p){ int nHeight = 0; heightOfSelect(p, &nHeight); return nHeight; } -#else - #define exprSetHeight(y) +#else /* ABOVE: Height enforcement enabled. BELOW: Height enforcement off */ +/* +** Propagate all EP_Propagate flags from the Expr.x.pList into +** Expr.flags. +*/ +void sqlite3ExprSetHeightAndFlags(Parse *pParse, Expr *p){ + if( p && p->x.pList && !ExprHasProperty(p, EP_xIsSelect) ){ + p->flags |= EP_Propagate & sqlite3ExprListFlags(p->x.pList); + } +} +#define exprSetHeight(y) #endif /* SQLITE_MAX_EXPR_DEPTH>0 */ /* ** This routine is the core allocator for Expr nodes. ** @@ -488,15 +514,15 @@ sqlite3ExprDelete(db, pLeft); sqlite3ExprDelete(db, pRight); }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); } } @@ -592,11 +618,11 @@ sqlite3ExprListDelete(db, pList); /* Avoid memory leak when malloc fails */ return 0; } pNew->x.pList = pList; assert( !ExprHasProperty(pNew, EP_xIsSelect) ); - sqlite3ExprSetHeight(pParse, pNew); + sqlite3ExprSetHeightAndFlags(pParse, pNew); return pNew; } /* ** Assign a variable number to an expression that encodes a wildcard @@ -1206,10 +1232,25 @@ sqlite3DbFree(db, pItem->zSpan); } sqlite3DbFree(db, pList->a); sqlite3DbFree(db, pList); } + +/* +** Return the bitwise-OR of all Expr.flags fields in the given +** ExprList. +*/ +u32 sqlite3ExprListFlags(const ExprList *pList){ + int i; + u32 m = 0; + if( pList ){ + for(i=0; inExpr; i++){ + m |= pList->a[i].pExpr->flags; + } + } + return m; +} /* ** 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 Index: src/parse.y ================================================================== --- src/parse.y +++ src/parse.y @@ -1076,11 +1076,11 @@ A.pExpr = sqlite3PExpr(pParse, N ? TK_NE : TK_EQ, X.pExpr, pRHS, 0); }else{ A.pExpr = sqlite3PExpr(pParse, TK_IN, X.pExpr, 0, 0); if( A.pExpr ){ A.pExpr->x.pList = Y; - sqlite3ExprSetHeight(pParse, A.pExpr); + sqlite3ExprSetHeightAndFlags(pParse, A.pExpr); }else{ sqlite3ExprListDelete(pParse->db, Y); } if( N ) A.pExpr = sqlite3PExpr(pParse, TK_NOT, A.pExpr, 0, 0); } @@ -1089,12 +1089,12 @@ } expr(A) ::= LP(B) select(X) RP(E). { A.pExpr = sqlite3PExpr(pParse, TK_SELECT, 0, 0, 0); if( A.pExpr ){ A.pExpr->x.pSelect = X; - ExprSetProperty(A.pExpr, EP_xIsSelect); - sqlite3ExprSetHeight(pParse, A.pExpr); + ExprSetProperty(A.pExpr, EP_xIsSelect|EP_Subquery); + sqlite3ExprSetHeightAndFlags(pParse, A.pExpr); }else{ sqlite3SelectDelete(pParse->db, X); } A.zStart = B.z; A.zEnd = &E.z[E.n]; @@ -1101,12 +1101,12 @@ } expr(A) ::= expr(X) in_op(N) LP select(Y) RP(E). [IN] { A.pExpr = sqlite3PExpr(pParse, TK_IN, X.pExpr, 0, 0); if( A.pExpr ){ A.pExpr->x.pSelect = Y; - ExprSetProperty(A.pExpr, EP_xIsSelect); - sqlite3ExprSetHeight(pParse, A.pExpr); + ExprSetProperty(A.pExpr, EP_xIsSelect|EP_Subquery); + sqlite3ExprSetHeightAndFlags(pParse, A.pExpr); }else{ sqlite3SelectDelete(pParse->db, Y); } if( N ) A.pExpr = sqlite3PExpr(pParse, TK_NOT, A.pExpr, 0, 0); A.zStart = X.zStart; @@ -1115,12 +1115,12 @@ expr(A) ::= expr(X) in_op(N) nm(Y) dbnm(Z). [IN] { SrcList *pSrc = sqlite3SrcListAppend(pParse->db, 0,&Y,&Z); 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); - sqlite3ExprSetHeight(pParse, A.pExpr); + ExprSetProperty(A.pExpr, EP_xIsSelect|EP_Subquery); + sqlite3ExprSetHeightAndFlags(pParse, A.pExpr); }else{ sqlite3SrcListDelete(pParse->db, pSrc); } if( N ) A.pExpr = sqlite3PExpr(pParse, TK_NOT, A.pExpr, 0, 0); A.zStart = X.zStart; @@ -1128,12 +1128,12 @@ } expr(A) ::= EXISTS(B) LP select(Y) RP(E). { Expr *p = A.pExpr = sqlite3PExpr(pParse, TK_EXISTS, 0, 0, 0); if( p ){ p->x.pSelect = Y; - ExprSetProperty(p, EP_xIsSelect); - sqlite3ExprSetHeight(pParse, p); + ExprSetProperty(p, EP_xIsSelect|EP_Subquery); + sqlite3ExprSetHeightAndFlags(pParse, p); }else{ sqlite3SelectDelete(pParse->db, Y); } A.zStart = B.z; A.zEnd = &E.z[E.n]; @@ -1143,11 +1143,11 @@ /* CASE expressions */ expr(A) ::= CASE(C) case_operand(X) case_exprlist(Y) case_else(Z) END(E). { A.pExpr = sqlite3PExpr(pParse, TK_CASE, X, 0, 0); if( A.pExpr ){ A.pExpr->x.pList = Z ? sqlite3ExprListAppend(pParse,Y,Z) : Y; - sqlite3ExprSetHeight(pParse, A.pExpr); + sqlite3ExprSetHeightAndFlags(pParse, A.pExpr); }else{ sqlite3ExprListDelete(pParse->db, Y); sqlite3ExprDelete(pParse->db, Z); } A.zStart = C.z; Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -3192,11 +3192,14 @@ ** ** Flattening is only attempted if all of the following are true: ** ** (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) ** ** (4) The subquery is not DISTINCT. @@ -3329,12 +3332,21 @@ assert( pSrc && iFrom>=0 && iFromnSrc ); pSubitem = &pSrc->a[iFrom]; 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)) + || (sqlite3ExprListFlags(p->pEList) & EP_Subquery)!=0 + || (sqlite3ExprListFlags(p->pOrderBy) & EP_Subquery)!=0 + ){ + return 0; /* Restriction (2b) */ + } + } + pSubSrc = pSub->pSrc; assert( pSubSrc ); /* Prior to version 3.1.2, when LIMIT and OFFSET had to be simple constants, ** not arbitrary expressions, we allowed some combining of LIMIT and OFFSET ** because they could be computed at compile-time. But when LIMIT and OFFSET Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -2044,10 +2044,16 @@ #define EP_MemToken 0x010000 /* Need to sqlite3DbFree() Expr.zToken */ #define EP_NoReduce 0x020000 /* Cannot EXPRDUP_REDUCE this 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 ** Expr.flags field. */ @@ -3151,10 +3157,11 @@ void sqlite3ExprDelete(sqlite3*, Expr*); ExprList *sqlite3ExprListAppend(Parse*,ExprList*,Expr*); void sqlite3ExprListSetName(Parse*,ExprList*,Token*,int); void sqlite3ExprListSetSpan(Parse*,ExprList*,ExprSpan*); void sqlite3ExprListDelete(sqlite3*, ExprList*); +u32 sqlite3ExprListFlags(const ExprList*); int sqlite3Init(sqlite3*, char**); int sqlite3InitCallback(void*, int, char**, char**); void sqlite3Pragma(Parse*,Token*,Token*,Token*,int); void sqlite3ResetAllSchemasOfConnection(sqlite3*); void sqlite3ResetOneSchema(sqlite3*,int); @@ -3734,16 +3741,15 @@ void sqlite3MemJournalOpen(sqlite3_file *); int sqlite3MemJournalSize(void); int sqlite3IsMemJournal(sqlite3_file *); +void sqlite3ExprSetHeightAndFlags(Parse *pParse, Expr *p); #if SQLITE_MAX_EXPR_DEPTH>0 - void sqlite3ExprSetHeight(Parse *pParse, Expr *p); int sqlite3SelectExprHeight(Select *); int sqlite3ExprCheckHeight(Parse*, int); #else - #define sqlite3ExprSetHeight(x,y) #define sqlite3SelectExprHeight(x) 0 #define sqlite3ExprCheckHeight(x,y) #endif u32 sqlite3Get4byte(const u8*); Index: test/collate8.test ================================================================== --- test/collate8.test +++ test/collate8.test @@ -11,11 +11,13 @@ #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this script is making sure collations pass through the # unary + operator. # -# $Id: collate8.test,v 1.2 2008/08/25 12:14:09 drh Exp $ +# 2015-02-09: Added tests to make sure COLLATE passes through function +# calls. Ticket [ca0d20b6cdddec5e81b8d66f89c46a5583b5f6f6]. +# set testdir [file dirname $argv0] source $testdir/tester.tcl do_test collate8-1.1 { @@ -119,7 +121,37 @@ do_test collate8-2.8 { execsql { SELECT a COLLATE nocase AS x FROM t2 WHERE 'abc'=x COLLATE binary; } } {abc} + +# Make sure the COLLATE operator perculates up through function calls +# and other Expr structures that use the Expr.x.pList field. +# +do_execsql_test collate8-3.1 { + SELECT 'abc'==('ABC'||'') COLLATE nocase; + SELECT 'abc'==('ABC'||'' COLLATE nocase); + SELECT 'abc'==('ABC'||('' COLLATE nocase)); + SELECT 'abc'==('ABC'||upper('' COLLATE nocase)); +} {1 1 1 1} +do_execsql_test collate8-3.2 { + SELECT 'abc'==('ABC'||max('' COLLATE nocase,'' COLLATE binary)); +} {1} + +# The COLLATE binary is on the left and so takes precedence +do_execsql_test collate8-3.3 { + SELECT 'abc'==('ABC'||max('' COLLATE binary,'' COLLATE nocase)); +} {0} + +do_execsql_test collate8-3.4 { + SELECT 'abc'==('ABC'||CASE WHEN 1-1=2 THEN '' COLLATE nocase + ELSE '' COLLATE binary END); + SELECT 'abc'==('ABC'||CASE WHEN 1+1=2 THEN '' COLLATE nocase + ELSE '' COLLATE binary END); +} {1 1} +do_execsql_test collate8-3.5 { + SELECT 'abc'==('ABC'||CASE WHEN 1=2 THEN '' COLLATE binary + ELSE '' COLLATE nocase END); +} {0} + finish_test Index: test/select6.test ================================================================== --- test/select6.test +++ test/select6.test @@ -554,8 +554,64 @@ SELECT * FROM k UNION ALL SELECT * FROM t UNION ALL SELECT l,m,l FROM j ) } $err + +# 2015-02-09 Ticket [2f7170d73bf9abf80339187aa3677dce3dbcd5ca] +# "misuse of aggregate" error if aggregate column from FROM +# subquery is used in correlated subquery +# +do_execsql_test 11.1 { + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(w INT, x INT); + INSERT INTO t1(w,x) + VALUES(1,10),(2,20),(3,30), + (2,21),(3,31), + (3,32); + CREATE INDEX t1wx ON t1(w,x); + + DROP TABLE IF EXISTS t2; + CREATE TABLE t2(w INT, y VARCHAR(8)); + INSERT INTO t2(w,y) VALUES(1,'one'),(2,'two'),(3,'three'),(4,'four'); + CREATE INDEX t2wy ON t2(w,y); + + SELECT cnt, xyz, (SELECT y FROM t2 WHERE w=cnt), '|' + FROM (SELECT count(*) AS cnt, w AS xyz FROM t1 GROUP BY 2) + ORDER BY cnt, xyz; +} {1 1 one | 2 2 two | 3 3 three |} +do_execsql_test 11.2 { + SELECT cnt, xyz, lower((SELECT y FROM t2 WHERE w=cnt)), '|' + FROM (SELECT count(*) AS cnt, w AS xyz FROM t1 GROUP BY 2) + ORDER BY cnt, xyz; +} {1 1 one | 2 2 two | 3 3 three |} +do_execsql_test 11.3 { + SELECT cnt, xyz, '|' + FROM (SELECT count(*) AS cnt, w AS xyz FROM t1 GROUP BY 2) + WHERE (SELECT y FROM t2 WHERE w=cnt)!='two' + ORDER BY cnt, xyz; +} {1 1 | 3 3 |} +do_execsql_test 11.4 { + SELECT cnt, xyz, '|' + FROM (SELECT count(*) AS cnt, w AS xyz FROM t1 GROUP BY 2) + ORDER BY lower((SELECT y FROM t2 WHERE w=cnt)); +} {1 1 | 3 3 | 2 2 |} +do_execsql_test 11.5 { + SELECT cnt, xyz, + CASE WHEN (SELECT y FROM t2 WHERE w=cnt)=='two' + THEN 'aaa' ELSE 'bbb' + END, '|' + FROM (SELECT count(*) AS cnt, w AS xyz FROM t1 GROUP BY 2) + ORDER BY +cnt; +} {1 1 bbb | 2 2 aaa | 3 3 bbb |} + +do_execsql_test 11.100 { + DROP TABLE t1; + DROP TABLE t2; + CREATE TABLE t1(x); + CREATE TABLE t2(y, z); + SELECT ( SELECT y FROM t2 WHERE z = cnt ) + FROM ( SELECT count(*) AS cnt FROM t1 ); +} {{}} finish_test