/ Check-in [ec7b648c]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Support other frame types that use "<expr> PRECEDING" or "<expr> FOLLOWING" as start or end conditions.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | exp-window-functions
Files: files | file ages | folders
SHA3-256: ec7b648c7f0ee266653561bbb9daa45b9be0d8a1a14f11dc93bce467c35154e6
User & Date: dan 2018-05-24 17:49:14
Context
2018-05-24
21:10
Allow "<expr> PRECEDING" to be used to specify the end of a window frame. check-in: 7b709a98 user: dan tags: exp-window-functions
17:49
Support other frame types that use "<expr> PRECEDING" or "<expr> FOLLOWING" as start or end conditions. check-in: ec7b648c user: dan tags: exp-window-functions
2018-05-23
20:55
Add support for "ROWS BETWEEN <expr> PRECEDING AND <expr> FOLLOWING" window frames. check-in: 3a203660 user: dan tags: exp-window-functions
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/window.c.

    89     89     sqlite3VdbeAddOp2(v, OP_Integer, 0, regZero);
    90     90     sqlite3VdbeAddOp2(v, OP_MustBeInt, reg, sqlite3VdbeCurrentAddr(v)+2);
    91     91     sqlite3VdbeAddOp3(v, OP_Ge, regZero, sqlite3VdbeCurrentAddr(v)+2, reg);
    92     92     sqlite3VdbeAddOp2(v, OP_Halt, SQLITE_ERROR, OE_Abort);
    93     93     sqlite3VdbeAppendP4(v, (void*)azErr[bEnd], P4_STATIC);
    94     94   }
    95     95   
           96  +/*
           97  +** ROWS BETWEEN <expr> PRECEDING    AND <expr> FOLLOWING
           98  +**
           99  +**   ...
          100  +**     if( new partition ){
          101  +**       Gosub flush_partition
          102  +**     }
          103  +**     Insert (record in eph-table)
          104  +**   sqlite3WhereEnd()
          105  +**   Gosub flush_partition
          106  +**
          107  +** flush_partition:
          108  +**   OpenDup (csr -> csr2)
          109  +**   OpenDup (csr -> csr3)
          110  +**   regPrec = <expr1>            // PRECEDING expression
          111  +**   regFollow = <expr2>          // FOLLOWING expression
          112  +**   if( regPrec<0 || regFollow<0 ) throw exception!
          113  +**   Rewind (csr,csr2,csr3)       // if EOF goto flush_partition_done
          114  +**     Aggstep (csr3)
          115  +**     Next(csr3)                 // if EOF fall-through
          116  +**     if( (regFollow--)<=0 ){
          117  +**       AggFinal (xValue)
          118  +**       Gosub addrGosub
          119  +**       Next(csr)                // if EOF goto flush_partition_done
          120  +**       if( (regPrec--)<=0 ){
          121  +**         AggStep (csr2, xInverse)
          122  +**         Next(csr2)
          123  +**       }
          124  +**     }
          125  +** flush_partition_done:
          126  +**   Close (csr2)
          127  +**   Close (csr3)
          128  +**   ResetSorter (csr)
          129  +**   Return
          130  +**
          131  +** ROWS BETWEEN <expr> PRECEDING    AND CURRENT ROW
          132  +** ROWS BETWEEN CURRENT ROW         AND <expr> FOLLOWING
          133  +** ROWS BETWEEN <expr> PRECEDING    AND UNBOUNDED FOLLOWING
          134  +** ROWS BETWEEN UNBOUNDED PRECEDING AND <expr> FOLLOWING
          135  +**
          136  +**   These are similar to the above. For "CURRENT ROW", intialize the
          137  +**   register to 0. For "UNBOUNDED ..." to infinity.
          138  +**
          139  +*/
    96    140   static void windowCodeRowExprStep(
    97    141     Parse *pParse, 
    98    142     Select *p,
    99    143     WhereInfo *pWInfo,
   100    144     int regGosub, 
   101    145     int addrGosub
   102    146   ){
................................................................................
   118    162     int csrFollow = pParse->nTab++;
   119    163     int regPrec;                    /* Value of <expr> PRECEDING */
   120    164     int regFollow;                  /* Value of <expr> FOLLOWING */
   121    165     int addrNext;
   122    166     int addrGoto;
   123    167     int addrIfPos1;
   124    168     int addrIfPos2;
          169  +
          170  +  assert( pMWin->eStart==TK_PRECEDING 
          171  +       || pMWin->eStart==TK_CURRENT 
          172  +       || pMWin->eStart==TK_UNBOUNDED 
          173  +  );
          174  +  assert( pMWin->eEnd==TK_FOLLOWING 
          175  +       || pMWin->eEnd==TK_CURRENT 
          176  +       || pMWin->eEnd==TK_UNBOUNDED 
          177  +  );
   125    178   
   126    179     pParse->nMem += nSub + 2;
   127    180   
   128    181     /* Allocate register and label for the "flush_partition" sub-routine. */
   129    182     regFlushPart = ++pParse->nMem;
   130    183     addrFlushPart = sqlite3VdbeMakeLabel(v);
   131    184     addrDone = sqlite3VdbeMakeLabel(v);
................................................................................
   169    222   
   170    223     /* flush_partition: */
   171    224     sqlite3VdbeResolveLabel(v, addrFlushPart);
   172    225     sqlite3VdbeAddOp2(v, OP_Once, 0, sqlite3VdbeCurrentAddr(v)+3);
   173    226     sqlite3VdbeAddOp2(v, OP_OpenDup, csrPrec, pMWin->iEphCsr);
   174    227     sqlite3VdbeAddOp2(v, OP_OpenDup, csrFollow, pMWin->iEphCsr);
   175    228   
   176         -  sqlite3ExprCode(pParse, pMWin->pStart, regPrec);
   177         -  sqlite3ExprCode(pParse, pMWin->pEnd, regFollow);
          229  +  /* If either regPrec or regFollow are not non-negative integers, throw 
          230  +  ** an exception.  */
          231  +  if( pMWin->pStart ){
          232  +    assert( pMWin->eStart==TK_PRECEDING );
          233  +    sqlite3ExprCode(pParse, pMWin->pStart, regPrec);
          234  +    windowCheckFrameValue(pParse, regPrec, 0);
          235  +  }
          236  +  if( pMWin->pEnd ){
          237  +    assert( pMWin->eEnd==TK_FOLLOWING );
          238  +    sqlite3ExprCode(pParse, pMWin->pEnd, regFollow);
          239  +    windowCheckFrameValue(pParse, regFollow, 1);
          240  +  }
   178    241   
   179    242     sqlite3VdbeAddOp2(v, OP_Null, 0, pMWin->regResult);
   180    243     sqlite3VdbeAddOp2(v, OP_Null, 0, pMWin->regAccum);
   181    244   
   182         -  /* If either regPrec or regFollow are not non-negative integers, throw an
   183         -  ** exception.  */
   184         -  windowCheckFrameValue(pParse, regPrec, 0);
   185         -  windowCheckFrameValue(pParse, regFollow, 1);
   186         -
   187    245     sqlite3VdbeAddOp2(v, OP_Rewind, pMWin->iEphCsr, addrDone);
   188    246     sqlite3VdbeAddOp2(v, OP_Rewind, csrPrec, addrDone);
   189    247     sqlite3VdbeChangeP5(v, 1);
   190    248     sqlite3VdbeAddOp2(v, OP_Rewind, csrFollow, addrDone);
   191    249     sqlite3VdbeChangeP5(v, 1);
   192    250   
   193    251     /* Invoke AggStep function for each window function using the row that
................................................................................
   201    259       for(i=0; i<pWin->nArg; i++){
   202    260         sqlite3VdbeAddOp3(v, OP_Column, csrFollow, pWin->iArgCol+i, reg+i);
   203    261       }
   204    262       sqlite3VdbeAddOp3(v, OP_AggStep0, 0, reg, pWin->regAccum);
   205    263       sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
   206    264       sqlite3VdbeChangeP5(v, (u8)pWin->nArg);
   207    265     }
   208         -  sqlite3VdbeJumpHere(v, addrNext+1);
          266  +  if( pMWin->eEnd==TK_UNBOUNDED ){
          267  +    sqlite3VdbeAddOp2(v, OP_Goto, 0, addrNext);
          268  +    sqlite3VdbeJumpHere(v, addrNext+1);
          269  +    addrNext = sqlite3VdbeCurrentAddr(v);
          270  +  }else{
          271  +    sqlite3VdbeJumpHere(v, addrNext+1);
          272  +  }
   209    273   
   210         -  addrIfPos1 = sqlite3VdbeAddOp3(v, OP_IfPos, regFollow, 0 , 1);
          274  +  if( pMWin->eEnd==TK_FOLLOWING ){
          275  +    addrIfPos1 = sqlite3VdbeAddOp3(v, OP_IfPos, regFollow, 0 , 1);
          276  +  }
   211    277     for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
   212    278       sqlite3VdbeAddOp3(v, 
   213    279           OP_AggFinal, pWin->regAccum, pWin->nArg, pWin->regResult
   214    280       );
   215    281       sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
   216    282     }
   217    283     sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrGosub);
   218    284     sqlite3VdbeAddOp2(v, OP_Next, pMWin->iEphCsr, sqlite3VdbeCurrentAddr(v)+2);
   219    285     sqlite3VdbeAddOp2(v, OP_Goto, 0, addrDone);
   220    286   
   221         -  addrIfPos2 = sqlite3VdbeAddOp3(v, OP_IfPos, regPrec, 0 , 1);
   222         -  sqlite3VdbeAddOp2(v, OP_Next, csrPrec, sqlite3VdbeCurrentAddr(v)+1);
   223         -  for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
   224         -    int i;
   225         -    for(i=0; i<pWin->nArg; i++){
   226         -      sqlite3VdbeAddOp3(v, OP_Column, csrPrec, pWin->iArgCol+i, reg+i);
          287  +  if( pMWin->eStart==TK_CURRENT || pMWin->eStart==TK_PRECEDING ){
          288  +    if( pMWin->eStart==TK_PRECEDING ){
          289  +      addrIfPos2 = sqlite3VdbeAddOp3(v, OP_IfPos, regPrec, 0 , 1);
          290  +    }
          291  +    sqlite3VdbeAddOp2(v, OP_Next, csrPrec, sqlite3VdbeCurrentAddr(v)+1);
          292  +    for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
          293  +      int i;
          294  +      for(i=0; i<pWin->nArg; i++){
          295  +        sqlite3VdbeAddOp3(v, OP_Column, csrPrec, pWin->iArgCol+i, reg+i);
          296  +      }
          297  +      sqlite3VdbeAddOp3(v, OP_AggStep0, 1, reg, pWin->regAccum);
          298  +      sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
          299  +      sqlite3VdbeChangeP5(v, (u8)pWin->nArg);
   227    300       }
   228         -    sqlite3VdbeAddOp3(v, OP_AggStep0, 1, reg, pWin->regAccum);
   229         -    sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
   230         -    sqlite3VdbeChangeP5(v, (u8)pWin->nArg);
          301  +    if( pMWin->eStart==TK_PRECEDING ){
          302  +      sqlite3VdbeJumpHere(v, addrIfPos2);
          303  +    }
   231    304     }
   232         -  sqlite3VdbeJumpHere(v, addrIfPos2);
   233         -
   234         -  sqlite3VdbeJumpHere(v, addrIfPos1);
          305  +  if( pMWin->eEnd==TK_FOLLOWING ){
          306  +    sqlite3VdbeJumpHere(v, addrIfPos1);
          307  +  }
   235    308     sqlite3VdbeAddOp2(v, OP_Goto, 0, addrNext);
   236    309   
   237    310     /* flush_partition_done: */
   238    311     sqlite3VdbeResolveLabel(v, addrDone);
   239    312     sqlite3VdbeAddOp1(v, OP_ResetSorter, pMWin->iEphCsr);
   240    313     sqlite3VdbeAddOp1(v, OP_Return, regFlushPart);
   241    314   
................................................................................
   424    497   **
   425    498   ** ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
   426    499   ** ROWS BETWEEN CURRENT ROW AND CURRENT ROW
   427    500   ** ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
   428    501   **
   429    502   **========================================================================
   430    503   **
   431         -** ROWS BETWEEN <expr> PRECEDING    AND <expr> FOLLOWING
   432         -**
   433         -**   ...
   434         -**     if( new partition ){
   435         -**       Gosub flush_partition
   436         -**     }
   437         -**     Insert (record in eph-table)
   438         -**   sqlite3WhereEnd()
   439         -**   Gosub flush_partition
   440         -**
   441         -** flush_partition:
   442         -**   OpenDup (csr -> csr2)
   443         -**   OpenDup (csr -> csr3)
   444         -**   regPrec = <expr1>            // PRECEDING expression
   445         -**   regFollow = <expr2>          // FOLLOWING expression
   446         -**   if( regPrec<0 || regFollow<0 ) throw exception!
   447         -**   Rewind (csr,csr2,csr3)       // if EOF goto flush_partition_done
   448         -**     Aggstep (csr3)
   449         -**     Next(csr3)                 // if EOF fall-through
   450         -**     if( (regFollow--)<=0 ){
   451         -**       AggFinal (xValue)
   452         -**       Gosub addrGosub
   453         -**       Next(csr)                // if EOF goto flush_partition_done
   454         -**       if( (regPrec--)<=0 ){
   455         -**         AggStep (csr2, xInverse)
   456         -**         Next(csr2)
   457         -**       }
   458         -**     }
   459         -** flush_partition_done:
   460         -**   Close (csr2)
   461         -**   Close (csr3)
   462         -**   ResetSorter (csr)
   463         -**   Return
   464         -**
   465         -** ROWS BETWEEN <expr> PRECEDING    AND CURRENT ROW
   466         -** ROWS BETWEEN CURRENT ROW         AND <expr> FOLLOWING
   467         -** ROWS BETWEEN <expr> PRECEDING    AND UNBOUNDED FOLLOWING
   468         -** ROWS BETWEEN UNBOUNDED PRECEDING AND <expr> FOLLOWING
   469         -**
   470         -**   These are similar to the above. For "CURRENT ROW", intialize the
   471         -**   register to 0. For "UNBOUNDED ..." to infinity.
   472         -**
   473    504   ** ROWS BETWEEN <expr> PRECEDING    AND <expr> PRECEDING
   474    505   **
   475    506   **   Replace the bit after "Rewind" in the above with:
   476    507   **
   477    508   **     if( (regFollow--)<=0 ){
   478    509   **       AggStep (csr3)
   479    510   **       Next (csr3)
................................................................................
   518    549     int regGosub, 
   519    550     int addrGosub,
   520    551     int *pbLoop
   521    552   ){
   522    553     Window *pMWin = p->pWin;
   523    554   
   524    555     if( pMWin->eType==TK_ROWS 
   525         -   && pMWin->eStart==TK_PRECEDING
   526         -   && pMWin->eEnd==TK_FOLLOWING
          556  +   && (pMWin->eStart==TK_PRECEDING || pMWin->eEnd==TK_FOLLOWING)
          557  +   && (pMWin->eStart!=TK_FOLLOWING || pMWin->eEnd==TK_PRECEDING)
   527    558     ){
   528    559       *pbLoop = 0;
   529    560       windowCodeRowExprStep(pParse, p, pWInfo, regGosub, addrGosub);
   530    561       return;
   531    562     }
   532    563   
   533    564     *pbLoop = 1;
   534    565     windowCodeDefaultStep(pParse, p, pWInfo, regGosub, addrGosub);
   535    566   }
   536    567   
   537    568   

Changes to test/window2.tcl.

   179    179   execsql_test 2.7 {
   180    180     SELECT a, sum(d) OVER (
   181    181       PARTITION BY b
   182    182       ORDER BY d 
   183    183       ROWS BETWEEN 0 PRECEDING AND 0 FOLLOWING
   184    184     ) FROM t1
   185    185   }
          186  +
          187  +execsql_test 2.8 {
          188  +  SELECT a, sum(d) OVER (
          189  +    ORDER BY d 
          190  +    ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
          191  +  ) FROM t1
          192  +}
          193  +
          194  +execsql_test 2.9 {
          195  +  SELECT a, sum(d) OVER (
          196  +    ORDER BY d 
          197  +    ROWS BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING
          198  +  ) FROM t1
          199  +}
          200  +
          201  +execsql_test 2.10 {
          202  +  SELECT a, sum(d) OVER (
          203  +    ORDER BY d 
          204  +    ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
          205  +  ) FROM t1
          206  +}
          207  +
          208  +execsql_test 2.11 {
          209  +  SELECT a, sum(d) OVER (
          210  +    ORDER BY d 
          211  +    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
          212  +  ) FROM t1
          213  +}
          214  +
          215  +execsql_test 2.13 {
          216  +  SELECT a, sum(d) OVER (
          217  +    ORDER BY d 
          218  +    ROWS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING
          219  +  ) FROM t1
          220  +}
          221  +
          222  +
          223  +==========
   186    224   
   187    225   puts $::fd finish_test
   188    226   ==========
   189    227   
   190    228   execsql_test 3.1 {
   191    229     SELECT a, sum(d) OVER (
   192    230       PARTITION BY b ORDER BY d

Changes to test/window2.test.

    91     91     SELECT a, sum(d) OVER (
    92     92       PARTITION BY b
    93     93       ORDER BY d 
    94     94       ROWS BETWEEN 0 PRECEDING AND 0 FOLLOWING
    95     95     ) FROM t1
    96     96   } {2 2   4 4   6 6   1 1   3 3   5 5}
    97     97   
           98  +do_execsql_test 2.8 {
           99  +  SELECT a, sum(d) OVER (
          100  +    ORDER BY d 
          101  +    ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
          102  +  ) FROM t1
          103  +} {1 6   2 9   3 12   4 15   5 11   6 6}
          104  +
          105  +do_execsql_test 2.9 {
          106  +  SELECT a, sum(d) OVER (
          107  +    ORDER BY d 
          108  +    ROWS BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING
          109  +  ) FROM t1
          110  +} {1 6   2 10   3 15   4 21   5 21   6 21}
          111  +
          112  +do_execsql_test 2.10 {
          113  +  SELECT a, sum(d) OVER (
          114  +    ORDER BY d 
          115  +    ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
          116  +  ) FROM t1
          117  +} {1 6   2 9   3 12   4 15   5 11   6 6}
          118  +
          119  +do_execsql_test 2.11 {
          120  +  SELECT a, sum(d) OVER (
          121  +    ORDER BY d 
          122  +    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
          123  +  ) FROM t1
          124  +} {1 1   2 3   3 6   4 9   5 12   6 15}
          125  +
          126  +do_execsql_test 2.13 {
          127  +  SELECT a, sum(d) OVER (
          128  +    ORDER BY d 
          129  +    ROWS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING
          130  +  ) FROM t1
          131  +} {1 21   2 21   3 21   4 20   5 18   6 15}
          132  +
          133  +#==========================================================================
          134  +
    98    135   finish_test
    99    136   #==========================================================================
   100    137   
   101         -do_execsql_test 2.1 {
          138  +do_execsql_test 3.1 {
   102    139     SELECT a, sum(d) OVER (
   103    140       PARTITION BY b ORDER BY d
   104    141       RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
   105    142     ) FROM t1
   106    143   } {2 12   4 10   6 6   1 9   3 8   5 5}
   107    144   
   108         -do_execsql_test 2.2 {
          145  +do_execsql_test 3.2 {
   109    146     SELECT a, sum(d) OVER (
   110    147       ORDER BY b
   111    148       RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
   112    149     ) FROM t1
   113    150   } {2 21   4 21   6 21   1 9   3 9   5 9}
   114    151   
   115         -do_execsql_test 2.3 {
          152  +do_execsql_test 3.3 {
   116    153     SELECT a, sum(d) OVER (
   117    154       ORDER BY d
   118    155       ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
   119    156     ) FROM t1
   120    157   } {1 21   2 21   3 21   4 21   5 21   6 21}
   121    158   
   122         -do_execsql_test 2.4 {
   123         -  SELECT a, sum(d) OVER (
   124         -    ORDER BY d
   125         -    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
   126         -  ) FROM t1
   127         -} {1 3   2 6   3 9   4 12   5 15   6 11}
   128         -
   129         -do_execsql_test 2.5 {
   130         -  SELECT a, sum(d) OVER (
   131         -    ORDER BY d
   132         -    ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
   133         -  ) FROM t1
   134         -} {1 {}   2 1   3 2   4 3   5 4   6 5}
   135         -
   136    159   finish_test