/ Check-in [ceaf798e]
Login

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

Overview
Comment:Add support for FILTER clause on aggregate window functions.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | exp-window-functions
Files: files | file ages | folders
SHA3-256: ceaf798ea09184bc0e7d3dcf3ad4d909d2e4e7018754a8417a813f33010140a7
User & Date: dan 2018-06-09 17:43:45
Context
2018-06-09
17:58
Update the amalgamation build script to include window.c. check-in: 21d2f4a6 user: dan tags: exp-window-functions
17:43
Add support for FILTER clause on aggregate window functions. check-in: ceaf798e user: dan tags: exp-window-functions
2018-06-08
20:58
Add support for the WINDOW clause. check-in: 19c983b5 user: dan tags: exp-window-functions
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/window.c.

   407    407       pWin->pStart = sqlite3ExprDup(pParse->db, p->pStart, 0);
   408    408       pWin->pEnd = sqlite3ExprDup(pParse->db, p->pEnd, 0);
   409    409       pWin->eStart = p->eStart;
   410    410       pWin->eEnd = p->eEnd;
   411    411     }
   412    412     if( pFunc->funcFlags & SQLITE_FUNC_WINDOW ){
   413    413       sqlite3 *db = pParse->db;
          414  +    if( pWin->pFilter ){
          415  +      sqlite3ErrorMsg(pParse, 
          416  +          "FILTER clause may only be used with aggregate window functions"
          417  +      );
          418  +    }else
   414    419       if( pFunc->xSFunc==row_numberStepFunc || pFunc->xSFunc==ntileStepFunc ){
   415    420         sqlite3ExprDelete(db, pWin->pStart);
   416    421         sqlite3ExprDelete(db, pWin->pEnd);
   417    422         pWin->pStart = pWin->pEnd = 0;
   418    423         pWin->eType = TK_ROWS;
   419    424         pWin->eStart = TK_UNBOUNDED;
   420    425         pWin->eEnd = TK_CURRENT;
   421         -    }
          426  +    }else
   422    427   
   423    428       if( pFunc->xSFunc==dense_rankStepFunc || pFunc->xSFunc==rankStepFunc
   424    429        || pFunc->xSFunc==percent_rankStepFunc || pFunc->xSFunc==cume_distStepFunc
   425    430       ){
   426    431         sqlite3ExprDelete(db, pWin->pStart);
   427    432         sqlite3ExprDelete(db, pWin->pEnd);
   428    433         pWin->pStart = pWin->pEnd = 0;
................................................................................
   598    603       /* Append the arguments passed to each window function to the
   599    604       ** sub-select expression list. Also allocate two registers for each
   600    605       ** window function - one for the accumulator, another for interim
   601    606       ** results.  */
   602    607       for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
   603    608         pWin->iArgCol = (pSublist ? pSublist->nExpr : 0);
   604    609         pSublist = exprListAppendList(pParse, pSublist, pWin->pOwner->x.pList);
          610  +      if( pWin->pFilter ){
          611  +        Expr *pFilter = sqlite3ExprDup(db, pWin->pFilter, 0);
          612  +        pSublist = sqlite3ExprListAppend(pParse, pSublist, pFilter);
          613  +      }
   605    614         pWin->regAccum = ++pParse->nMem;
   606    615         pWin->regResult = ++pParse->nMem;
   607    616         sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum);
   608    617       }
   609    618   
   610    619       pSub = sqlite3SelectNew(
   611    620           pParse, pSublist, pSrc, pWhere, pGroupBy, pHaving, pSort, 0, 0
................................................................................
   815    824         assert( bInverse==0 || bInverse==1 );
   816    825         sqlite3VdbeAddOp2(v, OP_AddImm, pWin->regApp+1-bInverse, 1);
   817    826       }else if( pWin->pFunc->xSFunc==leadStepFunc 
   818    827              || pWin->pFunc->xSFunc==lagStepFunc 
   819    828       ){
   820    829         /* no-op */
   821    830       }else{
          831  +      int addrIf = 0;
          832  +      if( pWin->pFilter ){
          833  +        int regTmp;
          834  +        if( csr>0 ){
          835  +          regTmp = sqlite3GetTempReg(pParse);
          836  +          sqlite3VdbeAddOp3(v, OP_Column, csr, pWin->iArgCol+pWin->nArg,regTmp);
          837  +        }else{
          838  +          regTmp = regArg + pWin->nArg;
          839  +        }
          840  +        addrIf = sqlite3VdbeAddOp3(v, OP_IfNot, regTmp, 0, 1);
          841  +        if( csr>0 ){
          842  +          sqlite3ReleaseTempReg(pParse, regTmp);
          843  +        }
          844  +      }
   822    845         if( pWin->pFunc->funcFlags & SQLITE_FUNC_NEEDCOLL ){
   823    846           CollSeq *pColl;
   824         -        pColl = sqlite3ExprCollSeq(pParse, pWin->pOwner->x.pList->a[0].pExpr);
          847  +        pColl = sqlite3ExprNNCollSeq(pParse, pWin->pOwner->x.pList->a[0].pExpr);
   825    848           sqlite3VdbeAddOp4(v, OP_CollSeq, 0,0,0, (const char*)pColl, P4_COLLSEQ);
   826    849         }
   827    850         sqlite3VdbeAddOp3(v, OP_AggStep0, bInverse, regArg, pWin->regAccum);
   828    851         sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
   829    852         sqlite3VdbeChangeP5(v, (u8)nArg);
          853  +      if( addrIf ) sqlite3VdbeJumpHere(v, addrIf);
   830    854       }
   831    855     }
   832    856   }
   833    857   
   834    858   static void windowAggFinal(Parse *pParse, Window *pMWin, int bFinal){
   835    859     Vdbe *v = sqlite3GetVdbe(pParse);
   836    860     Window *pWin;
................................................................................
   851    875         if( bFinal==0 ){
   852    876           sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regResult);
   853    877         }
   854    878         sqlite3VdbeAddOp2(v, OP_AggFinal, pWin->regAccum, pWin->nArg);
   855    879         sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
   856    880         if( bFinal ){
   857    881           sqlite3VdbeAddOp2(v, OP_Copy, pWin->regAccum, pWin->regResult);
          882  +        sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum);
   858    883         }else{
   859    884           sqlite3VdbeChangeP3(v, -1, pWin->regResult);
   860    885         }
   861    886       }
   862    887     }
   863    888   }
   864    889   

