/ Check-in [16168146]
Login

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

Overview
Comment:Add comments to window.c describing how other window frames will be implemented.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | exp-window-functions
Files: files | file ages | folders
SHA3-256: 16168146b202915252f9375aef38e65ca20c5d4aa906e851d4d3a484db57562d
User & Date: dan 2018-05-22 20:35:37
Context
2018-05-22
20:36
Merge latest trunk changes into this branch. check-in: cdb68d2c user: dan tags: exp-window-functions
20:35
Add comments to window.c describing how other window frames will be implemented. check-in: 16168146 user: dan tags: exp-window-functions
2018-05-21
19:45
Begin adding support for more esoteric window frames. check-in: bc4b81d6 user: dan tags: exp-window-functions
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/window.c.

   151    151   **
   152    152   ** ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
   153    153   ** ROWS BETWEEN CURRENT ROW AND CURRENT ROW
   154    154   ** ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
   155    155   **
   156    156   **========================================================================
   157    157   **
   158         -** ROWS BETWEEN UNBOUNDED PRECEDING AND <expr> PRECEDING
   159         -** ROWS BETWEEN <expr> PRECEDING    AND <expr> PRECEDING
   160         -** ROWS BETWEEN <expr> PRECEDING    AND CURRENT ROW
   161         -** ROWS BETWEEN UNBOUNDED PRECEDING AND <expr> FOLLOWING
   162    158   ** ROWS BETWEEN <expr> PRECEDING    AND <expr> FOLLOWING
   163         -** ROWS BETWEEN CURRENT ROW         AND <expr> FOLLOWING
   164         -** ROWS BETWEEN <expr> FOLLOWING    AND <expr> FOLLOWING
   165         -** ROWS BETWEEN <expr> PRECEDING    AND UNBOUNDED FOLLOWING
   166         -** ROWS BETWEEN <expr> FOLLOWING    AND UNBOUNDED FOLLOWING
   167         -**
   168         -**   Cases that involve <expr> PRECEDING or <expr> FOLLOWING.
   169    159   **
   170    160   **   ...
          161  +**     if( new partition ){
          162  +**       Gosub flush_partition
          163  +**     }
   171    164   **     Insert (record in eph-table)
   172    165   **   sqlite3WhereEnd()
          166  +**   Gosub flush_partition
          167  +**
          168  +** flush_partition:
          169  +**   OpenDup (csr -> csr2)
          170  +**   OpenDup (csr -> csr3)
          171  +**   regPrec = <expr1>            // PRECEDING expression
          172  +**   regFollow = <expr2>          // FOLLOWING expression
          173  +**   if( regPrec<0 || regFollow<0 ) throw exception!
          174  +**   Rewind (csr,csr2,csr3)       // if EOF goto flush_partition_done
          175  +**     Aggstep (csr3)
          176  +**     Next(csr3)                 // if EOF fall-through
          177  +**     if( (regFollow--)<=0 ){
          178  +**       AggFinal (xValue)
          179  +**       Gosub addrGosub
          180  +**       Next(csr)                // if EOF goto flush_partition_done
          181  +**       if( (regPrec--)<=0 ){
          182  +**         AggStep (csr2, xInverse)
          183  +**         Next(csr2)
          184  +**       }
          185  +**     }
          186  +** flush_partition_done:
          187  +**   Close (csr2)
          188  +**   Close (csr3)
          189  +**   ResetSorter (csr)
          190  +**   Return
          191  +**
          192  +** ROWS BETWEEN <expr> PRECEDING    AND CURRENT ROW
          193  +** ROWS BETWEEN CURRENT ROW         AND <expr> FOLLOWING
          194  +** ROWS BETWEEN <expr> PRECEDING    AND UNBOUNDED FOLLOWING
          195  +** ROWS BETWEEN UNBOUNDED PRECEDING AND <expr> FOLLOWING
          196  +**
          197  +**   These are similar to the above. For "CURRENT ROW", intialize the
          198  +**   register to 0. For "UNBOUNDED ..." to infinity.
          199  +**
          200  +** ROWS BETWEEN <expr> PRECEDING    AND <expr> PRECEDING
          201  +**
          202  +**   Replace the bit after "Rewind" in the above with:
          203  +**
          204  +**     if( (regFollow--)<=0 ){
          205  +**       AggStep (csr3)
          206  +**       Next (csr3)
          207  +**     }
          208  +**     AggFinal (xValue)
          209  +**     Gosub addrGosub
          210  +**     Next(csr)                  // if EOF goto flush_partition_done
          211  +**     if( (regPrec--)<=0 ){
          212  +**       AggStep (csr2, xInverse)
          213  +**       Next (csr2)
          214  +**     }
          215  +**
          216  +** ROWS BETWEEN <expr> FOLLOWING    AND <expr> FOLLOWING
          217  +**
          218  +**   regFollow = regFollow - regPrec
          219  +**   Rewind (csr,csr2,csr3)       // if EOF goto flush_partition_done
          220  +**     Aggstep (csr3)
          221  +**     Next(csr3)                 // if EOF fall-through
          222  +**     if( (regFollow--)<=0 ){
          223  +**       AggStep (csr2, xInverse)
          224  +**       Next (csr2)
          225  +**       if( (regPrec--)<=0 ){
          226  +**         AggFinal (xValue)
          227  +**         Gosub addrGosub
          228  +**         Next(csr)              // if EOF goto flush_partition_done
          229  +**       }
          230  +**     }
          231  +**
          232  +** ROWS BETWEEN UNBOUNDED PRECEDING AND <expr> PRECEDING
          233  +** ROWS BETWEEN <expr> FOLLOWING    AND UNBOUNDED FOLLOWING
          234  +**
          235  +**   Similar to the above, except with regPrec or regFollow set to infinity,
          236  +**   as appropriate.
          237  +**
          238  +**
   173    239   **
   174    240   */
   175    241   void sqlite3WindowCodeStep(
   176    242     Parse *pParse, 
   177    243     Select *p,
   178    244     WhereInfo *pWInfo,
   179    245     int regGosub, 
................................................................................
   206    272       int nPeer = (pOrderBy ? pOrderBy->nExpr : 0);
   207    273       int addrGoto = 0;
   208    274       int addrJump = 0;
   209    275   
   210    276       if( pPart ){
   211    277         int regNewPart = reg + pMWin->nBufferCol;
   212    278         KeyInfo *pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pPart, 0, 0);
   213         -      addr = sqlite3VdbeAddOp3(v, OP_Compare, regNewPart, pMWin->regPart, nPart);
          279  +      addr = sqlite3VdbeAddOp3(v, OP_Compare, regNewPart, pMWin->regPart,nPart);
   214    280         sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO);
   215    281         addrJump = sqlite3VdbeAddOp3(v, OP_Jump, addr+2, 0, addr+2);
   216    282         for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
   217    283           sqlite3VdbeAddOp2(v, OP_AggFinal, pWin->regAccum, pWin->nArg);
   218    284           sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
   219    285           sqlite3VdbeAddOp2(v, OP_Copy, pWin->regAccum, pWin->regResult);
   220    286         }
