/ Check-in [2c85668a]
Login

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

Overview
Comment:More fixes for different window frame types.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | exp-window-functions
Files: files | file ages | folders
SHA3-256:2c85668a0f86bcfd3de4c65847ac252e136ce97409a2249c5f4f58e3756e9a69
User & Date: dan 2018-05-26 21:17:29
Context
2018-05-28
18:30
Further window frame tests and fixes. check-in: e74c6e91 user: dan tags: exp-window-functions
2018-05-26
21:17
More fixes for different window frame types. check-in: 2c85668a user: dan tags: exp-window-functions
2018-05-25
20:30
Fix "RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING" window frame processing. check-in: b4e9c686 user: dan tags: exp-window-functions
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to src/window.c.

   346    346       if( pMWin->pStart && pMWin->eStart==TK_FOLLOWING ){
   347    347         assert( pMWin->eEnd==TK_FOLLOWING );
   348    348         sqlite3VdbeAddOp3(v, OP_Subtract, regStart, regEnd, regEnd);
   349    349       }
   350    350     }
   351    351   
   352    352     for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
   353         -    sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regResult);
   354    353       sqlite3VdbeAddOp2(v, OP_Null, 0, pWin->regAccum);
   355    354     }
   356    355   
   357    356     sqlite3VdbeAddOp2(v, OP_Rewind, pMWin->iEphCsr, lblFlushDone);
   358    357     sqlite3VdbeAddOp2(v, OP_Rewind, csrStart, lblFlushDone);
   359    358     sqlite3VdbeChangeP5(v, 1);
   360    359     sqlite3VdbeAddOp2(v, OP_Rewind, csrEnd, lblFlushDone);
................................................................................
   459    458     sqlite3VdbeAddOp1(v, OP_ResetSorter, pMWin->iEphCsr);
   460    459     sqlite3VdbeAddOp1(v, OP_Return, regFlushPart);
   461    460   
   462    461     /* Jump to here to skip over flush_partition */
   463    462     sqlite3VdbeJumpHere(v, addrGoto);
   464    463   }
   465    464   
          465  +/*
          466  +** RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
          467  +**
          468  +**   ...
          469  +**     if( new partition ){
          470  +**       AggFinal (xFinalize)
          471  +**       Gosub addrGosub
          472  +**       ResetSorter eph-table
          473  +**     }
          474  +**     else if( new peer ){
          475  +**       AggFinal (xValue)
          476  +**       Gosub addrGosub
          477  +**       ResetSorter eph-table
          478  +**     }
          479  +**     AggStep
          480  +**     Insert (record into eph-table)
          481  +**   sqlite3WhereEnd()
          482  +**   AggFinal (xFinalize)
          483  +**   Gosub addrGosub
          484  +*/
   466    485   static void windowCodeDefaultStep(
   467    486     Parse *pParse, 
   468    487     Select *p,
   469    488     WhereInfo *pWInfo,
   470    489     int regGosub, 
   471    490     int addrGosub
   472    491   ){
................................................................................
   476    495     int k;
   477    496     int iSubCsr = p->pSrc->a[0].iCursor;
   478    497     int nSub = p->pSrc->a[0].pTab->nCol;
   479    498     int reg = pParse->nMem+1;
   480    499     int regRecord = reg+nSub;
   481    500     int regRowid = regRecord+1;
   482    501     int addr;
          502  +
          503  +  assert( pMWin->eType==TK_RANGE 
          504  +      || (pMWin->eStart==TK_UNBOUNDED && pMWin->eEnd==TK_CURRENT)
          505  +  );
   483    506   
   484    507     pParse->nMem += nSub + 2;
   485    508   
   486    509     /* Martial the row returned by the sub-select into an array of 
   487    510     ** registers. */
   488    511     for(k=0; k<nSub; k++){
   489    512       sqlite3VdbeAddOp3(v, OP_Column, iSubCsr, k, reg+k);
................................................................................
   514    537         }
   515    538       }
   516    539   
   517    540       if( pOrderBy ){
   518    541         int regNewPeer = reg + pMWin->nBufferCol + nPart;
   519    542         int regPeer = pMWin->regPart + nPart;
   520    543   
   521         -      KeyInfo *pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pOrderBy, 0, 0);
   522    544         if( addrJump ) sqlite3VdbeJumpHere(v, addrJump);
          545  +      if( pMWin->eType==TK_RANGE ){
          546  +        KeyInfo *pKeyInfo = sqlite3KeyInfoFromExprList(pParse, pOrderBy, 0, 0);
   523    547         addr = sqlite3VdbeAddOp3(v, OP_Compare, regNewPeer, regPeer, nPeer);
   524    548         sqlite3VdbeAppendP4(v, (void*)pKeyInfo, P4_KEYINFO);
   525    549         addrJump = sqlite3VdbeAddOp3(v, OP_Jump, addr+2, 0, addr+2);
          550  +      }else{
          551  +        addrJump = 0;
          552  +      }
   526    553         for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
   527    554           sqlite3VdbeAddOp3(v, 
   528    555               OP_AggFinal, pWin->regAccum, pWin->nArg, pWin->regResult
   529    556           );
   530    557           sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
   531    558         }
   532    559         if( addrGoto ) sqlite3VdbeJumpHere(v, addrGoto);