Changes to test/window1.test.

   210    210   do_execsql_test 6.2 {
   211    211     SELECT * FROM t2, (SELECT x, count(*) OVER (ORDER BY x) FROM t1);
   212    212   } {
   213    213     b 1 1 b 2 2 b 3 3 b 4 4 b 5 5 b 6 6 b 7 7
   214    214     a 1 1 a 2 2 a 3 3 a 4 4 a 5 5 a 6 6 a 7 7
   215    215   }
   216    216   
          217  +do_catchsql_test 3.5 {
          218  +  SELECT x, lag(x) FILTER (WHERE (x%2)=0) OVER w FROM t1 
          219  +  WINDOW w AS (ORDER BY x)
          220  +} {1 {FILTER clause may only be used with aggregate window functions}}
   217    221   
   218    222   finish_test

Changes to test/window3.tcl.

   280    280     execsql_test 1.$tn.13.6 "
   281    281       SELECT lag(b,b) OVER (PARTITION BY b%2,a ORDER BY b%10 $window) FROM t2
   282    282     "
   283    283   
   284    284     execsql_test 1.$tn.14.1 "
   285    285       SELECT string_agg(CAST(b AS TEXT), '.') OVER (ORDER BY a $window) FROM t2
   286    286     "
   287         -
   288    287     execsql_test 1.$tn.14.2 "
   289    288       SELECT string_agg(CAST(b AS TEXT), '.') OVER (PARTITION BY b%10 ORDER BY a $window) FROM t2
   290    289     "
   291    290     execsql_test 1.$tn.14.3 "
   292    291       SELECT string_agg(CAST(b AS TEXT), '.') OVER ( ORDER BY b,a $window ) FROM t2
   293    292     "
   294    293     execsql_test 1.$tn.14.4 "
