Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch filter-clause Excluding Merge-Ins
This is equivalent to a diff from 18bfb2179c to 5dac8c38df
2019-07-13
| ||
16:39 | Add support for attaching a FILTER clause to an aggregate function. (check-in: ee293e5aea user: dan tags: trunk) | |
16:22 | Rework the FILTER clause implementation to share more code with window functions. (Leaf check-in: 5dac8c38df user: dan tags: filter-clause) | |
16:15 | Remove unreachable "break" statements to silence harmless compiler warnings from ICC. (check-in: 0d7287e1bf user: drh tags: trunk) | |
09:56 | Merge latest trunk changes into this branch. (check-in: 86ab963cc5 user: dan tags: filter-clause) | |
2019-07-11
| ||
19:50 | Improved comments and extra testcase() macros on the serial-type computation logic in the OP_MakeRecord opcode. (check-in: 18bfb2179c user: drh tags: trunk) | |
19:27 | Increase the version number to 3.30.0 for the next release cycle. (check-in: 2578e3c64b user: drh tags: trunk) | |
Changes to src/btree.c.
︙ | ︙ | |||
8303 8304 8305 8306 8307 8308 8309 | u8 aBalanceQuickSpace[13]; u8 *pFree = 0; VVA_ONLY( int balance_quick_called = 0 ); VVA_ONLY( int balance_deeper_called = 0 ); do { | | > > | < < | 8303 8304 8305 8306 8307 8308 8309 8310 8311 8312 8313 8314 8315 8316 8317 8318 8319 8320 8321 8322 8323 8324 8325 8326 8327 8328 8329 8330 8331 8332 8333 8334 8335 8336 8337 8338 8339 8340 8341 8342 8343 | u8 aBalanceQuickSpace[13]; u8 *pFree = 0; VVA_ONLY( int balance_quick_called = 0 ); VVA_ONLY( int balance_deeper_called = 0 ); do { int iPage; MemPage *pPage = pCur->pPage; if( NEVER(pPage->nFree<0) && btreeComputeFreeSpace(pPage) ) break; if( pPage->nOverflow==0 && pPage->nFree<=nMin ){ break; }else if( (iPage = pCur->iPage)==0 ){ if( pPage->nOverflow ){ /* The root page of the b-tree is overfull. In this case call the ** balance_deeper() function to create a new child for the root-page ** and copy the current contents of the root-page to it. The ** next iteration of the do-loop will balance the child page. */ assert( balance_deeper_called==0 ); VVA_ONLY( balance_deeper_called++ ); rc = balance_deeper(pPage, &pCur->apPage[1]); if( rc==SQLITE_OK ){ pCur->iPage = 1; pCur->ix = 0; pCur->aiIdx[0] = 0; pCur->apPage[0] = pPage; pCur->pPage = pCur->apPage[1]; assert( pCur->pPage->nOverflow ); } }else{ break; } }else{ MemPage * const pParent = pCur->apPage[iPage-1]; int const iIdx = pCur->aiIdx[iPage-1]; rc = sqlite3PagerWrite(pParent->pDbPage); if( rc==SQLITE_OK && pParent->nFree<0 ){ rc = btreeComputeFreeSpace(pParent); |
︙ | ︙ |
Changes to src/expr.c.
︙ | ︙ | |||
1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 | } #endif if( !ExprHasProperty(p, (EP_TokenOnly|EP_Leaf)) ){ /* The Expr.x union is never used at the same time as Expr.pRight */ assert( p->x.pList==0 || p->pRight==0 ); if( p->pLeft && p->op!=TK_SELECT_COLUMN ) sqlite3ExprDeleteNN(db, p->pLeft); if( p->pRight ){ sqlite3ExprDeleteNN(db, p->pRight); }else if( ExprHasProperty(p, EP_xIsSelect) ){ sqlite3SelectDelete(db, p->x.pSelect); }else{ sqlite3ExprListDelete(db, p->x.pList); | > > < > | < | > > | 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 | } #endif if( !ExprHasProperty(p, (EP_TokenOnly|EP_Leaf)) ){ /* The Expr.x union is never used at the same time as Expr.pRight */ assert( p->x.pList==0 || p->pRight==0 ); if( p->pLeft && p->op!=TK_SELECT_COLUMN ) sqlite3ExprDeleteNN(db, p->pLeft); if( p->pRight ){ assert( !ExprHasProperty(p, EP_WinFunc) ); sqlite3ExprDeleteNN(db, p->pRight); }else if( ExprHasProperty(p, EP_xIsSelect) ){ assert( !ExprHasProperty(p, EP_WinFunc) ); sqlite3SelectDelete(db, p->x.pSelect); }else{ sqlite3ExprListDelete(db, p->x.pList); #ifndef SQLITE_OMIT_WINDOWFUNC if( ExprHasProperty(p, EP_WinFunc) ){ sqlite3WindowDelete(db, p->y.pWin); } #endif } } if( ExprHasProperty(p, EP_MemToken) ) sqlite3DbFree(db, p->u.zToken); if( !ExprHasProperty(p, EP_Static) ){ sqlite3DbFreeNN(db, p); } } |
︙ | ︙ | |||
1328 1329 1330 1331 1332 1333 1334 | /* ** The gatherSelectWindows() procedure and its helper routine ** gatherSelectWindowsCallback() are used to scan all the expressions ** an a newly duplicated SELECT statement and gather all of the Window ** objects found there, assembling them onto the linked list at Select->pWin. */ static int gatherSelectWindowsCallback(Walker *pWalker, Expr *pExpr){ | | | > | 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 | /* ** The gatherSelectWindows() procedure and its helper routine ** gatherSelectWindowsCallback() are used to scan all the expressions ** an a newly duplicated SELECT statement and gather all of the Window ** objects found there, assembling them onto the linked list at Select->pWin. */ static int gatherSelectWindowsCallback(Walker *pWalker, Expr *pExpr){ if( pExpr->op==TK_FUNCTION && ExprHasProperty(pExpr, EP_WinFunc) ){ assert( pExpr->y.pWin ); assert( IsWindowFunc(pExpr) ); pExpr->y.pWin->pNextWin = pWalker->u.pSelect->pWin; pWalker->u.pSelect->pWin = pExpr->y.pWin; } return WRC_Continue; } static int gatherSelectWindowsSelectCallback(Walker *pWalker, Select *p){ return p==pWalker->u.pSelect ? WRC_Continue : WRC_Prune; |
︙ | ︙ | |||
4835 4836 4837 4838 4839 4840 4841 | } if( pB->op==TK_COLLATE && sqlite3ExprCompare(pParse, pA,pB->pLeft,iTab)<2 ){ return 1; } return 2; } if( pA->op!=TK_COLUMN && pA->op!=TK_AGG_COLUMN && pA->u.zToken ){ | | | < < < < < < < | > > | > > | 4839 4840 4841 4842 4843 4844 4845 4846 4847 4848 4849 4850 4851 4852 4853 4854 4855 4856 4857 4858 4859 4860 4861 4862 4863 | } if( pB->op==TK_COLLATE && sqlite3ExprCompare(pParse, pA,pB->pLeft,iTab)<2 ){ return 1; } return 2; } if( pA->op!=TK_COLUMN && pA->op!=TK_AGG_COLUMN && pA->u.zToken ){ if( pA->op==TK_FUNCTION || pA->op==TK_AGG_FUNCTION ){ if( sqlite3StrICmp(pA->u.zToken,pB->u.zToken)!=0 ) return 2; #ifndef SQLITE_OMIT_WINDOWFUNC assert( pA->op==pB->op ); if( ExprHasProperty(pA,EP_WinFunc)!=ExprHasProperty(pB,EP_WinFunc) ){ return 2; } if( ExprHasProperty(pA,EP_WinFunc) ){ if( sqlite3WindowCompare(pParse, pA->y.pWin, pB->y.pWin, 1)!=0 ){ return 2; } } #endif }else if( pA->op==TK_NULL ){ return 0; }else if( pA->op==TK_COLLATE ){ if( sqlite3_stricmp(pA->u.zToken,pB->u.zToken)!=0 ) return 2; }else if( ALWAYS(pB->u.zToken!=0) && strcmp(pA->u.zToken,pB->u.zToken)!=0 ){ |
︙ | ︙ |
Changes to src/parse.y.
︙ | ︙ | |||
1040 1041 1042 1043 1044 1045 1046 | A = sqlite3ExprFunction(pParse, Y, &X, D); } expr(A) ::= id(X) LP STAR RP. { A = sqlite3ExprFunction(pParse, 0, &X, 0); } %ifndef SQLITE_OMIT_WINDOWFUNC | | | | 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 | A = sqlite3ExprFunction(pParse, Y, &X, D); } expr(A) ::= id(X) LP STAR RP. { A = sqlite3ExprFunction(pParse, 0, &X, 0); } %ifndef SQLITE_OMIT_WINDOWFUNC expr(A) ::= id(X) LP distinct(D) exprlist(Y) RP filter_over(Z). { A = sqlite3ExprFunction(pParse, Y, &X, D); sqlite3WindowAttach(pParse, A, Z); } expr(A) ::= id(X) LP STAR RP filter_over(Z). { A = sqlite3ExprFunction(pParse, 0, &X, 0); sqlite3WindowAttach(pParse, A, Z); } %endif term(A) ::= CTIME_KW(OP). { A = sqlite3ExprFunction(pParse, 0, &OP, 0); |
︙ | ︙ | |||
1653 1654 1655 1656 1657 1658 1659 | %type frame_opt {Window*} %destructor frame_opt {sqlite3WindowDelete(pParse->db, $$);} %type part_opt {ExprList*} %destructor part_opt {sqlite3ExprListDelete(pParse->db, $$);} | | | > > > > > > | 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 1672 1673 1674 | %type frame_opt {Window*} %destructor frame_opt {sqlite3WindowDelete(pParse->db, $$);} %type part_opt {ExprList*} %destructor part_opt {sqlite3ExprListDelete(pParse->db, $$);} %type filter_clause {Expr*} %destructor filter_clause {sqlite3ExprDelete(pParse->db, $$);} %type over_clause {Window*} %destructor over_clause {sqlite3WindowDelete(pParse->db, $$);} %type filter_over {Window*} %destructor filter_over {sqlite3WindowDelete(pParse->db, $$);} %type range_or_rows {int} %type frame_bound {struct FrameBound} %destructor frame_bound {sqlite3ExprDelete(pParse->db, $$.pExpr);} %type frame_bound_s {struct FrameBound} %destructor frame_bound_s {sqlite3ExprDelete(pParse->db, $$.pExpr);} |
︙ | ︙ | |||
1720 1721 1722 1723 1724 1725 1726 | frame_exclude(A) ::= GROUP|TIES(X). {A = @X; /*A-overwrites-X*/} %type window_clause {Window*} %destructor window_clause {sqlite3WindowListDelete(pParse->db, $$);} window_clause(A) ::= WINDOW windowdefn_list(B). { A = B; } | > > > > | > > > > > > > > > | | < | < < < < | | 1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736 1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750 1751 1752 1753 1754 1755 1756 1757 1758 1759 1760 1761 1762 1763 1764 1765 1766 | frame_exclude(A) ::= GROUP|TIES(X). {A = @X; /*A-overwrites-X*/} %type window_clause {Window*} %destructor window_clause {sqlite3WindowListDelete(pParse->db, $$);} window_clause(A) ::= WINDOW windowdefn_list(B). { A = B; } filter_over(A) ::= filter_clause(F) over_clause(O). { O->pFilter = F; A = O; } filter_over(A) ::= over_clause(O). { A = O; } filter_over(A) ::= filter_clause(F). { A = (Window*)sqlite3DbMallocZero(pParse->db, sizeof(Window)); if( A ){ A->eFrmType = TK_FILTER; A->pFilter = F; } } over_clause(A) ::= OVER LP window(Z) RP. { A = Z; assert( A!=0 ); } over_clause(A) ::= OVER nm(Z). { A = (Window*)sqlite3DbMallocZero(pParse->db, sizeof(Window)); if( A ){ A->zName = sqlite3DbStrNDup(pParse->db, Z.z, Z.n); } } filter_clause(A) ::= FILTER LP WHERE expr(X) RP. { A = X; } %endif /* SQLITE_OMIT_WINDOWFUNC */ /* ** The code generator needs some extra TK_ token values for tokens that ** are synthesized and do not actually appear in the grammar: */ %token |
︙ | ︙ |
Changes to src/prepare.c.
︙ | ︙ | |||
631 632 633 634 635 636 637 | } if( pzTail ){ *pzTail = sParse.zTail; } rc = sParse.rc; #ifndef SQLITE_OMIT_EXPLAIN | | | 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 | } if( pzTail ){ *pzTail = sParse.zTail; } rc = sParse.rc; #ifndef SQLITE_OMIT_EXPLAIN if( sParse.explain && rc==SQLITE_OK && sParse.pVdbe ){ static const char * const azColName[] = { "addr", "opcode", "p1", "p2", "p3", "p4", "p5", "comment", "id", "parent", "notused", "detail" }; int iFirst, mx; if( sParse.explain==2 ){ sqlite3VdbeSetNumCols(sParse.pVdbe, 4); |
︙ | ︙ | |||
656 657 658 659 660 661 662 | } } #endif if( db->init.busy==0 ){ sqlite3VdbeSetSql(sParse.pVdbe, zSql, (int)(sParse.zTail-zSql), prepFlags); } | | | | 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 | } } #endif if( db->init.busy==0 ){ sqlite3VdbeSetSql(sParse.pVdbe, zSql, (int)(sParse.zTail-zSql), prepFlags); } if( rc!=SQLITE_OK || db->mallocFailed ){ if( sParse.pVdbe ) sqlite3VdbeFinalize(sParse.pVdbe); assert(!(*ppStmt)); }else{ *ppStmt = (sqlite3_stmt*)sParse.pVdbe; } if( zErrMsg ){ sqlite3ErrorWithMsg(db, rc, "%s", zErrMsg); |
︙ | ︙ |
Changes to src/resolve.c.
︙ | ︙ | |||
745 746 747 748 749 750 751 | int wrong_num_args = 0; /* True if wrong number of arguments */ int is_agg = 0; /* True if is an aggregate function */ int nId; /* Number of characters in function name */ const char *zId; /* The function name. */ FuncDef *pDef; /* Information about the function */ u8 enc = ENC(pParse->db); /* The database encoding */ int savedAllowFlags = (pNC->ncFlags & (NC_AllowAgg | NC_AllowWin)); | | > > | 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 | int wrong_num_args = 0; /* True if wrong number of arguments */ int is_agg = 0; /* True if is an aggregate function */ int nId; /* Number of characters in function name */ const char *zId; /* The function name. */ FuncDef *pDef; /* Information about the function */ u8 enc = ENC(pParse->db); /* The database encoding */ int savedAllowFlags = (pNC->ncFlags & (NC_AllowAgg | NC_AllowWin)); #ifndef SQLITE_OMIT_WINDOWFUNC Window *pWin = (IsWindowFunc(pExpr) ? pExpr->y.pWin : 0); #endif assert( !ExprHasProperty(pExpr, EP_xIsSelect) ); zId = pExpr->u.zToken; nId = sqlite3Strlen30(zId); pDef = sqlite3FindFunction(pParse->db, zId, n, enc, 0); if( pDef==0 ){ pDef = sqlite3FindFunction(pParse->db, zId, -2, enc, 0); if( pDef==0 ){ |
︙ | ︙ | |||
826 827 828 829 830 831 832 | if( 0==IN_RENAME_OBJECT ){ #ifndef SQLITE_OMIT_WINDOWFUNC assert( is_agg==0 || (pDef->funcFlags & SQLITE_FUNC_MINMAX) || (pDef->xValue==0 && pDef->xInverse==0) || (pDef->xValue && pDef->xInverse && pDef->xSFunc && pDef->xFinalize) ); | | | | | > > > > > > > | 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 | if( 0==IN_RENAME_OBJECT ){ #ifndef SQLITE_OMIT_WINDOWFUNC assert( is_agg==0 || (pDef->funcFlags & SQLITE_FUNC_MINMAX) || (pDef->xValue==0 && pDef->xInverse==0) || (pDef->xValue && pDef->xInverse && pDef->xSFunc && pDef->xFinalize) ); if( pDef && pDef->xValue==0 && pWin ){ sqlite3ErrorMsg(pParse, "%.*s() may not be used as a window function", nId, zId ); pNC->nErr++; }else if( (is_agg && (pNC->ncFlags & NC_AllowAgg)==0) || (is_agg && (pDef->funcFlags&SQLITE_FUNC_WINDOW) && !pWin) || (is_agg && pWin && (pNC->ncFlags & NC_AllowWin)==0) ){ const char *zType; if( (pDef->funcFlags & SQLITE_FUNC_WINDOW) || pWin ){ zType = "window"; }else{ zType = "aggregate"; } sqlite3ErrorMsg(pParse, "misuse of %s function %.*s()",zType,nId,zId); pNC->nErr++; is_agg = 0; }else if( is_agg==0 && ExprHasProperty(pExpr, EP_WinFunc) ){ assert( !IsWindowFunc(pExpr) ); sqlite3ErrorMsg(pParse, "filter clause may not be used with non-aggregate %.*s()", nId, zId ); pNC->nErr++; } #else if( (is_agg && (pNC->ncFlags & NC_AllowAgg)==0) ){ sqlite3ErrorMsg(pParse,"misuse of aggregate function %.*s()",nId,zId); pNC->nErr++; is_agg = 0; } |
︙ | ︙ | |||
870 871 872 873 874 875 876 | pNC->nErr++; } if( is_agg ){ /* Window functions may not be arguments of aggregate functions. ** Or arguments of other window functions. But aggregate functions ** may be arguments for window functions. */ #ifndef SQLITE_OMIT_WINDOWFUNC | | | | | | | | > > > > > | 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 | pNC->nErr++; } if( is_agg ){ /* Window functions may not be arguments of aggregate functions. ** Or arguments of other window functions. But aggregate functions ** may be arguments for window functions. */ #ifndef SQLITE_OMIT_WINDOWFUNC pNC->ncFlags &= ~(NC_AllowWin | (!pWin ? NC_AllowAgg : 0)); #else pNC->ncFlags &= ~NC_AllowAgg; #endif } } sqlite3WalkExprList(pWalker, pList); if( is_agg ){ #ifndef SQLITE_OMIT_WINDOWFUNC if( pWin ){ Select *pSel = pNC->pWinSelect; if( IN_RENAME_OBJECT==0 ){ sqlite3WindowUpdate(pParse, pSel->pWinDefn, pWin, pDef); } sqlite3WalkExprList(pWalker, pWin->pPartition); sqlite3WalkExprList(pWalker, pWin->pOrderBy); sqlite3WalkExpr(pWalker, pWin->pFilter); if( 0==pSel->pWin || 0==sqlite3WindowCompare(pParse, pSel->pWin, pWin, 0) ){ pExpr->y.pWin->pNextWin = pSel->pWin; pSel->pWin = pExpr->y.pWin; } pNC->ncFlags |= NC_HasWin; }else #endif /* SQLITE_OMIT_WINDOWFUNC */ { NameContext *pNC2 = pNC; pExpr->op = TK_AGG_FUNCTION; pExpr->op2 = 0; #ifndef SQLITE_OMIT_WINDOWFUNC if( ExprHasProperty(pExpr, EP_WinFunc) ){ sqlite3WalkExpr(pWalker, pExpr->y.pWin->pFilter); } #endif while( pNC2 && !sqlite3FunctionUsesThisSrc(pExpr, pNC2->pSrcList) ){ pExpr->op2++; pNC2 = pNC2->pNext; } assert( pDef!=0 ); if( pNC2 ){ assert( SQLITE_FUNC_MINMAX==NC_MinMaxAgg ); |
︙ | ︙ |
Changes to src/select.c.
︙ | ︙ | |||
4399 4400 4401 4402 4403 4404 4405 | ExprList *pEList = pFunc->x.pList; /* Arguments to agg function */ const char *zFunc; /* Name of aggregate function pFunc */ ExprList *pOrderBy; u8 sortOrder; assert( *ppMinMax==0 ); assert( pFunc->op==TK_AGG_FUNCTION ); | > | > > | 4399 4400 4401 4402 4403 4404 4405 4406 4407 4408 4409 4410 4411 4412 4413 4414 4415 4416 | ExprList *pEList = pFunc->x.pList; /* Arguments to agg function */ const char *zFunc; /* Name of aggregate function pFunc */ ExprList *pOrderBy; u8 sortOrder; assert( *ppMinMax==0 ); assert( pFunc->op==TK_AGG_FUNCTION ); assert( !IsWindowFunc(pFunc) ); if( pEList==0 || pEList->nExpr!=1 || ExprHasProperty(pFunc, EP_WinFunc) ){ return eRet; } zFunc = pFunc->u.zToken; if( sqlite3StrICmp(zFunc, "min")==0 ){ eRet = WHERE_ORDERBY_MIN; sortOrder = SQLITE_SO_ASC; }else if( sqlite3StrICmp(zFunc, "max")==0 ){ eRet = WHERE_ORDERBY_MAX; sortOrder = SQLITE_SO_DESC; |
︙ | ︙ | |||
4446 4447 4448 4449 4450 4451 4452 | pExpr = p->pEList->a[0].pExpr; assert( pTab && !pTab->pSelect && pExpr ); if( IsVirtual(pTab) ) return 0; if( pExpr->op!=TK_AGG_FUNCTION ) return 0; if( NEVER(pAggInfo->nFunc==0) ) return 0; if( (pAggInfo->aFunc[0].pFunc->funcFlags&SQLITE_FUNC_COUNT)==0 ) return 0; | | | 4449 4450 4451 4452 4453 4454 4455 4456 4457 4458 4459 4460 4461 4462 4463 | pExpr = p->pEList->a[0].pExpr; assert( pTab && !pTab->pSelect && pExpr ); if( IsVirtual(pTab) ) return 0; if( pExpr->op!=TK_AGG_FUNCTION ) return 0; if( NEVER(pAggInfo->nFunc==0) ) return 0; if( (pAggInfo->aFunc[0].pFunc->funcFlags&SQLITE_FUNC_COUNT)==0 ) return 0; if( ExprHasProperty(pExpr, EP_Distinct|EP_WinFunc) ) return 0; return pTab; } /* ** If the source-list item passed as an argument was augmented with an ** INDEXED BY clause, then try to locate the specified index. If there |
︙ | ︙ | |||
5326 5327 5328 5329 5330 5331 5332 5333 5334 5335 5336 5337 5338 5339 5340 5341 | pAggInfo->directMode = 1; for(i=0, pF=pAggInfo->aFunc; i<pAggInfo->nFunc; i++, pF++){ int nArg; int addrNext = 0; int regAgg; ExprList *pList = pF->pExpr->x.pList; assert( !ExprHasProperty(pF->pExpr, EP_xIsSelect) ); if( pList ){ nArg = pList->nExpr; regAgg = sqlite3GetTempRange(pParse, nArg); sqlite3ExprCodeExprList(pParse, pList, regAgg, 0, SQLITE_ECEL_DUP); }else{ nArg = 0; regAgg = 0; } if( pF->iDistinct>=0 ){ | > > > > > > > | > | 5329 5330 5331 5332 5333 5334 5335 5336 5337 5338 5339 5340 5341 5342 5343 5344 5345 5346 5347 5348 5349 5350 5351 5352 5353 5354 5355 5356 5357 5358 5359 5360 | pAggInfo->directMode = 1; for(i=0, pF=pAggInfo->aFunc; i<pAggInfo->nFunc; i++, pF++){ int nArg; int addrNext = 0; int regAgg; ExprList *pList = pF->pExpr->x.pList; assert( !ExprHasProperty(pF->pExpr, EP_xIsSelect) ); assert( !IsWindowFunc(pF->pExpr) ); if( ExprHasProperty(pF->pExpr, EP_WinFunc) ){ Expr *pFilter = pF->pExpr->y.pWin->pFilter; addrNext = sqlite3VdbeMakeLabel(pParse); sqlite3ExprIfFalse(pParse, pFilter, addrNext, SQLITE_JUMPIFNULL); } if( pList ){ nArg = pList->nExpr; regAgg = sqlite3GetTempRange(pParse, nArg); sqlite3ExprCodeExprList(pParse, pList, regAgg, 0, SQLITE_ECEL_DUP); }else{ nArg = 0; regAgg = 0; } if( pF->iDistinct>=0 ){ if( addrNext==0 ){ addrNext = sqlite3VdbeMakeLabel(pParse); } testcase( nArg==0 ); /* Error condition */ testcase( nArg>1 ); /* Also an error */ codeDistinct(pParse, pF->iDistinct, addrNext, 1, regAgg); } if( pF->pFunc->funcFlags & SQLITE_FUNC_NEEDCOLL ){ CollSeq *pColl = 0; struct ExprList_item *pItem; |
︙ | ︙ | |||
6218 6219 6220 6221 6222 6223 6224 | sAggInfo.nAccumulator = sAggInfo.nColumn; if( p->pGroupBy==0 && p->pHaving==0 && sAggInfo.nFunc==1 ){ minMaxFlag = minMaxQuery(db, sAggInfo.aFunc[0].pExpr, &pMinMaxOrderBy); }else{ minMaxFlag = WHERE_ORDERBY_NORMAL; } for(i=0; i<sAggInfo.nFunc; i++){ | > | | > > > > > > | 6229 6230 6231 6232 6233 6234 6235 6236 6237 6238 6239 6240 6241 6242 6243 6244 6245 6246 6247 6248 6249 6250 6251 6252 | sAggInfo.nAccumulator = sAggInfo.nColumn; if( p->pGroupBy==0 && p->pHaving==0 && sAggInfo.nFunc==1 ){ minMaxFlag = minMaxQuery(db, sAggInfo.aFunc[0].pExpr, &pMinMaxOrderBy); }else{ minMaxFlag = WHERE_ORDERBY_NORMAL; } for(i=0; i<sAggInfo.nFunc; i++){ Expr *pExpr = sAggInfo.aFunc[i].pExpr; assert( !ExprHasProperty(pExpr, EP_xIsSelect) ); sNC.ncFlags |= NC_InAggFunc; sqlite3ExprAnalyzeAggList(&sNC, pExpr->x.pList); #ifndef SQLITE_OMIT_WINDOWFUNC assert( !IsWindowFunc(pExpr) ); if( ExprHasProperty(pExpr, EP_WinFunc) ){ sqlite3ExprAnalyzeAggregates(&sNC, pExpr->y.pWin->pFilter); } #endif sNC.ncFlags &= ~NC_InAggFunc; } sAggInfo.mxReg = pParse->nMem; if( db->mallocFailed ) goto select_end; #if SELECTTRACE_ENABLED if( sqlite3SelectTrace & 0x400 ){ int ii; |
︙ | ︙ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
2487 2488 2489 2490 2491 2492 2493 | u8 op2; /* TK_REGISTER/TK_TRUTH: original value of Expr.op ** TK_COLUMN: the value of p5 for OP_Column ** TK_AGG_FUNCTION: nesting depth */ AggInfo *pAggInfo; /* Used by TK_AGG_COLUMN and TK_AGG_FUNCTION */ union { Table *pTab; /* TK_COLUMN: Table containing column. Can be NULL ** for a column of an index on an expression */ | | | 2487 2488 2489 2490 2491 2492 2493 2494 2495 2496 2497 2498 2499 2500 2501 | u8 op2; /* TK_REGISTER/TK_TRUTH: original value of Expr.op ** TK_COLUMN: the value of p5 for OP_Column ** TK_AGG_FUNCTION: nesting depth */ AggInfo *pAggInfo; /* Used by TK_AGG_COLUMN and TK_AGG_FUNCTION */ union { Table *pTab; /* TK_COLUMN: Table containing column. Can be NULL ** for a column of an index on an expression */ Window *pWin; /* EP_WinFunc: Window/Filter defn for a function */ struct { /* TK_IN, TK_SELECT, and TK_EXISTS */ int iAddr; /* Subroutine entry address */ int regReturn; /* Register used to hold return address */ } sub; } y; }; |
︙ | ︙ | |||
2574 2575 2576 2577 2578 2579 2580 2581 2582 2583 2584 2585 2586 2587 | #define EXPR_TOKENONLYSIZE offsetof(Expr,pLeft) /* Fewer features */ /* ** Flags passed to the sqlite3ExprDup() function. See the header comment ** above sqlite3ExprDup() for details. */ #define EXPRDUP_REDUCE 0x0001 /* Used reduced-size Expr nodes */ /* ** A list of expressions. Each expression may optionally have a ** name. An expr/name combination can be used in several ways, such ** as the list of "expr AS ID" fields following a "SELECT" or in the ** list of "ID = expr" items in an UPDATE. A list of expressions can ** also be used as the argument to a function, in which case the a.zName | > > > > > > > > | 2574 2575 2576 2577 2578 2579 2580 2581 2582 2583 2584 2585 2586 2587 2588 2589 2590 2591 2592 2593 2594 2595 | #define EXPR_TOKENONLYSIZE offsetof(Expr,pLeft) /* Fewer features */ /* ** Flags passed to the sqlite3ExprDup() function. See the header comment ** above sqlite3ExprDup() for details. */ #define EXPRDUP_REDUCE 0x0001 /* Used reduced-size Expr nodes */ /* ** True if the expression passed as an argument was a function with ** an OVER() clause (a window function). */ #define IsWindowFunc(p) ( \ ExprHasProperty((p), EP_WinFunc) && p->y.pWin->eFrmType!=TK_FILTER \ ) /* ** A list of expressions. Each expression may optionally have a ** name. An expr/name combination can be used in several ways, such ** as the list of "expr AS ID" fields following a "SELECT" or in the ** list of "ID = expr" items in an UPDATE. A list of expressions can ** also be used as the argument to a function, in which case the a.zName |
︙ | ︙ | |||
3599 3600 3601 3602 3603 3604 3605 | }; #ifndef SQLITE_OMIT_WINDOWFUNC void sqlite3WindowDelete(sqlite3*, Window*); void sqlite3WindowListDelete(sqlite3 *db, Window *p); Window *sqlite3WindowAlloc(Parse*, int, int, Expr*, int , Expr*, u8); void sqlite3WindowAttach(Parse*, Expr*, Window*); | | | 3607 3608 3609 3610 3611 3612 3613 3614 3615 3616 3617 3618 3619 3620 3621 | }; #ifndef SQLITE_OMIT_WINDOWFUNC void sqlite3WindowDelete(sqlite3*, Window*); void sqlite3WindowListDelete(sqlite3 *db, Window *p); Window *sqlite3WindowAlloc(Parse*, int, int, Expr*, int , Expr*, u8); void sqlite3WindowAttach(Parse*, Expr*, Window*); int sqlite3WindowCompare(Parse*, Window*, Window*, int); void sqlite3WindowCodeInit(Parse*, Window*); void sqlite3WindowCodeStep(Parse*, Select*, WhereInfo*, int, int); int sqlite3WindowRewrite(Parse*, Select*); int sqlite3ExpandSubquery(Parse*, struct SrcList_item*); void sqlite3WindowUpdate(Parse*, Window*, Window*, FuncDef*); Window *sqlite3WindowDup(sqlite3 *db, Expr *pOwner, Window *p); Window *sqlite3WindowListDup(sqlite3 *db, Window *p); |
︙ | ︙ |
Changes to src/vdbeaux.c.
︙ | ︙ | |||
4603 4604 4605 4606 4607 4608 4609 | if( (szHdr + nStr) > nKey1 ){ pPKey2->errCode = (u8)SQLITE_CORRUPT_BKPT; return 0; /* Corruption */ } nCmp = MIN( pPKey2->aMem[0].n, nStr ); res = memcmp(&aKey1[szHdr], pPKey2->aMem[0].z, nCmp); | | > > > > < < < < | 4603 4604 4605 4606 4607 4608 4609 4610 4611 4612 4613 4614 4615 4616 4617 4618 4619 4620 4621 4622 4623 4624 4625 4626 4627 4628 4629 4630 4631 4632 4633 4634 | if( (szHdr + nStr) > nKey1 ){ pPKey2->errCode = (u8)SQLITE_CORRUPT_BKPT; return 0; /* Corruption */ } nCmp = MIN( pPKey2->aMem[0].n, nStr ); res = memcmp(&aKey1[szHdr], pPKey2->aMem[0].z, nCmp); if( res>0 ){ res = pPKey2->r2; }else if( res<0 ){ res = pPKey2->r1; }else{ res = nStr - pPKey2->aMem[0].n; if( res==0 ){ if( pPKey2->nField>1 ){ res = sqlite3VdbeRecordCompareWithSkip(nKey1, pKey1, pPKey2, 1); }else{ res = pPKey2->default_rc; pPKey2->eqSeen = 1; } }else if( res>0 ){ res = pPKey2->r2; }else{ res = pPKey2->r1; } } } assert( vdbeRecordCompareDebug(nKey1, pKey1, pPKey2, res) || CORRUPT_DB || pPKey2->pKeyInfo->db->mallocFailed ); |
︙ | ︙ |
Changes to src/walker.c.
︙ | ︙ | |||
59 60 61 62 63 64 65 66 67 68 69 | while(1){ rc = pWalker->xExprCallback(pWalker, pExpr); if( rc ) return rc & WRC_Abort; if( !ExprHasProperty(pExpr,(EP_TokenOnly|EP_Leaf)) ){ if( pExpr->pLeft && walkExpr(pWalker, pExpr->pLeft) ) return WRC_Abort; assert( pExpr->x.pList==0 || pExpr->pRight==0 ); if( pExpr->pRight ){ pExpr = pExpr->pRight; continue; }else if( ExprHasProperty(pExpr, EP_xIsSelect) ){ if( sqlite3WalkSelect(pWalker, pExpr->x.pSelect) ) return WRC_Abort; | > > > | | | | | | > | 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 | while(1){ rc = pWalker->xExprCallback(pWalker, pExpr); if( rc ) return rc & WRC_Abort; if( !ExprHasProperty(pExpr,(EP_TokenOnly|EP_Leaf)) ){ if( pExpr->pLeft && walkExpr(pWalker, pExpr->pLeft) ) return WRC_Abort; assert( pExpr->x.pList==0 || pExpr->pRight==0 ); if( pExpr->pRight ){ assert( !ExprHasProperty(pExpr, EP_WinFunc) ); pExpr = pExpr->pRight; continue; }else if( ExprHasProperty(pExpr, EP_xIsSelect) ){ assert( !ExprHasProperty(pExpr, EP_WinFunc) ); if( sqlite3WalkSelect(pWalker, pExpr->x.pSelect) ) return WRC_Abort; }else{ if( pExpr->x.pList ){ if( sqlite3WalkExprList(pWalker, pExpr->x.pList) ) return WRC_Abort; } #ifndef SQLITE_OMIT_WINDOWFUNC if( ExprHasProperty(pExpr, EP_WinFunc) ){ if( walkWindowList(pWalker, pExpr->y.pWin) ) return WRC_Abort; } #endif } } break; } return WRC_Continue; } int sqlite3WalkExpr(Walker *pWalker, Expr *pExpr){ return pExpr ? walkExpr(pWalker,pExpr) : WRC_Continue; |
︙ | ︙ |
Changes to src/window.c.
︙ | ︙ | |||
1192 1193 1194 1195 1196 1197 1198 | /* ** Attach window object pWin to expression p. */ void sqlite3WindowAttach(Parse *pParse, Expr *p, Window *pWin){ if( p ){ assert( p->op==TK_FUNCTION ); | < < < | | | | | | | < > | > > > | 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 | /* ** Attach window object pWin to expression p. */ void sqlite3WindowAttach(Parse *pParse, Expr *p, Window *pWin){ if( p ){ assert( p->op==TK_FUNCTION ); assert( pWin ); p->y.pWin = pWin; ExprSetProperty(p, EP_WinFunc); pWin->pOwner = p; if( (p->flags & EP_Distinct) && pWin->eFrmType!=TK_FILTER ){ sqlite3ErrorMsg(pParse, "DISTINCT is not supported for window functions" ); } }else{ sqlite3WindowDelete(pParse->db, pWin); } } /* ** Return 0 if the two window objects are identical, or non-zero otherwise. ** Identical window objects can be processed in a single scan. */ int sqlite3WindowCompare(Parse *pParse, Window *p1, Window *p2, int bFilter){ if( p1->eFrmType!=p2->eFrmType ) return 1; if( p1->eStart!=p2->eStart ) return 1; if( p1->eEnd!=p2->eEnd ) return 1; if( p1->eExclude!=p2->eExclude ) return 1; if( sqlite3ExprCompare(pParse, p1->pStart, p2->pStart, -1) ) return 1; if( sqlite3ExprCompare(pParse, p1->pEnd, p2->pEnd, -1) ) return 1; if( sqlite3ExprListCompare(p1->pPartition, p2->pPartition, -1) ) return 1; if( sqlite3ExprListCompare(p1->pOrderBy, p2->pOrderBy, -1) ) return 1; if( bFilter ){ if( sqlite3ExprCompare(pParse, p1->pFilter, p2->pFilter, -1) ) return 1; } return 0; } /* ** This is called by code in select.c before it calls sqlite3WhereBegin() ** to begin iterating through the sub-query results. It is used to allocate |
︙ | ︙ |
Added test/filter1.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 | # 2018 May 8 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. # set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix filter1 ifcapable !windowfunc { finish_test return } do_execsql_test 1.0 { CREATE TABLE t1(a); CREATE INDEX i1 ON t1(a); INSERT INTO t1 VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9); } do_execsql_test 1.1 { SELECT sum(a) FROM t1; } 45 do_execsql_test 1.2 { SELECT sum(a) FILTER( WHERE a<5 ) FROM t1; } 10 do_execsql_test 1.3 { SELECT sum(a) FILTER( WHERE a>9 ), sum(a) FILTER( WHERE a>8 ), sum(a) FILTER( WHERE a>7 ), sum(a) FILTER( WHERE a>6 ), sum(a) FILTER( WHERE a>5 ), sum(a) FILTER( WHERE a>4 ), sum(a) FILTER( WHERE a>3 ), sum(a) FILTER( WHERE a>2 ), sum(a) FILTER( WHERE a>1 ), sum(a) FILTER( WHERE a>0 ) FROM t1; } {{} 9 17 24 30 35 39 42 44 45} do_execsql_test 1.4 { SELECT max(a) FILTER (WHERE (a % 2)==0) FROM t1 } {8} do_execsql_test 1.5 { SELECT min(a) FILTER (WHERE a>4) FROM t1 } {5} do_execsql_test 1.6 { SELECT count(*) FILTER (WHERE a!=5) FROM t1 } {8} do_execsql_test 1.7 { SELECT min(a) FILTER (WHERE a>3) FROM t1 GROUP BY (a%2) ORDER BY 1; } {4 5} do_execsql_test 1.8 { CREATE VIEW vv AS SELECT sum(a) FILTER( WHERE a>9 ), sum(a) FILTER( WHERE a>8 ), sum(a) FILTER( WHERE a>7 ), sum(a) FILTER( WHERE a>6 ), sum(a) FILTER( WHERE a>5 ), sum(a) FILTER( WHERE a>4 ), sum(a) FILTER( WHERE a>3 ), sum(a) FILTER( WHERE a>2 ), sum(a) FILTER( WHERE a>1 ), sum(a) FILTER( WHERE a>0 ) FROM t1; SELECT * FROM vv; } {{} 9 17 24 30 35 39 42 44 45} #------------------------------------------------------------------------- # Test some errors: # # .1 FILTER on a non-aggregate function, # .2 Window function in FILTER clause, # .3 Aggregate function in FILTER clause, # reset_db do_execsql_test 2.0 { CREATE TABLE t1(a); INSERT INTO t1 VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9); } do_catchsql_test 2.1 { SELECT upper(a) FILTER (WHERE a=1) FROM t1 } {1 {filter clause may not be used with non-aggregate upper()}} do_catchsql_test 2.2 { SELECT sum(a) FILTER (WHERE 1 - max(a) OVER () > 0) FROM t1 } {1 {misuse of window function max()}} do_catchsql_test 2.3 { SELECT sum(a) FILTER (WHERE 1 - count(a)) FROM t1 } {1 {misuse of aggregate function count()}} finish_test |
Added test/filter2.tcl.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 | # 2018 May 19 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # source [file join [file dirname $argv0] pg_common.tcl] #========================================================================= start_test filter2 "2019 July 2" ifcapable !windowfunc execsql_test 1.0 { DROP TABLE IF EXISTS t1; CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER); INSERT INTO t1 VALUES (1, 7), (2, 3), (3, 5), (4, 30), (5, 26), (6, 23), (7, 27), (8, 3), (9, 17), (10, 26), (11, 33), (12, 25), (13, NULL), (14, 47), (15, 36), (16, 13), (17, 45), (18, 31), (19, 11), (20, 36), (21, 37), (22, 21), (23, 22), (24, 14), (25, 16), (26, 3), (27, 7), (28, 29), (29, 50), (30, 38), (31, 3), (32, 36), (33, 12), (34, 4), (35, 46), (36, 3), (37, 48), (38, 23), (39, NULL), (40, 24), (41, 5), (42, 46), (43, 11), (44, NULL), (45, 18), (46, 25), (47, 15), (48, 18), (49, 23); } execsql_test 1.1 { SELECT sum(b) FROM t1 } execsql_test 1.2 { SELECT sum(b) FILTER (WHERE a<10) FROM t1 } execsql_test 1.3 { SELECT count(DISTINCT b) FROM t1 } execsql_test 1.4 { SELECT count(DISTINCT b) FILTER (WHERE a!=19) FROM t1 } execsql_test 1.5 { SELECT min(b) FILTER (WHERE a>19), min(b) FILTER (WHERE a>0), max(a+b) FILTER (WHERE a>19), max(b+a) FILTER (WHERE a BETWEEN 10 AND 40) FROM t1; } execsql_test 1.6 { SELECT min(b), min(b), max(a+b), max(b+a) FROM t1 GROUP BY (a%10) ORDER BY 1, 2, 3, 4; } execsql_test 1.7 { SELECT min(b) FILTER (WHERE a>19), min(b) FILTER (WHERE a>0), max(a+b) FILTER (WHERE a>19), max(b+a) FILTER (WHERE a BETWEEN 10 AND 40) FROM t1 GROUP BY (a%10) ORDER BY 1, 2, 3, 4; } execsql_test 1.8 { SELECT sum(a+b) FILTER (WHERE a=NULL) FROM t1 } execsql_test 1.9 { SELECT (a%5) FROM t1 GROUP BY (a%5) HAVING sum(b) FILTER (WHERE b<20) > 34 ORDER BY 1 } execsql_test 1.10 { SELECT (a%5), sum(b) FILTER (WHERE b<20) AS bbb FROM t1 GROUP BY (a%5) HAVING sum(b) FILTER (WHERE b<20) >34 ORDER BY 1 } execsql_test 1.11 { SELECT (a%5), sum(b) FILTER (WHERE b<20) AS bbb FROM t1 GROUP BY (a%5) HAVING sum(b) FILTER (WHERE b<20) >34 ORDER BY 2 } execsql_test 1.12 { SELECT (a%5), sum(b) FILTER (WHERE b<20) AS bbb, count(distinct b) FILTER (WHERE b<20 OR a=13) AS ccc FROM t1 GROUP BY (a%5) ORDER BY 2 } execsql_test 1.13 { SELECT string_agg(CAST(b AS TEXT), '_') FILTER (WHERE b%2!=0), string_agg(CAST(b AS TEXT), '_') FILTER (WHERE b%2!=1), count(*) FILTER (WHERE b%2!=0), count(*) FILTER (WHERE b%2!=1) FROM t1; } execsql_float_test 1.14 { SELECT avg(b) FILTER (WHERE b>a), avg(b) FILTER (WHERE b<a) FROM t1 GROUP BY (a%2) ORDER BY 1,2; } execsql_test 1.15 { SELECT a/5, sum(b) FILTER (WHERE a%5=0), sum(b) FILTER (WHERE a%5=1), sum(b) FILTER (WHERE a%5=2), sum(b) FILTER (WHERE a%5=3), sum(b) FILTER (WHERE a%5=4) FROM t1 GROUP BY (a/5) ORDER BY 1; } finish_test |
Added test/filter2.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 | # 2019 July 2 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. # #################################################### # DO NOT EDIT! THIS FILE IS AUTOMATICALLY GENERATED! #################################################### set testdir [file dirname $argv0] source $testdir/tester.tcl set testprefix filter2 ifcapable !windowfunc { finish_test ; return } do_execsql_test 1.0 { DROP TABLE IF EXISTS t1; CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER); INSERT INTO t1 VALUES (1, 7), (2, 3), (3, 5), (4, 30), (5, 26), (6, 23), (7, 27), (8, 3), (9, 17), (10, 26), (11, 33), (12, 25), (13, NULL), (14, 47), (15, 36), (16, 13), (17, 45), (18, 31), (19, 11), (20, 36), (21, 37), (22, 21), (23, 22), (24, 14), (25, 16), (26, 3), (27, 7), (28, 29), (29, 50), (30, 38), (31, 3), (32, 36), (33, 12), (34, 4), (35, 46), (36, 3), (37, 48), (38, 23), (39, NULL), (40, 24), (41, 5), (42, 46), (43, 11), (44, NULL), (45, 18), (46, 25), (47, 15), (48, 18), (49, 23); } {} do_execsql_test 1.1 { SELECT sum(b) FROM t1 } {1041} do_execsql_test 1.2 { SELECT sum(b) FILTER (WHERE a<10) FROM t1 } {141} do_execsql_test 1.3 { SELECT count(DISTINCT b) FROM t1 } {31} do_execsql_test 1.4 { SELECT count(DISTINCT b) FILTER (WHERE a!=19) FROM t1 } {31} do_execsql_test 1.5 { SELECT min(b) FILTER (WHERE a>19), min(b) FILTER (WHERE a>0), max(a+b) FILTER (WHERE a>19), max(b+a) FILTER (WHERE a BETWEEN 10 AND 40) FROM t1; } {3 3 88 85} do_execsql_test 1.6 { SELECT min(b), min(b), max(a+b), max(b+a) FROM t1 GROUP BY (a%10) ORDER BY 1, 2, 3, 4; } {3 3 58 58 3 3 66 66 3 3 71 71 3 3 88 88 4 4 61 61 5 5 54 54 7 7 85 85 11 11 79 79 16 16 81 81 24 24 68 68} do_execsql_test 1.7 { SELECT min(b) FILTER (WHERE a>19), min(b) FILTER (WHERE a>0), max(a+b) FILTER (WHERE a>19), max(b+a) FILTER (WHERE a BETWEEN 10 AND 40) FROM t1 GROUP BY (a%10) ORDER BY 1, 2, 3, 4; } {3 3 58 58 3 3 71 39 4 4 38 61 7 7 85 85 11 5 54 45 16 16 81 81 18 3 66 61 21 3 88 68 23 11 79 79 24 24 68 68} do_execsql_test 1.8 { SELECT sum(a+b) FILTER (WHERE a=NULL) FROM t1 } {{}} do_execsql_test 1.9 { SELECT (a%5) FROM t1 GROUP BY (a%5) HAVING sum(b) FILTER (WHERE b<20) > 34 ORDER BY 1 } {3 4} do_execsql_test 1.10 { SELECT (a%5), sum(b) FILTER (WHERE b<20) AS bbb FROM t1 GROUP BY (a%5) HAVING sum(b) FILTER (WHERE b<20) >34 ORDER BY 1 } {3 49 4 46} do_execsql_test 1.11 { SELECT (a%5), sum(b) FILTER (WHERE b<20) AS bbb FROM t1 GROUP BY (a%5) HAVING sum(b) FILTER (WHERE b<20) >34 ORDER BY 2 } {4 46 3 49} do_execsql_test 1.12 { SELECT (a%5), sum(b) FILTER (WHERE b<20) AS bbb, count(distinct b) FILTER (WHERE b<20 OR a=13) AS ccc FROM t1 GROUP BY (a%5) ORDER BY 2 } {2 25 3 0 34 2 1 34 4 4 46 4 3 49 5} do_execsql_test 1.13 { SELECT group_concat(CAST(b AS TEXT), '_') FILTER (WHERE b%2!=0), group_concat(CAST(b AS TEXT), '_') FILTER (WHERE b%2!=1), count(*) FILTER (WHERE b%2!=0), count(*) FILTER (WHERE b%2!=1) FROM t1; } {7_3_5_23_27_3_17_33_25_47_13_45_31_11_37_21_3_7_29_3_3_23_5_11_25_15_23 30_26_26_36_36_22_14_16_50_38_36_12_4_46_48_24_46_18_18 27 19} do_test 1.14 { set myres {} foreach r [db eval {SELECT avg(b) FILTER (WHERE b>a), avg(b) FILTER (WHERE b<a) FROM t1 GROUP BY (a%2) ORDER BY 1,2;}] { lappend myres [format %.4f [set r]] } set res2 {30.8333 13.7273 31.4167 13.0000} set i 0 foreach r [set myres] r2 [set res2] { if {[set r]<([set r2]-0.0001) || [set r]>([set r2]+0.0001)} { error "list element [set i] does not match: got=[set r] expected=[set r2]" } incr i } set {} {} } {} do_execsql_test 1.15 { SELECT a/5, sum(b) FILTER (WHERE a%5=0), sum(b) FILTER (WHERE a%5=1), sum(b) FILTER (WHERE a%5=2), sum(b) FILTER (WHERE a%5=3), sum(b) FILTER (WHERE a%5=4) FROM t1 GROUP BY (a/5) ORDER BY 1; } {0 {} 7 3 5 30 1 26 23 27 3 17 2 26 33 25 {} 47 3 36 13 45 31 11 4 36 37 21 22 14 5 16 3 7 29 50 6 38 3 36 12 4 7 46 3 48 23 {} 8 24 5 46 11 {} 9 18 25 15 18 23} finish_test |
Changes to test/window2.tcl.
︙ | ︙ | |||
420 421 422 423 424 425 426 427 428 429 430 431 | execsql_float_test 4.9 { SELECT rank() OVER win AS rank, cume_dist() OVER win AS cume_dist FROM t1 WINDOW win AS (ORDER BY 1); } finish_test | > > > > > > > | 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 | execsql_float_test 4.9 { SELECT rank() OVER win AS rank, cume_dist() OVER win AS cume_dist FROM t1 WINDOW win AS (ORDER BY 1); } execsql_test 4.10 { SELECT count(*) OVER (ORDER BY b) FROM t1 } execsql_test 4.11 { SELECT count(distinct a) FILTER (WHERE b='odd') FROM t1 } finish_test |
Changes to test/window2.test.
︙ | ︙ | |||
888 889 890 891 892 893 894 895 896 | if {[set r]<([set r2]-0.0001) || [set r]>([set r2]+0.0001)} { error "list element [set i] does not match: got=[set r] expected=[set r2]" } incr i } set {} {} } {} finish_test | > > > > > > > > | 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 | if {[set r]<([set r2]-0.0001) || [set r]>([set r2]+0.0001)} { error "list element [set i] does not match: got=[set r] expected=[set r2]" } incr i } set {} {} } {} do_execsql_test 4.10 { SELECT count(*) OVER (ORDER BY b) FROM t1 } {3 3 3 6 6 6} do_execsql_test 4.11 { SELECT count(distinct a) FILTER (WHERE b='odd') FROM t1 } {3} finish_test |