/ Check-in [25ff7091]
Login

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

Overview
Comment:Allow real values to be used in PRECEDING and FOLLOWING expressions for RANGE window frames.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | window-functions
Files: files | file ages | folders
SHA3-256:25ff7091cb12c63b1864ce68a9151f8432af5804b5ae905a2175761ab4b9fdd8
User & Date: dan 2019-03-12 18:28:51
Wiki:window-functions
Context
2019-03-13
08:28
Minor optimization in sqlite3WindowCodeStep(). check-in: b1322ffb user: dan tags: window-functions
2019-03-12
18:28
Allow real values to be used in PRECEDING and FOLLOWING expressions for RANGE window frames. check-in: 25ff7091 user: dan tags: window-functions
15:21
Expand on header comment for sqlite3WindowCodeStep(). Further simplify the implementation of the same. check-in: 5129bcc9 user: dan tags: window-functions
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/vdbe.c.

  1719   1719     pIn1 = &aMem[pOp->p1];
  1720   1720     memAboutToChange(p, pIn1);
  1721   1721     sqlite3VdbeMemIntegerify(pIn1);
  1722   1722     pIn1->u.i += pOp->p2;
  1723   1723     break;
  1724   1724   }
  1725   1725   
  1726         -/* Opcode: MustBeInt P1 P2 * * *
         1726  +/* Opcode: MustBeInt P1 P2 * * P5
  1727   1727   ** 
  1728         -** Force the value in register P1 to be an integer.  If the value
  1729         -** in P1 is not an integer and cannot be converted into an integer
  1730         -** without data loss, then jump immediately to P2, or if P2==0
         1728  +** If P5 is 0, force the value in register P1 to be an integer. If 
         1729  +** the value in P1 is not an integer and cannot be converted into an 
         1730  +** integer without data loss, then jump immediately to P2, or if P2==0
  1731   1731   ** raise an SQLITE_MISMATCH exception.
         1732  +**
         1733  +** Or, if P5 is non-zero, then force the register in P1 to be a number
         1734  +** (real or integer). Jump to P2 if this cannot be accomplished without
         1735  +** data loss. P2 must be non-zero in this case.
  1732   1736   */
  1733   1737   case OP_MustBeInt: {            /* jump, in1 */
         1738  +  u8 f;
         1739  +  f = (pOp->p5 ? (MEM_Int|MEM_Real) : MEM_Int);
  1734   1740     pIn1 = &aMem[pOp->p1];
  1735         -  if( (pIn1->flags & MEM_Int)==0 ){
         1741  +  if( (pIn1->flags & f)==0 ){
  1736   1742       applyAffinity(pIn1, SQLITE_AFF_NUMERIC, encoding);
  1737         -    VdbeBranchTaken((pIn1->flags&MEM_Int)==0, 2);
  1738         -    if( (pIn1->flags & MEM_Int)==0 ){
         1743  +    VdbeBranchTaken((pIn1->flags&f)==0, 2);
         1744  +    if( (pIn1->flags & f)==0 ){
  1739   1745         if( pOp->p2==0 ){
  1740   1746           rc = SQLITE_MISMATCH;
  1741   1747           goto abort_due_to_error;
  1742   1748         }else{
  1743   1749           goto jump_to_p2;
  1744   1750         }
  1745   1751       }
  1746   1752     }
  1747         -  MemSetTypeFlag(pIn1, MEM_Int);
         1753  +  if( f==MEM_Int ) MemSetTypeFlag(pIn1, MEM_Int);
  1748   1754     break;
  1749   1755   }
  1750   1756   
  1751   1757   #ifndef SQLITE_OMIT_FLOATING_POINT
  1752   1758   /* Opcode: RealAffinity P1 * * * *
  1753   1759   **
  1754   1760   ** If register P1 holds an integer convert it to a real value.

Changes to src/window.c.

  1164   1164         assert( pMWin->iEphCsr );
  1165   1165         pWin->csrApp = pParse->nTab++;
  1166   1166         sqlite3VdbeAddOp2(v, OP_OpenDup, pWin->csrApp, pMWin->iEphCsr);
  1167   1167       }
  1168   1168     }
  1169   1169   }
  1170   1170   
         1171  +#define WINDOW_STARTING_INT  0
         1172  +#define WINDOW_ENDING_INT    1
         1173  +#define WINDOW_NTH_VALUE_INT 2
         1174  +#define WINDOW_STARTING_NUM  3
         1175  +#define WINDOW_ENDING_NUM    4
         1176  +
  1171   1177   /*
  1172   1178   ** A "PRECEDING <expr>" (eCond==0) or "FOLLOWING <expr>" (eCond==1) or the
  1173   1179   ** value of the second argument to nth_value() (eCond==2) has just been
  1174   1180   ** evaluated and the result left in register reg. This function generates VM
  1175   1181   ** code to check that the value is a non-negative integer and throws an
  1176   1182   ** exception if it is not.
  1177   1183   */
  1178         -static void windowCheckIntValue(Parse *pParse, int reg, int eCond){
         1184  +static void windowCheckValue(Parse *pParse, int reg, int eCond){
  1179   1185     static const char *azErr[] = {
  1180   1186       "frame starting offset must be a non-negative integer",
  1181   1187       "frame ending offset must be a non-negative integer",
  1182         -    "second argument to nth_value must be a positive integer"
         1188  +    "second argument to nth_value must be a positive integer",
         1189  +    "frame starting offset must be a non-negative number",
         1190  +    "frame ending offset must be a non-negative number",
  1183   1191     };
  1184         -  static int aOp[] = { OP_Ge, OP_Ge, OP_Gt };
         1192  +  static int aOp[] = { OP_Ge, OP_Ge, OP_Gt, OP_Ge, OP_Ge };
  1185   1193     Vdbe *v = sqlite3GetVdbe(pParse);
  1186   1194     int regZero = sqlite3GetTempReg(pParse);
  1187         -  assert( eCond==0 || eCond==1 || eCond==2 );
         1195  +  assert( eCond>=0 && eCond<ArraySize(azErr) );
  1188   1196     sqlite3VdbeAddOp2(v, OP_Integer, 0, regZero);
  1189   1197     sqlite3VdbeAddOp2(v, OP_MustBeInt, reg, sqlite3VdbeCurrentAddr(v)+2);
         1198  +  if( eCond>=WINDOW_STARTING_NUM ) sqlite3VdbeChangeP5(v, 1);
  1190   1199     VdbeCoverageIf(v, eCond==0);
  1191   1200     VdbeCoverageIf(v, eCond==1);
  1192   1201     VdbeCoverageIf(v, eCond==2);
  1193   1202     sqlite3VdbeAddOp3(v, aOp[eCond], regZero, sqlite3VdbeCurrentAddr(v)+2, reg);
  1194   1203     VdbeCoverageNeverNullIf(v, eCond==0);
  1195   1204     VdbeCoverageNeverNullIf(v, eCond==1);
  1196   1205     VdbeCoverageNeverNullIf(v, eCond==2);
................................................................................
  1379   1388         int csr = pWin->csrApp;
  1380   1389         int lbl = sqlite3VdbeMakeLabel(pParse);
  1381   1390         int tmpReg = sqlite3GetTempReg(pParse);
  1382   1391         sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regResult);
  1383   1392   
  1384   1393         if( pFunc->zName==nth_valueName ){
  1385   1394           sqlite3VdbeAddOp3(v, OP_Column, pMWin->iEphCsr, pWin->iArgCol+1,tmpReg);
  1386         -        windowCheckIntValue(pParse, tmpReg, 2);
         1395  +        windowCheckValue(pParse, tmpReg, 2);
  1387   1396         }else{
  1388   1397           sqlite3VdbeAddOp2(v, OP_Integer, 1, tmpReg);
  1389   1398         }
  1390   1399         sqlite3VdbeAddOp3(v, OP_Add, tmpReg, pWin->regApp, tmpReg);
  1391   1400         sqlite3VdbeAddOp3(v, OP_Gt, pWin->regApp+1, lbl, tmpReg);
  1392   1401         VdbeCoverageNeverNull(v);
  1393   1402         sqlite3VdbeAddOp3(v, OP_SeekRowid, csr, 0, tmpReg);
................................................................................
  2210   2219     addrIfNot = sqlite3VdbeAddOp1(v, OP_IfNot, pMWin->regFirst);
  2211   2220   
  2212   2221     /* This block is run for the first row of each partition */
  2213   2222     s.regArg = windowInitAccum(pParse, pMWin);
  2214   2223   
  2215   2224     if( regStart ){
  2216   2225       sqlite3ExprCode(pParse, pMWin->pStart, regStart);
  2217         -    windowCheckIntValue(pParse, regStart, 0);
         2226  +    windowCheckValue(pParse, regStart, 0 + (pMWin->eType==TK_RANGE ? 3 : 0));
  2218   2227     }
  2219   2228     if( regEnd ){
  2220   2229       sqlite3ExprCode(pParse, pMWin->pEnd, regEnd);
  2221         -    windowCheckIntValue(pParse, regEnd, 1);
         2230  +    windowCheckValue(pParse, regEnd, 1 + (pMWin->eType==TK_RANGE ? 3 : 0));
  2222   2231     }
  2223   2232   
  2224   2233     if( pMWin->eStart==pMWin->eEnd && regStart && regEnd ){
  2225   2234       int op = ((pMWin->eStart==TK_FOLLOWING) ? OP_Ge : OP_Le);
  2226   2235       int addrGe = sqlite3VdbeAddOp3(v, op, regStart, 0, regEnd);
  2227   2236       windowAggFinal(pParse, pMWin, 0);
  2228   2237       sqlite3VdbeAddOp2(v, OP_Rewind, s.current.csr, 1);

Changes to test/window8.tcl.

    89     89     "
    90     90   }
    91     91   
    92     92   ==========
    93     93   
    94     94   execsql_test 2.0 {
    95     95     DROP TABLE IF EXISTS t1;
    96         -  CREATE TABLE t1(a INTEGER, b INTEGER);
           96  +  CREATE TABLE t1(a REAL, b INTEGER);
    97     97     INSERT INTO t1 VALUES
    98     98         (5, 10), (10, 20), (13, 26), (13, 26), 
    99     99         (15, 30), (20, 40), (22,80), (30, 90);
   100    100   }
   101    101   
   102    102   foreach {tn frame} {
   103    103     1 { ORDER BY a RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING }
   104    104     2 { ORDER BY a RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING }
   105    105     3 { ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING }
   106    106     4 { ORDER BY a DESC RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING }
   107    107     5 { ORDER BY a DESC RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING }
   108    108     6 { ORDER BY a DESC RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING }
          109  +
          110  +  7  { ORDER BY a RANGE BETWEEN 5.1 PRECEDING AND 5.3 FOLLOWING }
          111  +  8  { ORDER BY a RANGE BETWEEN 10.2 PRECEDING AND 5.4 PRECEDING }
          112  +  9  { ORDER BY a RANGE BETWEEN 2.6 FOLLOWING AND 3.5 FOLLOWING }
          113  +  10 { ORDER BY a DESC RANGE BETWEEN 5.7 PRECEDING AND 5.8 FOLLOWING }
          114  +  11 { ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND 5.9 PRECEDING }
          115  +  12 { ORDER BY a DESC RANGE BETWEEN 2.1 FOLLOWING AND UNBOUNDED FOLLOWING }
   109    116   } {
   110         -  execsql_test 2.$tn "SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ($frame)"
          117  +  execsql_test 2.$tn "
          118  +    SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ($frame)
          119  +  "
   111    120   }
   112    121   
   113    122   
   114    123   finish_test
   115    124   
   116    125   

Changes to test/window8.test.

   433    433     SELECT a, b, min(c) OVER (ORDER BY a,b  GROUPS BETWEEN 1 FOLLOWING         AND UNBOUNDED FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
   434    434   } {AA aa 102   AA aa 102   AA aa 102   AA aa 102   AA bb 102   AA bb 102   AA bb 102   AA bb 102   BB aa 102   BB aa 102   BB aa 102   BB aa 102   BB aa 102   BB aa 102   BB bb 102   BB bb 102   BB bb 102   BB bb 102   BB bb 102   BB bb 102   CC aa 102   CC aa 102   CC aa 102   CC aa 102   CC bb 102   CC bb 102   DD aa 102   DD aa 102   DD aa 102   DD bb 102   DD bb 102   DD bb 102   DD bb 102   EE aa 102   EE aa 102   EE bb 102   EE bb 102   EE bb 102   FF aa 102   FF aa 102   FF aa 102   FF aa 102   FF bb 113   FF bb 113   FF bb 113   FF bb 113   FF bb 113   FF bb 113   GG aa 113   GG aa 113   GG aa 113   GG aa 113   GG bb 113   GG bb 113   GG bb 113   GG bb 113   HH aa 113   HH aa 113   HH aa 113   HH bb 113   HH bb 113   HH bb 113   HH bb 113   HH bb 113   HH bb 113   II aa 113   II aa 113   II bb 113   II bb 113   II bb 113   II bb 113   II bb 113   JJ aa 257   JJ aa 257   JJ aa 257   JJ aa 257   JJ bb {}   JJ bb {}   JJ bb {}   JJ bb {}}
   435    435   
   436    436   #==========================================================================
   437    437   
   438    438   do_execsql_test 2.0 {
   439    439     DROP TABLE IF EXISTS t1;
   440         -  CREATE TABLE t1(a INTEGER, b INTEGER);
          440  +  CREATE TABLE t1(a REAL, b INTEGER);
   441    441     INSERT INTO t1 VALUES
   442    442         (5, 10), (10, 20), (13, 26), (13, 26), 
   443    443         (15, 30), (20, 40), (22,80), (30, 90);
   444    444   } {}
   445    445   
   446    446   do_execsql_test 2.1 {
   447         -  SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING )
          447  +  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING )
   448    448   } {5 30   10 112   13 102   13 102   15 142   20 150   22 120   30 90}
   449    449   
   450    450   do_execsql_test 2.2 {
   451         -  SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING )
          451  +  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING )
   452    452   } {5 {}   10 10   13 10   13 10   15 30   20 102   22 82   30 120}
   453    453   
   454    454   do_execsql_test 2.3 {
   455         -  SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING )
          455  +  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING )
   456    456   } {5 {}   10 52   13 30   13 30   15 {}   20 80   22 {}   30 {}}
   457    457   
   458    458   do_execsql_test 2.4 {
   459         -  SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING )
          459  +  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING )
   460    460   } {30 90   22 120   20 150   15 142   13 102   13 102   10 112   5 30}
   461    461   
   462    462   do_execsql_test 2.5 {
   463         -  SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING )
          463  +  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING )
   464    464   } {30 {}   22 90   20 90   15 120   13 120   13 120   10 70   5 102}
   465    465   
   466    466   do_execsql_test 2.6 {
   467         -  SELECT a, sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING )
          467  +  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING )
   468    468   } {30 {}   22 40   20 {}   15 52   13 20   13 20   10 {}   5 {}}
   469    469   
          470  +do_execsql_test 2.7 {
          471  +  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 5.1 PRECEDING AND 5.3 FOLLOWING )
          472  +} {5 30   10 112   13 102   13 102   15 142   20 150   22 120   30 90}
          473  +
          474  +do_execsql_test 2.8 {
          475  +  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 10.2 PRECEDING AND 5.4 PRECEDING )
          476  +} {5 {}   10 {}   13 10   13 10   15 10   20 72   22 82   30 120}
          477  +
          478  +do_execsql_test 2.9 {
          479  +  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 2.6 FOLLOWING AND 3.5 FOLLOWING )
          480  +} {5 {}   10 52   13 {}   13 {}   15 {}   20 {}   22 {}   30 {}}
          481  +
          482  +do_execsql_test 2.10 {
          483  +  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 5.7 PRECEDING AND 5.8 FOLLOWING )
          484  +} {30 90   22 120   20 150   15 142   13 102   13 102   10 112   5 30}
          485  +
          486  +do_execsql_test 2.11 {
          487  +  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND 5.9 PRECEDING )
          488  +} {30 {}   22 90   20 90   15 170   13 210   13 210   10 210   5 292}
          489  +
          490  +do_execsql_test 2.12 {
          491  +  SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 2.1 FOLLOWING AND UNBOUNDED FOLLOWING )
          492  +} {30 232   22 112   20 112   15 30   13 30   13 30   10 10   5 {}}
          493  +
   470    494   finish_test