Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Experimental implementation of FILTER clause for aggregate functions. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | filter-clause |
Files: | files | file ages | folders |
SHA3-256: |
1f1ae2d6ac8dcbb62e5aa3dc17bc67d5 |
User & Date: | dan 2019-07-02 11:56:47.637 |
Context
2019-07-03
| ||
18:31 | Add tests for the FILTER clause. And a bugfix. (check-in: 28aa1702f7 user: dan tags: filter-clause) | |
2019-07-02
| ||
11:56 | Experimental implementation of FILTER clause for aggregate functions. (check-in: 1f1ae2d6ac user: dan tags: filter-clause) | |
2019-06-28
| ||
07:08 | Use the OP_Sequence opcode for generating unique rowid values for an autoindex on a co-routine implementation of a subquery. (check-in: eab4297577 user: drh tags: trunk) | |
Changes
Changes to src/expr.c.
︙ | ︙ | |||
1023 1024 1025 1026 1027 1028 1029 | static SQLITE_NOINLINE void sqlite3ExprDeleteNN(sqlite3 *db, Expr *p){ assert( p!=0 ); /* Sanity check: Assert that the IntValue is non-negative if it exists */ assert( !ExprHasProperty(p, EP_IntValue) || p->u.iValue>=0 ); assert( !ExprHasProperty(p, EP_WinFunc) || p->y.pWin!=0 || db->mallocFailed ); assert( p->op!=TK_FUNCTION || ExprHasProperty(p, EP_TokenOnly|EP_Reduced) | | > | > > > > | 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 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 1062 1063 1064 | static SQLITE_NOINLINE void sqlite3ExprDeleteNN(sqlite3 *db, Expr *p){ assert( p!=0 ); /* Sanity check: Assert that the IntValue is non-negative if it exists */ assert( !ExprHasProperty(p, EP_IntValue) || p->u.iValue>=0 ); assert( !ExprHasProperty(p, EP_WinFunc) || p->y.pWin!=0 || db->mallocFailed ); assert( p->op!=TK_FUNCTION || ExprHasProperty(p, EP_TokenOnly|EP_Reduced) || p->y.pWin==0 || ExprHasProperty(p, EP_WinFunc|EP_Filter) ); #ifdef SQLITE_DEBUG if( ExprHasProperty(p, EP_Leaf) && !ExprHasProperty(p, EP_TokenOnly) ){ assert( p->pLeft==0 ); assert( p->pRight==0 ); assert( p->x.pSelect==0 ); } #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); } #ifndef SQLITE_OMIT_WINDOWFUNC if( ExprHasProperty(p, EP_WinFunc) ){ assert( p->op==TK_FUNCTION && !ExprHasProperty(p, EP_Filter) ); sqlite3WindowDelete(db, p->y.pWin); }else if( ExprHasProperty(p, EP_Filter) ){ assert( p->op==TK_FUNCTION || p->op==TK_AGG_FUNCTION ); sqlite3ExprDelete(db, p->y.pFilter); } #endif } if( ExprHasProperty(p, EP_MemToken) ) sqlite3DbFree(db, p->u.zToken); if( !ExprHasProperty(p, EP_Static) ){ sqlite3DbFreeNN(db, p); } } void sqlite3ExprDelete(sqlite3 *db, Expr *p){ |
︙ | ︙ | |||
1260 1261 1262 1263 1264 1265 1266 | pNew->x.pSelect = sqlite3SelectDup(db, p->x.pSelect, dupFlags); }else{ pNew->x.pList = sqlite3ExprListDup(db, p->x.pList, dupFlags); } } /* Fill in pNew->pLeft and pNew->pRight. */ | | > > > > | 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 | pNew->x.pSelect = sqlite3SelectDup(db, p->x.pSelect, dupFlags); }else{ pNew->x.pList = sqlite3ExprListDup(db, p->x.pList, dupFlags); } } /* Fill in pNew->pLeft and pNew->pRight. */ if( ExprHasProperty(pNew, EP_Reduced|EP_TokenOnly|EP_WinFunc|EP_Filter) ){ zAlloc += dupedExprNodeSize(p, dupFlags); if( !ExprHasProperty(pNew, EP_TokenOnly|EP_Leaf) ){ pNew->pLeft = p->pLeft ? exprDup(db, p->pLeft, EXPRDUP_REDUCE, &zAlloc) : 0; pNew->pRight = p->pRight ? exprDup(db, p->pRight, EXPRDUP_REDUCE, &zAlloc) : 0; } #ifndef SQLITE_OMIT_WINDOWFUNC if( ExprHasProperty(p, EP_WinFunc) ){ pNew->y.pWin = sqlite3WindowDup(db, pNew, p->y.pWin); assert( ExprHasProperty(pNew, EP_WinFunc) ); } if( ExprHasProperty(p, EP_Filter) ){ pNew->y.pFilter = sqlite3ExprDup(db, p->y.pFilter, 0); assert( ExprHasProperty(pNew, EP_Filter) ); } #endif /* SQLITE_OMIT_WINDOWFUNC */ if( pzBuffer ){ *pzBuffer = zAlloc; } }else{ if( !ExprHasProperty(p, EP_TokenOnly|EP_Leaf) ){ if( pNew->op==TK_SELECT_COLUMN ){ |
︙ | ︙ | |||
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){ | | | | 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 | /* ** 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 ); 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; |
︙ | ︙ | |||
4858 4859 4860 4861 4862 4863 4864 4865 4866 4867 4868 4869 4870 4871 | }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 ){ return 2; } } if( (pA->flags & EP_Distinct)!=(pB->flags & EP_Distinct) ) return 2; if( (combinedFlags & EP_TokenOnly)==0 ){ if( combinedFlags & EP_xIsSelect ) return 2; if( (combinedFlags & EP_FixedCol)==0 && sqlite3ExprCompare(pParse, pA->pLeft, pB->pLeft, iTab) ) return 2; if( sqlite3ExprCompare(pParse, pA->pRight, pB->pRight, iTab) ) return 2; | > > > > > > > > | 4867 4868 4869 4870 4871 4872 4873 4874 4875 4876 4877 4878 4879 4880 4881 4882 4883 4884 4885 4886 4887 4888 | }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 ){ return 2; } #ifndef SQLITE_OMIT_WINDOWFUNC else if( pA->op==TK_AGG_FUNCTION ){ assert( ExprHasProperty(pA, EP_WinFunc)==0 ); if( sqlite3ExprCompare(pParse, pA->y.pFilter, pB->y.pFilter, iTab) ){ return 2; } } #endif } if( (pA->flags & EP_Distinct)!=(pB->flags & EP_Distinct) ) return 2; if( (combinedFlags & EP_TokenOnly)==0 ){ if( combinedFlags & EP_xIsSelect ) return 2; if( (combinedFlags & EP_FixedCol)==0 && sqlite3ExprCompare(pParse, pA->pLeft, pB->pLeft, iTab) ) return 2; if( sqlite3ExprCompare(pParse, pA->pRight, pB->pRight, iTab) ) return 2; |
︙ | ︙ |
Changes to src/parse.y.
︙ | ︙ | |||
1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 | expr(A) ::= CAST LP expr(E) AS typetoken(T) RP. { A = sqlite3ExprAlloc(pParse->db, TK_CAST, &T, 1); sqlite3ExprAttachSubtrees(pParse->db, A, E, 0); } %endif SQLITE_OMIT_CAST expr(A) ::= id(X) LP distinct(D) exprlist(Y) RP. { A = sqlite3ExprFunction(pParse, Y, &X, D); } expr(A) ::= id(X) LP STAR RP. { A = sqlite3ExprFunction(pParse, 0, &X, 0); } %ifndef SQLITE_OMIT_WINDOWFUNC | > > | | | | | 1032 1033 1034 1035 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 1062 | expr(A) ::= CAST LP expr(E) AS typetoken(T) RP. { A = sqlite3ExprAlloc(pParse->db, TK_CAST, &T, 1); sqlite3ExprAttachSubtrees(pParse->db, A, E, 0); } %endif SQLITE_OMIT_CAST %ifdef SQLITE_OMIT_WINDOWFUNC expr(A) ::= id(X) LP distinct(D) exprlist(Y) RP. { A = sqlite3ExprFunction(pParse, Y, &X, D); } expr(A) ::= id(X) LP STAR RP. { A = sqlite3ExprFunction(pParse, 0, &X, 0); } %endif %ifndef SQLITE_OMIT_WINDOWFUNC expr(A) ::= id(X) LP distinct(D) exprlist(Y) RP filter_opt(F) over_opt(Z). { A = sqlite3ExprFunction(pParse, Y, &X, D); sqlite3WindowAttach(pParse, A, F, Z); } expr(A) ::= id(X) LP STAR RP filter_opt(F) over_opt(Z). { A = sqlite3ExprFunction(pParse, 0, &X, 0); sqlite3WindowAttach(pParse, A, F, Z); } %endif term(A) ::= CTIME_KW(OP). { A = sqlite3ExprFunction(pParse, 0, &OP, 0); } |
︙ | ︙ | |||
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; } | | | > | < | < < < | 1722 1723 1724 1725 1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736 1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 | 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; } %type over_opt {Window*} %destructor over_opt {sqlite3WindowDelete(pParse->db, $$);} over_opt(A) ::= . { A=0; } over_opt(A) ::= OVER LP window(Z) RP. { A = Z; assert( A!=0 ); } over_opt(A) ::= OVER nm(Z). { A = (Window*)sqlite3DbMallocZero(pParse->db, sizeof(Window)); if( A ){ A->zName = sqlite3DbStrNDup(pParse->db, Z.z, Z.n); } } filter_opt(A) ::= . { A = 0; } filter_opt(A) ::= FILTER LP WHERE expr(X) RP. { A = X; } %endif /* SQLITE_OMIT_WINDOWFUNC */ |
︙ | ︙ |
Changes to src/resolve.c.
︙ | ︙ | |||
845 846 847 848 849 850 851 852 853 854 855 856 857 858 | zType = "window"; }else{ zType = "aggregate"; } sqlite3ErrorMsg(pParse, "misuse of %s function %.*s()",zType,nId,zId); pNC->nErr++; is_agg = 0; } #else if( (is_agg && (pNC->ncFlags & NC_AllowAgg)==0) ){ sqlite3ErrorMsg(pParse,"misuse of aggregate function %.*s()",nId,zId); pNC->nErr++; is_agg = 0; } | > > > > > > | 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 | 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_Filter) ){ 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; } |
︙ | ︙ | |||
879 880 881 882 883 884 885 | pNC->ncFlags &= ~NC_AllowAgg; #endif } } sqlite3WalkExprList(pWalker, pList); if( is_agg ){ #ifndef SQLITE_OMIT_WINDOWFUNC | | > > > | 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 | pNC->ncFlags &= ~NC_AllowAgg; #endif } } sqlite3WalkExprList(pWalker, pList); if( is_agg ){ #ifndef SQLITE_OMIT_WINDOWFUNC if( ExprHasProperty(pExpr, EP_WinFunc) ){ Select *pSel = pNC->pWinSelect; if( IN_RENAME_OBJECT==0 ){ sqlite3WindowUpdate(pParse, pSel->pWinDefn, pExpr->y.pWin, pDef); } sqlite3WalkExprList(pWalker, pExpr->y.pWin->pPartition); sqlite3WalkExprList(pWalker, pExpr->y.pWin->pOrderBy); sqlite3WalkExpr(pWalker, pExpr->y.pWin->pFilter); if( 0==pSel->pWin || 0==sqlite3WindowCompare(pParse, pSel->pWin, pExpr->y.pWin) ){ 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 sqlite3WalkExpr(pWalker, pExpr->y.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.
︙ | ︙ | |||
4402 4403 4404 4405 4406 4407 4408 | 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 ); | | > > | 4402 4403 4404 4405 4406 4407 4408 4409 4410 4411 4412 4413 4414 4415 4416 4417 4418 | 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 ); if( pEList==0 || pEList->nExpr!=1 || ExprHasProperty(pFunc, EP_Filter) ){ 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; |
︙ | ︙ | |||
4449 4450 4451 4452 4453 4454 4455 | 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; | | | 4451 4452 4453 4454 4455 4456 4457 4458 4459 4460 4461 4462 4463 4464 4465 | 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_Filter) ) 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 |
︙ | ︙ | |||
5329 5330 5331 5332 5333 5334 5335 5336 5337 5338 5339 5340 5341 5342 5343 5344 | 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 ){ | > > > > > > | > | 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 5361 | 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( ExprHasProperty(pF->pExpr, EP_Filter) ){ Expr *pFilter = pF->pExpr->y.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; |
︙ | ︙ | |||
6221 6222 6223 6224 6225 6226 6227 | 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++){ | > | | > > > > | 6230 6231 6232 6233 6234 6235 6236 6237 6238 6239 6240 6241 6242 6243 6244 6245 6246 6247 6248 6249 6250 6251 | 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( !ExprHasProperty(pExpr, EP_WinFunc) ); sqlite3ExprAnalyzeAggregates(&sNC, pExpr->y.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 2502 | 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 definition for the func */ Expr *pFilter; /* EP_Filter: Filter definition for the func */ struct { /* TK_IN, TK_SELECT, and TK_EXISTS */ int iAddr; /* Subroutine entry address */ int regReturn; /* Register used to hold return address */ } sub; } y; }; |
︙ | ︙ | |||
2532 2533 2534 2535 2536 2537 2538 2539 2540 2541 2542 2543 2544 2545 | #define EP_Leaf 0x800000 /* Expr.pLeft, .pRight, .u.pSelect all NULL */ #define EP_WinFunc 0x1000000 /* TK_FUNCTION with Expr.y.pWin set */ #define EP_Subrtn 0x2000000 /* Uses Expr.y.sub. TK_IN, _SELECT, or _EXISTS */ #define EP_Quoted 0x4000000 /* TK_ID was originally quoted */ #define EP_Static 0x8000000 /* Held in memory not obtained from malloc() */ #define EP_IsTrue 0x10000000 /* Always has boolean value of TRUE */ #define EP_IsFalse 0x20000000 /* Always has boolean value of FALSE */ /* ** The EP_Propagate mask is a set of properties that automatically propagate ** upwards into parent nodes. */ #define EP_Propagate (EP_Collate|EP_Subquery|EP_HasFunc) | > | 2533 2534 2535 2536 2537 2538 2539 2540 2541 2542 2543 2544 2545 2546 2547 | #define EP_Leaf 0x800000 /* Expr.pLeft, .pRight, .u.pSelect all NULL */ #define EP_WinFunc 0x1000000 /* TK_FUNCTION with Expr.y.pWin set */ #define EP_Subrtn 0x2000000 /* Uses Expr.y.sub. TK_IN, _SELECT, or _EXISTS */ #define EP_Quoted 0x4000000 /* TK_ID was originally quoted */ #define EP_Static 0x8000000 /* Held in memory not obtained from malloc() */ #define EP_IsTrue 0x10000000 /* Always has boolean value of TRUE */ #define EP_IsFalse 0x20000000 /* Always has boolean value of FALSE */ #define EP_Filter 0x40000000 /* TK_[AGG_]FUNCTION with Expr.y.pFilter set */ /* ** The EP_Propagate mask is a set of properties that automatically propagate ** upwards into parent nodes. */ #define EP_Propagate (EP_Collate|EP_Subquery|EP_HasFunc) |
︙ | ︙ | |||
3598 3599 3600 3601 3602 3603 3604 | int regEndRowid; }; #ifndef SQLITE_OMIT_WINDOWFUNC void sqlite3WindowDelete(sqlite3*, Window*); void sqlite3WindowListDelete(sqlite3 *db, Window *p); Window *sqlite3WindowAlloc(Parse*, int, int, Expr*, int , Expr*, u8); | | | | 3600 3601 3602 3603 3604 3605 3606 3607 3608 3609 3610 3611 3612 3613 3614 3615 3616 3617 3618 3619 3620 3621 3622 3623 3624 3625 3626 3627 3628 3629 | int regEndRowid; }; #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*, Expr*, Window*); int sqlite3WindowCompare(Parse*, Window*, Window*); 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); void sqlite3WindowFunctions(void); void sqlite3WindowChain(Parse*, Window*, Window*); Window *sqlite3WindowAssemble(Parse*, Window*, ExprList*, ExprList*, Token*); #else # define sqlite3WindowDelete(a,b) # define sqlite3WindowFunctions() # define sqlite3WindowAttach(a,b,c,d) #endif /* ** Assuming zIn points to the first byte of a UTF-8 character, ** advance zIn to point to the first byte of the next UTF-8 character. */ #define SQLITE_SKIP_UTF8(zIn) { \ |
︙ | ︙ |
Changes to src/walker.c.
︙ | ︙ | |||
70 71 72 73 74 75 76 77 78 79 80 81 82 83 | }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){ | > > > | 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 | }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; } if( ExprHasProperty(pExpr, EP_Filter) ){ if( walkExpr(pWalker, pExpr->y.pFilter) ) return WRC_Abort; } #endif } break; } return WRC_Continue; } int sqlite3WalkExpr(Walker *pWalker, Expr *pExpr){ |
︙ | ︙ |
Changes to src/window.c.
︙ | ︙ | |||
1174 1175 1176 1177 1178 1179 1180 | } } } /* ** Attach window object pWin to expression p. */ | | < < < | > > > > > | 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 | } } } /* ** Attach window object pWin to expression p. */ void sqlite3WindowAttach(Parse *pParse, Expr *p, Expr *pFilter, Window *pWin){ if( p ){ assert( p->op==TK_FUNCTION ); if( pWin ){ p->y.pWin = pWin; ExprSetProperty(p, EP_WinFunc); pWin->pOwner = p; if( p->flags & EP_Distinct ){ sqlite3ErrorMsg(pParse, "DISTINCT is not supported for window functions"); } pWin->pFilter = pFilter; }else if( pFilter ){ p->y.pFilter = pFilter; ExprSetProperty(p, EP_Filter); } }else{ sqlite3WindowDelete(pParse->db, pWin); sqlite3ExprDelete(pParse->db, pFilter); } } /* ** Return 0 if the two window objects are identical, or non-zero otherwise. ** Identical window objects can be processed in a single scan. */ |
︙ | ︙ |
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 | # 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.6 { SELECT min(a) FILTER (WHERE a>3) FROM t1 GROUP BY (a%2) ORDER BY 1; } {4 5} #------------------------------------------------------------------------- # 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: 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 | # 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; } 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 | # 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} finish_test |