Index: src/build.c ================================================================== --- src/build.c +++ src/build.c @@ -210,11 +210,14 @@ /* Code constant expressions that where factored out of inner loops */ if( pParse->pConstExpr ){ ExprList *pEL = pParse->pConstExpr; pParse->okConstFactor = 0; for(i=0; inExpr; i++){ - sqlite3ExprCode(pParse, pEL->a[i].pExpr, pEL->a[i].u.iConstExprReg); + int iReg = pEL->a[i].u.iConstExprReg; + if( iReg>0 ){ + sqlite3ExprCode(pParse, pEL->a[i].pExpr, iReg); + } } } /* Finally, jump back to the beginning of the executable code. */ sqlite3VdbeGoto(v, 1); Index: src/expr.c ================================================================== --- src/expr.c +++ src/expr.c @@ -3809,10 +3809,11 @@ switch( op ){ case TK_AGG_COLUMN: { AggInfo *pAggInfo = pExpr->pAggInfo; struct AggInfo_col *pCol; assert( pAggInfo!=0 ); + assert( AggInfoValid(pAggInfo) ); assert( pExpr->iAgg>=0 && pExpr->iAggnColumn ); pCol = &pAggInfo->aCol[pExpr->iAgg]; if( !pAggInfo->directMode ){ assert( pCol->iMem>0 ); return pCol->iMem; @@ -4117,10 +4118,11 @@ || NEVER(pExpr->iAgg>=pInfo->nFunc) ){ assert( !ExprHasProperty(pExpr, EP_IntValue) ); sqlite3ErrorMsg(pParse, "misuse of aggregate: %s()", pExpr->u.zToken); }else{ + assert( AggInfoValid(pInfo) ); return pInfo->aFunc[pExpr->iAgg].iMem; } break; } case TK_FUNCTION: { @@ -5655,17 +5657,11 @@ /* ** Count the number of references to columns. */ static int exprSrcCount(Walker *pWalker, Expr *pExpr){ - /* There was once a NEVER() on the second term on the grounds that - ** sqlite3FunctionUsesThisSrc() was always called before - ** sqlite3ExprAnalyzeAggregates() and so the TK_COLUMNs have not yet - ** been converted into TK_AGG_COLUMN. But this is no longer true due - ** to window functions - sqlite3WindowRewrite() may now indirectly call - ** FunctionUsesThisSrc() when creating a new sub-select. */ - if( pExpr->op==TK_COLUMN || pExpr->op==TK_AGG_COLUMN ){ + if( pExpr->op==TK_COLUMN || NEVER(pExpr->op==TK_AGG_COLUMN) ){ int i; struct SrcCount *p = pWalker->u.pSrcCount; SrcList *pSrc = p->pSrc; int nSrc = pSrc ? pSrc->nSrc : 0; for(i=0; ifuncFlags & SQLITE_FUNC_MINMAX)!=0 ); pNC2->ncFlags |= NC_HasAgg | (pDef->funcFlags & SQLITE_FUNC_MINMAX); - } } pNC->ncFlags |= savedAllowFlags; } /* FIX ME: Compute pExpr->affinity based on the expected return @@ -1181,11 +1180,10 @@ int i; /* Loop counter */ ExprList *pEList; /* The columns of the result set */ NameContext nc; /* Name context for resolving pE */ sqlite3 *db; /* Database connection */ int rc; /* Return code from subprocedures */ - u8 savedSuppErr; /* Saved value of db->suppressErr */ assert( sqlite3ExprIsInteger(pE, &i)==0 ); pEList = pSelect->pEList; /* Resolve all names in the ORDER BY term expression @@ -1195,14 +1193,22 @@ nc.pSrcList = pSelect->pSrc; nc.uNC.pEList = pEList; nc.ncFlags = NC_AllowAgg|NC_UEList; nc.nErr = 0; db = pParse->db; - savedSuppErr = db->suppressErr; - if( IN_RENAME_OBJECT==0 ) db->suppressErr = 1; + assert( pParse->nErr==0 ); rc = sqlite3ResolveExprNames(&nc, pE); - db->suppressErr = savedSuppErr; + + /* Discard any error. The only error that can occur in the above call + ** that we care about is SQLITE_NOMEM, the occurence of which is recorded in + ** db->mallocFailed. */ + assert( rc!=SQLITE_NOMEM || db->mallocFailed ); + if( IN_RENAME_OBJECT==0 ){ + sqlite3DbFree(db, pParse->zErrMsg); + pParse->zErrMsg = 0; + pParse->nErr = 0; + } if( rc ) return 0; /* Try to match the ORDER BY expression against an expression ** in the result set. Return an 1-based index of the matching ** result-set entry. @@ -1305,11 +1311,11 @@ if( IN_RENAME_OBJECT ){ pDup = pE; }else{ pDup = sqlite3ExprDup(db, pE, 0); } - if( !db->mallocFailed ){ + if( !db->mallocFailed && !pParse->nErr ){ assert(pDup); iCol = resolveOrderByTermToExprList(pParse, pSelect, pDup); } if( !IN_RENAME_OBJECT ){ sqlite3ExprDelete(db, pDup); @@ -1713,10 +1719,26 @@ || sqlite3ResolveExprListNames(&sNC, pWin->pPartition) ){ return WRC_Abort; } } + }else if( p->pWin && ALWAYS( (p->selFlags & SF_WinRewrite)==0 ) ){ +#if SELECTTRACE_ENABLED + if( (sqlite3SelectTrace & 0x108)!=0 ){ + SELECTTRACE(0x104,pParse,p, + ("(%u/%p) before window rewrite:\n",p->selId,p)); + sqlite3TreeViewSelect(0, p, 0); + } +#endif + sqlite3WindowRewrite(pParse, p); +#if SELECTTRACE_ENABLED + if( (sqlite3SelectTrace & 0x108)!=0 ){ + SELECTTRACE(0x104,pParse,p, + ("(%u/%p) after window rewrite:\n",p->selId,p)); + sqlite3TreeViewSelect(0, p, 0); + } +#endif } #endif /* If this is part of a compound SELECT, check that it has the right ** number of expressions in the select list. */ Index: src/select.c ================================================================== --- src/select.c +++ src/select.c @@ -11,24 +11,10 @@ ************************************************************************* ** This file contains C code routines that are called by the parser ** to handle SELECT statements in SQLite. */ #include "sqliteInt.h" - -/* -** Trace output macros -*/ -#if SELECTTRACE_ENABLED -/***/ int sqlite3SelectTrace = 0; -# define SELECTTRACE(K,P,S,X) \ - if(sqlite3SelectTrace&(K)) \ - sqlite3DebugPrintf("%u/%d/%p: ",(S)->selId,(P)->addrExplain,(S)),\ - sqlite3DebugPrintf X -#else -# define SELECTTRACE(K,P,S,X) -#endif - /* ** An instance of the following object is used to record information about ** how to process the DISTINCT keyword, to simplify passing that information ** into the selectInnerLoop() routine. @@ -179,17 +165,45 @@ assert( pNew->pSrc!=0 || pParse->nErr>0 ); } return pAllocated; } - /* ** Delete the given Select structure and all of its substructures. */ void sqlite3SelectDelete(sqlite3 *db, Select *p){ if( OK_IF_ALWAYS_TRUE(p) ) clearSelect(db, p, 1); } + +/* +** Make arrangements to delete a subquery when the Parse object is +** destroyed. +** +** This works by adding the subquery to the Parse.pConstExpr list, but +** with a register number of zero. The pConstExpr list is destroyed +** when the Parse object is destroyed. And the zero register means +** that the expression is not coded. +*/ +void sqlite3SelectDeferredDelete(Parse *pParse, Select *p){ + Expr *pNew = sqlite3PExpr(pParse, TK_SELECT, 0, 0); + if( pNew==0 ){ + clearSelect(pParse->db, p, 1); + }else{ + sqlite3PExprAddSelect(pParse, pNew, p); + pParse->pConstExpr = + sqlite3ExprListAppend(pParse, pParse->pConstExpr, pNew); + testcase( pParse->pConstExpr==0 ); /* Only true following OOM */ +#ifdef SQLITE_DEBUG + if( pParse->pConstExpr ){ + ExprList *p = pParse->pConstExpr; + struct ExprList_item *pItem = &p->a[p->nExpr-1]; + assert( pItem->reusable==0 ); + assert( pItem->u.iConstExprReg==0 ); + } +#endif + } +} /* ** Delete all the substructure for p, but keep p allocated. Redefine ** p to be a single SELECT where every column of the result set has a ** value of NULL. @@ -4163,11 +4177,11 @@ } /* Finially, delete what is left of the subquery and return ** success. */ - sqlite3SelectDelete(db, pSub1); + sqlite3SelectDeferredDelete(pParse, pSub1); #if SELECTTRACE_ENABLED if( sqlite3SelectTrace & 0x100 ){ SELECTTRACE(0x100,pParse,p,("After flattening:\n")); sqlite3TreeViewSelect(0, p, 0); @@ -4423,15 +4437,18 @@ int iCursor, /* Cursor number of the subquery */ int isLeftJoin /* True if pSubq is the right term of a LEFT JOIN */ ){ Expr *pNew; int nChng = 0; + Select *pSel; if( pWhere==0 ) return 0; if( pSubq->selFlags & SF_Recursive ) return 0; /* restriction (2) */ #ifndef SQLITE_OMIT_WINDOWFUNC - if( pSubq->pWin ) return 0; /* restriction (6) */ + for(pSel=pSubq; pSel; pSel=pSel->pPrior){ + if( pSel->pWin ) return 0; /* restriction (6) */ + } #endif #ifdef SQLITE_DEBUG /* Only the first term of a compound can have a WITH clause. But make ** sure no other terms are marked SF_Recursive in case something changes @@ -5345,11 +5362,10 @@ Select *p, /* The SELECT statement being coded. */ NameContext *pOuterNC /* Name context for container */ ){ assert( p!=0 || pParse->db->mallocFailed ); if( pParse->db->mallocFailed ) return; - if( p->selFlags & SF_HasTypeInfo ) return; sqlite3SelectExpand(pParse, p); if( pParse->nErr || pParse->db->mallocFailed ) return; sqlite3ResolveSelectNames(pParse, p, pOuterNC); if( pParse->nErr || pParse->db->mallocFailed ) return; sqlite3SelectAddTypeInfo(pParse, p); @@ -5763,10 +5779,13 @@ if( p==0 || db->mallocFailed || pParse->nErr ){ return 1; } if( sqlite3AuthCheck(pParse, SQLITE_SELECT, 0, 0, 0) ) return 1; memset(&sAggInfo, 0, sizeof(sAggInfo)); +#ifdef SQLITE_DEBUG + sAggInfo.iAggMagic = SQLITE_AGGMAGIC_VALID; +#endif #if SELECTTRACE_ENABLED SELECTTRACE(1,pParse,p, ("begin processing:\n", pParse->addrExplain)); if( sqlite3SelectTrace & 0x100 ){ sqlite3TreeViewSelect(0, p, 0); } @@ -5785,39 +5804,28 @@ ** DISTINCT so it can be removed too. */ sqlite3ExprListDelete(db, p->pOrderBy); p->pOrderBy = 0; p->selFlags &= ~SF_Distinct; } - sqlite3SelectPrep(pParse, p, 0); - if( pParse->nErr || db->mallocFailed ){ - goto select_end; - } - assert( p->pEList!=0 ); + if( (p->selFlags & SF_HasTypeInfo)==0 ){ + sqlite3SelectPrep(pParse, p, 0); + if( pParse->nErr || db->mallocFailed ){ + goto select_end; + } + assert( p->pEList!=0 ); #if SELECTTRACE_ENABLED - if( sqlite3SelectTrace & 0x104 ){ - SELECTTRACE(0x104,pParse,p, ("after name resolution:\n")); - sqlite3TreeViewSelect(0, p, 0); - } + if( sqlite3SelectTrace & 0x104 ){ + SELECTTRACE(0x104,pParse,p, ("after name resolution:\n")); + sqlite3TreeViewSelect(0, p, 0); + } #endif + } if( pDest->eDest==SRT_Output ){ generateColumnNames(pParse, p); } -#ifndef SQLITE_OMIT_WINDOWFUNC - rc = sqlite3WindowRewrite(pParse, p); - if( rc ){ - assert( db->mallocFailed || pParse->nErr>0 ); - goto select_end; - } -#if SELECTTRACE_ENABLED - if( p->pWin && (sqlite3SelectTrace & 0x108)!=0 ){ - SELECTTRACE(0x104,pParse,p, ("after window rewrite:\n")); - sqlite3TreeViewSelect(0, p, 0); - } -#endif -#endif /* SQLITE_OMIT_WINDOWFUNC */ pTabList = p->pSrc; isAgg = (p->selFlags & SF_Aggregate)!=0; memset(&sSort, 0, sizeof(sSort)); sSort.pOrderBy = p->pOrderBy; @@ -6141,11 +6149,11 @@ ** BY and DISTINCT, and an index or separate temp-table for the other. */ if( (p->selFlags & (SF_Distinct|SF_Aggregate))==SF_Distinct && sqlite3ExprListCompare(sSort.pOrderBy, pEList, -1)==0 #ifndef SQLITE_OMIT_WINDOWFUNC - && p->pWin==0 + && ALWAYS(p->pWin==0) #endif ){ p->selFlags &= ~SF_Distinct; pGroupBy = p->pGroupBy = sqlite3ExprListDup(db, pEList, 0); p->selFlags |= SF_Aggregate; @@ -6213,10 +6221,19 @@ sqlite3VdbeChangeP5(v, BTREE_UNORDERED); sDistinct.eTnctType = WHERE_DISTINCT_UNORDERED; }else{ sDistinct.eTnctType = WHERE_DISTINCT_NOOP; } + +#ifndef SQLITE_OMIT_WINDOWFUNC + /* By the time the Select object reaches this point, it will have been + ** transformed (mostly by sqlite3WindowRewrite()) so it cannot be both + ** a single-row aggregate and contain a window function. In other words, + ** if the Select has a window function then it may not have either an + ** aggregate function or a GROUP BY clause. */ + assert( (!isAgg && pGroupBy==0) || p->pWin==0 ); +#endif if( !isAgg && pGroupBy==0 ){ /* No aggregate functions and no GROUP BY clause */ u16 wctrlFlags = (sDistinct.isTnct ? WHERE_WANT_DISTINCT : 0) | (p->selFlags & SF_FixedLimit); @@ -6228,11 +6245,11 @@ #endif assert( WHERE_USE_LIMIT==SF_FixedLimit ); /* Begin the database scan. */ - SELECTTRACE(1,pParse,p,("WhereBegin\n")); + SELECTTRACE(1,pParse,p,("WhereBegin-A\n")); pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, sSort.pOrderBy, p->pEList, wctrlFlags, p->nSelectRow); if( pWInfo==0 ) goto select_end; if( sqlite3WhereOutputRowCount(pWInfo) < p->nSelectRow ){ p->nSelectRow = sqlite3WhereOutputRowCount(pWInfo); @@ -6459,11 +6476,11 @@ ** This might involve two separate loops with an OP_Sort in between, or ** it might be a single loop that uses an index to extract information ** in the right order to begin with. */ sqlite3VdbeAddOp2(v, OP_Gosub, regReset, addrReset); - SELECTTRACE(1,pParse,p,("WhereBegin\n")); + SELECTTRACE(1,pParse,p,("WhereBegin-B\n")); pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pGroupBy, 0, WHERE_GROUPBY | (orderByGrp ? WHERE_SORTBYGROUP : 0), 0 ); if( pWInfo==0 ) goto select_end; if( sqlite3WhereIsOrdered(pWInfo)==pGroupBy->nExpr ){ @@ -6734,11 +6751,11 @@ ** be an appropriate ORDER BY expression for the optimization. */ assert( minMaxFlag==WHERE_ORDERBY_NORMAL || pMinMaxOrderBy!=0 ); assert( pMinMaxOrderBy==0 || pMinMaxOrderBy->nExpr==1 ); - SELECTTRACE(1,pParse,p,("WhereBegin\n")); + SELECTTRACE(1,pParse,p,("WhereBegin-C\n")); pWInfo = sqlite3WhereBegin(pParse, pTabList, pWhere, pMinMaxOrderBy, 0, minMaxFlag, 0); if( pWInfo==0 ){ goto select_end; } @@ -6788,10 +6805,20 @@ ** successful coding of the SELECT. */ select_end: sqlite3ExprListDelete(db, pMinMaxOrderBy); sqlite3DbFree(db, sAggInfo.aCol); +#ifdef SQLITE_DEBUG + if( db->mallocFailed==0 ){ + for(i=0; ipAggInfo==&sAggInfo ); + sAggInfo.aFunc[i].pExpr->pAggInfo = 0; + } + } + sAggInfo.iAggMagic = 0; +#endif sqlite3DbFree(db, sAggInfo.aFunc); #if SELECTTRACE_ENABLED SELECTTRACE(0x1,pParse,p,("end processing\n")); if( (sqlite3SelectTrace & 0x2000)!=0 && ExplainQueryPlanParent(pParse)==0 ){ sqlite3TreeViewSelect(0, p, 0); Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -974,11 +974,16 @@ ** SELECTTRACE_ENABLED will be either 1 or 0 depending on whether or not ** the Select query generator tracing logic is turned on. */ #if defined(SQLITE_ENABLE_SELECTTRACE) # define SELECTTRACE_ENABLED 1 +# define SELECTTRACE(K,P,S,X) \ + if(sqlite3SelectTrace&(K)) \ + sqlite3DebugPrintf("%u/%d/%p: ",(S)->selId,(P)->addrExplain,(S)),\ + sqlite3DebugPrintf X #else +# define SELECTTRACE(K,P,S,X) # define SELECTTRACE_ENABLED 0 #endif /* ** An instance of the following structure is used to store the busy-handler @@ -2521,12 +2526,27 @@ FuncDef *pFunc; /* The aggregate function implementation */ int iMem; /* Memory location that acts as accumulator */ int iDistinct; /* Ephemeral table used to enforce DISTINCT */ } *aFunc; int nFunc; /* Number of entries in aFunc[] */ +#ifdef SQLITE_DEBUG + u32 iAggMagic; /* Sanity checking constant */ +#endif }; +/* +** Allowed values for AggInfo.iAggMagic +*/ +#define SQLITE_AGGMAGIC_VALID 0x05cadade + +/* +** True if the AggInfo object is valid. Used inside of assert() only. +*/ +#ifdef SQLITE_DEBUG +# define AggInfoValid(P) ((P)->iAggMagic==SQLITE_AGGMAGIC_VALID) +#endif + /* ** The datatype ynVar is a signed integer, either 16-bit or 32-bit. ** Usually it is 16-bits. But if SQLITE_MAX_VARIABLE_NUMBER is greater ** than 32767 we have to make it 32-bit. 16-bit is preferred because ** it uses less memory in the Expr object, which is a big memory user @@ -4224,10 +4244,11 @@ void sqlite3DropIndex(Parse*, SrcList*, int); int sqlite3Select(Parse*, Select*, SelectDest*); Select *sqlite3SelectNew(Parse*,ExprList*,SrcList*,Expr*,ExprList*, Expr*,ExprList*,u32,Expr*); void sqlite3SelectDelete(sqlite3*, Select*); +void sqlite3SelectDeferredDelete(Parse*, Select*); void sqlite3SelectReset(Parse*, Select*); Table *sqlite3SrcListLookup(Parse*, SrcList*); int sqlite3IsReadOnly(Parse*, Table*, int); void sqlite3OpenTable(Parse*, int iCur, int iDb, Table*, int); #if defined(SQLITE_ENABLE_UPDATE_DELETE_LIMIT) && !defined(SQLITE_OMIT_SUBQUERY) @@ -4544,14 +4565,15 @@ extern const char sqlite3StrBINARY[]; extern const unsigned char sqlite3UpperToLower[]; extern const unsigned char sqlite3CtypeMap[]; extern SQLITE_WSD struct Sqlite3Config sqlite3Config; extern FuncDefHash sqlite3BuiltinFunctions; +extern u32 sqlite3SelectTrace; #ifndef SQLITE_OMIT_WSD extern int sqlite3PendingByte; #endif -#endif +#endif /* !defined(SQLITE_AMALGAMATION) */ #ifdef VDBE_PROFILE extern sqlite3_uint64 sqlite3NProfileCnt; #endif void sqlite3RootPageMoved(sqlite3*, int, int, int); void sqlite3Reindex(Parse*, Token*, Token*); Index: src/test1.c ================================================================== --- src/test1.c +++ src/test1.c @@ -8162,11 +8162,11 @@ #ifdef SQLITE_ENABLE_FTS3 extern int sqlite3_fts3_enable_parentheses; #endif #endif #if defined(SQLITE_ENABLE_SELECTTRACE) - extern int sqlite3SelectTrace; + extern u32 sqlite3SelectTrace; #endif for(i=0; ipFunc->zName, pWin->pFunc->nArg); + sqlite3TreeViewLine(pView, "WINFUNC %s(%d) cur=%d", + pWin->pFunc->zName, pWin->pFunc->nArg, + pWin->iEphCsr); sqlite3TreeViewWindow(pView, pWin, 0); sqlite3TreeViewPop(pView); } #endif /* SQLITE_OMIT_WINDOWFUNC */ @@ -594,10 +595,13 @@ if( pExpr->op2==NC_IdxExpr ) zOp2 = "NC_IdxExpr"; if( pExpr->op2==NC_PartIdx ) zOp2 = "NC_PartIdx"; if( pExpr->op2==NC_GenCol ) zOp2 = "NC_GenCol"; sqlite3TreeViewLine(pView, "FUNCTION %Q%s op2=%s", pExpr->u.zToken, zFlgs, zOp2); + }else if( ExprHasProperty(pExpr, EP_WinFunc) ){ + sqlite3TreeViewLine(pView, "WINDOW-FUNCTION %Q%s cur=%d", + pExpr->u.zToken, zFlgs, pExpr->y.pWin->iEphCsr); }else{ sqlite3TreeViewLine(pView, "FUNCTION %Q%s", pExpr->u.zToken, zFlgs); } if( pFarg ){ sqlite3TreeViewExprList(pView, pFarg, pWin!=0, 0); Index: src/window.c ================================================================== --- src/window.c +++ src/window.c @@ -921,16 +921,25 @@ ** contains TK_AGG_FUNCTION nodes that refer to an outer query, ** then we have to increase the Expr->op2 values of those nodes ** due to the extra subquery layer that was added. ** ** See also the incrAggDepth() routine in resolve.c +** +** In addition to increasing pExpr->op2, set pWalker->eCode to 1 if +** any top-level aggregate functions are encountered. This is needed +** to accurately set the SF_Aggregate flag on the new window-function +** subquery. */ static int sqlite3WindowExtraAggFuncDepth(Walker *pWalker, Expr *pExpr){ - if( pExpr->op==TK_AGG_FUNCTION - && pExpr->op2>=pWalker->walkerDepth - ){ - pExpr->op2++; + if( pExpr->op==TK_AGG_FUNCTION ){ + if( pExpr->op2>=pWalker->walkerDepth ){ + pExpr->op2++; + }else if( pExpr->op2==pWalker->walkerDepth-1 ){ + pWalker->eCode = 1; + } + }else if( ExprHasProperty(pExpr, EP_WinFunc) ){ + return WRC_Prune; } return WRC_Continue; } /* @@ -940,11 +949,11 @@ ** are invoked in the correct order as described under "SELECT REWRITING" ** at the top of this file. */ int sqlite3WindowRewrite(Parse *pParse, Select *p){ int rc = SQLITE_OK; - if( p->pWin && p->pPrior==0 && (p->selFlags & SF_WinRewrite)==0 ){ + if( ALWAYS(p->pWin && (p->selFlags & SF_WinRewrite)==0) ){ Vdbe *v = sqlite3GetVdbe(pParse); sqlite3 *db = pParse->db; Select *pSub = 0; /* The subquery */ SrcList *pSrc = p->pSrc; Expr *pWhere = p->pWhere; @@ -954,11 +963,10 @@ ExprList *pSublist = 0; /* Expression list for sub-query */ Window *pMWin = p->pWin; /* Master window object */ Window *pWin; /* Window object iterator */ Table *pTab; - u32 selFlags = p->selFlags; pTab = sqlite3DbMallocZero(db, sizeof(Table)); if( pTab==0 ){ return sqlite3ErrorToParser(db, SQLITE_NOMEM); } @@ -1020,10 +1028,11 @@ pSublist = sqlite3ExprListAppend(pParse, pSublist, pFilter); } pWin->regAccum = ++pParse->nMem; pWin->regResult = ++pParse->nMem; sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum); + VdbeComment((v, "acc of %s()", pWin->pFunc->zName)); } /* If there is no ORDER BY or PARTITION BY clause, and the window ** function accepts zero arguments, and there are no other columns ** selected (e.g. "SELECT row_number() OVER () FROM t1"), it is possible @@ -1037,34 +1046,39 @@ } pSub = sqlite3SelectNew( pParse, pSublist, pSrc, pWhere, pGroupBy, pHaving, pSort, 0, 0 ); + SELECTTRACE(0x1,pParse,p, + ("allocate cursors %d..%d and subquery (%d/%p) to WINDOW %p\n", + pMWin->iEphCsr, pParse->nTab-1, pSub->selId, pSub, pMWin)); p->pSrc = sqlite3SrcListAppend(pParse, 0, 0, 0); if( p->pSrc ){ Table *pTab2; Walker w; p->pSrc->a[0].pSelect = pSub; sqlite3SrcListAssignCursors(pParse, p->pSrc); pSub->selFlags |= SF_Expanded; pTab2 = sqlite3ResultSetOfSelect(pParse, pSub, SQLITE_AFF_NONE); - pSub->selFlags |= (selFlags & SF_Aggregate); if( pTab2==0 ){ /* Might actually be some other kind of error, but in that case ** pParse->nErr will be set, so if SQLITE_NOMEM is set, we will get ** the correct error message regardless. */ rc = SQLITE_NOMEM; }else{ memcpy(pTab, pTab2, sizeof(Table)); pTab->tabFlags |= TF_Ephemeral; + assert( pTab->zName==0 ); + pTab->zName = sqlite3MPrintf(db, "", pMWin->iEphCsr); p->pSrc->a[0].pTab = pTab; pTab = pTab2; memset(&w, 0, sizeof(w)); w.xExprCallback = sqlite3WindowExtraAggFuncDepth; w.xSelectCallback = sqlite3WalkerDepthIncrease; w.xSelectCallback2 = sqlite3WalkerDepthDecrease; sqlite3WalkSelect(&w, pSub); + if( w.eCode && pSub->pWin==0 ) pSub->selFlags |= SF_Aggregate; } }else{ sqlite3SelectDelete(db, pSub); } if( db->mallocFailed ) rc = SQLITE_NOMEM; Index: test/fuzzdata8.db ================================================================== --- test/fuzzdata8.db +++ test/fuzzdata8.db cannot compute difference between binary files Index: test/window1.test ================================================================== --- test/window1.test +++ test/window1.test @@ -1740,8 +1740,134 @@ JOIN a AS e ON a.c=e.c WHERE a.c=(SELECT (SELECT coalesce(lead(2) OVER(),0) + sum(d.c)) FROM a AS d WHERE a.c); } {4 4 4 4} + +#------------------------------------------------------------------------- +reset_db +do_execsql_test 54.1 { + CREATE TABLE t1(a VARCHAR(20), b FLOAT); + INSERT INTO t1 VALUES('1',10.0); +} + +do_execsql_test 54.2 { + SELECT * FROM ( + SELECT sum(b) OVER() AS c FROM t1 + UNION + SELECT b AS c FROM t1 + ) WHERE c>10; +} + +do_execsql_test 54.3 { + INSERT INTO t1 VALUES('2',5.0); + INSERT INTO t1 VALUES('3',15.0); +} + +do_execsql_test 54.4 { + SELECT * FROM ( + SELECT sum(b) OVER() AS c FROM t1 + UNION + SELECT b AS c FROM t1 + ) WHERE c>10; +} {15.0 30.0} + +# 2020-06-05 ticket c8d3b9f0a750a529 +reset_db +do_execsql_test 55.1 { + CREATE TABLE a(b); + SELECT + (SELECT b FROM a + GROUP BY b + HAVING (SELECT COUNT()OVER() + lead(b)OVER(ORDER BY SUM(DISTINCT b) + b)) + ) + FROM a + UNION + SELECT 99 + ORDER BY 1; +} {99} + +#------------------------------------------------------------------------ +reset_db +do_execsql_test 56.1 { + CREATE TABLE t1(a, b INTEGER); + CREATE TABLE t2(c, d); +} +do_catchsql_test 56.2 { + SELECT avg(b) FROM t1 + UNION ALL + SELECT min(c) OVER () FROM t2 + ORDER BY nosuchcolumn; +} {1 {1st ORDER BY term does not match any column in the result set}} + +reset_db +do_execsql_test 57.1 { + CREATE TABLE t4(a, b, c, d, e); +} + +do_catchsql_test 57.2 { + SELECT b FROM t4 + UNION + SELECT a FROM t4 + ORDER BY ( + SELECT sum(x) OVER() FROM ( + SELECT c AS x FROM t4 + UNION + SELECT d FROM t4 + ORDER BY (SELECT e FROM t4) + ) + ); +} {1 {1st ORDER BY term does not match any column in the result set}} + +# 2020-06-06 various dbsqlfuzz finds and +# ticket 0899cf62f597d7e7 +# +reset_db +do_execsql_test 57.1 { + CREATE TABLE t1(a, b, c); + INSERT INTO t1 VALUES(NULL,NULL,NULL); + SELECT + sum(a), + min(b) OVER (), + count(c) OVER (ORDER BY b) + FROM t1; +} {{} {} 0} +do_execsql_test 57.2 { + CREATE TABLE v0 ( v1 INTEGER PRIMARY KEY ) ; + INSERT INTO v0 VALUES ( 10 ) ; + SELECT DISTINCT v1, lead(v1) OVER() FROM v0 GROUP BY v1 ORDER BY 2; +} {10 {}} +do_execsql_test 57.3 { + DROP TABLE t1; + CREATE TABLE t1(a); + INSERT INTO t1(a) VALUES(22); + CREATE TABLE t3(y); + INSERT INTO t3(y) VALUES(5),(11),(-9); + SELECT ( + SELECT max(y) OVER( ORDER BY (SELECT x FROM (SELECT sum(y) AS x FROM t1))) + ) + FROM t3; +} {5} + +# 2020-06-06 ticket 1f6f353b684fc708 +reset_db +do_execsql_test 58.1 { + CREATE TABLE a(a, b, c); + INSERT INTO a VALUES(1, 2, 3); + INSERT INTO a VALUES(4, 5, 6); + SELECT sum(345+b) OVER (ORDER BY b), + sum(avg(678)) OVER (ORDER BY c) FROM a; +} {347 678.0} + +# 2020-06-06 ticket e5504e987e419fb0 +do_catchsql_test 59.1 { + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(x INTEGER PRIMARY KEY); + INSERT INTO t1 VALUES (123); + SELECT + ntile( (SELECT sum(x)) ) OVER(ORDER BY x), + min(x) OVER(ORDER BY x) + FROM t1; +} {1 {misuse of aggregate: sum()}} finish_test