/ Check-in [1f1ae2d6]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Experimental implementation of FILTER clause for aggregate functions.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | filter-clause
Files: files | file ages | folders
SHA3-256: 1f1ae2d6ac8dcbb62e5aa3dc17bc67d559cb565fc0d0a8c00a596075d35f8130
User & Date: dan 2019-07-02 11:56:47
Context
2019-07-03
18:31
Add tests for the FILTER clause. And a bugfix. check-in: 28aa1702 user: dan tags: filter-clause
2019-07-02
11:56
Experimental implementation of FILTER clause for aggregate functions. check-in: 1f1ae2d6 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: eab42975 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/expr.c.

  1023   1023   static SQLITE_NOINLINE void sqlite3ExprDeleteNN(sqlite3 *db, Expr *p){
  1024   1024     assert( p!=0 );
  1025   1025     /* Sanity check: Assert that the IntValue is non-negative if it exists */
  1026   1026     assert( !ExprHasProperty(p, EP_IntValue) || p->u.iValue>=0 );
  1027   1027   
  1028   1028     assert( !ExprHasProperty(p, EP_WinFunc) || p->y.pWin!=0 || db->mallocFailed );
  1029   1029     assert( p->op!=TK_FUNCTION || ExprHasProperty(p, EP_TokenOnly|EP_Reduced)
  1030         -          || p->y.pWin==0 || ExprHasProperty(p, EP_WinFunc) );
         1030  +          || p->y.pWin==0 || ExprHasProperty(p, EP_WinFunc|EP_Filter) );
  1031   1031   #ifdef SQLITE_DEBUG
  1032   1032     if( ExprHasProperty(p, EP_Leaf) && !ExprHasProperty(p, EP_TokenOnly) ){
  1033   1033       assert( p->pLeft==0 );
  1034   1034       assert( p->pRight==0 );
  1035   1035       assert( p->x.pSelect==0 );
  1036   1036     }
  1037   1037   #endif
