/ Check-in [c155125f]
Login

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

Overview
Comment:Add support for chaining of WINDOW definitions.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | window-functions
Files: files | file ages | folders
SHA3-256: c155125fd5dddb438c09d40f5137c47d88defb7a6ede4261f09d7bdaad250d37
User & Date: dan 2019-02-16 17:27:51
Wiki:window-functions
Context
2019-03-04
21:07
Support some "ROWS BETWEEN N PRECEDING AND M FOLLOWING" window functions without caching entire partitions. check-in: e7a91f12 user: dan tags: window-functions
2019-02-16
17:27
Add support for chaining of WINDOW definitions. check-in: c155125f user: dan tags: window-functions
2019-02-14
15:27
Improved oversized cell detection when updating ptrmap pages in balance_nonroot(). check-in: aa61435a user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/parse.y.

  1629   1629   //
  1630   1630   %ifndef SQLITE_OMIT_WINDOWFUNC
  1631   1631   %type windowdefn_list {Window*}
  1632   1632   %destructor windowdefn_list {sqlite3WindowListDelete(pParse->db, $$);}
  1633   1633   windowdefn_list(A) ::= windowdefn(Z). { A = Z; }
  1634   1634   windowdefn_list(A) ::= windowdefn_list(Y) COMMA windowdefn(Z). {
  1635   1635     assert( Z!=0 );
         1636  +  sqlite3WindowChain(pParse, Z, Y);
  1636   1637     Z->pNextWin = Y;
  1637   1638     A = Z;
  1638   1639   }
  1639   1640   
  1640   1641   %type windowdefn {Window*}
  1641   1642   %destructor windowdefn {sqlite3WindowDelete(pParse->db, $$);}
  1642         -windowdefn(A) ::= nm(X) AS window(Y). {
         1643  +windowdefn(A) ::= nm(X) AS LP window(Y) RP. {
  1643   1644     if( ALWAYS(Y) ){
  1644   1645       Y->zName = sqlite3DbStrNDup(pParse->db, X.z, X.n);
  1645   1646     }
  1646   1647     A = Y;
  1647   1648   }
  1648   1649   
  1649   1650   %type window {Window*}
................................................................................
  1663   1664   %type frame_bound {struct FrameBound}
  1664   1665   %destructor frame_bound {sqlite3ExprDelete(pParse->db, $$.pExpr);}
  1665   1666   %type frame_bound_s {struct FrameBound}
  1666   1667   %destructor frame_bound_s {sqlite3ExprDelete(pParse->db, $$.pExpr);}
  1667   1668   %type frame_bound_e {struct FrameBound}
  1668   1669   %destructor frame_bound_e {sqlite3ExprDelete(pParse->db, $$.pExpr);}
  1669   1670   
  1670         -window(A) ::= LP part_opt(X) orderby_opt(Y) frame_opt(Z) RP. {
         1671  +window(A) ::= PARTITION BY nexprlist(X) orderby_opt(Y) frame_opt(Z). {
         1672  +  A = sqlite3WindowAssemble(pParse, Z, X, Y, 0);
         1673  +}
         1674  +window(A) ::= nm(W) PARTITION BY nexprlist(X) orderby_opt(Y) frame_opt(Z). {
         1675  +  A = sqlite3WindowAssemble(pParse, Z, X, Y, &W);
         1676  +}
         1677  +window(A) ::= ORDER BY sortlist(Y) frame_opt(Z). {
         1678  +  A = sqlite3WindowAssemble(pParse, Z, 0, Y, 0);
         1679  +}
         1680  +window(A) ::= nm(W) ORDER BY sortlist(Y) frame_opt(Z). {
         1681  +  A = sqlite3WindowAssemble(pParse, Z, 0, Y, &W);
         1682  +}
         1683  +window(A) ::= frame_opt(Z). {
  1671   1684     A = Z;
  1672         -  if( ALWAYS(A) ){
  1673         -    A->pPartition = X;
  1674         -    A->pOrderBy = Y;
  1675         -  }
         1685  +}
         1686  +window(A) ::= nm(W) frame_opt(Z). {
         1687  +  A = sqlite3WindowAssemble(pParse, Z, 0, 0, &W);
  1676   1688   }
  1677   1689   
  1678         -part_opt(A) ::= PARTITION BY nexprlist(X). { A = X; }
  1679         -part_opt(A) ::= .                          { A = 0; }
  1680         -
  1681   1690   frame_opt(A) ::= .                             { 
  1682         -  A = sqlite3WindowAlloc(pParse, TK_RANGE, TK_UNBOUNDED, 0, TK_CURRENT, 0);
         1691  +  A = sqlite3WindowAlloc(pParse, 0, TK_UNBOUNDED, 0, TK_CURRENT, 0);
  1683   1692   }
  1684   1693   frame_opt(A) ::= range_or_rows(X) frame_bound_s(Y). { 
  1685   1694     A = sqlite3WindowAlloc(pParse, X, Y.eType, Y.pExpr, TK_CURRENT, 0);
  1686   1695   }
  1687   1696   frame_opt(A) ::= range_or_rows(X) BETWEEN frame_bound_s(Y) AND frame_bound_e(Z). { 
  1688   1697     A = sqlite3WindowAlloc(pParse, X, Y.eType, Y.pExpr, Z.eType, Z.pExpr);
  1689   1698   }
................................................................................
  1703   1712   
  1704   1713   %type window_clause {Window*}
  1705   1714   %destructor window_clause {sqlite3WindowListDelete(pParse->db, $$);}
  1706   1715   window_clause(A) ::= WINDOW windowdefn_list(B). { A = B; }
  1707   1716   
  1708   1717   %type over_clause {Window*}
  1709   1718   %destructor over_clause {sqlite3WindowDelete(pParse->db, $$);}
  1710         -over_clause(A) ::= filter_opt(W) OVER window(Z). {
         1719  +over_clause(A) ::= filter_opt(W) OVER LP window(Z) RP. {
  1711   1720     A = Z;
  1712   1721     assert( A!=0 );
  1713   1722     A->pFilter = W;
  1714   1723   }
  1715   1724   over_clause(A) ::= filter_opt(W) OVER nm(Z). {
  1716   1725     A = (Window*)sqlite3DbMallocZero(pParse->db, sizeof(Window));
  1717   1726     if( A ){

Changes to src/sqliteInt.h.

  3551   3551   **       object on a linked list attached to Select.pWinDefn.
  3552   3552   **
  3553   3553   ** The uses (1) and (2) are really the same Window object that just happens
  3554   3554   ** to be accessible in two different ways.  Use (3) is are separate objects.
  3555   3555   */
  3556   3556   struct Window {
  3557   3557     char *zName;            /* Name of window (may be NULL) */
         3558  +  char *zBase;            /* Name of base window for chaining (may be NULL) */
  3558   3559     ExprList *pPartition;   /* PARTITION BY clause */
  3559   3560     ExprList *pOrderBy;     /* ORDER BY clause */
  3560   3561     u8 eType;               /* TK_RANGE or TK_ROWS */
  3561   3562     u8 eStart;              /* UNBOUNDED, CURRENT, PRECEDING or FOLLOWING */
  3562   3563     u8 eEnd;                /* UNBOUNDED, CURRENT, PRECEDING or FOLLOWING */
         3564  +  u8 bImplicitFrame;      /* True if frame was implicitly specified */
  3563   3565     Expr *pStart;           /* Expression for "<expr> PRECEDING" */
  3564   3566     Expr *pEnd;             /* Expression for "<expr> FOLLOWING" */
  3565   3567     Window *pNextWin;       /* Next window function belonging to this SELECT */
  3566   3568     Expr *pFilter;          /* The FILTER expression */
  3567   3569     FuncDef *pFunc;         /* The function */
  3568   3570     int iEphCsr;            /* Partition buffer or Peer buffer */
  3569   3571     int regAccum;
................................................................................
  3587   3589   void sqlite3WindowCodeStep(Parse*, Select*, WhereInfo*, int, int);
  3588   3590   int sqlite3WindowRewrite(Parse*, Select*);
  3589   3591   int sqlite3ExpandSubquery(Parse*, struct SrcList_item*);
  3590   3592   void sqlite3WindowUpdate(Parse*, Window*, Window*, FuncDef*);
  3591   3593   Window *sqlite3WindowDup(sqlite3 *db, Expr *pOwner, Window *p);
  3592   3594   Window *sqlite3WindowListDup(sqlite3 *db, Window *p);
  3593   3595   void sqlite3WindowFunctions(void);
         3596  +void sqlite3WindowChain(Parse*, Window*, Window*);
         3597  +Window *sqlite3WindowAssemble(Parse*, Window*, ExprList*, ExprList*, Token*);
  3594   3598   #else
  3595   3599   # define sqlite3WindowDelete(a,b)
  3596   3600   # define sqlite3WindowFunctions()
  3597   3601   # define sqlite3WindowAttach(a,b,c)
  3598   3602   #endif
  3599   3603   
  3600   3604   /*

Changes to src/window.c.

   507    507       WINDOWFUNCNOOP(lead, 3, 0),
   508    508       WINDOWFUNCNOOP(lag, 1, 0),
   509    509       WINDOWFUNCNOOP(lag, 2, 0),
   510    510       WINDOWFUNCNOOP(lag, 3, 0),
   511    511     };
   512    512     sqlite3InsertBuiltinFuncs(aWindowFuncs, ArraySize(aWindowFuncs));
   513    513   }
          514  +
          515  +static Window *windowFind(Parse *pParse, Window *pList, const char *zName){
          516  +  Window *p;
          517  +  for(p=pList; p; p=p->pNextWin){
          518  +    if( sqlite3StrICmp(p->zName, zName)==0 ) break;
          519  +  }
          520  +  if( p==0 ){
          521  +    sqlite3ErrorMsg(pParse, "no such window: %s", zName);
          522  +  }
          523  +  return p;
          524  +}
   514    525   
   515    526   /*
   516    527   ** This function is called immediately after resolving the function name
   517    528   ** for a window function within a SELECT statement. Argument pList is a
   518    529   ** linked list of WINDOW definitions for the current SELECT statement.
   519    530   ** Argument pFunc is the function definition just resolved and pWin
   520    531   ** is the Window object representing the associated OVER clause. This
................................................................................
   532    543   void sqlite3WindowUpdate(
   533    544     Parse *pParse, 
   534    545     Window *pList,                  /* List of named windows for this SELECT */
   535    546     Window *pWin,                   /* Window frame to update */
   536    547     FuncDef *pFunc                  /* Window function definition */
   537    548   ){
   538    549     if( pWin->zName && pWin->eType==0 ){
   539         -    Window *p;
   540         -    for(p=pList; p; p=p->pNextWin){
   541         -      if( sqlite3StrICmp(p->zName, pWin->zName)==0 ) break;
   542         -    }
   543         -    if( p==0 ){
   544         -      sqlite3ErrorMsg(pParse, "no such window: %s", pWin->zName);
   545         -      return;
   546         -    }
          550  +    Window *p = windowFind(pParse, pList, pWin->zName);
          551  +    if( p==0 ) return;
   547    552       pWin->pPartition = sqlite3ExprListDup(pParse->db, p->pPartition, 0);
   548    553       pWin->pOrderBy = sqlite3ExprListDup(pParse->db, p->pOrderBy, 0);
   549    554       pWin->pStart = sqlite3ExprDup(pParse->db, p->pStart, 0);
   550    555       pWin->pEnd = sqlite3ExprDup(pParse->db, p->pEnd, 0);
   551    556       pWin->eStart = p->eStart;
   552    557       pWin->eEnd = p->eEnd;
   553    558       pWin->eType = p->eType;
          559  +  }else{
          560  +    sqlite3WindowChain(pParse, pWin, pList);
   554    561     }
   555    562     if( pFunc->funcFlags & SQLITE_FUNC_WINDOW ){
   556    563       sqlite3 *db = pParse->db;
   557    564       if( pWin->pFilter ){
   558    565         sqlite3ErrorMsg(pParse, 
   559    566             "FILTER clause may only be used with aggregate window functions"
   560    567         );
................................................................................
   852    859     if( p ){
   853    860       sqlite3ExprDelete(db, p->pFilter);
   854    861       sqlite3ExprListDelete(db, p->pPartition);
   855    862       sqlite3ExprListDelete(db, p->pOrderBy);
   856    863       sqlite3ExprDelete(db, p->pEnd);
   857    864       sqlite3ExprDelete(db, p->pStart);
   858    865       sqlite3DbFree(db, p->zName);
          866  +    sqlite3DbFree(db, p->zBase);
   859    867       sqlite3DbFree(db, p);
   860    868     }
   861    869   }
   862    870   
   863    871   /*
   864    872   ** Free the linked list of Window objects starting at the second argument.
   865    873   */
................................................................................
   895    903     int eType,        /* Frame type. TK_RANGE or TK_ROWS */
   896    904     int eStart,       /* Start type: CURRENT, PRECEDING, FOLLOWING, UNBOUNDED */
   897    905     Expr *pStart,     /* Start window size if TK_PRECEDING or FOLLOWING */
   898    906     int eEnd,         /* End type: CURRENT, FOLLOWING, TK_UNBOUNDED, PRECEDING */
   899    907     Expr *pEnd        /* End window size if TK_FOLLOWING or PRECEDING */
   900    908   ){
   901    909     Window *pWin = 0;
          910  +  int bImplicitFrame = 0;
   902    911   
   903    912     /* Parser assures the following: */
   904         -  assert( eType==TK_RANGE || eType==TK_ROWS );
          913  +  assert( eType==0 || eType==TK_RANGE || eType==TK_ROWS );
   905    914     assert( eStart==TK_CURRENT || eStart==TK_PRECEDING
   906    915              || eStart==TK_UNBOUNDED || eStart==TK_FOLLOWING );
   907    916     assert( eEnd==TK_CURRENT || eEnd==TK_FOLLOWING
   908    917              || eEnd==TK_UNBOUNDED || eEnd==TK_PRECEDING );
   909    918     assert( (eStart==TK_PRECEDING || eStart==TK_FOLLOWING)==(pStart!=0) );
   910    919     assert( (eEnd==TK_FOLLOWING || eEnd==TK_PRECEDING)==(pEnd!=0) );
   911    920   
          921  +  if( eType==0 ){
          922  +    bImplicitFrame = 1;
          923  +    eType = TK_RANGE;
          924  +  }
   912    925   
   913    926     /* If a frame is declared "RANGE" (not "ROWS"), then it may not use
   914    927     ** either "<expr> PRECEDING" or "<expr> FOLLOWING".
   915    928     */
   916    929     if( eType==TK_RANGE && (pStart!=0 || pEnd!=0) ){
   917    930       sqlite3ErrorMsg(pParse, "RANGE must use only UNBOUNDED or CURRENT ROW");
   918    931       goto windowAllocErr;
................................................................................
   940    953     }
   941    954   
   942    955     pWin = (Window*)sqlite3DbMallocZero(pParse->db, sizeof(Window));
   943    956     if( pWin==0 ) goto windowAllocErr;
   944    957     pWin->eType = eType;
   945    958     pWin->eStart = eStart;
   946    959     pWin->eEnd = eEnd;
          960  +  pWin->bImplicitFrame = bImplicitFrame;
   947    961     pWin->pEnd = sqlite3WindowOffsetExpr(pParse, pEnd);
   948    962     pWin->pStart = sqlite3WindowOffsetExpr(pParse, pStart);
   949    963     return pWin;
   950    964   
   951    965   windowAllocErr:
   952    966     sqlite3ExprDelete(pParse->db, pEnd);
   953    967     sqlite3ExprDelete(pParse->db, pStart);
   954    968     return 0;
   955    969   }
          970  +
          971  +/*
          972  +** Attach PARTITION and ORDER BY clauses pPartition and pOrderBy to window
          973  +** pWin. Also, if parameter pBase is not NULL, set pWin->zBase to the
          974  +** equivalent nul-terminated string.
          975  +*/
          976  +Window *sqlite3WindowAssemble(
          977  +  Parse *pParse, 
          978  +  Window *pWin, 
          979  +  ExprList *pPartition, 
          980  +  ExprList *pOrderBy, 
          981  +  Token *pBase
          982  +){
          983  +  if( pWin ){
          984  +    pWin->pPartition = pPartition;
          985  +    pWin->pOrderBy = pOrderBy;
          986  +    if( pBase ){
          987  +      pWin->zBase = sqlite3DbStrNDup(pParse->db, pBase->z, pBase->n);
          988  +    }
          989  +  }else{
          990  +    sqlite3ExprListDelete(pParse->db, pPartition);
          991  +    sqlite3ExprListDelete(pParse->db, pOrderBy);
          992  +  }
          993  +  return pWin;
          994  +}
          995  +
          996  +/*
          997  +** Window *pWin has just been created from a WINDOW clause. Tokne pBase
          998  +** is the base window. Earlier windows from the same WINDOW clause are
          999  +** stored in the linked list starting at pWin->pNextWin. This function
         1000  +** either updates *pWin according to the base specification, or else
         1001  +** leaves an error in pParse.
         1002  +*/
         1003  +void sqlite3WindowChain(Parse *pParse, Window *pWin, Window *pList){
         1004  +  if( pWin->zBase ){
         1005  +    sqlite3 *db = pParse->db;
         1006  +    Window *pExist = windowFind(pParse, pList, pWin->zBase);
         1007  +    if( pExist ){
         1008  +      const char *zErr = 0;
         1009  +      /* Check for errors */
         1010  +      if( pWin->pPartition ){
         1011  +        zErr = "PARTITION clause";
         1012  +      }else if( pExist->pOrderBy && pWin->pOrderBy ){
         1013  +        zErr = "ORDER BY clause";
         1014  +      }else if( pExist->bImplicitFrame==0 ){
         1015  +        zErr = "frame specification";
         1016  +      }
         1017  +      if( zErr ){
         1018  +        sqlite3ErrorMsg(pParse, 
         1019  +            "cannot override %s of window: %s", zErr, pWin->zBase
         1020  +        );
         1021  +      }else{
         1022  +        pWin->pPartition = sqlite3ExprListDup(db, pExist->pPartition, 0);
         1023  +        if( pExist->pOrderBy ){
         1024  +          assert( pWin->pOrderBy==0 );
         1025  +          pWin->pOrderBy = sqlite3ExprListDup(db, pExist->pOrderBy, 0);
         1026  +        }
         1027  +        sqlite3DbFree(db, pWin->zBase);
         1028  +        pWin->zBase = 0;
         1029  +      }
         1030  +    }
         1031  +  }
         1032  +}
   956   1033   
   957   1034   /*
   958   1035   ** Attach window object pWin to expression p.
   959   1036   */
   960   1037   void sqlite3WindowAttach(Parse *pParse, Expr *p, Window *pWin){
   961   1038     if( p ){
   962   1039       assert( p->op==TK_FUNCTION );

Changes to test/window1.test.

   696    696     WINDOW w1 AS (PARTITION BY b IN (SELECT rowid FROM t7));
   697    697   } {
   698    698     2 10
   699    699     1 101
   700    700     3 101
   701    701   }
   702    702   
          703  +#-------------------------------------------------------------------------
          704  +# Test error cases from chaining window definitions.
          705  +#
          706  +reset_db
          707  +do_execsql_test 17.0 {
          708  +  DROP TABLE IF EXISTS t1;
          709  +  CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d INTEGER);
          710  +  INSERT INTO t1 VALUES(1, 'odd',  'one',   1);
          711  +  INSERT INTO t1 VALUES(2, 'even', 'two',   2);
          712  +  INSERT INTO t1 VALUES(3, 'odd',  'three', 3);
          713  +  INSERT INTO t1 VALUES(4, 'even', 'four',  4);
          714  +  INSERT INTO t1 VALUES(5, 'odd',  'five',  5);
          715  +  INSERT INTO t1 VALUES(6, 'even', 'six',   6);
          716  +}
          717  +
          718  +foreach {tn sql error} {
          719  +  1 {
          720  +    SELECT c, sum(d) OVER win2 FROM t1
          721  +      WINDOW win1 AS (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING), 
          722  +             win2 AS (win1 ORDER BY b)
          723  +  } {cannot override frame specification of window: win1}
          724  +
          725  +  2 {
          726  +    SELECT c, sum(d) OVER win2 FROM t1
          727  +      WINDOW win1 AS (),
          728  +             win2 AS (win4 ORDER BY b)
          729  +  } {no such window: win4}
          730  +
          731  +  3 {
          732  +    SELECT c, sum(d) OVER win2 FROM t1
          733  +      WINDOW win1 AS (),
          734  +             win2 AS (win1 PARTITION BY d)
          735  +  } {cannot override PARTITION clause of window: win1}
          736  +
          737  +  4 {
          738  +    SELECT c, sum(d) OVER win2 FROM t1
          739  +      WINDOW win1 AS (ORDER BY b),
          740  +             win2 AS (win1 ORDER BY d)
          741  +  } {cannot override ORDER BY clause of window: win1}
          742  +} {
          743  +  do_catchsql_test 17.1.$tn $sql [list 1 $error]
          744  +}
          745  +
          746  +foreach {tn sql error} {
          747  +  1 {
          748  +    SELECT c, sum(d) OVER (win1 ORDER BY b) FROM t1
          749  +      WINDOW win1 AS (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
          750  +  } {cannot override frame specification of window: win1}
          751  +
          752  +  2 {
          753  +    SELECT c, sum(d) OVER (win4 ORDER BY b) FROM t1
          754  +      WINDOW win1 AS ()
          755  +  } {no such window: win4}
          756  +
          757  +  3 {
          758  +    SELECT c, sum(d) OVER (win1 PARTITION BY d) FROM t1
          759  +      WINDOW win1 AS ()
          760  +  } {cannot override PARTITION clause of window: win1}
          761  +
          762  +  4 {
          763  +    SELECT c, sum(d) OVER (win1 ORDER BY d) FROM t1
          764  +      WINDOW win1 AS (ORDER BY b)
          765  +  } {cannot override ORDER BY clause of window: win1}
          766  +} {
          767  +  do_catchsql_test 17.2.$tn $sql [list 1 $error]
          768  +}
          769  +
          770  +do_execsql_test 17.3.1 {
          771  +  SELECT group_concat(c, '.') OVER (PARTITION BY b ORDER BY c)
          772  +  FROM t1
          773  +} {four four.six four.six.two five five.one five.one.three}
          774  +
          775  +do_execsql_test 17.3.2 {
          776  +  SELECT group_concat(c, '.') OVER (win1 ORDER BY c)
          777  +  FROM t1
          778  +  WINDOW win1 AS (PARTITION BY b)
          779  +} {four four.six four.six.two five five.one five.one.three}
          780  +
          781  +do_execsql_test 17.3.3 {
          782  +  SELECT group_concat(c, '.') OVER win2
          783  +  FROM t1
          784  +  WINDOW win1 AS (PARTITION BY b),
          785  +         win2 AS (win1 ORDER BY c)
          786  +} {four four.six four.six.two five five.one five.one.three}
          787  +
          788  +do_execsql_test 17.3.4 {
          789  +  SELECT group_concat(c, '.') OVER (win2)
          790  +  FROM t1
          791  +  WINDOW win1 AS (PARTITION BY b),
          792  +         win2 AS (win1 ORDER BY c)
          793  +} {four four.six four.six.two five five.one five.one.three}
          794  +
          795  +do_execsql_test 17.3.5 {
          796  +  SELECT group_concat(c, '.') OVER win5
          797  +  FROM t1
          798  +  WINDOW win1 AS (PARTITION BY b),
          799  +         win2 AS (win1),
          800  +         win3 AS (win2),
          801  +         win4 AS (win3),
          802  +         win5 AS (win4 ORDER BY c)
          803  +} {four four.six four.six.two five five.one five.one.three}
   703    804   
   704    805   finish_test

Changes to test/window3.tcl.

    17     17   start_test window3 "2018 May 31"
    18     18   ifcapable !windowfunc
    19     19   
    20     20   execsql_test 1.0 {
    21     21     DROP TABLE IF EXISTS t2;
    22     22     CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER);
    23     23     INSERT INTO t2(a, b) VALUES
    24         -  (1,0), (2,74), (3,41), (4,74), (5,23), (6,99), (7,26), (8,33), (9,2),
    25     24     (10,89), (11,81), (12,96), (13,59), (14,38), (15,68), (16,39), (17,62),
    26     25     (18,91), (19,46), (20,6), (21,99), (22,97), (23,27), (24,46), (25,78),
    27     26     (26,54), (27,97), (28,8), (29,67), (30,29), (31,93), (32,84), (33,77),
    28     27     (34,23), (35,16), (36,16), (37,93), (38,65), (39,35), (40,47), (41,7),
    29     28     (42,86), (43,74), (44,61), (45,91), (46,85), (47,24), (48,85), (49,43),
    30     29     (50,59), (51,12), (52,32), (53,56), (54,3), (55,91), (56,22), (57,90),
    31     30     (58,55), (59,15), (60,28), (61,89), (62,25), (63,47), (64,1), (65,56),
................................................................................
   302    301     "
   303    302     execsql_test 1.$tn.14.5 "
   304    303       SELECT string_agg(CAST(b AS TEXT), '.') OVER ( ORDER BY b%10,a $window ) FROM t2
   305    304     "
   306    305     execsql_test 1.$tn.14.6 "
   307    306       SELECT string_agg(CAST(b AS TEXT), '.') OVER (PARTITION BY b%2,a ORDER BY b%10 $window) FROM t2
   308    307     "
          308  +
          309  +  execsql_test 1.$tn.14.7 "
          310  +    SELECT string_agg(CAST(b AS TEXT), '.') OVER (win1 ORDER BY b%10 $window) 
          311  +    FROM t2
          312  +    WINDOW win1 AS (PARTITION BY b%2,a)
          313  +    ORDER BY 1
          314  +  "
          315  +
          316  +  execsql_test 1.$tn.14.8 "
          317  +    SELECT string_agg(CAST(b AS TEXT), '.') OVER (win1 $window) 
          318  +    FROM t2
          319  +    WINDOW win1 AS (PARTITION BY b%2,a ORDER BY b%10)
          320  +    ORDER BY 1
          321  +  "
          322  +
          323  +  execsql_test 1.$tn.14.9 "
          324  +    SELECT string_agg(CAST(b AS TEXT), '.') OVER win2
          325  +    FROM t2
          326  +    WINDOW win1 AS (PARTITION BY b%2,a ORDER BY b%10),
          327  +           win2 AS (win1 $window)
          328  +    ORDER BY 1
          329  +  "
   309    330   
   310    331     execsql_test 1.$tn.15.1 "
   311    332       SELECT count(*) OVER win, string_agg(CAST(b AS TEXT), '.') 
   312    333       FILTER (WHERE a%2=0) OVER win FROM t2
   313    334       WINDOW win AS (ORDER BY a $window)
   314    335     "
   315    336   

Changes to test/window3.test.

cannot compute difference between binary files