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,29 @@ 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; + /* The Expr.x union is never used at the same time as Expr.pRight */ + assert( p->x.pList==0 || p->pRight==0 ); + /* p->flags holds EP_Collate and p->pLeft->flags does not. And + ** p->x.pSelect cannot. So if p->x.pLeft exists, it must hold at + ** least one EP_Collate. Thus the following two ALWAYS. */ + if( p->x.pList!=0 && ALWAYS(!ExprHasProperty(p, EP_xIsSelect)) ){ + int i; + for(i=0; ALWAYS(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 +368,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 +407,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 +519,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 +623,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 +1237,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 @@ -1247,11 +1293,11 @@ switch( pExpr->op ){ /* Consider functions to be constant if all their arguments are constant ** and either pWalker->eCode==4 or 5 or the function has the ** SQLITE_FUNC_CONST flag. */ case TK_FUNCTION: - if( pWalker->eCode>=4 || ExprHasProperty(pExpr,EP_Constant) ){ + if( pWalker->eCode>=4 || ExprHasProperty(pExpr,EP_ConstFunc) ){ return WRC_Continue; }else{ pWalker->eCode = 0; return WRC_Abort; } 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/resolve.c ================================================================== --- src/resolve.c +++ src/resolve.c @@ -245,13 +245,14 @@ */ if( zDb ){ testcase( pNC->ncFlags & NC_PartIdx ); testcase( pNC->ncFlags & NC_IsCheck ); if( (pNC->ncFlags & (NC_PartIdx|NC_IsCheck))!=0 ){ - /* Silently ignore database qualifiers inside CHECK constraints and partial - ** indices. Do not raise errors because that might break legacy and - ** because it does not hurt anything to just ignore the database name. */ + /* Silently ignore database qualifiers inside CHECK constraints and + ** partial indices. Do not raise errors because that might break + ** legacy and because it does not hurt anything to just ignore the + ** database name. */ zDb = 0; }else{ for(i=0; inDb; i++){ assert( db->aDb[i].zName ); if( sqlite3StrICmp(db->aDb[i].zName,zDb)==0 ){ @@ -318,11 +319,12 @@ } } if( pMatch ){ pExpr->iTable = pMatch->iCursor; pExpr->pTab = pMatch->pTab; - assert( (pMatch->jointype & JT_RIGHT)==0 ); /* RIGHT JOIN not (yet) supported */ + /* RIGHT JOIN not (yet) supported */ + assert( (pMatch->jointype & JT_RIGHT)==0 ); if( (pMatch->jointype & JT_LEFT)!=0 ){ ExprSetProperty(pExpr, EP_CanBeNull); } pSchema = pExpr->pTab->pSchema; } @@ -639,11 +641,12 @@ pExpr->iTable = pItem->iCursor; pExpr->iColumn = -1; pExpr->affinity = SQLITE_AFF_INTEGER; break; } -#endif /* defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY) */ +#endif /* defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) + && !defined(SQLITE_OMIT_SUBQUERY) */ /* A lone identifier is the name of a column. */ case TK_ID: { return lookupName(pParse, 0, 0, pExpr->u.zToken, pNC, pExpr); @@ -704,23 +707,24 @@ if( pDef->funcFlags & SQLITE_FUNC_UNLIKELY ){ ExprSetProperty(pExpr, EP_Unlikely|EP_Skip); if( n==2 ){ pExpr->iTable = exprProbability(pList->a[1].pExpr); if( pExpr->iTable<0 ){ - sqlite3ErrorMsg(pParse, "second argument to likelihood() must be a " - "constant between 0.0 and 1.0"); + sqlite3ErrorMsg(pParse, + "second argument to likelihood() must be a " + "constant between 0.0 and 1.0"); pNC->nErr++; } }else{ - /* EVIDENCE-OF: R-61304-29449 The unlikely(X) function is equivalent to - ** likelihood(X, 0.0625). - ** EVIDENCE-OF: R-01283-11636 The unlikely(X) function is short-hand for - ** likelihood(X,0.0625). - ** EVIDENCE-OF: R-36850-34127 The likely(X) function is short-hand for - ** likelihood(X,0.9375). - ** EVIDENCE-OF: R-53436-40973 The likely(X) function is equivalent to - ** likelihood(X,0.9375). */ + /* EVIDENCE-OF: R-61304-29449 The unlikely(X) function is + ** equivalent to likelihood(X, 0.0625). + ** EVIDENCE-OF: R-01283-11636 The unlikely(X) function is + ** short-hand for likelihood(X,0.0625). + ** EVIDENCE-OF: R-36850-34127 The likely(X) function is short-hand + ** for likelihood(X,0.9375). + ** EVIDENCE-OF: R-53436-40973 The likely(X) function is equivalent + ** to likelihood(X,0.9375). */ /* TUNING: unlikely() probability is 0.0625. likely() is 0.9375 */ pExpr->iTable = pDef->zName[0]=='u' ? 8388608 : 125829120; } } #ifndef SQLITE_OMIT_AUTHORIZATION @@ -733,11 +737,13 @@ } pExpr->op = TK_NULL; return WRC_Prune; } #endif - if( pDef->funcFlags & SQLITE_FUNC_CONSTANT ) ExprSetProperty(pExpr,EP_Constant); + if( pDef->funcFlags & SQLITE_FUNC_CONSTANT ){ + ExprSetProperty(pExpr,EP_ConstFunc); + } } if( is_agg && (pNC->ncFlags & NC_AllowAgg)==0 ){ sqlite3ErrorMsg(pParse, "misuse of aggregate function %.*s()", nId,zId); pNC->nErr++; is_agg = 0; @@ -1044,11 +1050,12 @@ if( pItem->u.x.iOrderByCol ){ if( pItem->u.x.iOrderByCol>pEList->nExpr ){ resolveOutOfRangeError(pParse, zType, i+1, pEList->nExpr); return 1; } - resolveAlias(pParse, pEList, pItem->u.x.iOrderByCol-1, pItem->pExpr, zType,0); + resolveAlias(pParse, pEList, pItem->u.x.iOrderByCol-1, pItem->pExpr, + zType,0); } } return 0; } 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 @@ -4750,10 +4762,17 @@ if( pParse->nErr || db->mallocFailed ){ goto select_end; } isAgg = (p->selFlags & SF_Aggregate)!=0; assert( pEList!=0 ); +#if SELECTTRACE_ENABLED + if( sqlite3SelectTrace & 0x100 ){ + SELECTTRACE(0x100,pParse,p, ("after name resolution:\n")); + sqlite3TreeViewSelect(0, p, 0); + } +#endif + /* Begin generating code. */ v = sqlite3GetVdbe(pParse); if( v==0 ) goto select_end; @@ -5498,13 +5517,13 @@ ** Generate a human-readable description of a the Select object. */ void sqlite3TreeViewSelect(TreeView *pView, const Select *p, u8 moreToFollow){ int n = 0; pView = sqlite3TreeViewPush(pView, moreToFollow); - sqlite3TreeViewLine(pView, "SELECT%s%s", + sqlite3TreeViewLine(pView, "SELECT%s%s (0x%p)", ((p->selFlags & SF_Distinct) ? " DISTINCT" : ""), - ((p->selFlags & SF_Aggregate) ? " agg_flag" : "") + ((p->selFlags & SF_Aggregate) ? " agg_flag" : ""), p ); if( p->pSrc && p->pSrc->nSrc ) n++; if( p->pWhere ) n++; if( p->pGroupBy ) n++; if( p->pHaving ) n++; Index: src/shell.c ================================================================== --- src/shell.c +++ src/shell.c @@ -3019,11 +3019,11 @@ sqlite3_bind_text(pStmt, i+1, z, -1, SQLITE_TRANSIENT); if( i0 - 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: src/vdbeInt.h ================================================================== --- src/vdbeInt.h +++ src/vdbeInt.h @@ -342,10 +342,13 @@ ynVar nVar; /* Number of entries in aVar[] */ ynVar nzVar; /* Number of entries in azVar[] */ u32 cacheCtr; /* VdbeCursor row cache generation counter */ int pc; /* The program counter */ int rc; /* Value to return */ +#ifdef SQLITE_DEBUG + int rcApp; /* errcode set by sqlite3_result_error_code() */ +#endif u16 nResColumn; /* Number of columns in one row of the result set */ u8 errorAction; /* Recovery action to do in case of an error */ u8 minWriteFileFormat; /* Minimum file format for writable database files */ bft explain:2; /* True if EXPLAIN present on SQL command */ bft inVtabMethod:2; /* See comments above */ Index: src/vdbeapi.c ================================================================== --- src/vdbeapi.c +++ src/vdbeapi.c @@ -379,10 +379,13 @@ sqlite3VdbeMemSetZeroBlob(pCtx->pOut, n); } void sqlite3_result_error_code(sqlite3_context *pCtx, int errCode){ pCtx->isError = errCode; pCtx->fErrorOrAux = 1; +#ifdef SQLITE_DEBUG + pCtx->pVdbe->rcApp = errCode; +#endif if( pCtx->pOut->flags & MEM_Null ){ sqlite3VdbeMemSetStr(pCtx->pOut, sqlite3ErrStr(errCode), -1, SQLITE_UTF8, SQLITE_STATIC); } } @@ -459,11 +462,11 @@ ** legacy behavior of returning SQLITE_MISUSE for cases where the ** previous sqlite3_step() returned something other than a SQLITE_LOCKED ** or SQLITE_BUSY error. */ #ifdef SQLITE_OMIT_AUTORESET - if( p->rc==SQLITE_BUSY || p->rc==SQLITE_LOCKED ){ + if( (rc = p->rc&0xff)==SQLITE_BUSY || rc==SQLITE_LOCKED ){ sqlite3_reset((sqlite3_stmt*)p); }else{ return SQLITE_MISUSE_BKPT; } #else @@ -505,10 +508,13 @@ db->nVdbeActive++; if( p->readOnly==0 ) db->nVdbeWrite++; if( p->bIsReader ) db->nVdbeRead++; p->pc = 0; } +#ifdef SQLITE_DEBUG + p->rcApp = SQLITE_OK; +#endif #ifndef SQLITE_OMIT_EXPLAIN if( p->explain ){ rc = sqlite3VdbeList(p); }else #endif /* SQLITE_OMIT_EXPLAIN */ @@ -549,11 +555,11 @@ ** were called on statement p. */ assert( rc==SQLITE_ROW || rc==SQLITE_DONE || rc==SQLITE_ERROR || rc==SQLITE_BUSY || rc==SQLITE_MISUSE ); - assert( p->rc!=SQLITE_ROW && p->rc!=SQLITE_DONE ); + assert( (p->rc!=SQLITE_ROW && p->rc!=SQLITE_DONE) || p->rc==p->rcApp ); if( p->isPrepareV2 && rc!=SQLITE_ROW && rc!=SQLITE_DONE ){ /* If this statement was prepared using sqlite3_prepare_v2(), and an ** error has occurred, then return the error code in p->rc to the ** caller. Set the error code in the database handle to the same value. */ 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 Index: test/shell5.test ================================================================== --- test/shell5.test +++ test/shell5.test @@ -403,6 +403,36 @@ # Handle platform end-of-line differences regsub -all {[\n\r]?\n} $x x set x } "0 \{\"test 1\"\x1F,test 2\x1Etest 3\x1Ftest 4\x1E\}" +do_test shell5-4.1 { + forcedelete shell5.csv + set fd [open shell5.csv w] + puts $fd "1,2,3" + puts $fd "4,5" + puts $fd "6,7,8" + close $fd + catchcmd test.db [string trim { +.mode csv +CREATE TABLE t6(a, b, c); +.import shell5.csv t6 + }] + db eval { SELECT * FROM t6 ORDER BY a } +} {1 2 3 4 5 {} 6 7 8} + +do_test shell5-4.2 { + forcedelete shell5.csv + set fd [open shell5.csv w] + puts $fd "1,2,3" + puts $fd "4,5" + puts $fd "6,7,8,9" + close $fd + catchcmd test.db [string trim { +.mode csv +CREATE TABLE t7(a, b, c); +.import shell5.csv t7 + }] + db eval { SELECT * FROM t7 ORDER BY a } +} {1 2 3 4 5 {} 6 7 8} + finish_test Index: tool/spaceanal.tcl ================================================================== --- tool/spaceanal.tcl +++ tool/spaceanal.tcl @@ -2,10 +2,28 @@ # how much disk space is used by a particular data to actually store data # versus how much space is unused. # if {[catch { + +# Argument $tname is the name of a table within the database opened by +# database handle [db]. Return true if it is a WITHOUT ROWID table, or +# false otherwise. +# +proc is_without_rowid {tname} { + set t [string map {' ''} $tname] + db eval "PRAGMA index_list = '$t'" o { + if {$o(origin) == "pk"} { + set n $o(name) + if {0==[db one { SELECT count(*) FROM sqlite_master WHERE name=$n }]} { + return 1 + } + } + } + return 0 +} + # Get the name of the database to analyze # proc usage {} { set argv0 [file rootname [file tail [info nameofexecutable]]] puts stderr "Usage: $argv0 database-name" @@ -165,24 +183,25 @@ set compressOverhead 0 set sql { SELECT name, tbl_name FROM sqlite_master WHERE rootpage>0 } foreach {name tblname} [concat sqlite_master sqlite_master [db eval $sql]] { set is_index [expr {$name!=$tblname}] + set idx_btree [expr {$is_index || [is_without_rowid $name]}] db eval { SELECT sum(ncell) AS nentry, - sum(isleaf(pagetype, $is_index) * ncell) AS leaf_entries, + sum(isleaf(pagetype, $idx_btree) * ncell) AS leaf_entries, sum(payload) AS payload, - sum(isoverflow(pagetype, $is_index) * payload) AS ovfl_payload, + sum(isoverflow(pagetype, $idx_btree) * payload) AS ovfl_payload, sum(path LIKE '%+000000') AS ovfl_cnt, max(mx_payload) AS mx_payload, - sum(isinternal(pagetype, $is_index)) AS int_pages, - sum(isleaf(pagetype, $is_index)) AS leaf_pages, - sum(isoverflow(pagetype, $is_index)) AS ovfl_pages, - sum(isinternal(pagetype, $is_index) * unused) AS int_unused, - sum(isleaf(pagetype, $is_index) * unused) AS leaf_unused, - sum(isoverflow(pagetype, $is_index) * unused) AS ovfl_unused, + sum(isinternal(pagetype, $idx_btree)) AS int_pages, + sum(isleaf(pagetype, $idx_btree)) AS leaf_pages, + sum(isoverflow(pagetype, $idx_btree)) AS ovfl_pages, + sum(isinternal(pagetype, $idx_btree) * unused) AS int_unused, + sum(isleaf(pagetype, $idx_btree) * unused) AS leaf_unused, + sum(isoverflow(pagetype, $idx_btree) * unused) AS ovfl_unused, sum(pgsize) AS compressed_size FROM temp.dbstat WHERE name = $name } break set total_pages [expr {$leaf_pages+$int_pages+$ovfl_pages}]