................................................................................
   296    295     "
   297    296     execsql_test 1.$tn.14.5 "
   298    297       SELECT string_agg(CAST(b AS TEXT), '.') OVER ( ORDER BY b%10,a $window ) FROM t2
   299    298     "
   300    299     execsql_test 1.$tn.14.6 "
   301    300       SELECT string_agg(CAST(b AS TEXT), '.') OVER (PARTITION BY b%2,a ORDER BY b%10 $window) FROM t2
   302    301     "
          302  +
          303  +  execsql_test 1.$tn.15.1 "
          304  +    SELECT string_agg(CAST(b AS TEXT), '.') 
          305  +    FILTER (WHERE a%2=0) OVER (ORDER BY a $window) FROM t2
          306  +  "
          307  +
          308  +  execsql_test 1.$tn.15.2 "
          309  +    SELECT string_agg(CAST(b AS TEXT), '.') 
          310  +    FILTER (WHERE 0=1) OVER (ORDER BY a $window) FROM t2
          311  +  "
          312  +
          313  +  execsql_test 1.$tn.15.3 "
          314  +    SELECT string_agg(CAST(b AS TEXT), '.') 
          315  +    FILTER (WHERE 1=0) OVER (PARTITION BY (a%10) ORDER BY a $window) FROM t2
          316  +  "
          317  +
          318  +  execsql_test 1.$tn.15.4 "
          319  +    SELECT string_agg(CAST(b AS TEXT), '.') 
          320  +    FILTER (WHERE a%2=0) OVER (PARTITION BY (a%10) ORDER BY a $window) FROM t2
          321  +  "
   303    322   }
   304    323   
   305    324   finish_test
   306    325   

Changes to test/window3.test.

cannot compute difference between binary files

Changes to test/window4.tcl.

    92     92   
    93     93   execsql_test 3.3 {
    94     94     SELECT a, count(*) OVER abc, count(*) OVER def FROM t5
    95     95     WINDOW abc AS (ORDER BY a), 
    96     96            def AS (ORDER BY a DESC)
    97     97     ORDER BY a;
    98     98   }
           99  +
          100  +execsql_test 3.4 {
          101  +  SELECT a, max(a) FILTER (WHERE (a%2)=0) OVER w FROM t5 
          102  +  WINDOW w AS (ORDER BY a)
          103  +}
          104  +
    99    105   
   100    106   finish_test
   101    107   

Changes to test/window4.test.

   172    172   do_execsql_test 3.3 {
   173    173     SELECT a, count(*) OVER abc, count(*) OVER def FROM t5
   174    174     WINDOW abc AS (ORDER BY a), 
   175    175            def AS (ORDER BY a DESC)
   176    176     ORDER BY a;
   177    177   } {1 1 5   2 2 4   3 3 3   4 4 2   5 5 1}
   178    178   
          179  +explain_i {
          180  +  SELECT a, max(a) FILTER (WHERE (a%2)=0) OVER w FROM t5 
          181  +  WINDOW w AS (ORDER BY a)
          182  +  }
          183  +  breakpoint
          184  +do_execsql_test 3.4 {
          185  +  SELECT a, max(a) FILTER (WHERE (a%2)=0) OVER w FROM t5 
          186  +  WINDOW w AS (ORDER BY a)
          187  +} {1 {}   2 2   3 2   4 4   5 4}
          188  +
   179    189   finish_test