................................................................................
   241    307         if( addrGoto ) sqlite3VdbeJumpHere(v, addrGoto);
   242    308       }
   243    309   
   244    310       sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrGosub);
   245    311       sqlite3VdbeAddOp1(v, OP_ResetSorter, pMWin->iEphCsr);
   246    312       sqlite3VdbeAddOp3(
   247    313           v, OP_Copy, reg+pMWin->nBufferCol, pMWin->regPart, nPart+nPeer-1
   248         -        );
          314  +    );
   249    315   
   250    316       sqlite3VdbeJumpHere(v, addrJump);
   251    317     }
   252    318   
   253    319     /* Invoke step function for window functions */
   254    320     for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
   255    321       sqlite3VdbeAddOp3(v, OP_AggStep0, 0, reg+pWin->iArgCol, pWin->regAccum);

Changes to test/window2.tcl.

   155    155   
   156    156   execsql_test 2.3 {
   157    157     SELECT a, sum(d) OVER (
   158    158       ORDER BY d
   159    159       ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
   160    160     ) FROM t1
   161    161   }
          162  +
          163  +execsql_test 2.4 {
          164  +  SELECT a, sum(d) OVER (
          165  +    ORDER BY d
          166  +    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
          167  +  ) FROM t1
          168  +}
          169  +
          170  +execsql_test 2.5 {
          171  +  SELECT a, sum(d) OVER (
          172  +    ORDER BY d
          173  +    ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
          174  +  ) FROM t1
          175  +}
   162    176   
   163    177   finish_test
   164    178   
   165    179   

Changes to test/window2.test.

    62     62   do_execsql_test 2.3 {
    63     63     SELECT a, sum(d) OVER (
    64     64       ORDER BY d
    65     65       ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    66     66     ) FROM t1
    67     67   } {1 21   2 21   3 21   4 21   5 21   6 21}
    68     68   
           69  +do_execsql_test 2.4 {
           70  +  SELECT a, sum(d) OVER (
           71  +    ORDER BY d
           72  +    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
           73  +  ) FROM t1
           74  +} {1 3   2 6   3 9   4 12   5 15   6 11}
           75  +
           76  +do_execsql_test 2.5 {
           77  +  SELECT a, sum(d) OVER (
           78  +    ORDER BY d
           79  +    ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
           80  +  ) FROM t1
           81  +} {1 {}   2 1   3 2   4 3   5 4   6 5}
           82  +
    69     83   finish_test