................................................................................
   534    561   
   535    562       sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrGosub);
   536    563       sqlite3VdbeAddOp1(v, OP_ResetSorter, pMWin->iEphCsr);
   537    564       sqlite3VdbeAddOp3(
   538    565           v, OP_Copy, reg+pMWin->nBufferCol, pMWin->regPart, nPart+nPeer-1
   539    566       );
   540    567   
   541         -    sqlite3VdbeJumpHere(v, addrJump);
          568  +    if( addrJump ) sqlite3VdbeJumpHere(v, addrJump);
   542    569     }
   543    570   
   544    571     /* Invoke step function for window functions */
   545    572     for(pWin=pMWin; pWin; pWin=pWin->pNextWin){
   546    573       sqlite3VdbeAddOp3(v, OP_AggStep0, 0, reg+pWin->iArgCol, pWin->regAccum);
   547    574       sqlite3VdbeAppendP4(v, pWin->pFunc, P4_FUNCDEF);
   548    575       sqlite3VdbeChangeP5(v, (u8)pWin->nArg);
................................................................................
   567    594       sqlite3VdbeAddOp2(v, OP_Copy, pWin->regAccum, pWin->regResult);
   568    595     }
   569    596     sqlite3VdbeAddOp2(v, OP_Gosub, regGosub, addrGosub);
   570    597   }
   571    598   
   572    599   
   573    600   /*
   574         -** RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
   575         -**
   576         -**   ...
   577         -**     if( new partition ){
   578         -**       AggFinal (xFinalize)
   579         -**       Gosub addrGosub
   580         -**       ResetSorter eph-table
   581         -**     }
   582         -**     else if( new peer ){
   583         -**       AggFinal (xValue)
   584         -**       Gosub addrGosub
   585         -**       ResetSorter eph-table
   586         -**     }
   587         -**     AggStep
   588         -**     Insert (record into eph-table)
   589         -**   sqlite3WhereEnd()
   590         -**   AggFinal (xFinalize)
   591         -**   Gosub addrGosub
   592         -**
   593    601   ** RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
   594    602   **
   595    603   **   As above, except take no action for a "new peer". Invoke
   596    604   **   the sub-routine once only for each partition.
   597    605   **
   598    606   ** RANGE BETWEEN CURRENT ROW AND CURRENT ROW
   599    607   **

Changes to test/window2.tcl.

   342    342   execsql_test 2.30 {
   343    343     SELECT a, sum(d) OVER (
   344    344       ORDER BY b 
   345    345       RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
   346    346     ) FROM t1
   347    347   }
   348    348   
   349         -==========
   350         -puts $::fd finish_test
          349  +execsql_test 3.1 {
          350  +  SELECT a, sum(d) OVER (
          351  +    PARTITION BY b ORDER BY d
          352  +    RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
          353  +  ) FROM t1
          354  +}
          355  +
          356  +execsql_test 3.2 {
          357  +  SELECT a, sum(d) OVER (
          358  +    ORDER BY b
          359  +    RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
          360  +  ) FROM t1
          361  +}
          362  +
          363  +execsql_test 3.3 {
          364  +  SELECT a, sum(d) OVER (
          365  +    ORDER BY d
          366  +    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
          367  +  ) FROM t1
          368  +}
          369  +
          370  +execsql_test 3.4 {
          371  +  SELECT a, sum(d) OVER (
          372  +    ORDER BY d/2
          373  +    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
          374  +  ) FROM t1
          375  +}
          376  +
          377  +#puts $::fd finish_test
          378  +
   351    379   ==========
   352    380   
   353         -# execsql_test 3.1 {
   354         -#   SELECT a, sum(d) OVER (
   355         -#     PARTITION BY b ORDER BY d
   356         -#     RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
   357         -#   ) FROM t1
   358         -# }
   359         -# 
   360         -# execsql_test 3.2 {
   361         -#   SELECT a, sum(d) OVER (
   362         -#     ORDER BY b
   363         -#     RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
   364         -#   ) FROM t1
   365         -# }
   366         -# 
   367         -# execsql_test 3.3 {
   368         -#   SELECT a, sum(d) OVER (
   369         -#     ORDER BY d
   370         -#     ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
   371         -#   ) FROM t1
   372         -# }
          381  +execsql_test 4.0 {
          382  +  DROP TABLE IF EXISTS t2;
          383  +  CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER);
          384  +  INSERT INTO t2(a, b) VALUES
          385  +  (1,0), (2,74), (3,41), (4,74), (5,23), (6,99), (7,26), (8,33), (9,2),
          386  +  (10,89), (11,81), (12,96), (13,59), (14,38), (15,68), (16,39), (17,62),
          387  +  (18,91), (19,46), (20,6), (21,99), (22,97), (23,27), (24,46), (25,78),
          388  +  (26,54), (27,97), (28,8), (29,67), (30,29), (31,93), (32,84), (33,77),
          389  +  (34,23), (35,16), (36,16), (37,93), (38,65), (39,35), (40,47), (41,7),
          390  +  (42,86), (43,74), (44,61), (45,91), (46,85), (47,24), (48,85), (49,43),
          391  +  (50,59), (51,12), (52,32), (53,56), (54,3), (55,91), (56,22), (57,90),
          392  +  (58,55), (59,15), (60,28), (61,89), (62,25), (63,47), (64,1), (65,56),
          393  +  (66,40), (67,43), (68,56), (69,16), (70,75), (71,36), (72,89), (73,98),
          394  +  (74,76), (75,81), (76,4), (77,94), (78,42), (79,30), (80,78), (81,33),
          395  +  (82,29), (83,53), (84,63), (85,2), (86,87), (87,37), (88,80), (89,84),
          396  +  (90,72), (91,41), (92,9), (93,61), (94,73), (95,95), (96,65), (97,13),
          397  +  (98,58), (99,96), (100,98), (101,1), (102,21), (103,74), (104,65), (105,35),
          398  +  (106,5), (107,73), (108,11), (109,51), (110,87), (111,41), (112,12), (113,8),
          399  +  (114,20), (115,31), (116,31), (117,15), (118,95), (119,22), (120,73), 
          400  +  (121,79), (122,88), (123,34), (124,8), (125,11), (126,49), (127,34), 
          401  +  (128,90), (129,59), (130,96), (131,60), (132,55), (133,75), (134,77),
          402  +  (135,44), (136,2), (137,7), (138,85), (139,57), (140,74), (141,29), (142,70),
          403  +  (143,59), (144,19), (145,39), (146,26), (147,26), (148,47), (149,80),
          404  +  (150,90), (151,36), (152,58), (153,47), (154,9), (155,72), (156,72), (157,66),
          405  +  (158,33), (159,93), (160,75), (161,64), (162,81), (163,9), (164,23), (165,37),
          406  +  (166,13), (167,12), (168,14), (169,62), (170,91), (171,36), (172,91),
          407  +  (173,33), (174,15), (175,34), (176,36), (177,99), (178,3), (179,95), (180,69),
          408  +  (181,58), (182,52), (183,30), (184,50), (185,84), (186,10), (187,84),
          409  +  (188,33), (189,21), (190,39), (191,44), (192,58), (193,30), (194,38),
          410  +  (195,34), (196,83), (197,27), (198,82), (199,17), (200,7);
          411  +}
          412  +
          413  +execsql_test 4.1 {
          414  +  SELECT a, sum(b) OVER (
          415  +    PARTITION BY (b%10)
          416  +    ORDER BY b
          417  +  ) FROM t2 ORDER BY a;
          418  +}
          419  +
          420  +execsql_test 4.2 {
          421  +  SELECT a, sum(b) OVER (
          422  +    PARTITION BY (b%10)
          423  +    ORDER BY b
          424  +    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
          425  +  ) FROM t2 ORDER BY a;
          426  +}
          427  +
          428  +execsql_test 4.3 {
          429  +  SELECT b, sum(b) OVER (
          430  +    ORDER BY b
          431  +    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
          432  +  ) FROM t2 ORDER BY b;
          433  +}
          434  +
          435  +
   373    436   
   374    437   finish_test
   375    438   
   376    439   

Changes to test/window2.test.

   254    254   do_execsql_test 2.30 {
   255    255     SELECT a, sum(d) OVER (
   256    256       ORDER BY b 
   257    257       RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
   258    258     ) FROM t1
   259    259   } {2 21   4 21   6 21   1 9   3 9   5 9}
   260    260   
          261  +do_execsql_test 3.1 {
          262  +  SELECT a, sum(d) OVER (
          263  +    PARTITION BY b ORDER BY d
          264  +    RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
          265  +  ) FROM t1
          266  +} {2 12   4 10   6 6   1 9   3 8   5 5}
          267  +
          268  +do_execsql_test 3.2 {
          269  +  SELECT a, sum(d) OVER (
          270  +    ORDER BY b
          271  +    RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
          272  +  ) FROM t1
          273  +} {2 21   4 21   6 21   1 9   3 9   5 9}
          274  +
          275  +do_execsql_test 3.3 {
          276  +  SELECT a, sum(d) OVER (
          277  +    ORDER BY d
          278  +    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
          279  +  ) FROM t1
          280  +} {1 21   2 21   3 21   4 21   5 21   6 21}
          281  +
          282  +do_execsql_test 3.4 {
          283  +  SELECT a, sum(d) OVER (
          284  +    ORDER BY d/2
          285  +    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
          286  +  ) FROM t1
          287  +} {1 1   2 3   3 6   4 10   5 15   6 21}
          288  +
   261    289   #==========================================================================
   262    290   
   263         -finish_test
   264         -#==========================================================================
          291  +do_execsql_test 4.0 {
          292  +  DROP TABLE IF EXISTS t2;
          293  +  CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER);
          294  +  INSERT INTO t2(a, b) VALUES
          295  +  (1,0), (2,74), (3,41), (4,74), (5,23), (6,99), (7,26), (8,33), (9,2),
          296  +  (10,89), (11,81), (12,96), (13,59), (14,38), (15,68), (16,39), (17,62),
          297  +  (18,91), (19,46), (20,6), (21,99), (22,97), (23,27), (24,46), (25,78),
          298  +  (26,54), (27,97), (28,8), (29,67), (30,29), (31,93), (32,84), (33,77),
          299  +  (34,23), (35,16), (36,16), (37,93), (38,65), (39,35), (40,47), (41,7),
          300  +  (42,86), (43,74), (44,61), (45,91), (46,85), (47,24), (48,85), (49,43),
          301  +  (50,59), (51,12), (52,32), (53,56), (54,3), (55,91), (56,22), (57,90),
          302  +  (58,55), (59,15), (60,28), (61,89), (62,25), (63,47), (64,1), (65,56),
          303  +  (66,40), (67,43), (68,56), (69,16), (70,75), (71,36), (72,89), (73,98),
          304  +  (74,76), (75,81), (76,4), (77,94), (78,42), (79,30), (80,78), (81,33),
          305  +  (82,29), (83,53), (84,63), (85,2), (86,87), (87,37), (88,80), (89,84),
          306  +  (90,72), (91,41), (92,9), (93,61), (94,73), (95,95), (96,65), (97,13),
          307  +  (98,58), (99,96), (100,98), (101,1), (102,21), (103,74), (104,65), (105,35),
          308  +  (106,5), (107,73), (108,11), (109,51), (110,87), (111,41), (112,12), (113,8),
          309  +  (114,20), (115,31), (116,31), (117,15), (118,95), (119,22), (120,73), 
          310  +  (121,79), (122,88), (123,34), (124,8), (125,11), (126,49), (127,34), 
          311  +  (128,90), (129,59), (130,96), (131,60), (132,55), (133,75), (134,77),
          312  +  (135,44), (136,2), (137,7), (138,85), (139,57), (140,74), (141,29), (142,70),
          313  +  (143,59), (144,19), (145,39), (146,26), (147,26), (148,47), (149,80),
          314  +  (150,90), (151,36), (152,58), (153,47), (154,9), (155,72), (156,72), (157,66),
          315  +  (158,33), (159,93), (160,75), (161,64), (162,81), (163,9), (164,23), (165,37),
          316  +  (166,13), (167,12), (168,14), (169,62), (170,91), (171,36), (172,91),
          317  +  (173,33), (174,15), (175,34), (176,36), (177,99), (178,3), (179,95), (180,69),
          318  +  (181,58), (182,52), (183,30), (184,50), (185,84), (186,10), (187,84),
          319  +  (188,33), (189,21), (190,39), (191,44), (192,58), (193,30), (194,38),
          320  +  (195,34), (196,83), (197,27), (198,82), (199,17), (200,7);
          321  +} {}
          322  +
          323  +do_execsql_test 4.1 {
          324  +  SELECT a, sum(b) OVER (
          325  +    PARTITION BY (b%10)
          326  +    ORDER BY b
          327  +  ) FROM t2 ORDER BY a;
          328  +} {1 0   2 754   3 251   4 754   5 101   6 1247   7 132   8 266   9 6   10 950   11 667   12 1052   13 535   14 128   15 428   16 250   17 336   18 1122   19 368   20 6   21 1247   22 1000   23 92   24 368   25 584   26 320   27 1000   28 24   29 478   30 133   31 1049   32 1090   33 632   34 101   35 54   36 54   37 1049   38 450   39 145   40 354   41 21   42 764   43 754   44 424   45 1122   46 930   47 42   48 930   49 352   50 535   51 42   52 118   53 536   54 6   55 1122   56 86   57 770   58 255   59 50   60 52   61 950   62 75   63 354   64 2   65 536   66 160   67 352   68 536   69 54   70 675   71 276   72 950   73 868   74 678   75 667   76 4   77 1184   78 160   79 120   80 584   81 266   82 133   83 405   84 468   85 6   86 806   87 166   88 500   89 1090   90 552   91 251   92 27   93 424   94 687   95 1215   96 450   97 32   98 360   99 1052   100 868   101 2   102 66   103 754   104 450   105 145   106 5   107 687   108 24   109 302   110 806   111 251   112 42   113 24   114 30   115 128   116 128   117 50   118 1215   119 86   120 687   121 683   122 672   123 178   124 24   125 24   126 299   127 178   128 770   129 535   130 1052   131 270   132 255   133 675   134 632   135 266   136 6   137 21   138 930   139 411   140 754   141 133   142 340   143 535   144 46   145 250   146 132   147 132   148 354   149 500   150 770   151 276   152 360   153 354   154 27   155 552   156 552   157 602   158 266   159 1049   160 675   161 384   162 667   163 27   164 101   165 166   166 32   167 42   168 18   169 336   170 1122   171 276   172 1122   173 266   174 50   175 178   176 276   177 1247   178 6   179 1215   180 604   181 360   182 212   183 120   184 210   185 1090   186 10   187 1090   188 266   189 66   190 250   191 266   192 360   193 120   194 128   195 178   196 770   197 92   198 634   199 38   200 21}
          329  +
          330  +do_execsql_test 4.2 {
          331  +  SELECT a, sum(b) OVER (
          332  +    PARTITION BY (b%10)
          333  +    ORDER BY b
          334  +    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
          335  +  ) FROM t2 ORDER BY a;
          336  +} {1 0   2 754   3 251   4 754   5 101   6 1247   7 132   8 266   9 6   10 950   11 667   12 1052   13 535   14 128   15 428   16 250   17 336   18 1122   19 368   20 6   21 1247   22 1000   23 92   24 368   25 584   26 320   27 1000   28 24   29 478   30 133   31 1049   32 1090   33 632   34 101   35 54   36 54   37 1049   38 450   39 145   40 354   41 21   42 764   43 754   44 424   45 1122   46 930   47 42   48 930   49 352   50 535   51 42   52 118   53 536   54 6   55 1122   56 86   57 770   58 255   59 50   60 52   61 950   62 75   63 354   64 2   65 536   66 160   67 352   68 536   69 54   70 675   71 276   72 950   73 868   74 678   75 667   76 4   77 1184   78 160   79 120   80 584   81 266   82 133   83 405   84 468   85 6   86 806   87 166   88 500   89 1090   90 552   91 251   92 27   93 424   94 687   95 1215   96 450   97 32   98 360   99 1052   100 868   101 2   102 66   103 754   104 450   105 145   106 5   107 687   108 24   109 302   110 806   111 251   112 42   113 24   114 30   115 128   116 128   117 50   118 1215   119 86   120 687   121 683   122 672   123 178   124 24   125 24   126 299   127 178   128 770   129 535   130 1052   131 270   132 255   133 675   134 632   135 266   136 6   137 21   138 930   139 411   140 754   141 133   142 340   143 535   144 46   145 250   146 132   147 132   148 354   149 500   150 770   151 276   152 360   153 354   154 27   155 552   156 552   157 602   158 266   159 1049   160 675   161 384   162 667   163 27   164 101   165 166   166 32   167 42   168 18   169 336   170 1122   171 276   172 1122   173 266   174 50   175 178   176 276   177 1247   178 6   179 1215   180 604   181 360   182 212   183 120   184 210   185 1090   186 10   187 1090   188 266   189 66   190 250   191 266   192 360   193 120   194 128   195 178   196 770   197 92   198 634   199 38   200 21}
          337  +
          338  +do_execsql_test 4.3 {
          339  +  SELECT b, sum(b) OVER (
          340  +    ORDER BY b
          341  +    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
          342  +  ) FROM t2 ORDER BY b;
          343  +} {0 0   1 1   1 2   2 4   2 6   2 8   3 11   3 14   4 18   5 23   6 29   7 36   7 43   7 50   8 58   8 66   8 74   9 83   9 92   9 101   10 111   11 122   11 133   12 145   12 157   12 169   13 182   13 195   14 209   15 224   15 239   15 254   16 270   16 286   16 302   17 319   19 338   20 358   21 379   21 400   22 422   22 444   23 467   23 490   23 513   24 537   25 562   26 588   26 614   26 640   27 667   27 694   28 722   29 751   29 780   29 809   30 839   30 869   30 899   31 930   31 961   32 993   33 1026   33 1059   33 1092   33 1125   33 1158   34 1192   34 1226   34 1260   34 1294   35 1329   35 1364   36 1400   36 1436   36 1472   36 1508   37 1545   37 1582   38 1620   38 1658   39 1697   39 1736   39 1775   40 1815   41 1856   41 1897   41 1938   42 1980   43 2023   43 2066   44 2110   44 2154   46 2200   46 2246   47 2293   47 2340   47 2387   47 2434   49 2483   50 2533   51 2584   52 2636   53 2689   54 2743   55 2798   55 2853   56 2909   56 2965   56 3021   57 3078   58 3136   58 3194   58 3252   58 3310   59 3369   59 3428   59 3487   59 3546   60 3606   61 3667   61 3728   62 3790   62 3852   63 3915   64 3979   65 4044   65 4109   65 4174   66 4240   67 4307   68 4375   69 4444   70 4514   72 4586   72 4658   72 4730   73 4803   73 4876   73 4949   74 5023   74 5097   74 5171   74 5245   74 5319   75 5394   75 5469   75 5544   76 5620   77 5697   77 5774   78 5852   78 5930   79 6009   80 6089   80 6169   81 6250   81 6331   81 6412   82 6494   83 6577   84 6661   84 6745   84 6829   84 6913   85 6998   85 7083   85 7168   86 7254   87 7341   87 7428   88 7516   89 7605   89 7694   89 7783   90 7873   90 7963   90 8053   91 8144   91 8235   91 8326   91 8417   91 8508   93 8601   93 8694   93 8787   94 8881   95 8976   95 9071   95 9166   96 9262   96 9358   96 9454   97 9551   97 9648   98 9746   98 9844   99 9943   99 10042   99 10141}
          344  +
          345  +do_execsql_test 4.4 {
          346  +  SELECT b, sum(b) OVER (
          347  +    ORDER BY b
          348  +    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
          349  +  ) FROM t2 ORDER BY b;
          350  +} {0 0   1 1   1 2   2 4   2 6   2 8   3 11   3 14   4 18   5 23   6 29   7 36   7 43   7 50   8 58   8 66   8 74   9 83   9 92   9 101   10 111   11 122   11 133   12 145   12 157   12 169   13 182   13 195   14 209   15 224   15 239   15 254   16 270   16 286   16 302   17 319   19 338   20 358   21 379   21 400   22 422   22 444   23 467   23 490   23 513   24 537   25 562   26 588   26 614   26 640   27 667   27 694   28 722   29 751   29 780   29 809   30 839   30 869   30 899   31 930   31 961   32 993   33 1026   33 1059   33 1092   33 1125   33 1158   34 1192   34 1226   34 1260   34 1294   35 1329   35 1364   36 1400   36 1436   36 1472   36 1508   37 1545   37 1582   38 1620   38 1658   39 1697   39 1736   39 1775   40 1815   41 1856   41 1897   41 1938   42 1980   43 2023   43 2066   44 2110   44 2154   46 2200   46 2246   47 2293   47 2340   47 2387   47 2434   49 2483   50 2533   51 2584   52 2636   53 2689   54 2743   55 2798   55 2853   56 2909   56 2965   56 3021   57 3078   58 3136   58 3194   58 3252   58 3310   59 3369   59 3428   59 3487   59 3546   60 3606   61 3667   61 3728   62 3790   62 3852   63 3915   64 3979   65 4044   65 4109   65 4174   66 4240   67 4307   68 4375   69 4444   70 4514   72 4586   72 4658   72 4730   73 4803   73 4876   73 4949   74 5023   74 5097   74 5171   74 5245   74 5319   75 5394   75 5469   75 5544   76 5620   77 5697   77 5774   78 5852   78 5930   79 6009   80 6089   80 6169   81 6250   81 6331   81 6412   82 6494   83 6577   84 6661   84 6745   84 6829   84 6913   85 6998   85 7083   85 7168   86 7254   87 7341   87 7428   88 7516   89 7605   89 7694   89 7783   90 7873   90 7963   90 8053   91 8144   91 8235   91 8326   91 8417   91 8508   93 8601   93 8694   93 8787   94 8881   95 8976   95 9071   95 9166   96 9262   96 9358   96 9454   97 9551   97 9648   98 9746   98 9844   99 9943   99 10042   99 10141}
   265    351   
   266    352   finish_test