................................................................................
  1042   1042       if( p->pRight ){
  1043   1043         sqlite3ExprDeleteNN(db, p->pRight);
  1044   1044       }else if( ExprHasProperty(p, EP_xIsSelect) ){
  1045   1045         sqlite3SelectDelete(db, p->x.pSelect);
  1046   1046       }else{
  1047   1047         sqlite3ExprListDelete(db, p->x.pList);
  1048   1048       }
         1049  +#ifndef SQLITE_OMIT_WINDOWFUNC
  1049   1050       if( ExprHasProperty(p, EP_WinFunc) ){
  1050         -      assert( p->op==TK_FUNCTION );
         1051  +      assert( p->op==TK_FUNCTION && !ExprHasProperty(p, EP_Filter) );
  1051   1052         sqlite3WindowDelete(db, p->y.pWin);
         1053  +    }else if( ExprHasProperty(p, EP_Filter) ){
         1054  +      assert( p->op==TK_FUNCTION || p->op==TK_AGG_FUNCTION );
         1055  +      sqlite3ExprDelete(db, p->y.pFilter);
  1052   1056       }
         1057  +#endif
  1053   1058     }
  1054   1059     if( ExprHasProperty(p, EP_MemToken) ) sqlite3DbFree(db, p->u.zToken);
  1055   1060     if( !ExprHasProperty(p, EP_Static) ){
  1056   1061       sqlite3DbFreeNN(db, p);
  1057   1062     }
  1058   1063   }
  1059   1064   void sqlite3ExprDelete(sqlite3 *db, Expr *p){
................................................................................
  1260   1265           pNew->x.pSelect = sqlite3SelectDup(db, p->x.pSelect, dupFlags);
  1261   1266         }else{
  1262   1267           pNew->x.pList = sqlite3ExprListDup(db, p->x.pList, dupFlags);
  1263   1268         }
  1264   1269       }
  1265   1270   
  1266   1271       /* Fill in pNew->pLeft and pNew->pRight. */
  1267         -    if( ExprHasProperty(pNew, EP_Reduced|EP_TokenOnly|EP_WinFunc) ){
         1272  +    if( ExprHasProperty(pNew, EP_Reduced|EP_TokenOnly|EP_WinFunc|EP_Filter) ){
  1268   1273         zAlloc += dupedExprNodeSize(p, dupFlags);
  1269   1274         if( !ExprHasProperty(pNew, EP_TokenOnly|EP_Leaf) ){
  1270   1275           pNew->pLeft = p->pLeft ?
  1271   1276                         exprDup(db, p->pLeft, EXPRDUP_REDUCE, &zAlloc) : 0;
  1272   1277           pNew->pRight = p->pRight ?
  1273   1278                          exprDup(db, p->pRight, EXPRDUP_REDUCE, &zAlloc) : 0;
  1274   1279         }
  1275   1280   #ifndef SQLITE_OMIT_WINDOWFUNC
  1276   1281         if( ExprHasProperty(p, EP_WinFunc) ){
  1277   1282           pNew->y.pWin = sqlite3WindowDup(db, pNew, p->y.pWin);
  1278   1283           assert( ExprHasProperty(pNew, EP_WinFunc) );
  1279   1284         }
         1285  +      if( ExprHasProperty(p, EP_Filter) ){
         1286  +        pNew->y.pFilter = sqlite3ExprDup(db, p->y.pFilter, 0);
         1287  +        assert( ExprHasProperty(pNew, EP_Filter) );
         1288  +      }
  1280   1289   #endif /* SQLITE_OMIT_WINDOWFUNC */
  1281   1290         if( pzBuffer ){
  1282   1291           *pzBuffer = zAlloc;
  1283   1292         }
  1284   1293       }else{
  1285   1294         if( !ExprHasProperty(p, EP_TokenOnly|EP_Leaf) ){
  1286   1295           if( pNew->op==TK_SELECT_COLUMN ){
................................................................................
  1328   1337   /*
  1329   1338   ** The gatherSelectWindows() procedure and its helper routine
  1330   1339   ** gatherSelectWindowsCallback() are used to scan all the expressions
  1331   1340   ** an a newly duplicated SELECT statement and gather all of the Window
  1332   1341   ** objects found there, assembling them onto the linked list at Select->pWin.
  1333   1342   */
  1334   1343   static int gatherSelectWindowsCallback(Walker *pWalker, Expr *pExpr){
  1335         -  if( pExpr->op==TK_FUNCTION && pExpr->y.pWin!=0 ){
  1336         -    assert( ExprHasProperty(pExpr, EP_WinFunc) );
         1344  +  if( pExpr->op==TK_FUNCTION && ExprHasProperty(pExpr, EP_WinFunc) ){
         1345  +    assert( pExpr->y.pWin );
  1337   1346       pExpr->y.pWin->pNextWin = pWalker->u.pSelect->pWin;
  1338   1347       pWalker->u.pSelect->pWin = pExpr->y.pWin;
  1339   1348     }
  1340   1349     return WRC_Continue;
  1341   1350   }
  1342   1351   static int gatherSelectWindowsSelectCallback(Walker *pWalker, Select *p){
  1343   1352     return p==pWalker->u.pSelect ? WRC_Continue : WRC_Prune;
................................................................................
  4858   4867       }else if( pA->op==TK_NULL ){
  4859   4868         return 0;
  4860   4869       }else if( pA->op==TK_COLLATE ){
  4861   4870         if( sqlite3_stricmp(pA->u.zToken,pB->u.zToken)!=0 ) return 2;
  4862   4871       }else if( ALWAYS(pB->u.zToken!=0) && strcmp(pA->u.zToken,pB->u.zToken)!=0 ){
  4863   4872         return 2;
  4864   4873       }
         4874  +#ifndef SQLITE_OMIT_WINDOWFUNC
         4875  +    else if( pA->op==TK_AGG_FUNCTION ){
         4876  +      assert( ExprHasProperty(pA, EP_WinFunc)==0 );
         4877  +      if( sqlite3ExprCompare(pParse, pA->y.pFilter, pB->y.pFilter, iTab) ){
         4878  +        return 2;
         4879  +      }
         4880  +    }
         4881  +#endif
  4865   4882     }
  4866   4883     if( (pA->flags & EP_Distinct)!=(pB->flags & EP_Distinct) ) return 2;
  4867   4884     if( (combinedFlags & EP_TokenOnly)==0 ){
  4868   4885       if( combinedFlags & EP_xIsSelect ) return 2;
  4869   4886       if( (combinedFlags & EP_FixedCol)==0
  4870   4887        && sqlite3ExprCompare(pParse, pA->pLeft, pB->pLeft, iTab) ) return 2;
  4871   4888       if( sqlite3ExprCompare(pParse, pA->pRight, pB->pRight, iTab) ) return 2;

Changes to src/parse.y.

  1032   1032   expr(A) ::= CAST LP expr(E) AS typetoken(T) RP. {
  1033   1033     A = sqlite3ExprAlloc(pParse->db, TK_CAST, &T, 1);
  1034   1034     sqlite3ExprAttachSubtrees(pParse->db, A, E, 0);
  1035   1035   }
  1036   1036   %endif  SQLITE_OMIT_CAST
  1037   1037   
  1038   1038   
         1039  +%ifdef SQLITE_OMIT_WINDOWFUNC
  1039   1040   expr(A) ::= id(X) LP distinct(D) exprlist(Y) RP. {
  1040   1041     A = sqlite3ExprFunction(pParse, Y, &X, D);
  1041   1042   }
  1042   1043   expr(A) ::= id(X) LP STAR RP. {
  1043   1044     A = sqlite3ExprFunction(pParse, 0, &X, 0);
  1044   1045   }
         1046  +%endif
  1045   1047   
  1046   1048   %ifndef SQLITE_OMIT_WINDOWFUNC
  1047         -expr(A) ::= id(X) LP distinct(D) exprlist(Y) RP over_clause(Z). {
         1049  +expr(A) ::= id(X) LP distinct(D) exprlist(Y) RP filter_opt(F) over_opt(Z). {
  1048   1050     A = sqlite3ExprFunction(pParse, Y, &X, D);
  1049         -  sqlite3WindowAttach(pParse, A, Z);
         1051  +  sqlite3WindowAttach(pParse, A, F, Z);
  1050   1052   }
  1051         -expr(A) ::= id(X) LP STAR RP over_clause(Z). {
         1053  +expr(A) ::= id(X) LP STAR RP filter_opt(F) over_opt(Z). {
  1052   1054     A = sqlite3ExprFunction(pParse, 0, &X, 0);
  1053         -  sqlite3WindowAttach(pParse, A, Z);
         1055  +  sqlite3WindowAttach(pParse, A, F, Z);
  1054   1056   }
  1055   1057   %endif
  1056   1058   
  1057   1059   term(A) ::= CTIME_KW(OP). {
  1058   1060     A = sqlite3ExprFunction(pParse, 0, &OP, 0);
  1059   1061   }
  1060   1062   
................................................................................
  1720   1722   frame_exclude(A) ::= GROUP|TIES(X).  {A = @X; /*A-overwrites-X*/}
  1721   1723   
  1722   1724   
  1723   1725   %type window_clause {Window*}
  1724   1726   %destructor window_clause {sqlite3WindowListDelete(pParse->db, $$);}
  1725   1727   window_clause(A) ::= WINDOW windowdefn_list(B). { A = B; }
  1726   1728   
  1727         -%type over_clause {Window*}
  1728         -%destructor over_clause {sqlite3WindowDelete(pParse->db, $$);}
  1729         -over_clause(A) ::= filter_opt(W) OVER LP window(Z) RP. {
         1729  +%type over_opt {Window*}
         1730  +%destructor over_opt {sqlite3WindowDelete(pParse->db, $$);}
         1731  +over_opt(A) ::= . { A=0; }
         1732  +over_opt(A) ::= OVER LP window(Z) RP. {
  1730   1733     A = Z;
  1731   1734     assert( A!=0 );
  1732         -  A->pFilter = W;
  1733   1735   }
  1734         -over_clause(A) ::= filter_opt(W) OVER nm(Z). {
         1736  +over_opt(A) ::= OVER nm(Z). {
  1735   1737     A = (Window*)sqlite3DbMallocZero(pParse->db, sizeof(Window));
  1736   1738     if( A ){
  1737   1739       A->zName = sqlite3DbStrNDup(pParse->db, Z.z, Z.n);
  1738         -    A->pFilter = W;
  1739         -  }else{
  1740         -    sqlite3ExprDelete(pParse->db, W);
  1741   1740     }
  1742   1741   }
  1743   1742   
  1744   1743   filter_opt(A) ::= .                            { A = 0; }
  1745   1744   filter_opt(A) ::= FILTER LP WHERE expr(X) RP.  { A = X; }
  1746   1745   %endif /* SQLITE_OMIT_WINDOWFUNC */
  1747   1746   

Changes to src/resolve.c.

   845    845               zType = "window";
   846    846             }else{
   847    847               zType = "aggregate";
   848    848             }
   849    849             sqlite3ErrorMsg(pParse, "misuse of %s function %.*s()",zType,nId,zId);
   850    850             pNC->nErr++;
   851    851             is_agg = 0;
          852  +        }else if( is_agg==0 && ExprHasProperty(pExpr, EP_Filter) ){
          853  +          sqlite3ErrorMsg(pParse, 
          854  +              "filter clause may not be used with non-aggregate %.*s()", 
          855  +              nId, zId
          856  +          );
          857  +          pNC->nErr++;
   852    858           }
   853    859   #else
   854    860           if( (is_agg && (pNC->ncFlags & NC_AllowAgg)==0) ){
   855    861             sqlite3ErrorMsg(pParse,"misuse of aggregate function %.*s()",nId,zId);
   856    862             pNC->nErr++;
   857    863             is_agg = 0;
   858    864           }
................................................................................
   879    885             pNC->ncFlags &= ~NC_AllowAgg;
   880    886   #endif
   881    887           }
   882    888         }
   883    889         sqlite3WalkExprList(pWalker, pList);
   884    890         if( is_agg ){
   885    891   #ifndef SQLITE_OMIT_WINDOWFUNC
   886         -        if( pExpr->y.pWin ){
          892  +        if( ExprHasProperty(pExpr, EP_WinFunc) ){
   887    893             Select *pSel = pNC->pWinSelect;
   888    894             if( IN_RENAME_OBJECT==0 ){
   889    895               sqlite3WindowUpdate(pParse, pSel->pWinDefn, pExpr->y.pWin, pDef);
   890    896             }
   891    897             sqlite3WalkExprList(pWalker, pExpr->y.pWin->pPartition);
   892    898             sqlite3WalkExprList(pWalker, pExpr->y.pWin->pOrderBy);
   893    899             sqlite3WalkExpr(pWalker, pExpr->y.pWin->pFilter);
................................................................................
   900    906             pNC->ncFlags |= NC_HasWin;
   901    907           }else
   902    908   #endif /* SQLITE_OMIT_WINDOWFUNC */
   903    909           {
   904    910             NameContext *pNC2 = pNC;
   905    911             pExpr->op = TK_AGG_FUNCTION;
   906    912             pExpr->op2 = 0;
          913  +#ifndef SQLITE_OMIT_WINDOWFUNC
          914  +          sqlite3WalkExpr(pWalker, pExpr->y.pFilter);
          915  +#endif
   907    916             while( pNC2 && !sqlite3FunctionUsesThisSrc(pExpr, pNC2->pSrcList) ){
   908    917               pExpr->op2++;
   909    918               pNC2 = pNC2->pNext;
   910    919             }
   911    920             assert( pDef!=0 );
   912    921             if( pNC2 ){
   913    922               assert( SQLITE_FUNC_MINMAX==NC_MinMaxAgg );

Changes to src/select.c.

  4402   4402     ExprList *pEList = pFunc->x.pList;    /* Arguments to agg function */
  4403   4403     const char *zFunc;                    /* Name of aggregate function pFunc */
  4404   4404     ExprList *pOrderBy;
  4405   4405     u8 sortOrder;
  4406   4406   
  4407   4407     assert( *ppMinMax==0 );
  4408   4408     assert( pFunc->op==TK_AGG_FUNCTION );
  4409         -  if( pEList==0 || pEList->nExpr!=1 ) return eRet;
         4409  +  if( pEList==0 || pEList->nExpr!=1 || ExprHasProperty(pFunc, EP_Filter) ){
         4410  +    return eRet;
         4411  +  }
  4410   4412     zFunc = pFunc->u.zToken;
  4411   4413     if( sqlite3StrICmp(zFunc, "min")==0 ){
  4412   4414       eRet = WHERE_ORDERBY_MIN;
  4413   4415       sortOrder = SQLITE_SO_ASC;
  4414   4416     }else if( sqlite3StrICmp(zFunc, "max")==0 ){
  4415   4417       eRet = WHERE_ORDERBY_MAX;
  4416   4418       sortOrder = SQLITE_SO_DESC;
................................................................................
  4449   4451     pExpr = p->pEList->a[0].pExpr;
  4450   4452     assert( pTab && !pTab->pSelect && pExpr );
  4451   4453   
  4452   4454     if( IsVirtual(pTab) ) return 0;
  4453   4455     if( pExpr->op!=TK_AGG_FUNCTION ) return 0;
  4454   4456     if( NEVER(pAggInfo->nFunc==0) ) return 0;
  4455   4457     if( (pAggInfo->aFunc[0].pFunc->funcFlags&SQLITE_FUNC_COUNT)==0 ) return 0;
  4456         -  if( pExpr->flags&EP_Distinct ) return 0;
         4458  +  if( ExprHasProperty(pExpr, EP_Distinct|EP_Filter) ) return 0;
  4457   4459   
  4458   4460     return pTab;
  4459   4461   }
  4460   4462   
  4461   4463   /*
  4462   4464   ** If the source-list item passed as an argument was augmented with an
  4463   4465   ** INDEXED BY clause, then try to locate the specified index. If there
................................................................................
  5329   5331     pAggInfo->directMode = 1;
  5330   5332     for(i=0, pF=pAggInfo->aFunc; i<pAggInfo->nFunc; i++, pF++){
  5331   5333       int nArg;
  5332   5334       int addrNext = 0;
  5333   5335       int regAgg;
  5334   5336       ExprList *pList = pF->pExpr->x.pList;
  5335   5337       assert( !ExprHasProperty(pF->pExpr, EP_xIsSelect) );
         5338  +    if( ExprHasProperty(pF->pExpr, EP_Filter) ){
         5339  +      Expr *pFilter = pF->pExpr->y.pFilter;
         5340  +      addrNext = sqlite3VdbeMakeLabel(pParse);
         5341  +      sqlite3ExprIfFalse(pParse, pFilter, addrNext, SQLITE_JUMPIFNULL);
         5342  +    }
  5336   5343       if( pList ){
  5337   5344         nArg = pList->nExpr;
  5338   5345         regAgg = sqlite3GetTempRange(pParse, nArg);
  5339   5346         sqlite3ExprCodeExprList(pParse, pList, regAgg, 0, SQLITE_ECEL_DUP);
  5340   5347       }else{
  5341   5348         nArg = 0;
  5342   5349         regAgg = 0;
  5343   5350       }
  5344   5351       if( pF->iDistinct>=0 ){
  5345         -      addrNext = sqlite3VdbeMakeLabel(pParse);
         5352  +      if( addrNext==0 ){ 
         5353  +        addrNext = sqlite3VdbeMakeLabel(pParse);
         5354  +      }
  5346   5355         testcase( nArg==0 );  /* Error condition */
  5347   5356         testcase( nArg>1 );   /* Also an error */
  5348   5357         codeDistinct(pParse, pF->iDistinct, addrNext, 1, regAgg);
  5349   5358       }
  5350   5359       if( pF->pFunc->funcFlags & SQLITE_FUNC_NEEDCOLL ){
  5351   5360         CollSeq *pColl = 0;
  5352   5361         struct ExprList_item *pItem;
................................................................................
  6221   6230       sAggInfo.nAccumulator = sAggInfo.nColumn;
  6222   6231       if( p->pGroupBy==0 && p->pHaving==0 && sAggInfo.nFunc==1 ){
  6223   6232         minMaxFlag = minMaxQuery(db, sAggInfo.aFunc[0].pExpr, &pMinMaxOrderBy);
  6224   6233       }else{
  6225   6234         minMaxFlag = WHERE_ORDERBY_NORMAL;
  6226   6235       }
  6227   6236       for(i=0; i<sAggInfo.nFunc; i++){
  6228         -      assert( !ExprHasProperty(sAggInfo.aFunc[i].pExpr, EP_xIsSelect) );
         6237  +      Expr *pExpr = sAggInfo.aFunc[i].pExpr;
         6238  +      assert( !ExprHasProperty(pExpr, EP_xIsSelect) );
  6229   6239         sNC.ncFlags |= NC_InAggFunc;
  6230         -      sqlite3ExprAnalyzeAggList(&sNC, sAggInfo.aFunc[i].pExpr->x.pList);
         6240  +      sqlite3ExprAnalyzeAggList(&sNC, pExpr->x.pList);
         6241  +#ifndef SQLITE_OMIT_WINDOWFUNC
         6242  +      assert( !ExprHasProperty(pExpr, EP_WinFunc) );
         6243  +      sqlite3ExprAnalyzeAggregates(&sNC, pExpr->y.pFilter);
         6244  +#endif
  6231   6245         sNC.ncFlags &= ~NC_InAggFunc;
  6232   6246       }
  6233   6247       sAggInfo.mxReg = pParse->nMem;
  6234   6248       if( db->mallocFailed ) goto select_end;
  6235   6249   #if SELECTTRACE_ENABLED
  6236   6250       if( sqlite3SelectTrace & 0x400 ){
  6237   6251         int ii;

Changes to src/sqliteInt.h.

  2487   2487     u8 op2;                /* TK_REGISTER/TK_TRUTH: original value of Expr.op
  2488   2488                            ** TK_COLUMN: the value of p5 for OP_Column
  2489   2489                            ** TK_AGG_FUNCTION: nesting depth */
  2490   2490     AggInfo *pAggInfo;     /* Used by TK_AGG_COLUMN and TK_AGG_FUNCTION */
  2491   2491     union {
  2492   2492       Table *pTab;           /* TK_COLUMN: Table containing column. Can be NULL
  2493   2493                              ** for a column of an index on an expression */
  2494         -    Window *pWin;          /* TK_FUNCTION: Window definition for the func */
         2494  +    Window *pWin;          /* EP_WinFunc: Window definition for the func */
         2495  +    Expr *pFilter;         /* EP_Filter: Filter definition for the func */
  2495   2496       struct {               /* TK_IN, TK_SELECT, and TK_EXISTS */
  2496   2497         int iAddr;             /* Subroutine entry address */
  2497   2498         int regReturn;         /* Register used to hold return address */
  2498   2499       } sub;
  2499   2500     } y;
  2500   2501   };
  2501   2502   
................................................................................
  2532   2533   #define EP_Leaf      0x800000 /* Expr.pLeft, .pRight, .u.pSelect all NULL */
  2533   2534   #define EP_WinFunc  0x1000000 /* TK_FUNCTION with Expr.y.pWin set */
  2534   2535   #define EP_Subrtn   0x2000000 /* Uses Expr.y.sub. TK_IN, _SELECT, or _EXISTS */
  2535   2536   #define EP_Quoted   0x4000000 /* TK_ID was originally quoted */
  2536   2537   #define EP_Static   0x8000000 /* Held in memory not obtained from malloc() */
  2537   2538   #define EP_IsTrue  0x10000000 /* Always has boolean value of TRUE */
  2538   2539   #define EP_IsFalse 0x20000000 /* Always has boolean value of FALSE */
         2540  +#define EP_Filter  0x40000000 /* TK_[AGG_]FUNCTION with Expr.y.pFilter set */
  2539   2541   
  2540   2542   /*
  2541   2543   ** The EP_Propagate mask is a set of properties that automatically propagate
  2542   2544   ** upwards into parent nodes.
  2543   2545   */
  2544   2546   #define EP_Propagate (EP_Collate|EP_Subquery|EP_HasFunc)
  2545   2547   
................................................................................
  3598   3600     int regEndRowid;
  3599   3601   };
  3600   3602   
  3601   3603   #ifndef SQLITE_OMIT_WINDOWFUNC
  3602   3604   void sqlite3WindowDelete(sqlite3*, Window*);
  3603   3605   void sqlite3WindowListDelete(sqlite3 *db, Window *p);
  3604   3606   Window *sqlite3WindowAlloc(Parse*, int, int, Expr*, int , Expr*, u8);
  3605         -void sqlite3WindowAttach(Parse*, Expr*, Window*);
         3607  +void sqlite3WindowAttach(Parse*, Expr*, Expr*, Window*);
  3606   3608   int sqlite3WindowCompare(Parse*, Window*, Window*);
  3607   3609   void sqlite3WindowCodeInit(Parse*, Window*);
  3608   3610   void sqlite3WindowCodeStep(Parse*, Select*, WhereInfo*, int, int);
  3609   3611   int sqlite3WindowRewrite(Parse*, Select*);
  3610   3612   int sqlite3ExpandSubquery(Parse*, struct SrcList_item*);
  3611   3613   void sqlite3WindowUpdate(Parse*, Window*, Window*, FuncDef*);
  3612   3614   Window *sqlite3WindowDup(sqlite3 *db, Expr *pOwner, Window *p);
................................................................................
  3613   3615   Window *sqlite3WindowListDup(sqlite3 *db, Window *p);
  3614   3616   void sqlite3WindowFunctions(void);
  3615   3617   void sqlite3WindowChain(Parse*, Window*, Window*);
  3616   3618   Window *sqlite3WindowAssemble(Parse*, Window*, ExprList*, ExprList*, Token*);
  3617   3619   #else
  3618   3620   # define sqlite3WindowDelete(a,b)
  3619   3621   # define sqlite3WindowFunctions()
  3620         -# define sqlite3WindowAttach(a,b,c)
         3622  +# define sqlite3WindowAttach(a,b,c,d)
  3621   3623   #endif
  3622   3624   
  3623   3625   /*
  3624   3626   ** Assuming zIn points to the first byte of a UTF-8 character,
  3625   3627   ** advance zIn to point to the first byte of the next UTF-8 character.
  3626   3628   */
  3627   3629   #define SQLITE_SKIP_UTF8(zIn) {                        \

Changes to src/walker.c.

    70     70         }else if( pExpr->x.pList ){
    71     71           if( sqlite3WalkExprList(pWalker, pExpr->x.pList) ) return WRC_Abort;
    72     72         }
    73     73   #ifndef SQLITE_OMIT_WINDOWFUNC
    74     74         if( ExprHasProperty(pExpr, EP_WinFunc) ){
    75     75           if( walkWindowList(pWalker, pExpr->y.pWin) ) return WRC_Abort;
    76     76         }
           77  +      if( ExprHasProperty(pExpr, EP_Filter) ){
           78  +        if( walkExpr(pWalker, pExpr->y.pFilter) ) return WRC_Abort;
           79  +      }
    77     80   #endif
    78     81       }
    79     82       break;
    80     83     }
    81     84     return WRC_Continue;
    82     85   }
    83     86   int sqlite3WalkExpr(Walker *pWalker, Expr *pExpr){

Changes to src/window.c.

  1174   1174       }
  1175   1175     }
  1176   1176   }
  1177   1177   
  1178   1178   /*
  1179   1179   ** Attach window object pWin to expression p.
  1180   1180   */
  1181         -void sqlite3WindowAttach(Parse *pParse, Expr *p, Window *pWin){
         1181  +void sqlite3WindowAttach(Parse *pParse, Expr *p, Expr *pFilter, Window *pWin){
  1182   1182     if( p ){
  1183   1183       assert( p->op==TK_FUNCTION );
  1184         -    /* This routine is only called for the parser.  If pWin was not
  1185         -    ** allocated due to an OOM, then the parser would fail before ever
  1186         -    ** invoking this routine */
  1187         -    if( ALWAYS(pWin) ){
         1184  +    if( pWin ){
  1188   1185         p->y.pWin = pWin;
  1189   1186         ExprSetProperty(p, EP_WinFunc);
  1190   1187         pWin->pOwner = p;
  1191   1188         if( p->flags & EP_Distinct ){
  1192   1189           sqlite3ErrorMsg(pParse,
  1193   1190              "DISTINCT is not supported for window functions");
  1194   1191         }
         1192  +      pWin->pFilter = pFilter;
         1193  +    }else if( pFilter ){
         1194  +      p->y.pFilter = pFilter;
         1195  +      ExprSetProperty(p, EP_Filter);
  1195   1196       }
  1196   1197     }else{
  1197   1198       sqlite3WindowDelete(pParse->db, pWin);
         1199  +    sqlite3ExprDelete(pParse->db, pFilter);
  1198   1200     }
  1199   1201   }
  1200   1202   
  1201   1203   /*
  1202   1204   ** Return 0 if the two window objects are identical, or non-zero otherwise.
  1203   1205   ** Identical window objects can be processed in a single scan.
  1204   1206   */

Added test/filter1.test.

            1  +# 2018 May 8
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library.
           12  +#
           13  +
           14  +set testdir [file dirname $argv0]
           15  +source $testdir/tester.tcl
           16  +set testprefix filter1
           17  +
           18  +ifcapable !windowfunc {
           19  +  finish_test
           20  +  return
           21  +}
           22  +
           23  +do_execsql_test 1.0 {
           24  +  CREATE TABLE t1(a);
           25  +  CREATE INDEX i1 ON t1(a);
           26  +  INSERT INTO t1 VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9);
           27  +}
           28  +
           29  +do_execsql_test 1.1 { SELECT sum(a) FROM t1; } 45
           30  +do_execsql_test 1.2 { SELECT sum(a) FILTER( WHERE a<5 ) FROM t1; } 10
           31  +
           32  +do_execsql_test 1.3 { 
           33  +  SELECT sum(a) FILTER( WHERE a>9 ),
           34  +         sum(a) FILTER( WHERE a>8 ),
           35  +         sum(a) FILTER( WHERE a>7 ),
           36  +         sum(a) FILTER( WHERE a>6 ),
           37  +         sum(a) FILTER( WHERE a>5 ),
           38  +         sum(a) FILTER( WHERE a>4 ),
           39  +         sum(a) FILTER( WHERE a>3 ),
           40  +         sum(a) FILTER( WHERE a>2 ),
           41  +         sum(a) FILTER( WHERE a>1 ),
           42  +         sum(a) FILTER( WHERE a>0 )
           43  +  FROM t1;
           44  +} {{} 9 17 24 30 35 39 42 44 45}
           45  +
           46  +do_execsql_test 1.4 {
           47  +  SELECT max(a) FILTER (WHERE (a % 2)==0) FROM t1
           48  +} {8}
           49  +
           50  +do_execsql_test 1.5 {
           51  +  SELECT min(a) FILTER (WHERE a>4) FROM t1
           52  +} {5}
           53  +
           54  +do_execsql_test 1.6 {
           55  +  SELECT count(*) FILTER (WHERE a!=5) FROM t1
           56  +} {8}
           57  +
           58  +do_execsql_test 1.6 {
           59  +  SELECT min(a) FILTER (WHERE a>3) FROM t1 GROUP BY (a%2) ORDER BY 1;
           60  +} {4 5}
           61  +
           62  +
           63  +#-------------------------------------------------------------------------
           64  +# Test some errors:
           65  +#
           66  +#   .1 FILTER on a non-aggregate function,
           67  +#   .2 Window function in FILTER clause,
           68  +#   .3 Aggregate function in FILTER clause,
           69  +#
           70  +reset_db
           71  +do_execsql_test 2.0 {
           72  +  CREATE TABLE t1(a);
           73  +  INSERT INTO t1 VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9);
           74  +}
           75  +
           76  +do_catchsql_test 2.1 {
           77  +  SELECT upper(a) FILTER (WHERE a=1) FROM t1
           78  +} {1 {filter clause may not be used with non-aggregate upper()}}
           79  +
           80  +do_catchsql_test 2.2 {
           81  +  SELECT sum(a) FILTER (WHERE 1 - max(a) OVER () > 0) FROM t1
           82  +} {1 {misuse of window function max()}}
           83  +
           84  +do_catchsql_test 2.3 {
           85  +  SELECT sum(a) FILTER (WHERE 1 - count(a)) FROM t1
           86  +} {1 {misuse of aggregate: count()}}
           87  +
           88  +finish_test
           89  +
           90  +

Added test/filter2.tcl.

            1  +# 2018 May 19
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +
           13  +source [file join [file dirname $argv0] pg_common.tcl]
           14  +
           15  +#=========================================================================
           16  +
           17  +
           18  +start_test filter2 "2019 July 2"
           19  +
           20  +ifcapable !windowfunc
           21  +
           22  +execsql_test 1.0 {
           23  +  DROP TABLE IF EXISTS t1;
           24  +  CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER);
           25  +  INSERT INTO t1 VALUES
           26  +   (1, 7), (2, 3), (3, 5), (4, 30), (5, 26), (6, 23), (7, 27),
           27  +   (8, 3), (9, 17), (10, 26), (11, 33), (12, 25), (13, NULL), (14, 47),
           28  +   (15, 36), (16, 13), (17, 45), (18, 31), (19, 11), (20, 36), (21, 37),
           29  +   (22, 21), (23, 22), (24, 14), (25, 16), (26, 3), (27, 7), (28, 29),
           30  +   (29, 50), (30, 38), (31, 3), (32, 36), (33, 12), (34, 4), (35, 46),
           31  +   (36, 3), (37, 48), (38, 23), (39, NULL), (40, 24), (41, 5), (42, 46),
           32  +   (43, 11), (44, NULL), (45, 18), (46, 25), (47, 15), (48, 18), (49, 23);
           33  +}
           34  +
           35  +execsql_test 1.1 { SELECT sum(b) FROM t1 }
           36  +
           37  +execsql_test 1.2 { SELECT sum(b) FILTER (WHERE a<10) FROM t1 }
           38  +
           39  +execsql_test 1.3 { SELECT count(DISTINCT b) FROM t1 }
           40  +
           41  +execsql_test 1.4 { SELECT count(DISTINCT b) FILTER (WHERE a!=19) FROM t1 }
           42  +
           43  +execsql_test 1.5 { 
           44  +  SELECT min(b) FILTER (WHERE a>19),
           45  +         min(b) FILTER (WHERE a>0),
           46  +         max(a+b) FILTER (WHERE a>19),
           47  +         max(b+a) FILTER (WHERE a BETWEEN 10 AND 40)
           48  +  FROM t1;
           49  +}
           50  +
           51  +execsql_test 1.6 { 
           52  +  SELECT min(b),
           53  +         min(b),
           54  +         max(a+b),
           55  +         max(b+a)
           56  +  FROM t1
           57  +  GROUP BY (a%10)
           58  +  ORDER BY 1, 2, 3, 4;
           59  +}
           60  +
           61  +execsql_test 1.7 { 
           62  +  SELECT min(b) FILTER (WHERE a>19),
           63  +         min(b) FILTER (WHERE a>0),
           64  +         max(a+b) FILTER (WHERE a>19),
           65  +         max(b+a) FILTER (WHERE a BETWEEN 10 AND 40)
           66  +  FROM t1
           67  +  GROUP BY (a%10)
           68  +  ORDER BY 1, 2, 3, 4;
           69  +}
           70  +
           71  +finish_test
           72  +
           73  +

Added test/filter2.test.

            1  +# 2019 July 2
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +# This file implements regression tests for SQLite library.
           12  +#
           13  +
           14  +####################################################
           15  +# DO NOT EDIT! THIS FILE IS AUTOMATICALLY GENERATED!
           16  +####################################################
           17  +
           18  +set testdir [file dirname $argv0]
           19  +source $testdir/tester.tcl
           20  +set testprefix filter2
           21  +
           22  +ifcapable !windowfunc { finish_test ; return }
           23  +do_execsql_test 1.0 {
           24  +  DROP TABLE IF EXISTS t1;
           25  +  CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER);
           26  +  INSERT INTO t1 VALUES
           27  +   (1, 7), (2, 3), (3, 5), (4, 30), (5, 26), (6, 23), (7, 27),
           28  +   (8, 3), (9, 17), (10, 26), (11, 33), (12, 25), (13, NULL), (14, 47),
           29  +   (15, 36), (16, 13), (17, 45), (18, 31), (19, 11), (20, 36), (21, 37),
           30  +   (22, 21), (23, 22), (24, 14), (25, 16), (26, 3), (27, 7), (28, 29),
           31  +   (29, 50), (30, 38), (31, 3), (32, 36), (33, 12), (34, 4), (35, 46),
           32  +   (36, 3), (37, 48), (38, 23), (39, NULL), (40, 24), (41, 5), (42, 46),
           33  +   (43, 11), (44, NULL), (45, 18), (46, 25), (47, 15), (48, 18), (49, 23);
           34  +} {}
           35  +
           36  +do_execsql_test 1.1 {
           37  +  SELECT sum(b) FROM t1
           38  +} {1041}
           39  +
           40  +do_execsql_test 1.2 {
           41  +  SELECT sum(b) FILTER (WHERE a<10) FROM t1
           42  +} {141}
           43  +
           44  +do_execsql_test 1.3 {
           45  +  SELECT count(DISTINCT b) FROM t1
           46  +} {31}
           47  +
           48  +do_execsql_test 1.4 {
           49  +  SELECT count(DISTINCT b) FILTER (WHERE a!=19) FROM t1
           50  +} {31}
           51  +
           52  +do_execsql_test 1.5 {
           53  +  SELECT min(b) FILTER (WHERE a>19),
           54  +         min(b) FILTER (WHERE a>0),
           55  +         max(a+b) FILTER (WHERE a>19),
           56  +         max(b+a) FILTER (WHERE a BETWEEN 10 AND 40)
           57  +  FROM t1;
           58  +} {3 3 88 85}
           59  +
           60  +do_execsql_test 1.6 {
           61  +  SELECT min(b),
           62  +         min(b),
           63  +         max(a+b),
           64  +         max(b+a)
           65  +  FROM t1
           66  +  GROUP BY (a%10)
           67  +  ORDER BY 1, 2, 3, 4;
           68  +} {3 3 58 58   3 3 66 66   3 3 71 71   3 3 88 88   4 4 61 61   5 5 54 54
           69  +  7 7 85 85   11 11 79 79   16 16 81 81   24 24 68 68}
           70  +
           71  +do_execsql_test 1.7 {
           72  +  SELECT min(b) FILTER (WHERE a>19),
           73  +         min(b) FILTER (WHERE a>0),
           74  +         max(a+b) FILTER (WHERE a>19),
           75  +         max(b+a) FILTER (WHERE a BETWEEN 10 AND 40)
           76  +  FROM t1
           77  +  GROUP BY (a%10)
           78  +  ORDER BY 1, 2, 3, 4;
           79  +} {3 3 58 58   3 3 71 39   4 4 38 61   7 7 85 85   11 5 54 45   16 16 81 81
           80  +  18 3 66 61   21 3 88 68   23 11 79 79   24 24 68 68}
           81  +
           82  +finish_test